Použitím vzorca pre poľa v programe Excel môžeme vytvoriť vzhľad vyhľadávania, ktorý používa viacero kritérií na vyhľadanie informácií v databáze alebo tabuľke údajov. Vzorec poľa obsahuje vkladanie funkcie MATCH do vnútra funkcie INDEX.
Tento návod obsahuje postupný príklad vytvorenia vzoru vyhľadávania, ktorý používa viacero kritérií na nájdenie dodávateľatitánové miniaplikácie v ukážkovej databáze. Postupujte podľa toho, ako ľahko je možné vytvoriť vlastné vlastné vzorce vyhľadávania.
Pripravte zošita programu Excel s údajmi
Ak chcete postupovať podľa krokov v tejto príručke, musíte začať zadaním vzorových údajov do nasledujúcich buniek, ako je uvedené na obrázku vyššie. Riadky 3 a 4 zostávajú prázdne, aby sa prispôsobil vzorec poľa vytvorený počas tohto tutoriálu.
- Zadajte najvyšší rozsah údajov do buniek D1 na F2
- Zadajte druhý rozsah do buniek D5 na F11
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.
02 z 08Vytvorte funkciu INDEX v programe Excel
INDEX funkcia je jedna z mála v programe Excel, ktorá má niekoľko foriem. Funkcia má funkciu Formulár poľa a a Referenčný formulár, Formulár Array vráti skutočné dáta z databázy alebo tabuľky dát, zatiaľ čo Referenčný formulár poskytuje odkaz na bunku alebo umiestnenie údajov v tabuľke.
V tomto výučbe budeme používať formát Array, pretože chceme poznať meno dodávateľa pre titánové miniaplikácie, a nie odkaz na bunku tohto dodávateľa v našej databáze. Postupujte podľa týchto krokov a vytvorte svoju funkciu INDEX:
- Kliknite na bunku F3 aby sme boli aktívnou bunkou - to je miesto, kde vstúpime do vnorenej funkcie.
- Klikni na vzorca v ponuke pásky.
- zvoliť Vyhľadávanie a odkaz z pásu s páskou na otvorenie rozbaľovacej funkcie.
- Kliknite na INDEX v zozname, aby ste vyvolali Vyberte Argumenty dialógové okno.
- Vyber poľa, riadka_číslo, col_num v dialógovom okne.
- kliknite OK otvoriť dialógové okno funkcie INDEX; otvorí sa nástroj na tvorbu formulárov v aplikácii Excel.
- V Formula Builder, klikni na rad linka.
- Zvýraznite bunky D6 na F11, kliknutím a ťahaním do pracovného hárka zadáte rozsah do staviteľa.
V starších verziách programu Excel Formula Builder nahrádza textom Funkčné argumenty okno. Použite rovnaké kroky, ako sú uvedené v tomto návode, do okna.
03 z 08Spustite funkciu vnoreného MATCH
Pri vkladaní jednej funkcie do druhej nie je možné otvoriť druhú alebo vnorenú funkciu tvorcu vzorcov o vložiť potrebné argumenty. Vnorená funkcia musí byť zadaná ako jedna z triedených v prvej funkcii.
Pri zadávaní funkcií ručne sú argumenty funkcie navzájom oddelené čiarkou.
Prvým krokom pri vstupe do vnorenej funkcie MATCH je zadanie lookup_value argumentom. Hodnota Lookup_value bude umiestnenie alebo referencia bunky pre hľadaný výraz, ktorý chceme v databáze porovnať.
Normálne hodnota Lookup_value akceptuje iba jedno vyhľadávacie kritérium alebo termín. Aby sme mohli hľadať viacero kritérií, musíme rozšíriť hodnotu vyhľadávania; Tjeho uskutočňuje zlučovaním alebo spájaním dvoch alebo viacerých odkazov na bunky spoločne pomocou symbolu ampersand - &.
- V Formula Builder, klikni na Row_num linka.
- Zadajte názov funkcie ZÁPAS nasleduje otvorená okrúhla konzola.
- Kliknite na bunku D3 zadajte odkaz na bunku do dialógového okna.
- Zadajte ampersand po referencii bunky D3 aby sa pridal druhý odkaz bunky.
- Kliknite na bunku E3 zadajte túto druhú bunkovú referenciu.
- Po zadaní bunky zadajte čiarku E3 na dokončenie zápisu funkcie MATCH lookup_value argumentom.
V poslednom kroku tutoriálu, náš Lookup_values budú vložené do buniek D3 a E3 pracovného hárka.
04 z 08Dokončite funkciu vnoreného MATCH
Tento krok zahŕňa pridanie prohledávané_pole argument pre vnorené ZÁPAS Funkcie. Vyhľadávací_obrázok je rozsah buniek, ktoré funkcia MATCH vyhľadá, aby našla argument vyhľadávania, ktorý bol pridaný v predchádzajúcom kroku tutoriálu.
Vzhľadom na to, že sme identifikovali dve vyhľadávacie polia v argumentu Lookup_array, musíme urobiť to isté pre Lookup_array. Funkcia MATCH vyhľadá iba jedno pole pre každý špecifikovaný výraz, a preto zadáme viacero políčok, ktoré opäť používame na zoskupenie polí ampersand.
Tieto kroky sa zadajú po čiarke zadanej v predchádzajúcom kroku na Row_num line v INDEX Funkcie.
- Klikni na Row_num Po začiarknutí čiarky umiestnite kurzor na konci aktuálneho záznamu.
- Zvýraznite bunky D6 na D11 v pracovnom hárku zadajte rozsah - toto je prvé pole, ktoré je vyhľadávané.
- Zadajte znak ampersand po odkazoch na bunku D6: D11 pretože chceme, aby funkcia vyhľadala dve polia.
- Zvýraznite bunky E6 na E11 v pracovnom hárku zadajte rozsah - toto je druhé pole, ktoré je vyhľadávané.
- Po zadaní bunky zadajte čiarku E3 na dokončenie zápisu ZÁPAS funkcia je prohledávané_pole argumentom.
- Odísť INDEX funkčné dialógové okno otvorené pre ďalší krok v príručke.
Pridajte Argument typu MATCH
Tretí a posledný argument funkcie MATCH je match_type argumentácia; informuje program Excel o tom, ako zodpovedať lookup_value s hodnotami vo vyhľadávači Lookup_array - dostupné voľby sú 1, 0 alebo -1.
Tento argument je nepovinný. Ak sa vynechá, funkcia použije predvolenú hodnotu 1.
- Ak Match_type = 1 alebo je vynechané: MATCH nájde najväčšiu hodnotu, ktorá je menšia alebo rovná hodnote Lookup_value. Údaje Lookup_array sa musia zoradiť vo vzostupnom poradí.
- Ak Match_type = 0: MATCH nájde prvú hodnotu, ktorá sa presne rovná hodnote Lookup_value. Údaje Lookup_array možno triediť v ľubovoľnom poradí.
- Ak Match_type = -1: MATCH nájde najmenšiu hodnotu, ktorá je väčšia alebo rovná hodnote Lookup_value. Údaje Lookup_array sa musia zoradiť v zostupnom poradí.
Tieto kroky sa zadajú po čiarke zadanej v predchádzajúcom kroku na Row_num line v INDEX Funkcie.
- Po čiarke na Row_num line, zadajte nulu - 0 - pretože chceme, aby vnorená funkcia vrátila presné zhody s výrazmi, ktoré sme vložili do buniek D3 a E3.
- Zadajte uzatváraciu kruhovú konzolu - ) - dokončiť ZÁPAS Funkcie.
- Odísť Formula Builder otvorte ďalší krok v príručke.
Dokončenie funkcie INDEX
Teraz, keď ZÁPAS funkcia sa vykoná budeme presunúť do tretej línie formulára Builder a zadajte posledný argument pre INDEX Funkcie. Toto tretie a posledné tvrdenie je COLUMN_NUM argument, ktorý informuje Excel o čísle stĺpca v rozsahu D6 na F11; toto je miesto, kde nájde informácie, ktoré chceme vrátiť funkciou. V tomto prípade dodávateľ pre titánové miniaplikácie.
- Klikni na COLUMN_NUM linka.
- Zadajte číslo tri - 3 - na tomto riadku, pretože hľadáme údaje v treťom stĺpci rozsahu D6 na F11.
- Opäť opustiť Formula Builder otvorte ďalší krok v príručke.
Vytvorenie vzorca pre pole
Pred zatvorením Formula Builder, musíme zmeniť našu vnorenú funkciu na vzorec poľa; to je to, čo umožňuje našej funkcii vyhľadávať viac výrazov v tabuľke dát. V tomto výučbe hľadáme dva výrazy: Miniaplikácie zo stĺpcov 1 a titán zo stĺpca 2.
Vytvorenie vzorca pre pole v programe Excel sa vykonáva stlačením klávesu CTRL, SHIFT, a ENTER na klávesnici súčasne. Po stlačení bude funkcia obklopená kučeravými zátvorkami, čo naznačuje, že funkcia je teraz pole.
- Zavrieť Formula Builder kliknutím na tlačidlo hotový Tlačidlo.
- Potom vyberte bunku F4 a stlačte tlačidlo vstúpiť pre zobrazenie vzorca.
- Ak chcete preformulovať vzorec do poľa, súčasne stlačte tlačidlo CTRL + SHIFT + ENTER na klávesnici.
- Ak je vykonané správne a # N / A sa zobrazí chyba v bunke F3 - bunke, do ktorej sme zadali funkciu.
- # N / A sa v bunke objaví chyba F3 pretože bunky D3 a E3 sú prázdne. D3 a E3 sú bunky, kde sme funkciu našli Lookup_values, Po pridaní údajov do týchto dvoch buniek sa chyba nahradí informáciou z databázy.
Pridávanie kritérií vyhľadávania
Nakoniec pridáme hľadané výrazy do nášho pracovného listu. Ako sme uviedli v predchádzajúcom kroku, snažíme sa dosiahnuť súlad s podmienkami Miniaplikácie zo stĺpcov 1 a titán zo stĺpca 2. Ak a len vtedy, ak náš vzorec nájde zhodu pre obidve výrazy v príslušných stĺpcoch v databáze, vráti hodnotu z tretieho stĺpca.
- Kliknite na bunku D3.
- typ Miniaplikácie a stlačte tlačidlo vstúpiť na klávesnici.
- Kliknite na bunku E3.
- typ titán a stlačte tlačidlo vstúpiť na klávesnici.
- Meno dodávateľa Widgety Inc. by sa mala nachádzať v bunke F3 - umiestnenie funkcie, pretože je jediným dodávateľom, ktorý predáva produkty Titanium Widgets.
- Po kliknutí na bunku F3 sa vo formulári nad pracovným hárkom zobrazí úplná funkcia.
{= INDEX (D6: F11, MATCH (D3 a E3, D6: D11 & E6: E11, 0), 3)}
V našom príklade bol len jeden dodávateľ pre titánové miniaplikácie. Ak by mal viac ako jeden dodávateľ, dodávateľ uvedený v databáze ako prvý je vrátený funkciou.
