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

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

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

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

Значения 5, 6, 7 и 6 выводятся в виде трех уникальных значений: 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 является вторым элементом диапазона.

Читайте также:  Учет заказов в excel

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

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

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

Дополнительные сведения

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

Источник: support.office.com

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

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

Количество уникальных значений в списке

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

Если применить такую формулу для массива с пустыми значениями, она вернет ошибку #ДЕЛ/0. Как это обойти – читайте дальше.

Считаем данные в массиве с пустыми ячейками

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

Легко заметить, что результат получился на 1 больше, чем в прошлом примере. Это потому, что формула посчитала и пустую ячейку, как уникальные данные. Чтобы этого не было, еще доработаем функцию:

Теперь получили правильный результат без учёта пустот.

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

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

Чтобы в Excel посчитать количество уникальных значений в столбце с числами, используем функцию ЧАСТОТА :

Количество уникальных строк с текстом

Похожая «скоростная» но для счёта текстовых строк:

=СУММПРОИЗВ(—(ЧАСТОТА(ПОИСКПОЗ(диапазон; диапазон;0);СТРОКА(диапазон)-СТРОКА(диапазон.первая ячейка)+1)>0))

Такая формула не подходит, когда в массиве есть пустые клетки. Если это критично, можно еще усложнить:

=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(диапазон<>»»;ПОИСКПОЗ(диапазон; диапазон;0));СТРОКА(диапазон)-СТРОКА(диапазон.первая ячейка)+1);1))

Это формула массива, после ввода нажимайте не Enter , а Ctrl+Shift+Enter . Иначе работать не будет.

Выглядит монструозно, а результат верный. Можно пользоваться!

Пересчёт числовых значений с условием

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

Формула получилась такая:

Это формула массива, подтверждаем ввод комбинацией Ctrl+Shift+Enter !

Счёт текстовых строк по условию

Похожее задание, но теперь нужно считать, сколько менеджеров продавали товар с кодом 1001.

А формула такая:

=СУММ(—(ЧАСТОТА(ЕСЛИ(диапазон<>»»;ЕСЛИ(условие;ПОИСКПОЗ(диапазон; диапазон;0)));СТРОКА(диапазон)-СТРОКА(диапазон.первая ячейка)+1)>0))

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

Это всё на сегодня, Задавайте ваши вопросы в комментариях. Понравилась статья – поделись с другом!

Источник: officelegko.com

Подсчет Уникальных ТЕКСТовых значений в EXCEL

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

Сначала поясним, что значит подсчет уникальных значений. Пусть имеется массив текстовых значений <"а","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)) . Разница между формулами состоит в том, что вторая формула учитыват значения Пустой текст (“”) , а первая их игнорирует.

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

Пусть дана таблица продаж товаров (см. рисунок ниже, столбцы А и В). С помощью формулы =ЕСЛИ(МЕСЯЦ(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 .

Источник: excel2.ru

Как посчитать количество уникальных значений в Excel?

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

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

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

Допустим значения содержатся в диапазоне С2:С9

Водим диапазон E2:E9 в ячейках которого содержится количество повторов каждого значения в диапазоне С2:С9

Формула количества повторов для значения C2 =СЧЁТЕСЛИ($C$2:$C$9;C2)

Эта формула растягивается на весь диапазон

Количество уникальных значений определится по формуле =СУММЕСЛИ(E2:E9;”=1″)

Чтобы увидеть уникальные элементы нужно наложить фильтр на столбец E

Если возникла задача посчитать количество уникальных значений в Экселе (Excel), то её можно решить несколькими способами – с формулами и без формул.

1) В том случае, если диапазон данных (числовых или текстовых) является статичным и меняться не будет, то можно воспользоваться командой “Удалить дубликаты”.

Для этого выделяем диапазон (в нашем примере – это B1:B14) и на панели инструментов Excel выбираем “Данные” -> “Удалить дубликаты”.

Появится окно, в котором указываются столбец / диапазон, из которых удаляются дубликаты.

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

Теперь нажимаем на кнопку “ОК”.

В результате Эксель выведет сообщение о том, сколько найдено дубликатов и сколько осталось уникальных значений.

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

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

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

Далее с помощью функции СУММ суммируются дроби вида:

1 / Число вхождений каждого элемента.

Для нашего примера формула будет выглядеть так:

При этом вводить её нужно как формулу массива – через комбинацию Ctrl + Shift + Enter.

В результате получится всё те же 10 уникальных значений.

А вот примеры работы этой формулы, если в таблице Excel имеются текстовые данные:

Источник: www.bolshoyvopros.ru

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

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

Однако, тем, кто знаком с функциональностью сводных таблиц, известны их проблемы:

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

А что, если стоит простая задача — посчитать уникальные ячейки диапазона и вывести списком? Или сделать простую сводку по одному-двум столбцам? Просто, чтобы в моменте взглянуть на данные и посмотреть, что получится, для принятия быстрых решений?

Читайте также:  Как в экселе округлить число

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

Поэтому в !SEMTools родился инструмент «Быстрая сводная таблица». Он делает лишь одно действие — создание сводной таблицы с суммированием, но делает ее максимально быстро, не задавая лишних вопросов.

Как посчитать количество ячеек с одинаковым значением в Excel

Подсчет количества ячеек с одинаковым текстом с помощью !SEMTools. В примере в ячейках по 1 букве, но в реальности могут быть какие угодно повторяющиеся данные. Макрос объединяет и одновременно суммирует одинаковые ячейки, выводя на отдельный лист их количество.

Объединение одинаковых ячеек в excel с суммированием остальных столбцов

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

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

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

Быстрая сводная таблица по разным группам через суммирование результатов каждого элемента группы

Источник: semtools.guru

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

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

Однако, тем, кто знаком с функциональностью сводных таблиц, известны их проблемы:

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

А что, если стоит простая задача — посчитать уникальные ячейки диапазона и вывести списком? Или сделать простую сводку по одному-двум столбцам? Просто, чтобы в моменте взглянуть на данные и посмотреть, что получится, для принятия быстрых решений?

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

Поэтому в !SEMTools родился инструмент «Быстрая сводная таблица». Он делает лишь одно действие — создание сводной таблицы с суммированием, но делает ее максимально быстро, не задавая лишних вопросов.

Как посчитать количество ячеек с одинаковым значением в Excel

Подсчет количества ячеек с одинаковым текстом с помощью !SEMTools. В примере в ячейках по 1 букве, но в реальности могут быть какие угодно повторяющиеся данные. Макрос объединяет и одновременно суммирует одинаковые ячейки, выводя на отдельный лист их количество.

Объединение одинаковых ячеек в excel с суммированием остальных столбцов

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

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

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

Быстрая сводная таблица по разным группам через суммирование результатов каждого элемента группы

Источник: semtools.guru