Теми статей
Обрати теми

Пошук рішення в Excel 2007. Частина 2

Редакція БК
Стаття

Пошук рішення в Excel 2007.
Частина 2

 

img 1

Підбір суми за накладною — не єдиний приклад використання надбудови «Поиск решения» у практиці бухгалтера-професіонала. Є безліч завдань, коли цей інструмент допоможе швидко знайти відповідне рішення там, де потрібно виконати насправді астрономічний обсяг обчислювальної роботи. Одне з таких завдань, що найбезпосередніше стосується бухгалтерської практики, ми й розглянемо у цій статті. А заразом закріпимо наші знання у плані використання оптимізатора зі складними умовами для пошуку рішень.

Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства

 

Перш ніж продовжити роботу з надбудовою «

Поиск решения», я пропоную сьогодні повернутися до завдання підбору суми за накладною, але тепер ми змінемо набір умов для оптимізації. Ми вже знаємо, що при роботі з оптимізатором найголовніше — правильно сформулювати завдання. А це означає, що потрібно однозначно задати цільову функцію та обмеження на значення змінних. У нашій попередній статті ми використовували тільки прості обмеження, коли змінні мають бути цілими і додатними числами. Зараз же трохи ускладнимо завдання і цим наблизимо його до умов реального життя.

 

ПІДБІР СУМИ ЗА НАКЛАДНОЮ ТА ДВОСТОРОННІ ОБМЕЖЕННЯ

Почнемо з вихідних даних. Форма накладної в нас не змінилася. Її зображено на рис. 1. Наше завдання — підібрати кількість проданих товарів у накладній на загальну суму «860,83 грн.». Але при цьому потрібно враховувати, що максимальний обсяг для ТМЦ «Ручка гел.» не повинен перевищити 100 шт., для ТМЦ «Карандаш» це обмеження становить 500 шт., а для ТМЦ «Роллер» — 150 шт. Інакше кажучи, наші змінні не лише мають бути цілими додатними числами, вони ще й обмежені зверху максимально допустимим значенням. Технічно вирішення такого завдання не викликає труднощів. Але є важливе зауваження.

img 2

Важливо!

Будь-яке додаткове обмеження в оптимізаторі неминуче скорочує кількість допустимих рішень. Якщо ви вводите нове обмеження, обов’язково проаналізуйте, чи дійсно воно має принципове значення для вирішення поставленого завдання?

У нашому випадку обмеження цілком розумні. Єдине, що я б зробив, — це відразу ж подивився б, чи можна отримати суму 860,83 грн. при максимальному значенні змінних. Якщо цього зробити не можна, то завдання, м’яко кажучи, не має сенсу. Перевіряємо: 100 х 1,05 + 500 х 0,75 + 150 х 2,2 = 810 (грн.). Додаємо ПДВ: 810 х 0,2 = 162 (грн.). Бачимо, що максимально можлива сума в накладній за наших обмежень становитиме 810 + 162 = 972 (грн.) Це означає, що у принципі завдання вирішувати можна. Далі виконуємо такі дії:

1) викликаємо пункт «Данные» головного меню;

2) у розділі «Анализ» клацаємо по іконці «Поиск решения». На екрані з’явиться вікно з параметрами, як показано на рис. 2. Оптимізатор запам’ятав свої настройки з попереднього сеансу роботи і показав їх у незмінному вигляді. Тож ми мусимо ці настройки виправити або доповнити;

img 3

3) у вікні «Поиск решения» для цільової функції зазначено комірку «K41». Нагадаю, що в ній записано формулу для визначення різниці між сумою в накладній та бажаним значенням, тобто «=ABS(K40-K39)». Цей параметр залишаємо без змін;

4) критерій оптимізації теж не змінюємо. Це буде мінімізація значення в комірці «K41»;

5) комірки зі змінними (у нас це блок «E29:E31») залишаємо. Ми, як і раніше, змінюватимемо кількості товарів у документі. Переходимо до формування обмежень;

6) в обмеженнях уже записано два вирази: значення у блоці «E29:E31» мають бути цілими і додатними числами. Нам потрібно додати ще три обмеження на максимальне значення цих змінних;

7) у вікні «Поиск решения» клацаємо по кнопці «Добавить». Відкриється вікно «Добавление ограничения», як показано на рис. 3;

img 4

8) ставимо курсор на область параметра «Ссылка на ячейку:». Клацаємо по комірці «E29»;

9) клацаємо по параметру для вибору операції порівняння. Зі списку, що відкрився, вибираємо «<=»;

10) у вікно параметра «Ограничение:» уводимо число «100». Цим ми вказали оптимізатору, що кількість ТМЦ «Ручка гел.» у документі не може перевищувати 100 шт.;

11) у вікні «Добавление ограничения» натискаємо на кнопку «Добавить» і за такою самою схемою вводимо обмеження для комірок «E30», «E31»;

12) закінчивши роботу зі створення обмежень, у вікні «Добавление ограничения» натискаємо на «ОК». Остаточний вигляд вікна «Поиск решения» з усіма параметрами показано на рис. 4;

img 5

13) у вікні «Поиск решения» натискаємо на кнопку «Выполнить». Через деякий час Excel покаже результат, як на рис. 5.

img 6

Як і слід було сподіватися, ми отримали інше рішення. Відразу перевіряємо кількості ТМЦ у накладній. У нашому випадку всі обмеження виконано, значення в комірках «E29:E31» цілі, додатні та не перевищують заданих меж. Отже, параметри оптимізатора зазначено правильно. Загальна сума за накладною становить 860,88 грн., що відрізняється від заданої величини на 0,05 грн. У принципі, результат нормальний, і я б вважав його цілком допустимим.

 

ФОРМУЄМО ПАКЕТ ДОКУМЕНТІВ

Як уже зазначалося, завдань, де оптимізатор здатний надати бухгалтеру відчутну допомогу, багато. Ось іще один приклад з моєї практичної роботи.

Є база даних, як показано на рис. 6. У ній зібрано реєстр документів за звітний період. Реєстр складається з трьох полів: дати документа, короткої назви (колонка «

Документ») і суми витрат у межах документа. З цієї бази потрібно підібрати пакет документів, в якому суму витрат буде максимально наближено до заданого значення. Припустимо, що це значення дорівнює 2172,90 грн. Схожі завдання у практиці бухобліку трапляються часто, наприклад: коли діяльність підприємства має сезонний характер і доводиться вирівнювати прибуток, ув’язуючи її із сумою передоплати за торгові патенти. Ситуацій багато. Думаю, ви зрозуміли, про що йтиметься.

img 7

Щоб з’ясувати для себе завдання, ми спочатку виконаємо його вручну, а потім застосуємо для цієї ж мети оптимізатор. Отже, виконуємо такі дії:

1) додаємо порожній рядок на початку бази даних;

2) ставимо покажчик активної комірки на адресу «

A3»;

3) викликаємо меню «

Вид»;

4) у групі «

Окно» клацаємо по іконці «Закрепить области». Таким чином, ми створили інформаційне табло, як показано на рис. 7;

img 8

5) до комірки «

B1» записуємо формулу «=СУММЕСЛИ(J:J;"д";F:F)». Ця формула аналізує вміст колонки «J». Як тільки в ній з’явиться символ «д», вона підсумовуватиме значення з відповідних комірок у колонці «F». Тепер наше завдання — вибрати з бази документи на суму 2172,90 грн.;

6) у колонці «

J» проставляємо символ «д» та аналізуємо значення в комірці «B1». Послідовно перебираємо варіанти доти, доки сума в комірці «B2» не стане близькою (або рівною) «2172,90».

Я, наприклад, зробив так. Спочатку відсортував діапазон з даними за убуванням поля «

СУММА» і після цього приступив до формування пакета, починаючи з великих сум та поступово рухаючись униз до менших сум. Після декількох експериментів я отримав рішення, як показано на рис. 7. Загальна сума за вибраними документами склала 2171,69, що відрізняється від заданого значення на 1,21 грн.

Тепер зробимо те саме, але за допомогою надбудови «

Поиск решения». Виконуємо такі дії:

1) відкриваємо базу даних, як показано на рис. 6 (лист «

Опт1»);

2) праворуч від стовпця «

СУММА» додаємо дві колонки: «Пр» і «Сумма_»;

