SUMPRODUCT () funcție - plus și de numărare cu multiple condiții în ms Excel - compatibil cu

Funcția SUMPRODUCT (), versiunea în limba engleză SUMPRODUCT (), nu este la fel de simplu cum pare la prima vedere: în plus față de faptul găsirea unei sume de produse, această caracteristică poate fi utilizată pentru numărarea și totalizarea pe baza valorilor criteriilor, precum și, în unele cazuri, elimina necesitatea aplicații formule matrice.

Există mai multe opțiuni pentru utilizarea SUMPRODUCT () Funcția:

• găsirea suma produselor din lista elementelor (matrice);
• valori de adunare și numărare care îndeplinesc anumite criterii;
• Formule de înlocuire matrice (în unele cazuri).

Găsirea suma matricelor multiplicate

Această funcție secțiune afișează SUMPRODUCT () sintaxa și dezvăluie potențialul său pentru alte aplicații.

Să fie o gama A3 2 cifre: A6 și B3: B6. care cuprinde, respectiv, două numere de matrice. și. Formula de scriere = SUMPRODUCT (A3: A6; B3: B6). 123. obține rezultatul obținut prin înmulțirea elementwise toate elementele două matrice și apoi adăugarea produselor obținute. Adică, următoarele operații aritmetice se efectuează: * 7 + 4 8 * 6 + 6 * 7 + 1 * 5 = 123

Astfel, este posibil să se găsească suma produselor 3, 4, etc. matrice.

Argumentele care sunt matrice trebuie să aibă aceeași dimensiune (în acest caz, patru șiruri de elemente). În caz contrar, funcția funcția SUMPRODUCT () returnează #VALUE!.

In formula = SUMPRODUCT (A3: A6; B3: B6) SUMPRODUCT funcția () tratează elementele de matrice non-numerice ca zero. Cu toate acestea, după cum se arată mai jos poate fi utilizată pentru a calcula textul valorilor funcției.

Ce se întâmplă dacă specificați doar o singură matrice, și anume = SUMPRODUCT (A3: A6). Apoi SUMPRODUCT funcția () returnează suma elementelor, adică, va fi echivalentă cu funcția sum (). = SUM (A3: A6).

Funcția Sintaxă SUMPRODUCT () vă permite să specificați nu doar ca argument un anumit interval, dar, de asemenea, să efectueze operații aritmetice înainte de operația de însumare. De exemplu, scris:

• = SUMPRODUCT (A3: A6 * 2). obține suma produselor = 38 (fiecare element al șirului A3: A6 a fost înmulțit cu 2, atunci toate produsele însumate);
• = SUMPRODUCT (A3: A6 * B3: B6). Obținem rezultatul sumei de lucrări - 123 (toate elementele de matrice sunt multiplicate împreună în perechi, și apoi stivuite, adică A3 * B3 + A4 * B4 + A5 * B5 + A6 * B6), adică această formulă de înregistrare = SUMPRODUCT echivalentă (A3: A6; B3: B6);
• = SUMPRODUCT (A3: A6 + B3: B6). Obținem suma elementelor din cele două game;
• = SUMPRODUCT (A3: A6 / B3: B6), se obține o sumă de relație pairwise a tuturor elementelor, adică + 8/6 + 4/7 6/7 + 1/5 = 2.9619

Calcule similare pot fi efectuate cu funcția SUM (). Doar trebuie să faceți pentru a introduce ca o formulă matrice. și anume După introducerea funcției într-o celulă în loc de ENTER, apăsați CTRL + SHIFT + ENTER. = SUM (A3: A6 / B3: B6)

Frumusetea funcției SUMPRODUCT () este că, după intrarea în funcție într-o celulă, puteți pur și simplu apăsați ENTER. care îndepărtează o barieră psihologică pentru matrice folosind formule.

Se pare că argumentul acestei funcții, puteți specifica nu numai produsul de matrice (A3: A6 * B3: B6), dar, de asemenea, de a utiliza alte funcții și chiar să se aplice matrici de operare comparație, și anume l utilizați pentru a adăuga numere care îndeplinesc anumite condiții.

Însumare și a numărului de valori care îndeplinesc anumite criterii

Încercați să contoriza numărul de valori mari în intervalul 2 A3: A6. care conține valorile 4, 8, 6, 1.

Dacă vom scrie formula = SUMPRODUCT (A3: A6> 2). 0. obținem rezultatul evidențiat în formule de linie A3: A6> 2 și apăsând F9 tasta. obține o mulțime de care spune că ne îndreptăm în direcția cea bună: în gama A3: A6 mai mult de 2, numai primele 3 valori. Deși valoarea corespunzătoare TRUE 1 FALSE și - 0, obținem 3, pentru că traducere valorile TRUE / FALSE în formă numerică este obligată să le aplice pentru o operație aritmetică. Acest lucru se poate face, de exemplu, să aplice funcționarea unui negativ dublu (-), care va avea ca rezultat o matrice în formă numerică.

Astfel, problema de numărare valori mai mari de 2 se realizează după cum urmează: = SUMPRODUCT (- (A3: A6> 2))

In loc de dubla negație se poate folosi o altă formulă: = SUMPRODUCT (1 * (A3: A6> 2)) sau = SUMPRODUCT (0+ (A3: A6> 2)), sau chiar așa SUMPRODUCT = ((A3: A6> 2) = 1 ).

Înregistrarea> 2 este un criteriu care poate specifica orice operație comparație (<; <=;>=; =).

Criteriile pot fi specificate în formă de legături: = SUMPRODUCT (- (A3: A6> G8)) - G8 celula trebuie să conțină numărul 2.

Criteriile pot fi aplicate la text valori, de exemplu, = SUMPRODUCT (- (B3: B6 = „mere“)) - returnează numărul de celule care conțin cuvântul Apple (pentru detalii, de exemplu, valorile de numărare articol cu ​​mai multe criterii (Partea 1. Stare şi)).

poate fi utilizat pentru selectarea mai multor criterii valorile funcției SUMPRODUCT () (cu mai multe condiții). După cum este bine cunoscut, criteriul 2 poate forma condiții diferite:

• Sau condiții. De exemplu, celulele care conțin o valoare numărului de mere sau pere = SUMPRODUCT ((B3: B6 = "mere") + (B3: B6 = "para"));
• Condițiile I. De exemplu, pentru a contoriza numărul de valori mai mari de 2 și mai mic de 5: SUMPRODUCT = ((A3: A6> 2) * (A3: A6<5) )
• Condiția I. De exemplu, pentru a găsi suma numerelor este mai mare de 2 și mai mică de 5: = SUMPRODUCT ((A3: A6> 2) * (A3: A6<5)*( A3:A6 ) )

Fisierul exemplu arată soluția acestor probleme.

SUMPRODUCT () - matrice cu formula

În unele cazuri (când este necesar să se calculeze sau valori pliate care satisfac anumite criterii) pot fi înlocuite prin utilizarea funcției formule matrice SUMPRODUCT (). de exemplu: