Расчёт сырьевой себестоимости производства краски
(описание используемых условных обозначений, а также список других публикаций канала по теме электронных таблиц, находится здесь).
При разработке лакокрасочных материалов (ЛКМ) важно знать себестоимость их производства на основе рецептурных данных и цен сырьевых компонентов. Для выполнения необходимых при этом расчётов подготовим инструмент, который может пригодиться, например, технологу завода по производству ЛКМ.
Сначала нужно создать новую книгу и отформатировать ячейки её листа примерно следующим образом:
Для большего удобства рекомендуется в ячейке “I1” установить шрифт крупнее и ещё сделать там полужирное начертание символов. Поскольку в процессе разработки рецептуры ЛКМ доводится тестировать много различных составляющих, то нелишним будет обрисовать границы столбцов “K”, “L” и “M”где-нибудь до 51-й строки, чтобы в диапазоне “K2:M51” было подготовлено место для размещения сведений о полусотне различных сырьевых компонентов красок – в этом случае стоит ещё выполнить фиксацию первой строки листа (Пособие, с. 19). Допустим, в заводской лаборатории проводилась работа со следующими компонентами лакокрасочных материалов (а) и в результате была подобрана рецептура такой водно-дисперсионной краски (б):
Рассчитаем сырьевую себестоимость производства ЛКМ (то есть без учёта затрат на электроэнергию, амортизацию оборудования, оплату труда операторов и т. п.), для чего в ячейках столбца “D” разместим формулу, которая сама будет находить и подставлять значение закупочной цены компонента краски. Эта формула может быть такой (показано на примере ячейки “D2” – в остальные ячейки диапазона “D3:D26” её можно размножить при помощи маркера заполнения):
=ЕСЛИ(B2=""; ""; ЕСЛИ(СЧЁТЕСЛИ($K$2:$K$51;B2)=0; 0; ВПР(B2; $K$2:$M$51; 3; 0)))
=IF(B2=""; ""; IF(COUNTIF($K$2:$K$51;B2)=0; 0; VLOOKUP(B2; $K$2:$M$51; 3; 0)))
Чтобы разобраться, как эта формула работает, рассмотрим сначала синтаксис использованной в ней функции
ВПР( ; ; ; )
VLOOKUP( ; ; ; )
поскольку главную работу выполняет именно она. Итак, в рассматриваемой функции четыре аргумента:
- первый – ячейка, содержимое которой нужно отыскать (наименование сырьевого компонента в рецептуре ЛКМ);
- второй – блок (диапазон) ячеек, образующий фактически таблицу, в первом столбце которой нужно осуществлять поиск (этот столбец представляет собой перечень названий закупаемых сырьевым материалов);
- третий – номер столбца «таблицы» (второго аргумента функции) из которого должны извлекаться необходимые данные в случае обнаружения искомого (в нашем случае это закупочная цена сырьевого компонента);
- четвёртый – дополнительная опция, регулирующая режим работы функции, может принимать значения 0 / 1 (ЛОЖЬ / ИСТИНА). В нашем случае требуется использовать первый вариант (0).
Таким образом эта функция осуществляет поиск цены компонента в имеющемся перечне сырья, однако если поиск оказывается неудачным (например, в ассортименте сырьевых компонентов отсутствует вода), то функция возвращает ошибку исполнения. Для избежания этого служит «оболочка» из других функций «вокруг» только что рассмотренной – для наглядности ниже приведена блок-схема алгоритма работы всей формулы:
После того, как цены на компоненты в рецептуре проставлены, можно посчитать стоимость каждого из них в 1 кг ЛКМ с учётом значений концентраций. Для этого в ячейку “E2” нужно ввести формулу
=ЕСЛИ(D2="";"";C2/100*D2)
=IF(D2="";"";C2/100*D2)
и размножить её маркером заполнения до “E26”.
Для вычисления себестоимости ЛКМ остаётся лишь просуммировать числа из диапазона “E2:E26”. Это делает формула в “I1”:
=ОКРУГЛ(СУММ(E2:E26);2)
=ROUND(SUM(E2:E26);2)
Дополнительно можно организовать вычисление вклада в себестоимость ЛКМ каждого используемого в рецептуре компонента. Для этого в ячейку “F2” нужно ввести формулу (а после откопировать её вниз до “F26”), производящую нормировку совокупности значений стоимостей компонентов в 1 кг ЛКМ:
=ЕСЛИ(E2="";"";ОКРУГЛ(E2/СУММ($E$2:$E$26)*100;3))
=IF(E2="";"";ROUND(E2/SUM($E$2:$E$26)*100;3))
Завершающее действие – установить для блоков “C2:C26” (концентрация компонента в материале по рецептуре) и “F2:F26” (долевой вклад компонента в себестоимость ЛКМ) условное форматирование типа «Гистограмма», чтобы данные в указанных ячейках были визуализированы:
Перечень публикаций на канале