テストのための表は画像の位置に置きましたので、適宜、式のセル位置を変えて下さい
(A1-K6, B8-E9, G8-K8)
その表が大きすぎるため,精一杯拡大して、この大きさでした
見出しは 品種,開始,終了.個,消耗品,1列空けて, 製造時間, 該当時間, 該当製造数, 単位消費, 消耗量です
開始,終了の書式は yyyy/mm/dd hh:mm
>>
●求める答は,E8,E9 にあるように丸めなしで、
4/1 9:00-4/2 9:00 >> 16.67 L
4/2/ 9:00-4/3 9:00 >> 4.74 L
です
配列数式が含まれていますので 作業列 (G-K列）は 使いませんが、参考になるように掲載しました
まず考え方ですが、4/1 9:00-4/2 9:00 の基準時間帯をZと呼び、品種 A の場合で説明しますと
1.品種の製造時間を計算 G2:=C2-B2 書式 :mm
2.Z時間帯に含まれる製造時間を計算 H2:=MAX(0,MIN(C2,$C$8)-MAX(B2,$B$8)) 書式 :mm
3.Z時間帯に製造される個数を上の1,2から 比例配分で計算 I2:=D2/G2*H2 書式 0:00
4.消耗品Pの製造個数1個当りの使用量を計算 J2:=E2/D2 書式 0:00
5.Ｚ時間帯の消耗品使用量を 単位消費 X 製造個数で計算 K2:=I2*J2 書式 0:00
さて,そこで単純には以上の5の合計を算出すれば良いわけです。
が, 2.でMIN、MAXを使ってますので、これにC2-> C2:C6 、B2->B2:B6 とそれぞれ配列にすると
エクセルの弱点でそれらと基準の$C$8,$B$8を含めてＭＩＮ，ＭＡＸを計算完了させてしまいます
>> ここからMIN,MAXは配列要素の単位で使えないことになります
それを踏まえて、上の2の式をIFのネストにして、以下のように変えています
H2:=IF(IF(C2<$C$8,C2,$C$8)-IF(B2>$B$8,B2,$B$8)<0,0,IF(C2<$C$8,C2,$C$8)-IF(B2>$B$8,B2,$B$8))
ここから各アイテムでしている式をB2:B6,C2:C6に配列含みに変更してます
その中途の式がG8:K8にあり
G8:=SUMPRODUCT($C$2:$C$6-$B$2:$B$6)
H8:={=SUMPRODUCT(IF(IF($C$2:$C$6<$C8,$C$2:$C$6,$C8)-IF($B$2:$B$6>$B8,$B$2:$B$6,$B8)<0,0,IF($C$2:$C$6<$C8,$C$2:$C$6,$C8)-IF($B$2:$B$6>$B8,$B$2:$B$6,$B8)))}
I8:={=SUMPRODUCT($D$2:$D$6/($C$2:$C$6-$B$2:$B$6)*IF(IF($C$2:$C$6<$C8,$C$2:$C$6,$C8)-IF($B$2:$B$6>$B8,$B$2:$B$6,$B8)<0,0,IF($C$2:$C$6<$C8,$C$2:$C$6,$C8)-IF($B$2:$B$6>$B8,$B$2:$B$6,$B8)))}
J8:=SUMPRODUCT($E$2:$E$6/$D$2:$D$6)
K8:={=SUMPRODUCT(($E$2:$E$6/$D$2:$D$6)*$D$2:$D$6/($C$2:$C$6-$B$2:$B$6)*IF(IF($C$2:$C$6<$C8,$C$2:$C$6,$C8)-IF($B$2:$B$6>$B8,$B$2:$B$6,$B8)<0,0,IF($C$2:$C$6<$C8,$C$2:$C$6,$C8)-IF($B$2:$B$6>$B8,$B$2:$B$6,$B8)))}
で H8,I8,K8 は配列数式になってますので 「ｃtrl, shiｆt, enterの同時押し」で式を確定した後です
求めている答:は上のロジックを整理して式にしたものです
●E8:
=SUMPRODUCT(($E$2:$E$6/$D$2:$D$6)*$D$2:$D$6/($C$2:$C$6-$B$2:$B$6)*IF(IF($C$2:$C$6<$C8,$C$2:$C$6,$C8)-IF($B$2:$B$6>$B8,$B$2:$B$6,$B8)<0,0,IF($C$2:$C$6<$C8,$C$2:$C$6,$C8)-IF($B$2:$B$6>$B8,$B$2:$B$6,$B8)))
この式は配列数式になってますので ↑の式を入力して「ｃtrl, shiｆt, enterの同時押し」で式を確定して下さい
{ = ・・・ } のように入力した式がはさまれますが、それが式成立の条件なので、構いません
そして↑Ｅ8の式を Ｅ9 まで下へ1行ドラッグしてオートフィルしてください
//
ちなみに サンプル表では上から
製造時関:=9:00,8:00,9:00,29:00,1:00 計 56:00
Z時間帯計:=6:00,8:00,6:00,0,0 計 20:00
Z時間帯の製造数計:=33.33, 20.00, 10.00, 0,0 計 63.33個
1個当りの使用量 = 0.1,0.4,0.53,0.13,0.20 計 1.37
Z時間帯使用量:=3.33,8.00,5.33,0,0 計 16.67
です
計算過程にて 時間をシリアル値で計算,かつ,割算をしてますので 演算誤差は現時点で入り込んでます
//
※仮にZ時間帯を計算する式が、別の立て方 (例えば、もっと簡便な式、 ユーザー定義関数 etc) で
見た目がスッキリすればE8,E9の式はズッと見やすく、判りやすいものになりますし
本件自体が式ではなく、マクロにする方がベターかも。
計算が合致してれば、是非、改良してください(^^)
//
