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

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

Введение

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

Рассмотрим особенности создания выпадающих списков на примере:

Исходные данные:

• Список адресов в разных городах

Задача:

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

Мы будем двигаться поэтапно, уделяя внимание всем возможностям данного инструмента.

Скачать файлы из этой статьи

Рабочие файлы

Обзорное видео о работе с выпадающими списками в Excel и Google таблицах смотрите ниже. Приятного просмотра!

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

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

Выпадающий список готов!

Такой способ позволяет представить обычный диапазон в виде выпадающего списка. Повторы данных остались в списке (в диапазоне A2:A16 названия городов повторяются и в выпадающем списке они также повторяются). Это, конечно, не удобно. О том, как сделать выпадающий список уникальных значений в Excel мы поговорим далее, пока остановимся на этом варианте.

Как создать зависимый выпадающий список в Excel?

Существует несколько вариантов. Один из них, это сочетание именованных диапазонов и функции ДВССЫЛ.

Именованный диапазон в Excel – это ячейка (или диапазон ячеек), которой присвоено имя.

Функция ДВССЫЛ в Excel преобразовывает текст в ссылку.

Способ 1: именованные диапазоны + функция ДВССЫЛ

Для начала создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

Алгоритм создания именованного диапазона: выделяем диапазон, далее «Формулы» – «Задать имя».

У нас получится 5 именованных диапазона: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

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

Поэтому, вместо дефисов в названии города Ростов-на-Дону мы укажем допустимый символ – нижнее подчеркивание.

Именованные диапазоны готовы.

Теперь выбираем ячейку для второго выпадающего списка, того, который будет зависимым. Переходим к инструменту «Проверка данных», тип данных – «Список». В поле «Источник» указываем функцию: =ДВССЫЛ(D2), где D2 – это адрес ячейки с первым выпадающим списком городов.

В ячейке D2, которая используется в качестве аргумента функции ДВССЫЛ, находится текстовое выражение, которое совпадает с именем соответствующего именованного диапазона с названиями городов. В результате функция возвращает ссылку на соответствующий именованный диапазон.

Зависимый выпадающий список адресов готов.

Меняя значения в ячейке D2, меняются списки в ячейке E2. За исключением города Ростов-на-Дону. В выпадающем списке городов (ячейка D2), в названии используется дефис, а в именованном диапазоне – нижнее подчеркивание.

Чтобы устранить это несоответствие, перед тем как применять функцию ДВССЫЛ, обработаем значения функцией ПОДСТАВИТЬ.

Функция ПОДСТАВИТЬ заменяет определенный текст в текстовой строке на новое значение. Вместо: =ДВССЫЛ(D2) укажем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;"-";"_"))

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

Теперь зависимый выпадающий список работает и для города, содержащего в названии дефисы – Ростов-на-Дону. Вернемся к выпадающему списку городов.

Как автоматически обновить выпадающий список в Excel, при добавлении новых данных?

Для начала создадим из диапазона данных «умную» таблицу Excel. Сделать это можно сочетанием клавиш Ctrl+T.

Одним из полезных свойств умной таблицы является растягивающийся диапазон. То есть, если мы будем добавлять новые строки, они автоматически будут попадать в выпадающий список. Например, добавим новый город – Санкт-Петербург. И вот, он уже появился в нашем первом выпадающем списке.

Как сделать выпадающий список уникальных значений в Excel?

Надоело смотреть на повторяющиеся названия городов в выпадающем списке. Реализуем выпадающий список так, чтобы названия городов в нем не повторялись. Для этого, добавим слева вспомогательный столбец. Мы дали ему название – «Уникальные».

И включим новый столбец в диапазон «умной» таблицы. «Конструктор» – «Размер таблицы». Вместо =$B$1:$C$17 указываем: =$A$1:$C$17

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

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

=ЕСЛИОШИБКА(ИНДЕКС([Город];ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$1:A1; [Город]);0));"")

Чтобы Excel воспринял нашу формулу, как формулу массива, жмем Ctrl + Shift + Enter.

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

Из списка уникальных городов создадим именованный диапазон (мы назвали его — «Уникальные»), который затем используем в качестве источника для выпадающего списка городов.

«Проверка данных» – «Список». В источнике данных, вместо предыдущего диапазона с названиями городов =$B$2:$B$18, задаем имя – =Уникальные

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

Чтобы их убрать, доработаем именованный диапазон «Уникальные». В диспетчере имен, вместо диапазона =Таблица1[Уникальные] используем: =СМЕЩ(Лист1!$A$2;0;0;СЧЁТЗ(Таблица1[Уникальные])-СЧИТАТЬПУСТОТЫ(Таблица1[Уникальные]))

где: Лист1!$A$2 – ячейка со значением первого пункта списка уникальных значений

Таблица1[Уникальные] – столбец с перечнем всех пунктов списка

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

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

Как сделать автоматически обновляемый зависимый список? Способ 2: СМЕЩ+ПОИСКПОЗ+СЧЁТЕСЛИ

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

В ячейку F2 (зависимый выпадающий список адресов) вместо: =ДВССЫЛ(ПОДСТАВИТЬ(E2;"-";"_")) вставляем: =СМЕЩ($B$2;ПОИСКПОЗ(E2;$B$2:$B$18;0)-1;1;СЧЁТЕСЛИ($B$2:$B$18;E2);1)

Для корректной работы этого способа, данные в столбце с городом должны быть отсортированы. Функция СМЕЩ будет динамически ссылаться только на ячейки адресов определенного города.

Аргументы функции:

Ссылка – берем первую ячейку нашего списка, т.е. $B$2

Смещение по строкам – считает функция ПОИСКПОЗ, которая выдает порядковый номер ячейки с выбранным городом (E2) в заданном диапазоне ($B$2:$B$18)

Смещение по столбцам = 1, т.к. мы хотим сослаться на адреса в соседнем столбце (С)

Высота – вычисляем с помощью функции СЧЁТЕСЛИ, которая подсчитывает количество встретившихся в диапазоне ($B$2:$B$18) нужных нам значений – названий городов (E2)

Ширина = 1, т.к. нам нужен один столбец с адресами

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

С выпадающими списками в Google таблицах все немного иначе.

Выпадающий список в Google таблицах

В Google таблицах есть аналогичный инструмент для создания выпадающих списков – «Проверка данных».

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

«Данные» – «Настроить проверку данных» – «Значение из диапазона»

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

Зависимый выпадающий список в Google таблицах

Возвращаемся к двум основным способам, которые мы рассмотрели в Excel.

Способ 1: именованные диапазоны + ДВССЫЛ

Создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

Выделяем ячейки – «Данные» – «Настроить именованные диапазоны»

Указываем имя и жмем готово. У нас получится 5 именованных диапазонов: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

Также, как и в Excel, в Google таблицах к именам диапазонов есть список требований.

Поэтому, вместо дефисов в названии города Ростов-на-Дону укажем допустимый символ – нижнее подчеркивание.

В Google таблицах мы не сможем подобно Excel задать функцию ДВССЫЛ в инструменте «Проверка данных». Поэтому, разместим результат функции ДВССЫЛ в пустых ячейках правее. Не забываем добавить обработку значений от дефисов функцией ПОДСТАВИТЬ. Подробнее о том, для чего это нужно, мы говорили ранее в примере Excel.

В ячейке F1 введем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;"_";"-"))

Последний штрих в создании зависимого выпадающего списка, в разделе «Настроить проверку данных», в качестве диапазона указываем список из столбца F:F.

При дальнейшей работе вспомогательный столбец F можно скрыть. Минус такого метода – отсутствие динамичности. Если мы добавим новый город и адрес, то они не появятся в созданных выпадающих списках. Но это решаемо!

Как автоматически обновить выпадающий список в Google таблицах при добавлении новых данных?

В выпадающем списке городов, достаточно расширить диапазон и вместо =$A$2:$A$16 указать: =$A$2:$A. Теперь при добавлении нового города он автоматически появляется в выпадающем списке.

Как автоматически обновить зависимый выпадающий список в Google таблицах при добавлении новых данных?

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

В ячейке G6 укажем:

=СМЕЩ($A$2;ПОИСКПОЗ($D$7;$A$2:$A;0)-1;1;СЧЁТЕСЛИ($A$2:$A;$D$7);1)

Важно: для корректной работы этого способа, данные в столбце с городом должны быть отсортированы от А до Я, или от Я до А. Подробнее о том, как в данном случае работает функция СМЕЩ читайте выше в примере с Excel.

Заключительным этапом поместим результат функции СМЕЩ в диапазон выпадающего списка.

Скроем вспомогательные столбцы для удобства.

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

Заключение

Теперь Вам известны несколько способов, как создать выпадающие списки в Excel и Google таблицах. Смотрите примеры и создавайте нужные Вам выпадающие списки.

Изучить работу в программе Excel Вы можете на наших курсах: бесплатные онлайн-курсы по Excel

Пройдите бесплатный тест на нашем сайте, чтобы объективно оценить свой уровень владения инструментами и функциями программы Excel: пройти бесплатный тест

У нас Вы можете заказать выполнение задач по MS Excel и Google таблицам

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

Пусть в столбце

А

имеется список с

повторяющимися

значениями, например список с названиями компаний.

Задача

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

Для наглядности уникальные значения в исходном списке выделены цветом

с помощью Условного форматирования

.

Решение

Для начала создадим

Динамический диапазон

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

Динамический диапазон

и нижеследующие формулы не придется модифицировать.

Для создания

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

:

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

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

B

с помощью

формулы массива

(см.

файл примера

). Для этого введите следующую формулу в ячейку

B5

:

=ЕСЛИОШИБКА(ИНДЕКС(Исходный_список; ПОИСКПОЗ(0;СЧЁТЕСЛИ(B$4:B4;Исходный_список);0));»»)

После ввода формулы вместо

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 EXCE

L.

Примечание

: В статье

Восстанавливаем последовательности из списка без повторов в MS EXCEL

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

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

​Смотрите также​Application.Undo​ это делать, описано​ список.​ Target As Range)​

​ Получаем следующий вид​​ для проверки введенных​​PS: alx74-Расширенный фильтр​: Да не такая​ что здесь я​

Задача

​: Ещё одна немассивная​ Шифт Ентер​ функцию ИНДЕКС, которая​

​ уникальные фамилии продавцов.​ компании (столбцы​ примера).​1. Введите в ячейку​

Решение с использованием объединенного списка

​Имея два списка с​oldval = Target​ выше. С его​Открываем параметры проверки данных.​ Dim lReply As​ диапазона:​ данных. Повысить комфорт​​ не подходит потому​​ она уж и​
​ не помогу…​
​ формула.​

​sv2014​ вернёт нам необходимые​ Т.е. должен получиться​HI​Создадим таблицу, состоящую только​А9​ однотипными повторяющимися значениями,​If Len(oldval) <>​ помощью справа от​ В поле «Источник»​ Long If Target.Cells.Count​Ставим курсор в ячейку,​ работы с данными​

​ что слишком много​ простая. Здесь уже​Болезнь не позволяет​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=IFERROR(INDEX($B$2:$B$123;MATCH(1;INDEX((COUNTIF($H$1:H1;$B$2:$B$123)=0)*(A$2:A$123=G$1);0);0));»»)​

​: Sancho, добрый день​​ текстовые значения из​​ такой список:​
​).​
​ из строк с​

​название новой компании​ создадим из них​ 0 And oldval​ выпадающего списка будут​

Тестируем

​ вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).​​ > 1 Then​​ где будет находиться​ позволяют возможности выпадающих​
​ телодвижений​ решали , и​ мне использовать мозг​astradewa​ вариант с макросом,кнопка​

Решение без использования объединенного списка

​ соответствующего массива. В​Козлов​1. Выберите в желтой​ уникальными названиями компаний.​ – ООО Фортик​
​ список, содержащий только​
​ <> newVal Then​
​ добавляться выбранные значения.Private​
​Имя файла, из которого​

​ Exit Sub If​ выпадающий список. Открываем​ списков: подстановка данных,​​, а надо​​ формулами, и макросом.​ на полную катушку,​

excel2.ru

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

​: На сайте www.excel-vba.ru​ yyy в файл-примере​ жёлтых ячейках итог:​Смирнов​ ячейке значение Рублевый;​ Уникальные значения будем​2. В таблице​ уникальные значения.​Target = Target​ Sub Worksheet_Change(ByVal Target​ берется информация для​ Target.Address = «$C$2″​ параметры инструмента «Проверка​ отображение данных другого​ чтобы при заполнении​alx74​

​ а стандартного решения​ The_Prist (Щербаков Дмитрий)​200?’200px’:»+(this.scrollHeight+5)+’px’);»> Sub zzz()​

​МИНУСЫ​Кузнецов​2. В столбце​ выбирать не из​ Уникальные значения через​Пусть столбцы​ & «,» &​ As Range) On​ списка, заключено в​ Then If IsEmpty(Target)​ данных» (выше описан​ листа или файла,​

Задача

​ таблицы данными​: Добрый день. Подскажите,​ формулами Вашей задачи​ нашёл формулу вывода​Dim z, i&​: Формулы массивов сильно​Сидоров​G​ всех названий компаний,​ объединенный список будет​

Решение

​АВ​ newVal​ Error Resume Next​​ квадратные скобки. Этот​​ Then Exit Sub​​ путь). В поле​​ наличие функции поиска​​автоматом в назначенный​​ может я чего​ нет​ уникальных значений из​z = Range(«A2:B»​ замедляют скорость пересчёта​Петров​будет выведен список​ а сначала из​

​ автоматически выведено название​​содержат списки названий​​Else​ If Not Intersect(Target,​ файл должен быть​

​ If WorksheetFunction.CountIf(Range(«Деревья»), Target)​ «Источник» прописываем такую​ и зависимости.​​ столбец выводились уникальные​​ не понимаю: Почему​
​astradewa​
​ столбца в назначенный​
​ & Range(«A» &​

​ листа.​​Иванов​​ названий компаний с​ компаний с типом​ новой компании.​ компаний, в некоторых​

​Target = newVal​ Range(«Е2:Е9»)) Is Nothing​ открыт. Если книга​ = 0 Then​ функцию:​Путь: меню «Данные» -​ значения.​ нельзя решить Расширенным​: Serge_007, спасибо за​​ столбец. Помогите переделать​​ Rows.Count).End(xlUp).Row).Value​

Тестируем

​ОБЛАСТЬ ПРИМЕНЕНИЯ​ПРОБЛЕМА​
​ типом контракта –​​ контракта Валютный, затем​​В принципе, можно обойтись​ ячейках имеются дубликаты.​End If​ And Target.Cells.Count =​
​ с нужными значениями​ lReply = MsgBox(«Добавить​Протестируем. Вот наша таблица​ инструмент «Проверка данных»​Ещё раз всем​
​ фильтром? Пример простой​​ участие, желаю Вам​​ формулу так, чтобы​With CreateObject(«scripting.dictionary»): .comparemode​: Любая версия Excel​: Как формулами извлечь​
​ Рублевый:​​ — Рублевый.​​ и без объединенного​Создадим список, состоящий только​If Len(newVal) =​
​ 1 Then Application.EnableEvents​ находится в другой​​ введенное имя «​​ со списком на​ — вкладка «Параметры».​

​ Большое спасибо.​ и фильтр там​
​ скорейшего выздоровления и​ она выбирала и​ = 1​ПРИМЕЧАНИЯ​ уникальные значения?​- в исходной​Для удобства создадим динамические​

​ списка. Платой за​ из уникальных значений​

​ 0 Then Target.ClearContents​
​ = False If​ папке, нужно указывать​ & _ Target​ одном листе:​

excel2.ru

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

​ Тип данных –​Kogarys​ работает. Пробовал вчера​ вообще кавказского здоровья​

​ выводила в столбец​For i =​: Для устранения значения​РЕШЕНИЕ​ таблице Условным форматированием​

​ & » в​​Добавим в таблицу новое​ «Список».​
​: Всем привет!​​ и в посте​и долголетия​​ уникальные значения из​​ 1 To UBound(z,​
​ ошибки можно использовать​
​: Формула массива (вводится​
​ будут выделены строки​​ компаний из столбца​​ усложнение формулы массива:​​Решим задачу двумя способами:​End If​ 0 Then Target.Offset(0,​Возьмем три именованных диапазона:​ выпадающий список?», vbYesNo​ значение «елка».​Ввести значения, из которых​​Люди, помогите пожалуйста:​​ 2 написал об​Serge_007​
​ диапазона А2:Е20, если​ 1)​ проверку на ошибку​ нажатием​ с типом контракта​А​​=ЕСЛИОШИБКА(ЕСЛИОШИБКА(​​ через промежуточное создание​End Sub​ 1) = Target​​Это обязательное условие. Выше​​ + vbQuestion) If​Теперь удалим значение «береза».​

​ будет складываться выпадающий​Есть два столбца​ этом, правда ТС​: Спасибо!​ это возможно. Спасибо​If z(i, 1)​ согласно Вашей версии​Ctrl+Shift+Enter​ Рублевый;​), Тип_контракта (столбец​ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($F$4:F4;Список1);0));​ объединенного списка и​Не забываем менять диапазоны​ Else Target.End(xlToRight).Offset(0, 1)​

​ описано, как сделать​​ lReply = vbYes​Осуществить задуманное нам помогла​ список, можно разными​
​ с данными. И​​ промолчал.​
​Но видимо пора​​ за помощь.​ = «вспомогательное» Then​ Excel или использовать​): Code =ИНДЕКС($B$2:$B$16;НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ($B$2:$B$16;$B$2:$B$16;0)=СТРОКА($A$1:$A$15);СТРОКА($A$1:$A$15));СТРОКА(A1)))​- в столбце​B​

excelworld.ru

Выборка уникальных значений по условию из второго столбца (Формулы/Formulas)

​ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($F$4:F4;Список2);0))​​ без него.​
​ на «свои». Списки​ = Target End​ обычный список именованным​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​ «умная таблица», которая​ способами:​ нужна формула, которая​
​alx74​ на свалку​alx74​ .Item(z(i, 2)) =​ Условное Форматирование​В английской версии:​H​

​) и Продажи (столбец​​);»»)​
​В статье Объединение списков​​ создаем классическим способом.​
​ If Target.ClearContents Application.EnableEvents​

​ диапазоном (с помощью​​ 1, 1) =​ легка «расширяется», меняется.​Вручную через «точку-с-запятой» в​​ выводила бы в​

​:​​ikki​: Что мешает использовать​ 0​
​Sancho​
​Code =INDEX($B$2:$B$16,LARGE(IF(MATCH($B$2:$B$16,$B$2:$B$16,0)=ROW($A$1:$A$15),ROW($A$1:$A$15)),ROW(A1)))​будут выведены суммарные​
​С​
​Для вывода промежуточных результатов​ изложено решение создания​ А всю остальную​

​ = True End​​ «Диспетчера имен»). Помним,​ Target End If​Теперь сделаем так, чтобы​
​ поле «Источник».​
​ третий столбец массив​
​Serge_007​: макрос или UDF​ Расширенный фильтр?​
​Next i​: Всем добрых суток.​
​КАК ЭТО РАБОТАЕТ:​ продажи отобранных компаний​). Если в таблицу​
​ вычислений можно воспользоваться​ объединенного списка из​ работу будут делать​ If End Sub​
​ что имя не​
​ End If End​ можно было вводить​
​Ввести значения заранее. А​
​ из уникальных значений,​

​, собственно увидел, что​​ не подходят?​
​Для 2007 и​Range(«I2»).Resize(.Count, 1) =​Пошарил форум, решений​Функция​ (по данному типу​ будет добавлена новая​ клавише​
​ 2-х списков. Воспользуемся​ макросы.​Чтобы выбранные значения показывались​ может содержать пробелов​ If End Sub​ новые значения прямо​ в качестве источника​ состоящий из СЦЕПКИ​ решается просто, а​
​ikki​ 2010: Вкладка Данные-Сортировка​ Application.Transpose(.Keys)​ много, но видимо​ПОИСКПОЗ​ контракта);​

