Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2010)
COUNTIFSで変数を使った集計について
投稿日時: 19/01/08 06:57:55
投稿者: R2D2

COUNTIFSを使った集計について、ご質問させてください。
 
C列にCOUNTIFS関数を入れて、ある日付以降且つ、品名ごとの集計をしたいのですが、
<式1>を使って、セルA1~ADまでCOUNTIFSの関数を入力して個数を算出出来ました。
ですが、品名数が多くなるとと行数が増えてしまいます。
そこで、D列に変数用の品名を設け、かつFor Next文を使って行数を少なくしたいと考えています。
その場合、COUNTIFS関数へ、どの様にして変数を入れれば良いのでしょうか?
<式2>のように条件部分に変数jを入れているのですが、うまくいかない状況です。
 
 A列    B列    C列    D列
 日付 品名 個数   変数用
2015/1/1  リンゴ  計算式  リンゴ
    ・   バナナ    ・   バナナ
  ・   ブドウ ・   ブドウ
2019/1/1  みかん   ・ みかん
 
<式1>
  Cells(1, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""リンゴ"")"
  Cells(2, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""バナナ"")"
  Cells(3, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""ブドウ"")"
  Cells(4, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""みかん"")"
 
<式2>
Dim i As Long
Dim j As String
        For i = 1 To 10
     For j = 1 to 10
            Cells(i, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""& j"")"
        Next i

回答
投稿日時: 19/01/08 09:43:01
投稿者: Suzu

A列	B列	C列	D列
日付	品名	個数	変数用
2015/1/1	リンゴ	1	リンゴ
2015/1/1	バナナ	2	バナナ
2015/1/1	ブドウ	3	ブドウ
2016/1/1	ブドウ	4	ブドウ
2017/1/1	ブドウ	5	ブドウ
2019/1/1	みかん	6	みかん

 
ご質問の意図を汲取れません。
 
引用:
<式1>を使って、セルA1~ADまでCOUNTIFSの関数を入力して個数を算出出来ました。

 
との事ですが、上記の様なデータが有った時、得たい結果としてはどうなるのですか?
 
 
 
また、
引用:
<式1>
  Cells(1, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""リンゴ"")"
  Cells(2, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""バナナ"")"
  Cells(3, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""ブドウ"")"
  Cells(4, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""みかん"")"

これらの左辺を拝見すると、A1、A2、A3、A4 に それぞれ関数式を与えているのですが。。
A列は、日付のデータなのでは? データ上書きしても良いのですか?
 
D列を設ける意図も判りません。。
 
何となく。。。ピボットテーブルでは希望の物を得られないのでしょうか?

投稿日時: 19/01/08 19:53:58
投稿者: R2D2

Suzuさん
 
コメントありがとうございます。
COUNTIFS関数をVBAで使う手間より、ピボットテーブルを使った方がシンプルで早そうですね。
そのように致します。
 
併せて一点質問してよろしいでしょうか?
下記<式2>の場合、条件部分(↓部分)に変数を入れる場合は、どの様に記載すれば良いのでしょうか?
 
                                 ↓
 Cells(i, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""& j"")"

回答
投稿日時: 19/01/08 22:58:58
投稿者: simple

Dim i As Long 
Dim j As String 
        For i = 1 To 10 
            For j = 1 to 10 
            Cells(i, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""& j"")" 
        Next i 

は、以下のつもりだと解釈します。(しっかり整理したもので質問して下さいね。)
   Dim i As Long
    Dim j As String
    For i = 1 To 10
        For j = 1 To 10
            Cells(i, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""& j"")"
        Next j
    Next i

(1)
引用:
これらの左辺を拝見すると、A1、A2、A3、A4 に それぞれ関数式を与えているのですが。。
A列は、日付のデータなのでは? データ上書きしても良いのですか?
という指摘に対するお答えがないのですが、ここはいかがですか?
 
(2) 変数jについて
j という変数は何を入れるものなのですか?
    Dim j As String
