Însumarea valorilor absolute (în valoare absolută) pentru a excela

Un coleg de-al meu mi-a cerut o dată cum să folosească formule Excel pentru a calcula suma valorilor absolute în intervalul specificat. Această întrebare vine destul de regulat în forumuri, mulți utilizatori au adesea probleme cu această acțiune aparent simplu.

Din păcate, în Microsoft Excel, nu există nici o funcție încorporată capabil de a efectua modulo, deci va trebui să lucreze un pic pentru a obține răspunsul corect.

Aici sunt descoperirile noastre:

Vedem că suma numerelor în gama A2: A8 -60 dă rezultatul:

= + 10 + -10 20 + -20 + 30 + -40 + -50 = -60

Dacă am considerat valorile absolute (nesemnate „-“), rezultatul ar fi 180:

= + 10 + 10 20 + 20 + 30 + 40 + 50 = 180

Opțiunea 1 - Utilizarea coloana auxiliară

În opinia mea, cel mai bun mod de a conta suma valorilor absolute în Excel - utilizați o coloană auxiliară. In celula B2 introduceți formula:

Apoi, trageți-l în celula B8. Funcția ABS returnează numărul modulului. Deci, acum putem rezuma doar la gama B2: B8, și ne va da un rezultat de 180.

În exemplul meu, gama A1: A8 - un tabel de date completă. De aceea, prin adăugarea de formula = ABS (A2) în celula B2. Excel extins de masă și completate automat în toate celulele din coloana. În continuare am mutat pe la fila Design (Design), care este într-un grup de file de lucru cu tabele (instrumente de masă), și a pus o bifare de lângă opțiunea rezultatele șir (Total Row). Toate valorile sunt în coloana B este rezumată în mod automat, iar rezultatul este afișat într-o linie separată.

Pentru calcularea sumei utilizate, în total la subtotală funcția (SUBTOTAL). Este o funcție universală, care poate efectua o însumare, precum și funcția sumei (sumă). Dar există diferențe semnificative, cum ar fi SUBTOTAL (SUBTOTAL) ignoră complet numerele care au fost ascunse manual sau prin filtrare. Există câteva diferențe, dar este un pic adevărat la tema acestui articol.

Metoda cu coloana auxiliară este bun, ceea ce conferă o mai mare flexibilitate, în cazul în care datele care urmează să fie utilizate în viitor, cum ar fi o masă sau un PivotTable. În plus, coloana auxiliară poate fi utilizată pentru sortarea întregi modulo.

Este, fără îndoială, o modalitate foarte bună, dar ce faci atunci când doriți să se potrivească totul într-o singură formulă, fără nici o coloană de susținere?

Opțiunea 2 - Utilizarea SUM în formula matrice sau SUMPRODUCT

Utilizați o formulă de matrice sau SUMPRODUCT (SUMPRODUCT) pentru a rezolva această problemă - o abordare foarte dur!

Funcția SUM într-o formulă matrice:

Atunci când introducerea de formule matrice nu uitați să apăsați pe Ctrl + Shift + Enter.

Formula cu SUMMPROZV:

Având în vedere că este posibil să se utilizeze mai eficient funcția SUMIF (SUMIF), pentru a obține același rezultat (a se vedea. 3 exemplu de realizare), calea cu utilizarea acestor două formule devine nepreferabil. Ambele formule funcționează bine cu intervale mici de date, sigur, nici măcar nu simt diferența. Dacă avem nevoie pentru a rezuma un număr mare de valori, viteza va încetini în mod semnificativ.

Opțiunea 3 - Utilizarea SUMIF

Cred că această abordare este cea mai convenabilă de toate au fost enumerate anterior. Folosind SUMIF (SUMIF) Valorile sunt împărțite la 2 matrice: cu numere pozitive și negative și însumate. Zero de înțeles ignorate. Apoi, vom deduce pur și simplu valoarea negativ pozitiv (adică le poate rezuma). Formula arată astfel:

Acesta poate fi scris în această formă:

Dacă luăm prima versiune a formulei, vom obține următoarele:

Pentru a completa imaginea, vom calcula valoarea folosind a doua opțiune: