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; | Формула для расчета суммы НДФЛ. Формулу |
| «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 (на с. 70); 
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 .
