«Решение задач с применением редактора электронных таблиц MS Excel»

Государственное автономное профессиональное образовательное учреждение Саратовской области

«Энгельсский колледж профессиональных технологий»

Тема урока:

«Решение задач с применением редактора электронных таблиц MS Excel»

Составитель: преподаватель информатики

Жданова Анна Александровна

Энгельс

2014г.

Практическая работа по дисциплине «Информатика и ИКТ» специальности «Техническое обслуживание и ремонт автомобильного транспорта» 1 курс

Тема: «Решение задач с применением редактора электронных таблиц MS Excel»

Тип урока: урок систематизации и обобщения знаний и умений.

Цель работы: закрепить теоретические знания по теме; получить навыки работы со статистическими функциями, изучить синтаксис некоторых функций, научиться решать задачи на поиск решения и подбор параметра, осуществлять анализ данных средствами редактора электронных таблиц.

Практическая работа заключается в решении экономических задач анализа данных. Представлена технология выполнения типовых задач с подробным описанием последовательности действий в редакторе электронных таблиц MS Excel. Для успешного выполнения задания необходимо повторить теоретические знания по темам: Обзор программного обеспечения для работы с электронными документами. Текстовые, табличные процессоры. Обзор программного обеспечения для обработки на ПК числовых данных. Применение определенного класса программ при решении конкретных задач. Статистические функции программы Microsoft Excel. Методы анализа доходов и прибыли, фиксированных активов, инвестиций.

2. Краткая теоретическая справка по использованию статистических функций.

Статистическая функция вставляется через меню Формулы→Вставить функцию

. Появится окно Мастер функций.

В окне Мастера функций выберите категорию Статистические, а затем конкретную функцию:

.

Клавиша F1вызовет справку по функции.

Для каждой функции имеются свои аргументы. Например, для функции СРЗНАЧМН:

Диапазон усреднения; Диапазон условия 1; условие 1;Диапазон условия 2; условие 2 и т.д.

Примеры использования статистических функций:

Заведующему отделом продаж может понадобиться спланировать продажи на следующий квартал (функция ТЕНДЕНЦИЯ).

Учителю может понадобиться построить кривую на базе средних оценок (СРЗНАЧ или МЕДИАНА, может быть даже МОДА).

Производитель, проверяющий качество продукта, может быть заинтересован в том, чтобы производимые изделия попадали в диапазон приемлемого качества (СТАНДОТКЛОН или ДИСП).

Исследователю рынка может потребоваться узнать, сколько ответов на опрос попадает в заданный диапазон (ЧАСТОТА).

Заведующая отделом продаж использует функцию ДИСП для анализа показателей продаж трех продавцов.

3. Краткая теоретическая справка по использования пакета анализа данных.

Пакет анализа. В состав Microsoft Excel входит набор средств анализа данных (так называемый пакет анализа), предназначенный для решения сложных статистических и инженерных задач. Для проведения анализа данных с помощью этих инструментов следует указать входные данные и выбрать параметры; анализ будет проведен с помощью подходящей статистической или инженерной макрофункции, а результат будет помещен в выходной диапазон. Другие средства позволяют представить результаты анализа в графическом виде. Доступные средства:   Чтобы просмотреть список доступных инструментов анализа, выберите команду Анализ данных в меню Сервис. Если команда Анализ данных в меню Сервис отсутствует — необходима установка пакета анализа.

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

Подбор параметра. При обработке табличных данных часто возникает необходимость в прогнозиро­вании результата на основе известных исходных значений или, наоборот, в опре­делении того, какими должны быть исходные значения, позволяющие получить нужный результат. Подбор параметра осуществляется с помощью команды Данные→Анализ «что-если»→ Подбор параметра

В поле Установить в ячейке появившегося диалогового окна будет отображаться адрес целевой ячейки. Задайте в поле Значение значение, которое должна содер­жать целевая ячейка. Укажите в поле Изменяя значение ячейки адрес ячей­ки, значение которой необходимо установить таким, чтобы в целевой ячейке получить заданное значение. Нажмите кнопку ОК, и нужный параметр будет подобран. Инструмент Подбор параметра помогает решить задачу, когда известно, что должно получиться в ответе, но не известно, какое значение должна иметь одна из переменных.

