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

Имеется таблица:

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

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

Но если по каким-то причинам сводная не Ваш вариант - в Excel имеется функция СЧЁТЕСЛИ(COUNTIF), при помощи которой все это можно сделать тоже буквально за секунды. Если количество повторений каждого наименования необходимо вывести в столбец В таблицы, а сами наименования расположены в столбце А:
=СЧЁТЕСЛИ($A$2:$A$30;A2)
=COUNTIF($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")

Подсчитать числа, которые больше нуля:=СЧЁТЕСЛИ($A$2:$A$30;">0")
Подсчитать количество непустых ячеек: =СЧЁТЕСЛИ($A$2:$A$30;"")

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

Если необходимо подсчитать количество повторений на основании нескольких условий(значений), то начиная с 2007 Excel это легко можно сделать при помощи функции

. Синтаксис функции почти такой же, как у

, только условий и диапазонов больше:

По сути идет просто перечисление:

Так же не могу не написать про небольшую особенность функции

(а так же

,

,

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

, которая к типам относится очень бережно и ничего не преобразует). Что это значит. Если у нас в ячейке записано число

- оно будет воспринято как число. Если тоже число будет записано как текст -

, то функция преобразует его сначала в число 23, а потом уже будет работать с ним. Т.е. и 23 и "023" у нас будут считаться одинаковым значением. Иными словами: если значение можно преобразовать в число - оно будет преобразовано в число и функция будет работать уже именно с этим числом.
С одной стороны это хорошо, но иногда такое поведение может сыграть злую шутку. Например, у нас в ячейках расположены некие номера счетов, длина которых более 15-ти символов и могут иметь ведущие нули:

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

и если записать функцию так:

, то она вернет значение 3. Особо обращаю внимание на тот факт, что все числа после 15-го знака будут преобразованы в нули. Эти особенности всегда необходимо учитывать при использовании данных функций, чтобы не попасть в неловкую ситуацию, когда подсчет будет некорректным.

Еще один вариант подсчета значений. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке(

):

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

- подсчитывает количество символов в указанной ячейке/строке(

)

- заменяет в указанном тексте заданный символ на любое другое заданное значение. По умолчанию заменяет все повторы указанного символа. Именно это и положено в основу алгоритма. На примере значения Банан(

) пошаговый разбор формулы:

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

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

Но тут есть и более каверзная ситуация - когда у нас диапазон ячеек, в каждой из которых наше слово может встречаться более одного раза. И подсчитать надо ВСЕ повторения. Диапазон для подсчета повторений у нас будет в ячейках A1:A10. Слово для подсчета повторений запишем в ячейку B1(там будет все тоже слово "банан"):

Базируясь на формуле выше можно написать такую:
=СУММПРОИЗВ((ДЛСТР(A1:A10)-ДЛСТР(ПОДСТАВИТЬ(A1:A10;B1;"")))/ДЛСТР(B1))
=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,B1,"")))/LEN(B1))

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

Function GetRepeat(sTxt As String, sCntWord As String) GetRepeat = (Len(sTxt) - Len(Replace(sTxt, sCntWord, ""))) / Len(sCntWord) End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA(Alt+F11) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций(Ctrl+F3), отыскав её в категории Определенные пользователем (User Defined Functions).
Синтаксис функции:
=GetRepeat($D$1;D3)
sTxt - текст, в котором подсчитываем кол-во вхождения.
sCntWord - текст для подсчета. Может быть символом или словом.

Скачать пример

  Пример Подсчета повторений.xls (70,5 KiB, 13 249 скачиваний)

Так же см.:
Что такое формула?
Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям

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

Поиск по меткам

Accessapple watchMultexPower Query и Power BIVBA управление кодамиБесплатные надстройкиДата и времяЗапискиИПНадстройкиПечатьПолитика КонфиденциальностиПочтаПрограммыРабота с приложениямиРазработка приложенийРосстатТренинги и вебинарыФинансовыеФорматированиеФункции Excelакции MulTExссылкистатистика

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

Excel

Формулы и функции

Формулы

Формулы

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Дополнительно. ..Меньше

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

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

