本ページはプロモーションが含まれています
EXCELで元のデータはそのまま
指定した条件で並び替えた表を別に作成する方法を紹介します
EXCELのバージョンによってジャンプしてください
EXCEL2021,OFFICE365以降では便利なSORT/SORTBY関数
それ以前のバージョンの方は下方へどうぞ
EXCEL2021、OFFICE365で使えるSORT/SORTBY関数
サンプル
サンプルの表はこちら
獲得ポイントが多い順に並べ替えます
SORT関数
SORT関数の使い方です
別に作成したい表の左上のセルに入力します
配列では並べ替えたい対象データ範囲を選択
続いて並べ替えインデックス
これは何行目、または何列目のデータで並べ替えるかを指定します
配列で選んだ範囲の中での何行目、何列目を入力します
獲得ポイントで並べ替えるので２を指定します
並べ替え順序を指定します
省略した場合は「１」（昇順）になります
例では獲得ポイントが多い人を上位表示するので「ー１」を指定します
| 1 | 昇順（小さい順） |
| -1 | 降順（大きい順） |
最後に並べ替え基準です
省略した場合は「FALSE」(行で並べ替え）になります
今回は「FALSE」行で並べ替えを指定します
| TRUE | 列で並べ替え |
| FALSE | 行で並べ替え |
結果はこちら
SORT関数ひとつで並べ替えた別の表ができました(^^♪
複数条件で並べ替えできるSORTBY関数
サンプルを少し変更しました
浅井さんと今村さんが同じ獲得ポイント
同ポイントの場合、昨年順位が上の人を上位とします
SORT関数を使用すると表の上に記載されている浅井さんを上位に表示します
そこで複数条件の指定ができるSORTBY関数を使用します
配列では並べ替えるデータ全体を選択
基準配列では対象となる配列を指定します
獲得ポイントの「C3:C9」です
SORT関数では「何列目/何行目」での指定なので間違いやすいですね
並べ替え順序は獲得ポイントが多い人が上位ですので「-1」（降順）を指定します
２つ目の条件を指定します
基準配列２では昨年順位ですので「D3:D9」を指定
並べ替え順序２では昨年順位の値が小さいほど上位なので「１」（昇順）を指定します
結果はこちらです
同ポイントの今村さん、浅井さんの判定も正しくできていますね
SORTBY関数の注意点
SORTBY関数では行方向の並べ替えのみ対応しています
その場合は行列を入れ替えるTRANSPORT関数が便利です
以前のバージョンで元の表はそのままで並べ替えた表を作る！
サンプル
サンプルの表はこちら
獲得ポイントが多いほど上位、同じポイントなら昨年順位が上の人が上位とします
RANK.EQ関数で順位を求める
RANK.EQ関数を使うと順位が求められます
指定方法は
RANK.EQ(数値,参照範囲,順序)
上の画像を例に紹介します
F3のセルに入力したのは
数値（C3)は参照（C3～C9)の中で降順（大きい順）に何番目？
という意味です
ちなみに最後に指定した順序の指定方法はこちら
| 0 | 降順（大きい順) |
| 1 | 昇順（小さい順） |
F3のセルに入力＆確定したあとでF9までコピーします
獲得ポイントの多い順番が求められました
獲得ポイントが同じ浅井さん、中村さんは同じ順位で表示されています
５位が２名いるので６位が欠番ですね
RANK.AVGもあります
RANK.EQと似た関数にRANK.AVG関数があります
書式はRANK.EQと同じ
同じ順位のときの結果がちがいます
その結果はこちら
５位と６位の平均なので「5.5」という結果です
はっきりいってRANK.AVGが活躍する場面が思い浮かびません
便利な使い方をご存じでしたら教えてください(^_-)-☆
同順位をCOUNTIFS関数で順位付け
同じ獲得ポイントの人を順位付けする方法です
昨年順位が上の人を上位にします
COUNTIFS関数を使いましょう
書式はこちらです
=COUNTIFS(検索条件範囲1,検索条件1,検索条件範囲2,検索条件2,・・・・・）
H3に入力した上の例で説明します
| 検索条件範囲１ | $C$3:$C$9 |
| 検索条件１ | C3 |
| 検索条件範囲2 | $D$3:$D$9 |
| 検索条件2 | “<“&D3 |
1. $C$3～$C$9の中でC3と同じ値を探す
2. 同じ値があった場合、$D$3:$D$9の中でD3より大きいものを１としてカウントする
H3の内容をH9までコピーします
結果はこちら
獲得ポイントでは同じ浅井さん、中村さん
COUNTIFS関数で昨年順位が上位の中村さんは「0」、下位の浅井さんは「1」が入りました
RANK.EQの結果とCOUTIFSの結果の合計が順位になりますね
順位で並べ替えた表を作る
順位で並べ替えた表を別に作るにはVLOOKUP関数を使用します
そのまえに１つ大事な作業をしましょう
最終順位をA列に移動またはコピーします
理由はVLOOKUP関数では一番左にある値をベースにするからです
A15:A21には１～７の順位を入力しています
=VLOOKUP(A15,A3:D9,2,FALSE)
の例で紹介します
| 検索値 | A15 |
| 範囲 | A3:D9 |
| 列番号 | 2 |
| 検索方法 | FALSE(完全一致） |
A3:D9の範囲の一番左の列(A列)でA15と同じ値（完全一致）を求める
同じ行で２列目の値を返す
という意味です
B15をD15までコピーします
獲得ポイント、昨年順位にも名前が入ってしまいました
理由は列番号「２」がC,D列にも適用されたためです
手入力で修正してもいいですが、関数を使えば簡単です
COLUMN()関数を使用します
COLUMN()関数は現在の列番号を返す関数です
列番号「２」の代わりに「COLUMN()」を入力します
これなら修正の必要はありませんね
あとは２１行目までコピーしたら完了です
まとめ
自分にあった方法で並べ替えてくださいね
Excelの魔術師へ！ さいしょの一歩ほかにもいろいろ書いてます
↓ 下記からご覧ください
サイトマップ
ブックマークして頂けると嬉しいです
よろしくお願いいたします
コメント