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

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

• оптимальное формирование заказа, после которого не остается излишков, не востребованных в производстве;
• минимизация расхода денежных средств, потраченных на доставку заказа и пр.

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

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

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

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

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

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

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

• KA;
• KB;
• KC;

• LA;
• LB;
• LC.

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

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

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

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

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

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

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

• С2 — количество коробок с корпусами, которые могут поместиться в один морской контейнер;
• С3 — количество корпусов, находящихся в одной коробке;
• C6 — количество коробок с магнитной лентой, которые могут поместиться в один морской контейнер;
• С7 — количество рулонов (бобин) магнитной ленты, находящихся в одной коробке;
• С10:С14 — количество аудиокассет каждого типа, которые могут быть изготовлены из одного рулона магнитной ленты.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=ЕСЛИ(E6<E4;Кратность_A_KA_LA;Кратность_A_KB_LA)

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

=ЕСЛИ(E6<E4;"KA";"KB")

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

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

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

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

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

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

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

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

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

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

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

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

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

Процесс формирования заказа

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

На рис. 11.2. видно, что в ячейках G12 по корпусам КА сформирован текст Уменьшите количество корпусов, а в ячейке G13 по корпусам КВ — Увеличьте количество корпусов. Для формирования полного количества ящиков уменьшите количество изготовляемых кассет AKALB на 50 штук, а кассет AKBLA увеличьте на 100 штук.

Количество контейнеров с корпусами близко к пяти, поэтому принимается решение увеличения заказа корпусов кассет какого-либо типа до полной загрузки последнего контейнера. Пусть это будут кассеты AKCLB. Тогда увеличьте количество планируемого заказа корпусов на эти аудиокассеты до тех пор пока не исчезнет текст в ячейке G24 Контейнер не заполнен. Подбор количества с 400000 штук до 426800 можно осуществлять вручную, а можно при помощи инструмента Excel Подбор параметра.

Применение инструмента Excel Подбор параметра

Для вызова диалогового окна Подбор параметра (рис. 11.7.) выделите ячейку С24 и выполните команду Сервис/Подбор параметра. В полях:

• Установить в ячейке: — будет указана ячейка, которую активизировали перед выполнением команды;
• Значение: — укажите значение подбираемого параметра. Для рассматриваемого примера это значение равно пяти;
• Изменяя значение ячейки: — установив курсор в этом поле, укажите ячейку Е8;
• нажмите кнопку ОК или клавишу Enter.

Рис. 11.7. Диалоговое окно Подбор параметра

Через некоторое время при условии выполнения Excel расчетов по подбору параметра на экране монитора появится диалоговое окно (рис. 11.8.). Если найденное решение устраивает пользователя, то нажмите кнопку ОК. Если нет — кнопку Отмена.

Рис.10.8. Диалоговое окно Результат подбора параметров с сообщением, что решение найдено

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

Но по-прежнему в ячейке G25 присутствует текст Лишние коробки, что говорит о необходимости уменьшения количества заказываемой магнитной ленты. Для этого введите в диапазон ячеек F18:F20 количество коробок выбранного типа магнитной ленты, на которые будет уменьшен заказ. Например, принято решение уменьшить заказ равномерно на все типы магнитной ленты. К сожалению в данном случае функция Excel Подбор параметра не сможет оказать никакой помощи, потому что нужно подобрать значения одновременно в трех ячейках. Для того чтобы вручную с клавиатуры не подбирать число коробок с магнитной лентой, на которые нужно уменьшить заказ, используйте инструмент Excel Поиск решения.

Инструмент Excel Поиск решения

Громадное преимущество инструмента Подбор параметра — простота и скорость получения заданного результата, как при вводе исходных параметров, так и в вычислениях. К сожалению, эти достоинства не относятся к функции Excel Поиск решения, которая является одним из самых сложных средств Excel. Применение этого инструмента на практике требует определенных навыков, но возможно, что время потраченное на овладение этим инструментом окупит себя в будущем. Основные достоинства функции Поиск решения:

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

Основные недостатки:

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

Но для поиска решения для нашего приложения эта функция гармонично вписывается. Вызов диалогового окна Поиск решения (рис. 11.9.) осуществляется командой Сервис/Поиск решения. В поле Установить целевую ячейку укажите адрес ячейки С25. В области Равной активизируйте переключатель Значению и в это поле введите значение 1. В поле Изменяя ячейки укажите диапазон ячеек F18:F20. Следующий, самый основной этап, на котором остановимся — ввод ограничений.

Рис. 11.9. Диалоговое окно Поиск решения с заданными параметрами вычислений

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

Достаточностью в нашем примере может служить принятое управленческое решение — снизить заказываемое количество коробок с магнитной лентой равномерно для всех типов. Тогда ограничением может служить условие, что все подбираемые значения должны быть равны. Если задать условие равенства подбираемых значений для всех ячеек, то это не позволит подобрать значения с разными знаками для отдельных ячеек. Но в таком случае возможно получение подбираемых значений с дробной частью.

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

Для ввода ограничений нажмите на кнопку Добавить что вызовет появление диалогового окна Добавить ограничения (рис. 11.10.).

Рис. 11.10. Диалоговое окно Добавить ограничения

Для поиска решения нажмите кнопку Выполнить на диалоговом окне Поиск решения и если решение задачи найдено, то появится диалоговое окно Результаты поиска решения, с помощью которого можете сформировать отчеты, выбрав их в поле Тип отчета.

Рис. 11.11. Диалоговое окно Результаты поиска решения

Один из сформированных отчетов по поиску решения приведен на рис. 11.12.

Рис. 11.12. Отчет по результатам, сформированных после выполнения поиска решения

Немаловажная деталь, на которую хотелось бы обратить внимание. Поиск решения является надстройкой Excel и при стандартной инсталляции Excel команда на вызов ее в меню Сервис будет отсутствовать. Для загрузки надстройки выполните команду Сервис/Надстройки и в диалоговом окне Надстройки и активизируйте опцию Поиск решения и нажмите на кнопку ОК.

Заказ сформирован

Полученные результаты при формировании заказа на рабочем листе Расчет приведены на рис. 11.13.

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

В результате проделанных манипуляций остаются лишние корпуса аудиокассет приблизительно в равной пропорции по всем типам. При необходимости можете рассмотреть другие сценарии.

Формирование бланка заказа

Бланк сформированного заказа расположите на отдельном листе — Заказ (рис. 11.14.). На этом листе применяются только ссылки на ячейки листа Расчет с итоговыми значениями. При сформированном заказе на листе Расчет, можно сразу же перейти на лист Заказ и вывести бланк заказа на печать или отправить его поставщикам в электронном виде.

Рис. 11.14. Бланк сформированного заказа

Итоги

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

Созданная модель содержит малую разновидность различных типов аудиокассет, корпусов и магнитной ленты. На практике номенклатура может состоять из сотен наименований, но усовершенствовать приложение, которое приведено в этой главе не составит большого труда. И это может относиться не только к предприятию, производящему аудиокассеты. Важно только увидеть проблему и захотеть найти пути ее решения.