Excelの質問です。


       A    B     C     D     E     F    G  ・・・
             
    1 支社  支店  社員ID  氏名  a数量  b数量   c数量 
    
    2

    3
    

上記の集計シートと1~31までの日計シートがあります。
1~31までのシートは休みだとデータがありませんので、毎日セルの位置が変わってしまいます。

こういった場合、集計シートで a数量・b数量・c数量を集計したいのですが、
どのような関数を使えばいいのか分かりません。
お分かりになる方、お知恵をお貸しいただけないでしょうか?

回答の条件
  • 1人20回まで
  • 登録:2010/02/06 23:43:32
  • 終了:2010/02/09 00:51:56

ベストアンサー

id:SALINGER No.4

SALINGER回答回数3430ベストアンサー獲得回数9692010/02/08 00:43:07

ポイント200pt

特定の列を集計するということで修正しました。

社員IDはユニークだと思うので、社員IDで検索して集計しています。

社員IDの列はC列としていますが、違う場合は1行目を修正してください。


Sub Macro()
    Const sID As String = "C"
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    Dim r As Range
    
    With Worksheets("集計")
        .Activate
        lastRow = .Cells(Rows.Count, sID).End(xlUp).Row
        .Range(Cells(4, "M"), Cells(lastRow, "M")).Clear
        .Range(Cells(4, "N"), Cells(lastRow, "N")).Clear
        .Range(Cells(4, "O"), Cells(lastRow, "O")).Clear
        .Range(Cells(4, "P"), Cells(lastRow, "P")).Clear
        .Range(Cells(4, "S"), Cells(lastRow, "S")).Clear
        .Range(Cells(4, "U"), Cells(lastRow, "U")).Clear
        .Range(Cells(4, "Y"), Cells(lastRow, "Y")).Clear
        
        For i = 4 To lastRow
            For Each ws In Worksheets
                If ws.Name <> "集計" Then
                    Set r = ws.Range(sID & ":" & sID).Find(.Cells(i, sID).Value, lookat:=xlWhole)
                    If Not r Is Nothing Then
                        .Cells(i, "M").Value = .Cells(i, "M").Value + ws.Cells(r.Row, "M").Value
                        .Cells(i, "N").Value = .Cells(i, "N").Value + ws.Cells(r.Row, "N").Value
                        .Cells(i, "O").Value = .Cells(i, "O").Value + ws.Cells(r.Row, "O").Value
                        .Cells(i, "P").Value = .Cells(i, "P").Value + ws.Cells(r.Row, "P").Value
                        .Cells(i, "S").Value = .Cells(i, "S").Value + ws.Cells(r.Row, "S").Value
                        .Cells(i, "U").Value = .Cells(i, "U").Value + ws.Cells(r.Row, "U").Value
                        .Cells(i, "Y").Value = .Cells(i, "Y").Value + ws.Cells(r.Row, "Y").Value
                    End If
                End If
            Next
        Next i
    End With
End Sub
id:cazica

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

拙い説明だったのに、思っていた通りの表が出来ました。

もう1点教えていただきたいのですが、各シートのフォントは8に設定しているのですが、

実行すると11のフォントで表示されるのはどうしてでしょうか?

2010/02/08 22:30:20

その他の回答(3件)

id:heke2mee No.1

heke2mee回答回数162ベストアンサー獲得回数432010/02/07 06:08:56

ポイント13pt

=SUM(1日のシート:28日のシート!E1)


この様に最初のシートと最後のシートを:で繋げるとできます。

このままだと月末の日付が変わるたびに集計シートを変更しないと駄目なので

最終日(月末)の後ろに終端用のダミーシートを作って


=SUM(1日のシート:終端シート!E1)


このようにして、新しく日付のシートを追加した場合は

シートの並びを終端シートより前に移動してあげればいけると思います。


複数のシートのデータを集計するには

http://support.microsoft.com/kb/883029/ja

id:cazica

回答ありがとうございます。

質問が初めてで、要領が分かっていないのに、

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

2010/02/07 19:16:29
id:km1967 No.2

km1967回答回数541ベストアンサー獲得回数402010/02/06 23:48:13

ポイント13pt

まずはお願いなのですが、

日計シートの形式など、

何も判らないと回答は難しくなりますので、必須だとご記憶ください。

不適当な回答が集まっても混乱をきたしますから、私への返信欄か、

あるいは、コメント欄を開く設定に変更してコメント欄)に、

書き加えておくことを勧めます。

id:cazica

回答ありがとうございます。

km1967さんのおっしゃるとおり、何も分からないと

回答できませんよね。

初めての質問で、要領を得てませんでした。

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

2010/02/07 19:21:51
id:SALINGER No.3

SALINGER回答回数3430ベストアンサー獲得回数9692010/02/07 00:33:05

ポイント26pt

関数の串刺し集計だとセルの位置が違うと厳しいので、集計作業はVBAでやってしまうのがいいかもしれません。

シートは"集計"シートと"1"~"31"のシートだけがあるとして、各表が例と同じように左上詰めだとします。

以下のコードを標準モジュールにコピペして最初の行の数量の数を変えて実行させてみてください。


VBAとはなんぞやという場合はこちらが参考になります。

http://www.officepro.jp/excelvba/ini/index1.html

http://www.officepro.jp/excelvba/ini/index2.html

http://www.officepro.jp/excelvba/ini/index3.html


Sub Macro()
    Const suryou As Integer = 3     '数量の数
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    Dim r As Range
    
    With Worksheets("集計")
        .Activate
        lastRow = .Cells(Rows.Count, "C").End(xlUp).Row
        .Range(Cells(2, 5), Cells(lastRow, suryou + 4)).Clear
        For i = 2 To lastRow
            For Each ws In Worksheets
                If ws.Name <> "集計" Then
                    Set r = ws.Range("C:C").Find(.Cells(i, "C").Value, lookat:=xlWhole)
                    If Not r Is Nothing Then
                        For j = 5 To suryou + 4
                            .Cells(i, j).Value = .Cells(i, j).Value + ws.Cells(r.Row, j).Value
                        Next j
                    End If
                End If
            Next
        Next i
    End With
End Sub
id:cazica

回答ありがとうございます。

実際のシートでは、1行目に表題、2行目に取得日時、3行目がタイトルとなり、

4~1500行くらいまであります。集計対象のデータはG~Y列の範囲です。

(質問の仕方が下手ですみません。)

集計がいらない列もあるのですが、

Const suryou As Integer = 3 '数量の数 ←は集計する列の数でしょうか?

この場合はどのようにしたら良いのか、教えていただけないでしょうか?

2010/02/07 19:12:54
id:SALINGER No.4

SALINGER回答回数3430ベストアンサー獲得回数9692010/02/08 00:43:07ここでベストアンサー

ポイント200pt

特定の列を集計するということで修正しました。

社員IDはユニークだと思うので、社員IDで検索して集計しています。

社員IDの列はC列としていますが、違う場合は1行目を修正してください。


Sub Macro()
    Const sID As String = "C"
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    Dim r As Range
    
    With Worksheets("集計")
        .Activate
        lastRow = .Cells(Rows.Count, sID).End(xlUp).Row
        .Range(Cells(4, "M"), Cells(lastRow, "M")).Clear
        .Range(Cells(4, "N"), Cells(lastRow, "N")).Clear
        .Range(Cells(4, "O"), Cells(lastRow, "O")).Clear
        .Range(Cells(4, "P"), Cells(lastRow, "P")).Clear
        .Range(Cells(4, "S"), Cells(lastRow, "S")).Clear
        .Range(Cells(4, "U"), Cells(lastRow, "U")).Clear
        .Range(Cells(4, "Y"), Cells(lastRow, "Y")).Clear
        
        For i = 4 To lastRow
            For Each ws In Worksheets
                If ws.Name <> "集計" Then
                    Set r = ws.Range(sID & ":" & sID).Find(.Cells(i, sID).Value, lookat:=xlWhole)
                    If Not r Is Nothing Then
                        .Cells(i, "M").Value = .Cells(i, "M").Value + ws.Cells(r.Row, "M").Value
                        .Cells(i, "N").Value = .Cells(i, "N").Value + ws.Cells(r.Row, "N").Value
                        .Cells(i, "O").Value = .Cells(i, "O").Value + ws.Cells(r.Row, "O").Value
                        .Cells(i, "P").Value = .Cells(i, "P").Value + ws.Cells(r.Row, "P").Value
                        .Cells(i, "S").Value = .Cells(i, "S").Value + ws.Cells(r.Row, "S").Value
                        .Cells(i, "U").Value = .Cells(i, "U").Value + ws.Cells(r.Row, "U").Value
                        .Cells(i, "Y").Value = .Cells(i, "Y").Value + ws.Cells(r.Row, "Y").Value
                    End If
                End If
            Next
        Next i
    End With
End Sub
id:cazica

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

拙い説明だったのに、思っていた通りの表が出来ました。

もう1点教えていただきたいのですが、各シートのフォントは8に設定しているのですが、

実行すると11のフォントで表示されるのはどうしてでしょうか?

2010/02/08 22:30:20
  • id:SALINGER
    質問の例は違うのですね。
    集計する列はG~Yのどの列でしょうか?
  • id:cazica
    M・N・O・P・S・U・Y列です。
    VLOOKUPだと、日毎に出勤者が違うので、
    行が変わってくるので、集計できませんでした。
  • id:SALINGER
    VLOOKUPだと31個書かないといけないから数式だと大変でしょうね。
  • id:SALINGER
    >実行すると11のフォントで表示されるのはどうしてでしょうか?
    Clearで書式ごと削除したためでした。
    7箇所あるClearをClearContentsにすればいいです。
  • id:cazica
    SALINGERさん、凄いです!
    今まで知らなかったVBAを知り、
    少しでも覚えたいと思いました。
    今後は少しずつ勉強してみます。
    本当にありがとうございました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません