Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1662 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【72775】二次元配列の列にcountif H=R 12/9/18(火) 21:25 質問[未読]
【72776】Re:二次元配列の列にcountif UO3 12/9/18(火) 21:43 発言[未読]
【72777】Re:二次元配列の列にcountif UO3 12/9/18(火) 21:53 発言[未読]
【72782】Re:二次元配列の列にcountif H=R 12/9/18(火) 23:30 質問[未読]
【72783】Re:二次元配列の列にcountif UO3 12/9/19(水) 0:01 発言[未読]
【72784】Re:二次元配列の列にcountif H=R 12/9/19(水) 8:42 お礼[未読]
【72785】Re:二次元配列の列にcountif UO3 12/9/19(水) 9:48 発言[未読]
【72794】Re:二次元配列の列にcountif ウッシ 12/9/19(水) 15:11 回答[未読]
【72796】Re:二次元配列の列にcountif 佳 12/9/19(水) 15:57 回答[未読]
【72798】Re:二次元配列の列にcountif H=R 12/9/19(水) 17:16 お礼[未読]
【72802】Re:二次元配列の列にcountif 佳 12/9/20(木) 6:23 回答[未読]
【72803】Re:二次元配列の列にcountif H=R 12/9/20(木) 10:01 お礼[未読]
【72775】二次元配列の列にcountif
質問  H=R  - 12/9/18(火) 21:25 -
引用なし
パスワード
   お世話になります。

variant型の二次元配列の列ごとに、countif関数で内容を数えたいのですが、
WorksheetFunction.CountIfの引数をどのように指定すればいいのかが
分かりません。
方法をご存知の方、お教えいただけますでしょうか。
よろしくお願いいたします。
【72776】Re:二次元配列の列にcountif
発言  UO3  - 12/9/18(火) 21:43 -
引用なし
パスワード
   ▼H=R さん：

こんばんは
２次元配列から単一行あるいは単一列を取り出すことは
WorksheetFunctionのIndexやTransposeで可能ですが
残念ながら、CountIfは領域に配列指定ができません。
【72777】Re:二次元配列の列にcountif
発言  UO3  - 12/9/18(火) 21:53 -
引用なし
パスワード
   ▼H=R さん：

ですから、CountIfには使えませんが、2次元配列から単一行、単一列を抽出するサンプル。
ご参考まで。

Sub Sample()
　　Dim v As Variant
　　Dim w1 As Variant
　　Dim w2 As Variant
　　Dim w3 As Variant
　　
　　v = Range("A1:C5").Value '5行3列の2次元配列
　　'配列の2行目だけを取り出す--->結果は1次元配列
　　w1 = WorksheetFunction.Index(v, 2, 0)
　　'配列の2列目だけを取り出す--->結果は5行1列の2次元配列
　　w2 = WorksheetFunction.Index(v, 0, 2)
　　'配列の2列目だけを取り出す--->結果は1次元配列
　　w3 = WorksheetFunction.Transpose(WorksheetFunction.Index(v, 0, 2))
　　
End Sub
【72782】Re:二次元配列の列にcountif
質問  H=R  - 12/9/18(火) 23:30 -
引用なし
パスワード
   UO3様

ご回答ありがとうございます。
サンプルまでつけていただき、感謝いたします。

CountIfに配列が使えないというのは想定しておりませんでした。
カウントしたければループで1つ1つ地道にやる以外ないでしょうか。
１万行ほどのデータから各列の度数分布を別の二次元配列に格納するのが目的です。
効率的な方法があればお教えいただけますと幸いです。

よろしくお願いいたします。
【72783】Re:二次元配列の列にcountif
発言  UO3  - 12/9/19(水) 0:01 -
引用なし
パスワード
   ▼H=R さん：

配列内ループもメモリー内処理ですからけっして非効率ではないと思います。
10000万行程度なら問題は全くないと思いますよ。
あるいは、配列を作業シートに落とし込んで、作業シート上でCountIfを使う手もあります。

変数定義は省略しますが以下の例では2次元配列名を v としています。

（配列内ループ方式）

　　Dim i As Long
　　Dim j As Long
　　Dim x As Long

　　For j = LBound(v, 2) To UBound(v, 2)
　　　　x = 0
　　　　For i = LBound(v, 1) To UBound(v, 1)
　　　　　　If v(i, j) = "ABC" Then x = x + 1
　　　　Next
　　　　MsgBox j & "列目:" & x
　　Next

（作業シート使用方式）

　　Dim r As Range
　　
　　With Sheets("作業シート")
　　　　.Cells.ClearContents
　　　　.Range("A1").Resize(UBound(v, 1), UBound(v, 2)).Value = v
　　　　For Each r In .Range("A1").CurrentRegion.Columns
　　　　　　MsgBox r.Column & "列目:" & WorksheetFunction.CountIf(r, "ABC")
　　　　Next
　　End With
【72784】Re:二次元配列の列にcountif
お礼  H=R  - 12/9/19(水) 8:42 -
引用なし
パスワード
   UO3様

>配列内ループもメモリー内処理ですからけっして非効率ではないと思います。
>10000万行程度なら問題は全くないと思いますよ。
>あるいは、配列を作業シートに落とし込んで、作業シート上でCountIfを使う手もあります。

元々、作業シート上でCountIfを使っていましたが、
シートへのデータの書き込みを含めて予想以上に時間がかかることから、
配列のまま処理できないかと考えた次第です。
一度ループを試してみて、作業シートの方法と比較してみます。
どうもありがとうございました。
【72785】Re:二次元配列の列にcountif
発言  UO3  - 12/9/19(水) 9:48 -
引用なし
パスワード
   ▼H=R さん：

おはようございます

>シートへのデータの書き込みを含めて予想以上に時間がかかることから

たしかにシート上のCountIfは、処理コストが高いと、よくいわれますね。
でも、その、時間がかかっていた原因は、むしろ、結果をセルに書き込む部分に
あったのではないでしょうか？

セルへの書き込みは、書き込み回数（書き込みセル数ではありません）が多くなれば
もう、極端に処理時間が長くなりますので、できるだけそれを減らす、できれば
そこのところを配列におさめておいて、最後に一回でシートに書き込めば
ずいぶん改善するのではと思います。
（この場合、よく使う方法の Application.ScreenUpdating　のFalse/True　処理も不要。
　というか、むしろ足を引っ張る場合もあるくらいです）
【72794】Re:二次元配列の列にcountif
回答  ウッシ  - 12/9/19(水) 15:11 -
引用なし
パスワード
   こんにちは

Sheet1のデータを配列に格納して列毎、要素毎にカウントしてSheet2に書き出してます。

Sub test()
　　Dim v　 As Variant
　　Dim vv　As Variant
　　Dim k　 As Variant
　　Dim h　 As Long
　　Dim i　 As Long
　　Dim j　 As Long
　　
　　v = Worksheets("Sheet1").Range("A1").CurrentRegion.Value
　　j = UBound(v, 2)
　　ReDim x(1 To j, 1 To 1)
　　
　　For i = 1 To j
　　　　vv = WorksheetFunction.Index(v, 0, i)
　　　　With CreateObject("Scripting.Dictionary")
　　　　　　For Each k In vv
　　　　　　　　.Item(k) = .Item(k) + 1
　　　　　　Next
　　　　　　h = 1
　　　　　　For Each k In .Keys()
　　　　　　　　If k <> "" Then
　　　　　　　　　　If h > UBound(x, 2) Then
　　　　　　　　　　　　ReDim Preserve x(1 To j, 1 To h)
　　　　　　　　　　End If
　　　　　　　　　　x(i, h) = i & "列:要素＝" & k & ":要素数＝" & .Item(k)
　　　　　　　　　　h = h + 1
　　　　　　　　End If
　　　　　　Next
　　　　End With
　　Next
　　Worksheets("Sheet2").Range("A1").Resize(UBound(x, 2), j).Value = _
　　　　WorksheetFunction.Transpose(x)
End Sub

このまま Transpose 使う場合は要素数の制限があるのでデータ数に注意が必要です。
【72796】Re:二次元配列の列にcountif
回答  佳  - 12/9/19(水) 15:57 -
引用なし
パスワード
   こんにちは。

自分は普段から大きな表の度数分布とか扱わないのではずしていたらすみませんが
度数分布には専門の関数がいくつかあったと思います。ぱっと思いつくのは
FREQUENCY関数ですがほかにもあった記憶です。それを専門とする関数なんだから
きっと早かろうと思います。いちど試されてはいかがでしょう。

ちなみにCOUNTIF関数が遅いのは、参照先のデータをひとつひとつ、全部確認する
ためです。データを昇順に並べ替えて、MATCH関数で第３引数に１を指定すれば、
探索方法が２分探索になりますので、劇的に早くなります。


> シートへのデータの書き込みを含めて予想以上に時間がかかることから、
あ、これを見落としていました。しかしそんなに時間がかかりますかね。
書き込みは一気にペタッと書き込んでいるのですよね。
書き込みするときは再計算は手動にしてありますよね。
【72798】Re:二次元配列の列にcountif
お礼  H=R  - 12/9/19(水) 17:16 -
引用なし
パスワード
   UO3様　ウッシ様　佳様

多くのアドバイス、本当にありがとうございます。
実際に試して確認しながら模索していますので時間がかかっておりますが、
まず自分の整理のためにも返信いたします。

UO3様

> セルへの書き込みは、書き込み回数（書き込みセル数ではありません）が多くなれば
> もう、極端に処理時間が長くなりますので、できるだけそれを減らす、できれば

書き込みセル数より回数が時間に関係するというのは非常にありがたい情報です。
いくらか改善できる点がありますので取り組んでみます。

ウッシ様

具体的なループの方法をお教えいただき、ありがとうございます。
現在、参考にして試作版を作成中です。

佳様

> FREQUENCY関数

文字列データですので、FREQUENCY関数が使えないという難点があります。

> ためです。データを昇順に並べ替えて、MATCH関数で第３引数に１を指定すれば、
> 探索方法が２分探索になりますので、劇的に早くなります。

MATCH関数はほとんど使ったことがないのですが、Dictionaryでのカウント方法でも
使えますでしょうか。

> 書き込みは一気にペタッと書き込んでいるのですよね。
> 書き込みするときは再計算は手動にしてありますよね。

書き込みは一部分割して行っている部分がありましたので、現在修正中です。
再計算は手動にしております。

以上、よろしくお願いいたします。
【72802】Re:二次元配列の列にcountif
回答  佳  - 12/9/20(木) 6:23 -
引用なし
パスワード
   こんにちは。


度数分布の関数もmatch関数も、countif関数の代わりに提案したものです。
dictionaryは関係ありません。

match関数を提案したときは、シート上で使うことを考えていましたが
ヘルプを見ると配列に対しても適用できるようです。
こんな感じです。

Sub sample()
　Dim v(6) As String
　Dim x As Long
　Dim y As Long
　
　v(0) = "aa"
　v(1) = "ab"
　v(2) = "ba"
　v(3) = "bb"
　v(4) = "bc"
　v(5) = "ca"
　v(6) = "cb"
　
　x = Application.Match("b", v, 1)
　y = Application.Match("c", v, 1)
　MsgBox "b*の数　" & y - x
End Sub
【72803】Re:二次元配列の列にcountif
お礼  H=R  - 12/9/20(木) 10:01 -
引用なし
パスワード
   佳様

Match関数を使うイメージがまったく分からなかったのですが、
なるほど、並び替えて区切りの位置を検出していくわけですね。
確かに、これは配列に使えるし時間も短縮できそうです。

現在、Dictionaryを使う方法で試行錯誤中ですので、
それが一区切りつけばこちらの方法も試させていただきます。
本当に助かります。
ありがとうございました。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1662 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free