【Excel】1レコード2行になっている表を修正する
総務省のルールに従って統計表を直していくシリーズの第３回目
セル結合によって１レコードが２行になっている表の結合を解除したとき、皆さんはどのようにレイアウトの修正を行っていますか？
働き方改革の折、一つ一つ選択してコピペを繰り返している時間はありません。
今回はコピペを使わずに、パワークエリで素早く作業する方法をご紹介します！
＜目次＞
- 概要
- 作業手順
- 結合を解除して奇数行と偶数行に表を分ける
- インデックスを付けて二つの表をマージ（結合）する
- 結果の表示
- 終わりに
「セルの結合はNG」と総務省が発信！
総務省が公表した『Excelの統一ルール』では、統計表におけるセル結合の使用を禁じています。
紙面レイアウトの都合上やむを得ない場合はあるものの、セル結合が使われた表は外部データとの接続やグラフ作成などが正しく実行できず、データベースとしての利用価値が低下してしまうためです。
チェック項目1-4では、例1としてセル結合によって1レコードが2行で構成された表が示されています。
データ集計や表の統合、グラフを作成する場合などは、レコードは1行で表記されていなければなりませんので、セルの結合を解除し、使えるデータベースに修正していきたいと思います。
なお、本記事ではチェック項目1-4の例1の表にデータを少し加えたものを使用しています。
作業手順
１．奇数行と偶数行で表を分ける
パワークエリにデータを取り込む
該当の表を選択して、右クリック。
“テーブルまたは範囲から…”というメニューを開き、表示されたデータ取得範囲を確認して、ＯKを押します。
インデックス列を追加する
先頭行を1とする行番号を振ります。
「列の追加タブ」から「インデックス列」の▼をクリックし、その中から“1から”を選択します。
 |
| 分かりやすいように「1から」を選択 |
 |
| インデックスが追加された |
奇数の判定列を追加する
次に、インデックスの判定列を追加します。
追加されたインデックス列を選択した状態で、「列の追加タブ」、「情報」の中から“偶数（もしくは奇数）”を選択します。
参照列の数値が偶数であればTRUE、偶数であればFALSEが表示されます。
 |
| 分かりやすいように「偶数」を選択 |
 |
| 判定列が追加された |
クエリを複製する
ここでクエリを複製します。
テーブルを選択して、右クリック、メニューから“複製”を選択します。
フィルターをかける
インデックスの判定列にフィルターをかけることによって、奇数行の表と偶数行の表に分けます。
判定列の▼を開いてTRUEのチェックを残して、OKを押します。
 |
| 偶数行の表が完成 |
もう一方の表も、判定列の▼を開いて、今度はFALSEのチェックを残して、OKを押します。
 |
| 奇数行の表が完成 |
不要な列の削除
表を分けるためのインデックス列や判定列はもう使いませんので、ここで削除してしまいます。
タイトル行を右クリック、“削除”を選択します。
列名の編集
分かりやすいように列名を付けなおしましょう。
列名をダブルクリックして編集します。
偶数行の表は“パート”。
奇数行の表は“正社員”に編集しておきます。
２．インデックスを付けて二つの表をマージ（結合）する
インデックスを追加する
奇数行の表と偶数行の表にレコード番号を振ります。
列の追加タブから、“インデックス列”を押します。
0から始まるインデックスが追加されます。
もう一方の偶数行の表にも、同じく0から始まるインデックスを追加します。
二つの表を結合する
奇数行の表を選択した状態で、ホームタブから“クエリのマージ”を開きます。
中央の▼にテーブルがリストアップされているので、この中から偶数行の表を選択し、照合列を選択します。
照合列は直前に追加したインデックス列を指定します。
データの展開
列名の右にあるアイコンを押して、展開したい列を選択します。
欲しいのはパートの人数だけなので、パートのチェックを残し、“元の列名をプレフィックスとして使用します”のチェックを外して、OKを押します。
不要な列の削除
表の結合のために使用したインデックス列は不要ですので、削除しておきます。
タイトルを右クリック、削除を選択します。
３．結果の表示
閉じて読み込む
「ホームタブ」、「閉じて読み込む」をクリックし、パワークエリを閉じます。
クエリを選択して右クリック、「読み込み先」を開いて、「テーブル」にチェックを入れ、読み込む場所を確認してＯＫを押します。
終わりに
セル結合の解除と解除後のレイアウト修正の方法について解説しましたが、いかがだったでしょうか？
一回の操作でイメージ通りの表に変換することは難しいですが、ロジカルに、そして柔軟な思考で一つずつ作業していけば、結果は得られると思います。
後半で解説している“表の結合”は、VLOOKUP関数を使ったデータ接続とイメージは同じです。
VLOOKUP関数はExcelの登竜門みたいな言われ方をしていましたが、今後はXLOOKUPやパワークエリでの作業が主流になりそうです。
みなさんも、是非この機会にパワークエリに挑戦してみてはいかがでしょうか？
それではまた！
動画もチェック！
是非ご視聴ください！
【総務省のルール紹介動画】
【総務省が定めたExcelのルール】
コメント