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

Как сделать выборку в Excel из списка с условным форматированием

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

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

Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

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

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

1. Выделите первый столбец таблицы A1:A19.
2. Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».
3. В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.
4. Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.

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

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

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».

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

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

1. Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
2. На вкладке «Параметры» в разделе «Условие проверки» из выпадающего списка «Тип данных:» выберите значение «Список».
3. В поле ввода «Источник:» введите =$F$4:$F$8 и нажмите ОК.

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

Примечание. Если данные для выпадающего списка находятся на другом листе, то лучше для такого диапазона присвоить имя и указать его в поле «Источник:». В данном случае это не обязательно, так как у нас все данные находятся на одном рабочем листе.

Выборка ячеек из таблицы по условию в Excel:

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

Как работает выборка уникальных значений Excel? При выборе любого значения (фамилии) из выпадающего списка B1, в таблице подсвечиваются цветом все строки, которые содержат это значение (фамилию). Чтобы в этом убедится в выпадающем списке B1 выберите другую фамилию. После чего автоматически будут выделены цветом уже другие строки. Такую таблицу теперь легко читать и анализировать.

Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel. Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат. Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.

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​ выпадающий список. Открываем​ списков: подстановка данных,​​, а надо​​ формулами, и макросом.​ на полную катушку,​

Отбор уникальных значений на основании значений из соседнего столбца в 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​ одном листе:​

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

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

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

​ диапазоны: Компании (названия​

​ это будет значительное​

​ (см. файл примера).​

​Application.EnableEvents = True​

​ Len(Target.Offset(0, 1)) =​

​ путь полностью.​

​ & » в​​Добавим в таблицу новое​ «Список».​
​: Всем привет!​​ и в посте​и долголетия​​ уникальные значения из​​ 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​

Выборка уникальных значений по условию из второго столбца (Формулы/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​ этим решением и​На вкладке «Разработчик» находим​ снизу, вставляем другой​

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

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

​ первых двух столбцов.​​ сейчас сомневаюсь, когда​: эт куда сабрался?​ и фильтр-Дополнительно-Ставим галку​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​
​ список. В нем​Когда мы введем в​ любой ячейке. Как​
​ со значениями из​:​

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

​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​ слова, которые соответствуют​
​ списка новое наименование,​​ известно. Источник –​ вносятся изменения в​​Формула массива.​

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

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

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

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

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

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​ одновременным нажатием Контрл​ значений ЛОЖЬ и​ необходимо извлечь все​ продаж по каждой​ т.к. (см. файл​ столбцов.​ сразу два столбца.​

Извлечение уникальных элементов из диапазона

Начиная с 2007-й версии функция удаления дубликатов является стандартной — найти ее можно на вкладке Данные — Удаление дубликатов (Data — Remove Duplicates) :

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

Способ 2. Расширенный фильтр

Если у вас Excel 2003 или старше, то для удаления дубликатов и вытаскивания из списка уникальных (неповторяющихся) элементов можно использовать Расширенный фильтр (Advanced Filter) из меню (вкладки) Данные (Data) .

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

Выбираем в меню Данные — Фильтр — Расширенный фильтр (Data — Filter — Advanced Filter) . Получаем окно:

• Выделяем наш список компаний в Исходный диапазон (List Range) .
• Ставим переключатель в положение Скопировать результат в другое место (Copy to another location) и указываем пустую ячейку.
• Включаем (самое главное!) флажок Только уникальные записи(Uniqe records only) и жмем ОК.

Получите список без дубликатов:

Если требуется искать дубликаты не по одному, а по нескольким столбцам, то можно предварительно склеить их в один, сделав, своего рода, составной ключ с помощью функции СЦЕПИТЬ (CONCATENATE) :

Тогда дальнейшая задача будет сводиться к поиску дубликатов уже в одном столбце.

Способ 3. Выборка уникальных записей формулой

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

Итак, снова имеем список беспорядочно повторяющихся элементов. Например, такой:

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

В английской версии это будет:

Эта формула проверяет сколько раз текущее наименование уже встречалось в списке (считая с начала), и если это количество =1, т.е. элемент встретился первый раз — дает ему последовательно возрастающий номер.

Для упрощения адресации дадим нашим диапазонам (например, исходя из того, что в списке может быть до 100 элементов) имена. Это можно сделать в новых версиях Excel на вкладке Формулы — Диспетчер имен (Formulas — Name manager) или в старых версиях — через меню Вставка — Имя — Присвоить (Insert — Name — Define) :

• диапазону номеров (A1:A100) — имя NameCount
• всему списку с номерами (A1:B100) — имя NameList

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

=ЕСЛИ(МАКС(NameCount)<СТРОКА(1:1);"";ВПР(СТРОКА(1:1);NameList;2))

или в английской версии Excel:

=IF(MAX(NameCount)

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

Как сделать выборку в Excel из списка

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

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

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

Как выбрать уникальные и повторяющиеся значения в Excel — пошаговая инструкция

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

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

Подготовка содержания выпадающего списка

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

Здесь нам надо выделить все операции, совершенные по отношению к каждому конкретному контрагенту, с помощью цвета. Чтобы переключаться между ними, применим выпадающий список. Следовательно, изначально нужно его сделать, а для этого надо отобрать данные, которые будут его элементами. В нашем примере нам требуются все фамилии контрагентов, которые есть в колонке А и при этом не повторяются. Для подготовки содержания выпадающего списка нам нужно выполнить следующую инструкцию:

1. Выделяем первый столбец нашей таблицы.
2. Используем инструмент «Данные» – «Сортировка и фильтр» – «Дополнительно».
3. После этого перед нами появится окно, в котором нам надо выбрать тип обработки «скопировать результат в другое место», а также поставить флажок возле пункта «Только уникальные записи». В нашем случае диапазоном, который мы используем будет ячейка с адресом $F$1. Знак доллара означает, что ссылка абсолютная и она не будет меняться в зависимости от того, что человек копирует или вставляет содержимое ячейки, которая с ней связана.
4. После того, как мы зададим все необходимые параметры, нам нужно нажать на клавишу ОК и так мы подтверждаем наши действия.

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

Модификация исходной таблицы

После этого надо внести некоторые изменения в нашу таблицу. Для этого нужно выделить первые два ряда и нажать на комбинацию клавиш Ctrl+Shift+=. Таким образом у нас вставляются две дополнительные строчки. В новосозданную ячейку A1 вставляем слово «Клиент».

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

После этого нам нужно создать выпадающий список. Чтобы это сделать, выполните следующие действия:

1. Делаем клик по ячейке B1. Переходим на вкладку «Данные» – «Работа с данными» – «Проверка данных».
2. Появится диалоговое окно, в котором нам надо выбрать тип данных «Список», а в качестве источника данных выбрать наш перечень фамилий. После этого нажимаем на кнопку ОК.

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

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

Выборка ячеек из таблицы по условию

Теперь же давайте попробуем создать выборку ячеек по условию. Для этого надо выделить ту таблицу, в которой указана фамилия контрагента, его код, номер транзакции и сумма операции, после чего откроем окно «Условное форматирование». Чтобы его вызвать, нужно перейти на вкладку «Главная», там найти группу «Стили», и в ней будет кнопка «Условное форматирование».

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

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

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

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

Как мы добились того, чтобы цветом окрашивалась целая строка, а не отдельная ячейка? Для этого мы применили ссылку на ячейку, где адрес колонки является абсолютным, а номер ряда – относительным.

Скачать пример выборки из списка с условным форматированием

Как это работает? Вы можете попробовать посмотреть наглядно, скачав пример такой таблицы, которая была нами рассмотрена ранее. Для этого нужно перейти по этой ссылке.

4 способа выборки данных в Excel

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

Расширенный автофильтр

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

1. Предположим, у нас есть таблица, содержащая наименование товара, дату и общее количество денег, которое получилось заработать на продаже конкретной позиции в определенный день. Нам надо выделить ту область, где надо отобрать выборку. Для этого переходим на вкладку «Главная», где находим кнопку «Сортировка и фильтр» и нажимаем на нее. Ее можно найти в блоке инструментов «Редактирование». После этого находим опцию «Фильтр». Приводим скриншот для наглядности.
2. Есть и второй способ, как можно поступить в этом случае. Можно найти кнопку «Фильтр» в группе «Сортировка и фильтр», которая располагается на вкладке «Данные».
3. После того, как мы это сделаем, сверху таблицы появятся стрелочки, с помощью которых можно отобрать данные для фильтра. Нужно нажать на одну из них (то, какая именно, зависит от того столбца, в котором нам нужно выполнить сортировку). После этого находим пункт «Числовые фильтры», и нажимаем на «Настраиваемый фильтр».
4. После этого появляется окно, через которое можно настроить пользовательскую фильтрацию. С его помощью пользователь может задать критерий, исходя из которого и будут отбираться данные. В выпадающем перечне для той колонки, в какой содержатся числовые ячейки (а именно их мы используем для примера), есть возможность выбрать такие критерии, как равно, не равно, больше, больше или равно и меньше. То есть, стандартные арифметические операции сравнения.
   Давайте для наглядности зададим правило, согласно которому программа должна отбирать исключительно те значения, в которых сумма выручки составляет больше, чем 10 тысяч рублей. Следовательно, нам нужно в поле, обозначенном на скриншоте цифрой 1, выставить пункт «больше», а в поле, обозначенном цифрой 2, установить значение 10 тысяч (цифрами). Далее осталось только подтвердить наши действия.
5. Как мы понимаем, после того, как мы отфильтровали данные остались исключительно те строки, в которых объем заработанных денег без вычета налогов составляет больше, чем 10 тысяч рублей.
6. Но у нас есть возможность еще один критерий добавить. Чтобы это сделать, надо снова вернуться к пользовательскому фильтру, в нижней части которого мы видим еще два поля, которые имеют такой же вид, как и тот, в котором мы вводили наши критерии. В нем можно задать второй параметр. Пусть, к примеру, нам надо будет отбирать только те данные, которые не превышают 14999. Для этого выбираем правило «Меньше», а в качестве значения выставляем «15000».
   Также можно воспользоваться переключателем условий, который может принимать одно из двух значений: И и ИЛИ. Первоначально он настроен на первый вариант, но если человеку нужно задать соответствие одному из этих условий, то можно выбрать значение ИЛИ. Чтобы переключить тип соотношения между условиями, необходимо поставить тумблер в соответствующее положение. После того, как мы выполнили все необходимые действия, нажимаем на кнопку «ОК».
7. Теперь наша таблица отображает только те значения, которые колеблются в пределах от 10 тысяч рублей до 14999 рублей.

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

Еще один вариант отбора – использование формулы массива. В этом случае результат выводится в отдельную таблицу, что может быть полезно, если первоначальные данные всегда нужно иметь перед глазами в неизменном виде. Чтобы это сделать, нам нужно следующее:

1. Копируем шапку таблицы в нужное место.
2. Выделяем все ячейки, которые содержатся в первой колонке новосозданной таблицы и переводим курсор в строку ввода формул. После этого туда вставляем следующую формулу (значения, естественно, меняем на свои). =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);»»);СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))
3. Подтверждаем ввод с помощью комбинации клавиш Ctrl + Shift + Enter.
4. Выполняем аналогичную операцию со вторым столбцом.
5. Делаем то же самое с третьим столбцом.

Во всех трех ситуациях формула в целом та же самая, просто изменяются координаты.

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

Выборка с несколькими условиями с помощью формулы

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

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

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

Случайная выборка

Ну и наконец, последний метод формирования выборки, который подойдет не во всех ситуациях – использование генератора случайных чисел. Для этого надо использовать функцию =СЛЧИС(). Далее заполняем такое количество ячеек, которое нам нужно, с помощью маркера автозаполнения.

Далее выбираем «Настраиваемая сортировка» в меню фильтра.

Появляется меню настроек, где выставляем параметры так, как на скриншоте.

Потом нажимаем «ОК» и получаем результат.

Видим, что нет ничего сложного. Если немного потренироваться, то все будет получаться очень легко. Главное – понять принцип, а метод можно выбрать любой, какой душе угодно.