Reguli și exemple pentru formulele matrice

Reguli și exemple pentru formulele matrice

Notă: Dorim să vă oferim cel mai recent conținut de ajutor, cât mai rapid posibil, în limba dvs. Această pagină a fost tradusă automatizat și poate conține erori gramaticale sau inexactități. Scopul nostru este ca acest conținut să vă fie util. Vă rugăm să ne spuneți dacă informațiile v-au fost utile, în partea de jos a acestei pagini. Aici se află articolul în limba engleză, ca să îl puteți consulta cu ușurință.

O formulă matrice este o formulă care poate efectua calcule mai multe unul sau mai multe elemente dintr-o matrice. Vă puteți gândi a unei matrice ca un rând sau o coloană de valori sau o combinație de rânduri și coloane de valori. Formulele matrice poate returna mai multe rezultate sau un singur rezultat.

Începând cu septembrie 2018 actualizare pentru Office 365, orice formula care poate returna mai multe rezultate va automat extindă le în jos sau în în celulele vecine. Această modificare în comportament, de asemenea, este însoțit de mai multe noi funcții dinamice matrice. Formulele matrice dinamice, dacă acestea utilizați funcțiile existente sau funcții dinamice matrice, trebuie doar să fie de intrare într-o singură celulă, apoi confirmat apăsând Enter. Formulele matrice mai devreme, moștenite necesită mai întâi selectând zona întreaga ieșire, apoi să confirme formula cu Ctrl + Shift + Enter. Când sunteți denumit ca formule CSE .

Puteți utiliza formule pentru a efectua sarcini complexe, cum ar fi:

  • Creați rapid seturi de date eșantion.

  • Contorizarea numărului de caractere conținute într-o zonă de celule.

  • Însumarea doar a numerelor care îndeplinesc anumite condiții, cum ar fi cele mai scăzute valori dintr-o zonă sau numere care se încadrează între un superioară și inferioară.

  • Însumarea a fiecărei a n-a valori dintr-o zonă de valori.

Exemplele următoare vă arată cum să creați formule matrice cu mai multe celule și cu o singură celulă. Dacă este posibil, am inclus exemple cu unele din dinamic matrice funcții, cât și formulele de matrice existente introdusă ca matrice atât dinamice și moștenite.

Descărcați exemplele noastre

Descărcați un registru de lucru exemplu cu toate exemple de formule de matrice în acest articol.

Acest exercițiu vă arată cum să utilizați formulele matrice cu mai multe celule și cu o singură celulă pentru a calcula un set de cifre de vânzări. Primul set de pași utilizează o formulă cu mai multe celule pentru a calcula un set de subtotaluri. Al doilea set utilizează o formulă cu o singură celulă pentru a calcula un total general.

  • Formulele matrice cu celule multiple

    Funcția matrice cu celule multiple în celula H10 = F10:F19 * G10:G19 pentru a calcula numărul de mașini vândute de preț unitar

  • Aici vă sunteți calculul totalul vânzărilor de Coupe și Sedan pentru fiecare vânzător, introducând = F19:F19 * G10:G19 în celula H10.

    Atunci când apăsați Enter, veți vedea rezultatele extindă în jos la celulele H10:H19. Observați că zona jocuri este evidențiată cu o bordură atunci când selectați orice celulă din intervalul jocuri. S-ar putea observa că formulele din celulele H10:H19 sunt estompate. Acestea sunt acolo doar pentru referință, astfel încât dacă doriți să se adapteze formula, va trebui să selectați celula H10, în cazul în care se află formula coordonatoare.

  • Formulă matrice singură celulă

    Formulă de matrice singură celulă pentru a calcula un total general cu =SUM(F10:F19*G10:G19)

    În celula H20 din registrul de lucru exemplu, tastați sau copiați și lipiți =SUM(F10:F19*G10:G19)și apoi apăsați pe Enter.

    În acest caz, Excel înmulțește valorile din matricea (zona de celule F10 prin G19) și apoi utilizează funcția SUM pentru a adăuga totaluri împreună. Rezultatul este un total de $1,590,000 în vânzări.

    Acest exemplu vă arată cum puternic poate fi acest tip de formulă. De exemplu, să presupunem că aveți 1000 de rânduri de date. Pot aduna parțial sau complet de date prin crearea unei formule de matrice într-o singură celulă în loc să glisați formula în jos prin rândurile 1000. De asemenea, observați că formula singură celulă în celula H20 este complet independent de formule cu mai multe celule (în formulă în celula H10 până H19). Acesta este un alt avantaj al utilizării formulelor matrice-flexibilitate. Puteți schimba alte formule din coloana H fără a afecta formula din H20. Poate fi, de asemenea, o idee bună să aibă totalurilor independent astfel, deoarece ajută valida acuratețea rezultatelor.

  • Formulele matrice dinamice oferă și următoarele avantaje:

    • Consistența    Dacă faceți clic pe oricare dintre celulele din H10 în jos, veți vedea aceeași formulă. Consistența care vă pot ajuta să asigurați-vă mai mare precizie.

    • Toate, fiți precaut    Nu puteți să Suprascrieți o componentă a unei formule matrice cu celule multiple. De exemplu, faceți clic pe celula H11 și apăsați pe Delete. Excel nu se va schimba rezultatul din matrice. Pentru a modifica, trebuie să selectați celula din stânga sus din matrice sau celula H10.

    • Dimensiune redusă a fișierelor    Adesea puteți utiliza o singură formulă matrice în loc de mai multe formule intermediar. De exemplu, exemplul vânzări cu mașini utilizează o formulă matrice pentru a calcula rezultate în coloana E. Dacă ați avut utilizat standard formule cum ar fi = F10 * G10, F11 * G11, F12 * G12, etc., care ar fi folosit 11 formule diferite pentru a calcula aceleași rezultate. Care nu este o mare de lucru, dar ce se întâmplă dacă ați avut mii de rânduri la total? Apoi se poate face diferența mare.

    • Eficiența    Funcții de matrice poate fi o modalitate eficientă a construi formule complexe. Matricea =SUM(F10:F19*G10:G19) formulă este la fel ca aceasta: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Deversând    Formulele matrice dinamice va extindă automat în zona de ieșire. Dacă datele sursă se află într-un tabel Excel, apoi formulele matrice dinamice va Redimensionați automat pe măsură ce adăugați sau eliminați date.

    • #SPILL! eroare    Matrice dinamică a introdus #SPILL! eroare, care indică faptul că au fost proiectate jocuri zona este blocat pentru un motiv sau altul. Când ați rezolvat blocajul, formula va extindă automat.

Constantele matrice sunt o componentă a formulelor matrice. Creați constante matrice introducând o listă de elemente, apoi încadrând manual lista cu acolade ({ }), astfel:

= {1,2,3,4,5} sau = {"Ianuarie", "Februarie", "Martie"}

Dacă le separați elementele utilizând virgule, creați o matrice orizontală (un rând). Dacă le separați elementele utilizând punct și virgulă, creați o matrice verticală (o coloană). Pentru a crea o matrice bidimensională, elementele din fiecare rând cu virgule ce separă și ce separă fiecare rând cu punct și virgulă.

Următoarele proceduri veți exersa crearea constantelor orizontale, verticale și bidimensionale. Vă vom arăta exemple utilizează funcția secvență pentru a genera automat constantele matrice, precum introduse manual constantele matrice.

  • Crearea unei constante orizontale

    Utilizați registrul de lucru din exemplele anterioare sau creați un registru de lucru nou. Selectați orice celulă necompletată și introduceți =SEQUENCE(1,5). Funcția secvență construiește un rând 1 5 coloana matrice la fel ca = {1,2,3,4,5}. Se afișează următorul rezultat:

    Crearea unei constante de matrice orizontală cu =SEQUENCE(1,5) sau = {1,2,3,4,5}

  • Crearea unei constante verticale

    Selectați orice celulă necompletată, cu sala de sub ea și introduceți =SEQUENCE(5)sau = {1; 2; 3; 4; 5}. Se afișează următorul rezultat:

    Crearea unei constante de matrice verticală cu = SEQUENCE(5) sau = {1; 2; 3; 4; 5}

  • Crearea unei constante bidimensionale

    Selectați orice celulă necompletată, cu sala de la dreapta și dedesubt și introduceți =SEQUENCE(3,4). Puteți vedea următorul rezultat:

    Creați un rând 3, 4 coloane constantă matrice cu =SEQUENCE(3,4)

    De asemenea, puteți introduce: sau = {1,2,3,4; 5,6,7,8; 9,10,11,12}, dar doriți să plătiți atenția asupra unde vă plasați virgulă comparativ cu virgule.

    Cum pot vedea, secvență opțiunea oferă avantaje semnificative prin introducerea manuală a valorile constante de matrice. În primul rând, salvează timpul, dar, de asemenea, vă poate ajuta reduce erorile de intrare manuală. De asemenea, este mai ușor de citit, mai ales în virgulă pot fi greu de a putea distinge de separatori virgulă.

Iată un exemplu care utilizează matrice constante ca parte dintr-o formulă mai mare. În registrul de lucru eșantion, accesați foaia de lucru constantă într-o formulă sau creați o nouă foaie de lucru.

În celula D9, am introdus =SEQUENCE(1,5,3,1), dar ați fi putut introduce, de asemenea, 3, 4, 5, 6 și 7 în celulele A9:H9. Nu este nimic special despre acea anumită selecție număr, am ales doar altă cifră decât 1-5 pentru diferențierea.

În celula E11, introduceți = SUM (D9:H9*SEQUENCE(1,5)), sau = SUM (D9:H9* {1,2,3,4,5}). Formulele returnată 85.

Utilizarea constantelor de matrice în formulele. În acest exemplu, am utilizat = SUM (D9:H(*SEQUENCE(1,5))

Funcția secvență construiește echivalentul matrice constantă {1,2,3,4,5}. Deoarece Excel execută operațiile expresiile dintre paranteze mai întâi, următoarele două elemente care intră în joc sunt valorile celulelor în D9:H9 și operator de înmulțire (*). În acest moment, formula înmulțește valorile din matricea stocată cu valorile corespunzătoare din Constanta. Acesta este echivalent pentru:

=SUM(D9*1,E9*2,F9*3,G9*4,H9*5)sau =SUM(3*1,4*2,5*3,6*4,7*5)

În fine, funcția SUM adună valorile și returnează 85.

Pentru a evita utilizarea matricea stocată și a păstra operațiunea în întregime în memorie, îl puteți înlocui cu alt constantă matrice:

=SUM(Sequence(1,5,3,1)*Sequence(1,5))sau =SUM({3,4,5,6,7}*{1,2,3,4,5})

Elemente care se pot utiliza în constantele matrice

  • Constantele matrice poate conține numere, text, valori logice (cum ar fi TRUE sau FALSE) și valorile de eroare, cum ar fi #N/A. Puteți să utilizați numere în formate de număr întreg, zecimal și științific. Dacă includeți text, trebuie să îl înconjoară cu ghilimele ("text").

  • Constantele matrice nu pot conține matrice suplimentare, formule sau funcții. Cu alte cuvinte, pot conține numai text sau numere separate prin bare oblice inverse sau punct și virgulă. Excel afișează un mesaj de avertisment când introduceți o formulă ca {1\2\A1:D4} sau {1\2\SUM(Q2:Z8)}. De asemenea, valorile numerice nu pot conține semnul procent, semnul dolar, bare oblice inverse sau paranteze.

Unul dintre cele mai bune metode pentru a utiliza constantele matrice este să le denumiți. Constante denumite pot fi mult mai ușor de utilizat și le puteți ascunde unele dintre complexitatea formulele matrice de alte persoane. La denumirea unei constante de matrice și utilizați-l într-o formulă, procedați astfel:

Accesați formulele > definite numele > Definire nume. În caseta nume, tastați trimestru1. În caseta se referă la, introduceți următoarea constantă (nu uitați să introduceți acoladele):

={"Ianuarie"\"Februarie"\"Martie"}

În caseta de dialog acum ar trebui să arate astfel:

Adăugați o constantă matrice denumite din formule > nume definite > Manager nume > nou

Faceți clic pe OK, apoi selectați orice rând cu trei celule necompletate și introduceți = trimestru1.

Se afișează următorul rezultat:

Utilizarea unei constante de matrice denumite într-o formulă, cum ar fi = trimestru1, în cazul în care a fost definită trimestru1 ca = {"Ianuarie", "Februarie", "martie"}

Dacă doriți ca rezultatele să se extindă pe verticală în loc de pe orizontală, utilizați =TRANSPOSE(trimestru1).

Dacă doriți să afișați o listă de 12 luni, cum ar fi puteți utiliza atunci când construiți un raport financiar, vă puteți baza una de pe anul curent cu funcția secvență. Lucru arate îngrijit despre această funcție este că, chiar dacă se afișează doar luna, există o dată calendaristică validă, în spatele ei care se pot utiliza în alte calcule. Veți găsi aceste exemple pe foile de lucru denumită constantă matrice și set de date eșantion rapidă în registrul de lucru exemplu.

=TEXT(date(Year(Today()),Sequence(1,12),1),"mmm")

Utilizați o combinație a funcțiilor TEXT, dată, an, astăzi, și secvență pentru a construi o listă dinamică de 12 luni

Acest lucru utilizează funcția DATE pentru a crea o dată bazat pe anul curent, secvență creează o constantă matrice de la 1 la 12 pentru ianuarie până în decembrie, atunci funcția TEXT efectuează conversia la formatul de afișare pentru a "mmm" (Ian, Feb, Mar, etc.). Dacă ați vrut să se afișeze nume complet, cum ar fi ianuarie, puteți utiliza "llll".

Atunci când utilizați o constantă denumită ca formulă matrice, nu uitați să introduceți semnul egal, ca în = trimestru1, nu doar trimestru1. Dacă nu, Excel interpretează matrice ca un șir de text și formula nu vor funcționa așa cum vă așteptați. În sfârșit, rețineți că puteți să utilizați combinații de funcții, text și numere. Totul depinde de modul creativ pe care doriți să obțineți.

Următoarele exemple demonstrează câteva moduri în care se poate pune constantele matrice de utilizat în formule matrice. Unele dintre aceste exemple utilizează funcția TRANSPOSE se convertesc rândurile la coloane și invers.

  • Mai multe fiecărui element dintr-o matrice

    Introduceți = secvență (1,12) * 2, sau = {1,2,3,4; 5,6,7,8; 9,10,11,12} * 2

    Puteți, de asemenea, împărțiți cu (/), adăugați cu (+), și scăderea cu (-).

  • Ridicați la pătrat elementele dintr-o matrice

    Introduceți = secvență (1,12) ^ 2, sau = {1,2,3,4; 5,6,7,8; 9,10,11,12} ^ 2

  • Găsiți rădăcina pătrată a pătratelor elementelor dintr-o matrice

    Introduceți =SQRT(SEQUENCE(1,12)^2), sau =SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)

  • Transpunerea unui rând unidimensional

    Introduceți =TRANSPOSE(SEQUENCE(1,5))sau =TRANSPOSE({1,2,3,4,5})

    Chiar dacă ați introdus o constantă matrice orizontală, funcția TRANSPOSE efectuează conversia constantei matrice într-o coloană.

  • Transpunerea unei coloane unidimensionale

    Introduceți =TRANSPOSE(SEQUENCE(5,1))sau = TRANSPOSE ({1; 2; 3; 4; 5})

    Chiar dacă ați introdus o constantă matrice verticală, funcția TRANSPOSE efectuează conversia constantei într-un rând.

  • Transpunerea unei constante bidimensionale

    Introduceți =TRANSPOSE(SEQUENCE(3,4))sau = TRANSPOSE ({1,2,3,4; 5,6,7,8; 9,10,11,12})

    Funcția TRANSPOSE efectuează conversia fiecărui rând într-o serie de coloane.

