Rivalry2

HTML Diff

0 added 0 removed

Original 2026-01-01

Modified 2026-02-26

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

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

3 <h2>Функция VLOOKUP</h2>

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

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

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

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

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

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

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

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

12 <ol><li>Ключ- это значение, по которому мы ищем необходимую информацию из другой таблицы. В нашем случае ключ - это значение столбца "Пользователь", потому что он есть в обеих таблицах</li>

13 <li>Диапазон поиска- таблица, в которой мы ищем интересующее значение</li>

14 <li>Номер столбца искомого значения- номер столбца, в котором можно найти нужное нам значение. В нашем случае, это второй столбец "Количество покупок"</li>

15 <li>Интервальный просмотр- логическое значение, которое определяет, какой поиск нам нужен (точный false или приближенный true). Нам нужен точный поиск, поэтому ставим false</li>

16 </ol>Подробнее о функции VLOOKUP можно прочитать<a>здесь</a>.

17 <h2>Функция QUERY</h2>

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

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

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

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

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

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

24 <ol><li>Данные- это диапазон значений, из которых нужно построить новую таблицу</li>

25 <li>Запрос- это строка, которая указывает, какие данные необходимо получить. Запрос пишется на языке запросов, схожем с SQL. Подробнее об этом можно прочитать<a>здесь</a></li>

26 <li>Номер строки с названиями столбцов- это опциональный параметр, обозначающий номер строки, в которой содержатся названия столбцов таблицы</li>

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

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

29 <ol><li>ДанныеB2:D6 - вся таблица с пользователями, количеством их покупок и городами</li>

30 <li>Запрос"select D, SUM(C) group by D" - нам нужна таблица с двумя столбцами, "Город" и "Суммарное количество покупок". Через ключевое слово SELECT мы выбираем столбец D с городами, а затем собираем столбец с суммарным количеством покупок по городам с помощью SUM и GROUP BY</li>

31 <li>Номер строки с названиями столбцов- 1</li>

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

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

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

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

36 <ul><li>С данными о количестве продаж каждой модели</li>

37 <li>С данными о ценах на каждую модель</li>

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

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

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

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

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

43 <ul><li>В качествеключаберем значение id - это столбец, который присутствует в обеих таблицах и содержит уникальные значения</li>

44 <li>Нашдиапазон значений- таблица с первого листа. Чтобы указать ее, вводим:<ul><li>Название листа, с которого нужно подтянуть данные</li>

45 <li>Символ !</li>

46 <li>Нужный нам диапазон значений</li>

47 </ul></li>

48 <li>Номер столбца искомого значения2 - номер столбца model</li>

49 <li>Нам необходим точный поиск, поэтому последним параметром пишем false</li>

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

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

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

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

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

55 <h2>Выводы</h2>

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

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

58 <ul><li>VLOOKUP - функция, которая помогает находить значения в одной таблице и переносить их в другую. Ее удобно использовать, когда необходимые для анализа данные расположены в разных таблицах. В функции четыре параметра:<ul><li>Ключ</li>

59 <li>Диапазон поиска</li>

60 <li>Номер столбца искомого значения</li>

61 <li>Интервальный просмотр</li>

62 </ul></li>

63 <li>QUERY - функция, которая позволяет быстро фильтровать, сортировать и агрегировать данные. В функции три параметра:<ul><li>Данные</li>

64 <li>Запрос</li>

65 <li>Номер строки с заголовками</li>

66 </ul></li>

67 </ul>