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

Обновлено: 04.07.2024

И самое удивительное - если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа Редактирование -Очистить содержимое) - то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд - "" ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:

Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет - они просто заполняются строкой нулевой длины.
в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
=ЕСЛИ( A1 =1;10;"")
=IF(A1=1,10,"")
в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо "" ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно -Показывать нули в ячейках, которые содержат нулевые значения

А если такой файл делали не Вы - он достался "по наследству" или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:

Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?

создаем в книге новый стандартный модуль: Alt+F11 -Insert -Module()
копируем в созданный модуль приведенный выше код
выделяем нужный диапазон(если надо заменить на всем листе - то можно выделить все ячейки листа или целиком нужные столбцы - программа сама определить нужные данные)
нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString

Предположим, у вас есть список данных с нулевыми или пустыми ячейками, и вы хотите условно отформатировать этот список данных, но игнорировать пустые или нулевые ячейки, что бы вы сделали? В этой статье мы покажем вам, как использовать условное форматирование с игнорированием пустых или нулевых ячеек в 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 кнопка. Смотрите скриншот:

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

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

Или иначе: как посчитать те "Б", которые попадают под нужные столбцы?

Вопрос задан более года назад
1324 просмотра

Сложный 8 комментариев

Обычно это делается иначе. Ручная отметка ячеек цветом - не самый удобный способ маркировки, в частности, потому что штатных функций для ее обработки как условия нет.
Я бы на вашем месте отмечал ячейки не цветом, а в строке выше выставлял флаги (например, для коричневых ячеек ставил над ними ноль. Саму строку снабдил бы условным форматированием, которое бы красило ячейки коричневым, если над ними ноль - это если цветовая отметка нужна. А "Б" считал бы функцией счётесли, если сверху в столбце ноль. John Smith, возможно, вариант подходящий) Подскажите, как реализовать счетесли так, как вы советуете?
Предположив, что есть флаги над нужными ячейками

EvgenyApMr,
Написать VBA процедуру, которая возвращает цвет ячейки (или флаг - если цвета нет 0, иное 1) и в следующей строке сделать промежуточные показания, где присваивать значение из строки "6" и какое-то отличное значение от Б если в строке "4" цвет коричневый.
Ну и уже счетесли использовать на промежуточной строчке - а так по уму подход менять. Ведь "Б" это что то значит, оно может принимать любое значение - сложно представить зачем кто-то красил верхнюю ячейку в коричневый цвет - усложняя последующий анализ при автоматизации, вместо того что б ввести БК (К-коричневый) и тогда бы формула отработала идеально без всяких ухищрений.

akelsey, Коричневый - это дни недели, сб и вс, под ними есть ещё много таблиц, туда попадает не только "Б", но и другие значения.

По VBA не подскажите, как должна выглядеть процедура?

John Smith, возможно, вариант подходящий) Подскажите, как реализовать счетесли так, как вы советуете?
Предположив, что есть флаги над нужными ячейками

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

в ячейку, где надо считать, ставите

=СЧЁТЕСЛИМН(%диапазон с буквами%;%буква, которую считаете, в кавычках%;%диапазон с флагами%;%флаг счета без кавычек, если цифра%)

Вот пример. Флаг у меня единица. Уловное форматирование делать не стал, но это элементарно - для всех ячеек строки с буквами, где над ней ячейка с флагом, ставить цвет. Статей про условное форматирование в сети навалом. Если непонятно что-то конкретное, спрашивайте.

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

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

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

Создание примера ошибки

Откройте чистый лист или создайте новый.

Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.

После знака равно (=) введите ЕСЛИERROR и открываю скобку.
ЕСЛИERROR(

Переместите курсор в конец формулы.

Введите ,0), то есть запятую и закрываюю скобки.
Формула =B1/C1 становится=ЕСЛИERROR(B1/C1;0).

Применение условного формата

Выделите ячейку с ошибкой и на вкладке Главная нажмите кнопку Условное форматирование.

Выберите команду Создать правило.

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

Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.

На вкладке Число в списке Категория выберите пункт (все форматы).

Скрытие значений ошибок путем изменения цвета текста на белый

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

Выделите диапазон ячеек, содержащих значение ошибки.

На вкладке Главная в группе Стили щелкните стрелку рядом с командой Условное форматирование и выберите пункт Управление правилами.
Появится диалоговое окно Диспетчер правил условного форматирования.

Выберите команду Создать правило.
Откроется диалоговое окно Создание правила форматирования.

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

В разделе Измените описание правила в списке Форматировать только ячейки, для которых выполняется следующее условие выберите пункт Ошибки.

Щелкните стрелку, чтобы открыть список Цвет, а затем в списке Цвета темывыберите белый цвет.

Описание функций

ЕСЛИERROR С помощью этой функции можно определить, содержит ли ячейка ошибку и возвращает ли ошибку формула.

Выберите отчет сводной таблицы.
Появится область "Инструменты для работы со pivottable".

Excel 2016 и Excel 2013: на вкладке Анализ в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.

Excel 2010 и Excel 2007: на вкладке Параметры в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.

Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

Изменение способа отображения ошибок. В поле Формат выберите значение ошибкиПоказывать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.

Изменение способа отображения пустых ячеек Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

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

Ячейка с ошибкой в формуле

В Excel 2016, Excel 2013 и Excel 2010: Выберите Файл >Параметры >Формулы.

In Excel 2007: Click the Microsoft Office button > Excel Options >Formulas.

В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.

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