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 mai multe calcule pe unul sau mai multe elemente dintr-o matrice. Puteți să vă gândiți la o matrice ca rând sau coloană de valori sau la o combinație de rânduri și coloane de valori. Formulele matrice pot returna fie rezultate multiple, fie un singur rezultat.

Începând cu actualizarea 2018 din septembrie pentru Office 365, orice formulă care poate returna mai multe rezultate le va vărsa automat fie în jos, fie în celulele vecine. Această modificare a comportamentului este însoțită și de mai multe funcții noi de matrice dinamică. Formulele matrice dinamice, indiferent dacă utilizează funcții existente sau funcțiile matrice dinamice, trebuie doar să fie introduse într-o singură celulă, apoi confirmate apăsând Enter. Anterior, formulele matrice moștenite necesită mai întâi Selectarea întregii zone de ieșire, apoi confirmând formula cu Ctrl + Shift + Enter. Acestea sunt denumite în mod obișnuit formule CSE .

Puteți utiliza formule matrice pentru a efectua activități complexe, cum ar fi:

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

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

  • Însumați doar numerele care îndeplinesc anumite condiții, cum ar fi cele mai mici valori dintr-o zonă sau numerele care se încadrează între o margine superioară și inferioară.

  • Însumați fiecare valoare de n dintr-o zonă de valori.

Următoarele exemple 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 dintre funcțiile de matrice dinamică, precum și formulele matrice existente introduse ca matrice dinamice și moștenite.

Descărcați exemplele noastre

Descărcați un exemplu de registru de lucru cu toate exemplele de formule matrice din 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 mai multe celule din celula H10 = F10: f19 * G10: G19 pentru a calcula numărul de autoturisme vândute după prețul unitar

  • Iată că calculăm vânzările totale de Coupe și Sedan pentru fiecare vânzător, introducând = F10: f19 * G10: G19 în celula H10.

    Atunci când apăsați pe Enter, veți vedea rezultatele propagării în jos la celulele H10: H19. Observați că zona de deversare este evidențiată cu o bordură atunci când selectați orice celulă din zona de deversare. De asemenea, este posibil să observați că formulele din celulele H10: H19 sunt estompate. Sunt acolo doar pentru referințe, așadar, dacă doriți să ajustați formula, va trebui să selectați celula H10, unde se află formula principală.

  • Formulă matrice cu o singură celulă

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

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

    În acest caz, Excel înmulțește valorile din matrice (zona de celule F10 prin G19), apoi utilizează funcția SUM pentru a aduna totalurile împreună. Rezultatul este un total general de 1.590.000 în vânzări.

    Acest exemplu arată cât de puternic este acest tip de formulă. De exemplu, să presupunem că aveți 1.000 de rânduri de date. Aveți posibilitatea să însumați datele parțial sau total creând o formulă matrice într-o singură celulă în loc să glisați formula în jos prin cele 1000 de rânduri. De asemenea, observați că formula cu o singură celulă din celula H20 este complet independentă de formula cu mai multe celule (formula din celulele H10 până la H19). Acesta este un alt avantaj al utilizării formulelor matrice: flexibilitatea. Puteți să modificați celelalte formule din coloana H fără a afecta formula în H20. De asemenea, poate fi o bună practică să aveți totaluri independente ca acesta, deoarece vă ajută să validați acuratețea rezultatelor.

  • Formulele matrice dinamice oferă, de asemenea, aceste avantaje:

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

    • Siguranță    Nu puteți suprascrie o componentă a unei formule matrice cu mai multe celule. De exemplu, faceți clic pe H11 celulă și apăsați pe Delete. Excel nu va modifica ieșirea matricei. Pentru a-l modifica, trebuie să selectați celula din stânga sus din matrice sau celula H10.

    • 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, exemplul de vânzări auto utilizează o singură formulă matrice pentru a calcula rezultatele în coloana E. Dacă ați utilizat formule standard, cum ar fi = F10 * G10, F11 * G11, F12 * G12 etc., ați fi utilizat 11 formule diferite pentru a calcula aceleași rezultate. Nu este mare lucru, dar dacă ați avut mii de rânduri de total? Atunci poate face o mare diferență.

    • Eficiență    Funcțiile matrice pot fi o modalitate eficientă de a construi formule complexe. Formula matrice = SUM (F10: f19 * G10: G19) este identică cu aceasta: = SUM (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * g16, F17 * G17, F18 * g18, f19 * G19).

    • Depășesc    Formulele matrice dinamice se vor răspândi automat în zona de ieșire. Dacă datele sursă se află într-un tabel Excel, atunci formulele matrice dinamice se vor redimensiona automat pe măsură ce adăugați sau eliminați date.

    • #SPILL! eroare    Matrice dinamice a introdus eroarea #SPILL!, ceea ce indică faptul că zona de scurgere intenționată este blocată din anumite motive. Atunci când rezolvați blocarea, formula se va vărsa 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} or = {"ianuarie", "februarie", "martie"}

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 cu virgule și delimitați fiecare rând cu punct și virgulă.