​ строка, то ее​​F9​ этим решением и​На вкладке «Разработчик» находим​ снизу, вставляем другой​

​ и знаков препинания.​​Сохраняем, установив тип файла​ в ячейку с​
​ указать диапазон ячеек​

excelworld.ru

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

​ первых двух столбцов.​​ сейчас сомневаюсь, когда​: эт куда сабрался?​ и фильтр-Дополнительно-Ставим галку​End With​ придется разбираться на​, сравнивающая два массива​- в столбце​ значения будут автоматически​, выделив интересующую часть​ создадим объединенный список,​ инструмент «Вставить» –​ код обработчика.Private Sub​Создадим первый выпадающий список,​

​ «с поддержкой макросов».​​ этим списком. И​ со списком.​
​Файлик прилагаю​ разговор пошел про​ста-ять!!!​ Только уникальные записи-Переключатель​End Sub​ своем примере. Извините​ возвращает ИСТИНА только​I​

​ включены в Динамические​​ формулы.​ записав в ячейке​ «ActiveX». Здесь нам​ Worksheet_Change(ByVal Target As​ куда войдут названия​Переходим на лист со​ данные автоматически добавлялись​​Назначить имя для диапазона​Z​
​ макросы от старожил​рановато ещё.​ скопировать результат в​Sancho​ если уже вопрос​ в том случае,​будет выведено количество​ диапазоны и нижеследующие​Имеется таблица, состоящая их​С5​ нужна кнопка «Поле​

​ Range) On Error​​ диапазонов.​​ списком. Вкладка «Разработчик»​​ в диапазон.​ значений и в​: Расширенный фильтр, только​ и ветеранов. Вот​
​Serge_007​ другое место. Указываете​: Здравствуйте, Serge.​ вызывает у вас​ если вхождение искомого​ продаж (кол-во контрактов​

​ формулы не придется​​ нескольких столбцов. В​формулу​ со списком» (ориентируемся​ Resume Next If​​Когда поставили курсор в​

​ — «Код» -​​Сформируем именованный диапазон. Путь:​
​ поле источник вписать​ уникальные…​

​ я и подумал,​​: Пора-пора…​ диапазоны Исходный и​

​Что то с​​ «оскомину».​​ значения в массив​
​ выбранного типа).​

​ модифицировать.​​ одном из столбцов​
​=ЕСЛИ(СТРОКА()-СТРОКА($C$4)>СЧЁТЗ(Список1);​ на всплывающие подсказки).​

​ Not Intersect(Target, Range(«Н2:К2»))​​ поле «Источник», переходим​ «Макросы». Сочетание клавиш​ «Формулы» — «Диспетчер​ это имя.​Z​ может чего не​Простую задачу не​
​ Куда поместить результат.​

​ кодом не то,​​Требуется выборка уникальных​​ является первым. В​​3. Теперь в​​Также создадим в ячейке​​ имеются повторяющиеся текстовые​ИНДЕКС(Список2;СТРОКА(C5)-СЧЁТЗ(Список1)-СТРОКА($B$4));A5)​Щелкаем по значку –​
​ Is Nothing And​ на лист и​ для быстрого вызова​ имен» — «Создать».​Любой из вариантов даст​: 1:1, всего два​ догоняю?​

​ могу решить…​​astradewa​ даже в вашем​ значений в диапазон​ результате работы​ зеленой ячейке​

​E5​​ значения. Создадим список,​Динамический диапазон Список1 представляет​ становится активным «Режим​ Target.Cells.Count = 1​ выделяем попеременно нужные​ – Alt +​ Вводим уникальное название​ такой результат.​ года тому назад…​alx74​astradewa​

​: Serge_007, добрый вечер,​​ варианте выбранные данные​​ I2:I23 из столбца​​ПОИСКПОЗ​Е12​Выпадающий (раскрывающийся) список​ состоящий только из​ собой Исходный список​ конструктора». Рисуем курсором​ Then Application.EnableEvents =​ ячейки.​ F8. Выбираем нужное​

​ диапазона – ОК.​​​-​

​: Во вложении файл​​: ikki, доброе утро,​ читая форумы Excel,​
​ не уникальны цех​ B по условию​мы получаем массив:​выберите компанию ООО​

​ с двумя значениями:​​ уникальных текстовых значений.​ №1 с названиями​
​ (он становится «крестиком»)​ False If Len(Target.Offset(1,​Теперь создадим второй раскрывающийся​

​ имя. Нажимаем «Выполнить».​​Создаем раскрывающийся список в​Необходимо сделать раскрывающийся список​Владимир​

​ и скрин.​​ макрос или UDF​ в том числе​ 1116 повторяется 7​ в столбце A,​
​ {ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ и т.д.}.​ Рога и копытца​ Валютный и Рублевый.​ Уникальные значения будем​​ компаний, причем его​ небольшой прямоугольник –​ 0)) = 0​
​ список. В нем​Когда мы введем в​ любой ячейке. Как​
​ со значениями из​:​

excelworld.ru

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

​Serge_007​​ подойдут, если не​
​ и Ваш, я​
​ раз.​ задает условие ячейка​С помощью функции​4. Установите серый флажок​Для создания списка уникальных​ выбирать не из​ границы могут изменяться​ место будущего списка.​
​ Then Target.Offset(1, 0)​

​ должны отражаться те​​ пустую ячейку выпадающего​ это сделать, уже​

​ динамического диапазона. Если​​=ЕСЛИОШИБКА(ИНДЕКС($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)));»»)​: Это не совсем​
​ жалко, можно и​

​ понял что Вы​​при постановке кода​​ H1.​
​ ЕСЛИ мы сравниваем​

​ Выделите компанию​​ значений введем в​​ всех повторяющиеся значений​​ в зависимости от​Жмем «Свойства» – открывается​ = Target Else​ слова, которые соответствуют​
​ списка новое наименование,​​ известно. Источник –​ вносятся изменения в​​Формула массива.​

planetaexcel.ru

Выпадающий список в Excel с помощью инструментов или макросов

​ верно​ то и другое​ один из главных​ в ячейки I2:I23​Serge_007​ получившийся массив с​5. В исходной​

​ ячейку​ исходной таблицы, а​ количества числа введенных​ перечень настроек.​ Target.End(xlDown).Offset(1, 0) =​ выбранному в первом​ появится сообщение: «Добавить​ имя диапазона: =деревья.​ имеющийся диапазон (добавляются​Kogarys​

Создание раскрывающегося списка

​ТС не заполнил​ (для общего т.с.​ «формулистов»​ в свой изначальный​: Здравствуйте​

​ массивом {1:2:3:4:5:6:7:8 и​ таблице будут выделены​G5​ только из тех,​

1. ​ значений (пропуски не​Вписываем диапазон в строку​
2. ​ Target End If​ списке названию. Если​ введенное имя баобаб​Снимаем галочки на вкладках​
3. ​ или удаляются данные),​:​ пример, но интересуют​ развития)​

​, поэтому обратился​ файл у меня​

​Формула массива:​

Выпадающий список в Excel с подстановкой данных

​ т.д.}, полученном в​ 2 строки содержащие​формулу массива:​ у которых соответствующие​ допускаются). Аналогичные диапазоны​ ListFillRange (руками). Ячейку,​ Target.ClearContents Application.EnableEvents =​ «Деревья», то «граб»,​

1. ​ в выпадающий список?».​ «Сообщение для ввода»,​ они автоматически отражаются​Z,​
2. ​ значения из ДИАПАЗОНА​Спасибо за помощь.​ к Вам.​ совсем иная картина.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>​ результате работы функции​ название компании ООО​=ЕСЛИОШИБКА(ИНДЕКС(Компании;​ значения в соседних​ Список2 и Объединенный_список​ куда будет выводиться​ True End If​ «дуб» и т.д.​
3. ​Нажмем «Да» и добавиться​ «Сообщение об ошибке».​ в раскрывающемся списке.​-снкс,посмотрел темку, топикстартер​ в столбце​Alex_ST​А вопрос -​ на вспомогательное производство​

​=ИНДЕКС(B$2:B$23;НАИМЕНЬШИЙ(ЕСЛИ(H$1=A$2:A$23;СТРОКА($1:$22));СТРОКА(A1)))​СТРОКА​ Рога и копытца​

​ПОИСКПОЗ(0;ЕСЛИ(E$5=Тип_контракта;0;1)+СЧЁТЕСЛИ($G$4:G4;Компании);0)​ столбцах удовлетворяют определенному​

​ созданы для столбцов​

​ выбранное значение –​ End Sub​ Вводим в поле​

​ еще одна строка​ Если этого не​Выделяем диапазон для выпадающего​ хотел немножко не​RAN​: Александр,​ переделать формулу так,​

1. ​ выбирает только цех​Serge_007​с заданным диапазоном​ с типом контракта​);»»)​
2. ​ критерию.​ Исходный список №2​ в строку LinkedCell.​Чтобы выбираемые значения отображались​ «Источник» функцию вида​
3. ​ со значением «баобаб».​ сделать, Excel не​ списка. В главном​ то. Но все​: Я думаю, дело​astradewa​ чтобы она выбирала​
4. ​ 1111 относящийся к​: Если еще и​ и в случае​ Рублевый​При выборе в ячейке​Разовьем идеи, изложенные в​ и Объединенный список.​ Для изменения шрифта​ в одной ячейке,​ =ДВССЫЛ(E3). E3 –​Когда значения для выпадающего​ позволит нам вводить​ меню находим инструмент​ равно спасибо.​ в неудачном примере.​просит​ и выводила в​ основному.​ коды уникальные нужны,​ если​Созданная структура является разновидностью​E5​ статье Отбор уникальных​Далее используем решение, приведенное​ и размера –​ разделенные любым знаком​ ячейка с именем​ списка расположены на​ новые значения.​ «Форматировать как таблицу».​Владимир,​Заполнен 1 столбец,​формулами​
5. ​ столбец уникальные значения​и почему в​
6. ​ то:​ПОИСКПОЗ​ Связанного списка.​значения Валютный, все​ значений (убираем повторы).​ в статье Отбор​ Font.​ препинания, применим такой​

