На этом шаге мы закончим изучение этого вопроса.

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

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

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

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

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

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

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

Считаем что оставшимся не целым рулоном магнитной ленты каждого типа в дальнейших расчетах пренебрегаем. Если производится заказ магнитной ленты в коробках, то при изготовлении месячной партии аудиокассет останется какое-то количество целых рулонов магнитной ленты. Расчет количества оставшихся не начатых (целых) рулонов с магнитной лентой производится в ячейке Е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 (рисунок 2). В нашем примере предполагается, что в один контейнер не могут быть помещены корпуса для аудиокассет и магнитная лента.

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

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

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

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

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

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

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

На следующем шаге мы рассмотрим процесс формирования заказа.

Предыдущий шаг Содержание Следующий шаг