Пошук значень за допомогою функцій VLOOKUP, INDEX або MATCH

Примітка.:  Ми хочемо надавати найновіший вміст довідки рідною мовою користувачів якомога швидше. Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була інформація корисною, унизу цієї сторінки. Для зручності цю статтю можна переглянути англійською мовою .

Припустімо, що вас списку чисел розташування офісу та відомості, які співробітники є в кожному office. В електронній таблиці – це величезну, щоб ви могли подумати складне завдання. Це насправді досить легко виконувати з функція lookup.

Функція VLOOKUP і HLOOKUP функцій, разом із INDEX і MATCH,наведено деякі найкорисніших функцій у програмі Excel.

Примітка.: Функція майстер підстановок більше не доступні в програмі Excel.

Ось приклад того, як використовувати функцію VLOOKUP.

=VLOOKUP(B2;C2:E7;3;TRUE)

У цьому прикладі B2 – це перший аргумент– елемент дані функції для роботи. Для функції VLOOKUP це перший аргумент – це значення, яке потрібно знайти. Цей аргумент може бути посиланням на клітинку або фіксовані значення, наприклад «Мавка» або 21 000 разів. Другий аргумент – це діапазон клітинок, C2-:E7, у яких для пошуку значення, яке потрібно знайти. Третій аргумент – це стовпець в цьому діапазоні клітинок, що містить значення, яке ви шукаєте.

Четвертого аргументу не є обов'язковим. Введіть значення TRUE або FALSE. Якщо ввести значення TRUE, або заповнюйте як аргумент, функція повертає приблизний значення, указане в першому аргументі. Якщо ви ввели ХИБНІСТЬ, функція збігатимуться значення за перший аргумент. Іншими словами, залишаючи четвертого аргументу пусті, або ввести значення TRUE, дає вам більшої гнучкості.

У цьому прикладі показано, як працює функція. Під час введення значення у клітинці B2 (перший аргумент), функція VLOOKUP шукає клітинки в діапазоні C2:E7 (другий аргумент) і повертає найближчої приблизного збігу з третього стовпця в діапазоні, стовпець E (аргумент «3»).

Типове використання функції VLOOKUP

Четвертого аргументу пусте, тож, функція повертає приблизний. Якщо це не, вам доведеться введіть значення у стовпці C "або" D ", щоб отримати результат на всіх.

Якщо ви вмієте VLOOKUP, функція HLOOKUP – це так само легко використовувати. Введення тих самих аргументів, але вона виконує пошук у рядках замість стовпців. "

Спробуйте

Якщо ви хочете поекспериментувати функції підстановки перед їх застосуванням до власних даних, ось деякі зразки даних. Excel для деяких користувачів, як за допомогою функції VLOOKUP і HLOOKUP; Інші користувачі також за допомогою функції INDEX і MATCH разом. Спробуйте кожен метод та побачити ті, які вам найбільше до вподоби.

ПРАПОРЕЦЬ ' ' на роботі

Скопіюйте такі дані на пусту таблицю.

Порада:    Перед вставленням дані в Excel, встановити ширину стовпців для стовпцях від A до C до 250 пікселів і натисніть кнопку Обтікання текстом (вкладкаосновне , Група « вирівнювання »).

Густина

В'язкість

Температура

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Формула

Опис

Результат

=VLOOKUP(1,A2:C10,2)

Використовуючи приблизний збіг, шукає значення 1 у стовпці A, знаходить найбільше значення, менше або рівне 1, у стовпці A (яке дорівнює 0,946), а потім повертає значення зі стовпця B того самого рядка.

2,17

=VLOOKUP(1;A2:C10;3;ІСТИНА)

Використовуючи приблизний збіг, шукає значення 1 у стовпці A, знаходить найбільше значення, менше або рівне 1, у стовпці A (яке дорівнює 0,946), а потім повертає значення зі стовпця С того самого рядка.

100

=VLOOKUP(0,7;A2:C10;3;ХИБНІСТЬ)

Використовуючи точний збіг, шукає значення 0,7 у стовпці A. Оскільки у стовпці А відсутній точний збіг, результат повертає помилку.

#N/A

=VLOOKUP(0.1,A2:C10,2,ІСТИНА)

Використовуючи приблизний збіг, шукає значення 0,1 у стовпці A. Оскільки 0,1 менше за найменше значення у стовпці А, результат повертає помилку.

#N/A

=VLOOKUP(2,A2:C10,2,ІСТИНА)

Використовуючи приблизний збіг, шукає значення 2 у стовпці A, знаходить найбільше значення, менше або рівне 2, у стовпці A (яке дорівнює 1,29), а потім повертає значення зі стовпця B того самого рядка.

1,71

Приклад HLOOKUP

Скопіюйте всі клітинки в цій таблиці та вставте їх у клітинку A1 на пустому аркуші Excel.

Порада:    Перед вставленням дані в Excel, встановити ширину стовпців для стовпцях від A до C до 250 пікселів і натисніть кнопку Обтікання текстом (вкладкаосновне , Група « вирівнювання »).

Осі

Опори

Гвинти

4

4

9

5

7

10

6

8

11

Формула

Опис

Результат

=HLOOKUP("Осі";A1:C4;2;TRUE)

Шукає слово «Осі» в рядку 1 і повертає значення з рядка 2, розташованого в тому самому стовпці (стовпець A).

4

=HLOOKUP("Опори";A1:C4;3;FALSE)

Шукає слово «Опори» в рядку 1 і повертає значення з рядка 3, розташованого в тому самому стовпці (стовпець B).

7

=HLOOKUP("B";A1:C4;3;TRUE)

Шукає літеру «В» в рядку 1 і повертає значення з рядка 3, розташованого в тому самому стовпці. Оскільки точний збіг для літери «В» не знайдено, використовується найбільше значення в рядку 1, яке менше «В»: «Осі» у стовпці A.

5

=HLOOKUP("Гвинти";A1:C4;4)

Шукає слово «Гвинти» в рядку 1 і повертає значення з рядка 4, розташованого в тому самому стовпці (стовпець С).

11

=HLOOKUP(3;{1,2,3;"a","b","c";"d","e","f"};2;TRUE)

Шукає число 3 в константі-масиві із трьох рядків і повертає значення з рядка 2, розташованого в тому самому стовпці (у цьому випадку, третьому). У константі-масиві три рядки значень, кожен із рядків відокремлено крапкою з комою (;). Оскільки символ «с» знайдено в рядку 2 та в тому самому стовпці що й 3, символ «c» повертається.

c

Функції INDEX і MATCH приклади

У цьому прикладі останнього використовує функції INDEX і MATCH разом, щоб повернути найраніше номер рахунку-фактури та його відповідних датою для кожного п'ять міст. Оскільки дата повертається числом, ми використання функції TEXT для відформатуйте його як дату. Функція INDEX фактично використовує результат функції MATCH як аргумент. Поєднання функцій INDEX і MATCH використовуються два рази в кожній формулі – по-перше, щоб повернути номер рахунку-фактури а потім, щоб повернутися до дати.

Скопіюйте всі клітинки в цій таблиці та вставте їх у клітинку A1 на пустому аркуші Excel.

Порада:    Перед вставленням дані в Excel, встановити ширину стовпців для стовпцях A-D до 250 пікселів і натисніть кнопку Обтікання текстом (вкладкаосновне , Група « вирівнювання »).

Рахунок

Місто

Дата виставлення рахунка

Перший рахунок за містом із датою

3115

Чернігів

7/4/12

="Чернігів = "&INDEX($A$2:$C$33,MATCH("Чернігів",$B$2:$B$33,0),1)& ", Дата виписки рахунка: " & TEXT(INDEX($A$2:$C$33,MATCH("Чернігів",$B$2:$B$33,0),3),"m/d/yy")

3137

Чернігів

9/4/12

="Алушта = "&INDEX($A$2:$C$33,MATCH("Алушта",$B$2:$B$33,0),1)& ", Дата виписки рахунка: " & TEXT(INDEX($A$2:$C$33,MATCH("Алушта",$B$2:$B$33,0),3),"m/d/yy")

3154

Чернігів

11/4/12

="Донецьк = "&INDEX($A$2:$C$33,MATCH("Донецьк",$B$2:$B$33,0),1)& ", Дата виписки рахунка: " & TEXT(INDEX($A$2:$C$33,MATCH("Донецьк",$B$2:$B$33,0),3),"m/d/yy")

3191

Чернігів

21/4/12

="Нововолинськ = "&INDEX($A$2:$C$33,MATCH("Нововолинськ",$B$2:$B$33,0),1)& ", Дата виписки рахунка: " & TEXT(INDEX($A$2:$C$33,MATCH("Нововолинськ",$B$2:$B$33,0),3),"m/d/yy")

3293

Чернігів

25/4/12

="Суми = "&INDEX($A$2:$C$33,MATCH("Суми",$B$2:$B$33,0),1)& ", Дата виписки рахунка: " & TEXT(INDEX($A$2:$C$33,MATCH("Суми",$B$2:$B$33,0),3),"m/d/yy")

3331

Чернігів

27/4/12

3350

Чернігів

28/4/12

3390

Чернігів

1/5/12

3441

Чернігів

2/5/12

3517

Чернігів

8/5/12

3124

Алушта

9/4/12

3155

Алушта

11/4/12

3177

Алушта

19/4/12

3357

Алушта

28/4/12

3492

Алушта

6/5/12

3316

Донецьк

25/4/12

3346

Донецьк

28/4/12

3372

Донецьк

1/5/12

3414

Донецьк

1/5/12

3451

Донецьк

2/5/12

3467

Донецьк

2/5/12

3474

Донецьк

4/5/12

3490

Донецьк

5/5/12

3503

Донецьк

8/5/12

3151

Нововолинськ

9/4/12

3438

Нововолинськ

2/5/12

3471

Нововолинськ

4/5/12

3160

Суми

18/4/12

3328

Суми

26/4/12

3368

Суми

29/4/12

3420

Суми

1/5/12

3501

Суми

6/5/12

Додаткові відомості про функції підстановки

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×