buhgalter.com.ua buhgalter.com.ua
21.01.13
Шановні співробітники «Б & К»! У мене виникла така проблема. Є таблиця Excel, яка представлена в форматі бази даних. Одна з колонок цієї таблиці містить дати. Мені потрібно в якихось клітинках аркуша записати дві змінні - початкову і кінцеву дати, а потім за допомогою формули порахувати суму значень в базі для зазначеного діапазону. Тобто завдання полягає в тому, щоб підсумувати не фіксували, а змінний діапазон комірок. Я думаю, це питання буде цікавий багатьом читачам вашого видання. По крайней мере, мені можливості підсумувати змінюються діапазони значень в MS Excel часто не вистачає.
У програмі Excel підсумовування діапазонів з «плаваючими» кордонами - окремий випадок підсумовування за умовою. Про способи вирішення такого завдання ми не раз писали на сторінках нашого видання (див. «Б & К», 2012, № 24). Тому детально розбиратися з усіма можливими прийомами я не буду. Просто покажу на одному конкретному прикладі, як впоратися з проблемою. Отже, на малюнку показаний фрагмент бази даних, де зібрані касові операції за березень 2012 року. Спочатку в базі було всього шість полів: дата, рахунок дебету і рахунок кредиту, далі в колонках «Д» і «К» показані обороти по дебету і кредиту за поточну дату. Останній стовпець (він називається «Контрагент») містить текст, в розрахунках ми його використовувати не будемо. База відсортована по колонці «Дата». Перед таблицею додані дві робочі рядки - сюди ми будемо вводити формули.
Тепер конкретно по нашому завданні. В осередку «E1» записано дату (на малюнку це «02/03/2012»). Ми повинні в осередок «I1» внести формулу, яка підсумує все обороти по дебету до «02/03/2012» включно. Зрозуміло, якщо дату в «E1» змінити, формула повинна порахувати суму для нового діапазону комірок.
Спосіб 1. Працюємо з функцією «СУММЕСЛИ ()»
Це найпростіший спосіб вирішення завдання. Щоб ним скористатися, ми створимо додаткову робочу колонку. У цю колонку напишемо формулу для визначення умови підсумовування записів, а потім вкажемо цю колонку в якості одного з параметрів функції «СУММЕСЛИ ()». Робимо так:
1. У осередок «G3» вводимо заголовок робочої колонки. На малюнку це текст «Пр».
2. У осередок «E1» вводимо дату (на малюнку це значення «02.03.2012»).
3. У осередок «G4» вводимо формулу «= A4 <= $ E $ 1».
4. Копіюємо формулу на всю висоту таблиці.
5. У осередок «F1» вводимо формулу «= СУММЕСЛИ (G: G; ІСТИНА; D: D)» і натискаємо «Enter». Відразу ж бачимо результат: дебетовийоборот від початку місяця і до «02.03.2012» включно склав 3548,93 грн.
Короткий коментар з приводу формули. Перший параметр (блок «G: G») - це діапазон таблиці, де записано ознака для підсумовування осередків. Ця ознака ми проставили за допомогою формули. Ті осередки, які повинні потрапити в загальну суму, відзначені значенням «ІСТИНА». Саме цей факт ми відбили у другому параметрі функції «= СУММЕСЛИ ()». І тепер в процесі роботи функція буде поелементно (!) Порівнювати вміст кожного осередку в колонці «G» зі значенням «ІСТИНА». Там, де умова справедливо, «= СУММЕСЛИ ()» буде накопичувати суму. А значення для підсумовування вона візьме з колонки «D» (обороти за дебетом), яка вказана в якості третьої параметра функції.
Трохи ускладнити завдання і подивимося, як можна підсумувати фрагмент бази даних, обмежений двома датами. Початкова дата у нас буде як і раніше в осередку «E1». Кінцеву дату ми запишемо в осередок «E2» (на малюнку це «05/03/2012»). Принцип роботи залишиться тим же - зміниться лише умова в робочій колонці. Тепер формула в комірці «G4» буде такою: «= І (A4> = $ E $ 1; A4 <= $ E $ 2)». Копіюємо цю формулу на всю висоту таблиці. Після чого та ж сама формула ( «= СУММЕСЛИ (G: G; ІСТИНА; D: D)») вважатиме сумарний оборот по дебету за період від «02/03/2012» до «05/03/2012» включно. Ясна річ, що змінивши умова, можна вибірково підсумувати дані по будь-яким найскладнішим логічним правилам.
Спосіб 2. Використовуємо функцію «СУММПРОИЗВ ()»
Детально про роботу з цією чудовою функцією можна почитати, наприклад, в «Б & К», 2012, № 24. Стосовно нашого завдання ця функція дозволяє отримати більш витончене рішення, при якому взагалі не знадобляться робочі колонки, а всі умови будуть вказані прямо в тексті функції. Повернемося до ситуації, коли ми підсумували фрагмент бази даних до вказаної дати (це значення було записано в комірці «E1»). У цьому випадку формула для підсумовування може виглядати так: «= СУММПРОИЗВ ((A4: A65536 <= E1) * D4: D65536)». Результат роботи формули: 3548,93 грн.
Важливо! Вказувати в якості блоку для підсумовування адресу всієї колонки або рядка (наприклад, замість «A: A65535» написати «A: A») в функції «= СУММПРОИЗВ ()» можна.
Для випадку з двома обмеженнями (початкова та кінцева дати) формула для підсумовування буде такою: «= СУММПРОИЗВ ((A4: A65536> = $ E $ 1) * (A4: A65536 <= $ E $ 2) * (D4: D65536))» . Як бачите, в цьому виразі функцію «І ()» замінює операція множення. Іншими словами, фрагмент формули «(A4: A65536> = $ E $ 1) * (A4: A65536 <= $ E $ 2)» поверне результат «ІСТИНА» для тих осередків колонки «А», які потрапляють в заданий інтервал. В іншому випадку результат формули буде дорівнює «БРЕХНЯ». При перемножуванні числових і логічних виразів результат «ІСТИНА» Excel вважає рівним «1». А в підсумку функція примножить на «1» все елементи з колонки «D», які потрапляють всередину заданого інтервалу, після чого підсумує отриманий результат.
І останнє. Виникає закономірне питання: а яке із запропонованих рішень все ж краще. Тут все залежить від завдання і особистих переваг. У першому способі можна використовувати дуже складні логічні вирази. Але доведеться використовувати додаткові колонки. При роботі з функцією «= СУММПРОИЗВ ()» складну логіку (наприклад, функцію «АБО ()») реалізувати не можна. Зате не потрібно ніяких колонок, формула працює сама по собі. На сьогодні все.
Вдалої роботи! Чекаю ваших запитань, зауважень і пропозицій на [email protected] , [email protected] або на форумі редакції www.bk.factor.ua/forum .
Помітілі помилку? Віділіть ее та натісніть Ctrl + Enter, щоб повідоміті нас про це
Помітілі помилку?
