OpenOfficeで表示対象の社員のIDを絞り込む
下の表は、A列に社員のIDが入力されています。
IDの一部分をE2セルに入力すれば、その候補がE-G列に表示されるシステムを関数を使って作ってみました。
まず左端の一文字を打ち込みます。10人の候補が表示されました。
 |
| 送信者 OpenOffice |
 |
| 送信者 OpenOffice |
D列の説明
D列では、E2セルに入力された文字数分を、A列のそれぞれのIDの左側から取り出しています。=LEFT(A2;LEN($E$2))
ここで取り出された文字列が評価の対象になります。
F2セルの説明
ここでは、E2セルに入力された文字分と一致するのが、D列に何個あるかを数えています。=COUNTIF(D2:D300;E2)
ここでF2セルの書式設定を文字列にしておく必要があります。
 |
| 送信者 OpenOffice |
D列とE2セルの書式を一致させないと、正しく判定されません。これを回避するための措置です。
E3からG3セルの説明
A1からC1セルとタイトルを同じにしておきます。こうすることでE列：G列の4行目以降の関数の組み立てがシンプルになります。
E列：G列の4行目以降の説明
=IF(COUNTA($E$4:E4)<=$F$2;INDIRECT(ADDRESS(MATCH($E$2;INDIRECT(ADDRESS(MATCH(E3;$A$1:$A$300;0)+1;4)):$D$300;0)+MATCH(E3;$A$1:$A$300;0);1));"")シンプルにと言ってもこんなに長いのですが、構造は意外にシンプルです。
=IF(COUNTA($E$4:E4)<=$F$2;処理;"")
E4セルでは、F2セルに表示されている該当セルの個数よりもE4:E4セルの間の空白ではないセルの個数の数が同じか、あるいは少なければ処理を行ない、それ以外のときは空白にしておくという数式です。
E列：G列の4行目以降で処理を行なう場合の説明
=MATCH($E$2;INDIRECT(ADDRESS(MATCH(E3;$A$1:$A$300;0)+1;4)):$D$300;0)この数式で、E2セルの値がADDRESS(MATCH(E3;$A$1:$A$300;0)+1;4):$D$300の間で最初に登場するのは何行目かを求めます。
ここで、ADDRESS(MATCH(E3;$A$1:$A$300;0)+1;4)は、E3セルに入力されている値が、A列で何行目に登場しているかを求めてその一行下のアドレスを返させています。
E4セルの場合、一行上のセルには"ID"が入力されていて、A列では一行目に現れるので、2行目から300行目までの間でE2セルに入力されている値が最初に登場するセルの行番号を返すことになります。この結果2が返されます。
=MATCH(E3;$A$1:$A$300;0)
ここで、E3セルの値が現れたセルの行番号を返させています。E4セルの場合、一行上のセルには"ID"が入力されていて、A列では一行目に現れるので、1が返されます。
2+1の結果である3がE4セルに返されるべきセルの値があるA列の行番号になります。
列は、IDが入力されている一列目なので1を当てはめ、ADDRESS関数とINDIRECT関数をかぶせて数式を完成させます。
=INDIRECT(ADDRESS(MATCH($E$2;INDIRECT(ADDRESS(MATCH(E3;$A$1:$A$300;0)+1;4)):$D$300;0)+MATCH(E3;$A$1:$A$300;0);1))
F4セルの場合はADDRESS関数に当てはめる列番号を2にします。G4セルの場合はADDRESS関数に当てはめる列番号を3にします。それ以外はE列と全く同じです。
E5セルの場合を例にとってもう一度処理してみましょう。
=IF(COUNTA($E$4:E5)<=$F$2;INDIRECT(ADDRESS(MATCH($E$2;INDIRECT(ADDRESS(MATCH(E4;$A$1:$A$300;0)+1;4)):$D$300;0)+MATCH(E4;$A$1:$A$300;0);1));"")
E2セルの値が、ADDRESS(MATCH(E4;$A$1:$A$300;0)+1;4):$D$300の間で最初に現れるセルの行番号を求めます。
ここでMATCH(E4;$A$1:$A$300;0)+1によってひとつ上の行のE4セルの値が現れる行番号を求め、その一行下のD列のセルをMATCH関数での検索の起点にします。
ここではD4セルが、検索の起点になります。
MATCH($E$2;$D$4:$D$300;0)で D4セルから数えて4行下に該当の行があるので、MATCH(E4;$A$1:$A$300;0)で得られる、先にE4の値が現れた行番号3を加えて7がADDRESS関数に当てはめられる行番号になります。
