こまってます。

質問

こまってます。
エクセル使って、英文の問題ダブりを見つけたいのですが、どうしても使えこなせません。たとえば、
1), I like ( ) tennis.（問題文）
1, playing 2, eating 3, running 4, waving　（選択肢）
みたいな問題があったとします。
こんな形式の問題が１０００個ほどあります。ぜんぶフォーマットそろってないワードでした涙。
それで、この中から１００個ぐらいテスト用に取り出したいです。
そのときの条件が、１００問選ぶときに、それぞれの問題の「答え」と「それいがい（不正解の選択し）」が重複（だぶり）しない。（できれば、問題文の中にも選択肢に出てくる語句が出てこないようにしたい。）理由は、問題Aから問題Bの答えがわかってしまうと困るから。
countifなどもやったのですが、なにせエクセルぺーぺーなもので、英文の変化系（playing, playなど）も振り分けの条件に対応させることができません。
お時間あれば助けてください。
質問わからなければ、がんばって説明するので、おねがいします。データベース化してとっておきたいのでエクセルをつかいたいです。
osはmac 10.6.4でエクセルは２００８です。VBAはよくわかりませんが必要であれば勉強します。。すいません。
でも、なるべく簡単な方法があればお願いします。。ぐぐるでは「excel だぶり」とか「エクセル　重複　関数」とかやりました。

回答No.1

aokii · ベストアンサー

vlookup関数を使ってみてはいかがでしょう。

お礼

ありがとうございます。vlookupもこころみたのですが、なかなか応用できない状態です。。

きっと、countifとかifとくみあわせれば何とかなるような気もするんですが。

回答No.7

Wendy02

#6の回答者です。
>皆さんの知恵を借りてステムなども一発でできる感じのシステムが作れたらと思います！

別に、とやかく言うつもりもありませんが、こちらの質問には答えずに、自分の質問の言葉や表現を変えただけですと、なんとも答えようがありません。それとも、こちらの質問していることは、あまりにも低レベルで、話にならないのでしょうか？

それに、個人的な趣味でしているものではないのでしょうから、あまりOSやOffice のVersion の事情はこの際には直接関係ないように思うのです。Excel 2008は、VBAは使えませんが、その前後のバージョンは使えます(現在は「後」バージョンはありませんが、搭載されると聞いています）。もちろん、AppleScript はありますから、マクロが使えないというわけではないのですが、VBAを搭載していないと、こちらはサポート出来ません。

それなりのシステムを作るというなら、こちらは、ある程度を考えることはできるかとは思います。私は、VBA専門ですし、関数でしようとしているものについて、他の方達が回答はするかとは思います。

>if(countif(A:D,A2)>1,"ダブり","")みたいなかんじです。
しかし、このぐらいの初歩的なものは、誰かの力を借りるまでもなく、そのぐらいは、事前に書籍なりで、調べていただきたいと思うのです。

現在の情報だけでは、決定的に不足していますから、手のつけようがありません。私たちは、学校の生徒ではありませんから、命題を出されて、先生が気に入らないなら、ダメだしされて、もう一度、やり直しさせられるということはありえませんし、大人のマナーにも反します。掲示板では、具体的な例をだして、求める結果を具体的に示してもらう、そのための組み立てをしてもらう、という帰納的な方法を求めるのがよいのです。

今の状態の様子からすると、人間が判断するべきものがあるなら、単語の一覧を作る程度で止めればよいのではないかと思います。

なお、数理的な処理でしたら、その単語自体を、Jacet(大学英語教育学会） で、ある程度の単語(8000語）には、数値化されていますので、それで、統計化は出来ます。しかし、今回のように、希望と現実の差が大きすぎるような場合、これ以上、あれこれと言ったところで、何もならないように思います。ここの質疑そのものの勢いも失ってしまいましたから、もし疑問があるなら、新たに質問をまとめてお聞きになったほうが良いかもしれません。

回答No.6

Wendy02

私には、よく質問が理解できない部分があります。私の解釈が正しければ、もともと、Excelを扱うのは結果であって、そこでツールを使いこなすには、相当の技術力が必要になってしまいます。

1), I like ( ) tennis.（問題文）
1, playing 2, eating 3, running 4, waving　（選択肢）

>それぞれの問題の「答え」と「それいがい（不正解の選択し）」が重複（だぶり）しない。
どういうことでしょうか？

この場合は、答えは、playing ですね。

そうすると、次の問題で、

2) He is ( ) in the ground of school.
1, playing 2, leaving 3, running 4, watering

3) She is ( ) a rice ball this morning.
1, stopping 2, leaving 3, running 4, eating

4) Tom ( ) the piano after school
1, breaks 2, plays 3, runs 4, hits

2) の場合は、もともと、設問として不可なのは分かりますが、そういうことは、仮にVBAでも、不可能です。

3) は、かろうじて、設問は確定しますが、これが1)と、不正解の中で重複するので不可だというのでしょうか。

4) も、危うい設問ですが、当然重複するから、これもダメとするのでしょうか。

こちらの解釈では、1,000問あっても、そこから確実に自動化して問題自体を抽出することなどは、相当に難しい内容だと思います。単語自体は、コンコーダンス・ツールを使えば、単語自体は、単語抽出出来ますし、また、VBAでも可能です。

>英文の変化系（playing, playなど）も振り分けの条件に対応させることができません。

しかし、、形態素解析(morphological analysis)をするという意味ですか？Perlなどをいれたら、大変になってしまいますから、例えば、ドイツのTreeTagger でしたら、Mac のシステムもあるようです。重複を除くというのは、上記のような内容の場合は、その妥当性がはっきり分かりにくいのです。もう少し、分かりやすい解説が必要ですが、もしかしたら、Excelでは基準を判断させるのは無理かもしれません。

TreeTagger:

参考URL：

http://www.ims.uni-stuttgart.de/projekte/corplex/TreeTagger/DecisionTreeTagger.html

お礼

ありがとうございます！説明わかりにくかったようなのでつぎたしますね。すいませんでした。

まず、私は各問いの問題文を「ステム」、答えを「ターゲット」、錯乱肢を「ディストラクター」と読んでます。。私のここで意味する「ダブり」の定義は各問いの

ターゲット同士の重複（理由は限られたテスト問題に同じターゲット（テストしたい項目）を取り入れても意味がないから。また、一方が一方のヒントになりうる＝テストとして機能しなくなるため。）

問題Aのターゲットが問題Bのステムにある場合の重複（ターゲットが文章から読み取れるためにヒントになってしまうから。）

このほかにも、ターゲットxディストラクター、ステムxステムなどお互いのヒントになりうる物をここでは「ダブり」とよんでいます。

結果的にもデータベースとして利用したいので、エクセルが便利と思い、利用を考えています。

たしかにVBAをつかうと１０００問（例）から、１００問、ダブりなしで抽出可能らしいのですが、できればいろんなヒトが使える形でリストを作成したいので、関数だけでがんばろうと思ってます。あと、マック版はマクロ使えないみたいです。w

さきほど、二枚のエクセルシートを作成しました。一枚はワードにあった問題にIDをふって、エクセルにタブ区切り（っていうのでしょうか？）で横並びに入れました。A列に、ID。Bにステム。C、D、Eに番号をとったディストラクターです。

もう一枚に、選択肢をすべて原型に直した状態で、A列にターゲット、B～D列にディストラクターを打ち込みました。現時点ではこれにifとcountifを入れてでダブりチェックしてます。
if(countif(A:D,A2)>1,"ダブり","")みたいなかんじです。

つまり、ステムは入れていないので、ステム内のダブりチェックはできませんが、少なくともターゲットとディストラクターのダブりがない状態で、１００問選べるのは、私としてはかなりの時間削減です。（いままでは、１０００問ワードに入れたまま、一語づつワードの検索機能でがんばってました；；）

それから、最終的には、これにvlookupでIDとダブりをマッチさせて、ダブりのない物を選んでいくという形にしたいと思います。

質問したときより、だいぶ進んでいまいましたが、このプロセスに、皆さんの知恵を借りてステムなども一発でできる感じのシステムが作れたらと思います！

ご協力お願いします！

回答No.5

lll49erlll

