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

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

  • Находите значения в диапазоне данных - VLOOKUP и HLOOKUP
  • Получить значение или ссылку на значение из таблицы или диапазона - ИНДЕКС
  • Получить относительное положение указанного элемента в диапазоне ячеек - MATCH

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

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

Синтаксис функции ВПР:

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 в первом столбце и

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

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

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

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

    • опущено, и в этом случае предполагается, что это ИСТИНА, и функция ВПР пытается найти приблизительное совпадение.

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

    • ЛОЖЬ, в этом случае функция ВПР пытается найти точное совпадение.

    • 1, в этом случае предполагается, что он ИСТИНА, и функция ВПР пытается найти приблизительное совпадение.

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

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

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

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

table_array -

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

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

Рекомендуется называть массивы таким образом, чтобы не запоминать диапазоны ячеек. Теперь вы готовы посмотреть оценку по списку ваших оценок:

Как видите,

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

  • то range_lookup правда

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

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

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

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

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

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

table_array -

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

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

  • Используйте функцию ВПР с range_lookup как ИСТИНА
  • Используйте функцию VLOOKUP с range_lookup как FALSE

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

Вы видите, что у вас есть -

  • Правильный результат, когда range_lookup имеет значение FALSE, и
  • Неверный результат, когда range_lookup ИСТИНА.

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

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

Вы можете использовать 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 возможно

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

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

    • FALSE, в этом случае HLOOKUP пытается найти точное совпадение.

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

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

Note- Если 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. Вы можете проверить результаты.

Вы видите, что, как и в случае с ВПР, у вас есть

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

  • Неверный результат, когда range_lookup ИСТИНА.

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

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

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

table_array -

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

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

Как видите,

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

  • то range_lookup правда

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

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

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

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

Использование функции ИНДЕКС

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

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

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

Используя функцию ИНДЕКС, вы можете найти -

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

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

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

Вы можете сделать это с помощью функции ПОИСКПОЗ, как описано в следующем разделе.

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

Если вам нужна позиция элемента в диапазоне, вы можете использовать функцию ПОИСКПОЗ. Вы можете комбинировать функции ПОИСКПОЗ и ИНДЕКС следующим образом:

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