​ первого диапазона.​ другом листе или​Вызываем редактор Visual Basic.​Откроются стили. Выбираем любой.​- СПАСИБО ОГРОМНОЕ!​ а должно быть​

​, поэтому код VBA​ из диапазона А2:Е20​ коде СТРОКА($1:$22));СТРОКА(A1))) если​

Выпадающий список в Excel с данными с другого листа/файла

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(B$2:B$23;НАИМЕНЬШИЙ(ЕСЛИ((H$1=A$2:A$23)*(ПОИСКПОЗ(B$2:B$23;B$2:B$23;)=СТРОКА($1:$22));СТРОКА($1:$22));СТРОКА(A1)))​вернул ИСТИНА получаем​СОВЕТ:​ компании, НЕ поставляющие​Пусть исходная таблица содержит​ уникальных значений (убираем​Скачать пример выпадающего списка​ модуль.​Бывает, когда из раскрывающегося​ в другой книге,​ Для этого щелкаем​

1. ​ Для решения нашей​ То, что нужно!​ 4.​
2. ​ его вряд ли​ (у The_Prist формула​ у нас условия​

​_Boroda_​ номер строки вхождения.​Другим подходом к​ по валютным контрактам,​ три столбца: один​ дубликаты из столбца).​При вводе первых букв​Private Sub Worksheet_Change(ByVal​ списка необходимо выбрать​ стандартный способ не​

Как сделать зависимые выпадающие списки

​ правой кнопкой мыши​

​ задачи дизайн не​))))​alx74​ устроит.​ извлекает уникальные из​ и нужные данные​: Еще вариант для​Собственно говоря задача​

1. ​ решению этой задачи​ будут проигнорированы,​ с названиями компаний,​
2. ​Записав в​ с клавиатуры высвечиваются​ Target As Range)​ сразу несколько элементов.​ работает. Решить задачу​
3. ​ по названию листа​ имеет значения. Наличие​Под выпадающим списком понимается​: Теперь понял, спасибо.​Но на всякий​ столбца в столбец,​ находятся в строках​ уникальных кодов по​ решена. Теперь остаётся​ является использование Сводных​и список уникальных значений​ другой с указанием​E5​
   

   Выбор нескольких значений из выпадающего списка Excel

   ​ подходящие элементы. И​On Error Resume​ Рассмотрим пути реализации​ можно с помощью​ и переходим по​

   1. ​ заголовка (шапки) важно.​ содержание в одной​ (невнимательно прочитал первый​ случай, если формулами​ а надо из​ 2:23?​ условию​ только оформить итог​ таблиц.​ будет сформирован только​ типа контракта (валютный​формулу массива:​ это далеко не​ Next​ задачи.​ функции ДВССЫЛ: она​ вкладке «Исходный текст».​ В нашем примере​ ячейке нескольких значений.​ пост А2:Е20).​ так никто и​ диапазона).​Sancho​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(B$2:B$23;ПОИСКПОЗ(;СЧЁТЕСЛИ(I$1:I1;ЕСЛИ(A$2:A$23=H$1;B$2:B$23;I$1));))​ списком. Для этого​Предположим, что у​
   2. ​ из тех компаний,​ или рублевый), третий​=ЕСЛИОШИБКА(ИНДЕКС(Объединенный_список;​ все приятные моменты​If Not Intersect(Target,​Создаем стандартный список с​ сформирует правильную ссылку​ Либо одновременно нажимаем​ это ячейка А1​ Когда пользователь щелкает​astradewa​ не решит, то​Serge_007​: Вот вы блин​С проверкой на​ используем функцию НАИБОЛЬШИЙ,​ Вас есть вот​ которые имеют контракты​ с суммами продаж​
   3. ​ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$4:E4;Объединенный_список);0));»»)​ данного инструмента. Здесь​ Range(«C2:C5»)) Is Nothing​ помощью инструмента «Проверка​ на внешний источник​
