OpenOfficeで一つの表から支店内ランク、エリア内ランクを求める
下の表では、社員の売上金額に基づいて、全体のランクと併せて所属エリア内、所属支店のランクが表示されています。
全体のランクは、売上金額をRANK関数でD列の中で判定すればいいことですが、所属エリア内、所属支店でのランクをこの一つの表から求めるには、どうしたらいいでしょうか？
 |
| 送信者 OpenOffice |
答えは、これです。↓↓↓↓↓↓↓
 |
| 送信者 OpenOffice |
この表ではRANK関数を使って答えを出しています。
関数名：RANK関数
書式：=RANK(処理対象の値;範囲;タイプ)
何ができる：処理対象の値が範囲内で、何位かを判定してランクを返す。
平たく書くと：=RANK(D2;$D$2:$D$300;0) で、D2の値がD2:D300の範囲内で何位かを返します。
ここで、タイプを1に設定すると、値が小さいものを上位にランクさせることができます（昇順）。
支店内ランクの求め方
支店内ランクは、支店の行が何行目から始まって何行目まで続いているかを求めることがポイントになります。そのためには、MATCH関数を使います。
関数名：MATCH関数
書式：=MATCH(処理対象の値;範囲;タイプ)
何ができる：処理対象の値が範囲内で、何番目に登場するかを数える。
平たく書くと：=MATCH("勝手なシンドバッド";C1:C45;0) で、勝手なシンドバッドがC1:C45の範囲内で11番目の行に登場することが分かります。
ここで、同じデータが複数登場する場合、タイプを0に設定すると、一番最初に登場する行を答えとして返します。
RANK関数の範囲の起点の設定
=MATCH(B2;$B$1:$B$300;0) でB2の値(北部第一支店)は2行目に初めて登場していることが分かります。
以上のプロセスで得られた値を行番号としてADDRESS関数に当てはめます。
判定の対象になる売上金額が入っている4列目をRANK関数内で範囲に設定したいので、ADDRESS関数に入れる列番号は4を入れます。
=ADDRESS(MATCH(B2;$B$1:$B$300;0);4)
これで、E2セルの場合、$D$2が返されるので、この値を数式の中で再計算の対象にできるように、INDIRECT関数に当てはめます。
=INDIRECT(ADDRESS(MATCH(B2;$B$1:$B$300;0);4))
ここまでで、RANK関数での判定範囲の起点が設定できました。
RANK関数の範囲の終点の設定
全体の範囲内に北部第一支店が何個登場するかを数えて、その値をMATCH関数で得られた値に加えて終点の行番号にすることにします。
=COUNTIF($B$1:$B$300;B2)+MATCH(B2;$B$2:$B$300;0)
ここで、MATCH関数の範囲の設定をB1からでなく、B2からにすると、最初に登場する行を除外して計算することができます。または、
=COUNTIF($B$1:$B$300;B2)+MATCH(B2;$B$1:$B$300;0)-1
で、同じ結果（ここでは6)が得られます。
MATCH関数で得られた値を、起点の時と同様にADDRESS関数に当てはめます。ここでも、ADDRESS関数に入れる列番号は4です。
さらにINDIRECT関数をかぶせて数式の中で使用できるようにします。
=INDIRECT(ADDRESS(COUNTIF($B$1:$B$300;B2)+MATCH(B2;$B$2:$B$300;0);4))
ここまでで、RANK関数での判定範囲の終点が設定できました。
これですべて材料が整ったので、RANK関数にすべてを当てはめてみましょう。
=RANK(D2;INDIRECT(ADDRESS(MATCH(B2;$B$1:$B$300;0);4)):INDIRECT(ADDRESS(COUNTIF($B$1:$B$300;B2)+MATCH(B2;$B$2:$B$300;0);4));0)
めでたく、河合菜穂子さんが北部第一支店内で1位であることが分かりました。
エリア内ランクの求め方
基本的な考え方は、支店内ランクと全く同じです。支店内ランクでは、支店名が入力されているB列を範囲設定のときに処理対象にしていましたが、今度はエリアが入力されているA列を対象にすることにします。
それだけです。
=RANK(D2;INDIRECT(ADDRESS(MATCH(B2;$B$1:$B$300;0);4)):INDIRECT(ADDRESS(COUNTIF($B$1:$B$300;B2)+MATCH(B2;$B$2:$B$300;0);4));0)
を下のようにします。
=RANK(D2;INDIRECT(ADDRESS(MATCH(A2;$A$1:$A$300;0);4)):INDIRECT(ADDRESS(COUNTIF($A$1:$A$300;A2)+MATCH(A2;$A$2:$A$300;0);4));0)
なお、この表と数式を成立させるためには、エリア＞支店名＞社員名のルールに基づいて表が構成されている必要があります。
たとえば、北部の北野支店のデータが16行目でいったん終了して再び22行目から始まったりすると正確な結果を返しません。
この場合は再度並べ替える必要があります。
 |
| 送信者 OpenOffice |
