Googleスプレッドシート 困った表を集計する為の 加工数式2（手入力クロス表をアンピボット）
前回に引き続き「あるある」な困った表を集計するシリーズです。
前回はセル結合されていたり、一番上だけに項目名が入力された表からの関数を使った集計を検証しました。
シリーズ前回の記事
今回は 手入力で作成された クロス表からリスト表に変換する方法。Googleスプレッドシートで アンピボットする方法を検証してみましょう。
アンピボットってなに？
そもそもの前提として クロス表とかリスト表、縦持ち / 横持ち のデータ、アンピボットってなに？って話から入りましょうか。
リスト表 （縦持ち）のデータ
リスト形式のデータ、縦持ちの表といわれるのは、上のような形式の表です。
データの基本の型ともいえる表形式で、FILTER関数や フィルタ表示で の絞り込みも容易ですし、そのまま SUMIF、SUMIFSやQUERY関数で集計することも出来ますね。
クロス表 （横持ち）のデータ
一方、クロス表（横持ち）は上のような 行と列 でそれぞれの項目があり、それらが クロスするセル に各データが配置された表です。マトリックス表と呼ばれることもあります。
ピボットテーブルやQuery関数による集計表をイメージすれば、わかりやすいかと思います。
パッと見で理解しやすい、手入力しやすいといった特徴があるので、バイトのシフト表や 学校の授業の時間割（縦が 1限目、2限目で 横が 月・火・水 と曜日になってる）なんかで使われています。
ただし、このクロス表は データの追加の際に面倒だったり、関数やプログラミングで データを取り出したり、集計しにくいという欠点があります。
ピボット、アンピボット とは
縦持ち（リスト表）から 横持ち（クロス表）に変換する 処理を ピボット処理と言ったりします。SQLのPIVOT句や、Excelのピボットテーブルで行う処理、Query関数での pivot がこれに該当します。
逆に 横持ち（クロス表）を縦持ち（リスト表）に変換する処理を アンピボット と呼びます。（SQLにはUNPIVOT句がある）
とはいえ、ピボットテーブルとは言えないような 内容が 集計値ではなく文字列だったり、改行区切りなどで 一つのセルに複数データが入っている表も 広い意味合いでは クロス表という扱いになります。
上のような表をリスト化するとなると、なかなか厄介だったりします。今回はこんな表を含めて、アンピボットに挑戦してみましょう。
Excelなら PowerQuery（パワークエリ）
やはり パワークエリ、パワークエリは全てを解決する
この横持ち表から縦持ち表への変換、アンピボットという処理は、Excelでやる場合は パワークエリ（略して パワク）の出番です。
データの成型・加工において超強力な味方である パワークエリは、モダンExcelを代表する機能の一つで、かゆいところに手が届く処理が満載の便利ツールです。
Excel信者が GoogleスプレッドシートをDisる際に持ち出すのが、テーブル機能とこの パワークエリですねｗ
残念ながら Googleスプレッドシートには パワークエリに該当する機能はありません。
とはいえ、Excel2016以降を使ってる人でも 未だにテーブルが良くわかってなかったり、パワークエリを使ったことがない、さらには「はて、パワークエリ？」と初めて聞いたようなリアクションをする人が多いのも事実。
パワークエリは Youtube動画や 解説サイトはいくらでもありますが、少しだけ触れておきましょう。
パワークエリ事例1 グループのサマリ集計表を作る
前回、複雑な Query関数で 集計した この表も Excelの場合は パワークエリを使うことで、マウスでポチポチと操作するだけで 同様の集計表が生成できます。
パワークエリ利用の為に、まずは困った表を選択して 右クリックで「テーブルまたは範囲から」で テーブル化＆パワークエリエディタを起動します。
パワークエリは Excelのシート内で処理されるのではなく、一度 異世界（エディタ）へデータを渡して、修行（加工）して、現実世界に戻ってくるという手順になります。
その間 エクセル側は時が止まっており操作できません。
データにとっての 精神と時の部屋と言えるかもしれませんねｗ
パワークエリの起動はメニューから Excelファイルを選択していく方法もありますが、右クリックからが一番簡単です。
ただしパワークエリに合わせて テーブル化されるので、 結合セルは解除されます。
この段階では 各営業部名は 先頭行にのみ記載されている状態です。これをパワークエリエディタ上で 列を選択して
右クリック ＞ フィル ＞ 下へ
とすることで、空白セルのデータが埋まります。一撃ですね。
再度、営業部の列を選択して、今度はグループ化を選択。
詳細設定として、こんな感じで 集計列を設定することで
集計表が設定できました。
さらに 達成値の列を作りたいので リボンを 「列の追加」にして、カスタム列を選択。カスタム列を作成する 画面で。
= [契約合計]/[目標合計]
として列を追加。
項目名をクリックして「達成率」に変更。列を右クリックで 型をパーセンテージにして 集計表を パワークエリ上で 完成させます。
最後に 「閉じて次に読み込む」で 出力先のセルを選択すれば
このように サマリー表が生成できました。簡単ですね。
パワークエリ事例2 癖が凄い クロス表をアンピボット
上のような1つのセルに改行区切りで複数データが入った、困ったクロス表も パワークエリで処理できます。
前回同様、パワークエリエディタでデータを読み込んだら、一番左のセルを選択して、「その他の列のピボット解除」
それだけで、このようにリスト表に展開されます。
改行データがある列を選択して
右クリック ＞ 列の分割 ＞ 区切り記号による分割
を選択。
自動で #(lf) ← 改行 を区切りと判別してくれるので、分割の方向だけ 行を選択して OK。
リスト表完成。アンピボット出来ました～。
あとは先ほどと同じように こちらを Excelに出力すればOK。
ちょっと知ってればサクサク操作できますね。
パワークエリは M言語と言われる 上の赤字部分の記述をマスターしようとするとハードルがあがりますし、まだまだ奥が深い機能なんですが、普通に利用する分には割と簡単。
とりあえず使ってみたってレベルでも、手間の削減効果が大きいのが人気の理由です。
この他にも 複数シートや 一つのフォルダ内の複数ブックをまとめて 結合、集計が出来るのが魅力です。
もちろん 複数シートの 結合は VSTACK関数でも可能ですが、スピル対応以前のExcelである 2016でもパワークエリは使えるのが良いですね。
パワークエリ事例3 追加データは 更新ボタンで らくらく反映
パワークエリには、後からデータの更新・追加があった際の反映が簡単というメリットもあります。
上のgif動画のように 黒板係は もともとは 広末くん1名だったのが、一人だと寂しいというので、もう1名黒板係に 鳥羽くんを 追加しました。
元となる クロス表 の追記データは、 出力された データテーブルを 選択して、リボンから 更新ボタンを押すだけで 反映されます。（裏で記憶しているクエリ処理が走る）
うーん便利。Googleスプレッドシート使いとしては 悔しい限りですが、Excelユーザーが ドヤるのもわかりますｗ
Googleスプレッドシートにも 部分的には近い処理が出来るアドオンがある
先ほども書きましたが、Googleスプレッドシートには Excelのパワークエリに該当する機能はありません。
じゃあどうするか？
単純な アンピボットや パワークエリの一部の処理は Power Tools というアドオンで一応出来ます。
有料アドオンとなりますが、30day無料試用期間があるんで気軽に試せます。（特に登録等は不要）
さすがに有料アドオンなんでよく出来てるとは思いますが、さすがに パワークエリほどの万能感はありません。
処理の流れを組み合わせて記憶するようなことはできないですし、先ほどパワークエリで処理したような、改行区切りで複数のデータ（名前）が入った特殊なクロス表のアンピボットなどは恐らく無理です。
フィル（セル埋め）処理などは 直接元データを加工してしまうので、状況によっては使えないことも。
さらに、英語のみで情報が少ないので利用のハードルが高かったり、Google公式ではないアドオンであるため、会社によってはセキュリティ上アドオン禁止ってこともあるでしょう。
もちろん コードを書けば GASを使っての処理もできますが、それは Excelだってパワークエリ登場前は VBAでやってたよ！と一緒のはなしですよね。
そうすると、やはり関数を組み合わせて 式で解決することになります。
また、Googleスプレッドシートは なんでも 式でやらせがちって言われそうですがｗ
Googleスプレッドシートでの アンピボット例 1
では、実際にどんな式を組めば アンピボットできるのか？
お題形式で Googleスプレッドシートで アンピボットする式を考えてみましょう。まずは比較的簡単なお題から。
Q1 . 穴あきありのクロス表を アンピボットする式は作れるか？
画像左のような 縦に時間、上に日付の入った 予約を表す クロス表を 右側の リスト表に変換できるか？ というお題です。
普通だと面白くないので、全部が埋まっていない 虫食い状態の表にています。
サンプルデータは ↓ を右上に表示されるコピーボタンで利用ください。
7/26(火) 7/27(水) 7/28(木) 7/29(金)
10:00-10:20
10:20-10:40 増田 絵里 鈴木 一郎
10:40-11:00 池野 優子 田中 花子
11:00-11:20 野村 玲子
11:20-11:40 西崎 雅美 森 知世
11:40-12:00 大森 陽子
12:00-12:20 加藤 未央 佐藤 沙織
12:20-12:40 吉田 愛子 斎藤 剛
12:40-13:00
13:00-13:20 赤岩 賢 境田 千賀子
13:20-13:40 森 賢一 前提条件として、
・式を入れるセルは1つであること
・予約（人の名前）の入っていない日時は出力しない
・日付、時間帯で昇順に並べる
としましょう。
いくつかアプローチ方法はありますが、出来そうでしょうか？
まずは自分で考えてみましょう。
↓↓↓
回答は以下
↓↓↓
A1. 穴あきありのクロス表を アンピボットする式
一番簡単な式はこれかなと。
=LET(days,B2:F2,times,A3:A14,data,B3:F14,
ARRAYFORMULA(SPLIT(TOCOL(IF(data="",,days&","×&","&data),3,true),",")))
解説していきましょう。
まず、事前準備の処理。これは 無くてもいいんですが、わかりやすいように セル範囲の部分を LETで変数化しています。
LET(days,B2:F2,times,A3:A14,data,B3:F14,
LET関数については、以下を参照ください。
そして一番目の処理が
IF(data="",,days&","×&","&data)
この部分。名前のセルが空白なら 空白を、値が入っていれば 日付、時間、セルの値（名前）を ","（カンマ）区切りで連結しています。
次に TOCOL関数 でこれを 縦一列にします。
TOCOL(IF(data="",,days&","×&","&data),3,true)
この際に、第2引数を3としてエラー（ないけど）と空白を削除して詰めるのと、第3引数の 列でスキャンを true として列方向に読み込ませています。
列方向（上から下へ）スキャンとしているのは、通常の行方向（左から右）だと縦に並べたときに日付順にならない為です。
最後に SPLIT で カンマで分割して、日付列の表示形式を M/d(ddd) とすれば完成。
SPLIT(TOCOL(IF(data="",,days&","×&","&data),3,true),",")
この複数セルの 一括分割 は ExcelのTEXTSPLIT では出来ない処理です！
Arrayformulaは上の3つを配列処理とする為に必要なので、一番外側につけます。
どうでしょうか？いったん文字列として結合させる処理を知らないと苦戦するかもしれませんが、割とシンプルな式で難しくはないですね。
TOCOLの登場前は、QUERYやTRANSPOSEの組み合わせだった
この処理が シンプルな式でかけるのは、実は 新関数の TOCOLの活躍が大きいです。
縦1列にする際に、TOCOLなら 空白除去、列方向スキャン を引数で制御できますが、TOCOLの登場前は
今 TOCOL(範囲,3,true)
↓
昔 QUERY(FLATTEN(TRANSPOSE(範囲)),"where Col1 is not null")
縦1列に ・・・ FLATTEN関数
空白除去 ・・・ Query関数
列方向スキャン ・・・ TRANSPOSE関数
と3つの関数を組み合わせてました。結構最近まで下の式書いてたんですけど、なんか遠い昔って感じがしますｗ
ちなみに、お題の表が違うので 少し手法は違いますが「いきなり答える備忘録」さんも Googleスプレッドシートでのアンピボットについて書かれています。
ちなみに REDUCEと VSTACKを使って文字列連結しないでアンピボットする方法もあります。でもちょっと長くなるんで割愛。
Googleスプレッドシートでの アンピボット例 2
それでは、もう少し面倒な 先ほど Excelのパワークエリでは アンピボットできた 1セルに複数データが入った ケースにも挑戦してみましょう。
Q2. 癖の強い 1セルに改行区切りで複数データが入ったクロス表を アンピボットする式は作れるか？
こちらのクセの強い クロス表のアンピボット、つまり 右の表を左のように変換する式は作れるでしょうか？
前提条件は以下の通り。
・式を入れるセルは1つであること
・人の名前の入っていないデータは出力しない
・日付順で並べたい（もともとのクロス表は日付順になっている）
・クロス表は下に伸びていくのでお尻を決めない範囲指定としたい
・1つのセルには改行区切りで複数データが入ることがある。
（それらを1つ1つのデータとして出力したい）
お題データは↓をコピペで
当番表 掃除 黒板 日直
6/21 "田中
山田" "広末
鳥羽" "松本
松山"
6/22 "東山
原" "田所
今井" "金城
戸田"
6/23 "相原
宇野" 井上 青戸
6/24
6/25
6/26 "藤田
水野" "河合
四方" "沢田
梶谷"
6/27 "染谷
日野" 灰谷 馬場これは結構難しいです。幾つか方法はありますが、実は 「改行入りの文字列操作のある処理方法」を知っていれば、ぐっと短い記述にできます。
自信のある人は、まずは自分で考えてみましょう。
↓↓↓
回答は以下
↓↓↓
A2. 癖の強い1セルに改行区切りで複数データが入ったクロス表を アンピボットする式
恐らく一番シンプルであろう回答の式はこちらになります。
=LET(c,A2:A,r,B1:D1,data,B2:D,
ARRAYFORMULA(SPLIT(TOCOL(SPLIT(TOCOL(IF(data="",,
REGEXREPLACE(data,"(?m)^(.+)$",c&"_"&r&"_$1"))
,3),CHAR(10)),3),"_")))
ちょっと長いので インデント付きにすると ↓ のような感じ。
=LET(c,A2:A,r,B1:D1,data,B2:D,
ARRAYFORMULA(
SPLIT(
TOCOL(
SPLIT(
TOCOL(
IF(
data="",,
REGEXREPLACE(data,"(?m)^(.+)$",c&"_"&r&"_$1")
)
,3
)
,CHAR(10)
)
,3)
,"_"
)
)
)冒頭の
=LET(c,A2:A,r,B1:D1,data,B2:D,
は先ほどと同じなのでいいですね。
IFで data 部分の空白判定をして TOCOLで空白除去をしているので、範囲は B2:D とお尻を決めない書き方で問題ないです。
SPLIT(TOCOL(SPLIT(TOCOL(
この SPLITとTOCOLが連続2回登場する部分が気になりますが、それ以上にポイントとなるのが
REGEXREPLACE(data,"(?m)^(.+)$",c&"_"&r&"_$1")
この部分ですね。
まずはこの正規表現を応用した 置換処理を解説していきましょう。
REGEXREPLACE関数 で マルチラインモード と キャプチャグループを活用する
1つのセル内に 改行区切りで複数データが入っているケースがあり、そのデータ（名前）ごとに 日付と当番を連結させる 文字列加工を行いたい。
この処理に正規表現を使える置換関数の REGEXREPLACEを使っています。
正規表現において
^ は文全体の先頭
$ は文全体の末尾
を意味しますが、通常はスプレッドシートの場合は 1つのセル内の文字列を改行ある・なし関係なしに 一つの塊と見なします。
だから、今回の場合は
このような扱いになります。
しかし、ここで
(?m)
という記号を先頭につけることで、正規表現がマルチラインモード というモードに切り替わります。
マルチラインモードとは、改行ごとに 1つの文として扱うもので、先ほどの文頭、文末を表す記号が
^ は行の先頭
$ は行の末尾
このような意味合いに変化します。
このセル内 の1行、1行を 表したものが
^(.+)$
です。カッコをつけることでキャプチャグループとして、REGEXREPLACEの第3引数で $1（1つ目のキャプチャグループ） という形で再利用ができます。
キャプチャグループは カッコ内の 正規表現にヒットした箇所をキャプチャ（保存）しておく機能です。
mirの noteでも何度か登場してますね。
1つ目のキャプチャグループという言い方だと、田中が1つ目で、山田は2つ目で $2になるのでは？と思うかもしれませんが、そうではありません。
そのうち正規表現について、もう少し掘り下げるnoteは書きたいと思いますが、1つ目のキャプチャグループ に合致したものは 全て $1 としてそれぞれ処理（加工）することができます。
だから
c&"_"&r&"_$1"
このように置換後（第3引数）を記述することで、変数である c （日付）、r（当番）と$1（セル内の行毎のデータ、つまり名前）を _ で連結する処理が、各行（名前）ごとに適用されるわけです。
ちなみに、このスプレッドシート REGEX系関数における マルチラインモードについては、いきなり答える備忘録さんも取り上げています。
ただ、マルチラインモードと対をなす シングルラインモード（ドットオール）については、あまりスプレッドシートでの使い方について取り上げられているサイトを見かけません。
一応、XMATCH関数の noteで mirがさらっと触れていますが、正規表現の活用例と合わせて、どこかでまとめたいですね。
SPLITとTOCOL を繰り返して 製麺していく
一番面倒な 改行部分の加工処理が REGEXREPLACE 一発で完了したので、あとはTOCOLとSPLITの繰り返しで成形していくだけです。
まずは一旦 1列にして（伸ばして）、改行で区切って（広げて）、また1列にして（伸ばして）、最後に"_"で区切る（広げる）。
なんとなく、小麦粉を練って、まとめて、麺棒で広げてを繰り返す 製麺作業っぽい感じｗ
先ほども書きましたが、この配列を一気に分割できる処理が Excelの TEXTSPLITでは対応できないのと、Excelでは正規表現を扱えるシート関数がない為、同じことをやろうとすると一気にハードルがあがります。
だからこそ、アンピボットなら パワークエリでしょ！ってのもあるのかなと。
関数ならではのリアルタイム反映
関数を使ったことで、元のクロス表を書き換えた瞬間にリスト表の方がリアルタイムで更新されているのがわかりますね。苦労が報われた感ｗ
もちろん、ここから再度 QUERY関数を適用させる、もしくはピボットテーブルを適用することで、たとえば 誰がどの係を何回やったか、〇月 の一月単位で 人毎に係をやった回数を集計といったことが可能です。
このように元になるクロス表が集計表でない場合でも、一旦データの基本の型である 縦持ちのリスト表に変換することで QUERY関数や SUMIF、COUNTIF、FILTERなどの関数が使いやすくなります。
でも、今回は改行区切りだから REGEXREPLACEのマルチラインモードが使えたけど、カンマや 半角スペースなどの他の区切りだったらどうするの？
こんな風に思う人がいるかもしれませんが、先に区切り文字を 改行 に置換して処理しちゃえば、今回の式に落とし込めます。
実は 今回の式は、一つのセルに複数データが入ったクロス表なら 結構応用できます！覚えておいて損は無し！
気軽さなら パワークエリだけど
今回は困った表の集計第2段ってことで、Googleスプレッドシートにおける 数式による アンピボット処理について紹介しました。
合わせて 前半では Excelのパワークエリの魅力と パワークエリでのアンピボット処理も紹介しましたが、やはり圧倒的に簡単なのは パワークエリですよね？
これは mirも認めています。凄いです。誰でも使えます。
でも、面白いか？といわれると、パワークエリは 機能を覚えて 手順通りに作業するだけなんで、あまり面白くないです。クラフト感がないんです。（完全に使いこなしているわけではないので、もっと使い込んで深い部分を知れば面白くなってくるのかもせんが）
Googleスプレッドシートの 数式による 処理も、関数の知識が前提とはなりますが、それでも 様々なアプローチがあり クラフト感があります。
今回紹介した式は 一つの回答例であって、別に他のやり方で進めてもいいんです。もしかしたら、もっと良い式があるかもしれません。
もちろん、お仕事でスプレッドシートを 使ってる人たちは、なるべく早く簡単に処理を終わらせたいでしょうから、面白さは求めないかもしれませんが。。
それでも、mir的には ゼルダの ティアキンの ウルトラハンドやスクラビルド的な楽しさが Googleスプレッドシートの 式作成にはあると思ってますし、その魅力を伝えたいなーと思っているわけです。（Excelの関数やVBA、GASなんかも同じような面白さがあります）
毎週（そこそこ時間かかって）書いているこの noteで 、そんなGoogleスプレッドシートで式をつくる面白さが、少しでも伝われば幸いです。
次回のテーマは、そろそろ プルダウン いってみようかなと。