Excel 2007: сводные таблицы
 | Завершая тему обработки данных, мы рассмотрим два очень полезных инструмента Excel 2007: сводные таблицы и Мастер текстов. Роль этих инструментов в бухгалтерской практике трудно переоценить. Режим сводных таблиц поможет быстро разобраться с большими базами данных, мгновенно получая из них любые итоги для произвольного набора ключевых полей. Более того, сводные таблицы позволяют реализовать главную «фишку» любого бухгалтера — отчеты перекрестного типа. А за этим кроется не только возможность проведения бухгалтерских расчетов, но и мощный механизм контроля данных, поиска ошибок в больших массивах информации. Что касается Мастера текстов, он станет надежным подспорьем для преобразования данных в формат базы MS Excel. Учитывая исключительную важность этих инструментов, неудивительно, что они получили существенное развитие в Excel 2007. Посмотреть, в чем заключается это развитие и как эффективно использовать сводные таблицы и Мастер текстов практикующему бухгалтеру, и будет нашей задачей. И начнем мы со сводных таблиц. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Подобно сортировке, итогам и средствам фильтрации, сводные таблицы тоже оперируют с базами данных. Основная задача этого инструмента — получать итоговые значения, используя для этого различный набор ключевых полей для обработки и группировки данных. В этом смысле сводные таблицы похожи на инструмент «
Итоги», но между ними есть важное отличие.Режим «
Итоги» изменяет базу данных, добавляя в нее строки с расчетными формулами для подведения итогов. При работе со сводными таблицами база остается неизменной. Все итоговые значения Excel собирает в специальный отчет, который можно расположить в любом месте рабочей книги. Есть и другие отличия, но мы их коснемся немного позже, когда будем на практике работать со сводными таблицами. А пока наша первая задача — научиться формировать сводный отчет в программе MS Excel 2007. И начнем мы с самого простого варианта.
С
оздание сводной таблицы по одному ключевому полюВ качестве исходной базы данных мы воспользуемся таблицей, изображенной на рис. 1. База состоит из семи полей. В ней собраны данные о продажах, которые организованы в такую структуру. Вначале расположено поле «
Дата» — это дата регистрации продажи. В колонке «Покупатель» зафиксировано название контрагента, купившего товар. В колонке «НаимТов» расположено название проданного товара. Далее следуют такие данные: «Кол» — количество проданного товара, «Цена» — его цена, «Сумма» — сумма продажи. Завершает базу колонка «НомДок» — это номер накладной, по которой отпущен товар. Наша первая задача будет очень простой — определить суммарные объемы продаж в количественном выражении (в штуках) по каждому контрагенту. Но прежде чем сделать это, разберемся с интерфейсом Excel 2007 в контексте создания сводных таблиц. 
В предыдущей версии Excel формирование сводных таблиц было представлено отдельным пунктом в меню «Данные». Посмотрим, как это выглядит в Excel 2007. Вызываем раздел главного меню «Данные». Появится соответствующая лента, которая состоит из пяти групп иконок: «Подключения», «Сортировка и фильтр», «Работа с данными», «Структура», «Анализ». Внимательно просмотрев состав каждой группы, мы увидим, что инструментов создания сводных таблиц среди них нет, как нет и других средств по работе со сводными отчетами. И это первое, что отличает интерфейс Excel 2007 от предыдущей версии этой программы.
Важно!
В Excel 2007 средства работы со сводными таблицами разделены на несколько частей. Инструмент создания сводных отчетов находится на ленте меню «Вставка». Редактирование сводных таблиц организовано через специальный «Конструктор». Он расположен на ленте меню «Данные» и становится доступным сразу после создания сводного отчета. Кроме того, после создания сводной таблицы на ленте меню «Данные» появятся три группы иконок для настройки параметров и управления сводной таблицей.Теперь посмотрим, как все это выглядит на практике.
ПРИМЕР
Итак, возвращаемся к нашей задаче. Есть база данных, изображенная на рис. 1. Она расположена на листе с именем «
Продажи». Мы хотим определить суммарные объемы продаж в количественном выражении (т. е. в штуках) по каждому контрагенту. Делаем так:1) нажимаем «
Shift+F11» — вставляем новый лист. На этом листе мы будем строить сводный отчет;2) двойным щелчком на ярлычке листа входим в редактирование названия. Изменяем его (я назвал лист с будущим сводным отчетом «
СВ»);3) ставим указатель активной ячейки там, где должна начинаться сводная таблица. Например, на ячейке «
A1» листа «СВ»;4) вызываем раздел «
Вставка» основного меню;5) на ленте меню «
Вставка» (рис. 2) находим группу «Таблицы»; 
6) в этой группе щелкаем на иконке «Сводная таблица». Появится небольшое меню из двух пунктов: «Сводная таблица» и «Сводная диаграмма» (рис. 2);
7) выбираем «Сводная таблица». Появится окно «Создание сводной таблицы», изображенное на рис. 3. В нем нужно указать два параметра: расположение исходных данных и местоположение формируемого сводного отчета. Наши данные находятся на листе «СВ». Поэтому делаем так:
8) оставляем переключатель «Выберите данные для анализа» в положении «Таблица или диапазон:»;
9) щелкаем в поле ввода для параметра «Таблица или диапазон:». Станут доступны для навигации листы рабочей книги;
10) щелкаем на листе «Продажи»;
11) удерживая левую кнопку мыши, обводим диапазон колонок «A:G» — здесь расположена база данных. Ее размер по высоте (количеству записей) мы ограничивать заранее не хотим, потому обвели только колонки;
12) проверяем значение в поле «Диапазон:». Мы начинали формировать сводную таблицу, находясь в ячейке «A1» листа «СВ». Именно это значение должно находиться в поле «Диапазон» (рис. 3). Если это не так, вы можете перейти на нужный рабочий лист и щелкнуть на той ячейке, откуда должен начинаться сводный отчет;
13) в окне «
Создание сводной таблицы» (рис. 3 на с. 26) нажимаем кнопку «ОК». На рабочем листе появится пустой макет сводного отчета и станет доступным специальное окно настроек с названием «Список полей сводной таблицы» (рис. 4 на с. 26). В этом окне перечислены все поля базы данных. В нижней части окна расположены некоторые параметры этих полей; 
14) щелкаем левой кнопкой слева от поля «Покупатель» (ставим галочку в квадратике возле этого поля). Перечень покупателей из базы данных тут же появится в области строк сводного отчета;
15) удерживая левую кнопку мыши, перемещаем поле «Кол» в область данных, как показано на рис. 4. Сводный отчет тут же изменит форму, возле каждого покупателя появятся сведения об объемах продаж. Но это еще не те цифры, которые нам нужны. Например, для покупателя ООО «Эталон» объем продаж в сводном отчете будет равен «179». На самом деле эта цифра должна быть намного больше;
16) возвращаемся к окну на рис. 4. В правом нижнем углу этого окна расположен параметр «Значения». Он представлен в виде списка. Сейчас этот список называется «Количество значений по полю Кол»;
17) щелчком мыши раскрываем этот список. Раскроется выпадающее меню, изображенное на рис. 5 на с. 27;
18) в этом окне щелкаем на пункте «Параметры полей значений…». Откроется одноименное окно настройки параметров, как на рис. 6 на с. 27. В этом окне можно выбрать тип операции, которую нужно произвести над полями в поле данных, в нашем случае — над единственным полем «Кол»;
19) в списке «Операция» окна «Параметры поля значений» щелкаем на строке «Сумма» (рис. 6);
20) переходим в поле «Пользовательское имя:» и печатаем в нем текст «Кол, шт.». Этот текст появится в виде заголовка в сводной таблице;
21) в окне «Параметры поля значений» нажимаем «ОК»;
22) закрываем окно «Параметры сводной таблицы». Вид сводного отчета на рабочем листе показан на рис. 7.
Посмотрим, что мы получили в этом отчете. По строкам таблицы перечислены все контрагенты, названия которых зарегистрированы в базе данных. В нашем случае таких контрагентов четыре. Справа возле каждого контрагента в колонке «Итог» записана сумма по полю «Кол». Это не что иное, как объем продаж всех видов товаров по каждому конкретному покупателю. Например, контрагент ООО «Эталон» приобрел товар в количестве 2226 шт. Для ООО «Талан» эта цифра составила 2605 шт. и т. д.
В верхней части таблицы (ячейка «A2») видно наименование поля, которое расположено в области строк. В нашем случае это поле «Покупатель». Над ним расположен комментарий, который мы ввели в окне «Параметры поля значений» (рис. 6). Этот комментарий напоминает, что мы посчитали объем продаж как количество в штуках.
Отдельной строкой сводного отчета стоит элемент «(пусто)». Он появился потому, что при формировании сводной таблицы на одиннадцатом шаге нашего примера мы указали диапазон колонок «A:G». Поскольку база занимает не весь рабочий лист, в этот диапазон попали пустые строки. Сделали мы это сознательно. Ведь в данном случае при пополнении базы данных новыми значениями они всегда попадут в область обработки и будут учтены при формировании сводного отчета. А присутствие дополнительной строки «(пусто)» с нулевой суммой итоговые цифры в отчете не меняет. И все же лишние данные нам ни к чему, тем более что скрыть лишние строки в итоговом отчете — дело нескольких секунд.
Корректировка состава отображаемых полей сводной таблицы
В готовом отчете в любой момент вы можете откорректировать состав полей, отображаемых на экране (и на печати). Для этого делаем так:
1) щелкаем на значке списка справа от поля «
Покупатель» (рис. 7). Раскроется окно, изображенное на рис. 8 на с. 28;
2) щелчком мыши снимаем галочку возле элемента «
(пусто)»;3) нажимаем «
ОК». Седьмая строка в итоговом отчете исчезнет.Чтобы восстановить отображение всех полей в итоговом отчете, нужно включить флажок возле пункта «
(Выделить все)».Важно!
Расставив соответствующие флажки в окне на рис. 8, можно выполнить практически любую фильтрацию сводной таблицы по ключевому полу в области строк.Такой подход вполне логичен, ведь сводная таблица — своего рода компактное представление данных в виде группы итоговых значений. Главное преимущество такого представления в том, что его можно быстро изменить. Например, указать другую операцию для вычисляемого поля, изменить поле группировки и т. п. Чтобы такие действия были возможны, Excel и вынужден работать со сводной таблицей как со специальным объектом и перестраивать его только по специальной команде.
Корректировка итогов сводной таблицы
Теперь посмотрим, что происходит с итогами сводного отчета при изменении исходных данных. Делаем так:
1) переходим на лист с базой данных (в примере это лист «
Продажи»);2) изменяем любые цифры в ячейках этого листа. Например, откорректируем объемы продаж по фирме ООО «Эталон»;
3) возвращаемся на лист «
СВ» со сводной таблицей. Видим, что значения итогов не изменились;4) ставим указатель активной ячейки внутрь области сводной таблицы;
5) щелкаем правой кнопкой мыши. Появится контекстное меню, как на рис. 9;
6) из этого меню выбираем пункт «
Обновить». Excel изменил данные в сводной таблице, теперь они посчитаны с учетом сделанных изменений.Важно!
Содержимое сводной таблицы не обновляется автоматически при изменении исходных данных. Для того чтобы привести сводный отчет в актуальное состояние, нужно выделить его и выполнить команду «Обновить».
Сводная таблица и специальная вставка
Настало время выяснить, как все-таки можно изменить саму сводную таблицу. Такая потребность на практике встречается очень часто. Речь идет о ситуации, когда сводный отчет является заготовкой для дальнейших вычислений. В этом случае вам понадобится в этот отчет добавлять, удалять строки или колонки, вставлять формулы, корректировать значения и т. д. Делаем так:
1) открываем лист «
СВ», где находится сводный отчет;2) выделяем одну или несколько строк в пределах этого отчета. Для нашего примера это могут быть, скажем, строки «
3», «4», «5»;3) нажимаем клавишу «
Del».На экране появится диалоговое окно с сообщением «
Нельзя изменить эту часть отчета сводной таблицы».Важно!
Сводная таблица — это специальный объект MS Excel. В этом объекте нельзя обычными средствами удалить строки, столбцы или стереть данные как при работе с ячейками рабочего листа. Единственный способ изменить состав строк, колонок и наполнение сводного отчета — это обратиться к окну «Список полей сводной таблицы» и переформировать в нем макет.Возникает вопрос: а как же поступить для решения нашей задачи, когда итоговый отчет нужно использовать в качестве заготовки для определенных расчетов? Ответ прост: применить специальную вставку. Делаем так:
1) открываем лист «СВ» с итоговой таблицей;
2) выделяем сводный отчет. Если он один на листе, можно воспользоваться комбинацией «Ctrl+A»;
3) копируем выделенную область в буфер обмена («Ctrl+C»);
4) не снимая выделения, щелкаем на значке раскрывающегося списка иконки «Вставить» меню «Главная»;
5) из предложенного списка выбираем пункт «Специальная вставка» (рис. 10 на с. 29);
6) в окне специальной вставки ставим переключатель «Вставить» в положение «Значения» (рис. 10);
7) нажимаем «ОК».
Внешний вид листа не изменился, но теперь на нем нет сводной таблицы. Вместо итогового отчета на листе остался только результат его обработки, т. е. значения. А это означает, что работать с ними можно как с обычными данными рабочего листа.
Важно!
Если вы собираетесь править данные сводной таблицы, преобразуйте ее в значения режимом специальной вставки.Первый опыт работы со сводными таблицами Excel 2007 мы получили. Поскольку объем статьи ограничен, я предлагаю сделать паузу и подвести небольшой итог по результатам первого знакомства. Это совершенно не означает, что мы закрываем столь важную для бухгалтера тему! В следующих статьях мы обязательно продолжим работу со сводными отчетами. А пока подытожим, что нового появилось в сводных таблицах Excel 2007.
Конечно же, это интерфейс. Он стал другим. Какие-то моменты в нем покажутся спорными, но факт остается фактом. Построить несложную сводную таблицу в Excel 2007 можно быстрее. При этом разработчики отказались от использования пошагового Мастера, а применили схему формирования таблицы прямо на рабочем листе. Думаю, это правильное решение. Привыкнуть к нему несложно, а для начинающих такой режим более нагляден.
Опытный пользователь наверняка заметил, что в меню «Вставить» представлены не все варианты создания сводных таблиц. Например, здесь нельзя построить отчет по нескольким диапазонам консолидации. Ограничены возможности по использованию внешних баз данных. Не переживайте, все эти средства в Excel 2007 есть, многие из них получили существенное развитие. Просто по логике интерфейса Excel 2007 такие возможности отнесли к разряду дополнительных и спрятали их внутри Конструктора таблиц и параметров программы. В то же время достаточно элементарного действия, чтобы все эти возможности стали легко доступны.
И все же интерфейс — это дело привычки, и не более того. Пару часов за экраном — и проблема решится сама собой. Для бухгалтера гораздо важнее функциональное наполнение и вычислительные возможности программы. И здесь сводным таблицам Excel 2007 есть чем порадовать своих почитателей.
Первое (и самое главное!) — в Excel 2007 практически нет ограничений на максимальное количество значений ключевого поля. Если в Excel 2003 эта величина составляла 32500 значений, то в Excel 2007 она увеличена до 1048576 уникальных элементов для каждого поля. То есть работая с Excel 2007, об ограничениях на объем данных бухгалтер может забыть. Размеры листа и новые сводные таблицы Excel 2007 способны переварить базу проводок даже солидной корпорации…
Есть и другие моменты. Так, приятной новинкой Excel 2007 является возможность отмены последних действий при создании или перегруппировке сводной таблицы. Удивительно, почему в ранних версиях эта команда была недоступна.
У сводных таблиц Excel 2007 существенно расширился список параметров. В сводный отчет теперь можно внедрить пустые строки для организации специальных вычислений. Появилась возможность создания системы фильтров и внедрения их прямо в макет сводных таблиц (рис. 4, кнопка «Фильтр отчета»). Одним словом, сводные таблицы Excel 2007 существенно улучшили свою функциональность. Причем большинство этих усовершенствований имеют прямое отношение к бухгалтерской специфике работы с Excel. В следующей нашей статье мы убедимся в этом на конкретных примерах.
Жду ваших вопросов, писем, предложений и замечаний на
bk@id.factor.ua, nictomlar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!
