double arrow

Упражнения по теме 9

9.5.1. Подбор параметра для достижения результата

1. Откройте книгу Проект Бюджета из папки Инф. Технологии. Выполните преобразование книги.

2. Изучите данные листа Бюджет. Для этоговыделите ячейку D7 и выполните команду на вкладке Зависимости формул /Зависимые ячейки. Повторите эту же команду для ячейки D8. Для ячейки J21 выполните команду Влияющие ячейки. Обратите внимание на формулу в ячейке J21, в которой рассчитывается прибыль за год.

14. Щелкните на ячейке J21. С помощью команды Сервис/Подбор параметра найдите, при каком росте объема продаж (ячейка D7) прибыль составит 80 000 руб.

15. Выделите диапазон С10:I13 и нажмите клавишу < F11 > для создания диаграммы на отдельном листе.

16. Используя меню Диаграмма, добавьте на диаграмму ряд Прибыль
(диапазон С21:I21).

17. Выполните подбор параметра, используя диаграмму. Для этого выделите столбец Расходы всего за март месяц и уменьшите его высоту до значения 4000 руб., изменяя расходы на рекламу в этом месяце.

18. Найдите, при каких расходах на рекламу в апреле сумма всех расходов в этом месяце составит 3000 руб.

19. Сохраните книгу Проект Бюджета под именем Упражнение_9_5_1.

9.5.2. Анализ данных с помощью таблиц данных

1. Откройте книгу Управление данными97 из папки Инф. Технологии. Выполните преобразование книги.

2. Изучите данные листа Размеры платежей. Обратите внимание на то, что в ячейке D13 содержится формула, =ПЛТ(Проценты/12;Срок_кредита;-Кредит) включающая функцию ПЛТ. Эта функция возвращает размеры ежемесячных платежей по кредиту. В диапазоне D8:D10 расположены аргументы функции. Каждой ячейке присвоено имя.

3. Рассчитайте, как будет зависеть размер ежемесячных платежей (D13) и выплаченный процент (Е13) от величины процентной ставки при фиксированных данных: размер кредита и срок кредита. Выделите диапазон С13:Е20 и выполните команду Таблица данных.

4. В диалоге Таблица данных первое поле оставьте пустым, поскольку по столбцам в этом случае переменной нет. Во втором поле введите ссылку на ячейку Процент ($D$8). И нажмите OK. Просмотрите D14:D:20 – это массив результатов расчета по формуле. Нажмите < F11>, чтобы на отдельном листе появилась диаграмма, содержащая один ряд данных. Установите подписи по горизонтальной оси диаграммы для этого ряда (='Размеры платежей'!$C$14:$C$20).

5. Сохраните книгу.

6. Теперь рассчитайте, как будет зависеть размер ежемесячных платежей от двух параметров сразу: величина процентной ставки и срок кредита при фиксированном размере кредита. Для этого выделите диапазон С24:I31 и выполните команду Таблица данных.

7. В окне диалога Таблица данных в первое поле введите (щелчком по ячейке Срок кредита) адрес $D$9, поскольку по столбцам в этой таблице заполнен диапазон сроков кредита. Во второе поле введите адрес ячейки Проценты ($D$8), поскольку по строкам в этой таблице заполнен диапазон значений процентов по кредиту. Нажмите OK.

8. Выделите массив результатов: D25:I31. Нажмите F11. Просмотрите диаграмму результатов расчета. Выполните настройку диаграммы. Замените используемые по умолчанию имена рядов и подписи по горизонтальной оси.

9. Введите в ячейку Кредит значение 80000. Посмотрите, как в обеих таблицах данных на листе Размеры платежей изменился массив результатов.

10. Сохраните книгу. Скопируйте лист Размеры платежей и построенные диаграммы в новую книгу и сохраните ее под именем Упражнение_9_5_2.

9.5.3. Построение графиков на основе таблицы данных

1.
Создайте новую книгу и сохраните ее под именем Упражнение_9_5_3. Для построения графиков плотности вероятности нормально распределенной случайной величины X при различных значениях параметров (m, σ) на основе таблицы данных с двумя переменными создайте таблицу исходных данных, состоящую из четырех строк и двух столбцов, имена которых:

· название параметра (данные вводятся: m, σ, x).

· значение параметра (данные вводятся).

2. Заполните таблицу исходных данных: m = 2, σ = 1, х = 3.

3. Введите ниже в свободную ячейку листа формулу для расчета значения функции p(x) при заданных значениях m, σ, x, используя абсолютные ссылки на соответствующие ячейки таблицы исходных данных.

4. Создайте таблицу данных с двумя переменными (m, x): m изменяется от 0 до 3 с шагом 1; x изменяется от –5 до 5 с шагом 0,5.

