Автоматизация и моделирование бизнес-процессов в Excel

С помощью Excel можно автоматизировать много процессов на предприятии:

• · оптимальный заказ поставки комплектующих
• · модели управления затратами
• · договор купли-продажи
• · учет дебиторской задолженности
• · складской учет
• · планирование денежных потоков и многое другое.

Пример: оптимального заказа поставки комплектующих.

В качестве примера будет рассмотрен процесс автоматизации формирования заказа для цеха по изготовлению аудиокассет. Предположим, что для изготовления аудиокассет используются только два вида комплектующих — корпуса аудиокассет и магнитная лента.

Транспортировка осуществляется в морских контейнерах. Предприятию не выгодно заказывать поставку партий комплектующих из 1−3 контейнеров из-за высоких накладных расходов, связанных с самим фактом поставки. Например, это банковские и таможенные расходы, оплата услуг транспорта на сухопутной территории, отвлечение специалистов и пр. Заказ партии более 5-ти контейнеров вымывает оборотные денежные средства, что также накладно. Поэтому принято управленческое решение, что оптимальная партия поступающих комплектующих — 5 контейнеров.

Известно, что в один морской контейнер входит 975 коробок с корпусами для аудиокассет или 750 коробок с магнитной лентой. В одной коробке находится 200 корпусов или 40 рулонов магнитной ленты. Одного рулона магнитной ленты, в зависимости от типа изготавливаемых аудиокассет, хватает на определенное количество аудиокассет.

Описание выпускаемой продукции

Мощность установленного в цеху оборудования рассчитана на изготовление 1 миллиона аудиокассет в месяц. Цех выпускает пять видов аудиокассет:

• · AKALA;
• · AKALB;
• · AKBLA;
• · AKBLC;
• · AKCLB.

Для изготовления аудиокассет используются корпуса типов:

• · KA;
• · KB;
• · KC;

и магнитная лента типов:

• · LA;
• · LB;
• · LC.

В названии типа производимых аудиокассет используется следующая аббревиатура:

• · первая буква — аудиокассета;
• · следующие две буквы — тип корпуса;
• · последние две буквы — тип магнитной ленты.

Состав разрабатываемого приложения

Рабочей книге, в которой будет создаваться приложение по расчету заказа, присвойте имя — Заказ. Книга Заказ состоит из трех рабочих листов:

• · Кратность — для ввода констант кратности, применяемых для расчетов;
• · Расчет — для расчета непосредственно заказа;
• · Заказ — для формирования бланка заказа поставщикам на приобретение комплектующих.

Рабочий лист. Кратность

На рабочем листе Кратность в столбце С указываются значения.

Рис. 1. Рабочий лист Кратность.

В столбец В введите текст имен, которые будут присвоены ячейкам столбца С со значениями кратности, введенными в них. Такой метод позволит в последующем сделать более понимаемыми формулы, с применяемыми в них именами и ускорит процесс присвоения ячейкам имен. Для одновременного присвоения имен ячейкам с введенными в них значениями кратности, выделите диапазон ячеек В2: С14 и нажмите комбинацию клавиш Ctrl+Shift+F3. В появившемся диалоговом окне Создать имена нажмите кнопку ОК.

Рабочий лист Расчет для формирования параметров заказа

Рабочий лист Расчет (рис. 2.) состоит из областей:

• · ввода месячной программы изготовления аудиокассет;
• · расчета необходимого количества коробок с корпусами для выполнения производственной программы;
• · расчета количества необходимых для выполнения производственной программы коробок с магнитной лентой;
• · расчета количества морских контейнеров, необходимых для перевозки заказанных комплектующих.

Рис. 2. Рабочий лист Расчет

Область ввода плановой месячной производственной программы изготовления аудиокассет

Таблица ввода плановых показателей производства количества аудиокассет находится в области ячеек В3: F9 и состоит из следующих диапазонов:

