Поиск решения в Excel 2007.
Часть 2
 | Подбор суммы по накладной — не единственный пример использования надстройки «Поиск решения» в практике бухгалтера-профессионала. Есть масса задач, когда этот инструмент поможет быстро найти подходящее решение там, где нужно выполнить поистине астрономический объем вычислительной работы. Одну из таких задач, которая имеет самое непосредственное отношение к бухгалтерской практике, мы и рассмотрим в этой статье. А заодно закрепим наши знание в плане использования оптимизатора со сложными условиями для поиска решений. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Прежде чем продолжить работу с надстройкой «
Поиск решения», я предлагаю вернуться к задаче подбора суммы по накладной, но теперь изменить набор условий для оптимизации. Мы уже знаем, что при работе с оптимизатором самое главное — правильно сформулировать задачу. А это значит, что мы должны однозначно задать целевую функцию и ограничения на значения переменных. В нашей предыдущей статье мы использовали только простые ограничения, когда переменные должны быть целыми и положительными числами. Сейчас мы немного усложним задачу и тем самым приблизим ее к условиям реальной жизни.
Подбор суммы по накладной и двусторонние ограничения
Начнем с исходных данных. Форма накладной у нас не изменилась. Она изображена на рис. 1. Наша задача — подобрать количество проданных товаров в накладной на общую сумму «860,83 грн.». Но при этом нужно учесть, что максимальный объем для ТМЦ «Ручка гел.» не должен превысить 100 шт., по ТМЦ «Карандаш» это ограничение составляет 500 шт., а по ТМЦ «Роллер» максимальное значение ограничено величиной 150 шт. Иными словами, наши переменные не только должны быть целыми положительными числами. Они еще и ограничены сверху максимально допустимым значением. Технически решение такой задачи не представляет труда. Но есть важное замечание.
Важно! Любое дополнительное ограничение в оптимизаторе неизбежно сокращает количество допустимых решений. Если вы вводите новое ограничение, обязательно проанализируйте — а действительно ли оно имеет принципиальное значение для решения поставленной задачи?
В нашем случае ограничения вполне разумны. Единственное, что я бы сделал, — это сразу же посмотрел, можно ли получить сумму 860,83 грн. при максимальном значении переменных. Если этого сделать нельзя, то задача, мягко говоря, не имеет смысла. Проверяем: 100 х 1,05 + 500 х 0,75 + 150 х 2,2 = 810 (грн.). Прибавляем НДС: 810 х 0,2 = 162 (грн.). Итого получаем, что максимально возможная сумма в накладной при наших ограничениях будет 810 + 162 = 972 грн. Это значит, что в принципе задачу решать можно. Дальше делаем так:
1) вызываем пункт «
Данные» главного меню;2) в разделе «
Анализ» щелкаем на иконке «Поиск решения». На экране появится окно с параметрами, как на рис. 2. Оптимизатор запомнил свои настройки с предыдущего сеанса работы и показал их в неизменном виде. Сейчас мы должны эти настройки исправить или дополнить; 
3) в окне «
Поиск решения» для целевой функции указана ячейка «K41». Напомню, что в ней записана формула для определения разности между суммой в накладной и желаемым значением, т. е. «=ABS(K40-K39)». Этот параметр оставляем без изменений;4) критерий оптимизации тоже не изменяем. Это будет минимизация значения в ячейке «
K41»;5) ячейки с переменными (у нас это блок «
E29:E31») оставляем. Мы по-прежнему будем изменять количества товаров в документе. Переходим к формированию ограничений;6) в ограничениях уже записаны два выражения: количества в блоке «
E29:E31» должны быть целыми и положительными числами. Нам нужно добавить еще три ограничения на максимальное значение этих переменных;7) в окне «
Поиск решения» щелкаем на кнопке «Добавить». Откроется окно «Добавление ограничения», как на рис. 3; 
8) ставим курсор в область параметра «
Ссылка на ячейку:». Щелкаем на ячейке «E29»;9) щелкаем на параметре для выбора операции сравнения. Из открывшегося списка выбираем «
<=»;10) в окно параметра «
Ограничение:» вводим число «100». Тем самым мы указали оптимизатору, что количество ТМЦ «Ручка гел.» в документе не может превышать 100 шт.;11) в окне «
Добавление ограничения» нажимаем кнопку «Добавить» и по такой же схеме вводим ограничения для ячеек «E30», «E31»;12) закончив работу по созданию ограничений, в окне «
Добавление ограничения» нажимаем «ОК». Окончательный вид окна «Поиск решения» со всеми параметрами показан на рис. 4; 
13) в окне «
Поиск решения» нажимаем кнопку «Выполнить». Через некоторое время Excel покажет результат, как на рис. 5. 
Как и следовало ожидать, решение мы получили другое. Сразу проверяем количества ТМЦ в накладной. В нашем случае все ограничения выполнены, значения в ячейках «
E29:E31» целые, положительные и не превышают заданных пределов. Значит, параметры оптимизатору указаны правильно. Общая сумма по накладной составляет 860,88 грн., что отличается от заданной величины на 0,05 грн. В принципе результат нормальный, я бы считал его вполне допустимым.
Формируем пакет документов
Как мы уже говорили, задач, где оптимизатор способен оказать бухгалтеру ощутимую помощь, много. Вот еще один пример из моей практической работы.
Есть база данных, как на рис. 6. В ней собран реестр документов за отчетный период. Реестр состоит из трех полей: дата документа, краткое название (колонка «
Документ») и сумма расходов в пределах документа. Из этой базы нужно подобрать пакет документов, в котором сумма затрат будет максимально приближена к заданному значению. Предположим, что это значение равно 2172,90 грн. Похожие задачи в практике бухучета встречаются часто. Например, когда деятельность предприятия носит сезонный характер и приходится выравнивать прибыль, увязывая ее с суммой предоплаты за торговые патенты. Ситуаций много. Думаю, вы поняли, о чем пойдет речь. 
Чтобы уяснить себе задачу, мы предварительно проделаем ее вручную. А потом применим для этой же цели оптимизатор. Для начала делаем так:
1) добавляем пустую строку в начале базы данных;
2) ставим указатель активной ячейки на адрес «
A3»;3) вызываем меню «
Вид»;4) в группе «
Окно» щелкаем на иконке «Закрепить области». Таким образом, мы создали информационное табло, как на рис. 7; 
5) в ячейку «
B1» пишем формулу «=СУММЕСЛИ(J:J;"д";F:F)». Эта формула анализирует содержимое колонки «J». Как только в ней появится символ «д», она будет суммировать значения из соответствующих ячеек в колонке «F». Теперь наша задача — выбрать из базы документы на сумму 2172,90 грн.;6) в колонке «
J» проставляем признак «д» и анализируем значение в ячейке «B1». Последовательно перебираем варианты, пока сумма в «B2» не станет близкой (или равной) «2172,90».Я, например, сделал так. Вначале отсортировал диапазон с данными по убыванию поля «
СУММА» и после этого приступил к формированию пакета, начиная с больших сумм, постепенно двигаясь вниз, к меньшим суммам. После нескольких экспериментов я получил решение, как на рис. 7. Общая сумма по выбранным документам составила 2171,69, что отличается от заданного значения на 1,21 грн.Теперь выполним то же самое, но при помощи надстройки «
Поиск решения». Делаем так:1) открываем базу данных, как на рис. 6 (лист «
Опт1»);2) справа от столбца «
СУММА» добавляем две колонки: «Пр» и «Сумма_»;3) значения в колонке «
Пр» не заполняем. Это сделает оптимизатор;4) в ячейку «
K2» вводим формулу «=J2*F2» и копируем ее вниз на всю высоту таблицы. Теперь если в колонке «Пр» проставить «1», то соответствующие значения из столбца «СУММА» появятся в колонке «Сумма_»;5) в свободной ячейке листа считаем сумму по колонке «
Сумма_». В нашем примере это значение посчитано в ячейке «K34». Формула для расчета элементарная: «=СУММ(K2:K33)»;6) в ячейку «
K35» вводим желаемое значение. У нас оно составляет «2172,9»;7) в ячейку «
K36» вводим формулу «=ABS(K35-K34)». Все готово к работе (рис. 8); 
8) вызываем меню «
Данные»;9) в группе «
Анализ» щелкаем на иконке «Поиск решения»;10) в одноименном окне заполняем параметры, как на рис. 9. Здесь изменяемыми ячейками являются элементы колонки «
Пр». Ограничение в виде «$J$2:$J$33 = двоичное» заставит оптимизатор ставить в ячейки «$J$2:$J$33» только «0» или «1». Соответственно будет изменяться сумма в ячейке «K34». Перебирая различные варианты расстановки «0» и «1» в ячейках колонки «Пр», Excel будет искать подходящее решение. Критерий поиска — минимальное отклонение суммы в ячейке «K34» от заданного значения, которое записано в ячейке «K35»; 
11) заполнив параметры в окне «
Поиск решения», нажимаем кнопку «Выполнить». Через некоторое время получим результат, как на рис. 10. Excel подобрал пакет документов, сумма расходов в которых составляет 2172,87 грн. Отклонение от заданного значения составило всего три копейки. Как на мой взгляд, более чем приличный результат! 
Вернемся к окну «
Поиск решения», изображенному на рис. 9. С ним мы работали уже не раз, но все элементы управления так и не рассмотрели. В окне есть девять кнопок, вот основные из них. Кнопка «Предположить» заполняет параметр «Изменяя ячейки:». Я этой кнопкой в своей практике не воспользовался ни разу. Проще ввести адреса прямо в поле «Изменяя ячейки:».Три кнопки справа от области «
Ограничения:» работают так. Кнопка «Добавить» открывает окно для формирования нового ограничения. Кнопка «Изменить» открывает окно для изменения существующего ограничения (его нужно предварительно выделить в имеющемся списке). Кнопка «Удалить» стирает ограничение.Справа в окне «
Поиск решения» расположены пять кнопок. Вот их назначение. Щелчок на кнопке «Выполнить» начинает процесс поиска решения. Кнопка «Закрыть» завершает работу с оптимизатором. Кнопка «Параметры» открывает доступ к настройкам оптимизатора. По кнопке «Восстановить» вы можете вернуть настройки оптимизатора по умолчанию. Щелчок на кнопке «Справка» открывает справочную систему по работе с Excel 2007.Хочу обратить ваше внимание на важный момент. При работе с надстройкой «
Поиск решения» приходится вводить большое количество параметров. Возникает вопрос: как сохраняет эти параметры программа Excel 2007? Что будет, если понадобится применить надстройку к нескольким экземплярам данных? Чтобы разобраться с этим вопросом, делаем так:1) открываем документ с базой данных, как на рис. 6;
2) копируем всю базу в буфер обмена (нажимаем «
Ctrl+A», затем «Ctrl+С»);3) создаем новый рабочий лист. Я назвал его «
Опт2»;4) вставляем на этот лист содержимое буфера обмена («
Ctrl+V»);5) вызываем меню «
Данные», в группе «Анализ» щелкаем на иконке «Поиск решения». Откроется окно «Поиск решения», как на рис. 9, но все параметры в нем будут пустыми;6) выполняем настройку параметров оптимизатора для данных на листе «
Опт2». Все ограничения оставляем, как в предыдущем примере. Изменим только значение в ячейке «K35». Будем считать, что общая сумма пакета документов должна составить 5000 грн.;7) возвращаемся на лист «
Опт1»;8) вызываем меню «
Данные», щелкаем на иконке «Поиск решения». Откроется окно настроек, как на рис. 9. В нем мы увидим, что все параметры поиска решения для листа «Опт1» остались без изменений.Важно! Программа Excel 2007 сохраняет настройки оптимизатора индивидуально для каждого листа рабочей книги. При этом действует общее правило: «один лист — один сценарий». То есть в пределах одного листа нельзя сохранить несколько сценариев оптимизации для разных наборов данных.
Режем целое на части
Этот небольшой пример не имеет непосредственного отношения к бухгалтерскому учету, хотя почему-то решить его пришлось именно нам, работникам бухгалтерии. А задача была такая. Недавно в нашем офисе нужно было обновить компьютерную сеть. Для этого понадобилось один кусок кабеля длиной 125 метров разрезать на части. Причем сделать так, чтобы неиспользуемый остаток кабеля был минимальным. Нам были известны размеры отрезков кабеля, которые понадобятся для монтажа сети. А также минимальное и максимальное количество отрезков каждой длины, которые мы должны были получить в конечном счете. Делаем так:
1) создаем небольшую таблицу, как на рис. 11. В ячейки «
A2:A7» вводим данные о длине отрезков кабеля, которые мы будем использовать. Нам понадобятся отрезки длиной «1,5», «2,2», «4,5», «7», «9» и «11,5» метров. Столбец «Кол-во» (ячейки «B2:B7») — это переменные. Сюда оптимизатор запишет точное количество отрезков каждого типоразмера, которые мы должны нарезать из одной бобины. В колонке «Минимум» я проставил минимальное количество отрезков каждого типа, в колонке «Максимум» — максимальное количество таких отрезков;2) в ячейку «
B8» пишем формулу «=СУММПРОИЗВ (A2:A7;B2:B7)». Она последовательно перемножает содержимое ячеек «A2:A7» на ячейки «B2:B7» и складывает полученные значения. Таким образом, мы определим общую длину отрезков в соответствии с тем их количеством, которое записано в колонке «Кол-во»;3) в ячейку «
B9» заносим длину кабеля в бобине.Этот кабель мы должны разрезать;4) в ячейку «
B10» вводим формулу «=ABS(B9-B8)». Это длина неиспользуемого остатка кабеля при условии, что содержимое в ячейке «B9» будет не меньше значения в ячейке «B8»;5) вызываем надстройку «
Поиск решения» и вводим ограничения, как показано на рис. 12. Условия «$B$2:$B$7<=$D$2: $D$7» и «$B$2:$B$7>=$C$2: $C$7» означают, что значения переменных должны быть не больше максимальных и не меньше минимальных. Количества должны быть целыми положительными числами («$B$2:$B$7 = целое», «$B$2:$B$7>=0»). А суммарная длина кабеля в бобине должна быть больше или равна сумме длин полученных отрезков («$B$9>=$B$8»);6) в окне «
Поиск решения» нажимаем «ОК». Через некоторое время оптимизатор предложит решение, как на рис. 11. Нарезав из одной бобины отрезки в соответствии с числами из колонки «Кол-во», остаток неиспользованного кабеля при этом будет равен нулю.Наконец, наше последнее действие с надстройкой «
Поиск решения» будет таким:1) в таблице на рис. 11 выделяем колонку «
Максимум» (или любую другую, на ваше усмотрение); 
2) через меню (или правую кнопку мыши) вставляем на рабочий лист новую колонку;
3) вызываем меню «
Дата», затем надстройку «Поиск решения». Смотрим на значения в параметрах оптимизатора.Важно! При добавлении, перемещении или удалении строк и колонок на рабочем листе адреса в параметрах оптимизатора изменятся вместе с адресами ячеек. Изменив рабочий лист, заново перенастраивать оптимизатор вам не придется, Excel это сделает автоматически. Главное, чтобы все ячейки, которые нужны ему для работы, остались на листе и содержали правильные данные.
Если оптимизатор не нашел подходящего решения
Такая ситуация встречается довольно часто. Опыт показывает, что в подавляющем большинстве случаев это результат неправильной (или неполной) постановки задачи. Возможно, целевая ячейка выбрана неверно или введены не все ограничения. Вариантов много. В любом случае первое, что нужно сделать, — это:
— спросить себя: уверены ли вы, что решение существует? Привести доказательства этой уверенности;
— проверить, нет ли ошибок в модели. В частности, не защищены ли случайно от изменений ячейки с переменными;
— убедитесь, что для всех изменяемых ячеек указаны ограничения. Помните железное правило: оптимизация без ограничений не имеет смысла. Вообще создание системы ограничений — самый ответственный этап при работе с оптимизатором. Вполне возможно, что с первого раза здесь что-то не срастется и надстройка «
Поиск решения» будет выдавать непонятные результаты. Выход из ситуации только один. Говорят, что с проблемой нужно переспать. Попробуйте отдохнуть и взглянуть на модель с утра, на свежую голову.И еще. Практика показывает, что линейную целочисленную задачу с десятками переменных оптимизатор решает за приемлемое время даже на маломощном (по нынешним меркам) компьютере. Если модель очень большая, подумайте, нельзя ли ее сократить. Например, сузить диапазон поиска, исключив часть переменных и откорректировав соответствующим образом искомое значение целевой функции.
Если стартовать с начальными значениями переменных, которые дают результат того же порядка, что искомый, процесс поиска закончится быстрее.
Если изменить стартовые условия, оптимизация может пойти по другому, возможно более удачному, пути. Правда, надстройка «
Поиск решения» отличается в этом плане завидным постоянством и часто «ходит по кругу».Наконец, последнее. При поиске решения оптимизатор пытается удовлетворить все ограничения, в том числе требования к относительной погрешности и допустимому отклонению. В результате работы оптимизатора во влияющей ячейке, на которую наложено целочисленное ограничение, может оказаться не целое число.
Совет
Всегда используйте для целочисленных ячеек с переменными формат «Общий». В этом случае вы сразу сможете увидеть ячейки с дробными числами. Это поможет избежать проблем при переносе результатов оптимизации в бухгалтерскую программу. Помните: если использовать в модели функцию округления до целых (например, «=ОКРУГЛ(F2;0)»), задача станет нелинейной.Вот, собственно, и все, что нужно знать о работе надстройки «Поиск решения». Как видите, бухгалтерский учет настолько разнообразен, что в нем найдется место даже для сложных вычислительных методов. Главное при этом — понять суть процесса и, применяя математику, извлекать из этого пользу.
На этом мы завершим описание настройки «Поиск решения». А в следующей статье познакомимся с возможностями других надстроек программы Excel 2007.
Успешной работы! Жду ваших писем, предложений и замечаний на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .
