Excelの課題です。
Excelの課題について相談されたのですが、すぐにどうすればいいか思いつきませんでした。ちょっと考えれば、なんとかなったのですが、あと10分で仕事が終わり、という時間だったでの、逃げ腰だったのがまずかったかも。
問題はこういうのです。
Excel で作られた３人の名前のリストのチェックをやりたい。
１つのグループが１行になっており、全く同じ組み合わせのみ除きたい。
例
1 A B C
2 B C D
3 C D E
4 C A B
5 B C D
6 D E F
上の例の、No.1 と、No.4 が同じ組み合わせ、また、No.2 と No.5 が同じです。リストは約1000行くらいになるそうで、自動で簡単にチェックできないか、というものでした。
質問された時は、「何かいい関数ない？」というものだったのですが思いつきませんでした。
じゃ、どうすれば確認できるか。
もちろん、VBAマクロ（というか普通にプログラム）すれば、なんとかなりそうです。それを、Excel のシートだけで、プログラムの経験の無い人でも使えるようなシートを、という要求でした。（まあ、無理ですね）
私が思うに、Excel だろうがプログラムだろうが、基本はいっしょです。どうすれば、全ての組み合わせを確認できるか、につきると思いました。最初に思ったのは、全員の氏名に重み付けしてそれを計算させる、というものでした。しかし、抜けがあった際、確認が難しくなります。そこで、ずべてのパーターンのマッチングをやらせる案を使いました。
Excel の =vlookup() は、非常に便利な機能で、私は前の会社でいろいろと使いました。
これを使うと、プログラムで組まないと難しいような処理を、Excel のシートの組み合わせでも十分実現することができます。
具体的には、３人の氏名から検索リストを作り、全ての組み合わせをキーとして =vlookup()で検索する方法ならやれそうと思いました。ただし、この方法では、自分と同じ組み合わせは見つけられません。そこだけ、別に調べる必要があります。これは、検索リストをソートして、上下で同じものがある行に印をつけることにしました。
という方針を決めてExcel のシートに関数を書いていきます。
(1) 検索リスト
=concatenate() で、”A” “B” “C” から、”ABC” という文字列を作ります。
(2) 組み合わせのキー
同じく =concatenate() で、３文字から順番を変えた全ての組み合わせを作ります。
元が”ABC”なら、”ACB”, “BAC”, “BCA”, “CAB”, CBA” ですね。
(3) 検索リストの同一判定
元の表は、検索リストをキーにして並び替えします。そして、その前後を比較する =if() を書いておきます。もし、前後に同じものがあれば、１に、なければ０になるようにしました。
こんな感じです。 =if(F10=F9,1,if(F10=F11,1,0))
(4) 順番を変えたキーを使って、検索リストを =vlookup() で一致しているものがないか探します。
(5) 最後に、全体の判定を行う関数を作ります。
同一判定で１だったら１、キーの検索でエラーでないものだ１つでもあったら１に、それ以外は０になるようにします。これは、=if() と、=iserror() や、 =and() などを組み合わせて
作ることができます。
=if( {同一判定で１},1,if( and(iserror(｛キーの検索｝), …. ), 0, 1 ))
(6) 最後に、オートフィルターを入れて、(5)で１になった行のみにし、行番号で元のリストの該当する行を対象と判定します。
元の行の右側に結構関数を書いたセルが並びますが、Excel のシートのみで抽出できます。
できたとはいえ、Excel でめったに関数を書かない人にとっては、「これって何語？ 日本語で説明してよ！」ということになるかもしれませんね。
前の会社では、女性事務員にこういう関数入りのシートを渡したら、「私、関数知らないので、こういうExcelシートは受け取れません」と言われました。
コピペして数字と文字だけにして渡し直しましたが、こう言われるとかなりガクッときます。