Методы выполнения подсчетов на листе

Важно :  Данная статья переведена с помощью машинного перевода, см. Отказ от ответственности. Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала.

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

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

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

В этой статье

Простой подсчет

Подсчет количества ячеек в столбце или строке с помощью встроенной команды

Подсчет количества ячеек в диапазоне с помощью функции

Подсчет количества ячеек в столбце с помощью структуры

Подсчет количества ячеек в столбце или строке с помощью сводной таблицы

Подсчет количества ячеек в списке или столбце таблицы Excel с помощью функции

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

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

Подсчет количества ячеек в столбце с учетом одного или нескольких условий

Подсчет количества ячеек в диапазоне с учетом нескольких условий с помощью одной функции или сочетания функций

Подсчет данных при содержатся пустые значения

Подсчет непустых ячеек в диапазоне с помощью функции

Подсчет непустых ячеек в списке с помощью функции

Количество пустых ячеек в смежных с помощью функции

Подсчет пустых ячеек в диапазоне несмежных с помощью сочетания функций

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

Подсчет уникальных значений в диапазоне с помощью отчета сводной таблицы

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

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

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

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

Особые случаи (подсчет всех ячеек, подсчет слов)

Общее количество ячеек в диапазоне с помощью функций счёт

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

Отображение вычислений и счетчики в строке состояния

Простой подсчет

Количество значений в диапазоне или таблице можно подсчитать с помощью простой формулы или соответствующей функции листа.

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

Подсчет ячеек в столбце или строке в помощью встроенной команды

Выделите диапазон ячеек, в котором содержится хотя бы одно числовое значение, а затем на вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой Автосумма и выберите Число. Excel подсчитает количество числовых значений диапазона в ячейке, смежной с выделенным диапазоном. Как правило, этот результат отображается в ячейке справа от горизонтального диапазона или под вертикальным диапазоном.

Использование функции «Число» команды «Автосумма»

К началу страницы

Подсчет количества ячеек в диапазоне с помощью функции

Чтобы подсчитать количество числовых значений в диапазоне, используйте в формуле функцию СЧЁТ. В примере ниже диапазон A2:A5 содержит три числовых значения (5, 32 и 10) и одно текстовое ("привет"). Функция СЧЁТ используется в формуле такого вида: =СЧЁТ(A2:A5). Формула возвращает значение 3, соответствующее количеству числовых значений, найденных в диапазоне.

1

2

3

4

5

6

A

Пример значения

17

32

привет

10

=СЧЁТ(A2:A5)

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

К началу страницы

Подсчет количества ячеек в столбце с помощью структуры

С помощью команды Промежуточный итог (вкладка Данные, группа Структура ) можно группировать и суммировать данные в столбце.

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

Команда «Промежуточный итог» группирует данные в структуру

На рисунке выше представлены итоговые объемы продаж, сгруппированные по регионам. Для восточного и западного регионов показаны объемы продаж по каждому из четырех кварталов. Кроме того, подсчитан общий объем продаж по каждому региону и по всем регионам.

Дополнительные сведения см. в статьях:

К началу страницы

Подсчет ячеек в столбце или строке с помощью сводной таблицы

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

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

Наглядный пример сводной таблицы приведен на следующем рисунке. Обратите внимание на то, что в этой таблице содержится много строк со значениями объемов продаж (40 строк с данными), но на графике отображается только часть этих строк. Данные не суммированы и не содержат ни промежуточных итогов, ни общего итога.

Данные, используемые в отчете сводной таблицы

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

Данные, обобщенные и суммированные в отчете сводной таблицы

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

Дополнительные сведения о начале работы со сводными таблицами см. в статьях:

К началу страницы

Подсчет количества ячеек в списке или столбце таблицы Excel с помощью функции

Чтобы подсчитать количество значений в таблице или диапазоне ячеек Excel, используйте функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

С помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно определить, нужно ли включать в результаты скрытые строки. Эта функция всегда пропускает строки, которые отфильтрованы.

Например, в результате применения функции к семи значениям в следующей таблице (ячейки A2–A8) возвращено значение 7.

1

2

3

4

5

6

7

8

9

A

Единиц продано

25

8

120

32

11

40

16

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2,A2:A8)

Число 2 в формуле указывает, что нужно использовать функцию СУММ для подсчета значений в диапазоне A2:A8 и следует учитывать все скрытые строки. Результат подсчета (в ячейке A9) равен 7.

Если вы скрыли строки 4, 5 и 6 и не хотите, чтобы они учитывались при подсчете, нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ несколько иначе. Вместо числа 2 в формуле укажите 102, чтобы приложение Excel игнорировало скрытые строки. При этом лист может выглядеть следующим образом (строки 4, 5 и 6 скрыты):

1

2

3

7

8

9

A

Единиц продано

25

8

40

16

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(102,A2:A8)

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

Подробнее читайте в статьях функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ и данные итогов в таблице Excel.


К началу страницы

Подсчет с одним или несколькими условиями

С помощью функций листа можно подсчитать количество ячеек в диапазоне, которые удовлетворяют заданным условиям (так называемым критериям).

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

Чтобы подсчитать количество ячеек, удовлетворяющих одному условию (критерию), используйте функцию СЧЁТЕСЛИ. В приведенном ниже примере функция подсчитывает количество значений больше 20 в диапазоне A2:A8. Результат равен 3. Обратите внимание: условие ">20" необходимо заключить в кавычки.

1

2

3

4

5

6

7

8

9

A

Единиц продано

25

8

120

32

11

40

16

=СЧЁТЕСЛИ(A2:A8,">20")

Для получения дополнительных сведений обратитесь к статье СЧЁТЕСЛИ.

К началу страницы

Подсчет количества ячеек в столбце с учетом одного или нескольких условий

Чтобы учесть заданные условия, используйте функцию базы данных БСЧЁТ.

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

Предположим, что необходимо подсчитать количество месяцев, начиная с марта 2008 года включительно, в которых было продано более чем 400 единиц товаров. Взглянув на таблицу, мы увидим, что только два месяца удовлетворяют заданному критерию: апрель (442) и июнь (405).

Примечание : Следующий способ может показаться несколько усложненным, однако он достаточно эффективный. Важно с точностью придерживаться инструкций и упорядочить данные на листе именно так, как показано в этом примере. Можно скопировать данные из таблицы и вставить на лист в ячейку A1. Результат должен отобразиться в ячейке B13.

Добавляем дополнительный диапазон, ячейки в котором упорядочены так же, как и данные по объему продаж в диапазоне A10–B7. В дополнительный диапазон ячеек включены ячейки диапазона A10–B11 с такими же названиями столбцов («Объем продаж в единицах продукции» и «Конец месяца»), а также добавлено условие в строке непосредственно под названием каждого столбца (ячейки A11 и B11).

Введите формулу в пустой ячейке (ее расположение не имеет значения, но в данном примере формула введена в ячейке B13). В данном случае в формуле используется функция БСЧЁТ: =БСЧЁТ(A1:B7;;A10:B11).

1

2

3

4

5

6

7

8

9

10

11

12

13

A

B

Продажи в единицах

Конец месяца

339

31.01.2008

270 градусов

29.02.2008

314

31.03.2008

442

30.04.2008

336

31.05.2008

405

30.06.2008

Продажи в единицах

Конец месяца

=">400"

=">=31.03.2008"

=БСЧЁТ(A1:B7,,A10:B11)

Функция БСЧЁТ проверяет данные в диапазоне A2–A7, применяет условия из ячеек A11 и B11, а затем возвращает значение 2, соответствующее количеству строк, которые удовлетворяют обоим условиям (строки 5 и 7).

Для получения дополнительных сведений обратитесь к статье БСЧЁТ.

К началу страницы

Подсчет количества ячеек в диапазоне с учетом нескольких условий с помощью одной функции или сочетания функций

Используйте функцию СЧЁТЕСЛИМН или сочетание функций СЧЁТ и ЕСЛИ.

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

На рисунке показана функция СЧЁТЕСЛИМН, которая используется для поиска автомобилей с двигателем мощностью более 250 лошадиных сил и средним расходом топлива на трассе более 9,4 л на 100 км (25 миль на галлон). Функция возвращает значение 2, соответствующее количеству строк, удовлетворяющих обоим условиям (строки 3 и 4).

Примечание : В функции СЧЁТЕСЛИМН все условия необходимо заключать в кавычки (""), например "<250", ">25" или "240".

Чтобы подсчитать, сколько раз в диапазоне встречается отдельное значение, используйте функцию СЧЁТЕСЛИ. Например, чтобы посмотреть, сколько раз значение 70 встречается в диапазоне A2–A40, используйте формулу =СЧЁТЕСЛИ(A2:A40;70).