În următoarele proceduri veți exersa crearea constantelor orizontale, verticale și bidimensionale. Vom afișa exemple folosind funcția Sequence pentru a genera automat constantele de matrice, precum și constantele matrice introduse manual.

  • Crearea unei constante orizontale

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

    Creați o constantă matrice orizontală cu = SECVENȚă (1,5) sau = {1, 2, 3, 4, 5}

  • Crearea unei constante verticale

    Selectați orice celulă necompletată cu cameră dedesubt și introduceți = secvență (5)sau = {1; 2; 3; 4; 5}. Se afișează următorul rezultat:

    Creați o constantă matrice verticală cu = SECVENȚă (5) sau = {1; 2; 3; 4; 5}

  • Crearea unei constante bidimensionale

    Selectați orice celulă necompletată cu cameră la dreapta și dedesubt și introduceți = secvență (3, 4). Veți vedea următorul rezultat:

    Creați o constantă 3 rânduri cu 4 coloane matrice cu = SECVENȚă (3, 4)

    De asemenea, puteți să introduceți: or = {1; 2; 3; 4; 5; 6, 7; 8; 9, 10, 11, 12}, dar veți dori să acordați atenție locului în care puneți semi-colonele versus virgule.

    După cum puteți vedea, opțiunea SECVENȚă oferă avantaje semnificative asupra introducerii manuale a valorilor constante ale matricei. În primul rând, vă economisește timpul, dar poate contribui, de asemenea, la reducerea erorilor din intrarea manuală. Este, de asemenea, mai ușor de citit, mai ales dacă semi-colonele pot fi greu de distins de separatorii de virgule.

Iată un exemplu care utilizează constantele matrice ca parte a unei formule mai mari. În registrul de lucru eșantion, mergeți la constantă într-o foaie de lucru formulă sau creați o foaie de lucru nouă.

În celula D9, am introdus = secvență (1, 5, 3, 1), dar puteți introduce, de asemenea, 3, 4, 5, 6 și 7 în celulele A9: H9. Nu este nimic special în această selecție de numere, tocmai am ales altceva decât 1-5 pentru diferențiere.

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

Utilizați constantele de matrice în formule. În acest exemplu, am utilizat = SUM (D9: H (* SECVENȚă (1,5))

Funcția SEQUENCE construiește echivalentul constantă a matricei {1, 2, 3, 4, 5}. Deoarece Excel efectuează operațiuni în expresiile încadrate mai întâi în paranteze, următoarele două elemente care intră în joc sunt valorile celulelor din D9: H9 și operatorul de înmulțire (*). În acest moment, formula înmulțește valorile din matricea stocată cu valorile corespunzătoare din constantă. Este echivalentul formulei:

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

În cele din urmă, funcția SUM adună valorile și returnează 85.

Pentru a evita utilizarea matricei stocate și a menține integral operațiunea în memorie, o puteți înlocui cu altă constantă matrice:

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

Elemente pe care le puteți utiliza în constantele matrice

  • Constantele matrice pot conține numere, text, valori logice (cum ar fi TRUE și FALSE) și valori de eroare, cum ar fi #N/A. Puteți utiliza numere în formate întregi, zecimale și științifice. Dacă includeți text, trebuie să îl înconjurați 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.

Una dintre cele mai bune modalități de a utiliza constantele matrice este să le denumiți. Constantele denumite pot fi mai ușor de utilizat și pot face formulele matrice mai ușor de înțeles de către alte persoane. Pentru a denumi o constantă matrice și a o utiliza într-o formulă, procedați astfel:

Accesați formule _GT_ nume definite > 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"}

Caseta de dialog ar trebui să arate acum astfel:

Adăugarea unei constante matrice numite 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:

Utilizați o constantă matrice denumită într-o formulă, cum ar fi = Trimestru1, unde Trimestru1 a fost definit ca = {"ianuarie", "February", "martie"}

Dacă doriți ca rezultatele să se răspândească pe verticală în loc de orizontală, puteți utiliza =transpose(trimestru1).

Dacă doriți să afișați o listă de 12 luni, așa cum se poate utiliza atunci când construiți o declarație financiară, puteți să o bazați pe anul curent cu funcția SEQUENCE. Lucrul elegant despre această funcție este că, deși se afișează doar luna, există o dată validă în spatele acestuia pe care o puteți utiliza în alte calcule. Veți găsi aceste exemple pe foile de lucru cu seturi de date eșantion , constantă și rapidă, din registrul de lucru exemplu.

= TEXT (dată (an (astăzi ()), SECVENȚă (1, 12), 1), "mmm")

Utilizați o combinație a funcțiilor TEXT, dată, an, TODAY și SECVENȚă pentru a construi o listă dinamică de 12 luni

Aceasta utilizează funcția date pentru a crea o dată bazată pe anul curent, secvența creează o constantă matrice de la 1 la 12 pentru ianuarie până în decembrie, apoi funcția text convertește formatul de afișare la "mmm" (Jan, Feb, Mar etc.). Dacă doriți să afișați numele complet al lunii, cum ar fi ianuarie, utilizați "mmmm".

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ă matricea ca șir de text și formula nu va funcționa așa cum vă așteptați. În sfârșit, rețineți că puteți utiliza combinații de funcții, text și numere. Totul depinde de modul creativ pe care doriți să-l obțineți.

Următoarele exemple demonstrează câteva dintre modurile în care se pot utiliza constantele matrice în formulele matrice. Unele exemple utilizează funcția transpose pentru a efectua conversia rândurilor la coloane și invers.

  • Mai multe elemente dintr-o matrice

    Enter = Sequence (1; 12) * 2sau = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} * 2

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

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

    Enter = Sequence (1; 12) ^ 2sau = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} ^ 2

  • Găsirea rădăcinii pătrate a elementelor pătrate dintr-o matrice

    Enter =Sqrt(secvență (1; 12) ^ 2), or = Sqrt ({1, 2, 3, 4; 5, 6, 7; 8; 9, 10, 11, 12} ^ 2)

  • Transpunerea unui rând unidimensional

    Enter = Transpose (secvență (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

    Enter = Transpose (secvență (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

    Enter = Transpose (secvență (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 explică modul în care se utilizează formulele matrice pentru a crea o matrice nouă dintr-o matrice existentă.

    Enter = Sequence (3, 6, 10, 10)sau = {10, 20, 30, 40, 50, 60; 70; 80, 90100110120; 130140150160170180}

    Asigurați-vă că tastați {(acoladă deschisă) înainte de a tasta 10 și} (acoladă de închidere) 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 celule 3 x 6 apare cu aceleași valori pe care le vedeți în D9: D11. Semnul # se numește operatorul zoneideversate și este modul Excel's de a face referire la întreaga gamă de matrice în loc să fie necesar să îl tastați.

    Utilizați operatorul de zonă deversată (#) pentru a face referire la o matrice existentă

  • Crearea unei constante matrice din valori existente

    Puteți să luați rezultatele unei formule de matrice vărsate și să le transformați în părțile componente. Selectați celula D9, apoi apăsați F2 pentru a comuta la modul de editare. În continuare, apăsați F9 pentru a efectua conversia referințelor la celule la valori, pe care Excel le convertește apoi într-o constantă matrice. Atunci când apăsați pe 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. Acestea includ spații.

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

    = SUM (LEN (C9: C13))

    În acest caz, funcția Len Returnează lungimea fiecărui șir text din fiecare celulă din zonă. Funcția SUM adună apoi acele valori și afișează rezultatul (66). Dacă doriți să obțineți numărul mediu de caractere, puteți utiliza:

    = AVERAGE (LEN (C9: C13))

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

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

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

    Să examinăm formula, începând de la elementele interioare și mergând spre exterior. Funcția Len Returnează lungimea fiecărui element din zona de celule D2: D6. Funcția Max calculează cea mai mare valoare dintre acele elemente, care corespunde celui mai lung șir text, care se află în celula D3.

    Aici lucrurile devin mai complicate. Funcția Match calculează decalajul (poziția relativă) a 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 (C9: C13)

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

    LEN (C9: C13)

    Argumentul tip de corespondență din acest caz este 0. Tipul de corespondență poate fi o valoare 1, 0 sau-1.

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

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

    • -1-returnează 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 cele din urmă, funcția index are următoarele argumente: o matrice și un număr de rând și de coloană în acea matrice. Zona de celule C9: C13 furnizează matricea, funcția MATCH oferă adresa celulei, iar argumentul final (1) specifică faptul că valoarea provine din prima coloană din matrice.

    Dacă doriți să obțineți conținutul celui mai mic șir de text, îl înlocuiți pe 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, unde o matrice de date eșantion din celulele B9: B18has a fost creată cu: = int (RANDARRAY(10, 1) * 100). Rețineți că RANDARRAY este o funcție volatilă, astfel încât veți primi un set nou de numere aleatoare de fiecare dată când calculează Excel.

    Formulă matrice Excel pentru a găsi cea mai mică valoare n: = SMALL (B9 #, SECVENȚă (D9))

    Enter = Small (B9 #, secvență (D9), = Small (B9: B18, {1; 2; 3})

    Această formulă utilizează o constantă matrice pentru a evalua funcția Small de trei ori și a returna cele mai mici 3 membri din matricea conținută în celulele B9: B18, unde 3 este o valoare variabilă în celula D9. Pentru a găsi mai multe valori, aveți posibilitatea să măriți valoarea din funcția SEQUENCE sau să adăugați mai multe argumente la constantă. De asemenea, aveți posibilitatea să utilizați funcții suplimentare cu această formulă, cum ar fi SUM sau AVERAGE. De exemplu:

    = SUM (SMALL (B9 #, SECVENȚĂ (D9))

    = AVERAGE (MICI (B9 #, SECVENȚĂ (D9))

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

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

    Enter = Large (B9 #, Row (indirect ("1: 3"))) sau = mare (B9: B18, Row (indirect ("1:3") ))

    Acum, poate fi util să cunoașteți câteva lucruri despre funcțiile ROW și INDIRECT. Aveți posibilitatea să utilizați funcția ROW pentru a crea o matrice de numere întregi consecutive. De exemplu, selectați un gol și introduceți:

    =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 ajustează referințele de rânduri, iar formula generează acum 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 de text ca argumente (de aceea zona 1:10 este înconjurată de ghilimele). 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ă. Puteți utiliza SECVENȚa la fel de ușor:

    = SECVENȚĂ (10)

    Să examinăm formula pe care ați utilizat-o mai devreme-= mare (B9 #, ROW (INDIRECT ("1:3"))) — începând de la parantezele interioare și lucrul exterior: funcția INDIRECT returnează un set de valori text, în acest caz valorile de la 1 la 3. Funcția ROW, la rândul său, generează o matrice coloană cu trei celule. Funcția mare utilizează valorile din zona de celule B9: B18 și este evaluată de trei ori, o dată pentru fiecare referință 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 sfârșit, la fel ca în cazul MICIlor exemple, puteți utiliza această formulă cu alte funcții, cum ar fi SUM și AVERAGE.

  • Însumarea unei zone care conține valori eroare

    Funcția SUM din Excel nu funcționează atunci când încercați să însumaț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 valorile dintr-o zonă denumită date care conține erori:

    Utilizați matrice pentru a rezolva erorile. De exemplu, = SUM (IF (ISERROR (date), "", date) va însuma zona denumită date, chiar dacă include erori, 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 de 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ă însumați valorile pe baza unor condiții.

Puteți utiliza matrice pentru a calcula în funcție de anumite condiții. = SUM (IF (Sales>0, vânzări)) va însuma toate valorile mai mari decât 0 într-o zonă denumită vânzări.

De exemplu, această formulă matrice însumează doar numerele întregi pozitive 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 pozitive și 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ă valori mai mari decât 0 și mai mici decât 2500:

= SUM ((Sales>0) * (Sales<2500) * (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, puteți să însumați valori mai mari decât 0 sau mai mici decât 2500:

= SUM (IF ((Sales>0) + (Sales<2500), vânzări))

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 adăugarea sau înmulțirea la valori care îndeplinesc condițiile sau sau și starea.

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 compară valorile din două zone de celule numite Datelemele și Dateletale și returnează numărul de diferențe dintre cele două. Dacă conținutul celor două intervale este identic, formula returnează 0. Pentru a utiliza această formulă, intervalele de celule trebuie să aibă aceeași dimensiune și aceeași dimensiune. De exemplu, dacă Datelemele este o zonă de 3 rânduri cu 5 coloane, Dateletale trebuie să fie, de asemenea, 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 * (MyData<>YourData))

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 pentru diferențele dintre foaia de lucru pentru Seturile 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 întregul tabel de mai jos și lipiți-l în celula a1 într-o foaie de lucru necompletată.

Vânzări Persoană

Mașină Tastați

Număr Vândute

Unitate Preț

Total Vânzări

Preda

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 vânzările totale 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 înconjoară formula cu acolade ({}) și inserează o instanță a formulei în fiecare celulă a zonei selectate. Acest lucru se întâmplă foarte rapid, deci ce vedeți în coloana E va fi cantitatea 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 a registrului de lucru, tastați următoarea formulă, apoi apăsați Ctrl + Shift + Enter:

=SUM(C2:C11*D2:D11)

În acest caz, Excel înmulțește valorile din matrice (zona de celule C2 până la D11), apoi utilizează funcția Sumpentru a aduna totalurile împreună. Rezultatul este un total general de 1.590.000 în vânzări. Acest exemplu arată cât de puternic este acest tip de formulă. De exemplu, să presupunem că aveți 1.000 de rânduri de date. Aveți posibilitatea să însumați datele parțial sau total creând o formulă matrice într-o singură celulă în loc să glisați formula în jos prin cele 1000 de rânduri.

De asemenea, observați că formula cu o singură celulă din celula D13 este complet independentă de formula cu mai multe celule (formula din celulele E2 până la E11). Acesta este un alt avantaj al utilizării formulelor matrice: flexibilitatea. Puteți să modificați formulele din coloana E sau să ștergeți coloana 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.

  • Siguranță    Nu se poate suprascrie o componentă a unei formule matrice cu mai multe celule. De exemplu, faceți clic pe celula E3 și apăsați pe Delete. Trebuie fie să selectați întreaga zonă de celule (E2 până la E11) și să modificați formula pentru întreaga matrice, fie să lăsați matricea neschimbată. Ca măsură de siguranță adăugată, trebuie să apăsați Ctrl + Shift + Enter pentru a confirma orice 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 utilizează sintaxa standard de formule. Toate încep cu semnul egal (=) și se pot utiliza majoritatea funcțiilor predefinite Excel. Diferența de cheie este că, atunci când utilizați o formulă matrice, apăsați Ctrl + Shift + Enter pentru a introduce formula. Când faceți acest lucru, Excel încadrează formula între acolade; dacă tastați acoladele manual, formula se va transforma într-un șir text și nu va funcționa.

Funcțiile matrice pot fi o modalitate eficientă de a construi formule complexe. Formula matrice =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 de câte ori trebuie să introduceți o formulă matrice. Aceasta se aplică atât pentru formulele cu o singură celulă, cât și pentru cele 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ă de formule (de exemplu, E2: E11), apoi apăsați pe Delete.

  • Nu puteți insera celule necompletate în sau să ștergeți celule dintr-o formulă matrice cu mai multe celule.

Uneori, poate fi necesar să extindeți o formulă matrice. Selectați prima celulă din zona de matrice existentă și continuați până când ați selectat întreaga zonă în care doriți să extindeți formula. Apăsați F2 pentru a edita formula, apoi apăsați Ctrl + Shift + Enter pentru a confirma formula după ce ați ajustat zona de formule. Cheia este să selectați întreaga zonă, începând cu celula din stânga sus din matrice. Celula din stânga sus este cea care se editează.

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

  • Uneori, puteți să uitați să apăsați Ctrl + Shift + Enter. Acest lucru li se poate întâmpla chiar și utilizatorilor cu o experiență îndelungată ai programului Excel. Nu uitați să apăsați această combinație de taste oricând introduceți sau editați o formulă matrice.

  • Este posibil ca alți utilizatori ai registrului de lucru să nu înțeleagă formulele dvs. De obicei, formulele matrice nu sunt explicate în foile de lucru. Prin urmare, dacă alte persoane trebuie să modifice registrele de lucru, trebuie fie să evitați formulele matrice, fie să vă asigurați că aceste persoane știu despre formulele matrice și cum să le modifice, dacă trebuie.

  • Î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 atunci când creați formule matrice. Deoarece constantele matrice sunt o componentă a formulelor matrice, încadrați constantele cu acolade, tastându-le manual. Apoi utilizați Ctrl + Shift + Enter pentru a introduce toată formula.

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}. Iar aici se află o matrice cu două rânduri și patru coloane: {1\2\3\4;5\6\7\8}. În matricea două rânduri, primul rând este 1, 2, 3 și 4, iar al doilea rând este 5, 6, 7 și 8. Cele două rânduri, între 4 și 5 sunt separate printr-un semn punct și virgulă.

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ă, apoi apăsați Ctrl + Shift + Enter:

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

    În acest caz, trebuie să tastați acoladele de deschidere și de închidere ({}), iar 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ă, apoi 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ă, apoi 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ă, apoi 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 din paranteze este constantă matrice: {1, 2, 3, 4, 5}. Rețineți că Excel nu înconjoară constantele matrice cu bretele; de fapt, tastați-le. 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 aceasta, copiați funcția, selectați o celulă necompletată din registrul de lucru, lipiți formula în bara de formule, apoi apăsați Ctrl + Shift + Enter. Vedeți același rezultat ca în exercițiul anterior, care a utilizat 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.

Una dintre cele mai bune căi de utilizare a constantelor matrice este să le denumiți. Constantele denumite pot fi mai ușor de utilizat și pot face formulele matrice mai ușor de înțeles de către alte persoane. Pentru a denumi o constantă matrice și a o utiliza într-o formulă, procedați astfel:

  1. În 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ă

Atunci când utilizați o constantă denumită ca formulă matrice, nu uitați să introduceți semnul egal. Dacă nu, Excel interpretează matricea ca șir de text și formula nu va funcționa așa cum vă așteptați. În sfârșit, rețineți că puteți utiliza combinații de text și numere.

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

  • Este posibil ca unele elemente să nu fie separate cu caracterul corect, conform setărilor regionale. Dacă omiteți o virgulă sau punct și virgulă sau dacă plasați una în locul nepotrivit, este posibil ca constanta matrice să nu fie creată corect sau 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 dintre modurile în care se pot utiliza constantele matrice în formulele matrice. Unele exemple utilizează funcția transpose pentru a efectua conversia rândurilor 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ă, apoi 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. Introduceți următoarea constantă, apoi 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 din zona de celule C8: E10 utilizând o formulă matrice. Pe foaia de lucru, C8- 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, apoi apăsați Ctrl + Shift + Enter:

    =C8:E10

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

Crearea unei constante matrice din valori existente

  1. Cu celulele C1: C3 selectat, apăsați F2 pentru a comuta la modul de editare. 

  2. Apăsați F9 pentru a efectua conversia referințelor la celule la valori. Excel efectuează conversia valorilor într-o constantă matrice. Formula ar trebui 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, apoi apăsați Ctrl + Shift + Enter pentru a vedea numărul total de caractere din celulele A2: A6 (66).

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

Următoarea formulă este utilizată în celula A8 numără numărul total de caractere (66) din celulele de la a2 la A6.

=SUM(LEN(A2:A6))

În acest caz, funcția LEN returnează lungimea fiecărui șir text din fiecare celulă din zonă. Funcția SUM adună apoi acele valori și afișează rezultatul (66).

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.

  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 conține rezultatele returnate de formula matrice.

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

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

Această formulă utilizează o constantă matrice pentru a evalua funcția Small de trei ori și a returna cea mai mică (1), a doua cea mai mică (2) și a treia cea mai mică (3) membri din matricea care este conținută în celulele a1: A10 pentru a găsi mai multe valori, adăugați mai multe argumente la constantă. De asemenea, aveți posibilitatea să utilizați funcții suplimentare cu această formulă, cum ar fi SUM sau AVERAGE. De exemplu:

= SUM (SMALL (A1: A10; {1; 2; 3})

= AVERAGE (SMALL (A1: A10; {1; 2; 3})

Găsirea celor mai mari n 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 până la D3.

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

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

Acum, poate fi util să cunoașteți câteva lucruri despre funcțiile ROW și INDIRECT. Aveți posibilitatea să utilizați funcția ROW pentru a crea o matrice de numere întregi consecutive. De exemplu, selectați o coloană goală de 10 celule din registrul de lucru practică, introduceți această formulă matrice, 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 la formula pe care ați utilizat-o mai devreme- = Large (A5: A14, Row (indirect ("1:3"))) — începând de la parantezele interioare și lucrul exterior: funcția indirect returnează un set de valori text, în acest caz valorile de la 1 la 3. Funcția Row , la rândul său, generează o matrice coloane cu trei celule. Funcția mare utilizează valorile din zona de celule A5: A14 și este evaluată de trei ori, o dată pentru fiecare referință returnată de funcția Row . Valorile 3200, 2700 și 2000 sunt returnate în matricea coloane din trei celule. Dacă doriți să găsiți mai multe valori, adăugați o zonă de celule mai mare la funcția indirect .

Ca în cazul exemplelor anterioare, puteți utiliza această formulă cu alte funcții, cum ar fi SUM și AVERAGE.

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

Reveniți la exemplul de șir text 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 "buchet de celule care" apare.

Să examinăm formula, începând de la elementele interioare și mergând spre exterior. Funcția Len Returnează lungimea fiecărui element din zona de celule a2: A6. Funcția Max calculează cea mai mare valoare dintre acele elemente, care corespunde celui mai lung șir text, care se află î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 preia aceste argumente: o matrice și un număr de rând sau coloană din acea matrice. Zona de celule a2: A6 oferă matricea, 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 numite Datelemele și Dateletale și returnează numărul de diferențe dintre cele două. Dacă conținutul celor două intervale este identic, formula returnează 0. Pentru a utiliza această formulă, intervalele de celule trebuie să aibă aceeași dimensiune și aceeași dimensiune (de exemplu, dacă Datelemele este o zonă de 3 rânduri cu 5 coloane, Dateletale trebuie să fie, de asemenea, 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 * (MyData<>YourData))

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

Părțile din acest articol s-au bazat pe o serie de coloane de utilizator Power Excel scrise de Colin Wilcox și adaptate din capitolele 14 și 15 din formulele Excel 2002, o carte scrisă de ioan Walkenbach, un fost membru Excel MVP.

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. formulele matrice MCP moștenite

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.

×