• · В4: В8 — ввод наименования аудиокассет;
• · С4: С8 — предназначена для определения типа корпусов по типу изготовляемой аудиокассеты;
• · D4: D8 — предназначена для определения типа магнитной ленты по типу изготовляемой аудиокассеты;
• · Е4: Е8 — область ввода планируемого количества изготавливаемых аудиокассет каждого типа;
• · F4: F8 — область расчета количества рулонов магнитной ленты, необходимых для изготовления планируемого объема аудиокассет каждого типа.

Рис. 3. Область ввода планируемого количества изготавливаемых кассет

На рис. 3 показаны планируемые объемы производства аудиокассет на будущий период (месяц).

Количество корпусов равно количеству изготавливаемых кассет. Определение необходимого для производства количества рулонов магнитной ленты (формула показана в строке формул на рис. 3) производится в ячейке F6 делением количества планируемого объема аудиокассет на кратность получения количества аудиокассет из одного рулона, введенную в ячейку С12 на листе Кратность с присвоенным ей именем Кратность_А_КВ_LА.

Определение типа корпусов в ячейке С4 для изготовления аудиокассеты типа, указанного в ячейке В4, осуществляется с помощью формулы, которая вначале с помощью функции ПРАВСИМВ выбирает из текста типа аудиокассеты четыре правых символа, после чего уже из этого текста, функция ЛЕВСИМВ выбирает два левых символа:

=ЛЕВСИМВ (ПРАВСИМВ (B4;4);2)

Для определения типа магнитной ленты в ячейке D4 для изготовления указанного типа аудиокассет в ячейке В4, функция ПРАВСИМВ выбирает два правых символа из текста типа аудиокассеты:

=ПРАВСИМВ (B4;2)

Расчет необходимого количества коробок с корпусами аудиокассет для выполнения планируемой месячной программы

Область расчета необходимого количества коробок с корпусами аудиокассет находится в области ячеек В11: G15 и состоит из двух частей:

диапазон ячеек В11: D15 — область непосредственного расчета количества коробок и находящихся в них корпусов для аудиокассет; диапазон ячеек G12: G14 — область формирования предупредительного текста при обнаружении ошибок для того, чтобы пользователь предпринял действия для их исправления.

Рис. 4. Область расчета количества коробок с корпусами.

В диапазоне ячеек С12: С14 производится расчет количества корпусов типа, указанного в диапазоне ячеек В12: В14. Формула в ячейке С12 основана на функции СУММЕСЛИ, которая по наименованию типа корпуса, введенного в ячейку В12, производит поиск наименований такого типа в диапазоне ячеек С4: С8 и суммирует общее количество корпусов этого типа из области ячеек Е4: Е8:

=СУММЕСЛИ ($C$ 4:$C$ 8;B12;$E$ 4:$E$ 8)

Область ячеек D12: D14 определяет количество коробок с корпусами каждого типа. Это определяется делением вычисленного количества корпусов в ячейках диапазона С12: С14 на количество корпусов, умещающихся в одной коробке. Формула показана в строке формул на рис. 4.

На рис. 4. видно, что полученное количество коробок с корпусами в первых двух случаях отличаются от целого числа, что невозможно. Чтобы этот факт не остался незамеченным, в области G12: G14 введены формулы, которые находят отличие рассчитанного количества коробок с корпусами от целого числа и формируют текст: Уменьшите количество корпусов или Увеличьте количество корпусов. Формула в ячейке G12:

=ЕСЛИ (ОСТАТ (D12;1)=0;0;ЕСЛИ (ОКРУГЛ (ОСТАТ (D12;1);0)=0;" Уменьшите количество корпусов"; «Увеличьте количество корпусов»))

Первая функция ЕСЛИ в первом аргументе с помощью функции ОСТАТ проверяет — присутствует ли в значении, возвращаемому формулой в ячейке D12, дробная часть. Если дробная часть отсутствует, то формула возвращает значение 0. Если это условие не удовлетворяется, то в первом аргументе второй функции ЕСЛИ с помощью функций ОКРУГЛ и ОСТАТ происходит определение — дробная часть значения в ячейке D12 ближе к единице или ближе к нулю. Этот алгоритм основан сначала на определении дробной части, возвращаемой с помощью функции ОСТАТ, после чего функция ОКРУГЛ производит округление полученной дробной части до целого числа. Так что результат может быть только: или 0 или 1.

