2009年10月07日

同じ名前がいたらNG

【数式クイズ】 難易度:★★★★☆
A2:A6のセル範囲にグループAのメンバー、B2:B6のセル範囲にグループBのメンバーが入力されています。
お互い相手のグループに、自分のグループと同じ名字のメンバーが1人もいない場合は「OK」、1人でもいた場合は「NG」と、D2セルに表示します。
同一グループ内に同じ名字のメンバーがいるのは問題ありません。

quiz23.jpg

quiz24.jpg

D2セルにはどのような数式を入力すればよいでしょうか?


【解答例】(2例)
=IF(SUM(COUNTIF(A2:A6,B2:B6))>0,"NG","OK")
=IF(OR(A2:A6=TRANSPOSE(B2:B6)),"NG","OK")

いずれの数式も、CtrlキーとShiftキーを押しながらEnterキーを押して、配列数式として確定します。

SUM関数とCOUNTIF関数を使った式は、条件部分の「>0」を省略し、

=IF(SUM(COUNTIF(A2:A6,B2:B6)),"NG","OK")

としても同じ結果になります。
(IF関数は、条件の部分が0であればFALSE、0以外ならTRUEとみなすため)
また、SUMの代わりにOR関数を使って、

=IF(OR(COUNTIF(A2:A6,B2:B6)>0),"NG","OK")

のようにすることもできます(こちらの「>0」も省略可)。
COUNTIFを使った数式は、要するに、2つのグループの一方のセル範囲をCOUNTIF関数の引数範囲に、もう一方のセル範囲を引数検索条件に指定し、配列数式にすることで後者のセル範囲を配列にしているわけです。

TRANSPOSE関数を使った式は、前回の問題の応用例です。
2つの配列がいずれも縦方向なので、TRANSPOSEで1つを横方向に変換して、平方配列の比較を行っているわけです。

なお、この問題の場合は同一グループ内に同じ名字がいるのはOKですが、グループに関係なく同じ名字がいたらNG、としたい場合は、A2:B6を1つのセル範囲としてまとめてしまい、

=IF(OR(COUNTIF(A2:B6,A2:B6)>1),"NG","OK")

で、Ctrl+Shift+Enter、とする手があります。
posted by 土屋和人 at 15:01| Comment(0) | TrackBack(0) | Excelクイズ
この記事へのコメント
コメントを書く
お名前: [必須入力]

メールアドレス:

ホームページアドレス:

コメント: [必須入力]

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。
この記事へのトラックバックURL
http://blog.sakura.ne.jp/tb/32735143
※言及リンクのないトラックバックは受信されません。

この記事へのトラックバック