出産前後の痔にはご注意!

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

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

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

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

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

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

A 回答 (6件)

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



添付図:
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位
と設定しておくなど。
「エクセルで、自動的にランク順に順番を表示」の回答画像6
    • good
    • 2
この回答へのお礼

ありがとうござました。
思ったように対応できました。

お礼日時:2010/06/12 12:02

補助列を使わずに関数で表示するなら以下のような数式を入力し下方向にオートフィルします。



C2セル
=LARGE($U$2:$U$100,ROW(A1))

B2セル
=INDEX(T:T,SMALL(IF($U$1:$U$100=C2,ROW($U$1:$U$100)),COUNTIF(C$1:$C2,C2)))

B2セルの数式は配列数式ですので、入力後Ctrl+Shift+Enterで確定してください。
    • good
    • 3
この回答へのお礼

今回は「簡単そう」という理由でNO6様の方法を使用させて頂きました。
ですが、わざわざ投稿して頂いてありがとうござました。

お礼日時:2010/06/12 12:04

こんばんは!


参考になるかどうか判りませんが・・・
↓の画像で説明させていただきます。
作業用の列を2列設けています。
作業列V2セルに
=IF(U2="","",RANK(U2,$U$2:$U$100))
(単純にランク表示です)

W2セルに
=IF(U2="","",COUNTIF($U$2:$U$100,">"&U2)+COUNTIF($U$2:U2,U2))
(同得点の場合に無理矢理ランク付けするためです。上の行の人が上位になります)

このV2・W2セルを範囲指定し、W2セルのフィルハンドルで下へずぃ~~~!っとコピーします。

そして、B2セルに
=IF(COUNT($U$2:$U$100)<ROW(A1),"",INDEX($V$2:$V$100,MATCH(ROW(A1),$W$2:$W$100,0)))

C2セルに
=IF(COUNT($V$2:$V$100)<ROW(A1),"",INDEX($T$2:$T$100,MATCH(ROW(A1),$W$2:$W$100,0)))
という数式を入れ、B2・C2セルを範囲指定し、C2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

尚、数式は100行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

作業用の列を使っていますので、目障りならこの2列を非表示にしても良いかと思います。

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m
「エクセルで、自動的にランク順に順番を表示」の回答画像4
    • good
    • 0
この回答へのお礼

今回は「簡単そう」という理由でNO6様の方法を使用させて頂きました。
ですが、わざわざ投稿して頂いてありがとうござました。

お礼日時:2010/06/12 12:05

ちょっと補足ですが


点数の列はLARGE(U:U,順位)と指定すれば表示されます。
単位については書式設定でユーザー定義→0"点"というように設定すると楽に作れます。
    • good
    • 1
この回答へのお礼

今回は「簡単そう」という理由でNO6様の方法を使用させて頂きました。
ですが、わざわざ投稿して頂いてありがとうござました。

お礼日時:2010/06/12 12:05

まず


順位は関数というよりVBAで求めることになると思います。

要素がいくつあるかは不明ですか。
    • good
    • 1

S2セルに =RANK(U2,U:U)


下へオートフィル
V2セルに =S2+COUNTIF($S$2:S2,S2)-1
下へオートフィル

A2:C2セル範囲を選択して
数式バーに
=INDEX($S:$U,MATCH(ROW(A1),$V:$V,0),{1,2,3})&{"位","","点"}
[Ctrl]+[Shft] +[Enter] で確定、配列数式です({}で囲まれる)
下へオートフィル
(別に配列数式にする必要はないですが、計算の重複を避けただけです)
添付図参照

同点の場合、行番号の小さい人が上になります
「エクセルで、自動的にランク順に順番を表示」の回答画像1
    • good
    • 0
この回答へのお礼

今回は「簡単そう」という理由でNO6様の方法を使用させて頂きました。
ですが、わざわざ投稿して頂いてありがとうござました。

お礼日時:2010/06/12 12:06

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

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

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

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

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

QExcelで入力したデータを自動的に並び替える方法

Excel2000で、名前のリストを作成し、これを、あいうえお順(昇順)に並び替えたものがあります。

このリストに、新しく名前を入力したときに、自動的に昇順の並び替えてくれる方法ってありますか?

質問がわかりにくければ、補足します。
よろしくお願いします。

Aベストアンサー

初めまして。次のようにすれば、貴方様の思い通りの動作をするようになると思います。A列に名前を入力してある設定でコードを記述しています。


Private Sub Worksheet_Change(ByVal Target As Range)

Dim myClm As Integer

If Target.Column <> 1 Then Exit Sub
myClm = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess

End Sub

Sheet1のモジュールに上記のコードを記述して下さい。
A列に名前を入力してエンターキーを押した時点で並べ替えが実行されます。

お試しになってみて下さい。

Qエクセルにてデータを大きい順に並べ替えることを自動でやりたい

sheet1をデータ入力用のシートとし、以下のデータが入っています。
  A  B  C  D
1 あ 5
2 い 4
3 は 8
4 

sheet2は上記のBのデータを大きい順に並べ替えた出力用のシートとし、
以下のように出力されます。
  A  B  C  D
1 は 8
2 あ 5
3 い 4
4 

sheet1のデータが更新されて大きさの順番が変わると、
sheet2の並び順が自動で変わるように出来るのでしょうか?
マクロを使わずに数式でやりたいと思っています。

Aベストアンサー

既出回答に似た考えのやり方があるが
「エクセル 関数 並べ替え」(または前の2つとソート)でWEB照会して見ると
http://pc.nikkeibp.co.jp/article/NPC/20060213/229138/
などがある。
解説が実例入りで詳しいので紹介しておく。
ーー
この方法は
(1)作業列を使う。
(2)同じ値があると困る。
などの問題点はあるが。
ーーー
(2)の点は、同じ値にも次の番号を振るやり方を見つければよい。
例データ
A列   B列
値順位
81
44
26
52
45
53
17

B列B2の式は =RANK(A2,$A$2:$A$10)+COUNTIF($A$1:A2,A2)-1
式を縦方向に複写する。
結果上記。
ーー
並べ替えは例えばF2に
=INDEX($A$2:$A$10,MATCH(ROW()-1,$B$2:$B$10,0),1)
と入れて下方向に式を複写。
結果
8
5
5
4
4
2
1
一種の自称 imogasi方式です。imogasi方式の関連はGoogke照会のこと。(銃器では#N/Aの問題、上記例では1列だが複数列のデータを並べ替える場合などの問題が残っているが、長くなるので略。そちらに書いてあるので略)

既出回答に似た考えのやり方があるが
「エクセル 関数 並べ替え」(または前の2つとソート)でWEB照会して見ると
http://pc.nikkeibp.co.jp/article/NPC/20060213/229138/
などがある。
解説が実例入りで詳しいので紹介しておく。
ーー
この方法は
(1)作業列を使う。
(2)同じ値があると困る。
などの問題点はあるが。
ーーー
(2)の点は、同じ値にも次の番号を振るやり方を見つければよい。
例データ
A列   B列
値順位
81
44
26
52
45
53
17

B列B2の式は =RANK(A2,$A$2:$A...続きを読む

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エクセルで順位表を並び替えたい。同順位表示方法は?

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

シート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エクセルで打ち込んだ数字を自動で別シートに表示したい

エクセルでセルに打ち込んだ数字を自動で別シートに表示できる方法があれば、教えてください。

例えば、シート1のC1に5を打ち込んだら、シート2のD2にシート1で打ち込んだ5が自動で表示される。

また1列すべてを自動で表示させる場合、一つのセルの時と違いがありましたら教えてください。よろしくお願いします。

Aベストアンサー

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コピー&リンク貼り付けを使うと便利です。

例)例)シート1のC1~C5に何かを入力したら、
  シート2のD2~D7にシート1で打ち込んだものが自動で表示される

  シート1にあるコピー元のセルを範囲選択して、
  シート2のD2の上で「右クリック」⇒「形式を選択して貼り付け」
  をクリックします。

  そして出てきた小さな画面の左下にある「リンク貼り付け」という
  ボタンをクリックすると完成です。
  試してみてください。。

  念のためにリンク貼り付けを図解しているURLを載せておきます。
  参考にしてみてくださいね。。
  http://www.geocities.jp/office_inoue/excel/eq21.htm

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コ...続きを読む

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

QExcel昇順関数について

データの昇順・降順方法として、メニューから「データ」→「並べ替え」とする方法がありますが、関数で同じことを実現できるものはあるのでしょうか?(例)A列に上から、「5、3、1、4、2」と並んでいるときに、(関数を入力した)B列上から「1、2、3、4、5」と表示する。

Aベストアンサー

A1からA5までデータが入っているものとして、
作業用としてC列に1行目から順位(1,2,3、・・・・)を作っておき、セルB1に
=LARGE($A$1:$A$5,C1)
とし、下の行までコピーします。

Qエクセルで同率の順位の人が表示されない

得点表を元に順位(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です。

Aベストアンサー

こんにちは。
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)

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

こんにちは。
Wendy02 です。No.8 に対するコメントです。

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

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

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

Q(Excel)あるセルに文字を入力しただけで、同じブック内のほかのワークシートにも、同じ文字が自動的に入るようにするには?

こんにちは。
質問内容はタイトルのとおりです。

あるワークシートのあるセルに文字を入力すると、ほかのワークシートのセルにも同じ文字が自動的に書き込まれる方法を知りたいです(ブックは同じ)。複数のワークシートを制御するには、やはりマクロを使うのでしょうか?

よろしくお願いします。

Aベストアンサー

clam_chowderさん、こんにちは。

Sheet1のA1にたとえば「100」と入力しますね。
Sheet2のA1に、これと同じ数値を表示するには、
 =Sheet1!A1
でOKです。

数式をいれるのが苦手なら、
Sheet2のA1セルで「=」を入力すると、数式入力状態に入りますから、
ここでSheet1のシートタブをクリックし、
リンクしたいA1セルをクリックしてEnterすると、
自動的にさきほどと同じ式が入ります。


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

人気Q&Aランキング