Построение таблицы значений в зависимости от одного аргумента

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

Процент	=x25

Ниже основной части таблицы введите 5-8 вариантов премии (5%, 10%, 15% и т.д.), как это показано выше. Над колонкой, где должны формироваться значения, запишите формулу, ссылающуюся на итоговую сумму в вашей таблице. В примере в этом месте стоит формула: =Х25. Выделите обе колонки. Войдите в пункт Данные - Таблица. В появившемся окне в пункте Ячейка ввода столбца укажите адрес ячейки, где расположен процент премии. Проанализируйте полученный результат.

Построение таблицы значений в зависимости от двух аргументов

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

Необходимо подготовить в свободном месте таблицы следующий фрагмент (ссылка на итоговую формулу записывается в левом верхнем углу).

=Х25

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

Вопросы к лабораторной работе 10

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

 

Лабораторная работа 11

ЦЕЛЬ: применение сценариев и отчетов

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

1. Загрузите таблицу, созданную в лабораторной работе 8 (структурирование и итоги).

1. С помощью Диспетчера сценариев проанализируйте, как изменятся общие начисления, общие удержания и итоговые выплаты при различных вариантах изменения процентов премии, отчисления в пенсионный фонд и подоходного налога. Для этого создайте 3 сценария (“Оптимистический”, “Пессимистический” и “Наиболее вероятный”).
1. Для создания сценария следует вызвать Диспетчер сценариев, выбрав команду Сервис- Сценарии, и щелкнуть по кнопке Добавить.

1. После этого сценарию следует присвоить имя и указать изменяемые ячейки. Обратите внимание, что несмежные ячейки можно выбрать мышью при нажатой клавише Ctrl или ввести их адреса с клавиатуры, разделяя символом “; ”.

1. Укажите набор значений изменяемых параметров для первого сценария и щелкните на кнопке Добавить для формирования следующего сценария.

1. Когда последний сценарий будет сформирован, щелкните на ОК и вернитесь в окно Диспетчера сценариев

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

1. С помощью Диспетчера сценариев научитесь редактировать и удалять созданные сценарии.

1. Создайте еще несколько сценариев и оцените их, используя кнопку Показать в окне Диспетчера сценариев.

1. Оцените итоги сценариев с помощью сводной таблицы. Для этого в окне Диспетчера сценариев выберите Итоги, тип отчета - Сводная таблица сценария. Сравните итоговую таблицу сценария со сводной таблицей.

1. Сохраните рабочую книгу.

1. Сдайте работу преподавателю.

Вопросы к лабораторной работе 11

• Перечислите отличия Диспетчера сценариев от таблиц значений.
• Как применить Диспетчер сценариев (задать переменные и интересующие значения)?
• В каких режимах можно просмотреть результат применения сценариев?
• Сформулируйте основные отличия отчета в виде итоговой таблицы от отчета в виде сводной таблицы. Какой вариант представления отчета является более наглядным?

Лабораторная работа 12

ЦЕЛЬ: основные приемы построения сводных таблиц.

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

• продумать, на основе каких данных будет формироваться сводная таблица. Это может быть одна таблица, несколько таблиц на разных листах одной рабочей книги или несколько таблиц из разных рабочих книг;
• продумать форму вывода результатов;
• выполнить команду Данные-Сводная таблица для вызова Мастера сводных таблиц;.
• указать источник данных и интервал, содержащий данные (область вашей таблицы с данными целесообразно выделить до начала работы с Мастером);
• сформировать структуру сводной таблицы. Для этого из предлагаемого списка полей следует мышью выбирать требуемые поля и "перетаскивать” их в нужное место схематически изображенной структуры. Если поле установлено ошибочно, это легко исправить, “вытащив” его за пределы схемы;
• указать поля данных и функции, с помощью которых эти поля будут обрабатываться. По умолчанию будет установлена функция СЧЕТ. Для изменения функции следует щелкнуть дважды на поле данных и выбрать из предлагаемого списка нужную функцию. Чаще всего используется функция суммирования;
• нажать кнопку Закончить для построения сводной таблицы. Она будет выведена на новом рабочем листе.

Если необходимо вернуться к Мастеру сводных таблиц, можно воспользоваться появившейся панелью инструментов (Запрос и сведение).

1. Загрузите таблицу, созданную в лабораторной работе 8 (структурирование и итоги).

1. Дополните таблицу колонкой “Филиал” и заполните эту колонку, исходя из предположения, что у данной фирмы имеются отделения в Москве и С.-Петербурге.

1. Введите в таблицу еще 5-10 строк, содержащих произвольные данные по работникам.

1. Постройте сводную таблицу суммарных выплат по филиалам, внутри филиалов - по подразделениям, внутри подразделения - по фамилиям. Представьте результаты первоначально в абсолютных значениях, а затем в виде процента от итогового значения. Для перехода к процентному представлению щелкните дважды на “поле данных” и установите в появившемся окне Суммирующая функция - СУММА, Параметры - Показать данные в виде - ПРОЦЕНТ ИТОГА.

1. Научитесь изменять формат представления данных в сводной таблице. Для изменения формата следует в диалоге Мастера сводных таблиц дважды щелкнуть мышью на “поле данных” и нажать кнопку Число. В открывшемся окне можно установить любой известный формат.

1. Меняя расположение полей в структуре сводной таблицы (Страница, Строка, Столбец), добейтесь, чтобы данные по каждому филиалу выводились на отдельной странице и содержали списки подразделений, а подразделения - фамилии сотрудников и данные о зарплате.

1. Незначительные изменения сводной таблицы можно производить без вызова Мастера сводных таблиц. Любое поле (они условно показаны серым цветом) можно “ухватить и переместить” мышью. Научитесь менять иерархию уровней в вашей сводной таблице. Измените структуру так, чтобы на отдельной странице выводились бы данные по различным подразделениям.

1. Самостоятельно на отдельных листах составьте:

• сводную таблицу, в которой для каждого работника фиксируется “Всего начислено”, “Всего удержано”, “Выплатить”;
• сводную таблицу, в которой подсчитывается количество сотрудников в каждом подразделении;
• сводную таблицу, в которой приводятся итоговые данные о выплатах по филиалам в разрезе подразделений. При этом следует рассмотреть различные варианты представления данных - в абсолютных величинах, в процентах к итогам, в процентах строк и процентах столбцов.

1. Сохраните рабочую книгу вместе с полученными сводными таблицами.

1. Сдайте работу преподавателю.

Вопросы к лабораторной работе 12

• Сформулируйте назначение и принципы построения сводной таблицы.
• На основе каких данных можно построить сводную таблицу?
• Что такое список?
• Как включить в сводную таблицу отдельные поля из списка?
• Как удалить из сводной таблицы неверно включенные поля?
• Как разные значения некоторого поля разместить на разных страницах?
• Поясните назначение свободных полей Страница, Строка, Столбец, Данные при работе с Мастером сводных таблиц.
• Как из готовой сводной таблицы вернуться к диалогу с Мастером сводных таблиц? Когда это бывает целесообразно?
• Как задать функцию, с помощью которой будут обрабатываться данные в сводной таблице?
• Какие поля могут быть обработаны функциями?
• Перечислите и поясните функции, которые могут использоваться при обработке данных в сводной таблице.
• Перечислите и поясните итоговые функции сводной таблицы
• В чем отличие функций подведения Итога по строке, По столбцу, Суммарного итога?

 

Лабораторная работа 13

ЦЕЛЬ: работа со списками. Поиск и выборка записей по заданному критерию, сортировка записей.

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

1. Загрузите таблицу, созданную в предыдущих лабораторных работах.

1. Придайте таблице статус списка. Для этого должно соблюдаться несколько условий:

а) каждый столбец должен содержать однородную информацию;

б) одна или две верхние строки должны содержать заголовки столбцов;

в) в списке нет пустых строк и столбцов;

г) список отделен от других данных хотя бы одной пустой строкой и столбцом.

1. Постройте форму данных и введите несколько новых записей. Для этого щелкните мышью в области списка и выполните команду Данные- Форма.

1. С помощью формы данных просмотрите все строки, значение зарплаты в которых превышает 10 000 (Критерии). Задайте еще несколько условий просмотра строк.

1. Выполните сортировку списка по нескольким полям. Обратите внимание, правильно ли MS Excelинтерпретировал ваш список. Для этого нужно щелкнуть мышью на любой ячейке внутри списка и выполнить команду Данные-Сортировка.

1. С помощью пользовательского автофильтра (Данные- Фильтр - Автофильтр) извлечь все данные:

• по бухгалтерии;
• по лицам, чьи фамилии, начинаются с буквы “Б”, до тех, чьи фамилии начинаются с “М”;
• по лицам, в фамилиях которых вторая буква “А”;
• по сотрудникам транспортного отдела, чьи зарплаты заключаются в заданном диапазоне;
• по сотрудникам, чей возраст от 20 до 25 лет, а зарплата меньше 10000 или больше 100 000.

1. Скопируйте отфильтрованные по одному из вышеперечисленных критериев данные в новый рабочий лист.

1. Удалите все пользовательские автофильтры.

1. С помощью усиленного фильтра (Данные -Фильтр -Усиленный фильтр) осуществите поиск данных по критериям, перечисленным в пункте 6.

1. Самостоятельно сформулируйте несколько критериев и осуществите по ним поиск данных в списке. Каждый критерий записывайте отдельно.

1. Найдите и извлеките в свободное пространство таблицы фамилии и возраст сотрудников, зарплата которых превышает среднее значение по всему списку.

1. Сохраните рабочую книгу со всеми критериями поиска.

1. Сдайте работу преподавателю.

Вопросы к лабораторной работе 13

• Перечислите требования к спискам.
• Как осуществляется поиск информации в списке?
• Перечислите возможности автофильтра.
• Сформулируйте, в чем заключаются отличия автофильтра от усиленного фильтра.
• Как формируется критерий поиска при использовании усиленного фильтра?
• Как можно использовать вычисляемый критерий поиска?
• Как копировать данные, полученные в результате применения автофильтра?
• Как можно скопировать данные, полученные с помощью усиленного фильтра, в свободное пространство рабочего листа (или на новый рабочий лист).

 

Лабораторная работа 14

ЦЕЛЬ: применение макросов.

При использовании часто повторяющихся операций для удобства пользователя их можно оформить в макрос и вызывать нажатием одной кнопки. Создать макрос можно двумя способами: написать программу на языке программирования VISUAL BASIC или записать последовательность действий пользователя с помощью макрорекордера. Для вызова макрорекордера используется пункт меню Сервис-Запись макро, в которой следует указать имя макроса и при необходимости назначить ему “горячие” клавиши или пункт меню. После нажатия кнопки ОК на экране появится панель Останов и все дальнейшие действия пользователя будут записаны в макрос. Для прекращения записи макроса воспользуйтесь кнопкой Остановить макрос или Сервис-Запись макроса- Остановить запись.. Для запуска макроса можно использовать “горячие” клавиши (если они были установлены) или назначить макросу кнопку панели инструментов, кнопку на поле таблицы (диаграммы) или рисованный объект.

1. Загрузите рабочую книгу, в которой содержится ведомость заработной платы за январь, февраль, март 1996 г.

1. С помощью пункта меню Правка- Переместить/Скопировать скопируйте эту рабочую книгу в новые рабочие книги. Создайте таким образом “Ведомость заработной платы за январь, февраль, март 1995 г.” и “Ведомость заработной платы за январь, февраль, март 1994 г.“

1. Сохраните все рабочие книги и закройте их.

1. В новой рабочей книге (menu.xls) с помощью больших кнопок на экране (Вид- Панели инструментов- Формы- Кнопка) создайте меню, позволяющее реализовать следующие функции.

• загрузить таблицу ведомостей заработной платы за 1994 г.;
• загрузить таблицу ведомостей заработной платы за 1995 г.;
• загрузить таблицу ведомостей заработной платы за 1996 г.;
• сохранить соответствующую таблицу и выйти;
• выйти без сохранения;
• скопировать соответствующую таблицу на дискету;
• загрузить соответствующую таблицу с дискеты;
• защитить рабочую книгу (кроме окладов и процента премии);
• обнулить зарплату;
• быстро переместиться в начало (верхний левый угол) таблицы.

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

1. Сохраните рабочую книгу menu.xls.

1. “Уберите” лишние панели инструментов, строку формул, строку состояния и сохраните новую рабочую область menu.xlw.

1. Установите режим автоматической загрузки вашего меню при запуске MS Excel.

1. Сдайте работу преподавателю.

1. Обязательно снимите п.9.

Вопросы к лабораторной работе 14

• Что такое макрос?
• Как можно написать макрос?
• В чем смысл макрорекордера?
• Поясните назначение кнопок Стоп и Пауза.
• Как установить автоматическое открытие вашей рабочей книги при загрузке MS Excel?
• Перечислите объекты, которым может быть назначен макрос.
• Как назначить макрос горячим клавишам?
• Как назначить макрос кнопке панели инструментов?
• Как отредактировать изображение на кнопке панели инструментов?
• Как создать кнопку в пространстве рабочего листа?
• Как назначить кнопке в пространстве рабочего листа макрос?

 

Лабораторная работа 15

ЦЕЛЬ: изучение следующих средств MS Excel:

• импорт данных в таблицы MS Excel из файлов баз данных типа DBF (СУБД dBASE, FoxPro, Clipper);
• импорт данных в таблицы MS Excel из текстовых файлов;
• формат ячеек типа “дата”;
• статистические функции;
• средства выравнивания (сглаживания) рядов распределения на примере динамического ряда;
• средства прогнозирования.

Процессор электронных таблиц MS Excel может обмениваться данными с другими приложениями (электронными таблицами, текстовыми редакторами, системами управления базами данных). Это значит, что он способен читать файлы других систем и правильно их интерпретировать, а также выводить данные из своих таблиц не в своем формате, а в формате других приложений. Особенно ценно то, что такое взаимодействие возможно не только с приложениями, которые разработаны Microsoft, но и другими производителями программного обеспечения. Совершенно очевидно, что обмен данными возможен не со всеми приложениями, а только с теми, для которых в MS Excel есть средства импорта и экспорта данных. Перечень возможных форматов данных выводится в специальном разделе окна открытия файлов (самая нижняя часть окна). По умолчанию подразумевается формат XLS. Аналогично выбирается формат при сохранении файлов. При этом если файлу при записи назначается не XLS-формат, то таблица будет преобразована в нужный формат автоматически. Имейте в виду, что расширение файла и формат файла - разные категории. Желательно чтобы они не противоречили друг другу, так как в противном случае при открытии файла его формат может не распознаться правильно.

Задача построена на исследовании динамики курса американского доллара к рублю в 1993 г. При этом исходные данные за первое полугодие представлены как DBF-файл, а данные за второе полугодие - как текстовый файл. Записи файлов состоят из двух полей: календарной даты и курса доллара на эту дату. Во втором файле между датой и курсом стоит запятая. Необходимо прочесть эту информацию, объединить их в одну таблицу, рассчитать на ее основе несколько статистических показателей, построить гистограмму изменения курса в течение года. Затем нужно произвести обработку ряда различными методами с целью выявления закономерности в изменении курса доллара и сделать прогноз на несколько позиций вперед.

Данная лабораторная работа предполагает знание Мастера диаграмм. Информация из таблиц других лабораторных работ в данной работе не используется.

1. Откройте файл kurs93.dbf^* и проанализируйте информацию, которая в нем содержится. Обратите внимание, что данные заканчиваются июнем 1993 г.
2. Откройте (не закрывая предыдущий) файл kurs93.txt^**. При этом будет вызван мастер текстов, который проведет с вами диалог, уточняющий:

• вид разделителя между элементами (укажите -запятая);
• тип полей (для первого укажите -дата, для второго - основной формат).

Проанализируйте информацию этого файла. Здесь находятся данные за второе полугодие.

1. Создайте новую книгу. Используйте инструмент в виде чистого листа. В ячейку А1 введите текст “Дата”, а в ячейку B1 - текст “Курс”.
2. Выделите у первого файла все строки без заголовка и скопируйте их через буфер обмена в новую книгу. Затем аналогичным образом скопируйте данные из второго файла и добавьте их в конец таблицы в новой книге. Внимательно проанализируйте полученный результат. Сохраните новую книгу в вашем каталоге, а исходные файлы закройте.
3. Используя статистические функции СРЗНАЧ, ДИСП, СТАНДОТКЛОН, МАКС, МИН, вычислите среднее значение, дисперсию, стандартное отклонение, максимальное и минимальное значения курса в 1993 г. Ознакомьтесь с другими статистическими функциями по справке.
4. Постройте на новом листе график в виде гистограммы во весь экран. При этом не включайте в него три-четыре последних наблюдения. Они нам понадобятся для анализа точности прогнозирования курса валюты. Дайте всем его элементам названия. Гистограмму окрасьте в неяркий цвет, чтобы линии, которые позже будут проводиться, были хорошо видны.
5. Выделите щелчком мыши гистограмму, а затем нажмите правую клавишу мыши. В появившемся меню вызовите пункт Вставить линии тренда. Обратите внимание, что возможно выравнивание ряда различными методами (тип тренда). Для анализа получаемых аналитических зависимостей отобразите уравнения на графике. Опробуйте каждый из способов выравнивания и визуально анализируйте насколько адекватен выбранный вами метод динамике курса доллара в 1993 г.

1. Выделите тренд и нажмите правую клавишу мыши. Отформатируйте тренд. Укажите цвет линии, ее толщину и вид. Выведите на диаграмму уравнение тренда и значение R^2. . Сделайте прогноз на 3-4 позиции вперед. Диаграмма с линией тренда должна иметь следующий вид:

	Таблица 1			Таблица 2
Дата	Курс валюты, руб.		Дата	Курс валюты, руб
14.01.93			02.07.1993
21.01.93			09.07.1993
28.01.93			16.07.1993
05.02.93			23.07.1993
12.02.93			30.07.1993
19.02.93			06.08.1993
26.02.93			13.08.1993
12.03.93			20.08.1993
19.03.93			27.08.1993
26.03.93			03.09.1993
02.04.93			10.09.1993
09.04.93			17.09.1993
15.04.93			24.09.1993
23.04.93			01.10.1993
30.04.93			08.10.1993
14.05.93			15.10.1993
21.05.93			22.10.1993
28.05.93			29.10.1993
04.06.93			05.11.1993
08.06.93			12.11.1993
15.06.93			19.11.1993
25.06.93			26.11.1993
			02.12.1993
			09.12.1993
			16.12.1993
			23.12.1993

 

Вопросы к лабораторной работе 15

• Для каких приложений в MS Excel имеются средства импорта и экспорта данных?
• Как преобразовать текстовый файл в формат электронных таблиц?
• Как можно прогнозировать тенденции с использованием графиков?
• Перечислите, какие методы выравнивания ряда применимы в диаграммах.

Лабораторная работа 16

ЦЕЛЬ: освоение математических функций для решения задач линейной алгебры

В MS Excel при выполнении действий над векторами и матрицами используется понятие массива. Под массивом понимается некоторый диапазон ячеек, для которых установлены единые правила обработки, например формулы расчета отдельных элементов. Одномерный массив может рассматриваться как вектор, а двумерный - как матрица. Формулы для массива вводятся несколько иначе, чем для обычной ячейки. Отличие состоит в двух обстоятельствах:

• перед вводом формулы диапазон ячеек должен быть выделен;
• ввод формулы завершается одновременным нажатием трех клавиш Ctrl+Shift+Enter.

Массивы передаются в качестве аргументов математическим функциям. В некоторых случаях результатом выполнения функции также является массив. Пример - умножение матриц.

