О возможностях Excel для решения некоторых динамических задач

Регистрационный номер статьи: 0085

Дата публикации: 28.01.2008

Обычно средство работы с электронными таблицами - Excel используется для выполнения расчетов в заранее подготовленных таблицах. Структура и размеры этих таблиц в процессе расчетов не подвергается изменениям и для вычисления какого-то значения должны быть предварительно вычислены или заданы все исходные данные. Однако встречаются задачи, имеющие итеративный, динамический характер, у которых результат каждого шага расчета может зависеть от результатов, полученных на других шагах. Для их решения обычно рекомендуется использовать непростой аппарат надстроек функциональных возможностей Excel (1).
Многие задачи, подлежащие решению при учете, подготовке и планировании производства, сводятся к вычислению значений некоторых функций, определенных на вершинах или дугах конечного ориентированного графа без контуров. Нетрудно представить граф (дерево изделия), в котором вершины соответствуют отдельным деталям или узлам, составляющим изделие. Дуги такого графа показывают вхождение деталей в узлы или меньших узлов в большие.

Функции, определенные на вершинах графа, значения которых подлежат вычислению, часто обладают неприятной особенностью. Значение такой функции, соответствующее некоторой вершине графа, может зависеть от значений этой же функции на других вершинах того же графа. Это значит, что расчеты имеют нелинейный, итеративный, динамический характер.
В статье рассматривается алгоритм решения одной из таких задач, относящейся, к так называемым, задачам разузлования (2). Алгоритм не использует надстроекExcel и основан на компиляционных свойствах Excel.

Формулировка задачи

Исходные данные
- Пусть граф изделия задан дугами X-->Y, где X и Y – детали или узлы этого изделия. В такой паре деталь или узел X входит в узел или изделие Y в некотором количестве K(X,Y) [Fig1] . Там, где это несущественно, узел, деталь, изделие будем называть объектом.
- Пусть просуммировано и задано для каждого объекта X время (в неделях) на его изготовление, время на сборку всего изделия и каждого узла, а также время межцеховых перемещений, пролеживания и т.п. - T(X).
- К моменту расчета на складе могут находиться уже готовые объекты X в разных количествах на каждую неделю расчетного периода. Обозначим эти величиныS(X,W), где W – номер текущей расчетной недели.
- Пусть, также, задан понедельный план выпуска изделий в штуках. Обозначим его Рo(@,W), где @ - обозначение самого изделия.
Требуется
Определить на основании этих данных понедельный план запуска в производство деталей, узлов и время начала сборки изделий Ps(X,W).

Расчет и вычислительный алгоритм

Исходные данные задачи представлены на Fig2. Массив ячеек Н65:R68 задает значения функции S(X,W), ячейки Т65:T68 задают значения T(X), ячейки U65:U68 - значения K(X,Y), ячейки V65:V68 задают дуги графа изделия – куда входит узел или деталь (3). Изменение данных автоматически приводит к пересчету вычисляемых демонстрационных величин в ячейках W65:W68 и X65:X68 и всего расчета в целом.
Основной расчетный блок программы (ОРБ), расположенный в поле В98 :T104, представлен на Fig3. Блок построен для вычисления Ps(@,W) – на какой неделе и в каком количестве необходимо начать процесс сборки собственно изделия для выполнения понедельного плана его выпуска Рo(@,W). В строке J96:R96 заданы исходные значения. ОРБ используется для генерации остальных расчетных блоков для объектов.

Генерация расчетных блоков

С точки зрения пользователя программы, генерация расчетного блока для каждого последующего объекта X выполняется последовательным копированием (через Clipboard ) поля В98:Т104 в ячейки В105, В113 и так далее с шагом 8.
Процесс генерации надо прекратить после соответствующего сообщения [Fig4-Fig7].
С точки зрения организации расчета, ОРБ построен так, что генерация расчетных блоков приводит к компиляции программных блоков, реализующих выполнение последовательных шагов итеративного процесса. Результатом оказываются расчетные блоки для объектов А [Fig 4], В [Fig5] и так далее. Причем при необходимости (например, для расчета А) используются результаты, полученные на других шагах процесса [Fig4-Fig6].

Формулы расчетного блока

Рассмотрим организацию ОРБ. Каждое значение в ячейках ОРБ вычисляется по формулам. Формулы построены как функции от номера итерации. Там, где это не оговорено особо, формулы приводятся для четвертой недели расчета, то есть при W=4. Для других недель формулы получаются копированием вдоль строки.

