În acest articol, vom învăța să returnăm prețul SUM în zilele lucrătoare în Excel.
Cu cuvinte simple, în timp ce lucrați cu date despre preț. Uneori, trebuie să găsim suma valorii având o condiție. Condiția este de a obține suma valorilor prețurilor în coloane separate.
Pentru acest articol vom avea nevoie de următoarele funcții:
- Funcția SUMPRODUCT
- Funcția COLUMN
- Funcția MOD
Acum vom face o formulă din aceste funcții. Aici ni se va da o listă de valori și trebuie să obținem suma valorilor stabilite în fiecare multiplu al celei de-a n-a coloane.
Folosiți formula:
= SUMPRODUCT (- (MOD (COLUMN (list)) - COLUMN (first) + 1, n) = 0), list)
listă : lista valorilor de-a lungul coloanei
primul : prima celulă a listei care returnează numărul coloanei
n : valorile coloanei a noua de adăugat.
Exemplu:
Toate acestea ar putea fi confuze pentru a înțelege. Deci, să testăm această formulă executând-o pe exemplul prezentat mai jos.
Aici am dat date care conțin numere. Aici trebuie să găsim SUMA de numere din fiecare multiplu al celei de-a treia coloane. Deci, pentru aceasta, am alocat liste într-o coloană pentru formulă.
Acum vom folosi formula de mai jos pentru a obține SUMA de numere la 3, 6, 9, 12 și așa mai departe până când lista se încheie
Formulă:
= SUMPRODUCT (- (MOD (COLUMN (listă) - COLUMN (C5) + 1, P5) = 0), listă)
list: listă de valori de-a lungul coloanei date ca folosind intervalele denumite.
C5: prima celulă a listei care returnează numărul de coloană dat ca referință de celulă
P5: valoarea lui n dată ca referință de celulă
Explicaţie:
- MOD (COLUMN (list) -COLUMN (C5) + 1, P5) returnează numărul relativ al coloanei pentru fiecare n interval. Ca
{ 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 }
- Matricea de mai sus va fi potrivită cu o valoare 0 și returnează o matrice de Adevărat și Fals.
{FALS, FALSE, ADEVĂRATE, FALSE, FALSE, ADEVĂRATE, FALSE, FALSE, ADEVĂRATE, FALSE, FALSE, ADEVĂRATE, FALSE}
- Funcția SUMPRODUCT consideră valoarea 1 pentru fiecare valoare ADEVĂRATĂ primită și 0 pentru valoarea FALSĂ așa cum se arată mai jos.
= SUMPRODUCT (- ({0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0}), {40, 36, 51, 33, 42, 30, 92, 67, 34, 54, 69, 56, 50})
- - convertește TRUE la 1 și FALSE la 0.
- Valorile corespunzătoare 1s se vor adăuga numai așa cum se arată în formula explicată mai sus.
- Acum funcția SUMPRODUCT adaugă toate valorile așa cum se arată mai jos.
= SUMPRODUCT ({0, 0, 51, 0, 0, 30, 0, 0, 34, 0, 0, 56, 0})
SUMA valorilor {51 + 30 + 34 + 56} trebuie să returneze 171.
Aici matricea funcției este dată ca interval denumit și celulă ca referință_celulă. Apăsați Enter pentru a obține rezultatul.
După cum puteți vedea în instantaneul de mai sus, suma valorilor extrase la fiecare al treilea interval returnează 171. Valorile sunt marcate sub casete roșii, așa cum se arată în instantaneul de mai sus.
Sau puteți utiliza matricea listă utilizând formula de mai jos:
= SUMPRODUCT (- (MOD (COLUMN (C5: O5) - COLUMN (C5) + 1, P5) = 0), C5: O5)
Formula de mai sus va întoarce același rezultat. Există o singură listă de înlocuire numită gamă care se înlocuiește cu gama matricei (C5: O5).
După cum puteți vedea, formula funcționează bine. Acum trebuie să copiem formula pentru celelalte liste. Deci folosiți CTRL + D sau trageți în jos opțiunea excel.
După cum puteți vedea din formula de mai sus, puteți obține valori condiționale.
Note:
- Funcția SUMPRODUCT consideră valori nenumerice ca 0s.
- Funcția SUMPRODUCT consideră valoarea logică TRUE ca 1 și False ca 0.
- Argumentul trebuie să aibă aceeași lungime, altfel formula returnează erori.
Sper că acest articol despre cum să returnați SUM numai fiecare coloană a noua cu formule în Excel este explicativ. Găsiți mai multe articole despre funcțiile SUMPRODUCT aici. Vă rugăm să împărtășiți interogarea dvs. mai jos în caseta de comentarii. Vă vom ajuta.
Cum se utilizează funcția SUMPRODUCT în Excel: Returnează SUMA după multiplicarea valorilor în mai multe tablouri în excel.
SUMA dacă data este între : Returnează SUMA valorilor între date sau perioade date în Excel.
Suma dacă data este mai mare decât data dată: Returnează SUMA valorilor după data sau perioada dată în Excel.
2 moduri de a suma prin lună în Excel: Returnează SUMA valorilor într-o anumită lună specifică în Excel.
Cum să însumați mai multe coloane cu condiție: Returnează SUMA valorilor pe mai multe coloane cu condiție în excel
Articole populare
50 Comandă rapidă Excel pentru a vă crește productivitatea
Editați o listă derulantă
Referință absolută în Excel
Dacă cu formatare condiționată
Dacă cu wildcards
Vizualizați după dată
Convertiți inci în picioare și inci în Excel 2016
Alăturați-vă prenumelui și prenumelui în Excel
Numărați celulele care se potrivesc fie cu A, fie cu B