Saturs
Iepriekš iesācējiem esam izskaidrojuši, kā izmantot VLOOKUP (angļu valodā VLOOKUP, saīsinājums apzīmē “vertikālās uzmeklēšanas funkcija”) pamatfunkcijas. Un pieredzējušiem lietotājiem tika parādītas vairākas sarežģītākas formulas.
Un šajā rakstā mēs centīsimies sniegt informāciju par citu metodi darbam ar vertikālo meklēšanu.
Jums var rasties jautājums: "Kāpēc tas ir vajadzīgs?". Un tas ir nepieciešams, lai parādītu visas iespējamās meklēšanas metodes. Turklāt daudzi VLOOKUP ierobežojumi bieži neļauj iegūt vēlamo rezultātu. Šajā sakarā INDEX( ) MATCH( ) ir daudz funkcionālāks un daudzveidīgāks, un tiem ir arī mazāk ierobežojumu.
Pamati INDEX MATCH
Tā kā šīs rokasgrāmatas mērķis ir parādīt, cik laba ir šī funkcija, mēs Apskatīsim pamatinformāciju par tā darbības principiem. Un mēs parādīsim piemērus, kā arī apsvērsim, kāpēc tas ir labāk nekā VLOOKUP ().
INDEX Funkciju sintakse un lietojums
Šī funkcija palīdz atrast vēlamo vērtību starp norādītajiem meklēšanas apgabaliem, pamatojoties uz kolonnas vai rindas numuru. Sintakse:
=INDEKSS(masīvs, rindas numurs, kolonnas numurs):
- masīvs – apgabals, kurā tiks veikta meklēšana;
- rindas numurs – norādītajā masīvā meklējamās rindas numurs. Ja rindas numurs nav zināms, jānorāda kolonnas numurs;
- kolonnas numurs – norādītajā masīvā atrodamās kolonnas numurs. Ja vērtība nav zināma, ir nepieciešams rindas numurs.
Vienkāršas formulas piemērs:
=INDEKSS(A1:S10,2,3)
Funkcija meklēs diapazonā no A1 līdz C10. Cipari parāda, no kuras rindas (2) un kolonnas (3) rādīt vēlamo vērtību. Rezultāts būs šūna C2.
Diezgan vienkārši, vai ne? Bet, strādājot ar reāliem dokumentiem, visticamāk, jums nebūs informācijas par kolonnu numuriem vai šūnām. Tam ir paredzēta funkcija MATCH().
Funkciju MATCH sintakse un lietojums
Funkcija MATCH() meklē vajadzīgo vērtību un parāda tās aptuveno numuru norādītajā meklēšanas apgabalā.
Searchpos() sintakse izskatās šādi:
=MATCH(vērtības uzmeklēšanai, masīva uzmeklēšanai, atbilstības veids)
- meklēšanas vērtība – atrodamais skaitlis vai teksts;
- meklētais masīvs – apgabals, kurā tiks veikta meklēšana;
- atbilstības veids — norāda, vai meklēt precīzu vērtību vai tai tuvākās vērtības:
- 1 (vai nav norādīta vērtība) – atgriež lielāko vērtību, kas ir vienāda ar norādīto vērtību vai mazāka par to;
- 0 — parāda precīzu atbilstību meklētajai vērtībai. Kombinācijā INDEX() MATCH() gandrīz vienmēr būs nepieciešama precīza atbilstība, tāpēc mēs rakstām 0;
- -1 — parāda mazāko vērtību, kas ir lielāka vai vienāda ar formulā norādīto vērtību. Šķirošana tiek veikta dilstošā secībā.
Piemēram, diapazonā B1:B3 ir reģistrētas Ņujorka, Parīze, Londona. Zemāk esošā formula parādīs skaitli 3, jo Londona ir trešā sarakstā:
=EXPOSE(Londona,B1:B3,0)
Kā strādāt ar funkciju INDEX MATCH
Jūs droši vien jau esat sācis saprast principu, pēc kura tiek veidots šo funkciju kopīgs darbs. Īsāk sakot, tad INDEX() meklē vēlamo vērtību starp norādītajām rindām un kolonnām. Un MATCH () parāda šo vērtību skaitļus:
=INDEX(kolonna, no kuras tiek atgriezta vērtība, MATCH(vērtība, kurā meklēt, kolonna, kurā meklēt, 0))
Vai joprojām ir grūti saprast, kā tas darbojas? Varbūt piemērs paskaidros labāk. Pieņemsim, ka jums ir pasaules galvaspilsētu un to iedzīvotāju saraksts:
Lai noskaidrotu noteiktas galvaspilsētas, piemēram, Japānas galvaspilsētas, iedzīvotāju skaitu, mēs izmantojam šādu formulu:
=INDEKSS(C2:C10, MATCH(Japāna, A2:A10,0))
Paskaidrojums:
- Funkcija MATCH() meklē vērtību – “Japāna” masīvā A2:A10 un atgriež skaitli 3, jo Japāna ir trešā vērtība sarakstā.
- Šis skaitlis iet uzlīnijas numurs” formulā INDEX() un liek funkcijai drukāt vērtību no šīs rindas.
Tātad iepriekš minētā formula kļūst par standarta formulu INDEKSS(C2:C10,3). Formula veic meklēšanu no šūnām C2 līdz C10 un atgriež datus no trešās šūnas šajā diapazonā, tas ir, C4, jo atpakaļskaitīšana sākas no otrās rindas.
Nevēlaties formulā norādīt pilsētas nosaukumu? Pēc tam ierakstiet to jebkurā šūnā, piemēram, F1, un izmantojiet to kā atsauci formulā MATCH (). Un jūs saņemat dinamiskas meklēšanas formulu:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Svarīgi! Rindu skaits iekšā masīvs INDEX() ir jābūt tādam pašam kā rindu skaitam uzskatīts masīvs MATCH(), pretējā gadījumā jūs saņemsiet nepareizu rezultātu.
Pagaidiet, kāpēc gan neizmantot VLOOKUP() formulu?
=VLOOKUP(F1, A2:C10, 3, False)
Kāda jēga tērēt laiku, mēģinot izdomāt visas šīs INDEX MATCH sarežģītības?
Šajā gadījumā nav nozīmes, kuru funkciju izmantot. Šis ir tikai piemērs, lai saprastu, kā funkcijas INDEX() un MATCH() darbojas kopā. Citi piemēri parādīs, ko šīs funkcijas spēj situācijās, kad VLOOKUP ir bezspēcīgs.
INDEX MATCH vai VLOOKUP
Izlemjot, kuru meklēšanas formulu izmantot, daudzi piekrīt, ka INDEX() un MATCH() ir ievērojami pārāki par VLOOKUP. Tomēr daudzi cilvēki joprojām izmanto VLOOKUP(). Pirmkārt, VLOOKUP() ir vienkāršāks, un, otrkārt, lietotāji pilnībā neizprot visas priekšrocības, ko sniedz darbs ar INDEX() un MATCH(). Bez šīm zināšanām neviens nepiekritīs tērēt savu laiku sarežģītas sistēmas izpētei.
Šeit ir galvenās INDEX() un MATCH() priekšrocības salīdzinājumā ar VLOOKUP():
- Meklēt no labās uz kreiso pusi. VLOOKUP() nevar meklēt no labās uz kreiso pusi, tāpēc meklētajām vērtībām vienmēr ir jāatrodas tabulas kreisajā malās. Bet INDEX() un MATCH() var tikt galā ar to bez problēmām. Šis raksts jums pateiks, kā tas izskatās praksē: kā kreisajā pusē atrast vēlamo vērtību.
- Droša kolonnu pievienošana vai noņemšana. Formula VLOOKUP() parāda nepareizus rezultātus, noņemot vai pievienojot kolonnas, jo VLOOKUP() ir nepieciešams precīzs kolonnas numurs, lai tā būtu veiksmīga. Protams, pievienojot vai noņemot kolonnas, mainās arī to skaits.
Un formulās INDEX () un MATCH () ir norādīts kolonnu diapazons, nevis atsevišķas kolonnas. Rezultātā varat droši pievienot un noņemt kolonnas, katru reizi neatjauninot formulu.
- Meklēšanas apjomam nav ierobežojumu. Izmantojot VLOOKUP(), kopējais meklēšanas kritēriju skaits nedrīkst pārsniegt 255 rakstzīmes, pretējā gadījumā jūs saņemsiet #VALUE! Tātad, ja jūsu datos ir daudz rakstzīmju, vislabākais risinājums ir INDEX() un MATCH().
- Augsts apstrādes ātrums. Ja jūsu galdi ir salīdzinoši mazi, tad diez vai pamanīsit nekādas atšķirības. Bet, ja tabulā ir simtiem vai tūkstošiem rindu un attiecīgi ir simtiem un tūkstošiem formulu, INDEX () un MATCH () tiks galā daudz ātrāk nekā VLOOKUP (). Fakts ir tāds, ka Excel apstrādās tikai formulā norādītās kolonnas, nevis visu tabulu.
VLOOKUP() veiktspējas ietekme būs īpaši pamanāma, ja darblapā ir daudz formulu, piemēram, VLOOKUP() un SUM(). Lai parsētu katru masīva vērtību, ir nepieciešamas atsevišķas funkciju VLOOKUP() pārbaudes. Tātad Excel ir jāapstrādā milzīgs informācijas apjoms, un tas ievērojami palēnina darbu.
Formulu piemēri
Mēs jau esam sapratuši šo funkciju lietderību, tāpēc varam pāriet uz interesantāko daļu: zināšanu pielietošanu praksē.
Formula meklēšanai no labās uz kreiso pusi
Kā jau minēts, VLOOKUP nevar veikt šo meklēšanas veidu. Tātad, ja vēlamās vērtības nav pašā kreisajā kolonnā, VLOOKUP() nedos rezultātu. Funkcijas INDEX() un MATCH() ir daudzpusīgākas, un vērtību atrašanās vietai nav lielas nozīmes, lai tās darbotos.
Piemēram, tabulas kreisajā pusē pievienosim rangu kolonnu un mēģināsim noskaidrot, kādu rangu iedzīvotāju skaita ziņā ieņem Mūsu valsts galvaspilsēta.
Šūnā G1 mēs ierakstām atrodamo vērtību un pēc tam izmantojam šo formulu, lai meklētu diapazonā C1:C10 un atgrieztu atbilstošo vērtību no A2:A10:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
Pamudināt. Ja plānojat izmantot šo formulu vairākām šūnām, noteikti labojiet diapazonus, izmantojot absolūto adresēšanu (piemēram, 2 ASV dolāri: 10 ASV dolāri un 2 ASV dolāri: 4 C$10).
RĀDĪTĀJS VAIRĀK IZSLĒGTS VAIRĀK IZSLĒGTS lai meklētu kolonnās un rindās
Iepriekš minētajos piemēros šīs funkcijas esam izmantojuši kā VLOOKUP() aizstājēju, lai atgrieztu vērtības no iepriekš noteikta rindu diapazona. Bet ko darīt, ja jums ir jāveic matricas vai divpusēja meklēšana?
Tas izklausās sarežģīti, taču šādu aprēķinu formula ir līdzīga standarta INDEX() MATCH() formulai, tikai ar vienu atšķirību: formula MATCH() jāizmanto divas reizes. Pirmo reizi, lai iegūtu rindas numuru, un otro reizi, lai iegūtu kolonnas numuru:
=INDEKSS(masīvs, MATCH(vertikālā meklēšanas vērtība, meklēšanas kolonna, 0), ATBILST(horizontālā meklēšanas vērtība, meklēšanas rinda, 0))
Apskatīsim tabulu zemāk un mēģināsim izveidot formulu INDEKSS() EXPRESS() EXPRESS() lai parādītu demogrāfiskos datus noteiktā valstī atlasītajā gadā.
Mērķa valsts atrodas šūnā G1 (vertikālā uzmeklēšana), un mērķa gads ir šūnā G2 (horizontālā meklēšana). Formula izskatīsies šādi:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Kā šī formula darbojas
Tāpat kā jebkuras citas sarežģītas formulas, tās ir vieglāk saprast, sadalot tās atsevišķos vienādojumos. Un tad jūs varat saprast, ko dara katra atsevišķa funkcija:
- ATBILST(G1,A2:A11,0) – meklē vērtību (G1) diapazonā A2:A11 un parāda šīs vērtības numuru, mūsu gadījumā tas ir 2;
- MEKLĒŠANA(G2,B1:D1,0) – meklē vērtību (G2) diapazonā B1:D1. Šajā gadījumā rezultāts bija 3.
Atrastie rindu un kolonnu numuri tiek nosūtīti uz atbilstošo vērtību INDEX() formulā:
=INDEKSS(B2:D11,2,3;XNUMX;XNUMX)
Rezultātā mums ir vērtība, kas atrodas šūnā 2 rindu un 3 kolonnu krustpunktā diapazonā B2:D11. Un formula parāda vēlamo vērtību, kas atrodas šūnā D3.
Meklējiet pēc vairākiem nosacījumiem, izmantojot INDEX un MATCH
Ja esat izlasījis mūsu VLOOKUP() rokasgrāmatu, iespējams, esat izmēģinājis vairākas meklēšanas formulas. Taču šai meklēšanas metodei ir viens būtisks ierobežojums – nepieciešamība pievienot papildu kolonnu.
Bet labā ziņa ir tā Izmantojot INDEX() un MATCH(), varat meklēt vairākus nosacījumus, nerediģējot vai nemainot darblapu.
Šeit ir vispārīga vairāku nosacījumu meklēšanas formula INDEX() MATCH():
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
Piezīme: šī formula ir jāizmanto kopā ar īsinājumtaustiņu CTRL+SHIFT+ENTER.
Pieņemsim, ka jums ir jāatrod meklētā vērtība, pamatojoties uz 2 nosacījumiem: Pircējs и Produkts.
Tam nepieciešama šāda formula:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
Šajā formulā C2:C10 ir diapazons, kurā tiks veikta meklēšana, F1 - šis nosacījums, A2:A10 — ir diapazons, lai salīdzinātu stāvokli, F2 - 2. nosacījums, V2:V10 – diapazons 2. stāvokļa salīdzināšanai.
Neaizmirstiet nospiest kombināciju darba beigās ar formulu CTRL+SHIFT+ENTER – Excel automātiski aizvērs formulu ar krokainajām lencēm, kā parādīts piemērā:
Ja nevēlaties savam darbam izmantot masīva formulu, pievienojiet formulai vēl vienu INDEX() un nospiediet ENTER, tas izskatīsies piemērā:
Kā šīs formulas darbojas
Šī formula darbojas tāpat kā standarta INDEX() MATCH() formula. Lai meklētu vairākus nosacījumus, vienkārši izveidojiet vairākus nepareizus un patiesus nosacījumus, kas atspoguļo pareizos un nepareizos atsevišķos nosacījumus. Un tad šie nosacījumi attiecas uz visiem atbilstošajiem masīva elementiem. Formula pārvērš False un True argumentus attiecīgi par 0 un 1 un izvada masīvu, kur 1 ir atbilstošās vērtības, kas tika atrastas virknē. MATCH() atradīs pirmo vērtību, kas atbilst 1, un nodos to INDEX() formulai. Un tas, savukārt, atgriezīs jau vēlamo vērtību norādītajā rindā no vēlamās kolonnas.
Formula bez masīva ir atkarīga no INDEX() spējas tos apstrādāt atsevišķi. Otrais INDEX() formulā atbilst falsy (0), tāpēc tas nodod visu masīvu ar šīm vērtībām uz MATCH() formulu.
Šis ir diezgan garš šīs formulas loģikas skaidrojums. Lai iegūtu vairāk informācijas, izlasiet rakstu "INDEKSA ATBILDĪBA ar vairākiem nosacījumiem'.
AVERAGE, MAX un MIN indeksā INDEX un MATCH
Programmai Excel ir savas īpašās funkcijas vidējo, maksimumu un minimumu atrašanai. Bet ko darīt, ja vēlaties iegūt datus no šūnas, kas saistīta ar šīm vērtībām? Šajā gadījumā AVERAGE, MAX un MIN ir jāizmanto kopā ar INDEX un MATCH.
INDEX MATCH un MAX
Lai atrastu lielāko vērtību kolonnā D un parādītu to kolonnā C, izmantojiet formulu:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
INDEX MATCH un MIN
Lai atrastu mazāko vērtību kolonnā D un parādītu to kolonnā C, izmantojiet šādu formulu:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
MEKLĒŠANAS INDEKSS un ČŪSKS
Lai atrastu vidējo vērtību kolonnā D un parādītu šo vērtību C:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
Atkarībā no tā, kā jūsu dati ir rakstīti, trešais MATCH() arguments ir 1, 0 vai -1:
- ja kolonnas ir sakārtotas augošā secībā, iestatiet 1 (tad formula aprēķinās maksimālo vērtību, kas ir mazāka vai vienāda ar vidējo vērtību);
- ja kārtošana ir dilstoša, tad -1 (formula izvadīs minimālo vērtību, kas ir lielāka vai vienāda ar vidējo);
- ja uzmeklēšanas masīvā ir vērtība, kas ir tieši vienāda ar vidējo, iestatiet to uz 0.
Mūsu piemērā populācija ir sakārtota dilstošā secībā, tāpēc mēs ievietojam -1. Un rezultāts ir Tokija, jo iedzīvotāju vērtība (13,189 000) ir vistuvāk vidējai vērtībai (XNUMX).
VLOOKUP() var veikt arī šādus aprēķinus, bet tikai kā masīva formulu: VLOOKUP ar AVERAGE, MIN un MAX.
INDEX MATCH un ESND/IFERROR
Jūs droši vien jau pamanījāt, ka, ja formula nevar atrast vēlamo vērtību, tā rada kļūdu # N / A. Standarta kļūdas ziņojumu varat aizstāt ar kaut ko informatīvāku. Piemēram, iestatiet argumentu formulā XNUMX:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
Izmantojot šo formulu, ievadot datus, kas nav tabulā, forma parādīs norādīto ziņojumu.
Ja vēlaties noķert visas kļūdas, tad izņemot XNUMX var izmantot IFERROR:
=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Kaut kas nogāja greizi!")
Bet atcerieties, ka šādā veidā maskēt kļūdas nav laba ideja, jo standarta kļūdas ziņo par formulas pārkāpumiem.
Mēs ceram, ka jums noderēja mūsu ceļvedis par funkcijas INDEX MATCH() lietošanu.