Kombinante la funkcion de VLOOKUP de Excel kun la funkcio de COLUMN, ni povas krei serĉilon, kiu permesas al vi redoni multnombrajn valorojn de unu vico de datumbazo aŭ tabelo de datumoj.
En la ekzemplo montrita en la supra bildo, la serĉa formulo faciligas redoni ĉiujn valorojn - kiel prezo, parto-numero kaj provizanto - rilataj al diversaj pecoj de aparataro.
01 de 10
Reiru Multoblajn Valorojn kun Excel VLOOKUP
Sekvante la paŝojn listigitajn sube kreas la serĉa formulo vidita en la supra bildo, kiu redonos multajn valorojn de ununura datuma rekordo.
La formulo de serĉo postulas, ke la funkcio de COLUMN estu nestita ene de VLOOKUP.
Nestanta funkcion implikas eniri la duan funkcion kiel unu el la argumentoj por la unua funkcio.
En ĉi tiu lernilo, la funkcio de COLUMN estos enmetita kiel la kolumna nombra argumento por VLOOKUP.
La lasta paŝo en la lernilo implikas kopii la serĉan formulon al suplementaj kolumnoj por rekuperi aldonajn valorojn por la elektita parto.
Tutorial Enhavo
- Enirante la Tutorial Data
- Kreante nomatan gamon por la Datuma Tabelo
- Komencante la funkcion VLOOKUP
- Enirante la Serĉa Valoro-Argumento uzante Absolute Cell-Referencoj
- Enirante la Tabelo Tabelo Argumento
- Enirante la Nestan COLUMN-Funkcion
- Plenumi la funkcion VLOOKUP
- Kopiante la Serĉo-Formulon kun la Plenigu Handle
- Retrovanta Datumoj kun la Serĉa Formulo
02 de 10
Enmetu la Tutorial Data
La unua paŝo en la lernilo estas eniri la datumojn en skatolon de Excel.
Por sekvi la paŝojn en la lernilo, eniru la datumojn montritajn en la supra bildo en la sekvajn ĉelojn .
- Enmetu la supran gamon de datumoj en ĉelojn D1 al G1
- Eniru la duan gamon en ĉelojn D4 al G10
La serĉaj kriterioj kaj la serĉa formulo kreita dum ĉi tiu lernilo estos enmetitaj en la vico 2 de la foliaro.
La lernilo ne inkluzivas la formatadon viditan en la bildo, sed ĉi tio ne influos kiel funkcias la formulo de serĉo.
Informoj pri formatigaj opcioj similaj al tiuj, kiuj estas viditaj supre, estas disponeblaj en ĉi tiu Baza Excel Formatiga Lernilo .
Tutorial Steps
- Enmetu la datumojn kiel en la supra bildo en ĉelojn D1 ĝis G10
03 de 10
Kreante nomatan gamon por la Datuma Tabelo
Enoficigita gamo estas facila maniero por raporti al gamo da datumoj en formulo. Prefere ol tajpi en la ĉelaj referencoj por la datumoj, vi simple povas tajpi la nomon de la rango.
Dua avantaĝo por uzado de nomata gamo estas, ke la ĉelaj referencoj por ĉi tiu rango neniam ŝanĝiĝas eĉ kiam la formulo estas kopiita al aliaj ĉeloj en la foliaro.
La nomoj de gamoj estas, sekve, alternativo al uzi absolutajn celajn referencojn por malhelpi erarojn kopiante formulojn.
Noto: La rango de nomo ne inkluzivas la rubrikojn aŭ kampojn nomojn por la datumoj (vico 4) sed nur la datumoj mem.
Tutorial Steps
- Emfazu ĉelojn D5 al G10 en la foliaro por elekti ilin
- Alklaku la Nomo-Skatolon lokita super kolumno A
- Tajpu "Tablon" (sen citaĵoj) en la Nomo-Skatolo
- Premu la ENTER- ŝlosilon sur la klavaron
- Ĉeloj D5 ĝis G10 nun havas la nomumon de "Tablo". Ni uzos la nomon por la VLOOKUP- tabelo-tabelo- argumento poste en la lernilo
04 de 10
Malfermante la dialogon de VLOOKUP-Skatolo
Kvankam ĝi nur povas tajpi nian serĉon de formulo rekte en ĉelon en folio de laboro, multaj homoj malfacilas konservi la sintakson rekte - precipe por kompleksa formulo kiel ekzemple, kiun ni uzas en ĉi tiu lernilo.
Alternativa, en ĉi tiu kazo, estas uzi la dialogon VLOOKUP. Preskaŭ ĉiuj funkcioj de Excel havas dialogon, kiu permesas al vi eniri ĉiun el la argumentoj de la funkcio sur apartaj linioj.
Tutorial Steps
- Alklaku la ĉelon E2 de la folio de laboro - la loko kie montriĝos la rezultoj de la du-dimensia serĉo
- Alklaku la pestaña Formulo de la rubando
- Alklaku la opcion de Serĉo kaj Referenco en la rubando por malfermi la funkcion malplenigi liston
- Alklaku VLOOKUP en la listo por malfermi la dialogon de la funkcio
05 de 10
Enirante la Serĉa Valoro-Argumento uzante Absolute Cell-Referencoj
Kutime, la serĉa valoro kongruas kampon de datumoj en la unua kolumno de la datuma tablo.
En nia ekzemplo, la serĉa valoro rilatas al la nomo de la aparataro, pri kiu ni volas trovi informon.
La permesindaj tipoj de datumoj por la serĉa valoro estas:
- teksto datumoj
- logika valoro (TRUE aŭ FALSE nur)
- nombro
- Ĉela referenco al valoro en la foliaro
En ĉi tiu ekzemplo, ni eniros la ĉelan referencon al kie la nomo nomiĝas - ĉelo D2.
Absoluta Ĉela Referencoj
En posta paŝo en la lernilo, ni kopii la serĉa formulo en la ĉelo E2 al ĉeloj F2 kaj G2.
Kutime, kiam formuloj estas kopiitaj en Excel, ĉifraj ŝanĝoj ŝanĝas por reflekti sian novan lokon.
Se tio okazas, D2 - la ĉela referenco por la serĉa valoro - ŝanĝos kiam la formulo estas kopiita kreante erarojn en ĉeloj F2 kaj G2.
Por malebligi la erarojn, ni konvertos la ĉel-referencon D2 en absolutan ĉelan referencon .
Absoltaj ĉeloj ne ŝanĝas kiam formuloj estas kopiitaj.
Absolutaj ĉeloj estas kreitaj premante la F4- ŝlosilon sur la klavaro. Farante do aldonas dolarajn signojn ĉirkaŭ la ĉela referenco kiel $ D $ 2
Tutorial Steps
- Alklaku la serĉilon en la dialogujo
- Alklaku la ĉelon D2 por aldoni ĉi tiun celan referencon al la serĉo-valora linio. Ĉi tiu estas la ĉelo, kie ni tajpu la partan nomon pri kiu ni serĉas informon
- Sen movi la enmetan punkton, premu la F4- ŝlosilon sur la klavaron por konverti D2 en la absolutan ĉelan referencon $ D $ 2
- Lasu la dialogon de la funkcio VLOOKUP malfermita por la sekva paŝo en la lernilo
06 de 10
Enirante la Tabelo Tabelo Argumento
La tabelo estas la tabelo de datumoj, kiujn la serĉo formulas serĉi la informon, kiun ni volas.
La tablo tabelo devas enhavi almenaŭ du kolumnojn da datumoj .
- la unua kolumno enhavas la serĉan valoron argumenton (antaŭa paŝo en la lernilo)
- la dua, kaj iuj kromaj kolumnoj, serĉos la formularon de serĉo por trovi la informon, kiun ni specifas.
La tabelo-tabelo argumento devas esti enmetita kiel aŭ gamo enhavanta la ĉelajn referencojn por la datumablaro aŭ kiel rango de nomo .
Por ĉi tiu ekzemplo, ni uzos rangon nomitan kreitan en la paŝo 3 de la lernilo.
Tutorial Steps
- Alklaku la tabulon de la tablo en la dialogujo
- Tajpu "Tablon" (sen citaĵoj) por eniri la nomumon por ĉi tiu argumento
- Lasu la dialogon de la funkcio VLOOKUP malfermita por la sekva paŝo en la lernilo
07 de 10
Nestanta la COLUMN-Funkcion
Kutime VLOOKUP nur redonas datumojn de unu kolumno de datuma tabelo kaj ĉi tiu kolumno estas difinita per la kalkulata nombra argumento.
En ĉi tiu ekzemplo, tamen, ni havas tri kolumnojn, kiujn ni deziras redoni datumojn, tial ni bezonas manieron por facile ŝanĝi la kolumnan indekson sen redakti nian serĉon.
Jen la funkcio de COLUMN. Enirante ĝin kiel la kalkulson-nombra argumento, ĝi ŝanĝiĝos, kiam la serĉo de la formulo estas kopiita de ĉelo D2 al ĉeloj E2 kaj F2 poste en la lernilo.
Nestigaj Funkcioj
La funkcio de COLUMN, sekve, agas kiel la argumento de numero de kolumno de VLOOKUP.
Ĉi tio plenumas nombrante la funkcion COLUMN ene de VLOOKUP en la linio Col_index_num de la dialogo.
Enirante la COLUMN-Funkcion Manually
Al la nestado de funkcioj, Excel ne permesas al ni malfermi la dialogon de la dua funkcio por eniri ĝiajn argumentojn.
La funkcio de COLUMN, do, devas esti enmetita permane en la linio Col_index_num .
La funkcio de COLUMN havas nur unu argumenton - la Referenca argumento, kiu estas ĉelo-referenco.
Elektante la Argumentan Argumenton de la COLUMN-Funkcio
La funkcio de la funkcio de COLUMN estas redoni la nombron de la kolumno donita kiel la Referenca argumento.
Alivorte, ĝi konvertas la kolumnan literon al numero kun kolumno A estante la unua kolumno, kolumno B la dua kaj tiel plu.
Pro tio ke la unua kampo de datumoj, kiujn ni volas reveni, estas la prezo de la ero - kiu estas en kolumno du el la datumaro - ni povas elekti la ĉelan referencon por iu ajn ĉelo en kolumno B kiel la Referenca Argumento por ricevi la numeron 2 por la argumento Col_index_num .
Tutorial Steps
- En la dialogo de la funkcio VLOOKUP, klaku sur la linio Col_index_num
- Tajpu la funkcion- kolumnon sekvitan per malferma ĉirkaŭa krampo " ( "
- Alklaku la ĉelon B1 en la folio de folioj por eniri tiun ĉelan referencon kiel la Reklara argumento
- Tajpu ferma krampo " ) " por kompletigi la funkcion COLUMN
- Lasu la dialogon de la funkcio VLOOKUP malfermita por la sekva paŝo en la lernilo
08 de 10
Enirante la VLOOKUP Range Lookup Argumento
VLOOKUP's Range_lookup argumento estas logika valoro (TRUE aŭ FALSE nur), kiu indikas ĉu vi volas VLOOKUP por trovi precizan aŭ proksimuman matĉon al la Lookup_value.
- Se TRUE aŭ se ĉi tiu argumento estas preterlasita, VLOOKUP revenas aŭ ĝusta matĉo al la Serĉo-valoro, aŭ se ĝusta rezulto ne troviĝas, VLOOKUP redonas la sekvan plej grandan valoron. Por la formulo fari ĉi tion, la datumoj en la unua kolumno de Table_array devas esti ordo en suprenira ordo .
- Se FALSE, VLOOKUP nur uzos ĝustan matĉon al la Lookup_value. Se estas du aŭ pli da valoroj en la unua kolumno de Table_array, kiu kongruas kun la serĉo, la unua valoro troveblas. Se ĝusta matĉo ne estas trovita, nombro de N / A estas redonita.
En ĉi tiu lernilo, ĉar ni serĉas specifajn informojn pri speciala aparataro, ni starigos Range_lookup egala al Falsa .
Tutorial Steps
- Alklaku la gamon Range_lookup en la dialogo
- Tajpu la vorton Falsa en ĉi tiu linio por indiki, ke ni volas VLOOKUP reveni precizan matĉon por la datumoj, kiujn ni serĉas
- Alklaku OK por kompletigi la serĉan formulon kaj fermi dialogan skatolon
- Pro tio ke ni ankoraŭ ne eniris la serĉojn de kriterioj en ĉelon D2, nombro estos nombro N / A en la ĉelo E2
- Ĉi tiu eraro estos korektita kiam ni aldonos la serĉajn kriteriojn en la lasta paŝo de la lernilo
09 de 10
Kopiante la Serĉo-Formulon kun la Plenigu Handle
La serĉo-formulo intencas rekuperi datumojn de pluraj kolumnoj de la datuma tablo samtempe.
Por tio fari, la formulo de serĉo devas loĝi en ĉiuj kampoj, de kiuj ni deziras informojn.
En ĉi tiu lernilo ni volas ĝin rekuperi datumojn de kolumnoj 2, 3 kaj 4 el la datumaro - tio estas la prezo, la nombro kaj la nomo de la provizanto, kiam ni eniras nomon kiel la Serĉo-valoro.
Pro tio ke la datumoj estas metitaj en regula ŝablono en la folio de laboro , ni povas kopii la serĉon de formulo en ĉelo E2 al ĉeloj F2 kaj G2.
Kiel la formulo estas kopiita, Excel ĝisdatigos la relativan ĉelan referencon en la funkcio COLUMN (B1) por reflekti la novan lokon de la formulo.
Ankaŭ, Excel ne ŝanĝas absolutan ĉelan referencon $ D $ 2 kaj la nomata gamo Tablo kiel la formulo estas kopiita.
Ekzistas pli ol unu maniero kopii datumojn en Excel, sed verŝajne la plej facila maniero estas per uzado de la Plenigu Handle .
Tutorial Steps
- Alklaku la ĉelon E2 - kie troviĝas la serĉo-formulo - por fari ĝin la aktiva ĉelo
- Metu la muson-punktilon super la nigra kvadrato en la malsupra dekstra angulo. La puntero ŝanĝos al plusa signo " + " - jen la plenigilo
- Alklaku la butonon de la maldekstra muso kaj trenu la plenan tenilon al la ĉelo G2
- Liberigu la muson-butonon kaj la ĉelon F3 devus enhavi la du-dimensian serĉan formulon
- Se farita ĝuste, ĉeloj F2 kaj G2 nun devus enhavi la nombro N / A, kiu ĉeestas en la ĉelo E2
10 el 10
Enirante la Serĉajn Kriteriojn
Fojo kiun la formulo de serĉo estis kopiita al la ĉeloj postulitaj, ĝi povas uzi por rekuperi informon de la tabulo de datumoj.
Por fari tion, tajpu la nomon de la ero, kiun vi volas ricevi en la ĉambro Lookup_value (D2) kaj premu la ENTER-ŝlosilon sur la klavaron.
Fojo farita, ĉiu ĉelo enhavanta la serĉan formulon devus enhavi malsaman datumon pri la aparataro, kiun vi serĉas.
Tutorial Steps
- Alklaku la ĉelon D2 en la labortablo
- Tajpu fenestraĵon en ĉelon D2 kaj premu la ENTER- ŝlosilon sur la klavaron
- La sekvaj informoj devas esti montritaj en ĉeloj E2 ĝis G2:
- E2 - $ 14.76 - la prezo de fenestraĵo
- F2 - PN-98769 - la parto-nombro por fenestraĵo
- G2 - Widgets Inc. - La nomo de la provizanto por widgets
- Provu la formulon de VLOOKUP-tabelo pli tajpante la nomon de aliaj partoj en ĉelon D2 kaj observante la rezultojn en ĉeloj E2 ĝis G2
Se erara mesaĝo kiel #REF! aperas en ĉeloj E2, F2 aŭ G2, ĉi tiu listo de VLOOKUP-eraraj mesaĝoj povas helpi vin determini, kie troviĝas la problemo.