Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Как подсчитать количество повторений

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

И необходимо подсчитать количество повторений каждого наименования:

Как ни странно, но сделать это весьма просто: в Excel имеется функция - СЧЁТЕСЛИ , при помощи которой все это сделать можно буквально за секунды. Если количество повторений каждого наименования необходимо вывести в столбец В таблицы, а сами наименования расположены в столбце А:
=СЧЁТЕСЛИ($A$2:$A$30 ; A2)
Диапазон ($A$2:$A$30) - указываются ячейки диапазона, в которых записаны значения, количество которых необходимо подсчитать. Главная особенность: данный аргумент может быть исключительно ссылкой на ячейку или диапазон ячеек. Недопустимо указывать произвольный массив значений.
Критерий (A2) - указывается ссылка на ячейку или непосредственно значение для подсчета. Т.е. можно указать и так: =СЧЁТЕСЛИ($A$2:$A$30 ;"Яблоко") . Помимо этого можно применять символы подстановки: ? и *. Т.е. указав в качестве Критерия "*банан*" можно подсчитать количество ячеек, в которых встречается слово "банан" (банановый, банан, бананы, банановый сок, сто бананов, три банана и орех и т.п.). А указав "банан*" - значения, начинающиеся на "банан" (бананы, банановый сок, банановая роща и т.п.). "?" - заменяет лишь один символ, т.е. указав "бан?н" можно подсчитать строки и со значением "банан" и со значением "банон" и т.д. Если в качестве критерия указать =СЧЁТЕСЛИ($A$2:$A$30 ;"*") , то будут подсчитаны все текстовые значения. Числовые значения при этом игнорируются. Данные подстановочные символы (* и?) не получится применить к числовым значениям - исключительно к тексту. Т.е. если если указать в качестве критерия "12*", то числа 1234, 123, 120 и т.п. не будут подсчитаны. Для подсчета числовых значений следует применять операторы сравнения: =СЧЁТЕСЛИ($A$2:$A$30 ;">12")

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

Если необходимо подсчитать количество повторений на основании нескольких условий(значений), то начиная с 2007 Excel это легко можно сделать при помощи функции СЧЁТЕСЛИМН(COUNTIFS). Синтаксис функции почти такой же, как у СЧЁТЕСЛИ(COUNTIF), только условий и диапазонов больше:
=СЧЁТЕСЛИМН($A$2:$A$30 ; A2 ; $B$2:$B$30 ; B2)
предполагается, что условия записаны в столбце В
По сути идет просто перечисление:
=СЧЁТЕСЛИМН(Диапазон_условий1;Условие1; Диапазон_условий2;Условие2; Диапазон_условий3;Условие3; и т.д.)

Но. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке($D$1):
Дыня Киви Груша Яблоко Дыня Груша Груша Арбуз Яблоко Банан Яблоко Яблоко Банан Яблоко Яблоко Дыня Дыня Киви Банан Дыня Арбуз Дыня Киви Яблоко Дыня Груша Яблоко Киви Арбуз
Здесь СЧЁТЕСЛИ точно не поможет. Но в Excel полно других функций и все можно сделать так же достаточно просто:
=(ДЛСТР($D$1)-ДЛСТР(ПОДСТАВИТЬ($D$1 ; D3 ;"")))/ДЛСТР(D3)
ДЛСТР - подсчитывает количество символов в указанной ячейке/строке($D$1 , D3)
ПОДСТАВИТЬ (текст; старый_текст; новый_текст) - заменяет в указанном тексте заданный символ на любое другое заданное значение. По умолчанию заменяет все повторы указанного символа. Именно это и положено в основу алгоритма. На примере значения Банан(D3) пошаговый разбор формулы:

• при помощи функции ДЛСТР получаем количество символов в строке с исходным текстом($D$1) =(170-ДЛСТР(ПОДСТАВИТЬ($D$1 ; D3 ;"")))/ДЛСТР(D3) ;
• при помощи функции ПОДСТАВИТЬ заменяем в строке с исходным текстом($D$1) все значения Банан(D3) на пусто и при помощи ДЛСТР получаем количество символом строки после этой замены =(170-155)/ДЛСТР(D3) ;
• вычитаем из общего количества символов количество символов в строке после замены и умножаем результат на количество символов в критерии =(170-155)/5 .

