Сумиране на несвързани диапазони

За по-лесно разбиране ще разгледаме всички примери за формули въз основа на най-простия икономически проблем: изчисляване на общия приход за известни стойности на обема и продажните цени за няколко купувача.

Проблемът предполага, че броят на купувачите и периодите не е постоянен. Искате да получите общи суми за продажби и приходи. Очевидно прилагането на проста функция за сумиране (SUM) няма да работи дори за получаване на общи суми. По същия начин използването на функцията SUMPRODUCT „на челото“ не е подходящо за изчисляване на приходите, тъй като диапазоните на клетките на множителя не са свързани помежду си.

На практика решаването на такива проблеми често се свежда до писане на "некопируеми" формули или изисква преобразуване на крайните формули след добавяне на данни (виж лист Пример 1, клетки C10, C11). Този метод е валиден само за малки количества данни и нарушава основните принципи за оптимизиране на работата в електронни таблици.

Вторият стандартен метод за решаване на проблема е създаването на допълнителни таблици с междинни изчисления.

Този метод ви позволява да прилагате същите (копирани) формули в получените клетки и също така не изисква промяна на крайните формули при добавяне на редове или колони с данни. Тези две условия осигуряват максимална надеждност на изчисленията по време на разработването и функционирането на икономическия модел.

Моля, обърнете внимание, че проблемът с коректността на общите формули при добавяне на редове се решава чрез добавяне на празен служебен ред преди общия ред (лист Пример2, редове 6 и 22). Без празен низ, когато в края на масива се добави нов клиент, функцията SUM не събира автоматично новите данни. Сервизните низове са стандартни"безопасен" трик при работа с променлив брой редове от данни. Ако желаете, те могат да бъдат скрити с помощта на стандартните методи на интерфейса на Excel.

Недостатъците на метода за решаване на проблема чрез добавяне на междинни изчисления включват:

Допълнителни трансформации на изходните данни, което често е трудно приложимо на практика (представете си, че в изходния файл има не 3, а 3000 купувача).
Сложността на добавянето на информация - нов купувач трябва да се добави не към една, а към три таблици.

Нека се опитаме да решим проблема, без да използваме междинни таблици, но запазвайки възможността за копиране и коректността на крайните формули при добавяне на купувачи и периоди.

Както беше отбелязано по-горе, формулите в клетки C10:C11 решават проблема правилно, но усложняват работата с модела. Просто искам да обърна внимание на използването на функцията SUM с допълнителни изчисления в клетка C11.

Останалите формули в диапазона D10:E11 също са правилни и в същото време напълно отговарят на изискванията на задачата. Нека ги разгледаме по-подробно.

Общ обем, кг: D10

Формулата е много проста и очевидна за разбиране. Сумирането се извършва според условието, фиксирано в клетка $B$3. Вместо $B$3, можете да напишете "обем, kg" директно във функцията. От една страна, това прави формулата по-четлива, но от друга страна е по-малко безопасна, тъй като при промяна на стойността в заглавните клетки, например от „обем, kg“ на „обем, l“, функцията няма да работи правилно.

Трябва също да се отбележи, че възможността за използване на SUMIF често е ограничена поради допустимостта на проверка само с едно условие (само в Excel 2007 се появи функцията SUMIFS). В сложни случаи формулите с обработка на масиви са незаменими.

Общ обем, кг: E10

Идеяформули - изчисляване на сумата само в нечетни редове с данни. Формулата работи с клетъчния масив E3:E9 (атрибутът са фигурните скоби). Нечетните редове се дефинират тук като остатък, равен на 1 при разделяне на поредния номер на реда от диапазона на 2. Освен това, за да се запази целостта, числата се броят от заглавката на таблицата ROW (E2). Това ви позволява да прехвърлите цялата таблица на друго място в текущия или друг лист на модела.

Формулите за обработка на масиви се прилагат чрез натискане на Ctrl+Shift+Enter

Общ приход, rub: D11

Една от нестандартните употреби на функцията SUMPRODUCT. Идеята е да се умножат три масива от числа. Първият масив е обеми, вторият е цени, а третият е тригер, който умножава ненужните данни по нула.

Нека трансформираме формулата, като изберете текста в скоби и натиснете F9, получаваме: