Excelで工数、勤怠管理、何月何日に誰がどこへ

質問

Excel2010を使っています。「何月何日に誰がどこへ行ったのか」がわかる表を作ろうとしていますが、うまく作れません。

Sheet1に以下のようなデータを作り、

　　　　　 鈴木　山田　田中
4月1日　新宿　新宿　横浜
4月2日　新宿　横浜　横浜
4月3日　新宿　渋谷　新宿

Sheet2のA1に「新宿」と入力したら、Sheet1を参照して、

新宿　　4月1日　4月2日　4月3日
鈴木　　 　1.0　　　　1.0　　　　1.0
山田　　 　1.0
田中　　　　　　　　　　　　　　　1.0

と出力されるようにしたいのです。

http://okwave.jp/qa/q5829569.html?&status=true&errcode=&msg=&qid=5829569
を参考に、
INDIRECT、SMALL、ROW等の関数を使ってやってみましたが、うまくいきません。

できれば、ピボットテーブル、関数を使って作りたいです。
無理ならば、VBAを考えています。

どなたか詳しい方がいれば教えてください。よろしくお願いします。

回答No.4

end-u

>できれば、ピボットテーブル、...を使って作りたいです。
という点に反応してみました。

Sub test()
　　ActiveWorkbook.Names.Add "database", "=INDEX($A:$A,COUNTA($A:$A)+1):INDEX($1:$1,COUNTA($1:$1)+1)"
　　ActiveWorkbook.PivotCaches.Add(xlConsolidation, "database").CreatePivotTable("").AddFields "列", "行", "値"
End Sub

データ範囲が拡張しても対応できるように「名前定義」を設定して、
[Alt][d][p]でConsolidation Type のピボットテーブルを作成するマクロです。

試す場合は、データが在るSheet1をアクティブにして実行してください。
Sheet1のデータはA列に日付、1行目に担当者、かつA1セルはデータが何も入力されていない事が前提です。

手作業の場合
1)データが在るSheet1をアクティブにして[ctrl]+[f3]、「名前の管理」「新規作成」、
　「名前」に database 「参照範囲」に =INDEX($A:$A,COUNTA($A:$A)+1):INDEX($1:$1,COUNTA($1:$1)+1)
　で[OK]。
　（A1セルに項目名がある場合は =INDEX($A:$A,COUNTA($A:$A)):INDEX($1:$1,COUNTA($1:$1)) で良いです）

以上が可変範囲の名前定義。

2)[Alt][d][p]で「ピボットテーブルウィザード」、「複数のワークシート範囲」にチェックして[次へ]
3)「指定」にチェックして[次へ]
4)「範囲」に database [追加]、[完了]
5)できたピボットテーブル「列ラベル」の列フィールドを「行ラベル」へ。「行ラベル」の行フィールドを「列ラベル」へ。
6)「フィールドリスト」から値フィールドを「レポートフィルター」に追加する。

..な流れでできます。
あとは体裁を整えればなんとかなるかと。

お礼

ありがとうございます。
ピボットテーブルすごいです！　毎月毎月SUMやIF関数使ってた自分がバカらしく思えてきました。とにかくデータ入力だけは毎日しっかりやっていきます。ピボットテーブルはいつでも作成できますし。もっと極めたいと思いました。