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

Обновлено: 21.11.2024

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

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

Что на этой странице

1. Подсчет непустых ячеек — функция СЧЁТЕСЛИ

В приведенном выше столбце B есть несколько пустых ячеек, на самом деле они совершенно пусты.

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

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

В данном случае "<>" означает "не равно" и "ничего", поэтому функция СЧЁТЕСЛИ подсчитывает количество ячеек, которые не равны ничему. Другими словами, непустые ячейки.

2. Подсчет непустых ячеек — функция СЧЕТЧИК

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

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

3. Функции COUNTIF и COUNTA возвращают неожиданные результаты

Однако бывают ситуации, когда функции СЧЁТЕСЛИ и СЧЁТЕСЛИ возвращают неожиданные результаты, если вы не знаете, как они работают.

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

Ячейки C5 и C9 содержат формулу, которая возвращает пустое значение, и функции СЧЁТЕСЛИ, и СЧЁТЕСЛИ считают эти ячейки непустыми.

Ячейка C8 содержит два символа пробела, а ячейка C12 — один символ пробела. Столбец E показывает их наличие, подсчитывая длину символа. Функции COUNTIF и COUNTA также считают эти ячейки непустыми.

4. Подсчет непустых ячеек - функция СУММПРОИЗВ

Следующая формула подсчитывает все непустые значения в диапазоне ячеек C3:C13, за исключением формул, которые ничего не возвращают. Он проверяет, не равны ли значения в диапазоне ячеек C3:C13 нулю.

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

4.1 Объяснение формулы в ячейке B16

Шаг 1. Проверьте, не пусты ли ячейки

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

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

Шаг 2. Преобразование логических значений

Функция СУММПРОИЗВ не может суммировать логические значения, нам нужно умножить на единицу, чтобы создать массив, содержащий 0 (ноль) и 1.

Они являются их числовыми эквивалентами:
True = 1
FALSE = 0 (ноль)

Шаг 3. Суммирование чисел

Зачем использовать функцию СУММПРОИЗВ, а не функцию СУММ? Функция СУММПРОИЗВ может выполнять вычисления в аргументах без необходимости вводить формулу как формулу массива.

Формулы массива — это прекрасно, но по возможности избегайте их. У пользователей Excel 365 этой проблемы нет, формулы динамического массива вводятся как обычные формулы.

и возвращает 9 в ячейке B16.

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

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

Иногда для лучшей визуализации или по другим причинам в вашей книге могут быть пустые ячейки. Но в процессе расчета или мониторинга данных приходится считать только непустые или непустые ячейки.

В этой статье я сосредоточусь на 6 полезных методах подсчета непустых ячеек в Excel.

Скачать рабочую тетрадь

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

На следующем рисунке мы видим овощной товар и страну-производителя. Но существуют пустые или пустые ячейки. Но мы должны учитывать не пустые ячейки для расчета. И мы можем использовать следующие методы.

1. Функция СЧЁТ

Функция COUNTA показывает количество непустых ячеек. Он также учитывает значения ошибок, текст, числа, формулы, пустые тексты (= "") при подсчете количества ячеек. Синтаксис функции:

Здесь аргументами могут быть ссылки на ячейки, элементы или диапазоны ячеек.

Выберите пустую ячейку, например F7.
Введите формулу =СЧЁТЕЗ (B4:C16), где B4 – это значение1, а C16 – значение2.
Нажмите клавишу ВВОД.

2. Функция СЧЁТЕСЛИ

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

Здесь диапазон относится к диапазону ячеек, а критерии означают любое условие.

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

Выберите пустую ячейку, например G7.
Введите формулу: =СЧЁТЕСЛИ(B4:D16, "<>"&""), где от B4 до D16 – диапазон ячеек, а "<>"& " – критерии.
Нажмите клавишу ВВОД.

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

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

Здесь диапазон1, диапазон2 относятся к первому и второму диапазону ячеек соответственно, а критерии1, критерии2 относятся к условиям для первого и второго диапазона ячеек.

