OpenOfficeで同姓同名を排して成績を検索する

下の画像は、某社の個人売上成績表です。
ご覧のとおり、同姓同名が結構います。同姓同名の人を上手く排して、個人の売上成績を抜き出す方法を紹介します。

こんなふうに。

このファイルはSKYDRIVEからcalc162.odsをダウンロードしてください。

この表のH列からJ列の入力は、リストによる入力になっています。
リストの作り方は、「OpenOfficeで膨大なリストのなかから、ほしい項目を効率的に見つけ出すには」　で紹介した方法を用いています。設定した入力規則は、上のファイルをダウンロードしていただき、H2セル、I2セル、J2セルをそれぞれ個別に選択して、ツールバのデータから入力規則をクリックしてソースを確認してください。

さて、該当する個人の成績を抜き出すための関数はVLOOKUP関数を用いています。
関数名：VLOOKUP関数
書式：=VLOOKUP(検索値;検索範囲;列番号;検索のタイプ)
何ができる：検索範囲に指定された表の1列目から検索値を探し出して、同じ行にある、指定した列番号の値を返す。
平たく書くと：=VLOOKUP("勝手なシンドバッド";C17:F22;2;0) で、上の表の場合、売上件数の値34が返されます。

ここでポイントになるのは、検索範囲になる表の起点と終点です。

起点の設定

上の画像では、中部統括支店の勝手なシンドバッドの成績を検索したいので、中部統括支店が何行目から始まっているかが、起点の行番号になります。
=ADDRESS(MATCH(I2;B1:B300;0);3)
MATCH関数を使って求めると、17が返されるので、行番号を17、列番号を名前が入力されている3列目として、ADDRESS関数でC17を返させます。

*MATCH関数では、検索のタイプに0を設定すると、検索範囲内に同じデータが複数存在する場合、最初に現れる行を返します。そのことを、ここでは利用しています。
*また、タイプを1に設定すると、　検索範囲内に同じデータが複数存在する場合、最後に現れる行を返します。
*検索の対象にする表をなぜC列から始めるかと言えば、VLOOKUP関数では、検索値が入力されている列は必ず1行目でなければいけないからです。

終点の設定

=ADDRESS(COUNTIF(B2:B300;I2)+MATCH(I2;B1:B300;0)-1;6)
ここでは、中部統括支店が、B列に何個入力されているかを数えます。その値を、中部統括支店が最初に入力されている行番号に加え、1を引くと中部統括支店の最後の行番号が得られます。ここでは22が得られます。
こんな面倒なことをしなくても=ADDRESS(MATCH(I2;B1:B300;1);6)
で済むのではないかと考えても無理はないのですが、こうすると、一番最後に入力されているエリアを検索対象にするときにエラーがでます。これを回避するための措置です。
得られた行番号と、表の最終列である6列目をADDRESS関数に当てはめてF22を得ます。

上のプロセスで得られた起点と終点のアドレスにそれぞれINDIRECT関数をかぶせて再計算できる形にしてVLOOKUP関数に組み込みます。
=VLOOKUP(J2;INDIRECT(ADDRESS(MATCH(I2;B1:B300;0);3)):INDIRECT(ADDRESS(COUNTIF(B2:B300;I2)+MATCH(I2;B1:B300;0)-1;6));2;0)

また、このように数式が長くなるのを好まないときは、起点と終点のアドレスをどこかに出力しておき、そのアドレスにINDIRECT関数をかぶせてVLOOKUP関数に組み込む方法もあります。
ここではN1セルに起点を出力してN2セルに終点を出力してみました。
=VLOOKUP(J2;INDIRECT(N1):INDIRECT(N2);2;0)

これで中部統括支店の営業担当者「勝手なシンドバッド」の売上件数３４が得られました。

さて、売上金額、利益については上の数式と仕組みはまったく同じで、列番号だけを変えています。
売上金額の場合：=VLOOKUP(J2;INDIRECT(N1):INDIRECT(N2);3;0)
利益の場合：=VLOOKUP(J2;INDIRECT(N1):INDIRECT(N2);4;0)

なお、この表と数式を成立させるためには、エリア＞支店名＞社員名のルールに基づいて表が構成されている必要があります。
たとえば、北部の北野支店のデータが16行目でいったん終了して再び22行目から始まったりすると正確な結果を返しません。
この場合は再度並べ替える必要があります。

送信者 OpenOffice