Excel meklēšanas formula ar vairākiem kritērijiem

Izmantojot masīvu formulu programmā Excel, mēs varam izveidot meklēšanas formulu, kas izmanto vairākus kritērijus, lai atrastu informāciju datu bāzē vai tabulā.

Masīva formula ietver MATCH funkcijas ievietošanu INDEX funkcijā.

Šajā apmācībā ir ietverts soli pa solim piemērs, kā izveidot meklēšanas formulu, kas izmanto vairākus kritērijus titāna logrīku piegādātājam paraugu datu bāzē.

Sekojot tālāk minētajiem apmācības tematiem, jūs varat izveidot un izmantot formulu, kas redzama attēlā iepriekš.

01 no 09

Ievadiet apmācības datus

Pirmais solis apmācībā ir ievadīt datus Excel darblapā .

Lai sekotu apmācības soļiem, ievadiet tālāk redzamajā attēlā redzamos datus šādās šūnās .

• Ievadiet vislabāko datu diapazonu šūnās no D1 līdz F2
• Ievadiet otro diapazonu no šūnām no D5 līdz F11

Rindas 3 un 4 paliek tukšas, lai pielāgotu šajā apmācībā izveidoto masīvu formulu .

Apmācība neietver attēlu, kas redzams attēlā, taču tas neietekmēs to, kā meklēšanas formulas darbojas.

Informācija par formatēšanas opcijām, kas ir līdzīgas iepriekš minētajām, ir pieejamas šajā Excel Excel formāta sagatavošanas pamācībā.

02 no 09

INDEKSA FUNKCIJAS palaišana

INDEX funkcija ir viena no nedaudzajām Excel programmām, kuras formas ir vairākas. Funkcijai ir masīva forma un atsauces forma .

Array Form atgriež faktiskos datus no datubāzes vai datu tabulas, bet atsauces forma sniedz tabulā norādīto datu šūnu atsauci vai atrašanās vietu.

Šajā apmācībā mēs izmantosim masīva veidlapu, jo mēs vēlamies uzzināt piegādātāja nosaukumu titāna logrīkiem, nevis šūnu atsauci uz šo piegādātāju mūsu datu bāzē.

Katrai veidlapai ir atšķirīgs argumentu saraksts, kas jāizvēlas pirms funkcijas sākuma.

Apmācības soļi

1. Noklikšķiniet uz šūnas F3, lai padarītu to par aktīvo šūnu . Šeit mēs ievadīsim ligzdoto funkciju.
2. Noklikšķiniet uz lentas izvēlnes cilnes Formulas .
3. Lai atvērtu nolaižamo sarakstu, izvēlieties logu "Meklēt" un "Atsauce" .
4. Sarakstā noklikšķiniet uz INDEX , lai atvērtu dialoglodziņu Atlasīt argumentus .
5. Dialoglodziņā izvēlieties array, row_num, col_num opciju.
6. Noklikšķiniet uz OK, lai atvērtu INDEX funkciju dialoglodziņu.

03 no 09

INDEX funkciju masīvu argumenta ievadīšana

Pirmais nepieciešamais arguments ir masīva arguments. Šis arguments nosaka meklējamo šūnu diapazonu vēlamajiem datiem.

Šajā apmācībā šis arguments būs mūsu izlases datu bāze .

Apmācības soļi

1. Dialoglodziņā INDEX funkcija noklikšķiniet uz līnijas array .
2. Lai darbvirsmā ievadītu diapazonu dialoglodziņā, iezīmējiet šūnas D6 līdz F11.

04 no 09

Nestiest MATCH funkciju uzsākšana

Ja viena funkcija tiek ievietota citā vietā, nav iespējams atvērt otru vai ligzdotu funkciju dialoglodziņu, lai ievadītu nepieciešamos argumentus .

Liecētā funkcija ir jāieraksta kā viens no pirmās funkcijas argumentiem.

Šajā pamācībā ligzdotā MATCH funkcija un tās argumenti tiks ievadīti dialoglodziņa INDEX funkcijai, rinda Row_num, otrajā rindā.

Ir svarīgi atzīmēt, ka, ievadot funkcijas manuāli, funkcijas arguments tiek atdalīts viens no otra ar komatu "," .

Ievadot MATCH funkciju Lookup_value argumentu

Pirmais solis, ievadot ligzdoto MATCH funkciju, ir ievadīt " Lookup_value" argumentu.

Lookup_value būs atrašanās vietas vai šūnas atsauce meklēšanas vienumam, kuru mēs vēlamies saskaņot datubāzē.

Parasti Lookup_value pieņem tikai vienu meklēšanas kritēriju vai terminu. Lai meklētu vairākus kritērijus, mums ir jāpaplašina Lookup_value .

To dara, apvienojot vai apvienojot divas vai vairākas šūnu atsauces kopā, izmantojot simbolu " & ".

Apmācības soļi

1. Dialoglodziņā INDEX funkcija noklikšķiniet uz rindas Row_num .
2. Ievadiet funkcijas nosaukuma atbilstību, kam seko atvērta apaļa kvadrātiņa " ( "
3. Noklikšķiniet uz šūnas D3, lai dialoglodziņā ievadītu šūnas atsauci.
4. Lai pievienotu otro šūnu atsauci, pēc šūnu atsauces D3 ierakstiet ampersandu " & ".
5. Noklikšķiniet uz šūnas E3, lai dialoga lodziņā ievadītu otro šūnu atsauci.
6. Ierakstiet komatu "," pēc šūnu atsauces E3, lai pabeigtu MATCH funkcijas " Lookup_value" argumenta ierakstu.
7. Atstājiet INDEX funkciju dialoglodziņu, lai atvērtu nākamajam apmācības posmam.

Pēdējā apmācības posmā Lookup_values ​​tiks ievadītas darblapas D3 un E3 šūnās.

05 no 09

MATRAŠU FUNKCIJAS pievienošana

Šis solis ir saistīts ar liekta MATCH funkcijas pievienošanu ar Lookup_array argumentu .

Lookup_array ir to šūnu diapazons, kuras MATCH funkcija meklēs, lai atrastu Lookup_value argumentu, kas pievienots iepriekšējā apmācības posmā.

Tā kā mēs esam identificējuši divus meklēšanas laukus ar Lookup_array argumentu, mums ir jādara tas pats par Lookup_array . MATCH funkcija meklē tikai vienu masīvu katram norādītajam terminam.

Lai ievadītu vairākus masīvus, mēs atkal izmantojam ampersandu " & ", lai savienotu masīvus kopā.

Apmācības soļi

Šie soļi jāievada pēc komata, kas ievadīta iepriekšējā posmā ROW_num līnijā INDEX funkciju dialoglodziņā .

1. Noklikšķiniet uz rindas Row_num pēc komata, lai ievietotu ievietošanas punktu pašreizējā ieraksta beigās.
2. Lai ievadītu diapazonu, izceliet darblapā no D6 līdz D11. Šī ir pirmā masīva funkcija ir meklēt.
3. Ierakstiet ampersandu " & " pēc šūnu atsaucēm D6: D11, jo mēs vēlamies, lai šī funkcija meklētu divus masīvus.
4. Lai darbvirsmā ievadītu diapazonu, izceliet šūnas no E6 uz E11. Šī ir otrā masīva funkcija ir meklēt.
5. Ierakstiet komatu "," pēc šūnu atsauces E3, lai pabeigtu MATCH funkcijas " Lookup_array" argumenta ierakstu .
6. Atstājiet INDEX funkciju dialoglodziņu, lai atvērtu nākamajam apmācības posmam.

06 no 09

Pievienojot atbilstības veidu un aizpildot MATCH funkciju

Trešais un pēdējais MATCH funkcijas arguments ir Match_type arguments.

Šis arguments pasaka Excel, kā saskaņot Lookup_value ar vērtībām Lookup_array. Izvēle 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 vislielāko vērtību, kas ir mazāka vai vienāda ar LookUp_value. "Lookup_array" dati ir jāsadala augošā secībā.
• ja match_type = 0: MATCH atrod pirmo vērtību, kas ir tieši vienāda ar LookUp_value. "Lookup_array" datus var sakā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. "Lookup_array" dati ir jāsadala dilstošā secībā.

Apmācības soļi

Šie soļi jāievada pēc komata, kas ievadīta iepriekšējā posmā ROW_num līnijā INDEX funkciju dialoglodziņā .

