Как да използвам VLOOKUP в Google Sheets

Как да

ОтРайън Дюбе

Последна актуализация на12 октомври 2020 г.

Ако някога използва функцията VLOOKUP в Excel, тогава знаете колко мощна е функцията. Ако не сте сигурни какво прави VLOOKUP в Google Sheets, той работи по същия начин, както в Excel.

Функцията VLOOKUP ви позволява да търсите в най-лявата колона на диапазон, за да върнете стойност от която и да е друга колона в същия диапазон.

В тази статия ще научите повече за това как VLOOKUP работи в Google Sheets и как да го използвате с няколко полезни примера.

Какво представлява VLOOKUP в Google Sheets?

Помислете за VLOOKUP в Google Sheets като за много просто търсене в базата данни. Когато искате информация от база данни, трябва да потърсите конкретна таблица за стойност от една колона. Независимо от реда, в който търсенето намира съвпадение в тази колона, след това можете да търсите стойност от която и да е друга колона в този ред (или запис, в случай на база данни).

Той работи по същия начин в Google Sheets. Функцията VLOOKUP има четири параметъра, като един от тях не е задължителен. Тези параметри са както следва:

• ключ за търсене: Това е конкретната стойност, която търсите. Това може да бъде низ или число.
• обхват: Всеки диапазон от колони и клетки, които искате да включите в търсенето.
• индекс: Това е номерът на колоната (в рамките на избрания диапазон), където искате да получите върнатата стойност.
• е_сортирано (по избор): Ако е зададено на TRUE, казвате на функцията VLOOKUP, че първата колона е сортирана.

Има няколко важни неща, които трябва да имате предвид за тези параметри.

Първо, можете да изберете диапазона като въвеждане на функцията VLOOKUP, когато стигнете до параметъра „range“. Това улеснява, тъй като няма да се налага да помните правилния синтаксис, за да дефинирате диапазон.

Второ, „индексът“ трябва да бъде между 1 и максималния брой колони в избрания от вас диапазон. Ако въведете число, по-голямо от броя на колоните в диапазона, ще получите грешка.

Използване на VLOOKUP в Google Sheets

Сега, след като разбрахте как работи VLOOKUP, нека разгледаме няколко примера.

Пример 1: Обикновено търсене на информация

Да предположим, че имате списък със служители и свързаната с тях лична информация.

След това може би имате Google Sheet със записани продажби на служители. Тъй като изчислявате комисионните им въз основа на началната им дата, ще ви трябва VLOOKUP, за да вземете това от полето „Начална дата“.

За да направите това, в първото поле „Старшинство“ ще започнете да пишете функцията VLOOKUP, като напишете „= VLOOKUP (“.

Първото нещо, което ще забележите, е да се появи прозорец за помощ. Ако не стане, натиснете синьото „?“ вляво от клетката.

Този помощен прозорец ще ви каже какъв параметър трябва да въведете след това. Първият параметър е ключът_за търсене, така че просто трябва да изберете името на служителя в колона А. Това автоматично ще попълни функцията с правилния синтаксис за тази клетка.

Помощният прозорец ще изчезне, но когато въведете следващата запетая, той ще се появи отново.

Както можете да видите, това ви показва, че следващият параметър, който трябва да попълните, е диапазонът, който искате да търсите. Това ще бъде диапазонът за търсене на данни за служителите на другия лист.

Затова изберете раздела, в който се съхраняват данните за служителите, и маркирайте целия диапазон с данни за служителите. Уверете се, че полето, което искате да търсите, е най-лявата избрана колона. В този случай това е „Име“.

Ще забележите малкото поле с функцията VLOOKUP и параметрите ще се носят над този лист, докато избирате диапазона. Това ви позволява да видите как диапазонът се въвежда във функцията, докато я избирате.

След като приключите, просто напишете друга запетая, за да преминете към следващия параметър VLOOKUP. Може да се наложи да изберете оригиналния раздел, в който сте били, за да се върнете и към листа с резултати.

Следващият параметър е индекс. Знаем, че началната дата за служителя е третата колона в избрания диапазон, така че можете просто да пишете 3 за този параметър.

Въведете „FALSE“ за е_сортирано параметър, тъй като първата колона не е сортирана. Накрая въведете затварящата скоба и натиснете Въведете.

Сега ще видите, че полето е попълнено с правилната дата на стартиране за този служител.

Попълнете останалите полета под него и готово!

Пример 2: Извличане на данни от референтна таблица

В този следващ пример ще създадем референтна таблица на оценките на буквите, за да извлечем правилната оценка на буквите за числовата оценка на ученика. За да настроите това, просто трябва да сте сигурни, че някъде е създадена референтна таблица за всички класове букви.

За да потърсите правилния клас букви в клетка C2, просто изберете клетката и напишете: „= VLOOKUP (B2, $ E $ 1: $ F $ 6,2, TRUE)“

Ето обяснение какво означават тези параметри.

• B2: Препраща се към числовата оценка на теста, за да се търси
• $ E $ 1: $ F $ 6: Това е таблицата с буквени класове, със символи за долари, за да не се променя диапазонът дори когато попълвате останалата част от колоната
• 2: Това препраща към втората колона на справочната таблица - Letter Grade
• ВЯРНО: Казва на функцията VLOOKUP, че резултатите в таблицата за търсене са сортирани

Просто попълнете останалата част от колона В и ще видите, че се прилагат правилните оценки на буквите.

Както можете да видите, начинът, по който това работи със сортирани диапазони, е функцията VLOOKUP грабва резултата за по-малкия край на сортирания диапазон. Така че всичко от 60 до 79 връща D, 80 до 89 връща C и т.н.

Пример 3: Двупосочно търсене на VLOOKUP

Последен пример е използването на функцията VLOOKUP с вложена функция MATCH. Случаят на употреба за това е, когато искате да търсите в таблица по различни колони или редове.

Да приемем например, че имате същата таблица на служителите като първия пример по-горе. Бихте искали да създадете нов лист, където можете просто да въведете името на служителя и каква информация искате да извлечете за тях. След това третата клетка ще върне тази информация. Звучи готино, нали?

Можете да създадете тази справочна таблица на същия или друг лист. От теб зависи. Просто създайте един ред за най-лявата стойност за търсене на колона (изборът на реда). Създайте друг ред за полето, което искате да търсите за резултата. Тя трябва да изглежда така.

Сега изберете празното поле Резултат и въведете „= VLOOKUP (I2, A1: F31, MATCH (I3, A1: F1,0), FALSE)“ и натиснете Въведете.

Преди да разгледаме резултатите, нека разбием как работят параметрите в тази функция VLOOKUP.

• I2: Това е името, което сте въвели в полето за търсене на име, което VLOOKUP ще се опита да съчетае с име в най-лявата колона на диапазона.
• A1: F31: Това е цялата гама от имена, включително цялата свързана информация.
• МАТЧ (I3, A1: F1,0): Функцията за съвпадение ще използва полето за търсене, което сте въвели, ще го намери в диапазона на заглавката и ще върне номера на колоната. След това този номер на колона се предава на параметъра за индекс на функцията VLOOKUP.
• НЕВЯРНО: Редът на данните в лявата колона не е сортиран.

Сега, когато разбирате как работи, нека да разгледаме резултатите.

Както можете да видите, като въведете името и полето за връщане (Email), можете да потърсите всяка информация, която ви харесва.

Можете също да използвате този двустранен подход за търсене, за да търсите всяка таблица както по ред, така и по колона. Това е едно от най-полезните приложения на функцията VLOOKUP.

Използване на VLOOKUP в Google Sheets

Добавянето на функцията VLOOKUP към Google Sheets беше едно от най-добрите неща, които Google можеше да направи. Той подобрява полезността на вашите електронни таблици и ви позволява да търсите и дори да обединявате множество листове.

Ако имате някакви проблеми с функцията, много от съвети за отстраняване на неизправности, които работят с VLOOKUP в Excel ще работи за него и в Google Sheets.