エクセルで順位表を並び替えたい。同順位表示方法は？

エクセルで順位表を別シートに並び替えたいと考えていますが、同点になった場合はどうしたらよいでしょうか？

シート１で集計した結果を参照し、シート２に上から順に1位から表示させる表を作りたいです。

試行錯誤の結果、下記まで作業しました。
＝＝＝＝＝＝＝＝＝
＜シート１＞
　　　 A 　　　　　　　B 　　　　　　C 　　　　　　
　　 (1)商品名　　(2)販売額　　(3)ランキング　
1　　　　a 　　　 1000 　　　　　　2
2 　 b 　　　　1500　　　　　　1
3 　c 　　　　1000　　　　　　2
4 　d　 　　 500　　　　　　4
5 　e 　　　 300　　　　　　5

(1)手入力
(2)ＳＵＭ関数で集計した値
(3)ＲＡＮＫ関数

＜シート２＞
　　　　Ａ　　　　　　　　Ｂ　　　　Ｃ
(3) ランキング　　(1)商品名　(2)販売額
1
2
3
4
5

(3)手入力
(2)ＩＮＤＥＸとＭＡＴＣＨ関数
(3)ＩＮＤＥＸとＭＡＴＣＨ関数
＝＝＝＝＝＝＝＝＝

１）シート２の(3)ランキングをシート１を参照し降順で自動表示させる方法を教えてください。
今は手入力なので、同順位があった場合にずれが生じてしまいます。

２）シート２に同順位表示のまま並び替えする方法を教えてください。
ＩＮＤＥＸ関数とＭＡＴＣＨ関数を使っていましたが、
シート１で同順位表示されていた箇所が、シート２で表示できませんでした。

どうぞ宜しくお願いいたします。

No.4 ベストアンサー 回答者： keithin 回答日時： 2014/02/09 10:37

順位の数字に細工をしておくだけで、簡単にできます。

添付図：
Ｃ２に
=RANK(B2,B:B)+ROW()/1000
と記入、カンマ区切りの書式設定を取り付けて以下コピーしておく

これで同順も違う数字が計算できるので、あとはINDEXとMATCHで持ってくるだけです。


Ｅ２：
簡単に１番からの連番を記入
ROW関数とか使ってもいいですが、手で記入するので十分

Ｆ２：順位の数字の小さい順に取ってくる
=INDEX(A:A,MATCH(SMALL($C:$C,$E2),$C:$C,0))
右にコピー、下にコピー。


実際に手を動かして、できることを確認してください。

この回答へのお礼

keithinさん、どうもありがとうございます！
最初に自分で考えてできなかった案に一番近かったので、スムーズに直せました！

私はシート１とシート２の順位がMATCHしたときに値を引っ張ってくるようにしたのが間違っていたんですね。
順位の小さい順に値を引っ張ってくれば同じことだと、「なるほど！」と思いました。

どうもありがとうございました。

お礼日時：2014/02/09 12:01

No.8 回答者： kagakusuki 回答日時： 2014/02/11 18:47

<br /> 　回答No.7の続きです。<br /> <br /> <br /> 　従いまして、<br /> <br /

INDEX(Sheet1!$C:$C,IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1))

という部分は

INDEX(Sheet1!$A:$A,「Sheet1のA51セルに表示されている順位である6が、『この関数が入力されている1つ上のセルであるSheet2のB50セルに入力されている商品名"d"、と同じ商品名が入力されているSheet1の48行目』よりも下の行において初めて現れているSheet1のC50セルの行番号」)

と同じ事になりますから、