Подробнее читайте в статьях Подсчет количества вхождений значения и счётеслимн.


К началу страницы

Подсчет ячеек с пустыми значениями

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

Подсчет непустых ячеек в диапазоне с помощью функции

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

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

В следующем примере функция, использующаяся для поиска значений в столбце «Объем продаж в западном регионе», возвращает значение 3.

1

2

3

4

5

6

7

8

A

B

Продавец:

Продажи в западном регионе

Авдеев

24000

Аксенова

Chai

Глазков

31000

Иванова

Маслов

8000

=СЧЁТ3(B2:B7)

Поскольку ячейки B3, B4 и B6 пусты, функция СЧЁТЗ их пропускает. В подсчет включаются только ячейки, содержащие значения 24000, 31000 и 8000. Результат подсчета (в ячейке B8) равен 3.

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

К началу страницы

Подсчет непустых ячеек в списке с помощью функции

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

В примере ниже функция БСЧЁТА используется для подсчета количества записей в базе данных, содержащихся в диапазоне A4:B9 и удовлетворяющих условиям, заданным в диапазоне условий A1:B2. Согласно этим условиям значение "Код товара" должно быть больше или равно 4000, а значение "Оценки" — больше или равно 50. Обоим условиям удовлетворяет только одна запись в строке 7.

1

2

3

4

5

6

7

8

9

A

B

Код товара

Оценки

=">=4000"

=">=50"

Код товара

Оценки

2048

61

16384

35336

83

1024

113

512

47

=БСЧЁТА(A4:B9,"Оценки",A1:B2)

Для получения дополнительных сведений обратитесь к статье БСЧЁТА.

К началу страницы

Количество пустых ячеек в смежных с помощью функции

Функция СЧИТАТЬПУСТОТЫ возвращает количество пустых ячеек в смежном диапазоне (ячейки являются смежными, если они составляют непрерывную последовательность). Если ячейка содержит формулу, которая возвращает пустой текст (""), эта ячейка включается в подсчет.

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

Для получения дополнительных сведений обратитесь к статье СЧИТАТЬПУСТОТЫ.

К началу страницы

Подсчет пустых ячеек в диапазоне несмежных с помощью сочетания функций

Используйте сочетание функций СУММ и ЕСЛИ. Как правило, для этого в формуле массива с помощью функции ЕСЛИ определяется, есть ли значение в каждой указанной ячейке, а затем формула суммирует количество значений ЛОЖЬ и возвращает результат.

Дополнительные сведения см. в статье базы знаний Использование функции СУММ(ЕСЛИ()) вместо СЧИТАТЬПУСТОТЫ() в Excel.

К началу страницы

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

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

Подсчет уникальных значений в диапазоне с помощью отчета сводной таблицы

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

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

К началу страницы

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

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

В примере ниже функция СЧЁТЕСЛИ возвращает значение 2, соответствующее количеству вхождений значения 250 в диапазоне A2:A7.

1

2

3

4

5

6

7

8

A

Единиц продано

245

250

250

Н/Д

=СЧЁТЕСЛИ(A2:A7,250)

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

К началу страницы

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

Используйте функции СУММ и ЕСЛИ. Как правило, для этого в формуле массива с помощью функции ЕСЛИ определяется соответствие условию, составленному из нескольких условий, а затем формула суммирует количество значений ИСТИНА и возвращает результат.

В примере ниже функция ЕСЛИ проверяет каждую ячейку в диапазоне A2:A10 и определяет, содержит ли она значение "Авдеев" или "Зайцев". Функция СУММ подсчитывает общее количество значений ИСТИНА и возвращает значение 7. Этот пример вы можете скопировать и вставить в ячейку A1 на листе. Вставив пример, вы увидите, что ячейка A11 содержит ошибку #ЗНАЧ!. Чтобы сделать формулу рабочей, преобразуйте ее в формулу массива: нажмите клавишу F2, а затем клавиши CTRL+SHIFT+ENTER. Число 7 появится в ячейке A11.

1

2

3

4

5

6

7

8

9

10

A

B

Продавец:

Счет

Авдеев

15000

Chai

11000

Авдеев

11000

Chai

4000

Иванова

8000

Chai

6000

Иванова

14000

Авдеев

7000

12000

Формула

Описание (результат)

=СУММ(ЕСЛИ((A2:A10="Владимир")+(A2:A10="Николай"),1,0))

Количество счетов Авдеева или Зайцева (7)

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

Дополнительную информацию см. в таких статьях базы знаний:

К началу страницы

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

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

Примечания : 

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

  • Если необходимо быстро просмотреть количество уникальных значений, выделите данные после применения расширенного фильтра (отфильтрованные или скопированные данные), а затем посмотрите на строку состояния. Значение Количество в строке состояния должно равняться количеству уникальных значений.

Уникальные значения можно определить с помощью команды Дополнительно (вкладка Данные, группа Сортировка и фильтр).

На следующем рисунке показано, как с помощью расширенного фильтра можно скопировать только уникальные записи в новое место на листе.

Применение расширенного фильтра к диапазону данных

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

Уникальные значения из диапазона в столбце А

Для получения дополнительных сведений обратитесь к разделу «Подсчет количества уникальных значений с помощью фильтра» в статье Подсчет количества уникальных значений среди повторяющихся.

К началу страницы

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

Используйте функции ЕСЛИ, СУММ, ЧАСТОТА, ПОИСКПОЗ и ДЛСТР в разных сочетаниях.

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

Кроме того читайте в статье как определить количество уникальных элементов в списке.

К началу страницы

Специальные подсчеты (подсчет всех ячеек, подсчет слов)

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

Общее количество ячеек в диапазоне с помощью функций счёт

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

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

  1. Создайте пустую книгу или лист.

  2. Выделите пример в разделе справки.

    Примечание : Не выделяйте заголовки строк и столбцов.

    Выделение примера из справки

    Выделение примера из справки

  3. Нажмите клавиши CTRL+C

  4. На листе выделите ячейку A1 и нажмите клавиши CTRL+V.

  5. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (апостроф) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показать формулы.

1

2

3

4

5

6

A

B

C

Регион

Месяц

Продажи

Восточный

янв

558 000 руб.

Восточный

фев

713 000 руб.

Восточный

мар

589 000 руб.

Формула

Описание (результат)

=ЧСТРОК(A2:C4) * ЧИСЛСТОЛБ(A2:C4)

Общее количество ячеек в диапазоне (9)

К началу страницы

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

Используйте функции СУММ, ЕСЛИ, ДЛСТР и ПОДСТАВИТЬ в формуле массива. В примере ниже показан результат использования формулы для поиска количества слов в диапазоне, включающем 7 ячеек (3 из которых пусты). Некоторые ячейки содержат начальные или конечные пробелы — функции СЖПРОБЕЛЫ и ПОДСТАВИТЬ удаляют эти лишние пробелы перед подсчетом.

Из следующей таблицы скопируйте текст в диапазоне A2–A11 и вставьте в ячейку A1 вашего листа. Но сначала увеличьте ширину столбца примерно до 100 единиц.

1

2

3

4

5

6

7

8

9

10

11

A

Текстовая строка

Шесть слов и четыре конечных пробела    

   Шесть слов и три начальных пробела

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

Некоторые символы игнорируются, например запятые или точки.

Формула

=СУММ(ЕСЛИ(ДЛСТР(СЖПРОБЕЛЫ(A2:A8))=0,0,ДЛСТР(СЖПРОБЕЛЫ(A2:A8))-ДЛСТР(ПОДСТАВИТЬ(A2:A8," ",""))+1))

Вставив текст на лист в ячейку A1, выделите ячейку A11, нажмите клавишу F2, а затем комбинацию клавиш SHIFT+CTRL+ВВОД, чтобы преобразовать формулу в формулу массива. В ячейке A11 должен отобразиться правильный результат: 29.

К началу страницы

Отображение вычислений в строке состояния

При выделении одной или нескольких ячеек сведения о данных в этих ячейках отображаются в строке состояния Excel. Например, если на листе выделено четыре ячейки, которые содержат значения 2, 3, текстовую строку (например, «облако») и цифру 4, все последующие значения могут отображаться в строке состояния одновременно: «Среднее», «Количество», «Количество чисел», «Мин», «Макс» и «Сумма». Щелкните правой кнопкой мыши строку состояния, чтобы отобразить или скрыть все ее значения или только некоторые из них. Эти значения показаны на рисунке ниже.

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

Примечание : В более ранних версиях Excel эти значения также могут отображаться в строке состояния, но только по одному за раз.

К началу страницы

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

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×