Подсчет является неотъемлемой частью анализа данных, будь то определение численности сотрудников отдела в организации или количества единиц, проданных поквартально. В Excel есть несколько методов для подсчета ячеек, строк или столбцов данных. Чтобы помочь вам сделать лучший выбор, эта статья содержит исчерпывающее описание методов, загружаемую книгу с интерактивными примерами и ссылки на связанные темы для более глубокого понимания.
Примечание
Подсчет не следует путать с суммированием. Дополнительные сведения о суммировании значений в ячейках, столбцах и строках см. в статье Общие сведения о суммировании и подсчете данных Excel.
Скачивание образцов
Вы можете скачать образец книги с примерами для дополнения сведений, приведенных в этой статье. Большинство разделов этой статьи будут ссылаться на соответствующий лист в примере книги, который содержит примеры и дополнительные сведения.
Скачать примеры для подсчета значений в электронной таблице
В этой статье
Подсчет на основе одного или нескольких условий
- Видео: использование функций СЧЁТ, СЧЁТЕСЛИ и СЧЁТЗ
- Подсчет ячеек в диапазоне с помощью функции СЧЁТ
- Подсчет ячеек в диапазоне на основе одного условия с помощью функции СЧЁТЕСЛИ
- Подсчет ячеек в столбце на основе одного или нескольких условий с помощью функции БСЧЁТ
- Подсчет ячеек в диапазоне на основе нескольких условий с помощью функции СЧЁТЕСЛИМН
- Подсчет количества вхождений на основе условий с помощью функций СЧЁТ и ЕСЛИ
- Подсчет количества вхождений нескольких текстовых и числовых значений с помощью функций СУММ и ЕСЛИ
- Подсчет количества ячеек в столбце или строке сводной таблицы
Простой подсчет
Можно подсчитать количество значений в диапазоне или таблице с помощью простой формулы, нажатия кнопки или функции листа.
Excel также может отображать количество выбранных ячеек в строке состояния. В следующей демонстрации приведены краткие сведения об использовании строки состояния. Дополнительные сведения см. в разделе "Отображение вычислений и количеств" в строке состояния . Если вы хотите быстро просмотреть данные, но у вас нет времени на ввод формул, можно обратиться к значениям в строке состояния.
Видео: подсчет ячеек с помощью строки состояния Excel
Посмотрите следующее видео, чтобы узнать, как отобразить счетчик в строке состояния.
Использование автосуммирования
Функция автосуммирования позволяет выбрать диапазон ячеек, содержащий хотя бы одно числовое значение. Затем на вкладке "Формулы" выберите "Счетчик чисел" в автосумме>.
Excel возвращает количество числовых значений в диапазоне в ячейке, смежной с выделенным диапазоном. Как правило, этот результат отображается в ячейке справа для горизонтального диапазона или в ячейке ниже для вертикального диапазона.
Добавление строки промежуточных итогов
К данным Excel можно добавить строку промежуточных итогов. Щелкните в любом месте в области данных и выберитепункт "Структура>данных>,итоги".
Примечание
Параметр "Промежуточные.итоги" будет работать только с обычными данными Excel, но не с таблицами Excel, сводными таблицами или сводными диаграммами.
Также можно обратиться к следующим статьям:
- Структурирование (группировка) данных на листе
- Вставка промежуточных итогов в списке данных на листе
Подсчет ячеек в списке или столбце таблицы Excel с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для подсчета значений в таблице Excel или диапазоне ячеек. Если в таблице или диапазоне есть скрытые ячейки, для включения или исключения этих скрытых ячеек можно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ. В этом заключается основное различие между функциями СУММ и ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Синтаксис функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ выглядит следующим образом:
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка1;[ссылка2];…])
Чтобы включить скрытые значения в диапазон, необходимо установить для аргумента function_num значение 2.
Чтобы исключить скрытые значения из диапазона, задайте для аргумента function_num значение 102.
Подсчет на основе одного или нескольких условий
С помощью ряда функций можно подсчитать количество ячеек в диапазоне, удовлетворяющих заданным условиям (критериям).
Видео: использование функций СЧЁТ, СЧЁТЕСЛИ и СЧЁТЗ
В видеоролике ниже показано, как использовать функцию СЧЁТ, а также функции СЧЁТЕСЛИ и СЧЁТЗ для подсчета только тех ячеек, которые удовлетворяют заданным условиям.
Подсчет ячеек в диапазоне с помощью функции СЧЁТ
Чтобы подсчитать количество числовых значений в диапазоне, используйте в формуле функцию СЧЁТ.
В приведенном выше примере ячейки A2, A3 и A6 являются единственными ячейками, содержащими числовые значения в диапазоне. Следовательно, результат будет равен 3.
Примечание
A7 — это значение времени, но оно содержит текст (a.m.), поэтому функция СЧЁТ не считает его числовым значением. Если бы вы удалили a.m. от ячейки, функция СЧЁТ будет считать ячейку A7 числовым значением и изменит выходные данные на 4.
Подсчет ячеек в диапазоне на основе одного условия с помощью функции СЧЁТЕСЛИ
Функция СЧЁТЕСЛИ используется для подсчета количества появлений определенного значения в диапазоне ячеек.
Подсчет ячеек в столбце на основе одного или нескольких условий с помощью функции БСЧЁТ
Функция БСЧЁТ подсчитывает количество ячеек в поле (столбце) записей списка или базы данных, которые содержат числа, удовлетворяющие заданным условиям.
В приведенном ниже примере необходимо найти количество месяцев, включая или более поздние, чем март 2016 г., когда было продано более 400 единиц. Данные о продажах содержатся в первой таблице на листе (от A1 до B7).
Функция БСЧЁТ использует условия, определяющие, откуда должны возвращаться значения. Как правило, условия вводятся в ячейки на самом листе, а затем на эти ячейки указывается аргумент условий . В данном примере ячейки A10 и B10 содержат два условия: одно условие определяет, что возвращаемое значение должно быть больше 400, а другое — что конечный месяц должен быть не ниже 31 марта 2016 г.
Используйте следующий синтаксис:
=БСЧЁТ(A1:B7;"Окончание месяца";A9:B10)
Функция DCOUNT проверяет данные в диапазоне A1–B7, применяет условия, указанные в этих ячейках A10 и B10, и возвращает 2 — общее количество строк, удовлетворяющих обоим условиям (строки 5 и 7).
Подсчет ячеек в диапазоне на основе нескольких условий с помощью функции СЧЁТЕСЛИМН
Функция СЧЁТЕСЛИМН аналогична функции СЧЁТЕСЛИ с одним важным исключением: СЧЁТЕСЛИМН позволяет применить критерии к ячейкам в нескольких диапазонах и подсчитывает число соответствий каждому критерию. С функцией СЧЁТЕСЛИМН можно использовать до 127 пар диапазонов и критериев.
Синтаксис функции СЧЁТЕСЛИМН имеет следующий вид:
СЧЁТЕСЛИМН(диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
См. пример ниже.
Подсчет количества вхождений на основе условий с помощью функций СЧЁТ и ЕСЛИ
Предположим, вам нужно определить, сколько продавцов продали определенный товар в определенном регионе, или вы хотите узнать, сколько продаж по определенной стоимости было сделано конкретным продавцом. Функции ЕСЛИ и СЧЁТ можно использовать вместе; это означает, что сначала используется функция ЕСЛИ для проверки условия, а затем, только если функция ЕСЛИ возвращает значение ИСТИНА, используется функция СЧЁТ для подсчета ячеек.
Примечание
- Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если вы открыли эту книгу в Excel для Windows или в Excel для Mac и хотите изменить формулу или создать аналогичную формулу, нажмите клавишу F2, а затем нажмите клавиши CTRL+SHIFT+ВВОД, чтобы формула вернула ожидаемые результаты. В более ранних версиях Excel для Mac используйте
+SHIFT+ВВОД. - Чтобы эти примеры формул работали, вторым аргументом функции ЕСЛИ должно быть число.
Подсчет количества вхождений нескольких текстовых и числовых значений с помощью функций СУММ и ЕСЛИ
В следующих примерах функции ЕСЛИ и СУММ используются вместе. Функция ЕСЛИ сначала проверяет значения в определенных ячейках, а затем, если возвращается значение ИСТИНА, функция СУММ складывает значения, удовлетворяющие условию.
Пример 1
Приведенная выше функция гласит, что если ячейка C2:C7 содержит значения Бьюкенена и Додсворта, то функция СУММ должна отобразить сумму записей, в которых выполняется это условие. Формула найдет в данном диапазоне три записи для "Шашков" и одну для "Туманов" и отобразит 4.
Пример 2
Функция выше гласит, что если ячейка D2:D7 содержит значения меньше 9000 $ или больше 19 000 $, то функция СУММ должна отобразить сумму всех записей, для которых выполняется это условие. Формула найдет две записи D3 и D5 со значениями меньше 9 000 ₽, а затем D4 и D6 со значениями больше 19 000 ₽ и отобразит 4.
Пример 3
Функция выше гласит, что если у D2:D7 есть счета для Бьюкенена на сумму менее 9000 $, то функция СУММ должна отобразить сумму записей, в которых выполняется это условие. Формула найдет ячейку C6, которая соответствует условию, и отобразит 1.
Важно
Формулы в этом примере должны быть введены как формулы массива. Это означает, что сначала нужно нажать клавишу F2, а затем клавиши CTRL+SHIFT+ВВОД. В более ранних версиях Excel для Mac используйте +SHIFT+ВВОД.
Подсчет количества ячеек в столбце или строке сводной таблицы
Сводная таблица обобщает ваши данные и помогает их анализировать и детализировать, позволяя выбирать категории для просмотра данных.
Вы можете быстро создать сводную таблицу, выделив ячейку в диапазоне данных или в таблице Excel, а затем на вкладке "Вставка " в группе "Таблицы " выберите "Сводная таблица".
Рассмотрим пример сценария электронной таблицы "Продажи", где можно подсчитать, сколько значений продаж доступно для гольфа и тенниса за определенные кварталы.
Примечание
Для интерактивного взаимодействия можно выполнить эти действия на примере данных, представленных на листе сводной таблицы в доступной для скачивания книге.
Введите данные в электронную таблицу Excel.
Выделите диапазон A2:C8
Выберите "Вставить>сводную таблицу".
Выберите "Из таблицы/диапазона, создать лист" и нажмите кнопку "ОК".
Пустая сводная таблица будет создана на новом листе.
В области "Поля сводной таблицы" выполните одно из указанных ниже действий.
Перетащите элемент Спорт в область Строки.
Перетащите элемент Квартал в область Столбцы.
Перетащите элемент Продажи в область Значения.
Повторите третье действие.
Имя поля Сумма_продаж_2 отобразится и в области "Сводная таблица", и в области "Значения".
На этом этапе область "Поля сводной таблицы" будет выглядеть так:
В области значений щелкните раскрывающийся список рядом с SumofSales2 и выберите пункт "Параметры поля значений".
В диалоговом окне Параметры поля значений выполните указанные ниже действия.
На вкладке Операция выберите пункт Количество.
В поле Пользовательское имя измените имя на Количество.
Нажмите кнопку ОК.
Сводная таблица отобразит количество записей для разделов "Гольф" и "Теннис" за кварталы 3 и 4, а также показатели продаж.
Подсчет, если данные содержат пустые значения
С помощью функций можно подсчитать количество ячеек, содержащих данные или являющихся пустыми.
Подсчет непустых ячеек в диапазоне с помощью функции СЧЁТ
Функция СЧЁТЗ используется для подсчета только ячеек в диапазоне, которые содержат значения.
Иногда при подсчете ячеек удобнее пропускать пустые ячейки, поскольку смысловую нагрузку несут только ячейки со значениями. Например, требуется подсчитать общее число продавцов, совершивших продажу (столбец D).
пропускает пустые значения в ячейках D3, D4, D8 и D11 и подсчитывает только ячейки со значениями в столбце D. Функция находит шесть ячеек в столбце D, содержащих значения, и выводит 6 в качестве выходных данных.
Подсчет непустых ячеек в списке с определенными условиями с помощью функции БСЧЁТА
С помощью функции БСЧЁТА можно подсчитать количество непустых ячеек, которые удовлетворяют заданным условиям, в столбце записей в списке или базе данных.
В следующем примере функция БСЧЁТА используется для подсчета количества записей в базе данных, содержащихся в диапазоне A1:B7, удовлетворяющих условиям, указанным в диапазоне условий A9:B10. Эти условия заключаются в том, что значение идентификатора товара должно быть больше или равно 2000, а значение рейтинга должно быть больше или равно 50.
Функция БСЧЁТА находит две строки, удовлетворяющие условиям (строки 2 и 4), и выводит значение 2 в качестве выходных данных.
Подсчет пустых ячеек в смежном диапазоне с помощью функции СЧИТАТЬПУСТОТЫ
Функция COUNTBLANK возвращает количество пустых ячеек в непрерывном диапазоне (ячейки являются непрерывными, если они все соединены в непрерывной последовательности). Если ячейка содержит формулу, которая возвращает пустой текст (""), эта ячейка включается в подсчет.
Иногда требуется включить в подсчет и пустые ячейки. Ниже приведен пример электронной таблицы продаж продуктов. Предположим, требуется узнать, сколько ячеек не содержат данные о продажах.
Примечание
Функция СЧИТАТЬПУСТОТЫ обеспечивает наиболее удобный метод определения количества пустых ячеек в диапазоне, но она не очень хорошо работает, когда интересующие ячейки находятся в закрытой книге или не образуют непрерывный диапазон.
Подсчет пустых ячеек в несмежном диапазоне с помощью сочетания функций СУММ и ЕСЛИ
Используйте сочетание функций СУММ и ЕСЛИ . Как правило, для определения того, содержит ли каждая ячейка, на которую имеется ссылка, значения в формуле массива, используется функция ЕСЛИ и на которую имеется суммирование значений ЛОЖЬ.
Примеры сочетаний функций СУММ и ЕСЛИ см. в предыдущем разделе. В этой статье можно подсчитать частоту появления нескольких текстовых или числовых значений с помощью функций СУММ и ЕСЛИ .
Подсчет частоты вхождения уникальных значений
Вы можете подсчитать уникальные значения в диапазоне с помощью сводной таблицы, функции СЧЁТЕСЛИ, функций СУММ и ЕСЛИ вместе или диалогового окна расширенного фильтра .
Подсчет количества уникальных значений в столбце списка с помощью расширенного фильтра
С помощью диалогового окна Расширенный фильтр можно найти уникальные значения в столбце данных. Эти значения можно отфильтровать на месте или извлечь их и вставить в другое место. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.
Чтобы использовать расширенный фильтр, перейдите на вкладку " Данные " и в группе "Сортировка & фильтр " выберите "Дополнительно".
На рисунке ниже показано, как с помощью расширенного фильтра скопировать только уникальные записи в другое место на листе.
На следующем рисунке столбец E содержит значения, скопированные из диапазона в столбце D.
Примечание
- При фильтрации значений на месте они не удаляются с листа, просто одна или несколько строк могут быть скрыты. Чтобы снова отобразить эти значения, нажмите кнопку "Очистить " в группе "Сортировка & фильтр " на вкладке "Данные ".
- Если вам нужно только быстро узнать количество уникальных значений, выделите данные после применения расширенного фильтра (фильтрованные или скопированные данные) и взгляните на строку состояния. Значение Количество, показанное в строке состояния, должно совпадать с количеством уникальных значений.
Дополнительные сведения см. в статье "Фильтрация с помощью расширенных условий"
Подсчет количества уникальных значений в диапазоне, удовлетворяющих одному или нескольким условиям, с помощью функций ЕСЛИ, СУММ, ЧАСТОТА, ПОИСКПОЗ и ДЛСТР
Используйте функции ЕСЛИ, СУММ, ЧАСТОТА, ПОИСКПОЗ и ДЛСТР в разных сочетаниях.
Дополнительные сведения и примеры см. в разделе "Подсчет количества уникальных значений с помощью функций" статьи Подсчет уникальных значений среди дубликатов.
Особые случаи (подсчет всех ячеек, подсчет слов)
Используя разные сочетания функций, можно подсчитать количество ячеек или количество слов в диапазоне.
Подсчет общего количества ячеек в диапазоне с помощью функций ЧСТРОК и ЧИСЛСТОЛБ
Предположим, вам нужно определить размер большого листа, чтобы решить, как выполнять вычисления в книге: автоматически или вручную. Чтобы подсчитать все ячейки в диапазоне, используйте формулу перемножения возвращаемых значений с помощью функций СТРОКИ и СТОЛБЦЫ . В качестве примера см. приведенное ниже изображение.
Подсчет количества слов в диапазоне с помощью сочетания функций СУММ, ЕСЛИ, ДЛСТР, СЖПРОБЕЛЫ и ПОДСТАВИТЬ
В формуле массива можно использовать комбинацию функций СУММ,ЕСЛИ,ДЛСТР, СЖПРОБЕЛЫ и ПОДСТАВИТЬ . В следующем примере показан результат использования вложенной формулы для поиска количества слов в диапазоне из 7 ячеек (3 из которых пустые). Некоторые ячейки содержат начальные или конечные пробелы, которые функции СЖПРОБЕЛЫ и ПОДСТАВИТЬ удаляют эти пробелы перед подсчетом. См. пример ниже.
Чтобы приведенная выше формула работала правильно, необходимо сделать ее формулой массива, в противном случае формула возвращает #VALUE! . Для этого выделите ячейку с формулой, а затем в строке формул нажмите клавиши CTRL+SHIFT+ВВОД. В Excel в начале и в конце формулы добавляется фигурная скобка, таким образом делая ее формулой массива.
Дополнительные сведения о формулах массива см. в статьях "Полные сведения о формулах в Excel " и " Создание формулы массива".
Отображение вычислений и подсчетов в строке состояния
При выделении одной или нескольких ячеек информация о данных в них отображается в строке состояния Excel. Например, если на листе выделены четыре ячейки, которые содержат значения 2, 3, текстовую строку (например, "облако") и 4, то в строке состояния могут одновременно отображаться следующие значения: среднее значение, количество выделенных ячеек, количество ячеек с числовыми значениями, минимальное значение, максимальное значение и сумма. Чтобы отобразить или скрыть все или любые из этих значений, щелкните строку состояния правой кнопкой мыши. Эти значения показаны на приведенном ниже рисунке.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.