1. Pēc rindas Row_num komata ievadiet nulli " 0 ", jo mēs vēlamies, lai ligzdota funkcija atgrieztu precīzas atbilstības noteikumiem, kurus mēs ievadām šūnās D3 un E3.
2. Ievadiet aizvēršanas kronšteinu " ) ", lai pabeigtu MATCH funkciju.
3. Atstājiet INDEX funkciju dialoglodziņu, lai atvērtu nākamajam apmācības posmam.

07 no 09

Atpakaļ uz INDEX funkciju

Tagad, kad MATCH funkcija ir pabeigta, mēs pārietam uz atvērto dialoglodziņu trešo rindu un ievadīsim pēdējo argumentu INDEX funkcijai.

Šis trešais un pēdējais arguments ir Column_num arguments, kurā Excel paskaidro kolonnas numuru diapazonā no D6 līdz F11, kur tas atradīs informāciju, kuru mēs vēlamies atgriezt ar funkciju. Šajā gadījumā piegādātājs titāna logrīkiem .

Apmācības soļi

1. Dialoglodziņā noklikšķiniet uz līnijas Column_num .
2. Šajā rindiņā ievadiet skaitli trīs " 3 " (bez citātiem), jo mēs meklējam datus no diapazona D6 līdz F11 trešajā ailē.
3. Noklikšķiniet uz Labi vai aizveriet INDEX funkciju dialoglodziņu. Tas paliek atvērts nākamajam apmācības posmam - izveidojot masīvu formulu .

08 no 09

Array formulas izveidošana

Pirms dialoglodziņa aizvēršanas mums ir jāpārvērš mūsu ligzdotā funkcija masīvu formā .

Array formula ir tas, kas ļauj tai meklēt vairākus terminus datu tabulā. Šajā pamācībā mēs meklējam atbilstību diviem terminiem: logotipi no 1. slejas un titāna no 2. slejas.

Excel formātā masīvu formulas izveidošana tiek veikta, vienlaikus nospiežot tastatūras CTRL , SHIFT un ENTER taustiņus.

Nospiežot šos taustiņus kopā, funkcija ir ieslēgta ar cirtainiem balsteņiem: {}, kas norāda, ka tagad tā ir masīva formula.

Apmācības soļi

1. Kad pabeigtais dialoglodziņš joprojām ir atvērts no iepriekšējā apmācības posma, nospiediet un turiet taustiņus CTRL un SHIFT, pēc tam nospiediet un atlaidiet ENTER taustiņu.
2. Ja tas tiks izdarīts pareizi, dialoglodziņš būs aizvērts un # N / A kļūda parādīsies šūnā F3 - šūnas, kurā mēs ievadījām funkciju.
3. # N / A kļūda parādās šūnā F3, jo šūnas D3 un E3 ir tukšas. D3 un E3 ir šūnas, kurās mēs teicām funkciju, lai atrastu Lookup_values ​​5. apmācībā. Kad dati tiek pievienoti šīm divām šūnām, kļūda tiks aizstāta ar informāciju no datubāzes .

09 no 09

Meklēšanas kritēriju pievienošana

Pēdējais solis apmācībā ir pievienot meklēšanas vienumus mūsu darblapai.

Kā minēts iepriekšējā posmā, mēs meklējam, lai atbilstu terminiem Logrīki no 1. slejas un Titāna no 2. slejas.

Ja un tikai tad, ja mūsu formula atradīs atbilstību abiem noteikumiem attiecīgajās datu bāzē esošajās slejās, vai tā atgriež vērtību no trešās slejas.

Apmācības soļi

1. Noklikšķiniet uz šūnas D3.
2. Ierakstiet logrīkus un nospiediet tastatūras taustiņu Enter .
3. Noklikšķiniet uz šūnas E3.
4. Ierakstiet Titanium un nospiediet tastatūras taustiņu Enter .
5. Piegādātāja vārds Widgets Inc. jāieraksta šūnā F3 - funkcijas atrašanās vietā, jo tas ir vienīgais piegādātājs, kurš pārdod Titanium Widgets.
6. Kad jūs noklikšķināt uz šūnas F3 pilnā funkcija
   {= INDEKSS (D6: F11, MATCH (D3 un E3, D6: D11 un E6: E11, 0), 3)}
   parādās formulas joslā virs darblapas .

Piezīme: mūsu piemērā bija tikai viens piegādātājs titāna logrīkiem. Ja tam bija vairāk nekā viens piegādātājs, pirmais datu bāzē uzskaitītais piegādātājs atdod funkciju.