Содержание

Как сделать запрос в excel 2010?

Запрос на выборку данных (формулы) в MS EXCEL

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра). Произведем отбор значений из исходной таблицы с помощью формул массива. В отличие от применения Фильтра (CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр ) отобранные строки будут помещены в отдельную таблицу.

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

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число ).

Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

Решить эту и последующие задачи можно легко с помощью стандартного фильтра. Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L. Через выпадающий список у заголовка Цены выберите Числовые фильтры. , затем задайте необходимые условия фильтрации и нажмите ОК.

Будут отображены записи удовлетворяющие условиям отбора.

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

Критерий (минимальную цену) разместим в ячейке Е6, таблицу для отфильтрованных данных — в диапазоне D10:E19.

Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива:

Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER.

Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную формулу массива:

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

Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в Е6 значение 65. В новую таблицу будет добавлена еще одна запись из Исходной таблицы, удовлетворяющая новому критерию.

Если в Исходную таблицу добавить новый товар с Ценой в диапазоне от 25 до 65, то в новую таблицу будет добавлена новая запись.

В файле примера также содержатся формулы массива с обработкой ошибок, когда в столбце Цена содержится значение ошибки, например #ДЕЛ/0! (см. лист Обработка ошибок).

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

3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата).

Для отбора строк используются формулы массива, аналогичные Задаче1 (вместо критерия =$B$12:$B$20)*(СТРОКА($B$12:$B$20)-СТРОКА($B$11));
$J$12-СТРОКА(A12)+СТРОКА($B$11)+1))

Примечание: После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER. Это сочетание клавиш используется для ввода формул массива.

Скопируйте формулу массива вниз на нужное количество ячеек. Формула вернет только те значения Товаров, которые были поставлены в диапазоне указанных дат. В остальных ячейках будут содержаться ошибки #ЧИСЛО! Ошибки в файле примера (Лист 4.Диапазон Дат) скрыты с помощью Условного форматирования.

Аналогичную формулу нужно ввести и для дат в столбец E.

В ячейке J12 вычислено количество строк исходной таблицы, удовлетворяющих критериям:

Для ввода первой формулы выделите диапазон ячеек G12:G20. После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER.

Решение3: Если столбец Дат СОРТИРОВАН, то можно не использовать формулы массива.

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

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

5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата (не позже) ).

Для отбора строк, дата которых не раньше (включая саму дату), используется формула массива:

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7 C15;И($B$7>=B15;$B$7 =$B$13:$B$21)*($B$13:$B$21>0);СТРОКА($B$13:$B$21);»»);СТРОКА($B$13:$B$21)-СТРОКА($B$12))
-СТРОКА($B$12))

Условие $E$7=$A$13:$A$21 гарантирует, что будут отобраны товары только определенного типа. Условие $E$8>=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

7. Один Текстовый критерий (Выбрать Товары определенного вида)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — Текст).

Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск.

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (И) ).

Для отбора строк используется формула массива:

Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).

Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел <1:2:3:4:5:6:7:8:9>, т.е. номера строк в таблице.

9. Два Текстовых критерия (Выбрать Товары определенных видов)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (ИЛИ)).

В отличие от Задачи 7 отберем строки с товарами 2-х видов (Условие ИЛИ).

Для отбора строк используется формула массива:

= ИНДЕКС(A$11:A$19;
НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))

Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).

Читать еще:  Как в excel сделать формат а5?

Вышеуказанное выражение вернет массив <0:0:0:0:1:1:1:0:0>. Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10) , т.е. на массив последовательных чисел <1:2:3:4:5:6:7:8:9>, получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив <0:0:0:0:5:6:7:0:0>.

С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3. Для этого используем выражение СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1 , которое последовательно (начиная со строки 11) будет возвращать числа 3; 2; 1; 0; -1; -2; . Формула НАИБОЛЬШИЙ(. ;3) вернет число 5, НАИБОЛЬШИЙ(. ;2) вернет число 6, НАИБОЛЬШИЙ(. ;1) вернет число 7, а НАИБОЛЬШИЙ(. ;0) и далее вернет ошибку, которую мы скроем условным форматированием.

И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: = ИНДЕКС(A$11:A$19;5) вернет Товар2, = ИНДЕКС(A$11:A$19;6) вернет Товар2, = ИНДЕКС(A$11:A$19;7) вернет Товар3.

10. Отбор значений с учетом повторов

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

Наиболее популярные статьи из этого раздела:

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

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

Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).

Решением является формула массива:

Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.

Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27 =$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование, которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

11. Используем значение критерия (Любой) или (Все)

В фильтре Сводных таблиц MS EXCEL используется значение (Все), чтобы вывести все значения столбца. Другими словами, в выпадающем списке значений критерия содержится особое значение, которое отменяет сам критерий (см. статью Отчеты в MS EXCEL, Отчет №3).

В файле примера на листе «11. Критерий Любой или (Все)» реализован данный вариант критерия.

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

Остальная часть формулы аналогична рассмотренным выше.

Примеры функция БИЗВЛЕЧЬ в Excel для выборки данных из таблицы

Функция БИЗВЛЕЧЬ в Excel для удобства поиска информации в больших массивах данных. Она предназначена для поиска данных в таблице или базе данных на основе критериев, указанных в качестве ее параметров, и возвращает искомое значение, если оно было найдено, или код ошибки, если совпадения найдены не были или с учетом введенных критериев поиска были найдены 2 и более совпадений.

Пример работы функции БИЗВЛЕЧЬ при выборке данных из таблицы Excel

Как создать базу данных в Excel? Базой данных в программе Excel считается таблица, которая была создана с учетом определенных требований:

  1. Заголовки таблицы должны находиться в первой строке.
  2. Любая последующая строка должна содержать хотя бы одну непустую ячейку.
  3. Объединения ячеек в любых строках запрещены.
  4. Для каждой ячейки каждого столбца должен быть определен единый тип хранящихся данных.
  5. Диапазон базы данных должен быть отформатирован в качестве списка и иметь свое имя.

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

Функция БИЗВЛЕЧЬ хорошо работает с корректно отформатированными таблицами.

Примеры использования функции БИЗВЛЕЧЬ в Excel

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

Вид таблиц данных и критериев:

В ячейке B2 запишем условие отбора данных следующим способом:

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

В результате получим следующее:

В ячейке A4 запишем следующую формулу:

  • A8:F15 – диапазон ячеек, в которых хранится БД;
  • 1 – числовое указание номера поля (столбца), из которого будет выводиться значение (необходимо вывести Бренд);
  • A2:F3 – диапазон ячеек, в которых хранится таблица критериев.

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

Пример составления запросов для выборки из базы данных в Excel

Пример 2. Используя таблицу из предыдущего примера выводить все данные для отдельных брендов, указанных в качестве критерия поиска (например, отобразить все характеристики модели бренда Apple и т. д.)

В данном случае для вывода данных о бренде Meizu запишем в ячейку A3 условие:

Для решения задачи можно в каждую ячейку ввести формулу БИЗВЛЕЧЬ, но это займет слишком много времени. Поэтому выделим диапазон ячеек A4:F4 и введем следующую формулу массива CTRL+SHIFT+Enter:

Запись СТОЛБЕЦ(A1:F1) интерпретируется как отдельные ссылки на ячейки из диапазона A1:F1, а функция СТОЛБЕЦ поочередно возвращает ссылки на поля данных исследуемой таблицы.

Получаем следующий результат:

Существенный недостаток функции БИЗВЛЕЧЬ – отсутствие возможности вывода сразу нескольких записей, если были найдены 2 и более совпадений. В таких случаях используют комбинации других функций Excel.

Описание возможностей функции БИЗВЛЕЧЬ в Excel

Функция БИЗВЛЕЧЬ имеет следующую синтаксическую запись:

  • база_данных – обязательный для заполнения, принимает ссылку на диапазон ячеек, являющихся таблицей, отформатированной в соответствии с требованиями, которые предъявляют к базам данных в Excel, списком или БД;
  • поле – обязательный для заполнения, принимающий текстовые строки (наименование поля данных БД) или числовые значения, соответствующие номеру поля данных (столбца, при этом отсчет начинается с единицы: первый столбец соответствует числу 1 и т. д.); В качестве аргумента может быть указана ссылка на ячейку, в которой содержится число или текст, указывающие на поле данных БД, с которой будет работать рассматриваемая функция.
  • условия – обязательный для заполнения, принимает ссылку на диапазон ячеек с критериями поиска в таблице или БД. Минимальным диапазоном условия является ссылка на две ячейки, одна из которых содержит наименование поля БД, а вторая – условие поиска.
  1. Результат выполнения функции является кодом ошибки #ЗНАЧ!, если ей не удалось найти ни одной записи, удовлетворяющей поставленным критериям поиска.
  2. При нахождении двух и более записей в таблице или БД, соответствующих указанным критериям поиска, функция БИЗВЛЕЧЬ генерирует код ошибки #ЧИСЛО!
  3. Диапазон условий не рекомендуют размещать под таблицей или БД, поскольку она может пополняться новыми записями (строками данных) в дальнейшем.
  4. При составлении условий нужно учитывать следующие правила:
  • для точных совпадений текстовые условия помещают в кавычки. Пример записи: =«=male» — для поиска по половому признаку;
  • для частичных совпадений указывают текст, например Samsung. Любая строка, содержащая данную подстроку (например, Samsung S9, Samsung Galaxy) будет считаться найденным совпадением.

Сборка таблиц из разных файлов Excel с помощью Power Query

Постановка задачи

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

Читать еще:  Как сделать чтобы примечание не переносилось в конец документа excel?

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

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

Количество строк (заказов) в таблицах, само-собой, разное, но набор столбцов везде стандартный.

Задача: собрать данные из всех файлов в одну книгу с последующим автоматическим обновлением при добавлении-удалении файлов-городов или строк в таблицах. По итоговой консолидированной таблице затем можно будет строить любые отчеты, сводные таблицы, фильтровать-сортировать данные и т.д. Главное — суметь собрать.

Подбираем оружие

Для решения нам потребуется последняя версия Excel 2016 (в нее нужный функционал уже встроен по умолчанию) или предыдущие версии Excel 2010-2013 с установленной бесплатной надстройкой Power Query от Microsoft (скачать ее можно здесь). Power Query — это супергибкий и супермощный инструмент для загрузки в Excel данных из внешнего мира с последующей их зачисткой и обработкой. Power Query поддерживает практически все существующие источники данных — от текстовых файлов до SQL и даже Facebook 🙂

Если у вас нет Excel 2013 или 2016, то дальше можно не читать (шучу). В более древних версиях Excel подобную задачу можно реализовать только программированием макроса на Visual Basic (что весьма непросто для начинающих) или монотонным ручным копированием (что долго и порождает ошибки).

Шаг 1. Импортируем один файл как образец

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

  • если у вас Excel 2016, то откройте вкладку Данные и выберите Создать запрос — Из файла — Из книги (Data — New Query- From file — From Excel)
  • если у вас Excel 2010-2013 с установленной надстройкой Power Query, то откройте вкладку Power Query и выберите на ней Из файла — Из книги (From file — From Excel)

Затем в открывшемся окне переходим в нашу папку с отчетами и выбираем любой из файлов-городов (не играет роли какой именно, т.к. они все типовые). Через пару секунд должно появиться окно Навигатор, где нужно в левой части выбрать требуемый нам лист (Продажи), а в правой отобразится его содержимое:

Если нажать в правом нижнем углу этого окна кнопку Загрузить (Load) , то таблица будет сразу импортирована на лист в исходном виде. Для одиночного файла — это хорошо, но нам нужно загрузить много таких файлов, поэтому мы пойдем немного другим путем и жмем кнопку Правка (Edit) . После этого должен в отдельном окне отобразиться редактор запросов Power Query с нашими данными из книги:

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

  • отфильтровывать ненужные данные, пустые строки, строки с ошибками
  • сортировать данные по одному или нескольким столбцам
  • избавляться от повторов
  • делить слипшийся текст по столбцам (по разделителям, количеству символов и т.д.)
  • приводить текст в порядок (удалять лишние пробелы, исправлять регистр и т.д.)
  • всячески преобразовывать типы данных (превращать числа как текст в нормальные числа и наоборот)
  • транспонировать (поворачивать) таблицы и разворачивать двумерные кросс-таблицы в плоские
  • добавлять к таблице дополнительные столбцы и использовать в них формулы и функции на встроенном в Power Query языке М.
  • .

Для примера, давайте добавим к нашей таблице столбец с текстовым названием месяца, чтобы потом проще было строить отчеты сводных таблиц. Для этого щелкните правой кнопкой мыши по заголовку столбца Дата и выберите команду Дублировать столбец (Duplicate Column) , а затем щелкните правой кнопкой мыши по заголовку появившегося столбца-дубликата и выберите команды Преобразование — Месяц — Название месяца:

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


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

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

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

Легко и изящно, не правда ли?

Шаг 2. Преобразуем наш запрос в функцию

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

В редакторе запросов перейдите на вкладку Просмотр и нажмите кнопку Расширенный редактор (View — Advanced Editor) . Должно открыться окно, где все наши предыдущие действия будут записаны в виде кода на языке М. Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера:

Теперь аккуратно вносим пару правок:

Смысл их прост: первая строка (filepath)=> превращает нашу процедуру в функцию с аргументом filepath , а ниже мы меняем фиксированный путь на значение этой переменной.

Все. Жмем на Готово и должны увидеть вот это:

Не пугайтесь, что пропали данные — на самом деле все ОК, все так и должно выглядеть 🙂 Мы успешно создали нашу пользовательскую функцию, где запомнился весь алгоритм импорта и обработки данных без привязки к конкретному файлу. Осталось дать ей более понятное имя (например getData ) на панели справа в поле Имя и можно жать Главная — Закрыть и загрузить (Home — Close and Load) . Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера.. Вы вернетесь в основное окно Microsoft Excel, но справа должна появиться панель с созданным подключением к нашей функции:

Шаг 3. Собираем все файлы

Все самое сложное — позади, осталась приятная и легкая часть. Идем на вкладку Данные — Создать запрос — Из файла — Из папки (Data — New Query — From file — From folder) или, если у вас Excel 2010-2013, аналогично на вкладку Power Query. В появившемся окне указываем папку, где лежат все наши исходные файлы-города и жмем ОК. Следующим шагом должно открыться окно, где будут перечислены все найденные в этой папке (и ее подпапках) файлы Excel и детализация по каждому из них:

Жмем Изменить (Edit) и опять попадаем в знакомое окно редактора запросов.

Теперь нужно добавить к нашей таблице еще один столбец с нашей созданной функцией, которая «вытянет» данные из каждого файла. Для этого идем на вкладку Добавить столбец — Пользовательский столбец (Add Column — Add Custom Column) и в появившемся окне вводим нашу функцию getData , указав для ее в качестве аргумента полный путь к каждому файлу:

Читать еще:  Как сделать чтобы в excel внизу отображались листы?

После нажатия на ОК созданный столбец должен добавиться к нашей таблице справа.

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

А теперь «вау-момент» — щелкнем мышью по значку со своенным стрелками в правом верхнем углу добавленного столбца с нашей функцией:

. снимаем флажок Использовать исходное имя столбца как префикс (Use original column name as prefix) и жмем ОК. И наша функция подгрузит и обработает данные из каждого файла, следуя записанному алгоритму и собрав все в общую таблицу:

Для полной красоты можно еще убрать расширения .xlsx из первого столбца с именами файлов — стандартной заменой на «ничего» (правой кнопкой мыши по заголовку столбца — Заменить) и переименовать этот столбец в Город. А также подправить формат данных в столбце с датой.

Все! Жмем на Главной — Закрыть и загрузить (Home — Close & Load) . Все собранные запросом данные по всем городам будут выгружены на текущий лист Excel в формате «умной таблицы»:

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

В будущем, при любых изменениях в папке (добавлении-удалении городов) или в файлах (изменение количества строк) достаточно будет щелкнуть правой кнопкой мыши прямо по таблице или по запросу в правой панели и выбрать команду Обновить (Refresh) — Power Query «пересоберет» все данные заново за несколько секунд.

Поправка. После январских обновлений 2017 года Power Query научился собирать Excel’евские книги сам, т.е. не нужно больше делать отдельную функцию — это происходит автоматически. Таким образом второй шаг из этой статьи уже не нужен и весь процесс становится заметно проще:

  1. Выбрать Создать запрос — Из файла — Из папки — Выбрать папку — ОК
  2. После появления списка файлов нажать Изменить
  3. В окне редактора запросов развернуть двойной стрелкой столбец Binary и выбрать имя листа, который нужно взять из каждого файла

Примеры функция БИЗВЛЕЧЬ в Excel для выборки данных из таблицы

Функция БИЗВЛЕЧЬ в Excel для удобства поиска информации в больших массивах данных. Она предназначена для поиска данных в таблице или базе данных на основе критериев, указанных в качестве ее параметров, и возвращает искомое значение, если оно было найдено, или код ошибки, если совпадения найдены не были или с учетом введенных критериев поиска были найдены 2 и более совпадений.

Пример работы функции БИЗВЛЕЧЬ при выборке данных из таблицы Excel

Как создать базу данных в Excel? Базой данных в программе Excel считается таблица, которая была создана с учетом определенных требований:

  1. Заголовки таблицы должны находиться в первой строке.
  2. Любая последующая строка должна содержать хотя бы одну непустую ячейку.
  3. Объединения ячеек в любых строках запрещены.
  4. Для каждой ячейки каждого столбца должен быть определен единый тип хранящихся данных.
  5. Диапазон базы данных должен быть отформатирован в качестве списка и иметь свое имя.

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

Функция БИЗВЛЕЧЬ хорошо работает с корректно отформатированными таблицами.

Примеры использования функции БИЗВЛЕЧЬ в Excel

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

Вид таблиц данных и критериев:

В ячейке B2 запишем условие отбора данных следующим способом:

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

В результате получим следующее:

В ячейке A4 запишем следующую формулу:

  • A8:F15 – диапазон ячеек, в которых хранится БД;
  • 1 – числовое указание номера поля (столбца), из которого будет выводиться значение (необходимо вывести Бренд);
  • A2:F3 – диапазон ячеек, в которых хранится таблица критериев.

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

Пример составления запросов для выборки из базы данных в Excel

Пример 2. Используя таблицу из предыдущего примера выводить все данные для отдельных брендов, указанных в качестве критерия поиска (например, отобразить все характеристики модели бренда Apple и т. д.)

В данном случае для вывода данных о бренде Meizu запишем в ячейку A3 условие:

Для решения задачи можно в каждую ячейку ввести формулу БИЗВЛЕЧЬ, но это займет слишком много времени. Поэтому выделим диапазон ячеек A4:F4 и введем следующую формулу массива CTRL+SHIFT+Enter:

Запись СТОЛБЕЦ(A1:F1) интерпретируется как отдельные ссылки на ячейки из диапазона A1:F1, а функция СТОЛБЕЦ поочередно возвращает ссылки на поля данных исследуемой таблицы.

Получаем следующий результат:

Существенный недостаток функции БИЗВЛЕЧЬ – отсутствие возможности вывода сразу нескольких записей, если были найдены 2 и более совпадений. В таких случаях используют комбинации других функций Excel.

Описание возможностей функции БИЗВЛЕЧЬ в Excel

Функция БИЗВЛЕЧЬ имеет следующую синтаксическую запись:

  • база_данных – обязательный для заполнения, принимает ссылку на диапазон ячеек, являющихся таблицей, отформатированной в соответствии с требованиями, которые предъявляют к базам данных в Excel, списком или БД;
  • поле – обязательный для заполнения, принимающий текстовые строки (наименование поля данных БД) или числовые значения, соответствующие номеру поля данных (столбца, при этом отсчет начинается с единицы: первый столбец соответствует числу 1 и т. д.); В качестве аргумента может быть указана ссылка на ячейку, в которой содержится число или текст, указывающие на поле данных БД, с которой будет работать рассматриваемая функция.
  • условия – обязательный для заполнения, принимает ссылку на диапазон ячеек с критериями поиска в таблице или БД. Минимальным диапазоном условия является ссылка на две ячейки, одна из которых содержит наименование поля БД, а вторая – условие поиска.
  1. Результат выполнения функции является кодом ошибки #ЗНАЧ!, если ей не удалось найти ни одной записи, удовлетворяющей поставленным критериям поиска.
  2. При нахождении двух и более записей в таблице или БД, соответствующих указанным критериям поиска, функция БИЗВЛЕЧЬ генерирует код ошибки #ЧИСЛО!
  3. Диапазон условий не рекомендуют размещать под таблицей или БД, поскольку она может пополняться новыми записями (строками данных) в дальнейшем.
  4. При составлении условий нужно учитывать следующие правила:
  • для точных совпадений текстовые условия помещают в кавычки. Пример записи: =«=male» — для поиска по половому признаку;
  • для частичных совпадений указывают текст, например Samsung. Любая строка, содержащая данную подстроку (например, Samsung S9, Samsung Galaxy) будет считаться найденным совпадением.
Ссылка на основную публикацию
Adblock
detector