Функция счетесли в excel

Как подсчитать пустые строки в Excel.

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

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

А затем используйте функцию СЧЁТЕСЛИ, чтобы узнать, в каком количестве строк все позиции пусты. Поскольку наша исходная таблица содержит 4 столбца (от A до D), мы подсчитываем строки с четырьмя пустыми клетками:

Вместо жесткого указания количества столбцов вы можете использовать функцию ЧИСЛСТОЛБ (COLUMNS в английской версии) для его автоматического вычисления:

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

Разберём, как это работает:

Сначала вы проверяете весь диапазон на наличие непустых ячеек с помощью выражения типа A2:D10 <> “”, а затем приводите возвращаемые логические значения ИСТИНА и ЛОЖЬ к 1 и 0 с помощью двойного отрицания (–). Результатом этой операции является двумерный массив единиц (означают непустые ячейки) и нулей (пустые).
При помощи СТРОКА создаётся вертикальный массив числовых ненулевых значений, в котором количество элементов равно количеству столбцов диапазона. В нашем случае диапазон состоит из 4 столбцов (A2:В10), поэтому мы получаем такой массив: {1; 2; 3; 4}
Функция МУМНОЖ вычисляет матричное произведение вышеупомянутых массивов и выдает результат вида: {7; 10; 6; 0; 5; 6; 0; 5; 6}. В этом массиве для нас имеет значение только нулевые значения, указывающие на строки, в которых все клетки пусты.
Наконец, вы сравниваете каждый элемент полученного выше массива с нулем, приводите ИСТИНА и ЛОЖЬ к 1 и 0, а затем суммируете элементы этого последнего массива: {0; 0; 0; 1; 0; 0; 1; 0; 0}. Помня, что 1 соответствуют пустым строкам, вы получите желаемый результат.

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

Здесь вы используете функцию СЧЁТЕСЛИ, чтобы узнать, сколько значений содержится в каждой строке, а ДВССЫЛ “подает” строки в СЧЁТЕСЛИ одну за другой. Результатом этой операции является массив вида {3; 4; 3; 0; 2; 3; 0; 2; 3}. Проверка на 0 преобразует указанный выше массив в {0; 0; 0; 1; 0; 0; 1; 0; 0}, где единицы представляют пустые строки. Вам остается просто сложить эти цифры.

Обратите также внимание, что это формула массива. На скриншоте выше вы можете увидеть результат работы этих двух формул

На скриншоте выше вы можете увидеть результат работы этих двух формул.

Также следуем отметить важную особенность работы этих выражений с псевдо-пустыми ячейками. Добавим в C5

=ЕСЛИ(1=1; “”)

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

Функция СЧЁТЕСЛИМН в Excel

Первое решение

В этом способе применяется функция СЧЕТЗ(). Для этого создаем дополнительный столбец, в каждую ячейку которого вставляем формулу:

=1*(COUNTA(B2:N2)>)

Ячейки B2 и N2 являются величинами переменными, которые изменяются в зависимости от строки. Функция COUNTA() – это английское название функции СЧЕТЗ().

Результат работы этой формулы таков. Если хотя бы в одной ячейке строки имеется значение, то строка считается непустой и в соответствующей ячейке дополнительного столбца помещается единица (1). Если же ни в одной ячейке строки нет значения, то строка считается пустой и в ячейке дополнительного столбца помещается значение нуль (0).

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

Результат работы представлен ниже:

Вроде бы и ничего результат. Все работает. Но выглядит как-то криво. Дополнительный столбец выполняет только одну единственную задачу – определение строки и мешается, занимая место. Конечно, можно скрыть его. Для этого нажимаем правой кнопкой мыши на заголовке дополнительного столбца (О) и в контекстном меню выбираем “Скрыть”. Но конечный результат меня не устраивал. Поэтому было найдено второе решение.

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

В диапазоне ячеек A1:A8 введем значения:

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

=СЧЁТЕСЛИ(A1:A8;»>10″)

Результатом будет значение 5

Если же нам нужно из этих ячеек найти количество больше 10 но меньше 50 то тут формула будет выглядеть иначе:

=СЧЁТЕСЛИ(A1:A8;»>10″)-СЧЁТЕСЛИ(A1:A8;»>50″)

Глаза вас не обманули: в формуле СЧЁТЕСЛИ(A1:A8;»>50″) действительно написано больше 50… Но если подсчитать число ячеек, значение которых больше 10 и вычесть из него количество ячеек значение которых больше 50, в результате и получится количество ячеек, числа в которых лежат в переделах больше 10 и меньше 50… Такая вот загогулина…

В этом видео показано как применяют функцию СЧЕТЕСЛИ в excel:

Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

Новые статьи

