Excel 表上にない値も含めて集計するには？

質問

下画像の左表は、ビルで必要な資材のリストです。
　　　　　　　　　　　　　　　　　　　（小型単純化してあります）
１行目だと、１階から３階まで５メータ物が各階ごと２０本必要だと言う事です。
始まる階と最後の階で記入されてるので、途中の階の本数は直接、表に現れていません。
　（１行目だと、２階の２０本）
隠れているのも含めて、各階別の必要本数を集計するにはどうすれば良いでしょうか。

右のような表が欲しいです。
例えば一階に必要なのは、５ｍ物が合計31本、１５ｍ物が４７本となります。

　※ 終階の欄が空白なのは、始階の階一ヶ所だけと言う意味です。

回答No.2

KURUMITO · ベストアンサー

表上には無い値も含めてとは階ばかりではなくメータものの長さなども多種になるのでしょう。
すべてを自動的に関数を使って分かり易く処理するには次のようにしてはどうでしょう。
お示しの表がシート１に有ってD列までの表示となっているとしてE列から右の列を作業列として使用します。
E２セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(C$2:C2,C2)=1,MAX(E$1:E1)+1,"")

部材の種類の数に応じて番号が表示されます。
F２セルには次の式を入力して右横方向に５階の建物ならJ２セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($A2="","",IF(AND(COLUMN(A1)>=MIN($A2:$B2),COLUMN(A1)<=MAX($A2:$B2)),$D2,""))

次にお求めの表ですが例えばシート２に表示させるとして例えばA２セルには次の式を入力して下方向にドラッグコピーします。

=IF(COUNTIF(Sheet1!$E:$E,ROW(A1)),INDEX(Sheet1!$C:$C,MATCH(ROW(A1),Sheet1!$E:$E,0)),"")

部材の長さが表示されます。
B１セルには次の式を入力して右横方向にドラッグコピーします。

=IF(COLUMN(A1)>MAX(Sheet1!$B:$B),"",MIN(Sheet1!$A:$A)+COLUMN(A1)-1)

建物の階が表示されます。
B２セルには次の式を入力して右横方向にドラッグコピーしたのちに下方向にもドラッグコピーします。

=IF(OR(B$1="",$A2=""),"",SUMIF(Sheet1!$C:$C,$A2,Sheet1!F:F))

お礼

＞表上には無い値も含めてとは階ばかりではなくメータものの長さなども多種になるのでしょう。

その部分は手入力で構わないと思っていたのですが、お察しの通りファイルによって項目数が異なり、重量分けの資材だったり色々なので、自動的に表示されれば更にありがたいです。

回答No.1

keithin

右の表と左の表で同じことを違う表記で記入していたのでは、無駄に計算が面倒になるだけなので、

準備：
Ｈ３：Ｌ３には数字の１から５を記入、セルの書式設定の表示形式のユーザー定義で
0F
と設定しておく

Ｇ４：Ｇ７には数字の５から２０を記入、セルの書式設定の表示形式のユーザー定義で
0"m"
と設定しておく

手順：
Ｈ４セルには
=SUMPRODUCT(($A$2:$A$99<=H$3)*(H$3<=($B$2:$B$99="")*($A$2:$A$99)+$B$2:$B$99)*($C$2:$C$99=$G4)*$D$2:$D$99)
と記入、必要に応じてセルの書式設定の表示形式のユーザー定義で
#
と設定しておく
右にコピー、下にコピーして完成。

＃補足
左の表の５行目は４の４と記入しておけば、もうちょっとだけシンプルには出来ます
また今回は掲示された画像で判りますが、ご利用のエクセルのバージョンによって使える関数とか変えてもっとシンプルにできる可能性もあります。ご相談投稿では、普段ご利用のソフトのバージョンまでキチンと明記する事を憶えておいてください。

お礼

とても長い式ですが、貼付けると旨くいきました。
Excelのバージョンを忘れてました、済みません。

ありがとうございました。