Office365に追加されたスピル機能とSEQUENCE関数とUNIQUE関数を使って一覧の縦横変換をしてみた

2021年3月3日 2021年10月13日

おはこんばんにちは、ゆーや＠Excelがまぁまぁ使える人です。

年度末ということで、来期の部署の異動情報を共有してもらったんですが、メールで転送されたときに残念な形になっていました。

この情報、Office365で2019年に追加された、スピル機能、SEQUENCE関数、UNIQUE関数を活用することでいい感じに整形出来たので、やり方を共有します。

注意

Office 365 ProPlusでないと今回ご紹介するやり方は実践出来ません。

こんな方におすすめ

Office365のスピル機能とSEQUENCE関数とUNIQUE関数で一覧の縦横変換をしてみた

まず、スピル関数は何かというと、2019年にoffice365に追加された新しい機能です。次の動画がわかりやすいです。

このタイミングで、SEQUENCE関数やらUNIQUE関数、XLookupなどが追加されました。噂には聞いていたのですが、使っていみたら結構便利でしたので、今回試してみました。

ヘッダは、氏名、異動前、異動後を列ヘッダにしたかったので、以前からあるINDEX関数と、スピル機能とUNIQUE関数とSEQUENCE関数を使いました。

UNIQUE関数で重複ないリストを取得
UNIQUE関数は指定した範囲の値を重複なしで取得してくれる関数です。
UNIQUE(A2:A13)というように重複なしで取得したいリストを指定をすれば、次のように縦に重複ないリスト値を自動設定してくれます。
SEQUENCE関数とINDEX関数で縦横変換
上の一覧を縦横変換したいので、INDEX関数とSEQUENCE関数を使います。
INDEX関数は、第１引数にリストの範囲を指定して、第２引数で行の数を入れる事で、指定行の値を取れます。

第2引数の指定は、このままだと芸が無いので、SEQUENCE関数で作り出すようにします。
SEQUENCE(1,3)とすれば自動的に次のような結果が表示されます。
C1セルにSEQUENCE(1,3)と入れたときの実行結果のイメージ
これを1のUNIQUE関数と組み合わせて
INDEX(UNIQUE(A2:A13),SEQUENCE(1,3))
と指定するコトで、スピル機能が自動生成してくれるゴーストが右に自動で列を追加してくれます。

明細もヘッダで使った関数を用いて設定します。

INDEX関数とSEQUENCE関数で値を縦横変換取得
ヘッダと同様にINDEX関数とSEQUENCE関数で一覧表を取得します。
SEQUENCE(10,3)という形で指定すると、10行、3列で順番に序列を振る指示になるので、下のような感じにスピル機能で数値が自動生成されます。
INDEX関数と組み合わせて、INDEX(B2:B13,SEQUENCE(10,3))と指定すると、B2:B13の値が序列の順に割り当てられます。
SEQUENCE(10,3)の実行結果イメージ
IFERRORで存在しない値のエラーを""に変換
上のデータだと、5行目以降（13件目以上）は値がないので、エラーが表示されるので、これをIFERRORで空（""）に置き換えます。
合せるとこんな感じです。IFERRIR(INDEX(B2:B13,SEQUENCE(10,3)),"")

今回は、スピル機能とUNIQUE関数、SEQUENCE関数を用いた一覧の縦横変換のイメージを共有しました。

結構便利な関数群で、この辺りの関数や機能を使うコトで、日々の業務の生産性を上げたり、品質を上げられる可能性があるので、Office365をお使いの方は試される事をお勧めします！

といったところで、今回はここまでです。

ご閲覧ありがとうございました。
ではでは(^^)/