Не учитывать пустые ячейки в excel в формуле

Обновлено: 06.07.2024

Под пустой ячейкой понимается ячейка, которая не содержит значения или формулы. Определить пустую ячейку можно с помощью функции ЕПУСТО() .

Если необходимо подсчитать пустые ячейки в диапазоне A1:D4 , то можно использовать формулу =СЧИТАТЬПУСТОТЫ(A1:D4) или =СУММПРОИЗВ(--ЕПУСТО(A1:D4)) .

Но не все так просто.

Если ячейка содержит формулу, результатом которой является значение "" (Пустой текст ), то начинаются сложности, т.к. одни функции считают, что это пустая ячейка, а другие с ними не соглашаются и считают, что Пустой текст – это текстовая строка. Еще более все усложняет то, что ячейка с Пустым текстом выглядит как пустая (если Пустой текст результат вычисления формулы или запись ="").

Что это еще за Пустой текст и откуда он берется? Значение Пустой текст (две кавычки (""), между которыми ничего нет) может быть результатом, например, вычисления формулы с условием: =ЕСЛИ(F1>0;"больше 0";"") . Т.е. разработчик намеренно использует значение Пустой текст . Это удобно, т.к. результат Пустой текст обладает замечательным свойством: ячейка выглядит пустой. Этого результата можно, конечно, добиться с помощью Условного форматирования или Пользовательского формата , но гораздо быстрее просто ввести "". Но, этот подход имеет и свою цену: некоторые функции и средства EXCEL интерпретирует ячейку, содержащую Пустой текст , как пустую ячейку, а другие, как содержащую текстовое значение.

Эксперимент

Для иллюстрации приведем пример того как рассматривают ячейку с Пустым текстом Условное форматирование и функция ЕПУСТО() (см. Файл примера ).

Рассмотрим диапазон A1:D4 , содержащий числа, текст, пустые ячейки и Пустой текст "" (наиболее общий случай).

Ячейка С4 содержит значение Пустой текст (введено как результат вычисления формулы =ЕСЛИ(1>2;1;"") ) и выделена жирной границей. Условное форматирование с правилом « Форматировать только те ячейки, которые пустые » выделит действительно пустые ячейки и ячейку со значением Пустой текст !

Функция ЕПУСТО() не разделяет такого подхода и говорит, что в С4 , что-то есть (формула =ЕПУСТО(C4) введенная в ячейку С5 возвращает ЛОЖЬ).

Функция СЧИТАТЬПУСТОТЫ() также как и Условное форматирование , учитывает при подсчете ячейки со значением Пустой текст вместе с пустыми ячейками.

Чтобы не запутаться с подсчетом пустых ячеек в диапазоне С2:С11 при наличии значений Пустой текст, приведем обобщающую таблицу:

Формула или средство EXCEL

Различает ли пустую ячейку и ячейку со значением Пустой текст ?

Комментарий

пустая ячейка и ячейка со значением Пустой текст считаются тождественными

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

Подсчет без учета нулей и пустых ячеек с формулой

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

Выберите пустую ячейку, в которую вы хотите поместить результат подсчета, и введите эту формулу =COUNT(IF(A1:E5<>0, A1:E5)) в это нажмите Shift + Ctrl + Enter ключ для получения результата.

Совет: В формуле A1: E5 - это диапазон ячеек, который вы хотите подсчитать, игнорируя пустые ячейки и нулевые значения.

Подсчет игнорируя нули и пустые ячейки с помощью Kutools for Excel

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите ячейки, которые нужно подсчитать, и нажмите Kutools > Выберите > Выбрать определенные ячейки. Смотрите скриншот:

2. в Выбрать определенные ячейки диалог, проверьте Ячейка под Тип выбора раздел и выберите Не равно критерий из первого раскрывающегося списка под Конкретный тип раздел, затем введите 0 в следующее текстовое поле. Смотрите скриншот:

3. Нажмите Ok or Применить, теперь появляется диалоговое окно, напоминающее вам, сколько ячеек выбрано, и тем временем были выбраны все ячейки, за исключением нулевых значений и пустых ячеек.

Подсчет без учета пустых ячеек только со строкой состояния

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

Выберите ячейки, в которых вы хотите подсчитать только ячейки, за исключением пустых ячеек, а затем перейдите в правый нижний угол строки состояния, чтобы просмотреть результат подсчета. Смотрите скриншот:

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

Игнорировать пустые ячейки при условном форматировании в Excel

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

1. Продолжайте оставаться в Диспетчер правил условного форматирования диалоговое окно, затем щелкните Новое правило кнопка. Смотрите скриншот:

Внимание: Вы можете открыть Диспетчер правил условного форматирования диалоговое окно, нажав Условное форматирование > Управление правилами под Главная меню.

2. Затем он попадает в Новое правило форматирования диалоговое окно. Чтобы игнорировать пустые ячейки при условном форматировании, вы можете использовать два следующих метода.

Метод 1

а. Выбрать Форматировать только ячейки, содержащие в Выберите тип правила коробка;
б. Выбрать Пробелы в Форматировать только ячейки с раскрывающийся список;
c. Не выбирайте какой-либо формат и нажмите OK кнопка. Смотрите скриншот:

Метод 2

а. в Выберите тип правила выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать.;
б. Скопируйте и вставьте формулу = ISBLANK (A2) = ИСТИНА в Формат значений, где эта формула истинна коробка;
Внимание: здесь A2 в формуле - это первая ячейка выбранного диапазона. Например, ваш выбранный диапазон - B3: E12, вам нужно изменить A2 на B3 в формуле.
c. Щелкните значок OK кнопку без указания формата.

3. Затем он возвращается в Диспетчер правил условного форматирования диалоговое окно. Независимо от того, какой метод вы используете для игнорирования пробелов, вам необходимо проверить Остановить, если истина в этом диалоговом окне, а затем щелкните значок OK кнопка. Смотрите скриншот:

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

Игнорировать нулевые ячейки при условном форматировании в Excel

Если у вас есть список данных в диапазоне B2: B12, и вы хотите отформатировать пять наименьших значений среди них, но игнорировать нулевые ячейки, сделайте следующее.

1. Выберите диапазон B2: B12, затем щелкните Условное форматирование > Новое правило под Главная меню.

2. в Изменить правило форматирования диалоговое окно, вам необходимо:

1). в Выберите тип правила выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать.;
2). Скопируйте и вставьте формулу =AND(B2<>0,B2<=SMALL(IF(B$2:B$12<>0,$B$2:$B$12),5)) в Формат значений, где эта формула истинна коробка;
3). Щелкните значок Формат кнопка для указания формата ячеек;
4). После указания формата щелкните значок OK кнопка. Смотрите скриншот:

Внимание: Вам необходимо изменить диапазон ячеек в формуле в соответствии с вашими потребностями.

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

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

Пусть имеется список с пустыми ячейками (столбец А ).

Задача

Убрать пустые ячейки из списка, сформировав формулами список в соседнем столбце. То есть под словом "убрать" будем понимать не удаление значения из исходного списка, а формирование еще одного списка, но уже без лишних символов. Чтобы действительно убрать значения из списка нужно использовать макросы - программу на VBA.

Решение

Для избавления от пустых ячеек запишем в ячейке B2 формулу массива и скопируем ее вниз (см. файл примера): =ЕСЛИОШИБКА(ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$14);"";СТРОКА($A$2:$A$14));СТРОКА(A1)));"")

Получим в соседнем столбце B список со значениями из исходого, но уже без пропусков. Формула работает одинакового и для текстовых значений и для чисел. Алгоритм работы формулы следующий:

ЕСЛИ(ЕПУСТО($A$2:$A$14);"";СТРОКА($A$2:$A$14)) – если ячейка не пуста, то эта часть формулы возвращает номер строки. То есть формируется массив номеров строк, НЕ содержащих пустоты На месте пустых ячеек в массиве будет символ "" (пустой текст), но можно его заменить в формуле на любую текстовую строку, например "ккк". Проверить результат можно выделив эту часть формулы и нажав клавишу F9 ;
Функция НАИМЕНЬШИЙ() сортирует массив строк по возрастанию. В сортированном списке сначала будут идти номера строк затем значения "", т.к. в EXCEL считается, что любое текстовое значение больше любого числа (значение пустой текст - текстовое значение);
Далее для функции ДВССЫЛ() формируются адреса ячеек с непустыми значениями. Например, ДВССЫЛ("A"&2) возвращает значение из ячейки А2 . Для пустых ячеек будет формироваться ошибочные адреса ячеек, состоящие только из символа А. Это вызовет ошибку после применения функции ДВССЫЛ();
Функция ЕСЛИОШИБКА() вместо ошибки будет возвращать "". Этот символ не отображается в ячейке и ячейка выглядит пустой.

На самом деле в соседнем столбце B список будет отличаться не только тем, что в нем значения будут идти подряд без пропусков. Если в исходном списке пустые ячейки действительно не содержали ничего, то в новом списке в пустых ячейках будут значения Пустой текст "". Хотя внешний вид пустых ячеек из обоих списков будет неотличим, но формулы увидят разницу. То есть, если Вы планируете делать дальнейшие манипуляции с новым списком, то имейте ввиду, что теперь пустые ячейки в конце списка теперь не совсем пусты - они содержат текстовое значение "". Подробнее про это специфическое значение читайте здесь .

Изменим немного формулу: =ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ( ЕПУСТО($A$2:$A$14);"";$A$2:$A$14);СТРОКА(A1));"")

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

Список также можно сформировать в столбце С другой формулой массива :

СписокСпропусками в формуле - это динамический диапазон , который образован формулой:

Длину списка с пропусками можно вычислить с помощью формулы:

СОВЕТ:

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

Удаляем заданные символы из списка

Часто в списке присутствуют ненужные для дальнейшего анализа значения, например 0 (нуль).

Немного модернизировав вышеуказанную формулу получим универсальный подход для удаления вообще любого символа, числа или текстовой строки из исходного списка (см. файл примера, в котором на листе Произвольный символ приведена универсальнаяформула).

Пусть исходный список находится в диапазоне A12:A24, а в ячейке B6 содержится значение которое нужно удалить из ячеек списка.

Единственным отличием является выражение $A$12:$A$24=$B$6, которое заменило формулу с ЕПУСТО(. )

Теперь если значение в исходном списке не равно искомому значению, то вместо него будет выведено значение "" (в конце списка).

Читайте также: