Excelで集計に関する関数の質問です

質問

添付した図を基に質問します。４行目をウィンドウ枠固定して表示してあります。
行列番号を見てもらえたら分かると思いますが、要所でセルを結合してあります。

セルB6に=IF(B5="","",(IF(B5>1999,0,IF(E5<501,0,IF(AND(B5>499,E5<2001),(TIME(INT(E5/100),MOD(E5,100),0)-TIME(INT(B5/100),MOD(B5,100),0)),IF(B5<500,TIME(INT(E5/100),MOD(E5,100),0)-(TIME(INT(500/100),MOD(500,100),0)),IF(E5>2000,TIME(INT(2000/100),MOD(2000,100),0)-TIME(INT(B5/100),MOD(B5,100),0),0))))))*24)

D6に=IF(B5="","",IF(AND(B5<500,E5>500),(TIME(INT(500/100),MOD(500,100),0)-TIME(INT(B5/100),MOD(B5,100),0)),IF(AND(B5<2000,E5>2000),(TIME(INT(E5/100),MOD(E5,100),0)-TIME(INT(2000/100),MOD(2000,100),0)),IF(AND(B5>499,E5<2001),0,(TIME(INT(E5/100),MOD(E5,100),0)-TIME(INT(B5/100),MOD(B5,100),0)))))*24)

これは、項目A列５行目を参照しています。以下オートフィルします。

例えば７時から１０時１０分の差を求める場合、B6セルに700、D6セルに1010と１分単位で入力し、表示形式は0.0" H"です。
項目C,Dも同様で、項目C内E列には１時間３０分の場合１.5と入力し30分単位です。表示形式は同様。

A列の日付欄には、１月１日の場合１／１と記入します。表示形式は日付の月/日です。

上記内容で添付図の上の表(A3:S30)項目B～D列のデータを下の表（A32:S39）で集計しようと思います。

1、F35～F38に月ごとの時間の計を表示させる関数
2、J35～J38には、月ごとの時間*単価を項目別に計算した値の計を表示させる関数
3、P35～P38にも上記同様の計を表示させる関数

以上、1,2,3に入力する関数が知りたいので、宜しくお願いします。
なお不明な点がありましたら随時補足していこうと思います。

回答No.1

noco_noco · ベストアンサー

回答になってないかもしれませんが、
私なら、月毎の集計表を作って、
全体は別シートで３Ｄ（串刺し）参照します。

＜例＞
集計値が各月別シートの同じB35にある場合、
全体集計のシートの適当なセルで、次のように入力する。
=sum(12月:3月!B35)

関数を複雑に組み合わせるより簡単だと思うのですが。

参考URL：

http://allabout.co.jp/computer/msexcel/closeup/CU20081105A/index2.htm

お礼

確かに別シートから参照すれば簡単に出来ますね。

答えを表示させたいセルのみに関数を打ち込むのは複雑になる事は分かっているのですが、その関数が知りたかったのです。

今回の場合は特に複雑な表でもなく入力する項目も僅かなので他のシートか、別の場所からの参照でも良いのですが、大量のデータ入力やシートが多数あるファイルなどではシート数も極力減らしたい時がありますので、今回この様な質問をさせて頂きました。

今回のパターンは他のケースでも色々と応用出来ると思いますので。

回答有難うございました。

補足

他に回答がない為とりあえず私が行なった方法を書きます。

別途=IF(A5="","",MONTH(A5))で月を抽出、=B6で上表参照D6も同様、=SUMIF($A$42:$A$53,"12",B$42:C$53)で集計して、表示させたいセルに項目ごとの集計を出しました。

ただ、この方法ですと元表の半分ぐらいセルが必要になるのと、表の性質からオートフィル出来ないので最初の列の入力が面倒ですね。

既に表を作成してある場合など、一部作り直す必要が出てくる場合もあると思います。
今回は表の規模が小さいので多少の入力は問題ありませんでしたが、やはり膨大なデータが入力してある大きな表ではとても根気が要るでしょうね・・
その時は元表自体を作り直すか別途置き換えてオートフィルしてそこから参照する方法が最も早いと思いますがシート数が増えてしまいますね。。

余談ではございますが、Excel2007は使いづらいですね。今回は2003で作成しましたが、ツールバー等使ときいちいち上のタブをクリックする時間が勿体無いですね＞＜あと互換性も微妙に感じます。部分的に便利な機能もあるのですけれど。

長々すいません。他に良い方法があればまた宜しくお願いします。
今回は回答有難うございました。