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