3) значення в колонці «

Пр» не заповнюємо. Це зробить оптимізатор;

4) до комірки «

K2» вводимо формулу «=J2*F2» та копіюємо її вниз на всю висоту таблиці. Тепер якщо в колонці «Пр» проставити «1», то відповідні значення зі стовпця «СУММА» з’являться в колонці «Сумма_»;

5) у вільній комірці листа підраховуємо суму за колонкою «

Сумма_». У нашому прикладі це значення підраховано в комірці «K34». Формула для розрахунку елементарна: «=СУММ(K2:K33)»;

6) до комірки «

K35» уводимо бажане значення. У нас воно становить «2172,90»;

7) до комірки «

K36» уводимо формулу «=ABS(K35-K34)». Усе готово до роботи (рис. 8);

img 9

8) викликаємо меню «

Данные»;

9) у групі «

Анализ» клацаємо по іконці «Поиск решения»;

10) в однойменному вікні заповнюємо параметри, як показано на рис. 9. Тут змінними комірками є елементи колонки «

Пр». Обмеження у вигляді «$J$2:$J$33 = двоичное» змусить оптимізатор ставити до комірок «$J$2:$J$33» тільки «0» чи «1». Відповідно змінюватиметься сума в комірці «K34». Перебираючи різні варіанти розставляння «0» і «1» у комірках колонки «Пр», Excel шукатиме відповідне рішення. Критерій пошуку — мінімальне відхилення суми в комірці «K34» від заданого значення, записаного в комірці «K35»;

11) заповнивши параметри у вікні «

Поиск решения», натискаємо на кнопку «Выполнить». Через деякий час отримаємо результат, як показано на рис. 10 (див. с. 26). Excel підібрав пакет документів, сума витрат у яких становить 2172,87 грн. Відхилення від заданого значення склало тільки три копійки. Як на мене, більш ніж пристойний результат!

Повернемося до вікна «

Поиск решения», зображеного на рис. 9. З ним ми працювали вже не раз, але всіх елементів управління так і не розглянули. У вікні є дев’ять кнопок, ось основні з них. Кнопка «Предположить» заповнює параметр «Изменяя ячейки:». Я цією кнопкою у своїй практиці не скористався жодного разу. Простіше ввести адресу безпосередньо до поля «Изменяя ячейки:».

img 10

 

img 11

Три кнопки праворуч від області «

Ограничения:» працюють так. Кнопка «Добавить» відкриває вікно для формування нового обмеження. Кнопка «Изменить» відкриває вікно для зміни існуючого обмеження (його потрібно перед цим виділити в наявному списку). Кнопка «Удалить» знімає обмеження.

Праворуч у вікні «

Поиск решения» розташовано п’ять кнопок. Ось їх призначення. Клацання по кнопці «Выполнить» починає процес пошуку рішення. Кнопка «Закрыть» завершує роботу з оптимізатором. Кнопка «Параметры» відкриває доступ до настройок оптимізатора. За допомогою кнопки «Восстановить» ви можете повернути настройки оптимізатора за умовчанням. Клацання по кнопці «Справка» відкриває довідкову систему по роботі з Excel 2007.

Хочу звернути вашу увагу на важливий момент. При роботі з надбудовою «

Поиск решения» доводиться вводити велику кількість параметрів. Виникає запитання: як зберігає ці параметри програма Excel 2007? Що буде, коли знадобиться застосувати надбудову до кількох примірників даних? Щоб розібратися з цим питанням, виконуємо такі дії:

1) відкриваємо документ з базою даних, як показано на рис. 6;

2) копіюємо всю базу до буфера обміну (натискаємо на «

Ctrl+A», потім на «Ctrl+С»);

3) створюємо новий робочий лист. Я назвав його «

Опт2»;

4) вставляємо на цей лист уміст буфера обміну («

Ctrl+V»);

5) викликаємо меню «

Данные», у групі «Анализ» клацаємо по іконці «Поиск решения». Відкриється вікно «Поиск решения», як показано на рис. 9, але всі параметри у ньому будуть порожніми;

6) виконуємо настроювання параметрів оптимізатора для даних на листі «

