【Excel】リストを任意の順序に表示する方法

名簿など、あらかじめリストになっているものを、もともとのデータはそのままで。任意の順序で並べて表示したいことはないでしょうか。

例えば、顧客リストをルート営業の順に表示したり、社員名簿から、係の当番順に表示したりといった用途です。

上の図ではvlookupを使っている関係上、左側の票は、名前の左に数字を書かなければ関数が使えません。

左側の表は、すべて手入力です。右側の表の名前が表示されているセルにvlookup関数を入れています。
右側の票の”1″が表示されているところがE2セルだとすると、F2セルには次の式が入ります。

=VLOOKUP(E2,$B$2:$C$8,2,FALSE)

名前の右側に数字を書きたい場合

左の表を、名前の右側に数字が来るように並び替えると、vlookupがエラーになってしまいます。
これは、vlookupの仕様で、検索対象は検索範囲内の左側にないといけません。

そこで、別の関数を組み合わせて実現するとこうなります。

=INDIRECT(ADDRESS(1+MATCH(E2,$C$2:$C$8,0),2))

一番内側のmatch関数は、検索範囲の中で、検索対象が何番目にマッチしたかを返します。
この例では、match関数が返す値は “3” になります。
“3” とは、左の表の上から4、2、1…つまり、検索対象の1は3番目に位置しています。という意味。
この “3” を使って、3番目にある名前を表示するために、次の二つの関数を使っています。

INDIRECT

indirectは、文字列で指定したセルの内容をそのまま表示するという関数です。

ADDRESS

address関数はセル参照を返します。
今回のようにセルの場所を計算で出す場合などで、最終的にセル参照の形で取り出したいときに使えます。

指定するセルは、matchで取得した “3” を利用します。

行は、1行目の空白にmatchで取得した “3” を足して、1+matchで、4行目になります。
列はB列固定なので、指定する形は “2” となります。

まとめ

vlookupの制限により、列を入れ替えるだけで結構な手間になりましたが、いろいろな関数を勉強する意味では、役に立ったのではないでしょうか。

もっと便利な方法があったらコメントいただけるとありがたいです。

Originally posted 2019-11-13 12:52:35.