【Excel】複数の表を一つに結合する！
総務省のルールに従って統計表を直していくシリーズの第７回目
表の結合は、事務仕事において発生頻度の高い作業の一つだと思いますが、結合するファイルの数か多いとコピペの繰り返し作業が苦痛になってきます。
今回の解説では、パワークエリを使った結合方法をご紹介しますので、ぜひ参考にしてください。
画像をクリックすれば、大きい画像が表示されます！
＜目次＞
- チェック項目2-1概要
- 表の結合と整理
- データの取り込み
- 表の結合
- 不要な列の削除
- 不要な行の削除
- 西暦の生成
- 和暦の複製
- 必要な文字の抽出
- 和暦を西暦に変換（解析）
- 閉じて読み込む
- 表示形式とレイアウト修正
- フォルダに格納したファイルの結合
- データの取り込みと結合
- 不要な列の削除
- まとめ
１．データを分断しちゃう表はNG！
チェック項目2-1
総務省が公表した『Excelの統一ルール』では、表の構成の注意点として、表の中、表の周囲に見た目を意識して空白行（列）を追加してはいけない。1シートに複数の表頭（列の見出し部分）や表側（行の見出し部分）を設定してはいけないと定めています。
Excelは空白行や空白列などでデータが分断されていると、例え罫線で繋がっていても別の表として認識しますので、データ接続の際に選択ミスやエラーの要因となります。
また、エラー回避のため、空白行（列）を処理する工程が必要になるなど、表を使った作業効率が下がってしまいます。
空白行や空白列は、見た目を意識して追加されることが多いと思いますが、ソフトウェアにとっても、表を使う他の人にとっても、あまり喜ばしいものではありません。
本記事ではチェック項目2-1、例3の「紙面に収めるために折り返された表」の修正作業として、“表の結合方法”と“和暦から西暦を生成する方法”について解説します。
なお、より現実的なシチュエーションを想定し、分割された表はワークシートではなく見出しで分けて1ファイルに収録したものをサンプルとして使用しています。
２．表の結合と整理
データの取り込み
データタブ、「データの取得と変換」グループ、「データの取得」、「ファイルから」、“Excelブックから”を選択します。
ファイルを指定して、“インポート”を押します。
「ナビゲーター」というウィンドウが開きますので、“複数のアイテムの選択”にチェックを入れ、取り込みたいワークシートにチェックを入れて、“データの変換”を押します。
表の結合
独立した形でシートが取り込めましたので、これらのシートを結合します。
ナビゲーションウィンドウの空いているエリアを右クリックして、新しいクエリ、結合、“クエリを新規クエリとして追加”を選択します。
追加というウィンドウが開きますので、“３つ以上のテーブル”にチェックを入れて、利用可能なテーブルの中から、必要なアイテムを選択して“追加”。
必要に応じて並べ替えをして、“ＯK”を押します。
列や行の削除などのレイアウト修正が不要であれば作業はこれで終了です。
「閉じて読み込む」まで、スクロールしてください。
不要な列の削除
セル結合が解除されたことによってできてしまった2列目と3列目の空白列は不要ですので、ヘッダーを選択して「列の管理」グループにある“列の削除”で削除します。ヘッダーを右クリックしても削除できます。
不要な行の削除
次に、各表のヘッダーに該当する行を削除します。各表のヘッダーは同じ値が使われていますので、“重複の削除”を使って重複行を削除できます。
しかしながら、1シートに複数のヘッダーを設定するメリットはありませんので、1行目だけ残して他を削除します。
ホームタブ、変換グループ、“1行目をヘッダーとして使用”をクリックして、ヘッダーを昇格させます。
消したい行の1列目にはすべて「null」が入力されていますので、ヘッダーの▼をクリックして、“空の削除”もしくは、nullのチェックを外してフィルタリングします。
ヘッダーも編集しておきましょう。
表の結合＆整理の工程は以上です。
３．西暦の生成
和暦の複製
統一ルール（チェック項目1-11）では、和暦の場合は西暦も併記するよう定められておりますので、和暦の列を変換してしまわずに西暦を追加して対応します。
列の追加タブから、“カスタム列”を開きます。
使用できる列の中から、和暦が入っている列を選択し、挿入をクリック、OKを押します。
ヘッダーも編集しておきましょう。
必要な文字の抽出
年号だけであれば西暦に変換できるのですが、「分」の文字が邪魔しているので変換できませんので、“区切り記号の前のテキスト”というコマンドを使って必要な文字列を抽出します。
変換タブ、テキストの列グループ、抽出、“区切り記号の前のテキスト”と選択し、区切り記号を「分」として、OKを押します。
値の置換で「分」の文字を削除しても結構です。
和暦を西暦に変換（解析）
「日付と時刻の列」グループ、「日付」をクリックし、“解析”を選択します。
閉じて読み込む
ホームタブに戻って、閉じて読み込む。
クエリを右クリックして、読み込み先…
テーブル、OKとクリックします。
４．表示形式の修正
西暦を年数のみの表示に変更
西暦の列に月日の表示は不要なので、年数だけの表示に変更します。
Ａ列を選択して、ホームタブ、数値グループから“セルの書式設定”を開きます。
ユーザー定義のテキストボックスに「yyyy”年”」と入力し、OKを押します。
選択範囲内で中央に表示
パワークエリで削除した表タイトルを再入力し、表の幅全体に表示します。
範囲を選択し、「セルを結合して中央揃え」ではなく、セルの書式設定を開いて、配置タブ、文字の配置、横位置の中から、“選択範囲内で中央”を選択して、OKを押します。
セルの結合を使わなくても、中央揃えで表示することができます。
※縦の範囲は選択できません。
以上でチェック項目2-1例３の表の修正作業は完了です。
５．フォルダに格納したファイルの結合（参考）
上記の例では、表を見出しで分けたファイルを使用しましたが、参考として、それぞれの表を別のファイルに保存したフォルダからデータを取得してみます。
データの取り込み
データタブ、「データの取得と変換」グループの、「データの取得」、「ファイルから」、“フォルダから”を選択し、ファイルが格納されたフォルダを指定して、“開く”をクリック。
フォルダに格納されているファイルを確認するウィンドウが開くので、「結合」、“データの結合と変換”を選択します。
結合する表を選択するウィンドウが開きますので、基準となる表を選択して、“OK”を押します。
不要な列の削除
自動的にファイル名を示す列が追加されてしまうので削除してしまいます。
ヘッダーを選択して「列の管理」グループにある“列の削除”。もしくは、ヘッダーを右クリックして削除を選択します。
閉じて読み込む
レイアウト修正などの必要が無ければ、ホームタブに戻って、“閉じて読み込む”をクリック。
クエリを右クリックして、読み込み先…
テーブル、OKとクリックして、ワークシートに表示します。
６．まとめ
今回は「表の結合」と「和暦から西暦を生成」する方法について解説いたしました。
日常業務では、支店からの○○報告の集計、調査票の結合、今月の○○データの結合…のように、数多くの表やデータを結合する機会があると思いますので、是非参考にしてみてください。
それでは、また！
動画もチェック！
本記事での解説内容は、動画でもご視聴いただけます。
是非参考にしてください！
【総務省のルール紹介動画】
【総務省が定めたExcelのルール】
コメント