Опт2». Усі обмеження залишаємо, як у попередньому прикладі. Змінимо тільки значення в комірці «K35». Вважатимемо, що загальна сума пакета документів має скласти 5000,00 грн.;

7) повертаємося на лист «

Опт1»;

8) викликаємо меню «

Данные», клацаємо по кнопці «Поиск решения». Відкриється вікно настройок, як показано на рис. 9. У ньому ми побачимо, що всі параметри пошуку рішення для листа «Опт1» залишилися без змін.

Важливо!

Програма Excel 2007 зберігає настройки оптимізатора індивідуально для кожного листа робочої книги. При цьому діє загальне правило: «один лист — один сценарій», тобто в межах одного листа не можна зберегти кілька сценаріїв оптимізації для різних наборів даних.

 

ділимо ЦІЛЕ НА ЧАСТИНи

Цей невеликий приклад безпосередньо не стосується бухгалтерського обліку. Хоча чомусь вирішити його довелося саме нам, працівникам бухгалтерії. А завдання було таке. Недавно в нашому офісі потрібно було оновити комп’ютерну мережу. Для цього знадобилося один шматок кабелю завдовжки 125 метрів розрізати на частини, причому зробити так, щоб зайвий залишок кабелю був мінімальним. Нам були відомі розміри відрізків кабелю, що знадобляться для монтажу мережі, а також мінімальна і максимальна кількість відрізків кожної довжини, які ми повинні були отримати в підсумку. Виконуємо такі дії:

1) створюємо невелику таблицю, як показано на рис. 11. До комірок «

A2:A7» уводимо дані про довжину відрізків кабелю, які ми використовуватимемо. Нам знадобляться відрізки завдовжки «1,5», «2,2», «4,5», «7», «9» і «11,5» метрів. Стовбець «Кол-во» (комірки «B2:B7») — це змінні. Сюди оптимізатор запише точну кількість відрізків кожного типорозміру, які ми повинні нарізати з однієї бобіни. У колонці «Минимум» я проставив мінімальну кількість відрізків кожного типу, у колонці «Максимум» — максимальну кількість таких відрізків;

img 12

 

img 13

2) до комірки «

B8» записуємо формулу «=СУММПРОИЗВ(A2:A7;B2:B7)». Вона послідовно перемножує вміст комірок «A2:A7» на комірки «B2:B7» та підсумовує отримані значення. У такий спосіб ми визначимо загальну довжину відрізків відповідно до тієї їх кількості, яку записано в колонці «Кол-во»;

3) до комірки «

B9» заносимо довжину кабелю в бобіні. Цей кабель ми повинні розрізати;

4) до комірки «

B10» уводимо формулу «=ABS(B9-B8)». Це довжина зайвого залишку кабелю за умови, що вміст у комірці «B9» буде не менше значення в комірці «B8»;

5) викликаємо надбудову «

Поиск решения» та вводимо обмеження, як показано на рис. 12. Умови «$B$2:$B$7< = $D$2:$D$7» і «$B$2:$B$7> = $C$2:$C$7» означають, що значення змінних мають бути не більше максимальних і не менше мінімальних. Кількості мають бути цілими додатними числами («$B$2:$B$7=целое», «$B$2:$B$7>=0»). А сумарна довжина кабелю в бобіні має перевищувати або дорівнювати сумі довжин отриманих відрізків («$B$9>=$B$8»);

6) у вікні «

Поиск решения» натискуємо на «ОК». Через деякий час оптимізатор запропонує рішення, як показано на рис. 11. Наріжемо з однієї бобіни відрізки відповідно до чисел з колонки «Кол-во», залишок невикористаного кабелю при цьому дорівнюватиме нулю.

Нарешті, наша остання дія з надбудовою «

Поиск решения» буде такою:

1) у таблиці на рис. 11 виділяємо колонку «

Максимум» (або будь-яку іншу, на ваш розсуд);

2) через меню (або праву кнопку миші) вставляємо на робочий лист нову колонку;

3) викликаємо меню «

Дата», потім надбудову «Поиск решения». Дивимося на значення в параметрах оптимізатора.

