Як використовувати VLOOKUP для діапазону значень

| | 0 Comments| 8:00 AM
Categories:

VLOOKUP — одна з найвідоміших функцій Excel. Зазвичай ви використовуєте його для пошуку точних збігів, наприклад ідентифікаторів продуктів або клієнтів, але в цій статті ми розглянемо, як використовувати VLOOKUP з діапазоном значень.

Приклад перший: використання VLOOKUP для присвоєння буквених оцінок результатам іспитів

Наприклад, скажімо, що у нас є список результатів іспитів, і ми хочемо призначити оцінку кожному балу. У нашій таблиці стовпець A показує фактичні результати іспитів, а стовпець B використовуватиметься для відображення буквених оцінок, які ми обчислюємо. Ми також створили таблицю праворуч (стовпці D і E), яка показує оцінку, необхідну для досягнення кожної літерної оцінки.

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

Формула VLOOKUP

Перш ніж ми приступимо до застосування формули до нашого прикладу, давайте коротко нагадаємо синтаксис VLOOKUP:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

У цій формулі змінні працюють так:

  Як ділити числа в Google Таблицях

lookup_value: це значення, яке ви шукаєте. Для нас це оцінка в стовпці A, починаючи з клітинки A2.
table_array: це часто неофіційно називають таблицею пошуку. Для нас це таблиця, що містить бали та відповідні оцінки (діапазон D2:E7).
col_index_num: це номер стовпця, де будуть розміщені результати. У нашому прикладі це стовпець B, але оскільки команда VLOOKUP вимагає числа, це стовпець 2.
range_lookup> Це питання з логічним значенням, тому відповідь є істинною або хибною. Ви виконуєте пошук діапазону? Для нас відповідь — так (або «TRUE» у термінах VLOOKUP).

Заповнена формула для нашого прикладу показана нижче:

=VLOOKUP(A2,$D$2:$E$7,2,TRUE)

Виправлено масив таблиці, щоб він не змінювався, коли формула копіюється в клітинки стовпця B.

  Як отримувати сповіщення про час сну на основі того, коли вам потрібно прокинутися

Треба бути обережними

При перегляді діапазонів за допомогою VLOOKUP важливо, щоб перший стовпець масиву таблиці (стовпець D у цьому сценарії) був відсортований у порядку зростання. Формула спирається на цей порядок, щоб розмістити значення пошуку в правильному діапазоні.

Нижче наведено зображення результатів, які ми отримали б, якби відсортували масив таблиці за літерою оцінки, а не за оцінкою.

Важливо чітко розуміти, що порядок важливий лише для пошуку діапазону. Коли ви ставите False в кінці функції VLOOKUP, порядок не так важливий.

Приклад другий: надання знижки на основі того, скільки витрачає клієнт

У цьому прикладі ми маємо деякі дані про продажі. Ми хотіли б надати знижку на суму продажу, і відсоток цієї знижки залежить від витраченої суми.

  Як оновити Safari на iPhone та Mac

Довідкова таблиця (стовпці D і E) містить знижки для кожної групи витрат.

Формулу VLOOKUP, наведену нижче, можна використовувати, щоб повернути правильну знижку з таблиці.

=VLOOKUP(A2,$D$2:$E$7,2,TRUE)

Цей приклад цікавий, оскільки ми можемо використовувати його у формулі для віднімання знижки.

Ви часто побачите, як користувачі Excel пишуть складні формули для цього типу умовної логіки, але цей ВПР надає стислий спосіб досягнення цього.

Нижче до формули додається VLOOKUP, щоб відняти знижку, повернену від суми продажу в стовпці A.

=A2-A2*VLOOKUP(A2,$D$2:$E$7,2,TRUE)

VLOOKUP корисний не тільки для пошуку конкретних записів, таких як співробітники та продукти. Він більш універсальний, ніж багато хто знає, і його повернення з діапазону значень є прикладом цього. Ви також можете використовувати його як альтернативу складним формулам.