Шаги:

Выберите пустую ячейку, а именно F7.
Введите формулу, например =СЧЁТЕСЛИМН(B4:C16,"<>")
Нажмите клавишу ВВОД.

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

Ну, функция СЧИТАТЬПУСТОТЫ в основном используется для подсчета пустых или пустых ячеек в диапазоне. Но вы можете легко подсчитать непустые ячейки путем вычитания из произведения СТРОКИ и СТОЛБЦЫ. ROWS и COLUMNS возвращают количество строк и столбцов в ссылке или массиве. Синтаксис трех функций

Здесь диапазон и массив в основном означают диапазон ячеек.

В следующем наборе данных есть несколько пустых ячеек. С помощью функции мы получим непустые ячейки.

Шаги:

Выберите пустую ячейку, например F7.
Введите формулу, а именно. =СТРОКИ(B4:C16)*СТОЛБЦЫ(B4:C16)-СЧИТАТЬПУСТОТЫ(B4:C16), где от B4 до C16 – диапазон ячеек
Нажмите клавишу ВВОД.

5. Функция СУММПРОИЗВ

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

Синтаксис функции СУММПРОИЗВ

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

Мы можем применить эту функцию к нашему набору данных следующим образом

Шаги:

Выберите пустую ячейку, например F9.
Введите формулу, т.е. =СУММПРОИЗВ((B4:C16<>"")*1), где от B4 до C16 — диапазон ячеек, логическое выражение подсчитывает ячейки, содержащие пробелы, но не формулы и пустые тексты, которые ничего не возвращают.
Нажмите клавишу ВВОД.

В выходных данных показано, что количество непустых ячеек, использующих СУММПРОИЗВ, равно 20, а для СЧЕТЧИКА – 21, что означает, что C9 не учитывается в случае СУММПРОИЗВ.

Тот же результат можно получить, используя комбинацию функций СУММПРОИЗВ и ДЛСТР. LEN может подсчитывать все специальные символы, непечатаемые символы и все пробелы в ячейке Excel. Синтаксис LEN:

Функция ДЛСТР находит количество символов для каждой ячейки в диапазоне, поэтому СУММПРОИЗВ подсчитывает ячейки, содержащие хотя бы один символ.

Затем функция СУММПРОИЗВ возвращает массив со значениями "Истина" и "Ложь". Символ «–» преобразует True в 1, а False в 0. Затем СУММПРОИЗВ суммирует значения и возвращает результат.

Шаги:

Выберите пустую ячейку
Введите формулу =СУММПРОИЗВ(--(LEN(B4:C16)>0))
Нажмите клавишу ВВОД.

6. Инструмент «Найти и заменить»

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

Шаги:

Выберите диапазон ячеек, в котором вы хотите подсчитать пустые ячейки.
Выберите диалоговое окно «Найти и выбрать» на вкладке «Главная».
Нажмите кнопку "Найти".

Примечание. Вы можете использовать CTRL+F в качестве сочетания клавиш для вышеуказанного процесса

Введите отметку * в поле «Найти что» и выберите «Найти все».
Вы получите 20 ячеек с именем листа, именем ячейки и значением.

О чем нужно помнить

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

Над сообщением показана опечатка в формуле

Заключение

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

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

Откройте электронную таблицу Excel.

Найдите ячейки, которые нужно посчитать. Например, если у вас есть спорадические данные, которые начинаются в ячейке A1 и продолжаются до E100, то их ссылка будет "A1:E100". Если у вас есть отдельные разделы данных, которые необходимо включить, также получите ссылку на их местоположение, например "G1: G100" или просто "F1" для одной записи.

Введите "=COUNTA(ссылка1,ссылка2,ссылка3)" и замените ссылки фактическими ссылками на ячейки, которые вы нашли ранее. Вы можете объединить многочисленные ссылки для подсчета непустых данных в отдельных разделах. В этом примере вы должны ввести «=COUNTA(A1:E100,G1:G100,F1)» и нажать «Enter», чтобы подсчитать непустые ячейки в этих диапазонах.

При необходимости включите функцию в другие расчеты. В примере, если вы подсчитали сумму в ячейке J1 и хотели получить среднее значение в ячейке K1, введите "=J1/СЧЁТЕЗ(A1:E100,G1:G100,F1)" в ячейке K1.

<р>С. Тейлор начал профессиональную писательскую карьеру в 2009 году и часто пишет о технологиях, науке, бизнесе, финансах, боевых искусствах и природе.Он пишет как для онлайн-изданий, так и для офлайн-изданий, включая Journal of Asian Martial Arts, Samsung, Radio Shack, Motley Fool, Chron, Synonym и другие. Он получил степень магистра биологии дикой природы в Университете Клемсона и степень бакалавра искусств в области биологических наук в Колледже Чарльстона. У него также есть несовершеннолетние по статистике, физике и изобразительному искусству.

Google Sheets 101. Пошаговое руководство по трем способам подсчета ячеек, ЕСЛИ НЕ пусто в Google Sheets. Мы расскажем вам, как использовать СЧЁТЕСЛИ, СЧЁТЕСЛИ и СУММПРОИЗВ для этого варианта использования, а также другие советы и рекомендации по работе с Google Таблицами.

Содержание

Метод COUNTA
Метод СУММПРОИЗВ
Метод СЧЁТЕСЛИ

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

COUNTA – это функция для подсчета непустых ячеек. Однако иногда функция СЧЁТЕСЧИТ не улавливает скрытые специальные символы, поэтому для решения этой проблемы можно использовать СУММПРОИЗВ или СЧЁТЕСЛИ.

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

Метод COUNTA

В Google Sheets функция COUNTA подсчитывает количество всех ячеек в диапазоне, содержащих значения или текстовую строку. Он игнорирует пустые ячейки при завершении подсчета.

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

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

Выберите пустую ячейку и введите функцию =COUNTA, включая диапазон ячеек, которые вы хотите подсчитать. Например, мы использовали =СЧЁТЗ(A2:A11).
Просто нажмите клавишу ВВОД, и функция СЧЕТЧИК автоматически подсчитает непустые ячейки.
Теперь у вас есть общее количество ячеек со значениями!

<Р>

Метод СУММПРОИЗВ

Функция COUNTA очень полезна для подсчета ячеек, которые пусты или не содержат данных. Однако могут быть случаи, когда COUNTA подсчитывает ячейки, которые кажутся пустыми. Это связано с тем, что технически эти ячейки не пусты, а просто имеют пустое пространство. Это происходит, например, когда вы используете условное форматирование, из-за которого оно выглядит как пустое, или прячете в нем функцию =””!

<Р>

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

Нажмите на пустую ячейку и введите функцию =СУММПРОИЗВ(--(ДЛСТР(диапазон)>0)) для подсчета ячеек, которые не кажутся пустыми. При этом функция ДЛСТР возвращает значение больше нуля при подсчете количества символов, которые появляются на листе.
Просто нажмите Enter, и количество ячеек со значениями появится автоматически.

Метод СЧЁТЕСЛИ

Если вы читали предыдущие руководства, Google Таблицы скрывают некоторые специальные символы из-за их специальных функций: это включает апостроф, который используется для указания того, что числовые символы должны интерпретироваться как строка (мы использовали это, чтобы ввод телефонных номеров в Google Sheets). Чтобы решить эту проблему, мы узнаем, как использовать СЧЕТЕСЛИ для подсчета ячеек, которые не являются пустыми и не имеют скрытых специальных символов.

В качестве примера рассмотрим эту таблицу:

Сколько ячеек имеют записи в таблице? В этой таблице девять (9) записей. Однако, если вы примените функцию COUNTA для подсчета записей, результатом будет тринадцать (13):

Как уменьшить фотографию в фотошопе