Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


3677 / 13644 ツリー ←次へ | 前へ→

【60577】CountIFで集計したい nokubo 09/3/3(火) 14:08 質問[未読]
【60579】Re:CountIFで集計したい koshimizu 09/3/3(火) 16:37 発言[未読]
【60582】Re:CountIFで集計したい こまつ 09/3/3(火) 17:20 回答[未読]
【60602】Re:CountIFで集計したい kanabun 09/3/4(水) 12:12 発言[未読]
【60604】Re:CountIFで集計したい こまつ 09/3/4(水) 15:30 お礼[未読]
【60725】別方の意味 こまつ 09/3/11(水) 15:20 質問[未読]
【60757】Re:別方の意味 kanabun 09/3/13(金) 11:23 発言[未読]
【60760】Re:別方の意味 こまつ 09/3/13(金) 11:52 質問[未読]
【60763】Re:別方の意味 kanabun 09/3/13(金) 14:12 発言[未読]

【60577】CountIFで集計したい
質問  nokubo  - 09/3/3(火) 14:08 -

引用なし
パスワード
   いろいろお世話になっております。
早速ですがご質問致します。

D1〜K1まで日付が2009/3/2〜2009/3/9まで入っております。

D8〜D65536に同じく上記の範囲で日付が入っております。
I8〜I65536には、A,B,C,D,Eがそれぞれランダムに入っております。

D2:K6までに集計数を記入したい。
D2は、Aの集計 D3はBの集計、D4はCの集計というように集計し日別表を
作成したいのです。

D8〜D65536=2009/3/2がD8〜D30なら、I8〜I30までを検索範囲とし、
検索対象はD1の2009/3/2でCountIFでできないでしょうか?

宜しくお願いいたします。

【60579】Re:CountIFで集計したい
発言  koshimizu  - 09/3/3(火) 16:37 -

引用なし
パスワード
   nokubo さん:
こんにちは。
こう言うことですか、
>D2は、Aの集計 
D2に数式で[=SUMPRODUCT(($D$8:$D$30=DATE(2009,3,2))*($I$8:$I$30="A")

【60582】Re:CountIFで集計したい
回答  こまつ  - 09/3/3(火) 17:20 -

引用なし
パスワード
   ▼koshimizu さん:
>nokubo さん:
>こんにちは。
>こう言うことですか、
>>D2は、Aの集計 
>D2に数式で[=SUMPRODUCT(($D$8:$D$30=DATE(2009,3,2))*($I$8:$I$30="A")

D8〜D65536=2009/3/2がD8〜D30ならというのは、あくまでも例であって、
実際にはD8〜D2000までが2009/3/2で、D2001〜D2564までが2009/3/3だったり
そこはランダムです。

つまり、$D$8:$D$30のように指定できないのです。

すいません。説明がうまくなく。

【60602】Re:CountIFで集計したい
発言  kanabun  - 09/3/4(水) 12:12 -

引用なし
パスワード
   ▼こまつ さん:

別法で、連想配列で仕分けしておいて、結果の配列だけをシートに値転記
する方法です。(つまり、シートに数式を埋め込んでいません)

Sub Try1() 'by dictionary
 Dim aryDate
 Dim aryKind
 Dim r As Range
 Dim dic As Object
 Dim ss As String, sDate As String
 Dim i As Long, j As Long
 Dim xx As Long
 Const yy = 5   'A,B,C,D,E 5種類
 
 '元データを配列に格納
 Set r = Range("D8", Cells(Rows.Count, "D").End(xlUp))
 aryDate = Application.Text(r, "mmdd")
 aryKind = r.Offset(, Asc("I") - Asc("D")).Value
 
 Set dic = CreateObject("Scripting.Dictionary")
 'D列+I列 を結合したキーで出現回数カウント
 For i = 1 To UBound(aryDate)
   ss = aryDate(i, 1) & aryKind(i, 1)
   dic(ss) = dic(ss) + 1
 Next
 
 'カウント結果を配列に出力
 Dim Kinds, Dates
 Kinds = Range("C2").Resize(yy).Value
 Dates = Application.Text( _
     Range("D1", Cells(1, Columns.Count).End(xlToLeft)), _
     "mmdd")
 xx = UBound(Dates)
 ReDim Ans(1 To yy, 1 To xx)
 For j = 1 To xx
   For i = 1 To yy
     ss = Dates(j) & Kinds(i, 1)
     If dic.Exists(ss) Then
       Ans(i, j) = dic(ss)
     End If
   Next
 Next
 Set dic = Nothing
 '結果をシートに出力
 Range("D2").Resize(yy, xx).Value = Ans
 MsgBox "完了"
End Sub

【60604】Re:CountIFで集計したい
お礼  こまつ  - 09/3/4(水) 15:30 -

引用なし
パスワード
   ▼kanabun様
ありがとうございます。

今、配列を勉強し、更に連想配列を勉強しております。
教示頂いたプログラムを理解しましたら、改めてお礼いたします。

【60725】別方の意味
質問  こまつ  - 09/3/11(水) 15:20 -

引用なし
パスワード
   ▼kanabun様

元データに配列を格納の箇所をご教示願えないでしょうか?
お手数ですが、宜しくお願いいたします。


>別法で、連想配列で仕分けしておいて、結果の配列だけをシートに値転記
>する方法です。(つまり、シートに数式を埋め込んでいません)
>
>Sub Try1() 'by dictionary
> Dim aryDate
> Dim aryKind
> Dim r As Range
> Dim dic As Object
> Dim ss As String, sDate As String
> Dim i As Long, j As Long
> Dim xx As Long
> Const yy = 5   'A,B,C,D,E 5種類
> 
> '元データを配列に格納
> Set r = Range("D8", Cells(Rows.Count, "D").End(xlUp))
    →変数rにDの最終行からD8までを代入する。
> aryDate = Application.Text(r, "mmdd")
    →変数aryDateは、変数rに代入した配列の表示形式をmmddとする?
    →"mmdd"とは〇〇月〇〇日のことでしょうか?
    →元データの表示形式は、2009/3/1です。その場合は"yyyymd"でいいので     しょうか?
> aryKind = r.Offset(, Asc("I") - Asc("D")).Value
     →変数aryKindには、変数rのどこがはいるんだろうか?
      Range("A1").Offset(1,1)はB2ですよね?
      Asc("I")-Asc("D")っていくつになるんですか?
      
> Set dic = CreateObject("Scripting.Dictionary")
> 'D列+I列 を結合したキーで出現回数カウント
> For i = 1 To UBound(aryDate)
>   ss = aryDate(i, 1) & aryKind(i, 1)
>   dic(ss) = dic(ss) + 1
> Next
> 
> 'カウント結果を配列に出力
> Dim Kinds, Dates
> Kinds = Range("C2").Resize(yy).Value
> Dates = Application.Text( _
>     Range("D1", Cells(1, Columns.Count).End(xlToLeft)), _
>     "mmdd")
> xx = UBound(Dates)
> ReDim Ans(1 To yy, 1 To xx)
> For j = 1 To xx
>   For i = 1 To yy
>     ss = Dates(j) & Kinds(i, 1)
>     If dic.Exists(ss) Then
>       Ans(i, j) = dic(ss)
>     End If
>   Next
> Next
> Set dic = Nothing
> '結果をシートに出力
> Range("D2").Resize(yy, xx).Value = Ans
> MsgBox "完了"
>End Sub

【60757】Re:別方の意味
発言  kanabun  - 09/3/13(金) 11:23 -

引用なし
パスワード
   ▼こまつ さん:
>▼kanabun様
>
>元データに配列を格納の箇所をご教示願えないでしょうか?

どういう問題だったか忘れてしまったので、
もういちど整理してみました。
>D1〜K1まで日付が2009/3/2〜2009/3/9まで入っております。

'--------------------------------------------------------------
'   C   D  E   F   G   H   I   J   K
'1      3/2 3/3 3/4  3/5  3/6  3/7  3/8  3/9
'2   A
'3   B
'4   C
'5   D
'6   E
'--------------------------------------------------------------

>D8〜D65536に同じく上記の範囲で日付が入っております。
>I8〜I65536には、A,B,C,D,Eがそれぞれランダムに入っております。
'--------------------------------------------------------------
'   D列  ・・・  ・・・ I列
' 7  日付          種別
' 8  3月2日         A
' 9  3月2日         A
'10  3月2日         B
'11  3月2日         D
'12  3月2日         A
'13  3月2日         A
'14  3月2日         B
'15  3月3日         B
'16  3月3日         B
'17  3月3日         B
'18  3月3日         C
' :   :          :
' :   :          :
'
'
'--------------------------------------------------------------

>D2: K6までに集計数を記入したい。
>D2は、Aの集計 D3はBの集計、D4はCの集計というように集計し日別表を
>作成したいのです。
つまり、日付別、種別でクロス集計したい、ということですね?
↓クロス集計後のイメージ
'--------------------------------------------------------------
'   C   D  E   F   G   H   I   J   K
'1      3/2 3/3 3/4  3/5  3/6  3/7  3/8  3/9
'2   A   8  8   12  48  281  37  3   4
'3   B   7  6   9  41  234  31  2   3
'4   C   6  5   7  33  187  25  1   3
'5   D   10 15   15  72  421  57  4   7
'6   E   6  9   12  48  281  37  3   4
'--------------------------------------------------------------
CountIf的な考え方でいうと、
[D2]セルに D列のデータが 「3月2日」で、
      かつ、I列のデータが「A」である行の数を、
[E2]セルに D列のデータが 「3月3日」で、
      かつ、I列のデータが「A」である行の数を、

[D3]セルに D列のデータが 「3月2日」で、
      かつ、I列のデータが「B」である行の数を、
という風に 2条件で CountIfしたい、ということですね?
 余談(
  Excel2007 で登場したCountIfs関数を使うと、
 [D2] =COUNTIFS(D8:D6000,D8:D6000,D1,I8:I6000,C2)
 ということですね?)
数式はセル単位にセットされますから、
[D2]セルの数式が答えを得るために元データを上から下まで走査し、
[D3]セルの数式が答えを得るためにまた元データを上から下まで
走査しています。
集計表の各セルが同じ集計範囲を個々の条件で走査していますから
表がおおきいと重くなります。
こういう集計はピボットテーブル機能が得意とするところであること
はさておいて、
「連想配列」による方法は考え方を変えて、元データのほうを
一回だけ走査して、D列の日付とI列の種別を組みとして、各組みの
出現回数をカウントします。
元データが
>   D列  ・・・  ・・・ I列
> 7  日付          種別
> 8  3月2日         A
> 9  3月2日         A
>10  3月2日         B
>11  3月2日         D
のようにあるとき、
最初のレコード(8行目)は 日付が「3月2日」で種別が「A」ですから、
「3月2日A」というラベルのついた入れ物をひとつ用意し、この容器の
なかに 数値1 を代入しておきますと、「3月2日A」というキーは1回
出現したとCountされたことになります。
次の行(9行目)のレコードからラベル(キー)を作ると 最初のレコード
同様、「3月2日A」というラベルです。このラベルのついた容器はすでに
用意されているので、「3月2日A」というラベルの入れ物に +1 してやり
ます。
次の行(10行目)のレコードからラベル(キー)を作ると「3月2日B」と
なります。そしたら 「3月2日B」の容器に +1 してやればいいわけです
(「3月2日B」という容器はまだ作られていませんでしたが、

  「3月2日B」という容器に+1しなさい
>>   ss = aryDate(i, 1) & aryKind(i, 1) '容器のラベル(キー)
>>   dic(ss) = dic(ss) + 1       'その容器に1を加算

 とコードを書いておけば、まだそういう名前の容器が存在しなければ
 自動で用意してくれます。)

といった手順で、全レコードを「日付と種別」別に用意された容器内に
出現回数を入れておきますと、
あとは集計表の 列見出しと行見出しをみて
 [D2] = 「3月2日A」という容器のなかの数量
 [D3] = 「3月2日B」という容器のなかの数量
 ...
と集計表の各セルに答え(カウント結果)を転記していけばいいわけです。
したがいまして、

>> '元データを配列に格納
>> Set r = Range("D8", Cells(Rows.Count, "D").End(xlUp))
>    →変数rにDの最終行からD8までを代入する。
     ↑ちょっとちがいます
⇒Range型の変数r にD8からD列最終行までの範囲を「Set」する
  ということです。
 
>> aryDate = Application.Text(r, "mmdd")
>    →変数aryDateは、変数rに代入した配列の表示形式をmmddとする?
>    →"mmdd"とは〇〇月〇〇日のことでしょうか?
>    →元データの表示形式は、2009/3/1です。その場合は"yyyymd"
>     でいいのでしょうか?
 ⇒表示形式は何でもかまいません。準備するカウント用容器につける
  ラベル名(これを Key といいます)が一意に識別できる文字列で
  あれば、なんでもいいです。
  日付データが2年間にわたるものであれば、年度の情報も必要となり
  そのばあいは "yyyymd" とかにしてください。

>> aryKind = r.Offset(, Asc("I") - Asc("D")).Value
>     →変数aryKindには、変数rのどこがはいるんだろうか?
>      Range("A1").Offset(1,1)はB2ですよね?
>      Asc("I")-Asc("D")っていくつになるんですか?
 ⇒r はいま D列の範囲です。
  rの実際の範囲が[D8:D6000]としますと、
  r.Offset(, 1) は [E8:E6000] ですよね?
  おなじように、
  r.Offset(, Asc("I") - Asc("D") は どこになりますか?

【60760】Re:別方の意味
質問  こまつ  - 09/3/13(金) 11:52 -

引用なし
パスワード
   ▼kanabun さん:
>>> aryKind = r.Offset(, Asc("I") - Asc("D")).Value
>>     →変数aryKindには、変数rのどこがはいるんだろうか?
>>      Range("A1").Offset(1,1)はB2ですよね?
>>      Asc("I")-Asc("D")っていくつになるんですか?
> ⇒r はいま D列の範囲です。
>  rの実際の範囲が[D8:D6000]としますと、
>  r.Offset(, 1) は [E8:E6000] ですよね?
>  おなじように、
>  r.Offset(, Asc("I") - Asc("D") は どこになりますか?

Asc("I")-Asc("D")=5ですよね!!
なら、[I8:I6000]と思います。
なぜAscで表現したのですか? r.Offset(,5)でもよかったのではないでしょうか?
確実性の問題でしょうか?
Asc("I")=9ですよね?

【60763】Re:別方の意味
発言  kanabun  - 09/3/13(金) 14:12 -

引用なし
パスワード
   ▼こまつ さん:
>Asc("I")-Asc("D")=5ですよね!!
>なら、[I8:I6000]と思います。
>なぜAscで表現したのですか? 
2つの列のオフセット量を表すのに
I列の列番号がすぐ分からなかったので、そうしたまでです。

>r.Offset(,5)でもよかったのではないでしょうか?
それでかまいません。

>確実性の問題でしょうか?
強いて言えば、可読性の問題ですかね?
  r.Offset(,5)
より、
  r.Offset(, asc("I") - asc("D"))
のほうが 「D」列から「I」列までのオフセット量を表す式
であることが直観的に分かりやすいと思いませんか?

>Asc("I")=9ですよね?
いえ ?asc("I") は
73
です。

------------
閑話休題
クロス集計するにはいろいろな方法があるわけです。
先ほど Excel2007の COUNTIFs 関数を紹介しましたが、
CountIf だって2つの条件でCOUNTすることができるんですよ

どこか空いている列(たとえば J列とします)に
 [J8] =D8&I8
として、データ最終行までフィルしておいてください。
                 ↓こうなります。
   D           I    J
7 日付          種別  
8 3月2日         A    39874A
9 3月2日         B    39874B
10 3月2日         C    39874C
11 3月2日         A    39874A
:  :          :

で、COUNTIF関数で集計表を埋めるには、
[D2:K6]を選択して、
[D2]セルに 数式 =COUNTIF($J$8:$J$6000,D$1&$C2) をタイプして
  Ctrl+[Enter] で確定すればいいわけです。

クロス集計には状況に応じていろいろな方法がありうるということです。
自分にあった方法をお選びください。

3677 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free