マガジン
Excel パワークエリ、パワーピボット に関する記事まとめ
Excelドカタの書きもの
- 32本
Excelやパワークエリやパワーピボットを実務で使う場合のテクニックやノウハウを書いた有料記事を記事をまとめている買い切りマガジンです。有料記事含め、これまで投稿した記事すべてを閲覧可能です。
記事一覧
『Excelドカタの集会所』オープンのお知らせ
これまで、私はExcelの使い方や機能に関する解説を
という形で続けてきました。
今回、情報を発信する場として、さらにこちらのサイトを開設しましたので、そのお知らせをさせていただきます。
この『Excelドカタの集会所』は、私が制作したExcelに関する解説動画を投稿するサイトです。
Excel解説動画の投稿は、これまでもYouTube『ExcelドカタCH.』で続けていましたが、動画
【Excel パワークエリ 動画解説】名前定義したセルからデータを取得する
こちらの動画では、Power Query でクエリを作成するExcelブック内で名前を定義したセルやセル範囲からデータを取得する方法を解説します。
Excelのシート上にあるテーブルのデータを、クエリで取得するには「テーブルまたは範囲から」という機能を使います。
この「テーブルまたは範囲から」という機能ですが、実は表示されるヘルプをよく見ると、名前付き範囲からデータを取得するクエリも作れる、と
【Excel動画解説】ワークシート関数で加重移動平均を集計する
こちらの動画では、Excelのワークシート関数を使った数式で、加重移動平均を計算する方法について解説します。
以下のような、日ごとの気温データあります。
このようなデータをそのまま使って線グラフなどを作成すると、日々の気温の変動がモロに現れるため増減が激しいギザギザしたグラフになり、全体的な傾向がつかみづらくなってしまいます。
こういった場合は、移動平均を求めて値の変化を平準化することで、グ
【Excel DAX 動画解説】メジャーで加重移動平均を集計する【パワーピボット PowerBI】
こちらの動画では、DAX関数を使ったメジャーの数式で、加重移動平均を集計する方法について解説します。
以下のような、日ごとの気温データがあります。
このようなデータをそのまま使って線グラフなどを作成すると、日々の気温の変動がモロに現れるため増減が激しいギザギザしたグラフになり、全体的な傾向がつかみづらくなってしまいます。
こういった場合は、移動平均を求めて値の変化を平準化することで、グラフで
【Excel動画解説】重複するコードに連番の内訳を追加する
こちらの動画では、Excelで重複するコード等に対して、各コードの内訳の番号を連番として追加する方法について解説します。
Excelのデータで、下の画像の注文番号のように、コード等が重複するデータがあります。
この重複する注文番号に対して、上から連番となる内訳をそれぞれ追加したい。
この内訳の番号を数式で作成する場合、データがテーブルに設定されているか否かで、
セル参照の数式
構造化参照
【Excel パワークエリ動画解説】クエリのマージならマウス操作だけでVLOOKUPできる
こちらの動画では、Excelで2つのテーブルのデータを結合するために、VLOOKUP関数をつかった数式ではなく、「クエリのマージ」をつかってマウス操作だけで行う方法について解説します。
Excelでテーブル同士のデータの結合を行う場合には、VLOOKUP関数を使った数式で行うのが一般的です。
VLOOKUPだけではなくINDEX&MATCHや、XLOOKUPといった関数を使っても同じようなデー
【Excel パワークエリ 動画解説】同じコードに対して内訳の番号を追加する
こちらの動画では、Power Query で重複して存在するコード等に対して内訳番号の列を追加する方法について解説します。
パワークエリで取得したテーブルに、このように重複するコードがあるとき、それぞれのコードに対して内訳となる番号の列を追加したい場合。
もしこのテーブルがExcelのシート上にあるのであれば、セルを相対参照する数式を作ることで、比較的簡単に追加することができます。
しかし、パ
【Excel パワークエリ動画解説】セル結合でスキマだらけの表をパワークエリで簡単に整形する
こちらの動画では、Excelでセル結合によってデータがスキマだらけになった表を、パワークエリの機能でマウスだけで簡単にスキマを埋めて整形する方法について解説します。
Excelで作成した表の中で、複数のセルが結合されてしまっていることがあります。
結合されたセルは、一見するとそのすべてに同じ値が入力されているように見えますが、実際は一番最初のセルにだけ値があり、その他のセルは空っぽです。
つ
【Excel パワークエリ 動画解説】列名を固定せずに、テーブルのすべての列を結合する
こちらの動画では、Power Queryでテーブルの列名を指定せずに、テーブルの全ての列を1つの列に結合する方法について解説します。
パワークエリによるデータの整形で、データが1列にくっついてしまっているものを別々の列に分割する、ということがよくあります。
こういった場合は、Power Query エディターがもつ列の分割という機能を使うと、簡単にカンマを区切り記号として列を分割することができ
【Excel パワークエリ動画解説】手作業コピペから卒業するために、集計元のデータを取得・結合するクエリを作る
こちらの動画では、Excelで集計する表が、1つではなく複数存在するときに、パワークエリをつかって複数の表を1つに結合してから、ピボットテーブルで集計する方法について解説します。
Excelでデータを集計するための機能といえばピボットテーブルですが、普通の使い方をする分にはピボットテーブルで集計する元データの表は1つしか設定できません。
例えば、2023年の表と2024年の表、2つの表のデータ
【Excel DAX 動画解説】新規顧客と既存顧客の人数と売上をメジャーで集計する【パワーピボット】
こちらの動画では、DAX関数を使ったメジャーの数式で、フィルターした期間における新規顧客と既存顧客の人数と売上を集計する方法について解説します。
Excelのピボットテーブルで月ごと・顧客ごとの売上の合計を集計し、以下のような結果になったとします。
仮に、この集計表で2月時点の新規顧客の人数と売上を算出すると、このようになります。
また、2月時点の既存顧客の人数と売上を算出すると、このように
【Excel動画解説】2024年度の縦型カレンダーをスピルする数式で作成する
こちらの動画では、Excelで3月決算の場合の2024年度の縦型カレンダーを、スピルする数式で作成する方法について解説します。
Excelで縦型の年度カレンダーを作成する場合、昔からの方法としては、
1年分の日付の列をオートフィルで作成する。
日付の列を参照して、数式で曜日や四半期といった各種日付項目の列を追加する。
といった手順を取るのが常套手段でした。
この方法だと、すべての日付・曜
【Excel パワークエリ 動画解説】グループ化で作成した集計表に小計・合計の行を追加する
こちらの動画では、Power Query の『グループ化』で作成した集計表のテーブルに、小計や合計の行を追加する方法について解説します。
Power Query の『グループ化』という機能を使うと、クエリで集計表を作成することができます。
ところが、グループ化によって作成した集計表には、小計や合計の行が存在しません。
こちらの動画では、Power Query の『グループ化』によって作成した
【Excel動画解説】OFFSET関数で参照するセルの位置や範囲を動的に変更する
こちらの動画では、ExcelのOFFSET関数の機能と、具体的な活用方法について解説します。
Excelのシートに作成した数式でセルを参照する場合、数式でセル番地を入力することになります。
また、最近のExcelでは、数式でセル範囲を参照した場合は配列として扱われますので、その配列がスピルによってシート上に展開されます。
このように、数式でセル番地やセル範囲を指定したら、当たり前ですが、指定
【Excel 動画解説】DAXで2つのテーブルの和集合・積集合・差集合のテーブルを作成する【パワーピボット】
こちらの動画では、DAX関数で、2つのテーブルから和集合・積集合・差集合のテーブルを作成する方法を解説します。
2つの集合があるときに、その集合をどのように組み合わせるかのパターンとして和集合・積集合・差集合という考え方があります。
集合をテーブル、集合の要素をテーブルの列の値としたときに、2つのテーブルの和集合・積集合・差集合のテーブルを作成する。
そのためのDAX関数として「UNION」