Inhalter
Mir hunn viru kuerzem en Artikel gewidmet fir eng vun den nëtzlechsten Excel Funktiounen genannt VPR a gewisen wéi et benotzt ka ginn fir déi erfuerderlech Informatioun aus enger Datebank an eng Aarbechtsblatzell ze extrahieren. Mir hunn och erwähnt datt et zwee Benotzungsfäll fir d'Funktioun sinn VPR an nëmmen ee vun hinnen beschäftegt sech mat Datebank Ufroen. An dësem Artikel léiert Dir en anere manner bekannte Wee fir d'Funktioun ze benotzen VPR Excel.
Wann Dir dëst nach net gemaach hutt, da gitt sécher de leschten Artikel iwwer d'Funktioun ze liesen VPR, well all d'Informatioun hei drënner gëtt ugeholl datt Dir scho mat de Prinzipien am éischten Artikel beschriwwe sidd.
Wann Dir mat Datenbanken schafft, Funktiounen VPR en eenzegaartegen Identifizéierer gëtt iwwerginn, dee benotzt gëtt fir d'Informatioun ze identifizéieren déi mir wëllen fannen (zum Beispill e Produktcode oder eng Client Identifikatiounsnummer). Dësen eenzegaartege Code muss an der Datebank präsent sinn, soss VPR wäert e Feeler mellen. An dësem Artikel wäerte mir dës Manéier kucken fir d'Funktioun ze benotzen VPRwann d'ID guer net an der Datebank existéiert. Wéi wann d'Funktioun VPR op de geschätzte Modus gewiesselt, a wielt wéi eng Donnéeën eis ubidden wa mir eppes fannen. A bestëmmten Ëmstänn ass dat genau wat néideg ass.
E Beispill aus dem Liewen. Mir setzen d'Aufgab
Loosst eis dësen Artikel mat engem richtege Beispill illustréieren - Kommissiounen ausrechnen baséiert op enger breet Palette vu Verkafsmetriken. Mir fänken un mat enger ganz einfacher Optioun, an da wäerte mir et graduell komplizéiere bis déi eenzeg rational Léisung fir de Problem ass d'Funktioun ze benotzen VPR. Den initialen Szenario fir eis fiktiv Aufgab ass wéi follegt: wann e Verkeefer méi wéi $30000 am Verkaf an engem Joer mécht, dann ass seng Kommissioun 30%. Soss ass d'Kommissioun nëmmen 20%. Loosst eis et a Form vun engem Dësch setzen:
De Verkeefer gitt hir Verkafsdaten an der Zelle B1 an, an d'Formel an der Zelle B2 bestëmmt de richtege Kommissiounsquote deen de Verkeefer erwaarden kann. Am Tour gëtt de resultéierende Taux an der Zelle B3 benotzt fir d'Gesamtkommissioun ze berechnen, déi de Verkeefer soll kréien (einfach multiplizéieren Zellen B1 a B2).
De stäerkste interessant Deel vun der Tabell ass an Zell B2 enthale - dat ass d'Formel fir Bestëmmung vun der Kommissioun Taux. Dës Formel enthält eng Excel Funktioun genannt IF (IF). Fir déi Lieser déi net mat dëser Funktioun vertraut sinn, wäert ech erkläre wéi et funktionnéiert:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Zoustand ass e Funktiounsargument dat de Wäert vun entweder hëlt TRUE CODE (WOUER), oder Falsch (FALSCH). Am Beispill hei uewen ass den Ausdrock B1
Ass et richteg datt B1 manner wéi B5 ass?
Oder Dir kënnt et anescht soen:
Ass et richteg datt de Gesamtbetrag vum Verkaf fir d'Joer manner ass wéi de Schwellwäert?
Wa mir dës Fro äntweren JO (TRUE), da gëtt d'Funktioun zréck Wäert wann richteg (Wäert wann TRUE). An eisem Fall wäert dëst de Wäert vun der Zell B6 sinn, dh Kommissiounsquote wann de Gesamtverkaf ënner der Schwell ass. Wa mir d'Fro beäntweren NEE (FALSCH) dann zréck Wäert wann falsch (Wäert wann FALSE). An eisem Fall ass dëst de Wäert vun der Zell B7, dh Kommissiounsquote wann de Gesamtverkaf iwwer der Schwell ass.
Wéi Dir gesitt, wa mir de Gesamtverkaf vun $ 20000 huelen, kréie mir en 2% Kommissiounsquote an der Zell B20. Wa mir e Wäert vun $40000 aginn, da ännert de Kommissiounsquote ëm 30%:
Esou funktionéiert eisen Dësch.
Mir komplizéiere d'Aufgab
Loosst eis d'Saache e bësse méi schwéier maachen. Loosst eis en anere Schwell setzen: wann de Verkeefer méi wéi $40000 verdéngt, da klëmmt d'Kommissiounsquote op 40%:
Alles schéngt einfach a kloer ze sinn, awer eis Formel an der Zell B2 gëtt däitlech méi komplizéiert. Wann Dir no der Formel kuckt, gesitt Dir datt dat drëtt Argument vun der Funktioun IF (WANN) an eng aner vollwäerteg Funktioun verwandelt IF (IF). Dës Konstruktioun gëtt Nestéiere vu Funktiounen anenee genannt. Excel erlaabt dës Konstrukter glécklech, a si funktionnéieren souguer, awer si si vill méi schwéier ze liesen a verstoen.
Mir wäerten net an d'technesch Detailer verdéiwen - firwat a wéi et funktionnéiert, a mir ginn net an d'Nuancen vum Schreiwen vun nested Funktiounen. No allem ass dëst en Artikel gewidmet fir d'Funktioun VPR, net e komplette Guide fir Excel.
Wat och ëmmer de Fall, gëtt d'Formel méi komplizéiert! Wat wa mir eng aner Optioun fir eng Kommissiounsquote vun 50% fir déi Verkeefer aféieren déi méi wéi $ 50000 am Verkaf maachen. A wann iergendeen méi wéi $ 60000 verkaaft huet, bezuelen se 60% Kommissioun?
Elo ass d'Formel an der Zell B2, och wann se ouni Feeler geschriwwe gouf, komplett onliesbar ginn. Ech mengen, datt et wéineg sinn, déi Formelen mat 4 Nistniveauen an hire Projete benotze wëllen. Et muss ee méi einfache Wee ginn?!
An et gëtt esou e Wee! D'Funktioun hëlleft eis VPR.
Mir applizéieren d'VLOOKUP Funktioun fir de Problem ze léisen
Loosst eis den Design vun eisem Dësch e bëssen änneren. Mir behalen all déiselwecht Felder an Daten, awer arrangéieren se op eng nei, méi kompakt Manéier:
Huelt e Moment an sécherstellen déi nei Dësch Taux Dësch enthält déiselwecht Donnéeën wéi déi vireg Schwelltabell.
D'Haaptiddi ass d'Funktioun ze benotzen VPR fir de gewënschten Tarif-Taux no der Tabell ze bestëmmen Taux Dësch ofhängeg vum Verkafsvolumen. Notéiert w.e.g. datt de Verkeefer Wueren fir e Betrag ka verkafen deen net gläich ass wéi ee vun de fënnef Schwellen an der Tabell. Zum Beispill, hie kéint verkafen fir $ 34988, mä et gëtt keng esou Zomm. Loosst d'gesinn wéi d'Funktioun VPR kann mat esou enger Situatioun ëmgoen.
Aféierung vun enger VLOOKUP Funktioun
Wielt Zelle B2 (wou mir eis Formel asetzen wëllen) a fanne VLOOKUP (VLOOKUP) an der Excel Funktiounsbibliothéik: Formelen (Formelen) > Funktiounsbibliothek (Funktiounsbibliothéik) > Sich & Referenz (Referenzen an Arrays).
Eng Dialogbox erschéngt Funktioun Argumenter (Funktiounsargumenter). Mir fëllen d'Wäerter vun den Argumenter een nom aneren aus, ugefaange mat Lookup_value (Lookup_value). An dësem Beispill ass dëst de Gesamtbetrag vum Verkaf vun der Zell B1. Setzt de Cursor am Feld Lookup_value (Lookup_value) a wielt Zell B1.
Als nächst musst Dir d'Funktioune spezifizéieren VPRwou sicht Donnéeën. An eisem Beispill ass dëst en Dësch Taux Dësch. Setzt de Cursor am Feld Dësch_array (Table) a wielt de ganzen Dësch Taux Dëschausser Header.
Als nächst musse mir spezifizéieren wéi eng Kolonn fir Daten aus der Formel ze extrahieren. Mir sinn interesséiert an der Kommissioun Taux, déi an der zweeter Kolonn vun der Tabell ass. Dofir, fir d'Argumentatioun Col_index_num (Column_number) gitt de Wäert 2.
A schlussendlech presentéiere mir dat lescht Argument - Range_lookup (Intervall_lookup).
wichteg: et ass d'Benotzung vun dësem Argument dat den Ënnerscheed tëscht den zwou Weeër mécht fir d'Funktioun z'applizéieren VPR. Wann Dir mat Datenbanken schafft, gëtt d'Argument Range_lookup (range_lookup) muss ëmmer e Wäert hunn Falsch (FALSCH) fir no engem genaue Match ze sichen. An eisem Gebrauch vun der Funktioun VPR, mir mussen dëst Feld eidel loossen, oder e Wäert aginn TRUE CODE (WOUER). Et ass extrem wichteg dës Optioun richteg ze wielen.
Fir et méi kloer ze maachen, wäerte mir virstellen TRUE CODE (TRUE) am Feld Range_lookup (Intervall_lookup). Obwuel, wann Dir d'Feld eidel verloossen, dëst wäert net e Feeler ginn, zënter TRUE CODE ass säi Standardwäert:
Mir hunn all d'Parameteren ausgefëllt. Elo drécke mir OK, an Excel erstellt eng Formel fir eis mat enger Funktioun VPR.
Wa mir experimentéiere mat verschiddene Wäerter fir de Gesamtverkaafsbetrag, da wäerte mir sécher sinn datt d'Formel richteg funktionnéiert.
Konklusioun
Wann d'Funktioun VPR Wierker mat Datenbanken, Argument Range_lookup (range_lookup) muss akzeptéieren Falsch (FALSCH). An de Wäert aginn als Lookup_value (Lookup_value) muss an der Datebank existéieren. An anere Wierder, et sicht no engem genaue Match.
Am Beispill, dee mir an dësem Artikel gekuckt hunn, ass et net néideg e genee Match ze kréien. Dëst ass de Fall wann d'Funktioun VPR muss op ongeféier Modus wiesselen fir dat gewënschte Resultat zréckzekommen.
Zum Beispill: Mir wëllen bestëmmen wat Tarif an der Kommissioun Berechnung fir e Verkeefer mat engem Verkaf Volume vun $ 34988 ze benotzen. Funktioun VPR bréngt eis e Wäert vun 30% zréck, wat absolut richteg ass. Awer firwat huet d'Formel d'Zeil ausgewielt, déi genau 30% enthält an net 20% oder 40%? Wat ass mat ongeféiere Sich gemengt? Loosst eis kloer sinn.
Wann d'Argument Range_lookup (interval_lookup) huet e Wäert TRUE CODE (TRUE) oder ewechgelooss, Funktioun VPR iteréiert duerch déi éischt Kolonn a wielt de gréisste Wäert, deen de Lookupwäert net iwwerschreift.
Wichteg: Fir datt dëst Schema funktionnéiert, muss déi éischt Kolonn vun der Tabell an opsteigend Uerdnung zortéiert ginn.