Изучение различных типов значений ячеек

Несмотря на все преимущества Excel, одна из самых запутанных вещей - это то, как он хранит и отображает данные.

Каждая ячейка имеет числовой формат, который можно выбрать в раскрывающемся списке на вкладке ленты Главная. Однако обычно оставляют значение по умолчанию Общие, так как оно автоматически адаптируется к значению.

Существует 11 стандартных числовых форматов, но пусть вас не обманывает название. В общих чертах их можно разделить на две группы: числа и текст. Все они, кроме Общие (которые представляют собой смесь обоих), и Текст имеют числовой вид. Забудьте о различиях вывода в форматах валюты, даты, времени и процентов; все они хранятся в виде чисел.

Если вы измените ячейку с Общие на что-то другое, изменится только видимый вывод, но не его тип хранения. Это не проблема, если ячейка изменена с одного числового типа на другой. Однако возникают проблемы, когда числа хранятся в виде текста или наоборот.

Сравните это с Google Таблицами, где изменение формата данных ячейки оказывает мгновенное влияние в зависимости от того, что вы выбрали.

То, что ячейка выглядит пустой, не означает, что это так. Я по опыту знаю, как легко попасть в ловушку из-за этого. В конечном итоге вы тратите часы, пытаясь выяснить, что пошло не так, если формула не дает ожидаемого результата. Во многом это сводится к чрезмерной зависимости от видимых результатов Excel. Слишком легко предположить, что то, что вы видите, и есть то, что вы получаете, но это не всегда так.

Здесь я исследую, как Excel обрабатывает пустые ячейки, которые на самом деле пустые, «пустые» ячейки, которые не являются пустыми, пустые строки, нулевые значения, ячейки с настраиваемым форматированием, текстовые ячейки, числовые ячейки. - среди многих других.

Смущенный? К концу все обретет смысл!

Пример рабочей книги

Затем загрузите книгу Excel:

🔗 Как-Excel-Stores-and-Displays-Data.xlsm

Для полной функциональности требуется версия Microsoft 365 Excel.

Часть 1

Часть 1 состоит из трех таблиц, которые демонстрируют, как можно проверить наличие пустых ячеек, пустых строк и нулей. Каждый из них прошел семь тестов, чтобы выяснить, возвращается ли значение ИСТИНА или ЛОЖЬ.

Испытания следующие:

  1. Пустая - пустая ячейка, внутри которой ничего нет.
  2. Пустая строка - ячейка, содержащая пустую строку (="").
  3. Невидимый символ - ячейка, содержащая односимвольный пробел. Excel имеет тенденцию добавлять ', когда вы подтверждаете подобное значение для обозначения текста; однако это поведение непоследовательно. В любом случае он не появится на выходе.
  4. Апостроф - ячейка, содержащая апостроф ('), представляющий текстовую запись.
  5. Пользовательское форматирование (;;;) - ячейка, содержащая числовое нулевое значение с примененным пользовательским форматированием ;;;. Перед первой точкой с запятой представлен формат положительных значений, второй - отрицательных, третьей - нулей, а после третьей - текста. Когда все они оставлены пустыми, вывод скрывается.
  6. 0 (Число) - ячейка, содержащая числовое нулевое значение (0).
  7. 0 (Строка) - ячейка, содержащая строку с нулем внутри (="0").

Таблица P1.1 - Проверка на пропуски

Syntax
=ISBLANK(cell reference)
Example
=ISBLANK(I11)

Функция ISBLANK проверяет каждую строку в столбце Значение, чтобы убедиться, что она пуста. Если это так, возвращается ИСТИНА, а в противном случае - ЛОЖЬ.

Функция ISBLANK делает то, что подразумевает, поскольку только строка пустая возвращает значение TRUE.

Таблица P1.2 - Проверка на пустые строки

Example
=I21=""

Как и ожидалось, строка Пустая строка возвращает значение ИСТИНА, однако также возвращает Пустая строка и Апостроф. Это доказывает, что Excel не различает пустую строку, пустую ячейку и апостроф.

Таблица P1.3 - Проверка нулей

Example
=I31=0

Неудивительно, что 0 (число) возвращает ИСТИНА, но также возвращает Пусто и Пользовательский формат (;;;). . Это связано с тем, что нулевые значения обрабатываются так же, как пустые ячейки. Более того, пусть вас не вводит в заблуждение отсутствие видимого нуля для Custom Formatted (;;;).

Часть 2

В этой части основное внимание уделяется подсчету значений, чтобы увидеть, как реагируют формулы.

Таблица P2.1 содержит семь столбцов:

  1. Пусто - пустые ячейки.
  2. Пустая строка - ячейки, содержащие ="".
  3. Невидимый символ - ячейки, содержащие один пробел.
  4. Апостроф - ячейки, содержащие апостроф.
  5. Число - числовые значения, например 1, 2, 3 и т. Д.
  6. Текст - текстовые значения, например A, B, C и т. Д.
  7. Число в произвольном формате - то же, что и Число, за исключением того, что к каждой ячейке применено пользовательское форматирование ;;;.
  8. Пользовательский форматированный текст - то же, что и Текст, за исключением того, что значения не отображаются, поскольку к каждой ячейке применено пользовательское форматирование ;;;.

В Таблице P2.2 перечислены 15 формул, протестированных для этих столбцов.

В методах 1–10 используются варианты формулы СЧЁТЕСЛИ, при этом аргумент criteria использует операторы сравнения и символы.

Что все это значит? Во-первых, вам нужно знать, что такое ASCII.

ASCII - это 7-битный набор кодировок. Всего 256 символов, и в основном он состоит из алфавита, цифр и символов, которые вы прямо или косвенно найдете на клавиатуре:

  • 0–31 - управляющие символы, которые не печатаются.
  • 32–127 - печатные символы.
  • 128–255 - это расширенные коды, которые различаются в зависимости от набора символов, который использует ваш компьютер. Windows-1252 - самая распространенная в Windows.

Используйте функцию CHAR, чтобы отобразить символ, указав его десятичное число ASCII. И наоборот, функция КОД выведет номер данного символа.

Например, =CHAR(42) отображает звездочку, а =CODE("*") возвращает 42.

Хотя коды ASCII одинаковы для всех символов, за исключением непечатаемых символов, способ их сортировки в Excel отличается.

На листе Дополнительные возможности 2 есть три таблицы:

  1. Таблица P2E.1 - левая половина содержит исходный список кодов ASCII, отсортированный по десятичным числам. Справа показано, как Excel преобразует их при использовании функций КОД и СИМВОЛ.
  2. Таблица P2E.2— Список символов Excel, отсортированный по кодовому номеру.
  3. Таблица P2E.3— Список символов Excel, отсортированный по знакам.
  4. Таблица P2E.4 - примеры сравнительных проверок символов.

Сосредоточившись на Таблице P2E.1, можно сделать несколько наблюдений:

  • Десятичные числа ASCII начинаются с нуля, тогда как символьные значения Excel начинаются с единицы. Поэтому в первой строке столбцов Код 2 и Символ возникает ошибка #VALUE!.
  • #VALUE! ошибки также возникают в столбце Код для неиспользуемых десятичных чисел. Это 129, 141, 143, 144 и 157. Однако 127 и 160 также имеют значение, несмотря на то, что они представляют собой символ.
  • Функция КОД учитывает только первый символ. Поскольку первые 31 символ ASCII содержат как минимум два, Excel вместо этого возвращает кодовый номер буквы (столбец Код). Вот почему необходимо указывать непечатаемый символ, а не ссылочное имя.

Таблица P2E.3 сортирует значения (в порядке возрастания) в таблице P2E.2 в соответствии со столбцом Символ.

Что интересно, так это то, насколько отличается порядок упорядочивания по сравнению с исходным списком ASCII. Код 173, который представляет собой мягкий дефис, является первым символом, за которым следуют непечатаемые символы, символы, числа и, наконец, буквы.

Одно ключевое отличие от символов Excel заключается в том, что все варианты каждой буквы алфавита сгруппированы вместе. Например, «А» предшествует «а», несмотря на то, что первый имеет номер символа 65 по сравнению с 97 вторым.

Разобравшись с этим, давайте быстро рассмотрим каждый метод:

Метод 1

=COUNTIF(range,"<<")

Символ меньше (‹) - это 89-е значение в Таблице P2E.3, и учитывается все, что было до него. Таким образом, включаются пустые и невидимые строки, но не числа и буквы.

Способ 2

=COUNTIF(range,">>")

Интересно отметить, что, несмотря на то, что после символа больше (›) появляются цифры и буквы, учитывается только текст.

Способ 3

=COUNTIF(range,"<>")

Объединенные символы "меньше" и "больше" представляют собой оператор не равно. Как зарезервированный оператор, обычные правила сортировки Excel не применяются.

Из всех методов это наименее строгий, поскольку он игнорировал только пробелы, но мог подсчитывать пустые строки, невидимые символы, текст и числа.

Метод 4

=COUNTIF(range,"><")

Возможно, вы могли бы назвать это «злыми глазами». Это противоположно методу 3, так как учитывается только текст.

Метод 5

=COUNTIF(range," ")

Этот критерий был немного более предсказуемым, поскольку только тест Невидимый символ подсчитывал значения.

Метод 6

=COUNTIF(range,"")

Строка с нулевым символом учитывает не только значения Пустой строки, но и значения Пустой.

Метод 7

=COUNTIF(range,"*")

Звездочка представляет собой подстановочный знак и сама по себе учитывает пустые и невидимые символы, а также текст. Однако числа исключены.

Метод 8

=COUNTIF(range,"*?")

Это небольшая разновидность метода 7. Знак вопроса (?), Который представляет один символ, означает, что учитываются только строки, состоящие хотя бы из одного символа.

Метод 9

=COUNTIF(range,"<*")

Распознается любой символ перед звездочкой, включая пробелы, поэтому скрываются пустые строки и невидимые символы.

Метод 10

=COUNTIF(range,">*")

Основное отличие от метода 9 заключается в том, что текст учитывается, а пробелы - нет.

Метод 11

=COUNT(range)

Функция COUNT предназначена только для подсчета чисел, и это действительно так, поскольку только столбец Number приводил к подсчету.

Метод 12

=COUNTA(range)

COUNTA считает каждую непустую ячейку. Каждый тест, кроме пустого, давал подсчет.

Метод 13

=COUNTBLANK(range)

Любопытно, что функция СЧИТАТЬ ПУСТОТЫ не только подсчитывает пустые ячейки; он также считает пустые строки.

Метод 14

=SUM(--ISBLANK(range))

В качестве альтернативы методу 13, если вы хотите подсчитывать только пустые ячейки, вложите функцию ISBLANK в SUM. ISBLANK игнорирует пустые строки, в отличие от COUNTBLANK.

Метод 15

=SUMPRODUCT(--(LEN(range)>0))

Для подсчета диапазона текстовых и числовых значений, состоящих хотя бы из одного символа, используйте комбинацию функций СУММПРОИЗВ и ДЛСТР.

LEN проверит, возвращает ли каждая ячейка в диапазоне больше нуля символов. Возвращается массив значений ИСТИНА и ЛОЖЬ, хотя -- преобразует их в единицы и нули. Это позволяет SUMPRODUCT умножать каждый индекс сам по себе - и, поскольку все, что умножается на ноль, дает ноль, будут суммироваться только единицы.

Примеры в Таблице P2E.4 показывают, что, хотя номер заказа (# ›#) является определяющим фактором, есть исключения.

Возьмем, например, «а» и «А». Несмотря на то, что первые имеют более высокий порядок и кодовый номер, они считаются равными. В этом есть смысл, поскольку я уже знал, что в тексте не учитывается регистр. Я все еще не уверен в секретной системе порядка, которая ставит строчные и прописные буквы наравне друг с другом.

Часть 3

Таблица P3.1 и Таблица P3.2 содержат набор значений вместе с двумя проверками данных.

В таблице P3.1 они хранятся в виде чисел, поэтому функция ЕЧИСЛО возвращает значение ИСТИНА для каждого. Напротив, Таблица P3.2 хранит их как текст.

Другой пример показан в следующих двух таблицах со значениями ошибок.

Если вы измените числовой формат ячейки первого столбца в Таблице P3.2 или Таблице P3.4, тип хранения изменится только по имени. Чтобы правильно изменить его, вы должны отредактировать ячейку и нажать Return, чтобы подтвердить значение.

Если у вас много ячеек, делать это для каждой будет очень утомительно. Вот здесь и пригодится утилита Преобразование текста в столбцы. Он находится на вкладке Данные на ленте.

Текст в столбцы обычно используется для разделения данных столбца на несколько на основе разделителя. Однако его также можно использовать для сброса значений диапазона.

Выбрав ячейки и открыв Текст в столбцы, вы можете сразу же нажать Готово. Это приведет к сбросу ячеек в формат Общий.

Если вы хотите выбрать конкретный формат для преобразования значений, шаг 3 позволяет это сделать.

Заключительные слова

Я уже довольно много рассказал здесь, но надеюсь, что это повысило осведомленность о том, как Excel обращается с вашими ценностями.

Хотя я бы хотел, чтобы он был более строгим. Например, почему функция СЧИТАТЬ ПУСТОТЫ не считает только пустые ячейки? Почему неправильно используется слово «пустой»? И почему мне нужно использовать комбинацию SUM и ISBLANK для подсчета действительно пустых ячеек?

Почему, когда я меняю формат ячейки с числового на текстовый, он не обновляется, пока я не подтвердю значение?

Почему Excel добавляет апостроф в текстовые ячейки, а в других случаях - нет?

По-прежнему остается много безответных причин, и я знаю, что есть миллионы пользователей, которые блаженно не осведомлены об этих нюансах. Так было до тех пор, пока я не провел это расследование.

Я не ожидаю, что вы запомните все, что вы здесь узнали, но, если у вас возникнут проблемы в будущем, возможно, стоит еще раз обратиться к моей рабочей тетради.