Excel как посчитать встречающиеся ячейки

Поиск и подсчет самых частых значений

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

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

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

Поиск самых часто встречающихся чисел

Предположим, перед нами стоит задача проанализировать имеющиеся данные по продажам в магазине, с целью определить наиболее часто встречающееся количество купленных товаров. Для определения самого часто встречающегося числа в диапазоне можно использовать функцию МОДА (MODE):

Т.е., согласно нашей статистике, чаще всего покупатели приобретают 3 шт. товара.

Если существует не одно, а сразу несколько значений, встречающихся одинаково максимальное количество раз (несколько мод), то для их выявления можно использовать функцию МОДА.НСК (MODE.MULT). Ее нужно вводить как формулу массива, т.е. выделить сразу несколько пустых ячеек, чтобы хватило на все моды с запасом и ввести в строку формул =МОДА.НСК(B2:B16) и нажать сочетание клавиш Ctrl+Shift+Enter.

На выходе мы получим список всех мод из наших данных:

Т.е., судя по нашим данным, часто берут не только по 3, но и по 16 шт. товаров. Обратите внимание, что в наших данных только две моды (3 и 16), поэтому остальные ячейки, выделенные «про запас», будут с ошибкой #Н/Д.

Частотный анализ по диапазонам функцией ЧАСТОТА

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

Для решения подобной задачи можно воспользоваться функцией ЧАСТОТА (FREQUENCY). Для нее нужно заранее подготовить ячейки с интересующими нас интервалами (карманами) и затем выделить пустой диапазон ячеек (G2:G5) по размеру на одну ячейку больший, чем диапазон карманов (F2:F4) и ввести ее как формулу массива, нажав в конце сочетание Ctrl+Shift+Enter:

Частотный анализ сводной таблицей с группировкой

Альтернативный вариант решения задачи: создать сводную таблицу, где поместить вес покупок в область строк, а количество покупателей в область значений, а потом применить группировку — щелкнуть правой кнопкой мыши по значениям весов и выбрать команду Группировать (Group). В появившемся окне можно задать пределы и шаг группировки:

… и после нажатия на кнопку ОК получить таблицу с подсчетом количества попаданий покупателей в каждый диапазон группировки:

Минусы такого способа:

• шаг группировки может быть только постоянным, в отличие от функции ЧАСТОТА, где карманы можно задать абсолютно любые
• сводную таблицу нужно обновлять при изменении исходных данных (щелчком правой кнопки мыши — Обновить), а функция пересчитывается автоматически «на лету»

Поиск самого часто встречающегося текста

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

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

Затем, само-собой, отсортировать получившийся столбец по убыванию и посмотреть на первые строчки.

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

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

Давайте разберем ее по кусочкам:

• СЧЁТЕСЛИ(A2:A20;A2:A20) – формула массива, которая ищет по очереди количество вхождений каждого товара в диапазоне A2:A100 и выдаст на выходе массив с количеством повторений, т.е., фактически, заменяет собой дополнительный столбец
• МАКС – находит в массиве вхождений самое большое число, т.е. товар, который покупали чаще всего
• ПОИСКПОЗ – вычисляет порядковый номер строки в таблице, где МАКС нашла самое большое число
• ИНДЕКС – выдает из таблицы содержимое ячейки с номером, который нашла ПОИСКПОЗ

Ссылки по теме

• Подсчет количества уникальных значений в списке
• Извлечение уникальных элементов из списка с повторами
• Группировка в сводных таблицах

Как посчитать количество повторяющихся значений в Excel?

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

Подсчет количества каждого из дубликатов.

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

Чтобы узнать, сколько раз запись появляется на вашем листе Excel, используйте простую формулу СЧЁТЕСЛИ, где A2 — это первый элемент, а A8 — последний элемент в списке:

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

Как показано на следующем снимке экрана, программа считает появления каждого элемента: «Фанта» встречается 2 раза, «Спрайт» встречается 3 раза и так далее.

Если вы хотите указать 1-й, 2-й, 3-й и т.д., когда встречается каждый элемент, используйте:

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

Мы отметили на рисунке первое, второе и третье вхождения Sprite.

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

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 17; A2; $ B $ 2: $ B $ 17; B2; $ C $ 2: $ C $ 17, C2)

На скриншоте мы отметили те же линии.

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

Считаем общее количество дубликатов в столбце.

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

= СЧЁТЕСЛИ (диапазон; «Дублировать»)

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

В этом примере наше выражение принимает следующую форму:

= СЧЁТЕСЛИ (B2: B17; «Дублировать»)

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

{= СТРОКИ ($ A $ 2: $ A $ 17) -SUM (IF (COUNTIF ($ A $ 2: $ A $ 17; $ A $ 2: $ A $ 17) = 1; 1; 0))}

Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, чтобы завершить ввод. 

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

Можно обойтись без формулы майисва:

= СУММПРОИЗВ (- (СЧЁТЕСЛИ (A2: A17; A2: A17)> 1))

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

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

Здесь также есть два пути. Первый использует вспомогательную колонку B.

В графе Б ставим соответствующие знаки, как мы это делали не раз.

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2; A2)> 1, «Дублировать»;»»)

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

= СЧЁТЕСЛИ (B2: B17; «Дублировать»)

или матричная формула

{= СЧЁТ (A2: A17) -SUM (1 / СЧЁТЕСЛИ (A2: A17; A2: A17))}

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

{= СУММ (1 / СЧЁТЕСЛИ (A2: A17; A2: A17))}

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

Что ж, теперь школьная проблема: если у нас есть только 16 слов (вы можете использовать функцию COUNT) и 10 из них уникальны, сколько будет неуникальных? Правильно — 6!

Количество совпадений по части ячейки.

Допустим, у нас в ячейке есть не только название товара, но и другая дополнительная информация: группа товаров, номер счета, единица измерения и т.д. Как в этом случае рассчитывается количество упоминаний того или иного товара?

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

Делаем это так:

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 17; «*» & C2&»*»)

Мы ищем любое вхождение нужного слова, используя подстановочный знак “*”.

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

 Бывает, что список находится вовсе не в таблице, а в ячейке ($ A $ 2):
Приемы, которые мы обсуждали выше, здесь определенно не сработают. Но в Excel нет других функций, и все можно сделать так же легко:

= (DLSTR ($ D $ 1) -LSTR (ПОДСТАВИТЬ ($ D $ 1; D3; «»))) / DLSTR (D3)

С помощью DLSTR подсчитываем количество символов в ячейке со списком товаров ($ A $ 2)
Затем, используя ЗАМЕНА, замените указанное слово в указанном тексте пробелом «». Заменить все повторы по умолчанию. Фактически мы их устраняем.

Теперь наша задача — узнать, сколько слов мы удалили. С помощью DLSTR узнаем количество символов, оставшихся в списке после этой замены. Вычтите количество оставшихся символов из начального количества символов, т.е узнайте, сколько символов было удалено. Разделите результат на количество букв в поисковом слове.

Результат вы можете увидеть на скриншоте выше.

Подсчет дубликатов строк.

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

{= СТРОКИ ($ A $ 2: $ A $ 17) -SUM (IF (COUNTIF ($ A $ 2: $ A $ 17; $ A $ 2: $ A $ 17; $ B $ 2: $ B $ 17 ; $ B $ 2: $ B $ 17) = 1; 1; 0))}

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

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

​Смотрите также​Пока так -​ что нужно) Браво.​Значения в нем​ наименьшее. Для решения​Примечание:​713 000 руб.​, группа​ в формулу массива:​. Как правило, для​

Пример

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

​Chai​30.06.2008​Для получения дополнительных сведений​ таблице (ячейки A2–A8)​Промежуточный итог​ с помощью сочетания​2​ раз встречается определенный​ единицы, выделили диапазон,​ ячейке B7 берет​Как посчитать сколько​ поисковыми и вычислительными​ машинного перевода​

​589 000 руб.​На следующем рисунке показано,​

Пример

​Маслов​=»>=31.03.2008″​Чтобы учесть заданные условия,​3​Структура​ в столбец списка​ В первом диапазоне​ функции СЧЁТЕСЛИ можно​Kuzmich​ соответствует значению А1))​

support.office.com

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

​ не получается. Может​​ воспользуемся такой схематической​ участия человека. Microsoft​Общее количество ячеек в​ записи в новое​2​ указанной ячейке, а​8000​=БСЧЁТ(A1:B7,,A10:B11)​

​ используйте функцию базы​4​) можно группировать​ с помощью расширенного​ применяются условия со​ посчитать, сколько раз​: Макрос выбирает уникальные​Bema​ формулой?))​ таблицей регистра заказов​ предлагает эти машинные​

​ диапазоне (9)​ место на листе.​3​ затем формула суммирует​=СЧЁТ3(B2:B7)​Функция​ данных​5​ и суммировать данные​ фильтра​

​ значением «2», а​ встречается одно значение​ записи из столбца​: Limyh, ну это​спасибо)​ от клиентов:​ переводы, чтобы помочь​К началу страницы​На следующем рисунке столбец​

В этой статье

​4​

​ количество значений ЛОЖЬ​Поскольку ячейки B3, B4​БСЧЁТ​БСЧЁТ​

​6​ в столбце.​Подсчет количества уникальных значений​

​ во втором — условия​ (см. пример ниже).​ А​

​ Вы уж слишком…​ПРикрепляю пример.​Теперь выполним простой анализ​ пользователям, которые не​

​Используйте функции​ С содержит пять​5​ и возвращает результат.​

​ и B6 пусты,​проверяет данные в​

​.​7​Если данные представлены в​

​ в диапазоне, удовлетворяющих​ со значением «Финансы».​Продавец​и переносит в​

​ Рад, что помог.​Bema​ наиболее часто и​ знают английского языка,​СУММ​

​ уникальных значений, скопированных​6​

​Дополнительные сведения см. в​ функция​ диапазоне A2–A7, применяет​

​Функция​8​ виде списка, можно​

​ одного или нескольких​ Функция СЧЁТЕСЛИМН проверяет​Счет​

​ столбец G, затем​Мотя​: Добрый вечер.​

​ редко повторяющихся значений​

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

