Excelで文字列から頻出Top5を抽出するには？

質問

A1セルからA500セルまでの列に現れた頻出ワード（文字列）のTop5をB1セルからB5セルへ抽出するには、どのような記載をすれば良いでしょうか？頻出ワードの回数はわからず、B1セルからB5セルまで、頻出ワード順に記載する方法を教えてください。よろしくお願い申し上げます。Excelは2019となります。

回答No.9

HohoPapa

#8で画像を上げ損ねたのでポストします。

回答No.8

HohoPapa

VBAを持ち込んでよければ以下のようなコードでいかがでしょうか。

なお、
C列にヒットした数を表示しない場合は
　SQL = SQL & "select Top 5 F1" & vbCrLf
C列にヒットした数を表示する場合は
　SQL = SQL & "select Top 5 F1,c" & vbCrLf
として下さい。

また、第5順位が同数で並ぶ場合は、それぞれを表示しています。
つまり、事例の場合は、出力結果は6行となります。

Option Explicit

Sub sampleX()

　Dim SQL As String
　Dim cn As Object
　Dim rs As Object

　'SQL用環境設定
　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=No;IMEX=1"
　cn.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name

'SQL文組み立て
　SQL = ""
　SQL = SQL & "select Top 5 F1" & vbCrLf　　'注
　SQL = SQL & "From(" & vbCrLf
　SQL = SQL & "SELECT F1 ,count(F1) as c" & vbCrLf
　SQL = SQL & "FROM [Sheet1$A1:A50000]" & vbCrLf
　SQL = SQL & "group by F1" & vbCrLf
　SQL = SQL & ") as t" & vbCrLf
　SQL = SQL & "order by c DESC" & vbCrLf

　'SQL全文を実行
　rs.Open SQL, cn

　'結果セットを出力
　With ThisWorkbook.Sheets("Sheet1")
　　.Cells(1, 2).CopyFromRecordset rs
　End With

　'後処理
　rs.Close　　　　　　'結果セットクローズ
　cn.Close　　　　　　'データベースをクローズ
　Set rs = Nothing　　'オブジェクトを破棄
　Set cn = Nothing

End Sub

回答No.7

kagakusuki

別シートに作業列を設けて処理する方法です。
　今仮に

>A1セルからA500セルまで

という元データーが入力されているセルが存在しているシートがSheet1であるものとし、Sheet2のA列を作業列をとして使用するものとします。

　まず、Sheet2のA1セルに次の関数を入力して下さい。

=IF(Sheet1!$A1="","",IF(COUNTIF(Sheet1!$A$1:A1,Sheet1!$A1)=1,COUNTA(Sheet1!$A:$A)*COUNTIF(Sheet1!$A:$A,Sheet1!$A1)-ROW(),""))

　次に、Sheet2のA1セルをコピーしてSheet2のA2～A500の範囲に貼り付けて下さい。
　次に、Sheet1のB1セルに次の関数を入力して下さい。

=IF(COUNT(Sheet2!$A:$A)<ROWS(B$1:B1),"",INDEX($A:$A,MATCH(LARGE(Sheet2!$A:$A,ROWS(B$1:B1)),Sheet2!$A:$A,0)))

　次に、Sheet1のB1セルをコピーしてSheet1のB2～B5の範囲に貼り付けて下さい。

　以上です。

回答No.6

imogasi

