Excel 2010 підбір суми за накладною, Сайт для бухгалтерів бюджетних установ
У практиці бухгалтера бувають ситуації, коли потрібно закрити заборгованість за передоплатою або бартерною угодою. Сума заборгованості, певна річ, відома. Асортимент товарів, зазвичай, теж заздалегідь обумовлений. Справа залишається за малою — сформувати податкову накладну на задану суму. У принципі завдання можна вирішити і вручну. Але за наявності Excel такий підхід виглядає як мінімум нелогічно. Навіщо витрачати час на рутинні розрахунки, якщо з цим завданням чудово впорається комп'ютер? Тим більше, що у програмі Excel 2010 для цього є зручний інструмент — пакет «Пошук рішення» (або так званий «Оптимізатор»). Як скористатися ним стосовно нашого завдання, які тонкощі потрібно враховувати при роботі з оптимізатором, я розповім у цій статті.
Насамперед конкретизуємо завдання. Тобто визначимося з тим, що у нас є, чого ми хочемо досягти і за рахунок якихось змін. Отже, ми маємо бланк податкової накладної (у новому форматі)*, фрагмент якого показаний на рис. 1. Вихідними даними у табличній частині цього документа є: перелік товарів (ТМЦ), ціни та обсяги реалізації (кількості). Інші дані в накладній (у тому числі сума ПДВ та загальний підсумок) Excel вважає за формулами. Причому всі результати він одразу підставляє на окремий аркуш із звичайною накладною (див. «Б», 2012, № 3 (123)).
З погляду математики
У першому наближенні завдання підбору суми за накладною можна так:
за умови, що Коли=0, Коли=0, КолиZ ) та максимальна кількість кожного ТМЦ у документі не повинна перевищувати величину «Колі 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. У списку операцій вибираємо =0, $P$37:$P$38
Важливо!Вводячи додаткове обмеження, переконайтеся, що з новою умовою завдання не втратило свого сенсу.
Поясню сказане з прикладу нашого документа. Спочатку максимальна кількість ТМЦ у накладній була обмежена значенням «100». Потім ми зменшили цю величину і в результаті вибрали максимальну кількість столів - "1", крісел - "4", а стільців - "10". При цьому загальна сума, під яку хочемо сформувати документ, залишилася незмінною (8059,80 грн.). Виникає питання: а чи можна в принципі отримати таку суму за вказаних обмежень? Щоб відповісти на нього, робимо так.
1. У комірки «P36:P40» вводимо максимально допустимі кількості відповідних ТМЦ. Ці значення ми беремо з обмежень.
2. Перевіряємо суму у документі — вона має бути більшою ніж 8059,80. Якщо це не так, далі вирішувати завдання безглуздо: максимально допустима кількість і є найкращим рішенням!
Налаштування параметрів оптимізатора
Отримавши деякий досвід використання надбудови «Пошук рішення», поговоримо докладніше про те, що вона має всередині. Надбудова дозволяє вирішувати лінійні та нелінійні задачі оптимізації, що містять до 200 змінних. Якщо завдання лінійне, кількість обмежень може бути будь-яким. Для нелінійних завдань допустимо використовувати не більше 100 складних обмежень та до 400 простих (верхня межа, нижня межа). Для вирішення нелінійних завдань використовується алгоритм оптимізації Generalized Reduced Gradient (GRG2). Лінійні задачі вирішуються симплекс-методом, лінійні цілочисловіметодом гілок та кордонів.
Пару слів про обчислювальну складність завдань. Трудомісткість лінійного завдання пропорційна кількості змінних. Обчислювальна складність нелінійного завдання пропорційна кількості змінних та характеру зв'язків між ними. Завдання із простими функціями надбудова «Пошук рішення» вирішує швидко. Якщо функції складні, нелінійні чи періодичні, процес пошуку може затягнутися надовго. Крім того, в діапазоні допустимих значень змінних може бути кілька локальних рішень. І вибір найкращого з них може виявитися непростим завданням. Якщо цільова функція має розриви (наприклад, у формулі використовується функція « =ЯКЛИ() »), знайти рішення буде дуже важко. Можливо, доведеться розбити завдання на частини, аби прискорити роботу оптимізатора. До речі, при використанні надбудови «Пошук рішення» не можна застосовувати функції з «внутрішньою логікою» роботи («СУМІСЛИ()», «РАХУНКИ()» тощо). Застосування таких функцій перетворить завдання налогічну, які надбудова « Пошук рішення » не вирішує. Що стосується цілих завдань, то їх обчислювальна трудомісткість зі збільшенням кількості змінних зростає дуже швидко.
Тепер коротко про параметри оптимізатора. Щоб увійти до установки параметрів, робимо так.
1. Клацаємо на іконці «Пошук рішення», відкриється вікно «Параметри пошуку рішення» (рис. 4).
2. У цьому вікні вибираємо метод рішення та натискаємо кнопку «Параметри». Відкриється вікно налаштувань, як на рис. 7.
У ньому всі параметри поділені на три закладки:
- «Всі методи» - тут зібрані загальні параметри для всіх алгоритмів роботи надбудови «Пошук рішення»;
- "Пошук вирішення нелінійних завдань методом ОЗУ" та "Еволюційний пошук рішення" - поєднує спеціальні параметридля конкретних алгоритмів оптимізатора.
Роль параметрів при роботі з надбудовою "Пошук рішення" дуже важлива. Тому я пропоную розібратися з основними налаштуваннями оптимізатора докладніше.
На мій погляд, до найважливіших з них належать такі (рис. 7):
— «Точність обмеження:»: дозволяє обмежити точність дотримання обмежень. Для лінійнихцілочисленнихзадач це значення краще зменшити до 0,001 або навіть до 0,1. Це підштовхне оптимізатор до використання алгоритму гілок та кордонів;
— «Використовувати автоматичне масштабування»: цей прапорець рекомендую включати, якщо значення вхідних змінних відрізняються на кілька порядків. Втім, встановлення прапорця в інших випадках теж не приносить шкоди;
— «Показувати результати ітерацій: »: при увімкненому прапорці комп'ютер зупинятиметься після кожної ітерації, причому на робочому аркуші ви зможете побачити поточне рішення. У разі роботи з нелінійною функцією у такому режимі можна побачити, куди рухається процес та вчасно змінити модель. Працюючи з цілими функціями користуватися прапорцем « Показувати результати ітерацій » найчастіше немає сенсу;
— «Ігнорувати цілісні обмеження»: при увімкненому прапорці надбудова «Пошук рішення» не буде дотримуватись умов цілісної кількості змінних. Натомість пошук рішення працюватиме набагато швидше;
— «Цілочисленна оптимальність (%)»: цей параметр використовується в цілих задачах. Він визначає припустиме відхилення (у відсотках) від шуканого значення. При пошуку максимуму чи мінімуму потрібно вказати припустиме відхилення рівним нулю. Якщо пошук працює довго, спробуйте переформулювати завдання – задати конкретне значення цільової функції та встановити у цьому вікні відхилення 1 – 5 %;
- "Максимальний час (у секундах):" і "Кількість ітерацій: ": визначають час і граничне число ітерацій роботи алгоритму. Після закінчення цього часу надбудова «Пошук рішення» призупинить обчислення та покаже вікно з пропозицією продовжити роботу або припинити процес. Зупинити оптимізатор можна і вручну, натиснувши клавішу Esc.
Параметри «Максимальна кількість підзадач» та «Максимальна кількість допустимих рішень» призначені для тонкого налаштування алгоритму оптимізації за методом гілок та кордонів. Ці значення я міняти не рекомендую. Найкраще подивимося на основні параметри алгоритму «Еволюційний пошук рішення». Робимо так.
1. Відкриваємо вікно «Параметри» (рис. 7).
2. Клацаємо лівою кнопкою на закладці "Еволюційний пошук рішення" (або двічі натискаємо "Ctrl+PgDn"). Вікно налаштувань набуде вигляду, як на рис. 8. У ньому шість параметрів, серед яких я звернув би увагу на такі:
- "Східність" - задає умову, коли оптимізатор припиняє свою роботу. Для нелінійних завдань це відбувається, якщо на п'яти останніх ітераціях значення цільової функції змінилися менше, ніж зазначено в полі "Східність:" (на рис. 8 це 0,0001);
— «Максимальний час без покращення:» — вказує часовий інтервал, протягом якого Excel відстежуватиме значення цільової функції. Якщо за цей період поліпшити рішення не вдається, пошук рішення припинить свою роботу;
- "Обов'язкові межі для змінних" - при включеному прапорці Excel вимагатиме вказати і мінімальне, і максимальне значення для кожної змінної. Якщо вимкнути цей прапорець, можна обмежитись лише односторонніми обмеженнями. Я вважаю, що при роботі з цілими завданнями прапорець «Обов'язкові межі для змінних» краще залишати включеним.
І останнє. Що робити, якщо оптимізатор не знаходить відповідного рішення?
1. Перевірити правильність обмежень. Ви впевнені, що при заданих обмеженнях відповідне рішення існує? Порахуйте цільову функцію в мінімумі та максимумі обмежень. Порівняйте значення цільової функції з бажаним результатом.
3. Переконайтеся, що комірки зі змінними не захищені від змін. Якщо потрібно, зніміть такий захист.
Практика показує, що лінійне ціле чисельне завдання з десятками змінних оптимізатор вирішує за прийнятний час навіть на малопотужному комп'ютері. Якщо модель дуже велика, спробуйте її скоротити. Наприклад, спробуйте звузити діапазон пошуку, виключивши частину змінних або змінивши цільову функцію. Пам'ятайте: у цілих і нелінійних завданнях різні стартові умови можуть дати різний результат. Змініть початкові умови, оптимізація може піти іншим, більш вдалим шляхом. Ось, власне, і все, що потрібно знати для успішної роботи з таким потужним інструментом, як «Пошук рішення» MS Excel 2010. Як бачите, бухгалтерський облік є досить різноманітним, щоб у ньому знайшлося місце навіть для складних обчислювальних методів.
Наш сайт є корисним для вас?
Підписатися на найактуальніше розсилання для бухгалтера бюджетної установи