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


СЧЁТЕСЛИ (функция СЧЁТЕСЛИ)

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

Самая простая функция СЧЁТЕСЛИ означает следующее:

Например:

Синтаксис

СЧЁТЕСЛИ(диапазон;критерий)

Имя аргумента

Описание

диапазон    (обязательный)

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

Узнайте, как выбирать диапазоны на листе.

критерий    (обязательный)

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

Например, критерий может быть выражен как 32, ">32", В4, "яблоки" или "32".

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

Примеры

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

Данные

Данные

яблоки

32

апельсины

54

персики

75

яблоки

86

Формула

Описание

=СЧЁТЕСЛИ(A2:A5;"яблоки")

Количество ячеек, содержащих текст "яблоки" в ячейках А2–А5. Результат — 2.

=СЧЁТЕСЛИ(A2:A5;A4)

Количество ячеек, содержащих текст "персики" (значение ячейки A4) в ячейках А2–А5. Результат — 1.

=СЧЁТЕСЛИ(A2:A5;A2)+СЧЁТЕСЛИ(A2:A5;A3)

Количество ячеек, содержащих текст "яблоки" (значение ячейки A2) и "апельсины" (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.

=СЧЁТЕСЛИ(B2:B5;">55")

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

=СЧЁТЕСЛИ(B2:B5;"<>"&B4)

Количество ячеек со значением, не равным 75, в ячейках В2–В5. Знак амперсанда (&) объединяет оператор сравнения "<>" (не равно) и значение в ячейке B4, в результате чего получается формула =СЧЁТЕСЛИ(B2:B5;"<>75"). Результат — 3.

=СЧЁТЕСЛИ(B2:B5;">=32")-COUNTIF(B2:B5,"<=85")

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 1.

=СЧЁТЕСЛИ(A2:A5;"*")

Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак "*" обозначает любое количество любых символов. Результат — 4.

=СЧЁТЕСЛИ(A2:A5;"????ки")

Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами "ки", в диапазоне A2–A5. Подставочный знак "?" обозначает отдельный символ. Результат — 2.

Распространенные неполадки

Проблема

Возможная причина

Для длинных строк возвращается неправильное значение.

Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.

Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;"длинная строка"&"еще одна длинная строка").

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

Формула СЧЁТЕСЛИ получает #VALUE! при ссылке на другой лист.

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

Рекомендации

Действие

Результат

Помните о том, что функция СЧЁТЕСЛИ не учитывает регистр символов в текстовых строках.

Критерий не чувствителен к регистру. Например, строкам "яблоки" и "ЯБЛОКИ" будут соответствовать одни и те же ячейки.

Использование подстановочных знаков

Подстановочные знаки — вопросительный знак (?) и звездочка (*) — можно использовать в критериях. Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.

Например, =СЧЁТЕСЛИ(A2:A5;"яблок?") возвращает все вхождения слова "яблок" с любой буквой в конце.

Убедитесь, что данные не содержат ошибочных символов.

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

Попробуйте воспользоваться функцией ПЕЧСИМВ или функцией СЖПРОБЕЛЫ.

Для удобства используйте именованные диапазоны.

