Как вытащить уникальные значения excel из столбца

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

Скачать файл с примером можно по ссылке.

Вам также могут быть интересны следующие статьи

• Четыре способа использования ВПР с несколькими условиями
• Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в Excel
• Функции СЛЧИС и СЛУЧМЕЖДУ Excel на примере создания имитации игральной кости
• Как отфильтровать список элементов из длинного-длинного списка
• Функция СЖПРОБЕЛЫ в Excel с примерами использования
• Несколько условий ЕСЛИ в Excel
• Расширенный фильтр в Excel с символами подстановки и уникальными значениями на VBA
• Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
• Как расчитать дисперсию в Excel с помощью функции ДИСП.В
• Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel

Рубрика: Формулы | Метки: фильтрация, формулы массивов | 21 комментарий | Permalink

21 комментарий

У вас описание так сформулировано, что я подумал, что вы ищите слова, которые ни разу не повторились в оригинальном перечне. А оказалось, вы составляете список значений избавленный от дублей. Т.е. список без повторов. Я уже порывался написать, что формула не работает, пока не понял, про что эта статья.

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

Добрый день. Очень нужная формула но с цифрами не работает.

Спасибо большое за полезную формулу!
Подскажите, пожалуйста, как можно доработать данную формулу так, чтобы она считала слова в разных регистрах разными, а не дублями одного и того же?

Формула не рабочая

Все работает, кавычки замените!
Подскажите, что надо изменить что бы работала для горизонтальных строк данных, вместо вертикальных

А если нужно создать список уникальных ячеек с диапазона, например B2:H15 ?
формула массива — это фантастика. Большое спасибо!

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

извините, но формула не рабочая: вручную вначале набирала, потом в соседний столбец копировала ее, в конце концов скачала заявленный файл — и ничего.

Вывод отобранных значений в отдельный диапазон в Excel. Бесплатные примеры и статьи.

Запрос на выборку данных (формулы) в MS EXCEL

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра ). Произведем отбор значений из исходной таблицы с помощью формул массива . В …

update Опубликовано: 13 апреля 2013

Запрос на выборку данных в MS EXCEL (на основе элементов управления формы)

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

update Опубликовано: 21 января 2013

Отбор повторяющихся значений в MS EXCEL

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

update Опубликовано: 22 апреля 2013

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть3. Поиск с учетом РЕГИСТРА

Найдем текстовые значения, удовлетворяющие заданному пользователем критерию с учетом РЕгиСТРА. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для …

update Опубликовано: 14 апреля 2013

Отбор уникальных значений (убираем повторы из списка) в MS EXCEL

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

update Опубликовано: 22 апреля 2013

Отбор уникальных значений из двух диапазонов в MS EXCEL

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

update Опубликовано: 22 апреля 2013

Отбор неповторяющихся значений из двух диапазонов в MS EXCEL

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

update Опубликовано: 22 апреля 2013

Отбор неповторяющихся значений со значениями из соседнего столбца в MS EXCEL

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

update Опубликовано: 22 апреля 2013

Отбор уникальных значений в MS EXCEL с условиями

Продолжим идеи, изложенные в статье Отбор уникальных значений в MS EXCEL . Сначала отберем из таблицы только те строки, которые удовлетворяют заданным условиям, затем из этих строк выберем только уникальные …

update Опубликовано: 13 января 2015

Вывод итогов столбцов по строкам в MS EXCEL

Пусть имеется 5 столбцов с данными. В каждом столбце по 10 чисел, необходимо найти сумму чисел в каждом столбце. Обычно итоговое значение выводится внизу столбца или над его заголовком, поэтому …

update Опубликовано: 21 мая 2016

Удаляем в MS EXCEL пропуски в списке формулами (удалить пустые ячейки и нули в таблице)

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

update Опубликовано: 24 апреля 2013

Динамическое разнесение данных по столбцам

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

update Опубликовано: 19 октября 2014

Отбор неповторяющихся значений в MS EXCEL

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

update Опубликовано: 22 апреля 2013

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск

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

update Опубликовано: 14 апреля 2013

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть2. Подстановочные знаки

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

update Опубликовано: 14 апреля 2013

Пять последних значений в MS EXCEL

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

update Опубликовано: 16 апреля 2013

