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