« 2024年8月 | トップページ | 2024年12月 »

2024年11月

Excel Link 集

Excelの参考情報です。

■ 入力制限リストの連動

モダンExcelで集計する元の表の項目データに同義語があると集計が分かれてしまうので、同義語が入力されないようにする必要があります。また、階層項目については、存在しない組合せが入力されないようにする必要があります。そこで、入力規制で不正な入力を禁止するようにします。

【Excel】動的に変わる可変連動プルダウンリスト。名前定義を毎回手で修正なんてしてられません【データの入力規則】
https://www.youtube.com/watch?v=P2iOI1UNvBE

【Excel講座】★超便利★｢2段階/3段階｣ドロップダウンリストの作り方
https://www.youtube.com/watch?v=iCIEeQwickI
各階層に同じ名前（例：その他）を作ると名前が重複します。そこで、名前に階層名を付加することが必要です。
下記階層別のリストではなく、階層構造の一覧リストから作ることができると便利ですが、工夫が必要ですね。


■ 表の作り方

あなたのそれ、間違ってますよ。Excelには、正しい使い方があるんです
https://www.youtube.com/watch?v=BFJMzjlFpMw

【総務省】統計表における機械判読可能なデータの表記方法の統一ルールの策定
https://www.soumu.go.jp/menu_news/s-news/01toukatsu01_02000186.html


■ AIの活用方法

LLMへの質問の仕方などAIの活用情報です。Excelに限らず、色々使えます。

【実践】経理業務で活用！コピペで使えるChatGPTプロンプト集
https://bakuraku.jp/resources/how-to/chatgpt_prompt_1

ChatGPT/Copilotで業務効率化を実現！ 財務・経理業務での使い方を解説
https://www.ricoh.co.jp/magazines/back-office/column/chatgpt-copilot-zaimu/

深津式プロンプトシステムをExcelに実装してみた（無料サンプルファイルあり）
https://note.com/kappei/n/nd9a876427ee9

2024年11月25日 (月) Excel | | コメント (0)

DAX Link 集 (Excel, Power BI)

Excelの標準のピボットテーブルではできない集計計算を行うメジャー(DAX)の参考情報です。Power BIと同じです。WINDOW()など一部Excelでは使用できない関数があります。

メジャー(DAX)はピボットテーブルで使うことが多いと思いますが、ピボットテーブルの集計値に対して計算をしているのではなく、ピボットテーブルの元のテーブルに対して計算をしています。この点を勘違いしやすいので注意が必要です。簡単なデータやDAXを使用しない方法での検算をすることをお勧めします。

■ 概要


■ リファレンス

  • Data Analysis Expressions (DAX) リファレンス
    https://learn.microsoft.com/ja-jp/dax/
    本家なので一番正確な情報だが難しい。また、Power BIのDAXの説明であり、Excelでは使えない関数が記載されている。Excelでの使用可否の公式情報は無く、試して見るしかない。


■ 全般

■ メジャー

  • メジャーがうまく書けないの？ぇ？なんで？Power BI 勉強会 #19 で話したこと #PBIJP
    https://qiita.com/PowerBIxyz/items/75c477dc27d0a3c3412e
    この記事の中に「間違った記述」の例があり、その理由が文章で説明されています。この文字だけの説明を理解するのが難しいです。行コンテキストとフィルターコンテキストが区別できず？？となってしまいます。具体的なテーブルで重複カウントしてしまうところを示してくれるとありがたいのですが。行コンテキストとフィルターコンテキストにより何処がテーブルの処理対象になっているのかを頭の中にイメージすることが難しいのです。なので、特に解説本では文字だけでなく処理対象を示したテーブルを描いてほしいのですね。

  • Power BI： 行コンテキストをのぞく時、行コンテキストもまたこちらをのぞいているのだ
    https://qiita.com/k_maki/items/3c9615158cbb0941b6f8
    こちらは言葉だけでなく非常に少ないデータ付きで解説してくれているので、上の記事よりは少し分かりやすいと感じました。
    それでも説明不足に感じたところがあったので、自分なりの解釈をしたのが次の添付ファイルです。解釈が間違っているかもしれないですので、間違いがあればコメントをください。

ダウンロード - daxrowcontext.xlsx

  • (Excel/Power BI)メジャーが予期しない値を返す原因とDAXコンテキストの理解 : 当ブログ : 2025/03/18
    https://bigarrow.tea-nifty.com/blog/2025/03/post-8025c7.html
    関数の引数の位置、関数の外と中、でコンテキストが違います。それを自分なりに整理した記事です。


■ 移動平均


■ ALL


