Надбудови 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.Порада При роботі зі складними обмеженнями запишіть формулу в окремій комірці робочого листа. А у вікні настроювання обмежень посилайтеся на адресу цієї комірки.
В останньому випадку ідея рішення буде такою: розцінювати три ролери та одну ручку як комплект, та вирішувати наше завдання щодо змінних «Карандаш» та «Комплект». Завдання залишиться лінійним, а кількість змінних скоротиться до двох.
Декілька слів про обчислювальну складність завдань. Складність лінійного завдання пропорційна кількості змінних. Обчислювальна складність нелінійного завдання пропорційна кількості змінних та характеру математичних зв’язків між ними. Оптимум завдання з простими функціями на кшталт x2 або 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 .
