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?

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

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

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

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

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

Читайте также:  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 является вторым элементом диапазона.

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

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

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

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

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

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

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

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

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

Читайте также:  Как в excel разделить текст по столбцам

Задача

Произведем подсчет числа уникальных текстовых значений в диапазоне 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 — отличный инструмент с широчайшими возможностями по анализу данных. Он позволяет в реальном времени смотреть на данные в разных разрезах, фильтровать данные и создавать новые вычисляемые поля. Сводным таблицам посвящены множество курсов и руководств.

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

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

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

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

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

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

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

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

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

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

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

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

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