01 од 03
Најди ги податоците лево
Преглед на формула за лево пребарување Excel
Функцијата VLOOKUP на Excel се користи за наоѓање и враќање на информации од табела со податоци врз основа на вредноста на изглед што ќе го одберете.
Нормално, VLOOKUP бара вредност за пребарување да биде во левата колона на табелата со податоци, а функцијата враќа друго поле на податоци лоцирано во истиот ред десно од оваа вредност.
Со комбинирање на VLOOKUP со функцијата CHOOSE ; сепак, може да се создаде лева формула за пребарување која ќе:
- дозволува да се избере вредноста за пребарување од која било колона во табелата со податоци
- информациите за враќање се наоѓаат во која било колона од лево од вредноста на пребарување
Пример: Користење на функциите VLOOKUP и CHOOSE во левата Формула за пребарување
Следните чекори ја креираат левата формула за формулација видена на сликата погоре.
Формулата
= VLOOKUP ($ D $ 2, ОДБИРАЈ ((1,2), $ F: $ F, $ D: $ D), 2, FALSE)овозможува да се најде дел обезбеден од различни компании наведени во колона 3 од табелата со податоци.
Работата на функцијата CHOOSE во формулата е да го измами VLOOKUP во верувајќи дека колоната 3 е всушност колона 1. Како резултат на тоа, името на компанијата може да се користи како вредност за пребарување за да го пронајде името на делот што го доставува секоја компанија.
Чекори за водење - Внесување на податоците од обуките
- Внесете ги следниве наслови во означените ќелии: D1 - Добавувачот Е1 - Дел
- Внесете ја табелата на податоци видени на сликата погоре во ќелиите D4 до F9
- Редовите 2 и 3 се оставени празно за да се приспособат на критериумите за пребарување и левата формула за формуларите креирани за време на ова упатство
Почнување на Формула за лево пребарување - Отворање на дијалогот VLOOKUP
Иако е можно само да ја напишете формулата погоре директно во ќелијата Ф1 на работниот лист, многу луѓе имаат потешкотии со синтаксата на формулата.
Алтернатива, во овој случај, е да се користи дијалогот VLOOKUP. Речиси сите функции на Excel имаат дијалог-кутија што ви овозможува да ги внесете сите аргументи на функцијата на посебна линија.
Чекори за чекори
- Кликнете на ќелијата E2 на работниот лист - локацијата каде што ќе бидат прикажани резултатите од левата формула за пребарување
- Кликнете на јазичето Формула на лентата
- Кликнете на опцијата Пронајди & референца во лентата за да ја отворите паѓачката листа на функции
- Кликнете на VLOOKUP во листата за да го прикажете дијалогот за функцијата
02 од 03
Внесување на аргументи во дијалогот VLOOKUP - Кликни за да ја видите поголема слика
Аргументи на ВЛООКУП
Аргументите на функцијата се вредностите што ги користи функцијата за пресметување на резултатот.
Во дијалогот за функција, името на секој аргумент се наоѓа на одделна линија проследена со поле во кое ќе внесете вредност.
Внесете ги следните вредности за секој од аргументите VLOOKUP на правилната линија на дијалогот како што е прикажано на сликата погоре.
Вредноста на пребарување
Вредноста на пребарување е полето на информации што се користи за пребарување на низата на табели. VLOOKUP враќа друго поле на податоци од истиот ред како вредност за пребарување.
Овој пример користи референца од ќелијата на локацијата каде што името на компанијата ќе биде внесено во работниот лист. Предноста на ова е тоа што го олеснува менувањето на името на компанијата без уредување на формулата.
Чекори за чекори
- Кликнете на линијата lookup_value во полето за дијалог
- Кликнете на ќелијата D2 за да ја додадете оваа референца на ќелијата во линијата lookup_value
- Притиснете го копчето F4 на тастатурата за да направите апсолутна референца на ќелијата - $ D $ 2
Забелешка: Апсолутните референци на ќелиите се користат за вредностите за пребарување и аргументите на низа на табели за да се спречат грешките ако формулата за пребарување се копира во други ќелии на работниот лист.
Табелата Array: Внесување на функцијата CHOOSE
Аргументот на табелата на маса е блок на соседни податоци од кои се вчитуваат специфични информации.
Вообичаено, VLOOKUP само изгледа десно од аргументот за lookup value за да се најдат податоци во низата на табели. За да го видите лево, VLOOKUP мора да биде измамен со преуредување на колоните во табелата со помош на функцијата CHOOSE.
Во оваа формула, функцијата CHOOSE остварува две задачи:
- тоа создава низа на табели што е само две колони ширум - колони D и F
- го менува правото на лево цел на колоните во низата на табели, така што колоната F е прва, а колоната D е втора
Детали за тоа како функцијата CHOOSE ги извршува овие задачи може да се најде на страница 3 од упатството .
Чекори за чекори
Забелешка: Кога рачно внесувате функции, секој од аргументите на функцијата мора да биде одделен со запирка "," .
- Во дијалогот за функција VLOOKUP, кликнете на линијата Table_array
- Внесете ја следната функција CHOOSE
- Изберете ({1,2}, $ F: $ F, $ D: $ D)
Број на индекс на колоната
Нормално, бројот на индексот на колоната покажува која колона од низата на табели ги содржи податоците што ги следите. Во оваа формула; сепак, се однесува на редоследот на колоните поставени од функцијата CHOOSE.
Функцијата CHOOSE создава низа на табели што се шири две колони со колона F, прва проследена со колона D. Бидејќи бараната информација - името на делот - е во колона D, вредноста на аргументот за индексот на колоната мора да биде поставена на 2.
Чекори за чекори
- Кликнете на линијата Col_index_num во дијалогот
- Напишете 2 во оваа линија
Пронајдете опсег
Аргументот VLOOKUP Range_lookup е логичка вредност (TRUE или FALSE само) што покажува дали сакате VLOOKUP да пронајде точна или приближна согласност со вредноста на пребарување.
- Ако TRUE или ако овој аргумент е испуштен, VLOOKUP враќа или точно совпаѓање на Lookup_value, или ако не е пронајден точен натпревар, VLOOKUP ја враќа следната најголема вредност. За формулата за да го направите ова, податоците во првата колона од Table_array мора да бидат подредени по растечки редослед .
- Ако FALSE, VLOOKUP ќе користи точно пребарување со Lookup_value. Ако има две или повеќе вредности во првата колона од Table_array кои се совпаѓаат со вредноста на пребарување, се користи првата пронајдена вредност. Ако точно не се пронајде точно, се враќа # N / A грешка.
Во ова упатство, бидејќи ние бараме одредено име на дел, Range_lookup ќе биде поставен на Неточно, така што формулата ќе се врати само точни совпаѓања.
Чекори за чекори
- Кликнете на линијата Range_lookup во дијалогот
- Напишете го зборот False во оваа линија за да покажеме дека сакаме VLOOKUP да врати точно пребарување за податоците што ги бараме
- Кликнете Во ред за да ја завршите левата формула за пребарување и затворете го дијалогот
- Бидејќи сè уште не сме влегле во името на компанијата во ќелијата D2, # N / A грешка треба да биде присутна во ќелијата Е2
03 од 03
Тестирање на левата формула за пребарување
Враќање на податоци со формула за лево пребарување
За да најдете кои компании ги снабдуваат кои делови, внесете име на компанија во ќелијата D2 и притиснете го копчето ENTER на тастатурата.
Името на дел ќе биде прикажано во ќелијата Е2.
Чекори за чекори
- Кликнете на ќелијата D2 во вашиот работен лист
- Напишете Gadgets Plus во ќелијата D2 и притиснете го копчето ENTER на тастатурата
- Текстот Gadgets - делот обезбеден од компанијата Gadgets Plus - треба да биде прикажан во ќелијата E2
- Проверете ја формулата за пребарување уште со внесување на други имиња на компании во ќелијата D2 и соодветното име на дел треба да се појави во ќелијата E2
Пораки за грешки VLOOKUP
Ако се појави порака за грешка како што е # N / A во ќелијата E2, прво проверете дали има грешки во правописот во ќелијата D2.
Ако правописот не е проблем, оваа листа на пораки за грешки VLOOKUP може да ви помогне да одредите каде лежи проблемот.
Прекинување на работата на ИЗБОР
Како што рековме, во оваа формула, функцијата CHOOSE има две задачи:
- тоа создава низа на табели што е само две колони ширум - колони D и F
- го менува правото на лево цел на колоните во низата на табели, така што колоната F е прва, а колоната D е втора
Креирање две низи за маса на табели
Синтаксата за функцијата CHOOSE е:
= ИЗБОР (index_number, value1, value2, ... Value254)
Функцијата CHOOSE нормално враќа една вредност од листата на вредности (Value1 to Value254) врз основа на внесениот индекс.
Ако бројот на индексот е 1, функцијата враќа Value1 од листата; ако бројот на индексот е 2, функцијата враќа Value2 од списокот и така натаму.
Со внесување на повеќе индексни броеви; сепак, функцијата ќе враќа повеќе вредности во која било посакувана вредност. Прв избор за враќање на повеќе вредности се прави со создавање на низа .
Внесувањето на низа се остварува со опкружување на броевите внесени со кадрици или загради. За индексниот број се внесуваат два броја: {1,2} .
Треба да се напомене дека ИЗБОР не е ограничен на создавање на табела со две колони. Со вклучување на дополнителен број во низата - како што е {1,2,3} - и дополнителен опсег во аргументот за вредност, може да се креира табела со три колони.
Дополнителните колони ќе ви овозможат да вратите различни информации со левата формула за пребарување едноставно со менување на VLOOKUP-от број на индекс за колона до бројот на колоната што ги содржи саканите информации.
Промена на редоследот на колоните со функцијата CHOOSE
Во функцијата CHOOSE употребена во оваа формула: ИЗБОР ({1,2}, $ F: $ F, $ D: $ D) , опсегот за колона F е наведен пред колона D.
Бидејќи функцијата CHOOSE поставува табела на VLOOKUP - извор на податоци за таа функција - префрлањето на редоследот на колоните во функцијата CHOOSE се пренесува заедно со VLOOKUP.
Сега, колку што е VLOOKUP, масата на табелата е само две колони ширум со колона F на левата и колона D од десната страна. Бидејќи во колоната F се наоѓа името на компанијата што сакаме да ја бараме, а бидејќи во колоната D се содржани имињата на делови, VLOOKUP ќе може да ги извршува своите нормални задачи за пребарување во наоѓање на податоци што се наоѓаат лево од вредноста на пребарување.
Како резултат на тоа, VLOOKUP може да го користи името на компанијата за да го пронајде делот што го обезбедува.