質問投稿でgooポイントが当たるキャンペーン実施中!!>>

得点表を元に順位(RANK関数)を出し名前順の表にして、
その順位表を元にVLOOKUP関数で順位順の表を作ってます。
この場合、順位順の表では同順位の人がいた場合、同順位2人目以降の人の名前が表示されません(#N/Aと表示されます)
同位の人も名前と得点をそのまま表示させたいのですが、何か方法は有りませんか?

現在↓

1位 Aさん 10点
2位 Bさん  9点
3位  #N/A  #N/A
4位 Dさん  8点
    ・
    ・
となります。それを

1位 Aさん 10点
2位 Bさん  9点
3位 Cさん  9点
4位 Dさん  8点

としたいです。
左の順位表記は書きこんでいるだけなので変化しません。もちろん順位に連動して変化してほしいですが、このままでも構いません。

Aさん・Bさんの各セルに=VLOOKUP(Q5,$U$4:$V$21,2,FALSE)のように設定しています。
ずいぶん前に本を見ながら設定したもので、理屈は忘れてます・・エクセル2000です。

このQ&Aに関連する最新のQ&A

A 回答 (10件)

こんにちは。


Wendy02 です。No.8 に対するコメントです。

他の方の回答を見てはいませんが、どうやら、私のほうは、振り出しです。

その数式をみた限りでは、もう、解答自体はよりも、失礼ですが、これは、基本的な問題ではないかと思いました。直接の解答というよりも、経験者としての回答をさせていただきます。

最初のご質問のようにVLOOKUP を使うことは、間違いないと思います。しかし、その関数を使用する場合は、検索に必要なもの/一意な(ユニークな)データを左端の格子状の1列目に持ってくるということは大前提です。

(もちろん、掲示板レベルで、他のワザをご披露する方もいるようですが、それはそういうテクニックであり、質問本位・データ本位の解答です。しかし、最初から、そういう作りにするのは、通常はありえないのです。)

順位を左端に以って表示するということ自体は問題ではなく、選び出すデータとして必要だということです。誤解なさらないでください。

つまり、以下の場合は、

  Q  R    S
1,名前  得点 順位 ←データの母体(データベース)
2,Aさん 10点  1位 

 変動しやすいもの、計算値は、右側に置きます。
 特定化する(ユニークなもの)は、左端になります。できない場合は、増加する数字でも可能です。

'---------------------------
21,順位 名前  得点  ←表示側は、自由です。
22,1位 Aさん 10点
  ・
  ・
  ・
'-------------------------------

私の前回の式で、VLOOKUPを使い、ユニークなものとして、「名前」を使っていたので、一部の式が完全に使えません。(INDEX関数とMATCH関数を使ってだせますが、最初からは作りません。)

次に、また基本的なことですが、

>=IF(RANK(D12,$S$2:$S$18)=COUNTIF($S$2:$S$18,">=" &D12),RANK(D12,$S$2:$S$18),COUNTIF($S$2:$S$18,">" & D12)+1)
>D12の部分が変わります。→F12,H12,J12・・J38,L38

>D12の部分が変わります。→F12,H12,J12・・J38,L38

 =RANK(数値,範囲)

$S$2:$S$18 の範囲の数値をランキングで出すのが基本です。それを別のところのデータを使うと、万が一には、そのランキングはめちゃめちゃにならないかと思います。

 =RANK(S2,$S$2:$S$18)

あくまでも、範囲の中の数値である必要があると思います。以下の式は、ROW のカッコの中の範囲を変えればよいのですが。

=IF(RANK(S2,$S$2:$S$18)=COUNTIF($S$2:$S$18,">=" &S2),RANK(S2,$S$2:$S$18),COUNTIF($S$2:$S$18,">" & S2)+1)

もしも、元データが点在する場合でも、表に作る場合は、必ず規則性があるはずです。それを、OFFSET 関数やINDIRECT関数によって、ひとつにまとめ合わせることが可能なはずです。

例:
点数が点在している場合のまとめ方
(ただし、実際のデータに当てはめていません)
=INDIRECT(ADDRESS(18+INT(ROW(A1)/6)*10,4+MOD((ROW(A1)-1)*2,8)))

以上が、その元のならびに関するコメントです。
今回、私の回答自体は見送りで構わないのですが、基本的な作りは大事にしたほうがよいですね。

*******

エラー自体については、以下のようにすればよいはずです。ROW($A$2:$A$18)というのは、行データですから、それは、行があっていなくては、取り出せません。

順位 /Q22 ~で、

=INDEX($Q$2:$S$18,MOD(SMALL(INDEX($Q$2:$Q$18+ROW($A$2:$A$18)/100,,),ROW(A1)),1)*100-1,1)

名前  /R22~

=INDEX($Q$2:$S$18,MOD(SMALL(INDEX($Q$2:$Q$18+ROW($A$2:$A$18)/100,,),ROW(B1)),1)*100-1,2)

ここは、VLOOKUPでも可
点数 /S22 ~
=INDEX($Q$2:$S$18,MOD(SMALL(INDEX($Q$2:$Q$18+ROW($A$2:$A$18)/100,,),ROW(C1)),1)*100-1,3)

これは、値コピーして、並べ替えすれば、式はいらないはずですよね。私自身は、あまり難しい式などは使わないのです。手作業で出来る部分は、そのまま手作業で作ります。
    • good
    • 0
この回答へのお礼

度々ありがとうございます。くわしくレクチャーいただき大変勉強なります。(完全に理解できる訳では無いですが)、理解できない者に教えるのは大変ですよね・・
「ユニークなデータ」や規則性データの抽出は参考にして何とか克服したいです。

ちなみに、***以下で思った通り表示しますので、個人的には満足です。
お時間とらせて申しわけ有りませんでした。
ありがとうございました。

お礼日時:2005/08/07 16:25

No.7です。



補足を見ましたが、おっしゃっている意味がわかりません。
要は、元の表がQ列(順位)・R列(名前)・S列(得点)なんですよね。
No.7の補足では「18人分」、No.8の補足では「17人分」となっていますが、どちらなんでしょうか?
一応、17人分と考えて、1行目が見出し、2行目から18行目までデータが入っているとします。

Q2に

=RANK(S2,$S$2:$S$18)

と入れて、Q18までフィルコピー

T2に

=Q2+ROW()/100

と入れて、T18までフィルコピー

> D列の設定ははT列22以下にしたのですが、

どういうことでしょうか?

> 3行ほど余分に設定しないとランク表、
> 上の3行分が####状態になります。
> その3行を消しても表示してくれるので現状でもいいのですが。。

この↑意味もさっぱりわかりません。
こちらで検証しましたが、何の問題もありませんでした。

ランク表がどの列の何行目かに関係なく
順位を表示させる列の先頭行のセルに、

=INDEX(Q$2:Q$18,MATCH(SMALL($T$2:$T$18,ROW(A1)),$T$2:$T$18,0))

と入れて、右に計3列分フィルコピー
そのまま(3列が選択された状態で)下に計17行分フィルコピー

元の表がQ列・R列・S列で、データが2行目から18行目まで。
T列の2行目から18行目までに、上記2番目の数式が入っているのなら、
上の数式は一切(!)変更しないでください。そのままコピーして貼り付けてください。
ROW(A1) もそのまま ROW(A1)です。(←何行目かに関係なく)

それでもうまくいかないのならもっと具体的に補足してください。
・元の表はQ2:S18でまちがいないですか?
・ランク表は、どの列の何行目から表示させたいのですか?
・実際に入力した数式を、そのままコピーして提示してください。

この回答への補足

再度、ご回答ありがとうございます。
上記の通りやりましたら問題なく表示できました。

>3行ほど余分に設定しないとランク表・・
>この↑意味もさっぱりわかりません。
の部分は自分でもどう説明したものか、わかりませんでした。すみません。

> D列の設定はT列22以下にしたのですが、
これは、Q22:S38にランク表を作ったため、
その横のT列22以下に、No.7の回答にあった
>D1に
>=A1+ROW()/100
>と入れて、下にフィルコピー
を設定すればよいのかと思った為です。

17人と18人の違いは、質問当初は元々あった表を改良するつもりでした。その表が、余分も含めて18人分の表だったのです。たくさん回答いただいたためすべて新規に作ってみようと思い、今現在、実際に必要な17人分で作ってます。そのため混同したようです。すみません。

いたらない点察していただき、適切な回答いただきありがとうございました。

補足日時:2005/08/07 00:53
    • good
    • 0

こんにちは。



#2 のWendy02 です。

>1位 Aさん 10点
>2位 Cさん  9点
>2位 Dさん  9点
>4位 Bさん  8点
>と言う表記になるようには出来ますでしょうか?

#2 の表を再び使います。

元のデータが以下のような状態であるとします。

例:
元データ
 G   H   I 
名前  点数  順位
Dさん  8   5
Fさん  9   2
Eさん  4   8
Aさん  10   1
Cさん  9   3
Uさん  5   7
Lさん  6   6
Bさん  9   4
Mさん  4   9

'------------

ランク表

 A   B    C
順位  名前  点数
 1位  Aさん  10
 2位  Fさん  9
  ・
  ・
'-----------------

I2:~
=IF(RANK(H2,$H$2:$H$10)=COUNTIF($H$2:$H$10,">=" &H2),RANK(H2,$H$2:$H$10),COUNTIF($H$2:$H$10,">" & H2)+1)


B2:~
=INDEX($G$2:$I$10,MOD(SMALL(INDEX($I$2:$I$10+ROW($A$2:$A$10)/100,,),ROW(A1)),1)*100-1,1)

解説:

ROW(A1)),1)*100-1 の -1 は、タイトル行を差し引いています。
SMALLの中で、INDEX を使うのは、データを配列として扱うからです。そのままですと、+ROW($A$2:$A$10) という式は、配列にさせないと、不活性のままなのです。

重み付けテクニック:

式の中で、/100 と100で割るのは、重み付けというワザ名で知られた方法です。元のデータと行データをいっしょに扱うときに、使う方法です。元のデータと行数をいっしょにして、SMALL で並べ替えをして、後で、分離させて、小数点以下を取り出し、×100 にしています。100行以内だから、÷ 100 ですが、100を超えるデータの時は、÷ 500 とか÷1000 とかして、データと交じり合わないようにするのがコツです。

A2:~
=VLOOKUP(B2,$G$1:$I$10,3,0)

C2:~
=VLOOKUP(B2,$G$1:$I$10,2,0)

この回答への補足

度々ありがとうございます。
せっかくだから新しく作り直しているのですが、17人分の元データ表とランク表をQ(順位)・R(名前)・S(得点)各列にたてに並べて設置したいのですが、ランク表全体が#N/Aとなります。参照セルの書き換えが間違っているのでしょうね。
Q2~18に
=IF(RANK(D12,$S$2:$S$18)=COUNTIF($S$2:$S$18,">=" &D12),RANK(D12,$S$2:$S$18),COUNTIF($S$2:$S$18,">" & D12)+1)
D12の部分が変わります。→F12,H12,J12・・J38,L38

R22以下
=INDEX($Q$2:$S$18,MOD(SMALL(INDEX($S$2:$S$18+ROW($Q$23:$Q$41)/100,,),ROW(Q1)),1)*100-1,1)

お時間ございましたらよろしくお願いします。

補足日時:2005/08/06 22:05
    • good
    • 0
この回答へのお礼

お礼欄ですが再度補足です。
元データはQ1:S18に、一番上は見出しです。
ランク表はQ21:S38に表示してます。
  Q  R   S
1,順位 名前 得点
2,1位 Aさん 10点
     ・
     ・
21,順位 名前  得点
22,#N/A #REF! #N/A
     ・
     ・
38,#N/A #NUM! #N/A

と言った状況です。
この場合R:38では=INDEX($Q$2:$S$18,MOD(SMALL(INDEX($S$2:$S$18+ROW($Q$22:$Q$38)/100,,),ROW(Q18)),1)*100-1,1)となっています。
・・・ROW(Q18)),・・・を
ROW(A1)),やROW(A18)),にしてみますがダメです。表記は#RUF!になったりします。

B1:O38には変動する得点計算の為の一人づつの別の表があります。
この上の補足にある
>D12の部分が変わります。→F12,H12,J12・・J38,L38
と言うのはその合計点が表示されているセルです。

No.9さんの方法で上手く表示できましたが、
Wendy02さんの方法も完成させたいなと言う気持ちもあり、お時間ございましたら教えていただけるとうれしいです。よろしくお願いします。

関数の意味が理解できず、説明も下手で、すみません。

お礼日時:2005/08/07 02:21

こんにちは~



> 同点の場合、
> 1位 Aさん 10点
> 2位 Cさん  9点
> 2位 Dさん  9点
> 4位 Bさん  8点
> と言う表記になるようには出来ますでしょうか?

別解です。
参照している元表のレイアウトがわかりませんので、A列が「順位」、B列が「名前」、C列が「点数」だとします(元の表です)。

A1に

=RANK(C1,$C$1:$C$20)

と入れて、下にフィルコピー

D1に

=A1+ROW()/100

と入れて、下にフィルコピー

ランク順の表を、F列からH列だとして、

F1に、

=INDEX(A$1:A$20,MATCH(SMALL($D$1:$D$20,ROW(A1)),$D$1:$D$20,0))

と入れ、右にH1までフィルコピー(←←右方向にコピーです!)
そのまま(F1からH1が選択された状態で)下に人数分フィルコピー(F列からH列まで同時に数式をコピー)

=INDEX(A$1:A$20・・、のように A$1:A$20 を行だけ絶対参照にしておけば、列ごとに別々の数式を入れる必要はありません。
上の数式を右に下にコピーするだけです。

順位を「1位」、点数を「10点」のようにする場合は、
「セルの書式設定」のユーザー定義で、F列の表示形式を 0位、H列の表示形式を 0点としてください。

この回答への補足

ありがとうございます。
何とか表示は出来るのですが、ランク表に項目「順位」「名前」「得点」を上に書くと表全体が#VALUE!になってしまいます。
元の表・ランク表をQ・R・S列に縦に18人分の表として並べて作ってます。D列の設定ははT列22以下にしたのですが、3行ほど余分に設定しないとランク表、上の3行分が####状態になります。その3行を消しても表示してくれるので現状でもいいのですが。。

補足日時:2005/08/06 21:42
    • good
    • 0

#3です。

#3の例のB列は参考までにあげたものです。
C列のようにすれば、順位という意味で各人固有の番号を振れるといったまでです。部分的な参考にしてほしい解答でした。
そもそも質問のしたいことがよく分からない。
VLOOKUP表の作り方で、今度はうまくいかないかもしれない。
>得点表を元に順位(RANK関数)を出し
得点で順位数字を出しでしょう。その場合氏名は同行にあるのでしょう。
Aさん 7点 3(順位)
Bさん 8  2
Cさん 7  3
Dさん 6  4
Eさん 9  1
>名前順の表にして
「順」とは名前文字コード順に並べるという意味ですが、別の意味で使ってませんか。これが質問が分かりにくい原因です。
この段階で何(の項目)をVLOOKUPでとってくるのでしょうか?
順位の順に(ソートをつかわず、関数で)並べ替えたいということですか。
a15501
b23301
c31101
d15502
e23302
f31102
の場合
A11に
=OFFSET($A$1,MATCH(SMALL($D$1:$D$6,ROW(A11)-10),$D$1:$D$6,0)-1,0)
と入れてA16まで複写すると
A列  B列(B列式は下記後述)
c3
f3
b2
e2
a1
d1

と並びます。
点数はB11に
=OFFSET($A$1,MATCH(SMALL($D$1:$D$6,ROW(B11)-10),$D$1:$D$6,0)-1,1)
で出ます。B12:B16に式を複写する。(結果上記のとおり)
こういうことでよいでしょうか。

この回答への補足

>得点表を元に順位(RANK関数)を出し
>名前順の表にして
>この段階で何(の項目)をVLOOKUPでとってくるのでしょうか?
>順位の順に(ソートをつかわず、関数で)並べ替えたいということですか。

実は、
1、最初に一人一人に誰が何点取ったか合計を出すだけの14x14マスの表を18人分程A列~N列・1行~88行目ぐらいの間に作る(とりあえず満足)
2、順位を出すためW列に得点一覧の表を作り一人一人の表の合計点の下のセルにランクが出るようにする。例=RANK($N$13,$W$4:$W$21)、U・V列にはセル参照で順位と名前を表示(とりあえず満足)
3、W列を参照してグラフを作る(とりあえず満足)
4、一目で順位・得点がわかる表がほしい、U・V・W列を利用してVLOOKUPの表を作る(#N/Aが出るがとりあえず満足)
5、得点入力を簡単にするための表を作る・・等々

と言った具合にセル参照で他の表にも反映する様、年月をかけ継ぎ足していったものですから
レイアウトはバラバラ、あっちへ行きこっちへ行きという具合なのです。
今は(5)の表がメインです。

・・余計にわかりにくい説明ですね。

補足日時:2005/08/06 14:09
    • good
    • 0

No.4です。


何度も訂正して申し訳ありません。

(誤)B1に =C1*100000*ROW(C1)
(正)B1に =C1*100000+ROW(C1)

この回答への補足

何度もありがとうございます。m(__)m
順番通り並びました。
いけそうですね!?後は私の工夫次第だと思います。

補足日時:2005/08/06 03:25
    • good
    • 0

No.1です。


>また、順位順(上から1位・名前、2位・名前、・・)に名前が並ぶ列が無いのですが・・


申し訳ありません。まちがえました。
(誤)B1に =C1*100000*ROW(C1)
(正)B1に =C1+100000*ROW(C1)
    • good
    • 0

根本的にVLOOKUP関数で参照する表の一番左列の索引値は2つ同じものを入れても、最初のものしか引いてきません。

そのことと関連してませんか。
RANK関数が同点を同一順位で返す特徴があるためです。
ですからそのからを破らないといけません。
A1:A6に
15501
23301
31101
15502
23302
31102
とあるとき
B列には普通のランク関数ですが=RANK(A1,$A$1:$A$6)
C列に=RANK(A1,$A$1:$A$6)& TEXT(COUNTIF($A$1:A1,A1),"00")
と入れてC6まで複写します。
こういう数字を使います。
同一点数者を99人以内と仮定できる場合です。

この回答への補足

ご回答ありがとうございます。
なにせ関数の意味を把握してるわけでは無いので、試して検証するのにも時間がかかりそうです。。
気長にお願いできれば幸いです。m(__)m

補足日時:2005/08/06 01:01
    • good
    • 0
この回答へのお礼

ありがとうございます。
A列に1、B列に5、C列に501で、設定せよと言うことでしょうか?
う~ん、理解できなくてすみません(質問の仕方が悪いんでしょうね?)

多くても20人前後の話なんですが・・

お礼日時:2005/08/06 03:53

こんばんは。



ご質問のデータ位置などが、ほとんど分かりませんが、こういうことでしょうか?

元のデータが以下のような状態であるとします。

例:
 G   H   I 
名前  点数  順位
Dさん  8   5
Fさん  9   2
Eさん  4   8
Aさん  10   1
Cさん  9   3
Uさん  5   7
Lさん  6   6
Bさん  9   4
Mさん  4   9


別に、降順に並び換える必要はありません。

I2: ~
順位の出し方
=IF(RANK(H2,$H$2:$H$10)=COUNTIF($H$2:$H$10,">=" &H2),RANK(H2,$H$2:$H$10),COUNTIF($H$2:$H$10,">" & H2)+COUNTIF($H$2:H2,H2))

ランク表

 A   B    C
順位  名前  点数
 1位  Aさん  10
 2位  Fさん  9
  ・
  ・


順位のA2:~

なぜか、ご質問では、全角を使っていたようなので、
書式-セル-ユーザー書式

[DBNum3]#,##0"位"

で、A2~下方に
1
2
3
4
5
.

と入れれば、中身は、半角数値で、表示は、全角+「位」がつきます。

B2:~
=INDEX($G$2:$H$10,MATCH(A2,$I$2:$I$10,0),1)

C2:~
=INDEX($G$2:$H$10,MATCH($A2,$I$2:$I$10,0),2)

とすればよいと思います。範囲は、適宜変えてください。

ただ、同点で、順位に差がつくのは、ちょっと疑問に思いました。

この回答への補足

ご回答ありがとうございます。
まだ試せてませんが、時間がかかりそうなので・・お待ちくだされば幸いです。

>同点で、順位に差がつくのは、ちょっと・・
それも何とかしたいのですが。

補足日時:2005/08/06 00:53
    • good
    • 0
この回答へのお礼

ありがとうございます。
多分これでいいのだと思いますが、同点の場合、
1位 Aさん 10点
2位 Cさん  9点
2位 Dさん  9点
4位 Bさん  8点
と言う表記になるようには出来ますでしょうか?

お礼日時:2005/08/06 03:35

こういう方法はどうでしょうか。



まず、得点表側です。
たとえば、D1からD20に人名、E1からE20に得点が入力されているとします。
C1に =RANK(E1,$E$1:$E$20,0)
C2以下コピー。これで、まず順位を求めます。

B1に =C1*100000*ROW(C1)
B2以下コピー。これで、異なる行に差をつけます。

A1に =RANK(B1,$B$1:$B$20,1)
A2以下コピー。これは昇順の順位です。これで、重複しない順位が求められます。

次に順位表です。
どこかの列に1から20まで通し番号を打ちます。たとえば、G1~G20とします。

H1に =VLOOKUP(G1,$A$1:$E$20,3,FALSE)&"位"
H2以下コピー。これで重複のある順位を取り出せます。
J1に =VLOOKUP(G1,$A$1:$E$20,4,FALSE)
J2以下コピー。これで人名を取り出せます。
K1に =VLOOKUP(G1,$A$1:$E$20,5,FALSE)&"点"
K2以下コピー。これで得点を取り出せます。

この回答への補足

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

とりあえず教えていただいた通りの設定をしてみたのですが、
E列の得点を適当に変え、同じ得点者を作ると やはり、H,J,K列に#N/Aが表示されたり、されなかったりです。また、順位順(上から1位・名前、2位・名前、・・)に名前が並ぶ列が無いのですが・・
何が悪いのか検証するのに、もう少し時間がかかりそうです。

補足日時:2005/08/06 00:52
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QエクセルRANK関数の同率順位を別表に抽出する時

お世話になります。
エクセル2003を利用しています。初心者なので教えていただきたく存じます。
以下文面のようにランキング表を作りました。
RANK関数を利用してランキングを出し、順位表にTOP5(重複していても)まで出したいと思っています。

A列にA店~Z店までの名前(A店という名前は仮です)が入っており、
B列に上から結果数字がランダムに入力がされており、
C列にRANK関数を使って順位がその結果の横に数字として反映されています。

B列の順位の数字が大体1~20程度までの数字が記入されており、C列のRANK関数の結果が当然ですが重複して順位が表示されている状態です。

別表を作成して1~5位のランキング表を作り、A列に入っている店名とを表示させたいと思っております。
1位と2位は単独であったので店名はINDEXとMATCH関数を使ってきれいに抽出できたのですが、
3位以下は重複しているようで#N/Aと表示されてしまいます。
表示の仕方として「あいうえお順(A~Zの若い順)」で表示できればと思っております。

無知で恐縮なのですが、情報不足の点等あればご指摘いただきたく存じます。
お詳しい方、ご教授宜しくお願い致します。

お世話になります。
エクセル2003を利用しています。初心者なので教えていただきたく存じます。
以下文面のようにランキング表を作りました。
RANK関数を利用してランキングを出し、順位表にTOP5(重複していても)まで出したいと思っています。

A列にA店~Z店までの名前(A店という名前は仮です)が入っており、
B列に上から結果数字がランダムに入力がされており、
C列にRANK関数を使って順位がその結果の横に数字として反映されています。

B列の順位の数字が大体1~20程度までの数字が記入されており...続きを読む

Aベストアンサー

No.1です。
たびたびごめんなさい。

投稿後気になったのですが、前回の方法はE列の順位は必ず1~5までという前提ですので、
同順位でも表示上はランクの差が出てしまいます。

E列順位はC列の順位そのものを表示させるのが一般的だと思いますので、
もう一度やってみました。

↓の画像でC列は小細工せずにRANK関数だけにしてC2セルに
=RANK(B2,B:B)
という数式を入れフィル&コピーしています。

そしてE2セルに
=SMALL(C:C,ROW(A1))

F2セルに
=INDEX(A$1:A$50,SMALL(IF(C$1:C$50=E2,ROW(A$1:A$50)),COUNTIF(E$2:E2,E2)))

これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定!
この画面からコピー&ペーストする場合は、
上記数式をドラッグ&コピー → F2セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。

最後にE2・F2セルを範囲指定 → F2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

こんな感じではどうでしょうか?m(_ _)m=

No.1です。
たびたびごめんなさい。

投稿後気になったのですが、前回の方法はE列の順位は必ず1~5までという前提ですので、
同順位でも表示上はランクの差が出てしまいます。

E列順位はC列の順位そのものを表示させるのが一般的だと思いますので、
もう一度やってみました。

↓の画像でC列は小細工せずにRANK関数だけにしてC2セルに
=RANK(B2,B:B)
という数式を入れフィル&コピーしています。

そしてE2セルに
=SMALL(C:C,ROW(A1))

F2セルに
=INDEX(A$1:A$50,SMALL(IF(C$1:C$50=E2,ROW(A$1:A$50)),COUNTIF(E$2...続きを読む

Qエクセルで、自動的にランク順に順番を表示させる方法

エクセルで、自動的にランク順に順番を表示させる方法

エクセル2003を使用しています。

T列に名前、U列にその人の点数が表示されています。
これに対し、点数の高い順に自動的に並べ替えてB列に順位、C列に名前を表示させたいです。

同じ点数がある場合、以下の様な表示をしたいです。
1位 Aさん 80点
2位 Bさん 70点
2位 Cさん 70点
4位 Dさん 60点

表示>並べ替えは使用しないで、関数で対応したいです。
分かる方いましたら、お願いします。

Aベストアンサー

なるべくシンプルな計算式で。

添付図:
S2に
=RANK(U2,U:U)+ROW()/1000
以下コピー。

B2に
=VLOOKUP(SMALL($S:$S,ROW(A1)),$S:$U,COLUMN(),FALSE)
B,C列にコピー。

A2に
=RANK(C2,C:C)
以下コピー,
必要に応じてA列を右クリックしてセルの書式設定の表示形式のユーザー定義で
0位
と設定しておくなど。

Qデータが重複データの扱い(対処方法) RANK・MATCH・INDEX

ずばりこんなデータから、順位とその人の名前を出す。

1)重複した場合、RANKは、同じ位置して、次の番は、欠としている。
  から、下記のような順位になると思われる。

 氏名 点数 順位
 鈴木 700 5
 斉藤 415 8 
 菅野 700 5
 赤井 780 4
 田村 900 2
 池田 620 7 
 楽谷 900 2
 金田 940 1

 ★問題なのが、同じランクになった人の名前をちゃんと表示したい。
  1位からの点数を表示するのは、LARGE関数でできるんだけど、
  同じランクになっている人が、別々に出てこない。
  とても安易な方法だと思ったんですが、
  検索する範囲(表の行番号)をMATCH関数でもとめておく
  点数をつかうので、当然同じ点数には、同じ行がはいる(上から
  順番だから、1行目と3行目に同じ点数なら、1(順位)が連続する)
  それで、その番号を使いVLOOKUP関数で、名前を拾ってくる。
  其の時、自分で同じ番号のところを修正しておく(1と3にする)
  そうすれば、名前がちゃんと出てくる。
  でも、其のセルには、数式がなくなる。

  ⇒VBAや、難しい関数が使えないと、これが限界なのか。。
   参考にさせてもらうものもみたんですが、
   なかなか・・この苦肉の策でもヨシと思っていいのか・・
   ちょっと自信なくて、、
  
 
 ベスト8(ランクごとに返す)
    点数 氏名 番号
 1位 ヾ  ヾ  
 2位 ヾ  ヾ
 2位 ヾ  ヾ
 4位 ヾ  ヾ
 5位 ヾ  ヾ
 5位 ヾ  ヾ
 7位 ヾ  ヾ
 8位 ヾ  ヾ
   

ずばりこんなデータから、順位とその人の名前を出す。

1)重複した場合、RANKは、同じ位置して、次の番は、欠としている。
  から、下記のような順位になると思われる。

 氏名 点数 順位
 鈴木 700 5
 斉藤 415 8 
 菅野 700 5
 赤井 780 4
 田村 900 2
 池田 620 7 
 楽谷 900 2
 金田 940 1

 ★問題なのが、同じランクになった人の名前をちゃんと表示したい。
  1位からの点数を表示するのは、LARGE関数でできるんだけど、
  同じランクになっている人...続きを読む

Aベストアンサー

こんばんは。

関数で作る場合です。

データが、A1 ~C9 までとしで、

D列~F列に表示しなおすとして、

 D列  E列  F列 
順位  点数  氏名
1位 
2位 
2位 
4位 
5位 
5位 
7位 
8位 

E列
E2 は、
=LARGE($B$2:$B$9,ROW(A1))
フィルダウン・コピーします。

D列は、
D2~
=JIS(RANK(E2,$B$2:$B$9))&"位"

を入れて、フィルダウンコピーします。
(JISは、全角にする関数です。)

F列は、
F2~

=INDEX($A$1:$B$9,MOD(LARGE($B$2:$B$9+ROW($A$2:$B$9)/100,ROW(A1)),1)*100,)

配列数式ですから、一旦式を入力したら、目的の式にF2を押して、『ShiftとCtrlを押しながらEnterキー』を押して、式を配列数式に変換させます。

それから、フィルダウン・コピーします。

これで、出来たかと思います。

こんばんは。

関数で作る場合です。

データが、A1 ~C9 までとしで、

D列~F列に表示しなおすとして、

 D列  E列  F列 
順位  点数  氏名
1位 
2位 
2位 
4位 
5位 
5位 
7位 
8位 

E列
E2 は、
=LARGE($B$2:$B$9,ROW(A1))
フィルダウン・コピーします。

D列は、
D2~
=JIS(RANK(E2,$B$2:$B$9))&"位"

を入れて、フィルダウンコピーします。
(JISは、全角にする関数です。)

F列は、
F2~

=INDEX($A$1:$B$9,MOD(LARGE($B$2:$B$9+ROW($A$2:$B...続きを読む

Qエクセルの関数でベスト3の名前を表示させたい

過去の質問を色々調べたのですが、分からなかったので教えてください。

現在、次のようなエクセルの表を作っています。
    AさんBさんCさん日計
1日目100006000150017500
2日目1500200030006500
3日目40000340001200086000

「日計」の隣に(その日の)1位~3位の人の名前を参照させるような関数はないでしょうか?
RANK関数で順位を挿入するようなスペースが無いので、何かいい方法があれば教えてください。

Aベストアンサー

一例です。
=INDEX($B$1:$D$1,MATCH(LARGE($B2:$D2,COLUMN(A1)),$B2:$D2,0))

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

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

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

試行錯誤の結果、下記まで作業しました。
=========
<シート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)SUM関数で集計した値
(3)RANK関数

<シート2>
    A        B    C
(3) ランキング  (1)商品名 (2)販売額
1
2
3
4
5

(3)手入力
(2)INDEXとMATCH関数
(3)INDEXとMATCH関数
=========

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

2)シート2に同順位表示のまま並び替えする方法を教えてください。
INDEX関数とMATCH関数を使っていましたが、
シート1で同順位表示されていた箇所が、シート2で表示できませんでした。

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

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

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

試行錯誤の結果、下記まで作業しました。
=========
<シート1>
    A        B       C       
   (1)商品名  (2)販売額  (3)ランキング 
1    a     1000       2
2   b     1500      1
3  c ...続きを読む

Aベストアンサー

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

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

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


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

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


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

Qエクセルでの順位に同率がある場合の対処について

■エクセル上で数字に順位を付けて上位(下位)3位までの数字に色を付けたい。

■現在の方法
書式→条件付書式

条件1
セルの値が次の値に等しいを選び、
関数「=LARGE($C$6:$C$100,1)」と入力し、書式で文字の色を指定。赤色

条件2
セルの値が次の値に等しいを選び、
関数「=LARGE($C$6:$C$100,2)」と入力し、書式で文字の色を指定。青色

条件3
セルの値が次の値に等しいを選び、
関数「=LARGE($C$6:$C$100,3)」と入力し、書式で文字の色を指定。緑色

■問題点
1番大きい数字が2つある場合、2箇所が赤色になり、2番目に大きい数字が緑色になる。青色は使われない。
(順位で言うと同率1位が二つ、2位が飛ばされて3位が一つと処理される(1位・1位・3位))

■やりたい事
1番大きい数字が2つある場合2箇所とも赤色にし、2番目に大きい数字を青色、3番目に大きい数字を緑色にしたい。
(順位で言うと1位・1位・2位・3位と言った具合にしたい)
もし2番目に大きい数字が3つあってもその3箇所を青色にして、3番目に大きい数字を緑色にする。
(順位で言うと1位・1位・2位・2位・2位・3位の様な感じ)

具体的に言うと

現在
10
20
30→緑色
40→赤色
40→赤色

これを
10
20→緑色
30→青色
40→赤色
40→赤色

にしたいのです。

■補足
表の範囲はC6~C100としていますが、実際はC30までしか使っていなく、それ以降は日々データを記入するので拡張用としている。
使用しているエクセルはエクセル2002です。

よろしくお願いします。

■エクセル上で数字に順位を付けて上位(下位)3位までの数字に色を付けたい。

■現在の方法
書式→条件付書式

条件1
セルの値が次の値に等しいを選び、
関数「=LARGE($C$6:$C$100,1)」と入力し、書式で文字の色を指定。赤色

条件2
セルの値が次の値に等しいを選び、
関数「=LARGE($C$6:$C$100,2)」と入力し、書式で文字の色を指定。青色

条件3
セルの値が次の値に等しいを選び、
関数「=LARGE($C$6:$C$100,3)」と入力し、書式で文字の色を指定。緑色

■問題点
1番大きい数字が2つある場合、2箇所が赤色になり、2番...続きを読む

Aベストアンサー

ありゃりゃ。とんだ手抜かりを。
んでは簡単じゃない数式を使った方法で。


それぞれ「次の値に等しい」で
1位:
=LARGE(IF(FREQUENCY($C$6:$C$100+0,$C$6:$C$100+0),$C$6:$C$100),1)

2位:
=LARGE(IF(FREQUENCY($C$6:$C$100+0,$C$6:$C$100+0),$C$6:$C$100),2)

3位:
=LARGE(IF(FREQUENCY($C$6:$C$100+0,$C$6:$C$100+0),$C$6:$C$100),3)

失礼しました。

QRANK関数 同点の場合違う項目で序列を付けたい

質問願います。
EXCELにて、RANK関数を使って順位付けをする事を理解しています。
スポーツの順位を勝ち点順に並べるのですが、総勝ち点が同点の場合、次に総得点で順位を確定させます。

この場合、順位付けする数値を勝ち点を優先参照・総得点を次に参照させる事は出来ますか?
RANK以外の関数も併用すれば良いのでしょうか?

どなたか回答お願い致します。

Aベストアンサー

総勝ち点が例えばA1セルに50のときに総得点がB1セルに10であったとします。この場合には作業列を作ってC1セルには例えば次の式を入力して重みを付けた値にします。

=A1+B1/1000

C列のデータについてRANK関数を使うことにすればよいでしょう。

QExcel MATCH関数で検索範囲内に同じ値の検索値が複数ある場合

MATCH関数で、検索した値が複数ある場合に1つしか出なくて困っています。
(例)
   A B C D
1 年月日 種類 番号 備考
2 4月1日 肉類 1
3 4月2日 野菜 2
4 4月3日 肉類 1
5 4月4日 果物 2
6 4月5日 野菜 1
7 4月6日 果物 3
8 4月7日 果物 2
9 4月8日 肉類 1

この表で、D9の備考の欄に「種類:肉類、番号:1」と同じものがあった時の日付を、新しい日付で取り出したいのです。

INDEX(A2:A8,(MATCH(B9,B2:B8,0)+MATCH(C9,C2:C8,0))/2)
としたのですが、本当は4月3日を取り出したいのですが、4月1日が出てきます。
どのようにしたら良いでしょうか?
Excel2003です。
よろしくお願いします。

Aベストアンサー

MATCH関数を使用しませんが、D9の書式を日付にして、
=MAX((A2:A8)*(C2:C8=1)*(B2:B8="肉類"))
と、入力してShift+Ctrl+Enterキーで入力完了してみてください(配列式になります)。

QExcelで最大値の入っている数値の右側にある項目名を拾いたい。

Excelで最大値の入っている数値の右側にある項目名を拾いたい。

下のようなデータがあります。(A列は年、Bは何かの生産量・・とします)

1 2000 7
2 2001 11
3 2002 14
4 2003 10
5 2004 9
6 2005 8

生産量(B列)の最大値を求めるのは=MAX(B1:B7)で"14"が得られると思いますが、求めているのは"14"の入っている年の"2002"という数字を隣のC1に入れたいと思います。
どのような関数が必要でしょうか?

Aベストアンサー

こんばんは!
一例です。

↓の画像でC2セルに表示するようになりますが・・・

C2セルの数式は
=INDEX($A$2:$A$1000,MATCH(MAX(B2:B1000),B2:B1000,0))
にしています。
(エラー処理はしていません)
A列の1000行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば幸いです。m(__)m

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング