Excel: konkreetse teabe viitefunktsioon

Nii kasutatakse Exceli funktsiooni õigesti

Kas teil on tohutul hulgal andmeid ja vajate kiiresti teavet tabelis sisalduva teatud väärtuse kohta? Siinkohal on mõttekas kasutada Exceli viitefunktsiooni. Selle põhjuseks on asjaolu, et see võimaldab nõutavat teavet otsingu kriteeriumi ja lahtripiirkondade abil kindlaks määrata, kust nõutavat väärtust otsida. See töötab automaatselt ja võimaldab teavet kiiresti küsida. Kui soovite teha sihipärasemat otsingut, peaksite kasutama täiustatud viitefunktsioone Vreference, Wreference või XVreference. Excel pakub täiendavaid alternatiive korraldamiseks ja andmete otsimiseks.

Exceli viitefunktsiooni kasutamine

Exceli viitefunktsioon on kasulik väärtuse otsimiseks veerust või reast, mis asub teises reas või veerus samas asendis. Viiteid saab kasutada ühelt poolt vektorversioonis ja teiselt poolt maatriksversioonis. Vektorversiooni kasutatakse siis, kui soovitakse otsida veergu või rida kindla otsingukriteeriumi järgi. Maatriksi versioonis saab otsida mitut veergu ja rida või kogu töölehte. Selleks on aga täiustatud funktsioonid Vreference ja Wreference. Programm lubab maatriksversiooni ainult ühilduvuse tõttu teiste arvutustabeliprogrammidega.

Kuidas on Exceli viitefunktsioon vektorversioonis üles ehitatud?

Exceli viitefunktsioon koosneb kolmest argumendist ja on üles ehitatud järgmiselt: Viide (otsingukriteerium, otsinguvektor, [tulemusvektor]).

• Otsingu kriteerium on kohustuslik argument. Siia sisestatakse otsitav väärtus. Väärtus võib olla number, tekst, loogiline väärtus, nimi või teine viitefunktsioon.
• Otsi vektorit on valemi teine nõutav argument. Siin määratakse veerg või rea ala, kust otsinguväärtust otsitakse. See võib olla ka tekst, numbrid või tõeväärtused.
• Tulemusvektor: Kolmanda argumendi, tulemusvektori, ümber olevad nurksulgud näitavad, et see argument on valikuline. Siinkohal saab määrata veel ühe valdkonna, mis peab sisaldama sama palju elemente kui teine argument.

Otsi veergudest - näide

Teil on veerus A tootenumbrite loend ja veerus B vastavad rõivaesemed. Otsite konkreetset tootenumbrit ja soovite teada saada, millise riideesemega artikli number käib.

Valige tühi rida.

Sisestage valem = viide ("ART7492031"; A2: A6; B2: B6).

Seetõttu otsib funktsioon veerust A artikli numbrit ja väljastab veerust B saadud tulemuse, näiteks "Üldine".

Maatriksiversiooni viite valemisüntaksis on vähem argumente

Maatriksfunktsioonis koosneb Exceli viitefunktsioon viitest (otsingukriteerium, maatriks). See ei sisalda valikulisi argumente, vaid pigem kahte kohustuslikku.

• Otsingu kriteerium on esimene nõutav argument ja see võib koosneda arvust, tekstist või loogilistest väärtustest, nimest või mõnest muust viitefunktsioonist.
• maatriks on lahtrite vahemik, mida kasutatakse võrdluseks. Siit leiate ka numbrid, stringid või loogilised väärtused.

Nagu mainitud, on soovitatav kasutada teisi Exceli viitefunktsioone Vreference ja Wreference, kuna neil on parem funktsionaalsus. Näiteks saate määrata veeru numbri või veeruindeksi, mis sisaldab tagastamisväärtust.

Otsi veergudest - vektorversioon: näide

Kui vektorversioonis eeldatakse sama olukorda nagu ülaltoodud näites, toimige sarnaselt.

Valige tühi rida.

Sisestage valem = viide ("ART7492031"; A2: B6).

Seetõttu otsib funktsioon veerust A kuni veergu B artikli numbrit ja väljastab selle tulemusel vastava riideeseme ("üldine").

Seda tuleks võrdlusfunktsiooni kasutamisel arvesse võtta

Väärtused sees Otsi vektorit mõlemad versioonid tuleb järjestada kasvavas järjekorras. See tähendab näiteks numbrite puhul -1, 0, 1, 2, 3, jne, tekstiväärtuste jaoks A, B, C, jne ja tõeväärtuste jaoks pole õige millele järgnes tõsi. Tekst on tõstutundlik. Kui otsingukriteeriumi järgi väärtust ei leita, võtab Exceli viitefunktsioon väärtuse Otsi vektoritmis on otsingukriteeriumi väärtusele kõige lähemal. Kui otsingukriteeriumi väärtus on väiksem kui otsinguala väärtus, tagastab Excel veaväärtuse #NV.

Kiire otsing nende kolme Exceli viitefunktsiooniga

