- •Часть I. Подготовка данных. 12
- •Часть II 21
- •Введение
- •Начальные сведения Excel 2007 Структура Экрана
- •Некоторые операции с листами
- •Сохранение рабочей Книги
- •Ячейки, блоки и диапазоны ячеек
- •Типы данных
- •Ввод и форматирование данных в таблице
- •Диагностика ошибок в формулах Excel
- •Использование встроенных функций при создании формул
- •Копирование формул в электронных таблицах
- •Редактирование ячеек
- •Форматирование электронной таблицы
- •Вставка и удаление строк и столбцов
- •Сортировка данных в таблице
- •Создание именованных блоков
- •Правила присвоения имён блокам и ячейкам:
- •Практическое задание Часть I. Подготовка данных.
- •Создание и ведение базы данных.
- •Поле со списком
- •Функции просмотр и впр
- •Применение функции просмотр
- •Применение функции впр
- •Логические функции
- •Применение функции если для вычисления Долга
- •Защита листа
- •Часть II Сортировка таблицы
- •Фильтрация данных
- •Формирование итогов
- •Построение круговой диаграммы
- •Фильтрация (выборка) данных
- •Сводные таблицы
- •Построение диаграммы по данным сводной таблицы
- •Группировка элементов по временным диапазонам
- •Слияние. Письма и рассылки
- •Порядок создания писем:
- •Сообщение
- •Использование технологии ole
- •Вопросы для самопроверки
Сводные таблицы
Сводная таблица – это один из мощных инструментов обработки данных, так как в этом случае сразу выполняются функции и заданные вычисления, подводятся итоги, выполняется сортировка и фильтрация данных. Построение сводной таблицы выполняется с помощью Мастера сводных таблиц, который вызывается командой Вставка /Сводная таблица.
На первом шаге выбирается источник данных. На втором шаге – диапазон исходных данных. На третьем строится макет сводной таблицы.
Макет сводной таблицы определяет её структуру и состоит из областей:
Фильтр отчёта (область страниц) - для размещения полей, по которым выполняется отбор записей;
Названия строк – для полей, по которым выполняется группировка;
Названия столбцов – для размещения полей группировки;
Значения (итоговые данные) – для размещения полей, по которым выполняются вычисления. Операции: сумма, среднее значение, количество значений, максимум, проводятся вычисления и т.д. На приведённых далее примерах рассмотрим создание сводных таблиц.
Пример 1. Создать сводную таблицу на основе таблицы листа Рабочая ведомость следующего вида:
Выполним команду Вставка/Сводная таблица. Затем Далее.
В окне указать имя Ведомость_список. (нажать F3 и выбрать Ведомость_список).
Все названия полей таблицы расположены справа в окне макета. Каждое поле можно переместить с помощью мыши в любую область макета.
Адрес разместим в область фильтр отчета. Поле Наименование заказчика в область Название строк, а Период в область Название столбцов.
В этом случае происходит размещение данных по каждому адресу на “отдельной странице”. Для отображения на экране данных о каком-либо городе нужно выбрать адрес (город) из списка в полученной сводной таблице.
Поля Сумма к выплате и Сумма Долг+Пеня следует разместить в область Значения. Так как в сводной таблице надо показать ещё и % задолженности, то Сумма Долг+Пеня разместить в область Значения дважды, чтобы затем преобразовать одно из них в % задолженности.
Чтобы переименовать поля в области данных, надо дважды щелкнуть по полю мышью – Параметры поля значений.
В окне Имя ввести Сумма_к_выплате. Нажать ОК.
Щелкнуть 2 раза по полю Сумма по полю Долг+Пеня, чтобы открыть окно Параметры поля. Пользовательское имя заменить новым - Задолженность.
Щёлкнуть 2 раза по другому полю Сумма по полю Долг+Пеня и заменить это название на % задолженности.
В этом же окне для получения значения % нажать вкладку Дополнительные вычисления, открыть список и выбрать Доля от суммы по столбцу.
Нажать ОК в этом и в следующем окне, а затем Готово. Получим сводную таблицу, показанную в начале примера1.
Для той же цели для активной сводной таблицы служит команда: группа Работа со сводными таблицами Параметры/ Вычисления/ Дополнительные вычисления или Вычисления/ / Итоги по/ Дополнительно.
Если надо внести изменения в макет таблицы, то можно воспользоваться панелью Работа со сводными таблицами:
Курсор должен находиться в сводной таблице.
Замечание: если итоги по строкам или по столбцам выдавать не требуется, то надо войти в окно Работа со сводными таблицами/ Параметры/, на ленте указать Сводная таблица/ Параметры, а затем в окне Параметры сводной таблицы снять флажки Общая сумма по столбцам и Общая сумма по строкам. Фрагмент окна параметров сводной таблицы: