Сопоставление данных нескольких вкладок — Аналитические задачи в бизнесе

• Функция VLOOKUP
• Функция QUERY
• Как решать задачи с помощью этих функций
• Выводы

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

В этом уроке мы изучим две мощные функции из Google Sheets, которые помогают легко находить и собирать нужные данные: VLOOKUP и QUERY. На примере аналитической задачи мы разберемся, как их применять в Google Sheets.

Функция VLOOKUP

VLOOKUP (от англ. vertical lookup — вертикальный просмотр) — это функция в Google Sheets, которая позволяет найти значения в одной таблице и перенести их в другую.

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

Нам нужно перенести информацию о количестве покупок во вторую таблицу, чтобы собрать данные вместе:

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

Поэтому можно воспользоваться функцией VLOOKUP, чтобы заполнить столбец «Количество покупок» во второй таблице:

Функция VLOOKUP имеет четыре параметра:

=VLOOKUP(ключ, диапазон поиска, номер столбца искомого значения, интервальный просмотр)

Рассмотрим параметры подробнее:

1. Ключ — это значение, по которому мы ищем необходимую информацию из другой таблицы. В нашем случае ключ — это значение столбца «Пользователь», потому что он есть в обеих таблицах
2. Диапазон поиска — таблица, в которой мы ищем интересующее значение
3. Номер столбца искомого значения — номер столбца, в котором можно найти нужное нам значение. В нашем случае, это второй столбец «Количество покупок»
4. Интервальный просмотр — логическое значение, которое определяет, какой поиск нам нужен (точный false или приближенный true). Нам нужен точный поиск, поэтому ставим false

Подробнее о функции VLOOKUP можно прочитать здесь.

Функция QUERY

QUERY (от англ. query — запрос) — это функция в Google Sheets, которая позволяет конструировать таблицы с необходимой информацией.

С помощью QUERY можно быстро фильтровать, сортировать и агрегировать данные.

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

Выбираем область, в которую необходимо вставить новую таблицу и вводим функцию QUERY. Она имеет три параметра:

=QUERY(данные, запрос, номер строки с названиями столбцов)

Разберем их подробнее:

1. Данные — это диапазон значений, из которых нужно построить новую таблицу
2. Запрос — это строка, которая указывает, какие данные необходимо получить. Запрос пишется на языке запросов, схожем с SQL. Подробнее об этом можно прочитать здесь
3. Номер строки с названиями столбцов — это опциональный параметр, обозначающий номер строки, в которой содержатся названия столбцов таблицы

Так выглядит поиск данных в нашем случае:

Отдельно обсудим наш случай:

1. Данные B2:D6 — вся таблица с пользователями, количеством их покупок и городами
2. Запрос "select D, SUM(C) group by D" — нам нужна таблица с двумя столбцами, «Город» и «Суммарное количество покупок». Через ключевое слово SELECT мы выбираем столбец D с городами, а затем собираем столбец с суммарным количеством покупок по городам с помощью SUM и GROUP BY
3. Номер строки с названиями столбцов — 1

Подробнее о функции QUERY, ее возможностях и особенностях можно прочитать здесь.

Как решать задачи с помощью этих функций

Теперь попробуем пошагово решить комплексную задачу, используя знания о VLOOKUP и QUERY.

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

• С данными о количестве продаж каждой модели
• С данными о ценах на каждую модель

Сначала мы соберем данные из таблицы в одном месте с помощью VLOOKUP. Далее мы конструируем таблицу со столбцами «Модель ноутбука» и «Выручка» через QUERY. В итоге мы определим, какая модель принесла больше выручки.

Шаг 1. Сначала решаем, где собирать данные. Видим, что на первом листе есть информация о модели ноутбука:

На втором листе мы видим информацию о цене, за которую был продан ноутбук:

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

Шаг 2. Собираем данные во второй таблице с помощью функции VLOOKUP:

• В качестве ключа берем значение id — это столбец, который присутствует в обеих таблицах и содержит уникальные значения
• Наш диапазон значений — таблица с первого листа. Чтобы указать ее, вводим:
  • Название листа, с которого нужно подтянуть данные
  • Символ !
  • Нужный нам диапазон значений
• Номер столбца искомого значения 2 — номер столбца model
• Нам необходим точный поиск, поэтому последним параметром пишем false

Посмотрим, как выглядит функция целиком:

Шаг 3. Чтобы написанная функция применилась ко всему столбцу, мы фиксируем диапазон значений с помощью знака $, нажимаем Enter и тянем за край ячейки C2 до конца таблицы:

Шаг 4. Как только мы собрали весь столбец model, начинаем конструировать таблицу с выручкой с помощью функции QUERY.

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

Построенная таблица явно показывает, что ноутбуки ASUS принесли самую большую выручку.

Выводы

В этом уроке мы познакомились с функциями подготовки данных, такими как VLOOKUP и QUERY. Мы рассмотрели несколько примеров использования этих функций для решения аналитических задач, но на практике их применение может быть еще более широким. Дополнительную информацию можно найти в официальной документации Google Sheets.

А теперь вспомним ключевые моменты урока:

• VLOOKUP — функция, которая помогает находить значения в одной таблице и переносить их в другую. Ее удобно использовать, когда необходимые для анализа данные расположены в разных таблицах. В функции четыре параметра:
  • Ключ
  • Диапазон поиска
  • Номер столбца искомого значения
  • Интервальный просмотр
• QUERY — функция, которая позволяет быстро фильтровать, сортировать и агрегировать данные. В функции три параметра:
  • Данные
  • Запрос
  • Номер строки с заголовками

Самостоятельная работа

Дополнительные материалы

1. Данные о продажах ноутбуков

---

Остались вопросы? Задайте их в разделе «Обсуждение»

Вам ответят команда поддержки Хекслета или другие студенты

Об обучении на Хекслете

• Статья «Как учиться и справляться с негативными мыслями»
• Статья «Ловушки обучения»
• Статья «Сложные простые задачи по программированию»
• Вебинар «Как самостоятельно учиться»