■ CALCULATE


■ 合成


■ SUM/SUMX


■ 事例集


■ タイムインテリジェンス関数


■ 累計

メジャーでの累計計算は、各累計を最初から加算するので遅いです。高速にできる方法をご存じの方は、コメントをください。
（メジャーの中で、自身のメジャーの参照ができると、前の累計結果を使えるのですが。。。）

■ 累計歯抜け

累計や区間合計をメジャーで計算する場合、ピボットテーブルに累計や区間合計を表示するセルがあるが、元データに該当するデータが無ければメジャーは計算されずその前にデータがあっても累計はnullになります。集計範囲を示すインデックスをMAX()などで求める場合、集計する元データがnullだと、MAX()で異なるインデックスを求めてしまいます。なので、nullを0に置換しておくなどの処理が必要になる場合があります。
別の視点で言うと、ファクトテーブルをALLの引数にして累計を計算するときは、ファクトテーブルの0埋めが必要らしいということです。

累計を計算するときは行方向のフィルタをALLで解除しますが、解除するテーブルの行に抜けが無ければ、0埋めの必要が無いと思われます。0埋めしないと計算が速いです。具体的には、抜けが無いカレンダーテーブルをピボットテーブルに展開し、ALLの引数にすると、ファクトテーブルの0埋めは不要になるらしいです。

累計のメジャーについては、下記に再考した正しい記事をアップしました。

  • 累計メジャーDAXの勘違い(歯抜け) : 当ブログ : 2025/01/27
    https://bigarrow.tea-nifty.com/blog/2025/01/post-08315e.html
    要点 : 時間軸の累計をするなら、必ず日付テーブルをディメンジョンテーブルに設定し、日付テーブルで累計範囲を指定するフィルタを設定する。これにより、歯抜けが無くなり、DAX式が短くなり、計算速度が向上する。

2024年11月21日 (木) Excel | | コメント (0)

Power Query Link 集 (Excel, Power BI)

Excelでの集計作業を楽にするPower Queryの使い方の参考になるリンクです。Power BIでもほぼ同じです。

■ ブロック図
モダンExcel(Power Query, Power Pivot)で複数のデータを集計する場合は、各データやクエリやピボットテーブルの関係をブロック図に書いておくことをお勧めします。
draw.ioはER図の図形が用意されているので、初心者がブロック図を書くのに便利です。
https://www.drawio.com/
241130drawio

■ 全般
パワークエリ x パワーピボットでエクセルのデータ集計が超進化！【Excel PowerQuery PowerPivot DAX】【データ分析】【ビッグデータ】/ExcelドカタCH
https://youtu.be/S_Wa_puqpD0?si=s-RNboYTBa2uU6_t

【機能】Power Queryとは何か？何ができるのか？を詳しく解説/Office TANAKA
https://youtu.be/Z-08XG5wwRc?si=imvUC7qoZ0VJ54Rc

【モダンExcel】PowerQueryってなに？【Excelでビッグデータ分析ができる？】/ExcelドカタCH
https://youtu.be/4N-OPrZcVb4?si=vBd9eANyBeERQyKP

あなたのそれ、間違ってますよ。Excelには、正しい使い方があるんです！/Office TANAKA（開始7分から1分間を見てください。）
https://www.youtube.com/watch?v=BFJMzjlFpMw

知らないと使い所を見極めきれない?Excelパワークエリでできること＆できないことを徹底解説します！
https://www.youtube.com/watch?v=LrAC2KXBpzg

パワークエリはデータクリーニングにて最強【EXCEL Power BI Power Query Data Cleaning】
https://youtu.be/22o2pCVCLFE?si=5z2qwihiUFy6fLps

この中にヘルパークエリを邪魔者あつかいしている人がいたら手を上げてください【Excel PowerBI】
https://youtu.be/uLV85sauJRw?si=pnAohvMvIwCG0iv8

"勤務時間表をフォルダに放り込むだけで残業45時間超の人を全自動でチェックできる【エクセル パワークエリ PowerQuery】"
https://www.youtube.com/watch?v=TvIsvzGiQTU

 

■ if文
PowerQuery IF文でカスタム列を作成する｜複数条件・ネスト
https://dono-log.com/powerquery-if/

 

■ M言語
PowerQueryのM言語とは？数式バーから使いこなそう【ユースフル パワークエリ】 ：あまり参考にならないが
https://youtu.be/Pxo0NVid50s?si=nKFCUk9K1VlWqJZ0

 

■ 型
Power Queryにおけるデータ型の考察
https://marshal115.hatenablog.com/entry/2021/09/26/183608

