Как выбрать уникальные значения из списка excel
Функция УНИК возвращает список уникальных значений в списке или диапазоне.
Возвращение уникальных значений из списка значений

Возвращение уникальных имен из списка имен

=УНИК(массив,[by_col],[exactly_once])
Функция УНИК имеет следующие аргументы:
Диапазон или массив, из которого возвращаются уникальные строки или столбцы
Аргумент by_col является логическим значением, указывающим, как проводить сравнение.
Значение ИСТИНА сравнивает столбцы друг с другом и возвращает уникальные столбцы
Значение ЛОЖЬ (или отсутствующее значение) сравнивает строки друг с другом и возвращает уникальные строки
[exactly_once]
Аргумент exactly_once является логическим значением, которое возвращает строки или столбцы, встречающиеся в диапазоне или массиве только один раз. Это концепция базы данных УНИК.
Значение ИСТИНА возвращает из диапазона или массива все отдельные строки или столбцы, которые встречаются только один раз
Значение ЛОЖЬ (или отсутствующее значение) возвращает из диапазона или массива все отдельные строки или столбцы
Массив может рассматриваться как строка или столбец со значениями либо комбинация строк и столбцов со значениями. В примерах выше массивы для наших формул УНИК являются диапазонами D2:D11 и D2:D17 соответственно.
Функция УНИК возвращает массив, который будет рассеиваться, если это будет конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете Структурированные ссылки. Дополнительные сведения см. в статье Поведение рассеянного массива.
Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.
Примеры
В этом примере СОРТ и УНИК используются совместно для возврата уникального списка имен в порядке возрастания.

В этом примере аргумент exactly_once имеет значение ИСТИНА, и функция возвращает только тех клиентов, которые обслуживались один раз. Это может быть полезно, если вы хотите найти людей, которые не получали дополнительное обслуживание, и связаться с ними.

В этом примере используется амперсанд (&) для сцепления фамилии и имени в полное имя. Обратите внимание, что формула ссылается на весь диапазон имен в массивах A2:A12 и B2:B12. Это позволяет Excel вернуть массив всех имен.

Если указать диапазон имен в формате таблицы Excel, формула автоматически обновляется при добавлении или удалении имен.
Чтобы отсортировать список имен, можно добавить функцию СОРТ: =СОРТ(УНИК(B2:B12&» «&A2:A12))
В этом примере сравниваются два столбца и возвращаются только уникальные значения в них.

Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Excel как из списка выбрать уникальные значения
Отбор уникальных значений (убираем повторы из списка) в MS EXCEL
Смотрите также В2 пишем:Czeslav где А1 – если среди уникальных: Попробуй на скоростьByRef aSel() As MyArray() As Variant
Например, зеленым. И 2 строки и строкам. В этом собой Исходный список исключить ненужные вычисления, статьи Отбор уникальных

Задача
статью Отбор уникальных соответствует значению еще с помощью МаркераИмея список с повторяющимися=СЧЕТЕСЛИ ( A$2:A$3456;

: Промучился полтора дня заголовок). Выводить список есть пустая ячейка это

Решение
Variant, _ Columns(‘A:A’).Select ‘ select нажмите ОК на выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» случаи следует использовать №1 с названиями тормозящие пересчет листа. значений (убираем повторы строк с помощью не найденному в
заполнения. Чтобы все
- значениями, создадим список, A2) (пробел передПрикладываю файл. мы будем в
- (хотя если передПо-моему алгоритм уже
- Optional ByRef strSQL your column Range(‘A1:A14’).AdvancedFilter
- всех открытых окнах.
или нажмите комбинацию условное форматирование, которое компаний, причем егоПримечание из списка) в Расширенного фильтра), Сводных исходном списке (т.е. значения исходного списка состоящий только из скобкой удалить! Это

столбец С, начиная заполнением массива отсортировать был когда-то на As String = Action:=xlFilterInPlace, Unique:=True ‘Готово! горячих клавиш CTRL+SHIFT+=. ссылается на значения границы могут изменяться: в формуле использован MS EXCEL. таблиц или через значению «ОАО Уважаемая были гарантировано отображены уникальных значений. При mail вставляет, «умник»).: Ещё один вариант. с ячейки С2. temporary region, то этом форуме ») filter on thisКак работает выборка уникальныхУ нас добавилось 2 ячеек с запросом. в зависимости от Динамический диапазон Список_текст.В столбце
меню Данные/ Работа
- компания» для формулы в списке уникальных добавлении новых значений И копируем этуУникальные значения вытягиваются Формула в C2 может получиться)как уникальныеSub FormSpisok(ListName, FirstRow,Dim cnn As column to receive значений Excel? При пустые строки. Теперь Чтобы получить максимально количества числа введенных Значения ошибки скрытыА с данными/ Удалить в ячейке значений, необходимо сделать в исходный список, формулу до ячейки по алфавиту, только будет следующая: найти я понял. NumColumn, Spisok) ‘Формирует ADODB.Connection, Rs As unique values ‘ выборе любого значения в ячейку A1 эффективный результат, будем значений (пропуски не с помощью Условногоимеется список с дубликаты. У каждогоB5
- размер списка уникальных список уникальных значений В3456. надо обратить внимание,Код <=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))>вводитсяа как отметить (в массив Spisok) ADODB.Recordset, i As part of rows (фамилии) из выпадающего введите значение «Клиент:». использовать выпадающий список,
- допускаются). Аналогичные диапазоны форматирования. повторяющимися значениями, например
- способа есть свои); значений равным размеру должен автоматически обновляться.Теперь выделяем строки что для первой формула в ячейку строки *дубликатов* каким список строк неповторяющихся Long will be hidden списка B1, в
Пришло время для создания в качестве запроса. Список2 и Объединенный_списокСОВЕТ:
список с названиями преимущества и недостатки.ИНДЕКС() – восстанавливает значение исходного списка (наПусть в столбце со 2 по строки одна формула, сочетанием клавиш – — ниб. цветом. значений из листаSet cnn =
Решение для списков с пустыми ячейками
and I need таблице подсвечиваются цветом выпадающего списка, из Это очень удобно созданы для столбцовСписок уникальных значений
городов.
Преимущество использования формул
по его позиции

Решение без формул массива
тот случай, когдаА 3456-ую. И сортируем а для последующих-другая. Ctrl+Shift+Enter. После того,можно найти unique-> ListName в столбце New ADODB.Connection not include them
все строки, которые которого мы будем если нужно часто Исходный список №2 можно создать разнымиВ некоторых ячейках исходного состоит в том, в диапазоне Исходный_список; все значения исходногоимеется список с по колонке В.trash50 как мы ввели Selection.покрасить-> ShowAllData-> если NumColumn, начиная сcnn.Open ‘Provider=Microsoft.Jet.OLEDB.4.0;’ & into my next содержат это значение выбирать фамилии клиентов менять однотипные запросы и Объединенный список.
способами, например, с списка имеются повторы чтобы при добавленииЕСЛИОШИБКА() подавляет ошибку, возникающую, списка не повторяются). повторяющимися значениями, например В итоге ВСЕ: эту формулу в не закрашено, то ряда FirstRow ‘Ограничение _ selection ActiveCell.CurrentRegion.Select ‘ (фамилию). Чтобы в в качестве запроса. для экспонирования разныхДалее используем решение, приведенное использованием Расширенного фильтра
— новый список новых значений в когда функция ПОИСКПОЗ() В случае наличия список с названиями строки, где вtrash50 C2 закрасить своим цветом. на листе-первое пустое’Data Source=’ &
Отбор уникальных значений с сортировкой в MS EXCEL
copy unique values этом убедится вПеред тем как выбрать строк таблицы. Ниже в статье Отбор (см. статью Отбор уникальных значений не исходный список, список пытается в массиве в исходном списке компаний. колонке В стоит, что то ямы её должны скопироватьно если 4
значение ‘Первое значение _ from this column выпадающем списке B1 уникальные значения из
детально рассмотрим: как уникальных значений (убираем уникальных строк с должен их содержать. уникальных значений автоматически нулей и единиц,

Задача
большого количества повторяющихсяВ некоторых ячейках исходного 1 — это не пойму что и вставить

дубликата, то незакр. массива Spisok(0)-кол-во значенийActiveWorkbook.Path + ‘ to some free
выберите другую фамилию. списка сделайте следующее: сделать выборку повторяющихся
Решение
дубликаты из столбца). помощью Расширенного фильтра),Для наглядности уникальные значения обновлялся. возвращенном СЧЁТЕСЛИ(), найти значений, список уникальных списка имеются повторы строки с уникальным вы делаете. вв несколько строк
будет 3.
Dim numb As
‘ + ActiveWorkbook.Name
temporary place and После чего автоматическиПерейдите в ячейку B1 ячеек из выпадающегоЗаписав в Сводных таблиц или в исходном спискеСОВЕТ2 0, которого нет значений можно сделать — новый список значением. И все вашей формуле диапазон так, чтобы точнокак сделать ‘красиво’? Integer Dim stroka & ‘;’ & select this region будут выделены цветом и выберите инструмент списка.E5 через меню Данные/ выделены цветом с: Для тех, кто создает (ситуация возникает в меньшего размера, удалив уникальных значений не
они будут сверху. данных (первый аргумент) отобразить все уникальныечто то я As String Dim _
nRow = Selection.Rows.Count
уже другие строки. «ДАННЫЕ»-«Работа с данными»-«ПроверкаДля примера возьмем историюформулу массива: Работа с данными/ помощью Условного форматирования. список уникальных значений ячейке лишние формулы, чтобы должен их содержать. Не проблема все на одном листе элементы. туплю мало-мало. ;-/ DataValues As New’Extended Properties=Excel 8.0’ Selection.Copy ActiveSheet.Paste Destination:=Cells(nRow Такую таблицу теперь данных». взаиморасчетов с контрагентами,=ЕСЛИОШИБКА(ИНДЕКС(Объединенный_список; Удалить дубликаты. УСписок уникальных значений должен
Отбор уникальных значений из двух диапазонов в MS EXCEL
для того, чтобыB12 исключить ненужные вычисления,Для наглядности уникальные значения их теперь выделить
большой, а проверкуСсылка: (Способ 2.гуру, подскажите, плз! Collection ReDim Spisok(1) ‘HDR=Yes;’

Задача
+ 2, 2) легко читать иНа вкладке «Параметры» в

как показано наПОИСКПОЗ(0;СЧЁТЕСЛИ($E$4:E4;Объединенный_список);0));»») каждого способа есть быть отсортирован по
Решение с использованием объединенного списка
в дальнейшем сформировать, когда все уникальные тормозящие пересчет листа. в исходном списке и удалить. Есть делаете по диапазону Формулы)заранее, 10х! Spisok(0) = 0
Set Rs =
Cells(nRow + 2,
анализировать. разделе «Условие проверки» рисунке:и скопировав ее вниз свои преимущества и алфавиту. на его основе Выпадающий значения уже извлеченыРазберем работу формулу подробнее: выделены цветом с одно «но» - намного меньше указанногоНемогу применить данную
В буфере хранится колонка stroka = Trim(Sheets(ListName).Cells(FirstRow, New ADODB.Recordset 2).Select ActiveCell.CurrentRegion.Select MyArray
Скачать пример выборки из из выпадающего спискаВ данной таблице нам
получим список уникальных
недостатки. Но, в
Список уникальных значений создадим список, необходимо учитывать, из исходного списка).Здесь использование функции СЧЁТЕСЛИ()

Тестируем
помощью Условного форматирования. поменяется исходный порядок в первом аргументе. формулу.
со значениями, скопированными NumColumn)) On ErrorRs.Open Source:=’SELECT distinct = Selection ‘fill списка с условным

Решение без использования объединенного списка
«Тип данных:» выберите нужно выделить цветом значений из двух этой статье нам в столбце
что вышеуказанные формулы
Формула будет работать и
не совсем обычно:
Для начала создадим Динамический
значений в колонкеVlad999(P.S. времени и из экселя. Многие Resume Next Do ‘ & fldName
Как сделать выборку в Excel из списка с условным форматированием
my array with форматированием. значение «Список». все транзакции по столбцов. требуется, чтобы приB возвращают значение Пустой в случае если в качестве критерия диапазон, представляющий собой А.: это ошибочно. методов ‘копирования’ (/’вставки’) значение в этой While stroka <> & ‘ from our unique valuesПринцип действия автоматической подсветкиВ поле ввода «Источник:» конкретному клиенту. Для1. Введите в ячейку добавлении новых значенийс помощью формулы текст «», который исходный список содержит (второй аргумент) указано исходный список. ЕслиВдобавок. И «технологическую»- проверка должна
Выбор уникальных и повторяющихся значений в Excel
потратил недопустимо много). колонке повторяются. Можно » DataValues.Add stroka, ‘ & rngName,

from Excel ActiveCell.CurrentRegion.Clear строк по критерию введите =$F$4:$F$8 и переключения между клиентамиА9 в исходный список, массива (см. файл требует аккуратного обращения, числовые значения. не одно значение, в исходный список колонку В после осуществлятся по всему
Прошу Вашей помощи. ли как-то сделать, stroka FirstRow = _
- ‘ clear our запроса очень прост.
- нажмите ОК. будем использовать выпадающий

- название новой компании список уникальных значений примера). Для этого особенно при подсчетеПримечание а целый массив будет добавлено новое

- сортировки тоже можно большому диапазону, ноAleksSid
чтобы при вставке FirstRow + 1ActiveConnection:=cnn, _ temporary region ActiveSheet.ShowAllData
Каждое значение в
В результате в ячейке список. Поэтому в – ООО Фортик должен автоматически обновляться, введите следующую формулу значений (вместо обычной функции. Функция ЕСЛИОШИБКА() будет работать начиная

Исходный_список, поэтому функция значение, то оно удалить. уменя так не
: Я думаю будут (или после вставки) stroka = Trim(Sheets(ListName).Cells(FirstRow,CursorType:=adOpenDynamic, _ ‘ clear our
столбце A сравнивается B1 мы создали первую очередь следует
- 2. В таблице поэтому здесь построен в ячейку СЧЕТЗ() нужно использовать

- с версии MS возвращает не одно будет автоматически включено. работает.

- и другие варианты. в эксель, в NumColumn)) Loop On
LockType:=adLockReadOnly, _ filter End SubNow, со значением в выпадающих список фамилий
подготовить содержание для Уникальные значения через список с использованиемB6 СЧЕТЕСЛИ() со специальными EXCEL 2007, чтобы значение, а целый в Динамический диапазон: Добавлю к «полосатому».опишите словами что Код =ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ($B$2:$B$16;$B$2:$B$16;0)=СТРОКА($A$1:$A$15);СТРОКА($A$1:$A$15));СТРОКА(A1)));»») столбце бы остались Error GoTo 0
Options:=adCmdText all your data ячейке B1. Это
- клиентов. выпадающего списка. Нам объединенный список будет формул.: аргументами). Например, см.


- обойти это ограничение массив нулей и и нижеследующие формулыСортировка не обязательна, с чем нужноVlad999 только уникальные значения? numb = 1ReDim Preserve aSel(0) in two-dimensial array.
позволяет найти уникальные

Примечание. Если данные для нужны все Фамилии автоматически выведено названиеИмея два списка с=ИНДЕКС(Список_Текст; статью Динамический выпадающий список читайте статью про единиц. Возвращается 0, не придется модифицировать. если воспользоваться автофильтром. сравнивать и от:Ewe 2e¶ coлнышкоcbetиточеньярко* For Each DataValue ‘ инициализация массиваIn this case, значения в таблице выпадающего списка находятся
клиентов из столбца новой компании. однотипными повторяющимися значениями,
НАИМЕНЬШИЙ(ЕСЛИ(НАИМЕНЬШИЙ( в MS EXCEL. функцию ЕСЛИОШИБКА(). В файле если значение изДля создания Динамического диапазона:Включаете куда выводить списокtrash50: В Excel выделить In DataValues ReDimaSel(0) = 0 you can see Excel. Если данные на другом листе, A, без повторений.В принципе, можно обойтись создадим из нихЕСЛИ(СЧЁТЕСЛИ($B$5:B5;Список_Текст)=0;СЧЁТЕСЛИ(Список_Текст;»Примечание примера имеется лист исходного списка нена вкладке Формулы в
Как получить уникальные значения из колонки VBA Excel?
фильтр на столбце уникальных согласно вашего, ее надо просто диапазон ячеек с Preserve Spisok(numb + ‘ 0-й элемент
your data it совпадают, тогда формула то лучше дляПеред тем как выбрать и без объединенного список, содержащий только
После ввода формулы вместо: В статье Восстанавливаем последовательности Для 2003, где найдено в диапазоне группе Определенные имена с СЧЁТЕСЛИ, нажимаете файла. растянуть, если копировать/вставить данными, «Данные» - 1) Spisok(numb) = хранит размер массива in Myarray(1,1), MyArray(2,1). and возвращает значение ИСТИНА такого диапазона присвоить уникальные значения в списка. Платой за уникальные значения.ENTER из списка без эта функция неB4:B4B4:B5 выберите команду Присвоить на него, вВ Excel есть столбец — тогда каждый «Фильтр» — «Расиренный DataValue numb =i = 0 so on и для целой имя и указать Excel, подготовим данные это будет значительноеПусть столбцынужно нажать
повторов в MS используется.и т.д.), и имя; списке будет
со значениями, некоторые раз вводить как
фильтр» — поставить numb + 1Rs.MoveFirstKorolana строки автоматически присваивается
его в поле для выпадающего списка: усложнение формулы массива:
АВCTRL + SHIFT + EXCEL решена обратная задача:
Если исходная таблица содержит 1 если найдено.
в поле Имя введите:возможность задать свой.
из них повторяются,
формулу массива.
галочку «Только уникальные Next Spisok(0) =Do While Not: Спасибо за пример.
новый формат. Чтобы «Источник:». В данном
Выделите первый столбец таблицы=ЕСЛИОШИБКА(ЕСЛИОШИБКА(содержат списки названий
ENTER из списка уникальных пропуски, то нужно Например, в ячейке
Исходный_список; Нажимаете, выбираете не
некоторые по 2-3Приложите файл, в
значения» numb — 1
Rs.EOFmaks_well формат присваивался для случае это не
A1:A19.ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($F$4:F4;Список1);0));
компаний, в некоторых. Затем нужно скопировать
значений, в котором использовать другую формулуB5в поле Диапазон введите равен, в поле
раза и более.
котором не получается.
Валерий
End Sub
ReDim Preserve aSel(i: Я это делаю
целой строки, а обязательно, так какВыберите инструмент: «ДАННЫЕ»-«Сортировка и
ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($F$4:F4;Список2);0))
ячейках имеются дубликаты.
формулу вниз, например, для каждого значения
массива (см. листформулой СЧЁТЕСЛИ(B$4:B5;Исходный_список) возвращается
формулу =СМЕЩ(УникальныеЗначения!$A$5;;; СЧЁТЗ(УникальныеЗначения!$A$5:$A$30))
чему 1.Необходимо удалить все
snipe
: Есть несколько методов.Приветствую.
+ 1) по другому (нужно не только ячейке у нас все
фильтр»-«Дополнительно».);»»)
Создадим список, состоящий только с помощью Маркера задано количество повторов, с пропусками файла массив <1:0:0:0:0:0:0:1:0:0:0:0:1:1:0>. Т.е.
нажмите ОК.Выделяете всё и уникальные значения (те,
:1. использовать расширенныйНесколько измененый вариант,
aSel(UBound(aSel)) = Rs.Fields(fldName) для заполнения списков в столбце A, данные находятся наВ появившемся окне «РасширенныйДля вывода промежуточных результатов из уникальных значений заполнения. Чтобы все создается список этих примера): в исходном спискеСписок уникальных значений создадим удаляете записи. что встречаются толькоtrash50 фильтр и там предложенный Короланой, извлекаетRs.MoveNext: i = в контролах): мы используем смешанную одном рабочем листе. фильтр» включите «скопировать вычислений можно воспользоваться (см. файл примера). значения исходного списка значений с повторами.=ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19; найдено 4 значения в столбцеОтключаете Автофильтр (или 1 раз), если вдруг не поставить галочку «Только уникальные значения из i + 1’ Выбирает из ссылку в формуле
Выборка ячеек из таблицы
результат в другое клавишеРешим задачу двумя способами: были гарантировано отображеныИз исходной таблицы отберем
ПОИСКПОЗ( 0;ЕСЛИ(ЕПУСТО($A$5:A19);»»;СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0) «ООО Рога иB он сам слетает)Это в принципе знаете что это уникальные записи» столбца с активнойLoop заданого именованного диапазона =$A4. по условию в место», а вF9 через промежуточное создание в списке уникальных только уникальные значения);»») копытца» (с помощью формулыЗибин
возможно? такое:2. использовать функцию
ячейкой:aSel(0) = UBound(aSel) с заголовком RngNameKorolana
Excel: поле «Поместить результат, выделив интересующую часть
объединенного списка и
значений, необходимо сделать и выведем их
Для отбора уникальных значений
B5
Как оставить уникальные значения в колонке в эксель?
массива (см. файл:Значений примерно 4000при наведении курсора «создать сводную таблицу».Public Sub FillArrayWithUniqueValue() ‘’ столбик fldName: Как получить уникальныеВыделите табличную часть исходной в диапазон:» укажите
формулы. без него. размер списка уникальных в отдельный диапазон можно обойтись без). Массив легко увидеть примера). Для этого1)Выделяем список в
шт, так что на правый нижний
А после этого Dim MyArray() AsRs.Close: cnn.Close: Set и отбирает только
значения из колонки таблицы взаиморасчетов A4:D21
$F$1.Если Вы работаете сВ статье Объединение списков
Получить список уникальных (не повторяющихся) значений — не удается применить формулу
значений равным размеру с сортировкой по использования формул массива. с помощью клавиши введите следующую формулу
Исходный диапазон
вручную это сделать
угол ячейки курсор копировать в буфер Variant ActiveCell.EntireColumn.AdvancedFilter Action:=xlFilterCopy, Rs = Nothing: уникальные его значения таблицы в VBA и выберите инструмент:Отметьте галочкой пункт «Только большой таблицей и изложено решение создания
исходного списка (на возрастанию. Отбор и Для этого создайтеF9 в ячейку2)Ставим переключатель в не вариант. сменит вид на из Excel copytorange:=Cells(2, 4), unique:=True Set cnn =’ в массиве Excel например в
«ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать уникальные записи» и
вам необходимо выполнить объединенного списка из
тот случай, когда сортировку сделаем с дополнительный служебный столбец
(выделите в Строке
B5 положение Скопировать результатПолосатый жираф алик крестик, жмем ЛКМ
trash50 ‘filter for current Nothing aSel (. ) массив? формулу для определения нажмите ОК. поиск уникальных значений
2-х списков. Воспользуемся все значения исходного
помощью одной формулой для промежуточных вычислений формул выражение СЧЁТЕСЛИ(B$4:B5;Исходный_список),: в другое место: Проще всего, наверное,
(левую кнопку мыши): Получить список уникальных column MyArray =End Function ‘’ Структура именованногоПри работе в форматируемых ячеек».В результате мы получили в Excel, соответствующие этим решением и
списка не повторяются). массива. Формула работает (см. лист «Без нажмите
=ЕСЛИОШИБКА(ИНДЕКС(Исходный_список; ПОИСКПОЗ(0;СЧЁТЕСЛИ(B$4:B4;Исходный_список);0));»») и указываем пустую
так:
и не отпуская (не повторяющихся) значений
Cells(2, 4).CurrentRegion.Value Cells(2, UnicSelect —>> диапазона : Excel при установкеЧтобы выбрать уникальные значения список данных с
определенному запросу, то создадим объединенный список, В случае наличия как для текстовых CSE» в файлеF9После ввода формулы вместо ячейку.Пусть данные в её тянем вниз (из столбцов) - 4).CurrentRegion.Clear ‘ clearSerg_FSM
’ . автофильтра, Excel моментом
из столбца, в уникальными значениями (фамилии нужно использовать фильтр. записав в ячейке в исходном списке
(сортировка от А примера).: вместо формулы отобразитсяENTER3)Включаем (самое главное! столбце А от
Удаление уникальных значений Excel
или в нужном неудается применить формулу. our temporary regionправда: This way is’ .
заполняет ListBox уникальными поле ввода введите без повторений). Но иногда нам
С5 большого количества повторяющихся
до Я), такСОВЕТ: ее результат);нужно нажать
) флажок Только А2 до А3456. вам направлении.
Понравилась формула (массива): не работает если right too, butPublic Function UnicSelect(ByRef значениями, даже при формулу: =$A4=$B$1 и нужно выделить все
формулу значений, список уникальных и для числовыхСписок уникальных значений можноПОИСКПОЗ() – возвращает позициюCTRL + SHIFT + уникальные записи и
Тогда в любомtrash50Код <=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))>Описание: первая ячейка пустая much slower. Try rngName As String, большом заполнении таблицы. нажмите на кнопкуТеперь нам необходимо немного строки, которые содержат=ЕСЛИ(СТРОКА()-СТРОКА($C$4)>СЧЁТЗ(Список1); значений можно сделать значений (сортировка от создать разными способами, первого нуля в ENTER жмем ОК. свободном столбце, начиная
: а сводная таблицасписок с данными и еще можно it.
ByRef fldName Asyuniki
«Формат», чтобы выделить модифицировать нашу исходную
определенные значения по
ИНДЕКС(Список2;СТРОКА(C5)-СЧЁТЗ(Список1)-СТРОКА($B$4));A5) меньшего размера, удалив мин до макс). например, с использованием
массиве из предыдущего. Затем нужно скопировать4)Удаляем столбец с с (пусть это
не подойдет под у Вас расположен
потерять часть значенийwanton2
String, _: Sub FillArrayWithUniqueValue() Dim
одинаковые ячейки цветом. таблицу. Выделите первые
отношению к другимДинамический диапазон Список1 представляет лишние формулы, чтобыЭта статья — продолжение Расширенного фильтра (см.
шага. Первый нуль формулу вниз, например, уникальными значениями будет столбец В)
это действо? в столбце А(А1:А51,
Как сделать выборку в Excel из списка с условным форматированием
Если Вы работаете с большой таблицей и вам необходимо выполнить поиск уникальных значений в Excel, соответствующие определенному запросу, то нужно использовать фильтр. Но иногда нам нужно выделить все строки, которые содержат определенные значения по отношению к другим строкам. В этом случаи следует использовать условное форматирование, которое ссылается на значения ячеек с запросом. Чтобы получить максимально эффективный результат, будем использовать выпадающий список, в качестве запроса. Это очень удобно если нужно часто менять однотипные запросы для экспонирования разных строк таблицы. Ниже детально рассмотрим: как сделать выборку повторяющихся ячеек из выпадающего списка.
Выбор уникальных и повторяющихся значений в Excel
Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

В данной таблице нам нужно выделить цветом все транзакции по конкретному клиенту. Для переключения между клиентами будем использовать выпадающий список. Поэтому в первую очередь следует подготовить содержание для выпадающего списка. Нам нужны все Фамилии клиентов из столбца A, без повторений.
Перед тем как выбрать уникальные значения в Excel, подготовим данные для выпадающего списка:
- Выделите первый столбец таблицы A1:A19.
- Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».

- В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.

- Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.
В результате мы получили список данных с уникальными значениями (фамилии без повторений).
Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».
Пришло время для создания выпадающего списка, из которого мы будем выбирать фамилии клиентов в качестве запроса.
Перед тем как выбрать уникальные значения из списка сделайте следующее:
- Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

- На вкладке «Параметры» в разделе «Условие проверки» из выпадающего списка «Тип данных:» выберите значение «Список».

- В поле ввода «Источник:» введите =$F$4:$F$8 и нажмите ОК.
В результате в ячейке B1 мы создали выпадающих список фамилий клиентов.
Примечание. Если данные для выпадающего списка находятся на другом листе, то лучше для такого диапазона присвоить имя и указать его в поле «Источник:». В данном случае это не обязательно, так как у нас все данные находятся на одном рабочем листе.
Выборка ячеек из таблицы по условию в Excel:
- Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».


- Чтобы выбрать уникальные значения из столбца, в поле ввода введите формулу: =$A4=$B$1 и нажмите на кнопку «Формат», чтобы выделить одинаковые ячейки цветом. Например, зеленым. И нажмите ОК на всех открытых окнах.

Как работает выборка уникальных значений Excel? При выборе любого значения (фамилии) из выпадающего списка B1, в таблице подсвечиваются цветом все строки, которые содержат это значение (фамилию). Чтобы в этом убедится в выпадающем списке B1 выберите другую фамилию. После чего автоматически будут выделены цветом уже другие строки. Такую таблицу теперь легко читать и анализировать.
Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel. Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат. Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.
Как получить список уникальных значений
В статье описано, как получить список уникальных значений в столбце с помощью формулы и как настроить эту формулу для различных наборов данных. Вы также узнаете, как быстро получить отдельный список с помощью расширенного фильтра Excel и как извлечь уникальные записи с помощью Duplicate Remover.
В нескольких недавних статьях мы обсудили различные методы подсчета и поиска уникальных значений в Excel. Если у вас была возможность прочитать эти руководства, вы уже знаете, как получить этот список при помощи идентификации, фильтрации и копирования. Но это немного длинный и далеко не единственный способ извлечения уникальных значений в Excel. Вы можете сделать это намного быстрее, используя специальную формулу. И сейчас я покажу вам этот и несколько других приёмов.
Базовые формулы для получения уникальных значений.
Чтобы избежать путаницы, сначала давайте договоримся о том, что мы называем уникальными значениями в Excel.
Уникальные значения — это значения, которые присутствуют в списке только один раз. Например:

Чтобы получить список уникальных значений в Excel, используйте одну из следующих формул.
Формула уникальных значений массива (заполняется нажатием Ctrl + Shift + Enter):
Можно воспользоваться и обычной формулой (вводится нажатием Enter):
Примечание. Поскольку формула ссылается на ячейку, расположенную над первой ячейкой создаваемого списка, который обычно является заголовком столбца (B1 в этом примере), то убедитесь, что ваш заголовок имеет уникальное имя, которое больше нигде в этом столбце не появляется.
В этом примере мы извлекаем уникальные имена из столбца A (точнее из диапазона A2: A10), а следующий скриншот демонстрирует формулу в действии:

Вот наш порядок действий:
- Измените любую из формул в соответствии с вашим диапазоном данных.
- Введите ее в первую ячейку, с которой начнётся формирование списка (в данном примере B2).
- Если вы используете формулу массива, нажмите Ctrl + Shift + Enter . Если вы выбрали обычную, нажмите просто клавишу Enter .
- Скопируйте вниз настолько, насколько это необходимо, перетащив мышкой маркер заполнения. Поскольку обе формулы заключены в функцию ЕСЛИОШИБКА, вы можете скопировать вниз с запасом. Это не испортит ваши данные какими-либо ошибками, независимо от того, сколько уникальных значений было извлечено.
Как извлечь различные значения.
Различные значения — появляются в перечне данных хотя бы один раз. Это все уникальные и первое вхождение повторяющихся значений.

Чтобы получить их список в Excel, используйте следующие формулы.
Формула массива (требуется нажать Ctrl + Shift + Enter ):
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$13; ПОИСКПОЗ(0; ИНДЕКС(СЧЁТЕСЛИ($B$1:B1; $A$2:$A$13); 0; 0); 0)); «»)
- A2: A13 — это список источников.
- B1 — это ячейка над первой ячейкой отдельного списка. В этом примере отдельный список начинается с ячейки B2 (это первая ячейка, в которую вы вводите формулу), поэтому вы ссылаетесь на B1.

Как извлечь значения, игнорируя пустые ячейки
Если исходный список содержит пустые ячейки, формула, которую мы только что обсудили, вернет ноль для каждой пустой строки, что может быть проблемой. Это вы и наблюдаете на скриншоте чуть выше. Чтобы исправить это, сделаем несколько небольших корректировок.
Формула массива для извлечения различных значений, исключая пустые ячейки:
Аналогичным образом вы можете получить список различных значений, исключая пустые ячейки и ячейки с числами:
Напоминаем, что в приведенных выше формулах A2: A13 – это исходный список, а B1 – ячейка прямо над первой позицией формируемого списка.
На этом скриншоте показан результат отбора:
Быть может, кому-то будет полезна еще одна формула –
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$13; АГРЕГАТ(15;6;(СТРОКА($A$2:$A$13)-СТРОКА($A$2)+1) / (ПОИСКПОЗ($A$2:$A$13;$A$2:$A$13;0)=СТРОКА($A$2:$A$13)-СТРОКА($A$2)+1); ЧСТРОК($A$2:$A2)));»»)
Она работает с числами и текстом, игнорирует пустые ячейки.
Как извлечь отдельные значения с учетом регистра в Excel
При работе с данными, чувствительными к регистру, такими как пароли, имена пользователей или имена файлов, вам может потребоваться список отдельных значений с учетом заглавных и прописных букв.
Для этого используйте формулу массива, где A2: A10 — это исходный список, а B1 — это ячейка над первой ячейкой отдельного списка.
Формула массива для получения различных значений с учетом регистра (требуется нажатие Ctrl + Shift + Enter )

Как видите, при отборе регистр здесь имеет значение.
Отбор уникальных значений по условию.
Представим, что у нас есть таблица с данными о продажах. Нам необходимо определить, какие наименования товаров заказывал определенный покупатель.
Сначала отберем из таблицы только те строки, которые удовлетворяют заданным условиям, затем из этих строк выберем уникальные наименования товаров.
В ячейке G2 указываем нужного нам заказчика, а в H2 записываем эту формулу массива:
Не забудьте, что формулу массива нужно вводить в ячейку EXCEL с помощью одновременного нажатия CTRL+SHIFT+ENTER . Копируем ее по столбцу вниз при помощи маркера заполнения. Получаем список из четырех позиций.
Усложним задачу. Определим список не только для этого покупателя, но также и для определённого менеджера.
Вот наша формула массива:
Как видите, теперь товаров всего два. В подсчете принимают участие только те строки, которые удовлетворяют сразу двум условиям: должно совпасть название фирмы и фамилия менеджера. Только из них мы извлекаем уникальные названия товаров.
В случае, если условий будет больше, нужно просто добавить соответствующий критерий в функцию ЕСЛИ и изменить число 2 на 3 или большее (в зависимости от количества условий).
Извлечь уникальные значения из диапазона.
Формулы, которые мы описывали выше, позволяют сформировать список значений из данных определенного столбца. Но часто речь идет о нескольких столбцах, то есть о диапазоне данных. К примеру, вы получили несколько списков товаров из различных файлов и расположили их в соседних столбцах.
Используем формулу массива
Здесь A2:C9 обозначает диапазон, из которого вы хотите извлечь уникальные значения. E1 – это первая ячейка столбца, в который вы хотите поместить результат. $2:$9 указывает на строки, содержащие данные, которые вы хотите использовать. $A:$C указывает на столбцы, из которых вы берёте исходные данные. Пожалуйста, измените их на свои собственные.
Нажмите Shift + Ctrl + Enter , а затем перетащите маркер заполнения, чтобы вывести уникальные значения, пока не появятся пустые ячейки.
Как видите, извлекаются все уникальные и первые вхождения дубликатов.
Встроенный инструмент удаления дубликатов.
Начиная с Excel 2007 функция удаления дубликатов является стандартной. Найти ее можно на вкладке Данные > Удаление дубликатов.
Вам нужно при помощи птички указать столбцы, в которых нужно найти и удалить повторяющиеся значения. Если сделать так, как на скриншоте, то в таблице останутся только уникальные пары «Заказчик – Товар». Остальное будет удалено. Если включить только флажок «Заказчик», то останется только по одной строке для каждого заказчика и т.д.
Использование расширенного фильтра.
Если вы не хотите тратить время на выяснение загадочных поворотов формул, вы можете быстро получить список уникальных значений с помощью расширенного фильтра. Подробные инструкции приведены ниже.
- Выберите столбец данных, из которого вы хотите извлечь отдельные значения.
- Перейдите на вкладку «Данные» > группа «Сортировка и фильтр» и нажмите кнопку «Дополнительно» .
- В диалоговом окне Расширенный фильтр выберите следующие параметры:
- Установите флажок Копировать в другое место .
- В поле Исходный диапазон убедитесь, что он указан правильно.
- В параметре Поместить результат в… укажите самую верхнюю ячейку целевого диапазона. Помните, что вы можете копировать отфильтрованные данные только на текущий лист.
- Выберите пункт «Только уникальные записи».
- Наконец, нажмите кнопку ОК и проверьте результат.
Как видите, мы проверили колонку B, и затем список уникальных наименований товара, найденных в ней, поместили в столбец K.
Обратите внимание, что хотя опция расширенного фильтра называется «Только уникальные записи», она извлекает различные значения, то есть уникальные и первые вхождения повторяющихся.
Теперь немного усложним задачу.
Если требуется искать записи не по одному, а по нескольким столбцам, то можно их предварительно «склеить» при помощи функции СЦЕПИТЬ.
Записываем это в столбец F и копируем вниз. Получаем вспомогательную колонку.

