Как посчитать количество пустых и непустых ячеек в excel

Принцип счета ячеек функциями СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ

Функция СЧЁТ подсчитывает количество только для числовых значений в заданном диапазоне. Данная формула для совей работы требует указать только лишь один аргумент – диапазон ячеек. Например, ниже приведенная формула подсчитывает количество только тех ячеек (в диапазоне B2:B6), которые содержат числовые значения:

СЧЁТЗ подсчитывает все ячейки, которые не пустые. Данную функцию удобно использовать в том случаи, когда необходимо подсчитать количество ячеек с любым типом данных: текст или число. Синтаксис формулы требует указать только лишь один аргумент – диапазон данных. Например, ниже приведенная формула подсчитывает все непустые ячейки, которые находиться в диапазоне B5:E5.

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

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

Подсчет действительно пустых ячеек.

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

ЧСТРОК( диапазон ) * ЧИСЛСТОЛБ( диапазон ) — СЧЁТЗ( диапазон )

Формула умножает количество строк на количество столбцов, чтобы получить общее количество клеток в диапазоне, из которого вы затем вычитаете количество непустых значений, возвращаемых СЧЁТЗ. Как вы помните, функция СЧЁТ в Excel рассматривает значения «» как непустые ячейки. Поэтому они не будут включены в окончательный результат.

Например, чтобы определить количество абсолютно пустых клеток в диапазоне A2: A8, используйте следующую формулу:

На скриншоте ниже показан результат:

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

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

Не стесняйтесь делиться любыми другими подсказками, которые могут у вас возникнуть. Будьте счастливы и преуспевайте в Excel!

Также рекомендуем:

Как посчитать количество заполненных и пустых ячеек в Excel

Excel предлагает несколько функций для считывания и подсчета значений в диапазоне ячеек: СЧЁТ(), СЧЁТЗ и СЧИТАТЬПУСТОТЫ. Каждая из этих функций по-своему считывает и считает значения, в зависимости о т того, является ли значение числом, текстом или просто пустой ячейкой. Рассмотрим все эти функции в действии на практическом примере.

Функция СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ для подсчета ячеек в Excel

Ниже на рисунке представлены разные методы подсчета значений из определенного диапазона данных таблицы:

В строке 9 (диапазон B9:E9) функция СЧЁТ подсчитывает числовые значения только тех учеников, которые сдали экзамен. СЧЁТЗ в столбце G (диапазон G2:G6) считает числа всех экзаменов, к которым приступили ученики. В столбце H (диапазон H2:H6) функция СЧИТАТЬПУСТОТЫ ведет счет только для экзаменов, к которым ученики еще не подошли.

Принцип счета ячеек функциями СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ

Функция СЧЁТ подсчитывает количество только для числовых значений в заданном диапазоне. Данная формула для совей работы требует указать только лишь один аргумент – диапазон ячеек. Например, ниже приведенная формула подсчитывает количество только тех ячеек (в диапазоне B2:B6), которые содержат числовые значения:

СЧЁТЗ подсчитывает все ячейки, которые не пустые. Данную функцию удобно использовать в том случаи, когда необходимо подсчитать количество ячеек с любым типом данных: текст или число. Синтаксис формулы требует указать только лишь один аргумент – диапазон данных. Например, ниже приведенная формула подсчитывает все непустые ячейки, которые находиться в диапазоне B5:E5.

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

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

Метод 3: условное форматирование и использование фильтра

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

1. Выделяем диапазон ячеек, данные которых станут критерием выбора строк.
2. Во вкладке “Главная” находим раздел “Стили”, среди инструментов находим и кликаем по кнопке “Условное форматирование”.
3. В появившемся перечне раскрываем пункт “Правила выделения ячеек”, который содержит список правил, определяющих условия форматирования. В нашем примере выделенный диапазон заполнен текстовыми данными, соответственно – выбираем строку “Текст содержит…”.
4. Далее задаем конкретные условия форматирования – для каких значений ячеек они будут окрашиваться в определенный цвет. В левой части окна указываем букву A, а в правой части оставляем установку по умолчанию (при желании – выбираем другую цветовую схему). Наша цель – чтобы все ячейки с буквой А подкрашивались в красных тонах. Нажимаем ОК.
5. Если ячейки с буквой А выделены цветом, значит настройки условного форматирования сделаны корректно.
6. Чтобы посчитать, сколько строк содержат “подкрашенные” ячейки, используем инструмент “Фильтр”.
7. Если выделение снято, опять выделяем требуемый диапазон ячеек. Во вкладке “Главная”, находим инструмент “Фильтр” (значок в виде воронки, группа инструментов “Редактирование”) и кликаем по нему.
8. На верхней ячейке выделенного столбца появится значок пока что неактивного фильтра в виде кнопки со стрелкой вниз:
   • кликаем на него;
   • ищем строку “Фильтр по цвету”, щелкаем по этому пункту;
   • выбираем цвет, использованный для условного форматирования ранее.
9. В результате работы фильтра в таблице останутся только строки с окрашенными ячейками в выбранном столбце. Теперь можно просто выделить их, чтобы определить итоговое количество в строке состояния. Это и есть количество строк, которое мы хотели выяснить согласно поставленной задаче.

Подсчет количества уникальных значений

Постановка задачи

​ записать другую формулу​ самым простым и​ оставшийся диапазон ячеек​ форматирование и последующая​

​Причем подсчитываться будут даже​ которых именно в​ она полностью повторяет​ в прошлый раз,​ в «Экселе» формулой​ однако в некоторых​СЧЕТЕСЛИМН​:​

​ деления на 0​ у тебя в​

Способ 1. Если нет пустых ячеек

​ указывающие статистическую функцию​Часто требуется выполнять функцию​ Но можно ее​ =БСЧЁТ(A1:B13;B1;D14:E15). Табличка с​ понятным.​ и смотрим на​

​ фильтрация​ полностью пустые строки​ той колонке нет​ предыдущую.​ появится окно, в​ СЧЕТЗ. В некоторых​

​ случаях его необходимо​позволяет подсчитывать ячейки,​Статистическая функция​ в дроби):​ первом столбце. Удачи)​ для расчета промежуточного​ СЧЕТЕСЛИ в Excel​​ «заставить» проанализировать 2​ ​ критериями не изменится.​​2. =СУММПРОИЗВ(—(A2:A13=D2);—(B2:B13>=E2)) Это решение сложнее,​ показатель​Выделяем диапазон, по которому​ диапазона. Стоит заметить,​

​ значений, не попадут​​Вот мы и разобрались,​​ котором необходимо ввести​ случаях она бывает​ будет предварительно включить.​ используя условие​СЧЕТЕСЛИ​

Способ 2. Если есть пустые ячейки

​Вот и все дела.​Алексей матевосов (alexm)​ результата. Подсчет количества​ по двум критериям.​ критерия одновременно.​Рассмотрим задачу, когда критерии​ но позволяет понять​«Количество»​

Мастер функций

Для неопытного пользователя легче всего производить подсчет ячеек, содержащих числа, используя формулу СЧЁТ при помощи Мастера функций.

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

   Есть и другой вариант запуска Мастера функций. Для этого после выделения ячейки нужно перейти во вкладку «Формулы». На ленте в блоке инструментов «Библиотека функций» жмем на кнопку «Вставить функцию».

   Существует ещё один вариант, наверное, самый простой, но вместе с тем требующий хорошей памяти. Выделяем ячейку на листе и жмем комбинацию клавиш на клавиатуре Shift+F3.

2. Во всех трёх случаях запустится окно Мастера функций. Для перехода к окну аргументов в категории «Статистические» или «Полный алфавитный перечень» ищем элемент «СЧЁТ». Выделяем его и кликаем по кнопке «OK».

   Также окно аргументов можно запустить другим способом. Выделяем ячейку для вывода результата и переходим во вкладку «Формулы». На ленте в группе настроек «Библиотека функций» жмем по кнопке «Другие функции». Из появившегося списка наводим курсор на позицию «Статистические». В открывшемся меню выбираем пункт «СЧЁТ».

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

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

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

Работа с оператором СЧЁТ

Функция СЧЁТ относится к большой группе статистических операторов, в которую входит около сотни наименований. Очень близка к ней по своим задачам функция СЧЁТЗ. Но, в отличие от предмета нашего обсуждения, она учитывает ячейки, заполненные абсолютно любыми данными. Оператор СЧЁТ, о котором мы поведем подробный разговор, ведет подсчет только ячеек, заполненных данными в числовом формате.

Какие же данные относятся к числовым? Сюда однозначно относятся собственно числа, а также формат даты и времени. Логические значения («ИСТИНА», «ЛОЖЬ» и т.д.) функция СЧЁТ учитывает только тогда, когда они являются именно её непосредственным аргументом. Если же они просто находятся в области листа, на которую ссылается аргумент, то в таком случае оператор их в расчет не берет. Аналогичная ситуация с текстовым представлением чисел, то есть, когда числа записаны в кавычки или окружены другими знаками. Тут тоже, если они являются непосредственным аргументом, то принимают участие в подсчете, а если просто на листе, то не принимают.

А вот применительно к чистому тексту, в котором не присутствуют цифры, или к ошибочным выражениям («#ДЕЛ/0!», #ЗНАЧ! И т.д.) ситуация другая. Такие значения функция СЧЁТ не учитывает в любом виде.

Кроме функций СЧЁТ и СЧЁТЗ, подсчетом количества заполненных ячеек занимается ещё операторы СЧЁТЕСЛИ и СЧЁТЕСЛИМН. С помощью данных формул можно производить подсчет с учётом дополнительных условий. Этой группе статистических операторов посвящена отдельная тема.

Подсчет цифр в ячейке и подсчет ячеек в Excel

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

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

Подсчет значений с множественными критериями (Часть 1. Условие И) в MS EXCEL

​ формулу:​их игнорирует.​Технически, эта формула пробегает​ 1).End(xlUp))​

​ такое количество ячеек,​ одном диапазоне. Условие​Функция СЧЕТЕСЛИ входит в​ вместо​ с определенным Фруктом​, кликаем по кнопке​

Задача1

​ этой области отобразились​ присутствуют значения, а​ чтобы не произошло​

​Далее вы можете указать​ с запятой прописать​​ отображение предусмотрено. В​​ может быть полезно​Функция​А вот ячейки, содержащие​ по всем ячейкам​Columns(«B:C»).Clear​ как в диапазоне​ поиска – один​ группу статистических функций.​ENTER​И​​«Сортировка и фильтр»​​ в поле окна​ в некоторых случаях​ двойного подсчета, так​​ еще диапазоны и​​ диапазон ячеек, в​ любом случае, если​ в разных случаях,​СЧЕТЕСЛИ​ значения даты и​

​ массива и вычисляет​For Each cc​

​ с критериями. После​ критерий.​ Позволяет найти число​

​нажать​с Количеством на​в группе инструментов​ аргументов, жмем на​ нужно произвести подсчет​ как нам нужно​ критерии к ним.​

​ которых необходимо проводить​ после выделения диапазона​

• ​ но главное, что​позволяет использовать в​ времени, учитываются:​ для каждого элемента​ In se.Cells​ введения аргументов нажать​​Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:​​ ячеек по определенному​
• ​CTRL+SHIFT+ENTER​ складе не менее​«Редактирование»​
• ​ кнопку​ всех элементов в​ узнать количество именно​По окончании ввода параметров​ подсчет.​ ячеек с данными​ данная возможность в​ качестве условия даже​​Функция​​ количество его вхождений​If WorksheetFunction.CountIf(se.Offset(0, 1),​ одновременно сочетание клавиш​Во втором случае в​ критерию. Работает с​
• ​5. Формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет​ минимального (Условие И​. В появившемся списке​«OK»​

​ совокупности, включая и​ строк, выделяем только​ нажмите кнопку «ОК».​Продолжая разжевывать тему, как​ нужный параметр не​

​ программе есть. В​ формулы. К примеру,​СЧЕТ​ в диапазон с​ cc.Value)

Формулы для подсчета пустых ячеек.

Функция СЧИТАТЬПУСТОТЫ.

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

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

СЧИТАТЬПУСТОТЫ(диапазон)

Где диапазон — это область вашего рабочего листа, в которой должны подсчитываться позиции без данных.

Вот пример формулы в самой простейшей форме:

Чтобы эффективно использовать эту функцию, важно понимать, что именно она подсчитывает

1. Содержимое в виде текста, чисел, дат, логических значений или ошибок, не учитывается.
2. Нули также не учитываются, даже если скрыты форматированием.
3. Формулы, возвращающие пустые значения («»), — учитываются.

Глядя на рисунок выше, обратите внимание, что A7, содержащая формулу, возвращающую пустое значение, подсчитывается по-разному:

• СЧИТАТЬПУСТОТЫ считает её пустой, потому что она визуально кажется таковой.
• СЧЁТЗ  обрабатывает её как имеющую содержимое, потому что она фактически содержит формулу.

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

Как вы видите на рисунке выше, для подсчета непустых ячеек отлично подходит функция СЧЁТЗ:

СЧИТАТЬПУСТОТЫ — наиболее удобный, но не единственный способ подсчета пустых ячеек в Excel. Следующие примеры демонстрируют несколько других методов и объясняют, какую формулу лучше всего использовать в каждом сценарии.

Применяем СЧЁТЕСЛИ или СЧЁТЕСЛИМН.

Другим способом подсчета пустых ячеек в Excel является использование функций  СЧЁТЕСЛИ или СЧЁТЕСЛИМН  с пустой строкой («») в качестве критериев.

В нашем случае формулы выглядят следующим образом:

или

Возвращаясь к ранее сказанному, вы можете также использовать выражение

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

Подсчёт пустых ячеек с условием.

В ситуации, когда вы хотите подсчитать пустые ячейки на основе некоторого условия, функция СЧЁТЕСЛИМН является весьма подходящей, поскольку ее синтаксис предусматривает несколько критериев.

Например, чтобы определить количество позиций, в которых записано «Бананы» в столбце A и ничего не заполнено в столбце C, используйте эту формулу:

Или введите условие в предопределенную позицию, скажем F1, что будет гораздо правильнее:

Пример использования функции СЧЁТЕСЛИ с условием

Очень часто используется такая разновидность функции «СЧЁТ». С помощью заданной формулы можно узнать количество ячеек с заданными параметрами. Функция имеет имя «СЧЁТЕСЛИ». В ней могут учитываться такие аргументы.

1. Диапазон. Табличная область, в которой будут искаться определённые элементы.
2. Критерий. Признак, который разыскивается в заданной области.

Синтаксис выглядит так:

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

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

Пример 3. Есть ведомость с фамилиями студентов и оценками за экзамен. В таблице 2 столбца и 10 ячеек. Нужно определить, какое количество студентов получили отличную оценку 5 (по пятибалльной системе оценивания), а какое 4, потом 3 и 2.

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

После нажатия на клавиатуре Enter будет получен результат:

Пример суммирования с использованием функции СУММЕСЛИ