タグ別アーカイブ: Excel

vlookup関数で参照先が複数ある時

春学期の成績つける際に書いてあって放置してあった完全に自分用メモです。

成績をつける際に,vlookup関数で名前やIDを参照するというのはよくある作業だと思います。vlookup関数の引数の基本は以下の通り。

vlookup(参照元,範囲,引っ張ってくる列,参照方法)

で,名前の列が1列ならこれだけでいいのですが,スピーキング授業の話の記事で紹介したようにペアでスピーキングテストなんかをやると,得点の列は1列で,名前の列が2列になります。最終的な成績が入るExcelシートでは名前は1列で,vlookup関数は指定範囲の1列目から名前を探してくることになりますから,2列目にある名前は参照されません。そして,誰が1列目で誰が2列目かはわからない状態なので,人によって参照範囲を変えることもできません。そこで使うのがIFERROR関数です。

IFERROR関数は,1つ目の引数でエラーが出た場合に2つ目の引数を実行するという関数です。つまり,この関数の1つ目でペアの1列目から始まる範囲を指定し,2つ目の関数でペアの2列目から始まる範囲を指定すれば解決するというわけです。意外に簡単でした。つまり,

IFERROR(VLOOKUP(名前セル, 参照先1, 引っ張ってくる列, 参照方法), VLOOKUP(名前セル, 参照先2, 引っ張ってくる列, 参照方法))

とすれば良いことになります。参照先1では2列あるペア列の1列目が先頭列になるように指定し,参照先2では2列目が先頭列になるように1列参照列を右側にずらします。そして,引っ張ってくる列も1列ずらして指定します。こうすることで,1列目の中から名前を探して点数を引っ張ってきて,もしここでエラーが出る(つまり1列目に名前がない)場合には2列目の中から名前を探して対応する点数を引っ張ってくることになります。このやり方を応用すれば,ペアだけではなく3人や4人といったグループの場合でも同じことができます。もちろん,Rとかで縦横変換して名前の列はすべて1列にまとめるようなことをしても最終的に名前が1列,点数で1列という形のデータを得ることはできるのですが。今回はEXCEL上で完結させる場合の話でした。

なにをゆう たむらゆう。

おしまい。

 

Excelで英文中の特定の単語までの語数を数える

自分の作業用のメモです。

博論の進捗が遅れすぎていていよいよやばくなってきました。そこで,ちょっとでも博論を進めるための作業をやっていて,プログラムの修正も済んだので,あとはプログラムに刺激文を流し込むだけになりました。そこでちょっとした問題発生。あまり詳しいことを書くとネタバレするので書けませんが,プログラムの中で,ある特定の単語数になった場合に処理をするというようなことをやっています。それが刺激文ごとに異なるので,配列変数にその単語数を指定しているのですが,100ちょいある刺激文をいちいち目視して何語目かを数えるとかやってられません。ちなみに刺激文自体はExcelファイルにまとめています。こんな感じで。

ブログ記事用_20160811

Excel上で単語数を数える関数は,ウェブ上で情報が見つけられます。いつも使っているのはこちら。

エクセル関数を利用して文字列中の英単語の数を数える

英文の中の特定の単語までの語数を数えるというのは,分解して考えてみればいくつかの作業に分けて考えることができます。

  1. 英文の中にある特定の単語をみつける
  2. 特定の単語の前にある文字列を抽出する
  3. 特定の単語の前にある文字列から単語の数を数える

という感じです。英文に含まれる単語数を数えるのは,上の3つのうちで3の作業になります。この単語数を数えるのはどういう原理かというと,つまりはスペースの数を数えるということです。英文にはスペース区切りで単語が入っているわけですから,スペースの数を数えて,それに1を加えた数が単語の数というわけです。

1は,特定の単語,つまり特定の文字列を探すということです。FIND関数を使うと,指定した文字列が現れる位置を探してくれます。そして,2の文字列の抽出にはMID関数が使えます。MID関数は,文字列の任意の位置から指定した文字数を抽出してくれます。このFIND関数とMID関数を組み合わせれば,特定の単語を見つけて,それより前にある文字列を抽出することができるわけです(参考サイト)。例えば,さきほどのExcelでD2に入っていた

The boy saw the cute girls in the park last week.

という文の中で,parkがtarget語であるとしましょう。そして,target語はF2に入れているとします。すると

=MID(D2,1,FIND(F2,D2,1)-1)

とすれば,parkの前までの単語をすべて抽出できます。

ブログ記事用2_20160811

FIND関数で指定した位置からだとその単語の先頭も含まれてしまうので,-1をしています。これで,1と2のステップができました。あとは,この抽出した文字列に含まれる単語の数を数えれば良いということになります。

単語の数を数えるには,引き算を使います。先ほど,スペースの数を数えれば良いと言いましたが,スペースを数えるには,スペースありの文字数(≠単語数)から,スペースなしの文字数(≠単語数)を引けば求めることができます。文字列を数えるのはLEN関数を使い,スペースの削除はSUBSTITUTE関数を使います(上記サイト参照)。先ほどの画像の中でG2に入っているセルの文字列を使うとすると,

=(LEN(G2)-LEN(SUBSTITUTE(G2,” “,””)))

とすれば単語数がカウントできます。ここで注意が必要なのは,通常英文の単語数をカウントする場合は+1をしないといけないという点です。例えば,次のような英文を考えてみます。

He is always shy.

普通英文はピリオドで終わっていて,その後ろにはスペースはありません。よって,単語の数よりスペースの数は1少ないことになります。ですから,数えたスペースに1を足すことで,単語の数を求めることができます。しかしながら,さきほど文字列抽出したときには,ある文字列(単語)の直前までの文字列を抽出しました。指定した文字列は単語であり,英文では単語の前には必ずスペースがあります。つまり,G2に入っている文字列の”the”のうしろには実はスペースが入っています。このため,単語の数とスペースの数が一致しており,最後に+1をする必要がなくなっているというわけです。

ここまでで,target語であるparkの前にある単語の数を求めることができました。もともとの目的は,「parkはその文の中で何語目なのか」でしたから,実は結局のところ,先ほどのものに最終的には+1をすることになります。この+1は,英文中に含まれる単語の数を数えるためにするものではなく,その次の単語(1語後ろ)が全体の何語目かを知りたいから行うということには注意が必要です。

ということで,賛否両論おありかと思いますが,私はガンガン埋め込み派なので,G2というセルに一旦吐き出してから計算するのではなく,LEN関数の中に,MIDとFINDを埋め込んでしまいます。

=(LEN(MID(D2,1,FIND(F2,D2,1)-1))-LEN(SUBSTITUTE(MID(D2,1,FIND(F2,D2,1)-1),” “,””)))+1

どん。

こうすれば,あとはF列にtargetとなる単語を刺激文の数だけ入力してさえあれば,「見つける->抽出する->数える」という3つのステップを表現した上の関数を別の列に縦にコピーするだけで自動的にtarget語の出現位置を計算することができるというわけです。

この方法がわかったおかげで目を細めながら単語の数をひたすら数えるという土方作業をやらずに済みました。ただし博論完成までは程遠いです(白目

なにをゆう たむらゆう

おしまい。