ФУНКЦИЯ СЧЁТЕСЛИ поддерживает именованные диапазоны в формуле (например, =СЧЁТЕСЛИ(> =32")-СЧЁТЕСЛИ(> 85). Именованный диапазон может располагаться на текущем листе, другом листе этой же книги или листе другой книги. Чтобы одна книга могла ссылаться на другую, они обе должны быть открыты.

Примечание: С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Вот пример подсчета количества ячеек определенного цвета с использованием VBA.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Для подсчета непустых ячеек используйте функцию СЧЁТЗ

Для подсчета ячеек на основании нескольких критериев используйте функцию СЧЁТЕСЛИМН

Просмотрите видео об использовании функции СЧЁТЕСЛИМН

Функция СУММЕСЛИ суммирует только те значения, которые соответствуют одному условию

Функция СУММЕСЛИМН суммирует только те значения, которые соответствуют нескольким условиям

Функция IFS (Microsoft 365, Excel 2016 и более поздних версий)

Для удаления начальных и конечных пробелов в ячейках используйте функцию СЖПРОБЕЛЫ

Для удаления непечатаемых символов в ячейках используйте функцию ПЕЧСИМВ

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах

Статистические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Способы подсчета количества ячеек в диапазоне с данными

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

  1. Щелкните ячейку, в которой должен выводиться результат.

  2. На вкладке Формулы щелкните Другие функции, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:

    • СЧЁТЗ: подсчитывает количество непустых ячеек.

    • СЧЁТ: подсчитывает количество ячеек, содержащих числа.

    • СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.

    • СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.

      Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.

  3. Выделите диапазон ячеек и нажмите клавишу RETURN.

  1. Щелкните ячейку, в которой должен выводиться результат.

  2. На вкладке Формулы щелкните Вставить, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:

    • СЧЁТЗ: подсчитывает количество непустых ячеек.

    • СЧЁТ: подсчитывает количество ячеек, содержащих числа.

    • СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.

    • СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.

      Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.

  3. Выделите диапазон ячеек и нажмите клавишу RETURN.

См. также

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

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

Функция СЧЁТ

Функция СЧЁТЗ

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

СЧЁТЕСЛИ (функция СЧЁТЕСЛИ)

Функция СЧЁТЕСЛИ (COUNTIF) - Справочник

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

Описание функции СЧЁТЕСЛИ

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

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

Пример:

=СЧЁТЕСЛИ(B2:B25;"Валентин")

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

Синтаксис

=СЧЁТЕСЛИ(диапазон; критерий)

Аргументы

диапазонкритерий

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

Обязательный аргумент. Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать. Например, критерий может быть выражен как 32, «>32», В4, «яблоки» или «32».
  • В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому символу, звездочка — любой последовательности символов. Для поиска самого вопросительного знака или звездочки следует ввести перед ними тильду (~).
  • Условие не чувствительно к регистру. Например, строкам «яблоки» и «ЯБЛОКИ» будут соответствовать одни и те же ячейки.

Замечания

  • Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов со строкой #ЗНАЧ!.

Пример

Видео работы функции

Подсчёт уникальных значений#ExcelWorldChamp 2-4

Дополнительные материалы

Простая формула подсчета уникальных записей диапазона
#ExcelWorldChamp 2-4 упражнения с формулами. Хитрые судьи.

выборка ячеек по цвету заливки – Zencoder

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

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

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

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

В Excel нет встроенных (готовых) инструментов для выборки подобного рода; можно отбирать ячейки только по одному условию - по значению, находящемуся в них. Поэтому решение задачи получалось только одно - через VBA (пользовательские функции).

Прекрасное и готовое решение моей задачи я нашел на сайте http://www.excel-vba.ru/. Даже не одно, а целых два решения, под разные условия. Ниже привожу последовательность шагов, которые привели меня к успеху.

Сразу скажу, что изображения были сделаны в Excel 2007. В Excel 2010 все несколько по другому, но запутаться невозможно, если что.

Режим “Разработчик” в Excel

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

Переходим в “Пуск - Параметры Excel” и находим в левом списке пункт “Надстройки”:

Выбираем в основном окне строчку “Пакет анализа - VBA” и жмем кнопочку “Перейти” в самом низу окна. Откроется еще одно окошко со списком доступных под Excel расширений (надстроек). Снова выбираем в этом списке “Пакет анализа - VBA” и соглашаемся, что хотим установить его, нажав кнопку “ОК”:

Потребуется установочный диск с Microsoft Office на нем (или же подключение к Интернет) чтобы программа получила необходимые пакеты для инсталляции. Если установка прошла успешно, то в “Ленте” появиться пункт “Разработчик” (Excel 2010). Можно перейти в него через эту панель или же с помощью сочетания клавиш Alt + F11.

Появиться окно, в котором выполняется написание кода на языке VBA, то есть фактически создаются пользовательские функции. Я писать их не буду, так как языка VBA не знаю и знать особого желания нет (все знать невозможно).

Вставка готовых функций в Excel VBA

Но есть готовые решения, которые я вставлю в виде кода с помощью меню “Insert - Module”. Просто берем отсюда код функций и вставляем в свой Excel. Затем сохраняем файл Excel с поддержкой VBA (макросов) и все готово для дальнейшей работы.

Вставленные функции появятся в списке формул таблицы:

Ниже представлен готовый код двух функций на VBA, написанных их автором Дмитрием Щербаковым. Первая функция с именем “CountByInteriorColor” выполняет подсчет количества ячеек по цвету заливки.

Вторая функция с именем “SumByInteriorColor” выполняет выборку ячеек по цвету заливки и суммирует все значения в этих ячейках.

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

  • - диапазон с ячейками для подсчета
  • - ячейка-образец с цветом заливки
  • - или учитывает скрытые ячейки; , или опущен(по умолчанию) - скрытые ячейки не подсчитываются.

Функция подсчета количества ячеек

'--------------------------------------------------------------------------------------- ' Procedure : CountByInteriorColor ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Функция подсчета ячеек на основе цвета заливки. ' Аргументы: ' rRange - диапазон с ячейками для подсчета. ' rColorCell - ячейка-образец с цветом заливки. ' bSumHide - ИСТИНА или 1 учитывает скрытые ячейки. ' ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не подсчитываются. '--------------------------------------------------------------------------------------- Function CountByInteriorColor(rRange As Range, rColorCell As Range, Optional bSumHide As Boolean = False) Dim lColor As Long, rCell As Range, lCnt As Long, vVal lColor = rColorCell.Interior.Color For Each rCell In rRange If rCell.Interior.Color = lColor Then If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then If bSumHide Then lCnt = lCnt + 1 Else lCnt = lCnt + 1 End If End If Next rCell CountByInteriorColor = lCnt End Function

Синтаксис этой функции прост:

=CountByInteriorColor(D8:AG8;$E$65)

Функция подсчета суммы ячеек

'--------------------------------------------------------------------------------------- ' Procedure : SumByInteriorColor ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Функция суммирования ячеек на основе цвета заливки. ' Аргументы: ' rRange - диапазон с ячейками для суммирования. ' rColorCell - ячейка-образец с цветом заливки. ' bSumHide - ИСТИНА или 1 учитывает скрытые ячейки. ' ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не суммируются. '--------------------------------------------------------------------------------------- Function SumByInteriorColor(rRange As Range, rColorCell As Range, Optional bSumHide As Boolean = False) Dim lColor As Long, rCell As Range, dblSum As Double, vVal lColor = rColorCell.Interior.Color For Each rCell In rRange If rCell.Interior.Color = lColor Then vVal = rCell.Value If IsNumeric(vVal) Then If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then If bSumHide Then dblSum = dblSum + vVal Else dblSum = dblSum + vVal End If End If End If Next rCell SumByInteriorColor = dblSum End Function

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

=SumByInteriorColor(D8:AG37;E63)

При вставке пользовательской функции “CountByInteriorColor” и “SumByInteriorColor” можно воспользоваться либо “Мастером функций”, либо произвести указание диапазона ячеек и ячейку-критерий вручную.

Описание рабочей формулы

Готовый пример работы функции “CountByInteriorColor” можно посмотреть на рисунке “Табель выходов с зелеными ячейками”. В нем подсчет отработанного времени производится по следующей формуле:

=((Сумма фактически отработанных часов) - (Норма часов выхода за месяц)) + ((Кол-во дней с переработкой)*4)

Фактически эта формула получается такой (смотри строку №13 на рисунке):

=(Ah23-AI13) + (CountByInteriorColor(D13:AG13;$E$65)*4)

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


vbaexcel

Как сделать счетесли в excel?

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

Синтаксис и особенности функции

Сначала рассмотрим аргументы функции:

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

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

В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.

Рекомендации для правильной работы функции:

  • Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
  • Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
  • Функция не учитывает регистр текстовых значений.
  • При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
  • Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.

Функция СЧЕТЕСЛИ в Excel: примеры

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

У нас есть такая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:

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

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

Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:

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

Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).

Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:

Формула посчитала «кровати» и «банкетки».

Используем в функции СЧЕТЕСЛИ условие поиска «не равно».

Формула: =СЧЁТЕСЛИ(A1:A11;»»&»стулья»). Оператор «» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».

При применении ссылки формула будет выглядеть так:

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

  1. Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».
  2. Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» — на базе критерия в ячейке А2.
  3. Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).
  4. Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.
  5. Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

Посчитаем количество реализованных товаров по группам.

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

Скачать примеры функции СЧЕТЕСЛИ в Excel

Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.

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

Описание

СЧЕТЕСЛИ можно отнести к группе критериев. Это вычисления, которые можно получить по заранее заданным условиям. К подобным инструментам также относятся СЧЕТЕСЛИМН, СУММЕСЛИ, СУММЕСЛИМН.

Оператор СЧЕТЕСЛИ в «Эксель» применяется для поиска значений, удовлетворяющих заданным условиям. Рассмотрим детально синтаксис. Функция записывается в ячейке в виде =СЧЕТЕСЛИ(диапазон;критерий)

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

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

В качестве критерия могут использоваться текст на русском или английском, числа, знаки < и >, даты, куски слов и т. д.

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

Как пользоваться?

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

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

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

  1. Зайти во вкладку «Формулы» и нажать на кнопку «Вставить функцию».
  1. После этого воспользоваться поиском (1) или выбрать группу «Статистические» и найти нужный оператор в списке (2).

Или так:

  1. Снова откройте раздел «Формулы» и нажмите на кнопку «Другие функции».
  1. В открывшемся списке выберите «Статистические» и найдите нужный инструмент в списке.

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

Как посчитать значения с одним критерием

Начнем с простых операций – поиск определенных слов/чисел по одному заданному условию. Считать будем на примере простой таблицы:

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

  1. Встаем в нужную ячейку и нажимаем отмеченную на скриншоте кнопку.
  1. Находим СЧЕТЕСЛИ (можно написать в поиске для удобства).
  1. Теперь задаем диапазон. Для этого мышкой выделяем промежуток с количеством единиц мебели.
  1. Задаем критерий. Вписываем то значение, которое необходимо найти в выбранном массиве.
  1. Нажмите кнопку «ОК» для начала вычисления. В выбранной клетке появится результат. В примере мало строк, поэтому его можно проверить вручную и убедиться в правильности выполнения.

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

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

  1. Снова выберите ячейку, в которой должен появиться конечный результат, и перейдите в верхнюю строку.
  1. Вписываем команду «=СЧЕТЕСЛИ» и открываем скобку.
  1. Выделяем диапазон столбца с названиями позиций.
  1. Теперь ставим знак «;» и пишем «торшеры». Между диапазоном и критерием пробел не ставится.
  1. Закрываем скобку и жмем Enter для выполнения операции. В выбранной клетке появится необходимое решение. Поскольку значений, опять же, не так много, результат можно проверить вручную.

Поиск по части слова или букве

Инструмент позволяет подсчитать количество клеток, которые начинаются или заканчиваются на определенную букву. Также поиск можно осуществить по части слова. Рассмотрим следующий пример:

  1. Найдем количество позиций, которые начинаются на букву «Т». Для этого вновь выбираем отдельную ячейку и начинаем писать «СЧЕТЕСЛИ».
  1. Выбираем диапазон с названиями продукции.
  1. Далее вписываем букву «Т» и ставим знак «*» (звездочка). Если поставить его после вписанного знака, то буква будет считаться начальной. Если поставить звездочку перед буквой, то оператор проведет поиск по последним символам слов.
  1. Жмем Enter и смотрим на результат.

Поиск исключений

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

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

Поиск по нескольким критериям

Зачастую пользователям требуется провести поиск по нескольким названиям или условиям сразу. В этом случае вы можете объединить несколько формул в одну с помощью знака «+». Посчитаем, сколько позиций столов в количестве более 140 встречается в таблице:

  1. Вводим синтаксис в необходимую клетку и выделяем диапазон. Вписываем «столы» и закрываем оператор.
  1. Далее ставим знак «+» и пишем новую формулу, в которой используем те же значения списка, но вместо критерия пишем «>140».
  1. Смотрим результат и сверяем.

Несколько полезных советов:

  • если ваша формула отсылается на ячейки из другой базы данных, то в момент вычисления она должна быть открыта в Excel;
  • при поиске текста не учитывается регистр символов;
  • чтобы программа искала сами знаки >,

    Очень часто при работе в Excel требуется подсчитать количество ячеек на рабочем листе. Это могут быть пустые или заполненные ячейки, содержащие только числовые значения, а в некоторых случаях, их содержимое должно отвечать определенным критериям. В этом уроке мы подробно разберем две основные функции Excel для подсчета данных – СЧЕТ и СЧЕТЕСЛИ, а также познакомимся с менее популярными – СЧЕТЗ, СЧИТАТЬПУСТОТЫ и СЧЕТЕСЛИМН.

    СЧЕТ()

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

    В следующем примере в двух ячейках диапазона содержится текст. Как видите, функция СЧЕТ их игнорирует.

    А вот ячейки, содержащие значения даты и времени, учитываются:

    Функция СЧЕТ может подсчитывать количество ячеек сразу в нескольких несмежных диапазонах:

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

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

    СЧЕТЕСЛИ()

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

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

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

    Логическое условие функции СЧЕТЕСЛИ может содержать групповые символы: * (звездочку) и ? (вопросительный знак). Звездочка обозначает любое количество произвольных символов, а вопросительный знак – один произвольный символ.

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

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

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

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

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

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

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

    Оцените качество статьи. Нам важно ваше мнение:

Как суммировать данные с разных листов книг Excel — Трюки и приемы в Microsoft Excel

Формулы могут работать с ячейками из других листов. Вам просто нужно указать перед ссылкой на ячейку имя листа и восклицательный знак. Например, следующая формула добавляет 12 к значению в ячейке С1, которая находится на листе Лист2: =Лист2!С1+12.

Что делать, если вам нужно вычислить сумму всех значений в С1, начиная с листа Лист2 и заканчивая листом Лист6? Следующая формула решает эту задачу: =СУММ(Лист2:Лист6!C1). В данном случае двоеточие разделяет название первого и последнего листов.

Для создания такой формулы выполните приведенные ниже действия.

  1. Активизируйте ячейку, которая будет содержать формулу, и наберите =СУММ(.
  2. Щелкните на вкладке первого листа (в нашем случае это Лист2) и выберите ячейку С1.
  3. Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Лист6).
  4. Нажмите Enter, и формула будет введена в ячейку.

В шаге 2 вы можете выбрать диапазон, а не одну-единствениую ячейку. Например, эта формула возвращает сумму C1:F12 для всех листов от Лист2 до Лист6: =СУММ(Лист2:Лист6!С1:Р12).

Теперь я покажу вам интересный прием, который узнал при прочтении новостных конференций Excel и на обучение в Минске. Если вы хотите просуммировать одну и ту же ячейку со всех листов, кроме текущего, просто введите формулу наподобие этой: =СУММ('*'!C1). Звездочка служит подстановочным символом, который интерпретируется как «все листы, кроме этого одного». Когда вы нажмете клавишу Enter после ввода этой формулы, Excel преобразует формулу, чтобы она использовала фактические имена листов. Это работает, даже если активный лист находится где-нибудь в середине, между другими листами. Например, если книга состоит из шести листов и вы введете приведенную выше формулу в ячейку листа Лист3, Excel создаст следующую формулу: =СУММ(Лист1:Лист2!С1;Лист4:Лист6!C1).

