На этом шаге мы рассмотрим структуру и особенности использования этого листа.
Рабочий лист Расчет (рисунок 1) состоит из областей:
- ввода месячной программы изготовления аудиокассет;
- расчета необходимого количества коробок с корпусами для выполнения производственной программы;
- расчета количества необходимых для выполнения производственной программы коробок с магнитной лентой;
- расчета количества морских контейнеров, необходимых для перевозки заказанных комплектующих.
Рис.1. Рабочий лист Расчет
Область ввода плановой месячной производственной программы изготовления аудиокассет
Таблица ввода плановых показателей производства количества аудиокассет находится в области ячеек В3:F9 и состоит из следующих диапазонов:
- В4:В8 - ввод наименования аудиокассет;
- С4:С8 - предназначена для определения типа корпусов по типу изготовляемой аудиокассеты;
- D4:D8 - предназначена для определения типа магнитной ленты по типу изготовляемой аудиокассеты;
- Е4:Е8 - область ввода планируемого количества изготавливаемых аудиокассет каждого типа;
- F4:F8 - область расчета количества рулонов магнитной ленты, необходимых для изготовления планируемого объема аудиокассет каждого типа.
Рис.2. Область ввода планируемого количества изготавливаемых кассет
Количество корпусов равно количеству изготавливаемых кассет. Определение необходимого для производства количества рулонов магнитной ленты (формула показана в строке формул на рисунке 2) производится в ячейке F6 делением количества планируемого объема аудиокассет на кратность получения количества аудиокассет из одного рулона, введенную в ячейку С12 на листе Кратность с присвоенным ей именем Кратность_А_КВ_LА.
Определение типа корпусов в ячейке С4 для изготовления аудиокассеты типа, указанного в ячейке В4, осуществляется с помощью формулы, которая вначале с помощью функции ПРАВСИМВ выбирает из текста типа аудиокассеты четыре правых символа, после чего уже из этого текста, функция ЛЕВСИМВ выбирает два левых символа:
=ЛЕВСИМВ(ПРАВСИМВ(B4;4);2)
Для определения типа магнитной ленты в ячейке D4 для изготовления указанного типа аудиокассет в ячейке В4, функция ПРАВСИМВ выбирает два правых символа из текста типа аудиокассеты:
=ПРАВСИМВ(B4;2)
Расчет необходимого количества коробок с корпусами аудиокассет для выполнения планируемой месячной программы
Область расчета необходимого количества коробок с корпусами аудиокассет находится в области ячеек В11:G15 и состоит из двух частей:
- диапазон ячеек В11:D15 - область непосредственного расчета количества коробок и находящихся в них корпусов для аудиокассет;
- диапазон ячеек G12:G14 - область формирования предупредительного текста при обнаружении ошибок для того, чтобы пользователь предпринял действия для их исправления.
В диапазоне ячеек С12:С14 производится расчет количества корпусов типа, указанного в диапазоне ячеек В12:В14. Формула в ячейке С12 основана на функции СУММЕСЛИ, которая по наименованию типа корпуса, введенного в ячейку В12, производит поиск наименований такого типа в диапазоне ячеек С4:С8 и суммирует общее количество корпусов этого типа из области ячеек Е4:Е8:
=СУММЕСЛИ($C$4:$C$8;B12;$E$4:$E$8)
Область ячеек D12:D14 определяет количество коробок с корпусами каждого типа. Это определяется делением вычисленного количества корпусов в ячейках диапазона С12:С14 на количество корпусов, умещающихся в одной коробке. Формула показана в строке формул на рисунке 3.
Рис.3. Область расчета количества коробок с корпусами
На рисунке 3 видно, что полученное количество коробок с корпусами в первых двух случаях отличаются от целого числа, что невозможно. Чтобы этот факт не остался незамеченным, в области G12:G14 введены формулы, которые находят отличие рассчитанного количества коробок с корпусами от целого числа и формируют текст: Уменьшите количество корпусов или Увеличьте количество корпусов. Формула в ячейке G12:
=ЕСЛИ(ОСТАТ(D12;1)=0;0;ЕСЛИ(ОКРУГЛ(ОСТАТ(D12;1);0)=0;
"Уменьшите количество корпусов";"Увеличьте количество корпусов"))
Первая функция ЕСЛИ в первом аргументе с помощью функции ОСТАТ проверяет - присутствует ли в значении, возвращаемому формулой в ячейке D12, дробная часть. Если дробная часть отсутствует, то формула возвращает значение 0. Если это условие не удовлетворяется, то в первом аргументе второй функции ЕСЛИ с помощью функций ОКРУГЛ и ОСТАТ происходит определение - дробная часть значения в ячейке D12 ближе к единице или ближе к нулю. Этот алгоритм основан сначала на определении дробной части, возвращаемой с помощью функции ОСТАТ, после чего функция ОКРУГЛ производит округление полученной дробной части до целого числа. Так что результат может быть только 0 или 1.
Если дробная часть ближе к нулю, то тогда считается что последняя коробка с корпусами лишняя и формула возвращает текст Уменьшите количество корпусов. В таком случае в таблице ввода планируемых к изготовлению аудиокассет необходимо уменьшить количество планируемых к изготовлению кассет, использующих корпуса этого вида, чтобы получилось целое количество коробок.
Если дробная часть ближе к единице, то тогда считается что последняя коробка с корпусами недоукомплектована и формула возвращает текст Увеличьте количество корпусов. В таком случае в таблице ввода планируемых к изготовлению аудиокассет необходимо увеличить количество изготавливаемых кассет, использующих корпуса этого вида.
На следующем шаге мы закончим изучение этого вопроса.
