Как настроить фильтр в экселе выборка из списка

Как сделать выборку в 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. Записываем три формулы с правильными координатами в каждый из вспомогательных столбцов, которые предварительно нужно создать. Точно так же используем для этого формулу массива.

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

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

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

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

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

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

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

Как пользоваться фильтрами в таблицах excel

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

Для начала работы необходимо выбрать одну, любую, ячейку внутри таблицы, открыть вкладку «Данные».

Затем нажать кнопку «Фильтр».

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

В таблице

Пошаговая инструкция: как поставить фильтр в эксель-таблице.

1. Нажать на значок в заголовке столбца.

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

2. Убрать лишние галочки, стоящие у параметров, которые пользователя не интересуют. Галочки останутся только у тех параметров, по которым необходимо провести поиск. После чего нажать «Ок».

3. Просмотреть результат — останутся только строки, соответствующие заданному параметру.

В диапазоне

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

Например, в рассматриваемом файле столбцы В и С имеют числовой отсев.

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

Например, в случае с числовым поиском этот порядок действий выглядит так:

1. Выбираем вид отсева.

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

3. Ввести цифру, которая и станет границей для отсева, — выведены будут все значения больше нее.

4. Нажать «Ок» и оценить результат. В выбранном столбце останутся только значения, превышающие установленную границу.

Как задать несколько параметров

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

Например, мы уже отфильтровали таблицу по условию «Страна» и оставили только товары российского происхождения.

Теперь необходимо дополнительно отделить товары стоимостью 100 рублей, для этого применяем отсев в разделе «Цена».

Нажимаем «Ок» и получаем только товары стоимостью 100 рублей, произведенные в России.

Как поставить расширенный поиск

Расширенный поиск позволяет отсеивать информацию сразу по нескольким условиям. Работая с ним, перед тем как поставить фильтр в таблице excel, необходимо подготовить саму таблицу — создать над ней поле из нескольких свободных строк и скопировать заголовки.

Затем в свободной строке под скопированными заголовками задать необходимые условия поиска. Например, необходимо найти товары, произведенные в России, проданные менеджером Ивановым, стоимостью менее 300 рублей.

После того как параметры корректно введены, необходимо снова открыть вкладку «Данные» и выбрать функцию «Дополнительно».

Перед пользователем появится окно, в котором ему предстоит заполнить две строки:

• «Исходный диапазон» — это диапазон таблицы, информация которой подлежит фильтрации, то есть исходной таблицы. Его эксель введет автоматически;
• «Диапазон условий» — это ячейки, из которых программа возьмет значения для отсева, — вторая таблица, которую мы создали сверху. Чтобы значения появились в строке окна, необходимо просто захватить две ее строки: с наименованием раздела и введенными значениями.

После того как оба диапазона сформированы, нажмите «Ок» и оцените результат.

Как удостовериться, поставлен ли фильтр

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

Значком «1» обозначается столбец, в котором поиск установлен, но не применяется. Значком «2» обозначен уже примененный поиск.

Удаление

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

Из столбца

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

С листа

Чтобы снять все имеющиеся отсевы со всех разделов одновременно, необходимо нажать на кнопку «Фильтр» во вкладке «Данные».

Расширенный фильтр в Excel и примеры его возможностей

Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.

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

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

Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.

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

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

Пользоваться автофильтром просто: нужно выделить запись с нужным значением. Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:

Сразу видим результат:

Особенности работы инструмента:

1. Автофильтр работает только в неразрывном диапазоне. Разные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
2. Инструмент воспринимает верхнюю строчку как заголовки столбцов – эти значения в фильтр не включаются.
3. Допустимо применять сразу несколько условий фильтрации. Но каждый предыдущий результат может скрывать необходимые для следующего фильтра записи.

У расширенного фильтра гораздо больше возможностей:

1. Можно задать столько условий для фильтрации, сколько нужно.
2. Критерии выбора данных – на виду.
3. С помощью расширенного фильтра пользователь легко находит уникальные значения в многострочном массиве.

Как сделать расширенный фильтр в Excel

Готовый пример – как использовать расширенный фильтр в Excel:

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

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

Как пользоваться расширенным фильтром в Excel

Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

Заполняем меню расширенного фильтра:

Получаем таблицу с отобранными по заданному критерию строками:

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.

Заполняем параметры фильтрации. Нажимаем ОК.

Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» — значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.

Применим инструмент «Расширенный фильтр»:

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

1. Результат формулы – это критерий отбора.
2. Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
3. Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
4. Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.

Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.

Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).

В таблице остались только те строки, где значения в столбце «Количество» выше среднего.

Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».

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

Применение фильтра в выпадающем списке Excel.

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

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

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

На помощь приходит создание фильтра для отбора только строк, содержащих нужный текст, вручную. Использование фильтра для выпадающего списка в Excel позволит отобрать необходимые позиции, основываясь на содержании элемента выпадающего списка. Создание такого списка может быть выполнено разными способами. В частности, в MS Office 365 присутствует специальная функция ФИЛЬТР, которая позволяет решить проблему. Однако далеко не везде эта версия MS Office установлена. Особенно это касается приложений, установленных в корпоративной среде. Использовать же онлайн версию офиса, в частности, работая в облаке от yandex.ru, mail.ru , onedrive.live.com и так далее не всегда возможно и не очень удобно. Данная версия, к сожалению, не содержит многих инструментов обработки данных, к которым мы привыкли. Нет консолидации, нет промежуточного итога. Отсутствуют возможности «Анализ что, если» и получение данных из внешних источников. И этот список отсутствующих инструментов можно продолжать. Существуют так же корпоративные правила, которые во многих случаях запрещают работу с документами в онлайн режиме.

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

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

Шаг первый. Проверяем вхождение шаблонного текста в список.

Для такой проверки можно применить функции ПОИСК или НАЙТИ. Функция НАЙТИ чувствительна к регистру, поэтому применим функцию ПОИСК во избежание ошибок с несовпадающим регистром. Ее синтаксис

=ПОИСК(искомый текст; строка для поиска наличия искомого текста).

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

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

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

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

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

Получить адрес нужной ячейки мы можем, используя функцию АДРЕС. В ней надо задать номер строки нужной ячейки, номер столбца этой ячейки, тип адреса – абсолютный, смешанный с закреплением строки и так далее. Остальные параметры в нашем случае значения не имеют. Важно помнить, что результатом работы данной функции будет текстовая строка с полученной ссылкой.

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

Для определения номера столбца можно просто прописать единицу, так как колонка А первая на листе. Однако мы применим функцию СТОЛБЕЦ, которая выводит номер столбца указанной ячейки. Это позволит сделать нашу формулу гибкой, не привязывая ее к одному столбцу.

Ну и наконец вспомним, что нам нужна абсолютная ссылка, поэтому третий параметр функции должен быть равен одному. Соберем все полеченные нами параметры функции АДРЕС вместе и получаем формулу

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

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

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

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

Чтобы это сделать, применим функцию ЕСЛИ в колонке С листа проверка . Она позволяет проверить условие и вывести один из двух вариантов в зависимости от его соблюдения.

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

Для параметра ИСТИНА функции ЕСЛИ зададим автоматическую нумерацию полученных строк с найденными числами. Для этого возьмем максимум из предыдущих ячеек и прибавим к полученному единицу. Так как в начале никаких предыдущих ячеек фактически нет, то для функции МАКС возьмем диапазон С1:С1 и закрепим первую ячейку. В результате при копировании формулы вниз будем получать диапазоны C1:C2 , C1:C3 и так далее.

Ну и для параметра ЛОЖЬ выведем пустое значение в виде двух кавычек подряд. В результате получаем такую запись.

Она кажется сложной, но на самом деле при последовательном ее создании сложностей возникнуть не должно.

Шаг четвертый. Соберем вместе все значения, соответствующие нашему искомому параметру.

Чтобы это сделать, выведем снова нумерацию, соответствующую нашему списку, в колонке D листа проверка . Напротив ее первой ячейки применим функцию ИНДЕКС. Ее параметры – адрес исходного диапазона, номер строки и номер столбца этого диапазона, из пересечения которых надо взять данные. Исходным диапазоном будет диапазон А1:С121 на листе проверка . Номер нужной строки найдем с помощью функции ПОИСКПОЗ, указав для поиска номер текущей позиции в колонке С листа проверка и запустив точный поиск в помощью типа сопоставления равного нулю. Номер столбца зададим первый.

Как видим, после перечня строк, содержащих последнее введение значение на листе список, в ненайденных позициях вывелась ошибка. Чтобы это исправить, обернем нашу формулу функцией ЕСЛИОШИБКА и указав в качестве второго параметра этой функции ноль.

Шаг пятый и последний. Формируем на основе полученного перечня источник для выпадающего списка.

Переходим на лист список . Выделяем столбец В , переходим на вкладку данные и нажимаем кнопку «проверка данных» . Тип данных выбираем «Список», а в строке “источник” введем функцию СМЕЩ. С ее помощью зададим диапазон, ячейки которого будут использованы в нашем списке. Параметры функции СМЕЩ по очереди в нашем случае:

1. начальная ячейка, относительно которой будем двигаться. В нашем случае это ячейка Е2 на листе проверка. Не забываем ее закрепить.

2. сдвиг по строкам. В нашем случае его нет, поэтому просто отделим место этого параметра стандартным разделителем.

3. сдвиг по колонкам. Его тоже не будет.

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

5. Количество столбцов в формируемом диапазоне. У нас будет только одна колонка.

В итоге получаем

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

Возможные проблемы.

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

Заключение.

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

На этом наше занятие подошло к концу. Желаем всем успеха в работе. До встречи на наших занятиях.