SUMIF関数の合計範囲を月に合わせて設定するには?

Question 84.2 Previous Next
1つのブックに、下図のような2つのシートが存在します。
「データ」シート A列 … 内容など判別用のkey、B~M列 … 月別データ
データシートと表シート
「表」シート
1. ピンクのコンボボックスで月を選択する→C6セルに月名、I4セルに月IDが表示される。J4セルは固定値
2. 黄色のコンボボックスで店名を選択する→C5セルに店名、I6セルに店名IDが表示される
3. 青いセル(K8~K15)には【店名ID&商品ID】でIDを完成させる
4. 商品ごとの売上個数は、SUMIF関数でIDを検索条件にして算出
  ex)単月 4月を選択したら…データシートのB列をSUMIF、5月…C列をSUMIF
    累計 4月を選択したら…データシートのB列をSUMIF、 5月…BとC列をSUMIF、 6月…BとCとD列をSUMIF

困っていることは、SUMIF関数では合計範囲を指定しなければいけないのに、列ごとに月別のデータが入ってしまっている為、コンボボックスの選択値が変わる度に、合計範囲を書き換えなければならなくなってしまいました。
何か良い方法はありませんか? 単月の他にB列~M列の累計も算出したいです。
初心者の為、説明不十分で申し訳ありません。作成中のExcelブックをお送りします。宜しくお願い致します。
Answer   Copyright (C) 2007.9.20 永井善王
送っていただいたExcelブックを拝見しました。 (上図は検証し易いように値を変えたものです。)
「表」シートに作成されたコンボボックス、または、コマンドボタンを操作すると実行されるマクロが、上手く機能します。 初心者さんとのことですが、相当努力されたのでしょうね。

困ってみえるSUMIF関数は、指定された検索条件に一致するセルの値を合計してくれるワークシート関数です。
その書式は、 SUMIF(範囲,検索条件,合計範囲) で、「表」シートの D8セルには今のところ、
4月度用に =SUMIF(データ!A:A,J8,データ!
B:B) と入力されています。
この中の赤字の部分を、月に対応する列名に置き換わるようにしたいのですね。

いろいろな方法があるでしょうが、「表」シートの I4セル(単月ID)に月が2桁で表示される仕組みになっていますので、その値から、B、C、D ・・ K、L、Mを導き出して利用する方法が、分かりやすいかと思います。
そのためにはワークシート関数のChooseを利用して、下記のようにコーディングします。
    単月ID = Range("I4").Value
    列 = Application.WorksheetFunction.Choose(単月ID, "K", "L", "M", _
        "B", "C", "D", "E", "F", "G", "H", "I", "J")
Choose関数の機能
この関数は値のリストから指定した値を取り出してくれ、書式は CHOOSE(インデックス,値 1,値 2,...) です。
上のコードでは、変数「単月ID」に 04 が入っていると、リストの4番目にある B を取得してくれます。 01 なら K、12 なら J となります。 分かりますね。
単月用SUMIF関数式の組み立て
こうして取得した列名を使って SUMIF関数式を組み立て、それを「表」シートの D8:D15セルに代入します。 コードは下記のようになります。
    単月 = "SUMIF(データ!A:A,J8,データ!" & 列 & ":" & 列 & ")"
    Worksheets("表").Range("D8:D15").Formula = "=" & 単月

累計表の作成
「表」シートの F8:F15セルに表示する売上個数の累計値の取得にも、いろいろな方法があるでしょうが、「データ」シートの累計版があれば、単月用のロジックと同じで済むことになります。
あれこれ考えることは面倒ですから、累計版を作ってしまいましょう。
    With Worksheets("データ")
        .Columns("A:M").Copy
        .Range("O1").PasteSpecial Paste:=xlPasteAll
        .Range("P2").Formula = "=B2"
        .Range("Q2").Formula = "=SUM($B2:C2)"
        .Range("Q2").Copy
        .Range("R2:AA2").PasteSpecial Paste:=xlPasteFormulas
        .Range("P2:AA2").Copy
        .Range("P3:AA36").PasteSpecial Paste:=xlPasteFormulas
    End With
上のコードでは、「データ」シートの右の方の余白に累計版を作成しています。
まず、A~M列をクリップボードへコピーしてから、O1セルを左上端として貼り付けます。そして、P2セルと Q2:AA2セルに数式をセットし、それらの数式を P3:AA36セルへコピー貼り付けします。
このコードを実行すると「データ」シートの A:M列の単月の右側、つまり、O:AA列に累計版ができます。

あとは、累計用SUMIF関数式を組み立て、F8:F15セル範囲に代入するコードを作成すれば終わりです。。
    列 = Application.WorksheetFunction.Choose(単月ID, "Y", "Z", "AA", _
        "P", "Q", "R", "S", "T", "U", "V", "W", "X")
    累計 = "SUMIF(データ!O:O,J8,データ!" & 列 & ":" & 列 & ")"
    Worksheets("表").Range("F8:F15").Formula = "=" & 累計
以上のコードを、あなたが作成されたマクロにどう組み込めばよいのかについては、サンプルブックをダウンロードして実物で見てください。

サンプルブックのダウンロードは ここをクリック  (YNxv9h0S2_Sumif.xls 84KB)
※ 一旦、ブックをハードディスクに保存し、開き直してから実行してください。

Excel VBA Macro