5. Постройте график зависимости p(x) при разных значениях математического ожидания m. Для этого выделите в таблице данных диапазон, содержащий только значения функции, и нажмите клавишу < F11>.

6. Выполните настройку полученной диаграммы. Заполните для каждого ряда поля Имя и Подписи по оси Х ссылками на соответствующие ячейки листа Графики.

7. Сохраните книгу Упражнение_9_5_3.

9.5.4. Решение задачи оптимизации

1. Откройте книгу Поиск решения из папки Инф. Технологии. Выполните преобразование книги.

2. На листе Стулья решается задача об оптимальном плане выпуска стульев. Стулья могут выпускаться двух типов (строки 2 и 3). Под заказ выделены материальные и людские ресурсы (строка 5). Спрашивается, сколько надо выпустить стульев каждого типа, чтобы получить максимальный доход (ячейка J4), принимая во внимание ограниченные ресурсы. Эта задача относится к широкому классу задач под названием линейное программирование.

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

4. Сохраните найденное решение.

5. Перейдите на лист Реклама, на котором составлен план рекламной кампании нового изделия. Изучите документ, обратите внимание, что здесь всем ячейкам присвоены имена. Обратите внимание на блок ограничений, сопровождающих рекламную кампанию.

6. Найдите минимальные затраты на рекламу нового изделия (ячейка Е8 с именем Всего), учитывая принятые в документе ограничения. Изменяемыми при поиске решения должны быть ячейки D2:D7. Для этого выполните команду Поиск решения. Сохраните решение на листе.

7. Изучите полученное решение. Обратите внимание на то, что значения в ячейках D2:D7 (число объявлений) получились не целыми.

8. Откройте диалоговое окно Поиск решения и установите еще одно ограничение: значения в ячейках D2:D7 должны быть целыми числами. Найдите новое решение.

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

10. Сохраните книгу под именем Упражнение_9_5_4.

9.5.5. Работа со сценариями и создание отчета по сценариям

1. Откройте книгу Диспетчер сценариев в папке Инф. Технологии. Выполните преобразование книги.

2. Перейдите на лист Попов (основа). На этом листе приведен анализ чистой прибыли некоторого предприятия и его расходов в зависимости от доходов от покупателей и различных накладных расходов. Заметим, что все ячейки листа имеют имя.

3. Выполните команду Диспетчер сценариев. Выделите один из сценариев и нажмите кнопку Вывести. Результат расчета выводится на рабочий лист. Нажмите кнопку Изменить. Измените параметры сценария по своему усмотрению.

4. Создайте самостоятельно новый сценарий с именем Мой_Ваше_ФИО.

5. Просмотрите другие листы в этой книге. Заметим, что все листы в книге одинаковы и представляют собой пример коллективной работы над книгой нескольких сотрудников. На каждом листе выполните команду Сервис/Сценарии (кнопка Вывести) для того, чтобы просмотреть результаты анализа типа «что ‑ если», который выполнял каждый из сотрудников.

6. Перейдите на лист Попов (основа). Выполните команду Диспетчер сценариев (кнопка Объединить), для того чтобы объединить сценарии со всех листов.

7. Выполните команду Диспетчер сценариев (кнопка Отчет), для того чтобы получить отчет по результатам анализа всех сценариев, существующих в этой книге.

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

9. Сохраните книгу Диспетчер сценариев под Упражнение_9_5_5.

9.5.6. Статистический анализ данных

1. Создайте новую книгу и сохраните ее под именем Упражнение_9_5_6. Переименуйте лист1: Статистика. На этом листе сгенерируйте таблицу из 5 столбцов и 100 строк, содержащую 500 случайных чисел, имеющих нормальное распределение со средним значением 100 и стандартным отклонением 5. Для этого в окне диалога Анализ данных/Генерация случайных чисел установите параметры: число переменных – 5, число значений – 100, и заполните параметры, характеризующие нормальное распределение.

2. Постройте гистограмму для этого множества случайных чисел, используя команду Анализ данных/Гистограмма.

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

4. Найдите с помощью статистической функции Excel стандартное отклонение этого множества случайных чисел.

5. Сохраните книгу Упражнение_9_5_6.

Контрольные вопросы

6. Для чего используется команда Подбор параметра?

7. Как построить таблицу данных с одной переменной?

8. Как построить таблицу данных с двумя переменными?

9. Для чего используется команда Поиск решения?

10. Что называется целевой ячейкой, зависимой ячейкой?

11. Что называется сценарием? Как создать на рабочем листе несколько сценариев?

12. Для чего используется команда Описательная статистика?



Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:  



Сейчас читают про: