Excelドカタの書きもの
マガジン
Excel パワークエリ、パワーピボット に関する記事まとめ
Excelドカタの書きもの
- 33本
Excelやパワークエリやパワーピボットを実務で使う場合のテクニックやノウハウを書いた有料記事を記事をまとめている買い切りマガジンです。有料記事含め、これまで投稿した記事すべてを閲覧可能です。
記事一覧
【Excel 動画解説】VLOOKUPではなくINDEX&MATCHを勧める人がいる理由
こちらの動画では、ExcelでVLOOKUP関数の代わりにINDEXとMATCHの関数を組み合わせた数式を使用する理由と、数式の作り方について解説します。
Excelで以下のように「社員ID」から「氏名」をサーチしたいとき、ほとんどの場合VLOOKUP関数を使うと思います。
では、「電話番号」から「氏名」をサーチしたいとき。
このようなケースではVLOOKUPは使えません。
なぜならVLOO
【Excel パワークエリ 動画解説】意図的にエラーメッセージを表示させてクエリの処理を停止する
こちらの動画では、Power Queryでエラーメッセージを表示させて、クエリの処理を停止させる方法について解説します。
パワークエリのM言語に組み込まれている関数に対して不正な引数を入力したりすると、エラーが返ってきて、かつエラーの内容に関するメッセージが表示されます。
これと同様に、自分で作成したカスタム関数クエリ等でも引数として渡された値を判定して、不正な引数だった場合はエラーメッセージ
【Excel パワークエリ】カスタム関数クエリの引数に既定値を設定する
+++ やりたいこと +++パワークエリで作成した以下のカスタム関数クエリの第2引数にデフォルトの値（既定値）を設定したい。
// 日付年度変換(_calendarDate as date, _fyEndMonth as number) as number =>let 日付 = _calendarDate, 決算月 = Number.Mod(_fyEndMonth, 12),
【Excel 動画解説】XLOOKUPで正規表現による検索
こちらの動画では、ベータ版のExcelで、XLOOKUP関数に追加された新たな一致モード【正規表現による検索】を解説します。
これまでExcelのXLOOKUP関数では、引数で4種類の一致モードを指定することができました。
そこに、今後はもう1つの一致モードとして【正規表現に一致する】が追加されます。
こちらの動画では、XLOOKUP関数の一致モードに正規表現が追加されたベータ版のExcel
【Excel パワークエリ 動画解説】《実践編》カスタム関数クエリにドキュメントを追加する
こちらの動画では、Power Queryで作成したカスタム関数クエリにドキュメントを追加する方法について、より実践的なサンプルを使って解説します。
前半となる解説編では、以下のように非常に単純な関数クエリをサンプルにして、自作の関数にドキュメントを追加する方法を解説しました。
// 数値を10倍する関数クエリ= (input as number) as number => input * 10
【Excel 動画解説】プレビュー実装された『Python in Excel』
こちらの動画では、Excelにプレビュー機能として実装された【Python in Excel】の紹介と、実際にExcel上でPythonのコードを書いて実行する方法の解説を行います。
PythonのコードでExcelブックを操作する、というのは昔からExcel作業自動化の方法として、VBAほどではないにしろよくありました。
しかし、今回Excelにプレビュー機能として新たに実装された【Pyth
【Excel パワークエリ 動画解説】《基礎編》カスタム関数クエリにドキュメントを追加する
こちらの動画では、Power Queryで作成したカスタム関数クエリにドキュメントを追加する方法について解説します。
パワークエリでカスタム関数クエリを作成した場合、通常は以下のように表示されます。
= (input as number) as number => input * 10
引数の入力欄だけがあり、この関数がどんな働きを持っているか、といった説明は何も表示されません。
しかし、
【Excel】フィルターで抽出された行を数式で判定する
+++ やりたいこと +++Excelの表でフィルターやスライサーによって抽出された行を判定してTRUE（抽出されない行はFALSE）と表示させたい。
最終的にはフィルターで抽出した行の氏名を結合して、１つのテキストにしたい。
+++ 前準備 +++データを用意
フィルターによって抽出するデータの表、またはテーブルを作成する。
抽出判定の列を追加
表またはテーブルに「抽出判定」の列を追加
【Excel パワークエリ 動画解説】1列の表から行列折返しの表を作成する
こちらの動画では、パワークエリで1列の表から規則的に行列を折返しの表を作成したり、逆に行列を折返しの表から1列の表を作成したりする方法を解説します。
このように氏名が縦に並んだ1列だけの表があります。
これを5行で列を折り返し、かつ4列で行を折り返した表に整列し直したい。
こういった一定の規則に沿ってデータを整列するのは、プログラミングの練習問題などでよくありそうですが、パワークエリをつかっ
【Excel パワーピボット DAX 動画解説】カンマ区切りの保有スキル一覧から必要なスキルをすべて持つメンバーを抽出する
こちらの動画では、カンマ区切りの保有スキル一覧から、スライサーで選択したスキルをすべて持つメンバーを、DAX関数をつかったメジャーの数式で抽出する方法を解説します。
各メンバーがどんなスキルをもっているのかをまとめた以下のようなテーブルがあります。
このような元データからスキルのスライサーを作り、スライサーで選択したすべてのスキルをもつメンバーだけを抽出したい。
以前、YouTubeチャンネ
【Excel パワークエリ 動画解説】ヘルパークエリなしでフォルダ内のファイルを結合する
こちらの動画では、パワークエリでフォルダ内のファイルを結合する際、ヘルパークエリを生成せずに結合する方法について解説します。
パワークエリでフォルダ内にあるファイルを結合する場合、通常は「ファイルの結合」という機能を使います。
すると、特に難しい操作をすることなく、自動でステップが生成されて、簡単にフォルダ内のファイルの中身が1つのテーブルに結合されます。
ところがこの方法ですと、ヘルパーク
【Excel パワークエリ 動画解説】Metadataとは
こちらの動画では、Power Queryで半角カタカナを全角カタカナに変換する方法について解説します。
一般的にMetadata（メタデータ）といえば、データに関する様々な属性情報のことを指します。
具体例をあげると、デジカメで撮影した写真の画像ファイルには、画像データと一緒に様々なメタデータが含まれています。
このメタデータですが、パワークエリで作成されるデータにおいても追加することができ
【Excel 動画解説】スライサーで選択した「すべて」のスキルをもつ人を抽出する
こちらの動画では、カンマで区切られた保有スキルの一覧表から、スライサーで選択したスキルを「すべて」もつ人を抽出する方法について解説します。
以下のような各メンバーがもつスキルの一覧表があります。
この中から特定のスキルをスライサーで複数指定することで、そのすべてのスキルをもつ人だけを抽出したいです。
これを実現するためのポイントは2つです。
1つ目は、スキル名のスライサーを作成するために、
【Excel パワークエリ 動画解説】半角ｶﾀｶﾅを全角カタカナに変換する
こちらの動画では、Power Queryで半角カタカナを全角カタカナに変換する方法について解説します。
Excelのシート上にある半角文字を全角文字に変換するのであれば、JIS関数を使うことで簡単に変換できます。
ところが、パワークエリで同じような半角→全角の変換をしようと思うと、こうはいきません。
なぜならパワークエリで使われるM関数にはJISのようなワークシート関数と同じ機能をもつ関数が
【Excel パワークエリ 動画解説】全角カタカナを半角カタカナに変換する
こちらの動画では、Power Queryで全角カタカナを半角カタカナにに変換する方法について解説します。
Excelのシート上にある全角文字を半角文字に変換するのであれば、ASC関数を使うことで簡単に変換できます。
ところが、パワークエリで同じような全角→半角の変換をしようと思うと、こうはいきません。
なぜならパワークエリで使われるM関数にはASCのようなワークシート関数と同じ機能をもつ関数
【Excel 動画解説】FILTER関数で2つのテーブルから差集合・積集合のテーブルを作る
こちらの動画では、ExcelのFILTER関数をつかって、2つのテーブルの差集合・積集合となるテーブルを作成する方法について解説します。
Excelでセル範囲や配列、テーブルから特定の条件を満たす値を抽出して配列を作成する場合FILTER関数を利用します。
もし抽出条件が「BまたはG」のように2つだけ、しかも今後変わることがないのであれば、以下の数式のようにFILTER関数の第2引数を指定して