Excel 2007: Підбір параметра
 | У попередніх статтях ми докладно ознайомилися з надбудовою «Поиск решения» та подивилися, як можна її застосувати до вирішення насущних бухгалтерських завдань. Однак в арсеналі Excel 2007 є й інші інструменти для аналізу даних. І хоча можливості цих інструментів набагато скромніші, користь від їх застосування у практиці бухгалтерського обліку може виявитися анітрохи не меншою. У цій статті розглянемо роботу з одним із таких інструментів, який називається «Подбор параметра». І, як завжди, зробимо це на конкретних прикладах. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
До цього моменту ми попрацювали з однією з надбудов Excel, яка називається «
Поиск решения». Зараз ітиметься про схожий, але зовсім інший інструмент, — «Подбор параметра», який по-своєму цікавий з точки зору практичної роботи. По суті, «Подбор параметра» — це не надбудова, а вбудований засіб Excel 2007 для вирішення рівнянь. У бухгалтерських розрахунках роль цього інструменту важко переоцінити. Адже він дозволяє організувати будь-який розрахунок у зворотному порядку — від бажаного результату до значень вихідних даних. А такі розрахунки у практиці бухгалтерського обліку зустрічаються набагато частіше, ніж може здатися на перший погляд.
Для чого потрібні рівняння
Перш ніж приступити до практичної роботи з інструментом «
Подбор параметра», я пропоную трохи відволіктися та подивитися на роботу бухгалтера очима математика чи програміста. З цієї точки зору суть бухгалтерського обліку дуже проста. У нас є вихідна інформація (з первинних документів, нормативних актів тощо), організована в бази даних. Це можуть бути бази програми «1С», Excel чи будь-якої іншої — смисл від цього не змінюється. Наявну інформацію за допомогою фільтрів, сортувань, зведених звітів та арифметичних операцій бухгалтер перетворить у кінцевий звіт. Тобто можна сказати, що в цьому випадку йдеться про пряме завдання, коли послідовність рішення направлена від вихідних даних (їх значення нам первісно відомі) до кінцевого результату. У реальному житті цього буває недостатньо. І доводиться вирішувати зворотне завдання. Коли відомий результат і потрібно з’ясувати, при яких значеннях вихідних даних його можна отримати. А це вже не прямий розрахунок. Це — вирішення рівнянь. У практичній роботі бухгалтера можна навести чимало прикладів зворотних завдань. Ось тільки деякі з них. Потрібно сформувати накладну під заздалегідь задану суму (з таким завданням ми вже зустрічалися, коли працювали з інструментом «Поиск решения»). Або ми бажаємо порахувати суму нарахованої зарплати, якщо відома сума до видачі. Або потрібно визначити допустиму величину умовно-постійних витрат у собівартості виробу, якщо обмежена його ціна. Ось ще цікавий приклад: визначити мінімально допустиму (граничну) націнку, якщо відомі обсяги та динаміка реалізації. Варіантів безліч. Але в будь-якому разі всі перелічені завдання так чи інакше пов’язані з вирішенням рівнянь.Для подібних завдань в Excel передбачено два принципово різні інструменти. Перший — це «
Поиск решения», з ним ми вже знайомі. По суті, він оперує не одним, а цілою системою рівнянь. Нагадаю: при роботі з пошуком рішення ми вказуємо групу змінюваних змінних (комірок), обмеження на їх граничні значення та формулу з цільовою функцією. За цими даними Excel побудує систему з декількох рівнянь. А надбудова «Поиск решения», обробивши цю систему, знайде для неї допустимий результат.Другий інструмент для роботи з рівняннями простіший, і називається він «
Подбор параметра». Тут ми можемо оперувати тільки одним рівнянням. Для нього ми вводимо бажаний результат, вказуємо одну змінну, від якої залежить рішення, а Excel визначить точне значення цієї змінної. Однак, перш ніж скористатися інструментом «Подбор параметра», нам буде потрібно якесь завдання. Я пропоную вибрати як таке завдання розрахунок заробітної плати та відрахувань.
Формуємо відомість із зарплати
Використовувати відомість розрахунку зарплати як приклад для роботи з інструментом «
Подбор параметра» я запропонував неспроста. Річ у тім, що при формуванні такої відомості нам доведеться застосувати досить різноманітні формули, задіяти в роботі декілька листів, підключити логічні функції. Усе це разом узяте дозволить оцінити, наскільки добре впорається інструмент «Подбор параметра» з поставленим завданням.Почнемо з вихідних даних. Для цього створюємо робочий лист з ім’ям «
Спр». До нього заносимо дані, як показано на рис. 1. Цифри ці добре відомі кожному бухгалтеру і докладних коментарів не потребують. На листі зібрано основні нормативні ставки та коефіцієнти для розрахунку утримань із зарплати працівника та нарахувань на фонд оплати праці. Більша частина інформації на листі «Спр» — це звичайні числа. Але частину даних розраховано за формулами. Ці формули наведено в табл. 1. 
Таблиця 1. Формули для розрахунку значень на листі
«Спр»| Адрес | Формула | Коментар |
| «B5» | =B4 | Поточний прожитковий мінімум прийняли таким, що дорівнює мінімальній зарплаті |
| «B7» | =ОКРУГЛ(B6*1,4;-1) | Граничну суму для соціальної пільги розраховано від розміру прожиткового мінімуму на початок року з коефіцієнтом 1,4. Результат округлено з точністю до десяти гривень |
| «B8» | =ОКРУГЛ(B6*0,5;0) | Суму пільги визначено від мінімальної зарплати з коефіцієнтом 0,5 |
| «B14» | =B4 | Граничну суму для соцстраху прийнято такою, що дорівнює розміру мінімальної зарплати на початок року |
| «B24» | =15*B5 | Гранична сума для розрахунку ПДФО |
Довідково-нормативну інформацію для виконання розрахунків із зарплати ми підготували. Переходимо до формування самої відомості із зарплати. Для цього створюємо новий робочий лист. У прикладі я назвав його «
Ведомость». На цьому листі будуємо таблицю, як наведено на рис. 2. Шапку відомості не оформлятимемо, у нас зараз інше завдання. Нам достатньо ввести тільки формули і на них випробувати інструмент «Подбор параметра». 
Вихідними даними для формування відомості будуть: оклад, кількість пільг та кількість відпрацьованих днів за розрахунковий період. Ця інформація записана в колонках «C», «D», «E» робочого листа «Ведомость». До колонки «F» введено формулу для розрахунку розміру основної заробітної плати. Для комірки «F3» ця формула виглядає так: «=ОКРУГЛ(C3*E3/Спр!$B$26;2)». Вона визначає розмір зарплати, виходячи з фактично відпрацьованого часу за умови, що загальний фонд часу у звітному місяці становить 21 день (комірка «B26» на листі «Спр», рис. 1). Цю формулу копіюємо на всю висоту таблиці.
Суми надбавки, премії, виплат за листком непрацездатності та інших нарахувань заносимо до колонок із «G» по «J». У колонці «K» підсумовуємо всі нарахування. Формула в комірці «K3» виглядає так: «=СУММ(F3:J3)». Це база для розрахунку відрахувань із зарплати. Формулу з комірки «K3» копіюємо на всю висоту таблиці.
У колонках з «L» по «S» розставляємо формули для розрахунку відрахувань із заробітної плати працівників. Почнемо з Пенсійного фонду. За основу беремо нараховану зарплату (комірка «K3») та помножуємо її на відповідний відсоток, який розташований на листі «Спр» у комірці «B$18». Результат округлюємо до двох знаків. Отже, формула для розрахунку відрахувань до Пенсійного фонду для комірки «M3» виглядає так: «=ОКРУГЛ(K3*Спр!B$18;2)». Копіюємо цю формулу з комірки «M3» вниз до комірки «M7».
За базу для розрахунку відрахувань до фондів соцстраху з безробіття та тимчасової непрацездатності беремо нараховану зарплату за вирахуванням виплат за листком непрацездатності («K3-I3»). Відсоток відрахувань до Фонду з безробіття беремо з листа «Спр», комірка «B10». Результат округлюємо до двох знаків після коми. Тобто формула в «N3» вийде такою: «=ОКРУГЛ(Спр!B$10*(K3-I3);2».
Розрахунок суми відрахувань до Фонду соцстраху з тимчасової непрацездатності організуємо у двох колонках. Одна — для відрахувань за ставкою 0,5 %, друга — для відрахувань за ставкою 1 %. База для розрахунку — нарахована зарплата за вирахуванням лікарняних («K3-I3»).
Переходимо до розрахунку податку на доходи фізичних осіб. Це — найскладніша формула в нашій відомості. Насамперед порівняємо нараховану зарплату з граничною сумою для застосування пільги. Ця сума у нас знаходиться на листі «Спр» у комірці «B7» (рис. 1). Якщо нарахована зарплата виявиться менше граничної суми для застосування пільг, оподатковувана сума зменшується на розмір пільги. В іншому разі як базу для прибуткового податку беремо суму всіх нарахувань (за вирахуванням утримань за соціальним страхуванням) без урахування пільги. Застосовуємо ставку податку 15 %. Це значення знаходиться на листі «Спр» у комірці «B3». Отже, у першому наближенні формула для розрахунку податку буде такою: «ОКРУГЛ(ЕСЛИ(K3<=Спр!B$7;(K3-(M3+N3+O3+P3)-D3*Спр!B$8)*Спр!B$3;(K3-(M3+N3+O3+P3)) *Спр!B$3);2)».
Здебільшого вона відпрацює правильно. За винятком ситуації, коли розмір нарахованої пільги перевищить суму зарплати за вирахуванням утримань за соціальним страхуванням. Для цього до формули потрібно внести ще одну перевірку, і в остаточному варіанті вираз для визначення податку на доходи фізичних осіб виглядатиме так: «=ЕСЛИ(D3*Спр!B$8>(K3-(M3+N3+O3+P3));0;ОКРУГЛ(ЕСЛИ(K3<=Спр!B$7;(K3-(M3+N3+O3+P3)-D3*Спр!B$8)*Спр!B$3;(K3-(M3+N3+O3+P3))*Спр!B$3);2))». Вводимо цю формулу до комірки «L3» та копіюємо її на всю висоту таблиці до комірки «L7». Дані про формули на листі «Ведомость» та коментарі до них наведено в табл. 2.
Таблиця 2. Основні формули документа «Ведомость по зарплате»
| Адреса | Формула | Коментар |
| «F3» | =ОКРУГЛ(C3*E3/Спр!$B$26;2) | Сума зарплати з урахуванням фактично відпрацьованого часу. Формулу копіюємо з «F3» у комірки «F4:F7» (рис. 2) |
| «K3» | =СУММ(F3:J3) | База для розрахунку ПДФО. Включає основну зарплату, надбавки, премії, виплати за листком непрацездатності та інші нарахування. Формулу копіюємо з «K3» у комірки «K4:K7» |
| «L3» | =ЕСЛИ(D3*Спр!B$8>(K3-(M3+N3+O3+P3));0;ОКРУГЛ(ЕСЛИ(K3<=Спр!B$7;(K3-(M3+N3+O3+P3)-D3*Спр!B$8)*Спр!B$3;(K3-(M3+N3+O3+P3))*Спр!B$3);2)) | Формула для розрахунку суми ПДФО. Формулу копіюємо з «L3» у комірки «L4:L7» |
| «M3» | =ОКРУГЛ(K3*Спр!B$18;2) | Відрахування до Пенсійного фонду. Формулу копіюємо з «M3» у комірки «M4:M7» |
| «N3» | =ОКРУГЛ(Спр!B$10*(K3-I3);2) | Відрахування до Фонду з безробіття. Формулу копіюємо з «N3» у комірки «N4:N7» |
| «O3» | =ОКРУГЛ(ЕСЛИ(K3>=Спр!B$14;0;Спр!B$13*(K3-I3));2) | Відрахування до Фонду соцстраху з тимчасової втрати працездатності за ставкою 0,5 %. Формулу копіюємо з «O3» у комірки «O4:O7» |
| «P3» | =ОКРУГЛ(ЕСЛИ(K3>=Спр!B$14;Спр!B$15*(K3-I3);0);2) | Відрахування до Фонду соцстраху з тимчасової втрати працездатності за ставкою 1 %. Формулу копіюємо з «P3» у комірки «P4:P7» |
| «R3» | =СУММ(L3:Q3) | Сума всіх утримань щодо працівника. Формулу копіюємо з «R3» у комірки «R4:R7» |
| «S3» | =K3-R3 | Сума до видачі. Формулу копіюємо з «S3» у комірки «S4:S7» |
| «F8» | =СУММ(F3:F7) | Підсумок за колонкою. Формулу копіюємо з «F8» до блоку «G8:S8» |
Важливо!
Розроблений нами документ у жодному разі не є повноцінним засобом для розрахунку заробітної плати. Щоб вирішити таке завдання в повному обсязі, над нашою таблицею потрібно неабияк попрацювати. Але для наших цілей це не принципово. Щоб попрацювати з інструментом «Подбор параметра», підготовленого прикладу більш ніж достатньо.
Застосовуємо «Подбор параметра»
Отже, що ми маємо зараз? У нас є відомість, де всі розрахунки йдуть у напрямку від нарахованої суми до кінцевого результату. Таким результатом є сума до видачі. Щоб визначити цю суму, використовуються формули, в яких є логічні функції, функції підсумовування та округлення. Тепер наше завдання буде таким. Відома сума, яку потрібно видати на руки. Наприклад, працівнику «Ляшенко В. В.» ми збираємося виплатити «чистими» 2950,00 грн. Для цієї суми потрібно визначити розмір нарахованої заробітної плати. Робимо так:
1) викликаємо Excel 2007, у ньому відкриваємо документ «
Зарплата.xls»;2) переходимо на лист «
Ведомость» (рис. 2);3) клацаємо по меню «
Данные». Стрічка цього меню показана на рис. 3 (див. с. 70);4) у групі «
Работа с данными» клацаємо по іконці «Анализ “что-если”». Відкриється список із трьох пунктів, як зображено на рис. 3; 
5) вибираємо пункт «Подбор параметра…». З’явиться вікно, як наведено на рис. 4 (див. с. 70);
6) у цьому вікні клацаємо мишею по полю «Установить в ячейке:» (входимо до цього поля);
7) клацаємо лівою кнопкою миші по комірці «S3» робочого листа. У цій комірці записана формула для визначення суми до видачі для працівника «Ляшенко В. В.»;
8) переходимо до поля «Значение:». Сюди вводимо число «2950»;
9) переходимо до поля «Изменяя значение ячейки:»;
10) клацаємо по комірці «K3». Тут знаходиться сума нарахованої зарплати для працівника «Ляшенко В. В.»;
11) у вікні «Подбор параметра» натискаємо «ОК». У результаті Excel видасть попередження про помилку. І це цілком логічно.
Важливо! Для роботи інструменту «Подбор параметра» змінна комірка повинна містити тільки числове значення (або бути порожньою). Формули в змінних комірках недопустимі.
Виправимо нашу помилку. Робимо так:
1) відкриваємо документ «
Зарплата», через іконку «Анализ “что-если”» викликаємо інструмент «Подбор параметра…»;2) параметри «
Установить в ячейке:» та «Значение:» залишаємо без змін;3) переходимо до параметра «
Изменяя значение ячейки:»;4) клацаємо лівою кнопкою по комірці «
F3» робочого листа. Тим самим ми вказали Excel, що підбирати нараховану суму він повинен за рахунок зміни розміру основної заробітної плати;5) у вікні «
Подбор параметра» натискуємо «ОК». З’явиться вікно з повідомленням про результати пошуку, як наведено на рис. 5. Відомість із зарплати при цьому набуде вигляду, як на зображено на рис. 6. Тепер у рядку для працівника «Ляшенко В. В.» установлена основна зарплата в розмірі 3400,19 грн. (комірка «F3»). Сума нарахованої зарплати разом з надбавкою вийде 3600,19 грн., а сума до видачі при цьому буде 2950,00 грн. Що й потрібно було отримати. 
При роботі з інструментом «Подбор параметра» багатьох бентежить той факт, що змінна комірка повинна містити тільки числа. Насправді це не є обмеженням. Припустимо, що в нашій відомості суму основної зарплати розраховано за формулою. Наприклад, виходячи з базового окладу з поправкою на коефіцієнт трудової участі. У цьому випадку в колонці «Осн. ЗП» буде записано формули. Застосувати «Подбор параметра» для працівника «Ляшенко В. В.» з нашими настройками не вийде. Вирішити таку проблему дуже просто. Потрібно як змінну комірку вказати, скажімо, «J3». Оскільки для працівника «Ляшенко В. В.» вона первісно порожня, після роботи інструменту «Поиск решения» у ній з’явиться сума, яка в результаті забезпечує розмір нарахованої зарплати 3600,19 грн. і відповідно суму до видачі 2950,00 грн.
Підбір параметра та робота зі списками
На практиці можливі ситуації, коли «
Подбор параметра» не впорається з поставленим завданням. Це слід мати на увазі, коли ви спробуєте застосувати цей інструмент до складної таблиці, де є функції підстановки значень зі списку. Я поясню викладене на реальному прикладі з моєї практики, максимально його спростивши. Залишу тільки найголовніше, так би мовити, суть проблеми.Завдання було таким. Є документ, наведений на рис. 7. У ньому зібрано відомості про замовлення на придбання товарів. У колонці «
Наименование» записана назва замовленого товару. У колонці «Цена» — вартість одиниці товару. У колонці «Кол-во» зазначено обсяг замовлення за кожною позицією. Сума закупівлі за кожним рядком визначається як добуток ціни на кількість, але з урахуванням знижки. Відомості про знижки зібрано в окрему таблицю. На рис. 7 ця таблиця розташована в комірках «F2:G6». У лівій колонці таблиці зазначено обсяг товару (у штуках). Праворуч біля обсягу записано відсоток знижки. Наприклад, при купівлі від одного до двох виробів знижки немає. При купівлі більше трьох виробів працює знижка 3 %. При купівлі п’яти і більше товарів діє знижка 5 % і так далі. Підсумкова вартість замовлення з урахуванням знижки обчислена в колонці «Сумма». Формула в комірці «D2» виглядає так: «=B2*(C2-C2*ВПР(C2;$F$2:$G$6;2;1))». У цій формулі функція «ВПР()» аналізує таблицю знижок «$F$2:$G$6». У першій колонці цієї таблиці вона знаходить обсяг закупівлі за товаром у поточному рядку. Для позиції «Свитер, муж.» це значення знаходиться в комірці «C2». Як результат функція «ВПР()» повертає значення з другої колонки таблиці знижок. Для позиції «Свитер, муж.» при обсязі закупівлі 3 шт. це буде величина 3 %. Тоді розмір знижки можна записати формулою «C2*ВПР(C2;$F$2:$G$6;2;1)». А сама ціна за вирахуванням знижки виглядатиме так: «(C2-C2*ВПР(C2;$F$2:$G$6;2;0))». Отримане значення помножуємо на обсяг замовлення (у штуках) та одержуємо остаточну формулу для визначення вартості за конкретним товаром з урахуванням знижки: «=B2*(C2-C2*ВПР(C2;$F$2:$G$6;2;0))». Цю формулу копіюємо вниз на всю таблицю. 
Спробуємо застосувати інструмент «Подбор параметра» до отриманої таблиці (рис. 7). Наша мета — визначити обсяг закупівлі за товаром «Свитер, муж.» так, щоб сума замовлення за цією позицією становила 2000,00 грн. Робимо так:
1) відкриваємо документ з таблицею, як зображено на рис. 7;
2) через іконку «Анализ “что-если”» викликаємо інструмент «Подбор параметра…». Відкриється однойменне вікно «Подбор параметра»;
3) параметри в цьому вікні заповнюємо, як показано на рис. 8, тобто як цільову комірку вказуємо загальну суму за товаром з урахуванням знижки, а змінною коміркою у нас буде кількість замовленого товару;
4) натискаємо «ОК». Excel підбере результат, як зображено на рис. 9.
Як і слід було очікувати, значення кількості в комірці «С2» вийшло дробовим (5,26316). Це наслідок того, що в інструменті «Подбор параметра» немає поняття обмежень на змінні. Проте рішення отримано. Порядок величини відомий, а округлити результат можна і вручну.
Тепер я пропоную дещо відкоригувати формулу для визначення суми за замовленням. До комірки «D2» пишемо вираз «=B2*(C2-C2*ВПР(C2;$F$2:$G$6;2;0))». Усе, що ми змінили, — це тип пошуку у функції «ВПР()»: її третій параметр тепер дорівнює «0». З таким значенням параметра функція «ВПР()» шукатиме в таблиці знижок значення, що точно відповідає записаному в комірці «C2». Здавалося б, зовсім незначні зміни… Принципового значення для вирішення завдання вони не матимуть, адже рішення у принципі існує й Excel просто зобов’язаний його знайти. Подивимося, чи так це насправді.
Ще раз викликаємо інструмент «Подбор параметра» та спробуємо знайти обсяг продажів для товару «Свитер, муж.» на суму 2000,00 грн. Цього разу нічого не вийде, замість значення «2000,00» (комірка «D2» після пошуку рішення) ми побачимо результат «#Н/Д». Причина цього проста й полягає вона в алгоритмі роботи інструменту «Подбор параметра». А виглядає цей алгоритм приблизно так.
Спочатку «Подбор параметра» порахує значення цільової комірки при вихідному значенні змінюваної змінної. Потім він трохи змінить змінну в бік збільшення та зменшення. Залежно від того, як зміниться цільова комірка, інструмент «Подбор параметра» продовжуватиме коригувати змінну в той чи інший бік. Причому робити це він буде з певним кроком, перебираючи в такий спосіб різні варіанти кількості товарів. Цей процес триватиме доти, доки Excel не знайде прийнятний результат. І тут є один нюанс. У процесі підбору параметра значення змінюваної змінної в якийсь момент стане дробовим. Дробових чисел у першій колонці таблиці знижок немає (комірки «F2:G6» на рис. 7). Функція «ВПР()» з третім параметром «0» спробує знайти шукане значення в таблиці знижок за принципом точного збігу. Зрозуміло, що дробового числа вона там не знайде і як результат поверне ознаку «#Н/Д». Відповідно в цій ситуації цільова комірка теж буде не визначена і набуде значення «#Н/Д». За такого значення цільової комірки інструмент «Подбор параметра» не зможе визначити, в який бік змінювати змінну: збільшувати її чи зменшувати? Адже функція мети зараз не визначена! І тому всі подальші обчислення не матимуть сенсу. Потрапивши один раз у ситуацію невизначеності, інструмент «Подбор параметра» не зможе рухатися далі. І після завершення його роботи в результуючій комірці залишиться значення «#Н/Д».
Важливо! Якщо ви збираєтеся користуватися інструментом «Подбор параметра», переконайтеся, що при будь-якому можливому значенні змінної комірки результуюча функція буде визначена. Якщо на якомусь проміжному етапі виникне ситуація, що результуюча комірка набуде недопустимого значення, результат пошуку може виявитися безуспішним.
Ось стисло і все, що потрібно знати про такий корисний бухгалтерський інструмент, як «
Подбор параметра». Незважаючи на простоту його використання, цей засіб у багатьох ситуаціях може надати неоціненну допомогу при виконанні різних аналітичних розрахунків.
Успішної роботи! Чекаю ваших листів, пропозицій та зауважень на
bk@id.factor.ua , nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum .
