Как найти исходные данные в экселе

---

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

    Видеоинструкции по использованию надстройки MulTEx

ГЛАВНАЯ

ТРЕНИНГИ

   Быстрый старт
   Расширенный Excel
   Мастер Формул
   Прогнозирование
   Визуализация
   Макросы на VBA

КНИГИ

   Готовые решения
   Мастер Формул
   Скульптор данных

ВИДЕОУРОКИ

ПРИЕМЫ

   Бизнес-анализ
   Выпадающие списки
   Даты и время
   Диаграммы
   Диапазоны
   Дубликаты
   Защита данных
   Интернет, email
   Книги, листы
   Макросы
   Сводные таблицы
   Текст
   Форматирование
   Функции
   Всякое
PLEX

   Коротко
   Подробно
   Версии
   Вопрос-Ответ
   Скачать
   Купить

ПРОЕКТЫ

ОНЛАЙН-КУРСЫ

ФОРУМ

   Excel
   Работа
   PLEX

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

Техническая поддержка сайта

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович         ИНН 633015842586         ОГРНИП 310633031600071

«Исходные данные» — это вкладка, на которой указываются действия по обработке данных, предшествующие выводу на лист.

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

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

1. Источник данных
2. Список действий

Блок Источник данных

В программе реализованы 5 режимов для исходных данных

Задачей блока «Источник данных» является формирование массива исходных значений, которые по одному будут передаваться на первый уровень списка действий и подставляться в поле «Исходное значение» редактора списка действий (на скриншоте ниже выделено красным).

Реальной подстановки в поле значений не происходит. В данном контексте слово «подставляется» используется лишь для объяснения механизмов работы парсера.

То есть, если мы выбрали режим «Брать данные с листа Excel из заданного столбца» и указали столбец 1 «A» в качестве источника данных, то из этого столбца значения ячеек по одному будут подставляться в поле исходное значение (на скриншоте). И если в 1 столбце, например, было 10 ссылок, то парсер будет обрабатывать данные ссылки по одной.

Точно так же парсер будет работать для всех других режимов блока «Источник данных». Значения передаются по одному.

Блок Список действий

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

Например, на первом уровне мы получили массив из 10 значений. Каждое из этих значений переходит по одному на 2 подуровень, где в результате преобразований мы получаем массив из 5 значений. Каждое из этих значений переходит по одному либо на следующий подуровень, либо если его нет — на вывод на лист или на загрузку файлов (в этом случае мы получим 10 * 5 = 50 строк).

Если в результате обработки данных списком действий мы получаем на самом «нижнем» подуровне пустое значение, то дальнейшего перехода к выводу на лист или к скачиванию файлов не происходит (с оговоркой, написанной в информационной сноске ниже). Поэтому если вам нужно, чтобы любое исходное значение было отражено на выходе (например, в новом формируемом файле), нужно быть уверенным в том, что значение «на выходе» вкладки «Исходные данные» будет не пустым. Для этого можно использовать, например, действие из раздела «Текстовые» — «Значение по умолчанию».

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

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

Кнопка «Run ParserStart» отображается только при включенном в общих настройках программы «Режиме разработчика». Её функция — запуск специального набора действий «ParserStart». Используется, например, в тех случаях, когда нужно протестировать работу парсера и быстро пройти авторизацию (без перехода на вкладку «Дополнительно», подвкладку «Наборы действий» и т.д.)

Кнопка «Редактировать список действий» открывает второе окно программы — окно редактора списка действий. Редактируемый уровень выбирается переключателем «Текущий уровень». Также возможно перемещаться на нижележащий уровень при помощи кнопки «Перейти на подуровень …». Если ниже нет никакого уровня, то кнопка меняется на «Добавить подуровень …».

Справа от переключателя «Текущий уровень» находится кнопка удаления подуровня и всех его действий. Данное действие нельзя отменить.

Справа от кнопки «Редактировать список действий» находится кнопка вставки разрыва подуровней (она появляется только при условии, что в списке действий слева выбрано какое-либо действие). На скриншоте отмечена красным.

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

Выделенное действие и всё, что «выше», осталось на месте. 3 и 4 действие перешли на 2 подуровень. Если бы в настройке до нажатия кнопки был подуровень №2, он бы стал подуровнем №3.

Кстати, обратите внимание, что сейчас на скриншоте не выделено ни одно действие, как следствие кнопка вставки разрыва не видна.

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

В этом примере =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.(«Продажи»; 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.

См. также

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Алгоритм работы функции:

1. Указываем, что искать (абсолютное значение или что-то в ячейке).
2. Указываем, где искать (некий диапазон в другой таблице).
3. Указываем, какое значение возвращать. Это – самая сильная сторона ВПР. Когда функция находит совпадение (например, фамилию работника), она возвращает не саму фамилию, а значение, которое находится в той же строке. То есть мы можем вернуть зарплату этого работника, его фамилию, дату рождения – в общем, все, что захотим, при условии, что все данные в одной строке принадлежат одному работнику (данные упорядочены).
4. Указываем точность совпадения. Эксель позволяет вернуть как точное совпадение, так и частичное. Рекомендуем всегда пользоваться точным совпадением, потому что «частичное совпадение» – вещь весьма расплывчатая, Эксель может взять вообще не те данные, которые вам на самом деле нужны. Как-то повлиять на алгоритм выбора простыми методами нельзя.

На практике это выглядит вот так: ВПР(B2;’Дневная потребность’!$A$3:$B$12;2;0), где:

• B2 – что мы будем искать.
• ‘Дневная потребность’!$A$3:$B$12 – где мы будем искать. Указываем таблицу на другом листе.
• 2 – из какого столбца мы будем возвращать данные, когда найдем соответствие.
• 0 – используем точное соответствие (1 – использовать приблизительное, не рекомендуется).

Примеры

Разберем несколько задач с использованием функции ВПР в Excel.

1. Поиск неизвестного в общей таблице.

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

Исходная информация:

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

Задача: найти стоимость препарата Хепилор.

Решение состоит в последовательности следующих действий:

• Выбор критерия: в ячейку В12 вводим название Лекарственного препарата «Хепилор».
• Выбор массива: выбираем диапазон начала и конца таблицы, где должен осуществляться поиск: В3:D10.
• Выбор номера столбца: указываем номер столбца, из которого должна быть считана информация в одной строке с названием препарата. В нашем примере это 3, т. к. столбец №1 расположен вне области нужного нам диапазона.
• Ставим функцию «0» или «Ложь».

Аналогично можно произвести поиск производителя Хепилора. Для этого потребуется заменить номер столбца, где расположены необходимые данные, т. е. 3 на 2.

2. Пример расчета неизвестного показателя из исходных данных.

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

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

  

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

  

В строке 2 указаны номера колонок – колонки обязательно нужно нумеровать, без этого функция работать не будет.

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

Нам нужно заполнить аргументы функции:

Искомое значение – это то значение, по которому мы будем искать. В нашем случае это «Вес кота», поэтому указываем B2. Таблица – это место, где мы будем искать. Нужно выбрать всю таблицу за исключением заголовка и номеров колонок.

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

Далее указываем номер столбца, из которого нужно брать данные. В нашем случае – второй столбец, поскольку в нем указано количество пакетиков. В «Интервальный просмотр» ставим 0, чтобы искать по точному соответствию.

Применяем – получаем результат. Растягиваем его на остальные ячейки.

Пакетик корма стоит 60 рублей, поэтому высчитываем стоимость как D2=C2*60 (и растягиваем на остальные пустые ячейки), в C9 пишем =СУММ(C2:C8), в D10 пишем =СУММ(D2:D8).

Получаем 14.5 пакетиков в день на сумму 870 рублей, по факту придется купить 15 на сумму 900 рублей, ибо половину пакетика никто не продаст.

3. Комбинирование таблиц с ВПР.

Исходные данные: имеем 2 таблицы.

«Отчет о количестве товара» и «Отчет о цене за единицу товара».

Задача: объединить данные двух таблиц.

Порядок действий:

Выбираем ячейку для вставления данных (D3) и пишем функцию: ВПР (В3;F3:G14;2;0), где:

• выбор критерия:В3;
• выбор диапазона: F3:G14;
• № столбца: 2;
• стандартно: «0» или «Ложь».

Алгоритм решения:

1. Поиск совпадений с верхней ячейки первого столбца.
2. Поиск соответствия установленному критерию сверху вниз.
3. После того, как найден Хепилор, производится отсчет столбцов вправо.
4. ВПР выдает искомое значение, в нашем случае это цена – 86,90.

Чтобы в столбец D первой таблицы вставить данные не по одной строке, а в целом, нужно скопировать функцию до последнего критерия. Но, для избежания «съезжания» массива вниз, нужно использовать абсолютные ссылки для диапазона в ячейке D3. Для этого нужно выделить диапазон F3:G14 и нажать клавишу F4, далее завершить копирование формулы.

Итоговая таблица будет такая:

Здесь вы сможете скачать примеры применения ВПР Excel

ВПР в Гугл Таблицах

У Гугла все работает абсолютно так же. Функция называется VLOOKUP, но вы можете написать ВПР, и Гугл автоматически поменяет название после того, как вы примените функцию. Единственная особенность – формулу нужно вводить непосредственно в поле, конструктор недоступен.

Примеры с пошаговыми инструкциями

1. Пример. Осуществляем поиск данных из списка.

Дана таблица с именами и оценками учащихся.

Требуется быстро найти оценку конкретного ученика, например, Martha.

Алгоритм: =VLOOKUP(E2,$A$2:$B$10,2,False)

При введении в ячейку Е2 любого имени, в Е3 будет отображаться значения его оценки.

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

2. Пример.

Создаем 2 таблицы – основную и ту, в которой мы будем искать информацию.

Выбираем первую ячейку, пишем =ВПР(. После этого Гугл предлагает нам ввести или выбрать данные. Данные разделяются знаком ;.

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

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

Возможные ошибки новичков

• Не фиксирован диапазон. Если вы не зафиксируете диапазон при указании границ таблицы с данными, в первой ячейке формула применится верно, а вот в остальных будут неправильные значения, потому что диапазон таблицы «поедет» вслед за смещением. Поэтому не забывайте поставить $ перед каждой координатой или просто выберите все и нажмите F4.
• Неправильно выбран диапазон таблицы. В диапазоне нужно указывать всю таблицу, за исключением заголовка и номеров столбцов. Если у вас выскакивает ошибка, связанная с неправильной ссылкой – поищите проблему в диапазоне таблицы.
• Поиск происходит не по первому столбцу таблицы. В этом случае результат может быть непредсказуем – всегда ищите совпадение именно в первом столбце.

Что почитать по теме

• Справка от Майкрософт.
• Справка от Гугла.

FAQ

Какой результат выдаст функция, если найдет несколько вхождений в таблице, в которой мы ищем данные?

Функция вернет результат из первого найденного вхождения.

Можно ли писать внутри функции формулы?

Да, вы можете как написать формулу внутри одного из параметров функции, так и передать результаты работы функции в формулу. Например, =ECЛИ(ВПР(“Иванов”;’сотрудники’!$B$3:$B$203;3;0)=1;”Есть”;”Не найден”) будет писать «Есть», если такой сотрудник есть в базе (и в специальной колонке ему присвоено значение 1), и «Не найден» в противном случае.

Подведем итоги 

Тезисно:

• ВПР позволяет вам вставить какие-то данные из другого листа (или с этого же, если они попадают под определенные критерии).
• Чтобы написать функцию, вам нужно указать: какие данные нужно искать; где их искать; из какой колонки таблицы брать результат; искать ли по точному совпадению.
• В Excel и Google Sheets – одинаковый синтаксис для ВПР, единственная разница – Excel позволяет создать функцию через мастера создания функций.