Формула 1
(СТРОКА($T104)-104)/8 – формула для вычисления и занесения в ячейку $T104 номера итерации. В ОРБ значение выражения СТРОКА($T104) равно104 и тем самым номер итерации равен нулю (Fig3а.) В последующих расчетных блоках (итерациях) значение выражения СТРОКА($T104) равно 112, 120 и так далее. Соответственно, номера итераций – 1,2 и так далее.

Формула 2
СИМВОЛ($T104+64) – формула для задания буквенного обозначения детали или узла, в том числе и всего изделия (Fig3b). Значение функции СИМВОЛ($T104+64) в ОРБ равно @, так как значение в $T104 равно нулю (см. описание Формулы 1), а код 64 соответствует знаку @. Выражение ($T104+64) в последующих итерациях будет иметь значения 65, 66 и так далее. Значениями формулы СИМВОЛ($T104+64) станут соответственно знаки латинских букв А, В и так далее.

Формула 3
СМЕЩ(O$96;СМЕЩ($V$65;$T104;0)*8;0)*СМЕЩ($U$65;$T104;0) – формула для вычисления значения плана выпуска объекта на расчетную неделю (Fig3с.). Очевидно, что план выпуска входящего объекта X на каждую неделю равен плану запуска объекта Y на эту неделю, умноженному на количество K(X,Y) вхождений -Рo(X,W)=Ps(Y,W)*K(X,Y)
В рассматриваемой формуле :
- выражение СМЕЩ($U$65;$T104;0) указывает адрес, где хранится количество K(X,Y) вхождений объекта X в непосредственно включающий узел Y. Этот адрес равен U65 для ОРБ, а для последующих итераций за счет значения $T104 - он равен U66, U67 и так далее;
- выражение СМЕЩ($V$65;$T104;0) указывает адрес, где хранится цифровое обозначение объекта Y («Куда входит?»). Этот адрес равен V65 для ОРБ, а для последующих итераций он равен V66, V67 и так далее (4);
- выражение СМЕЩ(O$96;СМЕЩ($V$65;$T104;0)*8;0) указывает адрес значения Ps(Y,W) из строки плана запуска узла Y. Этот адрес зависит от номера итерации и от структуры дерева конкретной задачи (5).

Формула 4
СМЕЩ(O$65;$T104;0) – формула для переноса в расчетный блок значений S(X,W) [Fig3d].

Формула 5
ЕСЛИ (ЕЧИСЛО(P102);ЕСЛИ(P102-O100+O101>0;P102-O100+O101;0);
ЕСЛИ(O101-O100>0;O101-O100;0)) – формула для вычисления величины резерва R(X,W), образующегося на складе после выполнения плана на расчетную неделюW.
Здесь:
- Р102 резерв объектов Х, образовавшийся на неделе W –1;
- O100 план выпуска объектов Х на неделе W;
- O101 количество объектов Х на складе на неделе W.
Если резерв Р102+O101 больше плана O100, то после выполнения плана, на складе на неделе W останется резерв, равный Р102+O101–O100. В противном случае, если P102+O101<=O100, на неделе W резерв не образуется.
В случае, когда расчет производится для первой недели (W=1), значения Р102 не существует и в расчете оно не участвует. Тогда, если резерв O101 больше плана на неделю O100, на складе появится резерв, равный O101–O100. В противном случае, если O101–O100<=0, резерва нет [Fig3e].

Формула 6
ЕСЛИ(ЕЧИСЛО(P102);ЕСЛИ(O100-O101-P102>0;O100-O101-P102;0);
ЕСЛИ(O100-O101>0;O100-O101;0)) – формула для определения количества объектов, которое необходимо изготовить в неделю W. Очевидно, искомое значение равно разности между планом на неделю O100 и суммой резервов
P102 +O101.
Если сумма P102 +O101 меньше плана O100, то для выполнения плана на неделю W останется произвести O100 – (P102 +O101) объектов. Иначе, если резерв больше плана, план выполнен за счет резерва.
В том случае, если расчет выполняется для первой недели (W=1), значения P102 не существует и в расчете оно не участвует. Тогда, если резерв O101 меньше плана на неделю O100, необходимо произвести O100-O101 объектов. В противном случае план выполнен за счет резерва [Fig3f].