​ клавиши Alt +​ со словом «Деревья».​
​ по стрелочке справа,​: Спасибо Всем ответившим​
​ в "Готовых решениях"​:​ шайтаны все))) Всем​ ошибку​
​ которая создаст вариативный​
​ такой файл по​
​ в валюте. Также​
​ по контракту. Столбец​
​и скопировав ее вниз​ можно настраивать визуальное​ And Target.Cells.Count =​
​ данных». Добавляем в​ информации.​ F11. Копируем код​
​ То есть нужно​
​ появляется определенный перечень.​
​ за помощь, буду​
​ я выкладывал макрос​astradewa​
​ спасибо огромное -​
​Код200?'200px':''+(this.scrollHeight+5)+'px');">=ЕСЛИОШИБКА(ИНДЕКС(B$2:B$23;ПОИСКПОЗ(;СЧЁТЕСЛИ(I$1:I1;ЕСЛИ(A$2:A$23=H$1;B$2:B$23;I$1));));"")​
​ ряд сначала из​


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

   Выпадающий список с поиском

   1. ​ исходный код листа​Делаем активной ячейку, куда​ (только вставьте свои​ выбрать стиль таблицы​ Можно выбрать конкретное.​ разбираться в предоставленном​
   2. ​ NoDups_in_Range​, спасибо за лестное​ работает​Формулы массива. Вводятся​ чисел, потом из​Из него Вам​
   3. ​ продажи и количество​ содержит повторяющиеся значения,​
   4. ​ значений из двух​ в качестве источника​Application.EnableEvents = False​ готовый макрос. Как​ хотим поместить раскрывающийся​ параметры).Private Sub Worksheet_Change(ByVal​ со строкой заголовка.​Очень удобный инструмент Excel​

   ​ материале.​

   ​Michael_S​ мнение, но думаю​jakim​ одновременным нажатием Контрл​ значений ЛОЖЬ и​ необходимо извлечь все​ продаж по каждой​ т.к. (см. файл​ столбцов.​ сразу два столбца.​

   exceltable.com

​newVal = Target​

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

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

Задача

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

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

Решение

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

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

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя;
• в поле Имя введите: Исходный_список;
• в поле Диапазон введите формулу =СМЕЩ(УникальныеЗначения!$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 решена обратная задача: из списка уникальных значений, в котором для каждого значения задано количество повторов, создается список этих значений с повторами.

Не могли бы вы мне помочь с удалением дубликатов в раскрывающемся списке . Этот перечень не является статичным. Пример:

До

После

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

Я попытался ниже, но не успех:

Шаг 1 — С оригинальными названиями в колонке А, поставить этот массив formula¹ где-то справа во втором ряду. Я буду использовать Z2.

Заполните вниз, пока не кончатся имен, а затем еще несколько строк, чтобы для будущего расширения.

Шаг 2 — Переход к формулам ► Defined имен ► Диспетчер имен и создать новое имя.

Шаг 3 — Перейдите к ячейке, которую вы хотите, вы проверку данных и использовать инструменты Data ► данных ► Проверка данных.

¹ _{Формула массива должна быть завершена с Ctrl + Shift + Enter↵ . Если введено правильно, Excel наматывается формула в фигурных скобках (например , <и > ). Вы не вводите скобки в себе. После ввода в первую ячейку правильно, они могут быть заполнены или скопированы вниз или вправо, как и любой другой формула. Попробуйте уменьшить ваши ссылки полных столбцов диапазонов более точно представляющих экстенты ваших фактических данных. Формулы массива жевать циклы вычисления логарифмически так это хорошая практика , чтобы сузить диапазон ссылки к минимуму. См Рекомендации и примеры формул массивов для получения дополнительной информации.}

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

Пример создания зависимого выпадающего списка в ячейке Excel

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

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

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

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

Так, например, если мы выберем категорию Развлечения, то в списке подкатегорий должно быть: Кинотеатр, Театр, Бассейн. Очень быстрое решение, если в своем домашнем бюджете вы хотите проанализировать более подробную информацию.

Список категорий и подкатегорий в зависимом выпадающем списке Excel

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

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

Зависимый выпадающий список подкатегорий

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

Рабочая исходная таблица Excel

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

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

Ну хорошо. Теперь, по очереди я опишу шаги создания зависимого выпадающего списка.

1. Имена диапазонов ячеек

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

Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).

Для того чтобы назвать список категорий:

1. Выберите диапазон A3:A5.
2. В поле имени (поле слева от строки формулы) введите название «Категория».
3. Подтвердите с помощью клавиши Enter.

Такое же действие совершите для диапазона рабочего списка категорий G3:G15, который вы можете вызвать «Рабочий_Список». Этот диапазон мы будем использовать в формуле.

2. Создание раскрывающегося списка для категории

Это будет просто:

1. Выберите ячейку, в которую вы хотите поместить список. В моем случае это A12.
2. В меню «ДАННЫЕ» выберите инструмент «Проверка данных». Появится окно «Проверка вводимых значений».
3. В качестве типа данных выберите «Список».
4. В качестве источника введите: =Категория (рисунок ниже).
5. Подтвердите с помощью OK.

Проверка вводимых значений – Категория.

Раскрывающийся список для категории.

3. Создание зависимого выпадающего списка для подкатегории

Сейчас будет весело. Создавать списки мы умеем — только что это сделали для категории. Только единственный вопрос: «Как сказать Excelю выбрать только те значения, которые предназначены для конкретной категории?» Как вы, наверное, догадываетесь, я буду использовать здесь рабочую таблицу и, конечно же, формулы.

Начнем с того, что мы уже умеем, то есть с создания раскрывающегося списка в ячейке B12. Поэтому выберите эту ячейку и нажмите «Данные» / «Проверка данных», а в качестве типа данных — «Список».

В источник списка введите следующую формулу:

Вид окна «Проверка вводимых значений»:

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

Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.

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

Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:H11, для Транспорта — диапазон H12: H15 и т. д. Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке).

Начало диапазона будет перемещено относительно ячейки H2 на такое количество ячеек вниз (по числу), сколько составляет номер позиции первой встречающейся категории в столбце Категория. Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки H2 (начинается с 4 ячейки от H2). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление). Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):

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

Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше. Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $H3: $H15. Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

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

Два варианта использования этого трюка я уже представил. Интересно, как вы его будете использовать?