Работа с данными в Excel 2007: режим «Итоги»
 | В бухгалтерской практике базы данных (табличные массивы) далеко не всегда имеют регулярную структуру, когда можно написать формулу, а затем скопировать ее. Как раз наоборот: в реальной жизни регулярные таблицы бухгалтеру встречаются нечасто. Возьмем хотя бы главную книгу. Количество операций за каждый день в ней разное. Чтобы определить, например, обороты по определенной группе проводок за каждый день, придется в формулах вручную проставлять диапазон суммирования для каждой даты! Что и говорить, перспектива не радужная… Если не использовать режим подведения итогов. Для бухгалтера работа с итогами очень важна. Поэтому мы и решили выделить эту тему в отдельную статью и посмотреть: что делают итоги, каковы их преимущества и недостатки, чем конкретно может быть полезен режим «Итоги» практикующему бухгалтеру. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Режим подведения итогов, как и автофильтр, предназначен для обработки больших баз данных. Но между этими инструментами есть принципиальное отличие: автофильтр не изменяет исходную базу данных, он только анализирует ее записи и показывает (или скрывает) их на экране. Что касается режима «Итоги», то он корректирует базу — вставляет в нее расчетные формулы и группирует строки, создавая так называемую структуру. Но это еще не все. Автофильтр — инструмент самодостаточный. Его можно применить к базе данных без ее предварительной обработки. А вот работа с итогами подразумевает, что база будет предварительно отсортирована. Поэтому, если вы подзабыли приемы сортировки данных, освежите их в памяти, прежде чем читать эту статью.
Алгоритм работы режима «Итоги»
Для работы в режиме «
Итоги» вы должны указать, как минимум, три вещи: ключевое поле, перечень полей для обработки и тип операции для выполнения вычислений. В режиме подведения итогов Excel анализирует ключевое поле. Как только значение этого поля изменится, он вставляет в базу новую строку. В этой строке он пишет комментарий и внедряет расчетные формулы для тех полей, которые указаны в перечне «для обработки». А характер вычислений в этих формулах режим «Итоги» установит исходя из заданного вами типа операции. Поясню это на конкретном примере. У вас есть, скажем, база бухгалтерских проводок. В одной из колонок этой базы записана дата проводки. По этим данным вы хотите посчитать дебетовый и кредитовый обороты за каждую дату. Понятно, что, работая вручную, вы будете действовать так. Последовательно просматривая базу, найдете запись, где дата проводки изменит свое значение. В этом месте добавите новую строку. В этой строке напишете формулы суммирования значений в колонках дебета и кредита для выделенного блока дат и затем повторите эти действия для всей базы данных, потратив при этом массу времени и сил. Режим «Итоги» сделает эту работу за считанные секунды.Важное замечание. Обратите внимание на фразу: «Изменит свое значение». Мы употребили ее применительно к ключевому полю в базе проводок. Она означает, что перед использованием режима «
Итоги» база по этому полю обязательно должна быть отсортирована! Иначе ничего не получится, расчет будет неверным. То есть сам по себе инструмент отработает, но что мы увидим в конечном счете? Применительно к базе проводок это будет выглядеть так. Вначале Excel найдет группу проводок за «01/01/2010». Как только дата изменится и станет «02/01/2010», он вставит строку и посчитает в ней сумму дебетового и кредитового оборота за «01/01/2010». Дальше Excel обработает данные за «02/01/2010» и посчитает соответствующие итоги. Затем обработает «03/01/2010» и т. д. Но в какой-то момент снова встретит проводки за «01/01/2010». Для этих проводок он тоже посчитает сумму, и в результате итоговый оборот за «01/01/2010» будет разбросан по базе в виде нескольких значений. Причем количество этих значений будет зависеть от того, сколько групп проводок за «01/01/2010» обнаружит Excel в разных местах базы данных. Но стоит базу отсортировать по дате, и Excel выстроит все проводки в хронологическом порядке. Записи, относящиеся к одной дате, будут расположены подряд. Соответственно для них Excel определит только одно итоговое значение, и все отработает правильно.
Как вызвать режим «Итоги»
Работа с итогами Excel 2007 сосредоточена в меню «
Данные». Но прежде чем обратиться к этому инструменту, нам понадобится какой-то объект. В качестве такого объекта я выбрал базу данных, изображенную на рис. 1. В ней собраны подробные сведения о реализации товаров в количественном и стоимостном выражении. В первой колонке таблицы находится дата операции. Затем следует номер документа, которым зарегистрирована операция (поле «Н-Док»). В колонке «Покупатель» записано название контрагента, купившего товар. Столбец «Наименование» заполнен названиями товаров. Далее расположены сведения о количестве, цене реализации и сумме соответствующей операции. Со структурой базы мы разобрались. Теперь делаем так:1) загружаем Excel, открываем файл, изображенный на рис 1;
2) вызываем меню «
Данные»;3) находим группу «
Структура» (рис. 2); 
4) щелкаем на иконке «Промежуточные итоги». Откроется окно, как на рис. 3.
В этом окне есть четыре группы параметров. Вот вкратце их назначение:
«При каждом изменении в:» — здесь нужно указать ключевое поле (или так называемое поле группировки). Вернемся к базе на рис. 1. Если я хочу определить итоговые значения по объемам продаж за день, ключевым полем будет выступать колонка «Дата». Содержимое ячеек в этой колонке Excel и будет просматривать на предмет изменения их значений. Параметр «При каждом изменении в:» оформлен в виде списка. Значениями этого списка являются все доступные поля базы данных Excel.
«Операция:» — это тоже список, в нем перечислены все возможные типы операций над полями при подведении итогов. Это может быть суммирование, подсчет количества значений, вычисление среднего, определение максимума, минимума или произведения.
«Добавить итоги по:» — этот параметр определяет, по каким из полей базы данных Excel должен посчитать итоги. Параметр оформлен в виде небольшого окна, в котором показан список всех полей базы данных. Слева от каждого названия есть небольшой квадратик. Щелчок мыши на квадратике делает его активным. В этом случае внутри квадратика появится галочка. На рис. 3 активными полями для итогов являются «Кол» и «Сумма». Это означает, что формулы для определения итогов Excel поставит именно в этих колонках.
«Заменить текущие итоги» — когда параметр включен, каждое новое определение итогов заменяет предыдущие значения. Если флажок отключить, то новые итоги Excel добавит к предыдущим. Таким образом, появляется возможность создать таблицу с многоуровневыми итогами.
«Конец страницы между группами» — параметр играет не столько функциональную, сколько оформительскую роль. Если его включить, то при каждом изменении в значении ключевого поля на лист Excel будет вставлен символ начала новой страницы. Таким образом, каждая группа итоговых значений будет напечатана с нового листа. На практике этот режим используют редко.
«Итоги под данными» — определяет, в каком месте Excel должен добавить строку с итоговыми значениями. Если флажок включен, в результирующей таблице вначале идут значения, а затем строка с итогами (так принято по умолчанию). Если флажок отключить, то в результирующем отчете Excel вначале расположит итоги, а под ними будут находиться данные.
Внизу окна расположены три кнопки:
— «Убрать все»: удаляет итоги из таблицы;
— «ОК»: инициирует подведение итогов;
— «Отмена»: отменяет выполнение операции по расчету итогов.
Примеры работы с итогами
Подробно детали работы с инструментом «
Итоги» мы рассмотрим на нескольких примерах. А заодно выясним, как «Итоги» сочетаются с другими способами обработки данных.
итоги по одному ключевому полю
Это самый простой способ использования итогов. Хотя на практике он, пожалуй, и самый распространенный… Работу в этом режиме мы рассмотрим на примере базы данных, изображенной на рис. 1. Наша задача — определить суммарный оборот по каждому контрагенту в стоимостном и количественном выражении. Делаем так:
1) открываем файл с базой данных;
2) делаем активной любую ячейку в колонке «
Дата»;3) вызываем меню «
Главная»;4) находим группу «
Редактирование» и щелкаем на иконке «Сортировка и фильтр»;5) из появившегося меню выбираем «
Сортировка от старых к новым». Записи базы данных будут упорядочены по возрастанию поля «Дата». В принципе активную ячейку мы могли бы поставить и вне колонки «Дата». Вполне достаточно, чтобы она просто находилась внутри базы данных. Но в этом случае нам пришлось бы обратиться к настраиваемой сортировке и явно указать ключевое поле;6) вызываем меню «
Данные»;7) находим группу «
Структура» и щелкаем на иконке «Промежуточные итоги» (рис. 2). Появится окно, как на рис. 3;8) щелчком мыши раскрываем список «
При каждом изменении в:», выбираем значение «Дата»;9) щелкаем на списке «
Операция:», выбираем значение «Сумма»;10) в списке «
Добавить итоги по:» ставим галочки возле полей «Кол» и «Сумма»;11) флажок «
Заменить текущие итоги» оставляем включенным;12) нажимаем «
ОК». Результат показан на рис. 4. 
После каждого изменения в поле «Дата» появилась дополнительная строка. Ключевым полем у нас была колонка «А». В этой колонке в каждой строке с итогами Excel поставил специальную запись — значение ключевого поля и слово «Итог». Например, для даты «03/02/2010» здесь будет стоять значение «03/02/2010 Итог». Дальше по строке (в колонках «Е» и «G») появились итоги. Первый итог по полю «Кол» (рис. 4 на с. 23) расположен в ячейке «E14», он равен 264 шт. Второй итог находится в ячейке «G14», он равен 464,8 грн. Это не что иное, как обороты в количественном и стоимостном выражении за дату «03/02/2010».
Слева от строк с итогами появились специальные значки. На рис. 4 они выглядят в виде квадратиков со знаком «-» (минус). Это символы управления структурой таблицы. От каждого прямоугольника в направлении вверх идет графический элемент, напоминающий прямоугольную скобку. Он показывает, какие строки в базе данных вошли в расчет текущего итога. Так, на рис. 4 в итоги за «03/02/2010» вошли строки рабочего листа со второй по тринадцатую.
Щелчок на квадратике с «минусом» сворачивает группу итогов в одну строку. Попробуйте щелкнуть левой кнопкой на квадратике, который расположен напротив строки «14». На экране останется только строка с итогом за эту дату. Записи базы данных начиная со второй по тринадцатую Excel уберет с экрана (сделает невидимыми).
В правом верхнем углу рабочего листа появились три новых элемента. Они выглядят как прямоугольники с надписями «1», «2» «3». Это кнопки сворачивания и разворачивания итогов по уровням группировки. С их помощью можно мгновенно изменить степень детализации итогового отчета. Делаем так:
1) щелкаем на прямоугольнике с цифрой «2». На экране остались только строки с итогами (номера «14», «32» «54» и т. д., рис. 5, вверху);
2) щелкаем на прямоугольнике с цифрой «1». На экране осталась единственная строка с общим итогом по всей базе данных (рис. 5, внизу).
Подытожим, что же мы получили. При настройке параметров итогов в качестве ключевого поля была указана колонка «Дата», поэтому в этой колонке Excel и поставил комментарии, продублировав значение ключевого поля вместе со словом «Итог». Остальные колонки (кроме тех, где есть вычисления) он оставил пустыми. Это не хорошо, и проблема проявилась при сворачивании итогов. Например, просматривая данные на втором уровне (рис. 5, вверху), мы не видим данных о покупателе, наименовании товара, номере документа. В нашем примере в этих данных смысла нет. Но бывают случаи, когда этот недостаток затрудняет работу с итоговой таблицей. Например, при работе с вложенными итогами. Как решить эту проблему, мы рассмотрим немного позже.
Теперь разберемся, как Excel определил итоги. Щелкаем на любом значении, где посчитан итог. Например, на ячейке «E14». В ней видим формулу «=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;E2:E13)». Это встроенная функция Excel, которую он применил для расчета итоговых значений в базе данных. Синтаксис функции такой: «ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции, Блок1, Блок2…)». Первый параметр определяет тип выполняемой операции. Далее следуют адреса блоков ячеек, над которым выполняется эта операция. Можно указать до 29 разных блоков. Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» многофункциональна. Перечень выполняемых ею операций указан в таблице.
| Номер функции | Эквивалентная функция Excel (тип операции) | |
| для обработки всех значений | для игнорирования скрытых значений | |
| 1 | 101 | СРЗНАЧ (среднее арифметическое) |
| 2 | 102 | СЧЕТ (количество чисел) |
| 3 | 103 | СЧЕТЗ (количество значений) |
| 4 | 104 | МАКС (максимум) |
| 5 | 105 | МИН (минимум) |
| 6 | 106 | ПРОИЗВЕД (произведение чисел) |
| 7 | 107 | СТАНДОТКЛОН (стандартное отклонение в выборке) |
| 8 | 108 | СТАНДОТКЛОНП (стандартное отклонение в генеральной совокупности) |
| 9 | 109 | СУММ (суммирование) |
| 10 | 110 | ДИСП (дисперсия по выборке) |
| 11 | 111 | ДИСПР (дисперсия по генеральной совокупности) |
Функцию «
ПРОМЕЖУТОЧНЫЕ.ИТОГИ» можно вставить на рабочий лист и обычным способом, например через мастер функций. Использовать режим «Итоги» для этого вовсе необязательно.Важным преимуществом функции «
ПРОМЕЖУТОЧНЫЕ.ИТОГИ» является возможность обрабатывать или исключать из обработки скрытые фрагменты рабочего листа.
Удаление итогов
Чтобы убрать итоги из базы данных, делаем так:
1) ставим активную ячейку в область базы данных;
2) вызываем меню «
Данные»;3) находим группу «
Структура» и щелкаем на иконке «Промежуточные итоги»;4) в окне «
Промежуточные итоги» (рис. 3) щелкаем на кнопке «Убрать все». База данных примет первоначальный вид, как на рис. 1.
Вложенные итоги
Вернемся к базе на рис. 1 и попробуем выяснить суммарный оборот по каждому отдельному контрагенту (в стоимостном и количественном выражении) за каждую дату. Для этого нам понадобится подвести итоги два раза. Вначале — по контрагенту (поле «
Покупатель»), затем — по дате. Естественно, что и отсортировать таблицу тоже придется дважды (по этим двум полям). Делаем так:1) открываем файл с базой данных;
2) ставим активную ячейку в область базы данных;
3) вызываем меню «
Главная»;4) находим группу «
Редактирование» и щелкаем на иконке «Сортировка и фильтр»;5) из появившегося меню выбираем «
Настраиваемая сортировка…»;6) в списке «
Сортировать по» выбираем значение «Покупатель»;7) параметр «
Сортировка» оставляем в положении «Значения»;8) в списке «
Порядок» указываем «От А до Я»;9) щелкаем на кнопке «
Добавить уровень». Появятся поля для заполнения еще одного условия сортировки;10) в списке «
Сортировать по» выбираем значение «Дата»;11) параметр «
Сортировка» оставляем в положении «Значения»;12) в списке «
Порядок» указываем «От старых к новым». В результате окно с параметрами сортировки должно выглядеть, как на рис. 6 на с. 25; 
13) нажимаем «ОК»;
14) вызываем меню «Данные»;
15) находим группу «Структура» и щелкаем на иконке «Промежуточные итоги» (рис. 2). Появится окно, как на рис. 3;
16) щелчком мыши раскрываем список «При каждом изменении в:», выбираем значение «Покупатель»;
17) щелкаем на списке «Операция:», выбираем значение «Сумма»;
18) в списке «Добавить итоги по:» ставим галочки возле полей «Кол» и «Сумма»;
19) флажок «Заменить текущие итоги» оставляем включенным;
20) нажимаем «ОК»;
21) опять вызываем меню «Данные» — нам нужно получить итоги еще по одному полю;
22) находим группу «Структура» и щелкаем на иконке «Промежуточные итоги». Появится окно, как на рис. 3;
23) щелчком мыши раскрываем список «При каждом изменении в:», выбираем значение «Дата»;
24) щелкаем на списке «Операция:», выбираем значение «Сумма»;
25) в списке «Добавить итоги по:» оставляем галочки возле полей «Кол» и «Сумма»;
26) флажок «Заменить текущие итоги» отключаем;
27) нажимаем «ОК». Результат показан на рис. 7.
В таблице появились два уровня итогов. В колонке «Дата» расположены итоги по дате, в колонке «Покупатель» — суммарные объемы реализации по каждому контрагенту.
Итоги плюс автофильтр
При работе с инструментами обработки данных особый интерес представляет их совместное использование. В данном случае речь идет о работе с итогами при одновременной фильтрации данных. В таком режиме вы можете получить итоги не по всей базе данных, а только для определенной ее части. Попробуем применить два упомянутых инструмента к таблице, изображенной на рис. 1. Наша цель — посчитать итоги реализации по датам выборочно для одного покупателя. Делаем так:
1) открываем файл с базой данных;
2) ставим активную ячейку в область базы данных;
3) вызываем меню «
Главная»;4) находим группу «
Редактирование» и щелкаем на иконке «Сортировка и фильтр»;5) из появившегося меню выбираем «
Фильтр». В заголовках базы появились значки автофильтра;6) щелкаем на значке выбора в колонке «
Покупатель», из раскрывшегося меню выбираем «Текстовые фильтры → Равно…». Появится окно «Пользовательский фильтр», изображенное на рис. 8; 
7) в качестве значения поля «Покупатель» выбираем «ООО “Коло”»;
8) нажимаем «ОК». На экране остались только данные по фирме «ООО “Коло”». По датам таблица отсортирована изначально, поэтому можно продолжить без предварительной сортировки базы;
9) вызываем меню «Данные»;
10) в группе «Структура» щелкаем на иконке «Промежуточные итоги»;
11) в окне «Промежуточные итоги» (рис. 3) раскрываем список «При каждом изменении в:»;
12) выбираем значение «Дата»;
13) в списке «Операция:» ставим значение «Сумма»;
14) в списке «Добавить итоги по:» ставим галочки возле полей «Кол» и «Сумма»;
15) включаем флажок «Заменить текущие итоги»;
16) нажимаем «ОК».
Перед нами результат практически такой же, как на рис. 7. Но есть одно отличие. Попробуем снять автофильтр. Делаем так:
1) щелкаем на значке выбора в поле «Покупатель»;
2) из раскрывшегося меню выбираем «Снять фильтр с “Покупатель”».
Мы отменили автофильтр. Теперь на экране появились все записи. Пролистав всю базу, мы увидим, что инструмент «Итоги» отработал выборочно. Итоговые значения посчитаны только для фирмы «ООО “Коло”».
Важно!
Режим «Итоги» работает с учетом автофильтра. Вы можете смело отобрать фрагмент базы данных, отсортировать его, внутри этого фрагмента подвести итоги — и все это будет работать правильно.Изменим наш эксперимент. Теперь мы вначале подведем итоги по всей базе, а потом применим автофильтр. Делаем так:
1) за основу берем таблицу, как на рис. 1. Открываем ее. Не забываем поставить указатель активной ячейки внутрь базы данных;
2) вызываем меню «Данные»;
3) в группе иконок «Структура» щелкаем на «Промежуточные итоги»;
4) в окне настройки итогов (рис. 3) раскрываем список «При каждом изменении в:»;
5) выбираем значение «Дата», в поле операции ставим значение «Сумма». В списке «Добавить итоги по:» оставляем галочки возле элементов «Кол» и «Сумма»;
6) включаем флажок «Заменить текущие итоги»;
7) нажимаем «ОК». В таблице появились итоги по каждой дате;
8) вызываем меню «Главная»;
9) находим группу «Редактирование», щелкаем на иконке «Сортировка и фильтр»;
10) из раскрывшегося меню выбираем «Фильтр». В заголовках базы станут доступны значки автофильтра;
11) щелкаем на значке в колонке «Покупатель», из предложенного меню выбираем «Текстовые фильтры → Равно…». Появится окно «Пользовательский фильтр», изображенное на рис. 8;
12) в качестве значения поля «Покупатель» выбираем «ООО “Коло”»;
13) нажимаем «ОК». На экране остались только данные по фирме «ООО “Коло”», а общий итог в поле «Сумма» по отфильтрованному фрагменту базы данных составил «5573,6».
Важно!
Несмотря на то что в таблице с итогами есть промежуточные значения, вы можете применить к ней автофильтр. Все значения будут посчитаны правильно, функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» при необходимости изменит свои значения и будет считать итоги только для видимого фрагмента базы данных.
Итоги, переход и заполнение ячеек
В практике бухгалтерских расчетов таблицу с итогами часто используют как заготовку для получения свернутого отчета из большой базы данных. Для этого таблицу с итогами вначале преобразуют в значения (чтобы удалить формулы «
ПРОМЕЖУТОЧНЫЕ.ИТОГИ» и не заботиться об их взаимодействии с автофильтром). Из полученного результата с помощью фильтра выбирают нужные записи. И таким образом в конечном счете получают компактный отчет. Однако при выполнении описанных действий есть определенные проблемы, и связаны они со структурой таблицы, где посчитаны итоги.Вернемся к базе с итогами, которая изображена на рис. 7. Напомню, что в ней мы определили итоговые значения по двум полям: вначале — по покупателю, затем — по дате. Допустим, что по этой базе нам нужно построить небольшую табличку, где останутся только даты и суммарные итоги по каждому покупателю за эту дату. Для этого нужно оставить на экране только те строки, у которых в колонке «
Покупатель» (столбец «С») есть слово «итог», а также строки с итогами по датам. На первый взгляд нет ничего проще — вызвать автофильтр, применить условие «содержит», отобрать нужные записи, скопировать их в буфер обмена и перенести в другое место (например, на другой рабочий лист). Но есть одна проблема. Обратите внимание на столбец «Дата». В тех строках, где расположены итоги по датам, соответствующие ячейки в поле «Покупатель» пусты. Например, строка «253» (рис. 7). В ячейке «А253» записано значение «31.02.2010 Итог», в ячейках «E253» и «F253» — соответствующие суммы. Но в ячейке «D253» ничего нет. То же самое можно сказать и о колонке «Покупатели». Там, где есть итоги по контрагентам, текста в колонке «Дата» нет. Поэтому, отобрав фильтром из таблицы строки с итогами по датам, мы не будем видеть названия покупателей. Разумеется, что такая ситуация нас не устроит и придется вручную продублировать наименования контрагентов в те позиции колонки «С», где есть итоги по датам. Применительно к рис. 7 — это ячейки «С248», «С253», «С256», «С259» и т. д. При большом количестве итогов такая работа может занять много времени, если не вспомнить о возможностях инструмента «Найти и выделить». Делаем так:1) открываем базу с итогами. В нашем примере она выглядит, как показано на рис. 7;
2) выделяем всю таблицу, копируем ее в буфер обмена («
Ctrl+C»);3) не снимая выделения, щелкаем на значке раскрытия списка иконки «
Вставить». Раскроется меню вариантов вставки;4) из этого меню выбираем «
Специальная вставка». Появится окно настроек специальной вставки;5) переключатель «
Вставить» переводим в положение «Значения»;6) нажимаем «
ОК» (рис. 9); 
Эти действия нужны для того, чтобы удалить из таблицы с итогами формулы промежуточного суммирования. В противном случае, применив автофильтр, мы не получим правильных итогов. Напомню, что функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» работает с учетом автофильтра. Поэтому вместо реальных значений на месте итогов мы увидим нули. Теперь наша задача — заполнить пустые ячейки в таблице. Для этого в пустые клетки нужно перенести данные из вышестоящей ячейки. Например, в ячейку «С248» скопировать значение «ООО “Коло”», т. е. написать в «С248» формулу «=С247» . Соответственно в ячейку «С256» нужно вставить формулу «=С255», в ячейку «С259» — формулу «=С258» и т. д. (рис. 7);
7) становимся внутрь базы данных;
8) входим в меню «Главная», щелкаем на иконке «Найти и выделить» (или нажимаем «Ctrl+G», рис. 10);
9) из появившегося меню выбираем «Перейти…». Появится окно «Переход» (рис. 10);
10) в нем щелкаем на кнопке «Выделить…». Откроется окно «Выделение группы ячеек»;
11) в этом окне находим переключатель «Выделить» и ставим его в положение «Пустые ячейки»;
12) нажимаем «ОК». Excel выделит все пустые ячейки в базе данных с итогами. Первая из них будет «C5»;
13) не снимая выделения, вводим в «C6» формулу «=C5»;
14) нажимаем «Ctrl+Enter». Excel заполнит все пустые ячейки в базе ссылками на соседние ячейки сверху;
15) удаляем лишние формулы-ссылки.
Теперь базу можно безболезненно отфильтровать. Все пустые ячейки будут заполнены значениями.
Итоги и контекстная замена
Функция «
ПРОМЕЖУТОЧНЫЕ.ИТОГИ», которую использует режим «Итоги» MS Excel, не всегда удобна при работе с таблицами. Все зависит от ситуации. В практике бухгалтера бывают случаи, когда нужно видеть суммы с учетом действия автофильтра. Типичный пример, когда из базы кассовых операций нужно выбрать автофильтром конкретного сотрудника и проверить правильность закрытия подотчетных сумм. В этом случае «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» работают как надо. Они покажут именно те суммарные значения, которые относятся к конкретному выбранному сотруднику. Анализируя ту же базу целиком, такая функция уже не понадобится. Скорее всего, нас будут интересовать не частные суммы по отдельному фрагменту таблицы, а реальные значения по всем данным. Даже если они скрыты фильтром. Говоря языком Excel, в первом случае вам нужны итоги, которые считает функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ». Во втором случае итоговые значения нужно получить функцией «СУММ». Чтобы решить такую проблему, я советую взять на заметку такой прием:1) открываем базу данных. Например, как на рис. 4;
2) становимся на ячейку, в которой записана функция «
ПРОМЕЖУТОЧНЫЕ.ИТОГИ». В базе на рис. 4 это может быть ячейка «E14»;3) щелкаем левой кнопкой мыши в строке формул;
4) выделяем из текста формулы (в данном случае это «
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;E2:E13)») фрагмент «=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;»;5) нажимаем «
Ctrl+C» — копируем текст в буфер обмена. Я это сделал для того, чтобы затем использовать строку «=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;» в контекстной замене;6) вызываем меню «
Главная»;7) в группе «
Редактирование» находим иконку «Найти и выделить» (рис. 10), щелкаем на ней левой кнопкой мыши (или нажимаем «Ctrl+H»). Откроется меню, как на рис. 11; 
8) выбираем пункт «Заменить…». Откроется окно, как на рис. 12;
9) становимся в поле «Найти:», нажимаем «Ctrl+V», чтобы вставить содержимое буфера обмена. В этом поле появится текст «=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;»;
10) переходим в поле «Заменить:», печатаем в него текст «=СУММ(»;
11) нажимаем кнопку «Заменить все».
Внешне на экране ничего не изменится. Все цифры останутся те же. Но теперь итоги будут посчитаны функцией «СУММ». Скрыв часть записей с помощью автофильтра, суммарные значения останутся без изменений.
Важно!
Заменив формулу суммирования с «=ПРОМЕЖУТОЧНЫЕ.ИТОГИ» на «СУММ», вы не сможете убрать итоги через кнопку «Убрать все» (рис. 3). Чтобы вернуть такую возможность, поменяйте с помощью контекстной замены строку «=СУММ(» на «=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;» — и все заработает.
На этом мы завершим знакомство с итогами и в следующий раз приступим к работе с одним из наиболее революционных инструментов Excel — консолидациями и сводными таблицами. А я жду ваших писем, вопросов и предложений на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!
