エクセルで現在ランキング表を作成しています。

質問

エクセルで現在ランキング表を作成しています。

A1～C3に数値が、D1～F3に文字列が入っています。
A1の数値とD1の文字列、A2の数値とD2の文字列を関連付けるようにし、
別シートに数値の大きい順に列で並べ替え、ランキング順を付けて下図のように
したいのですが、文字列を関連付けて並び替えることができません。どうしたらよいでしょうか？

シート２のA列がランキング順位、B列が数値の大きい順、C列にBの数値と関連付けた文字列

シート１　　　　　　　　　　　　　　シート２
____A____B____C____D____E____F　　　　　A__B___C
1__12__18__00__AA__AB__AC　　　　1__1__25__BC
2__10__08__25__BA__BB__BC　　　　2__2__19__CC
3__05__09__19__CA__AB__CC　　　　3__3__18__AB

回答No.6

kagakusuki · ベストアンサー

回答番号:ANo.3,4です。

>数値が同じ箇所でどうしても文字列も同じく重複してしまいます。

　重複する数字が在るのでしたら、面倒な事になりますが、入力されている数値が整数に限られている場合には、作業列を使用した方法があります。
　今仮に、Sheet3のA列～C列を作業列として使用するものとします。
　まず、Sheet3のA1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>ROWS(シート１!$A$1:$C$3)*COLUMNS(シート１!$A$1:$C$3),"",OFFSET(シート１!$A$1,INT((ROWS($1:1)-1)/COLUMNS(シート１!$A$1:$C$3)),MOD(ROWS($1:1)-1,COLUMNS(シート１!$A$1:$C$3))))

　次に、Sheet3のB1セルに次の数式を入力して下さい。

=IF($A1="","",$A1+COUNTIF($A$1:$A1,$A1)/COUNTIF($A:$A,$A1))

　次に、Sheet3のC1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>ROWS(シート１!$A$1:$C$3)*COLUMNS(シート１!$A$1:$C$3),"",OFFSET(シート１!$D$1,INT((ROWS($1:1)-1)/COLUMNS(シート１!$A$1:$C$3)),MOD(ROWS($1:1)-1,COLUMNS(シート１!$A$1:$C$3))))

　そして、Sheet3のA1～C1の範囲をコピーして、同じ列の2行目以下に、リスト中の数字が入力されているセルの個数を上回るのに充分な回数になるまで、貼り付けて下さい。

　次に、シート１のA1セルとB1セルには、ANo.4と同じ数式を入力して下さい。
　次に、シート１のC1セルに次の数式を入力して下さい。

=IF($A1="","",VLOOKUP(LARGE(Sheet3!$B:$B,ROWS($1:1)),Sheet3!$B:$C,2,0))

　そして、シート２のA1～C1の範囲をコピーして、シート２のA2～C9の範囲に貼り付けて下さい。

　以上です。

お礼

ありがとうございました。お蔭様でランキング表ができあがりました。

並び替えと取得した数値にCOUNTIFで数値を足して差異をつけるわけですね。

回答No.7

KURUMITO

回答Ｎｏ2です。
分かりやすくするには作業列を作って対応することでしょう。
シート1のＧ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)>9,"",INDEX($A$1:$C$3,ROUNDUP(ROW(A1)/3,0),MOD(ROW(A1)-1,3)+1))

Ｈ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(G1="","",RANK(G1,G$1:G$9)-COUNTIF(G$1:G1,G1)/100)

Ｉ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)>9,"",INDEX($D$1:$F$3,ROUNDUP(ROW(A1)/3,0),MOD(ROW(A1)-1,3)+1))

シート2では次のようにします。
Ａ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)>9,"",ROUNDUP(SMALL(Sheet1!H$1:H$9,ROW(A1)),0))

Ｂ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)>9,"",INDEX(Sheet1!G$1:G$9,MATCH(SMALL(Sheet1!H$1:H$9,ROW(A1)),Sheet1!H$1:H$9,0)))

Ｃ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)>9,"",INDEX(Sheet1!I$1:I$9,MATCH(SMALL(Sheet1!H$1:H$9,ROW(A1)),Sheet1!H$1:H$9,0)))

回答No.5

MackyNo1

Sheet1のA列からC列の数字データ範囲に「数字」という名前を付けておきます（もちろん絶対参照でもOKです）。

数字の重複がなければ以下のような数式をシート1に入力し下方向にオートフィルします。

A1セル
=RANK(B1,数字)

B1セル
=LARGE(数字,ROW(A1))

C1セル
=INDEX(Sheet1!D:F,SUMPRODUCT((数字=B1)*ROW(数字)),SUMPRODUCT((数字=B1)*COLUMN(数字)))

お礼

数字の重複があることを伝え忘れていました。

回答No.4

kagakusuki

回答番号:ANo.3です。
　先程の回答に関してですが、シート１のA1～C3の範囲に空欄等が存在した場合に対処するために、先程の回答中の数式を以下の様に変更して下さい。

シート１のA1セルの数式

=IF(ROWS($1:1)>COUNT(シート１!$A$1:$C$3),"",ROWS($1:1))
シート１のB1セルの数式

=IF($A1="","",LARGE(シート１!$A$1:$C$3,$A1)
)

シート１のC1セルの数式

=IF($A1="","",INDEX(シート１!$D$1:$F$3,SUMPRDUCT((シート１!$A$1:$C$3=$B1)*ROW(シート１!$A$1:$C$3))-ROW(シート１!$A$1)+1,SUMPRDUCT((シート１!$A$1:$C$3=$B1)*COLUMN(シート１!$A$1:$C$3))-COLUMN(シート１!$A$1)+1))

お礼

ありがとうございました。
すっぽりと答えに当てはまりました。

補足

答えに当てはまったと思ったのですが、数値が同じ箇所でどうしても文字列も同じく重複してしまいます。

回答No.3

kagakusuki

まず、シート１!A1セル～C3セルの書式設定の表示形式が文字列になっていない事を確認して下さい。
　そして、もし、書式設定が文字列となっていた場合には、シート１!A1～C3の範囲のセルの書式設定の表示形式を、
「分類」欄が

ユーザー定義

「種類」欄が

00

となる様に変更した上で、数値の入力をやり直して下さい。（入力をやり直す方法は、各セルを順番にダブルクリックして行くだけです）

　次に、シート２のA1セルに次の数式を入力して下さい。

=ROWS($1:1)

　次に、シート２のB1セルに次の数式を入力して下さい。

=LARGE(シート１!$A$1:$C$3,$A1)

　次に、シート２のC1セルに次の数式を入力して下さい。

=INDEX(シート１!$D$1:$F$3,SUMPRDUCT((シート１!$A$1:$C$3=$B1)*ROW(シート１!$A$1:$C$3))-ROW(シート１!$A$1)+1,SUMPRDUCT((シート１!$A$1:$C$3=$B1)*COLUMN(シート１!$A$1:$C$3))-COLUMN(シート１!$A$1)+1)

　そして、シート２のA1～C1の範囲をコピーして、シート２のA2～C9の範囲に貼り付けて下さい。

　以上です。

お礼

申し訳ありません数値が重複するところで文字列がどうしてもかぶってしまいます。

回答No.2

KURUMITO

シート1のＡ1セルからＣ3セルには同じ数値が無いことが必要です。シート2のＡ1セルには次の式を入力してＣ1セルまでオートフィルドラッグコピーしたのちに下方にもオートフィルドラッグします。

=IF(OR(ROW(A1)>9,COLUMN(A1)>3),"",IF(COLUMN(A1)=1,ROW(A1),IF(COLUMN(A1)=2,LARGE(Sheet1!$A$1:$C$3,ROW(A1)),IF(COLUMN(A1)=3,INDEX(Sheet1!$D$1:$F$3,IF(COUNTIF(Sheet1!$A$1:$A$3,$B1)>0,MATCH($B1,Sheet1!$A$1:$A$3,0),IF(COUNTIF(Sheet1!$B$1:$B$3,$B1)>0,MATCH($B1,Sheet1!$B$1:$B$3,0),MATCH($B1,Sheet1!$C$1:$C$3,0))),IF(COUNTIF(Sheet1!$A$1:$C$1,$B1)>0,MATCH($B1,Sheet1!$A$1:$C$1,0),IF(COUNTIF(Sheet1!$A$2:$C$2,$B1)>0,MATCH($B1,Sheet1!$A$2:$C$2,0),MATCH($B1,Sheet1!$A$3:$C$3,0))))))))

お礼

すいません　同じ数値が入ることもあるのです。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

ダブりがないことを前提にして
I1セル =LARGE($A$1:$C$3,$H1)
J1セル
=INDEX($D$1:$F$3,
SUMPRODUCT(($I1=$A$1:$C$3)*ROW($A$1:$A$3)),
SUMPRODUCT(($I1=$A$1:$C$3)*COLUMN($A$1:$C$1)))
I1:J1セルを下へオートフィル
H:J列を切り取ってSheet2へ貼り付け

お礼

数値のダブりはあるんです　申し訳ありません。