Как сделать выборку в Excel из списка
Если пользователю Excel приходится работать с большой таблицей, и перед ним ставится задача найти уникальные значения, которые подпадают под определенный критерий, то ему нередко приходится применять такой инструмент, как фильтр. Но в некоторых случаях приходится делать другое, а именно, выделять все ряды, в которых есть определенные значения в отношении к другим рядам. Если говорить про эту ситуацию, то здесь нужно применять другую функцию – условное форматирование.
Чтобы отдача была максимальной, нужно в качестве запроса применять выпадающий перечень.
Это хорошо подходит для тех ситуаций, когда надо постоянно изменять запросы одного типа, чтобы экспонировать разные строки диапазона. Сейчас мы подробно расскажем о том, какие действия надо выполнять, чтобы создать выборку из повторяющихся ячеек, входящих в состав выпадающего перечня.
Как выбрать уникальные и повторяющиеся значения в Excel – пошаговая инструкция
В первую очередь надо разобраться, что такое выборка. Это одно из самых главных статистических понятий, которое означает набор параметров, отобранных по определенному критерию. В качестве выборки может выступать что-угодно: люди для эксперимента, одежда, предприятия, ценные бумаги и так далее.
Чтобы создать выборку, необходимо сначала отобрать те результаты, которые подходят под условия из большого перечня, после чего отобразить эти значения отдельным списком или же в первоначальной таблице.
Подготовка содержания выпадающего списка
Чтобы наша работа сегодня была более наглядной, давайте возьмем историю расчетов с клиентами. Она будет такой, как на рисунке. 
Здесь нам надо выделить все операции, совершенные по отношению к каждому конкретному контрагенту, с помощью цвета. Чтобы переключаться между ними, применим выпадающий список. Следовательно, изначально нужно его сделать, а для этого надо отобрать данные, которые будут его элементами. В нашем примере нам требуются все фамилии контрагентов, которые есть в колонке А и при этом не повторяются. Для подготовки содержания выпадающего списка нам нужно выполнить следующую инструкцию:
Теперь видим перечень ячеек с уникальными фамилиями, которые уже не повторяются. Это и будет наша выборка для списка. 
Модификация исходной таблицы
После этого надо внести некоторые изменения в нашу таблицу. Для этого нужно выделить первые два ряда и нажать на комбинацию клавиш Ctrl+Shift+=. Таким образом у нас вставляются две дополнительные строчки. В новосозданную ячейку A1 вставляем слово «Клиент».
Создание выпадающего списка
После этого нам нужно создать выпадающий список. Чтобы это сделать, выполните следующие действия:
После этого ячейка B1 превращается в полноценный список фамилий клиентов. Если информация, которая служит источником для выпадающего перечня, располагается на другом листе, то в этом случае лучше сделать этот диапазон именным и сослаться на него таким образом.
В случае же с нами в этом нет никакой необходимости, потому что у нас и без того вся информация располагается на одном листе.
Выборка ячеек из таблицы по условию
Теперь же давайте попробуем создать выборку ячеек по условию. Для этого надо выделить ту таблицу, в которой указана фамилия контрагента, его код, номер транзакции и сумма операции, после чего откроем окно «Условное форматирование». Чтобы его вызвать, нужно перейти на вкладку «Главная», там найти группу «Стили», и в ней будет кнопка «Условное форматирование».
Появится меню, в котором нам надо кликнуть по пункту «Создать правило», в качестве которого выбираем «Использовать формулу для определения форматируемых ячеек». 
Далее вводим формулу, указанную на скриншоте, после чего нажимаем на кнопку «Формат», чтобы сделать все ячейки, содержащие одну и ту же фамилию, цветом. Например, зеленым. После этого подтверждаем все выполненные ранее действия путем многократного нажатия на «ОК» на всех окнах, которые будут на тот момент открытыми. После этого когда мы выбираем фамилию нашего человека, все ячейки, включающие ее, выделяются тем цветом, который мы задали.
Как это работает? Когда мы выбираем какое-то значение в выпадающем списке, то формула анализирует все имеющиеся строки, и если видит совпадение, выделяет их обозначенным пользователем цветом. В том, что формула работает, можно убедиться, выбрав другую фамилию. После этого выделение поменяется. Это значительно облегчает чтение таблицы.
Принцип действия следующий: проверяется значение в столбце А. Если оно равно тому, которое выбрано в списке, находящемся в ячейке B1, то эта формула возвращает значение ИСТИНА. После этого целая строка форматируется таким образом, каким вы захотите. В принципе, можно не только выделять эту строчку отдельным цветом, но и произвольно регулировать шрифт, границы и другие параметры. Но выделение цветом – самый быстрый метод.
Как мы добились того, чтобы цветом окрашивалась целая строка, а не отдельная ячейка? Для этого мы применили ссылку на ячейку, где адрес колонки является абсолютным, а номер ряда – относительным.
Скачать пример выборки из списка с условным форматированием
Как это работает? Вы можете попробовать посмотреть наглядно, скачав пример такой таблицы, которая была нами рассмотрена ранее. Для этого нужно перейти по этой ссылке.
4 способа выборки данных в Excel
Но на этом мы не заканчиваем нашу инструкцию. На самом деле, у нас есть целых четыре доступных способа формирования выборки данных в Excel.
Расширенный автофильтр
Это самый легкий метод, который позволяет отобрать значения, которые подходят под определенные критерии. Давайте более подробно рассмотрим, что нужно для этого.
Формула массива
Еще один вариант отбора – использование формулы массива. В этом случае результат выводится в отдельную таблицу, что может быть полезно, если первоначальные данные всегда нужно иметь перед глазами в неизменном виде. Чтобы это сделать, нам нужно следующее:
После этого присваиваем правильный формат ячейкам, в которых появляется ошибка. Далее используем условное форматирование для выделения тех ячеек, которые содержат определенное значение.
Выборка с несколькими условиями с помощью формулы
Использование формул также позволяет выбирать значения по нескольким критериям. Для этого выполняем следующие действия:
Преимущество этого метода в том, что нет необходимости изменять формулу, если вдруг понадобится изменить условия. Они всегда будут сохраняться в соответствующих ячейках.
Случайная выборка
Ну и наконец, последний метод формирования выборки, который подойдет не во всех ситуациях – использование генератора случайных чисел. Для этого надо использовать функцию =СЛЧИС(). Далее заполняем такое количество ячеек, которое нам нужно, с помощью маркера автозаполнения.
Далее выбираем «Настраиваемая сортировка» в меню фильтра.
Появляется меню настроек, где выставляем параметры так, как на скриншоте. 
Потом нажимаем «ОК» и получаем результат.
Видим, что нет ничего сложного. Если немного потренироваться, то все будет получаться очень легко. Главное – понять принцип, а метод можно выбрать любой, какой душе угодно.
Как сделать выборку в Excel с помощью формул массива
С помощью средств Excel можно осуществлять выборку определенных данных из диапазона в случайном порядке, по одному условию или нескольким. Для решения подобных задач используются, как правило, формулы массива или макросы. Рассмотрим на примерах.
Как сделать выборку в Excel по условию
При использовании формул массива отобранные данные показываются в отдельной таблице. В чем и состоит преимущество данного способа в сравнении с обычным фильтром.
Сначала научимся делать выборку по одному числовому критерию. Задача – выбрать из таблицы товары с ценой выше 200 рублей. Один из способов решения – применение фильтрации. В результате в исходной таблице останутся только те товары, которые удовлетворяют запросу.
Другой способ решения – использование формулы массива. Соответствующие запросу строки поместятся в отдельный отчет-таблицу.
Сначала создаем пустую таблицу рядом с исходной: дублируем заголовки, количество строк и столбцов. Новая таблица занимает диапазон Е1:G10.Теперь выделяем Е2:Е10 (столбец «Дата») и вводим следующую формулу: < >.
В столбец «Цена» введем такую же формулу массива, изменив первый аргумент функции ИНДЕКС.
В результате получаем отчет по товарам с ценой больше 200 рублей.
Такая выборка является динамичной: при изменении запроса или появлении в исходной таблице новых товаров, автоматически поменяется отчет.
Задача №2 – выбрать из исходной таблицы товары, которые поступили в продажу 20.09.2015. То есть критерий отбора – дата. Для удобства искомую дату введем в отдельную ячейку, I2.
Для решения задачи используется аналогичная формула массива. Только вместо критерия >.
Подобные формулы вводятся и в другие столбцы (принцип см. выше).
Теперь используем текстовый критерий. Вместо даты в ячейку I2 введем текст «Товар 1». Немного изменим формулу массива: < >.
Такая большая функция выборки в Excel.
Выборка по нескольким условиям в Excel
Сначала возьмем два числовых критерия:
Задача – отобрать товары, которые стоят меньше 400 и больше 200 рублей. Объединим условия знаком «*». Формула массива выглядит следующим образом: < =C2:C10);СТРОКА(C2:C10);"");СТРОКА(C2:C10)-СТРОКА($C$1))-СТРОКА($C$1))' >>.
Это для первого столбца таблицы-отчета. Для второго и третьего – меняем первый аргумент функции ИНДЕКС. Результат:
Чтобы сделать выборку по нескольким датам или числовым критериям, используем аналогичные формулы массива.
Случайная выборка в Excel
Когда пользователь работает с большим количеством данных, для последующего их анализа может потребоваться случайная выборка. Каждому ряду можно присвоить случайный номер, а затем применить сортировку для выборки.
Исходный набор данных:
Сначала вставим слева два пустых столбца. В ячейку А2 впишем формулу СЛЧИС (). Размножим ее на весь столбец:
Теперь копируем столбец со случайными числами и вставляем его в столбец В. Это нужно для того, чтобы эти числа не менялись при внесении новых данных в документ.
Чтобы вставились значения, а не формула, щелкаем правой кнопкой мыши по столбцу В и выбираем инструмент «Специальная вставка». В открывшемся окне ставим галочку напротив пункта «Значения»:
Теперь можно отсортировать данные в столбце В по возрастанию или убыванию. Порядок представления исходных значений тоже изменится. Выбираем любое количество строк сверху или снизу – получим случайную выборку.
Как сделать случайную выборку в excel из списка?
При работе с таблицами Excel довольно часто приходится проводить отбор в них по определенному критерию или по нескольким условиям. В программе сделать это можно различными способами при помощи ряда инструментов. Давайте выясним, как произвести выборку в Экселе, используя разнообразные варианты.
Выполнение выборки
Выборка данных состоит в процедуре отбора из общего массива тех результатов, которые удовлетворяют заданным условиям, с последующим выводом их на листе отдельным списком или в исходном диапазоне.
Способ 1: применение расширенного автофильтра
Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.
Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».
Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше». В правое поле вписываем значение «10000». Чтобы произвести выполнение действия, щелкаем по кнопке «OK».
Кроме того, существует ещё переключатель условий. У него два положения «И» и «ИЛИ». По умолчанию он установлен в первом положении. Это означает, что в выборке останутся только строчки, которые удовлетворяют обоим ограничениям. Если он будет выставлен в положение «ИЛИ», то тогда останутся значения, которые подходят под любое из двух условий. В нашем случае нужно выставить переключатель в положение «И», то есть, оставить данную настройку по умолчанию. После того, как все значения введены, щелкаем по кнопке «OK».
Кликаем по значку фильтра в наименовании столбца. Последовательно переходим по наименованиям списка «Текстовые фильтры» и «Настраиваемый фильтр…».
Второй вариант предполагает переход во вкладку «Главная». Там выполняем щелчок на ленте по кнопке «Сортировка и фильтр» в блоке «Редактирование». В активировавшемся списке нажимаем на кнопку «Фильтр».
При использовании любого из двух вышеуказанных методов фильтрация будет удалена, а результаты выборки – очищены. То есть, в таблице будет показан весь массив данных, которыми она располагает.
Урок: Функция автофильтр в Excel
Способ 2: применение формулы массива
Сделать отбор можно также применив сложную формулу массива. В отличие от предыдущего варианта, данный метод предусматривает вывод результата в отдельную таблицу.
Приготовьте данные в Excel. Определите количество рядов и колонок в вашем наборе.
Для этого нажмите правой кнопкой мыши на колонку А и нажмите Вставить (2 раза).
Вставьте в первую пустую клетку функцию генерации случайных чисел СЛЧИС (RAND, если у вас английская версия).
Скопируйте этот код и вставьте его во все клетки первого столбца. Теперь в каждой клетке стоит случайное число.
Выделите целый столбец случайных чисел. Скопируйте их.
Убедитесь, что вы выбрали опцию Вставить особенный элемент, а затем выбрали опцию Значение. Тогда Excel скопирует значение, но не формулу.
Выделите столбец В. Нажмите на иконку сортировки, выберите сортировку по восходящей.
Выделите свою выборку. Вы можете выбрать любое количество клеток и столбцов. Начните выделять данные с верхней части списка. Выделенные данные будут случайным образцом или случайной выборкой.
Нечастая, но интересная задача: выбрать из массива данных (списка) случайным образом N элементов. Причин для ее возникновения может быть несколько, например:
В любом случае перед нами стоит задача отобрать случайным образом заданное количество элементов из какого-либо набора (например, вот такого):
Способ 1. Случайная сортировка
Добавить к нашему списку еще один столбец и вставить в него функцию генерации случайных чисел СЛЧИС (RAND). Затем отсортировать наш список по добавленному столбцу (Данные — Сортировка) и взять N первых элементов из получившейся таблицы:
Минусы такого способа очевидны — придется вручную каждый раз пересортировывать список, если нам необходимо будет сделать другую случайную выборку. В плюсах — простота и доступность.
Способ 2. Функция НАИМЕНЬШИЙ
Этот способ заключается в использовании функции НАИМЕНЬШИЙ (SMALL) для выбора из списка N позиций с наименьшим случайным числом в столбце А:
После выбора пяти (в нашем примере) наименьших случайных чисел из столбца А, мы вытаскиваем имена, которые соответствуют этим числам с помощью функции ВПР (VLOOKUP).
Способ 3. Случайная выборка без повторов — функция Lotto на VBA
Можно создать простую функцию на VBA, которая будет выдавать заданное количество случайных чисел из нужного интервала. Откроем редактор Visual Basic (ALT+F11 или в старых версиях Excel через меню Сервис — Макрос — Редактор Visual Basic), вставим новый модуль через меню Insert — Module и скопируем туда текст вот такой функции:
У этой функции будет три аргумента:
Т.е., например, чтобы отобрать 5 случайных чисел от 10 до 100, нужно будет ввести =Lotto(10;100;5)
Теперь эту функцию легко использовать для отбора случайных значений. Добавим к нашему списку столбец с нумерацией и будем отбирать людей по случайным номерам, которые генерирует функция Lotto:
Обратите внимание, что наша функция Lotto должна быть введена как формула массива, т.е. сначала необходимо выделить диапазон ячеек результатов (D2:D6) затем ввести нашу функцио Lotto и, после ввода аргументов функции, нажать Ctrl+Shift+Enter, чтобы ввести эту функцию именно как функцию массива во все выделенные ячейки.
Ну, а дальше останется при помощи уже знакомой функции ВПР (VLOOKUP) вытащить имена из списка, соответствующие случайным номерам.
Случайная выборка из базы данных
Очень часто в работе программиста требуется вывести что-то случайное, чтобы это постоянно менялось от запуска к запуску. Например, вывести случайную статью, вывести случайную цитату, вывести случайные комментарии и многое другое. В этой статье мы с Вами разберём случайную выборку из базы данных.
В основе лежит команда сортировки (ORDER), и мы можем сделать так, чтобы сортировка была случайной. Это создаёт видимость случайной выборки. Вот пример запроса, который извлекает случайные записи:
SELECT * FROM `table` ORDER BY RAND()
Такой запрос вытащит все записи из таблицы и перемешает их случайным образом. На практике это нужно редко. Гораздо чаще нужно выводить именно определённое число записей:
SELECT * FROM `table` ORDER BY RAND() LIMIT 5
И данный запрос уже имеет практическую пользу, так как он позволяет вывести 5 случайных записей. Можно так вывести, например, 5 случайных изображений на страницу.
Очень часто требуется вытащить не просто случайные записи, но чтобы они удовлетворяли какому-либо условию:
SELECT * FROM `table` WHERE `id` > 5 ORDER BY RAND() LIMIT 5
Данный запрос вытащит 5 случайных записей, у которых id больше 5. Вот так делается случайная выборка из базы данных.
Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!
Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.
Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления
Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.
Порекомендуйте эту статью друзьям:
Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):
Комментарии ( 15 ):
почему вы не используете pdo для работы с базой данных?
Не люблю иметь дела ни с какими сторонними расширениями.
Здравствуйте, Михаил я сделал все так-же но у меня не получается. ошибок нет я 5 раз проверял подскажите с чем это связано?
Если не получается, ошибок не может не быть. Может проблема не в самом запросе, а в таблице, с которой Вы пытаетесь работать.
вот вам Михаил мой сайт http://dfiles.ru/files/r35om683r исправьте код пожалуйста везде где ошибки еще мой e-mail: balandin_2004@mail.ru. таблицу проверил нет не в этом ошибка
Не получается составить запрос, который выберет из базы (таблицы) 5 СЛУЧАЙНЫХ записей, но обязательно НАЧИНАЯ С id5 или так, чтобы id5 обязательно попал в эту выборку! Спасибо.
Здравствуйте, Александр, попробуйте так: SELECT * FROM `table` WHERE `id` > 5 ORDER BY RAND() LIMIT 5
в поле id ставлю unsinsed, prymeri,auto_inscrement
ошибка найдена.echo было вне цикла
Для добавления комментариев надо войти в систему.
Если Вы ещё не зарегистрированы на сайте, то сначала зарегистрируйтесь.
Copyright © 2010-2021 Русаков Михаил Юрьевич. Все права защищены.
