Excel VBAを用いたヘルプデスクFAQと解決策の効率的な抽出方法に対する実務上の答えは「FAQ候補はticket tableから解決済み・公開可・review済みをfilterし、ticket IDを保ったまま別sheetへcopyします。「FAQ:」文字列だけで抽出せず、個人情報とcredentialを除外して人間が公開承認します。」です。ここではfree textのkeyword抜出しではなく、ticket ID、question、solution、status、reviewed列を持つtableをsourceにする前提を明示し、初回確認、sample実行、本番判定、元へ戻す条件をそれぞれ独立させます。 確認ポイント：公開候補は全条件を満たす行だけとし、秘密情報候補は必ず人手reviewへ分離します。
FAQ抽出に使う列schemaと公開条件を確定する
FAQ抽出では、問い合わせ番号、公開可否、レビュー状態、質問、回答など必須列の定義を固定し、個人情報を除いた少数行で試します。公開条件をすべて満たす行だけを抽出し、メールアドレスや認証情報らしい値を検出した行は保留にします。出力先を毎回初期化せず、重複キーを確認してから追記します。
- source table列とunique ticket IDを確認する
- 解決済み、再現性、公開可の判定列を定義する
- 氏名、mail、host、secretを除外するruleを決める
- 出力sheetの版、reviewer、更新日を管理する
0件になったときはfilter列を順に点検する
- FAQ:文字列だけで抽出する
- 質問と解決策の行対応を失う
- 個人情報をcopyする
- obsolete solutionを公開する
- source ticketを直接加工する
抽出・重複・機密候補を一つの処理で分ける
tblTicketsの列名とindexを確認する
Sub InspectFaqTable()
Dim t As ListObject, c As ListColumn
Set t = ThisWorkbook.Worksheets("Tickets").ListObjects("tblTickets")
For Each c In t.ListColumns: Debug.Print c.Index, c.Name: Next c
End Sub列位置ではなく列名を確認します。
Resolved・Yes・Reviewedを同時に判定する
With ThisWorkbook.Worksheets("Tickets").ListObjects("tblTickets").Range
.AutoFilter Field:=5, Criteria1:="Resolved"
.AutoFilter Field:=6, Criteria1:="Yes"
.AutoFilter Field:=7, Criteria1:="Reviewed"
End WithField番号はschema確認後に名前から求める実装へ改善します。
対象行をFAQ_Draftへ新規出力する
Dim visible As Range
On Error Resume Next
Set visible = t.DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not visible Is Nothing Then visible.Copy Destination:=ThisWorkbook.Worksheets("FAQ_Draft").Range("A2")0件をerrorと区別し、sourceを変更しません。
秘密情報候補をManualReviewへ送る
Dim c As Range
For Each c In ThisWorkbook.Worksheets("FAQ_Draft").UsedRange
If InStr(1,CStr(c.Value2),"password",vbTextCompare)>0 Then c.Offset(0,8).Value2="ManualReview"
Next c自動mask完了ではなくmanual review候補です。
source件数と抽出件数を照合する
Debug.Print t.ListRows.Count
Debug.Print WorksheetFunction.Subtotal(103,t.ListColumns("TicketID").DataBodyRange)全件とvisible件数を記録します。
ReadyとManualReviewから公開判断を分ける
keywordを含むだけではFAQ品質や解決済みを示しません。同じquestionの表記揺れ、version違い、obsolete solutionを人間が統合します。ticket IDを保持すれば原sourceへ戻れます。solutionにcommandが含まれる場合は現行version、安全性、権限、rollbackを技術reviewします。
元tableを変更せず出力sheetだけを再生成する
support ticketには個人情報、credential、internal URLが含まれます。raw dataを外部serviceへ送らず、抽出sheetもaccess制御します。source ticketを色変更や削除で加工せずcopyを使います。公開前にsecurity/privacy/owner reviewを必須にし、不合格候補は公開せずsource IDだけ記録します。誤抽出はFAQ draftを削除してbackupから戻します。
review状態と抽出時刻を次回へ引き継ぐ
source filtered件数、draft件数、unique ticket ID、blank question/solution、manual review件数を照合します。既知の公開可・不可sampleをtestし、二回実行でduplicateが増えないことを確認します。公開版はreviewerとsource IDを記録します。
FAQ抽出を成功と判定する条件
tblTicketsの列名をListColumnsで参照し、Status=Resolved、Publishable=Yes、ReviewStatus=Reviewedを行ごとに評価します。TicketID、Question、SolutionをFAQ_Draftへ値として出し、password等の機密候補はManualReviewへ分けます。
review済みFAQを抽出する完成コード
Option Explicit
Private Function ContainsSensitiveText(ByVal value As String) As Boolean
Dim word As Variant
For Each word In Array("password", "token", "secret", "メール", "電話")
If InStr(1, value, CStr(word), vbTextCompare) > 0 Then ContainsSensitiveText = True: Exit Function
Next word
End Function
Public Sub ExtractReviewedFaq()
Dim t As ListObject, lr As ListRow, dst As Worksheet, ids As Object
Dim outRow As Long, id As String, question As String, solution As String
Dim status As String, publishable As String, reviewState As String, resultState As String
Dim published As Long, manual As Long, duplicates As Long
On Error GoTo Fail
Set t = ThisWorkbook.Worksheets("Tickets").ListObjects("tblTickets")
Set dst = ThisWorkbook.Worksheets("FAQ_Draft")
Set ids = CreateObject("Scripting.Dictionary")
ids.CompareMode = vbTextCompare
dst.Cells.ClearContents
dst.Range("A1:E1").Value = Array("TicketID", "Question", "Solution", "ReviewState", "ExtractedAt")
outRow = 2
For Each lr In t.ListRows
status = CStr(lr.Range.Cells(1, t.ListColumns("Status").Index).Value2)
publishable = CStr(lr.Range.Cells(1, t.ListColumns("Publishable").Index).Value2)
reviewState = CStr(lr.Range.Cells(1, t.ListColumns("ReviewStatus").Index).Value2)
If StrComp(status, "Resolved", vbTextCompare) = 0 And _
StrComp(publishable, "Yes", vbTextCompare) = 0 And _
StrComp(reviewState, "Reviewed", vbTextCompare) = 0 Then
id = Trim$(CStr(lr.Range.Cells(1, t.ListColumns("TicketID").Index).Value2))
question = Trim$(CStr(lr.Range.Cells(1, t.ListColumns("Question").Index).Value2))
solution = Trim$(CStr(lr.Range.Cells(1, t.ListColumns("Solution").Index).Value2))
resultState = "Ready"
If Len(id) = 0 Or Len(question) = 0 Or Len(solution) = 0 Then resultState = "ManualReview:MissingRequired"
If ids.Exists(id) Then resultState = "ManualReview:DuplicateID": duplicates = duplicates + 1
If ContainsSensitiveText(question & vbLf & solution) Then resultState = "ManualReview:SensitiveCandidate"
If Len(id) > 0 And Not ids.Exists(id) Then ids.Add id, True
dst.Cells(outRow, 1).Value2 = id
dst.Cells(outRow, 2).Value2 = question
dst.Cells(outRow, 3).Value2 = solution
dst.Cells(outRow, 4).Value2 = resultState
dst.Cells(outRow, 5).Value2 = Now
If resultState = "Ready" Then published = published + 1 Else manual = manual + 1
outRow = outRow + 1
End If
Next lr
Debug.Print "source=" & t.ListRows.Count, "ready=" & published, "manual=" & manual, "duplicates=" & duplicates
Exit Sub
Fail:
MsgBox "ExtractReviewedFaq failed: " & Err.Number & " " & Err.Description, vbExclamation
End Subこのcodeは画面表示だけを見るための例ではありません。通常系では「三条件を満たし必須列が非blank、機密候補なしの行だけFAQ_Draftへ一度ずつ出る」を確認し、0件と実行errorを別の結果として保存します。
公開可能・要確認・重複IDを分類する
- 期待どおり：三条件を満たし必須列が非blank、機密候補なしの行だけFAQ_Draftへ一度ずつ出る
- 0件・非適用：公開候補0件はheaderと0件summaryを残し、SpecialCellsのerrorへしない
- 実行error：列不存在、duplicate TicketID、保護sheet、privacy rule該当はSchemaError/ManualReviewへ分ける
AutoFilterのField番号を固定せず列名からindexを得ます。discontiguousなvisible rangeの一括copyに依存せずListRowsを走査し、source ticketを変更しません。keyword一致だけで安全と断定せず人手reviewを残します。
0件・欠落列・秘密語を含むfixture
' 同じTicketIDを2行、公開条件0件、passwordを含むSolutionをsampleにし、
' Published、ManualReview、Duplicateの各件数が期待表と一致するか確認する。0件、duplicate ID、blank Question/Solution、機密keyword、列順変更をtestします。source行数、eligible、published、manual review、duplicate、二回実行差分を保存します。
Excel VBAを用いたヘルプデスクFAQと解決策の効率的な抽出方法の証跡には、実行対象と取得時刻に加え、通常・0件・errorのどれへ分類したかを残します。通常系は「三条件を満たし必須列が非blank、機密候補なしの行だけFAQ_Draftへ一度ずつ出る」、停止系は「列不存在、duplicate TicketID、保護sheet、privacy rule該当はSchemaError/ManualReviewへ分ける」を判断文としてそのまま作業票へ写し、担当者ごとの言い換えで意味が変わらないようにします。
FAQ抽出を定期化するときは、column番号ではなくheader名でQuestion、Solution、ReviewStatus、Sensitivityを解決し、承認済みrowだけを新規sheetへ出します。duplicate IDや機密flagを検出したrowは削除せずmanual-review一覧へ残し、公開候補件数と分離します。前回成果物との差分をFAQ ID単位で示し、人の承認前にCMSへ送信しない構成にします。
コメント