PowerQuery データが数値かどうか判定する｜データ型判定
https://dono-log.com/powerquery-valueis/


■ クロス表
ピボット解除の応用技 簡単操作でマトリクス形式の表をデータベース形式に変換【エクセル パワークエリ】【Excel PowerQuery】
https://www.youtube.com/watch?v=fWw9ueVkHto

#2 表の変換にマクロなんかいらない【エクセル パワークエリ PowerQuery】1レコード複数行・複数列の表をリスト形式に変換する方法
https://www.youtube.com/watch?v=VleTkjBmaCA

ピボット解除応用：多階層クロス集計表のデータ化
https://modernexcel7.hatenablog.com/entry/2019/10/05/232407

【エクセル パワークエリ】 列が多段のマトリクス形式の表をリスト形式に変換する方法【Excel PowerQuery】【ピボット解除】
https://note.com/excel_dokata/n/neb26fe4b8331


■ クロス表(複数) 関数クエリ
【Excel】マウス操作だけのパワークエリからレベルアップ！カスタム関数クエリでワンランク上のデータ整形
https://www.youtube.com/watch?v=jDIwpBCVibY


■ GETPIVOTDATA
#6 田中亨先生が教える「今月のExcelテクニック」（2019年3月号） | 田中亨(Office TANAKA) 先生 ～ピボットテーブルを途中経過に使うには～
https://youtu.be/qYumneUQ044?si=Ns5CLG2aLJMVXB68

[Excel Tips] Power pivot からワークシートへデータを取り出して動かす
https://note.com/jinyanakamura/n/n05df06e06065

Relative Date Item in Power Pivot GETPIVOT DATA excel function
https://stackoverflow.com/questions/73737683/relative-date-item-in-power-pivot-getpivot-data-excel-function

GETPIVOTDATA formula for PowerPivot Tables
https://stackoverflow.com/questions/68416531/getpivotdata-formula-for-powerpivot-tables

GETPIVOTDATA Function for Power Pivot
https://www.myonlinetraininghub.com/getpivotdata-function-power-pivot


■ 更新
【Excel】VBAで、PowerQueryの更新を待ってピボットテーブルを更新
https://shikumika.org/entry/2023/05/12/120000

ファイル統合・集計の２つのクエリをVBAで一発更新
https://youtu.be/7fnUc9ag2zo?si=8xhT5TI0X6UrZSnB

【Excel】VBAで、PowerQueryの更新を待ってピボットテーブルを更新
https://shikumika.org/entry/2023/05/12/120000
ActiveWorkbook.RefreshAll
Sheets("集計").PivotTables("ピボットテーブル1").PivotCache.Refresh

【パワークエリ×マクロ】作成したクエリをボタンひとつで順番に更新する方法
https://sakatakablog.com/excel/1775/

VBAでPowerQueryとピボットテーブルの更新を制御する
https://www.kurumico.com/excel-vba-tool-queryrefresh/5328/

パワークエリの更新→反映を待つ→ユーザーフォームを表示 をする方法
https://euc-access-excel-db.com/tips/ct07_se/ct075012_xls2k_vba_tips/powerquery-refresh_n_userform-open01

VBA RefleshAll作業を待ってから次の作業に移る
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14205174064

【解決】ピボットテーブルの結果が更新/反映されない｜自動更新は？
https://pasokon-labo.com/pivottable-not-update/

[Excel][VBA] QueryTableの更新を待つ
https://gist.github.com/furyutei/195db82a57a098e7d8c5d09a84a92b64

Power BIのSharePointからのファイル読み込みが遅い原因
https://qiita.com/ryokita/items/56efb54b3452f56ec681

■ サイズ
VLOOKUP・クエリのマージ・リレーションでデータを接続してExcelブックのファイルサイズを比較してみた【Excel Power Query/ Pivot Table Relationship】
https://www.youtube.com/watch?v=ep-aQquyA88

 

■ 財務
"PLをフォルダに放り込むだけで科目ごとの数値を比較できる【エクセル パワークエリ PowerQuery】"
https://www.youtube.com/watch?v=xxc5sCl6U3w

Excelでリストの対照比較表を作る【VSTACK, XLOOKUP, パワークエリ, Power Query, クエリのマージ】
https://youtu.be/9mQ2dun72XU?si=isx5xQ3Fvm-Xrn5u

ビジネスで役立つExcelテクニック：ピボットテーブルを使った年別売上と前年比の同時集計方法を解説
https://youtu.be/jY-nhc5ckM8?si=dmcY6Se-HLt7CYTp

 

■ 速度
【初心者必見】パワークエリでステップを減らす?テク5選【Excel PowerBI】
https://youtu.be/7IUiJsyCRNA?si=B6Zz4I2jdCQuhYTv

Power Query でテーブルをマージしたいが遅いんだよね。それは仕方がないとして、できることを考える。
https://qiita.com/PowerBIxyz/items/5834e2fa37302e226bb2

Power Queryがここに来てぐっと重くなりました
https://asakaki.blogspot.com/2019/12/power-query.html
一番効果があるのはクエリを直接参照するのではなく、EXCELのシートを参照した方が関係するクエリを動かさないようで処理は速いです。

Speed/Performance aspects
https://www.thebiccountant.com/speedperformance-aspects/

重いPowerBIの処理速度を速くする7つの方法
https://power-bi-seminar.site/8562/

【Excel パワークエリ 動画解説】Table.Bufferでクエリの処理速度は本当に向上する？
https://note.com/excel_dokata/n/nc68f9e0068b7

Table.Buffer Power Query "M" 関数がデータセットの更新時間をどのように改善したかを学ぶ - Power BI
https://www.youtube.com/watch?v=xv5UxtUCOK0

List.Buffer To The Rescue - Saturday With Power Query
https://www.youtube.com/watch?v=0bXbllvA7Ns

Power BI、Power Queryのパフォーマンス最適化
https://www.youtube.com/watch?v=lHukBo2Whqo&t=232s


■ パスワード
Power Queryパスワード保護された複数ブックをPower Queryで操作する方法！4回 実務ではEXCELに必須のスキルはPower Queryとピボットになった！
https://youtu.be/-rJD5GRDkY4?si=8wC41KKf9Yb7mLZE


■ パラメータ クエリ
【Tips_Q001】クエリの接続先をセルの値で切替える？ 『パラメータークエリ』を作りませんか ？Excel Userだけの特権ですよ！
https://youtu.be/LNHwkYzFF9M?si=G8zXXiQzLH44TWWT

Excel PowerQuery > ファイルパスを変数として設定する方法
https://aftp.tokyo/rpa/uipath/excelpowerquerysetconfig/

セルの値でクエリを変化させる(パラメータクエリの基本)
http://officetanaka.net/excel/function/GetAndTransform/32.htm


■ 日付
タイムインテリジェンス関数で過去データを楽々集計【EXCEL DAX】
https://youtu.be/f6geJaqwrT8?si=V54TX60KaAH9uZaC

Power Queryで日付から月次締め日を作る【EXCEL/Power BI】
https://youtu.be/Bdt-rmVr140?si=5rwYG-s5QybYeQaS

タイムインテリジェンス関数で過去データを楽々集計【EXCEL DAX】
https://www.youtube.com/watch?v=f6geJaqwrT8

【Excel】ピボットテーブルで効率よく売上の前月比を計算！簡単なクリック操作だけでエクセルのデータを分析するテク
https://forest.watch.impress.co.jp/docs/serial/exceltips/1113100.html

 

■ 表示
【Excel】ピボットテーブルのデフォルトレイアウトを変更する
https://www.youtube.com/watch?v=7O3QwDv9LyI

 

■ フォルダ
【Tips_Q0015】作成時には気づかない！「フォルダーから」の罠 3選
https://www.youtube.com/watch?v=XjSLEPsVmiE

【Excel パワークエリ】これを見ても、まだコピペでいいなんて言うヤツ……いねぇよなぁ
https://www.youtube.com/watch?v=UapvFSxv2mE&t=2251s


■ 丸め
パワークエリで四捨五入するなら絶対に知っておいてください【Excel Power Query Round 偶数丸め 銀行丸め】
https://www.youtube.com/watch?v=HezN5E1M-KA

 

■ 予実偏差
予算実績ギャップ分析の仕組みづくり ①予実分析モデルの概要＋最終アウトプットの確認
https://youtube.com/playlist?list=PLy9HJBBnwVT_Mko3PlixAuhfPsRiw7e4e&si=6POoGkPml55CgLTs

 

■ リレーション
【EXCEL】アクティブなリレーションと非アクティブなリレーションをUSERRELATIONSHIP関数で切り替える【Power BI/Power Query/Power Pivot】
https://youtu.be/ZtX2pozqjRg?si=UD4I2nAqv9kUcesS


■ 累計
Power Query の累計 - 正しい方法!
https://www.youtube.com/watch?v=7kFZw8zimCU

 

■ 複数の表の結合、表の整形

2024年11月21日 (木) Excel | | コメント (0)

« 2024年8月 | トップページ | 2024年12月 »