Днес ние започваме поредица от статии, описващи един от най-полезните функции на Excel - CDF (VLOOKUP). Тази функция, в същото време, един от най-сложните и малко разбрани.
В този урок ще се опитам за CDF представи основите на най-прост език, за да направи процеса на обучение за неопитни потребители възможно най-ясно. В допълнение, ние ще разгледаме няколко примера с Excel формули, които демонстрират най-разпространените начини на използване на функцията за CDF.
VLOOKUP да Excel - общо описание и синтаксис
Така че, това, което е CDF. Ами, на първо място, това е функция на Excel. Какво прави тя? Тя търси даден ви стойност и връща съответната стойност в друга колона. От техническа гледна точка, за стойността CDF в първата колона на предварително определения диапазон от прегледа, и връща резултата в друга графа, в един и същи ред.
В обичайния заявлението, VLOOKUP справките в базата данни, дадени уникален идентификатор и извлича от базата данни на някаква информация, свързана с него.
Първата буква в името на функцията CDF (VLOOKUP) във вертикална, средства (V ertical). На него можете да се разграничат от CDF PGR (HLOOKUP), който търси стойност в най-горния ред на границите - D Хоризонтална (H ОРИЗОНТАЛНИ).
Синтаксис VLOOKUP
CDF функция (VLOOKUP) тук има следния синтаксис:
Както можете да видите, функцията CDF в Microsoft Excel има четири параметъра (или аргумент). Първите три - не са необходими, последното - ако е необходимо.
- търсена_стойност (търсена) - стойност, която трябва да се търси.
Ако стойността е по-малка от най-малката стойност в първата колона на обхвата на гледане, функцията CDF ще върне грешка # N / A (# Н / W).
- table_array (маса) - две или повече колони с данни.
Не забравяйте функция CDF винаги търси стойността в първата колона на границите, посочени в аргумент table_array на (маса). В обхвата на сканиране може да бъде различни данни, например, текст, дата, номер, логическа стойност. Не се прави разлика чувствителна функция, това е, горните и малки букви се считат за едни и същи.
Така че, нашата формула ще търси стойността на 40 в клетки А2 до А15. защото А - е първата колона гама А2: B15, посочено в аргумент table_array на (маса):
- номер колона в предварително определени граници, от който ще бъдат върнати на стойността в съвпадащи низ - col_index_num (колона_номер).
В лявата колона в предварително определен диапазон - втора колона 1. - 2. Този трети колона - е 3, и така нататък. Сега можете да прочетете цялата формула:
Формула изглежда на стойността 40 в А2 диапазон: A15 и връща съответната стойност в колона Б (защото Б - е втората колона в А2 диапазон: В15).
Този параметър е задължително, но е много важно. По-късно в този урок на CDF, аз ще ви покажа няколко примера, за да обясни как да се подготви формулата, за да намерите най-точните и приблизителни мачове.
Примери функционират CDF
Надявам се, VLOOKUP стана малко по-ясни за вас. Сега нека да разгледаме някои примери за CDF в формули с реални данни.
Как да се използва CDF, извършвате търсене от друг лист Excel
На практика, формулата с функцията на CDF рядко се използват, за да търсят данни за същия лист. Най-често, вие ще намерите и изтеглите съответните стойности от друг лист.
За използване на CDF. търсене на друг лист на Microsoft Excel, трябва да сте в спор table_array на (маса), за да укажете името на лист с удивителен знак, а след това група от клетки. Така например, по следната формула показва, че А2 диапазон: B15 е на листа име Лист2.
Разбира се, името на листа, не е задължително да се въведе ръчно. Просто започнете да въвеждате формула, а когато става въпрос за table_array аргумент (виж таблицата), преминете към желаната страница и плъзнете мишката желания диапазон от клетки.
Формулата е показано на снимката по-долу, търси текст "Продукт 1" в колона А (това е първият обхвата колона А2: B9) на цените лист.
= VLOOKUP ( "Продукт 1", Цени $ A $ 2: $ B $ 9,2, FALSE)
= CDF ( "Продукт 1"; Цени $ A $ 2: $ B $ 9, 2, FALSE)
Моля, не забравяйте, че при търсене на текстови стойности, които трябва да се загради в кавички ( ""), както обикновено се прави във формули в Excel.
За table_array аргумент (таблицата), е желателно да се използва винаги абсолютни връзки (с $ знак). В този случай, обхватът на търсене да остане непроменена, когато формулата се копира в други клетки.
Търсене в друга работна книга с помощта на CDF
За да VLOOKUP работил между две работни книги на Excel, трябва да посочите името на работната книга в скоби пред името на листа.
Например, формула, показана по-долу, който изглежда на стойността 40 в листа в регистър Лист2 Numbers.xlsx:
Ето един лесен начин да се създаде формула Excel с CDF. който се отнася към друга работна книга:
- Отворете двете книги. Не е необходимо, но това е по-лесно да се създаде формула. Вие не искате да въведете името на работната книга ръчно? В допълнение, тя ви предпазва от случайни правописни грешки.
- Започнете да пишете на функцията на CDF. но когато става въпрос за аргументи table_array (виж таблицата), преминете към друга работна книга и го изберете правилната гама търсене.
Снимката е показано по-долу, може да се види с формулата в която, за обхвата на търсене помещава в работна книга лист PriceList.xlsx върху цените.
CDF ще работи, дори когато я затворите, което се търси, и лентата с формула ще бъде пълния път до файла работна книга, както е показано по-долу:
Ако името на работната книга или работен лист съдържа интервали, приложете го в кавички:
Как да използвате наименуван диапазон или таблица във формули с CDF
Ако имате намерение да използвате набор от функции за търсене в рамките на CDF. можете да създадете наименуван диапазон, и въведете името му във формулата като аргумент table_array (виж таблицата).
За да създадете наименуван диапазон, просто изберете клетката и въведете подходящо име в полето Име. в ляво на лентата за формули.
Сега можете да пишете тук формула за цената на стоките Търсене на продукти 1:
= VLOOKUP ( "Продукт 1", Продукти, 2)
PPS = ( "Продукт 1"; продукти; 2)Повечето от имената работим диапазони за цялата работна книга на Excel, така че не е необходимо да се посочва името на листа за аргумент table_array (виж таблицата), дори и ако формулата и границите за търсене са на различни страници от книгата. Ако те са в различни книги, трябва да укажете името на работната книга, преди името на диапазона, например, по следния начин:
= VLOOKUP ( "Продукт 1", PriceList.xlsx! Продукти, 2)
= VLOOKUP ( "Продукт 1"; PriceList.xlsx продукти ;! 2)Ако конвертирате диапазон от клетки в пълен Excel електронна таблица, с помощта на командата на маса (таблица) раздел Insert (Insert), след разпределението на обхвата на мишката, Microsoft Excel автоматично добавя името на формула колона (или името на таблицата, ако изберете цялата таблица).
Приготвено формула ще изглежда така:
= VLOOKUP ( "Продукт 1", Table46 [[Продукт]: [цена]], 2)
PPS = ( "Продукт 1"; Table46 [[Продукт]: [цена]]; 2)Или дори по следния начин:
= VLOOKUP ( "Продукт 1", Table46,2)
PPS = ( "Продукт 1"; Table46 2)При използване на наименувани диапазони, връзки ще доведат до една и съща клетка, без значение къде сте копирате функция CDF в рамките на работната книга.
Използване на заместващи символи във формули с CDF
Както и в много други функции в CDF, можете да използвате следните заместващи символи:
- Въпросителната (?) - замества всеки отделен знак.
- Звездичката (*) - замества на последователност от знаци.
Използване на заместващи символи във функциите Фондацията гледат може да бъде полезна в много случаи, например:
- Когато не си спомням точно текста, който искате да намерите.
- Когато искате да намерите дума, която е част от съдържанието на клетката. Знайте, че ВПР търси чрез съдържанието на клетката изцяло сякаш опция мач цялото съдържание на клетката (цяла клетка) в стандартния Excel Solver.
- Когато една клетка съдържа допълнителни интервали в началото или в края на съдържанието. В тази ситуация, вие дълго може да озадачи, опитвайки се да разбере защо формулата не работи.
Пример 1: Търсим текста началото или в края на някои символи
Да предположим, че искате да намерите конкретен клиент в базата данни, както е показано по-долу. Ти не си спомням името му, но вие знаете, че това започва в "АСК". Ето една формула перфектно се справи с тази задача:
Сега, когато сте сигурни, че сте намерили правилното име, можете да използвате същата тази формула, за да се намери сумата, платена от този клиент. Това е достатъчно, за да промените Третият аргумент на функцията VLOOKUP да преброят дясната колона. В този случай, колона С (3 в границите):
Ето няколко примера за символи:
Ние намерите името, завършваща на "човек":
Ние считаме, име, което започва в "реклама" и завършва в "син":
Намерете първото име в списъка, състоящ се от 5 знака:
= VLOOKUP ($ $ 2 ".": $ C $ 11,1, окачени)
PPS = (; $ $ 2 ".": $ C $ 11; 1; невярно)За да VLOOKUP маска, за да работи правилно, винаги трябва да използвате, за да FALSE (FALSE), както е четвъртият аргумент. Ако диапазона на търсене съдържа повече от една стойност, която съответства на дума за търсене маска, първата стойност намери се връща.
Пример 2: Ние комбинират заместващи символи и на клетки във формулите на CDF
Сега да се процедира с малко по-сложен пример за това как да се извърши търсене с помощта на функцията CDF от стойността на дадена клетка. Представете си, че в колона А е даден списък на лицензионните ключове, а в колона В списъка с имена, които притежава лиценза. В допълнение, може да има парче (няколко знака) на лицензионен ключ в клетка C1, и искате да разберете името на собственика.
Това може да стане само с такава формула:
Тази формула търси стойност в клетка C1 в предварително определени граници, и се връща на съответната стойност на колона Б. Имайте предвид, че първият аргумент, ние използваме амперсанд () преди и след референтния номер на клетка, за да се свържат текстов низ.
Както е показано на фигурата по-долу, функцията CDF връща стойността "Джеръми Хил", тъй като лиценз ключ съдържа последователност от символи от клетка В1.
Имайте предвид, че аргументът на table_array (маса) на екранната снимка на върха на име на таблица (Таблица 7), вместо определя обхват от клетки. Така че ние направихме в предишния пример.
Точен или приблизителната мач в VLOOKUP
За да започнете, нека да разберете, че в Microsoft Excel се разбира под точна и приблизителното съвпадение.
Така например, по следната формула докладва за грешка # N / A (# H / D), ако гама А2: A15 поиска до 4:
За да се разбере по-добре значението на избора TRUE (ВЯРНО) или FALSE (FALSE), нека да продължи с няколко формули с функцията на CDF, и погледнете резултатите.
Пример 1: Търсене на съвпадение използване WRT
Как да се сетите, за да намерите точно съвпадение, четвъртия аргумент на функцията VLOOKUP трябва да е FALSE (FALSE).
Нека да се върнат на масата още от първия пример, и да разберете какво животно може да се движи със скорост от 50 мили в час. Аз вярвам, че това е такава формула няма да доведе до проблеми с вас:
Моля, имайте предвид, че нашата гама търсене (колона А) съдържа две стойности, 50 - в клетки А5 и А6. Формулата връща стойността на клетка В5. Защо? Защото, когато търси точно съвпадение VLOOKUP използва първата стойност, което съвпада с желания адрес.
Пример 2: Използване на VLOOKUP да намерите приблизителна мач
Това е много важно, тъй като функцията CDF връща следващата най-висока стойност след определен, а след това търсенето е спряло. Ако пренебрегнем правилно подредени, случаят е от факта, че вие ще получите много странни резултати, или съобщение за грешка # една N / A (# N / A).
Сега можете да използвате една от следните формули:
= VLOOKUP (69, $ $ 2: $ B $ 15,2, вярно) или = VLOOKUP (69, $ $ 2: $ B $ 15,2)
= CDF (69; $ $ 2: $ B $ 15; 2, вярно) или = CDF (69; $ $ 2: $ B $ 15, 2)Както можете да видите, аз искам да разбера на каква скорост на животните най-близо до 69 mph. И това, което аз се върнах резултат VLOOKUP:
Както можете да видите, формулата връща резултата Antelope (Antelope), скоростта е 61 мили в час, въпреки че списъкът е също Гепард (Cheetah), който работи със скорост от 70 мили в час, а 70 е по-близо до 69 от 61, нали? Защо се случва това? Защото VLOOKUP при търсене на приблизителни мачове връща най-голямата стойност, която не надвишава желания.
Надявам се, че тези примери хвърли малко повече светлина върху работата на функцията CDF за Excel, и вече не гледам на него като аутсайдер. Сега това няма да попречи за кратко се повтаря основните точки, които научихме от материала за по-добро задържане в паметта.
CDF за Excel - е да се помни!
Свързани статии