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

Обновлено: 04.07.2024

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

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

Если у вас есть формула для вычисления диапазона ячеек, и вы хотите игнорировать пустые ячейки. И вы можете использовать функцию ЕСЛИ, функцию ЕСЛИЧИСЛО, функцию И или ИЛИ для достижения результата.

Например, вы хотите добавить 1 ко всем ячейкам в диапазоне D2:D7, и если вы напрямую используете формулу =D2+1, к пустым ячейкам также будет добавлено 1. Поэтому, если вы хотите игнорировать все пустые ячейки в этой формуле, вам нужно использовать функцию ЕСЛИ, чтобы проверить, является ли это пустой ячейкой, если ИСТИНА, то игнорировать ее. В противном случае примените эту формулу. Просто используйте следующую формулу:

Для получения того же результата можно также использовать функцию ЕСЛИ в сочетании с функцией ЕСЛИЧИСЛО. Вот так:

Видео: игнорирование пустых ячеек в формуле

Связанные функции

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

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

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

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

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

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

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

ЕСЛИ ячейка соответствует определенному тексту. Функция ЕСЛИ часто используется на листе Excel для возврата «истинного значения» или «ложного значения» на основе результата логического теста. Если вы хотите проверить значения, чтобы увидеть, равны ли они определенному тексту, например .

Функция СРЗНАЧ — одна из самых популярных функций в Excel. Применяя AVERAGE вместе с некоторыми другими функциями, мы можем вычислить среднее просто для некоторых сложных ситуаций. В этой статье мы познакомим вас с расчетом среднего значения последнего файла .

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

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

Формула 1: игнорировать пустые ячейки в одном столбце

Эта конкретная формула добавляет 10 к значению в ячейке A2, только если значение в ячейке A2 не пустое.

Формула 2: игнорировать пустые ячейки в нескольких столбцах

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

В следующих примерах показано, как использовать каждую формулу на практике.

Пример 1. Игнорирование пустых ячеек в одном столбце

Предположим, у нас есть следующие данные в Excel, которые показывают очки, набранные различными баскетболистами:

Теперь предположим, что мы используем следующую формулу, чтобы добавить 10 к каждому значению в столбце A:

На следующем снимке экрана показано, как использовать эту формулу:

Обратите внимание, что 10 добавляется к каждой ячейке в столбце A, даже если ячейка в столбце A пуста.

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

На следующем снимке экрана показано, как использовать эту формулу:

Обратите внимание, что 10 добавляется только к каждой ячейке в столбце A, которая не пуста.

Пример 2. Игнорирование пустых ячеек в нескольких столбцах

Предположим, у нас есть следующие данные в Excel, которые показывают набранные очки и подборы, сделанные различными баскетболистами:

Мы можем использовать следующую формулу, чтобы сложить значения в столбцах очков и подборов только для тех строк, где оба значения не пусты:

На следующем снимке экрана показано, как использовать эту формулу:

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

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Чтобы запустить формулу только тогда, когда одна или несколько ячеек не пусты, вы можете использовать функцию ЕСЛИ с соответствующими логическими критериями. В показанном примере формула в E5 выглядит следующим образом:

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

Цель этого примера — проверить ввод перед вычислением результата. Важно понимать, что можно заменить любую допустимую формулу. Функция СУММ используется только в качестве примера. Логика также может быть скорректирована разными способами в зависимости от ситуации.

В показанном примере мы используем функцию ЕСЛИ вместе с функцией СЧЁТ. Критерий представляет собой выражение, основанное на функции COUNT, которая подсчитывает только числовые значения:

Если диапазон содержит три числа (т. е. все 3 ячейки не пусты), результатом будет ИСТИНА, и ЕСЛИ запустит функцию СУММ. Если нет, результатом является ЛОЖЬ, и ЕСЛИ возвращает пустую строку (""). Поскольку C7 не имеет значения на экране выше, формула не показывает результата.

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

