Efnisyfirlit
Nýlega tileinkuðum við grein einni af gagnlegustu Excel aðgerðunum sem kallast VPR og sýndi hvernig hægt er að nota það til að draga nauðsynlegar upplýsingar úr gagnagrunni í vinnublaðsreit. Við nefndum líka að það eru tvö notkunartilvik fyrir aðgerðina VPR og aðeins ein þeirra fjallar um gagnagrunnsfyrirspurnir. Í þessari grein muntu læra aðra minna þekkta leið til að nota aðgerðina VPR í Excel.
Ef þú hefur ekki gert þetta ennþá, vertu viss um að lesa síðustu greinina um aðgerðina VPR, vegna þess að allar upplýsingarnar hér að neðan gera ráð fyrir að þú þekkir nú þegar meginreglurnar sem lýst er í fyrstu greininni.
Þegar unnið er með gagnagrunna, aðgerðir VPR framhjá er einkvæmu auðkenni sem er notað til að auðkenna þær upplýsingar sem við viljum finna (til dæmis vörukóða eða kenninúmer viðskiptavinar). Þessi einstaki kóði verður að vera til staðar í gagnagrunninum, annars VPR mun tilkynna villu. Í þessari grein munum við skoða þessa leið til að nota aðgerðina VPRþegar auðkennið er alls ekki til í gagnagrunninum. Eins og aðgerðin VPR skipt yfir í áætlaða stillingu og velur hvaða gögn á að veita okkur þegar við viljum finna eitthvað. Við ákveðnar aðstæður er þetta einmitt það sem þarf.
Dæmi úr lífinu. Við settum verkefnið
Við skulum útskýra þessa grein með raunveruleikadæmi - reiknum þóknun út frá margs konar sölumælingum. Við byrjum á mjög einföldum valkosti og flækjum hann smám saman þar til eina skynsamlega lausnin á vandamálinu er að nota aðgerðina VPR. Upphafssviðsmyndin fyrir gerviverkefni okkar er sem hér segir: ef sölumaður græðir meira en $30000 í sölu á ári, þá er þóknun hans 30%. Annars er þóknunin aðeins 20%. Við skulum setja það í formi töflu:
Seljandi slær inn sölugögn sín í reit B1 og formúlan í reit B2 ákvarðar rétta þóknunarhlutfallið sem seljandinn getur búist við. Aftur á móti er hlutfallið sem myndast notað í reit B3 til að reikna út heildarþóknunina sem seljandi ætti að fá (einfaldlega margfaldað reit B1 og B2).
Áhugaverðasti hluti töflunnar er að finna í reit B2 - þetta er formúlan til að ákvarða þóknunarhlutfallið. Þessi formúla inniheldur Excel fall sem kallast IF (EF). Fyrir þá lesendur sem ekki þekkja þessa aðgerð mun ég útskýra hvernig hún virkar:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Skilyrði er fallröksemd sem tekur gildi annaðhvort SANN KÓÐI (SATT), eða RANGT (RANGT). Í dæminu hér að ofan er tjáningin B1
Er það satt að B1 sé minna en B5?
Eða þú getur sagt það öðruvísi:
Er það rétt að heildarupphæð sölu ársins sé minni en viðmiðunarverðið?
Ef við svörum þessari spurningu YES (TRUE), þá skilar fallið gildi ef satt er (gildi ef satt er). Í okkar tilviki mun þetta vera gildi klefi B6, þ.e. þóknunarhlutfall þegar heildarsala er undir viðmiðunarmörkum. Ef við svörum spurningunni Nei (FALSE) kemur svo aftur gildi ef rangt (gildi ef FALSE). Í okkar tilviki er þetta gildi klefi B7, þ.e. þóknunarhlutfall þegar heildarsala er yfir viðmiðunarmörkum.
Eins og þú sérð, ef við tökum heildarsöluna upp á $20000, fáum við 2% þóknunarhlutfall í reit B20. Ef við sláum inn gildi upp á $40000, þá mun þóknunarhlutfallið breytast um 30%:
Svona virkar borðið okkar.
Við flækjum verkefnið
Gerum hlutina aðeins erfiðari. Setjum annan þröskuld: ef seljandinn þénar meira en $40000, þá hækkar þóknunarhlutfallið í 40%:
Allt virðist vera einfalt og skýrt, en formúlan okkar í hólf B2 verður áberandi flóknari. Ef þú lítur vel á formúluna muntu sjá að þriðja rök fallsins IF (IF) breyttist í aðra fullgilda aðgerð IF (EF). Þessi smíði er kölluð hreiður aðgerða inn í hvort annað. Excel leyfir þessar smíðar með ánægju og þær virka jafnvel, en þær eru miklu erfiðari að lesa og skilja.
Við munum ekki kafa ofan í tæknilegar upplýsingar - hvers vegna og hvernig það virkar, og við munum ekki fara í blæbrigði þess að skrifa hreiður aðgerðir. Enda er þetta grein tileinkuð aðgerðinni VPR, ekki tæmandi leiðarvísir fyrir Excel.
Hvað sem því líður þá verður formúlan flóknari! Hvað ef við kynnum annan valmöguleika fyrir 50% þóknunarhlutfall fyrir þá seljendur sem græða meira en $50000 í sölu. Og ef einhver hefur selt meira en $60000, mun hann borga 60% þóknun?
Nú er formúlan í reit B2, jafnvel þótt hún hafi verið skrifuð án villna, orðin algjörlega ólæsileg. Ég held að það séu fáir sem vilja nota formúlur með 4 stigum hreiður í verkefnum sínum. Það hlýtur að vera auðveldari leið?!
Og það er svoleiðis! Aðgerðin mun hjálpa okkur VPR.
Við notum VLOOKUP aðgerðina til að leysa vandamálið
Við skulum breyta hönnun borðsins okkar aðeins. Við munum halda öllum sömu reitum og gögnum, en raða þeim á nýjan, þéttari hátt:
Taktu þér smá stund og vertu viss um nýja borðið Verðtafla inniheldur sömu gögn og fyrri þröskuldatöflu.
Meginhugmyndin er að nota aðgerðina VPR til að ákvarða æskilegt gjaldskrá samkvæmt töflunni Verðtafla eftir sölumagni. Athugið að seljandi getur selt vörur fyrir upphæð sem er ekki jöfn einum af fimm þröskuldum í töflunni. Til dæmis gæti hann selt fyrir $34988, en það er engin slík upphæð. Við skulum sjá hvernig virka VPR getur tekist á við slíkar aðstæður.
Setja inn VLOOKUP aðgerð
Veldu reit B2 (þar sem við viljum setja formúluna okkar inn) og finndu VILOOKUP (VLOOKUP) í Excel Function Library: Formúlur (formúlur) > Virka bókasafn (Verkunarsafn) > Leit og tilvísun (Tilvísanir og fylki).
Gluggi birtist Aðgerðarrök (Funkunarrök). Við fyllum út gildi röksemda einn í einu, byrja á Uppflettingargildi (Upplitsgildi). Í þessu dæmi er þetta heildarupphæð sölu frá reit B1. Settu bendilinn í reitinn Uppflettingargildi (Upplitsgildi) og veldu reit B1.
Næst þarftu að tilgreina aðgerðirnar VPRhvar á að leita að gögnum. Í okkar dæmi er þetta tafla Verðtafla. Settu bendilinn í reitinn Table_array (Tafla) og veldu alla töfluna Verðtaflanema hausar.
Næst þurfum við að tilgreina hvaða dálk á að draga gögn úr með því að nota formúluna okkar. Við höfum áhuga á þóknunarhlutfallinu, sem er í öðrum dálki töflunnar. Því fyrir rökin Col_index_num (Column_number) sláðu inn gildið 2.
Og að lokum kynnum við síðustu rökin - Range_lookup (Interval_lookup).
mikilvægt: það er notkun þessarar röksemdafærslu sem gerir muninn á milli tveggja leiða til að beita fallinu VPR. Þegar unnið er með gagnagrunna eru rökin Range_lookup (range_lookup) verður alltaf að hafa gildi RANGT (FALSE) til að leita að nákvæmri samsvörun. Í notkun okkar á aðgerðinni VPR, við verðum að skilja þennan reit eftir auðan, eða slá inn gildi SANN KÓÐI (SATT). Það er mjög mikilvægt að velja þennan valkost rétt.
Til að gera það skýrara munum við kynna SANN KÓÐI (TRUE) á sviði Range_lookup (Interval_lookup). Þó, ef þú skilur reitinn eftir auðan, þá verður þetta ekki villa, þar sem SANN KÓÐI er sjálfgefið gildi þess:
Við höfum fyllt út allar breytur. Nú ýtum við á OK, og Excel býr til formúlu fyrir okkur með falli VPR.
Ef við gerum tilraunir með nokkur mismunandi gildi fyrir heildarsöluupphæðina, þá munum við ganga úr skugga um að formúlan virki rétt.
Niðurstaða
Þegar aðgerðin VPR vinnur með gagnagrunna, rök Range_lookup (range_lookup) verður að samþykkja RANGT (RANGT). Og gildið slegið inn sem Uppflettingargildi (Upplitsgildi) verður að vera til í gagnagrunninum. Með öðrum orðum, það er að leita að nákvæmri samsvörun.
Í dæminu sem við höfum skoðað í þessari grein er engin þörf á að fá nákvæma samsvörun. Þetta er tilfellið þegar aðgerðin VPR verður að skipta yfir í áætlaða stillingu til að skila tilætluðum árangri.
Til dæmis: Við viljum ákvarða hvaða hlutfall á að nota í þóknunarútreikningi fyrir sölumann með sölumagn $34988. Virka VPR skilar okkur 30% gildi, sem er alveg rétt. En hvers vegna valdi formúlan línuna sem innihélt nákvæmlega 30% en ekki 20% eða 40%? Hvað er átt við með áætlaðri leit? Við skulum hafa það á hreinu.
Þegar rökin Range_lookup (interval_lookup) hefur gildi SANN KÓÐI (TRUE) eða sleppt, fall VPR endurtekur í gegnum fyrsta dálkinn og velur stærsta gildið sem fer ekki yfir uppflettingargildið.
Mikilvægt atriði: Til að þetta kerfi virki verður að raða fyrsta dálki töflunnar í hækkandi röð.