Чтобы сделать выборку из списка в Excel, то есть вынести в отдельное место только строки, которые подходят под условие, в Excel 2021, 2024 и Microsoft 365 хватает одной формулы: =ФИЛЬТР(A2:D13;B2:B13="Москва"). Она сама займёт столько строк, сколько нашлось, и пересчитается, когда поменяется исходная таблица.
В Excel 2016 и 2019 функции ФИЛЬТР нет. Там выборку делают расширенным фильтром с копированием в другое место или формулой ИНДЕКС + АГРЕГАТ. А если лишние строки нужно просто спрятать в той же таблице, это работа автофильтра, и про него у нас есть инструкция по фильтру в столбцах Excel.
CheckroiПодборка курсов по Microsoft Excel684 курса • 64 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
Какой способ подойдёт под вашу задачу
Словом «выборка» в Excel называют несколько разных задач. Ниже они собраны в одну таблицу, чтобы сразу перейти к нужному разделу.
| Задача | Способ | Где работает |
|---|---|---|
| Вынести строки по условию, чтобы список обновлялся сам | функция ФИЛЬТР | Excel 2021, 2024, Microsoft 365 |
| То же самое в старой версии | формула ИНДЕКС + АГРЕГАТ | Excel 2016 и новее |
| Один раз скопировать нужные строки на другой участок листа | расширенный фильтр | Excel 2016 и новее |
| Получить список без повторов | функция УНИК или расширенный фильтр | УНИК есть с Excel 2021 |
| Выбрать несколько случайных строк | СОРТПО + СЛУЧМАССИВ или столбец со СЛЧИС | СЛЧИС есть в любой версии |
| Скрыть лишние строки на месте | автофильтр | во всех версиях |
Дальше все примеры идут на одной учебной таблице: 12 заказов в диапазоне A1:D13, столбцы «Заказ», «Город», «Менеджер» и «Сумма, ₽».
Выборка по условию функцией ФИЛЬТР
Синтаксис у функции короткий: =ФИЛЬТР(массив;включить;[если_пусто]). Первый аргумент говорит, какие столбцы вернуть, второй задаёт условие, третий подставляется, когда ни одна строка не подошла. Так описывает её справка Microsoft по функции ФИЛЬТР.
- Скопируйте заголовки таблицы в свободное место, например в F1:I1.
- Встаньте в F2 и введите
=ФИЛЬТР(A2:D13;B2:B13="Москва"). - Нажмите Enter. Excel сам растянет результат вниз и вправо на нужное число ячеек.
- Поменяйте город в любой строке исходной таблицы: выборка перестроится без вашего участия.
Кадры ниже сняты в Google Таблицах, потому что Excel не открывается в браузере без входа. Там функция называется по-английски, FILTER, а аргументы разделяет запятая. В русском Excel пишите ФИЛЬТР и точку с запятой, логика и результат те же.
Город лучше не вписывать в формулу, а вынести в отдельную ячейку. Если в G1 стоит «Москва», формула станет такой: =ФИЛЬТР(A2:D13;B2:B13=G1). Теперь выборку переключают сменой одного слова, а саму формулу никто не трогает. Удобно сделать в G1 ещё и выпадающий список с городами, чтобы не ошибиться в написании.
Выборка по нескольким условиям
Условия во втором аргументе соединяют знаками умножения и сложения. Работает это потому, что ИСТИНА в Excel считается единицей, а ЛОЖЬ нулём: произведение равно единице, только когда выполнены оба условия.
- Оба условия сразу (логика «И») соединяют умножением:
=ФИЛЬТР(A2:D13;(B2:B13="Москва")*(D2:D13>15000)). Из пяти московских заказов останутся три, у которых сумма больше 15 000 ₽. - Хотя бы одно условие (логика «ИЛИ») соединяют сложением:
=ФИЛЬТР(A2:D13;(B2:B13="Москва")+(C2:C13="Белов")). Вернутся восемь строк: пять московских и ещё три заказа Белова из других городов.
Оба приёма приведены и в справке Microsoft. Каждое условие берите в скобки целиком: так Excel сначала сравнит, а потом сложит или перемножит результаты.
Что вернуть, если ничего не нашлось
Если условию не отвечает ни одна строка, ФИЛЬТР без третьего аргумента выдаёт ошибку. Справка Microsoft объясняет это тем, что Excel не умеет возвращать пустой массив, и советует заполнять [если_пусто].
=ФИЛЬТР(A2:D13;B2:B13=G1;"Заказов нет")Вместо текста можно поставить пустые кавычки "", тогда ячейка просто останется чистой.
Сама по себе ФИЛЬТР строки не сортирует, но её можно завернуть в СОРТ. Формула =СОРТ(ФИЛЬТР(A2:D13;B2:B13=G1;"");4;-1) выведет заказы выбранного города от самого крупного к самому мелкому. Подробнее о том, как Excel раскладывает такие результаты по ячейкам, мы писали в статье про формулы массива.
Если ФИЛЬТРа нет: расширенный фильтр с копированием
Расширенный фильтр есть в Excel 2016, 2019 и в новых версиях. Он не пересчитывается сам, зато не требует формул и копирует нужные строки куда скажете.
- Вставьте над таблицей три пустые строки. В верхнюю скопируйте заголовок столбца, по которому отбираете, например «Город», а под ним напишите «Москва». Это диапазон условий, между ним и таблицей должна остаться хотя бы одна пустая строка.
- Выделите таблицу вместе с заголовками.
- Откройте вкладку «Данные», в группе «Сортировка и фильтр» нажмите «Дополнительно».
- Выберите вариант «Копировать в другое место».
- В поле «Диапазон условий» укажите ячейки с заголовком и значением, а в поле «Поместить результат в диапазон» левую верхнюю ячейку для выборки.
- Нажмите «ОК».
Порядок шагов и названия полей взяты из справки Microsoft по расширенному фильтру. Там же есть важная деталь: если в месте для результата заранее записать только часть заголовков, Excel скопирует лишь эти столбцы.
Условия в одной строке диапазона работают как «И», в разных строках как «ИЛИ». Под «Город» в одной строке «Москва», а под «Сумма, ₽» рядом «>15000», и вы получите те же три заказа, что и формулой с умножением.
Скопированная выборка становится обычным диапазоном, и с ним можно работать как с любыми данными: например, отсортировать по алфавиту или по сумме.
Расширенный фильтр делает снимок. Добавили в исходную таблицу новый заказ, и выборку придётся запускать заново.
Формула выборки для Excel 2016 и 2019
Когда выборка нужна живой, а ФИЛЬТРа нет, помогает связка ИНДЕКС и АГРЕГАТ. Пусть город для отбора стоит в G1, а результат начинается с F3:
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$D$13;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$13)-СТРОКА($B$2)+1)/($B$2:$B$13=$G$1);СТРОКА(A1));СТОЛБЕЦ(A1));"")Формулу вводят в F3 и протягивают на четыре столбца вправо и на 12 строк вниз, по размеру исходной таблицы. Как она работает по частям:
СТРОКА($B$2:$B$13)-СТРОКА($B$2)+1даёт номера строк таблицы от 1 до 12.- Деление на
($B$2:$B$13=$G$1)оставляет номер там, где город совпал, и превращает в ошибку деления на ноль все остальные строки. АГРЕГАТ(15;6;…;k)— это НАИМЕНЬШИЙ, который пропускает ошибки: номер функции 15 и параметр 6 описаны в справке по АГРЕГАТ. Он находит первый подходящий номер, потом второй и так далее.СТРОКА(A1)иСТОЛБЕЦ(A1)при протягивании превращаются в 1, 2, 3… и выбирают нужную строку и столбец.- ЕСЛИОШИБКА прячет хвост: строки, для которых совпадений уже не осталось, выглядят пустыми.
На учебной таблице при «Москва» в G1 формула заполнит пять строк, те же заказы 1001, 1003, 1006, 1008 и 1011, что вернула ФИЛЬТР. Знаки доллара здесь нужны: без закреплённых ссылок диапазон уползёт при протягивании. Про закрепление и имена диапазонов есть отдельная статья о диапазонах.
Связки вроде ИНДЕКС + АГРЕГАТ кажутся громоздкими только первые пару раз. Их, вместе с динамическими массивами и сводными таблицами, разбирают на курсах по Excel, если формулы нужны в работе каждый день.
Выборка уникальных значений из списка
Частая задача звучит так: из длинного списка нужно вытащить каждое значение по одному разу. В новых версиях это делает функция УНИК, и её можно вложить в ФИЛЬТР, чтобы сначала отобрать строки, а потом убрать повторы.
=УНИК(ФИЛЬТР(C2:C13;B2:B13="Казань"))В казанских заказах четыре строки, но менеджеров только трое: Белов, Седов и Орлова. В Google Таблицах эта функция называется UNIQUE.
В Excel 2016 и 2019 список без повторов собирают тем же расширенным фильтром. Выбирают вариант «Копировать в другое место» и ставят флажок «Только уникальные записи», так советует справка Microsoft об уникальных значениях. Исходные данные при этом не меняются. Если же дубли нужно стереть насовсем, пригодится наша инструкция как удалить повторяющиеся значения, а посчитать, сколько их, поможет разбор подсчёта уникальных значений.
Случайная выборка строк
Иногда нужна не выборка по условию, а несколько строк наугад: проверить три случайных заказа, выбрать победителей, собрать тестовую группу. Тут два пути.
Формулой в Microsoft 365 и Excel 2024
=ВЗЯТЬ(СОРТПО(A2:D13;СЛУЧМАССИВ(СЧЁТЗ(A2:A13)));3)СЛУЧМАССИВ раздаёт каждой строке случайное число, СОРТПО перемешивает таблицу по этим числам, а ВЗЯТЬ оставляет первые три строки. Приём с перемешиванием через СОРТПО, СЛУЧМАССИВ и СЧЁТЗ показан в справке по СОРТПО. Функции ВЗЯТЬ в Excel 2021 нет, там формула =СОРТПО(A2:D13;СЛУЧМАССИВ(СЧЁТЗ(A2:A13))) перемешает весь список, а первые строки результата и будут выборкой.
Вспомогательным столбцом в любой версии
- В E2 введите
=СЛЧИС()и протяните до E13. - Отсортируйте таблицу по столбцу E.
- Возьмите первые три строки.
Случайные числа пересчитываются при каждом изменении на листе. Чтобы выборка не менялась, скопируйте результат и вставьте его как значения. Если подключена надстройка «Пакет анализа», на вкладке «Данные» появится команда «Анализ данных», а в ней инструмент «Выборка». Он берёт входной диапазон как генеральную совокупность и строит из него выборку, в том числе с шагом, например каждое четвёртое значение. Как включить надстройку, описано в справке по пакету анализа.
Канал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 800 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в каналФункция ВЫБОР и чем она отличается от выборки
По запросу «выбор в Excel» многие находят функцию ВЫБОР и пытаются собрать выборку с её помощью. Задача у неё другая: она возвращает одно значение из перечня по его номеру.
=ВЫБОР(2;"Москва";"Казань";"Самара")Эта формула вернёт «Казань», потому что номер 2. Номер может быть от 1 до 254, остальные детали есть в справке по функции ВЫБОР. Строки по условию она не отбирает, для этого нужны способы из разделов выше. Если же нужно найти одно значение по ключу, а не все подходящие строки, загляните в разбор функции ВПР.
Частые ошибки при выборке
| Что видно | Почему | Что сделать |
|---|---|---|
| #ПЕРЕНОС! вместо результата ФИЛЬТР | в ячейках, куда должна разлиться выборка, уже что-то есть | освободить место справа и снизу от формулы |
| #ВЫЧИСЛ! | ни одна строка не подошла, а третьего аргумента нет | дописать [если_пусто], например "" |
| #ИМЯ? | Excel не узнал имя функции: опечатка или ФИЛЬТР и УНИК в этой версии нет | проверить написание, в старой версии перейти на расширенный фильтр или формулу с АГРЕГАТ |
| Строка явно подходит, но не попала в выборку | лишний пробел в конце слова или число записано как текст | почистить данные функцией СЖПРОБЕЛЫ, текст-числа перевести в числа |
| Выборка не видит новые заказы | диапазон в формуле заканчивается на 13-й строке | оформить данные умной таблицей, тогда ссылки растут вместе с ней |
Про #ПЕРЕНОС! подробно рассказывает справка Microsoft: у ячейки с ошибкой есть значок проверки, и команда «Выделить препятствующие ячейки» сразу покажет, что мешает. Название #ВЫЧИСЛ! для пустого массива встречается в справке по функции ВЗЯТЬ, а главная причина #ИМЯ?, опечатка в имени функции, разобрана в справке об ошибке #ИМЯ?.
Где освоить Excel системно
Выборку по условию в работе с данными делают так же часто, как сводные таблицы и расчёты основными функциями. Если такие задачи встречаются каждую неделю, быстрее пройти курс, чем собирать ответы по одному. Мы сравнили программы по Excel в каталоге курсов, вот пять из них.
| Курс | Школа | Стоимость со скидкой | В рассрочку | Длительность | Обзор курса от Checkroi |
|---|---|---|---|---|---|
| Excel Перейти на сайт курса |  Нетология | 35 100 ₽ | 1691 ₽/мес. | 8 недель | Обзор курса |
| Power BI & Excel PRO Перейти на сайт курса | Нетология | 38 500 ₽ | 3208 ₽/мес. | 2 месяца | Обзор курса |
| Excel для работы Перейти на сайт курса |  Эдюсон | 16 500 ₽ | 1375 ₽/мес. | 2 недели | Обзор курса |
| Excel + Google Таблицы с нуля до PRO Перейти на сайт курса |  Skillbox | 29 899 ₽ | 2965 ₽/мес. | 4 месяца | Обзор курса |
| Excel для аналитика Перейти на сайт курса |  Moscow Business Academy | 17 200 ₽ | 2866 ₽/мес. | 2 месяца | Обзор курса |
Больше программ — в полном каталоге курсов по Microsoft Excel