• Если столбец содержит "Бьюкенен", "Додсворт", "Додсворт" и "Додсворт", то "Додсворт" встречается три раза.

Существует несколько способов подсчета частоты появления значения.

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

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

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

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

СЧЁТЕСЛИМН (критерий_диапазон1, критерий1, [критерий_диапазон2, критерий2],…)

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

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

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

Примечания:

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

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

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

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

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

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

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

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

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

Пример 1

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

Пример 2

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

Пример 3

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

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

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

2. Выберите A2:C8

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

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

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

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

   1. Перетащите Sport в область Rows .

   2. Драг Квартал в область столбцов .

   3. Перетащите Sales в область Values ​​.

   4. Повторить шаг c.

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

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

   5. В области Values ​​ щелкните раскрывающийся список рядом с SumofSales2 и выберите Параметры поля значений .

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

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

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

      3. Щелкните OK .

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

Нужна дополнительная помощь?

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

См. также

Обзор формул в Excel

Как избежать неработающих формул

Найдите и исправьте ошибки в формулах

Сочетания клавиш и функциональные клавиши Excel

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

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

Как подсчитать повторяющиеся значения в Excel

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

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

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

Чтобы найти количество повторяющихся оценок, включая первое вхождение:

• Перейти к ячейке F2 .
• Назначение формулы =СЧЁТЕСЛИ($C$2:$C$8,E2) .
• Нажмите Введите .
• Перетащите формулу из F2 в F4.

Теперь у вас есть количество повторяющихся оценок в столбце E.

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

• Выберите ячейку F2 .
• Назначение формулы =СЧЁТЕСЛИ($C$2:$C$8,E2)-1 .
• Нажмите Enter, чтобы применить формулу.

Это покажет количество повторяющихся значений без первого экземпляра в столбце E.

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

• Перейти к ячейке F2 .
• Присвоить формулу =СУММ(--EXACT($C$2:$C$8,E2)) .
• Нажмите Ctrl + Shift + Enter , чтобы применить формулу как формулу массива.

Формула EXACT выполняет сравнение с учетом регистра значений в столбце D с оценками от C2 до C8. В результате получается массив логических значений ИСТИНА и ЛОЖЬ. Унарный оператор (-) преобразует значения в массив из 0 и 1. Затем функция СУММ суммирует эти записи, чтобы найти количество повторяющихся значений.

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

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

• Выберите ячейку F2 , нажав на нее.
• Назначьте формулу =СЧЁТЕСЛИМН($A$2:$A$8,A2,$B$2:$B$8,B2,$C$2:$C$8,C2) для F2.
• Нажмите Введите .

Перетащите формулу в ячейки ниже с помощью мыши.

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

• Перейдите в ячейку B2 , нажав на нее.
• Назначьте формулу =ЕСЛИ(СЧЕТЕСЛИ($A$2:A2,A2)>1,"Да","") ячейке B2.
• Нажмите Введите . Это покажет значение Да, если запись в A2 является повторяющейся записью.
• Перетащите формулу вниз с B2 на B8 .
• Выберите ячейку B9.
• Назначьте формулу =СЧЁТЕСЛИ(B2:B8;"Да") ячейке B9.
• Нажмите Введите .

Это покажет общее количество повторяющихся значений в столбце A без первого вхождения.

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

• Выберите ячейку D4.
• Назначение формулы =СТРОКИ($A$2:$A$8)-СУММ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$8,$A$2:$A$8) =1,1,0)) в ряд В2.
• Нажмите Ctrl + Shift + Enter .

  ---

  Learn more

  • 
    Топ mp3 плееров
  • 
    Наушники i12 как пользоваться
  • 
    Кому пригодится статистика по данным компьютера
  • 
    Не могу подключить беспроводные наушники к телефону
  • 
    Как в биосе включить мышку
  • 
    Ошибка входа в аккаунт сессия устарела
  • 
    Карты скидок в телефоне
  • 
    Как подключить джойстик от пс3 к пк
  • 
    Номер виртуального счета google play
  • 
    Приложение записывающее телефонный разговор на андроид
  • 
    Создание usb windows 10