Как проверить пустая ли ячейка в excel в гугл таблице
Когда вы анализируете данные в электронной таблице, подсчет пустых или пустых ячеек может помочь вам сосредоточиться на определенных областях. Вот почему такие функции, как COUNTBLANK, COUNTIF, COUNTIFS и SUMPRODUCT, так важны в Google Таблицах.
Однако одно предупреждение. Если у вас есть ячейка, содержащая пустую текстовую строку («») или формулу, возвращающую аналогичный результат, эта ячейка будет пустой, но технически не будет пустой. Если вы хотите узнать количество действительно пустых ячеек, вам необходимо использовать комбинацию функций СУММ, СТРОКИ, СТОЛБЦЫ и СЧЁТЕСЛИ.
Использование COUNTBLANK
Вы можете попробовать функцию СЧИТАТЬПУСТОТЫ, чтобы подсчитать количество пустых ячеек в электронной таблице Google Таблиц. Это самый быстрый способ найти количество пустых, но не пустых ячеек.
Ячейки, содержащие числа или текст, не учитываются, включая ячейки с нулевым номером. Однако, как мы упоминали, если ячейка выглядит пустой, но содержит пустую текстовую строку («»), это будет засчитано.
Чтобы использовать его, откройте свой Google Таблицы электронная таблица. Щелкните пустую ячейку и введите = СЧИТАТЬПУСТОТЫ (диапазон). Замените диапазон диапазоном ячеек.
Например, если вы хотите подсчитать количество пустых ячеек между столбцами A и C, введите = COUNTBLANK (A: C).
В приведенном выше примере ячейки от A3 до H24 используются в пределах диапазона. Этот диапазон содержит четыре пустые ячейки (B4, C4, D4 и E4), что соответствует той же цифре, которая отображается в ячейке A1.
Использование COUNTIF и COUNTIFS
Хотя COUNTBLANK возвращает количество пустых ячеек, вы также можете использовать COUNTIF или COUNTIFS для достижения того же результата.
СЧЁТЕСЛИ подсчитывает количество ячеек, соответствующих критериям, которые вы определяете в самой формуле. Поскольку вы хотите подсчитывать пустые ячейки, вы можете использовать пустую текстовую строку в качестве критерия.
Чтобы использовать СЧЁТЕСЛИ, откройте электронную таблицу Google Таблиц и щелкните пустую ячейку. Введите = СЧЁТЕСЛИ (диапазон; ”»), заменив диапазон выбранным диапазоном ячеек.
В приведенном выше примере есть три пустые ячейки (B4, C4 и D4) в диапазоне от A3 до H24, при этом функция СЧЁТЕСЛИ в ячейке A1 возвращает такое же количество пустых ячеек.
Функцию СЧЁТЕСЛИМН можно использовать как альтернативу СЧЁТЕСЛИ. Используйте = СЧЁТЕСЛИМН (диапазон, ””), заменяя диапазон выбранным диапазоном ячеек.
В приведенном выше примере были обнаружены четыре пустые ячейки в диапазоне ячеек от A3 до H24.
Использование SUMPRODUCT
Функция СУММПРОИЗВ предлагает немного более сложный способ подсчета количества пустых ячеек. Он подсчитывает количество ячеек, соответствующих определенным критериям, которые в данном случае будут пустой текстовой строкой («»).
Чтобы использовать SUMPRODUCT, откройте электронную таблицу Google Sheets и щелкните пустую ячейку. Введите = СУММПРОИЗВ (- (диапазон = ””)), заменив диапазон выбранным диапазоном ячеек.
В приведенном выше примере показано, что в диапазоне ячеек от A2 до H24 были обнаружены две пустые ячейки (B4 и C4).
Подсчет пустых ячеек
Все перечисленные выше функции подсчитывают пустые ячейки, которые технически не пусты. Если функция возвращает нулевой или пустой результат, или если у вас есть пустая текстовая строка («») в ячейке, то эти ячейки считаются пустыми.
Обходной путь к этой проблеме — использовать СЧЁТЕСЛИ для подсчета количества ячеек с числовым значением, а затем использовать вторую формулу СЧЁТЕСЛИ для подсчета количества ячеек, содержащих текст или пустые текстовые строки.
Затем вы можете сложить результаты этих вычислений и вычесть их из числа ячеек в вашем диапазоне данных. Сначала вам нужно узнать количество ячеек в вашем диапазоне. Чтобы узнать это, вы можете использовать функции ROWS и COLUMNS.
Для начала откройте электронную таблицу Google Sheets, щелкните пустую ячейку и введите = ROWS (диапазон) * COLUMNS (диапазон), заменив значение диапазона диапазоном ячеек.
Во второй пустой ячейке введите = СЧЁТЕСЛИ (диапазон, ”> = 0 ″), чтобы подсчитать количество ячеек с числовым значением. Еще раз замените диапазон на соответствующий диапазон ячеек для ваших данных.
Для поиска пустых ячеек или ячеек, содержащих текст, введите = СЧЁТЕСЛИ (диапазон, «*») в третьей пустой ячейке. При необходимости замените диапазон.
Затем вы можете использовать СУММ для сложения двух значений СЧЁТЕСЛИ, вычитая это число из числа ячеек в вашем диапазоне, рассчитанного с помощью функций СТРОКИ и СТОЛБЦЫ.
В нашем примере общее количество ячеек можно найти в ячейке B8, количество ячеек с числовым значением в B9 и количество ячеек, содержащих текст или пустую текстовую строку, в B10.
Как показано в приведенном выше примере, в диапазоне из 20 ячеек (от A2 до E5) было обнаружено, что 19 ячеек содержат число, текст или пустую текстовую строку. Только одна ячейка, Е4, была полностью пустой.
Функция ЕПУСТО в Excel используется для наличия текстовых, числовых, логических и прочих типов данных в указанной ячейке и возвращает логическое значение ИСТИНА, если ячейка является пустой. Если в указанной ячейке содержатся какие-либо данные, результатом выполнения функции ЕПУСТО будет логическое значение ЛОЖЬ.
Примеры использования функции ЕПУСТО в Excel
Пример 1. В таблице Excel находятся результаты (баллы) по экзамену, который проводился в учебном заведении. В данной электронной ведомости напротив некоторых студентов оценки не указаны, поскольку их отправили на пересдачу. В столбце рядом вывести текстовую строку «Сдал» напротив тех, кому выставили оценки, и «На пересдачу» - напротив не сдавших с первого раза.
Выделим ячейки C3:C18 и запишем следующую формулу:
Формула ЕСЛИ выполняет проверку возвращаемого результата функции ЕПУСТО для диапазона ячеек B3:B18 и возвращает один из вариантов ("На пересдачу" или "Сдал"). Результат выполнения функции:
Теперь часть данной формулы можно использовать для условного форматирования:
- Выделите диапазон ячеек C3:C18 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
- В появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулы для определения форматируемых ячеек» и введите следующую формулу:
- Нажмите на кнопку «Формат» (как на образцу), потом укажите в окне «Формат ячеек» красный цвет заливки и нажмите ОК на всех открытых окнах:
Почему нужно использовать функцию ЕПУСТО при проверке пустых ячеек
У выше указанном примере можно изменить формулу используя двойные кавычки ("") в место функции проверки ячеек на пустоту, и она также будет работать:
=ЕСЛИ(ИЛИ( B3="" ;B3=2);"На пересдачу";"Сдал")
Но не всегда! Все зависит от значений, которые могут содержать ячейки. Обратите внимание на то как по-разному себя ведут двойные кавычки, и функция ЕПУСТО если у нас в ячейках находятся одни и те же специфические значения:
Как видно на рисунке в ячейке находится символ одинарной кавычки. Первая формула (с двойными кавычками вместо функции) ее не видит. Более того в самой ячейке A1 одинарная кавычке не отображается так как данный спецсимвол в Excel предназначенный для отображения значений в текстовом формате. Это удобно, например, когда нам нужно отобразить саму формулу, а не результат ее вычисления как сделано в ячейках D1 и D2. Достаточно лишь перед формулой ввести одинарную кавычку и теперь отображается сама формула, а не возвращаемый ее результат. Но функция ЕПУСТО видит, что в действительности ячейка А1 не является пустой!
Проверка на пустую ячейку в таблице Excel
Пример 2. В таблице Excel записаны некоторые данные. Определить, все ли поля заполнены, или есть как минимум одно поле, которое является пустым.
Исходная таблица данных:
Чтобы определить наличие пустых ячеек используем следующую формулу массива (CTRL+SHIFT+Enter):
Функция СУММ используется для определения суммы величин, возвращаемых функцией --ЕПУСТО для каждой ячейки из диапазона B3:B17 (числовых значений, поскольку используется двойное отрицание). Если запись СУММ(--ЕПУСТО(B3:B17) возвращает любое значение >0, функция ЕСЛИ вернет значение ИСТИНА.
То есть, в диапазоне B3:B17 есть одна или несколько пустых ячеек.
Примечание: в указанной выше формуле были использованы символы «--». Данный вид записи называется двойным отрицанием. В данном случае двойное отрицание необходимо для явного преобразования данных логического типа к числовому. Некоторые функции Excel не выполняют автоматического преобразования данных, поэтому механизм преобразования типов приходится запускать вручную. Наиболее распространенными вариантами преобразования текстовых или логических значений к числовому типу является умножение на 1 или добавление 0 (например, =ИСТИНА+0 вернет число 1, или =«23»*1 вернет число 23. Однако использование записи типа =--ИСТИНА ускоряет работу функций (по некоторым оценкам прирост производительности составляет до 15%, что имеет значение при обработке больших объемов данных).
Как посчитать количество пустых ячеек в Excel
Формула для расчета (формула массива):
Особенности использования функции ЕПУСТО в Excel
Функция ЕПУСТО в Excel относится к числу логических функций (выполняющих проверку какого-либо условия, например, ЕСЛИ, ЕССЫЛКА, ЕЧИСЛО и др., и возвращающих результаты в виде данных логического типа: ИСТИНА, ЛОЖЬ). Синтаксическая запись функции:
Единственный аргумент является обязательным для заполнения и может принимать ссылку на ячейку или на диапазон ячеек, в которых необходимо определить наличие каких-либо данных. Если функция принимает диапазон ячеек, функция должна быть использована в качестве формулы массива.
Если вам нужно проверить, пуста ли одна ячейка в Google Sheets или нет, вы можете сделать это вручную. На самом деле, это, наверное, самый быстрый способ. Однако, если вы имеете дело с несколькими ячейками, это вскоре станет утомительной и повторяющейся задачей. Но не волнуйтесь. Есть способ позволить Google Sheets понять это за вас.
Параметр, который проверяет, пуста ли ячейка, называется IПУСТО, и мы покажем вам, как его использовать.
Что такое ИСБЛАНК?
Если вы много использовали Excel, вы, вероятно, знакомы с этой функцией. Есть небольшие отличия, но он используется для одного и того же.
IПУСТО — это функция, разработанная для определения того, занимает ли значение ячейку. Мы используем термин «ценность», чтобы избежать недопонимания. Значение может быть чем угодно: числами, текстом, формулами или даже ошибкой формулы. Если что-либо из вышеперечисленного занимает ячейку, IПУСТО покажет вам знак ЛОЖЬ.
Не позволяйте этим терминам сбить вас с толку. Это как если бы вы спрашивали Google Sheets: «Эта ячейка пуста, пуста?» Если ответ отрицательный, будет указано False. С другой стороны, если ячейка пуста, это подтверждается показом знака ИСТИНА.
Как проверить, пуста ли ячейка в Google Sheets, используя ISBLANK
Давайте перейдем к практической части и посмотрим, что вы можете сделать с этой функцией. Вкратце, как написать свои функции в Google Sheets. Не беспокойтесь, для этого вам не обязательно быть ИТ-специалистом. Мы проведем вас через процесс:
Если A2 пуст, вы увидите знак TRUE. Если он не пуст, вы увидите знак FALSE. Это так просто!
Если вы хотите проверить, действительно ли работает эта функция, вы можете либо написать что-то в А2, либо удалить ее содержимое. После этого попробуйте сделать это еще раз и посмотрите, изменился ли вывод. Тем не менее, нет никакой реальной необходимости сомневаться в этой функции. Если вы сделаете все правильно, это будет 100% точно.
Как проверить, пусты ли несколько ячеек в Google Sheets, используя ISBLANK
Лучшее в этой функции то, что вы можете использовать ее, чтобы проверить, пусты ли несколько ячеек или нет. Количество ячеек, которые вы можете проверять одновременно, не ограничено. Только представьте, сколько времени сэкономит вам этот вариант!
Имейте в виду, что эта опция даст вам результат для всего диапазона ячеек. Хотя все ячейки, кроме одной, пусты, это еще не означает, что весь диапазон пуст. Следовательно, результат будет FALSE, даже если занята только одна ячейка. Для большей точности вам придется проверять ячейки одну за другой.
Как проверить, пуста ли ячейка в Google Sheets на Android и iOS
Лучшее в Google Sheets то, что вы можете делать почти все на своем телефоне. Однако вы, вероятно, не сможете использовать ISBLANK в своем мобильном веб-браузере. Поэтому вам необходимо загрузить приложение Google Sheets, которое доступно как для iOS а также Андроид устройства. Процесс аналогичен тому, который мы уже описали.
Единственным недостатком использования этой опции на вашем телефоне является то, что вы не сможете четко видеть все. Если вы имеете дело с важными данными, мы рекомендуем вам всегда выбирать настольную версию, поскольку она дает вам больше ясности.
Дополнительные параметры Google Таблиц
Чтобы получить максимальную отдачу от этой опции, вы можете использовать ее в сочетании с функциями ЕСЛИ. Вы можете заставить Google Sheets выполнять определенную задачу, только если ячейка пуста. Наиболее распространенная ситуация, когда вы хотите заполнить пустые ячейки текстом. Допустим, вы хотите написать «Отсутствующая информация» во всех пустых ячейках.
Вы собираетесь программировать Google Таблицы следующим образом: если функция ЕПУСТО возвращает ИСТИНА, выведите текст «Отсутствующая информация». Эта опция сэкономит вам массу времени, так как это практически невозможно сделать вручную, если у вас много ячеек.
Часто задаваемые вопросы
Почему я получил знак False, хотя ячейка кажется пустой?
Это одна из наиболее распространенных проблем с функцией IПУСТО. Тем не менее, главный вопрос, который вы должны задать себе: действительно ли ячейка пуста или она просто кажется пустой? Давайте объясним.
Ячейка может быть занята простым пробелом, который вы случайно ввели. Очевидно, вы не можете его увидеть, потому что там нечего видеть, но он все еще там. Другая возможность может заключаться в том, что скрытые символы или скрытые формулы занимают ячейку.
Если это вас беспокоит, самое быстрое решение — щелкнуть эту ячейку и очистить ее содержимое. После этого мы уверены, что вы получите правильный результат.
Экспериментируйте с Google Таблицами
Мы показали вам некоторые основные функции, которые подходят для начинающих. Однако Google Sheets позволяет вам делать гораздо больше, и мы надеемся, что вы не остановитесь на достигнутом. Поэкспериментируйте с другими функциями, которые могут облегчить вашу работу. Если вы знакомы с функциями Excel, вам будет несложно.
Если вы не очень хорошо разбираетесь в Excel, вы можете найти Google Sheets более доступным и удобным для пользователя. Используете ли вы какую-либо другую программу для работы с электронными таблицами? Дайте нам знать в комментариях ниже.
В Google Таблицах есть отличные формулы, которые позволяют быстро получить информацию о наборе данных. Одной из обычных вещей, которые часто приходится делать многим людям, является подсчет непустых ячеек в наборе данных в Google Таблицах (т. е. Подсчет непустых ячеек).
Хотя это можно сделать вручную, если у вас небольшой набор данных, для больших лучше оставить это замечательным формулам COUNT в Google Таблицах.
И снова, есть несколько способов снять шкуру с этой кошки.
В этом уроке я покажу вам две действительно простые формулы для подсчета ячеек, если они не пустые в Google Таблицах.
Подсчитайте ячейки, если они не пустые, с помощью функции COUNTA
В Google Sheets есть функция COUNTA, которая подсчитывает все непустые ячейки в наборе данных (т. Е. Все ячейки, содержащие любую текстовую строку или число).
Предположим, у вас есть набор данных, показанный ниже, и вы хотите посчитать в нем непустые ячейки.
Вы можете использовать приведенную ниже формулу COUNTA для подсчета всех непустых ячеек в наборе данных:
Хотя формула COUNTA дает правильный результат, в этом случае она может дать вам неправильный результат, если есть:
- Космический персонаж
- Нулевая / пустая строка (= ””).
- Апостроф (‘)
Пустая строка может быть результатом формулы, и многие люди используют апостроф при вводе чисел (чтобы показать эти числа как текст)
Во всех вышеперечисленных случаях может показаться, что ячейки пусты, но функция COUNTA все равно будет рассматривать их как непустые и подсчитывать их в результате.
Ниже приведен пример, в котором у меня есть пустая строка (= ””) в ячейке A4 и апостроф в ячейке A8. Вы можете видеть, что результат формулы COUNTA — 11 (хотя только девять ячеек заполнены именами — или, по крайней мере, так кажется невооруженным глазом).
Если вы уверены, что в ваших данных нет пустых строк или апострофов, вы можете использовать приведенную выше формулу COUNTA, но в случае, если это возможно, для этого лучше использовать комбинацию формул (описано в следующий раздел).
Подсчет непустых ячеек с помощью функции СУММПРОИЗВ
Замечательно, насколько легко вы можете решать сложные задачи с помощью простого сочетания формул в Google Таблицах.
Когда дело доходит до подсчета непустых ячеек в наборе данных, могут быть ячейки с пустой строкой, пробелами или апострофом.
Хотя вы не можете полностью полагаться на формулу COUNTA, вот простая формула SUMPRODUCT, которая даст вам правильный результат во всех сценариях:
= СУММПРОИЗВ (LEN (TRIM (A2: A13))> 0)
Приведенная выше формула проверяет, есть ли в ячейке хотя бы один символ / число. Это проверяется функцией LEN.
Если длина символов в ячейке больше 0, она засчитывается, иначе не засчитывается.
Это касается двух сценариев — пустых строк и апострофа.
И часть функции TRIM гарантирует, что если в ячейках есть пробелы, они также будут проигнорированы.
Если у вас есть значения ошибок в ячейках, то эта формула выдаст вам ошибку.
[Быстрый СОВЕТ] Получите значение счетчика на панели задач
Если вы хотите быстро получить количество непустых ячеек, вы можете получить это на панели задач.
Все, что вам нужно сделать, это выбрать ячейки, в которых вы хотите получить количество непустых ячеек, и увидеть значение COUNT на панели задач (в правой нижней части документа Google Sheets).
Если в вашем наборе данных есть числа, по умолчанию на панели задач будет отображаться СУММ, а не СЧЁТ. В этом случае просто нажмите на него, и он покажет вам СЧЁТ (вместе с другими данными, такими как Среднее или Макс / Мин).
Обратите внимание, что он будет включать все ячейки, в которых что-либо есть — будь то пустая строка (= ””), апостроф (‘) или пробел.
Это три способа, которыми вы можете быстро подсчитать количество ячеек, если они не пустые, в Google Таблицах.
Когда вы работаете с большими наборами данных в Google Таблицах, вам иногда нужно подсчитывать ячейки, содержащие определенный текст. Это могут быть имена, идентификаторы или даже даты. А благодаря классным формулам в Google Таблицах это можно сделать за секунды.
В этом уроке я покажу вам несколько сценариев, в которых вы можете подсчитать ячейки, содержащие определенный текст.
Подсчет ячеек, содержащих определенный текст
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро подсчитать, сколько раз имя «Отметка» встречается в столбце A.
Ниже приведена формула, которую вы можете использовать для этого:
СЧЁТЕСЛИ (A2: A13; «Отметка»)
Вот как работает приведенная выше формула СЧЁТЕСЛИ:
- Первый аргумент этой формулы — это диапазон, в котором у вас есть данные. В этом примере это A2: A13 с именами
- Второй аргумент — это критерии. Это то, что используется для проверки значения в ячейке, и если этот критерий соблюден, то ячейка считается. В этом примере я вручную ввел имя Mark в двойных кавычках. Если у вас есть этот критерий в ячейке, вы также можете использовать здесь ссылку на ячейку.
Приведенная выше формула выполняет условный подсчет, просматривая все ячейки в диапазоне и подсчитывая те ячейки, в которых присутствует текст критерия.
Обратите внимание, что текст критерия, используемый в этой формуле, не чувствителен к регистру. Итак, используете ли вы MARK, Mark или mark, результат будет одинаковым.
В этом примере я использовал имя, но это мог быть любой текст, например идентификаторы или числа.
Подсчет ячеек, не содержащих определенного текста
Вы можете немного изменить функцию СЧЁТЕСЛИ, чтобы получить количество ячеек, не содержащих определенного текста.
Например, если у меня есть набор данных, как показано ниже, и я хочу подсчитать все ячейки, имя которых не является «Маркой», я могу легко сделать это с помощью функции СЧЁТЕСЛИ.
Ниже приведена формула, по которой будет подсчитано количество всех ячеек, имя которых отличается от Mark:
= СЧЁТЕСЛИ (A2: A13; «<> Отметить»)
В приведенной выше формуле в качестве первого аргумента используется диапазон ячеек, а критерием является «<> Отметить» . Здесь знак «не равно» также должен быть заключен в двойные кавычки.
Подсчет ячеек, содержащих текст (в любом месте ячейки / частичное совпадение)
В приведенных выше примерах мы проверили все содержимое ячейки.
Поэтому, если мы хотели подсчитать все ячейки, содержащие имя Mark, мы использовали «Mark» в качестве критерия. Это подсчитало все ячейки, в которых все содержимое ячейки было «Отметить».
Но предположим, что у вас есть набор данных с полными именами (или другим текстом) вместе с именем, и вы хотите подсчитать все ячейки, содержащие имя «Отметить», тогда вы можете использовать приведенную выше формулу.
В этом случае вам нужно использовать функцию СЧЁТЕСЛИ с подстановочными знаками.
Ниже приведена формула, которая даст вам количество всех ячеек, в которых есть слово «Отметить».
= СЧЁТЕСЛИ (A2: A13; «* знак *»)
В приведенной выше функции критерий отмечен звездочкой (*) с обеих сторон.
Поскольку знак звездочки (*) — это подстановочный знак, который может представлять любое количество символов в формуле. Это означает, что там, где эта формула проверяет данное условие, может быть любое количество символов / слов до и после критериев.
Проще говоря, если в ячейке присутствует слово Mark (или любой другой критерий), эта формула будет считать ячейку.
Внимание: при использовании знака звездочки (*) в функциях Google Таблиц необходимо помнить одну вещь: условие будет считаться выполненным, как только будет найден текст критерия. Например, в приведенном выше примере, если ячейка содержит текст «Рынок» или «Маркетинг», они будут учитываться, поскольку они также содержат текст «Отметить», который не входит в критерии функции.
Если вы хотите подсчитать ячейки, проверяя наличие нескольких условий, вам необходимо использовать функцию СЧЁТЕСЛИМН.
Я надеюсь, что вы найдете этот урок полезным, и он поможет вам подсчитать ячейки с определенным текстом в Google Таблицах.
Один из вопросов, который я часто задаю, — можно ли использовать функцию СЧЁТЕСЛИ для подсчета ячеек с помощью цветов. К сожалению, нет. Однако вот руководство, в котором я показываю, как легко фильтровать и подсчитывать ячейки на основе цвета.
Читайте также: