Кој е премногу мрзлив или нема време да чита – погледнете го видеото. Детали и нијанси се во текстот подолу.
Формулирање на проблемот
Значи, имаме две табели - табела за нарачки и Ценовник:
Задачата е автоматски да се заменат цените од ценовникот во табелата со нарачки, фокусирајќи се на името на производот за подоцна да можете да ги пресметате трошоците.
Решение
Во множеството на функции Excel, под категоријата Референци и низи (Пребарување и референца) постои функција VPR (VLOOKUP).Оваа функција бара дадена вредност (во нашиот пример, ова е зборот „Јаболка“) во најлевата колона од наведената табела (ценовник) што се движи од врвот до дното и, откако ја најде, ја прикажува содржината на соседната ќелија (23 рубли) Шематски, работата на оваа функција може да биде претставена Така:
За полесно користење на функцијата, направете една работа одеднаш – дајте го вашето име на опсегот на ќелии во ценовникот. За да го направите ова, изберете ги сите ќелии од ценовникот освен „заглавието“ (G3: H19), изберете од менито Вметни – Име – Додели (Вметни - Име - Дефинирај) или притиснете CTRL + F3 и внесете кое било име (без празни места) како Цена… Сега, во иднина, можете да го користите ова име за да се поврзете со ценовникот.
Сега ја користиме функцијата VPR… Изберете ја ќелијата каде што ќе се внесе (D3) и отворете ја картичката Формули – Вметнување функција (Формули - Вметнете функција)… Во категоријата Референци и низи (Пребарување и референца) најдете ја функцијата VPR (VLOOKUP) и притиснете OK… Ќе се појави прозорец за внесување аргументи за функцијата:
Ги пополнуваме по ред:
- Посакувана вредност (Вредност за пребарување) – името на производот што функцијата треба да го најде во најлевата колона од ценовникот. Во нашиот случај, зборот „Јаболка“ од ќелијата Б3.
- Табела (Табела низа) – табела од која се земени саканите вредности uXNUMXbuXNUMX, односно нашиот ценовник. За повикување, го користиме нашето сопствено име „Цена“ дадено претходно. Ако не сте дале име, можете само да ја изберете табелата, но не заборавајте да го притиснете копчето F4да ја закачите врската со знаци за долар, бидејќи во спротивно, ќе се лизне надолу при копирање на нашата формула до останатите ќелии во колоната D3:D30.
- Колона_број (Број на индекс на колона) – сериски број (не буква!) Од колоната во ценовникот од која ќе земеме ценовни вредности. Првата колона од ценовникот со имињата е означена со 1, затоа ни треба цената од колоната со број 2.
- interval_lookup (Пребарување опсег) - во ова поле може да се внесат само две вредности: НЕТОЧНО или ТОЧНО:
- Ако се внесе вредност 0 or ЛАЖЕЊЕ (НЕТОЧНО), тогаш всушност тоа значи дека е дозволено само пребарување точен натпревар, т.е. ако функцијата не ја пронајде нестандардната ставка наведена во табелата за нарачки во ценовникот (ако е внесено, на пример, „Coconut“), ќе ја генерира грешката #N/A (нема податоци).
- Ако се внесе вредност 1 or ТОЧНО (ВИСТИНСКО), тогаш тоа значи дека го дозволувате пребарувањето не за точното, туку приближна совпаѓање, односно во случајот „кокос“, функцијата ќе се обиде да најде производ со име што е што поблиску до „кокос“ и да ја врати цената за ова име. Во повеќето случаи, таквата приближна замена може да го измами корисникот со замена на вредноста на погрешниот производ што всушност бил таму! Значи, за повеќето реални деловни проблеми, најдобро е да не се дозволи приближно пребарување. Исклучок е кога бараме бројки, а не текст - на пример, кога ги пресметуваме попустите за чекори.
Сè! Останува да се притисне OK и копирајте ја внесената функција во целата колона.
# N / A грешки и нивно потиснување
функција VPR (VLOOKUP) враќа грешка #N/A (# N/A) ако:
- Овозможено е точно пребарување (аргумент Приказ на интервал = 0) и саканото име не е во Табела.
- Вклучено е грубо пребарување (Приказ на интервал = 1), но Табела, во кој се одвива пребарувањето не е подредено по растечки редослед на имиња.
- Форматот на ќелијата од каде доаѓа потребната вредност на името (на пример, B3 во нашиот случај) и форматот на ќелиите од првата колона (F3: F19) од табелата се различни (на пример, нумерички и текст ). Овој случај е особено типичен кога се користат нумерички кодови (броеви на сметки, идентификатори, датуми итн.) наместо имиња на текст. Во овој случај, можете да ги користите функциите Ч и ТЕКСТОТ за конвертирање на формати на податоци. Ќе изгледа отприлика вака:
=VLOOKUP(TEXT(B3),цена,0)
Можете да прочитате повеќе за ова овде.
- Функцијата не може да ја најде потребната вредност затоа што кодот содржи празни места или невидливи знаци што не можат да се печатат (прекин на линии, итн.). Во овој случај, можете да користите текстуални функции TRIM (TRIM) и PRINT(ЧИСТО) да ги отстраните:
=VLOOKUP(TRIMSACES(CLEAN(B3)),цена,0)
=VLOOKUP(TRIM(CLEAN(B3));цена;0)
За да ја потиснете пораката за грешка # N / A (# N/A) во случаи кога функцијата не може да најде точно совпаѓање, можете да ја користите функцијата ИФЕРОР (ИФЕРОР)… Така, на пример, оваа конструкција ги пресретнува сите грешки генерирани од VLOOKUP и ги заменува со нули:
= IFERROR (VLOOKUP (B3, цена, 2, 0), 0)
= IFERROR (VLOOKUP (B3; цена; 2; 0); 0)
PS
Ако треба да извлечете не една вредност, туку целото множество одеднаш (ако има неколку различни), тогаш ќе мора да шаманизирате со формулата на низата. или користете ја новата функција XLOOKUP од Office 365.
- Подобрена верзија на функцијата VLOOKUP (VLOOKUP 2).
- Брза пресметка на попустите во чекор (опсег) со помош на функцијата VLOOKUP.
- Како да направите „лев VLOOKUP“ користејќи ги функциите INDEX и MATCH
- Како да се користи функцијата VLOOKUP за пополнување на формулари со податоци од списокот
- Како да ги извадите не првите, туку сите вредности од табелата одеднаш
- Функциите VLOOKUP2 и VLOOKUP3 од додатокот PLEX