回答者の皆さんが危惧されているように、各データの配置がネックでしょう。
（問題文）と（選択肢）がどのように取り込まれているか、、、ワードからでしたら たぶん
問題文が１行、全ての選択肢で１行 が 同じ列に取り込まれていると思います。

何らかの規則性があって（例えば 問題文は奇数行、選択肢は偶数行 等）取り込まれてれば、
わりかし、単純なＶＢＥコードで一気に分割できるでしょう、

選択肢行も、フィルで分割すれば、選択肢番号（1,2,3,4,)が くっついてきますので、比較の
邪魔になりそうです、
幸い 質問の選択肢を スペースで分割したら、綺麗に 番号部と単語部が別れて８列に分割
出来ましたので、望みがありそうです。

もう一つ難問が、例えば 選択肢4,のwavingとwaveのような単語を同一視するなら、ちょっと細工が
必要ですね、英語の事は もうほとんど忘れましたが一人称と三人称で単語の語尾が変わったり、
yをiに変えて云々 など＾＾あったのを かすかに覚えています、＾＾；
そういった 単語の同一視を考慮して比較すれば、結構 手間が掛かりそうです、、、

ま、とにかく データの規則的な整列が 肝要かと思われます。

お礼

フィルってすごい便利だったんですね。すごい使えそうです。こんな初歩的なのをしらなかったなんて。。。はずかしい。

回答No.4

layy

1), I like ( ) tennis.（問題文）
1, playing 2, eating 3, running 4, waving　（選択肢）

仕組みを作って重複を無くすにはそれなりの情報を用意しないと
これだけでどうしようかというのは無理があるように思います。

「like」を使った問題は・・・・・
「～ing」を選ぶ問題は・・・・
と使われるキーワードでカテゴリ分けする。

「I like playing tennis.」
最終形で判断する。
単語で面倒でしたら文字数（２２）でもそこそこ区分けできる。
文が１８文字の問題は・・・、
文が２０文字の問題は・・・、
等内容と関係ない情報で並べ替えするとランダムっぽくなる。

１００問にとりあえず分けて、微調整でもできなくもない。

参考になれば。

回答No.3

MackyNo1

実際のエクセルシートに入力されているデータの形式はどのようなものなのでしょうか？

例えば問題の列と回答の選択肢の列が違うのか、同じ1列にすべてのデータが入力されているのかによって対応が異なります。

1列に入力された複数の文章中に同じ単語が含まれていないか調べるなら以下のような操作で単語に分割して、「フィル」（文字の割り付け）の機能を利用してCOUNTIF関数でカウントするか条件付き書式で重複データに色付けするのが簡単かもしれません。、

Ｍａｃ版エクセルとのことで操作が違う可能性がありますが、A列にすべての文章が入力されているなら、A列の列幅を1文字分の幅にせまくして、データ範囲のセルを選択して、ホームタブの「フィル」で「両端揃え」をクリックします（WindowsのExcel2007の場合、200３までのバージョンなら「編集」「フィル」「文字の割り付け」）。

この操作ですべての単語がA列１行に分解されますので、例えばB列にCOUNTIF関数を入力して、A列の単語が複数あるセルを調べます。

上記の操作は、英単語を別々のセルに分解させる方法の1例ですが、目的によっては他の機能を利用するほうが効率的な場合もありますので、現在のデータベースをできる限り詳細に例示されると、皆さんからより的確な回答が得られると思います。

お礼

ありがとうございます。フィルもなんか応用できそうですね。皆さんの意見を参考にちょっと、並べ替えてやってみます。並び替え終わったら、どのようにしたのか載せるのでみてみてください。

回答No.2

yuix_99

1000問程度であれば、手でやった方が早いような気もします。
Excelやプログラムを使うのであれば、私ですと、
まず、英文全てから使っている単語を抽出します。
次に、それを、A:Aに置き、B:Bなどに問題があるとすれば、C:Cで、A:Aセルを参照して重複がないか調べた結果を表示させ重複があると所は潰していく感じですかね。
ただ、フォーマットが違うとありますので、その変換なども必要になるかもしれません。
英文から単語語抽出するには正規表現で行っても良いと思いますが、秀丸などお持ちでなければ下記のリンクなど参考になると思います。

参考URL：

http://pia.skr.jp/app/freq_sort.html