Итоги без «Итогов»

Итоги без «Итогов»

Уважаемая редакция! Помогите решить такую проблему. Есть база данных в формате 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.

Удачной работы! Жду ваших вопросов, замечаний и предложений на