"Diary" インターネットさんへの恩返し

いつもソースコードコピペばかりなので,みなさまへ少しばかりの恩返しを

Excelでテキスト分類(検索キーワードのカテゴライズ)



スポンサーリンク

検索キーワード毎にカテゴライズを付けてセッション数とかの集計がしたくて、機械学習テキストマイニングという流れで調査したけど、、、自分の知識じゃできないことがわかりまして。。。。

そんな時はGoogle先生に、Excelでぱぱっとやる方法を探すと素晴らしいエントリが。

検索キーワードを自動分類して“ざっくり”分析するExcelの秘蔵テクニック 第8回 | 1万円で真似できる“戦略的サイト運用術” - 小さく作って速く改善 | Web担当者Forum

このExcelでのテキスト分類の方法論を頂きまして自分なりにアレンジして作りました。

考え方

上記エントリでは、マッチングカテゴリやマッチングキーワード優先順位ができなかったので重み付けの機能をつけました。
f:id:azumami:20150320184216p:plain

スクリプトこちらのプログラムを張り替えてください)

Function myClassification(S1 As String, R1 As Range) As String

  Dim i As Integer, j As Integer
  Dim V1 As Variant
  Dim kouho
  
  'マッチングキーワードがある範囲
  V1 = R1.Value    
  
  '候補となるカテゴリで定義しているキーワードのマッチングの回数を記録する変数
  kouho_cnt = 0

  '現在マッチングを試行しているカテゴリでマッチングキーワードが合った時に、
 マッチング回数を記録する変数
  this_cnt = 0

  'マッチングキーワードのカウント(this_cnt)が、kouho_cntより多かった場合
(適していると判断)した場合、候補のカテゴリ名を入れる変数
  kouho_keyword = ""
  
   'マッチング領域の2行目から、最後の行まで(上から下)  1行名は主に定義行のため
  For i = LBound(V1, 1) + 1 To UBound(V1, 1)
    this_cnt = 0
    
   '2列目から右へ
    For j = LBound(V1, 2) + 1 To UBound(V1, 2)
      
      If V1(i, j) <> "" And InStr(S1, V1(i, j)) > 0 Then
        this_cnt = this_cnt + V1(1, j) '重み計算
      End If
    Next j
  
    If this_cnt > kouho_cnt Then
        kouho_cnt = this_cnt
        kouho_keyword = V1(i, 1) '& "|" & kouho_cnt 重みを含めた計算結果を表示する場合
    End If
      
  Next i
  
  myClassification = kouho_keyword

ruleシート

f:id:azumami:20150320184617p:plain


これで、Google Analyticsとかでキーワードをダウンロードして、キーワードの意味のカテゴリをオリジナルで定義し集計するといったことができると思います。ご参考まで。