Šīs Excel funkcijas pielietojums un definīcija
VLOOKUP ir Excel funkcija, ar kuras palīdzību lietotājs var meklēt un novērtēt tabulas saturu. Šī funkcija ir pieejama versijās no Excel 2007 operētājsistēmai Windows un Mac.
Kas ir VLOOKUP?
Iespējamie VLOOKUP lietojumi ir jāizskaidro šeit, izmantojot piemēru: Šajā rakstā jūs esat liels literatūras cienītājs un tāpēc esat izveidojis savu Excel izklājlapu, kurā varat rūpīgi sakārtot savāktās grāmatas. Katrs darbs tiek ievadīts ar informāciju par šādām kategorijām:
-
autors
-
titulu
-
Lapas numurs
-
Izdošanas gads
Tagad jūs vēlētos dot draugam grāmatas padomu, ko ņemt līdzi nākamajā tikšanās reizē. Diemžēl jūs varat domāt tikai par autoru, nevis par grāmatas nosaukumu. Šeit parādās VLOOKUP, jo tā var izmantot šo ievades vērtību, lai vienā mirklī izmestu meklēto informāciju.
Kā tiek izmantots VLOOKUP?
Pirms pat domāt par formulu formulēšanu, būtu jānosaka, kur vēlāk atradīsies ievades lauks un dažādi izvades lauki. Lai to izdarītu, ir jēga izveidot atsevišķu tabulu, kas sākotnēji ir tukša un tādējādi ļauj atstāt vietu minētajai informācijai. Ja veidosit šo jauno tabulu, pamatojoties uz esošās tabulas piemēru, vēlāk jums būs laika ietaupīšanas priekšrocības.
Pamatojoties uz to, VLOOKUP formulu var izveidot manuāli vai automātiski ģenerēt programmā Excel. Iesācējiem ir vērts izmantot pēdējo pieeju, lai pakāpeniski iepazītu formulas struktūru un efektu. Lai to izdarītu, cilnē “Formulas” ir atlasīta poga “Ievietot funkciju”. VLOOKUP ir paslēpts atvērtajā logā. Pēc apstiprināšanas atkal atveras logs, kurā var aizpildīt četrus formulas parametrus. Šie ir:
-
Meklēšanas kritērijs
-
matrica
-
Kolonnu indekss
-
Area_reference
Līdz ar to formulas neapstrādātais uzmetums izskatās šādi:
= VLOOKUP (meklēšanas kritērijs, matrica, kolonnu indekss, diapazona saite)
un vienā no iespējamām lietojumprogrammām:
= MEKLĒŠANA (H3; A3: E40; 5)
Meklēšanas kritērijs
Lai funkcija zinātu, kura vērtība jāizmanto kā sākumpunkts, rindiņa, kas divas darbības iepriekš tika izvēlēta kā ievades lauks, tiek atzīmēta laukā "Meklēšanas kritērijs". Mūsu piemērā tur ir ievadīts grāmatas autora vārds “Phillip Pulmann”. Tas padara formulu elastīgu, un tā nav jāpielāgo vēlreiz, tiklīdz ievadītā vērtība mainās.
matrica
Ievades lauks "Matrica" apraksta tabulu, kurā var atrast izvadāmo informāciju. Tādējādi šajā īpašajā matricā ir arī slejas grāmatas nosaukumam, lapas numuram un izdošanas gadam.
Matrica ir pilnībā izvēlēta vienu reizi bez virsrakstiem no augšējās kreisās puses uz apakšējo labo malu. Tādā veidā Excel zina, kurš saturs jāņem vērā, veicot novērtējumu.
Kolonnu indekss
Ievades lauks "kolonnu indeksam" liek lietotājam definēt matricas kolonnu, kurā ir norādīta tikai meklētā vērtība. Kolonnu piešķiršana ir numurēta hronoloģiski. Tas nozīmē, ka tabulas pirmā kolonna saņem vērtību 1, otrā vērtību 2 utt. Mūsu piemērā tas atbilst 1. slejas indeksam autoram, 2. slejas indeksam virsrakstam, 3. slejas indeksam lapas numuram un 4. slejas rādītājs publicēšanas gadam.
Lai tabula būtu pēc iespējas elastīgāka, kolonnas virsrakstu var sasaistīt skaitļa vietā. Tam ir priekšrocība, ka formulu var bez problēmām pārnest arī uz citām rindām, jo kolonnu virsrakstu var elastīgi pielāgot katru reizi.
Uzmanību: VLOOKUP nolasa matricu no kreisās uz labo pusi, tāpēc kolonnu indekss ir jāievieto pa labi no kolonnas meklēšanas kritērijam, lai funkcija to ņemtu vērā!
Area_reference
Parametrs "Range_Lookup" aizpilda VLOOKUP formulu, norādot tabulas novērtēšanas precizitāti. Tomēr tas atšķiras no iepriekš minētajiem formulas komponentiem, jo tas nav obligāts. Ja vērtība “nepareiza” tiek ievadīta 0, programma Excel meklē tikai to vērtību, kas tika norādīta kā meklēšanas kritērijs. Tomēr, ja vērtība “1” ir “patiesa”, tiek meklēta acīmredzama vērtība, ja precīzu vērtību nevarēja atrast.
Šī parametra norādīšana nav obligāta, jo vērtība 1 ir iestatīta pēc noklusējuma. Šis iestatījums būs noderīgs vēlāk uzlabotajā VLOOKUP ar vairākiem meklēšanas kritērijiem.
Apvienošanās
Tiklīdz ir iestatīti visi nepieciešamie parametri, var izmantot VLOOKUP. Pēc meklēšanas kritērija ievadīšanas un funkcijas apstiprināšanas meklējamā vērtība parādās rindā, kas definēta kā izvades lauks.
Mūsu piemērā tagad tiek parādīts grāmatas nosaukums “Zelta kompass”, kas atbilst ievadītajam autoram. Lai ātri uzzinātu lapas numuru un publicēšanas gadu, nekas cits nav jādara, kā vien esošās VLOOKUP formulas vilkšana nākamajās šūnās. Tas ir tik vienkārši, jo VLOOKUP kolonnu indekss ir saistīts ar pirmās tabulas sleju virsrakstu, un arī otrā tabula ir strukturēta tādā pašā secībā.
Gadījumā, ja tabulas atšķiras viena no otras vai rodas kļūda, neskatoties uz visu, VLOOKUP formulu var mainīt arī manuāli. Lai to izdarītu, kolonnu indeksa priekšpēdējais cipars jāsaskaņo ar izvadāmās jaunās vērtības kolonnu.
VLOOKUP ar vairākiem meklēšanas kritērijiem
Pietiekami bieži gadās, ka ar vienu meklēšanas kritēriju nepietiek, lai precīzi novērtētu lielu Excel tabulu. Tad ir jēga palaist VLOOKUP ar vairākiem meklēšanas kritērijiem. Lai to izdarītu, esošā formula jāpapildina ar papildu IF funkciju. Tādā veidā pieteikšanās laikā var ņemt vērā līdz astoņiem dažādiem meklēšanas kritērijiem.
VLOOKUP vairākās Excel izklājlapās
Ja meklēšanas kritēriju var atrast ne tikai vienā, bet, iespējams, arī citā tabulā, VLOOKUP formulu var attiecīgi pielāgot. Šim nolūkam gan funkcija, gan funkcija ISERROR ir jāievieto esošās formulas priekšā. Tam nepieciešami pieci parametri:
-
Meklēšanas kritērijs
-
Matrix1 un Matrix 2
-
Kolonnu indekss1 un kolonnu indekss2
Rezultāts izskatās šādi:
= IF (ISERROR (VLOOKUP (meklēšanas kritērijs, matrica1, kolonna-indekss1, 0));
VLOOKUP (meklēšanas kritērijs; matrica2; kolonnu indekss2,0); VLOOKUP (meklēšanas kritērijs; matrica1; kolonnas indekss1;))
un vienā no iespējamām lietojumprogrammām, piemēram:
= JA (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Meklēšanas kritēriju izmanto, lai abās tabulās ievietotu meklējamo vērtību. Matrix1 un Matrix2 definē abu tabulu attiecīgos šūnu apgabalus. Kolonnu indekss1 un sleju indekss2 tiek izmantoti, lai detalizētāk noteiktu, kuras attiecīgo tabulu kolonnas ir jāmeklē.
Ja vēlamā vērtība parādās abās tabulās, programma Excel izvadīs pirmās tabulas rezultātu. Tomēr, ja vērtība nav atrodama nevienā no divām tabulām, tiek parādīts kļūdas ziņojums. Formulas priekšrocība ir tāda, ka abiem sarakstiem nav jābūt vienādai struktūrai vai vienāda izmēra.
Piešķiriet vērtībām kategorijas, izmantojot funkciju VLOOKUP
Papildu VLOOKUP funkcija ļauj uzskaitītās vērtības automātiski sadalīt burtos un predikātos pēc jūsu izvēles. Iepriekšējā piemērā grāmatas tipam jāievieto papildu tabulas sleja. Grāmatām, kuru garums ir līdz 50 lappusēm, vajadzētu ietilpt īsā stāsta žanrā, savukārt grāmatas no 51 līdz 150 lappusēm tiek piešķirtas romānam un no 151 lappuses - romānam. Lai tas būtu iespējams, VLOOKUP nav nepieciešama papildu formula, tikai jāizmanto cirtainās iekavas “{}”. Gatavā formula izskatās šādi:
= VLOOKUP (B1; {1. "Īss stāsts"; 51. "Novella"; 151. "Romāns"}; 2)
Cirtainu iekavu saturs norāda uz matricu, kas nosaka attiecīgā grāmatas veida apgabalu. Tāpēc sānu garuma piešķiršana attiecīgajai ģintij ir cirtainās iekavās. Formulā izmantoti vērtību pāri, katrs atdalīts ar punktu. Matricu {1. "Īss stāsts"; 51. "Novella"; 151. "Romāns"} lasa šādi:
"No 1 rāda īsu stāstu, no 51 - romānu, no 151 - romānu."
Šo matricu var viegli pielāgot dažādiem uzdevumiem. Tas, no vienas puses, attiecas uz matricu lielumu un skaitu, kā arī to apzīmējumu. Tātad atsevišķu burtu vietā ir iespējams izvadīt virknes vai ciparus. Viss, kas jums jādara, ir pielāgot burtus formulā.
VLOOKUP vairākās darblapās
Vēl viena VLOOKUP funkcija ļauj lietotājiem saistīt saturu, kas atrodas dažādās izklājlapās. Mūsu piemērā šī opcija var būt noderīga, ja informācija vispirms tiek sakārtota dažādās darblapās un pēc tam atjaunināta kopsavilkuma tabulā.
Iedomājieties, ka papildus savām grāmatām Excel izklājlapā uzskaitāt arī savāktās filmas. Pēc tam jūs apvienojat abas kolekcijas vienā lielā tabulā.
Šīs procedūras priekšrocība ir ne tikai palielināta kārtība, bet arī izvairīšanās no iespējamām kļūdām. Ja vēlaties izveidot jaunu ierakstu vai atjaunināt esošu, jums nav jāmeklē lielajā tabulā, bet tā vietā varat piekļūt mazākiem. Pēc tam vērtības tiek automātiski pārsūtītas uz apkopojošo Excel tabulu. Tas padara pārrakstīšanu lielajā tabulā lieku, kas labākajā gadījumā ļauj izvairīties no neveiksmīga gājiena un sekojošas kļūdu ziņojumu ķēdes.
Kā izskatās formula?
Šo funkciju atkal padara iespējamu, ievietojot citu formulu. Lai gan, meklējot pēc vairākiem kritērijiem, bija nepieciešama papildu IF formula, darbam ar vairākām darblapām ir nepieciešama NETIEKA formula. Tādējādi VLOOKUP matricai var norādīt diapazonu no citas izklājlapas.
= VLOOKUP (meklēšanas kritērijs; NETIESA (matrica); kolonnu indekss; diapazona saite)
Uzmanību: Šī formula darbosies tikai tad, ja atsevišķām tabulām dažādās lapās būs tādi paši nosaukumi kā vispārējās tabulas sleju virsrakstiem. Visas tabulas var nosaukt "Nosaukuma laukā" augšējā kreisajā stūrī virs šūnu režģa. Tabulas, kas jau ir nosauktas, var apskatīt, izmantojot taustiņu kombināciju Ctrl + F3.
Darbs ar jauniem kļūdu ziņojumiem
Darbs ar saistītām Excel tabulām var radīt nevēlamas problēmas. Tas jo īpaši ietver nepareizu vērtību izvadi. Gadījumā, ja tiek izvadīta nepareiza vērtība 0, Excel iestatījumos ir neliela problēma, kuru var ātri novērst.
Savukārt izplatītais kļūdas ziņojums #NV ir apzināta VLOOKUP funkcija, kas lietotājam norāda, ka nepieciešamā vērtība nav pieejama. Šo piezīmi var noformēt dažādi, izmantojot formulu.
VLOOKUP - pārskats
VLOOKUP ir noderīga Excel funkcija, ko var izmantot tabulu meklēšanai un novērtēšanai. Tās priekšrocības ir acīmredzamas lietotājam draudzīgā un elastīgā lietojumā. Tādā veidā ikviens, kurš regulāri strādā ar Excel tabulām, var gūt labumu no šīs funkcijas. Vai tas būtu privāts kolekcionārs, kurš izveido savas mazās tabulas, vai lielais uzņēmums, kas apstrādā ievērojami būtiskākas datu kopas.
No otras puses, ja jums joprojām ir neatbildēti pieprasījumi, kurus VLOOKUP nevarēja izpildīt, varat gaidīt papildu Excel iespēju: Microsoft piedāvā Excel 365 lietotājiem jauno XLOOKUP kopš 2022-2023.-2022. Tas balstās uz VLOOKUP kompetencēm un papildina tās ar papildu, dažreiz pat vienkāršākām funkcijām. Tāpēc šajā brīdī tiek atvērta arī jauna datu novērtēšanas kārtība.