Странное поведение CountIf() для шестнадцатеричных значений
Среда: Excel 2021 под Windows 11 Pro 64.
Мы знаем, что функция CountIf() в Excel имеет неприятное поведение: преобразует текст, похожий на число, в число перед подсчетом. Но теперь оказывается, что он даже неправильно выполняет эти преобразования для шестнадцатеричных значений.
Рассмотрим следующую таблицу:
Мы видим, что:
- Правильно:
1в столбце «Шестнадцатеричный счет» для0указывает, что все остальное в столбце «Шестнадцатеричный» не рассматривается как равное нулю, и это правильно. - Правильно: два
1в столбце «Шестнадцатеричный счет» дляE0иE1означают, что они не считаются эквивалентами чисел, и это правильно. - Правильно и спорно: три
3в столбце «Отсчет шестнадцатеричных 4» указывают на то, что Excel рассматривает0000,00E0и00E1как равные друг другу. Это верно для0000и00E1, которые оба равны нулю. Это спорно для00E0, что неоднозначно, но обычно принимается равным 1. - Неверно:
2в каждом столбце «Посчитать шестнадцатеричный» и «Посчитать шестнадцатеричный 4» для1и0001указывают, что каждое из этих значений рассматривается как равное одному другому значению в своих столбцах, что неверно, поскольку в обоих есть много других значений. столбцы, равные1. - Неправильно:
1в каждом столбце «Посчитать шестнадцатеричный» и «Посчитать шестнадцатеричный 4» для1E3и01E3указывают, что каждое из этих значений рассматривается как НЕ равное любому другому значению в своих столбцах, что неверно, поскольку они оба равны1и, следовательно, равен многим другим значениям в обоих столбцах. - Неправильно: остальные десять
2в таблице не имеют смысла, поскольку все они относятся к шестнадцатеричным значениям, равным1. Они все должны быть7.
Это еще одна ошибка в Excel или есть какое-то разумное объяснение такому поведению?
Ответы 1
Здесь все работает так, как ожидалось. Путаница возникает из-за экспоненциальной записи: если x и y являются числами, xEy означает «x, умноженное на 10 в степени y», а не «x в степени y».
Например, 1E2 означает «1 раз 10^2» = 1 * 100 = 100, а 0E0 однозначно означает «0, умноженное на 10^0», что равно 0 (поскольку 10^0 четко определяется как 1).
Таким образом, неявное преобразование шестнадцатеричных значений в числа в Excel выглядит следующим образом:
- Е0/Е1 – недействителен. (Они будут рассматриваться как текст
COUNTIFS().) - 1E1, 1E2, 1E3 равны 10, 1000 и 1000 соответственно.
- 1E00, 1E01 и 1E02 равны 1, 10 и 100 соответственно.
- 00E0 и 00E1 равны 0 (соответственно 0 * 10^0 и 0 * 10^1).
- 01E1, 01E2 и 01E3 идентичны 1E1, 1E2 и 1E3 (10, 100, 1000).
- 1E00, 1E01 и 1E02 идентичны 1E0, 1E1 и 1E2 (1, 10, 100).
Чтобы увидеть неявные преобразования в Excel, попробуйте добавить два новых столбца, которые применяют операцию *1 к столбцам Hex и Hex 4. Вы увидите именно те числовые значения, в которые их преобразует Excel.
Применение формулы COUNTIFS() к преобразованным столбцам будет идентично результатам в вашей таблице, за исключением того, что E0 и E1 станут ошибками #VALUE! и поэтому будут считаться равными COUNTIFS().
ОЙ!!! Я глубоко смущен. Похоже, мне нужно вернуться и снова пройти школьную математику. Или это средняя школа? Интересно, что вопрос получил два голоса «за», хотя вместо этого, вероятно, следует проголосовать за его удаление. Раньше я думал, что глупого вопроса не существует, но теперь мне кажется, что я создал блестящий пример вопроса, который можно квалифицировать как действительно глупый. Как вы думаете, мне стоит удалить это?
Не вижу ничего плохого в том, чтобы оставить это (но ведь я явно предвзят, заслужив репутацию своим ответом!). Ваш вопрос подчеркивает поведение неявного преобразования COUNTIF(), которое неочевидно, и есть много пользователей Excel, которые на самом деле не понимают неявные преобразования Excel или экспоненциальную запись. Потенциальное столкновение между шестнадцатеричными строками и экспоненциальной записью также интересно/актуально. Мой голос «за» отразил качество вопроса (в том смысле, что он был достаточно ясен, и я смог воспроизвести результаты локально в течение пары минут). Кто-то другой явно согласился!
Ха-ха! Мне потребовался час или два, чтобы тщательно сформулировать вопрос, который делает его таким глупым. Во время всех этих размышлений мне должно было прийти в голову, что я неправильно использую обозначение E! В любом случае, я оставлю этот вопрос. Надеюсь, вы правы: кому-то моя глупость может оказаться полезной.
Может быть, это связано с тем, что Excel не может преобразовать значение в десятичное число, если оно не начинается с цифры?