Programmai Excel ir daudz funkciju - aptuveni 450 no tām.
Un daudzi no tiem ir vienkārši satriecoši. Darba apjoms, ko varat paveikt, izmantojot dažas formulas, mani joprojām pārsteidz (pat pēc Excel izmantošanas vairāk nekā 10 gadus).
Un starp visām šīm pārsteidzošajām funkcijām izceļas INDEX MATCH funkciju kombinācija.
Es esmu milzīgs INDEX MATCH kombinācijas ventilators, un esmu to daudzkārt skaidri norādījis.
Es pat uzrakstīju rakstu par Index Match vs VLOOKUP, kas izraisīja nelielas debates (uguņošanu varat pārbaudīt komentāru sadaļā).
Un šodien es rakstu šo rakstu, koncentrējoties tikai uz indeksa atbilstību, lai parādītu dažus vienkāršus un uzlabotus scenārijus, kuros varat izmantot šo jaudīgo formulu kombināciju un paveikt darbu.
Piezīme: Programmā Excel ir arī citas uzmeklēšanas formulas, piemēram, VLOOKUP un HLOOKUP, un tās ir lieliskas. Daudzi cilvēki uzskata, ka VLOOKUP ir vieglāk lietojams (un tā ir arī taisnība). Es uzskatu, ka INDEX MATCH daudzos gadījumos ir labāks risinājums. Bet, tā kā cilvēkiem ir grūti, tas tiek izmantots mazāk. Tāpēc es mēģinu to vienkāršot, izmantojot šo apmācību.Pirms es jums parādīšu, kā INDEX MATCH kombinācija maina analītiķu un datu zinātnieku pasauli, ļaujiet man vispirms iepazīstināt jūs ar atsevišķām daļām - INDEX un MATCH funkcijām.
Funkcija INDEX: atrod vērtību, pamatojoties uz koordinātām
Vienkāršākais veids, kā saprast, kā darbojas indeksa funkcija, ir to uzskatīt par GPS satelītu.
Tiklīdz jūs pateiksit satelītam platuma un garuma koordinātas, tas precīzi zinās, kur doties, un atradīs šo atrašanās vietu.
Tātad, neskatoties uz to, ka satriecoši daudz latgalo kombināciju, satelīts precīzi zinātu, kur meklēt.
Es ātri meklēju savu darba vietu, un tas ir tas, ko es saņēmu.
Lai nu kā, pietiek ar ģeogrāfiju.
Tāpat kā satelītam ir nepieciešamas platuma un garuma koordinātas, Excel funkcijai INDEX būtu nepieciešams rindas un kolonnas numurs, lai uzzinātu, uz kuru šūnu jūs atsaucaties.
Un tā ir Excel INDEX funkcija riekstu čaumalā.
Tāpēc ļaujiet man to definēt vienkāršos vārdos.
Funkcija INDEX izmantos rindas numuru un kolonnas numuru, lai atrastu šūnu noteiktā diapazonā un atgrieztu tajā esošo vērtību.
Viss pats par sevi, INDEX ir ļoti vienkārša funkcija, bez lietderības. Galu galā vairumā gadījumu jūs, visticamāk, nezināt rindu un kolonnu numurus.
Bet…
Fakts, ka varat to izmantot ar citām funkcijām (mājiens: MATCH), kas var atrast rindas numuru un kolonnas numuru, padara INDEX par ārkārtīgi spēcīgu Excel funkciju.
Zemāk ir funkcijas INDEX sintakse:
= INDEKSS (masīvs, rindas_numurs, [kolonnas_numurs]) = INDEKSS (masīvs, rindas_numurs, [kolonnas_numurs], [apgabala_numurs])
- masīvs - a šūnu diapazons vai masīva konstante.
- rindas_numurs - rindas numurs, no kura jāiegūst vērtība.
- [kolonnas_numurs] - kolonnas numurs, no kura jāiegūst vērtība. Lai gan tas nav fakultatīvs arguments, bet, ja rindas_numurs nav norādīts, tas ir jānorāda.
- [apgabala_numurs] - (Neobligāti) Ja masīva argumentu veido vairāki diapazoni, šis skaitlis tiktu izmantots, lai atlasītu atsauci no visiem diapazoniem.
Funkcijai INDEX ir 2 sintakses (tikai FYI).
Pirmais tiek izmantots vairumā gadījumu. Otrais tiek izmantots tikai progresīvos gadījumos (piemēram, veicot trīspusēju meklēšanu), ko mēs apskatīsim vienā no šīs apmācības piemēriem.
Bet, ja esat iepazinies ar šo funkciju, atcerieties pirmo sintaksi.
Zemāk ir video, kurā paskaidrots, kā izmantot funkciju INDEX
Funkcija MATCH: atrod pozīciju, kas balstīta uz meklēšanas vērtību
Atgriežoties pie mana iepriekšējā garuma un platuma piemēra, MATCH ir funkcija, kas var atrast šīs pozīcijas (Excel izklājlapu pasaulē).
Vienkāršā valodā Excel MATCH funkcija var atrast šūnas atrašanās vietu diapazonā.
Un uz kāda pamata tā varētu atrast šūnas stāvokli?
Pamatojoties uz uzmeklēšanas vērtību.
Piemēram, ja jums ir saraksts, kā parādīts zemāk, un vēlaties tajā atrast vārda “Atzīmēt” pozīciju, varat izmantot funkciju MATCH.
Funkcija atgriež 3, jo tā ir šūnas pozīcija ar nosaukumu Marks.
Funkcija MATCH sāk meklēt no augšas uz leju uzmeklēšanas vērtību (kas ir “Atzīmēt”) norādītajā diapazonā (kas šajā piemērā ir A1: A9). Tiklīdz tas atrod nosaukumu, tas atgriež pozīciju šajā konkrētajā diapazonā.
Tālāk ir redzama funkcijas MATCH sintakse programmā Excel.
= MATCH (lookup_value, lookup_array, [match_type])
- lookup_value - Vērtība, kurai jūs meklējat atbilstību lookup_array.
- lookup_array - Šūnu diapazons, kurā meklējat lookup_value.
- [atbilstības_tips] - (Neobligāti) Tas norāda, kā programmai Excel vajadzētu meklēt atbilstošu vērtību. Tam var būt trīs vērtības -1, 0 vai 1.
Izpratne par atbilstības veida argumentu MATCH funkcijā
Ir vēl viena lieta, kas jums jāzina par funkciju MATCH, un tā ir par to, kā tā iet caur datiem un atrod šūnas atrašanās vietu.
Funkcijas MATCH trešais arguments var būt 0, 1 vai -1.
Tālāk ir paskaidrots, kā šie argumenti darbojas:
- 0 - tas meklēs precīzu vērtības atbilstību. Ja tiek atrasta precīza atbilstība, funkcija MATCH atgriež šūnas pozīciju. Pretējā gadījumā tas atgriezīs kļūdu.
- 1 - tiek atrasta lielākā vērtība, kas ir mazāka vai vienāda ar uzmeklēšanas vērtību. Lai tas darbotos, jūsu datu diapazons ir jāsakārto augošā secībā.
- -1 - tiek atrasta mazākā vērtība, kas ir lielāka vai vienāda ar uzmeklēšanas vērtību. Lai tas darbotos, jūsu datu diapazons ir jāsakārto dilstošā secībā.
Zemāk ir video, kurā paskaidrots, kā izmantot funkciju MATCH (kopā ar atbilstības veida argumentu)
Apkopojot un izsakoties vienkāršos vārdos:
- INDEX ir nepieciešama šūnas pozīcija (rindas un kolonnas numurs), un tā norāda šūnas vērtību.
- MATCH atrod pozīciju, izmantojot uzmeklēšanas vērtību.
Apvienosim tos, lai izveidotu spēkstaciju (INDEX + MATCH)
Tagad, kad jums ir pamatzināšanas par to, kā INDEX un MATCH funkcijas darbojas individuāli, apvienosim šos divus un uzzināsim par visām brīnišķīgajām lietām, ko tā var darīt.
Lai to labāk izprastu, man ir daži piemēri, kuros tiek izmantota kombinācija INDEX MATCH.
Sākšu ar vienkāršu piemēru un pēc tam parādīšu arī dažus progresīvus lietošanas gadījumus.
Noklikšķiniet šeit, lai lejupielādētu parauga failu
1. piemērs: vienkārša uzmeklēšana, izmantojot kombināciju INDEX MATCH
Veiksim vienkāršu uzmeklēšanu, izmantojot INDEX/MATCH.
Zemāk ir tabula, kurā man ir desmit skolēnu atzīmes.
No šīs tabulas es vēlos atrast Džima zīmes.
Zemāk ir formula, kas to var viegli izdarīt:
= INDEKSS ($ A $ 2: $ B $ 11, MATCH ("Jim", $ A $ 2: $ A $ 11,0), 2)
Tagad, ja jūs domājat, ka to var viegli izdarīt, izmantojot funkciju VLOOKUP, jums ir taisnība! Tas nav labākais INDEX MATCH izcilības pielietojums. Neskatoties uz to, ka esmu INDEX MATCH fans, tas ir nedaudz grūtāk nekā VLOOKUP. Ja vēlaties iegūt datus no kolonnas labajā pusē, iesakām izmantot VLOOKUP.
Iemesls, kāpēc es parādīju šo piemēru, ko var viegli izdarīt arī ar VLOOKUP, ir parādīt, kā INDEX MATCH darbojas vienkāršā vidē.
Tagad ļaujiet man parādīt INDEX MATCH priekšrocības.
Pieņemsim, ka jums ir vienādi dati, bet nevis slejās, bet rindās (kā parādīts zemāk).
Jūs zināt, jūs joprojām varat izmantot kombināciju INDEX MATCH, lai iegūtu Džima atzīmes.
Zemāk ir formula, kas sniegs jums rezultātu:
= INDEKSS ($ B $ 1: $ K $ 2,2, MATCH (“Jim”, $ B $ 1: $ K $ 1,0))
Ņemiet vērā, ka jums ir jāmaina diapazons un jāmaina rindu/kolonnu daļas, lai šī formula darbotos arī horizontālajiem datiem.
To nevar izdarīt, izmantojot VLOOKUP, taču jūs joprojām varat to viegli izdarīt, izmantojot HLOOKUP.
INDEX MATCH kombinācija var viegli apstrādāt gan horizontālos, gan vertikālos datus.Noklikšķiniet šeit, lai lejupielādētu parauga failu
2. piemērs: Uzmeklēšana pa kreisi
Tas ir biežāk nekā jūs domājat.
Daudzas reizes jums var būt nepieciešams izgūt datus no kolonnas, kas atrodas pa kreisi no slejas, kurā ir uzmeklēšanas vērtība.
Kaut kas, kā parādīts zemāk:
Lai uzzinātu Maikla pārdošanas apjomus, jums būs jāmeklē kreisajā pusē.
Ja domājat VLOOKUP, ļaujiet man apstāties.
VLOOKUP nav paredzēts kreiso vērtību meklēšanai un iegūšanai.
Vai jūs joprojām varat to izdarīt, izmantojot VLOOKUP?
Jā tu vari!
Bet tas var pārvērsties par garu un neglītu formulu.
Tātad, ja vēlaties veikt uzmeklēšanu un iegūt datus no kolonnām kreisajā pusē, labāk ir izmantot kombināciju INDEX MATCH.
Zemāk ir formula, kas iegūs Maikla pārdošanas numuru:
= INDEKSS ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)
3. piemērs: divvirzienu uzmeklēšana
Līdz šim mēs esam redzējuši piemērus, kad vēlējāmies iegūt datus no kolonnas, kas atrodas blakus kolonnai, kurai ir uzmeklēšanas vērtība.
Bet reālajā dzīvē dati bieži aptver vairākas kolonnas.
INDEX MATCH var viegli apstrādāt divvirzienu meklēšanu.
Zemāk ir datu kopa par studenta atzīmēm trīs dažādos mācību priekšmetos.
Ja vēlaties ātri iegūt studenta atzīmes visos trīs priekšmetos, varat to izdarīt, izmantojot INDEX MATCH.
Tālāk sniegtā formula sniegs Jim atzīmes visiem trim priekšmetiem (kopējiet un ielīmējiet vienā šūnā un velciet, lai aizpildītu citas šūnas vai kopētu un ielīmētu citās šūnās).
= INDEKSS ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))
Ļaujiet man arī ātri izskaidrot šo formulu.
INDEX formula izmanto diapazonu B2: D11.
Pirmajā MATCH tiek izmantots nosaukums (Džims šūnā F3) un tiek iegūta tā atrašanās vieta nosaukumu slejā (A2: A11). Tas kļūst par rindas numuru, no kura jāiegūst dati.
Otrajā MATCH formulā tiek izmantots objekta nosaukums (šūnā G2), lai iegūtu konkrētā subjekta nosaukuma pozīciju B1: D1. Piemēram, matemātika ir 1, fizika ir 2 un ķīmija ir 3.
Tā kā šīs MATCH pozīcijas tiek ievadītas funkcijā INDEX, tā atgriež punktu skaitu, pamatojoties uz studenta vārdu un priekšmeta nosaukumu.
Šī formula ir dinamiska, kas nozīmē, ka, mainot studenta vārdu vai priekšmetu nosaukumus, tā joprojām darbosies un iegūs pareizos datus.
Viena lieliska lieta, lietojot INDEX/MATCH, ir tā, ka pat tad, ja mainīsit priekšmetu nosaukumus, tas turpinās sniegt pareizu rezultātu.4. piemērs: uzmeklēšanas vērtība no vairākām kolonnām/kritērijiem
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties iegūt atzīmes “Atzīmēt ilgi”.
Tā kā dati ir divās kolonnās, es nevaru uzmeklēt Marku un iegūt datus.
Ja es to darīšu šādā veidā, es saņemšu atzīmju datus Markam Frostam, nevis Markam Longam (jo funkcija MATCH sniegs man atbilstības zīmi).
Viens veids, kā to izdarīt, ir izveidot palīgu kolonnu un apvienot nosaukumus. Kad esat izveidojis palīga kolonnu, varat izmantot VLOOKUP un iegūt atzīmju datus.
Ja vēlaties uzzināt, kā to izdarīt, izlasiet šo pamācību par VLOOKUP izmantošanu ar vairākiem kritērijiem.
Bet, izmantojot kombināciju INDEX/MATCH, jums nav nepieciešama palīga kolonna. Jūs varat izveidot formulu, kas apstrādā vairākus kritērijus pašā formulā.
Zemāk redzamā formula sniegs rezultātu.
= INDEKSS ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))
Ļaujiet man ātri izskaidrot, ko šī formula dara.
Formulas MATCH daļa apvieno uzmeklēšanas vērtību (Mark un Long), kā arī visu uzmeklēšanas masīvu. Ja $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 tiek izmantots kā uzmeklēšanas masīvs, tas faktiski pārbauda uzmeklēšanas vērtību, salīdzinot to ar kombinēto vārda un uzvārda virkni (atdalīta ar caurules simbolu).
Tas nodrošina pareizu rezultātu, neizmantojot nevienu palīgu kolonnu.
Jūs varat veikt šāda veida meklēšanu (ja ir vairākas kolonnas/kritēriji) arī ar VLOOKUP, taču jums ir jāizmanto palīgs. INDEX MATCH kombinācija ļauj to viegli izdarīt bez palīgkolonnām.5. piemērs: iegūstiet vērtības no visas rindas/kolonnas
Iepriekš minētajos piemēros mēs esam izmantojuši funkciju INDEX, lai iegūtu vērtību no konkrētas šūnas. Jūs norādāt rindas un kolonnas numuru, un tas atgriež vērtību konkrētajā šūnā.
Bet jūs varat darīt vairāk.
Varat arī izmantot funkciju INDEX, lai iegūtu vērtības no visas rindas vai kolonnas.
Un kā tas var būt noderīgi, jautājiet!
Pieņemsim, ka vēlaties uzzināt Džima kopējo punktu skaitu visos trīs priekšmetos.
Varat izmantot funkciju INDEX, lai vispirms iegūtu visas Džima atzīmes un pēc tam izmantotu funkciju SUM, lai iegūtu kopsummu.
Apskatīsim, kā to izdarīt.
Zemāk man ir visu skolēnu rādītāji trīs priekšmetos.
Zemāk esošā formula man parādīs Džima kopējo punktu skaitu visos trīs priekšmetos.
= SUMMA (INDEKSS ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))
Ļaujiet man paskaidrot, kā šī formula darbojas.
Šeit triks ir izmantot 0 kā kolonnas numuru.
Funkcijā INDEX izmantojot kolonnas numuru 0, tas atgriezīs visas rindas vērtības. Līdzīgi, ja kā rindas numuru izmantojat 0, tas atgriezīs visas kolonnas vērtības.
Tātad tālāk esošā formulas daļa atgriež vērtību masīvu - {97, 70, 73}
INDEKSS ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)
Ja jūs vienkārši ievadāt šo formulu šūnā programmā Excel un nospiediet taustiņu Enter, jūs redzēsit #VALUE! kļūda. Tas ir tāpēc, ka tā neatgriež vienu vērtību, bet gan vērtību masīvu.
Bet neuztraucieties, vērtību masīvs joprojām pastāv. To var pārbaudīt, izvēloties formulu un nospiežot taustiņu F9. Tas parādīs formulas rezultātu, kas šajā gadījumā ir trīs vērtību masīvs - {97, 70, 73}
Tagad, iesaiņojot šo INDEX formulu funkcijā SUM, tā sniegs jums visu Džima iegūto atzīmju summu.
Varat arī izmantot to pašu, lai iegūtu Džima augstākās, zemākās un vidējās atzīmes.
Tāpat kā mēs to esam darījuši skolēnam, jūs varat to darīt arī mācību priekšmetam. Piemēram, ja vēlaties vidējo punktu skaitu tēmā, INDEX formulā varat saglabāt rindas numuru kā 0, un tas sniegs jums visas šī subjekta kolonnu vērtības.
Noklikšķiniet šeit, lai lejupielādētu parauga failu
6. piemērs: atrodiet studenta atzīmi (aptuvenās atbilstības tehnika)
Līdz šim mēs esam izmantojuši MATCH formulu, lai iegūtu precīzu uzmeklēšanas vērtības atbilstību.
Bet jūs varat arī to izmantot, lai veiktu aptuvenu spēli.
Tagad, kāda velna pēc ir aptuvenā atbilstība?
Ļauj man paskaidrot.
Kad meklējat tādas lietas kā vārdi vai ID, jūs meklējat precīzu atbilstību. Bet dažreiz jums jāzina diapazons, kurā atrodas jūsu meklēšanas vērtības. Parasti tas notiek ar skaitļiem.
Piemēram, kā klases audzinātājs, iespējams, vēlēsities uzzināt, kāda ir katra skolēna atzīme mācību priekšmetā, un vērtējumu nosaka, pamatojoties uz rezultātu.
Tālāk ir sniegts piemērs, kurā es vēlos atzīmi visiem skolēniem, un vērtēšana tiek noteikta, pamatojoties uz tabulu labajā pusē.
Tātad, ja skolēnam ir mazāk par 33, atzīme ir F, un, ja viņš/viņa saņem mazāk par 50, bet vairāk par 33, tas ir E utt.
Zemāk ir formula, kas to darīs.
= INDEKSS ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)
Ļaujiet man paskaidrot, kā šī formula darbojas.
Funkcijā MATCH mēs esam izmantojuši 1 kā [match_type] argumentu. Šis arguments atgriezīs lielāko vērtību, kas ir mazāka vai vienāda ar uzmeklēšanas vērtību.
Tas nozīmē, ka MATCH formula iet caur atzīmju diapazonu un, tiklīdz tā atrod atzīmju diapazonu, kas ir vienāds vai mazāks par uzmeklēšanas atzīmju vērtību, tā apstāsies un atgriezīs savu pozīciju.
Tātad, ja uzmeklēšanas zīmes vērtība ir 20, funkcija MATCH atgriež 1, un ja tā ir 85, tā atgriež 5.
Funkcija INDEX izmanto šo pozīcijas vērtību, lai iegūtu atzīmi.
SVARĪGI: lai tas darbotos, jūsu dati ir jāsakārto augošā secībā. Ja tā nav, jūs varat iegūt nepareizus rezultātus.Ņemiet vērā, ka iepriekš minēto var izdarīt arī, izmantojot zemāk esošo VLOOKUP formulu:
= MEKLĒŠANA (B2, $ E $ 3: $ F $ 8,2, TRUE)
Bet MATCH funkcija var iet soli tālāk, kad runa ir par aptuvenu atbilstību.
Varat arī iegūt dilstošus datus un izmantot kombināciju INDEX MATCH, lai atrastu rezultātu. Piemēram, ja es mainu atzīmju tabulas secību (kā parādīts zemāk), es joprojām varu atrast skolēnu atzīmes.
Lai to izdarītu, man atliek tikai nomainīt argumentu [match_type] uz -1.
Zemāk ir mana izmantotā formula:
= INDEKSS ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)VLOOKUP var arī veikt aptuvenu atbilstību, bet tikai tad, ja dati ir sakārtoti augošā secībā (bet tas nedarbojas, ja dati ir sakārtoti dilstošā secībā).
7. piemērs: reģistrjutīgas meklēšanas
Līdz šim visi mūsu veiktie meklējumi ir bijuši reģistrjutīgi.
Tas nozīmē, ka neatkarīgi no tā, vai uzmeklēšanas vērtība bija Džims, JIM vai Džims, tam nebija nozīmes. Jūs iegūsit tādu pašu rezultātu.
Bet ko darīt, ja vēlaties, lai meklēšana būtu reģistrjutīga.
Tas parasti notiek gadījumos, kad jums ir lielas datu kopas un iespēja atkārtoties vai atsevišķi nosaukumi/ID (vienīgā atšķirība)
Piemēram, pieņemsim, ka man ir šāda studentu datu kopa, kurā ir divi studenti ar vārdu Džims (vienīgā atšķirība ir tā, ka viens ir ievadīts kā Džims, bet otrs - kā Džims).
Ņemiet vērā, ka ir divi studenti ar tādu pašu vārdu - Džims (šūna A2 un A5).
Tā kā parastā uzmeklēšana nedarbotos, jums ir jāveic reģistrjutīga meklēšana.
Zemāk ir formula, kas sniegs jums pareizo rezultātu. Tā kā šī ir masīva formula, jums jāizmanto Control + Shift + Enter.
= INDEKSS ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)
Ļaujiet man paskaidrot, kā šī formula darbojas.
Funkcija EXACT pārbauda, vai uzmeklēšanas vērtība (kas šajā gadījumā ir “jim”) precīzi atbilst. Tas iziet visus nosaukumus un atgriež FALSE, ja tā nav atbilstība, un TRUE, ja tā ir atbilstība.
Tātad funkcijas EXACT rezultāts šajā piemērā ir - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Ņemiet vērā, ka ir tikai viena PATIESA, kad funkcija EXACT atrada perfektu atbilstību.
Funkcija MATCH pēc tam atrod TRUE pozīciju masīvā, ko atgriež funkcija EXACT, kas šajā piemērā ir 4.
Kad esam ieguvuši pozīciju, funkcija INDEX to izmanto, lai atrastu atzīmes.
8. piemērs. Atrodiet tuvāko atbilstību
Tagad nedaudz progresēsim.
Pieņemsim, ka jums ir datu kopa, kurā vēlaties atrast personu, kurai ir darba pieredze, kas ir vistuvāk nepieciešamajai pieredzei (minēta šūnā D2).
Lai gan uzmeklēšanas formulas nav paredzētas šim nolūkam, varat to apvienot ar citām funkcijām (piemēram, MIN un ABS), lai to paveiktu.
Zemāk ir formula, kas atradīs personu ar pieredzi, kas ir vistuvāk nepieciešamajai, un atgriezīs personas vārdu. Ņemiet vērā, ka pieredzei jābūt vistuvākajai (kas var būt mazāka vai lielāka).
= INDEKSS ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15))), ABS (D2- $ B $ 2: $ B $ 15), 0))
Tā kā šī ir masīva formula, jums jāizmanto Control + Shift + Enter.
Šīs formulas triks ir mainīt uzmeklēšanas vērtību un uzmeklēšanas masīvu, lai atrastu minimālo pieredzes atšķirību nepieciešamajās un faktiskajās vērtībās.
Pirms es izskaidroju formulu, sapratīsim, kā jūs to darītu manuāli.
Jūs iziesit katru B slejas šūnu un atradīsit atšķirību pieredzē starp prasīto un to, kas cilvēkam ir. Kad jums ir visas atšķirības, jūs atradīsit minimālo un iegūsit šīs personas vārdu.
Tieši to mēs darām ar šo formulu.
Ļauj man paskaidrot.
Uzmeklēšanas vērtība MATCH formulā ir MIN (ABS (D2-B2: B15)).
Šī daļa sniedz minimālo atšķirību starp doto pieredzi (kas ir 2,5 gadi) un visu pārējo pieredzi. Šajā piemērā tas atgriež 0.3
Ņemiet vērā, ka esmu izmantojis ABS, lai pārliecinātos, ka meklēju tuvāko (kas var būt vairāk vai mazāk par doto pieredzi).
Tagad šī minimālā vērtība kļūst par mūsu meklēšanas vērtību.
Funkcijas MATCH uzmeklēšanas masīvs ir ABS (D2- $ B $ 2: $ B $ 15).
Tādējādi mēs iegūstam skaitļu masīvu, no kura ir atņemta 2,5 (nepieciešamā pieredze).
Tagad mums ir uzmeklēšanas vērtība (0,3) un uzmeklēšanas masīvs ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})
Funkcija MATCH šajā masīvā atrod 0,3 pozīciju, kas ir arī tās personas vārda pozīcija, kurai ir tuvākā pieredze.
Šo pozīcijas numuru funkcija INDEX izmanto, lai atgrieztu personas vārdu.
Saistītā lasīšana: Atrodiet Excel tuvāko atbilstību (piemēri, izmantojot uzmeklēšanas formulas)
Noklikšķiniet šeit, lai lejupielādētu parauga failu
9. piemērs. Izmantojiet INDEX MATCH ar aizstājējzīmēm
Ja vēlaties meklēt vērtību, ja ir daļēja atbilstība, jums ir jāizmanto aizstājējzīmes.
Piemēram, zemāk ir uzņēmuma nosaukuma un to tirgus kapitalizācijas datu kopa, un jūs vēlaties iegūt tirgus ierobežojumu. dati par trim uzņēmumiem pa labi.
Tā kā šīs nav precīzas atbilstības, šajā gadījumā nevar veikt regulāru uzmeklēšanu.
Bet jūs joprojām varat iegūt pareizos datus, izmantojot zvaigznīti (*), kas ir aizstājējzīme.
Zemāk ir formula, kas sniegs jums datus, saskaņojot uzņēmumu nosaukumus no galvenās slejas un iegūstot tam tirgus ierobežojuma skaitli.
= INDEKSS ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)
Ļaujiet man paskaidrot, kā šī formula darbojas.
Tā kā nav precīzas uzmeklēšanas vērtību atbilstības, es izmantoju D2 un “*” kā uzmeklēšanas vērtību MATCH funkcijā.
Zvaigznīte ir aizstājējzīme, kas apzīmē jebkuru rakstzīmju skaitu. Tas nozīmē, ka Apple* formulā nozīmētu jebkuru teksta virkni, kas sākas ar vārdu Apple un pēc tās var būt neierobežots rakstzīmju skaits.
Tad, kad Apple* tiek izmantota kā uzmeklēšanas vērtība, un MATCH formula to meklē A slejā, tā atgriež pozīciju “Apple Inc.”, jo tā sākas ar vārdu Apple.
Varat arī izmantot aizstājējzīmes, lai atrastu teksta virknes, kur uzmeklēšanas vērtība ir starp tām. Piemēram, ja kā uzmeklēšanas vērtību izmantojat * Apple *, tā atradīs jebkuru virkni, kurā jebkurā vietā ir vārds ābols.
Piezīme. Šī metode darbojas labi, ja jums ir tikai viens atbilstības gadījums. Bet, ja jums ir vairāki atbilstības gadījumi (piemēram, Apple Inc un Apple Corporation), funkcija MATCH atgriež tikai pirmās atbilstošās instances stāvokli.
10. piemērs. Trīsceļu uzmeklēšana
Šī ir uzlabota INDEX MATCH izmantošana, taču es tomēr to aptveršu, lai parādītu šīs kombinācijas spēku.
Atcerieties, ka es teicu, ka funkcijai INDEX ir divas sintakse:
= INDEKSS (masīvs, rindas_numurs, [kolonnas_numurs]) = INDEKSS (masīvs, rindas_numurs, [kolonnas_numurs], [apgabala_numurs])
Līdz šim visos mūsu piemēros esam izmantojuši tikai pirmo.
Bet, lai meklētu trīs virzienus, jums jāizmanto otrā sintakse.
Vispirms ļaujiet man paskaidrot, ko nozīmē trīspusējs izskats.
Divpusējā uzmeklēšanā mēs izmantojam formulu INDEX MATCH, lai iegūtu atzīmes, kad mums ir skolēna vārds un priekšmeta nosaukums. Piemēram, Džima zīmju iegūšana matemātikā ir divvirzienu meklēšana.
Trīspusējs izskats tam pievienotu vēl vienu dimensiju. Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties uzzināt Džima rezultātu matemātikā vidusposma eksāmenā, tad tas būtu trīspusējs uzmeklējums.
Zemāk ir formula, kas sniegs rezultātu.
= INDEKSS (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Unit Test", 1, IF (G $ 3 = "Mid Term", 2,3))))
Iepriekšminētā formula pārbaudīja trīs lietas - studenta vārdu, priekšmetu un eksāmenu. Kad tas ir atradis pareizo vērtību, tas to atgriež šūnā.
Ļaujiet man paskaidrot, kā šī formula darbojas, sadalot formulu daļās.
- masīvs - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Tā vietā, lai izmantotu vienu masīvu, šajā gadījumā iekavās esmu izmantojis trīs masīvus.
- row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): Funkcija MATCH tiek izmantota, lai atrastu studenta vārda pozīciju šūnā $ F $ 5 studenta vārdu sarakstā.
- col_num - MATCH (4 ASV dolāri, 2 ASV dolāri: 2 ASV dolāri: 2,0 ASV dolāri): Funkciju MATCH izmanto, lai atrastu subjekta nosaukuma pozīciju šūnas $ B $ 2 subjekta nosaukumu sarakstā.
- [apgabala_numurs] - JA (G $ 3 = ”vienības tests”, 1, IF (G $ 3 = ”vidusposms”, 2,3)): Apgabala skaitļa vērtība norāda funkcijai INDEX, kurš no trim masīviem jāizmanto vērtības iegūšanai. Ja eksāmens ir vienības termins, funkcija IF atgriezīs 1 un funkcija INDEX izmantos pirmo masīvu, lai iegūtu vērtību. Ja eksāmens ir vidusposma, IF formula atgriezīs 2, pretējā gadījumā-3.
Šis ir uzlabots INDEX MATCH izmantošanas piemērs, un jūs, visticamāk, neatradīsit situāciju, kad tas būs jāizmanto. Bet joprojām ir labi zināt, ko var darīt Excel formulas.
Noklikšķiniet šeit, lai lejupielādētu parauga failu
Kāpēc INDEX/MATCH ir labāks par VLOOKUP?
VAI tā ir?
Jā, tā ir - vairumā gadījumu.
Pēc kāda laika es iepazīstināšu ar savu lietu.
Bet pirms es to daru, ļaujiet man pateikt šo - VLOOKUP ir ārkārtīgi noderīga funkcija, un man tas patīk. Tas var paveikt daudzas lietas programmā Excel, un es to ik pa laikam izmantoju pats. To sakot, tas nenozīmē, ka nevar būt nekas labāks, un INDEX/MATCH (ar lielāku elastību un funkcionalitāti) ir labāks.
Tātad, ja vēlaties veikt pamata meklēšanu, labāk izmantot VLOOKUP.
INDEX/MATCH ir VLOOKUP par steroīdiem. Un, kad esat iemācījies INDEX/MATCH, jūs vienmēr varētu vēlēties to izmantot (jo īpaši tā elastības dēļ).
Neizstiepjot to pārāk tālu, ļaujiet man ātri pateikt iemeslus, kāpēc INDEX/MATCH ir labāks par VLOOKUP.
INDEX/MATCH var meklēt pa kreisi (kā arī pa labi) no meklēšanas vērtības
Es to aptvēru vienā no iepriekš minētajiem piemēriem.
Ja jums ir vērtība, kas atrodas uzmeklēšanas vērtības kreisajā pusē, to nevar izdarīt, izmantojot VLOOKUP
Vismaz ne tikai ar VLOOKUP.
Jā, jūs varat apvienot VLOOKUP ar citām formulām un paveikt to, taču tas kļūst sarežģīts un netīrs.
INDEX/MATCH, no otras puses, ir paredzēts meklēt visur (neatkarīgi no tā, vai tas ir pa kreisi, pa labi, uz augšu vai uz leju)
INDEX/MATCH var strādāt ar vertikāliem un horizontāliem diapazoniem
Atkal, pilnībā ievērojot VLOOKUP, tas nav paredzēts to darīt.
Galu galā V in VLOOKUP apzīmē vertikāli.
VLOOKUP var iziet tikai tos datus, kas ir vertikāli, savukārt INDEX/MATCH var iziet datus vertikāli un arī horizontāli.
Protams, ir funkcija HLOOKUP, kas rūpējas par horizontālo uzmeklēšanu, bet tā nav VLOOKUP … vai ne?
Man patīk fakts, ka kombinācija INDEX MATCH ir pietiekami elastīga, lai darbotos gan ar vertikāliem, gan horizontāliem datiem.
VLOOKUP nevar strādāt ar dilstošiem datiem
Runājot par aptuveno atbilstību, VLOOKUP un INDEX/MATCH ir vienā līmenī.
Bet INDEX MATCH ņem vērā punktu, jo tā var apstrādāt arī datus, kas ir dilstošā secībā.
Es to parādīju vienā no šīs apmācības piemēriem, kur mums jāatrod skolēnu pakāpe, pamatojoties uz vērtēšanas tabulu. Ja tabula ir sakārtota dilstošā secībā, VLOOKUP nedarbosies (bet INDEX MATCH būtu).
INDEX/MATCH var būt nedaudz ātrāks
Es būšu patiess. Es pats neveicu šo pārbaudi.
Es paļaujos uz Excel meistara - Čārlija Kida - gudrību.
Ātruma atšķirības VLOOKUP un INDEX/MATCH ir gandrīz nemanāmas, ja jums ir nelielas datu kopas. Bet, ja jums ir tūkstošiem rindu un daudz kolonnu, tas var būt noteicošais faktors.
Čārlijs Kids savā rakstā norāda:
“Sliktākajā gadījumā INDEX-MATCH metode ir aptuveni tikpat ātra kā VLOOKUP; labākajā gadījumā tas ir daudz ātrāk. ”
INDEX/MATCH ir neatkarīgs no faktiskās slejas pozīcijas
Ja, iegūstot Džima rezultātu fizikā, jums ir datu kopa, kā parādīts zemāk, varat to izdarīt, izmantojot funkciju VLOOKUP.
Un, lai to izdarītu, sadaļā VLOOKUP varat norādīt kolonnas numuru kā 3.
Viss ir kārtībā.
Bet ja nu es izdzēsu sleju Matemātika.
Tādā gadījumā VLOOKUP formula sabojāsies.
Kāpēc? - Tā kā trešās kolonnas izmantošana bija grūti kodēta, un, izdzēšot kolonnu starp tām, trešā kolonna kļūst par otro kolonnu.
Šajā gadījumā labāk ir izmantot INDEX/MATCH, jo kolonnas numuru var padarīt dinamisku, izmantojot MATCH. Tātad kolonnas numura vietā tā pārbauda tēmas nosaukumu un izmanto to, lai atgrieztu kolonnas numuru.
Protams, jūs to varat izdarīt, apvienojot VLOOKUP ar MATCH, bet, ja tomēr apvienojat, kāpēc gan to nedarīt ar INDEX, kas ir daudz elastīgāks.
Izmantojot INDEX/MATCH, varat droši ievietot/dzēst kolonnas savā datu kopā.
Neskatoties uz visiem šiem faktoriem, ir iemesls, kāpēc VLOOKUP ir tik populārs.
Un tas ir liels iemesls.
VLOOKUP ir vieglāk izmantot
VLOOKUP aizņem ne vairāk kā četrus argumentus. Ja jūs varat apņemt galvu ap šiem četriem, jums ir labi iet.
Un tā kā lielāko daļu pamata meklēšanas gadījumu apstrādā arī VLOOKUP, tā ātri ir kļuvusi par populārāko Excel funkciju.
Es to saucu par Excel funkciju karali.
INDEX/MATCH, no otras puses, ir nedaudz grūtāk lietojams. Ja sākat to lietot, jūs varat saķerties, bet iesācējam VLOOKUP ir daudz vieglāk izskaidrot un iemācīties.
Un šī nav spēle ar nulles summu.
Tātad, ja jūs esat iesācējs meklēšanas pasaulē un nezināt, kā izmantot VLOOKUP, labāk to iemācieties.
Man ir detalizēts ceļvedis par VLOOKUP izmantošanu programmā Excel (ar daudziem piemēriem)
Mans nodoms šajā rakstā nav nostādīt divas satriecošas funkcijas viena pret otru. Es gribēju jums parādīt kombināciju INDEX MATCH un visas lieliskās lietas, ko tā var darīt.
Ceru, ka šis raksts jums šķita noderīgs.
Ļaujiet man zināt savas domas komentāru sadaļā, un, ja šajā apmācībā atrodat kādu kļūdu, lūdzu, dariet man to zināmu.