Важливо! При додаванні, переміщенні або видаленні рядків та колонок на робочому листі адреси в параметрах оптимізатора зміняться разом з адресами комірок. При зміні робочого листа наново перенастроювати оптимізатор вам не доведеться: Excel зробить це автоматично. Головне, щоб усі комірки, потрібні йому для роботи, залишилися на листі та містили правильні дані.

 

ЯКЩО ОПТИМІЗАТОР НЕ ЗНАЙШОВ потрібного РІШЕННЯ

Така ситуація трапляється досить часто. Досвід показує, що здебільшого це результат неправильної (або неповної) постановки завдання. Можливо, цільову комірку вибрано неправильно або введено не всі обмеження. Варіантів багато. У будь-якому разі насамперед виконайте такі дії:

— запитайте себе, чи впевнені ви, що рішення існує? Наведіть докази цієї впевненості;

— перевірте, чи немає помилок у моделі, зокрема, чи не захищено випадково від змін комірки зі змінними;

— переконайтеся, що для всіх змінних комірок зазначено обмеження. Пам’ятайте залізне правило: оптимізація без обмежень не має сенсу. Узагалі створення системи обмежень — найвідповідальніший етап при роботі з оптимізатором. Цілком можливо, що з першого разу тут щось не спрацює і надбудова «

Поиск решения» видаватиме незрозумілі результати. Вихід із ситуації тільки один. Кажуть, що з проблемою треба переспати. Спробуйте відпочити й подивитися на модель зранку, на свіжу голову.

І ще. Практика показує, що лінійну цілочисельну задачу з десятками змінних оптимізатор вирішує за прийнятний час навіть на малопотужному (за сьогоднішніми мірками) комп’ютері. Якщо модель дуже велика, подумайте, чи не можна її скоротити, наприклад, звузити діапазон пошуку, виключивши частину змінних та відкоригувавши відповідним чином шукане значення цільової функції?

Якщо стартувати з початковими значеннями змінних, що дають результат того самого порядку, що й шуканий, процес пошуку закінчиться швидше.

Якщо змінити стартові умови, оптимізація може піти іншим, можливо, більш успішним шляхом. Щоправда, надбудова «

Поиск решения» відзначається в цьому плані завидною постійністю і часто «ходить по колу».

Нарешті, останнє. При пошуку рішення оптимізатор намагається задовольнити всі обмеження, у тому числі вимоги до відносної похибки та допустимого відхилення. У результаті роботи оптимізатора у комірці, що «впливає» на рішення, на яку накладено цілочисельне обмеження, може виявитися не ціле число.

Порада

Завжди використовуйте для цілочисельних комірок зі змінними формат «Общий». У цьому випадку ви відразу зможете побачити комірки з дробовими числами. Це допоможе уникнути проблем при перенесенні результатів оптимізації до бухгалтерської програми. Пам’ятайте, якщо використовувати в моделі функцію округлення до цілих (наприклад, «=ОКРУГЛ(F2;0)»), задача стане нелінійною.

Ось, власне, і все, що потрібно знати про роботу надбудови «Поиск решения». Як бачите, бухгалтерський облік такий різноманітний, що в ньому знайдеться місце навіть для складних обчислювальних методів. Головне в такій ситуації — зрозуміти суть процесу і, застосовуючи математику, отримувати з цього користь.

На цьому ми завершимо опис надбудови «Поиск решения», а в наступній статті познайомимося з можливостями інших надбудов програми Excel 2007.

 

Успішної роботи! Чекаю ваших листів, пропозицій та зауважень на

bk@id.factor.ua, nictomkar@rambler.ru або форумі редакції www.bk.factor.ua/forum .
App
Завантажуйте наш мобільний додаток Factor

© Factor.Media, 1995 -
Всі права захищені

Використання матеріалів без узгодження з редакцією заборонено

Ознайомитись з договором-офертою

Приєднуйтесь
Адреса
м. Харків, 61002, вул. Сумська, 106а
Ми приймаємо
ic-privat ic-visa ic-visa

Ми використовуємо cookie-файли, щоб зробити сайт максимально зручним для вас та аналізувати використання наших продуктів та послуг, щоб збільшити якість рекламних та маркетингових активностей. Дізнатися більше про те, як ми використовуємо ці файли можна тут.

Дякуємо, що читаєте нас Увійдіть і читайте далі