Visi Excel VLOOKUP funkcijas noslēpumi datu atrašanai tabulā un izvilkšanai citā

2024 Autors: Malcolm Clapton | [email protected]. Pēdējoreiz modificēts: 2023-12-17 04:04

Pēc raksta izlasīšanas jūs ne tikai uzzināsit, kā atrast datus Excel izklājlapā un iegūt tos citā, bet arī paņēmienus, ko var izmantot kopā ar funkciju VLOOKUP.

Strādājot programmā Excel, ļoti bieži ir jāatrod dati vienā tabulā un jāizvelk citā. Ja jūs joprojām nezināt, kā to izdarīt, pēc raksta izlasīšanas jūs ne tikai uzzināsit, kā to izdarīt, bet arī uzzināsit, kādos apstākļos jūs varat izspiest no sistēmas maksimālu veiktspēju. Tiek apskatīta lielākā daļa no ļoti efektīvajiem paņēmieniem, kas būtu jāizmanto kopā ar funkciju VLOOKUP.

Pat ja VLOOKUP funkciju lietojat gadiem ilgi, tad ar lielu varbūtības pakāpi šis raksts jums noderēs un neatstās vienaldzīgus. Piemēram, būdams IT speciālists, pēc tam IT vadītājs, VLOOKUP lietoju jau 15 gadus, bet ar visām niansēm tiku galā tikai tagad, kad sāku profesionāli mācīt cilvēkiem Excel.

VLOOKUP ir saīsinājums vārdam vvertikāli NSpārbaude. Tāpat VLOOKUP - vertikālā LOOKUP. Jau pats funkcijas nosaukums mums norāda, ka tā veic meklēšanu tabulas rindās (vertikāli - atkārtojot rindas un fiksējot kolonnu), nevis kolonnās (horizontāli - atkārtojot kolonnas un fiksējot rindu). Jāpiebilst, ka VLOOKUP ir māsa – neglīts pīlēns, kurš nekad nekļūs par gulbi – tā ir HLOOKUP funkcija. HLOOKUP, atšķirībā no VLOOKUP, veic horizontālu meklēšanu, taču Excel koncepcija (un arī datu organizēšanas jēdziens) nozīmē, ka jūsu tabulās ir mazāk kolonnu un daudz vairāk rindu. Tāpēc mums ir jāmeklē pēc rindām daudzkārt biežāk nekā pēc kolonnām. Ja jūs pārāk bieži izmantojat HLO funkciju programmā Excel, tad, visticamāk, jūs kaut ko nesapratāt šajā dzīvē.

Sintakse

Funkcijai VLOOKUP ir četri parametri:

= VLOOKUP (;; [;]), šeit:

- vēlamā vērtība (reti) vai atsauce uz šūnu, kurā ir vēlamā vērtība (lielākajā daļā gadījumu);

- atsauce uz šūnu diapazonu (divdimensiju masīvs), kura PIRMAJĀ (!) kolonnā tiks meklēta parametra vērtība;

- kolonnas numurs diapazonā, no kura tiks atgriezta vērtība;

- tas ir ļoti svarīgs parametrs, kas atbild uz jautājumu, vai diapazona pirmā kolonna ir sakārtota augošā secībā. Ja masīvs ir sakārtots, mēs norādām vērtību TRUE vai 1, pretējā gadījumā - FALSE vai 0. Ja šis parametrs tiek izlaists, tas pēc noklusējuma tiek iestatīts uz 1.

Varu derēt, ka daudzi no tiem, kuri funkciju VLOOKUP zina kā nelīdzenu, pēc ceturtā parametra apraksta izlasīšanas var justies neērti, jo ir pieraduši to redzēt nedaudz citādākā formā: parasti viņi runā par precīzu atbilstību, kad meklēšanu (FALSE vai 0) vai diapazona skenēšanu (TRUE vai 1).

Tagad vajag sasprindzināties un vairākas reizes izlasīt nākamo rindkopu, līdz sajutīsi teiktā jēgu līdz galam. Katrs vārds tur ir svarīgs. Piemēri palīdzēs jums to noskaidrot.

Kā tieši VLOOKUP formula darbojas?

• Formulas veids I. Ja pēdējais parametrs ir izlaists vai norādīts vienāds ar 1, tad VLOOKUP pieņem, ka pirmā kolonna ir sakārtota augošā secībā, tāpēc meklēšana apstājas pie rindas, tieši pirms rindas, kurā ir vērtība, kas ir lielāka par vēlamo … Ja šāda virkne netiek atrasta, tiek atgriezta diapazona pēdējā rinda.

  

• Formula II. Ja pēdējais parametrs ir norādīts kā 0, VLOOKUP secīgi skenē masīva pirmo kolonnu un nekavējoties aptur meklēšanu, kad tiek atrasta pirmā precīzā atbilstība parametram, pretējā gadījumā kļūdas kods # N / A (# N / A) tiek atgriezta.

  

Formulu darbplūsmas

VPR I tips

VPR II tips

Secinājumi I formas formulām

1. Formulas var izmantot, lai sadalītu vērtības pa diapazoniem.
2. Ja pirmajā kolonnā ir dublētās vērtības un tā ir sakārtota pareizi, tiks atgriezta pēdējā no rindām ar dublētām vērtībām.
3. Ja meklējat vērtību, kas acīmredzami ir lielāka par to, ko var ietvert pirmā kolonna, varat viegli atrast tabulas pēdējo rindu, kas var būt diezgan vērtīga.
4. Šis skats atgriezīs kļūdu # N / A tikai tad, ja netiks atrasta vērtība, kas ir mazāka par vēlamo vai vienāda ar to.
5. Ir diezgan grūti saprast, ka formula atgriež nepareizās vērtības, ja jūsu masīvs nav sakārtots.

Secinājumi II tipa formulām

Ja vēlamā vērtība masīva pirmajā kolonnā parādās vairākas reizes, formula atlasīs pirmo rindu turpmākai datu izguvei.

VLOOKUP sniegums

Jūs esat sasniedzis raksta kulmināciju. Šķiet, kāda ir atšķirība, ja es kā pēdējo parametru norādīšu nulli vai vienu? Būtībā visi, protams, norāda nulli, jo tas ir diezgan praktiski: jums nav jāuztraucas par masīva pirmās kolonnas kārtošanu, jūs varat uzreiz redzēt, vai vērtība tika atrasta vai nē. Bet, ja jūsu lapā ir vairāki tūkstoši VLOOKUP formulu, jūs ievērosiet, ka VLOOKUP II darbojas lēni. Tajā pašā laikā parasti visi sāk domāt:

• Man vajag jaudīgāku datoru;
• Man vajag ātrāku formulu, piemēram, daudzi zina par INDEX + MATCH, kas it kā ir ātrāks par nieka 5-10%.

Un tikai daži cilvēki domā, ka, tiklīdz jūs sākat lietot I tipa VLOOKUP un nodrošināt, ka pirmā kolonna ir sakārtota ar jebkādiem līdzekļiem, VLOOKUP ātrums palielināsies 57 reizes. Es rakstu ar vārdiem - PIECDESMIT SEPTIŅAS REIZES! Nevis 57%, bet 5700%. Es pārbaudīju šo faktu diezgan ticami.

Šāda ātra darba noslēpums slēpjas apstāklī, ka sakārtotam masīvam var pielietot ārkārtīgi efektīvu meklēšanas algoritmu, ko sauc par bināro meklēšanu (uz pusi metode, dihotomijas metode). Tātad I tipa VLOOKUP to izmanto, bet II tipa VLOOKUP meklē bez optimizācijas. Tas pats attiecas uz funkciju MATCH, kas ietver līdzīgu parametru, un funkciju LOOKUP, kas darbojas tikai sakārtotos masīvos un ir iekļauta programmā Excel, lai nodrošinātu saderību ar Lotus 1-2-3.

Formulas trūkumi

VLOOKUP trūkumi ir acīmredzami: pirmkārt, tas meklē tikai norādītā masīva pirmajā kolonnā un, otrkārt, tikai pa labi no šīs kolonnas. Un, kā jūs saprotat, var gadīties, ka kolonna ar nepieciešamo informāciju atradīsies pa kreisi no kolonnas, kurā mēs meklēsim. Jau pieminētajai formulu kombinācijai INDEX + MATCH nav šī trūkuma, kas padara to par elastīgāko risinājumu datu iegūšanai no tabulām salīdzinājumā ar VLOOKUP (VLOOKUP).

Daži formulas piemērošanas aspekti reālajā dzīvē

Diapazona meklēšana

Klasisks diapazona meklēšanas piemērs ir uzdevums noteikt atlaidi pēc pasūtījuma lieluma.

Meklējiet teksta virknes

Protams, VLOOKUP meklē ne tikai ciparus, bet arī tekstu. Jāpatur prātā, ka formula nenošķir rakstzīmju reģistru. Ja izmantojat aizstājējzīmes, varat organizēt izplūdušo meklēšanu. Ir divas aizstājējzīmes: "?" - aizstāj jebkuru rakstzīmi teksta virknē, "*" - aizstāj jebkuru rakstzīmju skaitu.

Cīņas telpas

Bieži tiek uzdots jautājums, kā meklēt papildu atstarpju problēmu. Ja uzmeklēšanas tabulu joprojām var notīrīt, tad pirmais VLOOKUP formulas parametrs ne vienmēr ir atkarīgs no jums. Tāpēc, ja pastāv risks aizsprostot šūnas ar papildu atstarpēm, varat izmantot funkciju TRIM, lai to notīrītu.

Dažāds datu formāts

Ja pirmais funkcijas VLOOKUP parametrs attiecas uz šūnu, kurā ir skaitlis, bet kas šūnā ir saglabāts kā teksts, un masīva pirmajā kolonnā ir skaitļi pareizā formātā, meklēšana neizdosies. Iespējama arī pretēja situācija. Problēmu var viegli atrisināt, pārtulkojot parametru 1 vajadzīgajā formātā:

= VLOOKUP (−− D7; Produkti! $ A $ 2: $ C $ 5; 3; 0) - ja D7 satur tekstu un tabulā ir skaitļi;

= VLOOKUP (D7 & ""); Produkti! $ A $ 2: $ C $ 5; 3; 0) - un otrādi.

Starp citu, tekstu var tulkot ciparā vairākos veidos vienlaikus, izvēlieties:

• Dubultā noliegums -D7.
• Reizināšana ar vienu D7 * 1.
• Nulles pievienošana D7 + 0.
• Paaugstināšana līdz pirmajai pakāpei D7 ^ 1.

Skaitļa konvertēšana tekstā tiek veikta, savienojot ar tukšu virkni, kas liek Excel konvertēt datu tipu.

Kā apspiest # N / A

Tas ir ļoti ērti izdarāms ar funkciju IFERROR.

Piemēram: = IFERROR (VLOOKUP (D7; Produkti! $ A $ 2: $ C $ 5; 3; 0); "").

Ja VLOOKUP atgriež kļūdas kodu # N / A, tad IFERROR to pārtvers un aizstās parametru 2 (šajā gadījumā tukšu virkni), un, ja kļūda nenotiek, šī funkcija izliksies, ka tā vispār neeksistē, bet ir tikai VLOOKUP, kas atgrieza normālu rezultātu.

Masīvs

Bieži vien viņi aizmirst masīva atsauci padarīt absolūtu, un, izstiepjot masīvu, masīvs "peld". Atcerieties izmantot $ A $ 2: $ C $ 5, nevis A2: C5.

Atsauču masīvu ieteicams ievietot atsevišķā darbgrāmatas lapā. Tas nepaliek zem kājām, un tas būs drošāk.

Vēl labāka ideja būtu deklarēt šo masīvu kā nosauktu diapazonu.

Daudzi lietotāji, norādot masīvu, izmanto tādu konstrukciju kā A: C, norādot veselas kolonnas. Šai pieejai ir tiesības pastāvēt, jo jūs esat pasargāts no nepieciešamības sekot līdzi tam, ka jūsu masīvā ir iekļautas visas nepieciešamās virknes. Ja lapai pievienojat rindas ar sākotnējo masīvu, diapazons, kas norādīts kā A: C, nav jākoriģē. Protams, šī sintaktiskā konstrukcija liek Excel veikt nedaudz vairāk darba nekā precīzi norādīt diapazonu, taču šīs pieskaitāmās izmaksas var neņemt vērā. Mēs runājam par sekundes simtdaļām.

Nu uz ģenialitātes robežas - sakārtot masīvu formā.

Funkcijas COLUMN izmantošana, lai norādītu izņemamo kolonnu

Ja tabulai, kurā izgūstat datus, izmantojot VLOOKUP, ir tāda pati struktūra kā uzmeklēšanas tabulai, bet tajā vienkārši ir mazāk rindu, varat izmantot VLOOKUP funkciju COLUMN (), lai automātiski aprēķinātu izgūstamo kolonnu skaitu. Šajā gadījumā visas VLOOKUP formulas būs vienādas (pielāgotas pirmajam parametram, kas mainās automātiski)! Ņemiet vērā, ka pirmajam parametram ir absolūta kolonnas koordināte.

Saliktas atslēgas izveide ar & "|" &

Ja rodas nepieciešamība meklēt pēc vairākām kolonnām vienlaikus, tad meklēšanai ir jāizveido saliktā atslēga. Ja atgriešanas vērtība nebūtu tekstuāla (kā tas ir laukā "Kods"), bet gan skaitliska, tad šim nolūkam būtu piemērota ērtāka SUMIFS formula un saliktā kolonnas atslēga vispār nebūtu nepieciešama.

Šis ir mans pirmais raksts par Lifehacker. Ja patika, aicinu ciemos, kā arī labprāt palasu komentāros par saviem noslēpumiem, izmantojot VLOOKUP funkciju un tamlīdzīgi. Paldies.:)