1. Практические задания 2 Задание Структура документа и ввод данных. 2
 Скачать 13.33 Mb.
|
Задание № 18 (Часть 2). Подбор параметра.Цель: Использование инструментов "Подбор параметра" для решения типовых задач. Темы: Нахождение и анализ данных с помощью «Подбора параметров». 1. Создайте таблицу, приведенную справа на рис.18.5, воспользовавшись расчетными формулами из левой таблицы. Эта таблица представляет гипотетическую смету расходов на выполнение некоторых работ, где отдельные статьи сметы связаны между собой определенными зависимостями. При создании таблицы (сметы) исходными данными являются величины Мк и Зп, а Q - задаваемое значение. Анализ сметы сводится к сопоставлению отдельных статей расходов, общих расходов и прибыли с общей стоимостью работ. 2. Пользуясь командой Данные – Работа с данными – Анализ «что-если» - Подбор параметра, определите: при какой общей стоимости работ прибыль достигнет величины 500 т.р.; при какой общей стоимости работ прибыль достигнет величины 1000 т.р.; при какой общей стоимости работ отношение прибыли к общей стоимости достигнет величины 50%.  Рис.18.5 3. Изменяя значения общей стоимости работ (Q) и копируя результаты на новый лист (A1:G3), постройте таблицу, отражающую зависимость величин (П/Q)% и прибыли (П) от общей стоимости работ (Q). Пример такой таблицы - на рис.18.6. 4. Постройте графики зависимостей (П/Q)% и прибыли (П) от общей стоимости работ (Q) аналогично графикам приведенным на рис.18.6.  Рис.18.6 5. Создайте таблицу, приведенную на рис.18.7. В этой таблице представлены гипотетические поквартальные сведения о сбыте некоторых товаров, себестоимости продукции, доходах от реализации и величине прибыли. Объем сбыта зависит от некоторого сезонного коэффициента (Кi), а также не линейно зависит от затрат на рекламу продукции при прочих фиксированных факторах. При создании таблицы исходными данными являются величины Кi, Qi, C и R. Остальные величины вычисляются по формулам, приведенным в комментариях к таблице (диапазон A14:D17). Создав таблицу, убедитесь, что полученные результаты расчетов по формулам совпадают с приведенными в таблице рис.18.7. Точность представления данных в таблице – два десятичных знака после запятой, для процентных величин – младший разряд целой части числа. 6. Определите характер зависимостей Vi=f(Qi), Di=f(Qi), Pi=f(Qi), введя несколько значений Qi (в диапазоне от 1000 до 100000). Постройте вручную или используя средства Excel примерные графики названных зависимостей.  Рис.18.7 7. Пользуясь "подбором параметра", определите: можно ли получить в IV квартале прибыль P4=100000 т.р., изменяя расходы на рекламу Q4; величину расходов на рекламу в IV квартале (Q4), необходимую для получения прибыли P4=150000 т.р.; пользуясь последовательным подбором параметра, максимальное значение прибыли P4 с точностью до единиц целой части числа. 8. Сделайте выводы о возможностях использования "подбора параметра" и "правилах" применения данного инструмента, выполнив следующий эксперимент. 8.1. Задайте величину затрат на рекламу Q4=2000 т.р 8.2. Подбирая параметр Q4, определите, при каком значении Q4 будет достигнута величина прибыли P4=30000 т.р. 8.3. Задайте величину затрат на рекламу Q4=60000 т.р 8.2. Подбирая параметр Q4, определите, при каком значении Q4 будет достигнута величина прибыли P4=32000 т.р. 9. Найдите близкие к максимальным значения:
Результат разместите в ячейках F11:G11. Для определения названных величин рекомендуется последовательно воспользоваться несколько раз подбором параметра. 10. Пользуясь расчетными формулами (A14:D17), постройте на отдельном листе таблицу, отражающую зависимости величин V4, D4, P4 от величины Q4 (для значений Q4 меняющихся от 10000 до 100000 с шагом 10000). 11. Постройте на отдельном листе два графика (подобных представленным на рис.18.6), на которых будут отображены зависимости V4=f(Q4) для первого графика и D4=f(Q4) и P4=f(Q4) для второго графика. 12. Точность подбора параметра. 12.1. Запишите в ячейках С2, С3 и С4 (рис.18.8) исходные данные и формулу для вычисления произведения двух чисел Y=A*X. Исходные значения сомножителей: А=0,5 и Х=2,35. 12.2. Выполните следующие действия: скопируйте исходные данные и формулу (С2:С4) в диапазоны Е2:Е4, G2:G4 и I2:I4; для столбцов E, G и I установите разрядность отображаемых значений равную соответственно 4, 6 и 16 разрядов после десятичной запятой, как показано на рис.18.8. 12.3. Выполните "подбор параметра" для нахождения первого сомножителя (А), расположенного в ячейке С2 при С3=2,35, искомом значении целевой ячейки С4=4,3758 и начальном значении С2=0,5. 12.4. Повторите "подбор параметра" для той же формулы, записанной в диапазонах Е2:Е4, G2:G4 и I2:I4.  Рис.18.8 12.5. Выполните вручную умножение для данных в ячейках Е2:Е3, G2:G3 и I2:I3 с заданной разрядностью и запишите результаты в ячейки Е6, G6 и I6 соответственно. 12.6. Сравните данные, полученные в результате ручных вычислений и "подбора параметров". Оцените величину и знак погрешности вычислений. 13. Создайте таблицу, в которой выполняется возведение числа Х в степень Y по образцу, приведенному на рис 18.9. Формула Z=XY, обеспечивающая вычисления, записана в ячейке D13, а исходные данные Х=2 и У=2 - в ячейках D11 и D12 соответственно. 13.1. Пользуясь "подбором параметра", выполните поиск такого значения Х (при неизменном Y=2), которое обеспечит Z=4. Результат поиска должен быть представлен в ячейках В11:В13.  Рис.18.9 13.2. Аналогично п. 13.1 выполните поиск значения Y (при неизменном Х=2), которое обеспечит Z=4. Результат поиска должен быть представлен в ячейках С11:С13. 13.3. Сравните результаты непосредственного вычисления (D11:D13) и результаты двух "подборов параметров" (B11:B13 и C11:C13). Сделайте выводы относительно возможности использования инструмента "подбор параметра". Задание № 18 (Часть 3). Поиск решения.Цель: Использование инструментов "Поиск решения" для решения оптимизационных задач. Темы: Нахождение оптимального значения с помощью «Поиска решения». 1. Определите максимальное значение годовой прибыли (P), которое может быть получено за счет изменения месячных величин расходов на рекламу (Qi), пользуясь инструментом "поиск решения". 1.1. Запустите инструмент "поиск решения" (Office – Параметры Excel – Надстройки – в раскрывающемся списке Управление – Надстройки Excel – Перейти – установить флажок Поиск решения), просмотрите все варьируемые параметры поиска, задаваемые с помощью диалогового окна "Поиск решения" (рис.18.10.)  Рис.18.10 1.2. Определите как целевую ячейку, содержащую величину годовой прибыли (P). 1.3. Определите, будет ли в результате поиска достигаться определенное значение цели (прибыли) или ее экстремальное (максимальное или минимальное) значение. 1.4. Определите, за счет изменения содержания каких ячеек будет достигаться желаемая цель – увеличение прибыли. 1.5. Определите и последовательно добавьте в список выражения, ограничивающие условия поиска. В качестве таких выражений задайте следующие ограничения: затраты на рекламу в каждом квартале (Qi) не могут быть отрицательными; цена продукции не должна быть меньше себестоимости. 1.6. Выполните поиск решения. Убедитесь, что решение найдено и результаты поиска совпадают с результатами, приведенными на рис.18.11. Занесите полученные результаты в таблицу и сохраните на отдельном листе отчет по результатам поиска решения.  Рис.18.11 2. Предъявите результаты преподавателю. Задание № 19. Гиперссылки.Цель: Знакомство с использованием гиперссылок для построения гипертекстовых документов. Темы: Использование гиперссылок внутри локального документа и в сети. Структура гиперссылки. Интерфейсные объекты для гиперссылок. 1. Откройте новую книгу и создайте на первом листе ряд объектов, которые будут использоваться как гиперссылки. Сопроводите эти объекты пояснительными надписями, как показано на рис.19.1. Объектами для создания гиперссылок в первом листе должны являться: пустая ячейка С2; текст "Boys" в ячейке С4; объемный прямоугольник, созданный графическими средствами Excel; рисунок из набора Клипов; элемент управления (Command Button).  Рис.19.1 2. На втором листе книги создайте по образцу рис.19.2 структуры данных (таблицы), к которым будут обращаться создаваемые гиперссылки. Диапазон ячеек B8:D10 должен иметь имя "Студенты". Сохраните созданную книгу под именем Имя_19_1. 3. Создайте в первом и втором листах книги гиперссылки к данным другого листа. 3.1. В пустую ячейку С2 первого листа поместите гиперссылку на ячейку В2 второго листа. Для создания гиперссылки воспользуйтесь командой Вставка – Связи - Гиперссылка и в диалоговом окне Вставка гиперссылки в секции Связать с выберите тип необходимой ссылки и заполните поле ввода Введите адрес ячейки. Образец диалогового окна представлен на рис.19.3. Проверьте правильность работы гиперссылки, выполнив несколько переходов и возвратов с первого листа на второй (в ячейку В2) и обратно. Возврат на первый лист выполните, создав гиперссылку с текстом Возврат на Лист1 (рис.19.2). При создании гиперссылок используйте диалоговое окно Подсказка для гиперссылки для ввода подсказки, которая появится при наведении мыши на гиперссылку. 3.2. Пользуясь вставкой гиперссылок, превратите в гиперссылку текст "Boys", находящийся в ячейке С4. Гиперссылка должна выполнять переход на второй лист к именованному диапазону ячеек "Студенты". Для этого в диалоговом окне Вставка гиперссылки необходимо указать в качестве якоря имя диапазона. Выполните переходы и проверьте работу гиперссылки. 3.3. Пользуясь вставкой гиперссылок, превратите рисунок, расположенный поверх ячеек C6:D6 (объемный прямоугольник), в гиперссылку, выполняющую переход на второй лист. Выполните переходы и проверьте работу гиперссылки.  Рис.19.2 3.4. Превратите рисунок из коллекции клипов, расположенный поверх ячеек C8:D9, в гиперссылку, выполняющую переход к таблице "Гороскоп" на втором листе. Выполните переходы и проверьте работу гиперссылки. 3.5. Превратите элемент управления CommandButton в гиперссылку, выполняющую переход к диапазону ячеек G8:I10 на втором листе. Выполните переходы и проверьте работу гиперссылки.  Рис.19.3 4. Создайте гиперссылки к внешним источникам данных (документам Excel). 4.1. Создайте новую книгу, на третьем листе которой, начиная с ячейки В4, разместите таблицу с именами и антропометрическими данными студентов (по образцу В8:D10 второго листа книги Имя_19_1). Присвойте этому диапазону имя "NewBoys". Закройте созданную книгу, предварительно сохранив ее под именем Имя_19_2. 4.2. В ячейке Н6 первого листа книги Имя_19_1 создайте гиперссылку на ячейку В4 третьего листа закрытой книги Имя_19_2 (рис.19.4). Используйте кнопку Закладка диалогового окна Вставка гиперссылки. 4.3. В ячейке Н8 первого листа книги Имя_19_1 создайте гиперссылку на именованный диапазон (якорь) "NewBoys" третьего листа закрытой книги Имя_19_2 (рис.19.4). 4.4. Проверьте работу гиперссылок и создайте в третьем листе книги Имя_19_2 ссылку, обеспечивающую возврат на второй лист книги Имя_19_1. 4.5. Проверьте работу цепочки, состоящей из трех гиперссылок, расположенных на первом и втором листах книги Имя_19_1 и третьем листе книги Имя_19_2. 5. Создание гиперссылки к внешним источникам данных (гипертекстовым документам). 5.1. Создайте в ячейке Н2 первого листа книги Имя_19_1 гиперссылку, обеспечивающую переход к гипертекстовому документу, распложенному на Web-сервере компании Microsoft (рис.19.4). Для создания гиперссылки используйте следующий указатель ресурса (URL): HTTP://WWW.Microsoft.com/. 5.2. Проверьте работу внешней гиперссылки, обратив внимание на то, какими программными продуктами обеспечивается просмотр вызванного гипертекстового документа. Вернитесь к исходному документу, пользуясь кнопкой "Назад" программы, обеспечивающей просмотр гипертекста (браузера). 5.3. Создайте в ячейке Н4 первого листа книги Имя_19_1 гиперссылку, обеспечивающую просмотр папок удаленного компьютера с использованием сервиса FTP и соответствующего ему протокола FTP (рис.19.4). Для создания такой гиперссылки используйте следующий URL: FTP://FTP.Microsoft.com/. 5.4. Проверьте работу внешней гиперссылки, обратив внимание на способ представления данных, передаваемых по FTP протоколу и просматриваемых с помощью программы - браузера. Вернитесь к исходному документу.  Рис.19.4 6. Предъявите результаты преподавателю. 1 Оформление надписи "показатели производства" на рис.7.2 производится факультативно. 2 Реализацию логической функции И для данных одного столбца осуществлять повторным размещением в области критериев заголовка данного столбца. 3 Для выполнения этого задания в ячейку, с которой будет начинаться область для извлечения данных, ввести название заголовка столбца "Фамилия". |
||||||