Excelにはパワークエリという機能が搭載されていますが、今回はこれを使って複数のシートをひとつのデータとしてまとめてみましょう。

パワークエリって何に使えばいいのだろう？

最初に正直に告白しておきます。Excelのパワークエリ、今までまったく使っていませんでした。

「使っていなかった」と書くと意図的に使ってなかったみたいに聞こえますが、実際は

「何に使えばいいかまったく思いつかなかった」

というのが正しい表現です。

最近になってようやく使い方のイメージができてきましたので、今回は一例として次のようなケースを取り上げます。

「給与ソフトのデータを別のソフトにインポートしたい、もしくは分析のために全従業員の月別支給額のデータが欲しい。」ということで、給与ソフトから給与データをエクスポートします。

ところが出力されたデータは次のように従業員別のシートに分かれてしまっていました。

こうしたデータをみると

「イヤ違う、一枚のシートにデータベース形式でまとめてほしいんだ！」

と思うわけですが、なかなか思うようにはいきません。

もちろん手作業でコピペすることも可能ですが、もし従業員が10人のデータでも同じことやりますか？

頑張ってコピペして「今日も仕事頑張った！」っていうのはなんか違いますよね。ということでこうしたデータを1枚のシートにまとめるためにパワークエリを使ってみましょう。

広告

コピペせずに複数シートのデータをまとめる手順

パワークエリでシートごとに分かれたデータをまとめる前提として

「すべてのシート内のデータフォーマットが同じになっている必要がある」

という点には注意してください。具体的には各列の項目がすべて同じになっていないとうまく集計できません。

では手順を確認していきましょう。

【1】元データとは別の新しいExcelファイルを作成する

「2023給与データ.xlsx」が給与ソフトから出力したデータとします。これとは別に同じフォルダに新しいExcelファイルを準備してください。「給与データ集計クエリ.xlsx」が新しく準備したExcelファイルです。

【2】新規作成したExcelファイルを開き、「データ」-「データの取得」-「ファイルから」-「Excelブックから」を選びます

【3】ファイルを選ぶ画面が出たら「2023給与データ.xlsx」を選び「インポート」をクリックする

【4】ナビゲーター画面で左側の「2023給与データ.xlsx[3]」をクリックしてから「データの変換」を押します

【5】PowerQueryエディターという画面が表示されたら、不要なデータである右から3列を削除します

手順としては、中央の列(「Item」)をクリックしてからShiftキーを押したまま右端の列(「Hidden])をクリック。その後「ホーム」-「列の削除」をクリックします。

【6】Data列(右端の列)の右端にあるボタン(矢印が両端に向いているもの)をクリックします

「展開」と「すべての列の選択」が選択されていることを確認し、「元の列名をプレフィックスとして使用します」のチェックを外したら「OK」をクリックします。

【7】不要な列である「Column1」を【5】と同じ手順で削除します

【8】「Column2」の右端の下矢印をクリック後、「null」のチェックを外して「OK」を押します

【9】「ホーム」-「1行目をヘッダーとして使用」をクリックします

なお左端の列だけは「テスト太郎」が列名となってしまうため、列名をダブルクリックして「氏名」に変更します。

【10】「項目名」の右端の下矢印をクリック後、「合計」「項目名」のチェックを外して「OK]をクリックします

【11】「非課税通勤費」の位置を変更したいので列をドラッグして右端に移動します

【12】データを月別に並べたいので「項目名」の下矢印をクリック後「昇順で並び替え」をクリック

【13】「支給合計」から「非課税通勤費」の各列の書式を「整数」に変更します

具体的には、項目名左横の「ABC123」をクリックして「整数」を選びます。

【14】ここまでの手順が右端の「適用したステップ」に表示されていますので、わかりやすい名前に変更します

具体的には各ステップを右クリックして「名前の変更」を選んでからステップ名を変更します。

変更後の例としてはこのようになります。

【15】最後に「ホーム」-「閉じて読み込む」をクリックします

このように3枚のシートがひとつのテーブルまとまりました。このExcelファイルを保存すれば終了です。

今回は社員IDを設定していないため、月別に並べ替えた際に社員の並び順が崩れてしまいましたがご容赦ください。

次回、同じフォーマットの別データを集計したい場合には、「給与データ集計クエリ.xlsx」を開いて、「データ」-「データの取得」-「データソースの設定」を選んで、「ソースの変更」をクリックすれば別のデータファイルを選ぶことが可能です。

今回のような設定をすることなく、次回からは自動的に最終形まで集計してくれます。

広告

実際に使う場面をイメージできるかが大事

今回のようなデータを整形する手順はマクロを使っても行うことができますが、普段使わないマクロの場合

「これ何の処理してたんだっけ」

となってしまうこともあります。

パワークエリであれば実際の手順をステップという形で記録してくれますので、後からでも確認しやすくなっています。

もし毎回マニュアルなどを見ながらデータを加工しているのであれば、マニュアルの内容をExcelが代わりに覚えておいてくれるため非常に便利です。

本やネットでどれだけ情報を入手しても、その知識を具体的に活かす場面が想像できなければまったく意味がありません。

重要なのは実際に使う場面を具体的にイメージすることです。この作業に使えるんじゃないかとひらめくこと、何か使えないかと考えることが大事です。

もし似たような作業をしているのであれば、Excelのパワークエリを使ってみませんか。

投稿者

加藤 博己
加藤 博己加藤博己税理士事務所 所長
大学卒業後、大手上場企業に入社し約19年間経理業務および経営管理業務を幅広く担当。
31歳のとき英国子会社に出向。その後チェコ・日本国内での勤務を経て、38歳のときスロバキア子会社に取締役として出向。30代のうち7年間を欧州で勤務。

40歳のときに会社を退職。その後3年で税理士資格を取得。

中小企業の経営者と数多く接する中で、業務効率化の支援だけではなく、経営者を総合的にサポートするコンサルティング能力の必要性を痛感し、「コンサル型税理士」(経営支援責任者)のスキルを習得。

現在はこのスキルを活かして、売上アップ支援から個人的な悩みの相談まで、幅広く経営者のお困りごとの解決に尽力中。

さらに、商工会議所での講師やWeb媒体を中心とした執筆活動など、税理士業務以外でも幅広く活動を行っている。
広告