​7​ статье базы знаний​СЧЁТЗ​ условия из ячеек​БСЧЁТ​

​9​ группировать их логически​ условий с помощью​ соответствие обоим условиям.​Грачев​ сортирует уникальный​

​: Вариант без формул​=СЧЁТЕСЛИ($A$1:$A$15;A21)​ таблицы в столбце​ о продуктах, услугах​

​ЕСЛИ​ в столбце А.​8​ Использование функции СУММ(ЕСЛИ())​их пропускает. В​

​ A11 и B11,​пригодится, если у​

​A​ по значениям в​ составной формулы​

​Важно:​15 000​список и подсчитывает​

​ — Штатный режим​Limyh​

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

​ «Город». Для этого:​ и технологиях Microsoft.​,​Для получения дополнительных сведений​9​

​ вместо СЧИТАТЬПУСТОТЫ() в​ подсчет включаются только​ а затем возвращает​ вас есть список​Единиц продано​ столбце, а также​Особые случаи (подсчет всех​Данная статья переведена​Грачев​ количество каждого уникального​ Excel — Сводная​: Спасибо, но что​Сначала находим наиболее часто​ Поскольку статья была​ДЛСТР​

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

​ обратитесь к разделу​10​ Excel.​ ячейки, содержащие значения​ значение 2, соответствующее количеству​​ значений в столбце​​25​​ группировать и суммировать​​ ячеек, подсчет слов)​ с помощью машинного​​9 000​​вхождения​​ Таблица.​​ делать если слова​ повторяющиеся названия городов.​ переведена с использованием​и​ «Подсчет количества уникальных​A​К началу страницы​ 24000, 31000 и​ строк, которые удовлетворяют​

​ и вам удобнее​

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

​8​ данные, создав структуру.​Общее количество ячеек в​ перевода, см. Отказ​​Шашков​​ant​Zaka​ в ячейке А21​ В ячейку E2​ машинного перевода, она​ПОДСТАВИТЬ​​ значений с помощью​​B​Для подсчета количества уникальных​​ 8000. Результат подсчета​​ обоим условиям (строки​​ задать условия в​​120​На рисунке выше представлены​ диапазоне с помощью​ от ответственности. Используйте​

​32​ итоговые объемы продаж,​ функций счёт​ английский вариант этой​Шашков​ant​Помогите, пож-та, с​Нужно просто подсчитать​

​Обязательно после ввода формулы​

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

​ и грамматические ошибки.​​ В примере ниже​​ Подсчет количества уникальных​​Счет​​ можно использовать отчет​​ равен 3.​​Для получения дополнительных сведений​ а не использовать​11​

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

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

​ здесь, в качестве​Грачев​

• ​Hugo​Есть таблица с​

• ​ в столбце-диапазоне А1-А15.​ клавиш CTRL+SHIFT+Enter, так​ значений в столбце.​

• ​ формулы для поиска​

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

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

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

​ справочного материала.​5 000​: Скомпоновал немного из​ названиями организаций. Список​В примере я​​ как ее нужно​​ Рассмотрим неповторяющиеся серии​​ количества слов в​​Используйте функции​​Chai​​, сочетание функций или​

​ количества непустых ячеек​К началу страницы​ количество месяцев, начиная​=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2,A2:A8)​ объемы продаж по​Отображение вычислений и счетчики​Подсчет является неотъемлемой​Нагайчук​ кода Kuzmich’a, остальное​ большой.​ привел как нужно​ выполнить в массиве.​ значений (111222234444…) и​ диапазоне, включающем 7 ячеек​ЕСЛИ​11000​

​ диалоговое окно​ .​Используйте функцию​ с марта 2008​Число 2 в формуле​ каждому из четырех​

​ в строке состояния​ частью анализа данных,​22 500​ из кода​Нужно подсчитать в​

​ чтобы было по​Для вычисления наиболее редко​ повторяющиеся (110111000010 и​ (3 из которых​

• ​,​Авдеев​

• ​Расширенный фильтр​К началу страницы​СЧЁТЕСЛИМН​

​ года включительно, в​

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

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

​ отдельной таблице (​​ итогу, а эта​​ повторяющегося названия города​ 110221010112).​ пусты). Некоторые ячейки​СУММ​11000​.​

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

​Результат​ быстрее в 2​ лист) Сколько раз​​ значение из А21​​ формулу:​ повторяющимися значениями.​ конечные пробелы — функции​ЧАСТОТА​4000​ и подсчета количества​

​можно подсчитать количество​и​ единиц товаров. Взглянув​для подсчета значений​ региону и по​ простой формулы или​​ количества единиц, проданных​​=СЧЁТЕСЛИ(A2:A7,»Грачев»)​ раза.​ одно наименование указывалось​ и считает сколько​Результат поиска названий самых​Предположим, что имеется столбец​СЖПРОБЕЛЫ​,​Иванова​ уникальных значений можно​

​и​ПОИСКПОЗ​8000​ использовать отчет сводной​ удовлетворяют заданным условиям,​.​

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

​ есть несколько методов​

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

​ (3)​Dim tm: tm​В конечном виде​ А1-А15​ редких городов клиентов​ примера. Даты отсортированы​

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