YouTube все — ютубКапут — (видео) — 20/03/2022 11:22
Обращение к макросу через кнопку, как получить и передать данные на другой лист в книге excel — 26/09/2021 12:53
Опять разбиваем текст в ячейке — 12/09/2021 08:05
Как разбить ячейку в эксель, как сделать нормальную таблицу в Excel — 12/06/2021 18:30
Исправляем ошибку VBA № 5854 слишком длинный строковый параметр в шаблоне word из таблицы excel 255 символов — 21/02/2021 08:54
База данных из JavaScript для веб страницы из Excel на VBA модуле — 30/11/2019 09:15
Листы в Excel из списка по шаблону — 02/06/2019 15:42
Печать верхней строки на каждой странице в Excel — 04/06/2017 17:05
Создание диаграммы, гистограммы в Excel — 04/06/2017 15:12
Функция СИМВОЛ в Excel или как верстать HTML в Excel — 03/06/2017 17:32
Функция ЕСЛИОШИБКА в excel, пример использования — 20/05/2017 11:39
Как использовать функцию МИН в excel — 20/05/2017 11:36
Как использовать функцию МАКС в excel — 20/05/2017 11:33
Как использовать функцию ПРОПИСН в excel — 20/05/2017 11:31
Как использовать функцию СТРОЧН в excel — 20/05/2017 11:29

Предыдущие статьи

Как использовать функцию Функция СЧЁТ в excel — 20/05/2017 11:09
Как использовать функцию ПОИСК в эксель — 10/03/2017 21:28
Как использовать функцию СЦЕПИТЬ в эксель — 10/03/2017 20:41
Как использовать функцию ПРАВСИМВ в excel — 10/03/2017 20:35
Как использовать функцию ЛЕВСИМВ в excel — 06/03/2017 16:04
Как использовать функцию ЗАМЕНИТЬ в excel — 28/02/2017 18:44
Как использовать функцию ДЛСТР в эксель — 25/02/2017 15:07
Как использовать функцию ЕСЛИ в эксель — 24/02/2017 19:37
Как использовать функцию СУММЕСЛИ в Excel — 22/02/2017 19:08
Как использовать функцию СУММ в эксель — 20/02/2017 19:54
Печать документа в Excel и настройка печати — 16/02/2017 19:15
Условное форматирование в ячейках таблицы Excel — 16/06/2016 17:38
Объединить строку и дату в Excel в одной ячейке — 16/06/2016 17:33
Горячие клавиши в Microsoft Office Excel — 04/06/2016 14:57
Как использовать эксель в качестве фотошопа — 04/06/2016 09:01
Как разделить текст по столбцам, как разделить ячейки в Excel — 14/04/2016 16:19
Как применить функцию ВПР в Excel для поиска данных на листе — 08/01/2016 23:40
Как создать таблицу в Excel, оформление таблицы — 06/01/2016 20:29
Работа в эксель, как начать пользоваться Excel — 26/12/2015 15:48

Примеры

Метод 2: применение функции СЧЕТЗ

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

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

Эта функция подсчитывает только ячейки с данными, но имейте в виду, что «данные» могут включать пробелы, которые не видны. Да, в этом примере пробелы можно подсчитать самостоятельно, но представьте, что книга велика. Итак, чтобы использовать формулу:

Определить диапазон ячеек, которые нужно подсчитать. В приведенном примере это ячейки с B2 по D6.

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

Ввести формулу в ячейке результата или строке формул и нажать клавишу ВВОД:

Можно также подсчитать ячейки из нескольких диапазонов. В этом примере подсчитываются ячейки в ячейках b2–D6 и b9–D13.

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

Если известно, что нужно учесть только числа и даты, но не текстовые данные, используйте функцию СЧЕТ.

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

Инструмент «Выделить группу ячеек»

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

На ленте меню Главная выбираем Найти и выделить, затем — Bыделить группу ячеек. В открывшемся окне активируем кнопку Пустые ячейки и жмем ОК.

В результате все клетки без значений будут выделены.

Вы можете использовать «Цвет заливки» на вкладке «Главная», чтобы изменить цвет фона пустых ячеек и зафиксировать выделение.Обратите внимание, что этот инструмент не обнаруживает псевдо-пустые позиции — с формулами, возвращающими пустое значение. То есть, они не будут выделены

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

Также вы, я думаю, заметили, что аналогичным образом можно выделить и непустые ячейки. Просто нужно выбрать другой объект поиска — либо Константы, либо Формулы.

Подсчет заполненных ячеек в Microsoft Excel

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

Подсчет ячеек

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

Способ 1: счетчик на строке состояния

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

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

Способ 2: функция СЧЁТЗ

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

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

Способ 3: функция СЧЁТ

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

Как и в предыдущем случае, выделяем ячейку, куда будут выводиться данные и таким же способом запускаем Мастер функций. В нём выбираем оператора с наименованием «СЧЁТ». Жмем на кнопку «OK».

Для ручного введения формулы придерживаемся следующего синтаксиса:

Способ 4: функция СЧЁТЕСЛИ

Данная функция позволяет подсчитать не просто количество ячеек, заполненных числовыми выражениями, но только такие из них, которые соответствуют определенному условию. Например, если задать условие «>50», то будут учитываться только такие ячейки, в которых содержится значение больше числа 50. Также можно задавать значения «<» (меньше), «< >» (не равно) и т.д.

После того, как выделили ячейку для вывода результата и запустили Мастер функций, выбираем запись «СЧЁТЕСЛИ». Кликаем по кнопке «OK».

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

Способ 5: функция СЧЁТЕСЛИМН

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

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

Синтаксис у этой функции следующий:

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

Мы рады, что смогли помочь Вам в решении проблемы. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Заполняем аргументы функции

Как в Эксель посчитать количество ячеек, значений, чисел

Дополнительная информация

Формула для подсчета количества вхождений текстовой строки в диапазоне

=SUM(LEN(range)-LEN(SUBSTITUTE(range,”text”,””)))/LEN(“text”)

Где диапазон — диапазон ячеек, а “text” заменяется конкретной текстовой строкой, которую вы хотите посчитать.

Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массива в Excel для Windows, нажмите CTRL+SHIFT+ENTER. Чтобы ввести формулу в качестве массива в Excel для Macintosh, нажмите COMMAND+RETURN.

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

Пример 1. Подсчет количества вхождений текстовой строки в диапазоне

Запустите Excel, а затем откройте новую книгу

Введите следующие сведения на Листе 1:

Значение ячейки A8 составляет 4, потому что текст “яблоко” появляется четыре раза в диапазоне.

Формула для подсчета количества происшествий одного символа в одной ячейке

=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,”a”,””))

Где cell_refссылка на ячейку, и “а” заменяется символом, который вы хотите посчитать.

Эта формула не должна быть введена в качестве массивной формулы.

Пример 2. Подсчет количества появлений символа в одной ячейке.

Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество появлений символа “p” в A7. Введите следующую формулу в ячейку A9:

Значение ячейки A9 составляет 3, потому что символ “p” появляется три раза в A7.

Формула для подсчета количества появлений одного символа в диапазоне

=SUM(LEN(range)-LEN(SUBSTITUTE(range,”a”,””)))

Где range — это диапазон нужной ячейки, а “а” заменяется символом, который вы хотите сосчитать.

Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массивной формулы в Excel, нажмите на CTRL+SHIFT+ENTER.

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

Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество символа “p” в A2:A7. В ячейку А10 введите следующую формулу:

Значение ячейки A10 составляет 11, потому что символ “p” появляется 11 раз в A2:A7.

Формула для подсчета количества слов, разделенных символом в ячейке

=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,””))+1)

Где cell_ref – это ссылка на ячейку, а char – это символ, разделяющий слова.

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

Пример 4. Подсчет количества слов, разделенных пространством в ячейке

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

Запустите Excel, а затем откройте новую книгу

Введите следующие сведения на Листе 1:

Формула в ячейке A2 возвращает значение 4, чтобы отразить, что строка содержит четыре слова, разделенных пробелами. Если слова разделены несколькими пробелами или если слова начинаются или заканчиваются пробелами, это не имеет значения. Функция TRIM удаляет дополнительные пробелы и пробелы в начале и в конце в тексте ячейки.

В Excel можно также использовать макрос для подсчета количества случаев появления определенного символа в ячейке или диапазоне ячеек.

Как перевернуть текст в excel

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

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

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

Создаем условие, что количество пробелов равно нулю, и помещаем это выражение в логический тест ЕСЛИ:СЧИТАТЬПУСТОТЫ(B2:D2)=0
Если логический результат оценивается как ИСТИНА, выведите «Нет пустых».
Если же — ЛОЖЬ, возвращаем «Пустые».

Полная формула принимает такой вид:

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

Или вы можете создать другой расчет в зависимости от количества незаполненных позиций. Например, если в диапазоне нет пустот (т.е. если СЧИТАТЬПУСТОТЫ возвращает 0), сложите цифры продаж, в противном случае покажите предупреждение:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

или

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

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

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

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

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

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

СЧЕТЕСЛИ с несколькими условиями.

Подсчет заполненных ячеек в Microsoft Excel

СЧЕТЧИК

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

= COUNTDOWN (интервал)

Давайте покажем пример, чтобы увидеть, как работает эта функция:

Мы вводим таблицу с десятью самыми густонаселенными странами. Чтобы проверить, была ли ошибка, заполняющая данные, давайте использовать Говорить пусто.

Чтобы создать формулу функции, мы должны правильно ввести требуемый интервал. Поскольку мы хотим сначала проверить ячейки столбца «позиция», давайте определим диапазон как B4: B13.

По завершении функции мы видим, что результатом был «0». То есть в указанном диапазоне нет пустых ячеек. Повторяя процедуру для других столбцов, мы будем иметь следующий сценарий:

Мы можем видеть красным, что функция возвратила два пустых пространства в столбце «Население». Проведя конференцию на столе, мы заметили, что число жителей Индонезии и Нигерии не было напечатано.

Этот инструмент полезен для обнаружения возможных ошибок на более длинных интервалах

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

Считаем текстовые значения

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

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

Вписываем команду «=СЧЕТЕСЛИ» и открываем скобку.

Выделяем диапазон столбца с названиями позиций.

Теперь ставим знак «;» и пишем «торшеры». Между диапазоном и критерием пробел не ставится.

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