PowerQuery

Power Queryで、Excelファイルを読み込み処理をするケースで、Excelの名前の定義に#REF!があるとエラー発生することがあったので備忘録です。 名前の定義に#REF!があれば必ずエラー発生ではありません。 エラーの内容 Excelファイルを読み込み処理をしようとすると、 [DataFormat.Error] Relationship タグには、属性 'Target'が必要です。 というエラーが発生して処理が適切に実施されませんでした。 Relationship タグというものを利用した認識もなく、発生しました。 原因と対応 明確な原因は不明ですが、今回のExcelファイルには名…

デジタルツールの一部で利用される用語の認知度について、インターネット調査で102名にアンケートを実施してみました。 メンション、スレッド、レコード、フィールド、クエリ、リレーションについて「おおよその意味が分かるもの」の調査です。 デジタルツールの一部で利用される用語の認知度 「メンション」も最近よく耳にしますが約２割の認知度でした。 なお、「全て、おおよその意味が分からない」と回答した人の割合は半数超でした。 デジタルツール用語の使用例 上記用語のうち、PowerQueryでは、クエリ、フィールド、レコード、リレーションなどが使用されています。以下はクエリ、フィールド、レコードの用語紹介です…

Power Queryで、ユーザーフォームを準備してkintoneのデータをExcelに読み込む事例です。 ユーザーフォームはExcelのセルを使用した簡易的なものですが、利用者がPowerQueryエディターを起動せずに設定値を変更できるため、活用時の制約が少なくなります。 内容： アウトプットイメージ 設定手順 1．基本的な手順で「元になるクエリ」を作成（抽出条件は固定） 2．Excelのセルを使用し、簡易的なフォームを作成 設定の基本知識 Excelシートでの設定イメージ 3．PowerQueryの「ソース」ステップを修正する 「ソース」ステップの修正前後のイメージ 確認したExcelは…

Power Queryでデータを抽出するとき、フィルター条件をセルで指定できると便利です。 Excelのセルであれば、PowerQueryのフィルター条件（指定値）をPower Queryエディターを起動せずに変更が可能です。また、数式を用いた動的な日付指定なども可能で自動化の幅も広がります。 今回は、日付を含むデータを事例に、セルの値をもとにデータ抽出する方法です。 内容： アウトプットイメージ 実施手順 1．基本的な手順で「元になるクエリ」を作成（抽出条件は固定） （１）PowerQueryに読み込み （２）フィルターの処理 ２．セルの値を変数とするクエリに修正する （１）設定用のテーブル…

Power Queryの並び替えで、昇順・降順ではなく指定した任意の順番に出力したいときの方法です。都道府県の順に並び替えをする事例です。 内容： アウトプットイメージ 実施手順 1．並び替えの基準になるリストを準備する 2．元データと「並び替えの基準になるリスト」を結合する （1）クエリのマージをクリック （2）クエリのマージの設定 （3）結合されたテーブルを展開する （4）追加した順序の情報で、並び替えをする 参考情報 １．Excelの並び替えを活用した方法 ２．データの不足チェックの事例 アウトプットイメージ 下図左側の表（元データ）をPowerQueryで都道府県のコード順を基準にした…

Power Queryで、Excelの表からではなく「空のクエリ」からテーブルを作成する方法です。数式がシンプルとなりやすいテーブル関数#tableを活用した事例です。 活用場面としては、PowerQueryの利用目的上、簡易なマスタテーブルを「Excelの表」とは別で持ちたい場面などです。 #tableの基本知識 テーブル関数#tableの基本構文は、次のとおりです。 #table(columns as any, rows as any) as any 出典： #table - PowerQuery M | Microsoft Learn 実施手順 「空のクエリ」の作成 「空のクエリ」を起動…

最近のExcelに標準で含まれているPower Query（パワークエリ）は、マクロを利用せず、複数ファイルの結合やデータ抽出の自動化が可能なツールです。 ExcelユーザーがPower Queryを初めて操作するときに知っておくと良い用語について、個人的な備忘録です（正式な定義ではありません）。まずは概要理解のため、最低限と思うものについて、簡単な説明です。 内容 テーブル（Table） クエリ（Query） フィールド（Field） レコード（Record） リスト（List） テーブル（Table） 見出しの行と、各行にデータがある一覧表で、データの加工や抽出がしやすい形式で保存されてい…

Excel&VBA解説サイト「エクセルの神髄」様出題の問題集、 「VBA100本ノック」に対する私の回答と解説のページです。100本ノックの出題リストはこちらから excel-ubara.com 出題：WEBから100本ノックのリストを取得 ソースコード 解説 出題：WEBから100本ノックのリストを取得 以下のページにはVBA100本ノックのリストが掲載してあります。 https://excel-ubara.com/vba100sample/vba100list.html この一覧を表形式でシートに出力してください。 方法不問。VBAで自動取得すれば良い。 ◇ 出題ページはこちら excel…

WEBページ内にある表(テーブル)を、ワークシートに書き出すコードを紹介します。↓こちらの表を取り込んでみましょう。 品番 品物 種別 価格 1-1 みかん くだもの 100 1-2 りんご くだもの 200 1-3 いちご くだもの 300 WEB読込にはいろいろな方法がありますが、ここでは手軽に実行できる、 「QueryTableオブジェクト」を使用する方法を紹介します。 QueryTablesプロパティでWEBページを読み込む ソースコード 解説 汎用関数化 QueryTablesプロパティでWEBページを読み込む ソースコード Sub WEBページの表をシートに出力する() Dim 対…

(本投稿はゆる会計アドベントカレンダー 2024の12日目の記事です) CSVの山に戦慄せよ 会計系の実務では、多様なCSVを元にして色々とデータを加工する場面によく遭遇します。そんな中、数百万件の仕訳データや同形式の数十個のCSVを目の前にして絶望したご経験をお持ちの紳士淑女も多いことでしょう。本記事は、そんな絶望に立ち向かうための一つの選択肢についての紹介になります。 なお本手法はゴリゴリのEUCであり、スポット的に発生する大量or巨大なCSVに個人技で抗うためのものです。定型的なタスクであれば、組織のリテラシーレベルに合わせた手法を模索することを推奨します。 PowerQueryの限界 …

Microsoft Wordの差し込み印刷はExcelのリストを指定できるため、PowerQueryで抽出したデータも利用可能です。 Webページの仕様によりますが、PowerQueryを使えばWebページからデータをExcelに読み込むことができます。 従って、PowerQueryを起動しての更新を実行する必要はありますが、kintoneのデータをもとにWordの差し込み印刷文書なども作成可能です。 内容： Wordでの差し込み印刷設定 データ更新の注意点 PowerQueryでWebページからデータをExcelに読み込む方法 確認のWordは「Microsoft® Word® for Mi…

Webに公開される技術文書の中には少なからずjson形式のファイルが存在します。 【こんな形式です】 ＩＴなんてまったく理解していない管理職にこんな形式で報告しようものならヒステリックな反応されること間違いなしなので、EXCELなどの加工しやすい形式に変換する方法を知っておくと便利です。 というわけでJSONファイルをEXCELに展開する方法をご紹介します。 ネットワークをしているとドメイン名やグローバルIPアドレスの情報がjson形式で公開されているので、このような方法で定期的にrouting tableを更新するようにすれば、あの高価なベンダー情報を買う必要もなく運用することができるように…