1. В справке найдите раздел Математические функции, просмотрите список всех функции и подробно ознакомьтесь с функциями МОПРЕД, МУМНОЖ, МОБР. Кроме того, отдельно найдите функцию ТРАНСП и изучите к ней инструкцию.
2. Научитесь формировать на рабочем листе массивы. Для этого выделите диапазон A1:C3, размером 3х3 ячейки, в строке ввода наберите число 1 и нажмите одновременно клавиши Ctrl+Shift+Enter. Все выделенные ячейки получат значение, равное 1. Аналогично сформируйте другой массив со значениями, равными 2.
3. Научитесь формировать массивы на основе формул. Для этого выделите на свободном пространстве диапазон, размером 3х3 ячейки, в строке ввода наберите формулу =A1:C3*5 и нажмите одновременно клавиши Ctrl+Shift+Enter. Обратите внимание, что формула автоматически заключилась в фигурные скобки, что указывает на зону ее действия - массив, а все ячейки нового массива получили значения в 5 раз больше ячеек первого массива. Данное действие можно рассматривать как умножение матрицы на скаляр.
4. Самостоятельно сделайте сложение и вычитание матриц. При этом результатам дайте заголовки, чтобы было легко определить происхождение матриц.
5. Используя функцию ТРАНСП, транспонируйте одну из матриц.
6. Задайте имена всем матрицам (Вставка-Имя-Определить) и повторите операции умножения матрицы на скаляр, сложения и вычитания матриц. В дальнейшей работе рекомендуется пользоваться именами матриц вместо диапазонов.
7. Умножение матриц выполняется функцией МУМНОЖ. Функции передается два массива, а результат формируется в выделенных ячейках в виде нового массива. Умножьте две матрицы A*B=C при следующих значениях матриц А и В. Обратите внимание, что при этом будет получена матрица размера 3х4.

1. 
   Умножьте матрицу А на вектор-столбец, а затем вектор-строку на матрицу А. Значения векторов задайте самостоятельно.

1. Транспонируйте матрицу С. Результат разместите в свободном пространстве вашего листа. Дайте заголовок полученной матрице. Напоминаем, что при транспонировании первая строка становится первым столбцом, вторая строка - вторым столбцом и т.д.

1. Обратите матрицу: . Для этого воспользуйтесь функцией МОБР, которой в качестве аргумента передается матрица D, а результат записывается в предварительно выделенные ячейки массива. Для проверки правильности обратите обратную матрицу - вы должны получить исходную матрицу D. Дайте всем матрицам заголовки.

1. Вычислите определитель матрицы D (функция МОПРЕД).

1. Решите систему из двух линейных уравнений по формулам
   Крамера , где x и y находятся по формулам:

1. Решите систему линейных уравнений, используя функции обращения матрицы и умножения матрицы на вектор. В общем случае система AX=B решается путем домножения слева обеих частей на матрицу, обратную A. Таким образом, X=A^-1B.
   

1. Решите систему уравнений модели В.Леонтьева“затраты-выпуск” X=AX+Y. Решение записывается следующим образом:
   X=(E-A)^-1Y,
   где E - единичная матрица;
   А - матрица коэффициентов прямых затрат (0<=a_ij<1);
   Y -вектор конечного продукта;
   Х -вектор выпуска продукции.

Вопросы к лабораторной работе 16

• Сформулируйте понятие “массив” MS Excel.
• Сформулируйте правила построения массивов в MS Excel.
• Перечислите основные свойства массива.
• Перечислите основные функции MS Excel для работы с матрицами.
• Каким образом в функциях, предназначенных для решения задач линейной алгебры, используются имена матриц?
• Как присвоить начальные значения массиву?
• Как с помощью массивов решить систему линейных уравнений?
• С использованием каких функций можно обратить и транспонировать матрицу?
• Как найти скалярное произведение матриц?

 

 

Лабораторная работа 17

ЦЕЛЬ: освоение приемов решения задач оптимизации (линейного и нелинейного программирования).

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

• для каждой переменной (неизвестной) следует отвестиодну ячейку и определить для нее имя. Желательно разместить эти ячейки рядом, а в клетках сверху записать их имена. Тогда процедура определения имен через пункт Вставка-Имя будет проще, а анализ исходных данных - более наглядным. Для еще большей наглядности можно закрасить ячейки каким-либо цветом.В этих ячейках будет размещен результат решения задачи. Проблема здесь состоит в том, что искомые переменные в математике чаще всего называют Х₁, Х₂, Х₃ и т.д. Многие стремятся дать такие же имена ячейкам, где они будут храниться. Однако сделать это нельзя, так как в таблице существуют ячейки с адресами Х1, Х2, Х3. Поэтому следует использовать другие имена, например, ХХ1, ХХ2, ХХ3 и так далее, или буквы русского алфавита(что имеет свои достоинства и недостатки). Если же решается конкретная экономическая задача, где в качестве неизвестных выступают, например, виды продукции, то неизвестным можно присвоить имена в виде сокращенных названий видов продукции;
• отвести ячейку для критерия оптимальности и записать его в виде формулы, ссылаясь не на адреса, а на имена ячеек, определенных на предыдущем этапе, например: =xx1+4*xx2-3*xx3+2*xx4-xx5. Здесь в качестве имен использованы латинские буквы. Ячейке, содержащей критерий оптимальности, также можно присвоить имя. Особенно это удобно при решении экономической задачи. Например, ячейка может называться “Прибыль” или “Затраты” и т.д., то есть то, что подлежит максимизации или минимизации;
• отвести на каждое ограничение одну ячейку (желательно разместив их друг под другом). В эти ячейки следует ввести левые части ограничений в виде формул. Знак сравнения и правая часть ограничения задаются позже. Например: =xx1+3*xx2+xx3-xx4-2*xx5.
• вызвать инструмент Поиск решения (пункт Сервис). При этом на экран выводится окно, в котором будет нужно:

1. указать ячейку, где находится критерий оптимальности;
2. указать диапазон ячеек, где будет сформирован результат (ячейки переменных);
3. указать ячейки, где записаны ограничения, и задать для каждого ограничения операцию сравнения и правую часть;
4. как правило, также задаются ограничения на неотрицательность переменных. Для удобства их целесообразно ввести в виде одного ограничения, указав диапазон, где записаны переменные, а не перечислять переменные по одной;
5. изменить, если это необходимо, параметры расчетов (точность, время расчета, количество итераций и др.). На этапе изучения, кроме точности результата, ничего менять не рекомендуется;
6. нажать кнопку Выполнить;
7. оформить полученное решение в виде отчета. При решении реальных задач целесообразно сформировать и другие виды отчетов (они перечислены в меню). Каждый отчет записывается на новый лист;
8. проанализировать результат, при необходимости внести поправки в ограничения или критерий оптимальности и повторить расчеты.

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

1. В справке найдите раздел “ Общие сведенияоб инструменте Поиск решения” и изучите его. Потратьте на это не менее 10-15 минут. Скорее всего, вам не все будет понятно. Старайтесь уловить общий смысл изучаемого материала.

1. На чистом листе сформируйте исходные данные для решения следующей задачи:

Ответ: Х=(0,0,1,0,1) при F=-4.

1. Войдите в пункт Сервис -Поиск решения и укажите целевую ячейку, диапазон ячеек с переменными и три ограничения. Найдите решение и проанализируйте его. Сохраните файл на диске.
2. Решите более сложную задачу.

.

Если вы решите задачу верно, то значение целевой функции F=53,125.

1. Вновь найдите в справке раздел “Общие сведенияоб инструменте Поиск решения” и изучите его с учетом полученных знаний. Вы должны теперь понять гораздо больше в этом материале. Сделайте необходимые записи в свои тетради об инструменте Поиск решения. Помните, что этот инструмент является важнейшим в тех случаях, когда нужно найти наилучшее решение при ограниченных возможностях. Он работает на стыке трех отраслей знаний: математики, экономики и информационных технологий.

Вопросы к лабораторной работе 17

• Как подключить инструмент Поиск решения?
• Как задать критерий поиска решения?
• Как сформировать целевую функцию, для которой будет осуществляться поиск решения?
• Сформулируйте правила задания ограничений на значения переменных при оптимизации.
• Какие варианты критериев доступны в MS Excel?