Excelの関数だけで並び替えやフィルターを自動化する方法(SORT関数の代用)

未分類

Excelを使用していると、新しいデータを追加する度に並び替えやフィルターをするのが面倒な時があります。決まった基準の並べ替えを自動化させたい方向けの方法です。

このような事ができるようになります。
今回は社員名簿を常に年齢順に並べ替えを行います。

できることの画像

社員名簿の方を追加しても、自動的に年齢順にすることも可能です。

できることの画像②

この記事は以下の人向きです。

  • データを追加したら自動的に並べ替えをしたい。
  • 並べ替えには決まった基準がある
  • SORT関数が使えない
  • 数字に関する並べ替えをしたい
    ※今回の方法は文字の並べ替えができません。
yukimi
yukimi

今回は、Value・Countif・Rank・Index・Match・Iferror・Ifna関数を使いますよ。
新しく追加したデータが自動で並び替えができる点がおススメです。

手順
  • 手順①
    使用するデータの確認
  • 手順②
    CountifとRank関数で、並べ替え用の番号を作る
  • 手順③
    並べ替えの番号を基にIndex関数とMatch関数でピックアップする
  • 手順④
    フィルターの付け方

手順①:使用するデータの確認

まず今回使用するのはこちらの社員名簿のデータを使用します。
表はテーブル化しておきましょう。(テーブル化しておくと新しい人が追加した時も同時に計算してくれるためです。)

テストデータ

8名の従業員がおり、名前・生年月日・年齢・住所・入社年月日・勤続年数・社内評価点
※商品のリストなどでも使用可能です。

今回はこの表を常に年齢順で表示させたいと思います。

今回お伝えする方法では文字列のソートができないので、名前と住所が元のソートはできませんのでご了承下さい。(できる方法があれば追記します。)

手順②:CountifとRank関数で、並べ替え用の番号を作る

まずは、並べ替えをするために順序を決める必要があります。
その順序を決めるための関数が、CountifとRank関数になります。

Countifはお馴染みの関数で、指定した範囲内に指定したワードがいくつあるか数える関数です。
Rank関数は指定した範囲内で、順序を割り振ります。

一見Rank関数だけで事足りると思われますが、同順位となった場合の対策にCountifが役立ちます。

それではまず、生年月日の並べ替えを作っていきます。
F列に列を追加していきます。

列の追加

※今回生年月日は日付形式なので、一度数字形式に直すためValue列を設けています。
日付形式だとRank関数の計算ができないためです。
Value関数は数字を表す文字列を数字に変換する関数です。

生年月日をValueすると、このように数字になります。

Value列の設定

このValueで出した値を基に、CountifとRank関数でそれぞれ計算します。
まずはCountifは=COUNTIF($F$3:F3,F3)のように直接セルを参照するようにします。

Countif列の設定

Countifの範囲を「絶対参照:相対参照」にすることで、重複した時のカウントを連番にすることができます。

重複している時の状態の画像

続いてはValueで算出した値を基にRank関数で順位を決めます。

Rank列の設定

Rank関数は年齢が低い人を高い数字にしますので、昇順で設定するのを忘れずに。

同順位のものを連番にする設定をします。
この図の場合、3・4が同じ順位になっていますのでその設定を行います。

Rank列の隣に1列追加(C+R)しCountifの値-1とRankの値を足してあげます。

Countif-1+Rank関数

Countifの値-1+Rankの値にすることで、ランク関数でつけた順位が連番になります。
Countifの値‐1するところがポイントです。

これで自動的にピックアップする準備が整いましたので、手順③に移ります。

手順③:並べ替えの番号を基にIndex関数とMatch関数でピックアップする

Index関数とMatch関数といえばお馴染みのVlookupの上位互換の組み合わせ関数です。

今回は別シートに表示するようにしますので、以下のように予め表を作成しておきます。

別シート

Noから手順②で求めたCountifの値とRank関数の合算値を基に情報をピックアップします。
No列(1列目)の情報をピックアップする式は下記の式です。
=INDEX(テーブル3,MATCH(ROW()-2,テーブル3[C+R],0),1)

ピックアップ

特にポイントはMatch関数の検索値が下の行に行く毎に+1されるようにすることです。
ROW関数はセルの位置の行数を見る関数ですので、自動的に+1となるようにできます。

あとは、その他の項目も同じように式を入れていきます。
入力する際は=INDEX(テーブル3,MATCH(ROW()-2,テーブル3[C+R],0),1)の列数に気を付けてください。

全て入力すると、このようになります。

全て式入力後

生年月日などの日付形式に直してあげます。
これで自動での並べ替え完成です。

全て式入力後(+日付設定)

こちらが、社員名簿でフィルターを使って並べ替えを行ったものです。

並べ替えを手動で行った時

社員名簿に人を追加すると…

これのいいところは、社員を自動追加しても、その設定が反映されることです。

トム・バートンという人を追加しました。

社員名簿に人を追加した場合

すると、こちらも追加され自動的に年齢順になっていることがわかります。

社員名簿に人を追加した場合の結果

この自動追加ができるのはExcelのテーブル機能のお陰です。
他のExcelのようなソフトでは末尾に追加する場合、都度式(Index関数とMatch関数の列の式)の編集が必要になります。

手順④:フィルターの付け方

フィルターも関数で追加することが可能です。
今回のケースで住所が東京の人という条件のフィルターをかけます。

Rank関数の列を1つ追加します。

Rankの列を追加する理由

一つめのRank関数を更に順位をつけています。
このようにすることで、1,4という順番だったのを、1,2という連番にすることができます。

Rank関数が空欄になっているところがありますが、今回東京都に住んでいる人だけ表示させるので1列目のRank関数の列の式の所をIf関数で東京でない場合は空欄にしています。

フィルターのかけ方

Rank2とC+R列の方は空欄のところはエラーになりますので、Iferror関数でエラー回避しています。

エラー回避

一方、自動で出力している方は、2つの問題が発生します。

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関数の代用)の方法です。

まとめ

いかがでしたでしょうか。

フィルターでやれば早い時もありますが、並べ替えの基準は固定だから、常にその順番が反映されるようにしたいという方にとってはおススメなテクニックです。

ぜひ使ってみてください。

コメント