Функция подсчета уникальных значений в excel

Про подсчет уникальных текстовых и числовых значений (без критерий) можно прочесть в статье Подсчет Уникальных ТЕКСТовых значений в MS EXCEL и Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL. В данной нам статье разглядим наиболее сложные варианты с критериями.

Задача1

Пусть имеется таблица с списком продаж по торговцам.

Требуется подсчитать сколько разных продуктов продал определенный торговец. К примеру, Вася продал 1 товар1, 2 Товара2 и 1 Товар4 (выделено зеленоватым). Всего 3 различных продукта.

Это можно подсчитать формулой =СУММПРОИЗВ((A13:A21=A7)/СЧЁТЕСЛИМН(B13:B21;B13:B21;A13:A21;A13:A21)) , которая будет работать лишь с версии MS EXCEL 2007 из-за функции СЧЁТЕСЛИМН() .

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

Задача2

Аналогичным образом можно решить задачку с 2-мя критериями.

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

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

Задача3

Сейчас разглядим другую таблицу (столбцы А:С на рисунке ниже).

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

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

Сделаем доп столбец для определения месяца заключения контракта (см. статью Заглавие месяца прописью в MS EXCEL). Выведем из этого столбца лишь неповторимые месяцы (см. статью Отбор уникальных значений (убираем повторы из перечня) в MS EXCEL) и поместим их в столбец F.

И, в конце концов, помощью формулы =СУММПРОИЗВ(($D$9:$D$26=F9)/СЧЁТЕСЛИ($A$9:$A$26;$A$9:$A$26)) подсчитаем количество уникальных договоров в соответственном месяце.

Решение также может быть при помощи Сводной таблицы.

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

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

Значения 5, 6, 7 и 6 выводятся в виде 3-х уникальных значений: 5, 6 и 7.

Значения "Кирилл", "Сергей", "Сергей", "Сергей", итог — это два уникальных значения — "Кирилл" и "Сергей".

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

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

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

Удостоверьтесь, что спектр ячеек содержит заголовок столбца.

На вкладке Данные в группе Сортировка и фильтр нажмите клавишу Добавочно.

Раскроется диалоговое окно " Расширенный фильтр ".

Изберите команду скопировать в другое пространство.

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

Вы также сможете надавить клавишу Свернуть диалоговОе окно , чтоб временно скрыть диалоговое окно, выделить ячейку на листе и надавить клавишу Развернуть диалоговое окно .

Установите флаг лишь неповторимые записи и нажмите клавишу ОК.

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

В пустой ячейке, расположенной под крайней ячейкой спектра, введите функцию строчки . Используйте спектр уникальных значений, которые вы лишь что скопировали в качестве аргумента, исключая заглавия столбцов. К примеру, если спектр уникальных значений — B2: B45, вы вводите = Rows (B2: B45).

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

Назначьте значение 1 любому из настоящих критерий при помощи функции ЕСЛИ.

Добавьте результат при помощи функции сумм .

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

Возвращает размещение текстового значения в спектре при помощи функции ПОИСКПОЗ . Возвращаемое значение потом употребляется в качестве аргумента функции FREQUENCY , чтоб можно было оценивать надлежащие текстовые значения.

Поиск пустых ячеек при помощи функции Len . Длина пустых ячеек равна 0.

Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Office 365, можно просто ввести формулу в верхней левой ячейке спектра вывода и надавить кнопку ВВОД, чтоб подтвердить внедрение формулы динамического массива. По другому формулу нужно вводить с внедрением прежней версии массива, выбрав спектр вывода, введя формулу в левой верхней ячейке спектра и нажав клавиши CTRL+SHIFT+ВВОД для доказательства. Excel автоматом вставляет фигурные скобки в начале и конце формулы. Доп сведения о формулах массива см. в статье Внедрение формул массива: советы и примеры.

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

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

Функция ПОИСКПОЗ производит поиск определенного элемента в спектре ячеек и возвращает относительное размещение этого элемента в спектре. К примеру, если в спектре a1: A3 содержатся значения 5, 25 и 38, функция формула = Match (25; a1: A3; 0) возвращает число 2, потому что значение 25 является вторым элементом спектра.

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

Функция СУММ вычисляет сумму всех чисел, обозначенных в качестве аргументов. Любой аргумент быть может спектром, ссылкой на ячейку, массивом, константой, формулой либо результатом иной функции. К примеру, функция сумм (a1: A5) складывает все числа, содержащиеся в ячейкАх от a1 до A5.

Функция Если возвращает одно значение, если обозначенное условие имеет значение true, и другое, если условие имеет значение false.

Доп сведения

Вы постоянно сможете задать вопросец спецу Excel Tech Community, попросить помощи в обществе Answers community, также предложить новейшую функцию либо улучшение на сайте Excel User Voice.

См. также

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

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

Поначалу поясним, что означает подсчет уникальных значений. Пусть имеется массив текстовых значений <"а","b","а","а","а">. При подсчете уникальных игнорируются все повторы, т.е. значения выделенные жирным. Соответственно, подсчитываются другие значения, т.е. "а" и "b". Ответ предельно ясен: количество уникальных значений равно 2.

Задачка

Произведем подсчет числа уникальных текстовых значений в спектре A7:A15 (см. файл примера ). Спектр может содержать пустые ячейки.

Решение

Запишем формулу =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15))

Если в спектре не считая текстовых значений содержатся также и числа, то формула подсчитает и их. Чтоб игнорировать числовые значения необходимо записать формулу =СУММПРОИЗВ(ЕТЕКСТ( A7:A15 )/СЧЁТЕСЛИ(A7:A15;A7:A15))

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

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

Примечание: Неповторимые значения в файле примера выделены при помощи Условного форматирования (см. статью Выделение уникальных значений в MS EXCEL).

Нередко заместо формулы =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15)) употребляют наиболее ординарную формулу =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) . Разница меж формулами состоит в том, что 2-ая формула учитыват значения Пустой текст (""), а 1-ая их игнорирует.

Приведем пример, когда это бывает принципиально.

Пусть дана таблица продаж продуктов (см. набросок ниже, столбцы А и В). При помощи формулы =ЕСЛИ(МЕСЯЦ(B26)=1;A26;"") определяются продукты, которые были проданы в январе. Если продукт продан не в январе, то формула возвращает значение Пустой текст. Юзер решает подсчитать количество уникальных продуктов в январе (их всего 3: Товар1, Товар2 и Товар3).

Формула =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15)) возвратит верный итог 3, а формула =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) возвратит 4, т.к. в "пустых" ячейках С31:С34 по сути содержатся 4 значения "", которые воспринимаются ей как некоторое текстовое значение, хотя и нулевой длины.

СОВЕТ: Как подсчитать неповторимые числовые значения показано в одноименной статье Подсчет уникальных числовых значений.

СОВЕТ: Как подсчитать неповторимые числовые значения с доп критериями (аспектами) показано в статье Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL.

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

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

1. Подсчитайте неповторимые значения, используя Функция Sum и Countif.
2. Подсчитайте неповторимые значения, используя СУММПРОИЗВ и функция Countif.

Сейчас давайте тщательно обсудим любой из способов вкупе с примером —

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

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

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

• Шаг 1: Скопируйте приведенные ниже данные в собственный файл Excel.

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

Сейчас разрешите мне тщательно выложить формулу.

Я употреблял тут последующую формулу:

Если закрыть формулу, она будет заключена в фигурные скобки. Я не вошел сюда; быстрее, это признак того, что это формула массива.

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

Ctrl + Shift + Enter это автоматом введет эти фигурные скобки.

Это мало новенькая формула, если вы в первый раз работаете с формулами массива в Excel. Разрешите мне разбить формулу на три части.

Поначалу я объясню формулу СЧЁТЕСЛИ. Изберите часть формулы счета и нажмите F9.

Сейчас нажмите кнопку F9.

Сейчас мы делим обозначенные выше значения на цифру 1.

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

Как работает расчет?

• Если значения находятся в перечне дважды, то это ½, т.е. 0,5. Если значение находится в перечне три раза, то оно равно 1/3, т.е. 0,3333.
• В нашем перечне 1-ое имя — Рутурадж, которое встречается в перечне трижды, потому наш итог указывает 0,33333333 в качестве значения.
• Наше 2-ое имя Камаль встречается лишь один раз, и формула читается так: 1/1 I .e.1 лишь.
• Эти функции СЧЁТЕСЛИ и СУММ могут отдать нам общее количество уникальных значений в перечне.

# 2 Подсчет уникальных значений при помощи функции СУММПРОИЗВ и СЧЁТЕСЛИ

Возьмите те же данные из предшествующего примера.

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

• Шаг 1: Скопируйте приведенные ниже данные в собственный файл Excel.

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

Сейчас разрешите мне тщательно выложить формулу.

Я употреблял тут последующую формулу:

В нашем прошлом примере я употреблял формулу Array, т.е. закрыл формулу при помощи Ctrl + Shift + Enter.

Если я сломаю формулу при помощи клавиши F9, она будет работать буквально так же, как и предшествующая.

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

Обработка заготовок в спектре

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

В строке изображения выше номер 10 — это пустая строчка. Так как имеется пустая строчка, формула возвратила итог как ошибку, т.е. # DIV / 0 !.

Мы можем совладать с таковыми ошибками, не вставляя в их ничего («»).

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

Примечание: если есть две пустые строчки, мы можем применять -2; если есть три пустые строчки, мы можем применять -3 и так дальше.