Уявімо собі таблицю, в якій в рядках упереміш вказані назви відділів (або рахунків, або ще чогось).
Підсумовуємо осередки за критерієм
Необхідно обчислити загальну суму по кожному відділу. Багато хто робить це за допомогою фільтра і записи ручками в осередку.
Хоча зробити це можна легко і просто за допомогою всього однієї функції - СУММЕСЛИ.
СУММЕСЛИ (SUMIF) - Підсумовує осередки, що задовольняють заданій умові (умова можна задати тільки одне). Цю функцію так само можна застосувати, якщо таблиця розбита в шпальтах на періоди (помісячно, в кожному місяці по три стовпці - Дохід | Витрата | Різниця) і необхідно підрахувати загальну суму за всі періоди тільки за доходами, видатками і різниця.
Як це працює: функція шукає в Діапазоні значення, вказане аргументом Критерій. і при знаходженні збіги підсумовує дані, зазначені аргументом Діапазон_Суммірованія. Тобто якщо у нас в стовпці А назва відділу, а в стовпці В суми, то вказавши в якості критерію "Відділ розвитку" результатом функції буде сума всіх значень стовпця В, навпроти яких у стовпці А зустрічається "Відділ розвитку". Фактично Діапазон_Суммірованія може не збігатися за розміром з аргументом Діапазон і помилки самої функції це не викличе. Однак при визначенні осередків для підсумовування, в якості початкової комірки для підсумовування буде використана верхня ліва комірка аргументу Діапазон_Суммірованія. а потім сумуються осередки, відповідні за розміром і формою аргументу Діапазон.
Особливість вказівки аргументів: спочатку вказується діапазон критерію (вони пронумеровані) потім через точку-з-коми вказується безпосередньо значення (критерій), яке в цьому діапазоні необхідно знайти - $ A $ 2: $ A $ 50; $ I $ 3. І ніяк інакше. Не варто намагатися спочатку вказати всі діапазони, а потім критерії до них - функція видасть або помилку, або підсумує не те, що треба.
Всі умови порівнюються за принципом І. Це означає, що якщо всі перераховані умови виконуються. Якщо хоч одна умова не виконується - функція пропускає рядок і нічого не підсумовує.
Так само як і для СУММЕСЛИ діапазони підсумовування і критеріїв повинні бути рівні за кількістю рядків.
Розберемо логіку, тому що багатьом вона буде зовсім не ясна просто при погляді на цю функцію. Хоча б тому, що в довідці подібне її застосування не описується. Для більшої Новомосковскбельності зменшимо розміри діапазонів:
= СУММПРОИЗВ (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Отже, вираз ($ A $ 2: $ A $ 5 = $ I $ 3) і ($ B $ 2: $ B $ 5 = H5) є логічними і повертають масиви логічних БРЕХНЯ і ІСТИНА. ІСТИНА, якщо осередок діапазону $ A $ 2: $ A $ 5 дорівнює значенню комірки $ I $ 3 і осередок діапазону $ B $ 2: $ B $ 5 дорівнює значенню комірки H5. Тобто виходить у нас наступне:
= СУММПРОИЗВ (*; $ C $ 2: $ C $ 50)
Як видно, в першому масиві два збіги умові, а в другому одне. Далі ці два масиви перемножуються (за це відповідає знак множення (*)). При перемноження відбувається неявне перетворення масивів БРЕХНЯ і ІСТИНА в числові константи 0 і 1 відповідно (*). Як відомо, при множенні на нуль отримуємо нуль. І в результаті виходить один масив:
= СУММПРОИЗВ (; $ C $ 2: $ C $ 50)
Далі відбувається вже множення масиву на масив чисел в діапазоні $ C $ 2: $ C $ 50:
= СУММПРОИЗВ (;)
І як результат отримуємо 30. Що нам і потрібно - ми отримуємо лише ту суму, яка відповідає критерію. Якщо сум, що задовольняють критерію буде більше однієї, то вони будуть підсумовані.
перевага СУММІРОІЗВ
Якщо у аргументів замість знака множення вказати знак плюс:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
то умови будуть порівнюватися за принципом АБО: тобто підсумовуватися підсумкові суми будуть в разі, якщо хоча б одна умова виконується: або $ A $ 2: $ A $ 5 дорівнює значенню комірки $ I $ 3 або осередок діапазону $ B $ 2: $ B $ 5 дорівнює значенню комірки H5.
У цьому перевага СУММПРОИЗВ перед СУММЕСЛІМН. СУММЕСЛІМН не може підсумовувати значення за принципом АБО, тільки за принципом І (всі умови повинні виконуватися).
недоліки
У СУММПРОИЗВ неможливо використовувати символи підстановки * і. Точніше використовувати можна, але вони будуть сприйняті не як спец.символов, а як безпосередньо зірочка і вопр.знак. Я вважаю це суттєвим мінусом. І хоча це можна обійти, використовую всередині СУММПРОИЗВ інші функції - все ж було б чудово, якби функція якимось чином могла використовувати символи підстановки.
У прикладі знайдете пару прикладів функцій для більш кращого розуміння написаного вище.