【PowerQuery入門】数字の変換・フィルター機能の実務テクニック

今回は、Power Queryを使った「数字の変換」および「フィルター機能」について、実務で直面しやすい課題を交えながら詳しく解説していきます。

今回使用するのは、社員名簿に給与情報が記載されたデータです。これを使って、経理や人事の実務でも頻繁に発生するデータの加工（集計や抽出）を行っていきます。Excel関数で行う場合との違いや、Power Queryの動作確認についても触れていきます。

今回の主なタスクは以下の4点です。

そして最後に、Power Query初心者が必ずと言っていいほどつまずく、フィルター機能の「AND/OR条件」の罠とその回避方法、さらにパフォーマンスを意識したクエリの組み方についても解説します。

数字の変換・フィルター機能の実務テクニック

表をテーブル化する

データをまずはテーブル化します。

【A1】セルをクリックして選択した状態で、キーボードの【Ctrl】+【T】を押し、表をテーブル化にします。

表内に破線がになる状態で、【テーブルの作成】ダイアルボックスが表示されるので【先頭行をテーブルの見出しとして使用する】にチェックを入れ【OK】ボタンを押します。

すると、表がこの様に【テーブル】になります。

テーブルをカスタマイズ

テーブル内をクリックすると、【テーブルデザイン】タブが表示されるので、クリックすると、左上にテーブル名が表示されるので【社員別給与】と入力し、キーボードのエンターキーを押します。

【テーブルデザイン】タブから【テーブルスタイル】を【なし】にします。

【テーブルデザイン】タブから【フィルターボタン】のチェックも外しておきます。

数字の変換機能の基本

【データ】タブからデータを【テーブルまたは範囲から】をクリックして選択します。

PowerQueryエディターが開いたら、まずは数値データの扱いについて基本的なメニューを確認しておきましょう。

【変換】タブの真ん中より少し右側に、数字関連のメニューが集まっています。これらを活用することで、関数を手入力することなく複雑な計算が可能になります。

統計（最大値・最小値・平均など）

【初任給】のヘッダーをクリックして、【統計▼】から【最大値】をクリックして、選択します。

セルが1つに集約され、その列の中で最も大きい数字だけが表示されます。この様に、結果を確認することもできます。

右側にある【クエリの設定】から、結果を使うことも出来ます。【計算された最大】を×で消して、ステップを削除します。

標準（四則演算）

加算、減算、乗算、除算などがここに含まれます。例えば【加算】を選び【500】と入力すると、選択した列のすべてのレコードに対して一律に【500】が足されます。

丸め（四捨五入など）

• 切り上げ、切り捨て、四捨五入が選べます。【四捨五入】を選び、桁数に【1】を入れれば小数点第1位を、【0】を入れれば整数に丸められます。
• また、マイナスの数値も指定可能です。例えば【-1】なら1の位を四捨五入して10の位に、【-2】なら100の位に丸めることができます。給与計算や見積書作成など、端数処理のルールが厳格な業務では必須の機能です。

※これらの機能は、データの整形が必要な際によく使いますので、ぜひ覚えておいてください。

今年度の給与額を求める（Null値の扱い）

それでは本題に入ります。

まずは今年度の給与額を算出しましょう。 手元にある数字は【初任給】【昨年度までの昇給額】【今年度の昇給額】の3つです。これらを足し合わせれば、今年度の給与額になります。

通常、列同士を足し算する場合、足したい列を選択して【変換】タブを見ても【標準】メニューがグレーアウトして押せません。

Null値が含まれる場合の注意点

ここでPower Query特有の、Excelユーザーが陥りやすい問題が発生します。単純に2つの列（例えば初任給＋昨年度までの昇給額）を足そうとしたとき、もしデータの中に【Null（空白）】が含まれていると、計算結果も【Null】になってしまうのです。

ExcelのSUM関数などでは空白セルを【0】として扱ってくれることが多いですが、Power Queryの基本的な演算では、【数字 + 不明な値(Null) = 不明(Null)】 という厳密なロジックで処理されるためです。

これを回避し、正しく計算する方法は主に2つあります。

①置換機能を使う：
計算を行う前に、【変換】タブの【値の置換】を使って、対象列のNullをすべて【0】に置き換えておきます。データのクレンジングとして最も基本かつ確実な方法です。

②3列以上をまとめて計算する（推奨）：
実は、3つ以上の列を選択して【列の追加】→【標準】→【加算】を行うと、Power Queryの挙動が自動的に変わります。

2列の場合は [列A] + [列B] という数式が生成されますが、3列以上の場合は List.Sum({[列A], [列B], [列C]}) という関数が使われます。この List.Sum 関数は、リスト内のNullを無視して（0として扱って）合計を出してくれるという便利な特性を持っています。

今回は3つの列（初任給、昨年度昇給、今年度昇給）を足したいので、これらをまとめて選択し、【加算】を実行するのが最も手軽です。これでNullが含まれていても正しい合計額（給与合計額）が算出されました。

計算に使った元の3列は不要になるので削除し、テーブルをすっきりさせましょう。

加算された行のヘッダーは【給与合計】としておきます。

賞与と総支給額の計算

次に、今年度の賞与額を求めます。 【賞与率】の列にはNull（ボーナス対象外の人など）が含まれているため、掛け算を行うと結果がNullになってしまいます。

ここでは前述の回避策1を採用し、【変換】タブの【値の置換】を使って、Nullを【0】に変換しておきます。これで計算エラーを防げます。

【給与合計額】と【賞与率】の列を選択し、【列の追加】→【標準】→【乗算】をクリックします。

これで個別の賞与額が計算されました。【除算】のヘッダーは【賞与額】に書き換えておきます。

このように、【小さな計算結果（列）を作ってから、それを足し合わせる】というステップを踏むことで、計算過程の検証がしやすくなり、ミスが発生した際の原因特定も容易になります。

給与合計と、賞与額を合計

最後に、先ほど出した【給与合計額】と今出した【賞与額】を足し合わせます。2つの列を選択して【列の追加】→【標準】→【加算】を実行すれば、今年度の【総支給額】が完成です。

トップ5の抽出とデータの更新

続いて、総支給額が多い順に5名を抽出します。
データの分析において、【上位〇件】や【下位〇件】を見ていきましょう。

まず、総支給額の列を【降順】で並べ替えます。これで一番給料が高い人が1行目に来ます。

次に【上位の行を保持】をクリックします。もしくは、【ホーム】タブにある【行の保持】から【上位の行を保持】をクリックします。

保持する行数を聞かれるので【5】と入力してOKを押します。

これでトップ5のデータだけが残りました。【賞与】の列を削除して、これをExcelのワークシートに読み込みましょう。

【閉じて読み込む】で既存のワークシートの隣などにテーブルを表示させます。

データの更新確認と自動化のメリット

Power Queryの最大の利点は、一度作った処理手順（クエリ）が保存され、元データが変わっても【更新】ボタン一つで最新の状態になる点です。

例えば、元データに【伊藤さん】という非常に給与が高いデータを追加したり、既存社員の給与を修正したりしたとします。この状態でExcel上のクエリテーブルを右クリックして【更新】を押すと、自動的に【給与計算】→【並べ替え】→【トップ5抽出】の処理が再実行されます。 結果として、1位に田中さんが割り込み、元々5位だった人がリストから消えるといった動作が瞬時に完了します。毎月の手作業での集計業務が不要になるのです。

フィルター機能と正しい使い方

最後に、フィルター機能の重要な解説です。基本操作はExcelのオートフィルターと似ていますが、条件が複雑になった場合（AND/ORの組み合わせ）に、Power Query特有の構造に注意が必要です。

もう一度、原本の表内をクリックして、【データ】タブから【テーブル又は範囲から】をクリックして【パワークエリエディタ】を立ち上げます。

今回、同じデーターを取り込んだので【社員別給与（2）】となっています。コチラを【FILTER】などに変えておきます。

【フィルター】の使い方は、例えば【役職】のフィルターをクリックして、【全て選択】をクリックしてから、フィルターをかけたい部署をクリックして選択します。今回は、【主任】を選択して【OK】ボタンを押します。

すると、【主任】だけのデーターが表示されます。又、【課長】が付くものを抽出する際、

【右】の【フィルターされた行】の歯車マークをクリックして選択します。

すると、【行のフィルター】ポップアップが表示されます。【主任】【指定の値に等しい】になっている事が分かります。

ここでは、【指定の値を含む】にして、【主任▼】から【課長】を選びます。

すると、【課長】のデーターだけが抽出されます。

違う項目名を抽出する

今度は、違う項目名を抽出する場合は、

【行のフィルター】ポップアップから【詳細設定】をクリックすると、違う列のフィルターもかけることが出来ます。
【初任給】にフィルターをかけ、

【次の値以上】から【4130000】を選択します。【OK】ボタンを押します。

すると、【課長】の初任給が【4130000】以上に絞ることが出来ます。

ANDとORが混在する時の落とし穴

問題になるのは、【A または B】かつ【Cではない】といった複雑な条件の時です。 例として、以下の条件で抽出したいとします。

• 所属部署が【総務部】または【製造部】である。（どちらかならOK）
• かつ、役職が【主任】ではない人。（主任は除外したい）

これを通常のフィルター操作で一気に行おうとすると、意図しない結果になります。

Power Queryが自動生成するロジックが、意図せず以下のようになってしまうことがあるのです。
所属部署 = 総務部 OR (所属部署 = 製造部 AND 役職 <> 主任)

これでは、【製造部の主任以外】は抽出されますが、【総務部】に関しては役職に関係なく全員が表示されてしまいます。本来やりたかった(総務部 OR 製造部) AND (主任以外) という抽出とは異なります。

解決策：ステップを分ける

数式バーでカッコ ( ) を手入力して論理式を修正することも可能ですが、M言語の記述に慣れていないとエラーの原因になります。

最も安全で推奨される解決策は【ステップを分ける】ことです。

1. ステップ1：部署の絞り込み まず【所属部署】の列で【総務部】または【製造部】をフィルターします。この時点で、対象部署の人だけが表示された状態になります。
2. ステップ2：役職の絞り込み 次に、その結果に対して改めて【役職】列で【主任と等しくない】フィルターをかけます。これは新しいステップとして記録されます。

【可能な限りフィルターを先に、並び替えは後に】と覚えておくと良いでしょう。

パワークエリで最強の機能は何ですか？

Power Queryにはさまざまな便利機能が備えられていますが、その中でもPower Queryの「ピボット解除」が最強です。 ピボット解除のメリットは、横方向に並んでいるデータを縦方向に配置することで、データ集計・分析、可視化に適した状態に変換できます。

パワークエリとパワーピボットはどちらが先ですか？

学習順はどちらが先でも良いのですが、パワーピボットの活用にはパワークエリの利用が前提となること、パワーピボットを利用しないユーザーにも便利な機能が含まれることから、パワークエリを先に学んだ方がスムーズかもしれません。

まとめ

今回の内容は、実務での給与計算やデータ抽出ですぐに使えるテクニックを解説しました。又、フィルターの複合条件やNull値の扱いは、知らないと計算ミスやデータの取り違えにつながるポイントですので、ぜひこの機会にマスターしてください。