Script Labのカスタム関数でピボット解除

横持ちデータ死すべし

Excelで仕事をしているとよく出くわすのが横持ちデータ（縦横マトリクス）。集計結果としては別によいのですが、このデータをもとに何か作業をするためには非常に使いにくいことこの上ありません。

もちろん、Power Queryでピボット解除を行うことによって縦持ちデータ（整然形式）に変更することは可能です。ただ、わざわざそのためにテーブル化してエディタを開いて処理するのもまあまあ面倒で関数でさくっと処理したくなったりもします。LAMBDAで関数を定義することもできますが、処理するためにわざわざLAMBDAを定義するのも大変面倒です。

そこでScriptLabによるUDFの出番です。この手法ならいったアドインでコード実行してしまえば任意のタイミングでさくっとピボット解除を行えます。

コードは以下のとおり。

/**
 * @customfunction
 * @param {any[][]} array
 * @returns {any[][]} A dynamic array with multiple results.
 */
function UNPIVOT(array) {
  // 空の新しい配列を作成
  const transformedArray = [];

  // 与えられた配列の最初の要素（表のヘッダー）を取得
  const headers = array[0];

  // ヘッダーを除いた行の要素を処理
  for (let i = 1; i < array.length; i++) {
    const row = array[i];

    // ヘッダーに含まれる列の数だけ処理
    for (let j = 1; j < headers.length; j++) {
      // 新しい行の要素を作成し、変換された配列に追加
      const newRow = [row[0], headers[j], row[j]];
      transformedArray.push(newRow);
    }
  }
  return transformedArray;
}

本当はヘッダーやらなんやらをきれいに引数で指定するのが正しいのでしょうが、私の業務上ではきれいなマトリクスをアンピボットするだけの作業が必要なので、思い切って引数設定は一つにしてしまいました。

他人に渡す際には値コピペで数式をつぶすことを忘れないことだけが注意点ですかね。