Но это еще не все. Введите следующую формулу для нахождения суммы ячеек С1 во всех листах, которые начинаются со слова Регион: =СУММ('Регион*"!C1). Excel может преобразовать данную формулу во что-то наподобие этого: =СУММ(Регион1:Регион4!C1).

Вы также можете использовать подстановочный знак ? — он указывает на любой отдельный символ. Например, при вводе следующей формулы Excel создаст формулу, которая просуммирует значения, начиная с листа Лист1 и заканчивая листом Лист9 (названия, которые содержат одну цифру): =СУММ(‘Лист?’!C1). Этот прием не ограничивается функцией СУММ. Он работает и с другими функциями, такими как СРЗНАЧ, МИН, МАКС.

Подсчет имен в Excel | Как считать имена в Excel? (С примерами)

Подсчет имен в Excel (Содержание)

  • Обзор имен имен в Excel
  • Как считать имена в Excel?

Обзор имен имен в Excel

COUNT - это встроенная функция в MS Excel, которая будет подсчитывать количество ячеек, содержащих числа в ячейке. Он относится к категории статистической функции и используется для возврата целого числа в качестве выходных данных. Существует множество способов подсчета ячеек в заданном диапазоне по нескольким критериям пользователя. Например, COUNTIF, DCOUNT, COUNTA и т. Д.

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

Есть несколько конкретных встроенных функций Count, которые перечислены ниже:

  • COUNT : будет подсчитано количество ячеек, содержащих числа.
  • COUNTIF : будет подсчитывать количество ячеек, содержащих числа, и должно соответствовать критериям пользователя.
  • DCOUNT : будет подсчитывать ячейки, которые содержат некоторое количество в выбранной базе данных и которые удовлетворяют пользовательским критериям.
  • DCOUNTA : будет подсчитывать непустые ячейки в выбранной базе данных, которые удовлетворяют пользовательским критериям.

Как считать имена в Excel?

Подсчет имен в Excel очень прост и легок. Итак, вот несколько примеров, которые могут помочь вам подсчитать имена в Excel.

Вы можете скачать этот шаблон Excel Count Names здесь - Шаблон Excel Count Names
Пример № 1 - Имя счетчика с данными о возрасте

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

Давайте посмотрим, как мы можем сделать это с помощью функции Count.

Шаг 1: Откройте MS Excel из меню Пуск >> Перейдите на Лист1, где пользователь сохранил данные.

Шаг 2: Теперь создайте заголовки для имени счета, где пользователь хочет, чтобы имя счета имело данные о возрасте.

Шаг 3: Теперь вычислите количество имен в данных с помощью функции Count >> используйте знак равенства для вычисления >> Write в ячейке D2 и используйте COUNT >> «= COUNT («

Шаг 4: Теперь будет запрошено значение 1, указанное в ячейке B2 - B10 >> выберите ячейку B2 - B10 >> «= COUNT (B2: A10)»

Шаг 5: Теперь нажмите на клавишу ввода.

Краткое изложение примера № 1: поскольку пользователь хочет вычислить количество имен, для которых в таблице указаны данные о возрасте. Итак, в приведенном выше примере есть 6 имен, у которых есть данные о возрасте в таблице.

Пример # 2 - Имя счетчика, которое имеет некоторую общую строку

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

Шаг 1: Откройте MS Excel из меню «Пуск» >> Перейдите на Sheet2, где пользователь сохранил данные.

Шаг 2: Теперь создайте заголовок для имени Count, в имени которого есть строка «Jr.».

Шаг 3: Теперь вычислите количество имен в данных с помощью функции COUNTIF >> используйте знак равенства для вычисления >> Запись в ячейку D2 и используйте COUNTIF >> «= COUNTIF («

Шаг 4: Теперь он запросит значение1, которое указано в ячейке A2 - A10 >> выберите ячейку A2 - A10 >> «= COUNTIF (A2: A10, »

Шаг 5: Теперь он запросит критерии, по которым нужно искать только строку «Jr.» в имени >>, поэтому напишите в ячейке D2 >> «= COUNTIF (A2: A10, « * Jr. »)»

Шаг 6: Теперь нажмите клавишу Enter.

Краткое изложение примера № 2: поскольку пользователь хочет сосчитать имя, для которого в таблице есть строка «Jr.», общая для его имени. Итак, в приведенном выше примере есть 3 имени, в имени которых есть строка «младший».

Пример № 3 - количество букв с окончанием определенной строки

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

Шаг 1: Откройте MS Excel из меню «Пуск» >> Перейдите к Sheet3, где пользователь сохранил данные.

Шаг 2: Теперь создайте заголовок для имени Count, в котором есть 5 букв и строка «ry», общая для их имени.

Шаг 3: Теперь вычислите количество имен в данных с помощью функции COUNTIF >> используйте знак равенства для вычисления >> Запись в ячейку E2 и используйте COUNTIF >> «= COUNTIF («

Шаг 4: Теперь он запросит значение1, которое указано в ячейке A2 - A10 >> выберите ячейку A2 - A10 >> «= COUNTIF (A2: A10, »

Шаг 5: Теперь он запросит критерии, по которым нужно искать только строку «ry» в имени с 5 буквами >>, поэтому пишите в ячейку E2 >> «= COUNTIF (A2: A10, « ??? ry ») »

Шаг 6: Теперь нажмите клавишу Enter.

Краткое изложение примера № 3: Поскольку пользователь хочет подсчитать имена, которые имеют 5 букв в имени и строку «ry», общие для их имени в таблице. Итак, в приведенном выше примере есть 2 имени, в имени которых есть строка «ry» с пятью буквами.

Что нужно помнить о количестве имен в Excel

  • Функция Count относится к категории статистических функций и используется для возврата целого числа в качестве выходных данных.
  • Если ячейка, содержащая какое-либо значение, которое не является числовым, например, текст или # NA, то она не будет учитываться функцией count.
  • Звездочка (*) используется для соответствия любому набору символов в критериях COUNTIF.
  • Знак вопроса (?) Используется в качестве символа подстановки для соответствия любому отдельному символу в критериях функции.
  • В критериях пользователь может использовать больше, чем «>», меньше, чем «= 10»), здесь в качестве выходных данных он будет возвращать счетчик ячейки, значение которой больше или равно 10.

Рекомендуемые статьи

Это руководство по подсчету имен в Excel. Здесь мы обсуждаем, как считать имена в Excel вместе с примерами и загружаемым шаблоном Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше -

  1. Как добавить ячейки в Excel
  2. Кнопка вставки Excel
  3. Оценить формулу в Excel
  4. ГОД Формула в Excel

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

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

Примечания: Формулы в этом примере необходимо вводить как формулы массива.

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

  • Если эта книга была открыта в Excel для Windows или Excel 2016 для Mac и более поздних версиях и вы хотите изменить формулу или создать аналогичную, нажмите F2, , а затем нажмите Ctrl + Shift + Enter, для формулы чтобы вернуть ожидаемые результаты. В более ранних версиях Excel для Mac используйте клавиши + Shift + Enter .

Пример 1

Приведенные выше функции сообщают, содержат ли ячейки C2:C7 значения Михальски и Дудек , тогда функция СУММ должна вывести сумму записей, для которых условие истинно.Формула находит три записи для фамилии Михальски и одну для фамилии Дудек в заданном диапазоне и выводит число 4 .

Пример 2

Приведенная выше функция сообщает вам, содержит ли диапазон D2:D7 значения меньше 9000 долларов или больше 19000 долларов, тогда функция СУММ должна вывести сумму всех тех записей, для которых условие истинно. Формула находит две записи, D3 и D5, со значениями меньше 9000 долларов, затем записывает D4 и D6 со значениями больше 19000 долларов и отображает 4 .

Пример 3

Приведенные выше функции показывают, есть ли у функций D2: D7 счета-фактуры на сумму менее 9000 долларов США для cies. Функция SUM должна отображать сумму записей, для которых условие истинно. Формула определяет, что запись C6 соответствует указанному условию, и отображает результат 1 .

.

Способы подсчета значений на листе

Скачать наши примеры

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

Загрузите примеры для подсчета значений в вашей электронной таблице

Простой счет

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

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

Видео

: подсчет ячеек с помощью строки состояния Excel

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

Использование автосуммы

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

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

Начало страницы

Добавить строку промежуточного итога

Вы можете добавить строку промежуточного итога к своим данным Excel. Щелкните в любом месте данных, затем щелкните Данные > Промежуточный итог .

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

См. также следующие статьи:

Начало страницы

Подсчет ячеек в списке или столбце таблицы Excel с помощью функции СУММ.ЧАСТИЧНЫЙ

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

Синтаксис ПРОМЕЖУТОЧНЫХ ИТОГОВ выглядит следующим образом:

ПРОМЕЖУТОЧНЫЙ ИТОГ (номер_функции; инв1, [инв2] ;...)

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

Чтобы исключить скрытые значения из диапазона, установите номер_функции на 102 .

Начало страницы

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

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

Видеоклип: Использование СЧЁТЕСЛИ, СЧЁТЕСЛИ и СЧЁТЕСЛИ

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

Начало страницы

Подсчет ячеек в диапазоне с помощью функции COUNT.НОМЕР

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

В приведенном выше примере ячейки A2, A3 и A6 являются единственными ячейками, которые содержат числовые значения в диапазоне, поэтому результат равен 3.

Примечание: A7 является значением времени, но содержит текст (мм), , и число не считается числовым значением.Если бы надо было удалить м.м. в ячейке, count будет рассматривать значение A7 как числовое значение и изменит результат на 4.

Начало страницы

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

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

Начало страницы

Подсчет ячеек в столбце на основе одного или нескольких условий с помощью функции DB.ILE.RECORD

Функция ССЧИСЛ подсчитывает ячейки, содержащие числа в поле (столбце) списка или записей базы данных, которые соответствуют условиям Пользователя.

В приведенном ниже примере проверьте количество месяцев, включая месяцы после марта 2016 года.в которых было продано более 400 единиц. Первая таблица рабочего листа с A1 по B7 содержит данные о продажах.

Условия используются для определения того, откуда возвращать значения. Условия обычно вводятся в ячейки самого рабочего листа, а затем вам нужно обратиться к этим ячейкам в аргументе критериев. В этом примере ячейки A10 и B10 содержат два условия: одно — возвращаемое значение должно быть больше 400, а другое — конечный месяц должен быть 31 марта 2016 г.или более 31 марта 2016 г.

Используйте следующий синтаксис:

= СЧЕТ (A1: B7; "Конец месяца"; A9: B10)

Функция DCOUNT проверяет данные в диапазоне от A1 до B7, применяет условия, указанные в диапазонах A10 и B10, и возвращает значение 2,, общее количество строк, удовлетворяющих обоим условиям (строки 5 и 7).

Начало страницы

Подсчет ячеек в диапазоне на основе нескольких условий с помощью функции COUNT.УСЛОВИЯ 900 15

Функция СЧЁТЕСЛИ аналогична функции СЧЁТЕСЛИ с одним важным отличием: функция СЧЁТЕСЛИ позволяет применять критерии к ячейкам в нескольких диапазонах и подсчитывать количество совпадений всех критериев. Функция СЧЁТЕСЛИМН позволяет использовать до 127 пар условие/критерий.

Синтаксис функции СЧЁТЕСЛИМН следующий:

COUNTIFS (диапазон_критерия1, диапазон_критерия1, [диапазон_критерия2, критерий2], ...)

См. следующий пример:

90 152

Начало страницы

Подсчет при использовании функции СЧЕТЧИК.ЧИСЛО и ЕСЛИ на основе

критериев

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

Примечания:

  • Формулы в этом примере должны быть введены как формулы массива.Если эта книга была открыта в Excel для Windows или Excel 2016 для Mac и вы хотите изменить формулу или создать аналогичную, нажмите F2, а затем нажмите Ctrl + Shift + Enter, чтобы формула вернула нужные результаты. В более ранних версиях Excel для Mac используйте клавиши + Shift + Enter.

  • Чтобы примеры формул работали, вторым аргументом функции ЕСЛИ должно быть число.

Начало страницы

Подсчитайте, сколько раз различаются текстовые или числовые значения с помощью функций СУММ и ЕСЛИ

В следующих примерах функции ЕСЛИ и ИТОГО используются одновременно. Сначала функция ЕСЛИ проверяет значения в некоторых ячейках, и если условие выполняется, функция СУММ складывает значения, удовлетворяющие условиям.

Пример 1

Приведенные выше функции сообщают, содержат ли ячейки С2:С7 значения Михальского и Дудека , тогда функция СУММ должна вывести сумму записей, для которых условие истинно. Формула находит три записи для фамилии Михальски и одну для фамилии Дудек в заданном диапазоне и выводит число 4 .

Пример 2

Приведенная выше функция показывает, содержит ли диапазон D2:D7 значения меньше 9000 долларов или больше 19000 долларов, тогда функция СУММ должна вывести сумму всех тех записей, для которых условие истинно.Формула находит две записи, D3 и D5, со значениями меньше 9000 долларов, затем записывает D4 и D6 со значениями больше 19000 долларов и отображает 4 .

Пример 3

Приведенные выше функции показывают, есть ли у функций D2: D7 счета-фактуры на сумму менее 9000 долларов США для cies. Функция SUM должна отображать сумму записей, для которых условие истинно. Формула определяет, что запись C6 соответствует указанному условию, и отображает результат 1 .

Важно: Формулы в этом примере необходимо вводить как формулы массива. Это означает, что вы должны нажать F2 , а затем Ctrl + Shift + Enter . В более ранних версиях Excel для Mac используйте + Shift + Enter .

Дополнительные советы см. в следующих статьях базы знаний:

Начало страницы

Подсчет ячеек в столбце или строке сводной таблицы

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

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

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

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

  1. Введите следующие данные в электронную таблицу Excel.

  2. Выберите диапазон A2: C8.

  3. Щелкните Вставить > Сводная таблица .

  4. В диалоговом окне «Создать сводную таблицу» нажмите «Выбрать таблицу или диапазон» , нажмите «Новый лист» , а затем нажмите «ОК» .

    На новом листе будет создана пустая сводная таблица.

  5. В области полей сводной таблицы выполните следующие действия:

    1. Перетащите Спорт на Ряды .

    2. Перетащите квартал в столбцы .

    3. Перетащите Продажи в область Значения .

    4. Повторите шаг C.

      Имя поля будет отображаться как TotalSales2 как в сводной таблице, так и в области значений.

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

      90 320
    5. В разделе Значения щелкните раскрывающийся список рядом с SalesTotal2 и выберите Настройки поля значений .

    6. В диалоговом окне Value Field Settings, , выполните следующие действия:

      1. В разделе , Суммировать поле Значение по , выберите Счетчик .

      2. В поле Custom Name измените имя на Counter .

      3. Нажмите OK .

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

Начало страницы

Подсчет, когда данные содержат пробел

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

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

Функция COUNTA позволяет подсчитывать только те ячейки в диапазоне, которые содержат значения.

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

90 380

Игнорирует пустых значения в ячейках D3, D4, D8 и D11 и подсчитывает только ячейки, содержащие значения в столбце D. Функция находит шесть ячеек в столбце D, содержащих значения, и отображает результат 6.

Начало страницы

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

Использование функции BD.КОЛИЧЕСТВО ЗАПИСЕЙSA, вы можете подсчитать непустые ячейки в столбце записей в списке или базе данных, которые соответствуют заданным вами условиям.

В следующем примере функция RECORDS.A используется для подсчета записей в базе данных в диапазоне A1: B7, которые соответствуют условиям, указанным в диапазоне критериев A9: B10. Эти условия таковы, что значение идентификатора продукта должно быть больше или равно 2000, а значение рейтинга должно быть больше или равно 50.

DCOUNTA находит две совпадающие строки, строки 2 и 4, и отображает результат 2 .

Начало страницы

Подсчет пустых ячеек в непрерывном диапазоне с помощью функции СЧИТАТЬПУСТОТЫ

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

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

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

Начало страницы

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

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

См. некоторые примеры комбинаций функций СУММ и ЕСЛИ в предыдущем разделе. Подсчитайте, как часто встречаются разные текстовые или числовые значения при совместном использовании функций СУММ и ЕСЛИ в этом разделе.

Начало страницы

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

Вы можете подсчитать уникальные значения в диапазоне с помощью сводной таблицы, функция COUNT.Диалоговое окно ЕСЛИ, СУММ и ЕСЛИ Фильтр Дополнительно.

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

Используя диалоговое окно Advanced Filter , вы можете найти уникальные значения в столбце данных. Вы можете отфильтровать значения на месте или извлечь и вставить их в новое место. Затем вы можете подсчитать элементы в новом диапазоне, используя функцию COUNT.РЯДЫ .

Чтобы использовать расширенный фильтр, щелкните вкладку Данные и в группе Сортировка и фильтр щелкните Расширенный .

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

На следующем рисунке столбец E содержит значения, скопированные из диапазона в столбце D.

Примечания:

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

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

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

Начало страницы

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

Используйте различные комбинации функций ЕСЛИ , ИТОГО , ЧАСТОТА , ПОИСКПОЗ и ДЛИНА .

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

Начало страницы

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

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

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

Предположим, вам нужно определить размер большого рабочего листа, чтобы решить, следует ли использовать ручные или автоматические вычисления в рабочей книге. Чтобы подсчитать все ячейки в диапазоне, используйте формулу, которая умножает значения, возвращаемые функциями ROWS и NUMBER COLUMN. Для примера смотрите изображение ниже:

90 507

Начало страницы

Подсчет слов в диапазоне с использованием комбинации функций SUM, IF, LEN, TRIM и SUBS

Вы можете использовать комбинацию SUM, IF, LEN, TRIM и SUBS в формуле массива.В следующем примере показан результат использования вложенной формулы для поиска количества слов в диапазоне из 7 ячеек (3 из которых пусты). Некоторые ячейки содержат начальные или конечные пробелы — функции DELETE, и BASES удаляют эти лишние пробелы перед подсчетом. См. пример ниже:

Эта формула массива должна быть введена для правильной работы приведенных выше формул. В противном случае формула возвращает ошибку #ЗНАЧ! . Для этого щелкните ячейку с формулой, а затем в строке формул нажмите Ctrl+Shift+Enter.Excel добавляет фигурные скобки в начале и в конце формулы, превращая формулу в формулу массива.

Дополнительные сведения о формулах массива см. в разделах Обзор формул в Excel и Создание формулы массива.

Начало страницы

Отображение результатов расчета и подсчета в строке состояния

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

Начало страницы

.

Как подсчитать ячейки, соответствующие критерию COUNTIF

В этом уроке я покажу вам, например, как подсчитать количество вхождений/количество строк, содержащих определенные слова, числовые значения (больше или меньше) или другие символы.

Для этого я буду использовать формулу/функцию = СЧЁТЕСЛИ

Принцип работы функции следующий = СЧЁТЕСЛИ (диапазон ячеек, "какую формулу найти")

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

В ячейку Е2 ввожу формулу в виде =СЧЕТЕСЛИ(С2:С14;"Женщина") , далее нажимаем Вводим и получаем значение/количество женщин в ячейке, в данном случае это число 6 .

Рис. 1. Подсчет количества вхождений

Теперь посчитаем количество мужчин в таблице, форма функции будет очень похожа на предыдущую формулу, будем использовать тот же диапазон ячеек С2:С14 для теста, но только сравнение изменится аргумент, который нам не нужно вводить в виде "Мужской" достаточно выделить ячейку в которой встречается данное слово, в данном конкретном случае это ячейка С4 (см. рис. 2).Вот формула: = СЧЁТЕСЛИ(С2:С14,С4) .

Рис. 2. Подсчет количества ячеек с аргументом

Четвертая колонка рабочего листа содержит возраст людей, теперь мы посчитаем, сколько людей моложе 30 лет (см. рис. 3).
Вот наша формула = СЧЁТЕСЛИ(D2:D14; ".

Рис. 3. Подсчет количества, если число меньше

Если, например, мы хотим посчитать количество мужчин и женщин в одной ячейке (используем более одного аргумента), формулу можно записать в следующем виде: = ЧИСЛО.ЕСЛИ (C2: C14; «Женщина») + СЧЁТЕСЛИ (C2: C14; «Мужчина») .

Тогда как как посчитать все ячейки в Excel с любым содержимым? Для этого можно использовать следующую формулу: = СЧЕТЕСЛИ (D2: D14, "*") звездочка * Excel рассматривает как подстановочный знак (кроме чисел).

Другой пример, количество ячеек, содержащих три символа = СЧЁТЕСЛИ (A: A; "???")

Следующий пример: подсчет ячеек, содержащих только слово pay = ЧИСЛО.IF (A: A; «платить») (без учета регистра).

И еще пример, подсчет ячеек, содержащих слово зарплата = СЧЁТЕСЛИ (A: A; "* зарплата *") (без учета регистра).

И еще, возвращает количество ячеек, начинающихся с буквы В и формулы = СЧЁТЕСЛИ (A:A; "B*") (регистр не чувствителен).

07.09.2019 Adm
Ответ на @X
= СЧЁТЕСЛИМН (C: C; "женщина"; D: D; "> 30")
В этой функции можно использовать 2, 3 или более условий.

2019-07-05 X
А как считать условия "женщина" в столбце С и ">30" в столбце D?

28.03.2019 robson
Гениально, 5 минут и тема завалена!!!!

23.10.2018 из ада в рай
Спасибо, вы даже не представляете, насколько эти знания помогли вам сэкономить время :)

.

Как сделать подсчет экземпляров в Excel?

Содержимое

Количество начатых событий, что мы подразумеваем под этим. На самом деле мы попытаемся получить некоторый текст или значение в диапазоне по его положению. Например, первое появление «А» мы обозначим как 1, второе появление — 2, третье появление — 3 и так далее. Чем это полезно? Это очень полезно. мы увидим это позже в статье.

Общая формула подсчета экземпляров

 = ЧИСЛО.ЕСЛИ (раскрывающаяся ссылка, значение) 

Расширить ссылку : Это ссылка, которая расширяется при копировании. В этой ссылке первая часть заблокирована или, скажем, имеет абсолютную ссылку, а вторая часть является относительной ссылкой. Например, $A$1:A1. Чтобы узнать больше, вы можете прочитать статью по ссылке выше.
Значение: Значение, для которого вы хотите получить текущее количество вхождений. Оно должно быть относительным.
Возможно, вам надоела вся вышеизложенная теория.Давайте посмотрим пример.

Получить текущее количество вхождений Пример 1
В этом примере у нас есть список элементов. Мы должны постоянно подсчитывать появление каждого элемента.

Напишите приведенную ниже формулу на ячейке B2 и скопируйте или перетащите ее вниз на ячейку B12, чтобы получить текущее количество вхождений каждого элемента в диапазоне A2: A12 .

 = СЧЁТЕСЛИ ($ A $ 2: A2, A2) 


Вы можете видеть, что при первом появлении мёда он помечен как 1, при втором появлении - 2 и так далее.Это верно для каждого предмета.

Обратите внимание, что диапазон в C2: C12 расширяется от A2: A2 до A2: A12.

Как это работает:
Хитрость заключается в расширении области действия. Как мы знаем, функция СЧЁТЕСЛИ возвращает число заданного значения в диапазоне.
Теперь у нас есть диапазон $A2:A2 в первой ячейке C2, и мы считаем в ней A2. Поскольку в этом диапазоне есть только одна ячейка, и мы считаем только эту ячейку, очевидно, что она возвращает 1.
То же самое происходит со второй ячейкой, C3. Диапазон становится 2$A$2:A3.Считаем значение в А2 как раньше не было, опять получаем 1
Потом когда скопировали формулу вниз в ячейку С5 диапазон меняется на $A$2:A5. В этом диапазоне мы считаем значение A5. Это случалось раньше в A1, поэтому мы получаем 2. И для каждого значения в диапазоне.

Какая польза от текущего количества вхождений значений?

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


Разве это не полезно?
Вариантов использования множество, все зависит от потребностей и креативности пользователей.
Надеюсь, эта статья оказалась для вас полезной. Расскажите, как вы его используете.Если у вас есть какие-либо вопросы по этому или любому другому вопросу об Excel 2016, 2013, 2010 или любой другой версии, не стесняйтесь задавать их в разделе комментариев ниже.

Популярные статьи:

50 ярлыков Excel для повышения производительности : ускорьте свою работу. Эти 50 ярлыков ускорят вашу работу в Excel.

Как использовать t Функция Excel VLOOKUP : Это одна из наиболее часто используемых и популярных функций Excel для поиска значений из разных диапазонов и рабочих листов.

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

Как использовать функцию СУММ ЕСЛИ в Excel : Это еще одна важная функция панели инструментов. Это помогает складывать значения при определенных условиях.

Вы поможете в развитии сайта, поделившись сайтом с друзьями

.

Подсчет ячеек в MS Excel - Expose.pl

Мы можем подсчитывать ячейки на листе по-разному, в зависимости от их содержимого (или его отсутствия). Функции COUNTA, COUNT, COUNTBLANK имеют очень простой синтаксис, и цель именно такая, как они названы.

Синтаксис функции СЧЁТЗ:

СЧЕТЧИК (значение1, [значение2],…)

Синтаксис функции СЧЁТЕСЛИ:

СЧЁТ (значение1, [значение2], ...)

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

Синтаксис для функции СЧИТАТЬПУСТОТЫ:

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

Аргументом функции является диапазон поиска. Следует отметить, что в этой функции можно выбрать только один диапазон. Функция работает противоположно COUNT.BLANK, поэтому вы также должны учитывать, что ячейка, содержащая пробел, не считается пустой.

Синтаксис для СЧЁТЕСЛИ ()

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

СЧЁТЕСЛИ (диапазон, критерий)

где:

  • диапазон - равномерная область ячеек, которая будет проверяться по критериям,
  • критерии - условие, которое должно быть выполнено, чтобы ячейка была засчитана; критерии в функциях заключаются в кавычки, если они текстовые или содержат операторы сравнения; критерии не чувствительны к регистру; при их создании используйте операторы сравнения; также можно использовать подстановочные знаки для замены фрагмента текста:
    • ? - заменяет один символ,
    • * - заменяет строку (тоже одиночный символ).

Например, при поиске слов с буквой «а» на втором месте критерии должны выглядеть так: «=?А*».

Синтаксис для СЧЁТЕСЛИМН()

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

COUNTIFS (диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерии2], ...)

где:

  • критерии_диапазон1 - диапазон, в котором проверяется критерий1,
  • критерии - критерии допустимы для критерия_диапазон1.

Обратите внимание на то, что критерии идут парами - каждый критерии_диапазона_1 имеет свои собственные критерии; для правильной работы функции требуется по крайней мере одна пара аргументов.

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

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

Если вы хотите узнать об этих и других функциях Excel, ознакомьтесь с нашими курсами Excel .Мы приглашаем!

.

Условия подсчета, то есть Excel анализирует данные за нас

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

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

Рисунок 1. Образец рабочей книги

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

  • сколько товаров было закуплено компанией AERO sp.z o.o.;
  • на сколько товаров дороже средней цены товаров;
  • сколько товаров приобрели покупатели, имена которых начинаются на «А»;
  • сколько товаров было куплено после 22 мая 2014 года.
Итак, мы научимся считать ячейки в диапазоне, удовлетворяющем определенным условиям .

В первую очередь это делается с помощью функции СЧЁТЕСЛИ.

Функция СЧЁТЕСЛИ (диапазон, критерии) принимает диапазон ячеек для поиска и критерии (числа, даты, выражения), которым должны соответствовать подсчитываемые ячейки.

Также будет полезно СЧЁТЕСЛИМН (диапазон1; критерий1; диапазон2; критерий2,…) , который подсчитывает строки, для которых диапазон1 соответствует критериям диапазон2 и так далее — так что это вроде как COUNT.ЕСЛИ, но с возможностью указания разных критериев для разных диапазонов (например, столбцов). Мы также будем использовать функцию СРЗНАЧ, которая вычисляет среднее значение по заданному диапазону ячеек.

Сколько товаров купила компания AERO sp.z o.o.?

Вначале мы назовем диапазоны данных, чтобы нам не нужно было писать, например, C3: C12, , а только Покупатель . Для этого выделите диапазон ячеек A2:E2 и нажмите [CTRL] + [SHIFT] + [СТРЕЛКА ВНИЗ].

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

Теперь на вкладке Формулы в группе Определенные имена находим кнопку Создать из выделения и нажимаем ее. Отметим только позицию . Верхний ряд . Теперь, нажав на кнопку Name Manager (слева от Create from Selection ) мы увидим, что мы назвали диапазоны каждого столбца именно так, как звучат их заголовки.

Рисунок 2. Именованные столбцы в нашей рабочей тетради

Итак, чтобы посчитать, сколько товаров AERO sp.z o.o., нам нужно подсчитать ячейки Покупатель (т.е. C3: C12 ) в диапазоне ячеек C3: C12 ), которые имеют значение, такое как, например, ячейка C3 (т.е. AERO sp.z o.o. ). Окончательная формула выглядит следующим образом: = СЧЁТЕСЛИ (Покупатель; C3) .

Рисунок 3. Формула и ее результат: AERO sp.z o.o. купили 3 товара

Сколько товаров было дороже средней цены товаров?

Сначала нам нужно рассчитать среднюю цену товара. Сделаем это с помощью функции СРЗНАЧ ( диапазон ), что в нашем случае: = СРЗНАЧ (Цена) .

Теперь по формуле СЧЁТЕСЛИ посчитаем в диапазоне ячеек Цена (то есть те же ячейки Е3:Е12 ) только те ячейки, значение которых превышает среднюю цену. Мы будем использовать формулу = СЧЁТЕСЛИ (Цена; ">" и E14) . Чтобы сослаться на другую ячейку в критерии, поставьте перед ней символ &.

Рисунок 4. Средняя цена товара и количество товара выше этой цены (таких товаров три: один по цене 2500 и два по цене 1700).

Сколько товаров с названиями, начинающимися на "А", было куплено?

Для ответа на этот вопрос воспользуемся так называемым подстановочный знак (*). Такая звездочка представляет собой строку любых символов любой длины. Так что если мы зададим в Excel формулу = СЧЁТЕСЛИ (Покупатель; «А*»), , ответ будет 5 — потому что очень многие покупатели начинают с буквы А. Размер буквы не имеет значения.

Сколько товаров было куплено после 22 мая 2014 г.?

НОМЕРIF также может использовать дату в качестве критерия и находить даты позже или раньше, используя символы <и>.

Более поздняя дата считается большей, чем более ранняя дата, а более ранняя дата считается меньшей, чем более поздняя дата. Таким образом, формула = СЧЁТЕСЛИ ("Дата продажи"; "> 22.05.2014") будет подсчитывать только ячейки, дата продажи которых приходится на 23 мая 2014 года или позже.

.

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

В этом уроке вы научитесь считать уникальные значения в Excel с помощью формул (функции СЧЁТЕСЛИ и СУММАПРОИЗВ).

Как посчитать уникальные значения в Excel?

Предположим, у нас есть набор данных, как показано ниже:

Для целей этого руководства я буду называть диапазон A2: A10 NAMES. Мы будем использовать этот именованный диапазон в наших формулах в будущем.

 См. также: Как создавать именованные диапазоны в Excel. 

Этот набор данных повторяется в диапазоне NAMES. Чтобы получить количество уникальных имен из этого набора данных (A2: A10), мы можем использовать комбинацию функций СЧЁТЕСЛИ и СУММАПРОИЗВ, как показано ниже:

= СУММА ПРОИЗВЕДЕНИЯ (1 / СЧЁТЕСЛИ (ИМЯ, ИМЯ))

Как работает эта формула?

Давайте разберем эту формулу, чтобы лучше понять ее:

  • НОМЕР.ЕСЛИ (ИМЕНА, ИМЯ)
    • Эта часть формулы возвращает массив. В приведенном выше примере это будет {2; 2; 3; 1; 3; 1; 2; 3; 2}. Цифры здесь показывают, сколько раз значение встречается в заданном диапазоне ячеек.
      Например, имя Боба встречается в списке дважды, поэтому для Боба будет возвращено 2. Точно так же Стив встречается три раза, поэтому для Стива возвращается 3.
  • 1 / СЧЁТЕСЛИ (ИМЯ, ИМЯ)
    • Эта часть формулы вернет массив — {0,5; 0,5; 0.3333333333333333; 1; 0,3333333333333333; 1; 0,5; 0,33333333333333333; 0,5}
      Поскольку мы разделили 1 на массив, он возвращает этот массив.
      Например, первым возвращенным элементом массива является 2. Когда 1 делится на 2, возвращается 0,5.
  • СУММПРОИЗВ (1 / СЧЁТЕСЛИ (ИМЕНА, ИМЕНА))
    • СУММПРОИЗВ просто складывает все эти числа. Обратите внимание, что если Боб появляется в списке дважды, приведенный выше массив возвращает .5 везде, где в списке появляется имя Боба.Аналогичным образом, поскольку Стив появляется в списке три раза, массив возвращает .33333333 каждый раз, когда появляется имя Стив. Когда мы суммируем числа для каждого имени, оно всегда будет возвращать 1. А если мы сложим все числа, оно вернет общее количество уникальных имен в списке.

Эта формула работает нормально, пока в диапазоне нет пустых ячеек. Но если у вас есть пустые ячейки, он вернет # DIV / 0! ошибка.

Как работать с пустыми ячейками?

Во-первых, давайте разберемся, почему он возвращает ошибку, когда в диапазоне есть пустая ячейка.Предположим, у нас есть набор данных, как показано на скриншоте ниже (ячейка A3 пуста):

Теперь, если мы используем ту же формулу, что и выше, часть СЧЁТЕСЛИ возвращает массив {2; 0; 3; 1; 3; 1; 2; 3; 1}. Поскольку в ячейке A3 нет текста, ее номер возвращается как 0.

.

А так как мы делим 1 на весь массив, то возвращается #DIV/0! ошибка.

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

= ПРОДУКТСУММ ((1/ЧИСЛО.ЕСЛИ (ИМЕНА; ИМЕНА & ””)))

Одно из изменений в этой формуле является частью критерия СЧЁТЕСЛИ. Мы использовали NAMES & ”” вместо NAMES. Таким образом, формула вернет количество пустых ячеек (ранее она возвращала 0 для пустой ячейки).

ПРИМЕЧАНИЕ. Эта формула считает пустые ячейки уникальным значением и возвращает его в качестве результата.

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

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

= СУММА ПРОДУКТОВ ((ИМЯ "") / СЧЁТЕСЛИ (ИМЯ, НАЗВАНИЕ & ""))

В этой формуле мы использовали ИМЯ "" в качестве числителя вместо 1. Возвращает массивы TRUE и FALSE. Возвращает ЛОЖЬ всякий раз, когда имеется пустая ячейка. Поскольку при вычислении ИСТИНА равняется 1, а ЛОЖЬ равняется 0, пустые ячейки не учитываются, так как счетчик равен 0 (ЛОЖЬ).

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

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

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

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

= СУММА ПРОДУКТА ((ПРОЧИТАТЬ (ИМЯ) / СЧИТАТЬ (ИМЯ, ИМЯ & "")))

Все, что мы сделали, это использовали формулу ISEXT (ИМЯ) в качестве числителя. Возвращает TRUE, если ячейка содержит текст, и FALSE, если нет.Он не будет подсчитывать пустые ячейки, но будет подсчитывать ячейки с пустой строкой ("").

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

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

= СУММПРОИЗВ ((ЧИСЛО (ИМЯ)) / СЧЁТЕСЛИ (ИМЯ, ИМЯ и ""))

Здесь мы используем ISNUMBER (ИМЕНА) в качестве числителя. Возвращает TRUE, если ячейка содержит числовой тип данных, и FALSE, если нет. Он не считает пустые ячейки.

Вы поможете в развитии сайта, поделившись сайтом с друзьями

.

Смотрите также

Только новые статьи

Введите свой e-mail

Видео-курс

Blender для новичков

Ваше имя:Ваш E-Mail: