Підсумки без «Підсумків»
Шановна редакціє! Допоможіть вирішити таку проблему. Є база даних у форматі MS Excel. З неї потрібно побудувати лист з підсумковими значеннями. Ці значення повинні перераховуватися при кожній зміні в базі даних. Але режим «Підсумки» для вирішення завдання використовувати не можна, оскільки база повинна залишатися в первісному варіанті. Що можна зробити в такій ситуації? Наперед завдячую.
І. К. Подчасов, м. Харків
Відповідає Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
Для вирішення описаного завдання я б порадив звернутися до зведених таблиць. Але заважає умова: «…значення повинні перераховуватися при кожній зміні в базі даних...». Зведені таблиці не оновлюються автоматично, тому єдиним способом вирішення завдання залишається формула-масив. Як це зробити, я покажу на прикладі бази касових операцій, фрагмент якої наведено на рис. 1. Для цієї таблиці ми визначимо дебетовий та кредитовий обороти за всіма рахунками за кожну дату. Причому зробимо це за допомогою формул. Ключовою проблемою для вирішення завдання буде формула, яка проаналізує зміст колонки «
Дата», і в окремому стовпці сформує список відповідних значень. Якщо ми зробимо це, визначити сумарні обороти за допомогою функції Суммесли() вже не складе труднощів. Робимо так: 
1) відкриваємо базу даних, сортуємо її за ключовим полем «Дата»;
2) через меню «Вставка → Лист» додаємо до таблиці новий лист. У прикладі це «Итоги»;
3) на цьому листі комірку «A1» залишаємо порожньою. До комірки «B1» записуємо заголовок «ДО» (дебетовий оборот), до комірки «С1» уводимо «КО» (кредитовий оборот);
4) до комірки «A2» друкуємо формулу: «=Индекс(Касса!$A$1:$A$230;Наименьший(Если (Счетесли($A$1:A1;Касса!$A$1:$A$230)=0;Строка(Касса!$A$1:$A$230));Строка(Касса!$A$2:$A$230)))». У ній адреса «$A$230» вказує на останній рядок у вихідній базі даних (там усього 229 записів). Клавішу « Enter» не чіпаємо!
5) натискуємо на комбінацію «
Ctrl+Shift+Enter». Excel перетворить формулу на масив. Якщо тепер клацнути по комірці «A2», у рядку формул ми побачимо: « {=Индекс(Касса!$A$1:$A$230; Найменший(Если(Счетесли($A$1:A1;Касса!$A$1:$A$ 230)=0;Строка(Касса!$A$1:$A$230)); Строка(Касса!$A$2:$A$230)))} »;6) натискуємо на «
Ctrl+С», копіюємо формулу в буфер обміну;7) виділяємо блок від «
A3» до «A32» (розмір цього блока залежить від кількості дат у базі даних);8) натискуємо «
Ctrl+V», вставляємо дані з буфера. У колонці «A» з’явиться список усіх дат, які є в базі на листі «Касса» (рис. 2); 
9) до комірки «B2» уводимо «=Суммесли(Касса!$A$1: $A$230;A2; Касса!$D$1:$D$230)». До комірки «С2» — формулу «=СУММЕСЛИ(Касса!$A$1:$A$230; A2;Касса!$E$1:$E$230)». Ці формули підраховують дебетовий та кредитовий обороти за дату, яка записана в комірці «A2»;
10) копіюємо формули з «B2» і «С2» на всю висоту таблиці (у прикладі — це блок «B3:С32»), і готово. Остаточний варіант підсумкової таблиці наведено на рис. 2.
Чекаю на ваші запитання, зауваження та пропозиції на
bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!
