- •Раздел 1. Работа со списками (таблицами) как с базами данных 2
- •Раздел 2. Финансовые вычисления с использованием Excel 35
- •Раздел 1. Работа со списками (таблицами) как с базами данных Задание 1. Обеспечение контроля за вводимыми данными
- •Методические указания для выполнения задания 1
- •Задание 2. Обеспечение контроля правильности введенных ранее значений
- •Методические указания для выполнения задания 2
- •1. Удаление условий.
- •2. Задание условия.
- •3. Обнаружение значений, не отвечающих поставленным условиям.
- •Задание 3. Обеспечение контроля правильности введенных ранее значений с помощью условного форматирования
- •Методические указания для выполнения задания 3
- •Задание 4. Подведение итогов по каждой группе записей
- •Методические указания для выполнения задания 4
- •Задание 5. Технология выбора данных с помощью "Расширенного списка"
- •Методические указания для выполнения задания 5
- •Задание 6. Организация поиска информации с помощью Расширенного фильтра
- •Методические указания для выполнения задания 6
- •Функция суммесли.
- •Функция датазнач.
- •Функция левсимв.
- •Задание 7. Обработка данных с помощью Сводных таблиц
- •Методические указания для выполнения задания 7
- •1. Создание сводной таблицы. Общие положения.
- •2. Модификация сводной таблицы
- •3. Дополнительные вычисления в сводной таблице
- •4. Изменение структуры сводной таблицы.
- •5. Сводные таблицы и диаграммы. Построение диаграмм.
- •Задание 8. Консолидация данных
- •2. Использование сводных таблиц для консолидации.
- •Задание 9. Контрольное задание Задача 1.
- •Задача 2.
- •Методические указания для выполнения контрольного задания
- •Раздел 2. Финансовые вычисления с использованием Excel Задание 1. Решение задачи с помощью функции бз
- •Методические указания для решения задания 1
- •Задание 2. Решение задачи с помощью функции пз
- •Методические указания для решения задания 2
- •Задание 3. Решение задачи с помощью функции норма
- •Методические указания для решения задания 3
- •Задание 4. Решение задачи с помощью функции кпер
- •Методические указания для решения задания 4
- •Задание 5. Решение задачи с помощью функции пплат
- •Методические указания для решения задания 5
- •Задание 6. Решение задачи с помощью функций пплат, плпроц и оснплат
- •Методические указания для решения задания 6
- •Задание 8. Контрольное задание
Методические указания для выполнения задания 7
1. Создание сводной таблицы. Общие положения.
Создание сводной таблицы производится с помощью Мастера сводных таблиц и диаграмм, который вызывается командой Данные - Сводная таблица. В первом диалоговом окне Мастер сводных таблиц (рис. 1) установим переключатель в положение, показывающее, откуда берутся данные для сводной таблицы:
· В списке или базе данных Мiсгоsоft Ехсеl - если данные берутся с одного рабочего листа;
· Во внешнем источнике данных - если данные берутся из внешней базы данных;
· В нескольких диапазонах консолидации - если данные берутся с нескольких рабочих листов;
· В другой сводной таблице или сводной диаграмме - если сводная таблица создается на основе данных другой сводной таблицы.
Рис. 1. Первое диалоговое окно Мастер сводных таблиц и диаграмм
В рассматриваемом примере нужно установить переключатель в положение В списке или базе данных Мiсгоsоft Ехсеl.
В поле Диапазон второго диалогового окна Мастер сводных таблиц и диаграмм укажите координаты диапазона ячеек на основе данных из которого должна быть построена строится сводная таблица (рис. 2)
Рис. 2. Второе диалоговое окно Мастер сводных таблиц и диаграмм
В третьем диалоговом окне Мастер сводных таблиц необходимо указать структуру сводной таблицы (рис. 3).
Рис. 3. Третье диалоговое окно Мастер сводных таблиц и диаграмм
Для этого достаточно нажать кнопку Макет. Появится диалоговое окно для определения структуры сводной таблицы (рис. 4).
Конструирование сводной таблицы сводится к перетаскиванию мышкой названий полей в различные области. В центральной части этого окна расположены четыре области: Данные, Строка, Столбец и Страница, а в правой части окна присутствуют названия полей, которые имеются в указанном источнике данных. Области сводной таблицы имеют следующее назначение:
1. Области Строка и Столбец предназначены для группировки данных. Если название какого-то поля помещено в область Строка, то все записи источника данных группируются так, что каждая группа соответствует одному из значений поля, помещенного в область Строка. В область Строка может быть помещено несколько названий, что означает вложенную группировку. Аналогичное назначение имеет область Столбец.
2. Область Данные предназначена для выполнения групповых операций над данными. Если название какого-то поля помещено в область Данные, то ко всем записям источника данных, входящим в группу, соответствующую определенным значениям полей из области Строка и Столбец, применяется указанная групповая операция. По умолчанию групповой операцией для числовых данных является суммирование. В область Данные может быть помещено несколько названий, что означает выполнение групповой операции над значениями каждого поля.
Рис. 4. Диалоговое окно Мастер сводных таблиц и диаграмм - макет
3. Область Страница предназначена для фильтрации данных. Если название какого-то поля помещено в область Страница, то для дальнейшей обработки из источника данных выбираются только те записи, которые имеют заданное значение в этом поле. Выбор значения осуществляется с помощью раскрывающегося списка, содержащего все различные значения, которые принимает поле, помещенное в область Страница. По умолчанию выбирается значение Все, что означает отсутствие фильтрации. В область Страница может быть помещено несколько названий, что означает фильтрацию по нескольким полям.
В нашем примере можно сделать следующее.
1). Перетащить поле «Специализация» в область Страница. В результате сводная таблица будет либо представлять данные по отдельной специализации (выбранной в списке), либо сразу по всем специализациям, если выбран вариант Все.
2). Перетащить поля «Название» и «Дата счета» в область Строка. Тем самым задается группировка: нас интересуют данные по фирмам и для каждой фирмы по датам выставления счета.
3). Перетащить поле «Дата оплаты» в область Столбец. Тем самым задается группировка и по датам оплаты.
4). Перетащить поля «Сумма в счете» и «Сумма оплаты» в область Данные. В результате для групп записей, которые определяются значениями полей «Название», «Дата оплаты» и «Дата счета» будут вычисляться суммы значений полей «Сумма в счете» и «Сумма оплаты».
Чтобы подчеркнуть, что в сводной таблице в области Данные будут содержаться не значения из источника данных, а результаты групповых операций над этими значениями, Excel автоматически припишет полям имена «Сумма по полю Сумма в счете» и «Сумма по полю Сумма оплаты». Изменим эти имена на более понятные. Для этого нужно сделать двойной щелчок на названии поля в области Данные. Появится диалоговое окно, приведенное на рис. 5. В поле ввода Имя укажите новое название – «Сумма по счетам» и «Сумма оплаты» и нажмите кнопку ОК.
В диалоговом окне Вычисление поля сводной таблицы можно выбрать другую групповую операцию в списке Операция, а также задавать дополнительные вычисления на дополнительной области окна, появляющейся после нажатия кнопки Дополнительно.
В диалоговом окне Вычисление поля сводной таблицы можно выбрать другую групповую операцию в списке Операция, а также задавать дополнительные вычисления на дополнительной области окна, появляющейся после нажатия кнопки Дополнительно.
Рис. 5. Диалоговое окно Вычисление поля сводной таблицы
После того как макет таблицы определен, можно указать ее параметры в диалоговом окне, которое появляется при нажатии кнопки Параметры на третьем шаге Мастера сводных таблиц и диаграмм (рис. 6).
Рис. 6. Диалоговое окно Параметры сводной таблицы
В данном случае мы сняли флажок «общая сумма по строкам» (итоги по строкам мы будем определять другим способом) и «сохранить данные вместе с таблицей» (в целях экономии памяти).
Флажок «сохранить данные вместе с таблицей» указывает Excel, следует ли сохранять копию исходных данных вместе со сводной таблицей. Если флажок снят, то сводная таблица занимает меньше места в памяти компьютера, но данные следует обновлять при каждой загрузке приложения.
Теперь все подготовлено и можно нажать кнопку Готово в диалоговом окне Мастера сводных таблиц и диаграмм. Получим сводную таблицу, основная часть которой представлена на рис.7
Рис. 7 Сводная таблица