Kasutajad, kes ei kasuta ühilduvuse tõttu Exceli lingif.webpunktsiooni, peaksid kasutama Sreference, Wreference või täiustatud versiooni XLOOKUP. Need võimaldavad väärtusi täpsemalt otsida, kasutades näiteks metamärke.

Otsige veerge või tabelit viite alusel

Exceli viitefunktsioon VLOOKUP otsib väärtust ridadest, kasutades maatriksit ja veeruindeksit. Siin on oluline, et andmed oleksid korraldatud nii, et otsinguväärtus oleks otse tagastusväärtuse kõrval. Kuid väärtusi saab määrata ka nii, et need kuuluvad teatud kategooriatesse või rühmadesse. See protseduur säästab tabeli hindamisel aega.

Vreference ei saa otsida ainult terveid tabeleid, vaid seda saab kombineerida ka muude funktsioonidega. Näiteks on võimalik otsida mitut kriteeriumi korraga. Kui soovite kasutada Exceli viitefunktsiooni mitme lingitud töölehe jaoks, võite saada tõrketeateid, kuid need saab seadete abil kiiresti lahendada. Vea väärtus on aga #NV kuvatakse, on see rakenduse viga. Seda Exceli veateadet saab tabada teiste valemitega.

Kasutage viidet Exceli viitefunktsioonina ja otsige ridade kaudu

Vastupidiselt VLOOKUPile otsib HLOOKUP horisontaalselt. See Exceli viitefunktsioon otsib seega töölehe otsingukriteeriumi kindlaksmääratud rea veerghaaval. Funktsioonil HLOOKUP on järgmised funktsioonid:

• 3 kohustuslikku argumenti (Otsingukriteerium; Maatriks; Jooneindeks)
• 1 valikuline argument (Area_reference)
• Konkreetne otsing tasetabelis
• Metamärkide kasutamine otsingukriteeriumina

Igas suunas otsimine lubab XVreference'i

Microsoft 365 -l on täiustatud Exceli viitefunktsioon XLOOKUP, mis ühendab funktsioonid VLOOKUP ja HLOOKUP. Valemi abil saab töölehte otsida otsingukriteeriumi järgi olenemata tulemuste veeru asukohast. See viitefunktsioon on 6 argumendiga pisut keerulisem, kuid see võimaldab Exceli kasutajatel üksikasjalikku ja dünaamilist otsingut igas suunas.

Ülevaade teistest maatriksvalemitest tabeli hindamiseks

Kas andmete hindamisel on oluline kasutada väiketähti ja suurtähti? Selles küsimuses võib aidata keerulisem massiivivalem, mida viitefunktsioonid ei suuda täita. Exceli viitefunktsioonid ei ole ainus viis programmi repertuaaris veergudes või ridades teabe pärimiseks. Näiteks võib indeksifunktsioon väljastada lahtritest väärtusi või viiteid. Nõutavaid andmeid saab välja lugeda, määrates lahtri muutuva asukoha või kasutades veergude ja ridade numbreid. Võrdlusfunktsiooni lingi abil saavad Exceli kasutajad väärtusi eraldada ka ridade ja veergude ristumiskohtadest. Lahtrite vahemikke saab kasutada ka Exceli loendi viimase kirje määramiseks.

Muud Exceli funktsioonid, mis aitavad teabe korraldamisel ja hindamisel:

• Rida () on Exceli funktsioon, mille abil saate töölehel reanumbreid määrata. Näiteks saate Exceli loendeid nummerdada kahe sammuga. Seda valemit saab isegi teise valemisse manustada ja see võib automaatselt jätkata ridade nummerdamist niipea, kui tühi lahter saab uue väärtuse.
• Kaudne () aitab teil jaotada ja paigutada mitmerealisi andmeid veergudesse või lugeda sisu lahtri aadresside kaudu.
• Aadress () on veel üks maatriksvalem, mille abil saate määrata konkreetse väärtuse lahtri aadressi. Teiste funktsioonide integreerimisel saate funktsiooni laiendada ja näiteks määrata Exceli loendi suurima arvu positsiooni.
• Koos teiste valemitega ei saa hüperlink () mitte ainult väärtuse täpset asukohta väljastada, vaid ka salvestada positsiooni lingi samal ajal.

Exceli viitefunktsioon võimaldab kiiresti teavet pärida

Olenemata sellest, kas andmed tuleb määrata kindlast veerust, otsitud ridadest või seotud otsinguväärtuse tulemuse väärtusest, saab Exceli viitefunktsioone kasutada mitmel viisil. Kasutajad saavad valida teiste arvutustabeliprogrammidega ühilduva Exceli viitefunktsiooni või ulatuslikumate funktsioonide Vreference või Wreference vahel. Edasijõudnud kasutajatele sobib XVerweis sihipärasemaks otsinguks, mis ühendab SVerweisi ja Wreweisi funktsioone. Andmete korraldamiseks saab kasutada muid Exceli funktsioone, näiteks ridade automaatseks nummerdamiseks või väärtuse lahtri asukoha tagastamiseks.