Поиск решения. Окно средства:

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

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

Для того чтобы запустить процедуру поиска решения, необходимо выделить це­левую ячейку и вызвать команду Данные Поиск решения. В поле Установить целевую ячейку данного окна указывается ад­рес целевой ячейки. Переключатели Равной задают ее значение - максимальному значению, минимальному значению или значе­нию. В последнем случае значение вводится в поле справа. В поле Изменяя ячейки указывается, в каких ячейках программа должна изме­нить значения для получения оптимального результата.

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

В поле Ссыпка на ячейку этого окна введите адрес ячейки, содер­жимое которой должно удовлетворять заданному ограничению, а в поле Ограничение укажите значение, выступающее в качестве ограничения, или ад­рес ячейки с таким значением. Между этими двумя полями находится еще одно, в котором устанавливается оператор, определяющий отношение между значени­ем ячейки и ограничением. После нажатия кнопки заданное ограничение поя­вится в диалоговом окне Поиск решения.

Найденное решение можно сохранить в виде файла. Для этого нажмите кнопку Сохранить сценарий и в открывшемся окне задайте имя файла.

4. СОДЕРЖАНИЕ РАБОТЫ

Упражнение 1. ВЫЧИСЛЕНИЕ ДИСПЕРСИИ. Представьте себе заведующего отделом продаж, изучающего показатели продаж трех различных продавцов, чтобы сравнить их производительность. Одна из множества статистических функций, доступных заведующему – вычисление дисперсии (ДИСП).

Дисперсия позволяет измерить, насколько данные отличаются друг от друга. Данные с низкой дисперсией состоят из идентичных или близких значений: 6, 7, 6, 6, 7. Данные с высокой дисперсией содержат сильно отличающиеся значения: 598, 1, 134, 5, 92.

Вычислить дисперсию сотрудников фирмы Батурина, Королева, Рощина. Объяснить полученные результаты вычислений в ячейках. Найти интервалы продаж каждого сотрудника (т.е. минимальное и максимальное значения)

1. Откройте из папки Мои документы\ПР Excel файл Статистика.xls

2. Ознакомьтесь с содержанием листа1

3. Прокрутите список вниз, чтобы увидеть показатели продаж Батурина. Соответствующий диапазон начинается с ячейки B8 и заканчивается ячейкой B75.

4. Щелкните в ячейке E3.

Совет.   Можно быстро перейти в любую ячейку, нажав F5 и введя ссылку на ячейку.

1. Выберите меню Формулы→Вставить функцию→ выберите ДИСП и заполните аргументы (введите нужный диапазон B8:B75) . Ячейка Е3 должна выглядеть так:

Нажмите ОК. Результат вычисления формулы должен быть равен 2714502,563

1. Щелкните в ячейке E4.

2. Выберите меню Формулы→Вставить функцию→ выберите ДИСП и заполните аргументы (введите нужный диапазон B76:B142) .

3. Нажмите ОК. Результат вычисления формулы должен быть равен 6076167,614.

4. Щелкните в ячейке E5.

5. Выберите меню Формулы→Вставить функцию→ выберите ДИСП и заполните аргументы (введите нужный диапазон B143:B210) .

6. Нажмите ОК. Результат вычисления формулы должен быть равен 1034136,358.

7. Диапазон F3:Н5 заполните сами с помощью функция МАКС и МИН известных вам из курса Информатики.

Объяснение результатов:

Как показывает сводка в верхней части листа, хотя общие суммы заказов Батурина и Королева очень похожи, их дисперсии заметно отличаются. Дисперсия – это мера разброса данных, поэтому большая дисперсии показывает, что суммы заказов Королева сильнее отличаются друг от друга.

Гипотетически начальник отдела продаж, глядя на эти показатели, может быть удовлетворен стабильностью Батурина. Он может попросить Королева не тратить столько времени на небольшие заказы и проанализировать крупные заказы, чтобы понять, в чем секрет успеха.

А как насчет Рощина? Его общая сумма заказов и дисперсия намного ниже, чем у других. Поэтому он не только приносит меньше денег, он также приносит заметно меньшие заказы по сравнению с двумя другими продавцами. Начальник Рощина может попросить его скопировать методы работы Батурина и Королева, чтобы попытаться повторить их успех. Как можно видеть, данные Батурина меняются от заказа в 100 рублей до заказа в 9210,90 рублей, а данные Королева меняются от 60,00 рублей до 12615,05 рублей. Поэтому разброс сумм заказов у Королева больше, о чем и свидетельствует увеличенная дисперсия.

Упражнение 2. ПОДБОР ПАРАМЕТРА

Задача:

Для покупки автомобиля Вам необходима сумма 200000 руб. У вас есть возможность взять 30-летнюю ипотечную ссуду со ставкой 8% годовых. При этом нужно сделать 20% взнос. Определить, какую сумму нужно взять в банке, чтобы на руки вы получили требуемую сумму.

Решение:

Пусть в банке выдали х руб. (S₀). Первоначальный взнос (V) составляет 20% * х. Остается сумма S = S₀-V.

1. Представьте исходные данные, как показано на рисунке

2. Установить курсор на ячейку D2 и воспользуйтесь командой Данные→Анализ «что-если»→Подбор параметра.

3. В диалоговом окне нужно указать следующую информацию:

- В поле Установить в ячейке указать координаты ячейки, содержащей формулу, в которой нужно достичь определенного результата, щелкнув ее, т.е. в поле Установить в ячейке - ссылка на ячейку D2,

- В поле Значение указать значение, которое должно быть результатом формулы, т.е. в поле Значение - значение 200000,

- В поле Изменяя значение ячейки ввести координаты ячейки, значение которой Excel должен изменять, т.е. в поле Изменяя значение ячейки - ссылка на ячейку А2.

Итак, получится окно: .

4. Щелкните по кнопке "ОК" - откроется окно, информирующее о том, что решение найдено (если оно существует).

С помощью средства Подбор параметра будет найден ответ: 250000 руб.

Упражнение 3. ПОИСК РЕШЕНИЯ (транспортная задача)

1. Составляем таблицу исходных данных

рис. 1. Таблица с исходными данными

1. Вводим формулы в ячейки

В ячейку Е11 формулу =СУММ(B11:D11) . Объемы перевозимого горючего от поставщика А₁.

В ячейку Е12 формулу =СУММ(B12:D12) . Объемы перевозимого горючего от поставщика А₂.

В ячейку В13 формулу =СУММ(B11:В12) . Объемы перевозимого горючего к потребителю В₁.

В ячейку С13 формулу =СУММ(С11:С12) . Объемы перевозимого горючего к потребителю В₂.

В ячейку D13 формулу =СУММ(D11:D12) . Объемы перевозимого горючего к потребителю В₃.

В ячейку В14 формулу =СУММПРОИЗВ(B5:D6;B11:D12). Затраты на перевозимое горючее от поставщиков к потребителям. Именно эта величина должна быть минимальной (В14–целевая ячейка) при перевозке горючего. Сами искомые объемы перевозок находятся в ячейках В11:D12. Начальные значения объемов перевозок вводим равные нулю. При выполнении поиска решения в этих ячейках будут оптимальные значения объемов перевозок. После ввода формул и начальных значений таблица примет вид:

рис. 2. Таблица с введенными формулами

1. В ячейке В14 находится формула вычисления затрат на перевозку горючего. Затраты на перевозку должны быть минимальными. Эта ячейка в терминологии Excel будет являться целевой. Для осуществления поиска решения необходимо задать ограничения и условия поиска. Выполняем действия Данные→Поиск решения. На экране появится диалоговое окно Поиска решения.

рис. 3. Диалоговое окно поиска решения

В этой форме необходимо установить целевую ячейку $B$14 минимальному значению. Изменяя ячейки $B$11:$D$12. Для того, чтобы ввести адреса ячеек, нужно щелкнуть на значке справа от поля ввода, и затем в таблице выделить область (группу ячеек). Для того, чтобы задать ограничения, необходимо щелкнуть на кнопке Добавить. После этого появится форма для ввода ограничений.

рис. 4. Диалоговое окно ввода ограничений

В этой форме также для ввода адреса ячейки щелкнуть на значке справа от поля ввода. Знак отношений выбирается из списка, щелкнув на треугольнике справа от поля ввода. Для нашей задачи потребуются следующие ограничения:

$Е$11=$Е$5

$Е$12=$Е$6

$В$13=$В$7

$С$13=$С$7

$D$13=$D$7

Так как при поиске решения может оказаться нецелое число, то добавим в ограничения следующие записи:

$B$11= целое

$C$11= целое

$D$11= целое

$B$12= целое

$C$12= целое

$D$12= целое

После ввода условий поиска и ограничений диалоговое окно примет вид:

рис. 5. Диалоговое окно с введенными ограничениями

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

рис. 6. Окно задания параметров поиска решения

Затем щелкнуть на кнопке Выполнить. После этого на экране появятся результаты поиска. Щелкнуть на кнопке ОК.

рис. 7. Таблица с полученным решением

В ячейках B11:D12 будут находится значения, определяющие оптимальный план перевозок горючего. В нашей задаче затраты на перевозку составят 1020 условных денежных единиц.

Упражнение 7. ПОИСК РЕШЕНИЯ (управление оборотным капиталом)

Задача «Оптимизация портфеля ценных бумаг»

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

В данной задаче используется модель кредитного союза – финансовой организации, которая принимает деньги от своих членов и выдает ссуды под проценты другим членам, выдает кредиты банкам и осуществляет некоторые виды инвестиций. Кроме того, правление может установить несколько своих правил. Эти правила и составляют ограничения для данной задачи.

Входные данные

Ограничения

• Сумма инвестиций в предприятия, торгующие новыми автомобиля, должна быть по крайне мере, в 3 раза больше суммы инвестиций в предприятия, торгующие подержанными автомобилями, поскольку торговля подержанными автомобилями более рискованное дело. Это ограничение записывается так: C5=C6*3.

• Ссуды на автомобили должны составлять 15% от полной суммы портфеля. Это ограничение записывается так: D14=0,15

• Негарантированные ссуды должны составлять не более 25% от суммы портфеля (E8

• Банковские кредиты должны составлять, по крайне мере, 10% от суммы портфеля (E9=0,10)

• Все инвестиции должны быть больше или равны нулю.

Изменяемые ячейки – С5:С9, а цель задачи – максимизировать общий доход, указанный в ячейке D12.

Выходные данные

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

1. Какие статистические функции использовались в практической работе?

2. Как создать статистическую формулу?

3. Назовите аргументы функции СЧЕТЕСЛИМН.

4. В чем смысл инструмента Подбор параметра?

5. Чем является целевая ячейка в надстройке Поиск решения?

Приложение

Задания для самостоятельного выполнения

1. ПОИСК РЕШЕНИЯ (транспортная задача)

Постановка задачи.

Требуется минимизировать затраты на перевозку молока от предприятий-производителей на торговые склады. При этом необходимо учесть возможности поставок каждого из производителей при максимальном удовлетворении запросов потребителей.

В этой модели представлена задача доставки молока с трех молокозаводов на пять региональных складов. Товары могут доставляться с любого завода на любой склад, однако, очевидно, что стоимость доставки на большее расстояние будет большей. Требуется определить объемы перевозок между каждым заводом и складом, в соответствии с потребностями складов и производственными заводов, при которых транспортные расходы минимальны.

ТЕХНОЛОГИЯ ВЫПОЛНЕНИЯ ТИПОВОЙ ЗАДАЧИ

1. Открыть Excel.

2. Занести исходные данные (рис. 1.1):

• С5:G7 (Число перевозок от молокозавода x к складу y:);

• С10:G10 (Потребности складов);

• С12:G14 (Затраты на перевозку от завода x к складу y);

• B12:B14 (Поставки)

Рис. 1.1

1. Произвести форматирование документа (рис. 1.1)

2. В ячейку С8 ввести формулу =СУММ(C5:C7), протащить ячейку С8 до ячейки G8.

3. В ячейку B5 ввести формулу =СУММ(C5:G5), протащить ячейку B5 до ячейки B7.

4. В ячейку C16 ввести формулу =C5*C12+C6*C13+C7*C14, протащить ячейку C16 до ячейки G16.

5. В ячейку B16 ввести формулу =СУММ(C16:G16).

6. Результаты вычислений показаны на рис. 1.2.

Рис. 1.2

1. В меню Данные выбрать Поиск решения

2. Задать условия в окне Поиск решения (рис. 1.3)

Рис. 1.3

1. Щелкнуть по кнопке Выполнить.

2. Результаты задачи показаны на рис. 1.4.

2. ПОИСК РЕШЕНИЯ (управление оборотным капиталом)

Постановка задачи

Требуется найти соотношение акций различного вида в портфеле так, чтобы обеспечить максимальную скорость оборота при заданном уровне риска. В примере используется одноиндексная модель Шарпа. Возможно также использование также метода Марковица.

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

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

Поиск решения позволяет рассмотреть различные варианты размещения средств для получения наибольшего оборота при заданном уровне риска или минимального риска при заданном уровне оборота. При равном 20 процентном вложении оборот составит 16,4, а изменение - 7,1 процента.

ТЕХНОЛОГИЯ ВЫПОЛНЕНИЯ ЗАДАЧИ

1. Открыть Excel.

2. Занести исходные данные в следующие ячейки (рис. 1.21):

• А3:G4;

• B6:E11 (Бета – РезИзм - Доля);

Рис. 1.21

1. Произвести форматирование документа (рис. 1.21)

2. В ячейку F7 ввести формулу =E7*B7, протащить ячейку F7 до ячейки F11.

3. В ячейку G7 ввести формулу =E7^2*C7, протащить ячейку G7 до ячейки G11.

4. В ячейку E13 ввести формулу =СУММ(E7:E11), протащить ячейку E13 до ячейки G13.

5. В ячейку E15 ввести формулу =C3+(C4-C3)*F13.

6. В ячейку G15 ввести формулу =G3*F13^2+G13.

7. Результаты вычислений показаны на рис. 1.22.

Рис. 1.22

1. В меню Данные выбрать Поиск решения

2. Задать условия в окне Поиск решения (рис. 1.24)

Рис. 1.24

1. Щелкнуть по кнопке Выполнить

2. Результаты задачи показаны на рис. 1.25

Рис. 1.25

3. Подбор параметра

Вы хотите положить деньги в банк под 4,5% и получить ровно 1000 руб. по истечении года. Необходимо определить сумму вклада.

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

Если в банк положить Х руб. под 4,5% годовых, то в конце года банк выплатит (1+0,045) * х = 1,045 * х. Поскольку в конце года по условию задачи нужно получить сумму 1000 руб., то для решения задачи требуется решить уравнение 1,054 * х = 1000.

Данные расположены на рабочем листе, как показаны ниже

Средство Подбор параметра найдет решение, равное 956,94, и поместит это значение в ячейку В2.

4. Поиск решения (производственная задача)
Цех может производить стулья и столы. На производство стула идет 5 единиц материала, на производство стола - 20 единиц (футов красного дерева). Стул требует 10 человеко-часов, стол - 15. Имеется 400 единиц материала и 450 человеко-часов. Прибыль при производстве стула - 45 долларов США, при производстве стола - 80 долларов США. Сколько надо сделать стульев и столов, чтобы получить максимальную прибыль?

Ответ: 24 стула и 14 столов, прибыль 2200$

11