Cum să nu ciocănești unghiile cu un microscop cu funcția de sumare

Utilizare standard

Sintaxa de bază a funcției noastre este simplă:

= SUMPRODUCT (Array1; Array2 ;.)

Opțiunea cea mai plictisitoare de a utiliza această funcție minunată este să o aplicați așa cum este descris în ajutor - pentru însumarea produselor cu celule pereche în două (și mai multe) intervale specificate. De exemplu, puteți calcula costul total al unei comenzi fără o coloană suplimentară:

Cum să nu ciocănești unghiile cu un microscop cu funcția de sumare

De fapt, ce poate fi această funcție exprimată prin formula:

= B2 * C2 + B3 * C3 + B4 * C4 + B5 * C5

Din punct de vedere tehnic, matricele multiplicate (intervalele) nu pot fi două, ci trei sau mai multe (până la 255). Principalul lucru este că ele au aceeași dimensiune. Convenabil, dar nimic deosebit. Cu toate acestea, pentru a folosi SUMPRODUCT numai așa - să ciocănești unghiile cu un microscop, pentru că, de fapt, știe mult mai mult.

Lucrul cu matrice fără Ctrl + Shift + Enter

Dacă sunteți puțin familiarizat cu formulele Excel cu o matrice, ar trebui să înțelegeți puterea și frumusețea lor. Uneori, o formulă de matrice poate înlocui mai multe coloane de calcule suplimentare și de muncă manuală. Dar formulele matricei au minusuri. Principalele sunt complexitatea relativă a înțelegerii, încetinirea recalculării cărții și necesitatea de a introduce aceste formule cu combinația Ctrl + Shift + Enter în loc de introducerea obișnuită. Iată cum vă poate ajuta funcția SUMPROSE. Nuance este că știe cum să lucreze cu matrice prin definiție, adică nu este necesar să apăsați Ctrl + Shift + Enter când tastați.

În acest sens, se bazează majoritatea trucurilor care utilizează SUMPRODUCT. Să luăm, de exemplu, câteva dintre cele mai caracteristice scenarii.

Numărarea numărului de condiții îndeplinite

Să presupunem că trebuie să calculam numărul de sucursale ale companiei în care se execută planul (adică faptul este mai mare sau egal cu planul). Acest lucru se poate face cu o formulă cu SUMPROPYV fără coloane suplimentare:

Înmulțirea cu 1, în acest caz, este necesară pentru a converti rezultatele comparației dintre plan și faptele logice TRUE și FALSE la 1 și, respectiv, 0.

Verificarea condițiilor multiple

Dacă trebuie să verificați mai multe condiții. atunci formula din exemplul anterior va trebui să fie completată cu încă unul (sau mai mulți) factori. Și dacă trebuie să numărați nu suma, ci suma, atunci puteți multiplica nu cu 1, ci cu intervalul cu datele sumate:

De fapt, se obține ceva foarte asemănător cu funcția matematică de numărare selectivă a SUMIFS. care poate de asemenea să verifice mai multe condiții (până la 127) și să rezume valorile din intervalul specificat.

Link-uri logice AND și OR (OR și OR)

Dacă doriți să asociați condițiile cu un "AND" non-logic, ca în exemplul de mai sus (Fact> = Plan) AND (Regiune = Est). dar logic OR. atunci construcția se va schimba ușor - semnul de înmulțire este înlocuit cu un semn plus.

Numărătoare de date dintr-un fișier închis (!)

În plus față de toate cele de mai sus, SUMPROPYV are o altă proprietate nevăzută și foarte utilă - știe cum să lucreze cu date din cărți nedescoperite. Dacă pentru comparație încercați să calculați într-un alt dosar numărul de ramuri din regiunea de Est a cărții noastre și scrieți-le:

atunci a doua formulă cu funcția clasică COUNTIFS va funcționa numai atâta timp cât fișierul sursă este deschis. Dacă îl închideți, apare eroarea # VALUE! Funcția noastră SUMPRODUCT este calmă, potrivit datelor, chiar și dintr-o carte nedivulvă!