Naudodami masyvo formulę "Excel" mes galime sukurti paieškos formą, kuri naudoja keletą kriterijų, kad surastų informaciją duomenų bazėje arba duomenų lentelėje. Masyvo formulė apima funkcijos MATCH priskyrimą indekso funkcijoje.
Šioje pamokoje pateikiamas žingsnis po žingsnio pavyzdžio, kaip sukurti paieškos formą, kuri naudoja kelis kriterijus, norint rasti tiekėjotitano vitrinos imties duomenų bazėje. Sekite kartu, kad sužinotumėte, kaip lengva sukurti savo pasirinktines paieškos formules.
Paruoškite "Excel" darbaknygę su duomenimis
Kad galėtumėte sekti instrukcijų veiksmus, turėsite pradėti įvesti duomenų pavyzdį į šias langelius, kaip parodyta paveikslėlyje aukščiau. 3 ir 4 eilutės yra tuščios, kad būtų galima pritaikyti šioje instrukcijoje sukurtą masyvo formulę.
- Įveskite aukščiausią duomenų diapazoną į ląsteles D1 į F2
- Įveskite antrąjį intervalą į ląsteles D5 į F11
Mokomoji medžiaga neapima formato, matomo paveikslėlyje, tačiau tai neturės įtakos, kaip veikia paieškos formulė. Informacija apie formatavimo parinktis, panašias į anksčiau pateiktas, pateikiama šiame "Excel" formato vedlyje.
02 iš 08Sukurkite "INDEX" funkciją "Excel"
The INDEKSAS funkcija yra viena iš nedaugelio "Excel", kuri turi keletą formų. Funkcija turi Masyvo forma ir a Informacinė forma. Masyvo forma grąžina faktinius duomenis iš duomenų bazės ar duomenų lentelės, o informacinėje formoje pateikiama lentelės duomenų bazės ar lentelės vietos informacija.
Šioje pamokoje mes naudosime "Array form", nes norime sužinoti tiekėjo vardą titano elementams, o ne ląstelių nuorodą į šį tiekėją mūsų duomenų bazėje. Jei norite sukurti INDEKSO funkciją, atlikite šiuos veiksmus:
- Spustelėkite langelį F3 kad tai aktyvi ląstelė - čia mes pateiksime įdėtą funkciją.
- Spustelėkite ant Formulės juostos meniu skirtukas.
- Pasirinkite Ieškoti ir nuoroda iš juostos juostos atidaryti funkciją išskleidžiamajame meniu.
- Spustelėkite INDEKSAS sąraše atidaryti Pasirinkite argumentus dialogo langas.
- Pasirink masyvas, row_num, col_num parinktis dialogo lange.
- Spustelėkite Gerai atidaryti INDEX funkcijos dialogo langą; tai atvers Formulės Builder programoje "Excel".
- Viduje konors Formulės Builder, spustelėkite ant Masyvas linija
- Pažymėkite langelius D6 į F11, spustelėję ir vilkdami į darbalapį, kad įvesti asortimentą į statytoją.
Ankstesnėse Excel versijose Formulės Builder bus pakeistas Funkcija Argumentai langas. Taikykite tuos pačius veiksmus, kurie paminėti šiame vadove, į langą.
03 iš 08Paleiskite "Nested MATCH" funkciją
Kai vienoje funkcijoje priskiriama kita, neįmanoma atidaryti antros arba įdėtos funkcijos formulės konstruktoriaus o įveskite reikiamus argumentus. Įdėta funkcija turi būti įvedama kaip viena iš pirmosios funkcijos.
Įvesdami funkcijas rankiniu būdu, funkcijos argumentai atskiriami viena nuo kitos kableliais.
Pirmasis įvesties į įklijuotą MATCH funkciją įvedimas yra Lookup_value argumentas. "Lookup_value" bus vietovės arba ląstelės nuoroda paieškos terminui, kurį mes norime sutapti duomenų bazėje.
Paprastai "Lookup_value" priima tik vieną paieškos kriterijų arba terminą. Norint ieškoti kelių kriterijų, turime išplėsti "Lookup_value"; tjo daroma jungiant arba jungiant dvi ar daugiau ląstelių nuorodas kartu naudojant simbolį "ampersandas" - &.
- Viduje konors Formulės Builder, spustelėkite ant Row_num linija
- Įveskite funkcijos pavadinimą MATCH po to atidarytas apvalusis skliaustas.
- Spustelėkite langelį D3 įveskite langelio nuorodą į dialogo langą.
- Įrašykite ampersandą po ląstelės nuoroda D3 siekiant pridėti antrą langelį.
- Spustelėkite langelį E3 įeiti į antrąją ląstelių nuorodą.
- Po ląstelės nuoroda įveskite kablelį E3 baigti MATCH funkcijos įrašą Lookup_value argumentas.
Paskutiniame mokymo etape mūsų Lookup_values bus įrašytas į lakštus D3 ir E3 darbalapyje.
04 iš 08Užpildykite "Nested MATCH" funkciją
Šis žingsnis apima pridedant "Lookup_array" argumentas dėl įdėtos MATCH funkcija. "Lookup_array" yra ląstelių asortimentas, kurio MATCH funkcija ieškos, norėdami rasti "Lookup_value" argumentą, pridėtą ankstesniame žingsnio etape.
Kadangi mes nustatėme du paieškos laukelius "Lookup_array" argumentuose, mes turime padaryti tą patį ir "Lookup_array". MATCH funkcija ieško tik vieno masyvo kiekvienam nurodytam terminui, taigi, įveskite keletą matricų, mes vėl panaudojame ampersandą ir sujungi masyvus kartu.
Šie veiksmai turi būti įvedami po kableliais, įrašytais ankstesniame žingsnyje Row_num linija INDEKSAS funkcija.
- Spustelėkite ant Row_num po kablelės įterpimo tašką įrašyti dabartinio įrašo pabaigoje.
- Pažymėkite langelius D6 į D11 į darbalapį įveskite diapazoną - tai yra pirmasis masyvas, kurio funkcija yra ieškoti.
- Įrašykite ampersandą po ląstelių nuorodų D6: D11 nes mes norime, kad funkcija ieškotų dviejų matricų.
- Pažymėkite langelius E6 į E11 į darbalapį įveskite diapazoną - tai yra antrasis masyvas, kurio funkcija yra ieškoti.
- Po ląstelės nuoroda įveskite kablelį E3 užpildyti įrašą MATCH funkcija "Lookup_array" argumentas.
- Palikite INDEKSAS funkcijos dialogo langas atidaromas kitam žingsnio žingsniui.
Pridėti MATCH tipo argumentą
Trečias ir paskutinis MATCH funkcijos argumentas yra Match_type argumentas; ji pasakoja "Excel", kaip suderinti Lookup_value su reikšmėmis "Lookup_array" - galimi pasirinkimai yra 1, 0 arba -1.
Šis argumentas yra neprivalomas. Jei jis praleistas, funkcija naudoja numatytąją 1 reikšmę.
- Jei Match_type = 1 arba praleistas: MATCH randa didžiausią vertę, kuri yra mažesnė arba lygi Lookup_valuei. "Lookup_array" duomenys turi būti rūšiuojami didėjančia tvarka.
- Jei Match_type = 0: MATCH nustato pirmąją vertę, kuri yra lygi "Lookup_value" tikslui. "Lookup_array" duomenys gali būti rūšiuojami bet kokia tvarka.
- Jei Match_type = -1: MATCH nustato mažiausią vertę, didesnę arba lygią "Lookup_value". "Lookup_array" duomenys turi būti rūšiuojami mažėjančia tvarka.
Šie veiksmai turi būti įvedami po kableliais, įrašytais ankstesniame žingsnyje Row_num linija INDEKSAS funkcija.
- Po kableliais Row_num eilutėje įrašykite nulį - 0 - Kadangi mes norime, kad įdėta funkcija grąžintų tikslius atitikimus įvestiems į ląsteles terminus D3 ir E3.
- Įveskite uždaromąją skliaustą - ) - užpildyti MATCH funkcija.
- Palikite Formulės Builder atidarykite kitą žingsnį.
Indekso funkcijos užbaigimas
Dabar, kai MATCH funkcija daroma, mes persikelsime į trečią Formulės statybininko eilutę ir įveskite paskutinį argumentą INDEKSAS funkcija. Šis trečias ir paskutinis argumentas yra Column_num argumentas, kuris Excel nurodo stulpelio numerį diapazone D6 į F11; tai yra ta vieta, kur ji suras informaciją, kurią mes norime sugrąžinti pagal funkciją. Šiuo atveju tiekėjas Titano raštai.
- Spustelėkite ant Column_num linija
- Įveskite tris numerius - 3 - šioje eilutėje, nes mes ieškome duomenų trečioje diapazono stulpelyje D6 į F11.
- Vėlgi palikite Formulės Builder atidarykite kitą žingsnį.
Kūrimo masyvo formulė
Prieš uždarydami Formulės Builder, mes turime pasukti įdėtą funkciją į masyvo formulę; tai leidžia mūsų funkcijai ieškoti kelių terminų duomenų lentelėje. Šiame vadove mes ieškome suderinamų dviejų terminų: Widgets iš 1 ir 2 stulpelių Titanas iš 2 stulpelio.
"Excel" masyvo formulės sudarymas atliekamas paspaudus CTRL, SHIFT, ir ENTER klavišus tuo pačiu metu. Paspaudus, funkcija bus apsupta garbančiais petnešomis, todėl funkcija dabar yra masyvas.
- Uždaryk Formulės Builder spustelėję mygtuką padaryta mygtukas.
- Tada pasirinkite ląstelę F4 ir paspauskite Įveskite raktas, norint peržiūrėti formulę.
- Norėdami konvertuoti formulę į masyvą, vienu metu paspauskite CTRL + SHIFT + ENTER ant klaviatūros.
- Jei teisingai padaryta a # N / A klaida pasirodys langelyje F3 - ląstelėje, kurioje mes įvedėme funkciją.
- The # N / A langelyje rodoma klaida F3 nes ląstelės D3 ir E3 yra tušti. D3 ir E3 yra ląstelės, kuriose mes pasakėme funkciją rasti Lookup_values. Kai duomenys bus įtraukti į šias dvi ląsteles, klaida bus pakeista informacija iš duomenų bazės.
Paieškos kriterijų pridėjimas
Galiausiai mes pridėsime paieškos terminus į mūsų darbalapį. Kaip minėta ankstesniame žingsnyje, mes ieškome atitikti terminus Widgets iš 1 ir 2 stulpelių Titanas iš 2 stulpelio. Jei ir tik jei mūsų formulė randa abiejų terminų atitiktį atitinkamose duomenų bazės stulpeliuose, ji grąžins vertę iš trečio stulpelio.
- Spustelėkite langelį D3.
- Įveskite Widgets ir paspauskite Įveskite raktas ant klaviatūros.
- Spustelėkite langelį E3.
- Įveskite Titanas ir paspauskite Įveskite raktas ant klaviatūros.
- Tiekėjo pavadinimas "Widgets Inc." turėtų pasirodyti langelyje F3 - funkcijos vieta, nes ji yra vienintelis tiekėjas, kuris parduoda Titanium Widgets.
- Kai paspausite langelį F3, visa funkcija pasirodys formulės juostoje virš darbalapio.
{= INDEKSAS (D6: F11, MATCH (D3 ir E3, D6: D11 ir E6: E11, 0), 3)}
Mūsų pavyzdyje buvo tik vienas titano elementų tiekėjas. Jei buvo daugiau nei vienas tiekėjas, pirmiausia duomenų bazėje įrašytas tiekėjas grąžina šią funkciją.
