Excelマクロによる単語リストの結合

Excelマクロによる単語リストの結合についてご教授願えませんでしょうか。
2つの単語リストＡとＢとがあります。
（例）
＜単語リストA＞
go 3
work 4
（以下省略）

＜単語リストＢ＞
go 4
hear 5
（以下省略）

上の二つの単語リストＡ、Ｂを以下のように結合したいのです。
リストA リストB
go 3 4
hear 0 5
work 4 0

No.8 ベストアンサー 回答者： Wendy02 回答日時： 2010/12/04 11:57

>ということは、Sheetが何十枚あっても、つまり単語リストがいくつあってもSheet1に表を作成してくれるマクロの仕様になっていると考えてよいでしょうか。

使えるようになったので、良かったです。私の勘違いで、余計な手間をおかけしてしまい、すみませんでした。

Sheet1の物理的な限界までは可能です。今回、別のブックからでも集計元シートにも移せるはずですから、Excel2003では、列は256シートまでありますから、255回まで可能になります。

ここのカテゴリで実験してくれた方が、同じブックでは、50枚～60枚で挙動がおかしくなってくるという報告もありますが、今回は、量的に少ないので、もっと入るはずです。どのぐらいになるかは実際のところは分かりませんが、100枚以上は避けたほうがよいと思います。

また、別々のシートに入れなくても、範囲を指定して、集計元シートに移す方法もありますが、それは、ご要望しだいです。

なお、今回のマクロを応用したものが、ここのカテゴリの閲覧をするシステムと同じものが使われています。新しいものだけを書き加え、すでにある質問は、回答件数を書き換えるようにして、もし、ベストアンサーが付けば、「締切」にしています。

この回答へのお礼

Wendy02様
　何度も情報をいただき本当にありがとうございました。おかげさまでとても助かりました。このマクロを今後ともありがたく使用させていただきます。

お礼日時：2010/12/04 12:34

No.7 回答者： Wendy02 回答日時： 2010/12/03 00:30

理由が分かりました。

私の説明文では、可能なようなことを書いていましたが、実際は、集計元シートが、まったく空の状態であることは想定していませんでした。それが可能なように修正しました。失礼しました。

他の修正点では、他のブックからでも、マクロの置いてあるシートにコピーされるようにしたこと。新しい単語が出た時に、空白のセルにも、前の表に　0　か入るようにしました。

なお、IEから、Excel等のVBEditor に直接貼りつけしても問題は発生しません。不要のコードは全部弾かれます。
また、ツールというのは、コンコーダンサーのことを意味しています。

私の使っているコンコーダンサー
http://www.takke.jp/pss/concordancer.html

'//
Sub PickupWordsR()
　'一意の単語を拾い出すマクロ
　Dim osh As Worksheet '集計元シート
　Dim sh2 As Worksheet
　Dim rng1 As Range
　Dim rng2 As Range
　Dim Ret As Variant
　Dim col As Long
　Dim rw As Long
　Set osh = ThisWorkbook.Worksheets("Sheet1") '集計元シートの登録
　With osh
　　If Application.CountA(.Columns.Cells) = 0 Then
　　　.Range("A1").Value = "英単語" 'ダミー
　　End If
　　Set rng1 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
　　col = .Cells(2, Columns.Count).End(xlToLeft).Column
　　rw = rng1.Cells(1).Row
　End With
　With ActiveSheet
　　If .Name = osh.Name Then
　　　MsgBox "集計シートにはデータはコピー出来ません。", 48
　　　Exit Sub
　　Else
　　　Set rng2 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
　　End If
　End With
　Application.ScreenUpdating = False
　'リストがない場合
　If Application.CountA(rng1.Cells) < 2 Then
　　rng2.Resize(, 2).Copy osh.Cells(2, 1)
　　osh.Cells(1, 2).Value = rng2.Parent.Name
　Else
　　'リストの追加
　　For Each c In rng2
　　　Ret = Application.Match(c.Value, rng1, 0)
　　　If IsError(Ret) Then
　　　　osh.Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = c.Value
　　　End If
　　Next
　　With osh
　　　'元のリストの再設定
　　　Set rng1 = .Range(rng1.Cells(1), .Cells(Rows.Count, 1).End(xlUp))
　　　'数字の代入
　　　For Each c In rng1
　　　　Ret = Application.Match(Trim(c.Value), rng2, 0)
　　　　If IsNumeric(Ret) Then
　　　　　c.Offset(, col).Value = rng2.Cells(Ret, 2).Value
　　　　Else
　　　　　c.Offset(, col).Value = 0
　　　　End If
　　　Next
　　　.Cells(1, col + 1).Value = rng2.Parent.Name
　　End With
　End If
　On Error Resume Next
　With rng1.CurrentRegion.Offset(, 1)
　　.Resize(, .Columns.Count - 1).SpecialCells(xlCellTypeBlanks).Value = 0
　End With
　On Error GoTo 0
　Beep
　Application.ScreenUpdating = True
End Sub

この回答への補足

　完璧にSheet1に表を作成してくれています。凄いマクロだと思いました。
　いろいろと数字を入れてマクロを走らせてみました。Sheet5まで試してみても完璧にSheet1に一覧表を作成してくれています。ということは、Sheetが何十枚あっても、つまり単語リストがいくつあってもSheet1に表を作成してくれるマクロの仕様になっていると考えてよいでしょうか。

補足日時：2010/12/04 00:00

No.6 回答者： Wendy02 回答日時： 2010/12/02 10:20

1.の問題は、特に図である通りで、A2,B2が反映されないということはありえませんが、

Set rng1 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
とあるように、.Cells(2, 1)　2行目1列目はA2の意味ですから、何か書き換えたりしないとそのような現象は起こらないはずです。

2.当然、0を入れるように、
c.Offset(, col).Value = 0
書かれていますから、そのようなことはないはずです。文字や数字が認識されていない可能性もあります。

元のデータは、IEなどから取得したデータですか？一般にツールなどから取得したテキストデータなら、そのようなことは起こらないはずです。

なお、現在、12/1より「教えて!Goo」の改変のために、サイトのソースを読みながら、こちらの閲覧・ログシステムの変更をしていますので、多少、対応に遅れています。

この回答への補足

　相変わらず、症状は変わらないのです。
　Sheet2を先にアクティブにすればsheet2のA2,B2が反映されませんし、Sheet3を先にアクティブにすればやはり、Sheet3のA2,B2が反映されなくなります。

　下記の文面ですが、マクロの式は、直接IEから式をコピーし、Excelの標準モジュールに貼り付けています。また、一度テキストファイルにコピーしてからExcelの標準モジュールにも貼り付けてみましたが、症状は変わりません。この方法で良いのでしょうか？下記の文面の意味で「ツール」という意味を取り違えておりますでしょうか。ちなみに単語リストのほうもテキストデータをExcelに貼り付けています。
お手数をおかけします。
　「元のデータは、IEなどから取得したデータですか？一般にツールなどから取得したテキストデータなら、そのようなことは起こらないはずです。」

補足日時：2010/12/02 21:02

No.5 回答者： Wendy02 回答日時： 2010/12/01 00:16

>Wendy02様の方でマクロ関係で何か特別な設定をされておられますでしょうか？

ふつう、動かないのは、セキュリティの関係です。マクロ--セキュリティ--「高」にしてあると、VBEditor 側の「青い三角定規」のボタンがオンになっています。これは、押しても戻らないので、セキュリティ--「中」か「低」にします。そして、Excelを再起動させます。そうすると、マクロは動くようになります。安全性に関しては、単に、マクロが入っているか入っていないかの、検知だけで、本質的には、ウィルスの防御や防除をしてくれるわけではありません。

もし、VBAコードがどういう動きをしているか知りたいのでしたら、VBEditor のコードの中の適当な部分で、メニューのデバッグ--ブレークポイントの設定をすると良いと思います。

　　 '数字の代入
　　　For Each c In rng1
●　　　 Ret = Application.Match(Trim(c.Value), rng2, 0)
↑
ブレークポイント
ここまでくると止まりますので、F8 でステップモードにします。それでマクロが動いているか確認します。その行は実行されませんから、F8を押すと、始めて実行とれます。その時に上記の場合は、Ret(Return の意味）に、マウス・カーソルを乗せると、エラーか、数字のどちらかが出ています。エラーは、データの範囲に検索値がないという意味です。数字は、既存のデータがいるという意味です。

●の部分は、そこの行で、F9 か、マウスでクリックすると、ブレークポイントは解除されます。

この回答への補足

　ありがとうございました。私の操作方法の理解が足りなかったことが原因でしたが、今度はうまくいきました。下記２点についてご確認願えますでしょうか。
１．Sheet2のA2（単語）、B2（頻度）の内容がSheet1に反映されないのです。
２．片方のリストにデータがあって、もう片方のリストにデータがない場合、Sheet1にはデータがないセルに0と反映してほしいのですが、空欄のままのものがありました。
　とくに、上記の１番の場合については、マクロでの対応は可能でしょうか？

補足日時：2010/12/01 17:02

No.4 回答者： Wendy02 回答日時： 2010/11/29 12:12

>Sheet1にはList_Aの最後の単語とその頻度しか表示されません。

また、List_Bをアクティブにしてマクロを実行すれば、Sheet1には、List_Bの最後の単語とその頻度しか表示されません。

List_Aの内容を図で示します。別にタイトルの単語・頻度自体はあってもなくてもよいです。2行目からデータがある、コンコーダンスのままです。こういうものは、向き・不向きもありますから、あえてマクロでなくても、数式でも良いかと思います。

また、「結合」という言葉をお使いになっていますが、Excelには、「結合」という機能があります。この場合は、こういうものとは違うはずです。

なお、Set osh = ThisWorkbook.Worksheets("Sheet1")　と集計元シートとすれば、別のシートに対しても、アクティブにしているシートから持ってくることは可能です。

また、図のリスト(List_A)の単語自体は、コンコーダンスから作られたものに間違いありませんが、数字はらしく見せた架空のものです。

この回答への補足

　何度も情報をいただきありがとうございます。御礼申し上げます。
　こちらの環境では、Sheet1はデータを無記入のままにし、Sheet2とSheet3にはそれぞれデータ入力をし、Sheet2をアクティブにし、マクロを実行しておりますが、思うような結果が得られません。Excel2003とExcel2007でも試しましたがうまくいきませんでした。web上のWendy02様のマクロを標準モジュールにコピー＆ペーストで貼り付けて使用させていただきました。設定については初期値のままで、特に変更しておりませんが、Wendy02様の方でマクロ関係で何か特別な設定をされておられますでしょうか？

補足日時：2010/11/30 22:43

No.3 回答者： Wendy02 回答日時： 2010/11/28 18:46

サンプルの図のアップロードに失敗しましたので、再アップします。

この回答への補足

ご丁寧な説明をありがとうございます。
List_Aをアクティブにして、マクロを実行すれば、Sheet1にはList_Aの最後の単語とその頻度しか表示されません。また、List_Bをアクティブにしてマクロを実行すれば、Sheet1には、List_Bの最後の単語とその頻度しか表示されません。２つのリストがSheet1で結合されないのです。何か設定でもおかしいのでしょうか？

補足日時：2010/11/28 23:40

No.2 回答者： Wendy02 回答日時： 2010/11/28 18:34

>現在、単語リストのファイルが2つ有ります。

シート内で処理して欲しかったのです。一回キリなら、マクロは不要だと思います。ファイルが2つというのですと、もう少しコードを加えなくてはなりませんが、扱いがややこしく難しくなります。

>それぞれに名前がついています。list_A、list_Bです。

シート名はファイル名ではありません。なるべく、シート名など入れないで済むようにして、集計するシート名以外は、ActiveSheetとして、その新規データのシートを開いて、それを写してもらうことを考えたのです。

>　Set osh = Worksheets("Sheet1") '元の集計シートの登録　
>で実際のシート名を(・・・）の中にシート名を入れることと、」
>　これは、マクロの文中の"Sheet1"をlist_Aとかに書き換えるということなのでしょうか？

その部分は、シート名はシート名ですから、xls ファイルには、シート名はあるはずです。その部分は集計の元になるシートです。

CSVに書きこむにしても、最終的には、xlsファイルになるのではないでしょうか。

Set osh = Worksheets("シート名")
ファイル名を入れる所は、現在は設けていません。ファイル名を入れたりすると、ややこしくなるはずです。ただ、二つのブックを使うなら、以下のように書き換えます。
Set osh = ThisWorkbook.Worksheets("シート名")

>A1のセルにgo、B1のセルにその頻度5という具合です。

A1(一行目）は、フィールド部分ですから、本来は、A2からデータを入れるというのは、いろいろな応用を考えて、タイトル(フィールド名）を入れていただいたほうがよいのです。なるべく、そういう方法は避けたほうがよいと思います。並べ替えたりする時に、タイトルがあったほうが、楽になるのです。手動でする場合、フィルタオプションにも、タイトル部分は必要です。

--------
設定：

VBAのコードは、基本的には、その集計シートのあるブックの標準モジュールに貼り付けるものとします。実行は、Ctrl + F8で、マクロ名「PickupWords」を探して、クリックしていただければよいはずです。良く使うようでしたら、ツールバーなどにボタンを付けてあげるようにします。
--------
仕様と使い方：

Sheet1 が集計シート　'シート名を登録
Sheet2 が、[リストA]のデータ (括弧内はシート名とします）
Sheet3 が、[リストB]のデータ

Sheet2　を開いて、マクロを実行します。
(この[リストA]を、集計シートのベースにしてしまっても可能です）

Sheet1 にデータがない場合は、そのまま、集計シートに写されているということになります。
ただし、1行目は、フィールドに使われますから、そこは、集計の対象ではありません。
Sheet3 の場合は、すでに集計シートにリストがありますから、集計シートの単語リストから単語を探して、その数を書き込みます。

行　
1　単語　リストA　リストB　　←タイトル(リストA, リストBは、シート名）
2　go　　　3　　　　　4
3　hear　　0　　　　　5
4　work　　4　　　　　0

シート名は、コピーされて、写されますから、
-------
初期段階では、集計シートは、
A1　単語　

だけとなっています。文字のリストの並びには関係ありません。

思ったものとは違うのかもしれませんが、私個人、単語の集計の経験がありますから、ある程度、標準的な所は押さえているはずだと思います。
-------
数式、手作業での処理は？

もし、マクロが難しいようでしたら、関数で処理したほうが良いかもしれません。
二つの単語リストを、1つの列にいれて、フィルタオプションで、[重複するレコードは無視する]で、一意の文字列を抽出します。(要タイトル行）

後は、
=SUMIF(List_A!A:A,Sheet1!A1,List_A!B:B)
このように、オートフィルタで数式をコピーしても良いです。

No.1 回答者： Wendy02 回答日時： 2010/11/28 12:17

そのリストはコンコーダンスで取られたものだと思います。

「結合」という機能ではうまく行かないはずです。それを利用すると二度手間になります。

以下のマクロでは、Sheet1 に集計するようにし、他のシートから検索するようにします。

注意点は、
　Set osh = Worksheets("Sheet1") '元の集計シートの登録　
で実際のシート名を(・・・）の中にシート名を入れることと、
集計するデータシートをアクティブシートにしてください。

まったく単語リストがないときには、アクティブシートから集計シートにコピーされます。
また、集計シートにない単語も、コピーされて集計の対象とします。
データは、必ず、2行目からデータがあるとします。

===============

Sub PickupWords()
　'一意の単語を拾い出すマクロ
　Dim osh As Worksheet '元の集計シート
　Dim sh2 As Worksheet
　Dim rng1 As Range
　Dim rng2 As Range
　Dim Ret As Variant
　Dim col As Long
　Dim rw As Long
　Set osh = Worksheets("Sheet1") '元の集計シートの登録
　With osh
　　Set rng1 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
　　col = .Cells(2, Columns.Count).End(xlToLeft).Column
　　rw = rng1.Cells(1).Row
　　Set rng1 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
　End With
　
　With ActiveSheet
　　If .Name = osh.Name Then
　　　MsgBox "同じシート名にはデータはコピー出来ません。", 48
　　　Exit Sub
　　Else
　　　Set rng2 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
　　End If
　End With
　Application.ScreenUpdating = False
　'リストがない場合
　If Application.CountA(rng1.Cells) < 2 Then
　　rng2.Offset(1).Resize(, 2).Copy osh.Cells(2, 1)
　　osh.Cells(1, 2).Value = rng2.Parent.Name
　Else
　　'リストの追加
　　For Each c In rng2
　　　Ret = Application.Match(c.Value, rng1, 0)
　　　If IsError(Ret) Then
　　　　osh.Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = c.Value
　　　End If
　　Next
　　With osh
　　'元のリストの再設定
　　Set rng1 = .Range(rng1.Cells(1), .Cells(Rows.Count, 1).End(xlUp))
　　　'数字の代入
　　　For Each c In rng1
　　　　Ret = Application.Match(Trim(c.Value), rng2, 0)
　　　　If IsNumeric(Ret) Then
　　　　　c.Offset(, col).Value = rng2.Cells(Ret, 2).Value
　　　　Else
　　　　　c.Offset(, col).Value = 0
　　　　End If
　　　Next
　　　.Cells(1, col + 1).Value = rng2.Parent.Name
　　End With
　End If
　Beep
　Application.ScreenUpdating = True
End Sub

この回答への補足

　回答に感謝します。Excel 2003を使用しています。マクロは初心者ですが、登録の仕方や実行の仕方は間違いなくできたと思います。私の理解不足でどうもうまくいきません。
　現在、単語リストのファイルが2つ有ります。それぞれに名前がついています。list_A、list_Bです。list_Aは以下のようになっています。
A1のセルにgo、B1のセルにその頻度5という具合です。以下、A2にはwork、B2にはその頻度4という具合です。
list_A
go3
work4
（以下省略）
list_Bもlist_Aのような構成となっています。
下記の意味が理解できていないせいかうまくいきません。
「注意点は、
　Set osh = Worksheets("Sheet1") '元の集計シートの登録　
で実際のシート名を(・・・）の中にシート名を入れることと、」
　これは、マクロの文中の"Sheet1"をlist_Aとかに書き換えるということなのでしょうか？

補足日時：2010/11/28 16:01