Анализ данных 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
Если вам нужна позиция элемента в диапазоне, вы можете использовать функцию ПОИСКПОЗ. Вы можете комбинировать функции ПОИСКПОЗ и ИНДЕКС следующим образом:
Вы получите следующие результаты -
