エクセルで２つの表をずれないように合体させたい

質問

使っているエクセルのバージョンは2007です。

英語と数学の試験を行い、それぞれ別の人間が採点し、エクセルに集計しました。

A列に受験番号、例えばA00001
B列に点数、例えば62

今、英語の表C列とD列の部分に数学の表をコピペして、英語と数学の合計点を出そうとしています。

基本的に全員が英語・数学の両方を受験しているはずですので、そのままコピペするだけで、
全員の英語と数学の点数が一致し、E列でSUM関数を使って合計点が出せるはずです。

しかし、英語だけを受験した人、数学だけを受験した人が何名か居て、しかも片方しか受験しなかった人の数が同数である場合、受験番号と点数がずれてしまい、正しくない合計点が出てしまいます。

それを防ぐため、A列にある英語の受験番号とC列にある数学の受験番号が一致しているのかどうかを調べたいと思っています。

今までは合体させてからプリントアウトし、定規を当てて一人ずつ確認していました。

しかし、何百人、何千人ともなるとすごく時間が掛かりますし、目視での確認も絶対ではありません。

エクセルの機能で、例えば一致していればC列のセルの色が変わるとか、そのような操作はできないでしょうか。

条件付き書式でできそうな気がするのですが、具体的にはわかりません。

どなたか、よろしくお願いします。

回答No.2

yui56544 · ベストアンサー

VLOOKUP関数で受験番号を機軸に点数を呼び出せば間違いは起こらないはずです
VLOOKUPの解説サイト
http://hamachan4.exblog.jp/4752893/

画像のような例で説明すると、
B2セルに
=VLOOKUP(A2,$E$2:$F$12,2,0)
C2セルに
=VLOOKUP(A2,$H$2:$I10,2,0)
と入力し、数式を下方向にオートフィルで伸ばせば得点が自動で入ります
途中、得点がない（受験していない人）は「＃N／A」と記載されますので、その部分は手で消してください。

上記の数式を簡単に翻訳すると
=VLOOKUP(A2,$E$2:$F$12,2,0)　は

E2～F12セルの間の中からA2と同じ値を検索し、
A2と同じ値があった行の2番目の列の値を入力しますよ

というような意味になります
これを元に数値を変えればできるはずです

回答No.5

yui56544

No2　一部訂正
正しくは

C2セルに
=VLOOKUP(A2,$H$2:$I$10,2,0)

です。
VLOOKUP関数は、最も良く使われる関数なので、覚えておくと今後の作業が非常にラクになります。
最初はとっつきにくいかもしれませんが、これを機会に覚えてしまうと、今後同様の作業が一気にこなせるようになると思いますよ＾＾

回答No.4

kagakusuki

お求めの方法では御座いませんが、受験番号が必ず6文字等の一定の文字数となっていて、且つ、最初の１文字のみがアルファベットで、2文字目以降の文字は全て数字となっている場合には、色を変えて区別するまでもなく、目的とする合体させた表を、Ｅｘｃｅｌに自動的に作成させる事も出来ます。

　まず、Ｓｈｅｅｔ1からSheet4までを備えたExcelのbook（ファイル）を作成して下さい。
　次に、Sheet2のA1セルに次の数式を入力して下さい。

=IF(AND(LEN(INDEX(Sheet3!$A:$A,ROW()))=6,ISNUMBER(RIGHT(INDEX(Sheet3!$A:$A,ROW()),5)+0)),(CODE(INDEX(Sheet3!$A:$A,ROW()))&RIGHT(INDEX(Sheet3!$A:$A,ROW()),5))+0,"")

　次に、Sheet2のB1セルに次の数式を入力して下さい。

=IF(AND(LEN(INDEX(Sheet4!$A:$A,ROW()))=6,ISNUMBER(RIGHT(INDEX(Sheet4!$A:$A,ROW()),5)+0),COUNTIF($A:A,(CODE(INDEX(Sheet4!$A:$A,ROW()))&RIGHT(INDEX(Sheet4!$A:$A,ROW()),5))+0)=0),(CODE(INDEX(Sheet4!$A:$A,ROW()))&RIGHT(INDEX(Sheet4!$A:$A,ROW()),5))+0,"")

　次に、Sheet2のA1～B1の範囲をコピーして、同じ列の2行目以下に（受験者人数を上回るのに充分な回数だけ）貼り付けて下さい。

　次に、Sheet1の
A1セルに　　　受験番号
B1セルに　　　英語の点数
C1セルに　　　数学の点数
と入力して下さい。
　次に、Sheet1のA2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet2!$A:$B),"",CHAR(INT(SMALL(Sheet2!$A:$B,ROWS($2:2))/100000))&RIGHT(SMALL(Sheet2!$A:$B,ROWS($2:2)),5))

　次に、Sheet1のB2セルに次の数式を入力して下さい。

=IF(AND(INDEX($A:$A,ROW())<>"",COUNTIF(Sheet3!$A:$A,INDEX($A:$A,ROW()))),VLOOKUP(INDEX($A:$A,ROW()),Sheet3!$A:$B,2,FALSE),"")

　次に、Sheet1のC2セルに次の数式を入力して下さい。

=IF(AND(INDEX($A:$A,ROW())<>"",COUNTIF(Sheet4!$A:$A,INDEX($A:$A,ROW()))),VLOOKUP(INDEX($A:$A,ROW()),Sheet4!$A:$B,2,FALSE),"")

　後は、英語のデータはSheet3に、数学のデータはSheet4に、それぞれ受験番号はA列に、点数はB列に貼り付けますと、
Sheet1に合体させた表が、自動的に表示されます。

回答No.3

-9L9-

固有IDとして受験番号があるので、vlookup関数で他方のデータを読み出せばいいだけでしょう。
vlookup関数はエクセルでデータ処理をする上で基本的な関数の一つですから、ご自分で使い方を調べることをお勧めします。両方で同じように参照して、エラーになった受験番号があれば、それが片方のみ受験した者ですから、それだけを抽出してもう一方に付け加えればいいでしょう。

はっきりいって質問に書いているような集計の仕方（コピー＆ペースト）は正確性が全く担保できず、あり得ないやり方だと思います。チェックが入らない分、手集計より悪いです。他人を採点する立場の方のやるべき方法でありません。教育者ならもっと論理的にやりましょう。

参考URL：

http://www.yoshikawa.co.jp/ybs/skilup/ybs0308.htm

回答No.1

DJ-Potato

Ｆ列辺りに

=IF(A1=C1,"","●")

を入力すれば、受験番号がズレているところから●が並ぶのでわかりやすいかもしれません。