月が変わるたびに右へ列を追加し、商品や担当者が増えるたびに行を増やす表は、入力画面としては分かりやすくても、集計やシステム連携には扱いにくい形です。
「誰が・いつ・何を・いくつ」という単位で1行にすると、月が増えても列構成を変えずに追記できます。元の見た目を残したい場合は、縦長データから集計表を作り直します。
定期的に列が増える表は、Power Queryのピボット解除などで縦長へ変換できます。見出しが複数段ある、結合セルで分類している、空欄に意味がある場合は、変換規則を先に明文化します。
縦長へ直すと効果が出やすい表
次のような表は、毎月の列追加をやめられる可能性があります。
- 月・日・支店などが横方向に増え続ける
- 集計のたびに範囲を広げる必要がある
- ピボットテーブルやグラフの元範囲がずれる
- 同じ項目名が複数の見出し行に分かれている
- 空欄と0の意味が違う
1行が何を表すかを決める
縦長データでは、1行を「1商品×1月」「1社員×1日」のように定義します。月、商品、部署、数量、金額などを別列に分け、見出しの色や位置に持たせていた意味もデータへ移します。
変換後に元のクロス表が必要なら、ピボットテーブルや数式で表示用の表を作ります。入力・保存用のデータと、見るための帳票を分けるのがポイントです。
- 11行の単位を決める
取引、商品×月、社員×日など、重複しない最小単位を決めます。
- 2見出しの意味を列へ移す
月、地域、商品分類などをそれぞれの列として保持します。
- 3元表を再現して照合する
縦長データから元の合計を再現し、行列の取り違えがないか確認します。
どの方法で形を変えるか
定期更新ならPower Queryが向きます。元表が手入力で、今後も入力を続ける場合は、入力様式自体を一つの明細表へ変更する方が長期的には簡単です。
| 方法 | 向いている場面 | 確認したい点 |
|---|---|---|
| Power Query | 同じクロス表を毎月縦長へ変換 | 複数段見出しと空欄の意味を決める |
| 関数・ピボット | 小規模な表を表示用に組み替える | 元範囲が増えても追随する形にする |
| 入力様式の変更 | 今後は1行1件で直接入力 | 入力者が迷わない入力規則を用意する |
入力データの検査で確認しておきたいこと
まずコピーを作り、1か月分だけ縦長へ手で並べ替えると、必要な列が見えてきます。
- 結合セルを解除する前に見出しを控える
- 空欄・0・該当なしを区別する
- 月を文字列ではなく日付として持つ
- 元表の総合計と変換後の合計を比較する
入力データの検査を相談するときに伝えること
元表だけでなく、変換後に作りたい集計やグラフも示すと、必要なデータ粒度を決めやすくなります。
- 代表的なクロス表を2〜3期間分
- 1行の単位として想定する内容
- 空欄と0の扱い
- 最終的に見たい集計表
- 過去データをどこまで変換するか
入力データの検査をプロに任せる目安
- 見出しが3段以上あり結合セルが多い
- 数年分の過去データを一括変換したい
- 変換後に複数の集計表を自動更新したい
- 入力様式の変更と既存帳票の維持を両立したい
入力データの検査で確認しておきたいこと
行数が増えることを「データが重くなる」と誤解し、クロス表を残すケースがあります。
- 1行の単位を決めずに縦へ並べる
- 見出しの色や位置にあった情報を失う
- 空欄をすべて0へ置き換える
- 変換後の合計だけ見て重複行を確認しない
CSVの取込・整形で確認しておきたいこと
縦長にすると行数が増えますが大丈夫ですか？
行数は増えますが、列構成が固定されるため更新や集計はしやすくなります。データ量が非常に多い場合は別の保存先も検討します。
元の見た目は残せますか？
縦長データを原本にし、ピボットテーブルなどで元に近い表示を作れます。
結合セルが多い表も変換できますか？
可能な場合はありますが、結合セルが持っている分類情報を各行へ補う処理が必要です。