Excelを使用していると、新しいデータを追加する度に並び替えやフィルターをするのが面倒な時があります。決まった基準の並べ替えを自動化させたい方向けの方法です。
このような事ができるようになります。
今回は社員名簿を常に年齢順に並べ替えを行います。
社員名簿の方を追加しても、自動的に年齢順にすることも可能です。
今回は、Value・Countif・Rank・Index・Match・Iferror・Ifna関数を使いますよ。
新しく追加したデータが自動で並び替えができる点がおススメです。
- 手順①使用するデータの確認
- 手順②CountifとRank関数で、並べ替え用の番号を作る
- 手順③並べ替えの番号を基にIndex関数とMatch関数でピックアップする
- 手順④フィルターの付け方
手順①：使用するデータの確認
まず今回使用するのはこちらの社員名簿のデータを使用します。
表はテーブル化しておきましょう。（テーブル化しておくと新しい人が追加した時も同時に計算してくれるためです。）
８名の従業員がおり、名前・生年月日・年齢・住所・入社年月日・勤続年数・社内評価点
※商品のリストなどでも使用可能です。
今回はこの表を常に年齢順で表示させたいと思います。
今回お伝えする方法では文字列のソートができないので、名前と住所が元のソートはできませんのでご了承下さい。（できる方法があれば追記します。）
手順②：CountifとRank関数で、並べ替え用の番号を作る
まずは、並べ替えをするために順序を決める必要があります。
その順序を決めるための関数が、CountifとRank関数になります。
Countifはお馴染みの関数で、指定した範囲内に指定したワードがいくつあるか数える関数です。
Rank関数は指定した範囲内で、順序を割り振ります。
一見Rank関数だけで事足りると思われますが、同順位となった場合の対策にCountifが役立ちます。
それではまず、生年月日の並べ替えを作っていきます。
F列に列を追加していきます。
※今回生年月日は日付形式なので、一度数字形式に直すためValue列を設けています。
日付形式だとRank関数の計算ができないためです。
Value関数は数字を表す文字列を数字に変換する関数です。
生年月日をValueすると、このように数字になります。
このValueで出した値を基に、CountifとRank関数でそれぞれ計算します。
まずはCountifは=COUNTIF($F$3:F3,F3)のように直接セルを参照するようにします。
Countifの範囲を「絶対参照：相対参照」にすることで、重複した時のカウントを連番にすることができます。
続いてはValueで算出した値を基にRank関数で順位を決めます。
Rank関数は年齢が低い人を高い数字にしますので、昇順で設定するのを忘れずに。
同順位のものを連番にする設定をします。
この図の場合、３・４が同じ順位になっていますのでその設定を行います。
Rank列の隣に１列追加（C＋R）しCountifの値-1とRankの値を足してあげます。
Countifの値-1＋Rankの値にすることで、ランク関数でつけた順位が連番になります。
Countifの値‐１するところがポイントです。
これで自動的にピックアップする準備が整いましたので、手順③に移ります。
手順③：並べ替えの番号を基にIndex関数とMatch関数でピックアップする
Index関数とMatch関数といえばお馴染みのVlookupの上位互換の組み合わせ関数です。
今回は別シートに表示するようにしますので、以下のように予め表を作成しておきます。
Noから手順②で求めたCountifの値とRank関数の合算値を基に情報をピックアップします。
No列（１列目）の情報をピックアップする式は下記の式です。
=INDEX(テーブル3,MATCH(ROW()-2,テーブル3[C+R],0),1)
特にポイントはMatch関数の検索値が下の行に行く毎に＋１されるようにすることです。
ROW関数はセルの位置の行数を見る関数ですので、自動的に＋１となるようにできます。
あとは、その他の項目も同じように式を入れていきます。
入力する際は=INDEX(テーブル3,MATCH(ROW()-2,テーブル3[C+R],0),1)の列数に気を付けてください。
全て入力すると、このようになります。
生年月日などの日付形式に直してあげます。
これで自動での並べ替え完成です。
こちらが、社員名簿でフィルターを使って並べ替えを行ったものです。
社員名簿に人を追加すると…
これのいいところは、社員を自動追加しても、その設定が反映されることです。
トム・バートンという人を追加しました。
すると、こちらも追加され自動的に年齢順になっていることがわかります。
この自動追加ができるのはExcelのテーブル機能のお陰です。
他のExcelのようなソフトでは末尾に追加する場合、都度式（Index関数とMatch関数の列の式）の編集が必要になります。
手順④：フィルターの付け方
フィルターも関数で追加することが可能です。
今回のケースで住所が東京の人という条件のフィルターをかけます。
Rank関数の列を１つ追加します。
一つめのRank関数を更に順位をつけています。
このようにすることで、１，４という順番だったのを、１，２という連番にすることができます。
Rank関数が空欄になっているところがありますが、今回東京都に住んでいる人だけ表示させるので１列目のRank関数の列の式の所をIf関数で東京でない場合は空欄にしています。
Rank２とC+R列の方は空欄のところはエラーになりますので、Iferror関数でエラー回避しています。
一方、自動で出力している方は、２つの問題が発生します。
Rank2列の追加による値ずれは、Index関数の列を合わせてあげれば解決します。
=INDEX(テーブル3,MATCH(ROW()-2,テーブル3[C+R],0),1)←の部分
もう一つの方は、IFNA関数でエラー回避します。
=IFNA(INDEX(テーブル3,MATCH(ROW()-2,テーブル3[C+R],0),1),””)
するとこのようになります。
以上がExcelの関数だけで並び替えやフィルターを自動化する方法（SORT関数の代用）の方法です。
まとめ
いかがでしたでしょうか。
フィルターでやれば早い時もありますが、並べ替えの基準は固定だから、常にその順番が反映されるようにしたいという方にとってはおススメなテクニックです。
ぜひ使ってみてください。
コメント