Как подсчитать непустые ячейки с помощью Countif (2023)

Что такое Каунтиф?

Countif — популярная функция, используемая для подсчета непустых ячеек. Эта функция совместима со всеми программами для работы с электронными таблицами, такими как Excel, Google Sheets или Numbers.

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

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

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

В этой статье мы поговорим о функции «Не пусто» функции СЧЁТЕСЛИ. Как следует из названия, COUNTIF с критерием Not Blank в электронных таблицах используется для подсчета непустых ячеек в столбце. В этой функции подсчитываются ячейки с данными, тогда как пустые ячейки исключаются и не учитываются.

Формула графифа

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

=countif(диапазон,критерий)

Countif с аннотацией Not Blank

Ниже приведена общая форма формулы Countif с непустыми критериями:

=countif(диапазон,”<>“)Countif Непустая формулаCountif Непустая формула

В этой формуле мы указываем Countif для подсчета всех ячеек в заданном диапазоне, которые не равны ничему (представлены символом <>).

Пример № 1: один столбец

На изображении ниже у нас есть два столбца. Месяц и бюджет. Чтобы найти заполненные ячейки в столбце «Бюджет» (от C4 до C15), вы можете использовать эту формулу:

=счетчик(C4:C15,”<>“)Подсчет пустых ячеек в диапазоне от C4 до C15Подсчет пустых ячеек в диапазоне от C4 до C15

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

Пример № 2: несколько столбцов

Чтобы найти непустые ячейки в обоих этих столбцах (от B4 до C15), вы должны ввести следующую формулу:

=счетчик(B4:C15,”<>“)Подсчет пустых ячеек в диапазоне от B4 до C15Подсчет пустых ячеек в диапазоне от B4 до C15

И в результате получится 19, показывая, что в диапазоне есть 19 непустых ячеек.

Знакомство с функцией CountA

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

=СЧЕТЧАСТЬ(B4:C15)Использование формулы CountA для подсчета непустых ячеекИспользование формулы CountA для подсчета непустых ячеек

Который показывает результат 19, такой же, как функция Countif Not Blank.

Следует помнить, что функция CountA не может принимать более одного аргумента. Функцию Countif лучше использовать, если вы планируете использовать другие аргументы с данными.

Преимущество CountA: несколько диапазонов

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

Например, введите следующую формулу в ячейку I5:

=СЧЕТЧ(B4:C15;D4:H5)Использование нескольких диапазонов в формуле CountAИспользование нескольких диапазонов в формуле CountA

Это показывает результат 25, который имеет два разных диапазона, т.е. диапазон B4:C15 и диапазон D4:H5.

Использование Countif для нескольких диапазонов и критериев

Countif по-прежнему можно использовать для нескольких диапазонов, но это немного сложнее, чем CountA.

Например, посмотрите на следующую формулу в столбце J4:

=СЧЁТЕСЛИМН(B4:B15,”<>“&””,C4:C15,”<10000")Используйте несколько критериев с формулой CountIFsИспользуйте несколько критериев с формулой CountIFs

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

=СЧЁТЕСЛИМН(A1:A10,”<>0″,A1:A10,”<>“)

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

=СЧЁТЕСЛИМН(А:А,”В”,В:В,”>0″)

Следует помнить, что Countifs подсчитывает только те значения, которые соответствуют всем критериям. Вы также можете использовать функцию DCountA для вычисления непустых ячеек в поле с заданными критериями.

Используйте несколько функций Countif

Если вы не уверены в Countifs или он не работает, вы можете использовать несколько функций Countif для достижения того же. Посмотрите на формулу ниже:

=(СЧЁТЕСЛИ(B4:B15,”<>“)+СЧЁТЕСЛИ(C4:C15,”<>“)+СЧЁТЕСЛИ(D4:D15,”<>“))

Эта формула подсчитывает все пустые ячейки в трех разных диапазонах. Вы также можете использовать разные критерии для разных функций Countif.

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

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

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

=СЧЁТЕСЛИ(B4:C15;”<>“)Проблема невидимых непустых ячеекПроблема невидимых непустых ячеек

Теперь на изображении вы можете видеть, что всего ячеек в B4:C15 24.

Пустые ячейки, показанные на изображении, равны 5 (C6, C9, C11, C13 и C14). Итак, непустых ячеек должно быть 19 (24-5), но результат в D4 показывает 20.

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

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

Шаг 1: Найдите невидимые непустые ячейки с помощью формулы «Длина»

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

Мы можем узнать это, используя формулу длины. Следуя приведенному выше примеру, введите следующую формулу в D4:

=ДЛСТР(C4)Нахождение ячейки с пробелом с помощью формулы LENНахождение ячейки с пробелом с помощью формулы LEN

Теперь скопируйте формулу в ячейку D15. После этого вы заметите, что в ячейке D13 отображается 1 символ, но в ячейке C13 не отображается ни одного символа, который показывает нам, что в ячейке C13 есть невидимый символ.

Теперь выберите ячейку C13 и нажмите Delete. Вы заметите, что ячейка D4 показывает 19 непустых ячеек, что является правильным ответом.

Шаг 2: Проверка непустого счета

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

Во-первых, вот формула для подсчета пробелов с помощью Countif:

=СЧЁТЕСЛИ(B4:C15,””)

Это показывает результат 5 в ячейке G4. Вы также можете использовать формулу =СЧИТАТЬПУСТОТЫ(B4:C15).

Теперь подсчитайте общее количество ячеек в диапазоне следующим образом:

=СТРОКИ(B4:C15)*СТОЛБЦЫ(B4:C15)Подтверждение результата Countif Not Blank Подтверждение результата Countif Not Blank

Это показывает результат 24 в ячейке H4.

Теперь мы можем подтвердить, что Countif с «непустым» параметром показывает правильное количество ячеек, равное 19.

24 – 5 = 19

Проблема 2: проблема со скрытым апострофом

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

Чтобы понять проблему, посмотрите на формулу в ячейке D4 на изображении ниже:

=СЧЁТЕСЛИ(B4:C15;”<>“)

Ячейка показывает, что есть 20 непустых ячеек, но мы уже знаем (из рассмотренного ранее примера), что в ней 19 непустых ячеек.

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

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

Решение: используйте функцию «Умножить на 1», чтобы найти скрытый апостроф.

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

В ячейку F4 введите следующую формулу:

=С4*1Установите формулу умножения на 1 в ячейке F4Установите формулу умножения на 1 в ячейке F4

Теперь скопируйте формулу в ячейку F15. Затем вы заметите ошибку #value в ячейке F9.

Теперь выберите ячейку C9, и вы увидите скрытый апостроф в строке формул.

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

Скопируйте формулу умножения на 1 в другие ячейки и из-за ошибки значения из-за апострофа в ячейке C9Скопируйте формулу умножения на 1 в другие ячейки и из-за ошибки значения из-за апострофа в ячейке C9

Проблема 3: Проблема с пустой строкой (=””)

Подобно пробелам и апострофам, пустая строка (=””) также не отображается в ячейке.

Функция «Длина» не будет отображать длину пустой строки, но работает метод «Умножить на 1», описанный выше в разделе «Скрытый апостроф».

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

“=”

Теперь вы заметите, что вывод Countif not empty увеличился на 1 и стал 20, но ячейка C14 явно пуста. Теперь введите следующую формулу в ячейку E4:

=ДЛСТР(C4)

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

Решение: используйте функцию «Умножить на 1», чтобы найти пустую строку.

Теперь введите следующую формулу в ячейку F4:

=С4*1

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

Теперь выберите ячейку C4, и вы увидите пустую строку (=””) в строке формул приложения для работы с электронными таблицами.

Поиск пустой строковой ячейки путем умножения на 1 ФормулаПоиск пустой строковой ячейки путем умножения на 1 Формула

Теперь удалите пустую строку из ячейки F14, и формула Countif not empty в ячейке D4 теперь показывает точный результат 19.

Вы также можете использовать метод «Умножить на 1», чтобы найти ячейки с пробелами.

Обходной путь для всех проблем: Использование СУММПРОИЗВ

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

Чтобы обойти эту ручную работу, введите следующую формулу в ячейку F4, которая использует СУММПРОИЗВ:

=СУММПРОИЗВ((ОТРЕЗАТЬ(B4:C15)<>“”)*1)Функция суммирования, показывающая правильный ответ, в то время как Countif не является пустым, показывающая неверный результат из-за невидимых непустых ячеекФункция суммирования, показывающая правильный ответ, в то время как Countif не является пустым, показывающая неверный результат из-за невидимых непустых ячеек

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

  • В этой формуле TRIM(B4:C15) используется для удаления пробелов из вывода.
  • TRIM(B4:C15)<>”” используется для определения того, что ячейки не пусты.
  • Затем (TRIM(B4:C15)<>””)*1 преобразует логический вывод (True для не пустого, False для пустого) в их алгебраический аналог, т. е. 1 для True и 0 для False.
  • Теперь СУММПРОИЗВ будет умножать и суммировать массивы, в результате чего здесь получится 19.

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

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *