エクセルに関してです。


A B
あ 5
い 3
う 2
え 8
お 8

A列には全角文字列が
B列には、数値が入っています。
縦は、50行程あります。

これを、数値の大きい順にベスト10を
作りたいです。
上記の例ですと

え 8
お 8
あ 5
となります。
ベスト10なので、それ以外は表示しません。
しかし数値が同じ場合は、全て表示するため
10個以上表示する場合も発生します。

普通、並び替えて、上位10個を抜き出せばば良いだけですが、
ワークシートを作成したいので、
デザインを施した別シートに自動的に数値が
入るようにしたいのです。
関数でできないものでしょうか?

一番良い回答にはイルカ賞を進呈させて頂きます。
エクセル初心者のため分かりやすくお願いいたします。
よろしくお願いいたします。

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2010/04/27 13:43:17
  • 終了:2010/04/28 17:49:42

ベストアンサー

id:koriki-kozou No.3

koriki-kozou回答回数480ベストアンサー獲得回数792010/04/27 16:59:52

ポイント26pt

>同じ数値が同じ順位になっていない


改良版Sheet1

A B C D
1 5 =RANK(B1,B$1:B$5,FALSE) =C1+COUNTIF(B$1:B1,B1)-1
2 3 =RANK(B2,B$1:B$5,FALSE) =C2+COUNTIF(B$1:B2,B2)-1
3 2 =RANK(B3,B$1:B$5,FALSE) =C3+COUNTIF(B$1:B3,B3)-1
4 8 =RANK(B4,B$1:B$5,FALSE) =C4+COUNTIF(B$1:B4,B4)-1
5 8 =RANK(B5,B$1:B$5,FALSE) =C5+COUNTIF(B$1:B5,B5)-1

改良版Sheet2

A B C D
1 1 =INDEX(Sheet1!C$1:C$5,MATCH(A1,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(A1,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(A1,Sheet1!D$1:D$5,0),0)
2 2 =INDEX(Sheet1!C$1:C$5,MATCH(A2,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(A2,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(A2,Sheet1!D$1:D$5,0),0)
3 3 =INDEX(Sheet1!C$1:C$5,MATCH(A3,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(A3,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(A3,Sheet1!D$1:D$5,0),0)

その他の改良版Sheet2

B C D
1 =INDEX(Sheet1!C$1:C$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0)
2 =INDEX(Sheet1!C$1:C$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0)
3 =INDEX(Sheet1!C$1:C$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0)

サンプルは5行としているため実際の行数にあわせて適宜修正必要

id:martytaka

できました!ありがとうございます!

2010/04/28 17:49:09

その他の回答(3件)

id:hiroder No.1

hiroder回答回数20ベストアンサー獲得回数12010/04/27 14:14:26

ポイント27pt

エクセルのバージョンが分かりませんが、2007ですと以下のページでは動画で詳しく説明されています。

http://www.dougamanual.com/blog/75/514/e2872.html


こちらのページでは、手順が順番に書かれています。

http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/excel2007-filter_...

id:koriki-kozou No.2

koriki-kozou回答回数480ベストアンサー獲得回数792010/04/27 15:38:12

ポイント27pt

関数だけで行う例


Sheet1に順位を計算する列を追加

A B C
1 5 =RANK(B1,B$1:B$5,FALSE)+COUNTIF(B$1:B1,B1)-1
2 3 =RANK(B2,B$1:B$5,FALSE)+COUNTIF(B$1:B2,B2)-1
3 2 =RANK(B3,B$1:B$5,FALSE)+COUNTIF(B$1:B3,B3)-1
4 8 =RANK(B4,B$1:B$5,FALSE)+COUNTIF(B$1:B4,B4)-1
5 8 =RANK(B5,B$1:B$5,FALSE)+COUNTIF(B$1:B5,B5)-1

数式の入力は一行目だけで二行目以下はコピーで生成

C列に式を入れたがD列でもE列でも、離れた列でもかまわない

RANK関数は同数値は同位となるためCOUNTIFで重複分を調整している

意味不明であればC列にRANK、D列にCOUNTIFと分けてテストしてみると理解しやすい


Sheet2にて該当順位の値を取得

A B C
1 1 =INDEX(Sheet1!A$1:A$5,MATCH(A1,Sheet1!C$1:C$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(A1,Sheet1!C$1:C$5,0),0)
2 2 =INDEX(Sheet1!A$1:A$5,MATCH(A2,Sheet1!C$1:C$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(A2,Sheet1!C$1:C$5,0),0)
3 3 =INDEX(Sheet1!A$1:A$5,MATCH(A3,Sheet1!C$1:C$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(A3,Sheet1!C$1:C$5,0),0)

こちらも数式は一行目だけ

MATCHで行位置を特定し、INDEXで値を取得している

id:martytaka

ありがとうございます。

あれ?

でも、

=RANK(B1,B$1:B$5,FALSE)+COUNTIF(B$1:B1,B1)-1

だと同じ数値が同じ順位になっていないような。。

2010/04/27 16:23:49
id:koriki-kozou No.3

koriki-kozou回答回数480ベストアンサー獲得回数792010/04/27 16:59:52ここでベストアンサー

ポイント26pt

>同じ数値が同じ順位になっていない


改良版Sheet1

A B C D
1 5 =RANK(B1,B$1:B$5,FALSE) =C1+COUNTIF(B$1:B1,B1)-1
2 3 =RANK(B2,B$1:B$5,FALSE) =C2+COUNTIF(B$1:B2,B2)-1
3 2 =RANK(B3,B$1:B$5,FALSE) =C3+COUNTIF(B$1:B3,B3)-1
4 8 =RANK(B4,B$1:B$5,FALSE) =C4+COUNTIF(B$1:B4,B4)-1
5 8 =RANK(B5,B$1:B$5,FALSE) =C5+COUNTIF(B$1:B5,B5)-1

改良版Sheet2

A B C D
1 1 =INDEX(Sheet1!C$1:C$5,MATCH(A1,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(A1,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(A1,Sheet1!D$1:D$5,0),0)
2 2 =INDEX(Sheet1!C$1:C$5,MATCH(A2,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(A2,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(A2,Sheet1!D$1:D$5,0),0)
3 3 =INDEX(Sheet1!C$1:C$5,MATCH(A3,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(A3,Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(A3,Sheet1!D$1:D$5,0),0)

その他の改良版Sheet2

B C D
1 =INDEX(Sheet1!C$1:C$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0)
2 =INDEX(Sheet1!C$1:C$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0)
3 =INDEX(Sheet1!C$1:C$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!A$1:A$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0) =INDEX(Sheet1!B$1:B$5,MATCH(ROW(),Sheet1!D$1:D$5,0),0)

サンプルは5行としているため実際の行数にあわせて適宜修正必要

id:martytaka

できました!ありがとうございます!

2010/04/28 17:49:09
id:SALINGER No.4

SALINGER回答回数3454ベストアンサー獲得回数9692010/04/27 17:07:30

ポイント10pt

作業列を使う方法としてはだいたい2の回答でよろしいかと思います。

(同じ数値でも順位をつけることで上から順番に取り出せるわけです)

ただし複数同じ数値があり、10個以上表示させる場合が抜けています。


私は別のアプローチで作業列を必要としない方法です。

A B
1 数式1 数式2
11 数式3 数式4

表のようにA1とB1に下記の数式をコピペして、Shift+Ctrl+Enterで配列数式にしてください。(数式の前後に{}がつきます。)

そして10行目まで下にコピー。

11行目のA11とB11も下記の数式をコピーして同じように配列数式にして、何個か下にコピーしてください。


  • 数式1
=INDEX(Sheet1!A:A,SUM(IF(((Sheet1!B$1:B$100-ROW(Sheet1!B$1:B$100)/100)=LARGE((Sheet1!B$1:B$100-ROW(Sheet1!B$1:B$100)/100),ROW())),ROW(Sheet1!B$1:B$100),"")))

  • 数式2
=INDEX(Sheet1!B:B,SUM(IF(((Sheet1!B$1:B$100-ROW(Sheet1!B$1:B$100)/100)=LARGE((Sheet1!B$1:B$100-ROW(Sheet1!B$1:B$100)/100),ROW())),ROW(Sheet1!B$1:B$100),"")))

  • 数式3
=IF(B$10=INDEX(Sheet1!B:B,SUM(IF(((Sheet1!B$1:B$100-ROW(Sheet1!B$1:B$100)/100)=LARGE((Sheet1!B$1:B$100-ROW(Sheet1!B$1:B$100)/100),ROW())),ROW(Sheet1!B$1:B$100),""))),A$10,"")

  • 数式4
=IF(B$10=INDEX(Sheet1!B:B,SUM(IF(((Sheet1!B$1:B$100-ROW(Sheet1!B$1:B$100)/100)=LARGE((Sheet1!B$1:B$100-ROW(Sheet1!B$1:B$100)/100),ROW())),ROW(Sheet1!B$1:B$100),""))),B$10,"")

式中の100は99行目までの対応ということですので、データがさらに増える場合はそれ以上の数値にすればいいです。

式は配列数式を使っていますので、初心者には難解ですので説明は省きますが、配列数式の解説ページを紹介しておきます。

http://pc.nikkeibp.co.jp/pc21/special/hr/

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません