エクセルシートにある1000~2000件程度のデータの下記のような処理を

効率的に行う方法を教えてください。


アンケートの自由回答欄から効率的にデータを取り出したいと思っています。
自由回答欄は、200文字程度です。

その中から例えば、「楽しかった」を含むデータを取り出したいと思っています。
(または、なんらかの印を付けたいと思ってます。)


「楽しかった」だけではなく、「よかった」「悪かった」「良かった」など
抜き出すキーワードは複数(20ワードくらい)あります。


そういうアンケートのエクセルのファイルは、300くらいあって、
ファイル名は様々です。


キーワードを含むセルを区別できれば、なおありがたいです。


エクセルは、2003です。


できれば、エクセルのみで処理できればありがたいです。


その他の方法でも結構ですが、その場合はなるべく費用が掛からないような
方法でお願いします。(ちなみに、アクセスは保有してません)


できるだけ具体的に手順回答いただいた方にポイントを配分させて頂きます。

よろしくお願いします。

回答の条件
  • 1人1回まで
  • 登録:2008/03/27 14:31:16
  • 終了:2008/03/31 12:16:10

ベストアンサー

id:minubow No.2

minubow回答回数216ベストアンサー獲得回数52008/03/27 17:46:14

ポイント100pt

関数で、FIND(探したい文字,対象セル)により、

探したい文字が含まれていればTRUE、含まれていなければFALSEを返します。

探したい文字が多いほど、行は必要になりますが、使える関数かと思います。

また、下記のように数式を組み合わせて使えば、1行で表現できます。

=(ISERROR(FIND("楽しかった",対象セル))=FALSE)+(ISERROR(FIND("よかった",対象セル))=FALSE)

⇒楽しかった、または、よかったが含まれているものが1、2になり、

 含まれていないものが0になります。

いかがでしょうか?

id:samasuya

おお、これもいいですね、なるほどです。

ただ、キーワードが現在20個、そして以後増えるので、

キーワードが別管理できればありがたいですけどねぇ。

関数でやる限りむずかしいですかね。


>=(ISERROR(FIND("楽しかった",対象セル))=FALSE)+(ISERROR(FIND("よかった",対象セル))=FALSE)

を固定でメモ帳かなんかに書いておけばいいのですよねぇ。


うむむ、良いアイディアありがとうございます!

回答欄の列がファイルごとに違うので関数使うのが一番やりやすいのかな?と

思うようになりました。



みなさんのおかげで作業がカナリ効率化できそうです。

ありがとうございます。

2008/03/29 08:58:49

その他の回答(3件)

id:e10kg No.1

e10kg回答回数1ベストアンサー獲得回数02008/03/27 17:10:39

ポイント30pt

こんな感じでいかがでしょうか?

キーワードに一致するセルに色を付けています。

また、result.txt というファイルにブック名、キーワード、検索したセルの値を書いています。

-----

Option Explicit

Sub main()

Dim path As String

Dim fn As Integer

Dim nm As String

Dim i As Integer

Dim keyword As String

Dim keywords() As String

path = "c:\temp\" 'アンケートファイルがあるフォルダー

'探したいキーワード。数を増やしたいときは、カンマで区切ってここに追加。

keyword = "楽しかった,よかった,悪かった"

keywords = Split(keyword, ",")

fn = FreeFile

Open "c:\temp\result.txt" For Output As fn

nm = Dir(path & "\*.xls")

Do While nm <> ""

Workbooks.Open Filename:=path & nm

For i = LBound(keywords) To UBound(keywords)

Call markKeyword(fn, keywords(i))

Next i

ActiveWorkbook.Close True '保存しない場合は、Falseに。

nm = Dir

Loop

Close #fn

End Sub

Sub markKeyword(fn As Integer, keyword As String)

Dim rng As Range

Dim add As String

With ActiveSheet

Set rng = .Cells.Find(What:=keyword, After:=.Range("A1"), LookIn:=xlFormulas, LookAt:= _

xlPart, SearchDirection:=xlNext)

If rng Is Nothing Then Exit Sub

add = rng.Address

Print #fn, ActiveWorkbook.Name & vbTab & keyword & vbTab & rng

rng.Interior.ColorIndex = 6 '色を付けたくなければココをコメントに。

Do

Set rng = .Cells.FindNext(After:=rng)

If add = rng.Address Or rng Is Nothing Then Exit Sub

rng.Interior.ColorIndex = 6 '色を付けたくなければココをコメントに。

Print #fn, ActiveWorkbook.Name & vbTab & keyword & vbTab & rng

Loop

End With

End Sub

id:samasuya

すみません、このコード自体はどこに記述すればいいんでしょうか?

vbsファイルとか作ったらよいんでしょうか??


しかし、このやり方で出来そうですね、ありがとうございます。

2008/03/27 17:29:15
id:minubow No.2

minubow回答回数216ベストアンサー獲得回数52008/03/27 17:46:14ここでベストアンサー

ポイント100pt

関数で、FIND(探したい文字,対象セル)により、

探したい文字が含まれていればTRUE、含まれていなければFALSEを返します。

探したい文字が多いほど、行は必要になりますが、使える関数かと思います。

また、下記のように数式を組み合わせて使えば、1行で表現できます。

=(ISERROR(FIND("楽しかった",対象セル))=FALSE)+(ISERROR(FIND("よかった",対象セル))=FALSE)

⇒楽しかった、または、よかったが含まれているものが1、2になり、

 含まれていないものが0になります。

いかがでしょうか?

id:samasuya

おお、これもいいですね、なるほどです。

ただ、キーワードが現在20個、そして以後増えるので、

キーワードが別管理できればありがたいですけどねぇ。

関数でやる限りむずかしいですかね。


>=(ISERROR(FIND("楽しかった",対象セル))=FALSE)+(ISERROR(FIND("よかった",対象セル))=FALSE)

を固定でメモ帳かなんかに書いておけばいいのですよねぇ。


うむむ、良いアイディアありがとうございます!

回答欄の列がファイルごとに違うので関数使うのが一番やりやすいのかな?と

思うようになりました。



みなさんのおかげで作業がカナリ効率化できそうです。

ありがとうございます。

2008/03/29 08:58:49
id:airplant No.3

airplant回答回数220ベストアンサー獲得回数492008/03/28 01:27:06

ポイント30pt

特定の文字列を含むかどうかのクロス表は、下記で簡単に作れます。


A列に「アンケート」、1行目に「key」という名前(*1)をつけて、間のセルは全部下記の式を埋めます。

(*1)名前は、列を選択して右上のセルが表示されているところへ「アンケート」という文字を埋めればできます。

  A B C D
1 アンケート 楽しい 良い 悪い
2 xxx楽しい =IF(ISERROR(FIND(key,アンケート)),"",1) =IF(ISERROR(FIND(key,アンケート)),"",1) =IF(ISERROR(FIND(key,アンケート)),"",1)
3 xx良いxx =IF(ISERROR(FIND(key,アンケート)),"",1) =IF(ISERROR(FIND(key,アンケート)),"",1) =IF(ISERROR(FIND(key,アンケート)),"",1)
4 xx悪い悪いが楽しい =IF(ISERROR(FIND(key,アンケート)),"",1) =IF(ISERROR(FIND(key,アンケート)),"",1) =IF(ISERROR(FIND(key,アンケート)),"",1)
5 なし =IF(ISERROR(FIND(key,アンケート)),"",1) =IF(ISERROR(FIND(key,アンケート)),"",1) =IF(ISERROR(FIND(key,アンケート)),"",1)

上の表は、下記のような感じになりますので、後は目的のやり方で集計できます(sumやCountif、ピボットなど)。

  A B C D
1 アンケート 楽しい 良い 悪い
2 xxx楽しい 1
3 xx良いxx 1
4 xx悪いが楽しい 1 1
5 なし

問題は、ファイルが300件あるということですね。

全部同じフォーマットなら、CSVに出力するマクロを作って、テキストファイルをバッチなどでつなぎ合わせてから上記のことを行えばできそうです。

1000~2000件のファイルが300個あるのでしょうか?

そうすると、全部を扱うのはExcelの行数を超えていますので、10個くらいに分ける必要がありますね。

名前付けは、マクロの記録後に再生すれば一気に付けられます。


ちなみにキーワードに揺れはないのでしょうか?

[良かった、良い、良く]などの揺れがある場合は正規表現で抽出したほうが良さそうです。

揺れがあって、単に件数が出せればいいのであれば、テキストにしてからgrepツールがお奨めです。

http://www.vector.co.jp/soft/win95/util/se015011.html


Excel上で正規表現で検索したい場合は、下記を参照してみればヒントがあると思います。

http://q.hatena.ne.jp/1187227086

id:samasuya

回答ありがとうございます。

今回は集計したいわけではなくて、まずはまともな回答だけの抽出を行いたいという

感じです。

ただし、最終的に漏れがないか目検も必要なので元データは、全件残したままにしないと

いけないのです。

Grep、egrepだと何回か実行しているうちに重複したデータができそうで・・・。


キーワードの揺れは、キーワードに登録してカバーする予定です。


300個のファイル(○月○日のアンケート、○月○日のアンケートという感じです。)は、