Получаем число 3. Что нам и требовалось.

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

Если диапазон (например, a2: D20) имеет числовые значения 5, 6, 7 и 6, то число 6 встречается два значения.

Если столбец имеет значения «Батурин», «Белов», «Белов» и «Белов», то "Белова" выполняется три значения.

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

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

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

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

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

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

СЧЁТЕСЛИМН (диапазон_условия1;условие1;[диапазон_условия2;условие2];…)

См. пример ниже.

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

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

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

Примечания:

Дополнительные сведения об этих функциях см. в статьях Функция СЧЁТ и Функция ЕСЛИ .

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

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

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

Пример 1

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

Пример 2

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

Пример 3

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

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

Для отображения итоговых значений и подсчета числа повторений в сводной таблице можно использовать сводную таблицу. Сводная таблица - это интерактивный способ быстрого суммирования больших объемов данных. Вы можете использовать ее для развертывания и свертывания уровней представления данных, чтобы получить точные сведения о результатах и детализировать итоговые данные по интересующим вопросам. Кроме того, можно перемещать строки в столбцы или столбцы в строки ("сводить" их) для просмотра количества вхождений значения в сводной таблице. Рассмотрим пример электронной таблицы "Продажи", в которой можно подсчитать количество значений продаж для разделов "Гольф" и "Теннис" за конкретные кварталы.

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

1. Функций много и пользователь часто просто не знает, что ищет, т.к. не знает названия функции.
2. Функции - первый шаг к облегчению жизни в Экселе.

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

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

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

Если с первым аргументом более-менее понятно, можно подставить диапазон типа A1:A5 или просто название диапазона, то со вторым уже не очень, потому что возможности задания критерия достаточно обширны и часто незнакомы тем, кто не сталкивается с логическими выражениями.

Самые простые форматы "Критерия":

• Ячейка строго с определенным значением, можно поставить значения ("яблоко"), (B4),(36). Регистр не учитывается, но даже лишний пробел уже включит в подсчет ячейку.
• Больше или меньше определенного числа. Тут уже идет в ход знак равенства, точнее неравенств, а именно (">5");("<>10");("<=103").

Но ведь нам иногда нужны более специфичные условия:

• Есть ли текст. Хотя кто-то может сказать, что функция и так считает только непустые ячейки, но если поставить условие ("*"), то будет искаться только текст, цифры и пробелы в расчет приниматься не будут.
• Больше (меньше) среднего значения диапазона: (">"&СРЗНАЧ(A1:A100))
• Содержит определенное количество символов, например 5 символов:("?????")
• Определенный текст, который содержится в ячейке: ("*солнце*")
• Текст, который начинается с определенного слова: ("Но*")
• Ошибки: ("#ДЕЛ/0!")
• Логические значения ("ИСТИНА")

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

«Глаза боятся, а руки делают»

Навигация по записям

Функция СЧЁТЕСЛИ: подсчет количества ячеек по определенному критерию в Excel : 55 комментариев

1. поМарка

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

2. admin Автор записи

   по идее, СЧЁТЕСЛИ как раз и ищет совпадения, невзирая на регистр.

3. поМарка

   В том и дело, что не находит — он не различает при поиске заглавные и строчные буквы, а объединяет их в общее количество совпадений…
   Подскажите, может надо какой-нибудь символ поставить при поиске? (апострофы и кавычки не помогают)

4. Игорь

   Как задать условие в excel что бы он считал определенный диапазон ячеек в строках с определенным значением в первом столбце?

5. admin Автор записи

   Игорь, вообще-то СЧЁТЕСЛИ это и делает. Наверное, вам лучше конкретизировать задачу.

