Прокачайте навыки поиска!

Продвинутые функции Excel

Agata Bak-Geerinck

Product Owner Data, Telenet

От Excel — к руководству командой данных!

white space Фотография инструктора Агаты

Возможности функций поиска

Ранее на DataCamp:

VLOOKUP() — вертикальный поиск
HLOOKUP() — горизонтальный поиск

white space

Ограничения:

VLOOKUP() — искомое значение должно быть правее значения поиска
HLOOKUP() — искомое значение должно быть ниже значения поиска

white space

Таблица с несколькими строками и столбцами, одна ячейка выделена жёлтым

white space

Таблица с несколькими строками и столбцами, одна ячейка выделена жёлтым

Что такое массив?

Массив — набор значений в строке или столбце, либо их комбинация$^1$

Пример массива

Таблица Excel состоит из:

Заголовка: имён строк и столбцов
Массива: набора значений

¹ https://support.microsoft.com/

Встречайте... XLOOKUP!

XLOOKUP() — функция поиска, которая позволяет искать в любом направлении благодаря массивам.

Новинка Excel 2021!

Синтаксис: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Пример функции XLOOKUP

Функция XLOOKUP с именованными диапазонами

Двумерный поиск? INDEX()

Таблица с 4 столбцами и 3 строками для иллюстрации функции INDEX

white space

Продажи Labels за апрель? = INDEX ( B2:D4, 2, 2)

white space

Синтаксис: INDEX(array, row_num, [column_num])
Возвращает значение ячейки в указанном массиве

Двумерный поиск? MATCH()

Синтаксис: MATCH(lookup_value, lookup_array, [match_type])
Определяет строку и столбец для ссылки

Таблица Excel с 5 столбцами и 4 строками для иллюстрации функций INDEX и MATCH

Где найти продажи Labels за апрель?

= Match ( "Labels", Categories, 0) = строка 2

= Match ( "APR", Months, 0) = столбец 2

Двумерный поиск? INDEX и MATCH — идеальный союз

Таблица Excel с 5 столбцами и 4 строками для иллюстрации функций INDEX и MATCH

= INDEX ( B2:D4, 2, 2)

= INDEX(array, MATCH(rows), MATCH(columns))

= INDEX ( Sales, MATCH( "Labels", Categories, 0), MATCH( "APR", Months, 0) )

Знакомство с набором данных!

Коммерческий набор данных:

Визуальное описание набора данных: контур США, значок корзины покупателя и монет

Обзор данных:

Информация о заказах
Социально-демографические данные клиентов
Подробные данные о товарах
Продажи, объёмы, скидки и прибыль

white space

Подробнее — в листе метаданных.

Давайте потренируемся!

Продвинутые функции Excel