Счетесли в excel — примеры функции с одним и несколькими условиями

Примеры работы функций СЧЁТ, СЧИТАТЬПУСТОТЫ и СЧЁТЕСЛИ в Excel

Количества чисел в таблице Excel можно быстро выяснить, используя функцию «Счёт». Игнорируя текстовый формат, она учитывает только числовые значения.

Где используется и как работает функция СЧЁТ?

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

Аргументом функции может быть:

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

Синтаксис функции выглядит следующим образом:

=СЧЁТ(аргумент1; аргумент2; …)

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

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

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

Пример использования функции СЧЁТ в Excel

Пример 1. В таблицу введены некоторые значения. Среди них есть текст, числовые выражения и число, заключённое в кавычки «22». Именно его и считает программа Excel текстовым значением в ячейке.

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

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

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

=СЧЁТ(12; мост; кг; крыло; 33; 45678; «22»; сила; ампер; 16.02.1999; 14.07.1975; стена; потолок; 21)

Введя формулу, мы получим значение 5.

Разные результаты функции объясняются тем, что одно число («22») воспринимается программой, как текст, если оно находится в ячейке, и как число, если оно прописано в числе аргументов функции.

Пример использования функций СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel

Вы сможете быстро определить количество заполненных или пустых клеток в заданной области таблицы. Для выполнения этих операций используются функции с именами «СЧЁТЗ» и «СЧИТАТЬПУСТОТЫ». Для наглядности этой возможности необходимо рассмотреть пример.

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

В любом месте таблицы можно ввести функцию. Для определения заполненных ячеек в диапазоне А1:С5 нужно прописать такую формулу:

Введение её через Enter даст результат 8. Именно столько заполненных ячеек есть в обозначенной области.

Чтобы узнать количество пустых клеток, нужно ввести функцию СЧИТАТЬПУСТОТЫ:

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

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

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

1. Диапазон. Табличная область, в которой будут искаться определённые элементы.
2. Критерий. Признак, который разыскивается в заданной области.

Синтаксис выглядит так:

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

Оба аргумента являются обязательными для указания. Для наглядности стоит рассмотреть следующий пример.

Пример 3. Есть ведомость с фамилиями студентов и оценками за экзамен. В таблице 2 столбца и 10 ячеек. Нужно определить, какое количество студентов получили отличную оценку 5 (по пятибалльной системе оценивания), а какое 4, потом 3 и 2.

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

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

• 5 отличников;
• 3 студента с оценкой 4 балла;
• 2 троечника;
• ни одного двоечника.

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

Дополнительная информация

Формула для подсчета количества вхождений текстовой строки в диапазоне

=SUM(LEN(range)-LEN(SUBSTITUTE(range,”text”,””)))/LEN(“text”)

Где диапазон — диапазон ячеек, а “text” заменяется конкретной текстовой строкой, которую вы хотите посчитать.

Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массива в Excel для Windows, нажмите CTRL+SHIFT+ENTER. Чтобы ввести формулу в качестве массива в Excel для Macintosh, нажмите COMMAND+RETURN.

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

Пример 1. Подсчет количества вхождений текстовой строки в диапазоне

Запустите Excel, а затем откройте новую книгу

Введите следующие сведения на Листе 1:

Значение ячейки A8 составляет 4, потому что текст “яблоко” появляется четыре раза в диапазоне.

Формула для подсчета количества происшествий одного символа в одной ячейке

=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,”a”,””))

Где cell_refссылка на ячейку, и “а” заменяется символом, который вы хотите посчитать.

Эта формула не должна быть введена в качестве массивной формулы.

Пример 2. Подсчет количества появлений символа в одной ячейке.

Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество появлений символа “p” в A7. Введите следующую формулу в ячейку A9:

Значение ячейки A9 составляет 3, потому что символ “p” появляется три раза в A7.

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

=SUM(LEN(range)-LEN(SUBSTITUTE(range,”a”,””)))

Где range — это диапазон нужной ячейки, а “а” заменяется символом, который вы хотите сосчитать.

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

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

Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество символа “p” в A2:A7. В ячейку А10 введите следующую формулу:

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

Значение ячейки A10 составляет 11, потому что символ “p” появляется 11 раз в A2:A7.

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

=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,””))+1)

Где cell_ref – это ссылка на ячейку, а char – это символ, разделяющий слова.

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

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

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

Запустите Excel, а затем откройте новую книгу

