Excel

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

2021年3月3日

se.consultant

小林 祐也(ぷーさん)

未来志向と戦略思考が強みの30以上のプログラム言語を経験した外資コンサルタントSE

おはこんばんにちは、Excelがまぁまぁ使えるぷーさんです。

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

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

注意

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

 

こんな方におすすめ

  • Excel作業を効率化したい方

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

スピル機能とは

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

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

成形プロセスの解説

ヘッダの作成

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

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

    第2引数の指定は、このままだと芸が無いので、SEQUENCE関数で作り出すようにします。
    SEQUENCE(1,3)とすれば自動的に次のような結果が表示されます。

    C1セルにSEQUENCE(1,3)と入れたときの実行結果のイメージ

    これを1のUNIQUE関数と組み合わせて
    INDEX(UNIQUE(A2:A13),SEQUENCE(1,3))
    と指定するコトで、スピル機能が自動生成してくれるゴーストが右に自動で列を追加してくれます。

明細の作成

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

  1. INDEX関数とSEQUENCE関数で値を縦横変換取得
    ヘッダと同様にINDEX関数とSEQUENCE関数で一覧表を取得します。
    SEQUENCE(10,3)という形で指定すると、10行、3列で順番に序列を振る指示になるので、下のような感じにスピル機能で数値が自動生成されます。
    INDEX関数と組み合わせて、INDEX(B2:B13,SEQUENCE(10,3))と指定すると、B2:B13の値が序列の順に割り当てられます。

    SEQUENCE(10,3)の実行結果イメージ

  2. IFERRORで存在しない値のエラーを""に変換
    上のデータだと、5行目以降(13件目以上)は値がないので、エラーが表示されるので、これをIFERRORで空("")に置き換えます。
    合せるとこんな感じです。IFERRIR(INDEX(B2:B13,SEQUENCE(10,3)),"")

まとめ

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

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

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

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

  • この記事を書いた人
se.consultant

小林 祐也(ぷーさん)

未来志向と戦略思考が強みの30以上のプログラム言語を経験した外資コンサルタントSE

-Excel
-

© 2021 ぷーさんの閃考