個別に処理しないといけないのでマクロ登録は面倒ですね・・・。

2008/03/28 08:18:57
id:Gay_Yahng No.4

Gay_Yahng回答回数724ベストアンサー獲得回数262008/03/31 11:45:09

ポイント3pt

2 回答についてですが、

例えば

Book1のSheet1のA1セルにキーワードを入れておいて

=(ISERROR(FIND("=[Book1]Sheet1!$A$1",対象セル))=FALSE)

とかにして、

A1セルを

楽しかった、よかった などに書き換えていくとそのキーワードをすぐに変えられて良いのではないかと思います。

id:samasuya

回答ありがとうございます。


ただ、それはちょっと手間ですねぇ(笑)

抜き出しキーワードは決まってるので何とか自動でやりたいですが・・・。

マクロとかになるんでしょうね。

2008/03/31 12:12:46
  • id:airplant
    1回しか回答できないようでしたので、コメントで失礼します。
    下記のWSHで動作させれば、一気に沢山のファイルを処理できます。
    ただし、300個まで行くかどうかは試していません(Windowsの引数制限に引っかかる可能性あります)

    Step1. 下記をxxx.vbsで保存
    Step2. 必要なExcelファイルを複数選択して、xxx.vbsへドラッグ&ドロップする

    ヘッダは1行目、アンケートの回答内容はA列と仮定して作ってあります。違う場合は定義のところを変更してください。

    Option Explicit

    Dim strArg, oXL

    Set oXL = CreateObject("Excel.Application")
    oXL.Visible = True

    For Each strArg In WScript.Arguments
    ' wscript.echo strArg
    call SetNameAndExp(strArg)
    next
    oXL.Quit()
    set oXL = Nothing

    Const iRHeader = 1 'ヘッダ行の位置
    Const iCContents = 1 'アンケート結果文字列の列位置

    Sub SetNameAndExp(sFile)
        
    Dim sKeyword, sKeywords, oB

      With oXL
    set oB = .Workbooks.Open(sFile)
      sKeywords = "楽しい,良い,悪い" '★キーワードはここを変更する
      sKeyword = Split(sKeywords, ",")

      Dim iC
      For iC = iCContents + 1 To iCContents + 1 + UBound(sKeyword)
        .Cells(iRHeader, iC).Value = sKeyword(iC - (iCContents + 1))
      Next

       'キーワードをヘッダ行へ設定
        .ActiveWorkbook.Names.Add "key", "=$" & iRHeader & ":$" & iRHeader
        Dim sColAbs
        sColAbs = .Columns(iCContents).Address(True, True)
        .ActiveWorkbook.Names.Add "cont", "=" & sColAbs & ":" & sColAbs
        
        Dim lLastR, iLastC, rTop
        Set rTop = .Cells(iRHeader, iCContents)
        rTop.CurrentRegion.Select
        lLastR = .Selection.Rows.Count
        iLastC = .Selection.Columns.Count
        
        ' AutoFilterの設定
        .Selection.AutoFilter
        
        ' 有無判別式を全領域へ入れる
        .Range(.Cells(iRHeader + 1, iCContents + 1), _
          .Cells(lLastR, iLastC)).Formula = _
          "=IF(ISERROR(FIND(key,cont)),"""",1)"
        rTop.Select
        .ActiveWorkbook.Save
        .ActiveWorkbook.Close
      End With

    End Sub

    >個別に処理しないといけないのでマクロ登録は面倒ですね・・・。
    excelマクロでやる場合、別ファイルであっても、個人用マクロブックへ登録して操作すれば面倒なことはないです。
  • id:airplant
    すいません、コピーペーストだとエラーが出て動きません。

    エディタなどで、全角スペースを半角スペース2文字に変更をお願いします。

    (見やすくするために半角空白2文字を全角スペースに変えたのが、あだになった)
    後、Windowsの制限は多分無いので、何個でも食わせられます。
    先ずはバックアップを採取しておいてから、適当な個数をドラッグ&ドロップしてみてください。

    前のコメントで書いた個人用マクロブックというのはご存知なのですよね?
  • id:samasuya
    丁寧にありがとうございます。

    個人用マクロブックは、知りませんでしたがこれは自分で調べます。
    vbaで開発したことないですが、コードは、自分でもなんとか調べられますので
    問題ないです。

    今回の質問は、プログラミング、関数利用等のヒントが欲しかっただけなのです。


    ポイント付与の際は、コメント欄の内容も加味させていただきますね、
    ありがとうございます。

  • id:e10kg
    1.のご質問ですがExcelでマクロ用のブックを作り、標準モジュールに
    貼り付けてmain()を実行してください。
    #回答が遅れてすいません。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません