OpenOfficeで全員の売上一覧表から特定の営業員の成績だけを抜き出す
下の表では、A列からD列に各営業部員が売上を計上するたびに売上高と利益が入力されています。
複数の営業部員のなかから特定の営業部員の成績だけをF列からI列に抜き出しました。
こんな面倒くさい事をしなくてもフィルタ機能を使えば良いじゃないかと言ってしまえばここで話が終わってしまいますので、ま、ちょっとだけでも見てやってください。
 |
| 送信者 OpenOffice |
この表で抜き出し対象になる営業部員の名前は、G1セルに入力されています。
基本的な構成
F2セルに入力されている数式から説明します。=IF(COUNTIF(B2:B300;G1)>0;INDEX(A1:D300;MATCH(G1;B1:B300;0);1);"")
直訳すると、B2:B300の範囲内にG1セルの値が0より多く発見されれば、処理を行ない、そうでなければ空白にするということです。
ここでの処理は、INDEX関数とMATCH関数を組み合わせて処理を行なっています。
関数名：INDEX関数
関数名：=INDEX(参照範囲;行;列)
何ができる：参照範囲で、指定した行と列の交差するセルにある値を返す
平たく書くと：=INDEX(A1:B5;2;3)でA1:B5の参照範囲の中で、2行目の3列目にある値（C2セルの値）が返されます。
関数名：MATCH関数
関数名：=MATCH(検索値;検索範囲;検索タイプ)
何ができる：検索範囲で、検索値が何番目に発見されるかを求めます。
平たく書くと：平たく書くと：=MATCH(B3;B3:B10;0) で、B3セルの値が、B3：B10の範囲で何番目のセルにあるかを返します。
F2セルの場合、A1:A300の範囲内で、MATCH関数で返される行の一列めの日付が返されます。
ここでは、MATCH関数の働きが重要になります。
=MATCH(G1;B1:B300;0)
MATCH関数では検索タイプを設定することができます。
ここで、検索タイプが０（ゼロ)の場合、G1と完全に一致するセルを対象にします。もしも複数あれば、一番最初に現れるセルを戻り値にします。
この特性をここでは利用します。
B1:B300の場合、G1セルの値が複数発見されますが、一番最初に発見されるのは2行目なので2が返されます。
ここまでで、INDEX関数は、=INDEX(A1:D300;2;1)と等しくなり、F2セルに10月1日が返されます。
G2からI2セルまでは、F2セルと同じで、INDEX関数に設定する列の値が、2-4の範囲で変わるだけです。
次にJ2セルについて
J列の値が、以降の3行目以降の表示に影響を及ぼします。
=IF(F2="";"";MATCH(G1;B1:B300;0))
ここでは、G1の値がB1:B300の範囲内で何番目のセルに最初に登場するかが返されています。
表をご覧のとおり、2行目なので2が返されています。
３行目以降について
基本的なことは2行目とまったく同じですが、MATCH関数で検索タイプが０（ゼロ)の場合、G1と完全に一致するセルを対象にし、もしも複数あれば、一番最初に現れるセルを戻り値にする特性をここも利用するので、INDEX関数で参照するセルの範囲（起点）を変更する処理を各セルで行ないます。F3セルの場合
=IF(COUNTIF($B$2:$B$300;$G$1)>COUNTIF($G$2:G2;$G$1);INDEX(INDIRECT(ADDRESS(SUM($J$2:J2)+1;1)):$D$300;MATCH($G$1;INDIRECT(ADDRESS(SUM($J$2:J2)+1;2)):$B$300;0);1);"")
長い数式ですが、直訳すると、B2:B300の範囲内にG1セルの値が$G$2:G2の範囲より多く発見されれば、処理を行ない、そうでなければ空白にするということです。
ここで$G$2:G2の起点だけを絶対参照にしておけば、以降の行で$G$2:G3、$G$2:G4・・・・・のように起点が固定され終点だけが移動することになります。
この条件判定の結果、G2:G2の範囲では一回しかG1セルの値は発見されず、B列に入力されている回数よりも少ないので処理を行なうことになります。
↓↓↓↓↓処理の内容↓↓↓↓↓
=INDEX(INDIRECT(ADDRESS(SUM($J$2:J2)+1;1)):$D$300;MATCH($G$1;INDIRECT(ADDRESS(SUM($J$2:J2)+1;2)):$B$300;0);1)
INDEX関数の書式に照らすと、=INDEX(範囲;行;列)となります。
ここでひとつ上の行までのJ列の値を、合計して1を加えます。
INDEX関数での範囲指定
F3セルの場合、J2セルに入力されている値2がそのまま戻り、それに1を加えて3がINDEX関数の行の値になります。この値を、アドレス関数に当てはめてINDIRECT関数をかぶせると、数式の中で計算できる形になります。INDIRECT(ADDRESS(SUM($J$2:J2)+1;1)):$D$300
INDEX関数での行指定
MATCH($G$1;INDIRECT(ADDRESS(SUM($J$2:J2)+1;2)):$B$300;0)
ここでも範囲と同じくここでひとつ上の行までのJ列の値を、合計して1を加えます。
但し、G1の値を範囲内の2列目から検索するのでADDRESS関数での列の設定を2としてMATCH関数での範囲の終点をB300にします。
G3、H3 のときはADDRESS関数の列の値を3、4にそれぞれ設定するほかはF列と同じです。
J3セル
=IF(F3="";"";MATCH($G$1;INDIRECT(ADDRESS(SUM($J$2:J2)+1;2)):$B$300;0))ここでも基本的な設定はJ2セルと同じです。但し、F列と同じようにMATCH関数での処理の起点を行ごとに変えられるように設定します。INDIRECT(ADDRESS(SUM($J$2:J2)+1;2))
長くなってしまいましたが、こんな感じです。
条件に合ったデータの人に◎をつけたときの方法は、OpenOfficeで、リスト内の合致するデータの行に◎をつける をご覧ください。
特定の数値以上の人を抜き出したいときは、基準以上の数字の選手だけをリストアップする で紹介しています。