6. Анна

   понравилась статься, но увы не получается что-то, мне нужно суммировать из разных чисел повторяющиеся цифры, например 123 234 345 456 мне нужно посчитать сколько «1″, «2″, «3″ и т.д. в этих числах то есть чтобы формула распознала одинаковые цифры и считала их, если это возможно напишите как быть? Буду очень ждать, С уважением, Анна Ириковна

7. admin Автор записи

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

8. Alex

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

9. admin Автор записи

   СЧЁТЕСЛИ($A$1:$A$100;B1)

10. Анна

    Пример такой, 23 12 1972 то есть это дата рождения, мне нужно чтобы суммировалось количество двоек, то есть не 2+2+два, а что их всего три двойки, то есть в ячейке конечной должно стоять 3, единиц 2, 3 7 9 по единице, такое возможно? просто я голову сломала, я самоучка, но такие формулы мне сложноваты, если Вам не очень трудно дайте пожалуйста образец формулы полностью, хотя бы на одно число, с уважением, Анна Ириковна

11. admin Автор записи

    Представим, что ваша дата в ячейке А1.

    Тогда считаем сколько двоек: = длстр(A1)-длстр(ПОДСТАВИТЬ(A1;»2″;»"))

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

12. Анна

    спасибо, я сейчас попробую формулу … попробую сама, если совсем не выйдет тогда с большим поклоном буду просить советов еще)) С уважением, Анна Ириковна

13. Анна

    не выходит… можно я пришлю Вам то что мне нужно на электронный адрес? просто это таблица.. мне сложно ее описать… Анна И.

14. Анна
15. Макс

    Хорошая статья) Но все равно не смог разобраться со своим заданием. У меня есть 3 столбца, один это Студенты, второй Преподаватели, третий Оценки. Подскажите, как посчитать количество студентов, обучающихся у Ивановой, получивших положительные оценки? Получается вроде как 2 диапазона и 2 критерия, и я не могу понять)

16. admin Автор записи

    Используйте СЧЁТЕСЛИМН

17. Maykot

    Добрый день.
    Помогите разобраться с диапазоном.
    У меня есть ячейка А2 в которой есть текстовое значение — например «солнце».
    В ячейке A3 значение «море». И т.д.
    Как правильно вписать в Формулу =СУММЕСЛИ(C:C;»*солнце*»;D:D) вместо конкретного диапазона («*солнце*») содержимое ячейки A2, т.е. не =СУММЕСЛИ(C:C;»*солнце*»;D:D), а вместо «*солнце*» была ссылка на ячейку?

18. admin Автор записи

    СУММЕСЛИ(C:C;»*»&$A$1&»*»;D:D)

19. Дмитрий
20. Александр

    Здравствуйте! Подскажите пожалуйста, как в Excel представить формулу:
    Скорректированная стоимость =
    = Стоимость * (К1 + К2 + … + КN – (N — 1);
    где:
    К1, К2, КN — коэффициенты, отличные от 1
    N – количество коэффициентов, отличных от 1.

21. Александр

    Здравствуйте! Подскажите пожалуйста, как в формуле:
    =СУММЕСЛИ(C3:C14;»<1")-(СЧЁТЕСЛИ(C3:C14;"<1")-1)
    задать диапазон значений коэффициентов, отличных от 1 (менее 1, более 1, но менее 2).

22. Михаил

    Добрый вечер!
    Подскажите, пожалуйста, как посчитать количество ячеек в которых указана какая-либо дата? То есть, в столбце есть ячейки с датами (разными) и есть ячейки с текстом (разным), мне нужно посчитать количество ячеек с датами.
    Спасибо!

23. admin Автор записи

    СЧЁТЕСЛИ(F26:F29;»01.01.2016″)
    Пойдет?

24. Юлия

    Добрый вечер! Подскажите, пожалуйста, формулу, считающую цифры только которые больше 8 (переработка в табеле учета рабочего времени). Вот неправильный вариант: =SUMIF(C42:V42;»>8″)+SUMIF(C42:V42)

25. admin Автор записи

    SUMIF(C42:V42,»>8″) =СУММЕСЛИ(C1:C2;»>8″)

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

26. Д.Н.

    Добрый день! спасибо за формулу массива для диапазона с несколькими критериями!
    у меня, наверное, глупый вопрос, но как заменить текст {1;2;3} на ссылки на ячейки с текстовыми значениями.
    то есть если я ввожу { «X»; «Y»;»Z»} считает всё верно
    но при вводе {A1;A2;A3} — ошибка,
    дело в фигурных скобках?)

27. Виталий

    Спасибо. Очень помогла статья.

28. Денис

    Добрый день. Столкнулся с такой проблемой в функции СЧЁТЕСЛИМН. При вводе 2х диапазонов все считает отлично, но при добавлении 3-го — выдает ошибку. Может ли скрываться подвох в количестве ячеек?
    У меня =СЧЁТЕСЛИМН(‘очная форма обучения’!R11C13:R250C13;»да»; ‘очная форма обучения’!R11C7:R250C7;»бюджет»; ‘очная форма обучения’!R16C9:R30C9;»да»)
    Без 3-го диапазона и условия все нормально.
    Заранее благодарен.

29. admin Автор записи

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

30. A.K.

    Здравствуйте, помогите, пожалуйста, разобраться.
    Есть два столбца: один — дата, второй — время (формат 00:00:00).
    Необходимо выбрать даты соответствующие определенному периоду времени.
    При этом, таких промежутков должно быть 4, т.е. каждые 6 часов.
    Возможно ли это это задать одной формулой и если — да, то какой?

31. admin Автор записи

    Добрый день.
    Конечно, можно. Правда, я не понял, вам надо по датам или по часам? Две разные формулы. И как вы хотите это разбить? Чтобы промежутки помечались номерами? Типа первые 6 часов суток — это 1, вторые -2 и т.д.?

32. андрей

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

33. admin Автор записи

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

34. Кросс

    Здравствуйте! Вопрос такой. Имеются 4 столбца, в которых соответственно указаны ученики (столбец А), № школы (столбец В), баллы по химии (столбец С), баллы по физике (столбец D). Надо найти кол-во учеников определённой школы (например, 5), которые набрали по физике баллов больше, чем по химии. Всего учеников 1000. Можно ли использовать какую-то одну формулу для ответа на вопрос? Пробую использовать СЧЕТЕСЛИМН, но не получается.

35. admin Автор записи

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

36. Кросс

    Окей, спасибо

37. Александр

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

38. Алена

    Здравствуйте! Нужна помощь.
    Имеется столбец с датами рождения в формате 19740815, а необходимо преобразовать в формат 15.08.1974
    Спасибо за ранее.

39. admin Автор записи

    Добрый день.

    Ну самое простое — Текст по столбцам -фиксированная ширина (4-2-2) — потом добавить столбец с функцией ДАТА.

40. admin Автор записи Алина

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

    СЧЁТЕСЛИМН(‘Raw Data’!B2:B150;»Bucharest»;’Raw Data’!E1:E150;»06.11.2014″)

41. Вера

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

42. admin

    Добрый день всем!
    Есть фактический график выхода сотрудников, все рабочие часы написаны в формате «09*21″ — дневная полная смена и «21*09″ — ночная полная смена.
    Также есть дни с неполными сменами, которые считаются к зарплате по почасовой ставке, например «18*23″ и тд.
    Формат всех ячеек — текстовый.

    Необходимо, чтобы формула высчитывала по каждой строке (каждому сотруднику соответственно) количество полных смен за месяц, в идеале если она будет учитывать критерии «09*21″+»21*09″, но можно и по одному критерию, я тогда просто столбцы эти скрою и их уже объединю суммой.

    Через =счетесли пробовала, в окошке формулы значение считает верно, а в самой ячейке отображает тупо написанную формулу, формат какой только не ставила — не помогает.
    Пыталась заменить 09*21 на 09:00 — 21:00 в ячейках и формуле соответственно, но тоже ни в какую.
    Проставляла в формуле и «09*21*», и «*09*21*» — без толку.

    Если можно такую штуку делать при условии, что записано будет «09:00 — 21:00″ — вообще отлично, один месяц мне проще будет перелопатить, но дальше уже всё будет ровно)
    и сразу с ходу вопрос — есть ли формула, по которой можно будет считать общее количество часов в диапазоне со всеми любыми значениями («18:00 — 23:00″, «12:45 — 13:45″ и тд), кроме вышеуказанных «09:00 — 21:00″ и «21:00 — 09:00″ либо считать все ячейки, где количество часов 12 и отдельно все, где количество часов меньше 12.

    Заранее спасибо огромное, ломаю голову уже неделю!(((

43. admin Автор записи

    Попробуйте СЧЁТЕСЛИ($A$1:A10;A10) — вставляется в ячейку B10.

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

СЧЕТ()

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

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

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

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

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

СЧЕТЕСЛИ()

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

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

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

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

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

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

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

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

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

Говорят, что нет специальной функции без макросов посчитать количество выделенных строк в Excel . Неправда. Есть много других приемов, чтобы посчитать выделенные строки, ячейки Excel. Было бы желание, а способы найдем.)))
Один из способов узнать количество выделенных строк – это настроить порядковые номера строк после фильтра. Смотрите об этом способе статью «Порядковый номер строк по порядку после фильтра в Excel »
Второй способ – посчитать количество выделенных ячеек в столбце Excel .
Выделяем ячейки в столбце и смотрим внизу листа Excel в строке состояния цифру «Количество». Это число заполненных выделенных ячеек. Пустые ячейки так Excel не считает. Но, если нужно посчитать строку, в которой в столбце А нет значений, а есть в столбце В, то выделяем ячейку столбца В. Главное, чтобы были выделены ячейки всех нужных строк. Например, так. Здесь выделили четыре строки.
Третий способ .
Можно в таблице фильтром отфильтровать нужные данные. Затем выделить отфильтрованные ячейки в столбце. В строке состояния будет видно количество строк. Помним, что пустые ячейки не считаются в строке состояния. Получится так.Выделены три строки.
Быстро выделить столбец можно так. Выделяем верхнюю ячейку столбца. Нажимаем на клавишу F8 и ставим курсор в нижнюю ячейку столбца. Нажимаем левую мышку.
Или нажимаем сочетание клавиш «Ctrl» + «А». Активная ячейка, при этом, находится в таблице.
О других сочетаниях клавиш, чтобы выделить ячейки, смотрите в статье «Сочетание клавиш в Excel».
Четвертый способ .
Посчитать количество строк можно с помощью функции «Автосумма» в Excel . Выделяем ячейку, в которой будет стоять цифра количества строк. Нажимаем стрелку у кнопки «Автосумма» на закладке Excel «Главная» и выбираем функцию «Число».Эта функция считает только заполненные строки. Если есть пустые ячейки в столбце, то в формуле исправляем адрес первой ячейки диапазона. Например, мы вставили формулу функцией «Автосумма». В этой формуле указан диапазон до первой пустой ячейке столбца. =СЧЁТ(A4:A6) Мы адрес ячейки А4 исправляем на адрес ячейки А1. Получится так.
Пятый способ.
Посчитать, если выделено цветом – формула Excel .
Можно написать такую формулу. =СЧЁТ(A1:A6) Эта формула считает только ячейки с числами, а с текстом – не считает.
Шестой способ.
Чтобы посчитать ячейки (строки) по определенному условию, используем функцию «СЧЁТЕСЛИ». Примеры формул смотрите в статье «Функция «СЧЁТЕСЛИ» в Excel» .
Седьмой способ .
Посчитать строки, выделенные условным форматированием или окрашены цветом вручную в Excel .
Можно применить фильтр по цвету.

Затем выделить ячейки и посмотреть количество в строке состояния.
Здесь ячейки выделены условным форматированием. Отфильтровали по цвету. В строке состояния написано, что выделено 3 строки (ячейки).
Как преобразовать формат данных из других программ в формат числа Excel, чтобы формулы считали такие числа, смотрите в статье "