Если дробная часть ближе к нулю, то тогда считается что последняя коробка с корпусами лишняя и формула возвращает текст. Уменьшите количество корпусов. В таком случае в таблице ввода планируемых к изготовлению аудиокассет, необходимо уменьшить количество планируемых к изготовлению кассет использующих корпуса этого вида, чтобы получилось целое количество коробок.

Если дробная часть ближе к единице, то тогда считается что последняя коробка с корпусами недоукомплектована и формула возвращает текст Увеличьте количество корпусов. В таком случае в таблице ввода планируемых к изготовлению аудиокассет необходимо увеличить количество изготавливаемых кассет использующих корпуса этого вида.

Расчет количества коробок с магнитной лентой

Расчет количества коробок с магнитной лентой производится в области ячеек В18: J20.

Рис. 5. Область расчета количества коробок с магнитной лентой.

В ячейке С18 производится расчет целого количества рулонов с магнитной лентой, необходимого для изготовления аудиокассет, содержащих тип магнитной ленты введенной в ячейку В18. Формула в ячейке С18:

=ОКРУГЛВВЕРХ (СУММЕСЛИ ($D$ 4:$D$ 8;B18;$F$ 4:$F$ 8);0)

Аналогична расчету количества корпусов в ячейке С12, но после определения суммарного количества рулонов с магнитной лентой в диапазоне F4: F8 с помощью функции ОКРУГЛВВЕРХ производится округление вверх до целого числа. Смысл применения функции ОКРУГЛВВЕРХ заключается в том, что использование части рулона повлечет за собой заказ целого рулона.

Формула определения количества коробок с магнитной лентой показана в строке формул на рис. 5. и заключается в делении целого числа необходимых для выполнения производственной программы рулонов с магнитной лентой, на количество их, помещаемое в одной коробке. После вычисления производится округление полученного результата вверх до целого числа.

Считаем, что оставшимся не целым рулоном магнитной ленты каждого типа в дальнейших расчетах пренебрегаем. Если производится заказ магнитной ленты в коробках, то при изготовлении месячной партии аудиокассет останется какое-то количество целых рулонов магнитной ленты. Расчет количества оставшихся не начатых (целых) рулонов с магнитной лентой производится в ячейке Е18 по формуле:

=(D18-C18/КоробкаРулоновЛента)*КоробкаРулоновЛента

Которая из целого числа заказываемых коробок с магнитной лентой вычитает дробное число коробок, необходимых для выполнения производственной программы. После чего умножает полученный результат на количество рулонов, находящихся в одной коробке. Таким образом, получается остаток целых рулонов не использованной магнитной ленты в последней коробке.

В ячейки диапазона F18: F20 с клавиатуры вводятся значения для корректировки количества коробок с магнитной лентой при формировании заказа. Алгоритм ввода данных в эти ячейки будет рассмотрен далее, а для рассмотрения влияния этого диапазона в нашем примере в ячейку F18 введено значение -1 — которое указывает на то, что при формировании заказа необходимо уменьшить количество коробок с лентой LA на одну коробку.

Формирование предупредительного текста по заказу магнитной ленты

Область G18: J20 предназначена для формирования текста, который информирует о том каких корпусов аудиокассет будет находиться в сформированном заказе в избытке или недостатке.

Формула в ячейке Н18 определяет избыток или недостаток рулонов с магнитной лентой LA с учетом введенного в ячейку F18 значения корректировки коробок с магнитной лентой. Для этого формула определяет количество рулонов в коробках, введенных в ячейку F18, и добавляет к этому значению количество целых рулонов, вычисленных формулой в ячейке Е18:

=ОКРУГЛ ((E18+F18*КоробкаРулоновЛента);0)

