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ā.
Šī apmācība ietver pakāpenisku piemēru, kā izveidot meklēšanas formulu, kas izmanto vairākus kritērijus, lai atrastu piegādātājutitāna logrīki paraugu datu bāzē. Seko līdzi, lai uzzinātu, cik viegli ir izveidot savas pielāgotās uzmeklēšanas formulas.
Sagatavojiet Excel darbgrāmatu ar datiem
Lai sekotu šīs apmācības darbībām, jums vispirms jāievada parauga dati šajās lapās, kā parādīts attēlā, kas atrodas augšpusē. Rindas 3 un 4 paliek tukšas, lai pielāgotu šajā apmācībā izveidoto masīvu formulu.
- Ievadiet vislabāko datu diapazonu šūnās D1 uz F2
- Ievadiet otro diapazonu šūnās D5 uz F11
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 08Izveidojiet indeksa funkciju programmā Excel
The INDEX funkcija ir viens no nedaudzajiem programmā Excel, kuram ir vairākas formas. Funkcijai ir Masīvu forma un a 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īvu 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ē. Lai izveidotu INDEX funkciju, izpildiet šīs darbības:
- Noklikšķiniet uz šūnas F3 lai padarītu to par aktīvo elementu - tieši tur mēs ievadīsim ligzdoto funkciju.
- Noklikšķiniet uz Formulas lentas izvēlnes cilne.
- Izvēlies Meklēt un atsauce no lentes joslas, lai atvērtu funkciju nolaižamo.
- Klikšķiniet uz INDEX sarakstā, lai atvērtu Atlasiet Argumenti dialoglodziņš.
- Izvēlies masīvs, row_num, col_num dialoglodziņā.
- Klikšķis labi lai atvērtu INDEX funkciju dialoglodziņu; tas atver Formulas veidotāju programmā Excel.
- Iekš Formulas veidotājs, noklikšķiniet uz Masīvs līnija
- Izcelt šūnas D6 uz F11, noklikšķinot un velkot, darblapā, lai ievadītu reklāmu diapazonu celtniekā.
Vecākajās Excel versijās Formulas veidotājs tiks aizstāts ar Funkcijas Argumenti logs Izmantojiet tādas pašas darbības, kā norādīts šajā apmācībā logā.
03 no 08Sāciet Nested MATCH funkciju
Ja viena funkcija tiek ievietota citā, nav iespējams atvērt otru vai ligzdotu funkciju formulas būvētāju o ievadiet nepieciešamos argumentus. Nesaistītā funkcija ir jāieraksta kā viena no pirmajā funkcijā šķirotajām.
Ievadot funkcijas manuāli, funkciju argumenti ir atdalīti viens no otra ar komatu.
Pirmais solis, ievadot ligzdoto MATCH funkciju, ir ievadīt Lookup_value arguments. 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; tviņš tiek darīts, apvienojot vai pievienojot divas vai vairākas šūnu atsauces kopā, izmantojot simbolu - &.
- Iekš Formulas veidotājs, noklikšķiniet uz Row_num līnija
- Ievadiet funkcijas nosaukumu MATCH kam seko atvērta kronšteina.
- Noklikšķiniet uz šūnas D3 lai ievadītu šai šūnas atsaucei dialoglodziņā.
- Pēc šūnas atsauces ierakstiet rakstzīmi D3 lai pievienotu otro šūnu atsauci.
- Noklikšķiniet uz šūnas E3 lai ievadītu otro šūnu atsauci.
- Ierakstiet komatu pēc šūnas atsauces E3 lai pabeigtu MATCH funkcijas ierakstu Lookup_value arguments.
Pēdējā apmācības posmā mūsu Lookup_values tiks ievadīts darblapas D3 un E3 šūnās.
04 no 08Pabeigt Nested MATCH funkciju
Šis solis ietver pievienošanu Lookup_array arguments par ligzdotu MATCH funkcija. 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 definētajam terminam, tādējādi, lai ievadītu vairākus masīvus, mēs atkal izmantojam ampersandu, lai savienotu masīvus kopā.
Šīs darbības jāievada pēc komata, kas ievadīta iepriekšējā solī Row_num līnija INDEX funkcija.
- Noklikšķiniet uz Row_num pēc komata ievietot ievietošanas punktu pašreizējā ieraksta beigās.
- Izcelt šūnas D6 uz D11 darblapā, lai ievadītu diapazonu - tas ir pirmais masīvs, kura funkcija ir meklēt.
- Ievadiet ampersandu pēc šūnu atsaucēm D6: D11 jo mēs vēlamies, lai šī funkcija meklētu divus masīvus.
- Izcelt šūnas E6 uz E11 darblapā, lai ievadītu diapazonu - tas ir otrais masīvs, kurai ir nepieciešama meklēšana.
- Ierakstiet komatu pēc šūnas atsauces E3 lai pabeigtu ierakstu MATCH funkciju Lookup_array arguments.
- Atstājiet INDEX funkciju dialoglodziņš, kas atvērts nākamajam apmācības posmam.
Pievienojiet MATCH tipa argumentu
Trešais un pēdējais arguments MATCH funkcija ir Match_type arguments; tas stāsta Excel, kā saskaņot Lookup_value ar vērtībām Lookup_array - pieejamās izvēles 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ā.
Šīs darbības jāievada pēc komata, kas ievadīta iepriekšējā solī Row_num līnija INDEX funkcija.
- Pēc komata uz Row_num līnija, ierakstiet nulli - 0 - tā kā mēs vēlamies, lai ligzdota funkcija atgrieztos precīzas atbilstības noteikumiem, kurus mēs ievadījām šūnās D3 un E3.
- Ierakstiet aizvēršanas kronšteinu - ) - pabeigt MATCH funkcija.
- Atstājiet Formulas veidotājs atveriet nākamo pamācību apmācībā.
INDEKSA FUNKCIJAS pabeigšana
Tagad, kad MATCH funkcija ir pabeigta mēs pārietam uz trešo līniju Formula Builder un ievadiet pēdējo argumentu par INDEX funkcija. Šis trešais un pēdējais arguments ir Column_num arguments, kas Excel paskaidro kolonnas numuru diapazonā D6 uz F11; šajā vietā tā atradīs informāciju, kuru mēs vēlamies atdot ar funkciju. Šajā gadījumā piegādātājs ir titāna logrīki.
- Noklikšķiniet uz Column_num līnija
- Ievadiet numuru trīs - 3 - šajā rindiņā, jo mēs meklējam datus diapazona trešajā slejā D6 uz F11.
- Atkal atstājiet Formulas veidotājs atveriet nākamo pamācību apmācībā.
Array formulas izveidošana
Pirms slēgšanas Formulas veidotājs, mums jāpārvērš mūsu ligzdoto funkciju masīvu formā; tas ļauj mūsu funkcijai meklēt vairākus vārdus datu tabulā. Šajā apmācībā mēs meklējam atbilstību diviem terminiem: Logrīki no 1. un 2. slejas Titāna no 2. slejas.
Excel masīvu formulas izveidošana tiek veikta, nospiežot CTRL, SHIFT, un ENTER vienlaikus tastatūras taustiņi. Pēc tam, kad tas ir nospiests, funkciju ieskauj liektas lentes, kas norāda, ka funkcija tagad ir masīva.
- Aizveriet Formulas veidotājs noklikšķinot uz Gatavs poga
- Pēc tam atlasiet šūnu F4 un nospiediet Ievadiet lai apskatītu formulu.
- Lai pārveidotu Formulu masīvā, vienlaicīgi nospiediet CTRL + SHIFT + ENTER uz tastatūras.
- Ja izdarīts pareizi a # N / A kļūda parādīsies šūnā F3 - šūna, kurā mēs ievadījām funkciju.
- The # N / A kļūda tiek parādīta šū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. Kad dati tiek pievienoti šīm divām šūnām, kļūda tiks aizstāta ar informāciju no datubāzes.
Meklēšanas kritēriju pievienošana
Visbeidzot, mēs pievienosim meklēšanas vienumus mūsu darba lapai. Kā minēts iepriekšējā solī, mēs meklējam atbilstību noteikumiem Logrīki no 1. un 2. slejas Titāna no 2. kolonas. 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.
- Noklikšķiniet uz šūnas D3.
- Tips Logrīki un nospiediet Ievadiet tastatūras taustiņš.
- Noklikšķiniet uz šūnas E3.
- Tips Titāna un nospiediet Ievadiet tastatūras taustiņš.
- Piegādātāja vārds Widgets Inc. vajadzētu parādīties šūnā F3 - funkcijas atrašanās vieta, jo tā ir vienīgā piegādātāja, kas pārdod Titanium Widgets.
- Noklikšķinot uz šūnas F3, pilnā funkcija parādās formulas joslā virs darblapas.
{= INDEKSS (D6: F11, MATCH (D3 un E3, D6: D11 un E6: E11, 0), 3)}
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.
