エクセル 順位表

質問

エクセル 順位表

いつもお世話になります。
売上実績表の中に、順位表を追加したいのですが、実績表から抽出すると、同一の実績の営業所は、すべて同じ営業所になってしまい困っています。

Ａ　　　 Ｂ　　　 Ｃ　　　Ｄ　　　 Ｅ 　 Ｆ
営コード　実績　　　　　　順位　　営コード　 実績
2　　　 　200 　　1 　　5 　500
3 　　400 　　2 　　3 　400
4 　　300 　　3 　　4 　300
5 　　500 　　4 　　4 　300
6 　　300 　　5 　　4 　300
7 　　 300

順位3、4、5位は、それぞれ4,6,7の営業所コードを表示したいのですが、どうしたらいいでしょうか。
Ｆ列には、Ｆ２=LARGE(B:B,ROW(A1))
Ｅ列には、Ｅ２=INDEX(A:A,MATCH(F2,B:B,0))

営業所数は100程あり、ピボットテーブルを使用せずに作成したいです。
色々条件がついてますが、よろしくお願い致します。

回答No.2

noname#204879 · ベストアンサー

式を複雑にしないための別解です。

添付図参照

C2: =B2-ROW()/10000
E2: =INDEX($A:$C,MATCH(LARGE($C:$C,$D2),$C:$C,0),COLUMN(A1))
F2: セル E2 をドラッグ＆ペースト

お礼

シンプルでとてもわかりやすいです。

ありがとうございます。

回答No.5

kagakusuki

回答番号ANo.4です。
　先程の回答における、D2セルに入力する数式に関してですが、そのままでも特に問題はありませんが、

=IF(ROWS($2:2)>COUNT($B:$B),"",ROWS($2:2))

とした方が、若干、数式が短くなります。

　又、E2セルに入力する数式を、次の様な数式に変えれば、作業列を設けずとも（Sheet2のA列に数式を入力しなくても）、関数のみで抽出する事が出来ます。（但し、表の行数が増えると、計算処理の際の負荷が大きくなります）

=IF(ISNUMBER($D2),INDEX($A:$A,SUMPRODUCT(ROW(OFFSET($B$1,1,):INDEX($B:$B,MATCH(9E+99,$B:$B)))*(OFFSET($B$1,1,):INDEX($B:$B,MATCH(9E+99,$B:$B))=$F2)*(COUNTIF(OFFSET($B$1,,,ROW(OFFSET($B$1,1,):INDEX($B:$B,MATCH(9E+99,$B:$B)))-ROW($B$1)+1),$F2)=COUNTIF($F$1:$F2,$F2)))),"")

お礼

今回は営業所の順位表なので、100前後程です。
営業担当者の順位表が、もし今後必要な場合は、
SHeet2を使用した方が速そうですね！

ありがとうございます。

回答No.4

kagakusuki

配列数式を使わずに1000行以上ある場合にも対応する方法です。
　今仮に、売上実績表や順位表が存在しているシートがSheet1で、Sheet2のA列を作業列として使用するものとします。

　まず、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet1!$B:$B,ROW())),RANK(INDEX(Sheet1!$B:$B,ROW()),Sheet1!$B:$B)+COUNTIF(Sheet1!$B$1:INDEX(Sheet1!$B:$B,ROW()),INDEX(Sheet1!$B:$B,ROW()))/COUNTIF(Sheet1!$B:$B,INDEX(Sheet1!$B:$B,ROW())),"")

　次に、Sheet2のA1セルをコピーして、Sheet2のA2以下に貼り付けて下さい。
　次に、Sheet1の
D1セルに　　順位
E1セルに　　営業所コード
F1セルに　　実績
と入力して下さい。
　次に、Sheet1のD2セルに次の数式を入力して下さい。

=IF(ROWS($D$2:$D2)>COUNT($B:$B),"",ROWS($D$2:$D2))

　次に、Sheet1のE2セルに次の数式を入力して下さい。

=IF(ISNUMBER($D2),INDEX($A:$A,MATCH(SMALL(Sheet2!$A:$A,$D2),Sheet2!$A:$A,0)),"")

　次に、Sheet1のF2セルに次の数式を入力して下さい。

=IF(ISNUMBER($D2),LARGE($B:$B,$D2),"")

　次に、Sheet1のD2～F2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。

お礼

kagakusukiさん
ありがとうございます。

いろいろな方法があるんですね！
私ももっと勉強しようと思いました。

回答No.3

imogasi

配列数式を使わないために作業列を使うと
例データ
Ｃ列は作業列　式　　=RANK(I2,$I$2:$I$100)
営コード 実績 順位
2 200 11
3 400 2
4 300 3
5 500 1
6 300 4
7 300 5
8 300 6
9 300 7
10 300 8
11 300 9
12 300 10
Ｉ列に
=(B2&(1000-COUNTIF($B$2:B2,B2)))*1
を入れて下方向に式複写（下記Ｉ列）
Ｅ列は連続データで作成
Ｆ列は　=INT(LARGE(I:I,ROW(A2)-1)/1000)　または　=INDEX(B:B,MATCH(LARGE(I:I,E2),I:I,0),1)
Ｇ列は　=INDEX(A:A,MATCH(LARGE(I:I,E2),I:I,0),1)
それぞれ下方向に式を複写する。
Ｅ列ーＩ列
順位 実績 営コード 作業列
1 500 5 200999
2 400 3 400999
3 300 4 300999
4 300 6 500999
5 300 7 300998
6 300 8 300997
7 300 9 300996
8 300 10 300995
9 300 11 300994
10 300 12 300993
11 200 2 300992

お礼

配列関数を使わなくてもできるんですね！

ありがとうございます。
とても勉強になりました。

回答No.1

keithin

Ｆ２は今のまま
Ｅ２：
=IF(F2="","",INDEX(A:A,SMALL(IF($B$2:$B$200=F2,ROW($B$2:$B$200)),COUNTIF($F$2:F2,F2))))
と記入し，コントロールキーとシフトキーを押しながらEnterで入力，以下コピー。

お礼

keithinさん

早速の回答ありがとうございます。

期待通りの順位表ができました。
本当にありがとうございます。