Billi tgħaqqad il-funzjoni VLOOKUP ta 'Excel mal- funzjoni COLUMN nistgħu noħolqu formola ta' riċerka li tippermettilek tirritorna valuri multipli minn ringiela waħda ta ' database jew tabella ta' data.
Fl-eżempju muri fuq l-immaġni ta 'hawn fuq, il-formola ta' riċerka tagħmilha faċli li l-valuri kollha jiġu rritornati - bħal prezz, numru ta 'parti u fornitur - relatati ma' diversi biċċiet ta 'hardware.
01 ta '10
Ritorn Valuri Miftuħ b'VLOOKUP Excel
Wara l-passi elenkati hawn taħt toħloq il-formola tal-istħarriġ li tidher fl-immaġni ta 'hawn fuq li se tirritorna valuri multipli minn rekord tad-dejta wieħed.
Il-formula ta 'lookup teħtieġ li l-funzjoni COLUMN tkun imnaddfa ġewwa VLOOKUP.
L-immarkar ta 'funzjoni jinvolvi d-dħul fit-tieni funzjoni bħala wieħed mill- argumenti għall-ewwel funzjoni.
F'dan il-tutorja, il-funzjoni COLUMN se tiddaħħal bħala l-argument tan-numru tal-indiċi tal-kolonna għal VLOOKUP.
L-aħħar pass fit-tutorja jinvolvi l-ikkopjar tal-formula ta 'lookup għal kolonni addizzjonali sabiex jerġgħu jiksbu valuri addizzjonali għall-parti magħżula.
Kontenut tat-Tutorial
- Id-dħul tad-Data Tutorja
- Il-Ħolqien ta 'Medda Msejħa għat-Tabella tad-Dejta
- Bidu tal-Funzjoni VLOOKUP
- Id-dħul tal-Lookup Value L-argument li juża r-Referenzi taċ-Ċelloli Assolutivi
- Dħul fl-Argument tal-Array Tabella
- Id-dħul tal-Funzjoni NUMRATA TAL-KOLONNA
- Tlestija tal-Funzjoni VLOOKUP
- Ikkopja l-Lookup Formula bil-Imla Imla
- Irkupru tad-Data bil-Formula Lookup
02 ta '10
Daħħal id-Data tat-Tutorial
L-ewwel pass fit-tutorja huwa li ddaħħal id- data f'kalkolu ta 'l-Excel.
Sabiex issegwi l-passi fit-tutorja, daħħal id-dejta murija fid-dehra ta 'hawn fuq fiċ- ċelloli li ġejjin.
- Daħħal l-ogħla medda ta 'data fiċ-ċelloli D1 sa G1
- Daħħal it-tieni medda fiċ-ċelloli D4 sa G10
Il-kriterji tat-tfittxija u l-formola ta 'tfittxija maħluqa matul dan it-tutorja se jiddaħħlu fir- ringiela 2 tal-worksheet.
It-tutorja ma tinkludix il-formatting li jidher fl-immaġni, iżda dan ma jaffettwax kif taħdem il-formula ta 'lookup.
L-informazzjoni dwar għażliet ta 'formattjar simili għal dawk li jidhru hawn fuq hija disponibbli f'dan il -Tutorial Bażiku ta' Formattjar ta 'Excel .
Passi Tutoriali
- Daħħal id-dejta kif tidher fid-dehra ta 'hawn fuq fiċ-ċelloli D1 sa G10
03 ta '10
Il-Ħolqien ta 'Medda Msejħa għat-Tabella tad-Dejta
Firxa msemmija hija mod faċli biex tirreferi għal firxa ta 'data f'formula. Minflok ittajpjar fir- referenzi taċ - ċellula għad-data, tista 'sempliċement tip l-isem tal-firxa.
It-tieni vantaġġ għall-użu ta 'medda msemmija huwa li r-referenzi taċ-ċelluli għal din il-firxa qatt ma jinbidlu anki meta l-formola tiġi kkupjata lil ċelloli oħra fil-worksheet.
L-ismijiet tal-medda huma, għalhekk, alternattiva għall-użu ta 'referenzi taċ-ċelloli assoluti biex jipprevjenu l-iżbalji meta jikkopjaw il-formuli.
Nota: L-isem tal-firxa ma jinkludix l-intestaturi jew l- ismijiet tal-qasam għad-data (ringiela 4) iżda biss id-dejta nnifisha.
Passi Tutoriali
- Iċċelebra ċ-ċelluli D5 sa G10 fil-worksheet biex tagħżelhom
- Ikklikkja fuq il- Kaxxa tal-Isem li tinsab fuq il-kolonna A
- Tip "Tabella" (bla kwotazzjonijiet) fil-Kaxxa ta 'l-Isem
- Agħfas il-buttuna ENTER fuq it-tastiera
- Iċ-Ċelloli D5 sa G10 issa għandhom l-isem tal-firxa ta '"Tabella". Aħna se nużaw l-isem għall-argument tat- tabella VLOOKUP iktar tard fit-tutorja
04 ta '10
Ftuħ tal-Kaxxa ta 'Dialog VLOOKUP
Għalkemm huwa possibbli li sempliċement ittajpja l-formola ta 'riċerka tagħna direttament f'ċellula f'folja tax-xogħol, ħafna nies isibuha diffiċli biex iżommu s- sintassi dritt - speċjalment għal formula kumplessa bħal dik li qed tuża f'dan it-tutorial.
Alternattiva, f'dan il-każ, hija li tuża l- kaxxa ta 'dialog VLOOKUP. Kważi l- funzjonijiet kollha ta 'Excel għandhom kaxxa ta' dialog li tippermettilek tidħol f'kull waħda mill-argumenti tal-funzjoni fuq linja separata.
Passi Tutoriali
- Ikklikkja fuq iċ-ċellula E2 tal- worksheet - il-post fejn se jintwerew ir-riżultati tal-formola tat-tiftix b'żewġ dimensjonijiet
- Ikklikkja fuq it-tab tal- Formuli taż- żigarella
- Ikklikkja fuq l-Għażla ta ' Riċerka u Referenza fiċ-żigarella biex tiftaħ il-lista tal-qtar tal-funzjoni
- Ikklikkja fuq VLOOKUP fil-lista biex tiftaħ il-kaxxa ta 'dialog tal-funzjoni
05 ta '10
Id-dħul tal-Lookup Value L-argument li juża r-Referenzi taċ-Ċelloli Assolutivi
Normalment, il- valur Look Looks jaqbel mal- qasam tad-dejta fl-ewwel kolonna tat-tabella tad-dejta.
Fl-eżempju tagħna, il- valur tal-istħarriġ jirreferi għall-isem tal-parti tal-ħardwer li dwarha rridu nsibu informazzjoni.
It-tipi permissibbli ta ' data għall- valur tal-istħarriġ huma:
- data tat-test
- valur loġiku (TRUE jew FALSE biss)
- numru
- referenza taċ - ċellola għal valur fil-worksheet
F'dan l-eżempju, se nidħlu fir-referenza taċ-ċellula fejn se tkun tinsab l-isem tal-parti - ċellula D2.
Referenzi taċ-Ċelloli Assoluti
Fi stadju aktar tard fit-tutorja, aħna se niċċekkjaw il-formola ta 'riċerka fl-E2 taċ-ċelluli għaċ-ċelloli F2 u G2.
Normalment, meta l-formuli jiġu kkupjati f'Excel, ir-referenzi taċ-ċelluli jinbidlu biex jirriflettu l-post il-ġdid tagħhom.
Jekk jiġri dan, D2 - ir-referenza taċ-ċellula għall- valur tat- tfittxija - tinbidel hekk kif tiġi kkupjata l-formula billi toħloq żbalji fiċ-ċelloli F2 u G2.
Biex tevita l-iżbalji, aħna ser inbiddlu r-referenza taċ-ċellola D2 f'referenza taċ-ċellola assoluta .
Ir-referenzi taċ-ċelloli assoluta ma jinbidlux meta l-formuli jiġu kkupjati.
Referenzi taċ-ċelloli assoluta jinħolqu billi tagħfas il-buttuna F4 fuq it-tastiera. B'hekk jiżdiedu sinjali tad-dollaru madwar ir-referenza taċ-ċellola bħal $ D $ 2
Passi Tutoriali
- Ikklikkja fuq il-linja Lookup_value fil- kaxxa ta 'dialog
- Ikklikkja fuq iċ-ċellula D2 biex iżżid din ir-referenza taċ-ċellola għal-linja ta ' lookup_value . Din hija ċ-ċellula fejn se nittrezza l-isem tal-parti li qed tfittex informazzjoni
- Mingħajr ma ċċaqlaq il-punt ta 'inserzjoni, agħfas il-buttuna F4 fuq it-tastiera biex taqleb D2 fir-referenza taċ-ċellola assoluta $ D $ 2
- Ħalli l-kaxxa ta 'dialog tal-funzjoni VLOOKUP miftuħa għall-pass li jmiss fit-tutorja
06 ta '10
Dħul fl-Argument tal-Array Tabella
L-array tal-mejda huwa t- tabella tad-data li t-tiftix tal-formola tal-mistoqsija ssib l-informazzjoni li rridu.
L-array tal-mejda għandu jkun fih mill-inqas żewġ kolonni ta ' data .
- l-ewwel kolonna fiha l-argument tal-valur tal-istħarriġ (pass preċedenti fit-tutorja)
- It-tieni, u kwalunkwe kolonna addizzjonali, se jiġu mfittxija bil-formola ta 'lookup biex issib l-informazzjoni li npeċifikaw.
L-argument tal-firxa tal-mejda għandu jiddaħħal jew bħala medda li fiha r -referenzi taċ - ċelluli għat-tabella tad-dejta jew bħala isem ta ' firxa .
Għal dan l-eżempju, se nużaw l-isem tal-firxa maħluq fil-pass 3 tat-tutorja.
Passi Tutoriali
- Ikklikkja fuq il-linja tal- mejda fuq il- kaxxa ta 'dialog
- Tip "Tabella" (mingħajr kwotazzjonijiet) biex tidħol l-isem tal-firxa għal dan l-argument
- Ħalli l-kaxxa ta 'dialog tal-funzjoni VLOOKUP miftuħa għall-pass li jmiss fit-tutorja
07 ta '10
Nesting tal-Funzjoni tal-KOLONNA
Normalment VLOOKUP jirritorna biss id-dejta minn kolonna waħda ta 'tabella tad-dejta u din il-kolonna hija stabbilita mill-argument tan-numru tal-indiċi tal-kolonna .
F'dan l-eżempju, madankollu, għandna tliet kolonni li nixtiequ nirritornaw id-data minn hekk għandna bżonn mod biex inbiddlu faċilment in-numru ta 'l-indiċi tal-kolonna mingħajr ma nedukaw il-formula ta' lookup tagħna.
Dan huwa fejn tidħol il-funzjoni COLUMN. Permezz ta 'dħul bħala l-argument tan-numru ta' l-indiċi tal-kolonna , din se tinbidel hekk kif il-formula ta 'lookup tiġi kkupjata minn ċellola D2 għaċ-ċelloli E2 u F2 aktar tard fit-tutorja.
Funzjonijiet tan-Nesting
Il-funzjoni COLUMN, għalhekk, taġixxi bħala l- argument tan-numru tal-indiċi tal- kolonna VLOOKUP.
Dan jitwettaq billi tinżamm il-funzjoni COLUMN ġewwa VLOOKUP fil-linja Col_index_num tal-kaxxa tad-djalogu.
Dħul fil-Funzjoni tal-KOLONNA Manwalment
Meta l-funzjonijiet tat-tbejjit, Excel ma jippermettux li niftħu l- kaxxa tad - djalogu tat-tieni funzjoni biex tidħol l-argumenti tagħha.
Il-funzjoni COLUMN, għalhekk, għandha tiddaħħal manwalment fil-linja Col_index_num .
Il-funzjoni COLUMN għandha biss argument wieħed - l-argument ta ' Referenza li huwa referenza taċ-ċellula.
L-għażla tal-Argument ta ' Referenza tal-Funzjoni tal-COLUMN
Ix-xogħol tal-funzjoni tal-COLUMN huwa li jreġġa 'lura n-numru tal-kolonna mogħtija bħala l-argument ta' Referenza .
Fi kliem ieħor, tikkonverti l-ittra tal-kolonna f'numru bil-kolonna A tkun l-ewwel kolonna, il-kolonna B it-tieni u l-bqija.
Peress li l-ewwel qasam tad-data li rridu lura huwa l-prezz tal-oġġett - li jinsab fil-kolonna tnejn tat-tabella tad-dejta - nistgħu nagħżlu ċ-ċellola ta 'referenza għal kwalunkwe ċellula fil-kolonna B bħala l-Argument ta' Referenza sabiex tikseb in-numru 2 għal l-argument Col_index_num .
Passi Tutoriali
- Fil-kaxxa ta 'dialog tal-funzjoni VLOOKUP, ikklikkja fuq il-linja Col_index_num
- Ittajpja l- kolonna ta 'l- isem tal-funzjoni segwita minn parentesi tonda miftuħa " ( "
- Ikklikkja fuq iċ-ċellula B1 fil- worksheet biex tidħol fir-referenza taċ-ċellola bħala l-argument ta ' Referenza
- Tip ta 'kategorija tal-għeluq tal-għeluq " ) " biex timla l-funzjoni COLUMN
- Ħalli l-kaxxa ta 'dialog tal-funzjoni VLOOKUP miftuħa għall-pass li jmiss fit-tutorja
08 ta '10
Dħul fl-Argument tar-Lookup tal-Fattur VLOOKUP
L-argument Range_lookup ta 'VLOOKUP huwa valur loġiku (TRUE jew FALSE biss) li jindika jekk tridx VLOOKUP biex issib taqbila eżatta jew approssimattiva mal-Lookup_value.
- Jekk TRUE jew jekk dan l- argument jitħalla barra, VLOOKUP jirritorna jew taqbila eżatta mal-Lookup_value, jew, jekk ma tinstabx taqbila eżatta, VLOOKUP jirritorna l-akbar valur li jmiss. Għall-formula biex tagħmel dan, id-data fl-ewwel kolonna tat-Tabella_array trid tiġi magħżula f'ordni axxendenti .
- Jekk FALSE, VLOOKUP juża biss taqbila eżatta għall-Lookup_value. Jekk hemm tnejn jew aktar valuri fl-ewwel kolonna tat-Tabella_daq li jaqblu mal-valur tal-istħarriġ, l-ewwel valur misjub huwa użat. Jekk ma tinstabx taqbila eżatta, jingħata żball # N / A.
F'dan il-tutorja, peress li qed infittxu informazzjoni speċifika dwar oġġett ta 'hardware partikulari, aħna nwaqqfu Range_lookup ugwali għal False .
Passi Tutoriali
- Ikklikkja fuq il-linja Range_lookup fil- kaxxa ta 'dialog
- Tip il-kelma False f'din il-linja biex tindika li rridu VLOOKUP li jirritorna taqbila eżatta għad-dejta li qed infittxu
- Ikklikkja OK biex tikkompleta l-formola tal-mistoqsija u l-kaxxa ta 'dialog
- Minħabba li għadna ma ddaħħlux il-kriterji tat-tfittxija fiċ-ċellola D2, ikun hemm żball # N / A fiċ-ċellola E2
- Dan l-iżball jiġi kkoreġut meta se nżidu l-kriterji tat-tfittxija fl-aħħar pass tat-tutorja
09 ta '10
Ikkopja l-Lookup Formula bil-Imla Imla
Il-formola ta 'investigazzjoni hija intiża biex tikseb data minn kolonni multipli tat-tabella tad-dejta f'ħin wieħed.
Biex tagħmel dan, il-formola ta 'lookup għandha tirrisjedi fl-oqsma kollha minn fejn irridu informazzjoni.
F'dan il-tutorja irridu li tirkupra d-data mill-kolonni 2, 3 u 4 tat-tabella tad-data - jiġifieri l-prezz, in-numru tal-parti u l-isem tal-fornitur meta nidħlu isem ta 'parti bħala Lookup_value.
Peress li d-dejta hija mqassma b'mod regolari fil- worksheet , nistgħu nittemplu l-formola tal-istħarriġ f'ċellula E2 għaċ-ċelloli F2 u G2.
Hekk kif il-formola tiġi kkupjata, Excel ser jaġġorna r -referenza taċ-ċellola relattiva fil-funzjoni COLUMN (B1) biex jirrifletti l-post il-ġdid tal-formula.
Barra minn hekk, Excel ma jbiddilx ir -referenza taċ-ċellola assoluta ta ' $ D $ 2 u t- Tabella tal- firxa msemmija hekk kif tiġi kkopjata l-formula.
Hemm aktar minn mod wieħed biex tikkopja data f'Excel, imma probabbilment l-eħfef mod huwa billi tuża l- Imla Immaniġġja .
Passi Tutoriali
- Ikklikkja fuq iċ-ċellula E2 - fejn tinsab il-formula ta 'lookup - biex tagħmlu ċ-ċellula attiva
- Poġġi l-pointer tal-maws fuq il-kwadru iswed fir-rokna tal-lemin t'isfel. L-indikatur jinbidel f'sinjal plus " + " - dan huwa l-manku tal-mili
- Ikklikkja l-buttuna tal-maws fuq ix-xellug u ġġib il-manku tal-mili tul iċ-ċella G2
- Iħelsu l-buttuna tal-ġurdien u ċ-ċellula F3 għandu jkun fiha l-formula tat-tiftix b'żewġ dimensjonijiet
- Jekk isir b'mod korrett, iċ-ċelloli F2 u G2 issa għandhom ukoll fihom l-iżball # N / A li huwa preżenti fiċ-ċellola E2
10 ta '10
Id-dħul tal-Kriterji ta 'Ħarsa
Ladarba l-formula ta 'lookup tkun ġiet ikkupjata fiċ-ċelloli meħtieġa tista' tintuża biex tinġabar l-informazzjoni mit-tabella tad-dejta.
Biex tagħmel dan, ikteb l-isem tal-oġġett li tixtieq tirkupra fiċ-ċellola Lookup_value (D2) u agħfas il-buttuna ENTER fuq it-tastiera.
Ladarba jsir, kull ċellula li fiha l-formula ta 'lookup għandu jkun fiha biċċa informazzjoni differenti dwar l-oġġett ta' hardware li qed tfittex.
Passi Tutoriali
- Ikklikkja fuq iċ-ċellula D2 fil-worksheet
- Tip Widget fis-ċellola D2 u agħfas il-buttuna ENTER fuq it-tastiera
- L-informazzjoni li ġejja għandha tintwera fiċ-ċelloli E2 sa G2:
- E2 - $ 14.76 - il-prezz ta 'widget
- F2 - PN-98769 - in-numru tal-parti għal widget
- G2 - Widgets Inc - l-isem tal-fornitur għal widgets
- Ittestja l-formola tal-firxa VLOOKUP aktar billi ttajpja l-isem ta 'partijiet oħra f'ċellula D2 u tħares ir-riżultati fiċ-ċelloli E2 sa G2
Jekk messaġġ ta 'żball bħal #REF! tidher fiċ-ċelloli E2, F2 jew G2, din il-lista ta 'messaġġi ta' żball VLOOKUP jistgħu jgħinuk tiddetermina fejn tinsab il-problema.