Итоги вместе с автофильтром
Уважаемые сотрудники редакции! В программе Excel есть очень удобный инструмент обработки баз данных — «
Итоги». На мой взгляд, для бухгалтера он просто незаменим! Но к сожалению, при работе с этим инструментом есть одно неудобство. Дело в том, что Excel заполняет в строках итогов только те ячейки, по которым производились вычисления. Остальные позиции она оставляет пустыми. В результате после обработки базы данных «итогами» мы получаем какую-то нерегулярную таблицу. Если к такой таблице применить, скажем, автофильтр, то часть записей он будет выбирать неправильно. Для меня, например, это серьезный недостаток. Подскажите, есть ли способ решить такую проблему? Заранее благодарю.В. Крамаренко, главный бухгалтер, г. Харьков
Отвечает
Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Думаю, прежде чем перейти к решению проблемы, имеет смысл наглядно показать, в чем собственно она заключается. Я сделаю это на примере базы данных, изображенной на рис. 1. В ней собраны операции по кассе, полученные отчетом по проводкам из программы «
1С:Бухгалтерия». В базе есть такие поля: дата проводки, счет дебета и кредита (колонки «СчД» и «СчК»), сумма по дебету и кредиту (колонки «Д» и «К»), а также название контрагента. Предположим, я хочу посчитать суммарный оборот по кассе за каждый день, используя режим Excel «Итоги». В этом случае делаем так:1. Открываем документ, ставим указатель в область базы данных.
2. Вызываем «
Данные → Сортировка…». Появится окно настройки параметров сортировки, как на рис. 2. 
3. В нем щелкаем на поле «
Сортировать по», из раскрывшегося списка выбираем элемент «Дата». Здесь все понятно: мы будем подбивать итоги по датам, поэтому таблицу нужно предварительно отсортировать в хронологическом порядке.4. В окне «
Сортировка диапазона» нажимаем «ОК». Excel отсортирует таблицу по возрастанию дат.5. Вызываем меню «
Данные → Итоги…». Появится окно настроек, как на рис. 3. 
6. В этом окне щелкаем на поле «При каждом изменении в:», из раскрывшегося списка выбираем «Дата».
7. Щелкаем на элементе «Операция:», выбираем значение «Сумма».
8. В окне «Добавить итоги по:» ставим флажки возле элементов «Д» и «К». Это единственные поля базы данных, которые имеет смысл суммировать…
9. В окне «Промежуточные итоги» нажимаем «ОК». Результат таблицы с итогами показан на рис. 4.
Теперь попробуем применить к полученной таблице автофильтр и посмотрим, что у нас получится. Поступаем следующим образом:
1. Удерживая левую кнопку мыши, выделяем колонки «A:F».
2. Вызываем меню «Данные → Фильтр → Автофильтр». В заголовках базы появятся значки для выбора значений.
3. Щелкаем на значке в колонке «Дата», из раскрывшегося списка выбираем «(условие…)». Откроется окно «Пользовательский автофильтр».
4. В окне настроек автофильтра заполняем параметры, как показано на рис. 5: в качестве операции выбираем «меньше», в поле значения вводим дату «03/03/11». Иными словами, мы выбираем из базы с итогами данные о проводках по кассе за «01/03/11» и «02/03/11».
5. В окне «Пользовательский автофильтр» нажимаем «ОК». Результат работы автофильтра показан на рис. 6.
Excel обработала таблицу, но строки с итогами теперь стали не видны. Взгляните на строку «7» листа «Касса» (рис. 6). После нее сразу идет строка номер «9».
Причина кроется в том, как Excel оформил записи с результатами расчетов. Вернемся к базе данных на рис. 4 и посмотрим на значения в строке номер «8». В ячейке «A8» записан текст «01.03.11 Итог». Это именно текст, а не дата — обычный комментарий, который Excel внедрил в таблицу при подведении итогов. Когда мы наложили на таблицу фильтр и отобрали конкретные даты, строки с таким комментарием Excel, разумеется, показывать не будет. То есть в такой ситуации (после подведения итогов!) мы не можем отобрать часть записей из базы за определенный период и вместе с ними видеть промежуточные итоги.
Чтобы разобраться с такой ситуацией, можно вставить в базу рабочую колонку, заполнить ее нужными значениями и затем использовать их при фильтрации данных. Вот как это сделать для нашего примера:
1. Открываем исходную базу данных, как на рис. 1.
2. Слева от таблицы добавляем столбец.
3. Формируем для нового столбца заголовок. Например, в ячейку «A1» пишем текст «Дата_».
4. В ячейку «A2» вводим формулу «=B2».
5. Копируем эту формулу на всю высоту таблицы. Мы создали вспомогательный столбец, содержимое которого в точности повторяет колонку «Дата».
6. Оставаясь внутри базы данных, вызываем «Данные → Итоги…». Появится окно настроек, заполняем его параметрами, как показано на рис. 3.
7. В окне «Промежуточные итоги» нажимаем «ОК». Получаем таблицу, как на рис. 7. В ней появилась дополнительная колонка с датами проводок (она называется «Дата_»). В тех строках, где Excel внедрил итоги, ячейки в колонке «Дата_» остались пустыми. Теперь наша задача — заполнить эти ячейки конкретными значениями, а именно в каждую пустую ячейку колонки «Дата_» мы должны скопировать значение из расположенной выше ячейки. Например, в «A8» нужно ввести значение даты из «A7», в «A18» — значение из ячейки «A17» и т. д. Это удобно сделать формулой. Для этого в «A8» достаточно написать выражение «=A7», в ячейку «A18» ввести выражение «=A17» и таким образом откорректировать всю таблицу. Мы не будем заполнять эти ячейку вручную, а воспользуемся массивом формул. Делаем так:
1. Щелкаем на заголовке колонки «A» (выделяем ее).
2. Нажимаем «Ctrl+G» (или вызываем «Правка → перейти…»). Появится окно, как на рис. 8.
3. В этом окне нажимаем кнопку «Выделить». Excel откроет окно «Выделение группы ячеек», как на рис. 9.
4. В нем переключатель «Выделить» ставим в положение «пустые ячейки».
5. В окне «Выделение группы ячеек» нажимаем «ОК». Все незаполненные ячейки в пределах колонки «А» будут выделены в один несвязный блок. При этом активной окажется первая ячейка этого блока (в нашем примере это «A8»).
6. Не снимая выделения (!), вводим символ «=» и щелкаем на ячейке «A7». Тем самым в «A8» мы записали формулу «=A7».
7. Нажимаем комбинацию «Ctrl+Enter». Все пустые ячейки в колонке «A» будут заполнены ссылками на предыдущую ячейку. В результате в «A18» появится формула «=A17», в «A40» — формула «=A39» и т. д.
8. Выделяем всю базу данных и вызываем «Данные → Фильтр → Автофильтр».
9. Щелкаем на значке в колонке «Дата», из раскрывшегося списка выбираем «(условие…)» и в окне «Пользовательский автофильтр» заполняем параметры, как показано на рис. 5.
10. В окне «Пользовательский автофильтр» нажимаем «ОК». Результат работы автофильтра показан на рис. 10.
Задача решена. Теперь фильтр обрабатывает все строки базы данных, включая записи с итогами (рис. 10).
И последнее. Мы заполнили значениями ячейки только в колонке «А» (где продублировали даты проводок). В нашем случае переносить в строку итогов значения из других полей базы данных просто не имело смысла. На практике бывают и другие ситуации, когда в строке итогов нужно продублировать значения по нескольким колонкам. Чтобы решить такую задачу, вам даже не придется заполнять каждый столбец в отдельности — все можно сделать за один прием! Для этого, удерживая клавишу «Ctrl», выделите несколько колонок, и сформируйте массив формул.
Удачной работы! Жду ваших вопросов, замечаний и предложений на
bk@id.factor.ua , nictomkar@rambler.ru .
