Анализ данных Excel — Функции поиска

Вы можете использовать функции Excel для —

• Найти значения в диапазоне данных — VLOOKUP и HLOOKUP
• Получить значение или ссылку на значение из таблицы или диапазона — INDEX
• Получить относительное положение указанного элемента в диапазоне ячеек — MATCH

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

Использование функции VLOOKUP

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

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

куда

• lookup_value — это значение, которое вы хотите найти. Lookup_value может быть значением или ссылкой на ячейку. Значение Lookup_value должно находиться в первом столбце диапазона ячеек, указанного в table_array.

• table_array — это диапазон ячеек, в которых VLOOKUP будет искать lookup_value и возвращаемое значение. table_array должен содержать

  • значение lookup_value в первом столбце и

  • возвращаемое значение, которое вы хотите найти

    Примечание . Первый столбец, содержащий lookup_value, может быть отсортирован в порядке возрастания или нет. Тем не менее, результат будет основан на порядке этого столбца.

• col_index_num — номер столбца в table_array, который содержит возвращаемое значение. Числа начинаются с 1 для самого левого столбца таблицы-массива

• range_lookup — это необязательное логическое значение, которое указывает, хотите ли вы, чтобы VLOOKUP нашел точное или приблизительное совпадение. range_lookup может быть

  • опущено, и в этом случае предполагается, что оно TRUE, и VLOOKUP пытается найти приблизительное совпадение

  • ИСТИНА, и в этом случае VLOOKUP пытается найти приблизительное совпадение. Другими словами, если точное совпадение не найдено, возвращается следующее наибольшее значение, которое меньше lookup_value

  • ЛОЖЬ, в этом случае VLOOKUP пытается найти точное совпадение

  • 1, и в этом случае предполагается, что это ИСТИНА, а VLOOKUP пытается найти приблизительное совпадение

  • 0, в этом случае предполагается, что это ЛОЖЬ, а VLOOKUP пытается найти точное соответствие

lookup_value — это значение, которое вы хотите найти. Lookup_value может быть значением или ссылкой на ячейку. Значение Lookup_value должно находиться в первом столбце диапазона ячеек, указанного в table_array.

table_array — это диапазон ячеек, в которых VLOOKUP будет искать lookup_value и возвращаемое значение. table_array должен содержать

значение lookup_value в первом столбце и

возвращаемое значение, которое вы хотите найти

Примечание . Первый столбец, содержащий lookup_value, может быть отсортирован в порядке возрастания или нет. Тем не менее, результат будет основан на порядке этого столбца.

col_index_num — номер столбца в table_array, который содержит возвращаемое значение. Числа начинаются с 1 для самого левого столбца таблицы-массива

range_lookup — это необязательное логическое значение, которое указывает, хотите ли вы, чтобы VLOOKUP нашел точное или приблизительное совпадение. range_lookup может быть

опущено, и в этом случае предполагается, что оно TRUE, и VLOOKUP пытается найти приблизительное совпадение

ИСТИНА, и в этом случае VLOOKUP пытается найти приблизительное совпадение. Другими словами, если точное совпадение не найдено, возвращается следующее наибольшее значение, которое меньше lookup_value

ЛОЖЬ, в этом случае VLOOKUP пытается найти точное совпадение

1, и в этом случае предполагается, что это ИСТИНА, а VLOOKUP пытается найти приблизительное совпадение

0, в этом случае предполагается, что это ЛОЖЬ, а VLOOKUP пытается найти точное соответствие

Примечание. Если range_lookup опущено или TRUE или 1, VLOOKUP работает правильно, только если первый столбец в table_array отсортирован в порядке возрастания. В противном случае это может привести к неверным значениям. В таком случае используйте FALSE для range_lookup.

Использование функции VLOOKUP с range_lookup TRUE

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

table_array —

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

Назовите этот массив как Оценки .

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

Как вы можете наблюдать,

• col_index_num — указывает, что столбец возвращаемого значения в table_array равен 2

• range_lookup ИСТИНА

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

  • Вы также можете получить возвращаемое значение для приблизительных совпадений. т.е. VLOOKUP вычисляет следующим образом —

col_index_num — указывает, что столбец возвращаемого значения в table_array равен 2

range_lookup ИСТИНА

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

Вы также можете получить возвращаемое значение для приблизительных совпадений. т.е. VLOOKUP вычисляет следующим образом —

Метки	Пройти категорию
<35	Потерпеть поражение
> = 35 и <50	Третий класс
> = 50 и <60	Второй класс
> = 60 и <75	Первый класс
> = 75	Первый класс с отличием

Вы получите следующие результаты —

Использование функции VLOOKUP с range_lookup FALSE

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

table_array —

Назовите этот массив как ProductInfo.

Вы можете получить цену продукта по идентификатору продукта с помощью функции VLOOKUP, поскольку идентификатор продукта находится в первом столбце. Цена указана в столбце 3, поэтому col_index_ num должно быть 3.

• Использовать функцию VLOOKUP с range_lookup как TRUE
• Использовать функцию VLOOKUP с range_lookup как FALSE

Правильный ответ из массива ProductInfo: 171.65. Вы можете проверить результаты.

Вы наблюдаете, что вы получили —

• Правильный результат, когда range_lookup равен FALSE, и
• Неправильный результат, когда range_lookup равен TRUE.

Это связано с тем, что первый столбец в массиве ProductInfo не отсортирован в порядке возрастания. Следовательно, не забывайте использовать FALSE всякий раз, когда данные не отсортированы.

Использование функции HLOOKUP

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

пример

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

• Назовите этот массив ProductRange. Вы можете узнать цену продукта по идентификатору продукта с функцией HLOOKUP.

Назовите этот массив ProductRange. Вы можете узнать цену продукта по идентификатору продукта с функцией HLOOKUP.

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

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

куда

• lookup_value — это значение, которое будет найдено в первой строке таблицы

• table_array — таблица данных, в которой ищутся данные

• row_index_num — номер строки в table_array, из которого будет возвращено соответствующее значение

• range_lookup — это логическое значение, которое указывает, хотите ли вы, чтобы HLOOKUP нашел точное или приблизительное совпадение

• range_lookup может быть

  • опущено, и в этом случае предполагается, что оно TRUE, и HLOOKUP пытается найти приблизительное соответствие

  • ИСТИНА, и в этом случае HLOOKUP пытается найти приблизительное совпадение. Другими словами, если точное совпадение не найдено, возвращается следующее наибольшее значение, которое меньше lookup_value

  • ЛОЖЬ, и в этом случае HLOOKUP пытается найти точное совпадение

  • 1, и в этом случае предполагается, что это ИСТИНА, а HLOOKUP пытается найти приблизительное совпадение

  • 0, в этом случае предполагается, что это ЛОЖЬ, и HLOOKUP пытается найти точное соответствие

lookup_value — это значение, которое будет найдено в первой строке таблицы

table_array — таблица данных, в которой ищутся данные

row_index_num — номер строки в table_array, из которого будет возвращено соответствующее значение

range_lookup — это логическое значение, которое указывает, хотите ли вы, чтобы HLOOKUP нашел точное или приблизительное совпадение

range_lookup может быть

опущено, и в этом случае предполагается, что оно TRUE, и HLOOKUP пытается найти приблизительное соответствие

ИСТИНА, и в этом случае HLOOKUP пытается найти приблизительное совпадение. Другими словами, если точное совпадение не найдено, возвращается следующее наибольшее значение, которое меньше lookup_value

ЛОЖЬ, и в этом случае HLOOKUP пытается найти точное совпадение

1, и в этом случае предполагается, что это ИСТИНА, а HLOOKUP пытается найти приблизительное совпадение

0, в этом случае предполагается, что это ЛОЖЬ, и HLOOKUP пытается найти точное соответствие

Примечание. Если range_lookup опущен или имеет значение TRUE или 1, HLOOKUP работает правильно, только если первый столбец в table_array отсортирован в порядке возрастания. В противном случае это может привести к неверным значениям. В таком случае используйте FALSE для range_lookup.

Использование функции HLOOKUP с range_lookup FALSE

Вы можете получить цену продукта по идентификатору продукта с помощью функции HLOOKUP, поскольку идентификатор продукта находится в первой строке. Цена находится в строке 3 и, следовательно, row_index_ num должно быть 3.

• Используйте функцию HLOOKUP с range_lookup как TRUE.
• Используйте функцию HLOOKUP с range_lookup как FALSE.

Правильный ответ из массива ProductRange — 171,65. Вы можете проверить результаты.

Вы замечаете, что, как и в случае с VLOOKUP, вы получили

• Правильный результат, когда range_lookup равен FALSE, и

• Неправильный результат, когда range_lookup равен TRUE.

Правильный результат, когда range_lookup равен FALSE, и

Неправильный результат, когда range_lookup равен TRUE.

Это связано с тем, что первая строка в массиве ProductRange не сортируется в порядке возрастания. Следовательно, не забывайте использовать FALSE всякий раз, когда данные не отсортированы.

Использование функции HLOOKUP с range_lookup TRUE

Рассмотрим пример оценок учеников, используемых в VLOOKUP. Предположим, у вас есть данные в строках, а не в столбцах, как показано в таблице, приведенной ниже —

table_array —

Назовите этот массив как GradesRange.

Обратите внимание, что метки первой строки, на основе которых получены оценки, сортируются в порядке возрастания. Следовательно, используя HLOOKUP с TRUE для аргумента range_lookup, вы можете получить оценки с приблизительным соответствием, и это то, что требуется.

Как вы можете наблюдать,

• row_index_num — указывает, что столбец возвращаемого значения в table_array равен 2

• range_lookup ИСТИНА

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

  • Вы также можете получить возвращаемое значение для приблизительных совпадений. т.е. HLOOKUP вычисляет следующим образом —

row_index_num — указывает, что столбец возвращаемого значения в table_array равен 2

range_lookup ИСТИНА

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

Вы также можете получить возвращаемое значение для приблизительных совпадений. т.е. HLOOKUP вычисляет следующим образом —

Метки	<35	> = 35 и <50	> = 50 и <60	> = 60 и <75	> = 75
Пройти категорию	Потерпеть поражение	Третий класс	Второй класс	Первый класс	Первый класс с отличием

Вы получите следующие результаты —

Использование функции INDEX

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

Рассмотрим следующие данные о продажах, в которых вы найдете продажи в каждом из северного, южного, восточного и западного регионов по продавцам, которые указаны в списке.

• Назовите массив как SalesData.

Используя функцию INDEX, вы можете найти —

• Продажи любого из Продавцов в определенном регионе.
• Общий объем продаж в регионе всеми продавцами.
• Общий объем продаж продавца во всех регионах.

Вы получите следующие результаты —

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

Вы можете сделать это с помощью функции MATCH, как описано в следующем разделе.

Использование функции MATCH

Если вам нужно положение элемента в диапазоне, вы можете использовать функцию MATCH. Вы можете комбинировать функции MATCH и INDEX следующим образом:

Вы получите следующие результаты —