人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

エクセルに関してです。

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

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

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

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

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

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


●質問者: martytaka
●カテゴリ:ビジネス・経営 コンピュータ
✍キーワード:いもの イルカ賞 エクセル デザイン ベスト
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● hiroder
●27ポイント

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

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


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

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


2 ● koriki-kozou
●27ポイント

関数だけで行う例


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で値を取得している

◎質問者からの返答

ありがとうございます。

あれ?

でも、

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

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


3 ● koriki-kozou
●26ポイント ベストアンサー

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


改良版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行としているため実際の行数にあわせて適宜修正必要

◎質問者からの返答

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


4 ● SALINGER
●10ポイント

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

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

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


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

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

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

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

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


=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),"")))

=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),"")))

=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,"")

=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/

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