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.2

KURUMITO · ベストアンサー

回答No1です。
下記の文章を訂正してください。
K2セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(B$1="",$A2=""),"",B$1&B2&$A2)

これ式をK2セルに入力した後で横方向にドラッグコピーしたのちに下方向にもドラッグコピーします。に訂正してください。

回答No.5

end-u

ついでに関数。
（例題見る限りSheet1とSheet2の位置関係を行列入れ替えれば良いだけのように見えるので）

Sheet1のデータがA1から始まってるとして。
これもまず「名前定義」で
「名前」に database 「参照範囲」に =INDEX($A:$A,COUNTA($A:$A)+1):INDEX($1:$1,COUNTA($1:$1)+1)

Sheet2のB1セルに =INDEX(database,COLUMN(),1) 右に必要データ列数コピー。
A2セルに =INDEX(database,1,ROW()) 下に必要データ行数分コピー。
B2セルに =($A$1=INDEX(database,COLUMN(),ROW()))*1 右と下に必要データ行列数分コピー。

#Sheet1とSheet2の並びを行列入れ替えず、そのままで妥協できるならもっと簡単？

お礼

ありがとうございます。
改めて、いろいろなやり方があるのだなと思いました。Excelは深いです。

回答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関数使ってた自分がバカらしく思えてきました。とにかくデータ入力だけは毎日しっかりやっていきます。ピボットテーブルはいつでも作成できますし。もっと極めたいと思いました。

回答No.3

tom04

こんにちは！
関数での方法は回答されていますので、VBAでの一例です。

まずAlt＋F11キー　→　メニュー　→　挿入　→　「標準モジュール」　→　VBE画面に
↓のコードをコピー＆ペーストしておいてください。

Sub 表示() 'この行から
Dim i As Long, j As Long, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")

Application.ScreenUpdating = False
i = wS2.Cells(Rows.Count, 1).End(xlUp).Row
If i > 1 Then
wS2.Rows(2 & ":" & i).ClearContents
End If
j = wS2.Cells(1, Columns.Count).End(xlToLeft).Column
If j > 1 Then
Range(wS2.Cells(1, 2), wS2.Cells(1, j)).ClearContents
End If
i = wS1.Cells(Rows.Count, 1).End(xlUp).Row
Range(wS1.Cells(2, 1), wS1.Cells(i, 1)).Copy
wS2.Activate
wS2.Cells(1, 2).Select
Selection.PasteSpecial Paste:=xlAll, Transpose:=True
j = wS1.Cells(1, Columns.Count).End(xlToLeft).Column
Range(wS1.Cells(1, 2), wS1.Cells(1, j)).Copy
wS2.Activate
wS2.Cells(2, 1).Select
Selection.PasteSpecial Paste:=xlAll, Transpose:=True
For i = 2 To wS1.Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To wS1.Cells(i, Columns.Count).End(xlToLeft).Column
If wS1.Cells(i, j) <> "" And wS1.Cells(i, j) = wS2.Cells(1, 1) Then
wS2.Cells(j, i) = 1
End If
Next j
Next i
Application.ScreenUpdating = True
wS2.Cells(1, 1).Select
End Sub 'この行まで

次に画面左下のSheet2のSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてSheet2のA1セルデータを入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
If Target.Address = "$A$1" Then
Call 表示
End If
End Sub 'この行まで

こんなんではどうでしょうか？m（_ _）m

お礼

ありがとうございます。
言われたとおりにやったらできました！　生まれて初めて実行したVBAです。これからは、この内容がわかるよう一行一行、理解していきたいです。

回答No.1

KURUMITO

最も分かり易くデータが多くなっても計算に負担がかからない方法は作業列を作って対応することです。
シート1の1行目ではB1セルから横に名前が入力されており、例えばJ1セルまで入力されているとします。また、A2セルから下方には日付が入力されているとします。
K2セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(B$1="",$A2=""),"",B$1&B2&$A2)

シート2にはA1セルに新宿とかの検索する場所を入力し、B1セルから横方向には日付が入力されているとします。
また、A2セルから下方には名前が入力されているとします。
作成するとしてA2セルから下方には日付を入力し、B1セルから横の列には氏名を入力するとします。
B2セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(COUNTIF(Sheet1!$K:$Z,$A2&$A$1&B$1)=0,"",COUNTIF(Sheet1!$K:$Z,$A2&$A$1&B$1))

作業列をK列にしていますが人数に応じてより右の列にしてもよいでしょう。その場合にはB2セルへの入力の式も当然変わりますが応用してください。
このように作業列を作ることで簡単にしかも計算に負担を掛けることもなく処理することができます。

お礼

本当にありがとうございます！　質問から36分後にはもう回答が。
お礼が遅くなったのは、もっと違う回答が出るのではという不純な動機からでした。申し訳ありません。
この場を借りて回答してくださったお三方、本当にありがとうございました。Excelはすごいです！　それを使いこなしている方々はもっとすごいです！　少しでも近づけるよう勉強していきます。ありがとうございました！