と文字列型で宣言しているのに、
        For j = 1 To 10
というのは整合していません。
 
(3)
引用符"の中の、
""& j"") ですが、これの意図は何でしょうか。
& は文字列の連結をしようとしていると解されますが、
何と何を連結しようとしているのですか?

回答
投稿日時: 19/01/09 07:41:16
投稿者: Suzu

どんな状態の表に対して、どうしたい のかが読み取れないので、
数式をどう構築したら良いのか判らないのですよ。
 
 

引用:
下記<式2>の場合、条件部分(↓部分)に変数を入れる場合は、どの様に記載すれば良いのでしょうか?
                                 ↓
 Cells(i, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""& j"")"

 
を回答する為には、
元の表から、VBAを使いどんな表にしたいのかを知りたかったのです。
 
 
欲しい物としては、
 Cells(i, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10," & j & ")"
なのだと思います。
 
 
それが出来たとしても、
 
simpleさんが
引用:
For j = 1 To 10
            Cells(i, 1).Value = "=COUNTIFS(A1:A10, "">=2015/1/1"",B1:B10,""& j"")"
        Next j

 
の様に整理されていますが、
jが、1〜10 に変わる 間中、 に対し、右辺の数式を代入しています。
なので、
A列、i行目 のセル には、最終的に jが10 の時の 数式しか残らない。
 
なので、意味のない For j = 1 To 10 の繰り返しになるのです。
 
 
なので、このコードでどんな表を作りたいのか、実例として示してほしかったのです。

回答
投稿日時: 19/01/09 20:24:52
投稿者: simple

私も賛成です。
どんなインプットをもとにどんなアウトプットを出したいのか明確に示すことが
第一ステップだと思います。
その際、・・・などと省略してはダメです。
そして、インプットとアウトプットが論理的に整合したものを提示してください。

     A      B      C      D
1
2
3
のようなスタイルで行、列を明示するとよいと思います。
表部分を選択状態にしたうえで、「コード」というボタンを押すと、表がくずれません。
 
# 私は、日付の≧を使っているところが釈然としていないのですが、
# それも含めて、インプット、アウトプットを示して貰いたいと思います。
 
そのデータを示すことは、プログラムを開発する際の出発点です。
ここが曖昧だといつまでたっても、問題そのものが明確になりません。
頑張って下さい。

回答
投稿日時: 19/01/09 20:55:48
投稿者: simple

実際のデータは示せません、という回答が予想されますが、それは求めていません。
架空のもので結構です。
ただし、3行とかではなく、少なくとも10行くらいのもので、
ある程度、考えられる不規則性が入ってものが望ましいです。
(つまり、全部同じデータとかそういうのはダメということです。)

投稿日時: 19/01/10 06:31:01
投稿者: R2D2

Suzuさん simpleさん
 
意味不明の質問で、混乱させてしまい申し訳ありません。
記載しておりましたコードも間違っておりました。
 
実行したい内容としては、以下になります。
1. A列で2015/1/1以降のもの且つ、B列からC列の品名にヒットしたものをD列に表示(集計)する。
2. 品名は10種類の為、COUNTIFS関数を貼り付けるのは、D2〜D11までの10行。
3. COUNTIFS関数を貼り付けるのは10行ですが、For〜Next分を使ってコードの行数を少なくしたい。
 
Suzuさんに記載頂きましたコードを流用させて頂きました。
上記の内容用に修正しますと、下記の様なコードで良いでしょうか?
 
   Dim i As Long
    Dim j As String
    For i = 2 To 11
        For j = 2 To 11
    Cells(i, 4).Value = "=COUNTIFS(A2:A11, "">=2015/1/1"",B2:B11," & j & ")"
        Next j
    Next i
 
<マクロ実行前>

A      B       C      D 
1    日付       品名     変数用     個数
2  2015/1/1  リンゴ  リンゴ
3  2015/9/1  バナナ  バナナ
4  2016/1/1  ブドウ  ブドウ
5  2016/9/1  みかん  みかん
6  2016/9/1  メロン  メロン 
7 2017/1/1  キウイ  キウイ
8 2017/9/1  イチゴ  イチゴ
9  2018/1/1  スイカ  スイカ
10 2018/9/1  すもも  すもも
11 2018/9/1  ざくろ  ざくろ

 
<マクロ実行後>
A      B       C      D 
1    日付       品名     変数用     個数
2  2015/1/1  リンゴ  リンゴ   1個
3  2015/9/1  バナナ  バナナ     1個
4  2016/1/1  ブドウ  ブドウ     1個
5  2016/9/1  みかん  みかん     1個
6  2016/9/1  メロン  メロン     1個
7 2017/1/1  キウイ  キウイ    1個
8 2017/9/1  イチゴ  イチゴ   1個
9  2018/1/1  スイカ  スイカ   1個
10 2018/9/1  すもも  すもも     1個
11 2018/9/1  ざくろ  ざくろ     1個
[/code]

回答
投稿日時: 19/01/10 09:52:04
投稿者: Suzu

VBAのコード/表データから
・変数 「j」
・C列
の役割が判断できないので、あてずっぽうです・・・
 
D2 へ代入したい数式は
=COUNTIFS(A$2:A$11, ">=2015/1/1",B$2:B$11,C2)
という事?
D3が
=COUNTIFS(A$2:A$11, ">=2015/1/1",B$2:B$11,C3)
   :
  以下「3」の部分が数式を入れる行数と同じくカウントアップ
 
そうだとすれば、変わっているのは、C2/C3 の部分。
セルの参照が、A1形式の数式ですが、R1C1形式にすれば
D2〜Dx の数式 全て
=COUNTIFS(R2C[-3]:R11C[-3], ">=2015/1/1",R2C[-2]:R11C[-2],RC[-1])
にて表せます。
 
R1C1については、【Excelでお仕事】
http://www.asahi-net.or.jp/~ef2o-inue/shiki/sub03_010_02.html
 
 
なので
 
数式を入れる行が 2行目から、11行目 と 決まっているならループも何も必要とせず
 
Sub Sumple()
  Worksheets("Sheet1").Range("D2:D11").FormulaR1C1 = _
    "=COUNTIFS(R2C[-3]:R11C[-3], "">=2015/1/1"",R2C[-2]:R11C[-2],RC[-1])"
End Sub
 
で済むのでは?
 
https://www.moug.net/tech/exvba/0050098.html
https://www.moug.net/tech/exvba/0050143.html

回答
投稿日時: 19/01/10 10:27:14
投稿者: Suzu

引用:
VBAのコード/表データから
・変数 「j」
・C列
の役割が判断できないので、あてずっぽうです・・・

 
の意図として
 
R2D2 さんの 19/01/10 06:31:01 の投稿が
例として良くないです。
 
・B列とC列 は 値としては 同じ値。 違う場合があるのか無いのか判断できない。
 
・変数「j」は、数式の中では、B2:B11 と 比較 する 事になる 値です。
  先にも言いましたが、Jは最終的に「11」の値をとりますので
   Cells(i, 4) へは
   数式 =COUNTIFS(A2:A11, ">=2015/1/1",B2:B11, 11) が代入されています。
   
  B2:B11 は 「くだもの名」の文字列で、「11」は 数値 合致するはずがなく
  結果は 0 しか 取らないが、希望は、1。
 
上記二つから B2:B11 と比較したいのは、C列では無いかと推測し
 
 
引用:
D2 へ代入したい数式は
=COUNTIFS(A$2:A$11, ">=2015/1/1",B$2:B$11,C2)
という事?
D3が
=COUNTIFS(A$2:A$11, ">=2015/1/1",B$2:B$11,C3)

となっています。
 
回答が意図した物と違うのであれば
・整合性の取れた例
・セルに入っていてほしい数式
を提示ください。

投稿日時: 19/01/12 04:07:36
投稿者: R2D2

Suzuさん
 
当初は下記の様な表でA、B列の日付、品名は1,000列ほどあり、C列に式1を入力して集計していました。
品名は10個ありますので、C2〜C11まで式が入力されています。
ですが、品名が増えた場合はコードの行数が増える為、C列に変数用の品名列を設けて、
かつ、For〜Next文でコードの行数を少なくできないかと考えました。
 
Suzuさんに記載頂きましたR1C1形式を使うと、ループさせなくても出来るんでしょうか?
※すいません、まだコード内容を理解できておりません。
 
<式1>C列に入力
Cells(2, 3).Value = "=COUNTIFS(A1:A10, "">=2014/1/1"",B1:B10,""リンゴ"")"
Cells(3, 3).Value = "=COUNTIFS(A1:A10, "">=2014/1/1"",B1:B10,""バナナ"")"
Cells(4, 3).Value = "=COUNTIFS(A1:A10, "">=2014/1/1"",B1:B10,""ブドウ"")"
Cells(5, 3).Value = "=COUNTIFS(A1:A10, "">=2014/1/1"",B1:B10,""みかん"")"
 
<当初の表>

A      B      C 
1    日付       品名     個数
2  2014/1/1  リンゴ
3  2014/9/1  バナナ
4  2015/1/1  ブドウ
5  2015/9/1  みかん
6  2016/1/1  メロン 
7 2016/9/1  キウイ
8 2017/1/1  イチゴ
9  2017/9/1  スイカ
10 2018/1/1  すもも
11 2018/9/1  ざくろ
12 2019/1/1  リンゴ
〜
1000 2019/9/1 バナナ

 
<マクロ実行前>
   A     B    C    D
1    日付       品名     個数  変数用
2  2014/1/1  リンゴ      リンゴ
3  2014/9/1  バナナ      バナナ
4  2015/1/1  ブドウ      ブドウ
5  2015/9/1  みかん      みかん
6  2016/1/1  メロン      メロン
7 2016/9/1  キウイ      キウイ
8 2017/1/1  イチゴ      イチゴ
9  2017/9/1  スイカ      スイカ
10 2018/1/1  すもも      すもも
11 2018/9/1  ざくろ      ザクロ
12 2019/1/1  リンゴ
〜
1000 2019/9/1 バナナ

 
<マクロ実行後>
   A     B    C    D
1    日付       品名     個数  変数用
2  2014/1/1  リンゴ  ※個  リンゴ
3  2014/9/1  バナナ  ※個  バナナ
4  2015/1/1  ブドウ  ※個  ブドウ
5  2015/9/1  みかん  ※個  みかん
6  2016/1/1  メロン  ※個  メロン
7 2016/9/1  キウイ  ※個  キウイ
8 2017/1/1  イチゴ  ※個  イチゴ
9  2017/9/1  スイカ  ※個  スイカ
10 2018/1/1  すもも  ※個  すもも
11 2018/9/1  ざくろ  ※個  ザクロ
12 2019/1/1  リンゴ
〜
1000 2019/9/1 バナナ

回答
投稿日時: 19/01/12 11:14:58
投稿者: WinArrow
投稿者のウェブサイトに移動

集計するセルが11個なんでしょ!
 
VBAでしかもForループを使って、対応する必要があるのかな?
 
可変にする個所は、検索対象の行数でしょ!
A1:AXXX
B1:Bxxx
のように可変部分(xxx)を取得するのにVBAを使う問うことならば、意味があります。
その場合でも、ループは必要ない。・・・と思う
 

回答
投稿日時: 19/01/12 12:29:43
投稿者: simple

こんにちは。
 
例示をしてもらいましたが、それは集計する例になっていないですよね。
すべての個数が1 だなんて、特殊すぎるでしょう?
 
普通はこんな例を考えませんか?(除外するデータも入れるとか)

     A         B       C       D       E
