Excel データ集計関数
日報などで使える、項目と工数の集計について、Excelで作成してみました。
【エクセルサンプル】
集計エクセルサンプル
【仕様】
A列B列の7行目以降に日報のように例えば仕事内容とそれに費やした工数という具合に記録していったとします。これを1日から31日まで記録するといったやり方も良いでしょう。
集計として以下の情報を知るのに便利なものです。
◆D列E列には、各項目ごとに時間を集計したリストを生成しています。
◆A列B列の4行目には、各項目をリストで呼び出して、その項目の工数を表示できるようにしていまこの2つの表示方法ができるサンプルを作成しました。
◇C列は、D列の集計用にフラグを立てているため、この行については他の行に移したり、白文字で見えなくしても良いと思います。C列の役割としては、A列の各項目の重複をチェックして、最初に引っかかった文字列がある行数を抽出しています。
C7の式：=IF(OR(COUNTIF(A$7:A7,A7)>1,A7=""),"",ROW())
この式をコピーして15行目まで作成します。
◇D列は、C列の行数表示があるところの項目を上から詰めてリストアップしています。
D7の式：=@IF(ROW(D1)>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW(D1))))
この式をコピーして15行目まで作成します。
◇E列は、D列に該当する項目について、A列から抽出し、該当するB列の工数の合計値を計算します。
E7の式：=IF(D7="","",IF(D7="ここまで",SUM($B$7:$B$14),SUMIF($A$7:$B$14,D7,$B$7:$B$14)))
この式をコピーして15行目まで作成します。
◇セルA4はデータ入力規則を使って、リストアップ表示させます。
A4のデータ入力規則：[種類]：リスト、空白無視、リストから選択
[元の値]：=OFFSET($D$7,0,0,COUNTIF($D:$D,">!")-1,1)
◇セルB4はA4でリストアップされた項目に対する工数の合計値を表示します。
B4の式：=IF(A4="ここまで",SUM(B7:B15),SUMIF(A7:B14,A4,B7:B14))