Project Expert: назначение, основные функции, область применения

• Е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 Поиск решения.