計算式教えてください

質問

エクセル2010

Ａ　　　　　　Ｂ　　　　Ｃ　　　　　Ｄ　　　　Ｅ
名前　　　科目　　　科目　　　合計　　順位がはいっています。

別の表のＢ列とＣ列に１位から５位までの合計点数と名前をいれたいのですがどういう式にすればよいでしょう？合計と順位は関数をいれてあります。

Ａ　　　　　　Ｂ　　　　Ｃ　　　　　
順位　　　合計　　　名前

回答No.6

kagakusuki

他の回答者様方の御回答の中には、同じ点数の人間が複数居た場合には、同じ名前が重複して表示されたり、表示されない名前が出て来たりするものもある様ですから、その点を注意して確認しておく必要があると思います。

　今仮に元データの表の中の「名前」と入力されているセルがSheet1のA1セルであり、別の表の中の「名前」と入力されているセルがSheet2のA1せるであるものとします。

【方法１】作業列と関数を使う方法
　今仮に、Sheet3のA列を作業列として使用するものとします。
　まず、Sheet3のA2セルに次の数式を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet1!$E:$E,ROW())),Sheet1!$E2+COUNTIF(Sheet1!$E$1:INDEX(Sheet1!$E:$E,ROW()-1),INDEX(Sheet1!$E:$E,ROW())),"")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

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

=IF(ROWS($2:2)>COUNT(Sheet1!$E:$E),"",SMALL(Sheet1!$E:$E,ROWS($2:2)))

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

=IF($A2="","",LARGE(Sheet1!$D:$D,$A2))

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

=IF(ROWS($2:2)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$A,MATCH(ROWS($2:2),Sheet3!$A:$A,0)))

　次に、Sheet2のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

【方法2】作業列を使わずに、関数のみで処理を行う方法
　※こちらの方法は、元データの人数が余りにも多くなりますと、計算処理に要するパソコンの負荷が大きくなりますが、処理する人数が2000人程度までなら、問題にはならないと思います。

　まず、Sheet2のA2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet1!$E:$E),"",SMALL(Sheet1!$E:$E,ROWS($2:2)))

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

=IF($A2="","",LARGE(Sheet1!$D:$D,$A2))

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

=IF(ISNUMBER($A2),INDEX(Sheet1!$A:$A,SUMPRODUCT(ROW(INDEX(Sheet1!$E:$E,ROW(Sheet1!$E$1)+1):INDEX(Sheet1!$E:$E,MATCH(9^9+1,Sheet1!$E:$E)))*(INDEX(Sheet1!$E:$E,ROW(Sheet1!$E$1)+1):INDEX(Sheet1!$E:$E,MATCH(MAX(Sheet1!$E:$E)+1,Sheet1!$E:$E))=$A2)*(COUNTIF(OFFSET(Sheet1!$E$1,,,ROW(INDEX(Sheet1!$E:$E,ROW(Sheet1!$E$1)+1):INDEX(Sheet1!$E:$E,MATCH(9^9+1,Sheet1!$E:$E)))-ROW(Sheet1!$E$1)+1),$A2)=COUNTIF($A$1:$A2,$A2)))),"")

　次に、Sheet2のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

回答No.5

tom04

こんにちは！
すでに回答は出ていますので、参考程度で・・・

データはSheet1の2行目以降にあり、Sheet2の2行目以降に表示させるとします。
とりあえず、Sheet1の100行目までデータがあっても対応できる数式です。

Sheet2のA2セルに
=IF(COUNTIF(Sheet1!E:E,"<=5")<ROW(A1),"",SMALL(Sheet1!E:E,ROW(A1)))

B2セル（配列数式になってしまいます）に
=IF(A2="","",INDEX(Sheet1!$D$1:$D$100,SMALL(IF(Sheet1!$E$1:$E$100=A2,ROW($A$1:$A$100)),COUNTIF($A$2:A2,A2))))

※　この画面からB2セルにコピー＆ペーストしただけではエラーになると思います。
B2セルに貼り付け後、F2キーを押します。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

C2セル（これも配列数式です）に
=IF(A2="","",INDEX(Sheet1!$A$1:$A$100,SMALL(IF(Sheet1!$E$1:$E$100=A2,ROW($A$1:$A$100)),COUNTIF($A$2:A2,A2))))
として、Shift＋Ctrl＋Enter

最後にA2～C2セルを般指定しC2セルのフィルハンドルで下へオートフィルでコピーすると
希望に近い形にならないでしょうか？

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

回答No.4

MackyNo1

訂正です

お分かりになったかもしれませんが（修正しないでもおそらく問題ないと思いますが）、C2セルに入力する数式は正しくは以下のような数式になります。

=INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$E$2:$E$100<>$A2)*1000+ROW($E$2:$E$100),),COUNTIF($A$2:$A2,$A2)))

すなわち、数式を作成するときにB2セルを右にオートフィルコピーして、Sheet1!D:Dの部分をA:Aに参照範囲を変更したので、相対参照のA2部分がB2になっていました(以下のように複合参照の数式にすれば良かったのですが）。

回答No.3

KURUMITO

重複した順位がある場合には多少作業が複雑になります。元の表がシート1に有るとしてF2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(E2="","",E2*100+COUNTIF(E$2:E2,E2))

次にシート2に1位から5位までの表を表示させるとしたらエラー処理も含めてA2セルには次の式を入力してC2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(COLUMN(A1)>3,ROW(A1)>COUNT(Sheet1!$F:$F)),"",IF(INDEX(Sheet1!$E:$E,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0))>5,"",INDEX(Sheet1!$A:$E,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),IF(COLUMN(A1)=1,5,IF(COLUMN(A1)=2,4,IF(COLUMN(A1)=3,1,""))))))

回答No.2

MackyNo1

重複する順位がある場合にでも対応できる数式です。
例えば、元シートがSheet1にあり、別シートのA2セル以下に順位を表示するなら以下の数式を入力してください。

A2セル
=SMALL(Sheet1!$E$2:$E$100,ROW(A1))

B2セル
=INDEX(Sheet1!D:D,SMALL(INDEX((Sheet1!$E$2:$E$100<>$A2)*1000+ROW($E$2:$E$100),),COUNTIF($A$2:A2,$A2)))

C2セル（B2セルの参照範囲が違うだけ）
=INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$E$2:$E$100<>$A2)*1000+ROW($E$2:$E$100),),COUNTIF($A$2:B2,$A2)))

回答No.1

mu2011

別表の順位列に１～ｎ（データ表のＥ列と同じ書式）が入力、各表には見出し行ありとしています。
但し、重複順位は考慮していません。
（１）別表のB2は、=INDEX(データ表!D:D,MATCH(A2,データ表!E:E,0))
（２）別表のC2は、=INDEX(データ表!A:A,MATCH(A2,データ表!E:E,0))
（３）B2:C2を選択、下方向にコピー