Отбор повторяющихся значений (дубликатов) со значениями из соседнего столбца в MS EXCEL

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

update Опубликовано: 22 апреля 2013

Поиск нескольких значений с максимальной длиной строки в MS EXCEL

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

update Опубликовано: 16 апреля 2013

Поиск ЧИСЛОвых значений и вывод соответствующих значений в отдельный список в MS EXCEL

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

update Опубликовано: 16 апреля 2013

Отбор уникальных значений с сортировкой в MS EXCEL

Из исходной таблицы отберем только уникальные значения и выведем их в отдельный диапазон с сортировкой по возрастанию. Отбор и сортировку сделаем с помощью одной формулой массива. Формула работает как для …

Как извлечь уникальные значения из списка

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

Решение: предположим, что рабочий лист содержит список имен в диапазоне А2:А30 (рис. 1). Ячейка A1 содержит заголовок столбца. Вы можете решить эту проблему вручную, пройдя по меню: ДАННЫЕ –> Сортировка и фильтр –> Дополнительно. Откроется диалоговое окно Расширенный фильтр (рис. 2). Выберите опцию Скопировать результат в другое место, задайте исходный диапазон $A$1:$A$30, Диапазон условий оставьте пустым, в поле Поместить результат в диапазон установите ссылку $H$1, выберите Только уникальные записи, нажмите Оk.

Рис. 1. Диалоговое окно Расширенный фильтр

Скачать заметку в формате Word или pdf, примеры в формате Excel (файл содержит макросы)

Список уникальных имен (вместе с заголовком) появится в столбце Н (рис. 2). Если требуется, вы можете отсортировать список в алфавитном порядке.

Рис. 2. Список уникальных имен

Альтернативное решение: чтобы извлечь уникальные значения из списка можно применить код VBA. Рассмотрите два подхода: один с использованием объекта Collection (коллекция), другой – помощью объекта Dictionary (словарь). Эти подходы основаны на одном и том же механизме, использующем тот факт, что и коллекция, и словарь не могут содержать дубли.

1) Код VBA на основе объекта Collection.

Dim SourceRng As Range

Dim UniqColl As New Collection

Set SourceRng = Range( » A2:A30 » )

On Error Resume Next

For Each cell In SourceRng.Cells

UniqColl.Add cell.Value, cell.Value

On Error GoTo 0

‘Sort the collection (optional)

ReDim UniqArray(1 To UniqColl.Count)

For i = 1 To UniqColl.Count

Range( » H1 » ).Resize(UniqColl.Count, 1).Value = WorksheetFunction.Transpose(UniqArray)

Этот код создает новую коллекцию – UniqColl, и перебирает все значения в списке имен, пытаясь внести каждое имя в коллекцию. Обратите внимание, что в строке кода UniqColl.Add cell.Value, cell.Value содержатся две ссылки на значение ячейки – cell.Value. Это происходит потому, что первые два аргумента для метода Add – это Value и Key. Коллекция не может содержать дубли.

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

Когда все элементы добавлены в коллекцию, код создает массив UniqArray такого же размера, как и коллекция UniqColl. Массив необходим для передачи содержимого в таблицу. Обратите внимание на использование функция Transpose при передаче массива в столбец листа Excel из кода VBA; это необходимо, потому что UniqArray представляет собой горизонтальный массив.

Перед передачей уникального списка UniqArray на лист Excel, данные желательно отсортировать в алфавитном порядке. Для этого просто вставьте следующий код после комментария ‘Sort the collection (optional):

For i = 1 To UniqColl.Count — 1

For j = i + 1 To UniqColl.Count

If UniqColl(i) > UniqColl(j) Then

UniqColl.Add Temp1, before:=j

UniqColl.Add Temp2, before:=i

UniqColl.Remove i + 1

UniqColl.Remove j + 1

2) Код VBA на основе объекта Dictionary.

Dim UniqueDic As Object

Dim cell As Range

Set UniqueDic = CreateObject( » Scripting.Dictionary » )

For Each cell In Range( » A2:A30 » )

If Not UniqueDic.Exists(cell.Value) Then

UniqueDic.Add cell.Value, cell.Value

Range( » H1 » ).Resize(UniqueDic.Count, 1).Value = WorksheetFunction.Transpose(UniqArray)

Этот код работает аналогично предыдущей программе за исключением следующих особенностей:

• Вместо перехвата ошибок (в случае дублей), используется свойство Exists объекта Dictionary, которое решает, было ли значение ранее добавлено в словарь и должно ли оно быть пропущено.
• Содержание словаря переводится в UniqArray одной командой: UniqArray = UniqueDic.Items

Вместо передачи уникальных элементов списка на лист Excel, вы можете заполнить пользовательскую форму Поле со списком (combo box) используя инструкцию: UserForm1.ComboBox1.List=UniqArray. [1]

Резюме: вы можете извлечь уникальные значения из списка, содержащего дубли. Данные могут быть помещены на лист Excel или в пользовательскую форму Поле со списком.

[1] У меня пользовательская форма не заработала – Прим. Багузина

Функции таблиц уникальных значений в DAX: VALUES и DISTINCT в Power BI и Power Pivot

до конца распродажи осталось:

» Функции таблиц уникальных значений в DAX: VALUES и DISTINCT в Power BI и Power Pivot

Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

• DAX функция VALUES
• DAX функция DISTINCT

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье поговорим про функции в DAX, возвращающие таблицы уникальных значений в Power BI и Power Pivot, а именно, про VALUES и DISTINCT.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

Да, и еще один момент, до 24 ноября 2023 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 60% (вместо 10000, всего за 4000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 24 ноября 2023 г.): узнать подробнее

до конца распродажи осталось:

DAX функция VALUES в Power BI и Power Pivot

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

Синтаксис: VALUES (‘Таблица’) или VALUES ([Столбец])

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

В качестве примера возьмем исходную таблицу «СпрМенеджеры», находящуюся в Power BI и содержащую информацию по менеджерам и отделам:

Создадим в Power BI Desktop во вкладке «Моделирование» вычисляемую таблицу уникальных значений отделов на основе формулы с участием VALUES:

Таблица = VALUES ('СпрМенеджеры'[Отдел])

Как результат, мы увидим столбец с уникальными значениями отделов:

Саму по себе функцию VALUES, как на примере выше, используют достаточно редко. Обычно в формулах она используется в партнерстве с другими DAX функциями.

Например, мы можем в рамках одной формулы объединить VALUES с функцией COUNTROWS, которая считает количество строк. Тем самым, мы можем создать меру, вычисляющую на основе исходной таблицы «СпрМенеджеры» количество отделов в организации:

Количество Отделов = COUNTROWS ( VALUES ('СпрМенеджеры'[Отдел]) )

Результатом выполнения кода этой формулы будет количество отделов, равное 3:

DAX функция DISTINCT в Power BI и Power Pivot

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

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

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

Синтаксис: DISTINCT ([‘Таблица’]) или DISTINCT ([Столбец])

В качестве примера работы функции DISTINCT напишем простую формулу, которая создаст в нашей модели данных справочник по менеджерам на основе исходной таблицы фактов «Заявки»:

Итак, код формулы в DAX достаточно простой:

СпрМенеджеры = DISTINCT ('Заявки'[Менеджер])

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

Далее, в Power BI Desktop во вкладке «Связи» можно будет объединить все таблицы, содержащие столбец [Менеджер], на основе созданного функцией DISTINCT, справочника «СпрМенеджер»:

В результате использования функции DISTINCT, мы смогли создать справочник с уникальными значениями менеджеров, при помощи которого, объединили две таблицы фактов «Заявки» и «Дополнительные затраты».

На этом, с разбором DAX функций VALUES и DISTINCT в Power BI и Power Pivot, в этой статья все.

Также, напоминаю Вам, что до 24 ноября 2023 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 60% (вместо 10000, всего за 4000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 24 ноября 2023 г.): узнать подробнее

До конца распродажи осталось:

Пожалуйста, оцените статью:

1. 5
2. 4
3. 3
4. 2
5. 1

Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.

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

Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

до конца распродажи осталось:

Что еще посмотреть / почитать?

Информационные функции в DAX: ISBLANK, ISNUMBER, ISEVEN, ISTEXT, ISNONTEXT, ISLOGICAL в Power BI и Power Pivot

DAX функции YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot

Функции объединения таблиц в одну на основе внутренних связей DAX: NATURALINNERJOIN и NATURALLEFTOUTERJOIN в Power BI и Power Pivot