содржина
Неодамна посветивме статија на една од најкорисните функции на Excel наречена VPR и покажа како може да се користи за извлекување на потребните информации од базата на податоци во ќелија на работниот лист. Спомнавме и дека има два случаи на употреба за функцијата VPR а само еден од нив се занимава со прашања во базата на податоци. Во оваа статија, ќе научите уште еден помалку познат начин за користење на функцијата VPR во Excel.
Ако сè уште не сте го направиле ова, тогаш задолжително прочитајте ја последната статија за функцијата VPR, бидејќи сите информации подолу претпоставуваат дека веќе сте запознаени со принципите опишани во првата статија.
При работа со бази на податоци, функции VPR се пренесува единствен идентификатор, кој се користи за да се идентификуваат информациите што сакаме да ги најдеме (на пример, код на производ или идентификациски број на клиент). Овој единствен код мора да биде присутен во базата на податоци, во спротивно VPR ќе пријави грешка. Во оваа статија ќе го разгледаме овој начин на користење на функцијата VPRкога идентификацијата воопшто не постои во базата на податоци. Како функцијата VPR се префрли на приближниот режим и избира кои податоци да ни ги даде кога сакаме да најдеме нешто. Во одредени околности, тоа е токму она што е потребно.
Пример од животот. Ја поставивме задачата
Ајде да го илустрираме овој напис со пример од реалниот живот - пресметување на провизии врз основа на широк опсег на продажни метрики. Ќе започнеме со многу едноставна опција, а потоа постепено ќе ја комплицираме додека единственото рационално решение на проблемот не е користење на функцијата VPR. Почетното сценарио за нашата фиктивна задача е следново: ако продавачот заработи повеќе од 30000 долари во продажба за една година, тогаш неговата провизија е 30%. Инаку, провизијата е само 20%. Да го ставиме во форма на табела:
Продавачот ги внесува нивните податоци за продажба во ќелијата B1, а формулата во ќелијата B2 ја одредува точната стапка на провизија што продавачот може да ја очекува. За возврат, добиената стапка се користи во ќелијата B3 за да се пресмета вкупната провизија што продавачот треба да ја добие (едноставно множење на ќелиите B1 и B2).
Најинтересниот дел од табелата е содржан во ќелијата Б2 - ова е формулата за одредување на стапката на провизија. Оваа формула содржи Excel функција наречена IF (АКО). За оние читатели кои не се запознаени со оваа функција, ќе објаснам како функционира:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Состојба е функционален аргумент кој ја зема вредноста на било кој од нив ВИСТИНСКИ КОД (ВИСТИНА), или НЕТОЧНО (НЕТОЧНО). Во примерот погоре, изразот Б1
Дали е вистина дека Б1 е помал од Б5?
Или можете да го кажете поинаку:
Дали е точно дека вкупниот износ на продажба за годината е помал од прагот?
Ако одговориме на ова прашање ДА (TRUE), потоа функцијата се враќа вредност ако е точно (вредност ако е ВИСТИНА). Во нашиот случај, ова ќе биде вредноста на ќелијата B6, односно стапката на провизија кога вкупната продажба е под прагот. Ако одговориме на прашањето НЕ (FALSE) потоа се враќа вредност ако е неточна (вредност ако е НЕТОЧНО). Во нашиот случај, ова е вредноста на ќелијата B7, односно стапката на провизија кога вкупната продажба е над прагот.
Како што можете да видите, ако ги земеме вкупните продажби од 20000 долари, добиваме провизија од 2% во ќелијата B20. Ако внесеме вредност од $40000, тогаш стапката на провизија ќе се промени за 30%:
Вака функционира нашата маса.
Ја комплицираме задачата
Да ги отежниме работите малку. Ајде да поставиме друг праг: ако продавачот заработи повеќе од 40000 долари, тогаш стапката на провизија се зголемува на 40%:
Се чини дека сè е едноставно и јасно, но нашата формула во ќелијата Б2 станува значително посложена. Ако внимателно ја погледнете формулата, ќе видите дека третиот аргумент на функцијата IF (АКО) се претвори во друга полноправна функција IF (АКО). Оваа конструкција се нарекува вгнездување на функции една во друга. Excel со задоволство ги дозволува овие конструкции, па дури и функционираат, но тие се многу потешки за читање и разбирање.
Нема да навлегуваме во техничките детали - зошто и како функционира, и нема да навлегуваме во нијансите на пишување вгнездени функции. На крајот на краиштата, ова е статија посветена на функцијата VPR, не е целосен водич за Excel.
Како и да е, формулата станува посложена! Што ако воведеме друга опција за провизија од 50% за оние продавачи кои заработуваат повеќе од 50000 долари во продажба. И ако некој има продадено повеќе од 60000 долари, дали ќе плати 60% провизија?
Сега формулата во ќелијата Б2, дури и ако е напишана без грешки, стана целосно нечитлива. Мислам дека има малкумина кои сакаат да користат формули со 4 нивоа на вгнездување во своите проекти. Мора да има полесен начин?!
И постои таков начин! Функцијата ќе ни помогне VPR.
Ја применуваме функцијата VLOOKUP за да го решиме проблемот
Ајде малку да го промениме дизајнот на нашата маса. Ќе ги чуваме сите исти полиња и податоци, но ќе ги распоредиме на нов, покомпактен начин:
Одвојте момент и уверете се во новата маса Табела со оценки ги вклучува истите податоци како претходната табела за прагови.
Главната идеја е да се користи функцијата VPR да се одреди саканата тарифна стапка според табелата Табела со оценки во зависност од обемот на продажба. Ве молиме имајте предвид дека продавачот може да продава стоки за износ што не е еднаков на еден од петте прагови во табелата. На пример, тој би можел да продаде за 34988 долари, но таква сума нема. Ајде да видиме како функционира VPR може да се справи со таква ситуација.
Вметнување на функција VLOOKUP
Изберете ја ќелијата B2 (каде што сакаме да ја вметнеме нашата формула) и пронајдете VLOOKUP (VLOOKUP) во библиотеката со функции на Excel: Формула (формули) > Функционална библиотека (Библиотека на функции) > Пребарување и референца (Референци и низи).
Се појавува дијалог прозорец Функција Аргументи (Функциски аргументи). Ги пополнуваме вредностите на аргументите еден по еден, почнувајќи од Барај_вредност (Вредност_пребарување). Во овој пример, ова е вкупниот износ на продажба од ќелијата B1. Ставете го курсорот во полето Барај_вредност (Lookup_value) и изберете ја ќелијата B1.
Следно, треба да ги наведете функциите VPRкаде да барате податоци. Во нашиот пример, ова е табела Табела со оценки. Ставете го курсорот во полето Табела_низа (Табела) и изберете ја целата табела Табела со оценкиосвен заглавијата.
Следно, треба да одредиме од која колона да извлечеме податоци користејќи ја нашата формула. Ние сме заинтересирани за стапката на провизија, која е во втората колона од табелата. Затоа, за аргументот Кол_индекс_број (Број_колона) внесете ја вредноста 2.
И конечно, го воведуваме последниот аргумент - Range_lookup (Interval_lookup).
Важно: токму употребата на овој аргумент ја прави разликата помеѓу двата начини на примена на функцијата VPR. При работа со бази на податоци, аргументот Range_lookup (range_lookup) секогаш мора да има вредност НЕТОЧНО (НЕТОЧНО) за да барате точно совпаѓање. Во нашата употреба на функцијата VPR, мора да го оставиме ова поле празно или да внесеме вредност ВИСТИНСКИ КОД (ВИСТИНА). Исклучително е важно правилно да ја изберете оваа опција.
За да биде појасно, ќе воведеме ВИСТИНСКИ КОД (ВИСТИНА) на терен Range_lookup (Interval_lookup). Иако, ако го оставите полето празно, ова нема да биде грешка, бидејќи ВИСТИНСКИ КОД е неговата стандардна вредност:
Ги пополнивме сите параметри. Сега притискаме OK, а Excel ни создава формула со функција VPR.
Ако експериментираме со неколку различни вредности за вкупната сума на продажба, тогаш ќе се погрижиме формулата да работи правилно.
Заклучок
Кога функцијата VPR работи со бази на податоци, аргумент Range_lookup (range_lookup) мора да прифати НЕТОЧНО (НЕТОЧНО). И вредноста внесена како Барај_вредност (Lookup_value) мора да постои во базата на податоци. Со други зборови, бара точно совпаѓање.
Во примерот што го разгледавме во оваа статија, нема потреба да се добие точна совпаѓање. Ова е случај кога функцијата VPR мора да се префрли на приближен режим за да се врати саканиот резултат.
На пример: Сакаме да одредиме која стапка да ја користиме при пресметката на провизијата за продавач со обем на продажба од 34988 долари. Функција VPR ни враќа вредност од 30%, што е апсолутно точно. Но, зошто формулата го избра редот што содржи точно 30%, а не 20% или 40%? Што се подразбира под приближно пребарување? Да бидеме јасни.
Кога аргументот Range_lookup (interval_lookup) има вредност ВИСТИНСКИ КОД (ТОЧНО) или испуштена, функција VPR се повторува низ првата колона и ја избира најголемата вредност што не ја надминува бараната вредност.
Важна точка: За да функционира оваа шема, првата колона од табелата мора да биде подредена во растечки редослед.