Сводные таблицы
Таблицы с данными магазинов будут иметь вид:
Таблица №1 город Тула
| Январь | Февраль | Март | Апрель | |
| Овощи | 20 | 30 | 14 | 23 |
| Фрукты | 30 | 48 | 15 | 24 |
| Ягоды | 25 | 24 | 16 | 25 |
Таблица №2 город Орел
| Январь | Февраль | Март | Апрель | |
| Овощи | 31 | 21 | 31 | 25 |
| Фрукты | 32 | 22 | 32 | 23 |
| Ягоды | 33 | 23 | 33 | 24 |
Таблица №3 город Пенза
| Январь | Февраль | Март | Апрель | |
| Овощи | 12 | 24 | 24 | 23 |
| Фрукты | 14 | 21 | 45 | 33 |
| Ягоды | 17 | 26 | 44 | 32 |
Работу выполните в следующем порядке:
- Щелкните мышью в ячейке А2. Введите текст Овощи. Аналогично в ячейки A3 и А4 введите соответственно Фрукты и Ягоды.
- Выделите ячейки с А1 по Е4 и выберите команду Автоформат (AutoFonnat) в меню Формат (Format).
- В списке форматов выберите Классический1 (Classic1) и нажмите кнопку ОК.
- Щелкните правой кнопкой мыши по ярлыку первого листа и выберите в контекстном меню команду Переместить/скопировать (Move or Copy). и установите в появившемся диалоговом окне флажок Создать копию (Create a copy).
- Аналогичным образом создайте третью копию листа.
- Щелкните правой кнопкой мыши по ярлыку Лист 1(3) (Sheet 1(3)). Выберите команду Переименовать (Rename). В поле с именем листа введите Тула. Аналогичным образом двум другим листам с таблицей присвойте названия городов Орел, Пенза.
- Заполните данные по реализации продукции по каждому из трех городов, как показано в таблицах 1, 2 и 3 ниже.
- Для построения сводной таблицы выберите в меню Данные (Data) команду Сводная таблица (Pivot Table and PivotChart Report). Установите переключатель в положение В нескольких диапазонах консолидации (Multiple consolidation ranges), так как данные расположены на нескольких листах книги и нажмите кнопку Далее.
- В следующем диалоговом окне Мастер сводных таблиц и диаграмм – шаг 2а из 3 (PivotTable and PivotChart Wizard – Step 2a of3) поставьте переключатель в положение Создать одно поле страницы (Create a single page field for me), так как все листы аналогичны и отличаются только городом, в котором реализовывалась продукция. Щелкните мышью в поле Диапазон (Range). Выделите поочередно на всех листах ячейки с A1 no E4, и нажмите кнопку Добавить (Add) после каждого выделения. Кнопка свертывания окна справа от поля позволяет свертывать диалоговое окно для выделения каждого диапазона. Повторное нажатие на эту кнопку восстанавливает окно. Затем нажмите кнопку Далее (Next).
- Поставьте переключатель в положение Поместить таблицу на новый лист (New worksheet) и нажмите кнопку Готово (Finish).
- В появившейся сводной таблице дважды щелкните по полю со словом Строка (Row). Откроется диалоговое окно Вычисления сводной таблицы (PivotTable Field) (рис. 18.21). Введите слово Товар вместо Строка и нажмите ОК. Аналогично Столбец поменяйте на Месяц, а страница на Город. После создания сводной таблицы значения доходов просуммированы по месяцам и названиям товаров. Для анализа доходов по различным городам откройте раскрывающийся список городов в ячейке В1, выделите тот город который вас интересует и нажмите кнопку ОК. Вы получите просуммированные данные по данному городу.
- Щелкните по раскрывающемуся списку Товары и снимите галочки рядом с теми товарами, которые вас не интересует. В таблице отобразятся сводные данные без этого продукта.
Выводы
- Чтобы упорядочить данные по нескольким полям, выделите диапазон ячеек, который необходимо отсортировать, и выберите команду Сортировка (Sort) в меню Данные (Data).
- Чтобы упростить ввод и редактирование данных при составлении списков в Excel, установите курсор в одной из ячеек списка и выберите в меню Данные (Data) команду Форма (Form).
- Для прогнозирования зависимости выделите диапазон ячеек, содержащий исходные значения, и используйте диалоговое окно, отображаемое после выбора в меню Правка (Edit) команды Заполнить (Fill), Прогрессия (Series).
- Найти аргумент, обеспечивающий задаваемый результат, позволяет команда Подбор параметра (Goal Seek) в меню Сервис (Tools). Решение находится путем последовательных итераций.
- Чтобы обобщить однородные данные, расположенные в нескольких областях таблицы или на разных листах, в одной таблице, укажите верхнюю левую ячейку конечной области, где должны быть помещены консолидированные данные, и выберите команду Консолидация (Consolidate) в меню Данные (Data).
- Чтобы создать сводную таблицу, выберите команду Сводная таблица (Pivot Table and PivotChart Report) в меню Данные (Data). Мастер сводных таблиц облегчает обработку больших массивов данных и получение итоговых результатов в удобном виде.