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