締切済み

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

2023/04/20 20:24

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

retweet
お礼率94% (455/483)

Excel（エクセル）
回答数9
ありがとう数0

みんなの回答 （9）
専門家の回答

みんなの回答

HohoPapa
ベストアンサー率65% (454/690)

2023/04/23 14:35 回答No.9

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

HohoPapa
ベストアンサー率65% (454/690)

2023/04/23 14:32 回答No.8

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

kagakusuki
ベストアンサー率51% (2610/5101)

2023/04/21 16:26 回答No.7

　別シートに作業列を設けて処理する方法です。　今仮に >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の範囲に貼り付けて下さい。　以上です。

imogasi
ベストアンサー率27% (4737/17068)

2023/04/21 15:01 回答No.6

作業列を使うので、お気に召さないかも。取り急ぎやったので、不十分点はあるかも。ーー例データ　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 語句そのものを明示。このうち好きな上位数の語句を使う。４０３の先頭桁の４が出現数です。ーーエクセルでなくデータベース言語ＳＱＬなどなら、一度に出そう。

NuboChan
ベストアンサー率47% (745/1582)

2023/04/21 08:47 回答No.5

回答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

NuboChan
ベストアンサー率47% (745/1582)

2023/04/21 07:50 回答No.4

初心者の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

f272
ベストアンサー率46% (8003/17107)

2023/04/20 22:06 回答No.3

そういうことを一つの関数でやろうとすると、他の人にはよくわからない関数になってしまいます。作業列を作って順番に考えてください。 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) で頻出ワードを抽出するただし頻出ワードの回数が同じものがあれば、もう少し考える必要がある。