Excel: Referenčná funkcia pre konkrétne informácie

Takto sa funkcia Excelu používa správne

Máte obrovské množstvo údajov a súrne potrebujete informácie o určitej hodnote, ktorá je zapísaná vo vašej tabuľke? V tomto mieste má zmysel používať referenčnú funkciu programu Excel. Dôvodom je, že umožňuje určiť požadované informácie pomocou kritéria vyhľadávania a oblastí buniek, v ktorých sa má požadovaná hodnota hľadať. Funguje to automaticky a umožňuje rýchle vyžiadanie informácií. Ak chcete vykonávať cielenejšie vyhľadávanie, mali by ste použiť vylepšené referenčné funkcie Vreference, Wreference alebo XVreference. Excel ponúka ďalšie alternatívy pre organizáciu a získavanie údajov.

Ako sa používa referenčná funkcia programu Excel

Referenčná funkcia programu Excel je užitočná pri hľadaní hodnoty v stĺpci alebo riadku, ktorý je na rovnakom mieste v inom riadku alebo stĺpci. Referenciu je možné použiť na jednej strane vo vektorovej verzii a na druhej strane v maticovej verzii. Vektorová verzia sa používa vtedy, keď sa má v stĺpci alebo riadku hľadať konkrétne kritérium vyhľadávania. V maticovej verzii je možné prehľadávať niekoľko stĺpcov a riadkov alebo celý pracovný list. Na tento účel však existujú vylepšené funkcie Vreference a Wreference. Program povoľuje maticovú verziu iba z dôvodu kompatibility s inými tabuľkovými programami.

Ako je referenčná funkcia programu Excel štruktúrovaná vo vektorovej verzii?

Referenčná funkcia programu Excel pozostáva z troch argumentov a má nasledujúcu štruktúru: Referencia (kritérium vyhľadávania, vektor vyhľadávania, [vektor výsledkov]).

• Kritérium vyhľadávania je povinný argument. Tu sa zadáva hľadaná hodnota. Hodnota môže byť číslo, text, logická hodnota, názov alebo druhá referenčná funkcia.
• Hľadať vektor je druhý požadovaný argument vzorca. Tu je zadaná oblasť stĺpca alebo riadka, v ktorej sa má hľadať hľadaná hodnota. Môžu to byť aj textové, číselné alebo pravdivostné hodnoty.
• Výsledný vektor: Hranaté zátvorky okolo tretieho argumentu, vektora výsledkov, naznačujú, že tento argument je voliteľný. V tomto bode môže byť špecifikovaná ďalšia oblasť, ktorá musí obsahovať toľko prvkov ako druhý argument.

Hľadanie v stĺpcoch - príklad

V stĺpci A máte zoznam čísel článkov so zodpovedajúcimi kusmi oblečenia v stĺpci B. Hľadáte konkrétne číslo výrobku a chcete zistiť, s ktorým kusom oblečenia sa číslo výrobku spája.

Vyberte prázdny riadok.

Vložte vzorec = referencia ("ART7492031"; A2: A6; B2: B6).

Funkcia preto vyhľadá číslo článku v stĺpci A a vygeneruje výsledok zo stĺpca B, napríklad „Celkovo“.

Syntax odkazu vo vzorci vo verzii matice obsahuje menej argumentov

V maticovej funkcii pozostáva referenčná funkcia z Excelu z referencie (kritérium vyhľadávania, matica). Neobsahuje žiadne voliteľné argumenty, ale skôr dva požadované.

• Kritérium vyhľadávania je prvým požadovaným argumentom a môže pozostávať z čísla, textu alebo logických hodnôt, názvu alebo inej referenčnej funkcie.
• matica je rozsah buniek, ktorý sa používa na porovnanie. Nájdete tu aj čísla, reťazce alebo logické hodnoty.

Ako už bolo uvedené, odporúča sa použiť ďalšie referenčné funkcie programu Excel Vreference a Wreference, pretože majú lepšiu funkčnosť. Môžete napríklad zadať číslo stĺpca alebo index stĺpca, ktorý obsahuje návratovú hodnotu.

Hľadanie v stĺpcoch - vektorová verzia: príklad

Ak sa vo vektorovej verzii predpokladá rovnaká situácia ako vo vyššie uvedenom príklade, postupujte podobne.

Vyberte prázdny riadok.

Vložte vzorec = referencia ("ART7492031"; A2: B6).

Funkcia preto vyhľadá číslo výrobku v stĺpci A až stĺpci B a ako výsledok vydá zodpovedajúci kus oblečenia („celkovo“).

Toto by sa malo vziať do úvahy pri použití referenčnej funkcie

Hodnoty v Hľadať vektor obe verzie musia byť usporiadané vzostupne. To znamená napríklad pre čísla -1, 0, 1, 2, 3, atď., pre textové hodnoty A, B, C, atď. a pre pravdivostné hodnoty nesprávne nasledovaný pravda. V texte sa nerozlišujú malé a veľké písmena. Ak podľa kritéria vyhľadávania nemožno nájsť žiadnu hodnotu, referenčná funkcia programu Excel vezme hodnotu Hľadať vektorktorá sa najviac približuje hodnote v kritériu vyhľadávania. Ak je hodnota kritéria vyhľadávania menšia ako v oblasti vyhľadávania, Excel vráti chybovú hodnotu #NV.

Rýchle vyhľadávanie pomocou týchto troch referenčných funkcií programu Excel

Používatelia, ktorí z dôvodov kompatibility nepoužívajú funkciu odkazu na Excel, by mali používať odkazy, odkazy alebo vylepšenú verziu XLOOKUP. Umožňujú presnejšie vyhľadávanie hodnôt napríklad pomocou zástupných znakov.

Vyhľadajte stĺpce alebo tabuľku podľa odkazu

Referenčná funkcia VLOOKUP pre Excel hľadá hodnotu v riadkoch pomocou matice a stĺpcového indexu. Tu je dôležité, aby boli údaje usporiadané tak, aby sa hľadaná hodnota nachádzala hneď vedľa návratovej hodnoty. Hodnoty je však možné priradiť aj tak, že patria do určitých kategórií alebo skupín. Tento postup šetrí čas pri vyhodnocovaní tabuľky.

Vreference môže nielen prehľadávať celé tabuľky, ale je možné ich kombinovať aj s inými funkciami. Napríklad je možné vyhľadávať niekoľko kritérií súčasne. Ak chcete použiť referenčnú funkciu programu Excel pre niekoľko prepojených pracovných hárkov, môžu sa vám zobraziť chybové hlásenia, ktoré je však možné rýchlo vyriešiť pomocou nastavení. Hodnota chyby však bude #NV zobrazené, ide o chybu aplikácie. Toto chybové hlásenie programu Excel je možné zachytiť aj pomocou iných vzorcov.

Použite referenciu ako referenčnú funkciu programu Excel a prehľadávajte riadky

Na rozdiel od VLOOKUP, HLOOKUP hľadá horizontálne. Táto referenčná funkcia programu Excel preto hľadá v definovanom riadku stĺpce po stĺpci podľa kritéria vyhľadávania v pracovnom hárku. Funkcia HLOOKUP má nasledujúce funkcie:

• 3 povinné argumenty (Kritérium vyhľadávania; Matica; Čiarový index)
• 1 voliteľný argument (Area_reference)
• Konkrétne vyhľadávanie v tabuľke úrovní
• Použitie zástupných znakov ako kritérií vyhľadávania

Hľadanie vo všetkých smeroch umožňuje XVreferenciu

Microsoft 365 má vylepšenú referenčnú funkciu Excelu XLOOKUP, ktorá kombinuje funkcie VLOOKUP a HLOOKUP. Pomocou vzorca je možné v hárku vyhľadať kritérium vyhľadávania bez ohľadu na to, kde sa nachádza stĺpec s výsledkami. Táto referenčná funkcia je so 6 argumentmi o niečo komplikovanejšia, ale umožňuje používateľom Excelu podrobné a dynamické vyhľadávanie vo všetkých smeroch.

Prehľad ďalších maticových vzorcov na vyhodnotenie tabuľky

Je pri hodnotení údajov relevantné zohľadnenie veľkých a malých písmen? V tejto záležitosti môže pomôcť zložitejší vzorec poľa, ktorý referenčné funkcie nemôžu vykonávať. Referenčné funkcie programu Excel nie sú jediným spôsobom dotazovania na informácie v stĺpcoch alebo riadkoch, ktoré má program v repertoári. Napríklad funkcia indexu môže z buniek vytvárať hodnoty alebo referencie. Požadované údaje je možné načítať zadaním variabilnej polohy bunky alebo pomocou čísel stĺpcov a riadkov. S prepojením funkcie porovnania môžu používatelia Excelu extrahovať hodnoty aj z priesečníkov riadkov a stĺpcov. Rozsahy buniek je možné použiť aj na určenie poslednej položky v zozname programu Excel.

Ďalšie funkcie programu Excel, ktoré môžu pomôcť pri organizácii a vyhodnocovaní informácií:

• Riadok () je funkcia programu Excel, ktorú môžete použiť na nastavenie čísel riadkov v pracovnom hárku. Zoznamy Excelu môžete napríklad číslovať v dvoch krokoch. Tento vzorec môže byť dokonca vložený do iného vzorca a môže automaticky pokračovať v číslovaní riadkov, akonáhle prázdna bunka dostane novú hodnotu.
• Nepriame () vám pomôže rozdeliť a usporiadať viacriadkové údaje do stĺpcov alebo čítať obsah prostredníctvom adries v bunkách.
• Adresa () je ďalší maticový vzorec, ktorý môžete použiť na určenie adresy bunky konkrétnej hodnoty. Integráciou ďalších funkcií môžete funkciu rozšíriť a napríklad určiť pozíciu najväčšieho čísla v zozname programu Excel.
• V kombinácii s inými vzorcami môže hypertextový odkaz () nielen zobrazovať presnú polohu hodnoty, ale súčasne uložiť odkaz na pozíciu.

Referenčná funkcia programu Excel umožňuje rýchly dopyt po informáciách

Bez ohľadu na to, či sa majú údaje určovať z konkrétneho stĺpca, prehľadávaných riadkov alebo z ktorých sa má zobrazovať hodnota výsledku pre priradenú hodnotu vyhľadávania, referenčné funkcie v programe Excel je možné použiť rôznymi spôsobmi. Používatelia si môžu vybrať medzi referenčnou funkciou Excel, ktorá je kompatibilná s inými tabuľkovými programami, alebo rozsiahlejšími funkciami Vreference alebo Wreference. Pre pokročilých používateľov je XVerweis vhodný pre cielenejšie vyhľadávanie, ktoré kombinuje funkcie SVerweis a Wreweis. Na organizáciu údajov je možné použiť aj ďalšie funkcie Excelu, ako napríklad automatické číslovanie riadkov alebo vrátenie polohy bunky hodnoty.