Формула 7
ЕСЛИ(ЕЧИСЛО(СМЕЩ(O103;0;СМЕЩ($T$65;$T104;0)));
СМЕЩ(O103;0;СМЕЩ($T$65;$T104;0));0) - формула расчета понедельного плана Ps(X,W) запуска в производство объектов с учетом упреждения на сборку, межцеховых перемещений и пролеживания T(X) [Fig3g].
В этой формуле:
- выражение СМЕЩ($T$65;$T104;0) определяет смещение по горизонтали вправо от позиции расчетной недели на величину (упреждение), заданную адресом $T$65+ $T104. Поэтому, на нулевой и последующих итерациях будут получаться адреса Т65, Т66 и т.д. Эти адреса указывают значения упреждений [Fig2];
- выражение СМЕЩ(O103;0;СМЕЩ($T$65;$T104;0)) перемещает значение из строки «Сколько изготовить?» (СМЕЩ(O103;0;…)) в строку «План запуска» в позицию правее на СМЕЩ($T$65;$T104;0). Тем самым реализуется упреждение на сборку.
Если в результате расчетов упреждение выйдет за допустимый диапазон, то план запуска задается нулевым [Fig3h] (6) и появится сообщение об ошибке в исходных данных.

Формула 8
ЕСЛИ(СУММ(B103:R103)=СУММ(B104:R104);””;”ОШИБКА”)- формула позволяет в ячейке $Т103сформировать предупредительное сообщение об ошибке [Fig3g]. Это сообщение появляется в том случае, когда неправильно заданы значения функции T(X) и возможны потери рассчитанной информации. При некоторых значениях функции T(X) план выпуска может оказаться не реальным (7).
Совпадение сумм значений в строках 103 (Сколько изготовить?) и 104 (План запуска) является признаком отсутствия ошибки.

Заключение

1.Рассмотренный пример удобен для демонстрации, но в реальной жизни изделия могут состоять из гораздо большего количества узлов и деталей и планирование ведется на больший период времени. Для этого необходимо больше места под исходные данные, места для результатов расчетов для большего количества недель и, наконец, необходимы изменения в системе кодирования объектов. Например, можно сделать так:
• заменить формулу 2 на формулу:
СЦЕПИТЬ(СИМВОЛ(64+($T104-ЦЕЛОЕ($T104/27)*27));ЦЕЛОЕ($T104/27)), (Код объекта будет задаваться латинской буквой от @ до Z и номером периода повтора буквы кода);
• ОРБ вместе с исходными данными следует перенести на рабочий лист значительно правее (корректировок не требуется);
• ОРБ вместе с Рo(@,W). перенести ниже ранее занимаемого места (при переносе на величину, равную 8*n, следует в формуле 1 заменить константу 104 на 104+8*n);
• добавить необходимые исходные данные;
• в ОРБ распространить все формулы влево.
2. Использование других функций на дугах графа и соответствующие изменения в ОРБ, позволят, например, выполнить расчеты по себестоимости выпускаемой продукции, ее металлоемкости и др.

Пояснения текста
1. Новиков Ф., Яценко А.. Microsoft Office XP в целом. – СПб.: БХВ – Петербург, 2002, с. 570 – 611.
2. Брегман В.И., Михлин Г.З. Система машинной обработки информационных массивов, Москва, Статистика, 1972, с .154 – 179.
3. Для демонстрации выбрано изделие [Fig1], в которое входит два узла В, состоящие из трех узлов С каждый (В-->@ , K(B,@)=2; C-->B , K(C,B)=3). В свою очередь, каждый узел С состоит из двух деталей А (A-->C, K(A,C)=2).
4. Для объекта А этот адрес равен V66 и в нем содержится значение 4 [Fig2].
5. Так например, для объекта А в неделю W, в соответствии с условием (A-->C), он будет равен адресу O96+ (4*8) =O128
6. На Fig4 и Fig5 следует обратить внимание на то, что значение Рo(А,W) плана выпуска для детали А нулевое. Это связано с тем, что к этому моменту еще не определен план запуска Ps(С,W) для узла С. (Напомним, что А-->С, С-->В, B-->@). Только после генерации расчета для узла С появляется возможность расчета для детали А[Fig6]).
7. В рассматриваемом примере увеличение времени упреждения для детали А всего на одну неделю делает план выпуска изделии не реальным.