shikumika’s diary

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

PowerQuery

【Power Query】List.FirstNやTable.FirstNのcountOrConditionの理解

Power QueryのList.FirstNとTable.FirstN、LastNなどの関数は、リストやテーブルから最初(LastNの場合は末尾)のN個の要素を取得するために使用します。 これらの関数の第二引数countOrConditionは、数値または条件を指定できます。 数値を指定した場合は、…

【Excel】「データ」タブの重複削除とPowerQueryで重複削除の違い

Excelで重複しているデータを削除するとき、「データ」タブの重複削除とPower Queryでの重複削除では、残すデータの仕様に違いがあります。 具体的には、Excelの「データ」タブの重複削除は、リスト内で最初に出現する値が保持されるとなっています。 suppor…

【Power Query】VLOOKUPの近似一致と同様処理:区間分け(ビニング)の方法

PowerQueryで、ExcelのVLOOKUP関数の「検索の型をTRUE」(近似一致)にする方法の備忘録。Excelでは、「年齢」を「年代」に変換など、区間分け(ビニング)にVLOOKUP関数は便利です。 PowerQueryでも同様の処理を実現したい場合があり、その手順は次のとおり…

【Power Query】Excelバージョンによる取込可能なデータソースの違い

ExcelのPowerQueryは、バージョンによって取込可能なデータソースが異なります。例えば、PDFをExcelにインポートする際にはPowerQueryが便利ですが、Office 2016やOffice 2019では利用できず、Microsoft 365のExcelで利用可能です。 JSONの取り込みは、Offic…

【Power Query】列のピボット解除で、マトリクス表をリストに変換

Power Query(パワークエリ)には、マトリクス表をリストに変換できる「列のピボット解除」という機能があります。 「列のピボット解除」には、「その他の列のピボット解除」と「選択した列のみをピボット解除」を含めて3種類あり、それぞれの処理の違いに…

【Power Query】列のピボット解除で、単票形式のExcelシートからセルの値を抽出

前回はPower Query(パワークエリ)のカスタム関数の作成事例として、単票形式のExcelシートからセルの値を抽出する方法を紹介しました。今回は列のピボット解除などを活用して、同様にセルの値を抽出する方法の備忘録。 個人的な結論として、カスタム関数の…

【Power Query】カスタム関数の作成事例:単票形式のExcelシートからセルの値を抽出

Power Query(パワークエリ)のカスタム関数の作成事例として、単票形式のExcelシートからセルの値を抽出する方法の備忘録。 カスタム関数が利用できると、データ抽出等の作業効率化も可能です。 単票形式で紙印刷のレイアウトはPower Queryでの抽出も手間と…

【Power Query】動的な列名に対応するため、列を数値で指定する方法

Power Queryで、元データの列名が変わるなど動的な列名に対応するため、列番号を示す数字で列を指定したい場合があります。Table.ColumnNames関数を利用すると、列名ではなく数値で列指定が可能で、方法の備忘録。 これにより、クエリ更新時に列名が変わる場…

【Power Queryの初歩】簡単な事例説明(起こりそうなエラーと対応の事例)

Power Query(パワークエリ)の初心者の方が知っておくとよいエラーと対応の事例。簡単な事例ですが、通常の編集作業で起こりそうなエラーと対応方法の備忘録。Power Queryでのエラー対応の基本として紹介。 次の事例は、クエリの結果を変更したいケースで、…

【Power Query】既定ではクエリ名を変更すると出力のテーブル名は変わる

Power Queryを初めて操作してから慣れるまでは、設定変更の影響でエラーが発生しないかが気になるところです。そこで、クエリ名の変更でテーブル名とシート名への影響を調べた備忘録です。 使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」。 Powe…

【Power Queryの初歩】簡単な事例説明(使いながら覚える最初のポイント)

Power Query(パーワークエリ)は最近のExcelの標準機能として利用できます。Excelの機能ということで、使いながら覚えようとしたとき、最初に戸惑うことがいくつかあります。以下、簡単な事例で、初めてPower Queryを操作するときに知っておくと良いと思っ…

【Power Query】データソースの管理方法(ファイルの状況と対応例)

Power Query(パワークエリ)を活用する際、データソースをどこに配置し、どのように接続すると効率的かで悩みます。せっかく設定してもデータソースの保存場所や名前変更で、データソースエラーとなり、その都度の設定が必要になったり、部外や社外の人と結…

【Excel VBA】テーブルに別ファイルのデータをインポート

Excelのテーブルに「別ファイルのシートにあるデータ」をインポートし、テーブルのデータを更新するVBAサンプルの備忘録。 作成の背景は、Power Queryで同じファイル内のシート(テーブル)をデータソースとする管理をしたいが、元のデータ自体は別ファイル…

【Power Query】データソースを相対パスにして動的に変更する方法

Power Query(パワークエリ)は、データの変換や統合に便利なツールです。しかし、データソースのパスが固定(絶対パス)の指定で、ファイルの場所が移動すると設定変更が手間です。データソースを相対パスにして動的に変更できると便利なので、最もシンプル…

【Excel】ファイルの保存場所変更に備えた準備(ファイルパスの自動更新)

リンクの設定等があるExcelファイルの場合、保存場所の変更に注意が必要です。特に、VBAやPower Queryなどで固定のファイルパスに依存した構築を行うと、Excelに不慣れな人がファイルの保存場所変更に対応できない等の問題が生じる可能性があります。 ファイ…

【Excel】VBAで「 テーブルをセル範囲に変換してエクスポート」のサンプル

Excelのテーブル機能やPower Queryを含むファイルを他者に渡すことが妥当でないケースが時折あります。そこで、VBAで「 テーブルをセル範囲に変換してエクスポート」のサンプルを作成してみた備忘録です。

【Excel】フォーマットは同じで、 「ファイル名」が異なるデータのインポート効率化

Excelの標準機能にあるPowerQuery(パワークエリ)を活用して、フォーマットは同じで、「ファイル名」が異なるデータのインポート業務を効率化する方法を調べた備忘録。 背景は、同一フォーマットで、ファイル名が取得時期や対象によって異なる次のようなフ…

【Excel】パワークエリで、複数のファイルを 1 つに結合する方法

Excelの標準機能にあるPowerQuery(パワークエリ)は、同一フォーマットで入力された複数ファイルを一つにまとめたい時に便利です。 毎月の売上データや、部門別の実績データといった複数のファイルを一つに結合する場面などに活用できます。これまでであれ…

【Power Query】データソースの変更方法

PowerQuery(パワークエリ)は、毎月更新されるようなファイル(データソース)から、必要な情報の抽出を容易にできるツールで、とても便利です。 しかし、操作になれていないと、Excelであれば簡単なことの実現方法が分からずに困ることがあります。 例えば…

【Power Query】データソースのエラーを解消する方法(DataSource.Error)

PowerQuery(パワークエリ)で、データソースのファイル名や、ファイルの場所が変更になると、次のようなデータソースのエラーが発生します。 DataSource.Error: ファイル ‘〇〇.xlsx' が見つかりませんでした。 例えば、データソースとなるファイル名に「売…

【Power Query】空白ではない列の個数を行ごとにカウントする方法

Power Queryを使用して「ExcelのCOUNTA関数」のように、各行ごとに空白ではない列の個数をカウントする方法の備忘録。 ExcelのCOUNTA関数は、特定の範囲内の空白でないセルの数を数えるために使用しますが、Power Queryで同様な関数はList.NonNullCount関数…

【Power Query】特定の列と同じ値の列の個数を行ごとにカウントする方法

Power Queryを使用して「ExcelのCOUNTIF関数」のように、特定の列と同じ値の列の個数を各行ごとにカウントする方法の備忘録。 なお、特定の列ではなく、"指定した値"でカウントしたい場合は、前回の以下です。 shikumika.org アウトプットイメージ 横方向の…

【Power Query】指定した値と同じ値の列の個数を行ごとにカウントする方法

Power Query(パワークエリ)を使用して「ExcelのCOUNTIF関数」のように、指定した値と同じ値の列の個数を各行ごとにカウントする方法の備忘録。 アウトプットイメージ 横方向のカウントで、「ExcelのCOUNTIF関数」でいうと次のような数式。縦方向のカウント…

【Power Query】全角を半角に一括置換(Text.Replaceを使う時の注意点)

Power Queryで、全角文字を半角文字に一括置換。Text.Replaceを使った時にエラーが発生して変換できなかったので原因と解決方法の備忘録。 全角文字を半角文字に一括置換が必要になった背景は、Excelで表記ゆれがある日付文字を日付データ(シリアル値)に変…

【Power Query】Excelの日付文字列にある「元年」を「1年」に置換

Excelで日付文字列に和暦の「元年」があるとき、DATEVALUE関数ではシリアル値には変換できない(反対に、西暦から和暦の「元年」への表示は可能だが)。 そのため「検索と置換」の機能で”元年”を”1年”に一括置換をしたり、SUBSTITUTE関数で置換したりで対応…

PowerQueryの数式編集時、日本語変換で二重入力される事象の解決メモ

PowerQueryで数式を編集する際、日本語入力(漢字変換)をすると二重入力となり、都度、文字削除が必要でとても煩わしく感じていました。ATOK(ATOK for Windows ver32)を利用していましたが、Microsoft IMEに変更することで、適切に入力できるようになった…

【Excel】VBAで、PowerQueryの更新を待ってピボットテーブルを更新

VBAで、PowerQueryの更新を待ってピボットテーブルを更新するときの備忘録。PowerQueryの更新が完了してから次の処理を実行したいときはBackgroundQuery プロパティがFalseの状態にしておく。 以下は、PowerQueryの更新を待たずにピボットテーブルが更新され…

【Power Query】Excelで文字列から数字だけ抽出する方法

Excelで文字列から数字だけを抽出したい時があります。 例えば、名簿の名寄せをする場面などで、電話番号から余分な括弧やハイフンを除きたいなど。 このようなケースも、最近のExcelに標準で含まれているPower Queryは便利です。 以前は、以下の記事にある…

【Power Query】空白の列を追加する方法

Power Query(パワークエリ)で、空白の列を追加したい時があります。 例えば、他のExcelの表に貼り付けするなどの都合で、列の順序や数合わせののために項目名はあるが、データは空白にしたいケースなど。 初歩的なことですが、Power Queryを初めて使用した…

【Power Queryの初歩】列を追加する基本操作

Power Query(パワークエリ)を初めて利用するとき、色々な項目があって悩みます。しかし、列を追加する基本的な操作方法は、「列の追加」にある次の赤枠内を知っていれば十分に使える印象。 PowerQuery(パワークエリ)の基本的な操作方法 「列を追加」のポ…