2015-08-21
376
После ввода модели на лист Excel ее необходимо проверить п задокументировать. Для проверки задаются различные значения переменных (в нашем случае— в ячейках ВЗ:ЕЗ), чтобы выявить возможные ошибки вычислений. Если, например, для заведомо допустимых значений переменных нарушаются какие-то ограничения, появляются странные значения левых частей ограничений или целевой функции, то, скорее всего, в числах или формулах модели есть ошибки.
Чтобы задокументировать созданную модель, нужно сохранить ее изображение не только с числами, как на рис. 1.20, но и с формулами (рис. 1.21). Для этого сначала сделайте копию листа с моделью, чтобы на этой копии сохранить изображение формул модели. Наиболее простой способ копирования заключается в следующем. Удерживая нажатой клавишу Ctrl, перетащите ярлычок с названием листа по строке ярлычков (движущаяся стрелочка над строкой покажет, где будет вставлена копия) и затем отпустите кнопку мыши (раньше, чем клавишу Ctrl).
Чтобы на полученной копии листа отображались формулы, выберите:
|
|
|
в Excel 2010 — вкладку Формулы ► Показать формулы;
| т | А | В | С | Е | F | Н | ""1" ТП | |||
| Производственный план | ||||||||||
| Продукт | П1 | П2 | ПЗ | П4 | ||||||
| Произведенное к-во | Прибыль | |||||||||
| Удельная прибыль | =СУММПРОИЗВ($В$3:$Е$3;В4:Е4) | |||||||||
| Ограничения | Расход ресурсов | Лев.часть | | Прав.часть | Разница | ||||||
| б | труд | =СУММПРОИЗВ($В$3:$Е$3;Вб:Еб) | < | =H6-F6 | ||||||
| Сырье | =СУММПРОИЗВ($В$3:$Е$3;В7:Е7) | < | =H7-F7 | |||||||
| Финансы | =СУММПРОИЗВ($В$3:$Е$3;В8:Е8) | < | =H8-F8 | |||||||
| Мах.П1 | =СУММПРОИЗВ($В$3:$Е$3;ВЭ:Е9) | < | =H9-F9 | |||||||
| Мах.ПЗ | =СУММПРОИЗВ($В$3:$Е$3;В10:Е10) | < | =H10-F10 | |||||||
| Мах.П4 | =СУММПРОИЗВ($В$3:$Е$3;В11:Е11) | < | =H11-F11 | |||||||
| Мин.П1 | =СУММПРОИЗВ($В$3:$Е$3;В12:Е12) | > | =F12-H12 | |||||||
| Мин.П2 | =СУММПРОИЗВ($В$3:$Е$3;В13:Е13) | > | =F13-H13 | |||||||
| Мин.ПЗ | =СУММПРОИЗВ($В$3:$Е$3;В14:Е14) | > | =F14-H14 | |||||||
| Мин.П4 | =СУММПРОИЗВ($В$3:$Е$3;В15:Е15) | > | =F15-H15 | |||||||
Рис. 1.21. Модель с отображением формул
1.51. в Excel 2007 — вкладку Формулы ► Зависимости формул > Показать формулы;
1.52. в предыдущих версиях Excel — меню Сервис ► Параметры ► Формулы.
Поскольку при переходе к отображению формул столбцы значительно расширяются, важно подобрать их ширину в соответствии с содержимым ячеек, иначе некоторые формулы могут не поместиться на экране. Для этого, как и раньше, выделите весь лист, щелкнув кнопку Выделить все, и дважды щелкните правую границу заголовка любого из столбцов.
|
|
|
На рис. 1.22 показано использование имен диапазонов в формулах модели. Это позволяет сделать формулы понятнее. Рекомендуется всегда использовать имена для документирования сложных моделей.
Чтобы получить такое изображение нужно сначала создать имена диапазонов. Можно преобразовать в имена существующие заголовки строк и столбцов. Для этого их выбирают вместе с соответствующими диапазонами значений.
В нашем примере начнем с присвоения имен строкам. Для этого на листе с отображением формул, показанном на рис. 1.21, выделите одновременно два диапазона строк вместе с соответствующими заголовками: АЗ:Е4 и А6:Е15. (При выборе этих несмежных диапазонов не забудьте удерживать нажатой клавишу Ctrl.)
| и | А | » | с 1 о | Е | F | |G| II | 1 И | | ||
| Производственный план | |||||||||
| Продукт | ... | (1? | ПЗ | Г14 | |||||
| Произведенное к-во | 1 Ч | 1 11 | Прибыль | ||||||
| Удельная прибыль | 110 1140 | ^-СУММПРОИЗЩПронэиоденное К по;Уделышп прибыль) | |||||||
| Ограничения | Расход ресурсов | Леп.частъ | 1 1Г1рао.часть | Разница | |||||
| труд | =СУММПРОИЗВ(Г1роизведенное к во;Труд) | !< 119 | =Пров.часть-Лев.часть | ||||||
| Сырье | -1 | =СУММПРОИЗВ{Произиеденное к во;Сырье) | "1< 'so | =Пров.чааь-Лев.часть | |||||
| Финансы | / | =СУММПРОИЗВ(Произведенное к по;Финансы) | !< |ioo | =Проо.часть-Лев.часть | |||||
| Мах.П1 | =СУММПРОИЗВ(Ороизпеденное к во;Мах.П1) | !< ’5 | =Пров.часть-Лев.часть | ||||||
| Мах.ПЗ | =СУММПРОИЗВ(Произведенное к во;Мах.Г13) | < 3 | =Пров.часть-Лев.часть | ||||||
| Мах.П4 | ^СУММПРОИЗВ(Произпеденное к во;Мах.П4) | !<« | ^Лров.част^ Лев.часть | ||||||
| Мин.П1 | =СУММЛРОИЗВ(Произведенное к во;Мин.П1) | 1>з | =Лев.часть-Пров.часть | ||||||
| Мик.П2 | =СУММПРОИЗО(Произведенное к во;Мим.П2) | !> Ii | =Лев.часть Пров.часть | ||||||
| Мин.ПЗ | =СУММПРОИЗВ(Произведониое к по;Мин.ПЗ) | !>|i | =Лев.часть-Пров.часть | ||||||
| Мии.П4 | =СУММПРОИЗВ(Произведенное к во;Мин.1И) | "l> 12 | =Лев.часть-Пров.часть | ||||||
Рис. 1.22. Использование имен диапазонов
Далее выберите:
1.53. в Excel 2010 и 2007 — вкладку Формулы ► Создать из выделенного;
1.54. в предыдущих версиях Excel — меню Вставка ► Имя ► Создать.
В открывшемся окне будет правильно указано расположение заголовков — в столбце слева, поэтому щелкните кнопку ОК (при другом расположении заголовков этот параметр можно изменить).
Теперь присвоите имена столбцам. Удерживая нажатой клавишу Ctrl, выделите одновременно два диапазона столбцов: F5:FI5, Н5:Н15 и выполните те же самые действия (и на этот раз в открывшемся окне будет правильно указано расположение заголовков — в строке выше).
Созданные таким образом имена будут относиться только к диапазонам чисел, без заголовков. Посмотреть список этих имен можно, щелкнув стрелочку рядом с полем Имя у левого края строки формул. Выбрав любое из них, вы увидите выделенным соответствующий диапазон.
Чтобы созданные имена отображались в формулах, выделите все ячейки с формулами: F4, F6:F 15, 16:115 (удерживая нажатой клавишу Ctrl). Затем выберите:
1.55. в Excel 2010 и 2007— вкладку Формулы ► стрелку рядом с кнопкой Присвоить имя ► Применить имена;
1.56. в предыдущих версиях Excel — меню Вставка ► Имя ► Применить.
В открывшемся окне выделите все перечисленные имена, прокручивая список и щелкая их мышыо, а затем щелкните кнопку ОК.
|
|
|
Чтобы имена были видны полностью, используйте автоматический подбор ширины столбцов (щелкните сначала кнопку Выделить все, а затем дважды — правую границу заголовка любого столбца).
