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

Подсчет уникальных значений в 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

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

Здравствуй уважаемый пользователь!

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

Читайте также:  Как в excel найти средневзвешенное значение

Список уникальных значений возможно создать 6-ю способами:

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

Это очень простой способ для владельцев Excel выше 2007 версии как произвести отбор уникальных значений. Вам нужно на вкладке «Данные», в разделе «Работа с данными», использовать специальную команду «Удалить дубликаты».

В появившемся диалоговом окне «Удалить дубликаты», вы выделяете те столбики, где необходимо произвести отсев уникальных значений и нажимаете «Ок». В случае, когда в выделенном диапазоне размещается и заголовок таблицы, то поставьте галочку на пункте «Мои данные содержат заголовки», что бы вы случайно не удалили данные. Внимание! Когда вы будете производить отсев уникальных значений в таблице, где столбиков больше 2 и они взаимосвязаны информацией, Excel предложит вам расширить диапазон выбора, с чем вы должны, согласится, иначе будет нарушена логическая связь с другими столбиками.

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

Это также не сложный способ произвести отбор уникальных значений в таблице. Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно». Огромный плюс этого способа в том, что вы можете создать новый список уникальных значений в другом месте. После появления диалогового окна «Расширенный фильтр», устанавливаем галочку напротив пункта «Скопировать результат в другое место», потом указываем диапазон с вашими данными в поле «Исходный диапазон», при необходимости указываем критерий отбора, но для общего отсева поле оставляем пустым «Диапазон критериев», в третьем поле «Поместить результат в диапазон» указываем первую ячейку куда будут помещаться наши данные, отмечаем галочкой пункт «Только уникальные записи» и нажимаем «Ок». Если же вам не нужно никуда переносить ваши данные, то просто установите флажок для пункта «Фильтровать список на месте», данные не пострадают, произойдет наложение обыкновенного фильтра.

Внимание! Если программа запрещает вам переносить отфильтрованные данные на другой лист, вы просто запустите «Расширенный фильтр» на том листе, куда вам надо перенести отобранные уникальные значения.

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

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

Пример 1. Вам нужно пронумеровать, уникальные, значение в списке значений, для этого нужно использовать функцию ЕСЛИ в формуле следующего вида:

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;”«)

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

=ЕСЛИ(МАКС(A1:A100)

С ростом богатства растут и заботы. Гораций

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Как в графике excel изменить значения оси

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

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

В пустой ячейке, расположенной под последней ячейкой диапазона, введите функцию строки . Используйте диапазон уникальных значений, которые вы только что скопировали в качестве аргумента, исключая заголовки столбцов. Например, если диапазон уникальных значений — 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.

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

Как посчитать количество уникальных значений в 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 функций:

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

Читайте также:  Как в excel удалить

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

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

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

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

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

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

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

Функция СЧЁТЕСЛИ и подсчет количества значения ячейки в Excel

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

Функция СЧЁТЕСЛИ может быть использована для анализа числовых значений, текстовых строк, дат и данных другого типа. С ее помощью можно определить количество неповторяющихся значений в диапазоне ячеек, а также число ячеек с данными, которые совпадают с указанным критерием лишь частично. Например, таблица Excel содержит столбец с ФИО клиентов. Для определения количества клиентов-однофамильцев с фамилией Иванов можно ввести функцию =СЧЁТЕСЛИ(A1:A300;”*Иванов*”). Символ «*» указывает на любое количество любых символов до и после подстроки «Иванов».

Примеры использования функции СЧЁТЕСЛИ в Excel

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

Вид исходной таблицы данных:

Для расчета используем формулу:

  • C3:C17 – диапазон ячеек, содержащих названия фирм проданной техники;
  • “Samsung” – критерий поиска (точное совпадение);
  • A17 – ячейка, хранящая номер последней продажи, соответствующий общему числу продаж.

Доля проданной продукции техники фирмы Samsung в процентах составляет – 40%.

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

Пример 2. По итогам сдачи экзаменов необходимо составить таблицу, в которой содержатся данные о количестве студентов, сдавших предмет на 5, 4, 3 балла соответственно, а также тех, кто не сдал предмет.

Вид исходной таблицы:

Предварительно выделим ячейки E2:E5, введем приведенную ниже формулу:

  • B3:B19 – диапазон ячеек с оценками за экзамен;
  • D2:D5 – диапазон ячеек, содержащих критерии для подсчета числа совпадений.

В результате получим таблицу:

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

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

Вид исходной таблицы:

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

В результате расчета получим:

Особенности использования функции СЧЁТЕСЛИ в Excel

Функция имеет следующую синтаксическую запись:

=СЧЕТЕСЛИ( диапазон; критерий )

  • диапазон – обязательный аргумент, принимающий ссылку на одну либо несколько ячеек, в которых требуется определить число совпадений с указанным критерием.
  • критерий – условие, согласно которому выполняется расчет количества совпадений в рассматриваемом диапазоне. Условием могут являться логическое выражение, числовое значение, текстовая строка, значение типа Дата, ссылка на ячейку.
  1. При подсчете числа вхождений в диапазон в соответствии с двумя различными условиями, диапазон ячеек можно рассматривать как множество, содержащее два и более непересекающихся подмножеств. Например, в таблице «Мебель» необходимо найти количество столов и стульев. Для вычислений используем выражение =СЧЁТЕСЛИ(B3:B200;”*стол*”)+СЧЁТЕСЛИ(B3:B200;”*стул*”).
  2. Если в качестве критерия указана текстовая строка, следует учитывать, что регистр символов не имеет значения. Например, функция СЧЁТЕСЛИ(A1:A2;”Петров”) вернет значение 2, если в ячейках A1 и A2 записаны строки «петров» и «Петров» соответственно.
  3. Если в качестве аргумента критерий передана ссылка на пустую ячейку или пустая строка «», результат вычисления для любого диапазона ячеек будет числовое значение 0 (нуль).
  4. Функция может быть использована в качестве формулы массива, если требуется выполнить расчет числа ячеек с данными, удовлетворяющим сразу нескольким критериям. Данная особенность будет рассмотрена в одном из примеров.
  5. Рассматриваемая функция может быть использована для определения количества совпадений как по одному, так и сразу по нескольким критериям поиска. В последнем случае используют две и более функции СЧЁТЕСЛИ, возвращаемые результаты которых складывают или вычитают. Например, в ячейках A1:A10 хранится последовательность значений от 1 до 10. Для расчета количества ячеек с числами больше 3 и менее 8 необходимо выполнить следующие действия:
  • записать первую функцию СЧЁТЕСЛИ с критерием «>3»;
  • записать вторую функцию с критерием «>=8»;
  • определить разницу между возвращаемыми значениями =СЧЁТЕСЛИ(A1:10;”>3″)-СЧЁТЕСЛИ(A1:A10;”>=8″). То есть, вычесть из множества (3;+∞) подмножество [8;+∞).

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