エクセルで昨年度成績付きの今年度成績一覧表を作成

質問

エクセルで100名の名前入り成績一覧表を作成予定です。
条件は以下の通り。
※使用しているのはWindows　XPです
１、昨年度の成績が今年度の成績の下の行に表示されたまま、今年度の成績順に並べ変える
２、今年度の成績合計点数の順位で並べる
３、成績はA,B,C,D,E,F,Gという項目別に5段階評価

今までは昨年度と今年度の名前にそれぞれ番号をつる→今年度成績順に並べ替え順位を入れる→改めて先につけた名前の番号順に並べ替える→その順位を昨年度の表に入れる→昨年度と今年度の表を一つにくっつける→順位の列にフィルターにかけて全体を並べ変える
という作業をしていましたが、今年度の成績が何度も変更になり、その度に作成しなおしを繰り返しており、もっと簡単に昨年度と今年度をくっつけたまま、今年度成績順で並べ替えできないかと悩んでいます。
関数を使った方法を含め、何か良い方法があれば教えて下さい。

回答No.4

KURUMITO · ベストアンサー

回答No2,3です。
シート2のA列に順位を入れるとしたらシート2のA2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>COUNTA(Sheet1!$A$2:$A$500)*2,"",IF(AND(COLUMN(A1)=1,MOD(ROW(A1),2)=1),INT(INDEX(Sheet1!$K:$K,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0))),IF(INDEX(Sheet1!$A:$I,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0),COLUMN(A1)-1)=0,"",INDEX(Sheet1!$A:$I,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0),COLUMN(A1)-1))))

なお、順位は同じ点数の人が複数あれば行が下方になっても同じ順位となります。

回答No.3

KURUMITO

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

=IF(AND(A1="",A2=""),"",IF(MOD(ROW(A1),2)=1,SUM(C2:I2),""))

K2セルへの入力の式やシート2への入力の式は回答No2の場合と同じです。

お礼

色々と助けていただきましてありがとうございました。
お陰さまで上手く作成することができました。
とても一人ではたどりつけない大変な計算式を作成していただいことに深く感謝いたします。
本当にありがとうございました。

補足

新しい計算式をありがとうございました。
あっという間に順位付けができました。
どういう設定なのかしりたいところです（笑）

何度もすみません、追加の質問です。
あとシート２のA行に順位を入れたいのですが、数式はB2に貼り付けても見た目は問題ないのですが、参照しているセルがすれてしまうのですが、何か良い方法はないでしょうか。

よろしくご教示いただけますようお願いいたします。

回答No.2

KURUMITO

シート1に元データが次のように入力されているとします。シート2には今年度の成績順で並べ替えた表を表示させることにします。
シート1ではA2セルから下方に二つのセルを結合して一人の名前を入力した形で入力されているとします。したがってA2セルに、A4セルに、A6セルに…と氏名が入力されているとします。
B1セルから例えばI1セルまでに項目名が入力されているとします。それぞれの項目名の下方には氏名ごとに今年度の成績とその下行に昨年度の成績がA,B,C,D,E,F,Gの7段階でそれぞれ入力されているとします。
そこで作業列としてJ列のJ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(A1="",A2=""),"",IF(MOD(ROW(A1),2)=1,SUM(COUNTIF(B2:I2,{"A","B","C","D","E","F","G"})*{7,6,5,4,3,2,1}),""))

これによって今年度の7段階の成績が数値化されて表示されます。
作業列としてK2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(A1="",A2=""),"",IF(MOD(ROW(A1),2)=1,RANK(J2,J:J,0)+COUNTIF(J$2:J2,J2)/100,IF(MOD(ROW(A1),2)=0,K1+0.005,"")))

上の式では同点になった人が多数いる場合にも対応できる式となっています。
そこでシート2に移ります。
B1セルからI1セルまでにはシート1と同じ項目名を入力します。
A2セルには次の式を入力しI2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>COUNTA(Sheet1!$A$2:$A$500)*2,"",IF(INDEX(Sheet1!$A:$I,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0),COLUMN(A1))=0,"",INDEX(Sheet1!$A:$I,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0),COLUMN(A1))))

これでシート1で今年度の成績が変更されるたびにシート2では今年度の成績順に名前などが瞬時に並べ替えられます。

補足

ものすごい関数をありがとうございました。
サンプルでやってみました！できそうです。
図々しいお願いですが、実は評価は初めから数字で（５，４，３，２，１）入力されています。
そして、A２に名前、B2に社員番号、B3に所属（２２ABなど）が入っています。
その場合のシート１に入れる数式と、シート２に入れる数式を教えていただけないでしょうか。
なお、Iまで項目があるとして、Jに成績の合計点数を表示させたいと思っています。

教えていただきました数式を編集してやってみているのですが、どうしてもうまくいかないのでお伺いしている次第です。
本当にご面倒をおかけいたしますがよろしくお願いいたします。

回答No.1

aokii

昨年度の成績を今年度の成績の下の行ではなく、右の列に表示すれば問題ないのですが、どうしても下の行に表示するなら、右の列を、別のシートで下の行に絶対参照で引用するのはいかがでしょう。

お礼

早々に回答をしていただきましてありがとうございました。
活用させていただきます。

補足

早々にご返答いただきましてありがとうございました。
実は、項目は実際は40項目あり、昨年度と比較するのに上下に並んでいた方が見やすいということで下の行に入れることを希望しておりました。
絶対参照するということは、今年度の成績表を作成する際に、下に一行作成し名前を入れておき、常に別シートの昨年度評価（名前入り）を絶対参照させ表示させるということですね。
これで間違いないでしょうか。