Сумиране на абсолютните стойности (в абсолютна стойност), за да се отличат

Мой колега веднъж ме попита как да използвате Excel формули за изчисляване на сумата от абсолютните стойности на посочения диапазон. Този въпрос идва съвсем редовно във форумите, много потребители често имат големи затруднения с това на пръв поглед прост действие.

За съжаление, в Microsoft Excel, няма вградена функция, способна да изпълнява модул, така че трябва да се работи по-малко, за да получите правилния отговор.

Тук са нашите констатации:

Виждаме, че сборът от числата в диапазона от А2: A8 -60 дава резултат:

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

Ако се счита за абсолютните стойности (неподписана "-"), резултатът ще бъде 180:

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

Вариант 1 - Използване на спомагателната колона

По мое мнение, най-добрият начин да брои сумата от абсолютните стойности на Excel - използвайте допълнителна колона. В клетка В2 въведете формулата:

След това я плъзнете клетка B8. Функцията ABS връща броя на модула. Така че сега ние можем просто да обобщим клетки B2: B8, и това ще ни даде резултат 180.

В моя пример, гама A1: A8 - пълна таблица за данни. Следователно, чрез прибавяне на формула = ABS (А2) в клетка В2. Excel разширява маса и автоматично попълва всички клетки в колоната. Следваща се преместих в раздела Design (Дизайн), който е в група от раздели Работа с таблици (инструменти Table) и поставете отметка до опцията за резултатите от низ (Total Row). Всички стойности са в колона Б се сумират автоматично и резултатът се показва в отделен ред.

За изчисляване на количеството използван общо при междинната сума за функция (SUBTOTAL). Тя е универсална функция, която може да извършва сумиране, както и функцията на сумата (SUM). Но има и съществени разлики, като например SUBTOTAL (SUBTOTAL) напълно пренебрегва числата, които са скрити на ръка или чрез филтрация. Има няколко разлики, но това е малко по-вярно с темата на тази статия.

Метод с допълнителна колона е добро, което дава по-голяма гъвкавост, ако данните, които се използват в бъдеще, като маса или обобщена. В допълнение, спомагателни колоната може да се използва за сортиране числа модул.

Несъмнено това е един много добър начин, но това, което правите, когато искате да се поберат всичко в една формула, без носещи колони?

Вариант 2 - Използване SUM във формула масив или SUMPRODUCT

Използвайте формула за масив или SUMPRODUCT (SUMPRODUCT), за да се реши този проблем - много груб подход!

SUM функция във формула масив:

Когато въвеждате формули за масиви не забравяйте да натиснете Ctrl + Shift + Enter.

Формула с SUMMPROZV:

Като се има предвид, че е възможно да се използва по-ефективно функция SUMIF (SUMIF), за да се получи същия резултат (вж. 3 вариант), пътя с използване на тези две формули става недостатъка. И двете формули работят добре с малки области с данни, разбира се, дори не усетите разликата. Ако трябва да обобщим голям брой стойности, скоростта ще се забави значително.

Вариант 3 - Използване SUMIF

Мисля, че този подход е най-удобно на всички изредени преди. Използване SUMIF (SUMIF) стойности са разделени от два масива: с положителни и отрицателни числа и сумират. Нулева разбираемо игнорирани. Тогава ние просто ще приспадне сумата на положителните отрицателен (т.е. да ги обобщим). Формулата изглежда така:

Тя може да се запише в тази форма:

Ако вземем първата версия на формулата, получаваме следното:

За да бъде картината пълна, ние изчисляване на размера с помощта на втората опция: