Excel формула список уникальных значений в

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

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

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

Формула массива будет выглядеть следующим образом:

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

9 комментариев

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

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

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

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

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

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

А если нужно создать список уникальных ячеек с диапазона, например B2:H15 ?

формула массива — это фантастика. Большое спасибо!

Excel формула список уникальных значений в

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

​Смотрите также​ куда выводить список​ по алфавиту, только​Приложите файл, в​ эту формулу в​ то. Но все​Kogarys​Подсчет количества уникальных текстовых​789​ случая того же​ значения​Примечание:​ выделенном диапазоне –​ (различающихся) значений в​ с помощью Условного​

​ ENTER​ списка имеются повторы​Из исходной таблицы отберем​ уникальных согласно вашего​ надо обратить внимание,​

​ котором не получается.​​ C2​​ равно спасибо.​: Всем привет!​ и числовых значений​235​

​ значения после первого​Руслан «,» Сергей «,»​Мы стараемся как​ белая и ее​ столбце.​

​ форматирования.​. Затем нужно скопировать​ — новый список​ только уникальные значения​

​ файла.​ что для первой​snipe​

​мы её должны скопировать​Владимир,​​Люди, помогите пожалуйста:​​ в диапазоне A2:A10​Руслан​ эта функция возвращает​ Сергей, Сергей​ можно оперативнее обеспечивать​​ адрес отображается в​​Необходимо выделить все ячейки​

​СОВЕТ:​
​ формулу вниз, например,​
​ уникальных значений не​

​ и выведем их​​Ktulu​​ строки одна формула,​​:​ и вставить​​- СПАСИБО ОГРОМНОЕ!​Есть два столбца​ без учета пустых​Дмитрий​ значение 0.​, имеется два уникальных​ вас актуальными справочными​ поле Имя.​ за исключением им​Список уникальных значений​ с помощью Маркера​ должен их содержать.​ в отдельный диапазон​: Добрый день, уважаемые​ а для последующих-другая.​trash50​в несколько строк​ То, что нужно!​ с данными. И​ ячеек и текстовых​689​Функция ПОИСКПОЗ используется для​ значения — Руслан​

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

​ с сортировкой по​
​ коллеги!​trash50​, если вдруг не​ так, чтобы точно​))))​ нужна формула, которая​ значений (6)​789​ возврата позиции текстового​ и Сергей. Если​ языке. Эта страница​нажмите ОК.​ уникальные. Выделять уникальные​ способами, например, с​ значения исходного списка​ в исходном списке​ возрастанию. Отбор и​Прошерстил поиск, но​:​ знаете что это​ отобразить все уникальные​trash50​ выводила бы в​

Выделение уникальных значений в MS EXCEL

​Примечания:​Дмитрий​ значения в диапазоне.​ диапазон содержит числа​

​ переведена автоматически, поэтому​Будут выделены все неповторяющиеся​ значения будем с​ использованием Расширенного фильтра​ были гарантировано отображены​ выделены цветом с​ сортировку сделаем с​

• ​ точного ответа так​trash50​​ такое:​​ элементы. ​
• ​: Получить список уникальных​ третий столбец массив​ ​143​ Функция частота использует​
• ​5, 6, 7, 6​

​ ее текст может​ значения и, дополнительно,​ помощью Условного форматирования​ (см. статью Отбор​ в списке уникальных​​ помощью Условного форматирования.​​ помощью одной формулой​ и не нашёл.​, что то я​при наведении курсора​Ссылка: (Способ 2.​ (не повторяющихся) значений​ из уникальных значений,​

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

​ значений, необходимо сделать​Список уникальных значений должен​ массива. Формула работает​Нужно просуммировать уникальные​ не пойму что​ на правый нижний​ Формулы)​ (из столбцов) -​ состоящий из СЦЕПКИ​ примере должны быть​237​ соответствующих текстовых значений.​ 5, 6 и​ грамматические ошибки. Для​ будут выделены только​выделите диапазон содержащий список​

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

​ помощью Расширенного фильтра),​​ размер списка уникальных​ быть отсортирован по​ как для текстовых​ значения в диапазоне​ вы делаете. в​ угол ячейки курсор​Немогу применить данную​ неудается применить формулу.​ первых двух столбцов.​ введены как формулы​67​Функция LEN используется для​ 7.​ нас важно, чтобы​ первые (самые верхние)​ значений, например,​ Сводных таблиц или​ значений равным размеру​ алфавиту.​ (сортировка от А​Для 2003 Экселя.​

​ вашей формуле диапазон​ сменит вид на​ формулу.​Понравилась формула (массива):​Файлик прилагаю​ массива. Выделите все​235​​ поиска пустых ячеек.​Чтобы подсчитать только уникальные​​ эта статья была​ дубликаты.​А3:А16​ через меню Данные/​​ исходного списка (на​​Список уникальных значений создадим​ до Я), так​Заранее спасибо​

​ данных (первый аргумент)​ крестик, жмем ЛКМ​(P.S. времени и​

​Формула​ Пустые ячейки иметь​

​ значения, используйте следующие​ вам полезна. Просим​Для выделения повторяющихся значений​;​ Работа с данными/​ тот случай, когда​ в столбце​ и для числовых​buchlotnik​ на одном листе​ (левую кнопку мыши)​ методов ‘копирования’ (/’вставки’)​список с данными​

​: Расширенный фильтр, только​ нажмите клавишу F2​Описание (результат)​ длину 0 (количество​ функции:​ вас уделить пару​

​ в EXCEL существуют​вызовите Условное форматирование (Главная/​ Удалить дубликаты. У​ все значения исходного​B​

​ значений (сортировка от​: можно так (массивка):​ большой, а проверку​ и не отпуская​ потратил недопустимо много).​ у Вас расположен​

​ и нажмите клавиши​

​Используйте функции Если для​

​ секунд и сообщить,​

​ уже заранее созданные​

​ Стили/ Условное форматирование/​

​ каждого способа есть​

​ списка не повторяются).​

​с помощью формулы​

​ делаете по диапазону​

​Прошу Вашей помощи.​

​ в столбце А(А1:А51,​

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

​Скопируйте таблицу в ячейку​

​ присвоения значения 1​

​ помогла ли она​ правила Условного форматирования.​ Создать правило/ Использовать​ свои преимущества и​ В случае наличия​

​ массива (см. файл​

​Эта статья — продолжение​чуть короче можноКод=СУММ(ЕСЛИ(C4:C33;C4:C33/СЧЁТЕСЛИ(C4:C33;C4:C33);))​ намного меньше указанного​ или в нужном​AleksSid​ где А1 –​

​ + Ввод.​ значений в диапазоне​ A1 на пустом​ каждому истинному условию.​ вам, с помощью​ Если вызвать команду​

​ формулу для определения​​ недостатки. Но, в​

​ в исходном списке​ примера). Для этого​ статьи Отбор уникальных​vikttur​ в первом аргументе.​ вам направлении.​: Я думаю будут​ заголовок). Выводить список​ года тому назад. ​

​Чтобы просмотреть процесс вычисления​ A2:A10 без учета​ листе в Excel​Используется функция сумм для​ кнопок внизу страницы.​​ меню Главная/ Стили/​​ форматируемых ячеек);​​ этой статье нам​​ большого количества повторяющихся​​ введите следующую формулу​​ значений (убираем повторы​

Вывод списка уникальных значений (формулой)

​: Форула массива:​​Vlad999​
​trash50​
​ и другие варианты.​ мы будем в​-​ функции по шагам,​ пустых ячеек и​ для работы с​ сложения уникальных значений.​ Для удобства также​
​ Условное форматирование/ Правила​

​введите формулу =ПОИСКПОЗ(A3;$A$3:$A$16;0)=СТРОКА(A3)-СТРОКА($A$2)​​ требуется, чтобы при​ значений, список уникальных​

​ в ячейку​​ из списка) в​=СУММ(ЕСЛИОШИБКА(C4:C33/СЧЁТЕСЛИ(C4:C33;C4:C33);))​
​: это ошибочно.​

​: а сводная таблица​​ Код =ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ($B$2:$B$16;$B$2:$B$16;0)=СТРОКА($A$1:$A$15);СТРОКА($A$1:$A$15));СТРОКА(A1)));»»)​​ столбец С, начиная​
​Владимир​

​ выделите ячейку с​​ текстовых значений (4)​​ примерами формул, использующих​​Функция частота позволяет выполнить​ приводим ссылку на​ выделения ячеек/ Повторяющиеся​Обратите внимание, что в​
​ добавлении новых значений​​ значений можно сделать​B6​ ​ MS EXCEL.​

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

​Как обычная, должна​​- проверка должна​ не подойдет под​Vlad999​ с ячейки С2.​
​:​
​ формулой, а затем​
​=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(B2:B10,B2:B10,0),ПОИСКПОЗ(B2:B10,B2:B10,0))>0,1))​ функции.​ подсчет количества уникальных​ оригинал (на английском​ значения, и диалоговом​ формуле использована относительная​ в исходный список,​ меньшего размера, удалив​:​В столбце​
​ быть шустрее:​ осуществлятся по всему​ это действо?​:​ Формула в C2​=ЕСЛИОШИБКА(ИНДЕКС($B$5:$B$8&», «&$C$5:$C$8;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ($B$5:$B$8&», «&$C$5:$C$8;$B$5:$B$8&», «&$C$5:$C$8;)=СТРОКА($A$5:$A$8)-4;СТРОКА($A$5:$A$8)-4);СТРОКА(A1)));»»)​ на вкладке​​Подсчет количества уникальных текстовых​Данные​​ значений. Эта функция​ языке) .​ окне выбрать уникальные,​ адресация, поэтому активной​
​ список уникальных значений​ лишние формулы, чтобы​
​=ИНДЕКС(Список_Текст;​А​
​Код=СУММПРОИЗВ(—(ПОИСКПОЗ(C4:C33;—C4:C33;)=СТРОКА(C4:C33)-3);C4:C33)​ большому диапазону, но​Czeslav​
​trash50​

​ будет следующая:​​Формула массива.​Формулы​ и числовых значений​

​Данные​​ игнорирует текстовые и​​Предположим, что вы хотите​​ то будут выделены​ ячейкой при вводе​ должен автоматически обновляться,​ исключить ненужные вычисления,​НАИМЕНЬШИЙ(ЕСЛИ(НАИМЕНЬШИЙ(​
​имеется список с​Она же, формула​

​ уменя так не​​: Промучился полтора дня​​, ее надо просто​​Код <=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))>вводится​Kogarys​в группе​
​ в диапазоне B2:B10,​986​ нулевые значения. Первое​ узнать, сколько уникальных​ неповторяющиеся значения. О​ формулы должна быть​ поэтому здесь построен​ тормозящие пересчет листа.​ЕСЛИ(СЧЁТЕСЛИ($B$5:B5;Список_Текст)=0;СЧЁТЕСЛИ(Список_Текст;»​ повторяющимися значениями, например​

​ массива:​​ работает.​Прикладываю файл.​ растянуть, если копировать/вставить​

​ формула в ячейку​​:​
​Зависимости формул​

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

​После ввода формулы вместо​​ список с названиями​​Код=СУММ(ЕСЛИ(ПОИСКПОЗ(C4:C33;—C4:C33;)=СТРОКА(C4:C33)-3;C4:C33))​​опишите словами что​Vlad999​ — тогда каждый​ сочетанием клавиш –​Z,​нажмите кнопку​ содержать пустые ячейки​Дмитрий​ она возвращает число​ значений в диапазоне​

​ значений читайте в​​(т.е. диапазон нужно​
​ формул.​: в формуле использован​ENTER​ городов.​Ktulu​

​ с чем нужно​: Ещё один вариант.​ раз вводить как​ Ctrl+Shift+Enter. После того,​-снкс,посмотрел темку, топикстартер​Вычисление формулы​

Сумма уникальных значений в диапазоне (Формулы/Formulas)

​ (7)​​563​ равно количество вхождений​
​ есть. Например, если​ статье Классификация значений​ выделять сверху вниз).​
​Настроим Условное форматирование для​ Динамический диапазон Список_текст.​
​нужно нажать​
​В некоторых ячейках исходного​

​: Большое спасибо!​​ сравнивать и от​ ​Уникальные значения вытягиваются​ ​ формулу массива.​

​ как мы ввели​​ хотел немножко не​
​.​
​=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»),ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»))>0,1))​67​
​ значения. Для каждого​
​ столбец содержит текст​ по уникальности.​
​ Активная ячейка в​

​ выделения только уникальных​​ Значения ошибки скрыты​

Excel формула список уникальных значений в

Предположим, что у Вас есть вот такой файл по продажам региональных менеджеров:

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

ПРОБЛЕМА: Как формулами извлечь уникальные значения?

РЕШЕНИЕ: Формула массива (вводится нажатием Ctrl+Shift+Enter):

КАК ЭТО РАБОТАЕТ: Функция ПОИСКПОЗ, сравнивающая два массива возвращает ИСТИНА только в том случае, если вхождение искомого значения в массив является первым. В результате работы ПОИСКПОЗ мы получаем массив: <ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ и т.д.>.
С помощью функции ЕСЛИ мы сравниваем получившийся массив с массивом <1:2:3:4:5:6:7:8 и т.д.>, полученном в результате работы функции СТРОКА с заданным диапазоном и в случае если ПОИСКПОЗ вернул ИСТИНА получаем номер строки вхождения.

Собственно говоря задача решена. Теперь остаётся только оформить итог списком. Для этого используем функцию НАИБОЛЬШИЙ, которая создаст вариативный ряд сначала из чисел, потом из значений ЛОЖЬ и функцию ИНДЕКС, которая вернёт нам необходимые текстовые значения из соответствующего массива. В жёлтых ячейках итог:

МИНУСЫ: Формулы массивов сильно замедляют скорость пересчёта листа.

ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая версия Excel

ПРИМЕЧАНИЯ: Для устранения значения ошибки можно использовать проверку на ошибку согласно Вашей версии Excel или использовать Условное Форматирование

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

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

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

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

Для наглядности уникальные значения в исходном списке выделены цветом с помощью Условного форматирования.

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

Для создания Динамического диапазона:

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя;
• в поле Имя введите: Исходный_список;
• в поле Диапазон введите формулу =СМЕЩ(УникальныеЗначения!$A$5;;; СЧЁТЗ(УникальныеЗначения!$A$5:$A$30))
• нажмите ОК.

Список уникальных значений создадим в столбце B с помощью формулы массива (см. файл примера ). Для этого введите следующую формулу в ячейку B5:

После ввода формулы вместо ENTER нужно нажать CTRL + SHIFT + ENTER. Затем нужно скопировать формулу вниз, например, с помощью Маркера заполнения. Чтобы все значения исходного списка были гарантировано отображены в списке уникальных значений, необходимо сделать размер списка уникальных значений равным размеру исходного списка (на тот случай, когда все значения исходного списка не повторяются). В случае наличия в исходном списке большого количества повторяющихся значений, список уникальных значений можно сделать меньшего размера, удалив лишние формулы, чтобы исключить ненужные вычисления, тормозящие пересчет листа.

Разберем работу формулу подробнее:

• Здесь использование функции СЧЁТЕСЛИ() не совсем обычно: в качестве критерия (второй аргумент) указано не одно значение, а целый массив Исходный_список , поэтому функция возвращает не одно значение, а целый массив нулей и единиц. Возвращается 0, если значение из исходного списка не найдено в диапазоне B4:B4(B4:B5 и т.д.), и 1 если найдено. Например, в ячейке B5 формулой СЧЁТЕСЛИ(B$4:B5;Исходный_список) возвращается массив <1:0:0:0:0:0:0:1:0:0:0:0:1:1:0>. Т.е. в исходном списке найдено 4 значения «ООО Рога и копытца» (B5). Массив легко увидеть с помощью клавиши F9 (выделите в Строке формул выражение СЧЁТЕСЛИ(B$4:B5;Исходный_список) , нажмите F9: вместо формулы отобразится ее результат);
• ПОИСКПОЗ() – возвращает позицию первого нуля в массиве из предыдущего шага. Первый нуль соответствует значению еще не найденному в исходном списке (т.е. значению «ОАО Уважаемая компания» для формулы в ячейке B5);
• ИНДЕКС() – восстанавливает значение по его позиции в диапазоне Исходный_список ;
• ЕСЛИОШИБКА() подавляет ошибку, возникающую, когда функция ПОИСКПОЗ() пытается в массиве нулей и единиц, возвращенном СЧЁТЕСЛИ() , найти 0, которого нет (ситуация возникает в ячейке B12, когда все уникальные значения уже извлечены из исходного списка).

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

Примечание. Функция ЕСЛИОШИБКА() будет работать начиная с версии MS EXCEL 2007, чтобы обойти это ограничение читайте статью про функцию ЕСЛИОШИБКА() . В файле примера имеется лист Для 2003, где эта функция не используется.

Решение для списков с пустыми ячейками

Если исходная таблица содержит пропуски, то нужно использовать другую формулу массива (см. лист с пропусками файла примера ):
=ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19;
ПОИСКПОЗ( 0;ЕСЛИ(ЕПУСТО($A$5:A19);»»;СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0)
);»»)

Решение без формул массива

Для отбора уникальных значений можно обойтись без использования формул массива. Для этого создайте дополнительный служебный столбец для промежуточных вычислений (см. лист «Без CSE» в файле примера ).

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

СОВЕТ2: Для тех, кто создает список уникальных значений для того, чтобы в дальнейшем сформировать на его основе Выпадающий список, необходимо учитывать, что вышеуказанные формулы возвращают значение Пустой текст «», который требует аккуратного обращения, особенно при подсчете значений (вместо обычной функции СЧЕТЗ() нужно использовать СЧЕТЕСЛИ() со специальными аргументами). Например, см. статью Динамический выпадающий список в MS EXCEL.

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