Werrej
Dan l-aħħar iddedikajna artiklu għal waħda mill-iktar funzjonijiet utli ta’ Excel imsejħa VPR u wera kif jista 'jintuża biex jiġi estratt l-informazzjoni meħtieġa minn database f'ċellula worksheet. Semmejna wkoll li hemm żewġ każijiet ta’ użu għall-funzjoni VPR u wieħed biss minnhom jittratta mistoqsijiet tad-database. F'dan l-artikolu, titgħallem mod ieħor inqas magħruf biex tuża l-funzjoni VPR fl-Excel.
Jekk għadek ma għamiltx dan, imbagħad kun żgur li taqra l-aħħar artiklu dwar il-funzjoni VPR, għaliex l-informazzjoni kollha hawn taħt tassumi li inti diġà familjari mal-prinċipji deskritti fl-ewwel artiklu.
Meta taħdem ma 'databases, funzjonijiet VPR jiġi mgħoddi identifikatur uniku, li jintuża biex jidentifika l-informazzjoni li rridu nsibu (per eżempju, kodiċi tal-prodott jew numru ta 'identifikazzjoni tal-klijent). Dan il-kodiċi uniku għandu jkun preżenti fid-database, inkella VPR se tirrapporta żball. F'dan l-artikolu, se nħarsu lejn dan il-mod kif tuża l-funzjoni VPRmeta l-id ma teżisti xejn fid-database. Bħallikieku l-funzjoni VPR qalbu għall-mod approssimattiv, u jagħżel liema data tipprovdilna meta rridu nsibu xi ħaġa. F'ċerti ċirkostanzi, dan huwa eżattament dak li huwa meħtieġ.
Eżempju mill-ħajja. Aħna nistabbilixxu l-kompitu
Ejja nispjegaw dan l-artikolu b'eżempju tal-ħajja reali - il-kalkolu tal-kummissjonijiet ibbażati fuq firxa wiesgħa ta 'metriċi tal-bejgħ. Se nibdew b'għażla sempliċi ħafna, u mbagħad se nikkumplikawha gradwalment sakemm l-unika soluzzjoni razzjonali għall-problema hija li tuża l-funzjoni VPR. Ix-xenarju inizjali għall-kompitu fittizju tagħna huwa kif ġej: jekk bejjiegħ jagħmel aktar minn $ 30000 f'bejgħ f'sena, allura l-kummissjoni tiegħu hija ta '30%. Inkella, il-kummissjoni hija biss 20%. Ejja npoġġuha fil-forma ta 'tabella:
Il-bejjiegħ idaħħal id-dejta tal-bejgħ tiegħu fiċ-ċellula B1, u l-formula fiċ-ċellula B2 tiddetermina r-rata ta 'kummissjoni korretta li l-bejjiegħ jista' jistenna. Min-naħa tagħha, ir-rata li tirriżulta hija użata fiċ-ċellula B3 biex tikkalkula l-kummissjoni totali li l-bejjiegħ għandu jirċievi (sempliċement timmultiplika ċ-ċelloli B1 u B2).
L-aktar parti interessanti tat-tabella tinsab fiċ-ċellula B2 - din hija l-formula biex tiddetermina r-rata tal-kummissjoni. Din il-formula fiha funzjoni Excel imsejħa IF (JEKK). Għal dawk il-qarrejja li mhumiex familjari ma 'din il-funzjoni, se nispjega kif taħdem:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Kundizzjoni huwa argument ta 'funzjoni li jieħu l-valur ta' jew KODIĊI VERU (VERU), jew FALZ (FALZ). Fl-eżempju ta 'hawn fuq, l-espressjoni B1
Veru li B1 huwa inqas minn B5?
Jew tista 'tgħidha differenti:
Huwa veru li l-ammont totali tal-bejgħ għas-sena huwa inqas mill-valur limitu?
Jekk inwieġbu din il-mistoqsija IVA (VERU), imbagħad il-funzjoni terġa' lura valur jekk veru (valur jekk VERU). Fil-każ tagħna, dan se jkun il-valur taċ-ċellula B6, jiġifieri rata ta 'kummissjoni meta l-bejgħ totali jkun taħt il-limitu. Jekk inwieġbu l-mistoqsija LE (FALZ) imbagħad jirritorna valur jekk falz (valur jekk FALZ). Fil-każ tagħna, dan huwa l-valur taċ-ċellula B7, jiġifieri r-rata tal-kummissjoni meta l-bejgħ totali jkun 'il fuq mil-limitu.
Kif tistgħu taraw, jekk nieħdu l-bejgħ totali ta '$ 20000, nikseb rata ta' kummissjoni ta '2% fiċ-ċellula B20. Jekk indaħħlu valur ta '$ 40000, allura r-rata tal-kummissjoni tinbidel bi 30%:
Hekk taħdem il-mejda tagħna.
Aħna nikkomplikaw il-kompitu
Ejja nagħmlu l-affarijiet ftit aktar diffiċli. Ejja nistabbilixxu limitu ieħor: jekk il-bejjiegħ jaqla 'aktar minn $ 40000, allura r-rata tal-kummissjoni tiżdied għal 40%:
Kollox jidher li huwa sempliċi u ċar, iżda l-formula tagħna fiċ-ċellula B2 issir notevolment aktar ikkumplikata. Jekk tħares mill-qrib lejn il-formula, se tara li t-tielet argument tal-funzjoni IF (IF) inbidel f'funzjoni oħra sħiħa IF (JEKK). Din il-kostruzzjoni tissejjaħ nesting ta 'funzjonijiet f'xulxin. Excel heureusement jippermetti dawn il-kostruzzjonijiet, u anke jaħdmu, iżda huma ħafna aktar diffiċli biex jinqraw u jinftiehmu.
Aħna mhux se nidħlu fid-dettalji tekniċi - għaliex u kif taħdem, u mhux se nidħlu fl-sfumaturi tal-kitba ta 'funzjonijiet nested. Wara kollox, dan huwa artiklu ddedikat għall-funzjoni VPR, mhux gwida kompleta għal Excel.
Ikun xi jkun il-każ, il-formula ssir aktar ikkumplikata! X'jiġri jekk nintroduċu għażla oħra għal rata ta 'kummissjoni ta' 50% għal dawk il-bejjiegħa li jagħmlu aktar minn $50000 f'bejgħ. U jekk xi ħadd ikun biegħ aktar minn $60000, se jħallas 60% kummissjoni?
Issa l-formula fiċ-ċellula B2, anki jekk inkitbet mingħajr żbalji, saret kompletament ma tistax tinqara. Naħseb li hemm ftit li jridu jużaw formuli b'4 livelli ta 'bejta fil-proġetti tagħhom. Għandu jkun hemm mod aktar faċli?!
U hemm mod bħal dan! Il-funzjoni se tgħinna VPR.
Aħna napplikaw il-funzjoni VLOOKUP biex insolvu l-problema
Ejja nibdlu ftit id-disinn tat-tabella tagħna. Aħna se nżommu l-istess oqsma u dejta kollha, iżda nirranġawhom b'mod ġdid u aktar kompatt:
Ħu mument u kun żgur li t-tabella l-ġdida Tabella tar-Rati jinkludi l-istess data bħat-tabella tal-limitu preċedenti.
L-idea ewlenija hija li tuża l-funzjoni VPR biex tiddetermina r-rata tat-tariffa mixtieqa skond it-tabella Tabella tar-Rati skond il-volum tal-bejgħ. Jekk jogħġbok innota li l-bejjiegħ jista 'jbigħ oġġetti għal ammont li mhuwiex ugwali għal wieħed mill-ħames limiti fit-tabella. Per eżempju, huwa jista 'jbigħ għal $ 34988, iżda m'hemm l-ebda ammont bħal dan. Ejja naraw kif il-funzjoni VPR jistgħu jittrattaw sitwazzjoni bħal din.
Daħħal funzjoni VLOOKUP
Agħżel iċ-ċellula B2 (fejn irridu daħħal il-formula tagħna) u ssib LOOKUP (LOOKUP) fil-Librerija tal-Funzjonijiet ta' Excel: Formuli (formuli) > Librerija tal-Funzjonijiet (Librerija tal-Funzjoni) > Fittex u Referenza (Referenzi u matriċi).
Tidher kaxxa tad-djalogu Argumenti tal-Funzjoni (Argumenti tal-funzjoni). Aħna nimlew il-valuri tal-argumenti wieħed wieħed, nibdew minn Lookup_value (Lookup_value). F'dan l-eżempju, dan huwa l-ammont totali ta 'bejgħ miċ-ċellula B1. Poġġi l-cursor fil-qasam Lookup_value (Lookup_value) u agħżel iċ-ċellula B1.
Sussegwentement, għandek bżonn tispeċifika l-funzjonijiet VPRfejn tfittex id-data. Fl-eżempju tagħna, din hija tabella Tabella tar-Rati. Poġġi l-cursor fil-qasam Table_array (Tabella) u agħżel it-tabella kollha Tabella tar-Ratiħlief għall-headers.
Sussegwentement, irridu nispeċifikaw liema kolonna niġbdu d-dejta mill-użu tal-formula tagħna. Aħna interessati fir-rata tal-kummissjoni, li tinsab fit-tieni kolonna tat-tabella. Għalhekk, għall-argument Col_index_num (Column_number) daħħal il-valur 2.
U fl-aħħar, nintroduċu l-aħħar argument - Range_lookup (Interval_lookup).
importanti: huwa l-użu ta' dan l-argument li jagħmel id-differenza bejn iż-żewġ modi kif tiġi applikata l-funzjoni VPR. Meta taħdem ma 'databases, l-argument Range_lookup (range_lookup) għandu dejjem ikollu valur FALZ (FALZ) biex tfittex taqbila eżatta. Fl-użu tagħna tal-funzjoni VPR, irridu nħallu dan il-qasam vojt, jew daħħal valur KODIĊI VERU (VERU). Huwa estremament importanti li tagħżel din l-għażla b'mod korrett.
Biex nagħmluha aktar ċara, aħna se nintroduċu KODIĊI VERU (VERU) fil-qasam Range_lookup (Interval_lookup). Għalkemm, jekk tħalli l-qasam vojt, dan mhux se jkun żball, peress KODIĊI VERU huwa l-valur default tiegħu:
Imlejna l-parametri kollha. Issa aħna nagħfas OK, u Excel joħloq formula għalina b'funzjoni VPR.
Jekk nesperimentaw b'diversi valuri differenti għall-ammont totali tal-bejgħ, allura niżguraw li l-formula taħdem b'mod korrett.
konklużjoni
Meta l-funzjoni VPR jaħdem ma 'databases, argument Range_lookup (range_lookup) trid taċċetta FALZ (FALZ). U l-valur imdaħħal bħala Lookup_value (Lookup_value) għandu jeżisti fid-database. Fi kliem ieħor, qed tfittex taqbila eżatta.
Fl-eżempju li ħares lejn f'dan l-artikolu, m'hemmx bżonn li tikseb taqbila eżatta. Dan huwa l-każ meta l-funzjoni VPR irid jaqleb għall-mod approssimattiv biex jirritorna r-riżultat mixtieq.
Pereżempju: Irridu niddeterminaw liema rata tuża fil-kalkolu tal-kummissjoni għal bejjiegħ b'volum ta 'bejgħ ta' $ 34988. Funzjoni VPR jirritorna lilna valur ta '30%, li huwa assolutament korrett. Imma għaliex il-formula għażlet ir-ringiela li fiha eżattament 30% u mhux 20% jew 40%? Xi tfisser tfittxija approssimattiva? Ejja nkunu ċari.
Meta l-argument Range_lookup (intervall_lookup) għandu valur KODIĊI VERU (VERU) jew barra, funzjoni VPR itenni fl-ewwel kolonna u jagħżel l-akbar valur li ma jaqbiżx il-valur ta' tfittxija.
Punt importanti: Biex din l-iskema taħdem, l-ewwel kolonna tat-tabella trid tiġi magħżula f'ordni axxendenti.