INDEX(Sheet1!$A:$A,MATCH($A51,INDEX(Sheet1!$C:$C,IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1))
　　↓
INDEX(Sheet1!$A:$A,MATCH(6,INDEX(Sheet1!$C:$C,IF(6=6,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF(6=6,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1))
　　↓
INDEX(Sheet1!$A:$A,MATCH(6,INDEX(Sheet1!$C:$C,IF(TRUE,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF(TRUE,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1))
　　↓
INDEX(Sheet1!$A:$A,MATCH(6,INDEX(Sheet1!$C:$C,MATCH(B50,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+MATCH(B50,Sheet1!$A:$A,0))
　　↓
INDEX(Sheet1!$A:$A,MATCH(6,INDEX(Sheet1!$C:$C,MATCH("d",Sheet1!$A:$A,0)+1):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+MATCH("d",Sheet1!$A:$A,0))
　　↓
INDEX(Sheet1!$A:$A,MATCH(6,INDEX(Sheet1!$C:$C,48+1):INDEX(Sheet1!$C:$C,1048576),0)+48)
　　↓
INDEX(Sheet1!$A:$A,MATCH(6,Sheet1!$C$49:$C$1048576,0)+48)
　　↓
INDEX(Sheet1!$A:$A,MATCH(6,Sheet1!$C$49:$C$1048576,0)+48)
　　↓
INDEX(Sheet1!$A:$A,2+48)
　　↓
INDEX(Sheet1!$A:$A,50)
　　↓
Sheet1!$A$50
　　↓
　　"f"

という事になり、Sheet1おいて、「商品名」欄が「d」、「ランキング」欄が2になっている行の次に、「ランキング」欄の所に2が現れている行である、Sheet1の50行目の所に入力されている商品名「f」という値を返す事になります。




　処で、御質問の内容が「同順位表示方法は？」となっておりましたので、販売額が同じものに関しては、順位を表す数字が同じになっている事を質問者様は望んでおられるものと考えていたのですが、もし、回答No.4様の方法の様な「販売額が同じであっても、順位を示す数は必ずしも同じでなくとも良い」という方法でも良いという事でしたら、Sheet1のC45セルに入力する関数を次の様なものにされた方が、順位が整数値になりますので、解りやすくなると思います。

=IF(ISNUMBER($B45),RANK($B45,$B:$B)+COUNTIF($B$44:$B44,$B45),"")

　その場合、Sheet2のB列の関数は、回答No.4様の方法にある様な関数でも構いませんが、次の様な関数とする事も可能になります。(計算処理の負荷が僅かに軽くなります)

=IF(ISNUMBER($A45),IF(COUNTIF(Sheet1!$C:$C,$A45),INDEX(Sheet1!$A:$A,MATCH($A45,Sheet1!$C:$C,0)),""),"")

No.7 回答者： kagakusuki 回答日時： 2014/02/11 18:47

<br /> 　回答No.6の続きです。<br /> <br /> <br /> 　従いまして、<br /> <br /

5,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44)))

という部分は、「Sheet2において、同じ行に表示されている順位は前にも現れた事がある」場合には、

INDEX(Sheet1!$C:$C,MATCH(B44,Sheet1!$A:$A,0)+1)

と同じ事になり、「Sheet1の中で『Sheet2の1つ上の行の所に表示されているデータ』が入力されている行の1つ下の行」の中のC列（「ランキング」欄）のセルを指定する事になります。
　この処理は「同じ行内のA列に表示されている値(順位を示す数値)が、1つ上の行内のA列に表示されている値と同じである」場合においてのみ行われるのですから、結局、「Sheet1の中で、順位が『Sheet2の同じ行内に表示されている順位』と同じで、商品名が『Sheet2における1つ上の行に表示されている商品名』と同じである行の中のC列（「ランキング」欄）セル」の1つ下のセルを指定する事になります。
　例えば、Sheet1に

＜Sheet1＞
　　　 A 　　　　　B 　　　　　　 C 　　　　　　
44　商品名　　販売額　　ランキング　
45 　　a　　　　1000 　　　　　　2
46　 　b　　　　1500 　　　　　　1
47 　　c　　　　1000 　　　　　　2
48 　　d　　　　　500 　　　　　　6
49 　　e　　　　　300 　　　　　 10
50 　　f　　　　　500 　　　　　　6
51 　　g　　　　　600 　　　　　　4
52 　　h　　　　　500 　　　　　　6
53 　　i　　　　　400 　　　　　　9
54 　　j　　　　　500 　　　　　　6
55 　　k　　　　　800 　　　　　　5

の様に元データが並んでいるものとした場合には、Sheet2の表示は

＜Sheet2＞
　　　　A　　　　　　　　B　　　　　C 　　　　　　
44　ランキング　　商品名　　販売額　
45　　　1　　　　 　　　b　　　　1500
46　　　2　　　　 　　　a　　　　1000
47　　　2　　　　 　　　c　　　　1000
48　　　4　　　　 　　　k　　　　　800
49　　　5　　　　 　　　g　　　　　600
50　　　6　　　　 　　　d　　　　　500
51　　　6　　　　 　　　f　　　　　500
52　　　6　　　　 　　　h　　　　　500
53　　　6　　　　 　　　j　　　　　500
54　 　10　　　　 　　 i　　　　　400
55　 　11　　　　 　　 e　　　　　300

となりますが、この場合、例えば、Sheet2のB50セルとB51セルに表示されている順位は共に6で、同じ順位となっています。
　そして、Sheet2のB51セルに入力されている関数は、

=IF(ISNUMBER($A51),INDEX(Sheet1!$A:$A,MATCH($A51,INDEX(Sheet1!$C:$C,IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1)),"")

となっています。
　その中の

IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))

という部分は、

IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))
　　↓
IF(6=6,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))
　　↓
IF(TRUE,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))
　　↓
MATCH(B50,Sheet1!$A:$A,0)+1
　　↓
MATCH("d",Sheet1!$A:$A,0)+1
　　↓
　48+1
　　↓
　　49

という事で、「『Sheet2のB51セルの1つ上のセルであるSheet2のB50セル』に入力されている商品名"d"」と同じ商品名が入力されているSheet1のA48セルの行番号を求めて、それに1を加える事で、そのまた1つ下のセルであるSheet1のA49セルの行番号である49という値を求めている事になります。
　つまり、

MATCH($A51,INDEX(Sheet1!$C:$C,IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)

という部分は「Sheet2において、同じ行に表示されている順位は前にも現れた事がある」場合において、

MATCH($A51,Sheet1!$C$49:$C$1048576,0)

と同じ事になり、「この関数が入力されているセルと同じ行の中のA列に表示されている順位である6が、『この関数が入力されている1つ上のセルであるSheet2のB50セルに入力されている商品名"d"、と同じ商品名が入力されているSheet1のA48セル』よりも下の行において初めて現れる位置」である2という値を求める関数となります。
　但しこの「位置」を示す値である2とは、あくまで「『この関数が入力されている1つ上のセルであるSheet2のB50セルに入力されている商品名"d"、と同じ商品名が入力されているSheet1のA48セル』の1つ下の行であるSheet1の49行目」から数えた位置を示すものに過ぎず、行番号そのものではありません。
　そこで、その後に付けた

IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1)

という部分が返す値を加える事で、返される値がSheet1のC51セルの行番号になる様にしている訳です。



※そろそろ、このサイトの回答欄における入力可能文字数の制限を超えそうですので、残りは又後で回答させて頂きます。

No.6 回答者： kagakusuki 回答日時： 2014/02/11 18:47

<br /> 　回答No.5の続きです。<br /> <br /> <br /> 　一方、<br /> <br />

H(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44)))

という部分では、INDEX関数内の行番号を指定する所にIF関数やMATCH関数が入れ子になって入っており、条件によって行番号を指定する値が変化する様になっています。
　そのIF関数内の判定を行う部分には、

$A44=$A45

という判定式が入力されていますが、この関数はB45セルに入力されているのですから、「同じ行の中のA列に表示されている値(順位を示す数値)が、1つ上の行のA列に表示されている値と同じである」という場合には、IF関数の[真の場合]の処理へと進み、そうではない場合には[偽の場合]の処理へと進み事になります。
　Sheet2のA列においては、順位は昇順に並べ替えられて表示されていますから、「Sheet2において、同じ行に表示されている順位は前にも現れた事がある」場合には、必ず「同じ行の中のA列に表示されている値(順位を示す数値)が、1つ上の行のA列に表示されている値と同じである」という事になりますので、

$A44=$A45

という簡単な判定式で、「Sheet2において、同じ行に表示されている順位は前にも現れた事がある」のか否かを判定する事が出来る訳です。

　そして、IF関数内の[偽の場合]の所には

ROW(Sheet1!$A$44)

と入力されていますから、同じ行の中のA列に表示されている値(順位を示す数値)が、1つ上の行のA列に表示されている値とは異なっている」場合、即ち「同じ行に表示されている順位が、Sheet2において現れるのはこの行が最初である」という場合には、

INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44)))
　　↓
INDEX(Sheet1!$C:$C,IF(FALSE,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44)))
　　↓
INDEX(Sheet1!$C:$C,ROW(Sheet1!$A$44))
　　↓
INDEX(Sheet1!$C:$C,44)
　　↓
Sheet1!$C$44

という事になり、Sheet1!$C$44（Sheet2のにおいて、「ランキング」欄の中で最も上にあるセル）が指定されたのと同じ事になります。

　処で、INDEX関数を使用してセルを指定する方法を用いた場合、「A1」とか「B2」等の様にセル番号を直接指定するのと同様に、セル範囲の指定に使用する事が可能です。
　例えば、

INDEX($A$2:$D$5,3,4):INDEX(E6:Z99,2,3)

とした場合には、$D$4:G7というセル範囲を指定したのと同じ事になります。
　という事は、「同じ行に表示されている順位が、Sheet2において現れるのはこの行が最初である」場合においては、

INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C))

という部分は、Sheet1!$C$44:$C$1048576というセル範囲を表す事になります。
　つまり、「同じ行に表示されている順位が、Sheet2において現れるのはこの行が最初である」場合においては、

MATCH($A45,INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)

という部分は、

MATCH($A45,Sheet1!$C$44:$C$1048576,0)

と同じ事になり、A45セルに入っている値、即ち「この関数が入力されている行と同じ行の所に表示されている順位」と同じ値が入力されているセルが、Sheet1!$C$44:$C$1048576の範囲の中で、上から何番目の所で初めて現れるのか、という事を返す関数となります。
　この値はあくまでSheet1!$C$44の所から数え始めた番号に過ぎませんから、この値を基にして「『この関数が入力されている行と同じ行の所に表示されている順位』と同じ値が入力されているSheet1のC列のセル」の行番号を求めるために、

+IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1)

という部分を付け加えています。
　「同じ行に表示されている順位が、Sheet2において現れるのはこの行が最初である」場合においては、このIF関数内の[偽の場合]の処理である

ROW(Sheet1!$A$44)-1

という処理へと進みますから、Sheet1のA44セルの行番号から1を差し引いた値である43が返される事になります。
　この値を先程の「『この関数が入力されている行と同じ行の所に表示されている順位』と同じ値が入力されているセルが、Sheet1!$C$44:$C$1048576の範囲の中で、上から何番目の所で初めて現れるのか」という事を表す数値に加えるのですから、「『この関数が入力されている行と同じ行の所に表示されている順位』と同じ値が入力されているSheet1のC列のセル」の行番号が返される事になります。
　従いまして、

INDEX(Sheet1!$A:$A,MATCH($A45,INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1))
　　↓
INDEX(Sheet1!$A:$A,「『この関数が入力されている行と同じ行の所に表示されている順位』と同じ値が入力されているSheet1のC列のセルの行番号」)

という事になり、Sheet1のC列において、その順位が初めて現れている行の所に入力されている商品名を示す値が返されるわけです。



　それに対して、「同じ行内のA列に表示されている値(順位を示す数値)が、1つ上の行内のA列に表示されている値と同じである」という場合、即ち、「Sheet2において、同じ行に表示されている順位は前にも現れた事がある」という場合には、

IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))

というIF関数の中の[真の場合]である

MATCH(B44,Sheet1!$A:$A,0)+1

という処理へと進む事になります。
　MATCH関数は

MATCH(検査値, 検査範囲, [照合の型])

という形式で記述される関数で、「照合の型」の所で0を指定しますと、「検査値」の所に入力されている値と完全に同じ値が入っているセルが、「検査範囲」の所で指定されたセル範囲の中で何番目の位置にあるのかを求める関数となります。
　この時、「検査値」の所に入力されている値と完全に同じ値が入っているセルが複数存在する場合には、「検査値」の所に入力されている値と完全に同じ値が入っているセルの内、一番最初に現れたセルの位置が返されます。
　B44セルとは、1つ上の行の「商品名」欄のセルですから、

MATCH(B44,Sheet1!$A:$A,0)

という部分では、Sheet2において1つ上の行の所に表示されている商品名が、Sheet1のA列の中で最初に現れているセルの行番号が返される事になります。

>Sheet1の商品名欄に、同一の商品名が複数のセルに重複して入力されている様な事は無い

という条件がありますから、「Sheet2において1つ上の行の所に表示されている商品名が、Sheet1のA列の中で最初に現れているセルの行番号」は、必ず「Sheet1の中で『Sheet2の1つ上の行の所に表示されているデータ』が入力されている行の行番号」と一致する事になりますので、

>Sheet1の商品名欄に、同一の商品名が複数のセルに重複して入力されている様な事は無い

という条件が守られている限りにおいては、「商品名」とMATCH関数を組み合わせる事で、Sheet1における特定の行を指定する事が出来る訳です。
　そして、

MATCH(B44,Sheet1!$A:$A,0)+1

という具合に、後ろに「+1」が付いているという事は、「Sheet1の中で『Sheet2の1つ上の行の所に表示されているデータ』が入力されている行の1つ下の行の行番号」を求めている事になります。



※そろそろ、このサイトの回答欄における入力可能文字数の制限を超えそうですので、残りは又後で回答させて頂きます。

No.5 回答者： kagakusuki 回答日時： 2014/02/11 18:47

<br /> 　回答No.2です。<br /> <br /> <br /> >お手数ですが、それぞれの関数の指示の意味を教

> との御要望でしたので、追加の回答を投稿しようとしていたのですが、丁度、回答を回答欄に入力していた最中の2月9日の12時前後に質問が締め切られてしまいましたので、サイトのサポート様に連絡を入れて、回答を追加させて頂きました。



>なお、自分が今作業しているのは、ＡＢＣ列の45行～63行に該当する範囲です。

　45行とはずいぶん下の行から始まっていて、少々妙な気も致しますが、取り敢えずその事は置いておいて、44行目には項目名等が入力されているか、或いは空欄となっていて、実際の商品のを示す文字列データや名称や金額、順位等の数値データが入力されているのは45行～63行の範囲であるものとして回答致します。
　その場合は、Sheet2のA45セルの関数は次の様なものとなります。

=IF(ROWS($45:45)>COUNT(Sheet1!$C:$C),"",SMALL(Sheet1!$C:$C,ROWS($45:45)))

　又、Sheet2のB45セルの関数は次の様なものとなります。

=IF(ISNUMBER($A45),INDEX(Sheet1!$A:$A,MATCH($A45,INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1)),"")

　又、Sheet2のC45セルの関数は次の様なものとなります。

=IF(ROWS($45:45)>COUNT(Sheet1!$B:$B),"",LARGE(Sheet1!$B:$B,ROWS($45:45)))




>お手数ですが、それぞれの関数の指示の意味を教えていただけませんか？

　まず、Sheet2のA45の関数に関してですが、

ROWS($45:45)

という部分はROWS関数を使用して、関数が入力されているセルが存在している行は「結果を表示する行の範囲において、何行目にあたる行」であるのかを求めております。（行番号が45の行の場合は1、行番号が46の行の場合は2という値が返る事になります）
　ですから、もし、Sheet1とSheet2で、実際のデータが始まっている行が異なっている場合には、このROWS関数の中で指定している行範囲は、Sheet2の方に合わせる様にして下さい。

　次に、

COUNT(Sheet1!$C:$C)

という部分は、Sheet1のC列の中で、数値が入力されているセルが何個あるのかをカウントする関数です。
　Sheet1のA列～C列においてデータが入力されている行の所では、必ずC列の所に順位を表す数値が入っているのですから、

COUNT(Sheet1!$C:$C)

とする事で、Sheet1においてデータが入力されている行が何行あるのかを求めている訳です。
　そして、IF関数の中の判定を行う箇所を

ROWS($45:45)>COUNT(Sheet1!$C:$C)

とする事で、その行が、Sheet1においてデータが入力されている行数を超えているのかどうかを判定し、データが入力されている行数を超えていない場合には、

SMALL(Sheet1!$C:$C,ROWS($45:45))

という処理を行う様にしています。
　一方、データが入力されている行数を超えている場合には、その行よりも上の行の所までで既に全てのデータが表示され尽くされていて、その行の所で表示しなければならない様なデータは存在していない事になりますから、IF関数の「真の場合」の所で指定されている処理である、空欄表示が行われます。

　SMALL関数は、

SMALL(範囲, 順位)

という形式で記述される関数で、「範囲」の所で指定されたセル範囲内に入っている数値の中から、「順位」番目に小さな数値を返す関数です。

SMALL(Sheet1!$C:$C,ROWS($45:45))

という箇所においては、「順位」の所には前述の「結果を表示する行の範囲において、何行目にあたる行」であるのかを求めるための

ROWS($45:45)

が入っていますから、

SMALL(Sheet1!$C:$C,ROWS($45:45))

という箇所では、Sheet1のC列、即ち「ランキング」欄において、セルに入っている「順位を表している数値」を小さい順に表示する事になる訳です。



　Sheet2のC45の関数もROWS関数やCOUNT関数を使用している意味は、Sheet2のA45の関数と同様ですので、その部分に関する説明は割愛します。
　Sheet2のC45の関数の構造において、Sheet2のA45の関数と異なっている部分は

LARGE(Sheet1!$B:$B,ROWS($45:45))

という部分です。
　LARGE関数は、

LARGE(範囲, 順位)

という形式で記述される関数で、「範囲」の所で指定されたセル範囲内に入っている数値の中から、「順位」番目に大きな数値を返す関数です。

LARGE(Sheet1!$B:$B,ROWS($45:45))

という箇所においては、「順位」の所には前述の「結果を表示する行の範囲において、何行目にあたる行」であるのかを求めるための

ROWS($45:45)

が入っていますから、

LARGE(Sheet1!$B:$B,ROWS($45:45))

という箇所では、Sheet1のB列、即ち「販売額」欄において、セルに入っている「順位を表している数値」を大きい順（「ランキング」欄の数値が小さい順と同じ順番）に表示する事になる訳です。



　次にSheet2のB45セルの関数

=IF(ISNUMBER($A45),INDEX(Sheet1!$A:$A,MATCH($A45,INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1)),"")

に関してですが、IF関数の判定を行う箇所の所にある

ISNUMBER($A45)

という部分は、Sheet2のA列に数値が表示されている場合においてTRUEを返し、A列に何も入力されていない場合や、文字列が入力されている場合には、FALSEを返す関数となっています。
　ですから、Sheet2の「ランキング」欄に順位が表示されている場合には、IF関数の「真の場合」の処理へと進み、順位が表示されていない場合には、「偽の場合」の処理である空欄表示が行われます。


　次に、

INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C))

という部分に関してですが、ROWS関数は指定されたセル範囲の高さ（セル範囲の上端から下端まで何行あるのか）を求める関数であり、

ROWS(Sheet1!$C:$C)

の部分では、範囲としてSheet1のC列全体を指定しているのですから、返される値はそのExcelbookで使用可能な行数と同じ数（Excel2007よりも前のバージョンの場合は65536行、Excel2007以降のバージョンの場合は1048576行）が返される事になりますので、結局、

INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C))

という部分はSheet1のC列の中で最も下にあるセルであるSheet1!$C$1048576(←EXCEL2007以降のバージョンの場合の話。Excel2007よりも前のバージョンの場合はSheet1!$C$65536)を指定している事になります。