СО СЧИТАТЬПУСТОТЫ

Функция СЧИТАТЬПУСТОТЫ подсчитывает пустые ячейки в диапазоне, поэтому мы можем написать немного более компактную формулу, подобную этой:

Если СЧИТАТЬПУСТОТЫ возвращает любое число, кроме нуля, функция ЕСЛИ оценивается как ИСТИНА и ничего не возвращает (""). Если СЧИТАТЬПУСТОТЫ возвращает ноль, ЕСЛИ оценивается как ЛОЖЬ и возвращает сумму.

Пустой

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

В этом примере используется буквальный подход с функцией IПУСТО. Поскольку мы хотим проверить все три ячейки одновременно, нам нужно использовать IПУСТО три раза внутри функции ИЛИ. Это логический тест внутри ЕСЛИ:

Когда оператор ИЛИ возвращает значение ИСТИНА (хотя бы одна ячейка пуста), ЕСЛИ возвращает пустую строку (""). Когда ИЛИ возвращает ЛОЖЬ (пустых ячеек нет), ЕСЛИ запускает функцию СУММ и возвращает результат:

С логическими операторами

Функция ЕПУСТО может быть заменена стандартными логическими операторами, например:

Кроме того, мы можем комбинировать оператор не равно (<>) с функцией AND следующим образом:

Обратите внимание, что функция СУММ была перемещена к результату ИСТИНА. Он будет работать, только если C5, C6 и C5 не пусты.

С СЧЕТЧИКОМ

Наконец, вы можете использовать функцию СЧЕТЧИК для проверки числового или текстового ввода:

Если диапазон C5:C5 содержит три значения (числа или текст), результатом будет TRUE и будет запущена функция SUM. На самом деле это не имеет смысла для показанного примера (который требует числового ввода), но его можно использовать в других ситуациях.

Содержание

1. СРЕДНЕЕ игнорировать пробелы

Функция СРЗНАЧ предназначена для автоматического игнорирования пустых ячеек, но в некоторых случаях она не работает. На картинке выше пустые ячейки в B3:B8, однако они считаются нулями. Почему?

1.1 Ячейки, содержащие 0 (ноль), отформатированы как пробел

Ячейки в B3:B8 на самом деле не пусты, выберите B3:B8 и нажмите CTRL + 1, чтобы открыть настройки "Формат ячеек".

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

Это снова покажет ваши нули на вашем листе, если вы не читаете следующий раздел. Если вам интересно узнать больше о пользовательских кодах форматирования, прочитайте эту статью: Объяснение функции ТЕКСТ в Excel

1.2 Проверьте настройки Excel

Если это не помогло, перейдите в «Параметры Excel» и нажмите левой кнопкой мыши на «Дополнительно».

Пользователи Excel 2016 нажимают левой кнопкой мыши на «Файл» на ленте, а затем нажимают левой кнопкой мыши на «Параметры».

Найдите параметр "Показывать ноль в ячейках с нулевым значением", включен ли он?

2. Среднее — игнорировать пробелы и ошибки

Функция СРЗНАЧ автоматически игнорирует пустые ячейки, не беспокойтесь об этом. Однако, если вы работаете с возможными значениями ошибок и используете функцию ЕСЛИОШИБКА для обработки ошибок, происходят странные вещи.

Функция ЕСЛИОШИБКА удаляет ошибки, но имеет побочный эффект: она преобразует пустые ячейки в нули, что, в свою очередь, приводит к тому, что функция СРЗНАЧ возвращает неверный результат.

Попробуйте эту формулу: =СРЗНАЧ(ЕСЛИОШИБКА(B3:B8, "")) возвращает 2, если вы используете приведенные выше примеры значений, а это неверно. Вместо этого используйте следующую формулу.

Формула в ячейке D3:

Объяснение формулы

Шаг 1. Определите непустые ячейки

Сочетание знаков "меньше" и "больше" оценивается как "не равно", в данном случае не равно ничему (пробел). Результатом является логическое значение TRUE или FALSE.

Шаг 2. Замените пустые значения значением с ошибкой

Функция ЕСЛИ возвращает одно значение, если логическая проверка ИСТИНА, и другое значение, если логическая проверка ложна.

ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

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

Шаг 3. Обработка ошибочных значений

Функция ЕСЛИОШИБКА заменяет значения ошибок указанным значением.

В этом случае он заменяет значения ошибок ничем (пустым).

Шаг 4. Рассчитайте среднее значение

Функция СРЗНАЧ вычисляет среднее значение группы чисел, текст и пустые ячейки игнорируются.

и возвращает 3 в ячейке D3. 5 + 1 = 6. 6 / 2 = 3.

3. В среднем – игнорировать пробелы в несмежных ячейках

Следующая формула содержит два несмежных диапазона ячеек B3:B8 и D3:D4, функция СРЗНАЧ автоматически игнорирует пустые ячейки. Если это не так, убедитесь, что Excel не использует другой формат ячеек, см. разделы 1.1 и 1.2 выше.

Формула в ячейке B11:

5+3+1+5 = 14. 14/4 = 3,5

4. Средневзвешенное значение игнорирует пробелы

Формула в ячейке C14:

=СУММПРОИЗВ(ЕСЛИ(B3:B12<>"", ПРОСМОТР($B$3:$B$12, $E$3:$E$8, F3:F8), 0)*C3:C12)/СУММ(ЕСЛИ (B3:B12<>"", ПРОСМОТР($B$3:$B$12, $E$3:$E$8, F3:F8), 0))

4. Объяснение формулы в ячейке C14

Шаг 1. Найдите вес по ценности

Функция ПРОСМОТР позволяет найти значение в диапазоне ячеек и вернуть соответствующее значение в той же строке.

ПРОСМОТР(искомое_значение, вектор_искателя, [вектор_результата])

ПРОСМОТР($B$3:$B$12, $E$3:$E$8, F3:F8)

Шаг 2. Замените пустые значения на 0 (ноль)

ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

В предыдущем посте я рассмотрел, как игнорировать пустые ячейки при использовании функции ЗАПРОС в Google Sheets, у которой есть свой особый способ удаления пустых ячеек из захвата данных.

Но как игнорировать пустые ячейки в электронной таблице, если вы не используете функцию ЗАПРОС? Что делать, если вы находитесь в одной из множества различных функций ЕСЛИ, таких как ЕСЛИ, ЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧСЛИ, СРЗНАЧСЛИ, МАКСЕСЛИ, МИНИСЛИМН (и т. д.). Как написать условие, которое проверяет, пуста ли ячейка в этих функциях?

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

Чтобы игнорировать пустые ячейки с функциями, работающими с диапазоном_критериев и соответствующим критерием, нужно написать условие "<>" в критерии.

Например, в функции СРЗНАЧЕСЛИ, которая использует диапазон_критериев в качестве первого параметра и соответствующий критерий в качестве второго параметра, чтобы исключить вычисление среднего значения диапазона, если ячейка диапазона критериев пуста, используйте условие "<> " в критерии следующим образом:

A	B	C
1	6	Подтверждено
2	9
3	10	В ожидании
4	5	Подтверждено	7 =AVERAGEIF(B1:B4,"<>",A1:A4)

Получить среднее значения в диапазоне A1:A4, если диапазон B1:B4 не имеет пустого значения. Приведенная выше формула игнорирует использование A2 в своих вычислениях и возвращает 7 .

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

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

A	B	C
1	6	Подтверждено
2	9
3	10	В ожидании
4	5	Подтверждено	9 =AVERAGEIF(B1:B4,"",A1:A4)

Получить среднее значение в диапазон A1:A4, если диапазон B1:B4 содержит пустые значения.

Использование значения "<>" для игнорирования пустых ячеек в диапазоне удобно, но что, если мы хотим использовать ту же формулу для массива ячеек, где мы проверяем условие в одной ячейке, а не в диапазоне ячеек? .

Игнорирование пустых ячеек — формула ЕСЛИ

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

Функция ЕСЛИ Первый параметр	Пример использования
A1<>""	=IF(A1<>"", TRUE, FALSE)
LEN(A1)	=ЕСЛИ(ДЛСТР(A1), ИСТИНА, ЛОЖЬ)
ПУСТО(A1)	=ЕСЛИ(ПУСТО(A1), ИСТИНА, ЛОЖЬ)< /td>
НЕ(ПУСТО(A1))	=ЕСЛИ(НЕ(ПУСТО(A1)), ИСТИНА, ЛОЖЬ)

Как проверить, является ли ячейка пустой при использовании стандартной функции ЕСЛИ

Первый и последний примеры в приведенной выше таблице делают одно и то же: они проверяют, НЕ является ли ячейка A1 пустой/пустой.

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

Третий пример — это простая функция, которая определяет, пуста ли ячейка.

Внимание! Не делайте этого

Не используйте условие =ЕСЛИ(A1, ИСТИНА, ЛОЖЬ) для проверки того, пуста ли ячейка, так как в ячейке, на которую делается ссылка, могут содержаться значения, подобные ложным, которые вернут ЛОЖЬ .

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

A	B
1	ЛОЖЬ =ЕСЛИ(A1, ИСТИНА, ЛОЖЬ)
2	ЛОЖЬ	ЛОЖЬ = ЕСЛИ(A2, ИСТИНА, ЛОЖЬ)
3	0	ЛОЖЬ =ЕСЛИ(A3, ИСТИНА, FALSE)

Простая проверка, содержит ли ячейка что-то, может не работать должным образом, обе строки 2 и 3 содержат что-то, но эти значения ложны-y.

Как видно из приведенной выше таблицы, использование простой формулы =ЕСЛИ(ячейка, ИСТИНА, ЛОЖЬ) дает некоторые неверные и проблематичные результаты, если предположение, стоящее за формулой, состоит в том, чтобы просто проверить, существует ли что-то в ячейке.< /p>

Поэтому, поскольку эта формула может ввести в заблуждение, не используйте ее при проверке наличия значения в ячейке. Используйте альтернативные формулы, такие как ДЛСТР(ячейка) или ЕПУСТО(ячейка) .

A	B
1	ИСТИНА =ЕСЛИ(ЕСПУСТО(A1), ИСТИНА, ЛОЖЬ)
2	ЛОЖЬ	ИСТИНА =ЕСЛИ(ДЛСТР(A2), ИСТИНА, ЛОЖЬ)
3	0	ИСТИНА = IF(LEN(A3), TRUE, FALSE)

Лучше использовать формулы, которые лучше описывают, что вы делаете при проверке пустых ячеек.

Обзор

При проверке наличия пустых ячеек в формулах ЕСЛИ используйте любое из следующих условий в параметре критерия:

Если ваш критерий основан на диапазоне и вы используете одну из нескольких формул, содержащих ЕСЛИ , например СУММЕСЛИ , СУММЕСЛИМН , СРЗНАЧСЛИ , СРЗНАЧЕСЛИМН , СЧЁТЕСЛИ , СЧЁТЕСЛИМН , МАКСИМСЛИМН , МИНИМФСЛИМН (и т. д.), то следует игнорировать пустые ячейки. используйте критерий "<>" .

Метки AVERAGEIFSCOUNTIFSGoogle SheetsIFIFSMAXIFSMINIFSSUMIFS сообщают об этом объявлении

сообщить об этом объявлении

< бр />

О нас

сообщить об этом объявлении

Последние сообщения

Категории

сообщить об этом объявлении

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