Excel: Referenčna funkcija za posebne informacije

Tako se funkcija Excel pravilno uporablja

Ali imate ogromno podatkov in nujno potrebujete podatke o določeni vrednosti, ki je vnesena v vašo tabelo? Na tej točki je smiselno uporabiti referenčno funkcijo Excel. To pa zato, ker omogoča določanje zahtevanih informacij z uporabo iskalnega kriterija in območij celic, v katerih je treba iskati zahtevano vrednost. To deluje samodejno in omogoča hitro zahtevanje informacij. Če želite bolj ciljno iskati, uporabite izboljšane referenčne funkcije Vreference, Wreference ali XVreference. Excel ponuja dodatne možnosti za organizacijo in iskanje podatkov.

Kako se uporablja referenčna funkcija Excel

Referenčna funkcija Excel je uporabna za iskanje vrednosti v stolpcu ali vrstici, ki je na istem mestu v drugi vrstici ali stolpcu. Sklicevanje se lahko na eni strani uporablja v vektorski različici, na drugi strani pa v matrični različici. Vektorska različica se uporablja, ko je treba po stolpcu ali vrstici iskati določeno iskalno merilo. V matrični različici lahko iščete več stolpcev in vrstic ali celoten delovni list. Vendar pa v ta namen obstajajo izboljšane funkcije Vreference in Wreference. Program omogoča matrično različico samo zaradi združljivosti z drugimi programi za preglednice.

Kako je referenčna funkcija Excel strukturirana v vektorski različici?

Referenčna funkcija Excel je sestavljena iz treh argumentov in je strukturirana na naslednji način: Sklic (iskalno merilo, iskalni vektor, [vektor rezultata]).

• Merilo iskanja je obvezen argument. Tu vnesete vrednost, ki jo želite poiskati. Vrednost je lahko številka, besedilo, logična vrednost, ime ali druga referenčna funkcija.
• Išči vektor je drugi zahtevani argument formule. Tu je podano območje stolpca ali vrstice, v katerem je treba iskati vrednost iskanja. To so lahko tudi besedilo, številke ali resnične vrednosti.
• Vektor rezultata: Oglati oklepaji okrog tretjega argumenta, vektorja rezultata, kažejo, da je ta argument neobvezen. Na tej točki je mogoče podati drugo področje, ki mora vsebovati toliko elementov kot drugi argument.

Iščite v stolpcih - primer

V stolpcu A imate seznam številk artiklov z ustreznimi oblačili v stolpcu B. Iščete določeno številko izdelka in želite izvedeti, s katerim kosom oblačila je številka izdelka.

Izberite prazno vrstico.

Vstavite formulo = sklic ("ART7492031"; A2: A6; B2: B6).

Zato funkcija poišče številko članka v stolpcu A in prikaže rezultat iz stolpca B, na primer "Splošno".

Referenčna sintaksa formule v različici matrike ima manj argumentov

V matrični funkciji je referenčna funkcija Excel sestavljena iz reference (iskalni kriterij, matrika). Ne vsebuje izbirnih argumentov, temveč dva zahtevana.

• Merilo iskanja je prvi zahtevani argument in je lahko sestavljen iz števila, besedila ali logičnih vrednosti, imena ali katere druge referenčne funkcije.
• matrika je vrsta celic, ki se uporablja za primerjavo. Tu lahko najdete tudi številke, nize ali logične vrednosti.

Kot že omenjeno, je priporočljivo uporabiti druge Excelove referenčne funkcije Vreference in Wreference, saj imata boljšo funkcionalnost. Na primer, lahko podate številko stolpca ali indeks stolpca, ki vsebuje vrnjeno vrednost.

Išči po stolpcih - vektorska različica: primer

Če se v vektorski različici predvideva enaka situacija kot v zgornjem primeru, ravnajte podobno.

Izberite prazno vrstico.

Vstavite formulo = sklic ("ART7492031"; A2: B6).

Zato funkcija poišče številko izdelka v stolpcu A do stolpca B in kot rezultat prikaže ustrezen kos oblačila ("skupno").

To je treba upoštevati pri uporabi referenčne funkcije

Vrednosti v Išči vektor obe različici morata biti razporejeni po naraščajočem vrstnem redu. To pomeni na primer za številke -1, 0, 1, 2, 3, itd. za besedilne vrednosti A, B, C, itd. in za resnične vrednote nepravilno sledi prav. Besedilo ne razlikuje med velikimi in malimi črkami. Če glede na iskalno merilo ni mogoče najti nobene vrednosti, ima vrednost referenčna funkcija Excel vrednost Išči vektorki je najbližje vrednosti v iskalnem kriteriju. Če je vrednost iskalnega kriterija manjša od tiste v iskalnem območju, Excel vrne vrednost napake #NV.

Hitro iskanje s temi tremi referenčnimi funkcijami programa Excel

Uporabniki, ki zaradi združljivosti ne uporabljajo povezave Excel, naj uporabljajo Sreference, Wreference ali izboljšano različico XLOOKUP. Omogočajo natančnejše iskanje vrednosti z uporabo na primer nadomestnih znakov.

Išči stolpce ali tabelo po Vreference

Referenčna funkcija Excel VLOOKUP išče vrednost v vrsticah z uporabo matrice in indeksa stolpca. Pri tem je pomembno, da so podatki organizirani tako, da je iskalna vrednost tik ob vrnjeni vrednosti. Vrednosti pa je mogoče dodeliti tudi tako, da spadajo v določene kategorije ali skupine. Ta postopek prihrani čas pri ocenjevanju tabele.

Vreference ne more samo iskati po celotnih tabelah, ampak jo je mogoče kombinirati tudi z drugimi funkcijami. Na primer, je mogoče iskati več meril hkrati. Če želite uporabiti referenčno funkcijo Excel za več povezanih delovnih listov, boste morda prejeli sporočila o napakah, ki pa jih lahko hitro odpravite s pomočjo nastavitev. Vendar bo vrednost napake enaka #NV prikazano, gre za napako aplikacije. To sporočilo o napaki v Excelu je mogoče ujeti z drugimi formulami.

Sklic uporabite kot referenčno funkcijo v Excelu in poiščite vrstice

V nasprotju z VLOOKUP HLOOKUP išče vodoravno. Ta referenčna funkcija Excel zato išče stolpec za stolpcem v določeni vrstici za iskalno merilo na delovnem listu. Funkcija HLOOKUP ima naslednje funkcije:

• 3 obvezni argumenti (Kriterij iskanja; Matrica; Linijski indeks)
• 1 izbirni argument (Area_reference)
• Posebno iskanje v tabeli ravni
• Uporaba nadomestnih znakov kot merilo iskanja

Iskanje v vseh smereh omogoča XVreference

Microsoft 365 ima izboljšano referenčno funkcijo Excel XLOOKUP, ki združuje funkcije VLOOKUP in HLOOKUP. S formulo lahko po iskalnem kriteriju iščete delovni list, ne glede na to, kje se nahaja stolpec z rezultati. Ta referenčna funkcija je nekoliko bolj zapletena s 6 argumenti, vendar uporabnikom Excela omogoča podrobno in dinamično iskanje v vseh smereh.

Pregled drugih matričnih formul za vrednotenje tabele

Ali je upoštevanje velikih in malih črk za ovrednotenje podatkov ustrezno? Pri tem lahko pomaga bolj zapletena formula matrike, ki je referenčne funkcije ne morejo izvesti. Referenčne funkcije Excel niso edini način za iskanje informacij v stolpcih ali vrsticah, ki jih ima program v svojem repertoarju. Na primer, funkcija indeksa lahko iz celic prikaže vrednosti ali reference. Zahtevane podatke je mogoče prebrati tako, da določite spremenljiv položaj celice ali uporabite številke stolpcev in vrstic.Z uporabo povezave funkcije za primerjavo lahko uporabniki Excela izvlečejo vrednosti tudi iz presečišč vrstic in stolpcev. Razpone celic lahko uporabite tudi za določitev zadnjega vnosa na Excelovem seznamu.

Druge Excelove funkcije, ki lahko pomagajo pri organizaciji in oceni informacij:

• Vrstica () je Excelova funkcija, s katero lahko nastavite številke vrstic na delovnem listu. Excelove sezname lahko na primer oštevilčite v dveh korakih. Ta formula je lahko celo vdelana v drugo formulo in lahko samodejno nadaljuje oštevilčevanje vrstic takoj, ko prazna celica prejme novo vrednost.
• Indirect () vam pomaga razdeliti in razporediti večvrstične podatke v stolpce ali prebrati vsebino prek naslovov celic.
• Naslov () je še ena matrična formula, s katero lahko določite naslov celice določene vrednosti. Z integracijo drugih funkcij lahko razširite funkcijo in na primer določite položaj največjega števila na seznamu Excel.
• V kombinaciji z drugimi formulami Hyperlink () ne more samo prikazati natančnega položaja vrednosti, ampak lahko hkrati shrani tudi povezavo do položaja.

Referenčna funkcija Excel omogoča hitro iskanje informacij

Ne glede na to, ali je treba podatke določiti iz določenega stolpca, iskalnih vrstic ali izvesti vrednost rezultata za ustrezno vrednost iskanja, je mogoče referenčne funkcije v Excelu uporabiti na različne načine. Uporabniki lahko izbirajo med referenčno funkcijo Excel, ki je združljiva z drugimi programi za preglednice, ali obsežnejšimi funkcijami Vreference ali Wreference. Za napredne uporabnike je XVerweis primeren za bolj ciljno iskanje, ki združuje funkcije SVerweis in Wreweis. Druge Excelove funkcije lahko uporabite za organiziranje podatkov, na primer samodejno oštevilčenje vrstic ali vrnitev položaja celice vrednosti.