1    日付      品名            品名    個数
2    2013/1/1  リンゴ          リンゴ     2
3    2014/9/1  バナナ          バナナ     3
4    2015/1/1  リンゴ          ブドウ     2
5    2015/9/1  バナナ          みかん     1
6    2016/1/1  ブドウ          メロン     1
7    2016/9/1  みかん
8    2017/1/1  メロン
9    2017/9/1  リンゴ
10   2018/1/1  バナナ
11   2018/9/1  ブドウ

 
19/01/10 09:52:04の Suzuさんのご発言をお読みになっているのですか?
品名の文字列を直接使わずに、集計表のセルの値を参照するのが普通ですよ。
 
全般に、マクロの前に普通のワークシート関数の使い方を
もっと勉強されたほうがよいと思います。(私も余り言えた義理ではないですが)
 
ワークシートでの計算式の設定方法は色々あります。
(1)E2 セルに =COUNTIFS(A:A,">=2014/1/1",B:B,D2) と入力して、
   これを下にコピーしてもよいし(これが一番簡単で一般的?)
(2)E2:E6セルを選択して、
   =COUNTIFS(A:A,">=2014/1/1",B:B,D2)
   を入力して、Ctrl+Enter でも一発でセットできます。(Excelが、適切に補正してくれます。)
(3)もちろん、R1C1形式を一度にセットしてもよいでしょう。
 
--------------------------------
マクロを使う必要性も殆どないはずですが、あえて書くならこんなことでしょう。
Sub test1()
    Dim r  As Long
    
    r = Cells(Rows.Count, "D").End(xlUp).Row
    Range("E2:E" & r).Formula = "=COUNTIFS(A:A,"">=2014/1/1"",B:B,D2)"
End Sub

Sub test2()
    Dim r  As Long
    
    r = Cells(Rows.Count, "D").End(xlUp).Row
    Range("E2:E" & r).FormulaR1C1 = "=COUNTIFS(C[-4],"">=2014/1/1"",C[-3],RC[-1])"
End Sub

Sub test3()     ' 繰り返しの練習のために
    Dim r   As Long
    Dim k   As Long
    
    r = Cells(Rows.Count, "D").End(xlUp).Row
    For k = 2 To r
        Cells(k, "E").Formula = "=COUNTIFS(A:A,"">=2014/1/1"",B:B,D" & k & ")"
    Next
End Sub

 
なお、集計の起算日(2014/1/1)もどこかのセル(例えばH1)に書き、
=COUNTIFS(A:A,">=" & $H$1,B:B,D2)
といった式にするのが普通です。

回答
投稿日時: 19/01/12 16:42:03
投稿者: WinArrow
投稿者のウェブサイトに移動

simpleさんのレスの最後の
>抽出用日付をH1セルにする
を拝借して
抽出用品名抽出〜集計用数式作成(ループなし)
の参考コードを掲示します。
 
 
 
Dim MaxRow As Long
 
    With ActiveSheet
        MaxRow = .Range("B" & .Rows.Count).End(xlUp).Row
        '集計用(重複なし)品名をD列に抽出
        .Range("B1:B" & MaxRow).AdvancedFilter _
            Action:=xlFilterCopy, _
            CopyToRange:=Range("D1"), _
            Unique:=True
        '集計用数式をC列に作成
        .Range("C2:C" & .Range("D" & .Rows.Count).End(xlUp).Row).Formula = _
            "=COUNTIFS(A:A,"">="" & $H$1,B:B,D2)"
    End With

投稿日時: 19/01/13 21:34:42
投稿者: R2D2

WinArrowさん simpleさん
 
ご記載頂きましたコードで、希望通りの結果を出す事が出来ました。
伝わりにくい返答ばかりで、何度もお手数をおかけいたしました。
今後も質問させて頂くことがあると思いますので、よろしくお願い致します
 
>マクロの前に普通のワークシート関数の使い方をもっと勉強されたほうがよいと思います。
おっしゃる通りです。
関数の使い方に関する勉強も並行して行ってまいります。