Excel array formula je formula, ki izvaja izračune na vrednosti v eni ali več matrikah, ne pa na eni podatkovni vrednosti. V programih za preglednice je matrika obseg ali vrsta povezanih podatkovnih podatkov, ki so običajno shranjene v sosednjih celicah v delovnem listu. Ta priročnik razlaga, kako uporabljati formule matrike v vseh sodobnih različicah Excela.
Ta vadnica je združljiva z večino različic programa Excel, vključno z letoma 2007, 2010, 2013, 2016 in Office 365.
Kaj so matrične formule?
Formule matrike so podobne rednim formulam:
- Začnejo z enakim znakom ( = )
- Uporabite isto sintakso kot redne formule
- Uporabite iste matematične operaterje
- Sledite istemu vrstnemu redu delovanja
V Excelu so matrične formule obkrožene z zavihanimi oporniki { } - teh zavor ni mogoče vnesti samo; jih je treba dodati v formulo s pritiskom na Ctrl , Shift , in Vnesite ključe po vnašanju formule v celico ali celice. Iz tega razloga se formula za matrike včasih imenuje a CSE formula v Excelu.
Izjema od tega pravila je, kadar se za vnos v matriko uporabljajo kot kodirani zavoji kot argument za funkcijo, ki običajno vsebuje samo eno vrednost ali referenco celice. Na primer, v vajah, ki jih spodaj uporablja VLOOKUP in IZBERITE funkcijo ustvariti levo lookup formulo, array je ustvarjen za IZBERITE funkcije Index_num argument, tako da vnesete oklepaje okoli vnesene matrike.
Koraki za izdelavo formule matrike
- Vnesite formulo.
- Drži dol Ctrl in Shift tipke na tipkovnici.
- Pritisnite in spustite Vnesite ključ za ustvarjanje matrične formule.
- SpustiteCtrl in Shift ključi.
Če bo pravilno opravljeno, bo formula obkrožena z zavihanimi oporniki, vsaka celica s formulo pa bo vsebovala drugačen rezultat.
Urejanje formule matrike
Kadarkoli je urejena formula matrike, se zavite utrjene oblike izginejo iz okrog matrike. Če jih želite vrniti, morate vnesti formulo matrike s pritiskom na Ctrl, Shift, in Vnesite spet enako kot takrat, ko je bila prva formula ustvarjena.
Vrste matričnih formul
Obstajata dve glavni vrsti matričnih formul:
- Enolične celične matrike, ki izvajajo več izračunov v eni sami celici.
- Formule večceličnih matrik, ki se nahajajo v več kot eni preglednici celice.
Formule enojnih celičnih matrik
Ta vrsta formule matrike uporablja funkcijo, na primer SUM, POVPREČNO, ali COUNT, da združijo izhod formule večceličnih matrik v eno samo vrednost v eni sami celici. Primer enolične celične formule bi bil:
{= SUM (A1: A2 * B1: B2)}
Ta formula dodaja skupaj izdelek A1 * B1 in A2 * B2, in nato vrne en sam rezultat v eni celici v delovnem listu. Drugi način pisanja zgornje formule bi bil:
Formule večmestnih matrik
Kot navaja njihovo ime, se te matrične formule nahajajo v več celicah delovnega lista in vrnejo matriko kot odgovor. Z drugimi besedami, enaka formula se nahaja v dveh ali več celicah in vrne različne odgovore v vsaki celici.
Vsaka kopija ali primer, formule matrike izvede isti izračun v vsaki celici, v kateri se naseljuje, vendar vsaka instanca formule v svojih izračunih uporablja različne podatke. Zato vsak primer proizvaja različne rezultate. Primer formule s številnimi celicami bi bil videti tako:
{= A1: A2 * B1: B2}
Če se zgornji primer nahaja v celice C1 in C2 na delovnem listu, potem bi bili rezultati naslednji:
- Podatki v A1 se pomnožijo s podatki v B1, rezultati pa so shranjeni v celica C1.
- Podatki v A2 se pomnožijo s podatki v B2, rezultati pa so shranjeni v celica C2.
Uporaba nizov za prenos vrstic in stolpcev
The TRANSPOSE funkcija se uporablja za kopiranje podatkov iz vrstice v stolpec ali obratno. Ta funkcija je ena izmed redkih v Excelu, ki jo je vedno treba uporabljati kot matrično formulo.
Formule matrike in funkcije Excel
Veliko Excelovih vgrajenih funkcij, kot so SUM, POVPREČNO, in COUNT, lahko uporabite tudi v matrični formuli. Obstaja tudi nekaj funkcij, kot je TRANSPOSE funkcijo, ki mora biti vedno vpisana kot matrika, da bi lahko pravilno delovala. Uporabnost številnih funkcij, kot so INDEKS in MATCH ali MAX in ČE se lahko razširijo z njimi skupaj v matrični formuli.
Primeri matričnih formul v Excelu:
01 od 08Ustvarite enostavno enoto celične matrike
Formule enovite celične matrike običajno najprej izvedejo izračun multi-celice in nato uporabijo funkcijo, kot je npr POVPREČNO ali SUM združiti izhodno polje matrike v en sam rezultat.
02 od 08Prezrite vrednosti napak pri iskanju podatkov
Ta niz formula uporablja POVPREČNO, ČE, in ISNUMBER funkcije, da bi našli povprečno vrednost za obstoječe podatke, medtem ko ignorirajo vrednosti napak, kot so # DIV / 0! in #NAME?
03 od 08Številne celice podatkov
Uporabi SUM inČE funkcije v matrični formuli za štetje celic podatkov, ki izpolnjujejo enega od več pogojev; ta tehnika se razlikuje od uporabe Excelove COUNTIFS funkcija, ki zahteva izpolnitev vseh nastavljenih pogojev, preden se celica šteje.
04 od 08Poiščite največjo pozitivno ali negativno številko
Ta primer združuje MAX funkcijo inČE funkcijo v matrični formuli, ki najde največjo ali največjo vrednost za vrsto podatkov, ko so izpolnjena določena merila.
05 od 08Poiščite najmanjšo pozitivno ali negativno številko
Podobno kot zgornji primer, ta združuje MIN funkcijo in IF funkcijo v matrični formuli, da bi našli najmanjšo ali najmanjšo vrednost za vrsto podatkov, ko so izpolnjena določena merila.
06 od 08Poiščite srednjo ali srednjo vrednost
The MEDIAN funkcija v Excelu najde srednjo vrednost za seznam podatkov. S kombiniranjem z ČE funkcijo v matrični formuli, lahko najdete srednjo vrednost za različne skupine povezanih podatkov.
07 od 08Izdelajte formulo za iskanje z več merili
Ta niz formula vsebuje gnezdenje MATCH in INDEKS funkcije za iskanje določenih podatkov v bazi podatkov.
08 od 08Naredite levo formulo za iskanje
The VLOOKUP funkcija običajno išče samo podatke, ki se nahajajo v stolpcih na desni, vendar jih združujejo z IZBERITE funkcijo, lahko ustvarite levo lookup formulo, ki bo iskanje stolpcev podatkov na levi strani Lookup_value prepir.