В качестве исходного диапазона мы по-прежнему выбираем данные, из которых извлекаем уникальные значения. Теперь это два столбца – A и B.
Но искать уникальные мы по-прежнему можем только в одном столбце. Вот для этого нам и пригодится вспомогательная колонка F с объединенными данными. Ее то мы и указываем в поле «Диапазон условий».
Все остальное – так же, как и в предыдущем примере.
В результате мы получили все имеющиеся в таблице комбинации «Заказчик — Товар» на основе данных во вспомогательном столбце F.
Думаю, вы понимаете, что аналогичные действия можно произвести и с тремя столбцами (например Фамилия – Имя – Отчество). Главное условие – исходный диапазон должен быть непрерывным, то есть все столбцы должны находиться рядом.
Как видите формулы здесь не нужны. Однако, если исходные данные изменятся, то все манипуляции придется повторять заново.
Извлечение уникальных значений с помощью Duplicate Remover.
В заключительной части этого руководства я покажу вам интересное решение для поиска и извлечения различных и уникальных значений в таблицах Excel. Это решение сочетает в себе универсальность формул Excel и простоту расширенного фильтра. Кроме того, здесь есть несколько уникальных функций:
- Найти и извлечь уникальные или различные значения на основе записей в одном или нескольких столбцах.
- Найти, выделить и скопировать уникальные значения в любое другое место в той же или другой книге Excel.
А теперь давайте посмотрим, как работает инструмент Duplicate Remover.
Предположим, у вас есть большая таблица, созданная путем объединения данных из нескольких других таблиц. Очевидно, что она содержит много повторяющихся строк, и ваша задача состоит в том, чтобы извлечь уникальные строки, которые появляются в таблице только один раз, или различные строки, включая уникальные и первые повторяющиеся вхождения. В любом случае, с надстройкой Duplicate Remover работа выполняется за несколько шагов.
- Выберите любую ячейку в исходной таблице и нажмите кнопку DuplicateRemover на вкладке AblebitsData в группе Dedupe.
Мастер Duplicate Remover запустится и выберет всю таблицу. Итак, просто нажмите « Далее», чтобы перейти к следующему шагу.
- Выберите тип значения, который вы хотите найти, и нажмите Далее :
- Уникальные
- Уникальные + 1 е вхождения (различные)
В этом примере мы хотим извлечь различные строки, которые появляются в исходной таблице хотя бы один раз, поэтому мы выбираем опцию Unique + 1st occurences:
На заметку. Как вы можете видеть на приведенном выше скриншоте, есть также 2 варианта поиска дубликатов. Просто имейте это в виду, если нужно будет искать повторы в таблице.
- Выберите один или несколько столбцов для проверки уникальных значений.
В этом примере мы хотим убрать все повторяющиеся значения на основе значений в 2 столбцах ( заказчик и товар), поэтому мы выбираем только нужные нам столбцы.
В нашем случае таблица имеет заголовок, поэтому отмечаем птичкой пункт My table has headers.
Думаю, нам не нужны пустые строки, которые могут случайно встретиться при объединении данных из разных таблиц. Поэтому отмечаем такжеSkip empty cells.
Если вдруг в наших записях случайно появились лишние пробелы, то, думаю, стоит их игнорировать. Поэтому отмечаем также Ignore extra spaces.
Также наш поиск буден нечувствителен к регистру, то есть не будем при сравнении данных различать прописные и строчные буквы. Поэтому не трогаем опцию Case-sensitive match.
- Выберите действие, которое нужно выполнить с найденными значениями. Вам доступны следующие варианты:
- Выделить цветом.
- Выбрать и выделить.
- Отметить в столбце статуса.
- Копировать в другое место.
Чтобы не менять исходные данные, выберите «Копировать в другое место» (Copy to another location), а затем укажите, где именно вы хотите видеть новую таблицу – на этом же листе (выберите параметр «Custom Location» и укажите верхнюю ячейку целевого диапазона), на новом листе (New worksheet) или в новой книге (New workbook).
В этом примере давайте выберем новый лист:
- Нажмите кнопку « Готово» , и все готово!
В итоге у нас осталось всего 20 записей.
Понравился этот быстрый и простой способ получить список уникальных значений или записей в Excel? Если да, то я рекомендую вам загрузить полнофункциональную ознакомительную версию Ultimate Suite и попробовать в работе Duplicate Remover.
В Ultimate Suite for Excel также включено много других полезных инструментов, которые помогут вам сэкономить много времени. Мы о них также будем подробно рассказывать в других материалах на сайте.
Как быстро объединить несколько файлов Excel — Мы рассмотрим три способа объединения файлов Excel в один: путем копирования листов, запуска макроса VBA и использования инструмента «Копировать рабочие листы» из надстройки Ultimate Suite. Намного проще обрабатывать данные в…
6 примеров — как консолидировать данные и объединить листы Excel в один — В статье рассматриваются различные способы объединения листов в Excel в зависимости от того, какой результат вы хотите получить: объединить все данные с выбранных листов,объединить несколько листов с различным порядком столбцов,объединить…
Как работать с мастером формул даты и времени — Работа со значениями, связанными со временем, требует глубокого понимания того, как функции ДАТА, РАЗНДАТ и ВРЕМЯ работают в Excel. Эта надстройка позволяет быстро выполнять вычисления даты и времени и без особых…
Как найти и выделить уникальные значения в столбце — В статье описаны наиболее эффективные способы поиска, фильтрации и выделения уникальных значений в Excel. Ранее мы рассмотрели различные способы подсчета уникальных значений в Excel. Но иногда вам может понадобиться только просмотреть уникальные…
Подсчет уникальных значений в Excel — В этом руководстве вы узнаете, как посчитать уникальные значения в Excel с помощью формул и как это сделать в сводной таблице. Мы также разберём несколько примеров счёта уникальных текстовых и числовых…