データをアンピボット

アンピボットについて

レポートが横長で届くのは、人がそのように読むからです。1行に1商品、1列に1か月、下端に合計。印刷されたページには正しい形であり、それ以外のほとんどには間違った形です。

そのデータをグラフにしたい、集計したい、絞り込みたい、取り込みたいというツールはどれも、代わりに縦長を求めます。商品と月の組み合わせごとに1行があり、月は見出しではなく列の中の値として入っている形です。

• 横長 — 商品 | 1月 | 2月 | 3月
• 縦長 — 商品 | 月 | 売上、行数は3倍

Excelでもパワークエリを使えばできますが、いくつものダイアログと読み込み手順、そして更新が必要です。ここではチェックボックス2つと名前だけです。

使い方

• 行を貼り付けるか、表計算ファイルをアップロードします
• そのまま残す列にチェックを入れます。行を識別する列で、たいていは最初の1〜2列です
• チェックしなかった列はすべて行になります。ついでに新しい2つの列に名前を付けてください
• CSVまたはExcelでダウンロードするか、コピーして戻します

最初の列はあらかじめチェックされています。横長のレポートではほぼ例外なく、それが識別子だからです。

空のセルは既定で取り除かれます

横長の表はたいてい隙間だらけです。すべての商品が毎月売れるわけではないので、かなりの割合のセルが空になります。それをそのまま持ち込むと、中身のほとんどが空という縦長の表ができあがってしまいます。

そこで、指定しない限り空欄は飛ばされます。空のセルも行として残すは、空欄が「欠けている」のではなく「意味を持つ」場合のためにあります。「無回答」が本当の回答であるアンケートや、空き枠が見えていなければならない予定表などです。

見出し行が2段あるとき

印刷用のレポートには、本当の見出しの上にタイトル行があることがよくあります。月別売上と書かれた結合セルが 1月・2月・3月 の上に載っている、という形です。これをデータとして読むと、その最上段が見出しになり、本当の見出しが最初の値の行になってしまい、月別売上 という名の列と名前のない列が並ぶ、意味をなさない結果になります。

アンピボットはどちらの行がどちらなのかを推測できません。どちらも文字列で、どちらも数字の上にあるからです。解決策は、貼り付ける前に飾りの行を削除しておくことです。表計算ソフトでクリック1回、それだけで以降はすべて正しくなります。

下端の合計行についても同じことが言えます。それは観測値ではないので縦長の表には属しません。選択から外すか、結果から削除してください。黙って持ち込むと、誰かが値の列を合計した瞬間に、すべての数字が2倍になります。

こんな場面で使われています

• 1観測につき1行を必要とするピボットテーブルで使える形に、横長のレポートを直す
• 月次・四半期の列を、グラフが時系列で描ける形にする
• 1行に1つの値を求めるデータベースへの取り込みに向けて表を整える
• 予算の表を明細の一覧に変える
• 設問ごとに列ができてしまったアンケート結果を組み替える
• R・pandasなど、それを前提とする分析ツール向けに整然データを作る

知っておくと便利なこと

• Windows・macOS・Linux・ChromeOS、スマートフォンやタブレットでも動きます。 受け取るのは貼り付けたテキストかファイルで、フォルダーではありません
• アップロードは行いません。読み取りも組み替えもページ内で完結します
• 1行目は常に見出しです。列見出しが新しい項目列の値になるためで、見出しなしに表をアンピボットする方法はありません
• すべての列にチェックを入れると展開するものが残らないため、最初の1列だけを残す動作に切り替わります
• 行の順序は元の表に従います。まず1行目のすべての値、次に2行目のすべての値、という順です
• プレビューには最初の200行が表示されますが、ダウンロードには常にすべての行が含まれます

関連ツール

• グループごとに集計 — 逆方向 — 行をまとめて要約にする
• 行と列を入れ替え — 代わりに表全体を倒す
• 列の分割と結合 — 列そのものを組み替える
• ExcelをJSONに変換 — 縦長の表を別の場所へ持っていく