Kas jāzina
- Vispirms izveidojiet funkciju INDEX, pēc tam sāciet ligzdoto MATCH funkciju, ievadot argumentu Lookup_value.
- Pēc tam pievienojiet argumentu Lookup_array, kam seko arguments Match_type, pēc tam norādiet kolonnu diapazonu.
- Pēc tam pārvērtiet ligzdoto funkciju par masīva formulu, nospiežot Ctrl+ Shift+ Enter. Visbeidzot pievienojiet darblapai meklēšanas vienumus.
Šajā rakstā ir paskaidrots, kā izveidot uzmeklēšanas formulu, kas programmā Excel izmanto vairākus kritērijus, lai atrastu informāciju datu bāzē vai datu tabulā, izmantojot masīva formulu. Masīva formula ietver funkcijas MATCH ligzdošanu funkcijā INDEX. Informācija attiecas uz programmām Excel darbam ar Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 un Excel darbam ar Mac.
Sekojiet līdzi apmācībai
Lai izpildītu šajā apmācībā norādītās darbības, ievadiet parauga datus tālāk norādītajās šūnās, kā parādīts tālāk esošajā attēlā. 3. un 4. rinda ir atstāta tukša, lai iekļautu šīs apmācības laikā izveidoto masīva formulu. (Ņemiet vērā, ka šajā apmācībā nav iekļauts attēlā redzamais formatējums.)
- Ievadiet augšējo datu diapazonu šūnās D1 līdz F2.
- Ievadiet otro diapazonu šūnās D5 līdz F11.
Izveidojiet INDEX funkciju programmā Excel
Funkcija INDEX ir viena no retajām funkcijām programmā Excel, kurai ir vairākas formas. Funkcijai ir masīva forma un atsauces forma. Masīva forma atgriež datus no datu bāzes vai datu tabulas. Atsauces veidlapā ir norādīta šūnas atsauce vai datu atrašanās vieta tabulā.
Šajā apmācībā masīva forma tiek izmantota, lai atrastu titāna logrīku piegādātāja nosaukumu, nevis šūnu atsauci uz šo piegādātāju datu bāzē.
Lai izveidotu INDEX funkciju, veiciet šīs darbības:
- Atlasiet šūnu F3, lai padarītu to par aktīvo šūnu. Šajā šūnā tiks ievadīta ligzdotā funkcija.
-
Dodieties uz Formulas.
Image - Izvēlieties Lookup & Reference, lai atvērtu funkciju nolaižamo sarakstu.
- Atlasiet INDEX, lai atvērtu dialoglodziņu Select Arguments.
- Izvēlieties masīvs, rindas_numurs, kolonnas_numurs.
- Atlasiet OK, lai atvērtu dialoglodziņu Funkciju argumenti. Programmā Excel darbam ar Mac tiek atvērts Formulu Builder.
- Novietojiet kursoru tekstlodziņā Array.
-
Izceliet darblapā esošās šūnas D6 līdz F11, lai dialoglodziņā ievadītu diapazonu.
Atstājiet atvērtu dialoglodziņu Funkciju argumenti. Formula nav pabeigta. Jūs aizpildīsiet formulu tālāk sniegtajos norādījumos.
Image
Sāciet ligzdoto MATCH funkciju
Ievietojot vienu funkciju citā, nav iespējams atvērt otrās jeb ligzdotās funkcijas formulu veidotāju, lai ievadītu nepieciešamos argumentus. Ligzdotā funkcija jāievada kā viens no pirmās funkcijas argumentiem.
Ievadot funkcijas manuāli, funkcijas argumenti tiek atdalīti viens no otra ar komatu.
Pirmais solis, lai ievadītu ligzdoto MATCH funkciju, ir argumenta Lookup_value ievadīšana. Uzmeklēšanas_vērtība ir atrašanās vieta vai šūnas atsauce meklēšanas vienumam, kas jāatbilst datu bāzē.
Uzmeklēšanas_vērtība pieņem tikai vienu meklēšanas kritēriju vai terminu. Lai meklētu vairākus kritērijus, paplašiniet vērtību Lookup_value, savienojot vai savienojot divas vai vairākas šūnu atsauces, izmantojot simbolu &.
- Dialoglodziņā Funkciju argumenti novietojiet kursoru tekstlodziņā Row_num.
-
Ievadiet MATCH(.
- Atlasiet šūnu D3, lai ievadītu šīs šūnas atsauci dialoglodziņā.
- Ievadiet & (& ) aiz šūnas atsauces D3, lai pievienotu otru šūnas atsauci.
- Atlasiet šūnu E3, lai ievadītu otrās šūnas atsauci.
-
Ievadiet , (komats) aiz šūnas atsauces E3, lai pabeigtu funkcijas MATCH argumenta Lookup_value ievadi.
Image Pamācības pēdējā solī Lookup_values tiks ievadītas darblapas šūnās D3 un E3.
Pabeidziet ligzdoto MATCH funkciju
Šajā darbībā ir ietverta argumenta Lookup_array pievienošana ligzdotajai funkcijai MATCH. Uzmeklēšanas_masīvs ir šūnu diapazons, ko funkcija MATCH meklē, lai atrastu argumentu Lookup_value, kas pievienots apmācības iepriekšējā solī.
Tā kā argumentā Lookup_array tika identificēti divi meklēšanas lauki, tas pats ir jādara ar Lookup_array. Funkcija MATCH katram norādītajam terminam meklē tikai vienu masīvu. Lai ievadītu vairākus masīvus, izmantojiet simbolu &, lai savienotu masīvus kopā.
- Novietojiet kursoru teksta lodziņā Row_num datu beigās. Kursors tiek parādīts aiz komata pašreizējā ieraksta beigās.
- Lai ievadītu diapazonu, darblapā iezīmējiet šūnas no D6 līdz D11. Šis diapazons ir pirmais masīvs, ko meklē funkcija.
- Ievadiet & (& ) pēc šūnu atsaucēm D6:D11. Šis simbols liek funkcijai meklēt divos masīvos.
- Izceliet darblapā esošās šūnas no E6 līdz E11, lai ievadītu diapazonu. Šis diapazons ir otrais masīvs, ko meklē funkcija.
-
Ievadiet , (komatu) aiz šūnas atsauces E3, lai pabeigtu funkcijas MATCH argumenta Lookup_array ievadi.
Image - Atstājiet dialoglodziņu atvērtu nākamajai apmācības darbībai.
Pievienojiet MATCH tipa argumentu
Trešais un pēdējais funkcijas MATCH arguments ir Match_type arguments. Šis arguments norāda Excel, kā uzmeklēšanas_vērtību saskaņot ar vērtībām uzmeklēšanas_masīvā. Pieejamās iespējas ir 1, 0 vai -1.
Šis arguments nav obligāts. Ja tas tiek izlaists, funkcija izmanto noklusējuma vērtību 1.
- Ja Match_type=1 vai tiek izlaists, MATCH atrod lielāko vērtību, kas ir mazāka par vai vienāda ar Lookup_value. Meklēt_masīva dati ir jākārto augošā secībā.
- Ja Match_type=0, MATCH atrod pirmo vērtību, kas ir vienāda ar Lookup_value. Meklēt_masīva datus var kārtot jebkurā secībā.
- Ja Match_type=-1, MATCH atrod mazāko vērtību, kas ir lielāka vai vienāda ar Lookup_value. Meklēt_masīva dati ir jākārto dilstošā secībā.
Ievadiet šīs darbības aiz komata, kas tika ievadīts iepriekšējā darbībā funkcijas INDEX rindas_numurs rindā:
- Ievadiet 0 (nulle) aiz komata Row_num tekstlodziņā. Šis skaitlis liek ligzdotajai funkcijai atgriezt precīzas atbilstības terminiem, kas ievadīti šūnās D3 un E3.
-
Ievadiet ) (apaļā iekava), lai pabeigtu funkciju MATCH.
Image - Atstājiet dialoglodziņu atvērtu nākamajai apmācības darbībai.
Pabeidziet INDEX funkciju
Funkcija MATCH ir pabeigta. Ir pienācis laiks pāriet uz dialoglodziņa teksta lodziņu Column_num un ievadīt pēdējo funkcijas INDEX argumentu. Šis arguments norāda, ka programmai Excel kolonnas numurs ir diapazonā no D6 līdz F11. Šajā diapazonā tiek atrasta funkcijas atgrieztā informācija. Šajā gadījumā titāna logrīku piegādātājs.
- Novietojiet kursoru tekstlodziņā Column_num.
-
Ievadiet 3 (trīs). Šis skaitlis liek formulai meklēt datus diapazona D6–F11 trešajā kolonnā.
Image - Atstājiet dialoglodziņu atvērtu nākamajai apmācības darbībai.
Izveidojiet masīva formulu
Pirms dialoglodziņa aizvēršanas pārvērtiet ligzdoto funkciju par masīva formulu. Šis masīvs ļauj funkcijai datu tabulā meklēt vairākus vārdus. Šajā apmācībā ir saskaņoti divi termini: logrīki no 1. kolonnas un Titanium no 2. slejas.
Lai programmā Excel izveidotu masīva formulu, nospiediet taustiņus CTRL, SHIFT un ENTER taustiņi vienlaikus. Kad funkcija ir nospiesta, to ieskauj krokainas figūriekavas, kas norāda, ka funkcija tagad ir masīvs.
- Atlasiet OK, lai aizvērtu dialoglodziņu. Programmā Excel darbam ar Mac atlasiet Gatavs.
- Atlasiet šūnu F3, lai skatītu formulu, un pēc tam novietojiet kursoru formulas joslā formulas beigās.
- Lai pārveidotu formulu par masīvu, nospiediet CTRL+ SHIFT+ ENTER.
- A N/A kļūda parādās šūnā F3. Šī ir šūna, kurā tika ievadīta funkcija.
-
Šūnā F3 parādās kļūda N/A, jo šūnas D3 un E3 ir tukšas. D3 un E3 ir šūnas, kurās funkcija meklē, lai atrastu Lookup_value. Pēc datu pievienošanas šīm divām šūnām kļūda tiek aizstāta ar informāciju no datu bāzes.
Image
Pievienot meklēšanas kritērijus
Pēdējais solis ir darblapai pievienot meklēšanas vienumus. Šī darbība atbilst terminiem Logrīki no 1. kolonnas un Titanium no 2. slejas.
Ja formula atrod atbilstību abiem terminiem attiecīgajās datu bāzes kolonnās, tā atgriež vērtību no trešās kolonnas.
- Atlasīt šūnu D3.
- Ievadiet Logrīki.
- Atlasīt šūnu E3.
- Ierakstiet Titanium un nospiediet Enter.
- Šūnā F3 tiek parādīts piegādātāja nosaukums Widgets Inc.. Šis ir vienīgais piegādātājs, kas pārdod titāna logrīkus.
-
Atlasiet šūnu F3. Funkcija parādās formulu joslā virs darblapas.
{=INDEKSS(D6:F11, ATBILSTĪBA(D3&E3, D6:D11&E6:E11, 0), 3)}
Šajā piemērā ir tikai viens titāna logrīku piegādātājs. Ja būtu bijis vairāk nekā viens piegādātājs, funkcija atgriež piegādātāju, kas datubāzē norādīts pirmais.
Image
