エクセルで100名の名前入り成績一覧表を作成予定です。
条件は以下の通り。
※使用しているのはWindows XPです
1、昨年度の成績が今年度の成績の下の行に表示されたまま、今年度の成績順に並べ変える
2、今年度の成績合計点数の順位で並べる
3、成績はA,B,C,D,E,F,Gという項目別に5段階評価
今までは昨年度と今年度の名前にそれぞれ番号をつる→今年度成績順に並べ替え順位を入れる→改めて先につけた名前の番号順に並べ替える→その順位を昨年度の表に入れる→昨年度と今年度の表を一つにくっつける→順位の列にフィルターにかけて全体を並べ変える
という作業をしていましたが、今年度の成績が何度も変更になり、その度に作成しなおしを繰り返しており、もっと簡単に昨年度と今年度をくっつけたまま、今年度成績順で並べ替えできないかと悩んでいます。
関数を使った方法を含め、何か良い方法があれば教えて下さい。
No.4ベストアンサー
- 回答日時:
回答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
- 回答日時:
J2セルへは次の式を入力し下方にオートフィルドラッグします。
=IF(AND(A1="",A2=""),"",IF(MOD(ROW(A1),2)=1,SUM(C2:I2),""))
K2セルへの入力の式やシート2への入力の式は回答No2の場合と同じです。
この回答への補足
新しい計算式をありがとうございました。
あっという間に順位付けができました。
どういう設定なのかしりたいところです(笑)
何度もすみません、追加の質問です。
あとシート2のA行に順位を入れたいのですが、数式はB2に貼り付けても見た目は問題ないのですが、参照しているセルがすれてしまうのですが、何か良い方法はないでしょうか。
よろしくご教示いただけますようお願いいたします。
色々と助けていただきましてありがとうございました。
お陰さまで上手く作成することができました。
とても一人ではたどりつけない大変な計算式を作成していただいことに深く感謝いたします。
本当にありがとうございました。
No.2
- 回答日時:
シート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では今年度の成績順に名前などが瞬時に並べ替えられます。
この回答への補足
ものすごい関数をありがとうございました。
サンプルでやってみました!できそうです。
図々しいお願いですが、実は評価は初めから数字で(5,4,3,2,1)入力されています。
そして、A2に名前、B2に社員番号、B3に所属(22ABなど)が入っています。
その場合のシート1に入れる数式と、シート2に入れる数式を教えていただけないでしょうか。
なお、Iまで項目があるとして、Jに成績の合計点数を表示させたいと思っています。
教えていただきました数式を編集してやってみているのですが、どうしてもうまくいかないのでお伺いしている次第です。
本当にご面倒をおかけいたしますがよろしくお願いいたします。
No.1
- 回答日時:
昨年度の成績を今年度の成績の下の行ではなく、右の列に表示すれば問題ないのですが、どうしても下の行に表示するなら、右の列を、別のシートで下の行に絶対参照で引用するのはいかがでしょう。
この回答への補足
早々にご返答いただきましてありがとうございました。
実は、項目は実際は40項目あり、昨年度と比較するのに上下に並んでいた方が見やすいということで下の行に入れることを希望しておりました。
絶対参照するということは、今年度の成績表を作成する際に、下に一行作成し名前を入れておき、常に別シートの昨年度評価(名前入り)を絶対参照させ表示させるということですね。
これで間違いないでしょうか。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 高校 高校生の成績の付け方【新課程】について 教えてください 私は高校1年生です 今年から新課程という事で 1 2023/02/28 20:24
- 大学・短大 大学の成績 2年次の成績で、専門累積GPAが4.25でした。 これって普通に良い成績だと思っていいで 6 2022/07/19 20:57
- 営業・販売・サービス 自分個人の営業成績が悪いとき、毎日のように叱責されるのは普通でしょうか。 特段サボっているわけではあ 3 2022/06/26 16:00
- 世界情勢 勉強しない日本の大学生と、死に物狂いで勉強するアメリカの大学生。日本が負けて当然の理由ですね^^ 11 2022/10/24 07:09
- Excel(エクセル) エクセルでキーリストからデータを取り出して1枚1枚印刷するには? 11 2022/06/27 09:52
- 中学校 高校いけないかもしれない 4 2022/09/28 20:10
- 野球 ぶっちゃけ坂本より山田の方が先に引退しそうじゃね? 6 2022/12/04 19:12
- 面接・履歴書・職務経歴書 私は偏差値35の本物のfラン大学に通っているのですが、成績がその大学でトップクラスなのは就活に有利で 5 2023/03/27 14:03
- Excel(エクセル) 達成率の計算式を教えていただきたいです。 KPIでの不良削減達成率の計算方法を教えて下さい。 昨年度 3 2022/04/10 15:11
- 個人事業主・自営業・フリーランス 個人事業主で建設業をしています。 私は専従者で主人が代表です。 令和4年度分売上高が3000万で所得 5 2022/06/22 13:06
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
絶対にいけるWindowsXPsp3の無...
-
Windows XP SP3のインストール...
-
アウトルックを「タスクバー」...
-
シムズ4(PS4版) マイライブラリ...
-
WindowsXPを最近のパソコンに...
-
Windows XPをダウンロードして...
-
Windows xpにWindows10を無理や...
-
WindowsXPが起動しなくなったと...
-
パソコンが操作できなくなりま...
-
プロダクトキー
-
フリープリントソフト「かんた...
-
パソコンで一定の文字数を打つ...
-
windowsxpのパスワード忘れた
-
コンピュータが数秒毎に更新さ...
-
パソコンが再起動中にフリーズ...
-
google日本語入力が削除できない
-
パソコン起動時に2つのユーザー...
-
パソコンでCDを見たい、見る...
-
メモリ、SSD、HDDの違いを教え...
-
[virtualbox] Windows XP のVHD...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
WindowsXPを最近のパソコンに...
-
絶対にいけるWindowsXPsp3の無...
-
Windows XP SP3のインストール...
-
シムズ4(PS4版) マイライブラリ...
-
windows 終了時にプログラムを...
-
アウトルックを「タスクバー」...
-
WindowsXPのプロダクトキーを紛...
-
Windows xpにWindows10を無理や...
-
パソコンでCDを見たい、見る...
-
メモリ、SSD、HDDの違いを教え...
-
アカウント名とユーザー名との...
-
windows10 フォルダを上書きす...
-
windows xp proが起動しなくな...
-
やっぱり現代のPCにWindowsXPを...
-
電源ボタンでスリープするよう...
-
プロダクトキー
-
win 10 のログイン時に不要なユ...
-
Windows vistaのパスワードが分...
-
ラズパイでwindows XPは動くのか
-
Windows アカウント名 とは?
おすすめ情報