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 грн.). По-друге, вибрати інший алгоритм роботи надбудови «Поиск решения», що підходить для нелінійних завдань. Почнемо з критерію та виконаємо такі дії:
— відкриємо документ із податковою накладною;
— станемо на комірку «AI44», уведемо до неї підсумкову суму (у нас це «8059,80»);
— до комірки «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 грн.). Виникає запитання: а чи можна у принципі отримати таку суму при зазначених обмеженнях? Щоб відповісти на нього, виконуємо такі дії:
— до комірок «P36:P40» уводимо максимально допустимі кількості для відповідних ТМЦ. Ці значення ми беремо з обмежень;
— перевіряємо суму в документі — вона має перевищувати 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. У ньому 6 параметрів, серед яких я б звернув увагу на такі:
— «Сходимость» — задає умову, коли оптимізатор припиняє свою роботу. Для нелінійних завдань це відбувається, якщо на п’яти останніх ітераціях значення цільової функції змінилися менше, ніж зазначено в полі «Сходимость:» (на рис. 8 це 0,0001);
— «Максимальное время без улучшения:» — указує часовий інтервал, протягом якого Excel відстежуватиме значення цільової функції. Якщо за цей період поліпшити рішення не вдається, «Поиск решения» припинить свою роботу;
— «Обязательные границы для переменных» — при включеному прапорці Excel вимагатиме вказати і мінімальне, і максимальне значення для кожної змінної. Якщо відключити цей прапорець, можна обмежитися лише односторонніми обмеженнями. Я вважаю, що при роботі з цілочисловими завданнями прапорець «Обязательные границы для переменных» краще залишати включеним.
І останнє. Що робити, якщо оптимізатор не знаходить прийнятного рішення?
1. Перевірити правильність обмежень. Ви впевнені, що при заданих обмеженнях відповідне рішення існує? Обчисліть цільову функцію в мінімумі та максимумі обмежень. Порівняйте значення цільової функції з бажаним результатом.
2. Переконайтеся, що в моделі немає помилок. Уважно перегляньте формули в документі. Чи всі вони лінійні? Чи немає у формулах функцій округлення, перевірки умов тощо? Залежно від моделі виберіть алгоритм роботи оптимізатора.
3. Перевірте, що комірки зі змінними не захищено від змін. Якщо потрібно — зніміть такий захист.
Практика показує, що лінійне цілочислове завдання з десятками змінних оптимізатор вирішує за прийнятний час навіть на малопотужному комп’ютері. Якщо модель дуже велика, спробуйте її скоротити. Наприклад, звузити діапазон пошуку, виключивши частину змінних або змінивши цільову функцію. Пам’ятайте: у цілочислових і нелінійних завданнях різні стартові умови можуть дати різний результат. Змініть початкові умови — і оптимізація може піти іншим, більш прийнятним шляхом. Ось, власне, і все, що потрібно знати для успішної роботи з таким потужним інструментом, як «Поиск решения» MS Excel 2010. Як бачите, бухгалтерський облік досить різноманітний, і в ньому знаходиться місце навіть для складних обчислювальних методів.
Успішної роботи! Чекаю ваших листів та пропозицій на bk@id.factor.ua , nictomkar@rambler.ru або на форумі редакції.
