エクセルで単語データを作成したい

質問

単語学習を効率よく進めたくて次のような単語リストを作りたいと考えています。

A列には番号、 B列には英文のデータがあります。
C列には英文の単語をデータとして生成したいのです。
一度出たデータは省いて新規のものだけ。
この例では、I、 you、mistake(s)は２度目に除外されます。
大文字や、複数形、ed, ing形はややこしくなりますので、手作業で省いてもよいかと思います。

D列には元の英文（A列）の番号

このようなことは可能でしょうか。
自分なりに作っていると、代名詞や前置詞などが多数出てきて困ります。
要は目新しい単語を英文から抽出したいということです。
良い方法があれば教えてください。よろしくお願いします。

A 列　　　B 列
1 　　　　I won't talk to you unless you admit your mistake.
2 　　　　I wonder why you are fighting over such small mistakes.

C列　　　D 列
I 　　　　 1
won't 　　　　 1
talk 　　　　 1
to 　　　　　 1
you 　　　　　 1
unless 　　　　 1
admit 　　　　 1
your 　　　　　 1
mistake 　　　　 1
wonder 　　　　 2
why 　　　　　 2
are 　　　　　　 2
fighting 　　　　 2
over 　　　　　　 2
such 　　　　　　 2
a 　　　　　　　 2
small 　　　　　　 2

回答No.2

Wendy02 · ベストアンサー

こんにちは。

>このようなことは可能でしょうか。

いつも同じ質問が出るようですが。あえて、Excelでやりたいというなら、それもかまいませんが、VBAの処理になります。それをするだけの意味があるかは分かりません。Dictionary オブジェクトを使って、ダブりを排除していくのが一般的です。

この取り出しの一般的なツール名は、「コンコーダンサー(Concordancer)」というものです。主に大学のサイトで、フリーツールとして出ています。本来、コンコーダンサーは聖書の単語抽出に使われてきたもので、昔は、膨大な文献を手書きで拾い上げてコンコーダンサーを作ってきたという歴史を持っています。現在は、コンピュータがありますから、テキストファイルを処理するものとして、データベースと組み合わせて使われるものです。

日本では、学習用として、非常に簡単なものとして以下の場所で出ていますが、いくつか探して試してみるとよいです。

例 PSS Concordancer 　http://www.takke.jp/

他にも、単語抽出ツールには、同作者のPssEditor のツールの中にあります。多少、使いこなしには煩雑さを感じますが、学習目的には良いかと思います。

Pss Concordancerから
-------------------------------------------
単語 出現回数
you　　　3
i　　　　2
t　　　　1 　'←省略形は分離されてしまう
to　　　 1
are　　　1
why　　　1
won　　　1 　'←省略形は分離されてしまう
over　　 1
such　　 1
talk　　 1
your　　 1
admit　　1
small　　1
unless　 1
wonder　 1
mistake　1 　'単数
fighting 1
mistakes 1 　'複数

から取り出したものです。ただ、一旦、英文をWordに通してやると良いです。そうしたら、won't は、will not と切り替えられるはずです。

お礼

詳しいご回答、紹介ありがとうございました。
P-Study は以前に少し使ったことがあり、よくできていると感心しました。人にも勧めていました。
毎日、洋書を読んでいるのですが、知らない単語をリストアップして随分たまりました。
時々、見直すと重複している語が結構あって、まだ覚えきれてなかったんだと痛感します。
悔しくてこれからはエクセルで管理してしっかりと覚えていこうと考えました。文といっしょの方が覚えやすいので、自分なりの単語ノートを作ろうと。
しかし、ご指摘のように、自分が思うようにうまくできないですね。知らない単語だけを抽出するのは大変です。
PSS Concordancerまだ使い勝手がわかりませんが、勉強してみたいと思います。
ご親切に教えていただき感謝します。ありがとうございました。
　​

回答No.9

MackyNo1

>１番最初に試したのですが、「置換でピリオドやクエスチョンマークなどを置換操作で削除して」のところでつまづいてしまいました。

No1の回答を見ればわかると思うのですが・・・

ちなみに、この操作を行わずに、文字の割り付け操作だけで1列にすべての単語が並んで表示されると思いますが、うまくいかないということでしょうか？

＞ご指摘いただいたように、山のような既知の単語の中から自分が望む未習の単語だけを抜き取るのは大変だと、２日間試してみてわかりました。

既知の単語のデータのリストがあるなら、エクセルのフィルタオプションの機能で未習の単語を簡単に抽出することができます。

上記の操作のうち、もしわからない部分があるなら、実際にどの部分がうまくいかないのか具体的に提示してください（いずれも1，2分でできる簡単な操作のように思えるのですが・・・）。

お礼

度々のご丁寧な回答ありがとうございました。
ちょっと、私の最初の注文がまずかったのでみなさんにご迷惑をおかけしました。
みなさんのおかげで、悪戦苦闘の末、ほぼ思い通りの処理ができました。
英文の中から、指定の単語を抽出し、それを前述の単語リストでチェック、既出のものはその文番号を書き出す。
みなさんからの熱心な回答のおかげで、この３日間私も解決にくらいつくことができました。
これで自分なりの英文、単語のストックが可能になると思います。
一層、英語力の強化に努めたいと思います。
ありがとうございました。

回答No.8

MackyNo1

質問者からの応答がないので、質問を放置していたのかと思っていましたが、レスがあったようですね。

ところで私のNo1の回答は、エクセルの一般機能だけで誰でもできる簡便な方法だと思うのですが、試されたでしょうか？

No1では少し無駄な操作をしていましたので、新たに回答を補足すると、例示でデータなら、B列の列幅を1文字分プラスアルファの幅に狭くして、B1セルとB2セル（英文が入っている範囲）を選択して「編集」「フィル」「文字の割り付け」でOKして、置換でピリオドやクエスチョンマークなどを置換操作で削除して、最後にフィルタオプションの設定で重複データを削除する操作を行えば完成です（１、２分で作業できますよ）。

お礼

ご回答ありがとうございました。お礼が遅くなり申し訳ありません。
１番最初に試したのですが、「置換でピリオドやクエスチョンマークなどを置換操作で削除して」のところでつまづいてしまいました。

いろんな方から回答いただき感謝しています。
ご指摘いただいたように、山のような既知の単語の中から自分が望む未習の単語だけを抜き取るのは大変だと、２日間試してみてわかりました。

それで文を止めて単語のみに変更しようかと考えています。
お手間をかけ、お世話になりありがとうございました。

回答No.7

Wendy02

こんばんは。
#2の回答者です。

私の学習情報を加えておきます。
本当は、シャープの『翻訳これ一本』が使えると良いのですが、もう市販されていません。

これは、自動的に単語帳を作ってくれます。なぜ、やめてしまったかは分かりませんが、このソフトは、私がシャープに要望書を書いて出したら、リクエストも組み入れてくれ、連絡も頂きました。それだけ愛着のあるソフトです。

>毎日、洋書を読んでいるのですが、知らない単語をリストアップして随分たまりました。

やはりExcelなどを使うと便利ですね。訳付けで、P-DICを使えると良いと思っていますが、P-Studyで出来るので開発していません。P-DICは、ActiveX などで取り出せますから、Excelに組み込めるはずです。P-Study は、私は気にならないのですが、いろんな人の話を総合すると、未開発の部分が残っているようで、使い勝手が今ひとつのようです。この作者は10代後半か20代初めに作ったようなので、万民向きとは言えなかったようです。

私の読むほうは、単語リストは作るのはやめてしまいました。単語の記録をとるのは翻訳をする場合ですが、訳語の統一の意味もあります。対象はコンピュータ系か、医学系かどちらかです。最初から覚えようとは思っていません。いえ、覚えなくてはならないものもありますが、それは、たとえば、内分泌系とかの単語リストを使います。ひとつ覚えてもしょうがないです。

私が読むNewsは、JapanTimes ですから、文化欄や読者投稿以外の日本で書いている部分は、だいたい15,000語レベルだそうですから、一生懸命に覚える必要はないと思っています。ひとつの記事で、3語以上分からないことは、まずないとは思っています。辞書で調べたらそれでおしまいです。ただ、上記の『翻訳これ一本』のおまかせ訳振り（教科書ガイド式の単語訳が行間につくスタイル）で、政治や経済の訳をあてると、日本語の訳に定訳があるということを、知らないなって思うことが多いです。（意味を取るのとは別です。）

さて、それでは、私は単語を覚えないかというと、そんなことはありません。ALC の「SVL」を中心にしてリストを作り、それを、P-Study にP-DICと英辞郎を組み合わせて使っています。英辞郎は私の持っているのは古いせいなのか、例文があまり良いものが少ないです。音声は、SAPI5.1 を使っていますから、かなりきれいです。それで、上級ではSVL≒英検単語（ほぼ同じ）　ですが、日本の英語教育界で作った単語リスト(jacetvoc)もあるようですね。こちらは歯が立ちませんでした。

P-Study は6年ぐらい続いていましたが、今年になって、体調不良で、使ったり使わなかったりしています。4,000語くらいは覚えたつもりです。(ロスが2割ぐらい出た結果です）これを続けていたせいか、Japan Times の国内版辺りは、あまり困らないです。

他にも、P-DIC　やEBWINには、検索ログを残せますから、それでリストを作ると便利かもしれません。

私は、いろんな工夫をしましたが、現在に落ち着いています。紙の単語カードも作りましたが、600枚を越えたら、管理が面倒になってやめました。コクヨの単語帳ソフトがいいなって思うのですが、私は乗り物には乗ったりしないので購入しませんでした。
電子文具　[メモリボ]
http://www.kokuyo-st.co.jp/stationery/e-bungu/memoribo/

でも、私の恩師は、大学生の時にソシュールの『言語学概論』を原文で読んだというし、八木誠一先生という人も、学生時代、病気で入院したので、新約聖書をギリシャ語で読んだというし、新渡戸稲造さんなんかは、大学入学レベルで、英語は、もう大学院生以上のレベルだったと言います。英作文なんか半端じゃないです。何が違うのだろうかなって思ってしまいます。
現代の英語の達人というと、私はすぐに大前研一さんが頭に浮かびますが、もう、この人は別格だろうなって思っています。大脳生理学の大島清医師という人が言うように、脳の言語野の発達の問題なのかもしれません。ある通訳の人は、30代前に目が出なければダメだといっています。でも、語学バカほど他人からみて哀しいものはありません。

最後に、私も、1単語ずつ広い上げていた時期がありますが、結局のところ、それでは上手くいかないというのか、単語には単語の覚え方のコツがあるのだと思います。まず、自分の実力と学習する単語のレベルを合わせることだと思います。

お礼

上に別に質問を立てますと書きましたが、悪戦苦闘の末、ほぼ思い通りの処理ができました。
英文の中から、指定の単語を抽出し、それを前述の単語リストでチェック、既出のものはその文番号を書き出す。
みなさんからの熱心な回答のおかげで、この３日間私も解決にくらいつくことができました。
これで自分なりの英文、単語のストックが可能になると思います。
一層、英語力の強化に努めたいと思います。
ありがとうございました。

補足

再度、ごていねいに回答いただきましてありがとうございます。
私はやっと英書が読めるようになり嬉しくて毎日読み漁っています。
知らない単語だけでなく気に入った英文もいっしょに蓄積できたらという欲求が強まりました。
現在の単語力は５０００～７０００くらいでしょうか。これが倍増できたらもっと読解が楽になると思った次第です。
今ほど英語学習者に恵まれた時代はないとおもいます。本でもネットでもいくらでも学習できます。
明治時代の英語の達人は今の人間の数倍、英語力があったと言われます。先月、松江のラフカディオ・ハーンの館を見学してきました。
ヘレン・ケラーも指の上だけで６ケ国語をマスターしています。
人間の力は感動・情熱・集中で発揮されるのですね。

エクセルは単語で管理に変えます。
VBAのことでおしえてほしいので、別に質問を立てます。
教えていただいた学習教材など時間をみてチェックさせていただきます。ありがとうございました。

回答No.6

imogasi

表題は「英文から、そこに含まれる単語データを作成」ぐらいにしたら。現状表現では「作製したら良いじゃん」という答えになる。
ーー
したいといっても、関数でやっても複雑で、ＶＢＡの経験はないのでしょう。
であれば出来ませんね。
ーー
ただし
エクセルには、データー区切り位置ー（区切り文字）スペースという機能がある。
これで単語を分解して別セルに配置できる。
２００３までは1文内が２５６単語以内であるべきだが、大丈夫だろう。
最後のピリオドは関数でも省ける。
ーー
例データ
質問の２行
it's fine today
ーーーー
これらをＳｈｅｅｔ２のＡ列に１列化する。
ここから関数で面倒なのでＶＢＡにして（関数でも出来ると予想。（略）
ーー
結果
I
won't
talk
to
you
unless
you
admit
your
mistake.
I
wonder
why
you
are
fighting
over
such
small
mistakes
it's
fine
today
ーーー
これをソートして
admit
are
fighting
fine
I
I
it's
mistake.
mistakes
over
small
such
talk
to
today
unless
why
wonder
won't
you
you
you
your
＝＝admit
are
fighting
fine
I
I
it's
mistake.
mistakes
over
small
such
talk
to
today
unless
why
wonder
won't
you
you
you
your
ここで同じものの件数などカウントできる。
＝＝
しかしここまでテストをやってみて、結果を良く観察して、頭を冷やして考えること。
大文字小文字
単数と複数のｓ、３単のｓ
文末ピリオド
ing形
など私は良く考えてないが、望むものと比べて、難しい点は無いのか。
＝＝＝
でもここまでくれば自分で考えて入力するより早く正確だろう。
その辺良く考えて問題点も含めて再質問したら。
ーーー
ＶＢＡの例　参考
Sub test01()
Dim sh1, sh2
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
d = sh1.Range("A65536").End(xlUp).Row
k = 1
MsgBox d
For i = 1 To d
r = sh1.Range("IV" & i).End(xlToLeft).Column
MsgBox r
For j = 1 To r
sh2.Cells(k, "A") = sh1.Cells(i, j)
k = k + 1
Next j
Next i
End Sub
上記結果までなら全体をＶＢＡでできる。ソートも、重複も省くこともＶＢＡなら出来る。
この部分は関数でも出来ると予想。

お礼

回答ありがとうございました。
ご指摘いただいたとおり、最初の注文がまずかったようでみなさんにご迷惑をおかけしました。

悪戦苦闘の末、ほぼ思い通りの処理ができました。
英文の中から、指定の単語を抽出し、それを前述の単語リストでチェック、既出のものはその文番号を書き出す。
みなさんからの熱心な回答のおかげで、この３日間私も解決にくらいつくことができました。
これで自分なりの英文、単語のストックが可能になると思います。
一層、英語力の強化に努めたいと思います。
ありがとうございました。

回答No.5

mitarashi

#2の方の言及されているdictionary（連想配列）使用版です。
一旦配列に受けてから処理し、シートに転写していますので、少々分かり難いかもしれません。
Sheet1のA1から、A列の各セルに英文が入っているとします。途中に空白行がある事は想定しておりません。
Sub test()
Dim srcArray As Variant, buf As Variant, dstArray() As Variant
Dim i As Long, j As Long, myKeysCount As Long
Dim myDic As Object, myKey As String, keys As Variant
Dim srcSheet As Worksheet, dstSheet As Worksheet

Set srcSheet = ActiveWorkbook.Sheets("Sheet1") '文の入っているシート
Set dstSheet = ActiveWorkbook.Sheets("Sheet2") '出力先のシート
'セルの値を配列に入れる
With srcSheet
srcArray = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).Value
End With
Set myDic = CreateObject("Scripting.Dictionary")
'連想配列に入れる／既存アイテムの場合加算
For i = 1 To UBound(srcArray, 1)
buf = Split(srcArray(i, 1), " ")
For j = 0 To UBound(buf)
myKey = buf(j)
'文末の.を除去
If Right(myKey, 1) = "." Then myKey = Left(myKey, Len(myKey) - 1)
If Not myDic.exists(myKey) Then
myDic.Add myKey, 1
Else
myDic.Item(myKey) = myDic.Item(myKey) + 1
End If
Next j
Next i
keys = myDic.keys
myKeysCount = UBound(keys)
'出力データを一旦入れる配列のサイズを決めて、連想配列のデータを代入
ReDim dstArray(1 To myKeysCount + 1, 1 To 2)
For i = 0 To myKeysCount
dstArray(i + 1, 1) = keys(i)
dstArray(i + 1, 2) = myDic.Item(keys(i))
Next i
'出力先シートのA1セルから配列を丸ごと貼り付け
With dstSheet
.Cells.Clear
.Range("A1").Resize(myKeysCount + 1, 2) = dstArray
End With
Set myDic = Nothing
End Sub

お礼

ちょっと、私の最初の注文がまずかったのでみなさんにご迷惑をおかけしました。
みなさんのおかげで、悪戦苦闘の末、ほぼ思い通りの処理ができました。
英文の中から、指定の単語を抽出し、それを前述の単語リストでチェック、既出のものはその文番号を書き出す。
みなさんからの熱心な回答のおかげで、この３日間私も解決にくらいつくことができました。
これで自分なりの英文、単語のストックが可能になると思います。
一層、英語力の強化に努めたいと思います。
ユニークな提言をいただきましたので、また時間を見つけてトライしたいと思います。
ありがとうございました。

回答No.4

hige_082

＃２さんの意見に賛成ですが
今日はヒマなんで

Sub test()
Dim mStr As Variant
Dim tStr As String
Dim x, y, z
For x = 1 To Cells(Rows.Count, 1).End(xlUp).Row
mStr = Replace(Cells(x, 2).Value, ".", "")
mStr = Split(mStr)
For y = 0 To UBound(mStr)
z = 0
Do
z = z + 1
If Cells(z, 3).Value = mStr(y) Then Exit Do
If Cells(z, 3).Value = "" Then
Cells(z, 3).Value = mStr(y)
Cells(z, 4).Value = x
Exit Do
End If
Loop
Next y
Next x
End Sub

お礼

ご回答ありがとうございました。お礼が遅くなり申し訳ありません。
私の注文通りぴたっとうまくいきました。しかし、ご指摘いただいたように、山のような既知の単語の中から自分が望む未習の単語だけを抜き取るのは大変だと、２日間試してみてわかりました。

それで文を止めて単語だけをデータに入れて使わしてもらおうと思います。
それと、知り合いの中学生で英語熱心な子がいますので、このコードを教えてやろうと思います。
ありがとうございました。

補足

書き忘れましたが、単語のみですので重複はvlookup関数でチェックできます。

回答No.3

noname#99913

次のマクロを試してください。

Option Explicit

Private Sub GetWord()

Dim br As Integer
Dim cr As Integer
Dim stc As String
Dim wrd As String
Dim spc1 As Integer
Dim spc2 As Integer

Range("c:d").Select
Selection.ClearContents

br = 1
Do While Cells(br, 2) <> ""

stc = Cells(br, 2).Value
stc = Replace(stc, ".", " ")

spc1 = 1
Do

spc2 = InStr(spc1, stc, " ")
If spc2 = 0 Then Exit Do
wrd = Mid(stc, spc1, spc2 - spc1)
spc1 = spc2 + 1

cr = 1
Do
Select Case Cells(cr, 3).Value
Case Is = wrd
Exit Do
Case ""
Cells(cr, 3).Value = wrd
Cells(cr, 4).Value = br
Exit Do
End Select
cr = cr + 1
Loop

Loop

br = br + 1
Loop

End Sub

お礼

ちょっと、私の最初の注文がまずかったのでみなさんにご迷惑をおかけしました。
みなさんのおかげで、悪戦苦闘の末、ほぼ思い通りの処理ができました。
英文の中から、指定の単語を抽出し、それを前述の単語リストでチェック、既出のものはその文番号を書き出す。
みなさんからの熱心な回答のおかげで、この３日間私も解決にくらいつくことができました。
これで自分なりの英文、単語のストックが可能になると思います。
一層、英語力の強化に努めたいと思います。
ありがとうございました。

回答No.1

MackyNo1

以下のようなエクセルの機能（裏ワザ的になりますが）を駆使すれば、重複のない英単語のリストを作成することができます。

まず、B列(B1セルからB10セル)に入力されている英文をD1セルに「=PHONETIC(B1:B10)」として1つのセルにまとめます。

そのまま右クリックから「コピー」、もう一度右クリック「形式を選択して貼り付け」で「値」を選択し、その後、Ctrl+Hで置換ダイアログを出して、検索する文字列に「.」置換後の文字列に「 」(半角スペース）と入力して「置換」して、ピリオドを半角スペースにした文字列に変換します。

次にD列の列幅を1文字分よりやや大きい幅にしてから、このD1セルを選択して「データ」「フィル」「文字の割り付け」（エクセル2007ならホームタブの「編集」の中の「フィル」ボタンの右をクリックして「両端ぞろえ」）を選択します。

このようにして生成したすべての単語のデータを選択し「データ」「フィルタ」「フィルタオプションの設定で「重複するデータは無視する」にチェックを入れOKすると、ご希望の重複のない単語データが作成されていますので、これをそのまま右クリックコピーし、C列に貼り付けてみてください。

最後にフィルタモードを解除し、作業用のD列を削除すれば完成です。