作業列を使うので、お気に召さないかも。
取り急ぎやったので、不十分点はあるかも。
ーー
例データ　A1:A２０　第1行は見出し必須
語句
bb
cc
aa
bb
aa
bb
cc
aa
aa
cc
bb
dd
ff
gg
dd
ee
dd
gg
hh
＝＝
データーーフィルター詳細設定で重複する「レコードは無視する」で
Ｅ列に（第1行は見出し必須）
語句
bb
cc
aa
dd
ff
gg
ee
hh
重複を除いた語句の１セットが出る。
ーー
F2に=COUNTIF($A$1:$A$20,E2)*100+ROW(A1)　この式を下方向に複写
結果
頻度
401
302
403
304
105
206
107
108
ーー
H2に　=LARGE($F$1:$F$9,ROW(A1))
結果　H列
頻出順
403
401
304
302
206
108
107
頻出順になる。
ーー
I列　=INDEX($E$2:$E$20,MATCH(H2,F$2:F$20,0))　下方向に式複写
結果　H,I列
頻出順 語句
403 aa
401 bb
304 dd
302 cc
206 gg
108 hh
107 ee
語句そのものを明示。
このうち好きな上位数の語句を使う。４０３の先頭桁の４が出現数です。
ーー
エクセルでなくデータベース言語ＳＱＬなどなら、一度に出そう。

回答No.5

NuboChan

回答No.4に関して追加です。

>Excelは2019となります。

であれば、以下に差し替えてください。

'重複なしのデータをB列に抽出_2

　Dim lRow As Long

　lRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

　Worksheets("Sheet1").Range("B1:B" & lRow).Value = Worksheets("Sheet1").Range("A1:A" & lRow).Value
　Worksheets("Sheet1").Range("B1:B" & lRow).RemoveDuplicates Columns:=1, Header:=xlNo

回答No.4

NuboChan

初心者のVBAですが、参考にどうぞ。

TOP５ではなく、頻出ワード（文字列）毎の抽出と件数をソートした結果です。
Office３６５以降の関数を一部利用しているので旧EXCELでは変更が必要です。

Option Explicit

Sub Sample()
Dim cnt As Long
Dim rg As Range
Dim varData As Variant
Dim Ans As Variant
Dim i As Long, lc As Long

'重複なしのデータをB列に抽出
With Worksheets("Sheet1")
varData = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
End With

Ans = WorksheetFunction.Unique(varData)

Worksheets("Sheet1").Range("b1").Resize(UBound(Ans), 1).Value = Ans

lc = Cells(Rows.Count, 2).End(xlUp).Row

'指定範囲の件数カウント
For Each rg In Range("A1:A500")
For i = 1 To lc
cnt = WorksheetFunction.CountIf(Range("A1:A500"), Cells(i, "B"))
Cells(i, "C") = cnt
Next
Next

'ソート
Range("B1:C" & lc).Sort _
Key1:=Range("C1"), Order1:=xlDescending, Header:=xlNo
End Sub

回答No.3

f272

そういうことを一つの関数でやろうとすると、他の人にはよくわからない関数になってしまいます。作業列を作って順番に考えてください。
B1=1
B2=IF(B2=B1,B1+1,1)としてB500までコピーする
C1＝LARGE(B1:B500,1)
C2＝LARGE(B1:B500,2)
C3＝LARGE(B1:B500,3)
C4＝LARGE(B1:B500,4)
C5＝LARGE(B1:B500,5)
として頻出ワードの回数を抽出する
D1=INDEX(A1:A500,MATCH(C1,B1:B500,0),1)
D2=INDEX(A1:A500,MATCH(C2,B1:B500,0),1)
D3=INDEX(A1:A500,MATCH(C3,B1:B500,0),1)
D4=INDEX(A1:A500,MATCH(C4,B1:B500,0),1)
D5=INDEX(A1:A500,MATCH(C5,B1:B500,0),1)
で頻出ワードを抽出する
ただし頻出ワードの回数が同じものがあれば、もう少し考える必要がある。

回答No.2

SI299792

B1: =INDEX(A:A,MATCH(LARGE(C:C,ROW()),C:C,0))
B5迄コピペ。
C1: =IF(COUNTIF(A$1:A1,A1)=1,COUNTIF(A:A,A1)-ROW()/1000)
C500迄コピペ。（ワークエリアです。目障りなら非表示にして下さい。）

回答No.1

msMike

添付図参照(Excel 2019)
￣￣￣￣￣￣￣￣￣￣￣￣￣↓↓↓↓↓↓↓↓↓↓↓↓↓