PowerQuery

Excelにはいろんな機能があります。 関数 ピボットテーブル VBA（マクロ） Power Query / Power Pivot じゃあ、どれを使えばいいの？どれを勉強すればいいの？ Excelに慣れてきた人が直面する課題です。 この図を見てください。それぞれの機能の学習に必要な時間と得られる効果を対比しているイメージです。 Excelを始めるときに、みなさん最初は「関数」から入りますよね。いちばんとっつきやすいですし、まあ、それしか方法を知らないというのもあります。関数が使えないとExcelを使えることにはなりませんし。でも、この関数は、とっつきやすいですが、奥は深く、学習時間もかかりま…

おはこんばんにちは、ビショップです。最近PowerQuery for Excelってのが社内で話題です。私もなんか使い方を勉強してますけども、今回はその使い方ではなく、おそらく誰しもよく分からずに使ってしまっているであろう、データソースの「プライバシーレベル」について、設定によってどんな動きになるか調査・検証してみました。プライバシーレベル自体は、データソースごとに設定するもので、そのデータソース自体がどの程度の機密性を持つかを指定するものだという認識で良いと思います。異なる2つのデータソース間で検索条件等をやり取りする場合に、設定したレベルによって、データの取り扱い方が変わります。レベルは3…

パワークエリとは クリックだけでやってみた パワークエリの凄さはここから パワークエリとは こんにちは、今回はいよいよパワークエリについて触れます。といってもパワークエリが何か？と言われると正確なことはわかりません。しかし、確かにわかっていることは エクセルの加工集計などの機能が VBAなどのプログラミングをしなくても 簡単に自動化できるような機能 簡単に言うと、「簡単且つ便利」な機能です。もちろんパワークエリにも言語があり、プログラミングすることもあります、それにより更に多くの機能が使えますが、ここでは、 クリックだけでできる 簡単 便利な機能 を中心に触れていきます。 クリックだけでやって…

PowerQueryカスタム関数の練習で、生年月日から基準日(又は現在日付)時点の年齢を出力するものを考えてみました。 （１） 条件 （２） 作成手順(要約) （３） コード （４） 関数の実行 （５） 実際の使用例 （６） さいごに （１） 条件 ExcelのDATEDIF関数と同じ結果が得られること。 関数に設定する引数は、birthdate(生年月日)、basedate(基準日)の二つ。但し、基準日は省略可能とし、省略した場合は現在日付を設定する。 （２） 作成手順(要約) ①生年月日から基準日間の日数を取得 ②期間の日付リストを作成 ③年齢の計算(②から生年月日の数をカウント、但しうる…

PowerQueryとVBAを使ったExcel生産管理システムがもうすぐ完成します(*'ω'*)そのあとはまだ幾つか作らなくてはいけないけど・・・。 でも･･･VBAで配列考えるよりも断然良い！データサーバー持っていなくても全然問題ない！VBAのみでシステム作っていた時よりも本当に楽です(⋈◍＞◡＜◍)。✧♡ ヤバーい！興奮しかない！ありがとー、私にPowerQueryの存在を教えてくれたMicrosoft！ この写真の男性、私じゃないですよ～。 商用無料の素材からチョイスです♪ 私は女性です(￣ｍ￣〃)ぷぷっ!

大した内容ではないのですが、備忘も含め書いておこうと思います。 （１） やりたいこと （２） 実践 （３） 解説 （１） やりたいこと 下図の通り、フォルダからPowerQueryエディターへファイルを読み込んだ後、そのファイルの一覧のフォルダパスから最終保存先フォルダのみを新たな列に抜き出す方法となります。単純に「列の分割」で区切り記号毎に分割してしまうと各行のフォルダ数が合わない場合（下図3,4行目のように）はうまくいきませんのでそこも考慮した書き方にしています。 （２） 実践 下図はフォルダからファイルを取り込んだ後、適当な列を削除した状態です。 列の追加タブ → カスタム列 をクリック…

最初に言っておくと、今回の記事は例によって本来PowerQueryでやるようなものではありません。元々Excelワークシート関数でやったことをPowerQueryでもできるかなと思い試しました。ただ、試行錯誤しながらやった中でM関数の使い方等勉強になったところが色々あり、他で活かせるかなと思っています。 （１） やりたいこと （２） 事前準備 （３） コード （４） 実践・経過説明 （５）動作確認 （６）別解 ※２０２１/１０/２９追記 （１） やりたいこと ある月(又は期間)の勤務日程を決める際、一日毎の出社率が決まっており、日ごとに出社する人と在宅勤務する人とに分けたい場合に、決められた条…

仕事で、とあるフォルダの中のエクセルブックの特定のシートの情報をまとめる必要がありました。しかも、まとめる対象の内容のエクセルブックは毎回変わるというものでした。こんな時こそPowerQueryの出番…というわけで意気揚々と使用してたんですが、なんとフォルダ内のエクセルは別の人が作業中である可能性があることが判明。 誰かがフォルダの中のエクセルを開いていると、フォルダの中にエクセルのテンポラリファイルができるので、それも処理対象にしてしまってエラーになるみたいなんですよね。解決方法がないかとネットで調べていたら、やはり同じことで悩んでいる人がいました。↓ social.technet.micr…

状況 対処法 手順：/x オプション付きのショートカットを作成する Excelファイル（.xlsx, .xlsなど）を直接開くときは？ 注意点：別プロセスのExcelは挙動が変わる おまけ 参考 状況 仮想デスクトップ1でExcelを起動する。 続いて、仮想デスクトップ2でExcelを立ち上げると、仮想デスクトップ1に強制的に切り替わってしまう。 地味にストレスだったので解決策を調べてみました。 対処法 結論としては、別プロセスでExcelを開くと良いです。 Excel 2013 なら、Alt + クリック で起動できます。（リンク先を参照） support.microsoft.com バージ…

普段、私はテーブルを好んで使用しています。テーブルを使う理由、テーブルを使わない理由について、個人的に思いつく限りのことを書いてみました。 個人的な解釈に基づいた表現をしており、見方によっては評価が逆転することもある点にご注意ください。 テーブル化するメリット 良い感じに着色とオートフィルタが設定される（好みによる） ウィンドウ枠の固定が不要（好みによる） スライサーが設定できる 範囲選択が簡単になる（Ctrl+Space / Shift+Space / Ctrl+Shift+Space / Ctrl+A) セル結合が絶対に使われていない表にできる 列の数式が自動入力される データを追加したと…

たとえば、 文書ID,組織コード,保管アドレス 901234567890987654321,0012,3-5 909876543210123456789,0013,3-7のような CSV ファイルを Excel で直接開くと、 文書ID 組織コード 保管アドレス 9.01235E+20 12 3月5日 9.09877E+20 13 3月7日 のように勝手に変換されて表示されてしまう。かといって、いちいち PowerQuery で開くのも面倒だ。そこで、Excel の QueryTable で CSV データを文字列として 取り込む VBSript ファイルを作ってみる。 CSVをExcelで開…

こんにちは。CData Software Japan リードエンジニアの杉本です。 今回はMicrosoft が提供するBIツール「Power BI」から Yahoo! JAPAN DATA SOLUTION API に接続する方法を紹介したいと思います。 Yahoo! JAPAN DATA SOLUTION API とは？ Yahoo! JAPAN DATA SOLUTION API とはヤフー・データソリューションのデータをAPI経由で様々なツールから利用できるようにするサービスです。 2021年1月時点、ヤフーのビッグデータをブラウザー上で調査・分析できるツール「DS.INSIGHT」の…

ExcelのPowerQuery。機能がいいなぁ。 ずっと大量ファイルを結合する時にしか使っていなかったけど、本当に良い！ 便利すぎる！感動だー！ この写真の中で使っている商品名は私の考えた「ミヤジ」セレクト。 今年の受講生、気付いた人いるだろうか・・・ わかる人にだけわかればいい。 それこそが、真のファン(￣ｍ￣〃)ぷぷっ!

こちらは素人です。 エクセル２０１６でパワークエリ、ピボットテーブルを使います。office2016スタンダードなので、パワーピボットタブ出ません。（「開発」タブのＣＯＭアドインにパワーピボットがない） （１）日付を作る数字の羅列があるとして（半角）、 2020101920201020 整数型の場合、これを日付にするには、（2020/10/19など）一度テキスト型に変換するそうです。（その上で日付型にする） その時、「現在のものを置換」でなく、「新規手順の追加」を選ぶこと。 そうしないとエラーになります。 現在のものを置換にすると、ステップが追加されず、「整数型」→「日付型」でエラーになります…