Kombináciou programu ExcelVLOOKUP funkcia sSTĹPIK funkciu môžeme vytvoriť vzorec vyhľadávania, ktorý vám umožňuje vrátiť viac hodnôt z jedného riadku databázy alebo tabuľky údajov.
01 z 10Vráťte viac hodnôt pomocou aplikácie Excel VLOOKUP
Nasledujúcimi krokmi sa vytvorí vzorec vyhľadávania, ktorý je videný na obrázku vyššie, čím sa z jedného záznamu údajov vrátia viac hodnôt.
Formulár vyhľadávania vyžaduje, aby STĹPIK funkcia je vnorená do vnútra VLOOKUP, Nasadenie funkcie zahŕňa zadanie druhej funkcie ako jeden z argumentov pre prvú funkciu.
V tejto príručke sa STĹPIK funkcia bude zadaná ako index stĺpcov číslo argument pre VLOOKUP, Posledný krok v príručke zahŕňa kopírovanie vzoru vyhľadávania do ďalších stĺpcov, aby sa získali ďalšie hodnoty pre vybranú časť.
Zadajte výučbové údaje
Prvým krokom v príručke je zadanie údajov do pracovného hárka programu Excel. Ak chcete postupovať podľa krokov v príručke, zadajte údaje uvedené na obrázku vyššie do nasledujúcich buniek:
- Zadajte najvyšší rozsah údajov do buniek D1 na G1.
- Zadajte druhý rozsah do buniek D4 na G10.
Zobrazia sa kritériá vyhľadávania a vzorec vyhľadávania vytvorený počas tohto tutoriálu riadok 2 pracovného hárka.
Tutoriál nezahŕňa formátovanie zobrazené na obrázku, ale to neovplyvní spôsob fungovania vyhľadávacieho vzorca. Informácie o možnostiach formátovania, ktoré sú podobné tým, ktoré sú uvedené vyššie, sú k dispozícii v tomto základnom výučbe formátovania programu Excel.
03 z 10Vytvorenie pomenovaného rozsahu pre tabuľku s údajmi
Pomenovaný rozsah je jednoduchý spôsob, ako odkázať na rozsah údajov vo vzorci. Namiesto toho, aby ste zadali údaje o bunkách, môžete zadať len názov rozsahu.
Druhou výhodou pri používaní pomenovaného rozsahu je to, že referencie buniek pre tento rozsah sa nikdy nezmení ani vtedy, keď je vzorec skopírovaný do iných buniek v pracovnom hárku. Názvy rozsahu sú preto alternatívou k používaniu absolútnych odkazov na bunky, aby sa zabránilo chybám pri kopírovaní vzorcov.
Názov rozsahu neobsahuje nadpisy alebo názvy polí pre dáta (riadok 4), ale iba samotné údaje.
- Zlatý klinec buniek D5 na G10 v pracovnom hárku ich vyberte.
- Klikni na Pole Názov umiestnené vyššie stĺpec A.
- typ stôl do Pole Názov.
- Stlačte tlačidlo vstúpiť na klávesnici.
- Bunky D5 na G10 teraz má názov rozsahu stôl, Budeme používať názov pre VLOOKUP tabuľkového poľa argument neskôr v príručke.
Otvorenie dialógového okna VLOOKUP
Aj keď je možné jednoducho napísať vzorec vyhľadávania priamo do bunky v pracovnom hárku, mnohí ľudia považujú za ťažké zachovať syntax rovno - najmä pre komplexný vzorec, ako je ten, ktorý používame v tomto výučbe.
V tomto prípade je alternatívou použiť VLOOKUP dialógové okno. Takmer všetky funkcie programu Excel majú dialógové okno, ktoré umožňuje zadávať jednotlivé argumenty funkcie na samostatnom riadku.
Tutorial Kroky
- Kliknite na bunka E2 pracovného hárka - miesto, kde sa zobrazia výsledky dvojrozmerného vzorca vyhľadávania.
- Klikni na Karta Vzorce z stuha.
- Klikni na Vyhľadávanie a referencia možnosť v stuha otvorte rozbaľovaciu ponuku funkcií.
- Kliknite na VLOOKUP v zozname otvorte dialógové okno funkcie.
Zadanie argumentu vyhľadávacieho hodnotenia
Zvyčajne vyhľadať hodnota sa zhoduje s poľom údajov v prvom stĺpci tabuľky s údajmi. V našom príklade vyhľadať hodnota odkazuje na názov hardvérovej časti, o ktorej chceme nájsť informácie. Prípustné typy údajov pre vyhľadať hodnota sú textové údaje, logické hodnoty, čísla a odkazy na bunky.
Absolútne odkazy na bunky
Normálne, keď sú vzorce skopírované v programe Excel, odkazy na bunky sa zmenia tak, aby odzrkadľovali ich nové umiestnenie. Ak k tomu dôjde, D2, referencia bunky pre vyhľadať hodnota, sa zmení, keď sa vzorec skopíruje, čím sa vytvoria chyby buniek F2 a G2.
Absolútne odkazy na bunky sa pri kopírovaní vzorcov nemenia.
Ak chcete zabrániť chybám, skonvertujeme odkaz na bunku D2 do absolútnej bunkovej referencie. Absolútne odkazy na bunky sa vytvárajú stlačením tlačidla F4 na klávesnici. Týmto spôsobom sa pridávajú znaky dolára okolo odkazu na bunku, ako napríklad $ D $ 2.
- Klikni na lookup_value line v dialógovom okne.
- Kliknite na bunka D2 pridať tento odkaz na bunku lookup_value line - to je bunka, kde budeme písať názov časti, o ktorú hľadáme informácie
- Bez presunutia vkladacieho bodu stlačte tlačidlo F4 kľúč na klávesnici na konverziu D2 do absolútnej bunkovej referencie $ D $ 2.
- Odísť VLOOKUP funkčné dialógové okno otvorené pre ďalší krok v príručke.
Zadanie argumentu tabuľkového poľa
Tabuľkový pohľad je tabuľka údajov, ktoré vyhľadávací vzorec vyhľadáva, aby našiel požadované informácie.Súbor tabuľky musí obsahovať aspoň dva stĺpce údajov.
Prvý stĺpec obsahuje argument s hodnotou vyhľadávania (predchádzajúci krok v príručke), zatiaľ čo druhý stĺpec vyhľadá vyhľadávací vzorec na nájdenie informácií, ktoré špecifikujeme.
Argument tabuľkového poľa musí byť zadaný buď ako rozsah obsahujúci referencie buniek pre tabuľku s údajmi alebo ako názov rozsahu.
- Klikni na table_array line v dialógovom okne.
- typ stôl zadajte názov rozsahu tohto argumentu.
- Odísť VLOOKUP funkčné dialógové okno otvorené pre ďalší krok v príručke.
Vloženie funkcie COLUMN
normálne VLOOKUP iba vracia údaje z jedného stĺpca údajovej tabuľky a tento stĺpec je nastavený hodnotou indexové číslo stĺpca argumentom.
V tomto príklade však máme tri stĺpce, z ktorých chceme vrátiť údaje, takže potrebujeme spôsob, ako ich ľahko zmeniť indexové číslo stĺpca bez úpravy nášho vyhľadávacieho vzorca - to je miesto, kde STĹPIK funkcia príde.
Zadaním ako indexové číslo stĺpca argument, zmení sa, ako sa zkopíruje vzhľad vyhľadávania bunka D2 na buniek E2 a F2 neskôr v príručke.
Funkcie hniezdenia
STĹPIK funkcia, preto pôsobí ako VLOOKUP, s indexové číslo stĺpca argumentácia; to sa dosiahne hniezdením STĹPIK funkcia vo vnútri VLOOKUP v Col_index_num riadku dialógového okna.
Ručné zadávanie funkcie COLUMN
Pri vkladaní funkcií program Excel neumožňuje otvoriť dialógové okno druhej funkcie a zadat jeho argumenty. STĹPIK Funkcia musí byť preto zadaná ručne v systéme Col_index_num linka. STĹPIK funkcia má iba jeden argument - referencie argument, ktorý je odkaz na bunku.
Výber referenčného argumentu funkcie COLUMN
STĹPIK úlohou funkcie je vrátiť číslo stĺpca zadaného ako referencie argumentom. Inými slovami, konvertuje písmeno stĺpca na číslo s stĺpec A čo je prvý stĺpec, stĺpec B druhá a tak ďalej.
Od prvého poľa údajov, čo chceme vrátiť, je cena položky, ktorá je v stĺpci 2 tabuľky údajov. Môžeme vybrať bunkovú referenciu pre ľubovoľnú bunku stĺpec B ako referencie Argument s cieľom získať číslo 2 pre Col_index_num argumentom.
- V VLOOKUP dialógové okno, kliknite na Col_index_num linka.
- Zadajte názov funkcie stĺp nasleduje otvorené okrúhle držadlo.
- Kliknite na bunka B1 v pracovnom hárku zadajte odkaz na bunku ako referencie argumentom.
- Zadajte a uzavretie okrúhleho držiaka na dokončenie STĹPIK funkcie
- Odísť VLOOKUP funkčné dialógové okno otvorené pre ďalší krok v príručke.
Zadanie argumentu vyhľadávania rozsahu VLOOKUP
VLOOKUP, s argument typ argument je logická hodnota (TRUE alebo FALSE), ktorý označuje, či chcete VLOOKUP nájsť presnú alebo približnú zhodu s lookup_value.
- ak TRUE alebo ak je tento argument vynechaný: VLOOKUP vráti buď presnú zhodu s lookup_valueAlebo ak sa nenašla presná zhoda, VLOOKUP vráti ďalšiu najväčšiu hodnotu. Na to, aby to urobil, údaje v prvom stĺpci table_array sa musia zoradiť vo vzostupnom poradí.
- ak FALSE: VLOOKUP bude používať presnú zhodu iba s lookup_value, Ak sú v prvom stĺpci reťazca dve alebo viac hodnôt table_array ktoré sa zhodujú s hodnotou vyhľadávania, použije sa prvá nájdená hodnota. Ak presná zhoda nie je nájdená, a # N / A chyba je vrátená.
V tomto návode, pretože hľadáme konkrétne informácie o konkrétnej hardvérovej položke, nastavíme argument typ rovná nepravdivý.
- Klikni na argument typ line v dialógovom okne.
- Zadajte slovo nepravdivý v tomto riadku, aby sme naznačili, že chceme VLOOKUP vrátiť presnú zhodu pre údaje, ktoré hľadáme.
- kliknite OK doplňte vzorec vyhľadávania a zatvorte dialógové okno.
- Keďže sme ešte nezadali do vyhľadávacích kritérií bunka D2 # N / A chyba bude prítomný v bunka E2.
- Táto chyba bude opravená, keď pridáme vyhľadávacie kritériá v poslednom kroku tutoriálu.
Kopírovanie formulára vyhľadávania
Vzor vyhľadávania je určený na získanie údajov z viacerých stĺpcov údajovej tabuľky naraz. Aby sme tak urobili, vzorec vyhľadávania musí byť vo všetkých poliach, z ktorých chceme získať informácie.
V tomto návode chceme získať údaje z stĺpce 2, 3, a 4 tabuľky údajov - to je cena, číslo dielu a meno dodávateľa pri zadávaní čiastočného názvu ako lookup_value.
Keďže údaje sú v pracovnom hárku usporiadané pravidelne, môžeme skopírovať vzorec vyhľadávania v bunka E2 na buniek F2 a G2.
Keď je vzorec skopírovaný, aplikácia Excel aktualizuje referenčný odkaz na bunku v súbore STĹPIK funkcie (bunka B1), aby sa zohľadnila nová poloha formulára.
Aplikácia Excel tiež nemení absolútnu referenciu buniek $ D $ 2 a pomenovaný rozsah stôl ako je vzorec skopírovaný.
Existuje viac ako jeden spôsob, ako skopírovať údaje v programe Excel, ale pravdepodobne najjednoduchší spôsob je použitie Plniaca rukoväť.
- Kliknite na bunka E2, kde sa nachádza vyhľadávacia vzorec, aby sa stala aktívnou bunkou.
- Umiestnite ukazovateľ myši cez čierny štvorec v pravom dolnom rohu - ukazovateľ sa zmení na znak plus, ktorý označuje popisovač plnenia.
- Kliknite na ľavé tlačidlo myši a presuňte rukoväť naplnenia cez bunka G2.
- uvoľnenie tlačidlo myši a bunka F3 by mal obsahovať dvojdimenzionálny vzorec vyhľadávania.
- Ak je vykonané správne, buniek F2 a G2 by teraz tiež mali obsahovať # N / A chyba, ktorá je prítomná v bunka E2.
Zadanie vyhľadávacích kritérií
Akonáhle bol vyhľadávací vzorec skopírovaný do požadovaných buniek, môže byť použitý na získanie informácií z tabuľky údajov. Za týmto účelom zadajte reťazec názov položky ktoré chcete prevziať do priečinka lookup_value bunka (D2) a stlačte tlačidlo vstúpiť na klávesnici.
Po dokončení každej bunky obsahujúcej vzorec vyhľadávania by mala obsahovať iný údaj o hardvérovej položke, ktorú hľadáte.
- Kliknite na bunka D2 v pracovnom liste.
- typ widget do bunka D2 a stlačte tlačidlo vstúpiť na klávesnici.
- Nasledujúce informácie by sa mali zobraziť v buniek E2 na G2.
- E2: 14,76 dolárov - cena miniaplikácie
- F2: PN-98769 - číslo dielu pre widget
- G2: Widgets Inc. - meno dodávateľa pre widgety
- Otestujte VLOOKUP array vzorec ďalej zadaním názvu iných častí do bunka D2 a sledovanie výsledkov v buniek E2 na G2
![Ako otestovať rýchlosť internetu [jednoduché, menej ako 5 minút]](https://i.go-travels.com/img/how-to/how-to-test-your-internet-speed.png "Ako otestovať rýchlosť internetu [jednoduché, menej ako 5 minút]")
