Excel VBA質問箱 IV

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

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


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

【20864】データの頻出順にリストを作りたいのですが あいりあり 04/12/23(木) 12:47 質問[未読]
【20865】Re:データの頻出順にリストを作りたいので... IROC 04/12/23(木) 12:55 回答[未読]
【20867】Re:データの頻出順にリストを作りたいので... あいりあり 04/12/23(木) 13:33 質問[未読]
【20871】Re:データの頻出順にリストを作りたいので... ちゃっぴ 04/12/23(木) 13:51 回答[未読]
【20877】Re:データの頻出順にリストを作りたいので... IROC 04/12/23(木) 14:17 回答[未読]
【20892】Re:データの頻出順にリストを作りたいので... あいりあり 04/12/23(木) 18:15 お礼[未読]
【20893】Re:データの頻出順にリストを作りたいので... あいりあり 04/12/23(木) 18:22 お礼[未読]
【20887】Re:データの頻出順にリストを作りたいので... Hirofumi 04/12/23(木) 15:54 回答[未読]
【20891】Re:データの頻出順にリストを作りたいので... あいりあり 04/12/23(木) 18:14 お礼[未読]
【20894】Re:データの頻出順にリストを作りたいので... Hirofumi 04/12/23(木) 18:42 回答[未読]
【20890】Re:データの頻出順にリストを作りたいので... [名前なし] 04/12/23(木) 17:31 回答[未読]
【20895】Re:データの頻出順にリストを作りたいので... [名前なし] 04/12/23(木) 19:44 発言[未読]
【20896】Re:データの頻出順にリストを作りたいので... [名前なし] 04/12/23(木) 19:48 発言[未読]

【20864】データの頻出順にリストを作りたいのです...
質問  あいりあり  - 04/12/23(木) 12:47 -

引用なし
パスワード
   こんにちは
あるデータの頻出順に、別シートに降順のリストを作りたいのですが
いまいち上手くいきません。

ご指導よろしくお願いします。

【20865】Re:データの頻出順にリストを作りたいの...
回答  IROC  - 04/12/23(木) 12:55 -

引用なし
パスワード
   データを別シートにコピーして並び替えし、
フィルタオプションの「重複を無視する」で抽出する。
というようなことですか?

【20867】Re:データの頻出順にリストを作りたいの...
質問  あいりあり  - 04/12/23(木) 13:33 -

引用なし
パスワード
   ▼IROC さん:
>データを別シートにコピーして並び替えし、
>フィルタオプションの「重複を無視する」で抽出する。
>というようなことですか?

返信ありがとうございます。
フィルタオプションのような機能のは気づかなかったです。
VBAに固執しすぎていたのかもしれないです。

申し訳ないのですが、
頻出順に並び替えという部分を詳しく教えていただけないでしょうか。
因みに、私のデータは以下のようなものです。

 A B C

1 a f e
2 b d e
3 a d s
4 a d e
・・・・・・・

これを各列ごとに並び替えしたいのですが
(データは列ごとに独立しているものとしたいです)

ご指導よろしくお願いします。

【20871】Re:データの頻出順にリストを作りたいの...
回答  ちゃっぴ  - 04/12/23(木) 13:51 -

引用なし
パスワード
   Scripting.Dictionaryを使用すれば、簡単に出来るかと・・・

DictionaryのKeyに対象の文字列をセット、値には見つかるたびに加算する
という風にしてやればよいかと・・・

Dim dicData As New Scripting.Dictionary

strTarget = "見つかった文字列"
dicData.Item(strTarget) = dicData.Item(strTarget) + 1

あとは、Keys Method, Items Methodを使って配列に出力
さらにCellに出力して、件数をKeyにSortしてやればよいでしょう。

【20877】Re:データの頻出順にリストを作りたいの...
回答  IROC  - 04/12/23(木) 14:17 -

引用なし
パスワード
   >頻出順に並び替えという部分を詳しく教えていただけないでしょうか。

A列分だけ別シートにコピーし、
そのデータからフィルタオプションで重複を無視したリストを
隣の列にでも抽出し、更にその隣の列で、 CountIfでカウントして、
カウントされた数値で並べ替えしては如何でしょうか?

【20887】Re:データの頻出順にリストを作りたいの...
回答  Hirofumi  - 04/12/23(木) 15:54 -

引用なし
パスワード
   こんな物を必要としているのかな?

Option Explicit

Public Sub Sample()

  '処理列数
  Const clngColCount As Long = 3
  
  Dim i As Long
  Dim dicIndex As Object
  Dim rngResult As Range
  Dim vntRisult As Variant
  
  'Dictionaryオブジェクトを取得
  Set dicIndex = CreateObject("Scripting.Dictionary")

  '出力先のセル位置を指定
  Set rngResult = Worksheets("Sheet2").Cells(1, "A")
  
  Application.ScreenUpdating = False
  
  'データの有る先頭セル位置を指定
  With Worksheets("Sheet1").Cells(1, "A")
    '処理列数分繰り返し
    For i = 0 To clngColCount - 1
      '処理結果を取得
      vntRisult = Frequency(.Offset(, i), dicIndex)
      '結果を出力
      rngResult.Offset(, i).Resize(UBound(vntRisult, 1)).Value = vntRisult
    Next i
  End With
  
  '出力先の参照を破棄
  Set rngResult = Nothing
  'dicIndexを破棄(Dictionaryオブジェクト)
  Set dicIndex = Nothing
  
  Application.ScreenUpdating = True
  
  Beep
  MsgBox "処理が完了しました"
  
End Sub

Private Function Frequency(rngList As Range, _
              dicIndex As Object) As Variant

  Dim i As Long
  Dim vntData As Variant
  Dim vntItems As Variant
  Dim lngKeys() As Long
  Dim lngIndex() As Long
  Dim lngRow As Long
  
  '指定のセルを基準とする
  With rngList
    'データ数を取得
    lngRow = .Offset(65536 - .Row).End(xlUp).Row - .Row + 1
    'データを配列に取得
    vntData = .Resize(lngRow).Value
  End With
  
  
  With dicIndex
    'Dictionaryオブジェクトを初期化
    .RemoveAll
    'データの先頭から最終まで繰り返し
    For i = 1 To UBound(vntData, 1)
      'dicIndexにKeyデータが存在するなら
      If .Exists(vntData(i, 1)) Then
        '項目に、カウントを加算
        .Item(vntData(i, 1)) = .Item(vntData(i, 1)) + 1
      Else
        'dicIndexにKeyデータと、初期値を登録
        .Add vntData(i, 1), 1
      End If
    Next i
    'データ用配列を破棄
    Erase vntData
    'dicIndexからKeyデータを結果用配列に取り出す
    vntItems = .Keys
    'Index用配列、頻度用配列を確保
    ReDim lngKeys(0 To UBound(vntItems, 1))
    ReDim lngIndex(0 To UBound(vntItems, 1))
    '頻度用配列に頻度を記入
    For i = 0 To UBound(vntItems, 1)
      lngKeys(i) = .Item(vntItems(i))
      lngIndex(i) = i
    Next i
  End With
  
  '配列をソート
  ShellSortNum lngKeys, lngIndex()
  
  'データを出力用に並べ替え
  ReDim vntData(1 To UBound(lngIndex, 1) + 1, 1 To 1)
  For i = 0 To UBound(lngIndex, 1)
    vntData(i + 1, 1) = vntItems(lngIndex(i))
  Next i
  
  '結果を戻り値として返す
  Frequency = vntData
  
End Function

Private Sub ShellSortNum(lngKeys() As Long, _
            lngIndex() As Long)

  Dim i As Long
  Dim j As Long
  Dim lngGap As Long
  Dim lngTmpIdx As Long
  Dim lngTop As Long
  Dim lngEnd As Long
  
  lngTop = LBound(lngKeys, 1)
  lngEnd = UBound(lngKeys, 1)
  
  lngGap = 1
  Do While lngGap < (lngEnd - lngTop + 1) \ 3
    lngGap = 3 * lngGap + 1
  Loop
  
  Do Until lngGap <= 0
    For i = lngGap + lngTop To lngEnd
      lngTmpIdx = lngIndex(i)
      For j = i To lngGap + lngTop Step -lngGap
        If lngKeys(lngIndex(j - lngGap)) _
            >= lngKeys(lngTmpIdx) Then
          Exit For
        End If
        lngIndex(j) = lngIndex(j - lngGap)
      Next j
      lngIndex(j) = lngTmpIdx
    Next i
    lngGap = lngGap \ 3
  Loop

End Sub

【20890】Re:データの頻出順にリストを作りたいの...
回答  [名前なし]  - 04/12/23(木) 17:31 -

引用なし
パスワード
   ▼あいりあり さん:
>頻出順に並び替えという部分を詳しく教えていただけないでしょうか。
>因みに、私のデータは以下のようなものです。
>
> A B C
>
>1 a f e
>2 b d e
>3 a d s
>4 a d e
>・・・・・・・
>
>これを各列ごとに並び替えしたいのですが
>(データは列ごとに独立しているものとしたいです)

これならピボットテーブルで出来るような気がしますが。

【20891】Re:データの頻出順にリストを作りたいの...
お礼  あいりあり  - 04/12/23(木) 18:14 -

引用なし
パスワード
   ▼Hirofumi さん:
>こんな物を必要としているのかな?

返信ありがとうございました。
わざわざプログラムまで付けていただいて、すみません。
上手くできました。プログラミングの内容は
理解してない部分もありますが・・・

できれば、空白セルは無視したいのですが、
プログラムを変更しようにも、プログラムを理解してないもので
手も足も出ません。Hirofumiさん、教えていただけないでしょうか?

【20892】Re:データの頻出順にリストを作りたいの...
お礼  あいりあり  - 04/12/23(木) 18:15 -

引用なし
パスワード
   ▼IROC さん:
>A列分だけ別シートにコピーし、
>そのデータからフィルタオプションで重複を無視したリストを
>隣の列にでも抽出し、更にその隣の列で、 CountIfでカウントして、
>カウントされた数値で並べ替えしては如何でしょうか?

返信ありがとうございます。
早速試してみます。

【20893】Re:データの頻出順にリストを作りたいの...
お礼  あいりあり  - 04/12/23(木) 18:22 -

引用なし
パスワード
   ▼ちゃっぴ さん:
>Scripting.Dictionaryを使用すれば、簡単に出来るかと・・・

すみませんが、Scripting.Dictionaryを全く知らないです。
せっかく、返答していただいて申し訳ありません。

調べるときに、分らないときもあるかと思いますが
そのときは、またよろしくお願いします。

【20894】Re:データの頻出順にリストを作りたいの...
回答  Hirofumi  - 04/12/23(木) 18:42 -

引用なし
パスワード
   >できれば、空白セルは無視したいのですが、
>プログラムを変更しようにも、プログラムを理解してないもので
>手も足も出ません。Hirofumiさん、教えていただけないでしょうか?

「Function Frequency」の中に、以下の★印の様にコードを追加して下さい

    'データの先頭から最終まで繰り返し
    For i = 1 To UBound(vntData, 1)
      '空白を無視             '★この行追加
      If vntData(i, 1) <> "" Then     '★この行追加
        'dicIndexにKeyデータが存在するなら
        If .Exists(vntData(i, 1)) Then
          '項目に、カウントを加算
          .Item(vntData(i, 1)) = .Item(vntData(i, 1)) + 1
        Else
          'dicIndexにKeyデータと、初期値を登録
          .Add vntData(i, 1), 1
        End If
      End If               '★この行追加
    Next i
    'データ用配列を破棄

【20895】Re:データの頻出順にリストを作りたいの...
発言  [名前なし]  - 04/12/23(木) 19:44 -

引用なし
パスワード
   補足でピボットテーブルの作成手順
1)一行目に項目名を入力し、表を以下のようにします。
   A
1 項目名
2 a
3 b
4 a
5 a

2)A列を選択します。
3)メニューで データ→ピボットテーブルとピボットグラフレポートを選択
4)ウィザードの1/3,2/3はそのまま「次へ」を選択。
3/3で既存のワークシートを選択し、別シートのA1を選択して完了。
5)仮に4)の既存のワークシートをSheet2にした場合、Sheet2にピボットテーブルの
テンプレートみたいなのが出てくるので、項目名を行のフィールドとデータアイテムの
所にドラッグアンドドロップします。すると、以下のような表が出来ます。

データの個数 / タイトル    
タイトル 合計
a     3
b     1
(空白)    
総計   4

6)ピボットテーブルのどこかを右クリックし、オプションを選択。
名前を「一列目」に変更します。
総計が不要なら、「列の総計」のチェックをはずします。
7)また右クリックし、今度はフィールドの設定を選択。右側の詳細ボタンを押し、
自動並べ替えオプションを降順、使用するフィールドを「データの個数 / 項目名」
に変更します。
8)項目名の右にある▼をクリックすると、フィールドのリストが出てくるので、
(空白)のところのチェックをはずします。
9)最後に、元データのあるシートのChangeイベントを以下のように作成します。
(ピボットテーブルを更新してるだけです。)
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 1 Then Sheets("Sheet2").PivotTables("一列目").PivotCache.Refresh
End Sub
これで、元データのシートに項目を追加すると、自動でピボットテーブルが
更新されます。

これを、他の列も同様の手順で設定するだけです。
ChangeイベントのIf Target.Column = 1 Then は、「一列目だったら」という
意味なので、二列目の場合は、2に変更したものを追加します。
PivotTables("一列目")のカッコ内は、6)で付けた名前を指定します。

複数列同時に設定出来そうな気がするのですが、私にはやり方がよくわかりません。

【20896】Re:データの頻出順にリストを作りたいの...
発言  [名前なし]  - 04/12/23(木) 19:48 -

引用なし
パスワード
   >データの個数 / タイトル    
>タイトル 合計
>a     3
>b     1
>(空白)    
>総計   4

最初「項目名」じゃなくて「タイトル」でやってたんで・・・
以下のような表になります。

データの個数 / 項目名   
項目名  合計
a     3
b     1
(空白)    
総計   4

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