Inhalter
- Sich an Excel duerch verschidde Critèren
- Mir extrahéieren déi 2., 3., etc. Wäerter mat VLOOKUP
- Recuperéieren all Wiederholungen vum gewënschten Wäert
- XNUMXD Sich no bekannter Zeil a Kolonn
- Benotzt verschidde VLOOKUPs an enger Formel
- Dynamesch Ersatz vun Daten aus verschiddenen Dëscher mat VLOOKUP an INDIRECT
Am zweeten Deel vun eisem Tutorial iwwer d'Funktioun VPR (VLOOKUP) an Excel wäerte mir e puer Beispiller analyséieren, déi Iech hëllefen all d'Kraaft ze dirigéieren VPR fir déi ambitiéisst Excel Aufgaben ze léisen. D'Beispiller huelen un datt Dir schonn e Basiskenntnisser hutt wéi dës Fonktioun funktionnéiert. Wann net, kënnt Dir interesséiert sinn mam éischten Deel vun dësem Tutorial unzefänken, wat d'Syntax an d'Basisnotzung erkläert. VPR. Gutt, loosst eis ufänken.
Sich an Excel duerch verschidde Critèren
Funktioun VPR an Excel ass e wierklech mächtegt Tool fir Sich no engem bestëmmte Wäert an enger Datebank auszeféieren. Wéi och ëmmer, et gëtt eng bedeitend Begrenzung - seng Syntax erlaabt Iech nëmmen no engem Wäert ze sichen. Wat wann Dir duerch verschidde Konditioune sichen wëllt? Dir fannt d'Léisung hei ënnen.
Beispill 1: Sich no 2 verschiddene Critèren
Ugeholl mir hunn eng Lëscht vun Bestellungen a mir wëllen fannen Quantitéit vu Wueren (Qty.), baséiert op zwee Critèren - Client Numm (Client) и ProduktName (Produkt). D'Saach ass komplizéiert vun der Tatsaach, datt jidderee vun de Keefer verschidden Aarte vu Wueren bestallt huet, wéi aus der Tabell hei ënnen gesi ka ginn:
regelméisseg Funktioun VPR wäert net an dësem Szenario funktionnéieren, well et den éischte Wäert zréckkënnt, deen et fënnt, deen de gegebene Lookup-Wäert entsprécht. Zum Beispill, wann Dir d'Quantitéit vun engem Artikel wësse wëllt Séissegkeeten'vum Keefer bestallt Jeremy Hill, schreift déi folgend Formel:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
- Dës Formel gëtt d'Resultat zréck 15entspriechend dem Produkt Äppel, well et ass den éischte Wäert deen entsprécht.
Et gëtt eng einfach Léisung - eng zousätzlech Kolonn erstellen an där all déi gewënschte Critèren kombinéiert ginn. An eisem Beispill sinn dës Sailen Client Numm (Client) и ProduktName (Produkt). Vergiesst net datt déi fusionéiert Kolonn ëmmer déi lénks Kolonn am Sichberäich muss sinn, well et déi lénks Kolonn ass déi d'Funktioun VPR kuckt op wann Dir e Wäert sicht.
Also, Dir füügt eng Hëllefskolonne un den Dësch a kopéiert déi folgend Formel iwwer all seng Zellen: =B2&C2. Wann Dir wëllt datt d'String méi liesbar ass, kënnt Dir déi kombinéiert Wäerter mat engem Raum trennen: =B2&» «&C2. Duerno kënnt Dir déi folgend Formel benotzen:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Wou ass d'Zell B1 enthält de konkatenéierte Wäert vum Argument lookup_value (lookup_value) an 4 - Argument col_index_num (column_number), also d'Zuel vun der Kolonn, déi d'Donnéeën enthält, déi ze recuperéieren.
Beispill 2: VLOOKUP vun zwee Critèren mat Dësch op engem anere Blat gekuckten
Wann Dir d'Haapttabell (Main Table) aktualiséieren musst andeems Dir Daten aus der zweeter Tabell (Lookup Table) bäidréit, déi op engem anere Blat oder an engem aneren Excel Aarbechtsbuch läit, da kënnt Dir de gewënschten Wäert direkt an der Formel sammelen déi Dir setzt an den Haapt Dësch.
Wéi am virege Beispill, braucht Dir eng Hëllefskolonn an der Lookup Tabelle mat de kombinéierte Wäerter. Dës Kolonn muss déi lénks Kolonn am Sichberäich sinn.
Also d'Formel mat VPR kéint esou sinn:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Hei, Kolonnen B an C enthalen Client Nimm a Produit Nimm, respektiv, an de Link Bestellungen!$A&$2:$D$2 definéiert en Dësch fir an engem anere Blat ze kucken.
Fir d'Formel méi liesbar ze maachen, kënnt Dir dem View Range en Numm ginn, an da gesäit d'Formel vill méi einfach aus:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Fir datt d'Formel funktionnéiert, mussen d'Wäerter an der lénkser Kolonn vun der Tabell, déi Dir sicht, genee déiselwecht kombinéiert ginn wéi an de Sichkriterien. An der Figur hei uewen hu mir d'Wäerter u2bu2band kombinéiert e Raum tëscht hinnen gesat, op déiselwecht Manéier wéi Dir am éischten Argument vun der Funktioun (BXNUMX& "" & CXNUMX) maache musst.
Denkt drun! Funktioun VPR limitéiert op 255 Zeechen, et kann net no engem Wäert sichen dee méi wéi 255 Zeechen laang ass. Halt dëst am Kapp a gitt sécher datt d'Längt vum gewënschten Wäert dës Limit net iwwerschreit.
Ech averstanen datt d'Addéieren vun enger Hëllefskolonn net déi elegantst an net ëmmer akzeptabel Léisung ass. Dir kënnt datselwecht maachen ouni d'Hëllefkolonne, awer dat géif eng vill méi komplex Formel mat enger Kombinatioun vu Funktiounen erfuerderen INDEX (INDEX) an MATCH (MÉI ausgesat).
Mir extrahéieren déi 2., 3., etc. Wäerter mat VLOOKUP
Dir wësst dat schonn VPR kann nëmmen ee passende Wäert zréckginn, méi präzis, déi éischt fonnt. Awer wat wann dëse Wäert e puer Mol am gekuckte Array widderholl gëtt an Dir wëllt den 2. oder 3. vun hinnen extrahéieren? Wat wann all Wäerter? De Problem schéngt komplizéiert, awer d'Léisung existéiert!
Ugeholl datt eng Kolonn vun der Tabell d'Nimm vun de Clienten enthält (Clientennumm), an déi aner Kolonn enthält d'Produkter (Produkt) déi se kaaft hunn. Loosst eis probéieren déi 2., 3. a 4. Artikelen ze fannen, déi vun engem bestëmmte Client kaaft ginn.
Deen einfachste Wee ass eng Hëllefskolonn virun der Kolonn ze addéieren Client Numm a fëllt et mat Clientennimm mat der Widderhuelungsnummer vun all Numm, zum Beispill, John Doe 1, John Doe 2 etc.. Mir wäerten den Trick mat der Nummer mat der Funktioun maachen COUNTIF (COUNTIF), well d'Clientennimm an der Kolonn B sinn:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Duerno kënnt Dir déi normal Funktioun benotzen VPRdéi néideg Uerdnung ze fannen. Zum Beispill:
- Op ee Bléck 2-an Artikel vum Client bestallt Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Op ee Bléck 3-an Artikel vum Client bestallt Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Tatsächlech kënnt Dir eng Zellreferenz als Lookupwäert anstatt Text aginn, wéi an der folgender Figur gewisen:
Wann Dir nëmmen sicht 2-e Widderhuelung, Dir kënnt et ouni Hëllefskolonne maachen andeems Dir eng méi komplex Formel erstellt:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
An dëser Formel:
- $F$2 - eng Zell déi den Numm vum Keefer enthält (et ass onverännert, notéiert w.e.g. - de Link ass absolut);
- $ B $ - Kolonn Client Numm;
- Table4 - Ären Dësch (dës Plaz kann och eng regulär Gamme ginn);
- $ C16 - d'Ennzell vun Ärem Dësch oder Range.
Dës Formel fënnt nëmmen den zweete passende Wäert. Wann Dir déi verbleiwen Wiederholungen extrahéiert musst, benotzt déi viregt Léisung.
Wann Dir braucht eng Lëscht vun all Matcher - d'Funktioun VPR dëst ass keen Helfer, well et nëmmen ee Wäert zur Zäit zréckginn - Period. Awer Excel huet eng Funktioun INDEX (INDEX), déi mat dëser Aufgab einfach eens kann. Wéi esou eng Formel ausgesäit, léiert Dir am folgende Beispill.
Recuperéieren all Wiederholungen vum gewënschten Wäert
Wéi schonn erwähnt VPR kann net all duplizéiert Wäerter aus dem gescannte Beräich extrahéieren. Fir dëst ze maachen, brauch Dir eng liicht méi komplex Formel, déi aus verschiddenen Excel Funktiounen, wéi z INDEX (INDEX), KLEIN (KLEIN) an rudderen (LINE)
Zum Beispill fënnt d'Formel hei ënnen all Widderhuelunge vum Wäert vun der Zell F2 am Beräich B2: B16 a gëtt d'Resultat vun de selwechte Reihen an der Kolonn C zréck.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Gitt dës Arrayformel a verschidde benodeeleg Zellen, wéi d'Zellen f4: f8wéi an der Figur ënnendrënner gewisen. D'Zuel vun den Zellen muss gläich oder méi grouss sinn wéi déi maximal méiglech Unzuel vun Wiederholungen vum gesichte Wäert. Vergiesst net ze klicken Ctrl + Umeldung + Enterfir d'Arrayformel richteg anzeginn.
Wann Dir interesséiert sidd ze verstoen wéi et funktionnéiert, loosst eis e bëssen an d'Detailer vun der Formel tauchen:
Deel 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 - Vergläicht de Wäert an der Zell F2 mat jiddereng vun de Wäerter am Beräich B2: B16. Wann e Match fonnt gëtt, dann den Ausdrock STRING(C2:C16)-1 gëtt d'Zuel vun der entspriechender Linn zréck (Wäert -1 erlaabt Iech d'Headerlinn net ze enthalen). Wann et keng Mätscher, der Funktioun IF (IF) gëtt eng eidel String zréck.
Funktioun Resultat IF (WANN) et gëtt esou eng horizontal Array: {1,"",3,"",5,"","","","","","",12,"","",""}
Deel 2:
ROW()-3
СТРОКА()-3
Hei d'Funktioun rudderen (LINE) Akten als zousätzlech Konter. Well d'Formel an d'Zellen F4:F9 kopéiert ass, subtrahéiere mir d'Zuel 3 aus Funktioun Resultat ze kréien Wäert 1 an der Zell F4 (Linn 4, subtract 3) ze kréien 2 an der Zell F5 (Linn 5, subtract 3) a sou weider.
Deel 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funktioun KLEIN (KLENG) zréck n-o op de klengste Wäert an der Datearray. An eisem Fall, wéi eng Positioun (vum klengsten) fir zréckzekommen, gëtt vun der Funktioun bestëmmt rudderen (LINE) (kuckt Deel 2). Also, fir eng Zell F4 Funktioun SMALL({array},1) preparéieren 1-an (klengste) Array-Element, dh 1. Fir Zell F5 preparéieren 2-an dat klengst Element an der Array, dat ass 3Etc.
Deel 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funktioun INDEX (INDEX) gëtt einfach de Wäert vun enger spezifescher Zell an enger Array zréck c2:c16. Fir Zell F4 Funktioun INDEX($C$2:$C$16) wäert erëm zeréck Äppelfir F5 Funktioun INDEX($C$2:$C$16) wäert erëm zeréck Séissegkeeten' a sou weider.
Deel 5:
IFERROR()
ЕСЛИОШИБКА()
Endlech setzen mir d'Formel an der Funktioun IFERROR (IFERROR), well Dir sidd onwahrscheinlech zefridden mat der Fehlermeldung #AT (#N/A) wann d'Zuel vun den Zellen, an deenen d'Formel kopéiert ass, manner ass wéi d'Zuel vun duplizéierte Wäerter an der Gamme déi gekuckt gëtt.
XNUMXD Sich no bekannter Zeil a Kolonn
D'Ausféierung vun enger XNUMXD Sich an Excel beinhalt d'Sich no engem Wäert no enger bekannter Zeil a Kolonnnummer. An anere Wierder, Dir extrahéiert den Zellwäert op der Kräizung vun enger bestëmmter Zeil a Kolonn.
Also, loosst eis op eisen Dësch dréien an eng Formel mat enger Funktioun schreiwen VPR, déi Informatiounen iwwer d'Käschte vun Zitrounen am Mäerz verkaaft fannen.
Et gi verschidde Weeër fir eng XNUMXD Sich auszeféieren. Préift d'Optiounen a wielt deen deen Iech am Beschten passt.
VLOOKUP a MATCH Funktiounen
Dir kënnt eng Rëtsch Funktiounen benotzen VPR (VLOOKUP) an MÉI ausgesat (MATCH) fir de Wäert op der Kräizung vun de Felder ze fannen ProduktName (String) an Mount (Kolonn) vun der betreffend Array:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
D'Formel hei uewen ass eng regulär Funktioun VPR, déi no engem genaue Match vum Wäert "Zitrounen" an Zellen A2 bis A9 sicht. Awer well Dir net wësst an wéi enger Kolonn de March Verkaf ass, kënnt Dir d'Kolonnnummer fir dat drëtt Funktiounsargument net setzen. VPR. Amplaz gëtt d'Funktioun benotzt MÉI ausgesatfir dës Kolonn ze definéieren.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Iwwersat an mënschlech Sprooch heescht dës Formel:
- Mir sichen no de Personnagen "Mar" - Argument lookup_value (lookup_value);
- Sichen an Zellen vun A1 bis I1 - Argument lookup_array (lookup_array);
- Retour genee Match - Argument match_type (Match_Typ).
benotzt 0 am drëtten Argument, sot Dir Funktiounen MÉI ausgesat kuckt no den éischte Wäert, dee genau de Wäert entsprécht deen Dir sicht. Dëst entsprécht dem Wäert Falsch (FALSCH) fir dat véiert Argument VPR.
Dëst ass wéi Dir eng zwee-Wee Sichformel an Excel erstellt, och bekannt als zweedimensional Sich oder bidirektional Sich.
SUMPRODUCT Funktioun
Funktioun SUMPRODUCT (SUMPRODUCT) gëtt d'Zomm vun de Produkter vun de gewielte Arrays zréck:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
INDEX an MATCH Funktiounen
Am nächsten Artikel wäert ech dës Funktiounen am Detail erklären, also kënnt Dir just dës Formel kopéieren:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Benannt Beräicher an der Kräizung Bedreiwer
Wann Dir net an all déi komplex Excel Formelen sidd, kënnt Dir dës visuell an onvergiesslech Manéier gär hunn:
- Wielt den Dësch, öffnen d'Tab Formelen (Formelen) a klickt Erstellt aus Selektioun (Aus Selektioun erstellen).
- Kuckt d'Këschten Top Rei (op der Linn uewen) an Lénks Kolonn (an der Kolonn lénks). Microsoft Excel wäert Nimm un de Beräicher vun de Wäerter an der ieweschter Zeil an der lénkser Kolonn vun Ärem Spreadsheet zouginn. Elo kënnt Dir mat dësen Nimm direkt sichen ouni Formelen ze kreéieren.
- An all eidel Zell, schreiwen =row_name column_name, zum Beispill esou:
= Zitrounen Mar
… oder vice versa:
= Mar Zitrounen
Denkt drun datt d'Zeil- a Spaltnamen duerch e Raum getrennt sinn, wat an dësem Fall funktionnéiert wéi de Kräizungsoperateur.
Wann Dir en Numm gitt, weist Microsoft Excel e Tooltip mat enger Lëscht mat passenden Nimm, sou wéi wann Dir eng Formel agitt.
- Press gitt a kontrolléiert d'Resultat
Am Allgemengen, egal wéi eng vun den uewe genannte Methoden Dir wielt, ass d'Resultat vun enger zweedimensionaler Sich datselwecht:
Benotzt verschidde VLOOKUPs an enger Formel
Et geschitt datt d'Haapttabell an d'Sichtabell keng eenzeg Kolonn gemeinsam hunn, an dëst verhënnert datt Dir déi üblech Funktioun benotzt VPR. Wéi och ëmmer, et gëtt eng aner Tabell déi net d'Informatioun enthält déi mir interesséiert sinn, awer eng gemeinsam Kolonn mat der Haapttabell an der Lookup Tabelle huet.
Loosst eis dat folgend Beispill kucken. Mir hunn en Main Dësch mat enger Kolonn SKU (nei), wou Dir eng Kolonn mat den entspriechende Präisser vun enger anerer Tabelle addéiere wëllt. Zousätzlech hu mir 2 Lookup Dëscher. Déi éischt (Lookup Dësch 1) enthält aktualiséiert Zuelen SKU (nei) an Produit Nimm, an déi zweet (Lookup Dësch 2) - Produit Nimm an al Zuelen SKU (alt).
Fir d'Präisser vun der zweeter Lookup Dësch op d'Haapttabell ze addéieren, musst Dir eng Aktioun ausféieren, déi als duebel bekannt ass VPR oder nestéiert VPR.
- Schreift eng Funktioun VPR, déi de Produit Numm an der Tabell fënnt Sichttabell 1Hëllef SKU, als gewënschte Wäert:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)hei Nei_SKU - genannt Beräich $A:$B an der Tabell Sichttabell 1, 2 - dëst ass Kolonn B, déi d'Nimm vun de Wueren enthält (kuckt d'Bild hei uewen)
- Schreift eng Formel fir Präisser aus engem Dësch ze setzen Sichttabell 2 baséiert op bekannte Produit Nimm. Fir dëst ze maachen, paste d'Formel déi Dir virdru erstallt hutt als Lookup-Wäert fir déi nei Funktioun VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)hei Präis - genannt Beräich $A:$C an der Tabell Sichttabell 2, 3 ass Kolonn C mat Präisser.
D'Figur hei ënnen weist d'Resultat zréck vun der Formel déi mir erstallt hunn:
Dynamesch Ersatz vun Daten aus verschiddenen Dëscher mat VLOOKUP an INDIRECT
Als éischt, loosst eis klären wat mir mam Ausdrock "Dynamic Substitution of Data from different tables" mengen fir sécherzestellen, datt mir all aner richteg verstinn.
Et gi Situatiounen, wann et e puer Blieder mat Daten vum selwechte Format sinn, an et ass néideg déi néideg Informatioun aus engem bestëmmte Blat ze extrahieren, ofhängeg vum Wäert deen an enger bestëmmter Zell aginn ass. Ech mengen et ass méi einfach dëst mat engem Beispill z'erklären.
Stellt Iech vir datt Dir Verkafsberichter fir verschidde Regioune mat deene selwechte Produkter an am selwechte Format hutt. Dir wëllt Verkafszuele fir eng spezifesch Regioun fannen:
Wann Dir nëmmen zwee esou Berichter hutt, da kënnt Dir eng schrecklech einfach Formel mat Funktiounen benotzen VPR и IF (IF) fir de gewënschte Bericht ze wielen fir ze sichen:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
wou:
- $ D $ 2 an ass eng Zell déi den Numm vum Produkt enthält. Bedenkt datt mir absolute Referenzen hei benotzen fir de Lookupwäert z'änneren wann Dir d'Formel an aner Zellen kopéiert.
- $D3 ass eng Zell mam Numm vun der Regioun. Mir benotzen eng absolut Kolonnreferenz an eng relativ Zeilreferenz well mir plangen d'Formel an aner Zellen an der selwechter Kolonn ze kopéieren.
- FL_Salles и CA_Verkaf - d'Nimm vun den Dëscher (oder benannte Beräicher) déi entspriechend Verkafsberichter enthalen. Dir kënnt selbstverständlech déi üblech Blatnamen an Zellberäichreferenzen benotzen, zum Beispill 'FL Sheet'!$A$3:$B$10, mee genannt Beräicher si vill méi praktesch.
Wéi och ëmmer, wann et vill sou Dëscher sinn, ass d'Funktioun IF ass net déi bescht Léisung. Amplaz kënnt Dir d'Funktioun benotzen INDIREKTE (INDIRECT) fir de gewënschte Sichberäich zréckzekommen.
Wéi Dir wahrscheinlech wësst, ass d'Funktioun INDIREKTE gëtt benotzt fir e Link zréckzeginn, deen vun engem Textstring gëtt, dat ass genau dat wat mir elo brauchen. Also, ersetzen fett an der uewe genannter Formel den Ausdrock mat der Funktioun IF mat der Funktioun ze verbannen INDIREKTE. Hei ass eng Kombinatioun VPR и INDIREKTE funktionnéiert super mat:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
wou:
- $ D $ 2 an - dëst ass eng Zell mam Numm vum Produkt, et ass onverännert wéinst dem absolute Link.
- $D3 ass d'Zelle déi den éischten Deel vum Regiounnumm enthält. An eisem Beispill, dëst FL.
- _Verkaf - de gemeinsamen Deel vum Numm vun all benannte Beräicher oder Dëscher. Wann et mam Wäert an der Zell D3 kombinéiert gëtt, bildt et de vollqualifizéierten Numm vum erfuerderleche Beräich. Drënner sinn e puer Detailer fir déi, déi nei an der Funktioun sinn INDIREKTE.
Wéi INDIRECT a VLOOKUP funktionnéieren
Als éischt, loosst mech Iech un d'Syntax vun der Funktioun erënneren INDIREKTE (INDIREKTE):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Dat éischt Argument kann eng Zellreferenz (A1 oder R1C1 Stil), e Range Numm oder eng Textstring sinn. Dat zweet Argument bestëmmt wéi ee Linkstil am éischten Argument enthält:
- A1wann d'Argument ass TRUE CODE (TRUE) oder net spezifizéiert;
- R1C1, wann FAS E (FALSCH).
An eisem Fall huet de Link de Stil A1, also kënnt Dir dat zweet Argument erausloossen an op dat éischt fokusséieren.
Also loosst eis zréck op eis Verkafsberichter kommen. Wann Dir Iech erënnert, dann ass all Bericht eng separat Tabell op engem separaten Blat. Fir datt d'Formel richteg funktionnéiert, musst Dir Är Dëscher (oder Gamme) nennen, an all Nimm mussen e gemeinsamen Deel hunn. Zum Beispill, wéi dëst: CA_Verkaf, FL_Verkaf, TX_Verkaf a sou weider. Wéi Dir gesitt, ass "_Sales" an allen Nimm präsent.
Funktioun INDIREKTE verbënnt de Wäert an der Kolonn D an den Textstring "_Sales", domat seet VPR a wéi engem Dësch ze sichen. Wann Zell D3 de Wäert "FL" enthält, wäert d'Formel d'Tabell sichen FL_Verkaf, wann "CA" - an der Tabell CA_Verkaf a sou weider.
D'Resultat vun de Funktiounen VPR и INDIREKTE wäert déi folgend sinn:
Wann d'Donnéeën a verschiddene Excel-Bicher sinn, da musst Dir den Numm vum Buch virum genannte Beräich addéieren, zum Beispill:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Wann d'Funktioun INDIREKTE bezitt sech op en anert Aarbechtsbuch, dat Aarbechtsbuch muss op sinn. Wann et zou ass, wäert d'Funktioun e Feeler bericht. #REF! (#SSYL!).