※そろそろ、このサイトの回答欄における入力可能文字数の制限を超えそうですので、残りは又後で回答させて頂きます。

No.3 回答者： tom04 回答日時： 2014/02/09 08:58

こんにちは！

一例です。

↓の画像で左側がSheet1・右側がSheet2で、両Sheetの1行目項目名は同じにしておきます。

Sheet1に作業用の列を1列設けます。
画像ではE2セルに
=IF(C2="","",COUNTIF(B:B,">"&B2)+COUNTIF(B$2:B2,B2))
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてSheet2のB2セルに
=IF(A2="","",INDEX(Sheet1!$A:$C,MATCH($A2,Sheet1!$E:$E,0),MATCH(B$1,Sheet1!$1:$1,0)))
という数式を入れ、列・行方向にオートフィルでコピー！
これで画像のような感じになります。

※　同順位の場合は上側の行のデータが上位に表示されます。m(_ _)m

この回答へのお礼

tom4さん、どうもありがとうございます。

countif関数でもできるんですね！
知りませんでした。
いろんな方法があるのですね。

今回は作業列をできれば避けたかったのですが、
次回はこの方法も活用させていただきますね！

お礼日時：2014/02/09 12:06

No.2 回答者： kagakusuki 回答日時： 2014/02/09 05:20

　もし、Sheet1の商品名欄に、同一の商品名が複数のセルに重複して入力されている様な事は無い、という事が保障されている場合には、作業列を使わずに済む方法もあります。

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

=IF(ROWS($2:2)>COUNT(Sheet1!$C:$C),"",SMALL(Sheet1!$C:$C,ROWS($2:2)))

　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),INDEX(Sheet1!$A:$A,MATCH($A2,INDEX(Sheet1!$C:$C,IF($A1=$A2,MATCH(B1,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$1))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A1=$A2,MATCH(B1,Sheet1!$A:$A,0),ROW(Sheet1!$A$1)-1)),"")

　次に、Sheet2のC2セルに次の関数を入力して下さい。

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

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

　これで、Sheet2にランキング順に並び替えられたデータが表示されます。

この回答へのお礼

kagakusukiさん、具体的にお教えいただきありがとうございます。

＞もし、Sheet1の商品名欄に、同一の商品名が複数のセルに重複して入力されている様な事は無い、
＞という事が保障されている場合には、作業列を使わずに済む方法もあります。

⇒こちらの方法をイメージしていたので助かりました！

教えていただいた関数を入力を自分でエクセルにコピペしたら再現することができました！


ただ、いざ自分の困っていたシートにコピペし範囲等修正するにあたり、
一部教えていただいた関数の意味が自分の知識不足でわからず修正対応がうまくできません。。。
なお、自分が今作業しているのは、ＡＢＣ列の45行～63行に該当する範囲です。

お手数ですが、それぞれの関数の指示の意味を教えていただけませんか？

お礼日時：2014/02/09 06:24

No.1 回答者： kagakusuki 回答日時： 2014/02/09 04:54

　今仮に、Sheet3のA列を作業列として使用するものとします。

　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet1!$C:$C,ROW())),INDEX(Sheet1!$C:$C,ROW())+COUNTIF(Sheet1!$C$1:INDEX(Sheet1!$C:$C,ROW()-1),INDEX(Sheet1!$C:$C,ROW())),"")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(COUNTIF(Sheet3!$A:$A,ROWS($2:2)),INDEX(Sheet1!$A:$C,MATCH(ROWS($2:2),Sheet3!$A:$A,0),MATCH(A$1,Sheet1!$A$1:$C$1,0)),"")

　次に、Sheet2のA2セルをコピーして、Sheet2のB2～C2の範囲に貼り付けて下さい。
　次に、Sheet2のA2～C2の範囲をコピーして、同じ列範囲の3行目以下に貼り付けて下さい。

　これで、Sheet2にランキング順に並び替えられたデータが表示されます。