​ПОДСТАВИТЬ​и​Chai​​ таблицы.​​ в столбце записей​На рисунке показана функция​ два месяца удовлетворяют​ все скрытые строки.​ статьях:​ счетчик количество выделенных​ для подсчета ячеек,​3​

​: На всякий случай​ отображен на рисунке:​ удобства одинаковые даты​

​ пробелы перед подсчетом.​

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

​в разных сочетаниях.​Иванова​ обратитесь к разделу​​ базе данных.​​, которая используется для​

​ (442) и июнь​​ ячейке A9) равен 7.​​ листе​ строка состояния. В​ данных.​Количество вхождений фамилии Шашков​ As Object, i​Трактор 2​ выкладываю файл. Если​Если таблица содержит одинаковое​

​ выделены условным форматированием).​Из следующей таблицы скопируйте​Для получения дополнительных сведений​14000​ «Подсчет количества вхождений​В примере ниже функция​ поиска автомобилей с​ (405).​Если вы скрыли строки​Вставка промежуточных итогов в​ разделе Отображение вычисления​Чтобы помочь вам в​ (2)​

​ As Long, temp​​Адмирал 1​ это не то,​ количество двух самых​Сформируем в столбце​ текст в диапазоне​ обратитесь к разделу​Авдеев​ нескольких значений, с​БСЧЁТА​ двигателем мощностью более​Примечание:​ 4, 5 и​ списке данных на​ и количество в​ выборе наиболее оптимального​

​2​ As String​Господа 2​ что нужно, тогда​ часто повторяемых городов​С​ A2–A11 и вставьте​ «подсчет количества уникальных​7000​ помощью отчета сводной​используется для подсчета​ 250 лошадиных сил​ Следующий способ может показаться​ 6 и не​ листе​ строке состояния для​ метода, мы подробно​=СЧЁТЕСЛИ(B2:B7,»< 20000″)​

​Dim iLastRow As​и т.д.​ я Вас не​ или два самых​список уникальных значений​ в ячейку A1​ значений с помощью​12000​ таблицы» в статье​​ количества записей в​​ и средним расходом​​ несколько усложненным, однако​​ хотите, чтобы они​​Удаление промежуточных итогов​​ получения дополнительных сведений.​

​ также случаи их​​4​​ Range​Zaka​ знаю.​ том же столбце,​ =СЧЁТЕСЛИ($A$8:$A$23;C8) найдем количество​ столбца примерно до​ значений среди повторяющихся.​=СУММ(ЕСЛИ((A2:A10=»Владимир»)+(A2:A10=»Николай»),1,0))​К началу страницы​

​ и удовлетворяющих условиям,​ 100 км (25 миль​ придерживаться инструкций и​

​ПРОМЕЖУТОЧНЫЕ.ИТОГИ​

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

​ в котором будут​​ строке состояния при​​ применения. Дополнительную информацию​​=СЧЁТЕСЛИ(B2:B7,»>=»&B5)​​iLastRow = Cells(Rows.Count,​​: А можете объяснить​​Limyh​

​ тогда будет отображаться​​ подряд идущих значений​​ 100 единиц.​Кроме того читайте в​Количество счетов Авдеева или​Чтобы подсчитать, сколько раз​ заданным в диапазоне​ на галлон). Функция​ упорядочить данные на​несколько иначе. Вместо​ подытожены ваши данные​ необходимости краткий обзор​ вы сможете найти​Число счетов с суммой,​ 1).End(xlUp).Row ‘посл.строка в​

​ как ею в​​: не, не то.​​ первый из них.​​ дат.​1​ статье как определить​​ Зайцева (7)​​ значение встречается в​ условий A1:B2. Согласно​

​ возвращает значение 2, соответствующее​ листе именно так,​ числа 2 в​ и с помощью​​ данных и нет​​ по ссылкам на​ большей или равной​ диапазоне названий​ данном случае воспользоваться?​Попробую объяснить. Будем​​​​Задача усложняется, если серии​​2​​ количество уникальных элементов​

​Для получения дополнительных сведений​ диапазоне, используйте функцию​ этим условиям значение​

​ количеству строк, удовлетворяющих​

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

​ как показано в​ формуле укажите 102,​ которого вы сможете​ времени по вводу​ полные статьи.​

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

​ 20 000 (2)​Range(Cells(2, 7), Cells(iLastRow,​Юрий М​​ считать, что есть​​Принцип действия поиска популярных​

​ значений могут повторяться.​3​ в списке.​ обратитесь к разделу​СЧЁТЕСЛИ​ «Код товара» должно​ обоим условиям (строки​ этом примере. Можно​

​ чтобы приложение Excel​ анализировать данные, выбирая​ формул.​Подсчет не должно быть​2​ 7)).Clear ‘очистка диапазона​

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

​’определение диапазона названий​ могут показать на​Вот нужно в​ формул то можно​

​А29:А48​

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

​6​​ функций листа можно​​ значений вхождений с​СЧЁТЕСЛИ​ значение «Оценки» — больше​ В функции​ на лист в​ может выглядеть следующим​

​Чтобы создать сводную таблицу,​​ бы одно числовое​​ суммирование значений в​ важным исключением: СЧЁТЕСЛИМН​a = Range(Cells(2,​ конкретном примере. На​ этом столбце посчитать,​ легко заметить, что​.​7​ подсчитать количество ячеек​ помощью функции» в​возвращает значение 2, соответствующее​ или равно 50. Обоим​СЧЁТЕСЛИМН​ ячейку A1. Результат​ образом (строки 4,​ выделите ячейку в​

​ вхождений значения.​ 250 в диапазоне​ одна запись в​

​ заключать в кавычки​

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

​ ячейке B13.​​ скрыты):​​ таблице Excel, а​Главная​ статье суммирования значений​ нескольких диапазонах и​ CreateObject(«Scripting.Dictionary»)​: Или сводная таблица​В данном диапазоне​ функций: =МАКС() и​ задачи (серии положительных​10​

​Предположим, вам нужно определить​​Дополнительную информацию см. в​ A2:A7.​ строке 7.​ (​Добавляем дополнительный диапазон, ячейки​1​ затем на вкладке​

​в группе​ ячеек, столбцов и​ подсчитывает число соответствий​

​oDict.CompareMode = 1​

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

​Zaka​​ в ячейке А1​​ =МИН(). Все остальные​​ значений, серии значений​​11​ размер большого листа,​ таких статьях базы​1​​1​​»»​ в котором упорядочены​2​Вставка​Редактирование​ строк.​

​ каждому условию. С​For i =​: Мой пример прикрепила.​ — слово Table.​ аргументы формулы –​

​ >5%, даты попадающие​

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

​A​ чтобы решить, как​ знаний:​2​​2​​), например «25» или​ так же, как​​3​​в группе​

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

​щелкните стрелку рядом​Простой подсчет​ функцией СЧЁТЕСЛИМН можно​ 1 To UBound(a)​Юрий М​

​Всего в этом​ идентичны. Функция =СЧЕТЕСЛИ()​ в определенный интервал​Текстовая строка​ выполнять вычисления в​Использование функции СУММ(ЕСЛИ()) в​3​3​

​ «240».​

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

​ и данные по​7​Таблицы​​ с кнопкой​​Подсчет количества ячеек в​

​ использовать до 127 пар​​’ If Len(a(i,​​: Можно было и​ диапазоне данное слово​ подсчитывает, сколько раз​ и т.п.).​

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

​ в диапазоне встречается​

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

​ диапазоне A10–B7. В​​9​​Сводная таблица​​и выберите​​ с помощью встроенной​ Синтаксис функции имеет​If Not IsEmpty(a(i,​ И где считать?​​Нужно, чтобы формула​​ повторяется в диапазоне​ также найдем длину​   Шесть слов и три​ количество всех ячеек​ с критериями И​6​

​6​​ отдельное значение, используйте​​ дополнительный диапазон ячеек​A​.​Число​ команды​ следующий вид:​​ 1)) Then​​ Можно было и​ это посчитала и​ ячеек C2:C16. Таким​ серии (количество подряд​ начальных пробела​ в диапазоне, используйте​ и ИЛИ​7​7​ функцию​ включены ячейки диапазона​Единиц продано​Наглядный пример сводной таблицы​. Excel подсчитает количество​Подсчет количества ячеек в​СЧЁТЕСЛИМН(диапазон_условия1; условие1; [диапазон_условия2; условие2];​temp = Trim(a(i,​ показать вручную посчитанное​

​ подсчитаем длину каждой​Некоторые символы игнорируются, например​ЧСТРОК​ диапазоне​Единиц продано​A​ сколько раз значение​ («Объем продаж в​

​40​ на то, что​ с выделенным диапазоном.​

• ​Подсчет количества ячеек в​Номер региона​ Then​: Ваш файл.​Ячейка А2 -​

• ​Скачать пример поиска наибольшего​ серии. Формулу разместим​ запятые или точки.​

​и​

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

​К началу страницы​​245​​B​ 70 встречается в​ единицах продукции» и​16​ в этой таблице​ Как правило, этот​ столбце с помощью​Отдел​oDict.Add temp, 1​​Guest​​ Food​ и наименьшего повторения​ в столбце​

​Формула​​ЧИСЛСТОЛБ​

• ​С помощью диалогового окна​250​Код товара​ диапазоне A2–A40, используйте​ «Конец месяца»), а​=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(102,A2:A8)​ содержится много строк​ результат отображается в​​ структуры​​20552​​Else​​: Не поняла как.​​В диапазоне встречается​​ значения​

• ​В​=СУММ(ЕСЛИ(ДЛСТР(СЖПРОБЕЛЫ(A2:A8))=0,0,ДЛСТР(СЖПРОБЕЛЫ(A2:A8))-ДЛСТР(ПОДСТАВИТЬ(A2:A8,» «,»»))+1))​.​Расширенный фильтр​250​Оценки​ формулу​ также добавлено условие​​В нашем примере функция​​ со значениями объемов​ ячейке справа от​Подсчет количества ячеек в​

​2​oDict.Item(temp) = oDict.Item(temp)​​ Попыталась продлить формулу,​​ 1 раз. Тоже,​​Функция МАКС или МИН​​.​​Вставив текст на лист​​Чтобы этот пример проще​

​можно найти уникальные​Н/Д​=»>=4000″​=​ в строке непосредственно​ возвращает число 4,​

​ продаж (40 строк​ горизонтального диапазона или​ столбце или строке​Продажи​ + 1​

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

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

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

​=»>=50″​​СЧЁТЕСЛИ(​​ под названием каждого​​ что соответствует количеству​​ с данными), но​​ под вертикальным диапазоном.​​ с помощью сводной​​21268​​End If​​ организация и кол-во​​ значение — 1.​

​ массива наибольшее или​ чтобы была заполнена​ выделите ячейку A11,​ его на пустой​ данных. Эти значения​Для получения дополнительных сведений​Код товара​

​A2:A40;70)​ столбца (ячейки A11​ ячеек с данными,​ на графике отображается​

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

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

​ таблицы​2​End If​ 0.​Я так думаю,​

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

​ наименьшее значение. Функция​ желтая ячейка​ нажмите клавишу F2,​ лист.​ можно отфильтровать на​ обратитесь к разделу​Оценки​.​ и B11).​ которые отображаются в​​ только часть этих​​Чтобы подсчитать количество числовых​​Подсчет количества ячеек в​​Финансы​

​’End If​Прикрепила укороченную (:))​ что можно формулой​ =ПОИСКПОЗ() возвращает номер​

​А49​

1. ​ а затем комбинацию​Копирование примера​

2. ​ месте или извлечь​ «Подсчет количества вхождений​

   ​2048​​Подробнее читайте в статьях​Введите формулу в пустой​

   

   ​ столбце.​

3. ​ строк. Данные не​

4. ​ значений в диапазоне,​ списке или столбце​23949​

5. ​Next​ таблицу. В ней​ делать так:​ позиции на которой​(иначе, если последняя​ клавиш SHIFT+CTRL+ВВОД, чтобы​​Создайте пустую книгу или​​ их и вставить​​ отдельного значения, в​​61​​ Подсчет количества вхождений​​ ячейке (ее расположение​

​ 1 значения, то​

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

​ формулу массива. В​​Выделите пример в разделе​​ Затем с помощью​​ Подсчет количества вхождений​​35336​​К началу страницы​​ но в данном​​ данные итогов в​​ итогов, ни общего​СЧЁТ​Подсчет на основе одного​24522​’определение диапазона уникальных​ получить в итоге:​ берет значение А1​ наибольшему или наименьшему​ формула не произведет​ ячейке A11 должен​​ справки.​​ функции​​ значения.​​83​С помощью функций листа​

​ примере формула введена​ таблице Excel.​ итога.​. В примере ниже​ или нескольких условий​4​ записей и его​ Список организаций, и​

​Используйте функции​113​ ячейки с данными,​ В данном случае​С помощью функций листа​ основе этих же​ три числовых значения​ диапазоне с учетом​28010​iLastRow2 = Cells(Rows.Count,​Казанский​

​ встречается в диапазоне​

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

​ в качестве аргумента​ нулей используем формулу​К началу страницы​ и столбцов.​ элементов в новом​СУММ​512​ так и пустые​ в формуле используется​ можно подсчитать количество​ данных, который содержит​ (5, 32 и​ одного условия​3​ 7).End(xlUp).Row​: Еще есть такая​ А1:А15 и число​ для функции =ИНДЕКС(),​ массива:​При выделении одной или​Выделение примера из справки​ диапазоне.​и​47​ ячейки.​ функция​

​ ячеек в диапазоне,​​ промежуточные итоги, общие​ 10) и одно​Подсчет количества ячеек в​ИТ​Set Rng2 =​ штука Данные -​

​ совпадений и есть​

​ которая возвращает конечный​​=ЕСЛИОШИБКА(ИНДЕКС(Длины_серий;НАИМЕНЬШИЙ(ЕСЛИ((Значения=0)*(Длины_серий<>»»);СТРОКА(Длины_серий);»»);СТРОКА()-СТРОКА($E$28))-СТРОКА($E$28));»»)​​ нескольких ячеек сведения​Нажмите клавиши CTRL+C​​Примечания:​ЕСЛИ​=БСЧЁТА(A4:B9,»Оценки»,A1:B2)​Чтобы подсчитать количество ячеек​БСЧЁТ​ которые удовлетворяют заданным​ итоги и предоставляет​ текстовое («привет»). Функция​ столбце с учетом​29546​ Range(Cells(1, 7), Cells(iLastRow2,​ Итоги (в 2007​ результат формулы.​ результат в ячейку.​Аналогичную формулу запишем для​ о данных в​

support.office.com

Подсчет подряд идущих значений в MS EXCEL

​На листе выделите ячейку​ ​. Как правило, для​Для получения дополнительных сведений​ в диапазоне, содержащих​:​

​ условиям (так называемым​ краткий обзор данных.​

Неповторяющиеся серии значений

​СЧЁТ​ одного или нескольких​4​ 8)) ‘в G1-​ Данные — Промежуточные​В ячейке B2​

​Gambit​​ подсчету серий единиц:​​ этих ячейках отображаются​ A1 и нажмите​При фильтрации значений на​ этого в формуле​ обратитесь к статье​ значения, используйте функцию​

​=​ критериям).​

Серии повторяются (2 разных значения)

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

​ заглавие столбца-название​ итоги):​ соответственно формула берет​: Пиши макрос, который​=ЕСЛИОШИБКА(ИНДЕКС(Длины_серий;НАИМЕНЬШИЙ(ЕСЛИ((Значения=1)*(Длины_серий<>»»);СТРОКА(Длины_серий);»»);СТРОКА()-СТРОКА($G$28))-СТРОКА($G$28));»»)​ в строке состояния​ клавиши CTRL+V.​

​ месте они не​ массива с помощью​ БСЧЁТА.​СЧЁТЗ​

​БСЧЁТ(​Чтобы подсчитать количество ячеек,​ и работы с​ такого вида:​​Подсчет количества ячеек в​​31634​

​Rng2.Sort Key1:=Range(«G1»), Order1:=xlAscending,​Юрий М​ значение А2 и​​ попежит по всем​​Для удобства созданы именованные​ Excel. Например, если​Чтобы переключиться между просмотром​ удаляются с листа,​ функции​

​К началу страницы​.​A1:B7;;A10:B11)​

​ удовлетворяющих одному условию​

​ ними могут потребоваться​=​

​ диапазоне с учетом​

​3​ Header:=xlYes​: Не видно Вашей​

Серии повторяются (3 разных значения)

​ также до низу​ ячейкам столбца, и​​ диапазоны Значения и​​ на листе выделено​

​ результатов и просмотром​ просто одна или​

​ЕСЛИ​Функция​При подсчете ячеек иногда​.​​ (критерию), используйте функцию​​ некоторые навыки и​

​СЧЁТ(​ нескольких условий с​ИТ​

​Debug.Print Timer -​

​ укороченной… Попробуйте теперь​ считает.​ если значение ячейки​

excel2.ru

Как найти наиболее повторяющиеся значение в Excel

​ Длины_серий. ​ четыре ячейки, которые​ формул, возвращающих эти​ несколько строк могут​определяется соответствие условию,​СЧИТАТЬПУСТОТЫ​ необходимо игнорировать пустые​1​СЧЁТЕСЛИ​ предварительная подготовка данных.​A2:A5)​ помощью одной функции​

Поиск наиболее повторяющегося значения в Excel

​32131​ tm​ протянуть формулу на​Проблема мне видится​ равно определенной цифре,​

​Рассмотрим серию вида 110221010112…​ содержат значения 2,​ результаты, нажмите клавиши​ быть скрыты. Чтобы​ составленному из нескольких​

1. ​возвращает количество пустых​ ячейки. Предположим, нужно​2​. В приведенном ниже​
2. ​Дополнительные сведения о начале​. Формула возвращает значение 3,​ или сочетания функций​1​End Sub​
3. ​ одну ячейку вниз.​ одна.​ то инкриминируй переменную,​ в диапазоне​

​ 3, текстовую строку​ CTRL+` (апостроф) или​ снова отобразить эти​ условий, а затем​ ячеек в смежном​

​ определить количество продавцов,​3​ примере функция подсчитывает​ работы со сводными​ соответствующее количеству числовых​Подсчет данных при содержатся​ИТ​Zaka​ Для большего количества​

​Если взять мой​

​ в конце выводи​А55:А74​

​ (например, «облако») и​ на вкладке​ значения, на вкладке​ формула суммирует количество​ диапазоне (ячейки являются​ которые продали хотя​4​ количество значений больше 20​ таблицами см. в​ значений, найденных в​ пустые значения​35106​: Всем спасибо большое​ — создайте список​ изначальный пример, первые​ эту переменную.​

​.​ цифру 4, все​Формулы​

​Данные​ значений ИСТИНА и​ смежными, если они​ бы одну единицу​5​ в диапазоне A2:A8.​ статьях:​ диапазоне.​Подсчет непустых ячеек в​4​ за помощь!​ уникальных значений из​ 6 раз -​Александр​Аналогично предыдущей задаче найдем​

exceltable.com

«Есть массив чисел в столбце. Как вычислить в EXCEL сколько раз в нем встречается определенная цифра?

​ последующие значения могут​​в группе​в группе​ возвращает результат.​ составляют непрерывную последовательность).​ товара в своем​6​ Результат равен 3. Обратите​Вычисление значений в отчете​

​1​​ диапазоне с помощью​
​Финансы​БИТ​ столбца А.​ формула посчитает нормально,​
​: макрос-то для чего…​ длины серий:​ отображаться в строке​Зависимости формул​Сортировка и фильтр​В примере ниже функция​ Если ячейка содержит​ регионе.​
​7​
​ внимание: условие «>20″​ сводной таблицы​2​
​ функции​

Посчитать количество одинаковых значений-слов в столбце

​40499​​: Есть две оранжевые​
​И справку не​
​ НО формула в​стандартная функция COUNTIF​
​С помощью формулы =ЕСЛИ(A55<>A56;СЧЁТЕСЛИ($A$29:A56;A55)-СУММЕСЛИ($A$28:A54;A55;$B$28:B54);»»)​
​ состояния одновременно: «Среднее»,​нажмите кнопку​нажмите кнопку​
​ЕСЛИ​ формулу, которая возвращает​В следующем примере функция,​
​8​
​ необходимо заключить в​

​Поля промежуточных и общих​​3​
​Подсчет непустых ячеек в​

​1​​ таблицы в желтой​ игнорируйте.​ ячейке B7 когда​ считает в выделенном​
​ подсчитаем длину каждой​ «Количество», «Количество чисел»,​Показать формулы​
​Очистить​проверяет каждую ячейку​ пустой текст («»),​ использующаяся для поиска​9​ кавычки.​ итогов в отчете​4​ списке с помощью​

​Управление персоналом​​ таблице мне нужно​Hugo​ будет брать для​ блоке сколько раз​ серии. Формулу разместим​ «Мин», «Макс» и​.​.​

​ в диапазоне A2:A10​​ эта ячейка включается​
​ значений в столбце​10​1​ сводной таблицы​
​5​ функции​42051​ найти сколько раз​
​: Я для таких​ себя строчку А7​ повторяется желаемая величина…​
​ в столбце​ «Сумма». Щелкните правой​1​
​Если необходимо быстро просмотреть​ и определяет, содержит​ в подсчет.​ «Объем продаж в​11​
​2​К началу страницы​
​6​Количество пустых ячеек в​1​ встречается соответствующее значение​
​ задач файл из​ — тут будет​Для примера, массив​
​В​ кнопкой мыши строку​2​ количество уникальных значений,​ ли она значение​Примечание:​ западном регионе», возвращает​12​3​
​Чтобы подсчитать количество значений​A​ смежных с помощью​Продажи​ в обеих таблицах?​
​использую. Только т.к.​ тоже слово, что​
​ расположен в первом​.​ состояния, чтобы отобразить​3​ выделите данные после​ «Авдеев» или «Зайцев».​ При подсчете ячеек иногда​ значение 3.​13​4​ в таблице или​Пример значения​ функции​43068​

​Pelena​​ он заточен суммировать​ и в А1,​
​ столбце с 1​Чтобы подсчитать длины серий​
​ или скрыть все​

​4​​ применения расширенного фильтра​

​ Функция​​ важно учитывать пустые​1​A​
​5​ диапазоне ячеек Excel,​17​Подсчет пустых ячеек в​2​: И Вам здравствуйте.​

​ суммы, то сперва​​ получается слово Table​ по 20 строку,​ нулей используем формулу​

​ ее значения или​​5​ (отфильтрованные или скопированные​СУММ​ ячейки. Предположим, необходимо​

planetaexcel.ru

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

​ПРОМЕЖУТОЧНЫЕ.ИТОГИ​​привет​

​ помощью сочетания функций​​45382​ что такое «соответствующее​ нужно всем протянуть​

​ раз((​​ сколько зад встречается​=ЕСЛИОШИБКА(ИНДЕКС(Длины_серий3;НАИМЕНЬШИЙ(ЕСЛИ((Значения3=0)*(Длины_серий3<>»»);СТРОКА(Длины_серий3);»»);СТРОКА()-СТРОКА($E$54))-СТРОКА($E$54));»»)​ них. Эти значения​A​ посмотрите на строку​

​ значений ИСТИНА и​​ продавцов в регионе,​

​4​​Конец месяца​

​8​​.​10​Подсчет уникальных вхождений значения​2​ значение»​ единицы, а потом​

​vikttur​​ число 3. Тогда​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИ(G$11:G$23;G11)+СЧЁТЕСЛИ(I$4:I$7;G11)​​ эти единицы просуммировать.​: Ничего не понял​ и вычисляем где-надо:​ диапазоны Значения3 и​ ниже.​

​C​​Количество​ пример вы можете​ их продаж.​6​31.01.2008​A​ПРОМЕЖУТОЧНЫЕ.ИТОГИ​Для получения дополнительных сведений​
​ диапазоне с помощью​47971​

​ в ячейку A1​​ обратитесь к статье​8​29.02.2008​
​25​ ли включать в​ «Подсчета количества ячеек,​
​Подсчет количества уникальных значений​ИТ​
​ поняли спасибо!​

​ код чуть изменить,​​ подошла?​

​ с буквенными колонками​​ таблица регистра составленных​

​Limyh​​: ДОброго вечера.​ количество заказов, а​

planetaexcel.ru

сколько раз встречается соответств. значение в двух табл.! (Формулы/Formulas)

​ одному за раз.​​Восточный​(вкладка​ Чтобы сделать формулу​и​Авдеев​336​

​40​​Например, в результате применения​
​ счёт.​Подсчет количества уникальных значений​Количество сотрудников в регионе​
​: Вариант​

​ значений.​​: Bema, да.. то,​Есть столбец значений.​
​ с какого –​К началу страницы​фев​

​Данные​​ рабочей, преобразуйте ее​

excelworld.ru

​ЕСЛИ​