Введите следующие сведения на Листе 1:

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

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

Подсчет ячеек в Excel, используя функции СЧЕТ и СЧЕТЕСЛИ

​ ячеек сразу в​ в MS EXCEL.​ ​=СЧЁТЕСЛИ($A$2:$A$12; C4&C5)​ а также покажем​ или пустая строка​Для определения количества просмотренных​ 5, 4, 3​ С ее помощью​ вывода его на​ функции​ представленное в виде​ блоке инструментов​,​. Но, в отличие​ в которых начинаются​​ один произвольный символ.​​ нескольких несмежных диапазонах:​​Очень часто при работе​​Для ввода формулы выделите​3​ как ее использовать​​ «», результат вычисления​​ страниц пользователями default​​ балла соответственно, а​​ можно определить количество​​ экран жмем на​​СЧЁТ​

СЧЕТ()

​ ссылки или просто​​«Библиотека функций»​​#ЗНАЧ!​ от предмета нашего​ с буквы​Например, чтобы подсчитать количество​Если необходимо подсчитать количество​ в Excel требуется​ диапазон ячеек такого​Подсчитывает количество ячеек, содержащих​ для подсчета неповторяющихся​

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

​ записанное в соответствующее​жмем на кнопку​И т.д.) ситуация​

​ обсуждения, она учитывает​​А​​ ячеек, содержащих текст,​ непустых ячеек в​ подсчитать количество ячеек​

​ же размера как​ числа равных или​ значений и вычисления​ ячеек будет числовое​​ формулу:​​ не сдал предмет.​ диапазоне ячеек, а​Enter​«Значение1»​ поле. Правда, начиная​«Вставить функцию»​

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

СЧЕТЕСЛИ()

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

​ функция​ любыми данными. Оператор​К​ буквы​

​ воспользоваться статистической функцией​​ Это могут быть​ критерии. В Строке​ указан через ссылку​Синтаксис​Функция может быть использована​Функция имеет следующую синтаксическую​Предварительно выделим ячейки E2:E5,​

​ с данными, которые​​Как видим, после этих​​ с данными, а​ 2007, таких значений​​Существует ещё один вариант,​​СЧЁТ​​СЧЁТ​​:​Н​СЧЕТЗ​ пустые или заполненные​ формул введите формулу​

​ и параметр​СЧЁТЕСЛИдиапазонкритерий​ в качестве формулы​ запись:​​ введем приведенную ниже​​ совпадают с указанным​ действий итог вычислений​ в поле​

​ может быть до​ наверное, самый простой,​не учитывает в​, о котором мы​Функции Excel для подсчета​

​(без учета регистра),​​. Непустыми считаются ячейки,​​ ячейки, содержащие только​ и нажмите​Примечание​)​ массива, если требуется​=СЧЕТЕСЛИ(диапазон; критерий)​ формулу:​ критерием лишь частично.​

​ выводится на экран​«Значение2»​ 255 включительно. В​ но вместе с​​ любом виде.​​ поведем подробный разговор,​ данных очень полезны​ можно воспользоваться следующей​ содержащие текст, числовые​ числовые значения, а​

​CTRL+SHIFT+ENTER​. О подсчете значений,​Диапазон​ выполнить расчет числа​

​Описание аргументов:​​=СЧЁТЕСЛИ(B3:B19;D2:D5)​​ Например, таблица Excel​ в выбранной ячейке.​​вписываем логическое выражение​​ более ранних версиях​ тем требующий хорошей​Кроме функций​​ ведет подсчет только​​ и могут пригодиться​ формулой:​​ значения, дату, время,​​ в некоторых случаях,​, т.е. введите ее​ удовлетворяющих нескольким критериям​    — диапазон, в котором​​ ячеек с данными,​​диапазон – обязательный аргумент,​​Описание аргументов:​​ содержит столбец с​

​ Для опытных пользователей​«ИСТИНА»​ их было всего​ памяти. Выделяем ячейку​СЧЁТ​ ячеек, заполненных данными​ практически в любой​Если необходимо подсчитать количество​​ а также логические​​ их содержимое должно​​ как формулу массива.​​ читайте в статье​ нужно подсчитать ячейки,​​ удовлетворяющим сразу нескольким​​ принимающий ссылку на​​B3:B19 – диапазон ячеек​​ ФИО клиентов. Для​​ данный способ может​​. Жмем на кнопку​ 30.​ на листе и​и​

​ в числовом формате.​

office-guru.ru>

Excel COUNTIF функция

Описание

Была основана COUNTIF Функция — это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию. Он поддерживает логические операторы (<>, =,> и <), а также символы подстановки (? И *) для частичного сопоставления.

Синтаксис и аргументы

Синтаксис формулы

COUNTIF(range,criteria)

аргументы

Range: Обязательный, диапазон ячеек, который вы хотите подсчитать. Criteria: Обязательный критерий, по которому вы подсчитываете ячейки.

Возвращаемое значение

Была основана COUNTIF функция возвращает количество ячеек, удовлетворяющих указанным критериям.

Основное использование

Пример1 Подсчитать ячейки равными

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

=COUNTIF(B2:D4,2)

Or

=COUNTIF(B2:D4,G3)

Нажмите Enter Клавиша возвращает количество ячеек, равное 2.

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

Пример 2 Подсчитайте ячейки больше или меньше

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

Предположим, что существует диапазон (B2: D5) чисел, вы хотите подсчитать количество ячеек, превышающих 65, используя следующую формулу:

=COUNTIF(B2:D5,»>65″)

Or

=COUNTIF(B2:D5,»>65″)

Нажмите Enter ключ для получения результата.

Если вы хотите подсчитать ячейки меньше числа, например, подсчитать ячейки меньше 50, просто используйте формулу, подобную этой =COUNTIF(B2:D5,»<50 «)нажмите здесь, чтобы узнать больше о подсчете ячеек, которые больше или меньше заданного значения.

Расширенное использование

Пример1 Количество ячеек не равно

щелчок здесь чтобы узнать больше о формуле

Пример2 Подсчет ячеек, равных x или y

щелчок здесь чтобы узнать больше о формуле

Скачать образец файла

Относительные функции:

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

Excel COUNTBLANK функцияБыла основана COUNTBLANK функция возвращает количество пустых ячеек. Функция СЧИТАТЬПУСТОТЫ — это встроенная статистическая функция, которую можно использовать как функцию рабочего листа (WS) в Excel.

Excel COUNT функцияБыла основана COUNT Функция используется для подсчета количества ячеек, содержащих числа, или подсчета чисел в списке аргументов. Возьмите пример, COUNT(1,2,3,4,11,2,1,0) возвращает 8.

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

Относительные статьи:

Считайте ячейки равнымиЕсли вы хотите подсчитать ячейки, точно соответствующие заданному критерию, вы можете выбрать COUNTIF функция или SUMPRODUCT (в сочетании с EXACT function) функция для точного подсчета ячеек без учета регистра или с учетом регистра.

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

Считайте ячейки равными x или yЕсли вы хотите подсчитывать ячейки, если они соответствуют хотя бы одному из заданных условий (логика ИЛИ) в Excel, т.е. подсчитывать ячейки, равные либо x, либо y, вы можете использовать несколько COUNTIF формулы или объедините COUNTIF формула и SUM формула для выполнения этой работы.

Суммирование с множеством условий.

Имеются данные о заказах и продаже шоколада. Подсчитаем итог совершённых продаж по молочному шоколаду. То есть, у нас два требования: должно совпадать наименование товара и в колонке «Выполнен» должно быть указано «Да».

Первым аргументом мы указываем диапазон суммирования E2:E21, а затем попарно – диапазон условия и само условие для него.

В C2:C21 будем искать слово «молочный» с любым его вхождением. То есть, до и после него могут быть еще любые другие символы.

В F2:F21 ищем «Да», то есть отметку о том, что заказ выполнен.

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

Как видите, у нас найдено 2 совпадения, в которых был продан молочный шоколад.

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

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

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

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

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

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

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

).

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



Функция СЧЕТЕСЛИ в 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?

Общая формула

=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,character,»»)))

аргументы

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

Характер: Конкретный персонаж, которого нужно считать. Это может быть: 1. Символ, заключенный в кавычки; 2. Или ссылку на ячейку, содержащую символ.

Как пользоваться этой формулой?

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

Предположим, вы хотите посчитать символ «e» в диапазоне B5: B8, сделайте следующее.

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

=SUMPRODUCT(LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,»e»,»»)))

Ноты:

• 1. Измените диапазон «B5: B8» и символ «e» по своему усмотрению;
• 2. Если символ не найден в заданном диапазоне, он вернет 0;
• 3. Формула чувствительна к регистру;
• 4. Если вы хотите подсчитать определенный символ в диапазоне и игнорировать регистр, используйте функцию UPPER внутри подстановки, как показано ниже:=SUMPRODUCT(LEN(B5:B8)-LEN(SUBSTITUTE(UPPER(B5:B8),UPPER(«e»),»»)))

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

• 1. LEN(SUBSTITUTE(B5:B8,»e»,»»))): Функция ЗАМЕНА удаляет конкретный символ «e» из указанного диапазона B5: B8. А функция LEN вычисляет длину строки для каждой ячейки без определенного символа, а затем возвращает в виде массива чисел. Здесь он вернет {18; 10; 8; 18};
• 2. LEN(B5:B8): Эта функция LEN вычисляет общую длину строки для каждой ячейки в заданном диапазоне и возвращает длины в виде массива чисел. Здесь функция LEN вернет {20; 11; 10; 20};
• 3. SUMPRODUCT({20;11;10;20}-{18;10;8;18}): После вычитания окончательный массив должен быть {2; 1; 2; 2}, а затем функция СУММПРОИЗВ суммирует числа в массиве и возвращает общее количество «e» в диапазоне B5: B8.

Связанные функции

Функция ДЛСТР в Excel Функция LEN возвращает количество символов в текстовой строке.

Функция ПОДСТАВИТЬ в Excel Функция SUBSTITUTE заменяет текст или символы в текстовой строке другим текстом или символами.

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

Родственные формулы

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

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

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

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

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

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

Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.

Вид исходной таблицы:

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

В результате расчета получим:

Способ 1: счетчик на строке состояния

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

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

Способ 2: функция СЧЁТЗ

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

1. Выделяем область, в которую будет выводиться результат подсчета. Кликаем по значку «Вставить функцию».

Открывается окно Мастера функций. Ищем в представленном списке элемент «СЧЁТЗ». После того, как это наименование выделено, жмем на кнопку «OK».

Запускается окно аргументов. Аргументами этой функции являются ссылки на ячейки. Ссылку на диапазон можно прописать вручную, но лучше установить курсор в поле «Значение1», куда нужно ввести данные, и выделить соответствующую область на листе. Если нужно произвести подсчет заполненных ячеек в нескольких удаленных друг от друга диапазонах, то координаты второго, третьего и последующего диапазона нужно вводить в поля под названием «Значение2», «Значение3» и т.д. Когда все данные введены. Жмем на кнопку «OK».

Данную функцию также можно ввести вручную в ячейку или строку формул, придерживаясь следующего синтаксиса:

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

Способ 3: функция СЧЁТ

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

1. Как и в предыдущем случае, выделяем ячейку, куда будут выводиться данные и таким же способом запускаем Мастер функций. В нём выбираем оператора с наименованием «СЧЁТ». Жмем на кнопку «OK».

Запускается окно аргументов. Аргументы те же самые, что и при использовании предыдущего способа. В их роли выступают ссылки на ячейки. Вставляем координаты диапазонов на листе, в которых нужно подсчитать количество заполненных ячеек числовыми данными. Жмем кнопку «OK».

Для ручного введения формулы придерживаемся следующего синтаксиса:

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

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

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

1. Вводим синтаксис в необходимую клетку и выделяем диапазон. Вписываем «столы» и закрываем оператор.

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

1. Смотрим результат и сверяем.

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

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

Примеры работы функций СЧЁТ, СЧИТАТЬПУСТОТЫ и СЧЁТЕСЛИ в Excel

​Все версии Excel, начиная​ Функции СУММЕСЛИ, СЧЁТЕСЛИ​ таблице. О разных​ не считает пустые​ больше 300.​ написали в формуле.​

Где используется и как работает функция СЧЁТ?

​ конечных пробелов, недопустимых​Эта ошибка возникает при​ 3.​32​Самая простая функция СЧЁТЕСЛИ​ ДЕНЬНЕД сначала на​ двойные минусы в​ обеих строк.​ определить, какое количество​

​ А1:С5 нужно прописать​

• ​ столько чисел введено​ с 2007, могут​ и СЧИТАТЬПУСТОТЫ возвращают​
• ​ способах анализа, смотрите​ ячейки. Формат ячейки​
• ​Получилось такая формула.​

​ Рассмотрим,​ прямых и изогнутых​

​ вычислении ячеек, когда​

​=СЧЁТЕСЛИ(B2:B5;»>=32″)-СЧЁТЕСЛИ(B2:B5;»>85″)​апельсины​ означает следующее:​ 1, потом, на​ формуле??​Может быть, есть​ студентов получили отличную​ такую формулу:​ в ячейки при​ учитывать до 255​ ошибку #ЗНАЧ!​ в статье «Анализ​ ставим «процентный».​Формула посчитала так​

1. ​как посчитать количество ячеек​ кавычек или непечатаемых​ в формуле содержится​
2. ​Количество ячеек со значением,​54​
3. ​=СЧЁТЕСЛИ(где нужно искать;что нужно​ 3. В любом​Yurii_74​ какие-то другие варианты​ оценку 5 (по​Введение её через Enter​ помощи цифр, которые​ аргументов при расчёте​Решение:​

​ данных в Excel»​Если нужно, чтобы​ — в двух​

Пример использования функции СЧЁТ в Excel

​ символов. В этих​ функция, которая ссылается​ большим или равным​персики​ найти)​ случае — И​: Только сегодня разобрался​ решения?​ пятибалльной системе оценивания),​ даст результат 8.​

​ не заключены в​ результата этой формулы.​Сократите строку, если​ тут.​

​ расчет производился с​ ячейках стоят цифры​.​ случаях функция СЧЁТЕСЛИ​ на ячейки или​ 32 и меньшим​75​Например:​

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

​ это возможно. Если​В Excel можно​ учетом пустых ячеек,​ больше 300 (330,​Например, нужно посчитать​

​ может вернуть непредвиденное​ диапазон в закрытой​

​ или равным 85,​яблоки​=COUNTIF(A2:A5;»Лондон»)​ вычисления привязаны к​ Это простой перевод​ пожалуйста…​ потом 3 и​ ячеек есть в​Теперь введём в ячейку​

Пример использования функций СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel

​ первый из них.​ сократить строку нельзя,​ из данных разных​ формула будет такая.​ 350).​ ячейки, которые содержат​ значение.​ книге. Для работы​ в ячейках В2–В5.​86​

​=СЧЁТЕСЛИ(A2:A5;A4)​ месяцу октябрю?​ логических значений ИСТИНА,​Yurii_74​ 2.​ обозначенной области.​

​ ниже D2, другую​ При этом есть​ используйте функцию СЦЕПИТЬ​ ячеек составить предложение.​=СЧЁТЕСЛИ(B15:B19;»да»)/ЧСТРОК(B15:B19)​Другие примеры​

​ определеную цифру, слово,​Попробуйте воспользоваться функцией ПЕЧСИМВ​ этой функции необходимо,​ Результат — 3.​Формула​

​СЧЁТЕСЛИ(диапазон;критерий)​Добавлено через пять​ ЛОЖЬ в 1​

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

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

​ разница в том,​ или оператор-амперсанд (&),​ Эта функция удобна​Функцией «СЧЁТЕСЛИ» в Excel​применения функции «СЧЁТЕСЛИ».​ посчитать результат голосования​ или функцией СЖПРОБЕЛЫ.​ чтобы другая книга​=СЧЁТЕСЛИ(A2:A5;»*»)​

1. ​Описание​Имя аргумента​ минут……​
2. ​ или 0 (первый​ их​

​ нужно провести анализ​

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

​ была открыта.​Количество ячеек, содержащих любой​=СЧЁТЕСЛИ(A2:A5;»яблоки»)​Описание​

​Правда, формула правильно​ минус переводит ИСТИНУ​=СЧЁТЕСЛИ($F3:$L3;»=Я») + СЧЁТЕСЛИ($F3:$L3;»=8-12″)​ содержимого ячеек второго​ функцию СЧИТАТЬПУСТОТЫ:​ будут прописаны в​ если оно хранится​ на несколько строк.​ документов, т.д. Подробнее​ количество ячеек по​ формулах стоит знак​ анкете, опросе, провести​ диапазоны.​

​Действие​ текст, в ячейках​Количество ячеек, содержащих текст​диапазон​ работает, если​ в -1, 2ой​.​ столбика. В отдельной​

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

• ​ Пример:​
• ​ об этой возможности​ условию и узнать​
• ​ «звездочка». Это подстановочный​
• ​ другой анализ данных,​

• ​Функция СЧЁТЕСЛИ поддерживает именованные​Результат​ А2–А5. Подстановочный знак​ «яблоки» в ячейках​

  exceltable.com>