Лабораторная работа № 8
MS Excel. Работа с данными
Цель работы
Научиться решать задачи типа «что – если» с помощью команды Подбор параметра. Изучить технологию связывания и внедрения документов Word и Excel. Изучить средства для анализа списков в MS Excel.
Порядок выполнения работы
Решение задач типа «что – если»
-
Создайте в своей папке документ MS Excel с именем Подбор параметра.xls.
-
Книга должна содержать 2 рабочих листа:
-
Ссуда – определение срока возврата ссуды;
-
Уравнение – решение уравнения.
-
Определите, в течении какого периода будет возвращена ссуда в размере S рублей, взятая под P% годовых, при ежемесячных отчислениях в X рублей (варианты исходных данных в приложении).
Для этого воспользуйтесь функцией ПЛТ(...), которая вычисляет размер периодической (например, ежемесячной) платы в погашение займа. Обращение к функции состоит из имени ПЛТ, трех параметров и имеет вид:
=ПЛТ(Ставка – процент на периоде выплат (месяц); Количество периодов выплаты; Сумма займа)
После задания параметров функция возвращает сумму ежемесячных выплат при постоянном проценте, но с учетом того, что с каждой выплатой процент вычисляется с меньшей невыплаченной суммы займа.
Например, ежемесячная выплата при займе в 100 руб под 12% годовых на 12 месяцев: вычисляется функцией =ПЛТ(12%/12;12;100), которая возвращает отрицательное значение -88,8 руб.
Сначала проверьте работу функции для вашего варианта в случае, если срок выплат будет 12 месяцев. Затем решите обратную задачу, т.е. рассчитайте срок выплат, при котором ежемесячный платеж составит желаемую (приемлемую) для заемщика сумму. Для этого воспользуйтесь командой Excel Сервис | Подбор параметра.
Примените цветовое и шрифтовое оформление к таблице. Пример оформления листа «Ссуда» показан на рис. 1.
Рис. 1. Пример листа «Ссуда»
-
Решите уравнение методом подбора параметра. Значение y устанавливается, изменяя значение x (корень уравнения). Значения y задавайте произвольно, так чтобы построить таблицу решений уравнения для 4-5 значений x. Для проверки правильности задания уравнения вычислите значение y для простого допустимого корня (например, 0) и используйте в качестве первого значения x.
Примените цветовое и шрифтовое оформление к исходной таблице и к таблице результатов. Пример оформления листа «Уравнение» показан на рис. 2.
Рис. 2. Пример листа «Уравнение»
Технология ole
-
Создайте новый документ MS Word, в который вставьте полученные таблицы Excel следующим образом:
-
Как объект;
-
В формате RTF;
-
Как неформатированный текст;
-
Как рисунок.
-
Для вставок «Как рисунок» и «Неформатированный текст» установите связь с источником.
-
Измените данные таблицы в файле MS Excel и проверьте произошедшие изменения в связанной таблице документа Word.
-
Объясните, чем отличаются таблицы, вставленные различным образом.
-
Выполните команду Вставка | Объект | Точечный рисунок.
-
Во встроенном окне растрового редактора Paint нарисуйте картинку-логотип.
-
Завершите редактирование, щелкнув правой кнопкой мыши вне объекта.
-
Скопируйте картинку в буфер обмена.
-
Вставьте вашу картинку вновь через специальную вставку:
-
как рисунок (GIF);
-
как графический объект MS Office.
-
Проанализируйте разницу.
-
Сохраните файл с именем Таблицы.doc.
Списки
-
Создайте в своей папке документ MS Excel с именем Списки.xls.
-
Создайте список, как показано на рис. 3. Для этого выполните следующие действия:
-
откройте пустой рабочий лист, назовите его Исходный список;
-
введите в первую строку списка заголовки полей (Товар, Кол-во, Цена, Сумма);
-
введите первую запись списка (поле «Сумма» должно содержать соответствующую формулу (количество умножить на цену);
-
введите остальные записи, используя диалоговое окно Форма данных (поле «Сумма» будет проставляться автоматически).
-
Рис. 3. Пример списка
-
Сохраните файл.
-
Попробуйте перестроить список с помощью сортировки (меню Данные | Сортировка и фильтр). Отсортируйте список по полю Товар (в алфавитном порядке), затем по полю Цена (по возрастанию).
-
Попробуйте отсортировать список по нескольким полям (например, по товару и количеству). Проанализируйте результат.
-
Скопируйте список на новый лист. Назовите лист Фильтр.
-
Примените фильтр к списку (меню Данные | Сортировка и фильтр). Выполните следующие условия отбора записей списка:
-
Выведите записи только по Амортизаторам;
-
Отмените фильтр;
-
Выведите записи по Болтам и Колодкам тормозным;
-
Отмените фильтр;
-
Выведите записи товаров по цене от 120 до 1000;
-
Отмените фильтр;
-
Найдите записи по условию: товар Ремень ГРМ по цене менее 2000;
-
Отмените фильтр.
-
-
Измените вид Вашего списка с помощью стилей (Главная | Стили). Сохраните файл.
-
Скопируйте список на новый лист. Назовите лист Итоги.
-
Примените команду Итоги для Вашего списка (например, для каждого товара выведите минимальную цену). Для этого выполните следующее:
-
Отсортируйте записи в списке по полю, на основе значений которого будут базироваться группы (поле Товар).
-
Выберите команду меню Данные | Промежуточные итоги. Откроется диалоговое окно Промежуточные итоги.
-
В поле При каждом изменении в: выделите столбец для подсчета итогов (тот, по которому вы отсортировали список в п.1 –Товар).
-
Выберите операцию для вычисления итогов (поле Операция - Минимум).
-
В поле Добавить итоги по выберите столбцы, по которым необходимо подвести итоги (Цена).
-
Нажмите ОК. Проанализируйте результат.
-
-
Отмените команду Итоги (кнопка Убрать все).
-
Попробуйте применить другую операцию при подведении итогов или подведите итоги по другому столбцу.
-
Перейдите на лист Исходный список.
-
Обобщите ваши данные с помощью сводной таблицы (Вставка | Сводная таблица). Для этого выполните следующее:
-
Выделите ячейку, в которую вы хотите поместить сводную таблицу.
-
Выберите команду меню Данные | Сводная таблица. Откроется диалоговое окно Создание сводной таблицы.
-
Выделите диапазон данных (всю таблицу вместе с заголовками!).
-
Укажите, куда разместить отчет сводной таблицы – выберите Новый лист.
-
Нажмите ОК. Программа откроет новый лист и поместит туда макет таблицы, список доступных полей и панель инструментов Сводные таблицы.
-
Перетащите в область строк – поле Товар; в область столбцов – поле Количество; в поле данных – поле Сумма.
-
-
Проанализируйте результат. Объясните смысл сводной таблицы.
-
Создайте диаграмму на основе данных сводной таблицы. Диаграмму разместите на новом листе.
-
Сохраните файл.