Excel 2010: подбор суммы по накладной
 | В практике бухгалтера бывают ситуации, когда нужно закрыть задолженность по предоплате или же по бартерной сделке. Сумма задолженности, разумеется, известна. Ассортимент товаров, как правило, тоже заранее оговорен. Дело остается за малым — сформировать налоговую накладную на заданную сумму. В принципе, задачу можно решить и вручную. Но при наличии Excel такой подход выглядит, как минимум, нелогично. Зачем тратить время на рутинные расчеты, если с этой задачей прекрасно справится компьютер? Тем более что в программе Excel 2010 для этого есть удобный инструмент — пакет «Поиск решения» (или так называемый «Оптимизатор»). Как воспользоваться им применительно к нашей задаче, какие тонкости нужно учитывать при работе с оптимизатором, я расскажу в этой статье. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Первым делом конкретизируем задачу. То есть определимся с тем, что у нас есть, чего мы хотим добиться и за счет каких изменений. Итак, у нас есть бланк налоговой накладной (в новом формате)*, фрагмент которого показан на рис. 1. Исходными данными в табличной части этого документа являются: перечень товаров (ТМЦ), цены и объемы реализации (количества). Остальные данные в накладной (в том числе сумма НДС и общий итог) Excel считает по формулам. Причем все результаты он сразу подставляет на отдельный лист с обычной накладной (см. «Б & К», 2012, № 3 (123)).
* Скачать бланк налоговой накладной в Excel можно по ссылке http://www.bk.factor.ua/ru/publications/14/8/1059.html .
Идем дальше. В результате взаимоотношений с каким-то контрагентом у нас образовалась задолженность, которую нужно погасить поставкой товаров. Сумма задолженности известна. Перечень ТМЦ, которыми можно закрыть задолженность, тоже определен — об этом мы договорились заранее. И теперь наша задача — из оговоренного перечня ТМЦ составить налоговую накладную так, чтобы итоговая сумма в документе равнялась сумме задолженности (или была максимально близка к ней). Решить такую задачу можно по-разному. Проще всего — подобрать сумму за счет изменения цен. Но это не самый лучший метод, поскольку в этом случае наши действия будут противоречить ряду положений Законов Украины «О налогообложении прибыли предприятий» и «О НДС» в части рекомендаций о применения обычных цен. Понятие «обычная цена» определено в п. 1.20 Закона о прибыли**, где говорится, что для товаров (работ, услуг), которые продаются путем публичного оглашения условий их продажи, обычной ценой признается цена, содержащаяся в таком публичном оглашении. При этом четко указано и условие, при котором цены на товары (работы, услуги) могут считаться обычными: информация о них должна быть доступна заранее не определенному кругу лиц. То есть опубликована (в прайс-листе, на ценниках и т. п.). И поэтому корректировать ее произвольным образом нельзя. Так что для подбора суммы в накладной мы можем изменять только количества товаров. И это существенно меняет дело.
** Статья 39 Налогового кодекса Украины, которой регулируются методы определения и порядок применения обычной цены, вступит в силу с 01.01.2013 г.
С точки зрения математики
В первом приближении задачу подбора суммы по накладной можно представить так:
найти Цена1*Кол1+Цена2*Кол2+ … +Ценаn*Колn=Сумма,
при условии, что Колi>=0, Колi<=Колi max, Колi
Z.
Перед нами не что иное, как частный случай так называемой задачи целочисленного линейного программирования. Первое выражение (его называют целевой функцией) описывает сумму по накладной. Причем переменными величинами здесь являются количества Кол1, Кол2, …, Колn. Область допустимых значений этих переменных ограничена тремя условиями: они должны быть положительными целыми числами (Колi>=0, КолiZ) и максимальное количество каждого ТМЦ в документе не должно превышать величину «Колi max». Математик скажет, что мы имеем дело с линейной функцией и системой линейных ограничений. Он же добавит, что в нашей постановке задача записана некорректно. И это правда — в таком виде она вообще может не иметь подходящего решения. Однако нам сейчас математические тонкости ни к чему. Чтобы двигаться дальше, нужно просто зафиксировать для себя несколько моментов, а именно.
1. В документе «Налоговая накладная» есть целевая функция, ее значение записано в объединенной ячейке «AD44:AG44».
2. Сумму в накладной мы будем подбирать за счет количества ТМЦ, эти значения расположены в блоке «P36:P40».
3. Для решения самой задачи в Excel 2010 есть специальная надстройка «Поиск решения». И все, что мы должны сделать, — правильно настроить параметры ее работы.
Активизируем надстройку «Поиск решения»
Прежде чем воспользоваться надстройкой «Поиск решения», ее нужно включить. Для этого делаем так.
1. Вызываем меню «Файл», выбираем пункт «Параметры». Откроется окно «Параметры Excel», изображенное на рис. 2.
2. В нем выбираем раздел «Надстройки».
3. В нижней части окна нажимаем кнопку «Перейти…». Откроется окно «Надстройки», как на рис. 3.
4. В этом окне ставим галочку возле пункта «Solver».
5. Нажимаем «ОК». Все, надстройка «Поиск решения» включена. Можно приступать к настройке параметров для решения задачи.
Вводим основные настройки
Для правильной работы оптимизатора мы должны определить такие параметры:
1. Указать ячейку с формулой, где находится целевая функция.
2. Сказать, что с ней нужно делать (найти минимум, максимум или точное значение).
3. Показать диапазон рабочего листа, где записаны переменные.
4. Для каждой переменной задать ограничения на область допустимых значений.
После этого оптимизатор сам найдет подходящее решение (если оно существует) и сохранит его в ячейках рабочего листа. Посмотрим, как это выглядит практически. Делаем так:
1. Вызываем MS Excel 2010 и загружаем документ «Налоговая накладная» (рис. 1).
2. Переходим в меню «Данные».
3. В группе «Анализ» щелкаем на иконке «Поиск решения». Откроется окно «Параметры поиска решения», как на рис. 4.
4. Щелкаем внутри окошка «Оптимизировать целевую функцию:».
5. Щелкаем левой кнопкой мышки на ячейке «AD44» — в ней записана формула для определения общей суммы по документу «Налоговая накладная».
6. Переключатель «До:» ставим в положение «Значения».
7. В окошке параметра вводим «8059,80» — это сумма задолженности, под которую нужно сформировать документ.
8. Щелкаем на параметре «Изменяя ячейки переменных:».
9. На рабочем листе обводим блок «P36:P40». В этом блоке записаны количества ТМЦ, за счет этих значений мы будем формировать документ.
10. Щелкаем мышкой внутри поля «В соответствии с ограничениями:».
11. Нажимаем кнопку «Добавить». Откроется окно, изображенное на рис. 5.
12. Становимся на область «Ссылка на ячейки:». Удерживая левую кнопку мышки, обводим блок «P36:P40» на рабочем листе.
13. Щелчком мышки открываем список с операциями (в центральной части окна «Добавление ограничения»).
14. Из этого списка выбираем вариант «>=».
15. В область «Ограничение:» вводим «0». Тем самым мы показываем, что количества ТМЦ в документе должны быть положительными.
16. В окне «Добавление ограничения» нажимаем «Добавить».
17. Оставаясь в области «Ссылка на ячейки:», обводим блок «P36:P40».
18. Раскрываем список операций и выбираем вариант «цел». Это гарантирует, что в конечном решении количества останутся целыми числами.
19. Нажимаем кнопку «Добавить» (рис. 5).
20. В области «Ссылка на ячейки:» указываем блок ячеек «P36:P40».
21. В списке операций выбираем «<=», в окошко «Ограничения:» вводим «100». Это означает, что в налоговой накладной количества по любой позиции ТМЦ не может превысить 100 шт.
22. В окне «Добавление ограничения» нажимаем «ОК».
23. В окне «Параметры поиска решения» нажимаем «Найти решение».
Через некоторое время на экране появится окно с сообщением, что Excel не смог найти подходящего решения в пределах заданных ограничений (рис. 6). И это вполне объяснимо. Причина кроется в самой постановке задачи. В ней я допустил, как минимум, две ошибки, причем сделал это специально. Именно ошибки такого рода чаще всего делают пользователи Excel при работе с надстройкой «Поиск решения».
Первое. В качестве критерия я указал точное значение итоговой суммы. Это условие сильно сузило область возможных решений. Поэтому неудивительно, что Excel не смог найти подходящий вариант.
Второе. Алгоритм работы оптимизатора (рис. 4) я оставил по умолчанию — «Поиск решения линейных задач симплекс-методом», что в данном случае неправильно. Дело в том, что в налоговой накладной мы использовали функцию округления. Например, сумма по товару «Стол компьютерный С 203СН» в ячейке «R36» считается по формуле «=ОКРУГЛ(P36*Q36;2)». А это уже нелинейная зависимость. Поэтому для успешного решения задачи настройки оптимизатора нужно изменить. Во-первых, искать не точное значение, а минимальное отклонение результата от заданной суммы (8059,80 грн.). Во-вторых, выбрать другой алгоритм работы надстройки «Поиск решения», который подходит для нелинейных задач. Начнем с критерия, делаем так.
1. Открываем документ с налоговой накладной.
2. Становимся на ячейку «AI44», вводим в нее итоговую сумму (у нас это «8059,80»).
3. В ячейку «AJ44» вводим формулу «=ABS(AD44-AI44)» для определения разности между текущим решением и заданной суммой. Функция «ABS()» в этой формуле делает эту разность всегда положительной. Значение в «AJ44» мы будем минимизировать.
С критерием мы разобрались. Переходим к параметрам оптимизатора.
1. Через иконку «Поиск решения» открываем окно «Параметры поиска решения», как на рис. 4.
2. В окошко «Оптимизировать целевую функцию:» вводим адрес «$AJ$44».
3. Переключатель «До:» ставим в положение «Минимум».
4. Щелкаем на списке «Выберите метод решения:». Excel предложит три варианта, из которых выбираем «Эволюционный поиск решения».
5. В окне «Параметры поиска решения» нажимаем «Найти решение».
Через некоторое время Excel предложит такой вариант: «Стол компьютерный С 203СН» и «Кресло офисное, Галант CHR» — по 1 шт., «Кресло офисное, Мастер CHR» — 3 шт., «Стул "Ascona"» — 14 шт., «Стул "ISO"» — 5 шт. Общая сумма по документу составила 8069,76, что отличается от желаемого значения всего на 4 коп. Вполне приемлемый результат!
Если предложенный вариант по каким-то причинам не подходит, можно изменить ограничения и повторно решить задачу. Предположим, что мы хотим получить накладную, где будут фигурировать не более одного стола, четырех кресел и десяти стульев каждого вида. Делаем так.
1. Открываем документ с налоговой накладной, вызываем «Поиск решения».
2. В окне настройки параметров (рис. 4) вводим такие ограничения: «$P$36<=1», «$P$36:$P$40 — цел», «$P$36:$P$40 >=0», «$P$37:$P$38<=4», «$P$39:$P$40<=10». Чтобы откорректировать имеющееся ограничение используем кнопку «Изменить». Кнопками «Добавить» и «Удалить» можно создать новое или стереть существующее ограничение.
3. Нажимаем кнопку «Найти решение». Excel предложит новый вариант: «Кресло офисное, Галант CHR» — 3 шт., «Кресло офисное, Мастер CHR» — 4 шт., «Стул "ISO"» — 8 шт. Общая сумма в документе изменилась — теперь она составляет 8059,99. Отклонение от заданного значения составило всего 19 коп., что вполне допустимо. При этом соблюдены все ограничения на допустимые количества товаров каждого вида.
Важно! Вводя дополнительное ограничение убедитесь, что с новым условием задача не потеряла свой смысл.
Поясню сказанное на примере нашего документа. Изначально максимальное количество ТМЦ в накладной было ограничено значением «100». Затем мы уменьшили эту величину и в результате выбрали максимальное количество столов — «1», кресел — «4», а стульев — «10». При этом общая сумма, под которую мы хотим сформировать документ, осталась неизменной (8059,80 грн.). Возникает вопрос: а можно ли в принципе получить такую сумму при указанных ограничениях? Чтобы ответить на него, делаем так.
1. В ячейки «P36:P40» вводим максимально допустимые количества для соответствующих ТМЦ. Эти значения мы берем из ограничений.
2. Проверяем сумму в документе — она должна быть больше чем 8059,80. Если это не так, дальше решать задачу бессмысленно: максимально допустимые количества и есть наилучшее решение!
Настройка параметров оптимизатора
Получив некоторый опыт использования надстройки «Поиск решения», поговорим подробнее о том, что у нее внутри. Надстройка позволяет решать линейные и нелинейные задачи оптимизации, содержащие до 200 переменных. Если задача линейная, количество ограничений может быть любым. Для нелинейных задач допустимо использовать не более 100 сложных ограничений и до 400 простых (верхний предел, нижний предел). Для решения нелинейных задач используется алгоритм оптимизации Generalized Reduced Gradient (GRG2). Линейные задачи решаются симплекс-методом, линейные целочисленные — методом ветвей и границ.
В практике бухгалтера сугубо линейные задачи встречаются нечасто. А целочисленные и нелинейные — буквально на каждом шагу. Иногда от нелинейности можно избавиться, перестроив модель. Поясним сказанное на последнем примере. Предположим, мы хотим получить решение, где на каждую единицу товара «Стол…» приходится три ТМЦ «Стул "Ascona"». Для решения такой задачи мы можем в отдельную ячейку (например, в «AI39») записать формулу «=P39/P36», а в оптимизаторе добавить ограничение «$AI$39=3». Но в этом случае модель станет нелинейной, т. е. будет неоправданно усложнена. Для работы с ней Excel задействует достаточно трудоемкий алгоритм поиска GRG2. Чтобы упростить задачу нужно вспомнить, что в правой части окна «Добавление ограничения» (рис. 5) можно вводить не только числа или адреса, но и формулы. Если в этом окне добавить ограничение «$E$29=3*$E$31», задача останется линейной. Только действовать таким способом я все же не советую. Надстройка «Поиск решения» не умеет пересчитывать формулы «на лету». Поэтому формульное выражение в параметрах оптимизатора сильно замедлит его работу. Лучше перенести формулу в отдельную ячейку и сослаться на ее адрес. Оптимальным решением в данном случае будет такое: расценивать три стула «Ascona» и один стол как комплект и решать нашу задачу относительно переменных «Кресла…», «Стул "ISO"» и «Комплект». Задача останется линейной, а число переменных сократится.
Пару слов о вычислительной сложности задач. Трудоемкость линейной задачи пропорциональна количеству переменных. Вычислительная сложность нелинейной задачи пропорциональна количеству переменных и характеру связей между ними. Задачи с простыми функциями надстройка «Поиск решения» решает быстро. Если функции сложные, нелинейные или периодические — процесс поиска может затянуться надолго. Кроме того, в диапазоне допустимых значений переменных может оказаться несколько локальных решений. И выбор лучшего из них может оказаться непростой задачей. Если в целевой функции есть разрывы (например, в формуле используется функция «=ЕСЛИ()»), найти решение будет очень тяжело. Возможно, придется разбить задачу на части, чтобы хоть как-то ускорить работу оптимизатора. Кстати, при использовании надстройки «Поиск решения» нельзя применять функции с «внутренней логикой» работы («СУММЕСЛИ()»,«СЧЕТЕСЛИ()» и т. п.). Применение таких функций превратит задачу в логическую, а их надстройка «Поиск решения» не решает. Что касается целочисленных задач, то их вычислительная трудоемкость с увеличением количества переменных возрастает очень быстро.
Теперь вкратце о параметрах оптимизатора. Чтобы войти в установку параметров, делаем так.
1. Щелкаем на иконке «Поиск решения», откроется окно «Параметры поиска решения» (рис. 4).
2. В этом окне выбираем метод решения и нажимаем кнопку «Параметры». Откроется окно настроек, как на рис. 7.
В нем все параметры разделены на три закладки:
— «Все методы» — здесь собраны общие параметры для всех алгоритмов работы надстройки «Поиск решения»;
— «Поиск решения нелинейных задач методом ОПГ» и «Эволюционный поиск решения» — объединяет специальные параметры для конкретных алгоритмов работы оптимизатора.
Роль параметров при работе с надстройкой «Поиск решения» очень важна. Поэтому я предлагаю разобраться с основными настройками оптимизатора подробнее.
На мой взгляд, к наиболее важным из них относятся такие (рис. 7):
— «Точность ограничения:»: позволяет ограничить точность соблюдения ограничений. Для линейных целочисленных задач это значение лучше уменьшить до 0,001 или даже до 0,1. Это подтолкнет оптимизатор к использованию алгоритма ветвей и границ;
— «Использовать автоматическое масштабирование»: этот флажок советую включать, если значения входных переменных отличаются на несколько порядков. Впрочем, установка флажка в других случаях тоже вреда не приносит;
— «Показывать результаты итераций:»: при включенном флажке компьютер будет останавливаться после каждой итерации, причем на рабочем листе вы сможете увидеть текущее решение. В случае работы с нелинейной функцией в таком режиме можно увидеть, куда движется процесс и вовремя изменить модель. При работе с целочисленными функциями пользоваться флажком «Показывать результаты итераций» зачастую не имеет смысла;
— «Игнорировать целочисленные ограничения»: при включенном флажке надстройка «Поиск решения» не будет соблюдать условия целочисленности переменных. Зато поиск решения будет работать намного быстрее;
— «Целочисленная оптимальность (%)»: этот параметр используется в целочисленных задачах. Он определяет допустимое отклонение (в процентах) от искомого значения. При поиске максимума или минимума нужно указать допустимое отклонение равным нулю. Если поиск работает долго, попробуйте переформулировать задачу — задать конкретное значение целевой функции и установить в этом окне отклонение 1 — 5 %;
— «Максимальное время (в секундах):» и «Число итераций:»: определяют время и предельное число итераций работы алгоритма. По истечении этого времени надстройка «Поиск решения» приостановит вычисления и покажет окно с предложением продолжить работу или прекратить процесс. Остановить оптимизатор можно и вручную, нажав на клавишу «Esc».
Параметры «Максимальное число подзадач» и «Максимальное число допустимых решений» предназначены для тонкой настройки алгоритма оптимизации по методу ветвей и границ. Эти значения я менять не рекомендую. Лучше посмотрим на основные параметры алгоритма «Эволюционный поиск решения». Делаем так.
1. Открываем окно «Параметры» (рис. 7).
2. Щелкаем левой кнопкой на закладке «Эволюционный поиск решения» (или дважды нажимаем «Ctrl+PgDn»). Окно настроек примет вид, как на рис. 8. В нем шесть параметров, среди которых я бы обратил внимание на такие:
— «Сходимость» — задает условие, когда оптимизатор прекращает свою работу. Для нелинейных задач это происходит, если на пяти последних итерациях значения целевой функции изменились меньше, чем указано в поле «Сходимость:» (на рис. 8 это 0,0001);
— «Максимальное время без улучшения:» — указывает временной интервал, в течение которого Excel будет отслеживать значения целевой функции. Если за этот период улучшить решение не удается, «Поиск решения» прекратит свою работу;
— «Обязательные границы для переменных» — при включенном флажке Excel потребует указать и минимальное, и максимальное значения для каждой переменной. Если отключить этот флажок, можно ограничиться только односторонними ограничениями. Я считаю, что при работе с целочисленными задачами флажок «Обязательные границы для переменных» лучше оставлять включенным.
И последнее. Что делать, если оптимизатор не находит подходящего решения?
1. Проверить правильность ограничений. Вы уверены, что при заданных ограничениях подходящее решение существует? Посчитайте целевую функцию в минимуме и максимуме ограничений. Сравните значение целевой функции с желаемым результатом.
2. Убедитесь, что в модели нет ошибок. Внимательно просмотрите формулы в документе. Все ли они линейные? Нет ли в формулах функций округления, проверки условий и т. п.? В зависимости от модели выберете алгоритм работы оптимизатора.
3. Проверьте, что ячейки с переменными не защищены от изменений. Если нужно, снимите такую защиту.
Практика показывает, что линейную целочисленную задачу с десятками переменных оптимизатор решает за приемлемое время даже на маломощном компьютере. Если модель очень большая, попытайтесь ее сократить. Например, попробуйте сузить диапазон поиска, исключив часть переменных или изменив целевую функцию. Помните: в целочисленных и нелинейных задачах разные стартовые условия могут дать разный результат. Измените начальные условия, оптимизация может пойти по другому, более удачному пути. Вот, собственно, и все, что нужно знать для успешной работы с таким мощным инструментом, как «Поиск решения» MS Excel 2010. Как видите, бухгалтерский учет достаточно разнообразен, чтобы в нем нашлось место даже для сложных вычислительных методов.
Удачной работы! Жду ваших писем и предложений на bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции.