Магнитная лента типа LA используется для производства аудиокассет типа AKALA и AKBLA. Поэтому в зависимости от того, какое количество и какого типа аудиокассет является преобладающим, определяется кратность изготовления аудиокассет преобладающего типа из одного рулона магнитной ленты. Формула в ячейке I18 возвращает значение содержимого ячейки, в которую введен размер этой кратности:

=ЕСЛИ (E6

Формула в ячейке J18 аналогична предыдущей, но возвращает текст типа корпусов, используемых при изготовлении типа аудиокассет преобладающих при использовании этого типа ленты.

=ЕСЛИ (E6

После создания этих формул скройте столбцы H: J.

Формула в ячейке G18 предназначена для формирования текста сообщения предупреждения и содержит текст и функции, объединенные функцией СЦЕПИТЬ:

=СЦЕПИТЬ (ЕСЛИ (H18<0;" Лишние «;» Не хватает «);ABS (ОКРУГЛ (H18*I18;0));» корпусов «;J18))

Функция ЕСЛИ возвращает текст Лишние или Не хватает, в зависимости от того остаются корпуса определенного типа или их не хватает при выработке магнитной ленты. Для этого первый аргумент ее анализирует значение ячейки Н18 — больше или меньше нуля.

Функция ABS предназначена для того, чтобы в созданном тексте не присутствовал знак минус. И в конце сообщения добавляется текст типа корпусов, определенный формулой в ячейке J18.

Расчет целого количества контейнеров необходимых для транспортировки заказа

Область расчета количества морских контейнеров, необходимых для транспортировки магнитной ленты и корпусами расположена в строках 24:25 (рис. 6.). В нашем примере предполагается, что в один контейнер не могут быть помещены корпуса для аудиокассет и магнитная лента.

Рис. 6. Область расчета количества морских контейнеров.

В ячейке С24 (строка формул на рис. 6.) находится формула деления общего количества коробок с корпусами на количество коробок, которые могут поместиться в один контейнер, согласно условий кратности, введенных на листе Кратность. Аналогичная формула и в ячейке С25, но производит вычисления с коробками содержащими магнитную ленту.

Только в данном случае нужно помнить, что согласно условий указанных выше (лента будет иметь остаток при заказе комплектующих) в числителе формулы в ячейке С25 будет находиться сумма значений ячейки D21 (количество коробок с лентой полученных при автоматическом расчете) и значений ячейки F21 (количество коробок с лентой, на которые будет уменьшен заказ на ленту).

Формула в ячейке G24 формирует текст Лишние коробки или Контейнер не заполнен при получении числа контейнеров отличного от целого. Либо же возвращает значение ноль, если контейнер заполнен полностью:

=ЕСЛИ (ОСТАТ (C24;1)=0;" «;ЕСЛИ ((ОТБР (C24;0)-ОКРУГЛ (C24;0))=0;» Лишние коробки" ;" Контейнер не заполнен"))

Формула в первом аргументе функции ЕСЛИ с помощью функции ОСТАТ проверяет — присутствует ли дробная часть в вычисленном в ячейке С24 количестве контейнеров. Дробная часть отсутствует, то возвращается значение «». При наличии дробной части, в первом аргументе второй функции ЕСЛИ происходит сравнение со значением 0 результата, полученного при вычитании значения ячейки С24, помещенной в аргументы функций ОТБР и ОКРУГЛ. С помощью этих функций производится анализ:

• · если значение ячейки С24 в функции ОКРУГЛ округляется до целого числа в меньшую сторону, то значит что последний контейнер заполнен менее, чем наполовину. В таком случае значение, возвращаемое функцией ОКРУГЛ равно значению, возвращаемому функцией ОТБР, и тогда первый аргумент функции ЕСЛИ возвращает значение ИСТИНА. Это значит, что в последнем контейнере находятся лишние коробки.
• · при округлении значения ячейки С24 функцией ОКРУГЛ в большую сторону, разность между возвращаемыми значениями функций ОТБР и ОКРУГЛ равна единице. Тогда первый аргумент функции ЕСЛИ возвращает значение ЛОЖЬ, что позволяет сформировать текст: Контейнер не заполнен.