Această secțiune oferă exemple de formule matrice simple.

  • Crearea unei matrice din valori existente

    Următorul exemplu vă arată cum să utilizați formule matrice pentru a crea o matrice nou dintr-o matrice existente.

    Introduceți =SEQUENCE(3,6,10,10)sau = {10,20,30,40,50,60; 70,80,90,100,110,120; 130,140,150,160,170,180}

    Asigurați-vă că tastați {(acoladă deschisă) înainte de a tasta 10 și} (acoladă închisă) după ce tastați 180, deoarece creați o matrice de numere.

    În continuare, introduceți = D9 #sau = D9:I11 într-o celulă necompletată. O matrice de 3 x 6 de celule apare cu aceleași valori pe care le vedeți în D9:D11. Semnul # se numește varsat operator de zonăși este Excel metodă de faceți referire la zona întreaga matrice în loc să tastați-l.

    Utilizați operatorul de zonă varsat (#) să faceți referire la o matrice existente

  • Crearea unei constante matrice din valori existente

    Puteți să urmați rezultatele unei formule matrice vărsate și care face conversia în părțile de componentă. Selectați celula D9, apoi apăsați F2 pentru a trece la modul de editare. În continuare, apăsați F9 pentru a efectua conversia referințele la celule pentru a valorilor care Excel apoi efectuează conversia într-o constantă matrice. Atunci când apăsați Enter, formula = D9 #, ar trebui să fie acum = {10,20,30; 40,50,60; 70,80,90}.

  • Contorizarea caracterelor dintr-o zonă de celule

    Următorul exemplu vă arată cum să contorizați numărul de caractere dintr-o zonă de celule. Aceasta include spații.

    Contorizarea numărului total de caractere dintr-o zonă și alte matrice pentru lucrul cu șiruri text

    = SUM (LEN(C9:C13))

    În acest caz, funcția LEN Returnează lungimea modelului fiecare șir text în fiecare dintre celulele din zonă. Funcția SUM adună acele valori apoi și afișează rezultatul (66). Dacă ați vrut să numărul mediu de caractere, ar putea utiliza:

    = AVERAGE (LEN(C9:C13))

  • Conținutul celei mai lungi celule în zona C9:C13

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

    Această formulă funcționează doar atunci când o zonă de date conține o singură coloană de celule.

    Să aruncăm o privire mai detaliată asupra formula, începând de la elementele din interior și lucrul spre exterior. Funcția LEN Returnează lungimea modelului fiecare dintre elementele din zona de celule D2:D6. Funcția MAX calculează mai mare valoare dintre acele elemente care corespunde mai lung șir text, care este în celula D3.

    Iată unde lucrurile se puțin complexe. Funcția MATCH calculează offset (poziția relativă) de celule care conține mai lung șir text. Pentru a face acest lucru, aceasta necesită trei argumente: o valoare de căutare, o matrice de căutare și un tip de potrivire. Funcția MATCH caută matrice de căutare pentru valoarea de căutat specificat. În acest caz, valoarea de căutat este mai lung șir text:

    MAX(LEN(C9:C13)

    iar acel șir se află în această matrice:

    LEN(C9:C13)

    În acest caz, argumentul Potrivire tip este 0. Tipul de potrivire poate fi un 1, 0 sau valoarea-1.

    • 1 - returnează cea mai mare valoare care este mai mare sau egală cu căutare val

    • 0 - returnează prima valoare exact egal cu valoarea de căutat

    • -1 - returnează cea mai mică valoare care este mai mare sau egal cu valoarea specificată căutare

    • Dacă se omite un tip de potrivire, Excel presupune 1.

    În cele din urmă, funcția INDEX are următoarele argumente: unei matrice și un număr de rânduri și coloane din cadrul matricei respectivă. Zona de celule C9:C13 furnizează matrice, funcția MATCH furnizează adresa de celulă și argumentul finală (1) specifică faptul că valoarea provine din prima coloană din matrice.

    Dacă ați vrut să conținutul șirul text cel mai mic, să înlocuiți MAX în exemplul de mai sus cu MIN.

  • Găsirea celor mai mici n valori dintr-o zonă

    Acest exemplu vă arată cum să găsiți cele mai mici trei valori dintr-o zonă de celule, în cazul în care o matrice de date eșantion din celulele B9:B18has fost create cu: = INT (RANDARRAY(10,1) * 100). Rețineți că RANDARRAY este o funcție volatilă, astfel încât veți primi un nou set de numere aleatoare de fiecare dată când Excel calculează.

    Excel formulă matrice pentru a găsi valoarea minimă a n-a: =SMALL(B9#,SEQUENCE(D9))

    Introduceți =SMALL(B9#,SEQUENCE(D9), = mici (B9:B18, {1; 2; 3})

    Această formulă utilizează o constantă matrice pentru evaluarea funcției SMALL de trei ori și a reveni cel mai mic 3 membri din matricea conținută în celule B9:B18, unde 3 este o variabilă valoarea din celula D9. Pentru a găsi mai multe valori, puteți mări valoare în funcția secvență, sau adăugați mai multe argumente constantă. De asemenea, puteți utiliza funcții suplimentare cu această formulă, cum ar fi SUM sau medie. De exemplu:

    =SUM(SMALL(B9#,SEQUENCE(D9))

    =AVERAGE(SMALL(B9#,SEQUENCE(D9))

  • Găsirea celor mai mari n valori dintr-o zonă

    Pentru a găsi mai mari valori dintr-o zonă, puteți înlocui funcția SMALL cu funcția LARGE. În plus, următorul exemplu utilizează funcțiile rând și INDIRECT .

    Introduceți = mare (B9 #, rând (INDIRECT ("1:3"))), sau = mare (B9:B18,ROW(INDIRECT("1:3")))

    În acest moment, vă poate ajuta să știți câteva informații despre funcțiile de rând și INDIRECT. Puteți utiliza funcția ROW pentru a crea o matrice de numere întregi consecutive. De exemplu, selectați o goliți și introduceți:

    =ROW(1:10)

    Formula creează o coloană de numere întregi consecutive 10. Pentru a vedea o problemă potențiale, inserați un rând deasupra zona care conține formula matrice (adică deasupra rândul 1). Excel se reglează referințe rând, iar formula generează acum întregi de la 2 la 11. Pentru a remedia această problemă, puteți adăuga funcția INDIRECT la formula:

    = RÂND (INDIRECT ("1:10"))

    Funcția INDIRECT utilizează șiruri text ca argumentele (care este de ce zonă 1:10 este inclus între ghilimele). Excel nu se ajustează valorile text atunci când inserați rânduri sau mutați în caz contrar, formula matrice. Prin urmare, funcția ROW generează întotdeauna matrice de numere întregi pe care o doriți. La fel de ușor să utilizați secvență:

    =SEQUENCE(10)

    Să examinăm formula care utilizează o versiune anterioară, LARGE (B9 #, rând (INDIRECT ("1:3"))) =, începând din interior paranteze și lucrul spre exterior: funcția INDIRECT returnează un set de valori text, în acest caz valorile 1 la 3. Funcția ROW la rândul său generează o matrice de celule de trei coloane. Funcția LARGE utilizează valorile din zona de celule B9:B18, iar acesta este evaluat de trei ori, o dată pentru fiecare referințe returnată de funcția ROW. Dacă doriți să găsiți mai multe valori, adăugați o zonă de celule mai mare la funcția INDIRECT. În cele din urmă, ca cu exemple mici, puteți să utilizați această formulă cu alte funcții, cum ar fi SUM și AVERAGE.

  • Însumarea unei zone care conține valori eroare

    Funcția SUM în Excel nu funcționează atunci când încercați să adunați o zonă care conține o valoare de eroare, cum ar fi #VALUE! sau #N/A. Acest exemplu vă arată cum să însumați valori dintr-o zonă denumită date care conține erori:

    Utilizați matrice pentru a face cu erori. De exemplu, =SUM(IF(ISERROR(Data),"",Data) va aduna zonă denumită date, chiar dacă include erorile, cum ar fi #VALUE! sau #NA!.

  • =SUM(IF(ISERROR(Date);"";Date))

    Formula creează o matrice nouă, care conține valorile inițiale minus valorile eroare. Începând de la funcțiile din interior și mergând înspre exterior, funcția ISERROR caută în zona de celule (Date) pentru a găsi erori. Funcția IF returnează o anumită valoare dacă o condiție pe care o specificați este evaluată ca adevărată și altă valoare dacă este evaluată ca fiind falsă. În acest caz, returnează șiruri goale ("") pentru toate valorile eroare, deoarece acestea sunt evaluate ca o condiție adevărată, și returnează restul valorilor din zona de celule (Date), deoarece sunt evaluate ca false, adică nu conțin valori eroare. Funcția SUM calculează apoi totalul pentru matricea filtrată.

  • Contorizarea numărului de valori eroare dintr-o zonă

    Acest exemplu este ca formula anterioară, dar returnează numărul de valori eroare dintr-o zonă denumită date în loc să le filtreze:

    =SUM(IF(ISERROR(Date);1;0))

    Această formulă creează o matrice care conține valoare 1 pentru celulele care conțin erori și valoarea 0 pentru celulele care nu conțin erori. Aveți posibilitatea să simplificați formula și să obțineți același rezultat eliminând al treilea argument pentru funcția IF, astfel:

    =SUM(IF(ISERROR(Date);1))

    Dacă nu specificați argumentul, funcția IF returnează FALSE dacă o celulă nu conține o valoare de eroare. Aveți posibilitatea să simplificați formula și mai mult:

    =SUM(IF(ISERROR(Date)*1))

    Această versiune funcționează deoarece TRUE*1=1 și FALSE*1=0.

Poate fi necesar să însumarea valorilor pe baza condițiilor.

Puteți utiliza matrice pentru a se calculează în funcție de anumite condiții. =SUM(if(Sales>0,Sales)) va suma tuturor valorilor mai mare decât 0 dintr-o zonă denumită vânzări.

De exemplu, această formulă matrice adună doar pozitiv întregi dintr-o zonă denumită vânzări, care reprezintă celulele E9:E24 în exemplul de mai sus:

=SUM(IF(Vânzări>0;Vânzări))

Funcția IF creează o matrice de valori false. Funcția SUM, în esență, ignoră valorile false, deoarece 0 + 0 = 0. Zona de celule pe care îl utilizați în această formulă poate conține orice număr de rânduri și coloane.

De asemenea, puteți aduna valorile care îndeplinesc mai multe condiții. De exemplu, această formulă matrice calculează valorile mai mare decât 0 și mai mic decât 2500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

Rețineți că această formulă va returna o eroare dacă zona conține una sau mai multe celule non-numerice.

De asemenea, puteți crea formule care utilizează un tip de OR condiție. De exemplu, puteți aduna valorile care sunt mai mari decât 0 sau mai mică decât 2500:

=SUM(if((Sales>0)+(Sales<2500),Sales))

Nu puteți utiliza AND și OR funcțiile în formulele de matrice direct, deoarece aceste funcții returnează un rezultat unic, adevărate sau FALSE, iar funcțiile matrice necesită matrice de rezultate. Puteți rezolva problema utilizând logica afișate în formula anterioară. Cu alte cuvinte, puteți efectua operațiuni matematice, cum ar fi Adunarea sau înmulțirea pe valorile care îndeplinesc OR sau și condiție.

Acest exemplu arată cum să eliminați valorile zero dintr-o zonă atunci când trebuie să calculați o medie a valorilor din acea zonă. Formula utilizează o zonă de date denumită Vânzări:

=AVERAGE(IF(Vânzări<>0;Vânzări))

Funcția IF creează o matrice de valori care nu sunt egale cu 0 și transmite acele valori la funcția AVERAGE.

Această formulă matrice se compară valorile din două zone de celule denumite Datelemele și Dateletale și returnează numărul de diferențe între cele două. În cazul în care conținutul a două zone sunt identice, formula returnează 0. Pentru a utiliza această formulă, zone de celule trebuie să fie aceeași dimensiune și de aceeași dimensiune. De exemplu, dacă Datelemele este o zonă de 3 rânduri după 5 coloane, Dateletale trebuie să fie, de asemenea, 3 rânduri după 5 coloane:

=SUM(IF(DateleMele=DateleTale;0;1))

Formula creează o matrice nouă de aceeași dimensiune ca zonele pe care le comparați. Funcția IF completează matricea cu valoarea 0 și valoarea 1 (0 pentru nepotriviri și 1 pentru celule identice). Funcția SUM returnează suma valorilor din matrice.

Formula poate fi simplificată astfel:

=SUM(1*(DateleMele<>DateleTale))

La fel ca formula care contorizează valorile eroare dintr-o zonă de date, această formulă funcționează deoarece TRUE*1=1 și FALSE*1=0.

Această formulă matrice returnează numărul de rând al valorii maxime dintr-o zonă cu o singură coloană denumită Date:

=MIN(IF(Date=MAX(Date);ROW(Date);""))

Funcția IF creează o matrice nouă, care corespunde cu zona denumită Date. Dacă o celulă corespondentă conține valoarea maximă din zonă, matricea conține numărul de rând. Altfel, matricea conține un șir gol (""). Funcția MIN utilizează matricea nouă ca al doilea argument și returnează cea mai mică valoare, care corespunde cu numărul de rând al valorii maxime din Date. Dacă zona denumită Date conține valori maxime identice, formula returnează rândul primei valori.

Dacă doriți să returnați adresa efectivă a celulei în care se află valoarea maximă, utilizați această formulă:

=ADDRESS(MIN(IF(Date=MAX(Date);ROW(Date);""));COLUMN(Date))

Veți găsi exemple similare în registrul de lucru eșantion din foaia de lucru diferențele dintre seturi de date .

Acest exercițiu vă arată cum să utilizați formulele matrice cu mai multe celule și cu o singură celulă pentru a calcula un set de cifre de vânzări. Primul set de pași utilizează o formulă cu mai multe celule pentru a calcula un set de subtotaluri. Al doilea set utilizează o formulă cu o singură celulă pentru a calcula un total general.

  • Formulele matrice cu celule multiple

Copiați tot tabelul de mai jos și lipiți-le în celula A1 într-o foaie de lucru necompletată.

Vânzări Persoană

Mașină Tip

Număr Vândute

Unitate Preț

Total Vânzări

Roman

Sedan

5

33000

Coupe

4

37000

Dumitriu

Sedan

6

24000

Coupe

8

21000

Lupu

Sedan

3

29000

Coupe

1

31000

Roman

Sedan

9

24000

Coupe

5

37000

Petculescu

Sedan

6

33000

Coupe

8

31000

Formulă (Total general)

Total general

'=SUM(C2:C11*D2:D11)

=SUM(C2:C11*D2:D11)

  1. Pentru a vedea totalul vânzărilor de Coupe și Sedan pentru fiecare vânzător, selectați celulele E2: E11, introduceți formula = C2: C11 * D2: D11, apoi apăsați Ctrl + Shift + Enter.

  2. Pentru a vedea totalul general al tuturor vânzărilor, selectați celula F11, introduceți formula =SUM(C2:C11*D2:D11), apoi apăsați Ctrl + Shift + Enter.

Atunci când apăsați Ctrl + Shift + Enter, Excel încadrează formula cu acolade ({}) și inserează o instanță a formula în fiecare celulă din zona selectată. Acest lucru se întâmplă foarte rapid, astfel încât ceea ce vedeți în coloana E este numărul total de vânzări pentru fiecare tip de mașină pentru fiecare vânzător. Dacă selectați E2, apoi selectați E3, E4 și așa mai departe, veți vedea că se afișează aceeași formulă: {= C2: C11 * D2: D11}.

Totalurile din coloana E sunt calculate de o formulă matrice

  • Crearea unei formule matrice cu o singură celulă

În celula D13 din registrul de lucru, tastați următoarea formulă și apăsați Ctrl + Shift + Enter:

=SUM(C2:C11*D2:D11)

În acest caz, Excel înmulțește valorile din matricea (zona de celule C2 până la D11) și apoi utilizează funcția SUMpentru a adăuga totaluri împreună. Rezultatul este un total de $1,590,000 în vânzări. Acest exemplu vă arată cum puternic poate fi acest tip de formulă. De exemplu, să presupunem că aveți 1000 de rânduri de date. Pot aduna parțial sau complet de date prin crearea unei formule de matrice într-o singură celulă în loc să glisați formula în jos prin rândurile 1000.

De asemenea, observați că formula singură celulă în celula D13 este complet independent de formule cu mai multe celule (în formulă în celula E2 până E11). Acesta este un alt avantaj al utilizării formulelor matrice-flexibilitate. Când ar putea modifica formulele din coloana E sau ștergeți acea coloană cu totul, fără a afecta formula din D13.

De asemenea, formulele matrice oferă și următoarele avantaje:

  • Consistență    Dacă faceți clic pe oricare dintre celulele de la E2 în jos, vedeți aceeași formulă. Această consistență poate contribui la o mai mare acuratețe.

  • Toate, fiți precaut    Nu puteți să Suprascrieți o componentă a unei formule matrice cu celule multiple. De exemplu, faceți clic pe celula E3 și apăsați pe Ștergere. Trebuie să fie selectați întreaga zonă de celule (E2 prin E11) și modificați formula pentru întreaga matrice sau lăsați matrice așa cum este. Ca o măsură de siguranță adăugată, trebuie să apăsați Ctrl + Shift + Enter pentru a confirma vreo modificare a formulei.

  • Dimensiune redusă a fișierelor    Deseori, aveți posibilitatea să utilizați o singură formulă matrice în loc de mai multe formule intermediare. De exemplu, registrul de lucru utilizează o formulă matrice pentru a calcula rezultatele în coloana E. Dacă ați fi utilizat formule standard (cum ar fi =C2*D2, C3*D3, C4*D4…), ați fi utilizat 11 formule diferite pentru a calcula aceleași rezultate.

În general, formulele matrice utilizați sintaxa de formulă standard. Acestea se începe cu semnul egal (=) și puteți să utilizați majoritatea funcțiilor Excel încorporate în formule matrice. Diferența de cheie este că atunci când se utilizează o formulă matrice, apăsați Ctrl + Shift + Enter pentru a introduce formula. Atunci când faceți acest lucru, Excel încadrează formula matrice cu acolade, dacă vă introduceți acoladele, formula va fi transformată în un șir de text și nu va funcționa.

Funcții de matrice poate fi o modalitate eficientă a construi formule complexe. Matricea formulă =SUM(C2:C11*D2:D11) este la fel ca aceasta: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Important: Apăsați Ctrl + Shift + Enter, ori de câte ori trebuie să introduceți o formulă matrice. Acest lucru se aplică la formule atât singură celulă și cu mai multe celule.

Oricând lucrați cu formule cu mai multe celule, rețineți, de asemenea:

  • Selectați zona de celule care vor conține rezultatele înainte să introduceți formula. Ați făcut acest lucru atunci când ați creat formula cu mai multe celule când ați selectat celulele de la E2 până la E11.

  • Nu aveți posibilitatea să modificați conținutul unei celule individuale dintr-o formulă matrice. Pentru a încerca acest lucru, selectați celula E3 din registrul de lucru și apăsați pe Delete. Excel afișează un mesaj care vă anunță că nu puteți modifica o parte a unei matrice.

  • Aveți posibilitatea să mutați sau să ștergeți o întreagă formulă matrice, dar nu aveți posibilitatea să mutați sau să ștergeți o parte din aceasta. Cu alte cuvinte, pentru a restrânge o formulă matrice, ștergeți mai întâi formula existentă, apoi luați-o de la început.

  • Pentru a șterge o formulă matrice, selectați întreaga zonă formulă (de exemplu, E2: E11), apoi apăsați pe Ștergere.

  • Nu puteți să introduceți celule necompletate, sau ștergeți celule dintr-o formulă matrice cu celule multiple.

Uneori, poate fi necesar pentru a extinde o formulă matrice. Selectați prima celulă din zona de matrice existente și continuați până când ați selectat întreaga zonă că doriți să Prelungiți formula pentru. Apăsați F2 pentru a edita formula, apoi apăsați CTRL + SHIFT + ENTER pentru a confirma formula după ce ați ajustate zona formulă. Soluția este să selectați întreaga zonă, începând cu celula din stânga sus din matrice. Celula din stânga sus este una care este editat.

Formulele matrice sunt foarte bune, dar au și câteva dezavantaje:

  • Ce-ar putea uita din când în când să apăsați Ctrl + Shift + Enter. Se poate întâmpla să chiar utilizatorilor mai experimentați Excel. Nu uitați să apăsați această combinație de taste ori de câte ori vă introduce sau a edita o formulă matrice.

  • Alți utilizatori din registrul de lucru nu poate înțelege formulelor. În practică, formulele matrice sunt în general nu explicat într-o foaie de lucru. Prin urmare, dacă alte persoane să modifice registrele de lucru, care ar trebui să fie evita formulele matrice sau asigurați-vă că persoanele știu despre formulele matrice și înțelegeți cum să modificați-le, în cazul în care au nevoie pentru a.

  • În funcție de viteza de procesare și de memoria computerului, formulele matrice de mari dimensiuni pot încetini calculele.

Constantele matrice sunt o componentă a formulelor matrice. Creați constante matrice introducând o listă de elemente, apoi încadrând manual lista cu acolade ({ }), astfel:

={1\2\3\4\5}

Până acum, știți că trebuie să apăsați Ctrl + Shift + Enter când creați formule matrice. Deoarece constantele matrice sunt o componentă de formule matrice, care înconjoară constante cu acolade introducând manual le. Apoi utilizați Ctrl + Shift + Enter pentru a introduce întreaga formulă.

Dacă separați elementele folosind virgule (pentru setările regionale în engleză) sau bare oblice inverse (\) pentru setările regionale în română, creați o matrice orizontală (un rând). Dacă separați elementele utilizând semnul punct și virgulă, creați o matrice verticală (o coloană). Pentru a crea o matrice bidimensională, delimitați elementele din fiecare rând folosind virgule și delimitați fiecare rând folosind punct și virgulă.

Iată o matrice într-un singur rând: {1,2,3,4}. Iată o matrice într-o singură coloană: {1; 2; 3; 4}. Iată o matrice de două rânduri și de patru coloane: {1,2,3,4; 5,6,7,8}. Matrice două rânduri, primul rând este 1, 2, 3 și 4 și al doilea rând este 5, 6, 7 și 8. Un singur punct și virgulă separă două rânduri, între 4 și 5.

Ca și în cazul formulelor matrice, aveți posibilitatea să utilizați constante matrice cu majoritatea funcțiilor predefinite oferite de Excel. Următoarea secțiune arată cum se creează fiecare tip de constantă și cum se utilizează constantele cu funcțiile din Excel.

În următoarele proceduri veți exersa crearea constantelor orizontale, verticale și bidimensionale.

Crearea unei constante orizontale

  1. Într-o foaie de lucru necompletată, selectați celulele A1 până la E1.

  2. În bara de formule, introduceți următoarea formulă și apăsați Ctrl + Shift + Enter:

    = {1,2,3,4,5}

    În acest caz, care ar trebui să tastați cele două acolade ({}) și Excel va adăuga al doilea set pentru dvs.

    Se afișează următorul rezultat.

    Constantă matrice orizontală în formulă

Crearea unei constante verticale

  1. În registrul de lucru, selectați o coloană cu cinci celule.

  2. În bara de formule, introduceți următoarea formulă și apăsați Ctrl + Shift + Enter:

    ={1;2;3;4;5}

    Se afișează următorul rezultat.

    Constantă matrice verticală în formula matrice

Crearea unei constante bidimensionale

  1. În registrul de lucru, selectați o zonă de celule de patru coloane lățime și trei rânduri lungime.

  2. În bara de formule, introduceți următoarea formulă și apăsați Ctrl + Shift + Enter:

    ={1\2\3\4;5\6\7\8;9\10\11\12}

    Veți vedea următorul rezultat:

    Constantă matrice bidimensională în formula matrice

Utilizarea constantelor în formule

Iată un exemplu simplu care utilizează constante:

  1. În registrul de lucru eșantion, creați o foaie de lucru nouă.

  2. În celula A1, tastați 3, apoi tastați 4 în B1, 5 în C1, 6 în D1 și 7 în E1.

  3. În celula A3, tastați următoarea formulă și apăsați Ctrl + Shift + Enter:

    =SUM(A1:E1*{1\2\3\4\5})

    Observați că Excel încadrează constanta cu alt set de acolade, deoarece ați introdus-o ca o formulă matrice.

    Formulă matrice cu constantă matrice

    Valoarea 85 apare în celula A3.

Următoarea secțiune explică modul în care funcționează formula.

Formula pe care tocmai ați utilizat-o conține mai multe părți.

Sintaxa formulei matrice cu constanta matrice

1. Funcție

2. Matrice stocată

3. Operator

4. Constantă matrice

Ultimul element în parantezele este constanta matrice: {1,2,3,4,5}. Rețineți că Excel încadra constantele matrice cu acolade; de fapt le tastați. De asemenea, rețineți că după ce adăugați o constantă la o formulă matrice, apăsați Ctrl + Shift + Enter pentru a introduce formula.

Deoarece Excel efectuează mai întâi operațiuni cu expresiile dintre paranteze, următoarele două elemente care intră în joc sunt valorile stocate în registrul de lucru (A1:E1) și operatorul. În acest moment, formula înmulțește valorile din matricea stocată cu valorile corespunzătoare din constantă. Este echivalentul formulei:

=SUM(A1*1\B1*2\C1*3\D1*4\E1*5)

În fine, funcția SUM adună valorile și suma 85 apare în celula A3.

Pentru a evita utilizarea matricei stocate și a păstra operațiunea în memorie în întregime, înlocuiți matricea stocată cu altă constantă matrice:

=SUM({3\4\5\6\7}*{1\2\3\4\5})

Pentru a încerca acest lucru, copiați funcția, selectați o celulă goală din registrul de lucru, lipiți formula în bara de formule și apăsați Ctrl + Shift + Enter. Veți vedea același rezultat, așa cum ați procedat la exercițiul anterioare care utilizează formula matrice:

=SUM(A1:E1*{1\2\3\4\5})

Constantele matrice pot conține numere, text, valori logice (cum ar fi TRUE și FALSE) și valori eroare ( cum ar fi #N/A). Aveți posibilitatea să utilizați numere în format întreg, zecimal și științific. Dacă adăugați text, trebuie să încadrați textul între ghilimele (").

Constantele matrice nu pot conține matrice suplimentare, formule sau funcții. Cu alte cuvinte, pot conține numai text sau numere separate prin bare oblice inverse sau punct și virgulă. Excel afișează un mesaj de avertisment când introduceți o formulă ca {1\2\A1:D4} sau {1\2\SUM(Q2:Z8)}. De asemenea, valorile numerice nu pot conține semnul procent, semnul dolar, bare oblice inverse sau paranteze.

Unul dintre cea mai bună modalitate de a utiliza constantele matrice este să le denumiți. Constante denumite pot fi mult mai ușor de utilizat și le puteți ascunde unele dintre complexitatea formulele matrice de alte persoane. La denumirea unei constante de matrice și utilizați-l într-o formulă, procedați astfel:

  1. Pe fila formule, în grupul Nume definite, faceți clic pe Definire nume.
    Apare caseta de dialog Definire nume.

  2. În caseta Nume, tastați Trimestru1.

  3. În caseta Se referă la:, introduceți următoarea constantă (nu uitați să introduceți acoladele):

    ={"Ianuarie"\"Februarie"\"Martie"}

    Conținutul casetei de dialog arată acum astfel:

    Caseta de dialog Editare nume cu formulă

  4. Faceți clic pe OK, apoi selectați un rând din trei celule necompletate.

  5. Tastați următoarea formulă, apoi apăsați Ctrl + Shift + Enter.

    =Trimestru1

    Se afișează următorul rezultat.

    Matrice denumită introdusă ca formulă

Când utilizați o constantă denumită ca o formulă matrice, nu uitați să introduceți semnul egal. Dacă nu faceți acest lucru, Excel interpretează matricea ca șir text și formula dvs. nu va funcționa așa cum vă așteptați. În fine, nu uitați că aveți posibilitatea să utilizați combinații de text și numere.

Când constantele matrice nu funcționează, verificați dacă există următoarele probleme:

  • Unele elemente nu poate fi separate cu caracter potrivite. Dacă se omite o virgulă sau punct și virgulă, dacă este una în locul nepotrivit constantă matrice nu poate fi creat corect, sau, este posibil să vedeți un mesaj de avertizare.

  • Este posibil să fi selectat o zonă de celule care nu se potrivește cu numărul de elemente din constantă. De exemplu, dacă selectați o coloană de șase celule pentru a o utiliza cu o constantă de cinci celule, va apărea valoarea de eroare #N/A în celula necompletată. În același mod, dacă selectați prea puține celule, Excel omite valorile care nu au o celulă corespondentă.

Următoarele exemple demonstrează câteva moduri în care se poate pune constantele matrice de utilizat în formule matrice. Unele dintre aceste exemple utilizează funcția TRANSPOSE se convertesc rândurile la coloane și invers.

Înmulțirea fiecărui element dintr-o matrice

  1. Creați o foaie de lucru nouă și apoi selectați o zonă de celule necompletate de patru coloane lățime și trei rânduri înălțime.

  2. Tastați următoarea formulă, apoi apăsați Ctrl + Shift + Enter:

    = {1,2,3,4; 5,6,7,8; 9,10,11,12} * 2

Ridicați la pătrat elementele dintr-o matrice

  1. Selectați o zonă de celule necompletate de patru coloane lățime și trei rânduri înălțime.

  2. Tastați următoarea formulă matrice, apoi apăsați Ctrl + Shift + Enter:

    ={1\2\3\4;5\6\7\8;9\10\11\12}*{1\2\3\4;5\6\7\8;9\10\11\12}

    Ca alternativă, introduceți această formulă matrice, care utilizează operatorul caret (^):

    ={1\2\3\4;5\6\7\8;9\10\11\12}^2

Transpunerea unui rând unidimensional

  1. Selectați o coloană de cinci celule necompletate.

  2. Tastați următoarea formulă, apoi apăsați Ctrl + Shift + Enter:

    =TRANSPOSE({1\2\3\4\5})

    Chiar dacă ați introdus o constantă matrice orizontală, funcția TRANSPOSE efectuează conversia constantei matrice într-o coloană.

Transpunerea unei coloane unidimensionale

  1. Selectați un rând de cinci celule necompletate.

  2. Introduceți următoarea formulă și apăsați Ctrl + Shift + Enter:

    =TRANSPOSE({1;2;3;4;5})

Chiar dacă ați introdus o constantă matrice verticală, funcția TRANSPOSE efectuează conversia constantei într-un rând.

Transpunerea unei constante bidimensionale

  1. Selectați o zonă de celule de trei coloane lățime și patru rânduri înălțime.

  2. Tastați următoarea constantă și apăsați Ctrl + Shift + Enter:

    =TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})

    Funcția TRANSPOSE efectuează conversia fiecărui rând într-o serie de coloane.

Această secțiune oferă exemple de formule matrice simple.

Crearea matricelor și a constantelor matrice din valori existente

Următorul exemplu arată cum se utilizează formulele matrice pentru a crea legături între zonele de celule din diferitele foi de lucru. De asemenea, vă arată cum să creați o constantă matrice din același set de valori.

Crearea unei matrice din valori existente

  1. Într-o foaie de lucru din Excel, selectați celulele C8:E10 și introduceți această formulă:

    ={10\20\30;40\50\60;70\80\90}

    Asigurați-vă că tastați { (acoladă deschisă) înainte de a tasta 10 și } (acoladă închisă) după ce tastați 90, deoarece creați o matrice de numere.

  2. Apăsați Ctrl + Shift + Enter, care intră în această matrice de numere în zona de celule C8: E10 utilizând o formulă matrice. Pe foaia de lucru, C8 la E10 ar trebui să arate astfel:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Selectați zona de celule C1 până la E3.

  4. Introduceți următoarea formulă în bara de formule și apăsați Ctrl + Shift + Enter:

    =C8:E10

    O matrice de celule 3 x 3 apare în celule C1 până la E3 cu aceleași valori pe care le vedeți în C8 la E10.

Crearea unei constante matrice din valori existente

  1. Cu celule C1:C3 selectată, apăsați F2 pentru a comuta pentru a edita modul.

  2. Apăsați F9 pentru a efectua conversia referințele la celule la valori. Excel face conversia valorilor într-o constantă matrice. Formula trebuie să fie acum = {10,20,30; 40,50,60; 70,80,90}.

  3. Apăsați Ctrl + Shift + Enter pentru a introduce Constanta matrice ca formulă matrice.

Contorizarea caracterelor dintr-o zonă de celule

Următorul exemplu vă arată cum să contorizați numărul de caractere, inclusiv spațiile, dintr-o zonă de celule.

  1. Copiați tot tabelul și lipiți-l într-o foaie de lucru în celula A1.

    Date

    Acesta este

    un interval de celule care

    luate împreună

    formează

    o singură propoziție.

    Total caractere în A2:A6

    =SUM(LEN(A2:A6))

    Conținutul celei mai lungi celule (A3)

    =INDEX(A2:A6;MATCH(MAX(LEN(A2:A6));LEN(A2:A6);0);1)

  2. Selectați celula A8 și apăsați Ctrl + Shift + Enter pentru a vedea numărul total de caractere din celulele A2: A6 (66).

  3. Selectați celula A10 și apoi apăsați Ctrl + Shift + Enter pentru a vedea conținutul celei mai lungi dintre celulele A2: A6 (celula A3).

Se utilizează următoarea formulă în celula A8 adună numărul total de caractere (66) din celulele A2-A6.

=SUM(LEN(A2:A6))

În acest caz, funcția LEN Returnează lungimea modelului fiecare șir text în fiecare dintre celulele din zonă. Funcția SUM adună acele valori apoi și afișează rezultatul (66).

Găsiți n mai mici valori dintr-o zonă

Acest exemplu vă arată cum să găsiți cele mai mici trei valori dintr-o zonă de celule.

  1. Introduceți câteva numere aleatoare în celulele A1:A11.

  2. Selectați celulele C1 până la C3. Acest set de celule va afla rezultatele returnate de formula matrice.

  3. Introduceți următoarea formulă și apăsați Ctrl + Shift + Enter:

    = SMALL(A1:A11,{1;2;3})

Această formulă utilizează o constantă matrice pentru a evaluează funcția mici de trei ori și returnează cea mai mică (1), al doilea cel mai mic (2) și al treilea cel mai mic (3) membri din matricea conținută în celulele A1:A10 pentru a găsi mai multe valori, puteți adăuga mai multe argumente la constantă. De asemenea, puteți utiliza funcții suplimentare cu această formulă, cum ar fi SUM sau medie. De exemplu:

= SUM (MICI (A1:A10, {1,2,3})

= AVERAGE (MICI (A1:A10, {1,2,3})

Găsirea n mai mari valori dintr-o zonă

Pentru a găsi cele mai mari valori dintr-o zonă, puteți să înlocuiți funcția SMALL cu funcția LARGE. În plus, următorul exemplu utilizează funcțiile ROW și INDIRECT.

  1. Selectați celulele D1 prin D3.

  2. În bara de formule, introduceți această formulă și apăsați Ctrl + Shift + Enter:

    =LARGE(A1:A10,ROW(INDIRECT("1:3")))

În acest moment, aceasta poate ajuta să știți câteva informații despre rând și INDIRECT funcții. Puteți utiliza funcția ROW pentru a crea o matrice de numere întregi consecutive. De exemplu, selectați o coloană goală de 10 celule în practică registrul de lucru, introduceți această formulă matrice, și apoi apăsați Ctrl + Shift + Enter:

=ROW(1:10)

Formula creează o coloană de 10 numere întregi consecutive. Pentru a vedea o posibilă problemă, introduceți un rând deasupra rândului care conține formula matrice (adică deasupra rândului 1). Excel reglează referința de rând, iar formula generează numere întregi de la 2 la 11. Pentru a remedia problema, adăugați funcția INDIRECT la formulă:

=ROW(INDIRECT("1:10"))

Funcția INDIRECT utilizează șiruri text ca argumente (motiv pentru care zona 1:10 este încadrată de ghilimele duble). Excel nu ajustează valorile text atunci când introduceți rânduri sau mutați formula matrice. Drept urmare, funcția ROW generează întotdeauna matricea de numere întregi dorită.

Să aruncăm o privire asupra formula care utilizează o versiune anterioară, = LARGE (A5:A14,ROW(INDIRECT("1:3"))) , începând din interior paranteze și lucrul spre exterior: funcția INDIRECT returnează un set de valori text, în acest caz valorile 1 la 3. Funcția ROW la rândul său generează o matrice de celule de trei coloane. Funcția LARGE utilizează valorile din zona de celule A5:A14, iar acesta este evaluat de trei ori, o dată pentru fiecare referințe returnată de funcția ROW . Valorile 3200, 2700 și 2000 sunt returnate în celula trei coloane matrice. Dacă doriți să găsiți mai multe valori, adăugați o zonă de celule mai mare la funcția INDIRECT .

Ca cu exemplele anterioare, puteți să utilizați această formulă cu alte funcții, cum ar fi SUM și AVERAGE.

Găsirea celui mai lung șir text dintr-o zonă de celule

Salt înapoi la text șir exemplul anterior, introduceți următoarea formulă într-o celulă goală și apăsați Ctrl + Shift + Enter:

=INDEX(A2:A6;MATCH(MAX(LEN(A2:A6));LEN(A2:A6);0);1)

Textul "zona de celule care" apare.

Să aruncăm o privire mai detaliată asupra formula, începând de la elementele din interior și lucrul spre exterior. Funcția LEN Returnează lungimea modelului fiecare dintre elementele din zona de celule a2: A6. Funcția MAX calculează mai mare valoare dintre acele elemente care corespunde mai lung șir text, care este în celula A3.

Aici lucrurile devin mai complicate. Funcția MATCH calculează deplasarea (poziția relativă) celulei care conține cel mai lung șir text. Pentru a face aceasta, are nevoie de trei argumente: o valoare de căutare, o matrice de căutare și un tip de matrice. Funcția MATCH caută în matricea de căutare pentru a găsi valoarea de căutare dorită. În acest caz, valoarea de căutare este cel mai lung șir text:

(MAX (LEN(A2:A6))

iar acel șir se află în această matrice:

LEN(A2:A6)

Argumentul tipului de potrivire este 0. Tipul de potrivire poate fi valoarea 1, 0 sau -1. Dacă specificați 1, MATCH returnează cea mai mare valoare care este mai mică sau egală cu valoarea de căutare. Dacă specificați 0, MATCH returnează prima valoare care este exact egală cu valoarea de căutare. Dacă specificați -1, MATCH găsește cea mai mică valoare care este mai mare sau egală cu valoarea de căutare specificată. Dacă omiteți tipul de potrivire, Excel stabilește valoarea 1.

În fine, funcția INDEX are următoarele argumente: unei matrice și un număr de rânduri și coloane din cadrul matricei respectivă. Zonă de celule din a2: A6 furnizează matrice, funcția MATCH furnizează adresa de celulă și argumentul finală (1) specifică faptul că valoarea provine din prima coloană din matrice.

Această secțiune oferă exemple de formule matrice complexe.

Însumarea unei zone care conține valori eroare

Funcția SUM din Excel nu funcționează când încercați să însumați o zonă care conține o valoare de eroare, cum ar fi #N/A. Acest exemplu vă arată cum să însumați valorile dintr-o zonă denumită Date, care conține erori.

=SUM(IF(ISERROR(Date);"";Date))

Formula creează o matrice nouă, care conține valorile inițiale minus valorile eroare. Începând de la funcțiile din interior și mergând înspre exterior, funcția ISERROR caută în zona de celule (Date) pentru a găsi erori. Funcția IF returnează o anumită valoare dacă o condiție pe care o specificați este evaluată ca adevărată și altă valoare dacă este evaluată ca fiind falsă. În acest caz, returnează șiruri goale ("") pentru toate valorile eroare, deoarece acestea sunt evaluate ca o condiție adevărată, și returnează restul valorilor din zona de celule (Date), deoarece sunt evaluate ca false, adică nu conțin valori eroare. Funcția SUM calculează apoi totalul pentru matricea filtrată.

Contorizarea numărului de valori eroare dintr-o zonă

Acest exemplu este similar cu formula anterioară, dar returnează numărul de valori eroare dintr-o zonă denumită Date, în loc să le filtreze:

=SUM(IF(ISERROR(Date);1;0))

Această formulă creează o matrice care conține valoare 1 pentru celulele care conțin erori și valoarea 0 pentru celulele care nu conțin erori. Aveți posibilitatea să simplificați formula și să obțineți același rezultat eliminând al treilea argument pentru funcția IF, astfel:

=SUM(IF(ISERROR(Date);1))

Dacă nu specificați argumentul, funcția IF returnează FALSE dacă o celulă nu conține o valoare de eroare. Aveți posibilitatea să simplificați formula și mai mult:

=SUM(IF(ISERROR(Date)*1))

Această versiune funcționează deoarece TRUE*1=1 și FALSE*1=0.

Însumarea valorilor pe baza condițiilor

Poate fi necesar să însumați valorile pe baza unor condiții. De exemplu, această formulă matrice însumează numai numerele întregi pozitive dintr-o zonă denumită Vânzări:

=SUM(IF(Vânzări>0;Vânzări))

Funcția IF creează o matrice de valori pozitive și valori false. Funcția SUM ignoră practic valorile false, deoarece 0+0=0. Zona de celule pe care o utilizați în această formulă poate consta în orice număr de rânduri și coloane.

De asemenea, aveți posibilitatea să însumați valorile care respectă mai multe condiții. De exemplu, această formulă matrice calculează valorile mai mari decât 0 și mai mici sau egale cu 5:

=SUM((Vânzări>0)*(Vânzări<=5)*(Vânzări))

Rețineți că această formulă va returna o eroare dacă zona conține una sau mai multe celule non-numerice.

De asemenea, aveți posibilitatea să creați formule matrice care utilizează un tip de condiție OR (SAU). De exemplu, aveți posibilitatea să însumați valorile mai mici de 5 și mai mari de 15:

=SUM(IF((Vânzări<5)+(Vânzări>15);Vânzări))

Funcția IF găsește toate valorile mai mici decât 5 și mai mari decât 15, apoi transmite acele valori la funcția SUM.

Nu aveți posibilitatea să utilizați funcțiile AND și OR în formulele matrice în mod direct, deoarece acele funcții returnează un singur rezultat, TRUE sau FALSE, iar funcțiile matrice necesită matrici de rezultate. Aveți posibilitatea să rezolvați problema utilizând elementele logice din formula anterioară. Cu alte cuvinte, efectuați operațiuni matematice, cum ar fi adunarea sau înmulțirea, pentru valori care respectă condiția OR (SAU) sau AND (ȘI).

Calculul unei medii care exclude zerourile

Acest exemplu arată cum să eliminați valorile zero dintr-o zonă atunci când trebuie să calculați o medie a valorilor din acea zonă. Formula utilizează o zonă de date denumită Vânzări:

=AVERAGE(IF(Vânzări<>0;Vânzări))

Funcția IF creează o matrice de valori care nu sunt egale cu 0 și transmite acele valori la funcția AVERAGE.

Contorizarea numărului de diferențe dintre două zone de celule

Această formulă matrice compară valorile din două zone de celule denumite DateleMele și DateleTale și returnează numărul de diferențe dintre cele două. Dacă conținutul celor două zone este identic, formula returnează 0. Pentru a utiliza această formulă, zonele de celule trebuie să fie de aceeași mărime și de aceeași dimensiune (de exemplu, dacă DateleMele reprezintă o zonă de 3 rânduri cu 5 coloane, DateleDvs. trebuie, de asemenea, să aibă 3 rânduri cu 5 coloane):

=SUM(IF(DateleMele=DateleTale;0;1))

Formula creează o matrice nouă de aceeași dimensiune ca zonele pe care le comparați. Funcția IF completează matricea cu valoarea 0 și valoarea 1 (0 pentru nepotriviri și 1 pentru celule identice). Funcția SUM returnează suma valorilor din matrice.

Formula poate fi simplificată astfel:

=SUM(1*(DateleMele<>DateleTale))

La fel ca formula care contorizează valorile eroare dintr-o zonă de date, această formulă funcționează deoarece TRUE*1=1 și FALSE*1=0.

Găsirea locației valorii maxime dintr-o zonă

Această formulă matrice returnează numărul de rând al valorii maxime dintr-o zonă cu o singură coloană denumită Date:

=MIN(IF(Date=MAX(Date);ROW(Date);""))

Funcția IF creează o matrice nouă, care corespunde cu zona denumită Date. Dacă o celulă corespondentă conține valoarea maximă din zonă, matricea conține numărul de rând. Altfel, matricea conține un șir gol (""). Funcția MIN utilizează matricea nouă ca al doilea argument și returnează cea mai mică valoare, care corespunde cu numărul de rând al valorii maxime din Date. Dacă zona denumită Date conține valori maxime identice, formula returnează rândul primei valori.

Dacă doriți să returnați adresa efectivă a celulei în care se află valoarea maximă, utilizați această formulă:

=ADDRESS(MIN(IF(Date=MAX(Date);ROW(Date);""));COLUMN(Date))

Confirmare de primire

Părți din acest articol au fost bazată pe o serie de coloane Excel Power utilizator scris de Colin Wilcox și adaptat din capitolele 14 și 15 din formule Excel 2002, o carte scrisă de John Walkenbach, un MVP fostului Excel.

Aveți nevoie de ajutor suplimentar?

Puteți întreba întotdeauna un expert de la Excel Tech Community, puteți obține asistență de la comunitatea Answers sau puteți sugera o caracteristică nouă sau o îmbunătățire pe Excel UserVoice.

Consultați și

Matricele dinamice și comportamentul matricelor vărsate

Formulele matrice dinamice vs moștenite CSE formulele matrice

Funcția FILTER

Funcția RANDARRAY

Funcția SEQUENCE

Funcția SINGLE

Funcția SORT

Funcția SORTBY

Funcția UNIQUE

Erorile #SPILL! din Excel

Prezentare generală a formulelor

Extindeți-vă competențele Office
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Office Insider

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×