Как вытащить данные из таблицы эксель

Обновлено: 08.07.2024

Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает видимые данные из сводной таблицы.

В этом примере =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.("Продажи"; A3) возвращает общий объем продаж из сводной таблицы:

Синтаксис

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных; сводная_таблица; [поле1; элемент1; поле2; элемент2]; …)

Аргументы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ описаны ниже.

поле_данных

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

сводная_таблица

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

поле1, элемент1, поле2, элемент2.

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

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

Можно быстро ввести простую формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, введя = (знак равенства) в ячейке, в которой должно быть возвращено значение, и затем щелкнув ячейку в сводной таблице, содержащей необходимые данные.

Вы можете отключить эту возможность. Для этого нужно выбрать любую ячейку в существующей сводной таблице, а затем перейти к вкладке Анализ сводной таблицы > Сводная таблица > Параметры > и снять флажок у параметра Генерировать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

Вычисляемые поля или элементы и дополнительные вычисления могут включаться в расчеты для функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

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

Если аргументы "поле" и "элемент" описывают одну ячейку, возвращается значение, содержащееся в этой ячейке, независимо от его типа (строка, число, ошибка или пустая ячейка).

Если аргумент "элемент" содержит дату, необходимо представить это значение как порядковый номер или воспользоваться функцией ДАТА, чтобы это значение не изменилось при открытии листа в системе с другими языковыми настройками. Например, элемент, ссылающийся на дату 5 марта 1999 г., можно ввести двумя способами: 36 224 или ДАТА(1999;3;5). Время можно задать в виде десятичных значений или с помощью функции ВРЕМЯ.

Примеры

Формулы в примере ниже представляют различные методы извлечения данных из сводной таблицы.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном и том же порядке). Необходимо помочь функции ВПР определить, из какой именно таблицы ей извлечь результаты поиска?

1. Использование обработки условия при помощи ЕСЛИ.

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

Для того, чтобы легче было работать с таблицами ставок, используем именованные диапазоны и обозначим их tabl1, tabl2 и tabl3.

В качестве условия для использования каждой из таблиц служит стаж работника. Таким образом, внутри функции ВПР (VLOOKUP) используем обработку условий при помощи ИЛИ (IF). В ячейке D2 запишем:

=ВПР(C2;ЕСЛИ(B2>3;tabl3;ЕСЛИ(B2<1;tabl1;tabl2));2;1)

Если стаж более 3 лет, то используем tabl3. Если нет, то проверяем условие "стаж менее года". В этом случае данные будем извлекать из tabl1. Если и это не выполняется, тогда остается только второй вариант и диапазон tabl2.

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

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

Сумма комиссионных находится простым произведением ставки комиссионных на объем продаж. Далее копируем эти формулы для всех менеджеров.

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

Как видите, все отлично работает, хотя диапазон поиска находится левее столбца с условиями. Своего рода "обратный" или "левый" ВПР, о котором мы уже рассказывали. (см. ссылки в конце статьи).

2. Использование функции ДВССЫЛ и именованных диапазонов.

Еще один хороший способ создать ссылку на именованный диапазон - это функция ДВССЫЛ (INDIRECT). Она позволяет преобразовать текст в ссылку. Разберем на примере.

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

Чтобы не вводить критерии поиска руками, создадим два выпадающих списка: один - с именами, второй - с названиями месяцев.

Когда вам нужно найти и извлечь столбец данных из одной таблицы и поместить ее в другую, используйте функцию VLOOKUP. Эта функция работает в любой версии Excel под Windows и на Mac, а также в Google Sheets. Она позволит вам найти данные в одной таблице используя некоторый идентификатор в ней, общий с другой таблицей. Обе таблицы могут находиться на разных листах или даже в в разных книгах. Есть также функция HLOOKUP, которая делает то же самое, но с данными, расположенными горизонтально, по строкам.

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

Лучшие варианты

Перед тем, как мы погрузимся в функции Excel, вы в курсе, что Envato Market содержит массу скриптов и плагинов Excel, которые позволят вам выполнять продвинутые функции?

Например, вы можете:

Скринкаст

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

Использование VLOOKUP

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

Уникальный идентификатор должен быть как серийный номер, не должно быть двух одинаковых идентификаторов в одной таблице.

Синтаксис

Синтаксис функции VLOOKUP:

=VLOOKUP(искомое значение, диапазон таблицы, номер столбца, [true/false])

Вот что означают эти аргументы:

Искомое значение. Ячейка, в которой находится уникальный идентификатор.
Диапазон таблицы. Диапазон ячеек, которые содержат идентификатор в первом столбце, а в последующих столбцах располагаются остальные данные.
Номер столбца. Номер столбца, в котором находятся данные, которые вам нужны. Не путайте его с буквой столбца. На рисунке выше, штаты находятся в столбце 4.
True/False. Этот аргумент необязателен. True означает, что приемлемо приблизительное совпадение, а False означает, что допустимо только точное совпадение.

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

Синтаксис функции VLOOKUP

Определение имени диапазона для создания абсолютной ссылки

В Vlookup example.xlsx посмотрите на лист Sales Amounts. Мы введем формула в ячейку B5, затем используем возможность автозаполнения, чтобы скопировать формула в ячейки ниже нее. Это значит, что диапазон ячеек в формуле должен быть абсолютной ссылкой. Хороший способ сделать это - это задать имя для диапазона ячеек.

Задание имени диапазона в Excel

Задание имени диапазона в Google Sheets.

В Google Sheets имя задается немного по другому.

Ввод формулы

Чтобы ввести формулу, перейдите на лист Sales Amounts и кликните по ячейке B5.

Нажмите Enter.

Ввод функции VLOOKUP

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

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

Кликните дважды, чтобы заполнить значения ниже по столбцу.

Дважды кликните перекрестием Автозаполнения, чтобы скопировать формулу ниже по столбцу

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

Использование MATCH

Функция MATCH не возвращает вам значение из данных; вы задаете значение, которое вы ищете и функция возвращает позицию этого значения. Это как спросить, где находится Мейн стрит, 135, и получить ответ, что это четвертое здание вниз по улице.

Предположим, что из вот такой базы данных по продажам:

. вы создали небольшую, но симпатичную сводную таблицу:

Но ваш руководитель хочет не совсем ее, а что-то похожее на:

То есть имеем несколько ощутимых трудностей:

Исходный внешний вид сводной таблицы не подходит - дизайн отчета должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). "Дорабатывать напильником" дизайн сводной - долгий и мучительный процесс. И не факт, что красота не слетит после пересчета и обновления.
Из всей сводной для отчета вам нужны не все данные, а только конкретные модели Ford по Питеру - придется руками фильтровать.
Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зеленых ячейках, но среднее по месяцу в итогах - сводная так не умеет.
Полученные в сводной результаты - еще не конец, нам необходимо произвести с ними какие-то дополнительные вычисления: пересчитать выручку в тысячах, добавить прогноз на апрель, сравнить этот год с прошлым. Многое из перечисленного в сводных или невозможно в принципе (особенно для сводных на основе OLAP-кубов) или делается, но "через одно место" с помощью вычисляемых полей и объектов.
Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).

Сделаем на отдельном от сводной листе заготовку отчета:

Выделите первую ячейку зеленого диапазона, введите знак "равно" и щелкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки а-ля "морской бой" Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:

Давайте разберем ее подробно:

Первый ее аргумент ("Выручка") - это имя извлекаемого поля.
Второй (Лист1!$A$4) - это адрес первой ячейки сводной таблицы, откуда мы берем данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать из какой именно нужно вытащить число.
Все остальные аргументы начиная с третьего - это попарно название поля и его значение, т.е., в нашем случае, это имя модели (Наименование="Fiesta") и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто "январь".

А теперь самое интересное.

Аккуратно замените в формуле "Fiesta" на $С7, а единичку на D$5 и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зеленые ячейки.

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

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

Читайте также: