Надстройки Excel 2007. Поиск решения
 | В составе программы Excel 2007 есть специальные модули, которые расширяют ее функциональные возможности. В основном они направлены на решение задач статистического анализа, поиск оптимальных решений, оценку по принципу «что если» и т. п. К сожалению, многие из этих надстроек на практике используют редко. И совершенно напрасно! В работе бухгалтера есть масса ситуаций, когда надстройки Excel 2007 могут сократить объем рутинной работы в десятки и даже сотни раз. Главное — знать, где их можно применить, как правильно сформулировать задачу и настроить параметры для ее решения. Именно эти вопросы мы и рассмотрим в нашей статье. И начнем с надстройки, которая называется «Поиск решения». Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Среди надстроек Excel 2007 наибольший интерес для бухгалтера представляет «
Поиск решения». Работа этой надстройки направлена на решение сложных оптимизационных задач (поэтому «Поиск решения» иногда называют оптимизатором). И надо признать, справляется она с этими задачами хорошо. Предвижу возгласы скептиков: «Какая оптимизация! Какой поиск решения! Когда квартальный отчет на носу…» Не торопитесь с выводами. Лучше уделите немного времени и поработайте с примерами этой статьи. Я убежден, что иногда имеет смысл отвлечься от текучки и посмотреть, какие инструменты можно дополнительно задействовать в своей работе и какую пользу можно от них получить. Такой подход всегда дает положительные результаты. Все новые знания рано или поздно окупятся. Причем, как правило, сторицей. Любые дополнительные возможности так или иначе вам пригодятся. Особенно когда речь идет о такой разнообразной работе, как бухгалтерский труд. Итак, наша задача будет следующей. Вначале мы поработаем с надстройкой «Поиск решения» в «классическом» варианте. Для этого мы создадим фрагмент документа «Налоговая накладная» и посмотрим, как с помощью этой надстройки можно подобрать общую сумму по накладной. Затем обобщим полученные результаты, разберемся с основными параметрами этого инструмента и тонкостями его применения. И в завершение темы рассмотрим еще один пример, где применим поиск решения для выбора записей из базы данных.
Включаем «Поиск решения»
Напомню, что по умолчанию надстройки
Excel 2007 отключены. Поэтому, прежде чем пользоваться инструментом «Поиск решения», его нужно включить. Сделайте так:1) откройте окно параметров на закладке «
Надстройки»;2) щелкните на кнопке «
Перейти». Откроется вспомогательное окно «Надстройки», изображенное на рис. 1;3) в этом окне поставьте галочку возле компоненты «
Поиск решения»;4) в окне «
Надстройки» нажмите «ОК»;5) в окне параметров нажмите «
ОК»;6) щелкните на пункте «
Данные» основного меню. В группе иконок «Анализ» вы увидите значок «Поиск решения» (рис. 2). Это означает, что инструмент доступен для работы. 
Возможность использовать надстройку «
Поиск решения» мы получили. Теперь нужно определиться с задачей и создать документ, для которого мы эту надстройку применим. И начнем мы с формирования накладной на заранее оговоренную сумму.
Подбор суммы в накладной
Такая задача в практике бухгалтера встречается не так уж редко. Например, чтобы закрыть задолженность по бартерной сделке, зачастую приходится точно подобрать общую сумму в накладной. Та же ситуация возникает при закрытии задолженности по предоплате. В любом из этих случаев перечень товаров, из которых формируется документ, нам известен. А вот дальше есть два варианта действий — изменять цены на товары или подбирать их количество. Здесь ситуация такова.
В Законе Украины «О налогообложении прибыли предприятий» от 22.05.97 г. № 283/97-ВР (далее —
Закон о прибыли) предусмотрено шесть, а в Законе Украины «О налоге на добавленную стоимость» от 03.04.97 г. № 168/97-ВР (далее — Закон об НДС) — семь случаев применения обычных цен. В частности, их нужно применять при расчете налога по операциям с единоналожниками (п.п. 7.4.3 Закона о прибыли, п. 4.2 Закона об НДС) и при бартере (п.п. 7.1.1 Закона о прибыль, п. 4.2 Закона об НДС), что в практике оптовиков и производственников случается чуть ли не каждый день. Определение понятия «обычная цена» дано в п. 1.20 Закона о прибыли. В п. 1.12 Закона об НДС при определении обычной цены содержится ссылка на Закон о прибыли. А в п.п. 1.20.3 Закона о прибыли указано, что для товаров (работ, услуг), которые продаются путем публичного оглашения условий их продажи, обычной признается цена, содержащаяся в таком публичном оглашении. Поскольку способ публичного оглашения цены законодательно не оговорен, достаточно повесить ценники на образцах товаров или опубликовать прайс-лист. Главное, чтобы информация о ваших ценах была доступна пусть не широкому, но заранее не определенному кругу лиц*. Из всего этого следует вывод: при формировании документа мы не может изменять в нем цены, как нам заблагорассудится. Цены в накладной должны быть актуальны на дату документа. Изменять их не стоит, дабы не вызвать дополнительные вопросы при ближайшей проверке. Таким образом, при подборе суммы в накладной мы можем безболезненно изменять только количество проданных товаров.* Если в налоговой инспекции не согласятся с тем, что ваша цена обычная, пусть они попробуют доказать обратное: в п.п. 1.20.8 Закона о прибыли указано, что это теперь их обязанность.
Если номенклатура товаров в документе велика, задача подбора может оказаться довольно трудоемкой. Для ее решения придется потратить немало времени и сил. Однако не все так плохо: если вспомнить математический аппарат целочисленного программирования и грамотно сформулировать задачу, решение вполне сможет найти компьютер. Но вначале подготовим документ.
Создаем налоговую накладную
Для нашего примера я использовал документ, изображенный на рис. 3. Это хорошо знакомая каждому бухгалтеру налоговая накладная. Правда, на рисунке показана только ее табличная часть (она начинается с 26 строки рабочего листа). Описывать процесс создания и форматирования таблицы мы не будем, наша задача сейчас в другом. Остановимся только на расчетных формулах, которые используются в документе. Они приведены в табл. 1.
Таблица 1
Формулы документа «Налоговая накладная»
| Адрес | Формула | Комментарий |
| G29 | =E29*F29 | Расчет базы налогообложения по конкретной строке накладной (цена умножена на количество). Эта формула скопирована вниз на всю высоту таблицы |
| G32 | =СУММ(G29:G31) | Сумма по всем товарам без учета НДС |
| К32 | =G32 | Копирует в ячейку «К32» сумму из ячейки «G32» |
| G37 | =G32+G33+G35+G36 | Сумма по всем товарам с учетом транспортных расходов и надбавки (скидки) |
| K37 | =G37 | Копирует в ячейку «К37» сумму из ячейки «G37» |
| G38 | =ОКРУГЛ(G37/5;2) | Расчет суммы НДС с округлением до двух знаков после запятой |
| K38 | =G38 | Копирует сумму НДС из ячейки «G38» в ячейку «К38» |
| G39 | =G38+G37 | Общая сумма по товарам с НДС включительно |
| K39 | =K38+K37+K34 | Всего сумма по накладной с НДС и залоговой тарой |
Безусловно, в примере использованы упрощенные формулы для расчета значений в налоговой накладной. В вашем конкретном случае они могут отличаться. Но это не столь важно. Главное, что для нашего примера этих формул достаточно, а логика их работы понятна каждому бухгалтеру. И последнее. Размер табличной части в примере составляет всего три строки. Это сделано исключительно для того, чтобы рисунок с документом поместился на странице. Разумеется, что в реальной работе размер налоговой накладной будет другой. Теперь переходим непосредственно к самой задаче.
Постановка задачи
Итак, что мы имеем на самом деле. Итоговая сумма по документу записана в ячейке «
K39». Эта величина по цепочке зависит от ячеек «E29:E31» и «F29:F31» (т. е. от цен на товары и от их количества). С точки зрения математики, перед нами — классический пример задачи целочисленного линейного программирования. В нашем случае он выглядит так:
Первое уравнение описывает сумму по накладной:
Цена1*Кол1+Цена2*Кол2+ … +Ценаn*Колn= Сумма. Область допустимых значений переменных (Колn) ограничена двумя выражениями. Это — условия неотрицательности переменных (количество должно быть положительным) и требование к их целочисленности. Математик скажет, что наши ограничения представляют собой систему линейных уравнений, где Цена1…Ценаn — константы, а Кол1 … Колn — переменные. Систему уравнений, где число неизвестных превышает число уравнений, решить в общем случае нельзя. Но количества в бухгалтерском учете измеряют в штуках, мешках, т. е. в целых числах. Цены и сумму тоже определяют с точностью до целых копеек. Алгебраические уравнения или их системы с целыми коэффициентами, где число неизвестных превосходит число уравнений, называют диофантовыми уравнениями. Доказано, что для линейных диофантовых уравнений существует общее решение в целых числах. Проблема только в том, что решения в заданном диапазоне значений переменных может не оказаться. И определить это заранее нельзя. На самом же деле все не так плохо. Ведь бухгалтеру-практику не нужно математически строгое решение. Ему достаточно найти результат, который удовлетворит его самого и его оппонентов. Иными словами, в реальной жизни можно ограничиться приблизительным решением, максимально близким к оптимальному. А такое решение существует всегда. Что же касается самого поиска, то здесь неоценимую помощь окажут численные методы оптимизации, с которыми успешно справится оптимизатор Excel 2007.
Работа с надстройкой «Поиск решения»
Надстройка «
Поиск решения» предназначена для решения задач линейного программирования, в том числе целочисленного. Последовательность шагов по работе с надстройкой такова:1) вызываем пункт «
Данные» главного меню;2) в разделе «
Анализ» щелкаем на иконке «Поиск решения». На экране появится окно с параметрами, как на рис. 4;3) в этом окне выбираем ячейку с формулой, где записана целевая функция (у нас это сумма по накладной);
4) затем вводим критерий, т. е. цель оптимизации. Это может быть конкретное значение, минимум или максимум целевой функции;
5) обязательно указываем ячейки с переменными. Это те ячейки, за счет которых Excel будет подбирать решение. В нашем примере такими ячейкам являются количества товаров в документе;
6) задаем ограничения на диапазон значений этих переменных. У нас переменные (количества) должны быть положительными целыми числами;
7) в окне настроек оптимизатора нажимаем «
ОК». Excel найдет решение, если оно существует. 
Теперь вернемся к нашей задаче и посмотрим, как это выглядит практически. Итак, на листе с именем «
НН» есть налоговая накладная, фрагмент которой показан на рис. 3. Нужно подобрать количество товаров в накладной таким образом, чтобы сумма по документу составила 860,83 грн. Делаем так:1) активизируем лист «
НН»;2) вызываем меню «
Данные», в разделе «Анализ» щелкаем на иконке «Поиск решения». На экране появится окно «Поиск решения», как на рис. 4;3) ставим курсор в поле «
Установить целевую ячейку:»;4) щелкаем на ячейке «
K39» рабочего листа «НН». Тем самым мы указали Excel, где находится формула с целевой функцией (сумма по накладной);5) переключатель «
Равной:» ставим в положение «значению:». Справа от этого переключателя станет доступным окно для ввода значения;6) в это окно вводим число «
860,83». Тем самым мы указали, что собираемся найти решение, которое в точности совпадает с указанным значением;7) щелкаем левой кнопкой внутри поля «
Изменяя ячейки:»;8) на рабочем листе обводим блок «
E29:E31». Мы указали ячейки с переменными, за счет которых Excel будет подбирать сумму в накладной. Переходим к формированию ограничений;9) щелкаем по кнопке «
Добавить». Откроется окно «Добавление ограничения», как на рис. 5. В этом окне мы можем определить ограничения на изменяемые переменные;10) ставим курсор в область параметра «
Ссылка на ячейку:». Обводим на листе «НН» блок «E29:E31»; 
Совет При настройке параметров оптимизатора адреса ячеек вводите, выбирая их мышкой на рабочем листе.
11) щелкаем на параметре для выбора операции сравнения (он расположен в средней части окна на рис. 5);
12) из открывшегося списка выбираем значение «цел». В области «Ограничения:» появится запись «$E$29:$E$31=целое». Таким образом, мы указали оптимизатору Excel, что изменяемые ячейки должны содержать только целые значения. Теперь нужно задать еще одно ограничение — количества должны быть положительными числами;
13) в окне на рис. 5 нажимаем кнопку «Добавить». Выбираем такие параметры: «Ссылка на ячейку:» — «$E$29:$E$31», тип ограничения — «>=», в окошко «Ограничение:» вводим «0»;
14) в окне «Добавление ограничения» нажимаем «ОК». Окончательный вид окна с ограничениями показан на рис. 4. В нем изменяемыми переменными являются количества товаров в ячейках «E29:E31». В области «Ограничения» введены две строки. Первая означает требования к целочисленности переменных, вторая ограничивает решение только положительными числами;
15) чтобы начать поиск решения, нажимаем кнопку «Выполнить».
Через некоторое время Excel выводит сообщение «Поиск не может найти подходящего решения.» в виде окна, как на рис. 6. В этом окне Excel предлагает сохранить найденное решение либо восстановить первоначальные значения переменных. Выбираем вариант «Восстановить исходные значения» и нажимаем «ОК».
Результат вполне закономерный: цены в накладной образуют ряд 1,05, 0,75 и 2,20 грн. Разумеется, при таких ценах нельзя подобрать количества так, чтобы в точности получить сумму 860,83 грн. Кстати, если сохранить найденное (промежуточное) решение, оно в нашем случае будет почти оптимальное. Это — следствие малого количества переменных, и не более того! Если переменных будет много, промежуточное решение может очень сильно отличаться от желаемого.
Чтобы Excel таки смог справиться с задачей, ее нужно переформулировать. Попробуем искать не точное значение, а минимальное отклонение результата поиска от заданной суммы. Для этого мы в отдельных ячейках запишем искомое значение (860,83 грн.) и формулу для определения разности между текущей суммой в накладной и этой величиной. Эта формула и будет выступать целевой функцией. Ограничения у нас останутся без изменений, а критерий мы выберем другой. Теперь мы будем минимизировать значение целевой функции. На самом деле все очень просто. Делаем так:
1) в ячейку «K40» пишем число «860,83». Это сумма в накладной, которую мы хотели бы получить;
2) в ячейку «K41» вводим формулу «=ABS(K40-K39)» (рис. 7). Функция «ABS()» находит модуль разности между текущим решением и заданной суммой. Здесь нужен небольшой комментарий. В процессе поиска отклонение суммы в накладной от желаемого значения может быть как в большую, так и в меньшую сторону. В результате оптимизатор может запутаться. Используя функцию «ABS()», мы уходим от такой ситуации. Теперь отклонение будет всегда положительным, в процессе поиска оно будет постепенно стремиться к нулю;
3) ограничения и варьируемые переменные остаются прежними, а вот критерий и целевая ячейка станут другими;
4) параметр «Установить целевую ячейку:» делаем равным «K41» (рис. 8). Здесь находится формула для определения отклонения между суммой в накладной и значением «860,83»;
5) вместо строгого равенства переключатель «равной:» устанавливаем в положение «Минимальному значению» (рис. 8);
6) в окне «Поиск решения» нажимаем «Выполнить». Через некоторое время Excel предлагает такой вариант: «Ручка гел.» 139 шт., «Карандаш» 96 шт., «Роллер» 227 шт. Общая сумма по накладной при этом получилась 860,82 грн. Отклонение составило 0,01 грн., что на практике вполне допустимо (рис. 7).
Настройки оптимизатора
Получив некоторый опыт использования надстройки «
Поиск решения», поговорим подробнее о том, что у нее внутри. Надстройка позволяет решать линейные и нелинейные задачи оптимизации, содержащие до 200 переменных. Если задача линейная, количество ограничений может быть любым. Для нелинейных задач допустимо использовать до 100 сложных ограничений и до 400 простых (верхний предел, нижний предел). Для решения нелинейных задач используется специальный алгоритм оптимизации Generalized Reduced Gradient (GRG2). Линейные задачи решаются симплекс методом. Линейные целочисленные задачи — методом ветвей и границ.В бухгалтерской практике действительно нелинейные задачи (например, подсчет суммы сложных процентов) встречаются редко. А вот целочисленные — сплошь и рядом. Но если вам все же придется столкнуться с нелинейной задачей, знайте, от нелинейности иногда можно избавиться. Для этого, конечно, придется перестроить модель. Поясним сказанное, вернувшись к последнему примеру.
Допустим, мы захотели найти решение, где на каждую единицу товара «Ручка гел.» приходятся три ТМЦ «Роллер». Дополнительное ограничение в данном случае можно построить так. В ячейку «
L31» записать формулу «=E31/E29» и через окно на рис. 5 добавить в оптимизатор условие «$L$31=3». Но при этом задача наша станет нелинейной и тем самым будет неоправданно усложнена. Оптимизатор будет вынужден использовать гораздо более сложный алгоритм GRG2 для решения этой задачи. Что можно предпринять в такой ситуации? Изменить ограничение.Важно! В правой части окна для ввода ограничений (рис. 5) можно указывать не только число или адрес, но и формулу.
Таким образом, в окне «
Добавить ограничение» достаточно ввести выражение «$E$29= 3*$E$31», и задача останется линейной. Но все же действовать таким образом я бы не советовал. Оптимизатор, в отличие от Excel, не умеет пересчитывать формулы на лету. Наличие ограничений в виде формул существенно замедляет его работу. Кроме того, такие ограничения считаются сложными, а их можно применить не более 100.Совет При работе со сложными ограничениями запишите формулу в отдельной ячейке рабочего листа. А в окне настройки ограничений ссылайтесь на адрес этой ячейки.
В последнем случае идея решения будет такой: расценивать три роллера и одну ручку как комплект и решать нашу задачу относительно переменных «Карандаш» и «Комплект». Задача останется линейной, а число переменных сократится до двух.
Несколько слов о вычислительной сложности задач. Сложность линейной задачи пропорциональна количеству переменных. Вычислительная сложность нелинейной задачи пропорциональна количеству переменных и характеру математических связей между ними. Оптимум задачи с простыми функциями вида x
2 или x*y надстройка «Поиск решения» находит легко и быстро. Если функции сложные, например периодические, в диапазоне допустимых значений переменных может оказаться несколько локальных оптимумов. Придется повозиться, чтобы отыскать среди них один глобальный. Если же в оптимизируемой функции есть разрывы, например в модели используется хотя бы одна формула вида «=ЕСЛИ(A1>0;B1*C1;B2*C2)», найти решение будет очень тяжело. Возможно, придется разбить задачу на части, решить их по отдельности и сравнить результаты. Отметим, что при использовании надстройки «Поиск решения» нельзя применять функции, обладающие «внутренней логикой» работы. Например, нельзя использовать функцию «СУММЕСЛИ()». Применение этой функции превратит задачу из алгебраической в логическую, а их надстройка «Поиск решения» не решает. Кстати, наш пример является «маленькой, но вредной» задачей, и это неспроста. Вычислительная сложность целочисленных задач очень быстро возрастает с ростом количества переменных.Особенность надстройки «
Поиск решения» в том, что алгоритм ее работы явно нигде не указывается. Рассмотрим подробнее параметры оптимизатора для решения линейных целочисленных задач. Чтобы войти в установку параметров, нужно нажать кнопку «Параметры» (рис. 4). Значения параметров устанавливаются в окне, представленном на рис. 9. 
В первых двух полях можно указать время (в секундах) или предельное число итераций работы алгоритма. Эти значения не критичны. После остановки можно нажать на кнопку «
Продолжить» и терпеливо дожидаться результатов. Можно остановить оптимизатор в любой момент, нажав на клавишу «Esc».В поле «
Относительная погрешность» задается точность, с которой должны выполняться ограничения. Для линейных целочисленных задач значение, заданное по умолчанию (0,000001), лучше увеличить до 0,0001 или даже до 0,1. Это подтолкнет оптимизатор к использованию алгоритма ветвей и границ.Поле «
Допустимое отклонение» используется в целочисленных задачах. Оно задает допустимое отклонение (в процентах) от искомого значения. При поиске максимума или минимума нужно указать допустимое отклонение равным нулю. Если поиск решения работает долго, можно попытаться переформулировать задачу — задать конкретное значение целевой функции и установить в этом окне 1 — 5 % отклонения (или даже больше). Следующее поле — «Сходимость» используется при решении нелинейных задач. Алгоритм GRG2 останавливается, если при пяти последних итерациях значения целевой функции отличаются друг от друга на величину, меньшую заданной в этом поле. При решении линейных задач (установлен флажок «Линейная модель») это поле игнорируется.Если задача линейная, то имеет смысл отметить флажок «
Линейная модель». При этом будет использован более быстрый симплекс метод, а при целочисленных ограничениях — метод ветвей и границ. Обратите внимание: модель должна быть линейной в математическом смысле этого понятия, т. е. должна содержать исключительно суммы произведений переменных на постоянные коэффициенты. Оптимизатор проверяет задачу на линейность и, если модель нелинейная, выдает сообщение «Условия для линейной модели не удовлетворяются».Пометив пункт «
Неотрицательные значения», мы укажем надстройке, что все изменяемые ячейки не могут быть меньше нуля. Ограничение вида «>=0» на переменные при этом можно не применять.Флажок «
Автоматическое масштабирование» нужно обязательно включить в тех ситуациях, когда значения входных переменных различаются на много порядков (задача плохо масштабирована). Впрочем, установка этого флажка и в других случаях вреда не приносит.Если включить флажок «
Показывать результаты итераций», компьютер будет останавливаться и пищать после каждой итерации, а в таблице будут видны текущие результаты. При отыскании оптимума нелинейной функции можно заметить, в какую сторону движется процесс, и внести в модель коррективы. При использовании алгоритма ветвей и границ ход процесса предугадать невозможно и пользоваться этим флажком не имеет смысла.Кнопки в группах «
Оценки», «Разности» и «Метод поиска» предназначены для настройки внутреннего алгоритма оптимизации (GRG2). При включенном флажке «Линейная модель» они игнорируются. Об их назначении, а также о других элементах управления надстройкой «Поиск решения» можно узнать из файла помощи (вызывается кнопкой «Справка»). Краткий обзор надстройки (с примерами) можно найти в файле «\Microsoft Office\Office12\Samples\SOLVSAMP.XLS». Ответы на оставшиеся вопросы по использованию надстройки можно найти на сайте ее разработчика — www.solver.com. К сожалению, поиск в литературе или в русскоязычном интернете малоэффективен, так как большинство авторов цитируют либо файл помощи, либо друг друга.
В следующем номере мы рассмотрим еще один практический пример использования инструмента «Поиск решения», а также поговорим о том, что делать, если оптимизатор не нашел подходящего решения. Успешной работы! Жду ваших писем, предложений и замечаний на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .
