Лабораторная работа №7

MS Excel. Работа с данными

Цель работы

Научиться решать задачи типа «что – если» с помощью команды Подбор параметра. Изучить технологию связывания и внедрения документов Word и Excel. Изучить средства для анализа списков в MS Excel.

Порядок выполнения работы

Решение задач типа «что – если»

1. Создайте в своей папке документ MS Excel с именем Подбор параметра.xls.

2. Книга должна содержать 2 рабочих листа:

• Ссуда – определение срока возврата ссуды;

• Уравнение – решение уравнения.

3. Определите, в течении какого периода будет возвращена ссуда в размере S рублей, взятая под P% годовых, при ежемесячных отчислениях в X рублей (варианты исходных данных в приложении).

Для этого воспользуйтесь функцией ПЛТ(...), которая вычисляет размер периодической (например, ежемесячной) платы в погашение займа. Обращение к функции состоит из имени ПЛТ, трех параметров и имеет вид:

=ПЛТ(Ставка – процент на периоде выплат (месяц); Количество периодов выплаты; Сумма займа)

После задания параметров функция возвращает сумму ежемесячных выплат при постоянном проценте, но с учетом того, что с каждой выплатой процент вычисляется с меньшей невыплаченной суммы займа.

Например, ежемесячная выплата при займе в 100 руб под 12% годовых на 12 месяцев: вычисляется функцией =ПЛТ(12%/12;12;100), которая возвращает отрицательное значение -88,8 руб.

Сначала проверьте работу функции для вашего варианта в случае, если срок выплат будет 12 месяцев. Затем решите обратную задачу, т.е. рассчитайте срок выплат, при котором ежемесячный платеж составит желаемую (приемлемую) для заемщика сумму. Для этого воспользуйтесь командой Excel Сервис | Подбор параметра.

Примените цветовое и шрифтовое оформление к таблице. Пример оформления листа «Ссуда» показан на рис. 1.

Рис. 1. Пример листа «Ссуда»

4. Решите уравнение методом подбора параметра. Значение y устанавливается, изменяя значение x (корень уравнения). Значения y задавайте произвольно, так чтобы построить таблицу решений уравнения для 4-5 значений x. Для проверки правильности задания уравнения вычислите значение y для простого допустимого корня (например, 0) и используйте в качестве первого значения x.

Примените цветовое и шрифтовое оформление к исходной таблице и к таблице результатов. Пример оформления листа «Уравнение» показан на рис. 2.

Рис. 2. Пример листа «Уравнение»

Технология ole

5. Создайте новый документ MS Word, в который вставьте полученные таблицы Excel следующим образом:

• Как объект;

• В формате RTF;

• Как неформатированный текст;

• Как рисунок.

6. Для вставок «Как рисунок» и «Неформатированный текст» установите связь с источником.

7. Измените данные таблицы в файле MS Excel и проверьте произошедшие изменения в связанной таблице документа Word.

8. Объясните, чем отличаются таблицы, вставленные различным образом.

9. Выполните команду Вставка | Объект | Точечный рисунок.

10. Во встроенном окне растрового редактора Paint нарисуйте картинку-логотип.

11. Завершите редактирование, щелкнув правой кнопкой мыши вне объекта.

12. Скопируйте картинку в буфер обмена.

13. Вставьте вашу картинку вновь через специальную вставку:

• как рисунок (GIF);

• как графический объект MS Office.

14. Проанализируйте разницу.

15. Сохраните файл с именем Таблицы.doc.

Списки

1. Создайте в своей папке документ MS Excel с именем Списки.xls.

2. Создайте список, как показано на рис. 3. Для этого выполните следующие действия:

   1. откройте пустой рабочий лист, назовите его Исходный список;

   2. введите в первую строку списка заголовки полей (Товар, Кол-во, Цена, Сумма);

   3. введите первую запись списка (поле «Сумма» должно содержать соответствующую формулу (количество умножить на цену);

   4. введите остальные записи, используя диалоговое окно Форма данных (поле «Сумма» будет проставляться автоматически).

Рис. 3. Пример списка

3. Сохраните файл.

4. Попробуйте перестроить список с помощью сортировки (меню Данные | Сортировка и фильтр). Отсортируйте список по полю Товар (в алфавитном порядке), затем по полю Цена (по возрастанию).

5. Попробуйте отсортировать список по нескольким полям (например, по товару и количеству). Проанализируйте результат.

6. Скопируйте список на новый лист. Назовите лист Фильтр.

7. Примените фильтр к списку (меню Данные | Сортировка и фильтр). Выполните следующие условия отбора записей списка:

   1. Выведите записи только по Амортизаторам;

   2. Отмените фильтр;

   3. Выведите записи по Болтам и Колодкам тормозным;

   4. Отмените фильтр;

   5. Выведите записи товаров по цене от 120 до 1000;

   6. Отмените фильтр;

   7. Найдите записи по условию: товар Ремень ГРМ по цене менее 2000;

   8. Отмените фильтр.

8. Измените вид Вашего списка с помощью стилей (Главная | Стили). Сохраните файл.

9. Скопируйте список на новый лист. Назовите лист Итоги.

10. Примените команду Итоги для Вашего списка (например, для каждого товара выведите минимальную цену). Для этого выполните следующее:

    1. Отсортируйте записи в списке по полю, на основе значений которого будут базироваться группы (поле Товар).

    2. Выберите команду меню Данные | Промежуточные итоги. Откроется диалоговое окно Промежуточные итоги.

    3. В поле При каждом изменении в: выделите столбец для подсчета итогов (тот, по которому вы отсортировали список в п.1 –Товар).

    4. Выберите операцию для вычисления итогов (поле Операция - Минимум).

    5. В поле Добавить итоги по выберите столбцы, по которым необходимо подвести итоги (Цена).

    6. Нажмите ОК. Проанализируйте результат.

11. Отмените команду Итоги (кнопка Убрать все).

12. Попробуйте применить другую операцию при подведении итогов или подведите итоги по другому столбцу.

13. Перейдите на лист Исходный список.

14. Обобщите ваши данные с помощью сводной таблицы (Вставка | Сводная таблица). Для этого выполните следующее:

    1. Выделите ячейку, в которую вы хотите поместить сводную таблицу.

    2. Выберите команду меню Данные | Сводная таблица. Откроется диалоговое окно Создание сводной таблицы.

    3. Выделите диапазон данных (всю таблицу вместе с заголовками!).

    4. Укажите, куда разместить отчет сводной таблицы – выберите Новый лист.

    5. Нажмите ОК. Программа откроет новый лист и поместит туда макет таблицы, список доступных полей и панель инструментов Сводные таблицы.

    6. Перетащите в область строк – поле Товар; в область столбцов – поле Количество; в поле данных – поле Сумма.

15. Проанализируйте результат. Объясните смысл сводной таблицы.

16. Создайте диаграмму на основе данных сводной таблицы. Диаграмму разместите на новом листе.

17. Сохраните файл.