Приемы проектирования электронных таблиц
Цель работы
Получить представление о назначении электронных таблиц (ЭТ), изучить основные элементы ЭТ и этапы конструирования рабочих книг, овладеть практическими знаниями и навыками по проектированию, созданию и эксплуатации ЭТ.
Теоретическое введение
ЭТ предназначены для хранения и автоматизированной обработки информации, представленной в табличной форме (в виде двумерных массивов, состоящих из строк и столбцов). Программные средства для проектирования электронных таблиц называются табличными процессорами. В качестве базового представителя программ данного класса выбран MS Excel в силу своего массового распространения в России.
Основные элементы электронных таблиц:
· ячейка – элемент для ввода и хранения данных или вычисляемых значений; конструктивно ячейка образована пересечением других элементов: строки и столбца и имеет идентифицированный адрес (A1, D23, …);
· строка – элемент с заголовком (1, 2, 3, … 16384) определенной высоты;
· столбец – элемент с заголовком (A, B, C… AA, AB, …) определенной ширины;
· активная ячейка – ячейка, выделенная жирной рамкой и приготовленная к операциям;
· поле имени – элемент, содержащий имя ячейки или другого объекта (элемента диаграммы, графического объекта и т.п.), с его помощью можно изменить имя ячейки на более удобное или же быстро перейти к нужной ячейке;
· диапазон ячеек или интервал – совокупность ячеек, выделенная для определенных действий и идентифицируемая именем (A1 .. C3, A .. C, 1 .. 14, …);
· строка состояния – индикатор сведений о команде или операции;
· абсолютная адресация – адрес элемента, содержащего постоянное (неизменяемое значение), например, $H$2; $A1; D$3;
· относительная адресация – адрес элемента, содержащего переменное (изменяемое значение), например, H2 или D7.
Типы данных электронных таблиц:
· текст – любая последовательность символов, применяемых для заголовков и комментариев;
· число – числовая константа;
· дата – форма для отображения календарных значений;
· формула – выражение, состоящее из числовых (или иных) величин и операций (арифметических, логических и др.), например, =A5/(H8+13);
· функция – встроенная формула для часто встречающихся вычислений, например, =СУММ(A1:A6), MAKC(D4:G4).
Правильный выбор и задание определенного типа данных является важнейшим шагом проектирования ЭТ, так как позволяет не только наглядно отобразить значение входной величины, но и точнее применять эти значения в дальнейшем при использовании встроенных функций.
Основные типы документов электронных таблиц:
· рабочий лист – отдельная таблица с именем, которое отображается на ярлычке листа (по умолчанию Лист 1, Лист 2, …);
· рабочая книга – совокупность рабочих листов, связанных общей задачей (в MS Excel сохраняется в файлах со стандартным расширением xls);
· диаграмма – графическое изображение связей между числовыми данными ЭТ;
· макротаблица (макрос) – последовательность команд для автоматического исполнения часто применяемых пользовательских операций и т.п.
Основные интерфейсные элементы окна MS Excel:
· строка меню;
· панели инструментов;
· строка ввода данных;
· окно адреса активной ячейки;
· ярлыки листов рабочей книги и кнопки навигации по ним;
· линейки прокрутки.
Практическая часть
Постановка задачи
По данным таблицы 4 рассчитать прибыль от продажи изделий за квартал, итоговую прибыль и выполнить графический анализ вклада в общую прибыль предприятия каждого изделия (с учетом коэффициента реализации, равного 0,2).
Таблица 4 – Исходные данные задачи «Реализация изделий»
| Наименование | Выпуск, шт. | Цена, р. | ||
| Январь | Февраль | Март | ||
| Изделие 9/001 | 299,00 | |||
| Изделие 9/002 | 199,00 | |||
| Изделие 9/003 | 150,00 |
Ход работы
1. Открываем MS Excel и сохраняем пустую рабочую книгу в свою папку под именем "laba_5.xls".
2. Разбиваем рабочую книгу на отдельные листы с необходимыми именами; у нас листы называются "Исходные данные" и "Расчет".
3. Заносим исходные данные на лист "Исходные данные" и решаем вопрос о типе данных, их формате и расположении (см. рисунок 17). Оформление таблицы производим, используя пункт меню Формат/Формат ячеек, вкладки Число и Выравнивание. Для ввода наименований изделий и названий месяцев используем автозаполнение.
Рисунок 17 – Рабочая книга "laba_5.xls". Лист "Исходные данные"
4. Скопируем таблицу на лист "Расчет" и добавим столбцы "Всего", "Выручка" и "Прибыль" (см. рисунок 18).
Рисунок 18 – Рабочая книга "laba_5.xls". Лист "Расчет"
5. Данные в этих столбцах рассчитаем по формулам:
Всего=Сумма(Январь,Февраль,Март);
Выручка=Всего*Цена;
Прибыль= Выручка *Коэффициент реализации.
Значение итоговой прибыли по всем изделиям рассчитывается в ячейке H8 с помощью операции автосуммирования значений в ячейках H5:H7 или при использовании функции =СУММ(H5:H7). Формулы для расчетов представлены на рисунке 19.
Рисунок 19 – Расчетные формулы
6. Для проведения графического анализа, показывающего вклад изделий в суммарную прибыль предприятия, построим диаграмму, пользуясь услугами Мастера диаграмм. Для нашего примера на рисунке 20 построена круговая диаграмма, на которой изображена процентная доля вклада каждого изделия в общую сумму прибыли.
Рисунок 20 – Диаграмма "Прибыль от реализации изделий"
На основании графического анализа формулируем вывод о том, что наибольший вклад (74%)в общую прибыль предприятия вносит изделие 9/001; наименьший вклад (8%) – изделие 9/003; средний вклад (18%) – изделие 9/002.
Варианты заданий для самостоятельной работы
Задача 1. «Меховое ателье»
Постановка задачи: в ателье производят плановый поквартальный пошив и реализацию 4-х наименований изделий; требуется рассчитать общую прибыль ателье за год и оценить вклад в общую прибыль каждого изделия.
Исходные данные: наименования изделий; количество пошитых изделий за каждый квартал; отпускная цена; норма чистой прибыли (коэффициент реализации).
Задача 2. «Книжная база»
Постановка задачи: на базе реализуют продукцию по 5-ти видам полиграфических изданий; требуется рассчитать общую прибыль базы за месяц и оценить вклад в общую прибыль каждого вида изданий.
Исходные данные: наименования видов изданий (книги, журналы и т.п.); количество и цена реализованных изданий по каждому виду еженедельно; норма чистой прибыли (коэффициент реализации).
Задача 3. «Склад»
Постановка задачи: на складе хранят и реализуют по мере поступления заявок от потребителя 5 видов групп товаров; требуется рассчитать общую прибыль склада за текущий месяц и оценить вклад в общую прибыль каждого вида товара.
Исходные данные: наименования видов групп товаров (аппаратура, мебель и т.д.) и их цена; количество реализованных групп товаров за каждую неделю текущего месяца; норма чистой прибыли (коэффициент реализации).
Задача 4. «Банк»
Постановка задачи: банк реализует 5 видов валютных средств; требуется рассчитать общий доход банка за текущий месяц и оценить вклад в общую прибыль каждого вида валютных средств.
Исходные данные: наименования видов валютных средств (DM нем., $ англ. и т.д.); курс валюты; количество реализованных валютных средств за каждую неделю текущего месяца; норма чистой прибыли
Практическая работа 6
РешениЕ задач оптимизации
Цель работы
Получить общие представления о задачах оптимизации, освоить основные приемы и сервисные надстройки табличных процессоров для оптимизации решений и анализа данных, овладеть практическими знаниями и навыками поиска оптимальных решений в электронных таблицах, решить типовые задачи оптимизации с помощью инструментов табличных процессоров.
Теоретическое введение
В электронных таблицах (ЭТ), помимо базовых операций с данными, можно также эффективно обрабатывать и исследовать результаты, то есть решать задачи оптимизации и анализа решений.
Оптимизационными называются задачи, в которых при заданном множестве вариантов значений исходных параметров, изменяющихся во времени, определяется оптимальный вариант по какому-либо критерию (критериям). Применительно к экономическим явлениям оптимизационными задачами называются ситуации поиска значений, оптимальных для определенных выходных характеристик моделей, которые описывают исследуемые экономические явления. Классическими моделями оптимизации с одним критерием оптимальности являются задачи максимизации общей прибыли предприятия или минимизации общих затрат на производство.
В табличном процессоре MS Excel используют два способа оптимизации и анализа данных:
· решение экономических задач типа "что надо, чтобы" методом "Подбор параметра";
· анализ экономических решений по типу "что будет, если" с помощью технологии "Поиск решения".
Оптимизация данных методом "Подбор параметра" применяется, как правило, для решения задач, в которых исследуется выходное значение, наиболее значимое для нас (целевая функция), за счет изменения определенного параметра. В этом случае на изменяемый параметр не вводятся ограничения, а величина оптимального значения устанавливается постановщиком задачи. Таким способом можно, например, "улучшить" прибыль до желаемого фиксированного значения за счет изменения объема выпуска или цены продажи до неограниченной величины. Как следствие, оптимизация по технологии "Подбор параметра" ограничена и далека от реальности.
Оптимизация типа "Поиск решения" проводится методом "от обратного". В этом случае для изменяемых параметров можно устанавливать границы (ограничения), в которых они могут изменяться или представляют экономический интерес, при этом поиск оптимального решения ведется либо по критериям экстремальности (минимума и максимума), либо по точному значению, задаваемому постановщиком. К оптимизации "Поиск решения" можно свести многие типовые задачи линейного программирования, например, поиск оптимального состава сырья или составления оптимальных планов производства при ограниченных ресурсах и др. Оптимизация методом "Поиск решений" в отличие от оптимизации методом "Подбор параметра" многовариантна, более гибкая по структуре и достовернее приближает модель к действительности.
Практическая часть
Постановка задачи
Требуется оптимизировать различными способами значение общей прибыли, полученной в предыдущей работе (см. таблицу 5), до установленного значения (200000р.)
Таблица 5 – Исходные данные задачи «Оптимизация прибыли»
| Наименование | Выпуск, шт. | Всего | Цена | Выручка | Прибыль | ||
| Январь | Февраль | Март | |||||
| Изделие 9/001 | 299,00р. | 602 784,00р. | 120 556,80р. | ||||
| Изделие 9/002 | 199,00р. | 149 051,00р. | 29 810,20р. | ||||
| Изделие 9/003 | 150,00р. | 61 050,00р. | 12 210,00р. | ||||
| Прибыль итого | 162 577,00р. |
Ход работы
1. Открываем книгу "laba_5.xls" и сохраняем её как книгу "laba_6.xls".
2. Удаляем в книге лист "Исходные данные"; лист "Расчет" переименовываем в "Исходные данные"; добавляем листы "Подбор параметра" и "Поиск решения".
3. Копируем таблицу с листа "Исходные данные" на лист "Подбор параметра".
4. В меню Сервис выбираем пункт Подбор параметра. В строке Установить в ячейке введем ссылку на ячейку с адресом H8, содержащую формулу для прибыли. В строке Установить значение укажем величину, до которой должна увеличиться прибыль, то есть 200 000. В строке Изменяя значение ячейки введем ссылку на ячейку с адресом F5, содержащую значение цены первого изделия. Подтвердим задание Выполнить. Исходная таблица преобразуется в таблицу оптимальных результатов, полученных при помощи подбора цены первого изделия (рисунок 21 показывает, что для увеличения прибыли с 162 577 р. до 200 000 р. необходимо повысить цену изделия с 299 р. до 391,81 р.). Выделим оптимизируемую и изменяемую ячейку, например, заливкой.
Рисунок 21 – Оптимизация прибыли изменением цены первого изделия
5. Повторим пункты 3, 4 и оптимизируем прибыль, изменяя цены на второе и третье изделие.
6. На том же листе "Подбор параметра" построим вспомогательную таблицу (рисунок 22) и выполним графический анализ результатов оптимизации прибыли, для чего построим диаграмму изменения цены всех изделий. На этой диаграмме (рисунок 23) видим, что для увеличения прибыли требуется весьма существенно изменить цену на третье изделие, почти наполовину поднять в цене второе изделие и значительно менее повысить цену на первое изделие.
Рисунок 22 – Лист "Подбор параметра"
Рисунок 23 – Графический анализ оптимизации прибыли подбором параметра
7. Копируем таблицу с листа "Исходные данные" на лист "Поиск решения".
8. Рассмотрим, как можно увеличить общую прибыль, одновременно изменяя цены на все изделия без ограничений на последние. Выберем команду Сервис/Поиск решения. Откроется диалоговое окно Поиск решения. В поле Установить целевую ячейку укажем выделенную ячейку, в которой вычисляется значение прибыли (ячейка H8). В строке Установить равной значению введем значение 200 000. В поле Изменяя ячейки укажем диапазон подбираемых параметров (ячеек), содержащих значения цен (ячейки F5:F7). В строке Ограничение ничего указывать не будем, так как мы решили на данном этапе решения ограничения не вводить (рисунок 24). Нажмем кнопкуВыполнить.
Рисунок 24 – Оптимизация прибыли изменением цен без ограничений
По завершении оптимизации открывается диалоговое окно Результаты поиска решения, в котором при установке флажка Сохранить найденное решение, можно сохранить протокол оптимизации. В этом случае автоматически появляется новый лист, содержащий Отчет по результатам 1 (рисунок 25).
Рисунок 25 – Отчет по результатам оптимизации
9. Скопируем исходную таблицу еще раз на лист "Поиск решения". Рассмотрим, как можно увеличить общую прибыль, вновь одновременно изменяя цены на все изделия, но с ограничением на рыночную цену изделия9/001(не более 309 р.). Поиск решения по таким условиям будет отличаться от вышеприведенной схемы наличием дополнительных действий: чтобы определить набор ограничений, в окне Поиск решения выполним действие Добавить. В поле Ссылка на ячейку укажем F15, а в качестве Условия выберем неравенство <=309 (рисунок 26). Окно Поиск решения представлено на рисунке 27.
Рисунок 26 – Ввод ограничений в окне Поиск решения
Рисунок 27 – Оптимизация прибыли c ограничением на цену 1-го изделия
Далее подтвердим операции Выполнить и Сохранить найденное решение. В результате можно получить новую таблицу оптимальных результатов и отчет по результатам оптимизации.
10. Можно попробовать оптимизировать значение прибыли до установленного значения изменением выпуска изделий, например за 1 квартал, с ограничением на целочисленность выпуска. Окно Поиск решения для данной оптимизации представлено на рисунке 28.
Рисунок 28 – Оптимизация прибыли изменением выпуска
Аналогично можно решить задачу оптимизации для других условий и ограничений.
Варианты заданий для самостоятельной работы
Задача 1. «Меховое ателье»
Постановка задачи: требуется оптимизировать значение общей прибыли ателье до установленного значения (например, на 25% выше фактической) следующими способами:
· с помощью поочередного изменения цен на изделия;
· посредством одновременного изменения цен на изделия без ограничений на цены;
· с помощью одновременного изменения цен на изделия с ограничением на цену одного изделия (не выше 25% от исходной цены);
· изменением количества пошитых изделий за квартал с ограничением по целочисленности выпуска.
Исходные данные: задача «Меховое ателье», практическая работа 5.
Задача 2. «Книжная база»
Постановка задачи: оптимизировать значение общей прибыли базы до установленного значения (например, на 30% выше фактической) следующими способами:
· с помощью поочередного изменения цен на издания;
· посредством одновременного изменения цен на издания без ограничений на цены;
· с помощью одновременного изменения цен на издания с ограничением на цену одного издания (не выше 15% от исходной цены);
· изменением количества приобретенных изданий за каждый квартал с ограничением по целочисленности партий.
Исходные данные: задача «Книжная база», практическая работа 5.
Задача 3. «Склад»
Постановка задачи: требуется оптимизировать значение общей прибыли склада до установленного значения (например, на 35% выше фактической) следующими способами:
· с помощью поочередного изменения цен на товары;
· посредством одновременного изменения цен на товары без ограничений на цены;
· с помощью одновременного изменения цен на товары с ограничением на цену одного товара (не выше 25% от исходной цены);
· изменением количества реализованных товаров за квартал с ограничением по целочисленности реализации.
Исходные данные: задача «Склад», практическая работа 5.
Задача 4. «Банк»
Постановка задачи: оптимизировать значение общей прибыли банка до установленного значения (например, на 25% выше фактической) следующими способами:
· с помощью поочередного изменения курсов валют;
· посредством одновременного изменения курсов валют без ограничений на курсы;
· с помощью одновременного изменения курсов валют с ограничением на курс одной валюты (не выше 5% от исходного курса);
· изменением количества реализованной валюты за квартал с ограничением по целочисленности объема реализации.
Исходные данные: задача «Банк», практическая работа 5.
Практическая работа 7