Mat Kombinéiere vun Excel's VLOOKUP-Funktion mat der Funktioun KOLONN Mir kënnen eng Lookup Formel maache fir Iech méi Wäerter aus enger eenzeger Zeil vun enger Datebank oder enger Tabelle vun Daten ze kréien.
Am Beispiller, deen am Bild hei steet, fënnt d'Lookup-Formuléierung einfach all d'Wäerter zréck - wéi zum Beispill Präis, Deel Nummer, a Liwweranten - mat verschiddene Hardwarestécker.
01 vun 10
Gitt méi Wierder mat Excel VLOOKUP zréck
No der folgend Schrëtt erënnere sech d'Lookup Formel déi am Bild uginn, déi méi wéi e Wäert vun engem eenzegen Datensatz zréckkéiers.
D'Lookup Formel erfuerdert datt d'Funktioun KOLONN am Innere vu VLOOKUP verschachtelt gëtt.
Nesting eng Funktioun bedeit d'zweet Funktioun als ee vun den Argumenter fir déi éischt Funktioun.
An dësem Tutorial gëtt d'Funktioun COLUMN als Spaltenindex Nummer Argumenter fir VLOOKUP aginn.
Déi lescht Schrëtt am Tutorial beinhalt d'Kopie vun der Lookup Formel fir zousätzlech Spalten, fir Extra Wäerter fir de gewielte Bestand ze kréien.
Tutorial Inhalt
- D'Tutorial ginn erfëllt
- Schreift e Named Range fir den Data Table
- Vun der VLOOKUP Funktioun
- Andeems de Lookup Value Argument mat Absolute Cell Referenzen heescht
- Beim Table Array Argument
- An der verschachtelten COLUMN -Funktion erreechen
- Komplettéiert d'VLOOKUP Funktion
- Fichieren der Passwuert Formula mat der Fill Handle
- Dat Daten mat der Lookup Formulaire erausfuerderen
02 vun 10
Gitt d'Tutorial Donnéeën un
Den éischte Schrëtt am Tutorial ass d' Daten an e Excel- Arbeitsblatt .
Fir d'Schrëtt am Tutorial ze verfollegen, gitt d'Donnéeën déi am Bild hei drënner era an d'folgend Zellen agefouert ginn .
- Gitt d'Topqualitéit vun Daten an d'Zellen D1 bis G1
- Gitt d'zweet Sortie an d'Zellen D4 bis G10
Déi Sichrettriibstrukturen an d'Formuléierung déi sech an dësem Tutorial erstallt hunn, gëtt an d' Ronn 2 vum Arbeitsblat geschat.
D'Tutorial gitt net d'Formatéierung am Bild gesinn, awer dëst wäert net beaflossen, wéi d'Lookupformel funktionnéiert.
Informatiounen iwwer Formatéierungsoptiounen ähnlech wéi déi virdru gesinn gesi gëtt et an dësem Basis Excel-Formatting Tutorial .
Tutorial Schrëtt
- Gitt d'Donnéeën wéi am Bild virgesinn an d'Zellen D1 bis G10 gesinn
03 vun 10
Schreift e Named Range fir den Data Table
Eng benannt Sortiment ass eng einfach Manéier fir eng Rei vun Daten an enger Formel ze verweisen. Nëmme wéi et an d' Zelle Referenzen fir d'Donnéeën ze schécken, kënnt Dir just den Numm vum Beräich wielen.
Een zweet Virdeel fir eng benenneg Band ze benotzen ass datt d'Zelle Referenzen fir dës Band ni äntweren, wann d'Formel zu anere Zellen am Arbeitsblat kopéiert ass.
Range Nimm sinn dofir eng Alternativ fir d' Absolutenzell referenzéiert ze vermeiden fir Fehler ze vermeiden wann Formulare ofgeschloss ginn.
Bemierkung: Den Nummbezeechen enthält d'Rubriken oder Feldennamen fir d'Donnéeën (4) awer nëmmen déi Donnéeën.
Tutorial Schrëtt
- Helleg Zellen D5 bis G10 an der Aarbechtszweck fir ze wielen
- Klickt op d' Numm Box iwwer der Spalt A
- Typ "Table" (keng Zitater) am Numm Box
- Dréckt der ENTER Taste op der Tastatur
- D'Zellen D5 bis G10 hunn elo d'Sortiment vun "Tabelle". Mir benotzen den Numm fir de VLOOKUP-Talsarray-Argument méi spéit am Tutorial
04 vun 10
Öffnen der VLOOKUP Dialogbox
Obwuel et méiglech ass just eis Lookup Formel direkt an eng Zell an engem Workheet ze grënnen, ett vill Leit fannen et schwéier fir de Syntax direkt ze halen - virun allem fir eng komplex Formel wéi déi, déi mir an dësem Tutorial benotzen.
Eng Alternativ, an dësem Fall, ass d'VLOOKUP Dialogbox . Bal all d'Excel'er Funktiounen hunn eng Dialogbox fir Iech all eenzel Argumente vun der Funktioun op enger separater Linn z'ënnerstëtzen.
Tutorial Schrëtt
- Klickt op Zelle E2 vum Workheet - de Standort wou d'Resultater vun der zweedimensionaler Lookupformel gewisen ginn
- Klickt op d' Tabulairen Formulaire vun der Band
- Klickt op d'Optioun Lookup & Referenz am Stilband, fir d'Lëscht drop ze maachen
- Klickt op VLOOKUP an der Lëscht fir all Dialogbox opzemaachen
05 vun 10
Andeems de Lookup Value Argument mat Absolute Cell Referenzen kënnt
Normalerweis passt de Lookup-Wert mat engem Feld vun Daten an der éischter Kolonn vun der Datentabelle.
An eisem Beispill befaasst de Lookup Value op den Numm vum Hardware Deel, wou mir Informatioune fannen.
D'Zulässeg Typ vun Donnéeë fir de Lookup Value sinn:
- Textendaten
- e logesche Wäert (TRUE oder FALSE nëmmen)
- eng Rei
- e Zellen-Referenz op e Wäert am Arbeitsblat
An dësem Beispill wäerte mir d'Zuel-Referenz eraussichen, wou de Partnerschaft steet - Zelle D2.
Absolute Zelle Referenzen
An engem spéidere Schrëtt am Tutorial ginn d'Kopplung vun der Ziler E2 op Zellen F2 a G2 kopéiert.
Normalerweis wann d'Formelen an Excel kopéiert ginn, änneren d'Zuel Referenzen änneren fir hir nei Positioun ze reflektéieren.
Wann dat passéiert ass Äert D2 - d'Zuel-Referenz fir den Lookup Value - wäert änneren ginn wéi d'Formel ass kopéiert d'Erschrecken vu Feeler an Zellen F2 a G2.
Fir d'Fehler ze vermeiden, wäerte mir d'Zuel-Referenz D2 an eng absolute Zuel-Referenz konvertéieren.
Absolut Zelle Referenzen änneren net wann d'Formelen kopéiert ginn.
Absolut Zelle Referenzen ginn erstallt andeems d' F4- Taste op der Tastatur dréckt. Dat maachen d'Dollar Zeechen ëm d'Zuel referenz wéi d' $ D $ 2
Tutorial Schrëtt
- Klickt op d' lookup_value Zeil am Dialogbox
- Klickt op Zelle D2 fir dës Zell referenzéieren op d' lookup_value Linn. Dëst ass d'Zelle wou mer den Deelnumm drun drénke wéi iwwer déi mir Informatiounen suchen
- Ouni Beweegung vum Insertion Point dréckt d' F4- Taste op der Tastatur fir D2 an d'absolut Cell Referenz $ D $ 2 ze konvertéieren
- Lass de VLOOKUP-Funk Dialog op fir de nächste Schrëtt am Tutorial op
06 vun 10
Beim Table Array Argument
De Table Array ass d' Tabelle vun Daten, déi d'Lookup Formel gesucht, fir d'Informatioun ze fannen déi mir wëllen.
De Table Array muss mindestens zwee Spalten vun Donnéeën enthalen.
- Déi éischt Kolonn enthält d'Argument vun der Lookup Value (virdrun Schrëtt am Tutorial)
- Déi zweet an all zousätzlech Spalten, wäerten duerch d'Sichformuléierung gesicht ginn, fir d'Informatioun ze fannen déi mir uginn.
Den Argument Argument vun Dësch ass muss als e Sortiment gebaut ginn, deen d' Zuel referenzéiert fir den Datentabellen oder als Räichnumm huet .
Fir dëst Beispill wäerte mir de Programmnamen am Stuf 3 vum Tutorial benotzen.
Tutorial Schrëtt
- Klickt op de Table_array Zeil am Dialogbox
- Typ "Table" (keng Zitater) fir de Sortiment fir dëse Argument ze markéieren
- Lass de VLOOKUP-Funk Dialog op fir de nächste Schrëtt am Tutorial op
07 vun 10
Nestelen der COLUMN -Funktion
Normalerweis VLOOKUP léisst nëmmen Daten aus enger Spalte vun enger Datentabelle zréck an dës Kolonn gëtt duerch de Spaltenindex Nummer Argument festgeluegt.
An dësem Beispill si mir dräi Spalten, déi mir Donnéeë vum SREL retournéieren wëllen, dofir brauche mer e Wee fir d' Indexnummer vum Säulen z'änneren ouni eis Passungsformel ze änneren.
Dëst ass wou d'Funktioun COLUMN erlaabt ass. Wann et e Spëtznumm Index Argument gëtt, ännert dës Zortformel aus der Zelle D2 an d'Zellen E2 an F2 spéit am Léierprogramm.
Nestingfunktionen
D'Funktioun KOLONN ageleet als VLOOKUPs Spaltenindex Nummer Argument .
Dëst gëtt erreechtert andeems d'COLUMN-Funktioun am VLOOKUP an der Col_index_num Linn vun der Dialogbox geschéckt ass.
An der COLUMN-Funktion manuell Enter
Wann Nesting-Fichieren excel erlaabt eis net déi zweet Funktioun Dialogbox fir seng Argumenter z'informéieren.
D'Funktioun KOLONN muss deemno manuell an der Col_index_num Linn erfëllt ginn .
D'Funktioun COLUMN huet nëmmen e Argument - de Referenzargument , deen eng Zell Referenz ass.
Auswiel vun der Referenz Argument vum Referenzk Argumn
D'Funktioun vun der Aarbecht COLUMN ass d'Zuel vun der Spalt als Referenzargument zréck .
An anere Wierder ännert se den Spaltententwéckel an eng Zuel vu Späicher A als éischt Kolonn, Spalte B den zweeten an sou weider.
Zënter dat éischt Feld vun Daten, déi mir zréckginn, ass de Präis vum Punkt - wat an der Spalt 2 vun der Datentabelle steet - mir kënnen d'Zuelreferenz fir eng Zelle an der Spalt B als Referenz Argument wielen fir de Nummer 2 fir Den Argument Col_index_num .
Tutorial Schrëtt
- Fëllt d'Dialogbox VLOOKUP-Fënster op d'Linn Col_index_num
- Gidd d' Spalte vun der Funktiounsnamme an der Ufro vun enger rouder router Klammer " ( "
- Klickt op Zell B1 am Arbeitsblatt , fir dës Zell-Referenz als Referenzargument z'entwéckelen
- Gidd d'Schluss ronn Klammer " ) " fir d'COLUMN Funktion ze kompletéieren
- Lass de VLOOKUP-Funk Dialog op fir de nächste Schrëtt am Tutorial op
08 vun 10
Beim VLOOKUP Range Lookup Argument
Den Argument Range_lookup Argument vum VLOOKUP ass e logesche Wäert (TRUE oder FALSE), wat weist datt Dir wëllt VLOOKUP fir e genee oder en onbekannt Match op den Lookup_value fannen.
- Wann TRUE oder ob dësen Argument wegléisst, kënnt VLOOKUP entweder e genee Match op den Lookup_value, oder wann e genee Match net fonnt gëtt, gëtt den VLOOKUP den nächste gréissten Wert zréck. Fir d'Formel fir dat ze maachen, musse d'Donnéeën an der éischter Spalt vum Table_array opsteigend sinn .
- Wann FALSE, VLOOKUP benotzt just e genau Match mat dem Lookup_value. Wann et zwou oder méi Wäerter an der éischter Späuer vum Table_array entsprécht, déi mat dem Lookup value passen, gëtt deen éischte Wäert fonnt. Wann e genee Match net fonnt ginn ass, gëtt en # N / A Fehler zréckgezunn.
An dësem Tutorial, well mir nei spezifesch Informatiounen iwwer e bestëmmten Hardwareartikel kucken, wäerte mir Range_lookup egal wéi Falsch setzen .
Tutorial Schrëtt
- Klickt op der Range_lookup Zeil am Dialogbox
- Gidd d'Wuert falsch an dëser Linn fir ze weisen datt mir VLOOKUP fir e genau Match fir d'Daten zeréckgitt, déi mir sicht
- Klickt op OK fir dës Formulairen a fëllt Dialogbox
- Well mir d'Kritëschekontrollkräfte an d'Zelle D2 net erakomm sinn, ass et an der Zelle E2 en # N / A-Fehler
- Dëse Feeler gëtt korrigéiert wann mer de Lookup Criterien am läschte Schrëtt vun der Tutorial ginn
09 vun 10
Fichieren der Passwuert Formula mat der Fill Handle
D'Lookup Formel soll d' Donnéeë vun e puer Späicher vun der Datebank erofhuelen.
Fir dat ze maachen, muss d'Lookupformel an all de Felder wunnen, aus denen mir Informatioun wënschen.
An dësem Tutorial wëlle mir datt d'Daten aus Säulen 2, 3 an 4 vun der Datentabelle réckgängeg sinn - dat ass de Präis, d'Teilsnummer an den Numm vum Supplier wann mir en Deelpartner wéi den Lookup_value erliewen.
Well d'Donnéeën an engem normale Muster am Arbeitsblat gezeechent goufen , kënne mir d'Zuchformel an der Zelle E2 op d'Zellen F2 a G2 kopieieren.
Wann d'Formule kopéiert gëtt, aktualiséiert Excel den relativen Zellbeitrag an der COLUMN-Funktion (B1) fir d'nei Standort vun der Formel ze reflektéieren.
Och Excel verännert den absoluten Zuel-Referenz $ D $ 2 an de benannte Sortiment Tabelle wéi d'Formel gëtt kopéiert.
Et gëtt méi wéi ee Wee fir d'Daten an Excel ze kopéieren, awer wahrscheinlech ass de einfachste Wee duerch d' Fill Handle .
Tutorial Schrëtt
- Klickt op Zelle E2 - woubäi d'Lookup Formel läit - fir dat d'aktive Zelle maachen
- Gitt de Mauszeiger iwwert de schwaarze Quadrat an der rechter Säit riets. De Zeecher äntweren an e Plus-Schild " + " - dat ass de Fëllgrëff
- Klickt op déi lénks Maustast an zéien d'Fill Griff op d'Zelle G2
- Verréckt d'Maustast an d'Zuel F3 sollt d'zweedimensional Lookup Formel enthalen
- Wann et richteg ass, kënnen d'Zellen F2 a G2 elo och den # N / A-Fehler enthält, deen an der Zelle E2 ass
10 vun 10
D'Passwuert Criteria eranhuelen
Sidd d'Reesformuléierung op déi néideg Zellen kopéiert ginn , kann et benotzt ginn fir Informatioun aus der Datentabelle ze kréien.
Gitt hei den Numm vum Element deen Dir wëllt an d'Lookup_value Zelle eraweren (D2) a dréckt d'ENTER-Taste op der Tastatur.
Eemol gemaach, all Zelle mat der Passungsformuléierung sollt en anere Dateschutz iwwer de Hardwareerart sinn, deen Dir sicht.
Tutorial Schrëtt
- Klickt op Zelle D2 am Aarbechtsblat
- Type Widget an D2 D2 a dréckt d' ENTER- Taste op der Tastatur
- Déi folgend Informatiounen sollen an Zellen E2 op G2 sinn:
- E2 - $ 14,76 - de Präiss vun engem Widget
- F2 - PN-98769 - De Startnummer fir e Widget
- G2 - Widgets Inc. - de Numm vum Supplier fir Widgets
- Test de VLOOKUP -Array Formel weider duerch den Typ vum Numm vun aneren Deeler an d'Zelle D2 an d'Observatioun vu Resultater vun Zellen E2 bis G2
Wann eng Fehlermeldung wéi #REF! an Zellen E2, F2 oder G2. Dës Lëscht vu VLOOKUP Fehlermeldung kann Iech hëllefen, festzeleeën wou de Problem läit.