タグ別アーカイブ: エクセル
XLOOKUP関数_ドリル
OneDriveのフォルダ内の全てのファイル・シートを自動変換する
今回はクラウド上のエクセルファイル内で使える、オフィススクリプト(旧VBA)とPower Automateの組み合わせで自動化処理を行います

目次
やりたい事
OneDriveのフォルダに入れた「すべて」のエクセルファイルの「すべて」のシートを、事前に決められた内容に従って変換処理を行うことです

上の画像では、B2セルの①フォント文字を強調、②背景の色を黄色に、の2種類の変換が行われています
この①②の処理をすべてのファイル、すべてのシートで行います
ポイント
読者の皆様の中には、エクセルの記録マクロを使用した方が多くいらっしゃると思います
今回はエクセルマクロのクラウド版、オフィススクリプトで記録した内容を修正して活用します
修正した内容は、Power Automate/クラウド版RPAで動かします
Power AutomateでOneDriveのあるフォルダから全てのファイルの内容を取得し、エクセルファイルのみを選別して前述のオフィススクリプトを動かします
オフィススクリプト
記録操作
オフィススクリプトは「自動化」のタブから行います

自動化のタブをクリックすると「操作を記録」が行えるようになります

「操作を記録」を押した後に①フォント文字を強調、②背景の色を黄色、を行い「停止」を押します

「停止」を押すと、記録されたコードが「編集」から見れるようになります

コードで注目して頂きたいのは2つの点です
一つ目は「波括弧」です

{と}の間にB2セルを選択した処理が2つ書かれています
特に注目して頂きたいのが2点目です
こちらの「let」です

こちらはプログラミングの「変数」と同じ役割をします
「workbook.getActiveWorksheet()」すなわちファイル内の「アクティブ=選択・処理しているシート/単数」を「selectedSheet」に当てはめています
ですので、次に続くコードはselectedSheetすなわち「選択・処理しているシート/単数」 の 「getRange(“B2”)=B2セルの内容」 の「getFormat=書式」 の 「getFont=フォント」 を 「setBold(true)=太くする」になります
次はこのletで設定する変数と、変数を使用していたコードの内容を変更します
コードの変更
この時点では、letで設定する「selectedSheet」は選択されているシートのみが対象になります
こちらを次のように変更します

「workbook.getWorksheets()」とすることで、ファイル内の全てのシート(sがつく複数形)にします
そして、次に取得したシートの全てに前述の①②の処理が行えるように修正を行います
要は繰り返し処理を行うのですが、繰り返し処理の構文*は次の通りとなります
*こちらのページのコレクションの箇所になります
for(let 変数2 of 変数1){
・繰り返し処理する内容1
・繰り返し処理する内容2
}
こちらの内容は英語の「All of ~」と同じになります
All of ~とすることで~の内容の全てが対象になります
この仕組みと同じです
ですので、letで取得したSheetsの全ての各シート:Sheetを変数2として設定します

ここで、では「Sheets」で設定した内容と「Sheet」の内容とは何が違うのか?と疑問に思われる方もいらっしゃると思います
対象は同じですが、Sheetsの方は複数形で全体そのものを指しています
一方、Sheetの方はあくまで全体の構成要素の一つ一つを指します
では、コードの修正の最後として「selectedSheet」を「Sheet」に変更します

こちらは「Ctrl+H」によって一気に変更することも可能です

コードの修正が完成したらコードの保存を行い、名称(allSheets)を付けます

Power Automate
Power Automateでは、フォルダ内にある全てのファイルを取得しつつ、各ファイルに対してオフィススクリプトを実行します

Power Automateではシナリオを実行するトリガー(起動条件*)をまず最初に選ぶのですが、今回は「インスタントクラウドフロー」すなわち手動でトリガーを選択します
*メールが届いた時、〇時に起動などを本来は選択できます
次にフロー名を付けて「手動でフローをトリガーします」を選択します

次の画面にて新しいステップを追加します

そして「OneDrive」を検索します

次にOneDrive関連のアクションから「フォルダー内のファイルリスト」を選択します

「フォルダー内のファイルのリスト」のアクションでは、該当のフォルダを設定します

この「フォルダー内のファイルのリスト」アクションにより、該当フォルダ内のファイルが一覧になります
ファイルの一覧は後述する「ID」という形で次のアクションに受け渡します
これで2つアクションが設定できました
次は「スクリプト(オフィススクリプト)の実行」アクションを設定します

スクリプトの実行アクションでは、前述のIDを指定します

上のGIF画像では、ファイル欄にIDを設定した途端に「Apply to each」というものが適用されています
これはeach/それぞれのファイルにスクリプトを実行するという意味になります
スクリプトは前述の「allSheets」を選択します

付け加えてスクリプトを実行するファイルは、拡張子が「.xlsx」に限定できるようにします
この限定作業には「コントロール」から「条件」を使用します

次に展開する画面で実際の条件を指定します

条件を設定したら、「スクリプトの実行」アクションは「はい」の下に移動します

これで「フォルダーから全てのファイルを取得」⇒「拡張子が.xlsxのファイルにてスクリプトを実行」のシナリオが完成しました
<まとめ>
今回はオフィススクリプトとPower Automateを組み合わせ、フォルダ内のエクセルファイルの全てのシートを変更する方法を解説しました
これは従来のVBAやRPAでは行えない処理です
クラウドの醍醐味がある処理だと思います
ぜひ試してみてください
【クラウド活用】Power BIのデータセットの内容をOneDriveのエクセルに自動転記
Power BIで作成した内容をエクセルで検証したい、もしくはエクセル活用したいというケースも多いと思います
しかも、クラウド上にて利用できるととても便利です
今回はPower BI Desktopからクラウドに「発行」した内容を、OneDriveのエクセルにPower Automateを利用して自動転記する仕組みを解説します
ポイント
Power Automate内で、対象となるデータセットを指定した上でDaxクエリを書くのがポイントです

基本的にはクエリの中身は、後述するようにPower BI DeskTop内で作成し、コピーするのが無難です
クエリの準備
クエリを作成するのに何をしたらいいかわからないかたは、基本的には「SummarizeColumns」関数を使うところから始めるのがよいと思います

SummrizeColumns関数により、必要な対象列を指定します
クエリのコピー
Power BI Desktopで作成したDAX式は、前述のようにPower Automateのアクションにコピーします

ただ、EVALUATEと宣言するのを忘れないでください

クエリの出力内容の処理
クエリの出力内容はデータ操作のアクションで処理します

その後に、エクセルに転記します

転記内容は関数で前処理を行っておきます

<まとめ>
今回はPower Automateによりクラウド上のデータセットから、OneDriveのエクセルに転記する方法を解説しました
クラウドでデータを連携できると、データ活用の効率があがるので、ぜひご利用頂きたいと思います
尚、今回の仕組みだとPower Automateのフローを動かすとOneDrive上には、自動的にデータ行が追加されていきます
シナリオによっては、重複が起こらないようにデータを削除しておく必要があるかと思います
その時には、クエリを実行する前のフローに対して、繰り返しエクセル行を削除しておくフローを追加する必要があります
お仕事の依頼

実務を担う現場の方が使い慣れたエクセル。Excellent仕事術は、このエクセルを効果的に活用して、御社の業務改善やお困りごと解決の最適なスキームをご提案/ご提供します!
御社にこんな悩みはございませんでしょうか?
・エクセル作業がブラックボックス化しており、膨大な人手がかかっている
・分析資料は沢山あるが、業績改善に向けた課題や傾向が全く把握できない
Excellent仕事術では、これらの悩みに対して「現場重視」で現場の声を反映させながら解決策を提案します!
ご相談自体は無料でうけたまわります。エクセルに関することであれば、どんなことでも幅広く「Excellent仕事術のガッツ鶴岡」へご相談ください!
プロトタイプ(試用版)の開発についても低価格で請負ます!
Excellent仕事術の得意分野
VBA/マクロ開発
エクセル作業がブラックボックス化する理由は、大きく分けて3つあります。
Excellent仕事術では従来のIT部門では対応できなかったこの3つの問題を、これまで培ってきたマクロ/VBA開発力で解決します。
エクセルシートのスパゲティ化

エクセルシートのスパゲティ化は、1シートに「データ」「計算」「作業スペース」を詰め込むことで起こります。
異なる内容が詰め込まれている為、「どこの範囲がどの内容」で、「どの範囲とどの範囲がどのように絡み合っているのか?」が第3者に分かりにくくなります。
スパゲティ化したエクセルシートは、作成した担当者しか扱えないだけでなく、データが不規則に蓄積されているため有効活用ができず効率化が行えません。
スパゲティ化したエクセルシートを改善するポイントは、「データ」「計算」「作業スペース」を1シートに混ぜず、それぞれシート毎に集約することです。
Excellent仕事術ではスパゲティ化したエクセルシートを、3つのステップで劇的に改善します。
・Firstステップ
➡ ヒアリング及びシートの中味の分析
・Secondステップ
➡ シートを「データ」「計算」「作業スペース」などの目的別に作成
・Thirdステップ
➡ 作業スペース・シートでの作業をVBAにより自動化
改善したエクセルシートでの作業は効率化されているだけでなく、特定の担当者以外でも引継ぎの負担なく作業が行えるようになります。
データのバケツリレー

エクセルファイルでデータを外部の会社や他部署とやり取りすることは、良く行われていると思います。
データ活用の目的が外部の会社や他部署と違う為、「エクセルファイルの交換」はファイルやシート間での膨大な「データの転記処理」を発生させることになります。
実は、VBA/マクロを活用すれば複数のファイルやシートの「データ取込み・変換処理」 を、一括で行うことができます。

一括で「データ取込み・変換処理」が行えれば、膨大な転記処理から解放されるだけでなく、取り込んだデータを活用して「周辺業務」の効率化も行えるようになります。
Excellent仕事術では、現場の声をお聞きして「データ取込み・変換処理」から「周辺業務の改善」まで徹底的にサポート致します。
エクセルの共有

エクセル1ファイルで大量のデータを扱う場合によく出てくる問題として「共有」の問題があります。
エクセルは表計算ソフトであり、本来は大量のデータを扱うには不向きです。1シートに蓄積できる行数に制限があり、蓄積データが大量だと動きが重たくなります。
しかも、 1ファイルを複数の担当者で同時に共有して使用できない為、担当者に待ち時間が発生してしまい、どうしても業務効率が上がらなくなります。
Microsoft社の製品では、データベースソフトとして「Access」があります。Accessであれば、行数に関係なく大量のデータを扱うことができ、複数の担当者で共有して使うことも可能です。
ところが、Accessだと現場では行えない業務も中にはあります。特に「大きなクロス表での作業」などは、エクセルのような表計算ソフトを業務用にカスタマイズした表でないと効率があがりません。
こういったエクセルでないと行えない業務を、複数担当者でエクセルファイルを共有しながら作業したい場合には抜け道があります。
エクセルとAccessをVBA技術により連携して使うという方法です。
この方法であればエクセルとAccess、互いのメリットを組み合わせて業務を行うことが可能です。

このエクセルとAccessn連携する仕組みでは、データはAccessに一元管理し、エクセルには都度、必要なデータだけ呼び出して活用します。
このエクセルとAccessの連携を行う場合にポイントとなるのは「エクセルの役割」と「Accessの役割」の切り分けです。
Excellent仕事術では、連携の仕組みを構築するだけでなく、業務を理解させて頂いた上で「連携のポイント」についても提案させて頂きます。
➡事例①
データ分析/統計処理
「数値/分析資料は沢山あるが、業績改善に向けた課題や傾向が全く把握できない」ことの主な原因は、鳥の目で全体像を捉えることができていないことが考えられます。

例えば「前期と比べて注文単価が何故下がったか?」について、注文単価自体の推移を見ても、単位が荒すぎて「何故下がった」かはよく分からないはずです。
逆に注文台帳を見ても、今度は単位が細かすぎて「注文単価が前期と比べて何故下がったか?」はよく分からないはずです。
では下に記述したように、注文を単価別にグループ分けして件数の推移を確認した場合はどうでしょう?
・低単価の注文件数の推移
・中単価の注文件数の推移
・高単価の注文件数の推移
低単価の注文内容と高単価の注文内容では、注文に含まれる商品の違いや、注文した会員の行動や属性に違いが必ずあるはずです。
低単価の注文件数の構成が高くなっているのか、もしくは高単価の注文件数の構成が低くなっているのか、を注視すれば「注文単価が下がった原因」はある程度見えてくるはずです。
このように業績改善に向けた課題や傾向を捉えるには、必ず鳥の目でグループ別の推移を捉えることが必要です。
このグループ別の推移を捉えるのに、必ずしも「データアナリスト」や「CRMソフト」が必要とは限りません。エクセルの集計機能と統計機能を駆使すれば、エクセルでも対応は可能です。
Excellent仕事術では、これまで培ってきたエクセル集計技術と統計処理・技術を活用して、御社の数字分析のお手伝いをします。
会員分析
会員分析のポイントは大きく分けて3つあります。
・新規会員の獲得状況 ➡会員数自体の分母を増やせているか?
・新規会員の2回目のリピート状況 ➡新規会員が定着しているか?
・既存会員のリピート状況 ➡優良会員が確実に育成できているか?
つまり、まずは「新規・既存」と「優良・非優良」の2つの切り口で会員のグループ分け(セグメント)を行った上で、会員のグループ間の移行状況を把握することが必要です。
Excellent仕事術では上記の2つのグループの切り口から、更に深堀した切り口でグループ分けを行います。
そして、基本の「新規・既存」「優良・非優良」のグループに加えて、更に深堀りしたグループ間の移行率を把握するためのツールを提供します!
➡事例②
その他の分析
Excellent仕事術では、発注・在庫分析や予算作成・中期計画のシミュレーションなども得意分野としております。
ぜひ、この分野についての悩み事もExcellent仕事術にお問い合わせしてみてください!
ダッシュボード化
数字分析を行った内容については、社内で共有し、次の行動につなげていくことも大事なことです。
Excellent仕事術では社内の効率的な業績報告、情報共有のために一目で現状を掴むことができるダッシュボードの作成も行います 。

プレゼン・資料作成
競合他社とのプレゼン競争に勝つためには、「数字の魅せ方」に突破口があります。海外のエクセル活用事例をうまく取り入れた「数字の魅せ方」こだわりましょう。「数字なんてただの数でしょ?」。そんなことはありません。「目で見て、数字の変化がリアルにわかる」効率的な資料作成術を、Excellent仕事術/ガッツ鶴岡がご提供します
-シンプルで誰の目も釘付けにするグラフ関連資料の作成をいたします。

Excellent仕事術/ガッツ鶴岡の強み
業務分析の経験が豊富
-上場企業から創業期のベンチャー企業まで、様々な事業規模での業務分析の経験が豊富
-内部監査も含めた内部統制対策をとおして、業務フロー図等の作成・業務分析の経験多数あり。
数値管理の経験が豊富
-銀行や証券会社等の金融機関への数値説明・交渉の経験が豊富
-EC通販での予算管理や中期計画作成、各種分析などの経験が豊富
最新のエクセル技術
Excellent仕事術/ガッツ鶴岡は、世界の最先端のエクセル技術を常にキャッチアップしています!
-海外では日本と違い、エクセルの新機能/便利機能をうまく使いこなして業務の効率化に成功している事例が沢山あります
-小難しいVBAコードや複雑な関数は必要ありません。Excellent仕事術/ガッツ鶴岡がお教えする世界最先端のエクセルの新機能を使って、効率よく簡単にエクセル作業ができるようになります。
-エクセルは元々、表計算ソフトです。データベースソフトではありません。しかしパワークエリという新機能を使えば、エクセルをデータベースソフトとしても使いこなせます
Excellent仕事術のガッツ鶴岡がパワークエリとの連動を作成します/お教えします。
事例①
<商品管理システム/エクセルとAccessの連携>
業務メンバー各自が業務に即したエクセルのクロス表を使用しながら、エクセルへの入力データはAccessファイルに蓄積して各自で共有する仕組みを構築

<改善前・課題>
★1つのエクセルシートに様々な業務データと入出力表が混在
・商品の特性上、入荷した商品を手動で分割・並べ替えて管理する必要があった
・商品の並べ替え順を各業務で使い回すため、1シート上に様々なデータが混在
・データ蓄積型式が不規則になり、VBAなどの自動化手段の活用が不可能
・1ファイルをメンバー全員で共有して使用するため、業務メンバーに待ち時間が発生

<改善後・効果>
★業務データはAccessに一元管理し、エクセルの入出力画面を業務毎に分散
・データはAccessに一元管理し、ADOという技術を使用して、都度、必要なデータのみエクセルファイルに呼び出す仕組みを構築。このADOの技術により、1つ1つの業務に即した画面作成が可能になった。さらに他の業務メンバーがファイルを使っている間に発生していた待ち時間も削減できた。
・Accessに一元管理したデータを、RPAに連携して会計システムへの入力を自動化した。これにより、残業時間が1月あたり200時間減らすことに成功
事例②
<EC通販における会員のセグメンテーション>
会員の1年間の購買履歴とアンケートの集計結果を分析し、会員のセグメンテーションを実施。それまで一律にマーケティング施策を行っていたが、会員のセグメント毎の施策(例:セグメント別の割引率設定など)を実施できるようになった

実績
-商社・畜産部門向け商品管理システムの開発
➡エクセルとAccess及びRPAと連携処理
-客先マスタ登録業務用システム開発/エクセル・RPA連携
➡エクセルVBAにてRPA入力用データを成型
-EC通販会社での会員分析・マーケティング施策立案
➡エクセル分析ツールにて会員の購買履歴を統計処理にて分析
-EC通販会社向け在庫管理システム
➡適性発注率をエクセルを使用して分析
☆ストリートアカデミーで講師もしています

経歴
Excellent仕事術運営者:鶴岡 敦(ガッツ鶴岡)

新卒で総合商社に入社後、ユニクロに転職。更にEC通販会社に経営企画室長として入社。
EC通販会社にて膨大なデータ量に苦戦。必要に迫られて自らエクセル学習を重ねるうちに、 海外の活用例などを知るようになり、エクセルの可能性に目覚める
1993年慶應義塾大学商学部卒
2002年USCPA合格
大学在学中にプロボクサーとしてデビュー
現在もボクシングジムで練習を続けている

パワークエリ記事一覧
M関数逆引き辞典はこちらから!
初級編
タイトル | 内容 |
---|---|
Power Queryで操作はどう変わる?~初級講座1回目 | パワークエリとはなにか?そのメリットをスマホに例えて解説します |
シートからデータ取得~ 初級講座2回目~ | 別シートのデータをテーブル化して取得する方法を解説します |
ファイルからデータ取得 初級講座3回目 | 別ファイルのデータをテーブル化して取得する方法を解説します |
フォルダから複数ファイルデータを一括取得~初級講座4回目 | フォルダ内にある複数のファイルからデータをまとめて取得する方法を解説します |

中級編
タイトル | 内容 |
---|---|
複数データの組み合わせ/クエリのマージ~中級編1回目~ | クエリのマージ技術の概要について解説します |
クエリのマージ/項目追加~中級編2回目~ | 複数のクエリをマージする方法について、実際のデータで解説します |
クエリのマージ/計算処理~中級編3回目~ | マージした項目を活用して計算処理を行う方法を解説します |
クエリの追加作成~中級編4回目~ | 同じ項目のクエリを追加して結合する方法を解説します |
クエリの追加・結合~中級編5回目~ | クエリの追加をピボットテーブルと連携して行う方法を解説します |
ピボット解除/縦横並びの変換処理~中級編6回目 | 縦横の使いにくいデータを、使いやすい並びに変える方法を解説します |
セル分割他・変換処理~中級編7回目 | 複数のデータが1つのセルに入っている場合に分割する方法を解説します |
セル分割他・変換処理2~中級編8回目 | 前回の内容を使いやすい形式にして出力する方法を解説します |
エディター内のやり直し操作~中級編9回目~ | ワークシートにおけるCtrl+Zに相当する処理の方法を解説します |
変換した列の追加~中級編10回目~ | 連番、条件列(IF関数に相当)及び列削除・移動他を解説します |
グループ化1~中級編11回目~ | データを階層化して集計する方法を解説します |
複数条件でグループ化~中級編12回目~ | データを複数の条件で階層化する方法を解説します |
上級編
タイトル | 内容 |
---|---|
行削除とヘッダー行の調整~上級編1回目~ | 見出し行/ヘッダーがおかしい場合の対処方法を解説します |
セル結合により見出しが2行の表をデータ活用1~上級編2回目~ | セル結合された使いにくいデータを使いやすくする方法を解説します |
セル結合により見出しが2行の表をデータ活用2~上級編3回目~ | セル結合された使いにくいデータを使いやすくする方法を解説します |
各種集計/合計から四捨五入まで~上級編4回 | 四則演算の計算から四捨五入まで、様様な集計方法を解説します |
エラー修正_処理ステップエラー~上級編5回目 | ファイル保存先変更などに伴うエラーの対処方法について解説します |
途中のステップを削除した場合のエラー修正処理~上級編6回目 | 途中のステップを削除した場合のエラー対処処理について解説します |
エラー修正_データ自体のエラー~上級編7回目 | データ自体がエラーになっている場合の対処方法について解説します |
カスタム関数を自身で登録して使い回す~上級編8回目 | ユーザーが独自で作成するカスタム関数の作成方法を事解説します |
カスタム関数を自身で登録して使い回す2~上級編9回目 | 作成したカスタム関数を呼び出して活用する方法を解説します |
IF式を組み合わせて列作成~上級編10回目 | 条件付きカスタム列を作成する方法を解説します |
「例からの列」にて変換パターン自作~上級11回目~ | 入力した変換例を汲み取って変換を行う方法を解説します |
「 例からの列」による桁数が規則列への対応~上級12回目~ | 「例からの列」による変換方法の応用方法を解説します |
エディタを開かずにソース変更処理~上級13回 | 「パラメーター」機能を使用してデータソースを変更する方法を解説します |
シート上からデータソースを変更する方法~上級編14回目 | シート上の入力を変更するだけでデータソースを変更する処理を解説します |
セルの値を変更するだけで読み込みを変更する方法~上級編15回 | セルの変更内容をクエリに組み入れて、クエリの内容を変更できるようにする方法を解説します |
クエリのコピー・バックアップ・削除他~上級編16回 | クエリのコピーやバックアップ及び削除、その他グループ化などの方法を解説します |
文字列の抽出~上級編17回 | RIGHT関数やLEFT関数、MID関数に相当する内容に加え、特定の文字列を抽出する裏技を解説します |
文字列の追加、置換~上級編18回 | 前回の特定の文字列の抽出に加え、文字列の追加や置換方法について解説します |
エラー発生の予防/列のデータ形式変更と削除について~上級19回~ | エディタ内の操作のちょっとした工夫で、エラー発生を予防する方法を解説します |
M言語編
タイトル | 内容 | 使用するM関数 |
---|---|---|
M言語に慣れる_1回目~コード構造の把握~ | Mコードの基礎的なルール/文字の色、大文字の区別、ステップの参照について解説します | List.Average/平均値・計算 |
M言語に慣れる_2回目~カスタム列~ | カスタム列・作成画面でM関数を使用して新たな列を作成する方法を解説します | Text.From/文字列への変換 |
M言語に慣れる_3回目~ダイナミックフィルタリング~ | セルの変更内容をフィルタイングの内容に反映する方法を解説します | List.Max/最大値の計算 |
M言語に慣れる_4回目~特殊テンプレート作成~ | 2つのクエリを組み合わせて、1担当者に対して複数の勤務日がある表を作成する方法を解説します | – |
M言語に慣れる_5回目~M関数を検索する方法~ | ワークシート内の関数のように、M関数を参照する方法を解説します | Text.PositionOf/特定の文字列の位置抽出 |
M言語に慣れる_6回目~3種類の括弧~ | 丸括弧と角括弧そして波括弧とテーブル、リスト、レコードの関係を解説します | – |
M言語に慣れる_7回目~空のクエリからテーブル作成~ | 空のクエリから、リストやレコード、テーブルを作成する方法解説します | #table/テーブル作成、Table.RenameColumns/列名変更 |
M言語に慣れる_8回目~eachを使いこなしてM関数作成~ | 数式バーに良く出てくる「each」の用法について解説します | Table.SelectRows/テーブルから特定の行を抽出、List.Select/リストから特定の値を抽出 |
M言語に慣れる_9回目~複数シートをコード1行で結合~ | ファイル内のシートからデータをまとめるクエリを作成する方法を解説します | Excel.CurrentWorkbook/ワークブックの内容を一括抽出 |
M言語に慣れる_10回目~グループ毎に連番作成~ | 「グループ化の操作/全ての行」を活用してグループ毎に連番を作成する方法を解説します | Table.AddIndexColumn/連番作成 |
M言語に慣れる 11回目~前行を参照して計算~ | 連番と波括弧:{}を組み合わせて活用して前行を参照する方法を解説します | – |
M言語に慣れる~12回目例外処理 | エラーが発生した際の対処処理を行う方法を解説しています | – |
M言語に慣れる13回~ダイナミックに連続した日付作成 | ワークシート内にある連続性のない日付から、連続する日付を作成した後、他データも紐づける方法を解説します | #date/「年、月、日」から日付を作成、#duration/「日、時間、分、秒」から期間値を作成します、List.Dates/「開始日、リスト個数、増分」から日付のリストを作成、Number.From/値を数値型式にして返します |
M言語に慣れる14回目~複数行を1つのセルにまとめる | グループ毎のテーブルを作成後、グループ別に1つのセルにテーブルの内容をまとめる方法を解説します | – |
M言語に慣れる15回目~TEXT関数/文字列関数 | テキスト関数の事例について解説します | Text.Contains/文字列の有無を判定、Text.Replace/文字列の置換 |
M言語に慣れる16回目~List関数 | リスト関数の事例について解説します | List.Max/最大値の計算 |
不規則に散らばっているデータを一括取得~M関数~ | 見出し行/ヘッダーがそれぞれ違うシートの内容を一括でまとめる方法を解説します | List.PositionOf/リストの中から特定文字列のリスト内位置を抽出、Table.RemoveFirstN/テーブルの先頭から指定行数を削除します、Table.PromoteHeaders/先頭行を新しい列見出しにします |
参照先バッファー化によるパフォーマンス向上~M関数~ | クエリのパフォーマンスを向上させる方法を解説します | Table.Buffer/参照テーブルのバッファー化 |
クエリの列名をダイナミックに変更する~M関数使用~ | 入れ子のリストを使用して、ワークシート上の内容でクエリの列名を変更する方法を解説します | Table.RenameColumns/列名変更、Table.ToColumns/テーブルからリスト(入れ子)作成 |
Power Queryエディタ上で複数条件付き合計処理~M関数使用 | M関数を組み合わせて「SUMIFS関数」と同様の計算をエディタ上で行う方法を解説します | Table.SelectRows/条件に合う行のみを抽出、List.Sum/合計値を抽出 |
指定した範囲の値を使用してフィルタリング | M関数を利用して、ワークシート上の表にある値に一致するもののみをフィルタリングする方法を解説します | List.Contains/リストに指定の値が入っているかを判定 |
複数リストを1対1の関係でうまく組み合わせる | 1行に複数の値を持つリストを複数組合わせる場合に、リスト間で1対1の関係になるようにします(例:8月2日/月、火、水➡8月2日/月) | Table.FromColumns/複数のリストからテーブルを作成 |
列名をダイナミックにしたピボット解除を行う | 新たな列を追加した場合でも、並べ替える列とそうでない列をコントロールできるようにする | Table.ColumnNames/列名リストを作成 |
PDF変換の応用~フォルダに入れた複数PDFから都合良くデータを切り取る方法 | 複数のPDFファイルから都合よい箇所だけデータを一括抽出できるようにする | Table.RemoveFirstN/指定行から上の行を削除 List.PositionOf/リストの中から特定文字列のリスト内位置を抽出 |
【POWER QUERY裏技】フォルダにある複数ファイルから特定のシートのみを可変で取得する方法 | フォルダ内のファイルを一括で取得する際に、パラメーターを使用して取得するシートを可変にします | Excel.Workbook/Excel ブックの内容を返します。 |
M関数を使用して、可変で出現する特定文字の下の行を切り捨てる | 特定文字列の下の行を可変で削除します | List.PositionOf/リストの中から特定文字列のリスト内位置を抽出 |
置き換えのIF式を簡略化する方法~リスト活用、列削除省略~ | メンテナンスが面倒なIF式を簡略化する方法を解説します | List.Contains/リストに特定の値が含まれていればTRUEを、そうでなければFALSEを返します |
分析編
タイトル | 内容 |
---|---|
POWER QUERYで手軽にABC分析 | パワークエリのM関数をうまく組み合わせれば、ABC分析も行うことができます |
RFM分析の「R」を集計する | 注文データから注文者(会員) 別に最終注文データを集計できるようにします |
RFM分析の「F」を集計する | 注文データから注文者(会員) 別に注文頻度を集計できるようにします |
RFM分析の「M」を集計してまとめる | 注文データから注文者(会員)別に注文金額を集計できるようにします |
コラム
タイトル | 内容 |
---|---|
POWER QUERYをクラウドで活用する方法~POWER AUTOMATE使用~ | DataverseとPower Automateを組み合わせて、Power Queryをクラウドで活用します |
(続編)2つの表にてどの行が追加、削除されている? ⇒どの列が変更になっている? | ピボット解除と複合キーを活用して2つの表を比較しやすくします |
どの行が追加、削除されている?~結合の6種類~ | 結合の各種類をつかいこなせば、行の追加や行の削除の状況が一目でわかります |
【パワークエリ・チャレンジ】セル結合が行列にある表をテーブル形式に変換 | 行列双方向にセル結合がある表をテーブル形式に直します |
【パワークエリ・チャレンジ】行ではなく、列方向でフィルター・置き換えを行う方法 | 実は行列の入れ替えを行えば、列方向でのフィルター処理が可能です |
【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する | 余りの計算とピボット機能を組み合わせて1列のデータから複数列のテーブルデータを作成します |
【M言語実践】グループ別に累計を集計する方法 | グループ別に累計値を集計する方法を解説します |
【M言語実践】顧客の2回目のリピート率を分析する方法 | 通常のエクセル処理であれば相当煩雑な分析処理をM言語を活用して簡略化します |
M言語実践】グループ別にIDを設定 | グループ別に連番を採番した上で桁数を揃えてIDを作成します |
マスタデータの履歴管理を行う方法 | マスタデータが重複している場合に、日付が最新のもののみを表示する方法を解説します |
POWER QUERYのステップ数を減らす3つの工夫 | 「変更された型」のステップを減らすなどの工夫を解説します |
エディタからデータ入力してクエリ作成 | エディタ上で直接データを入力する方法を解説します |
【豆知識】どこの行が変わったかを調べる方法 | クエリのマージの仕方を工夫しつつ、条件列を組み合わせて、元データと修正があった箇所を特定する方法を解説します |
【豆知識】ピボット/縦縦並び⇒縦横に並び替え集計 | ピボット解除と別な方向で並べ替える方法を解説します |
【豆知識】ピポット解除時の空欄の扱い | グループ化 |
【分析作業用】大量データを1目で把握する | パワークエリの表示タブでは大量のデータを1目で分かるようにする為の工夫がありますので、そちらを解説します |
【分析作業用】グループ毎の平均と個の平均の差を自動集計する方法 | グループ化機能を2段階で行うことで、グループ単位の集計と個々の集計とを同時に行う方法を解説します |
クエリの複製、参照、そしてコピーとの違い~ | クエリを右クリックした時に出てくる「複製」「参照」「コピー」の違いについて解説します |
POWER QUERYって何?~エディタ上でのショートカットキー | エディタと他ファイルを並行して開く方法も含めて、操作を早く行う方法を解説します |
POWER QUERYって何??~時刻を曜日と日付に変換 | 時刻データを曜日や、日付データに変換する方法を解説します |
POWER QUERYって何??~経過時間を一括で計算する | ワークシート関数では、直接計算できない経過時間を簡単に計算する方法を解説しています |
POWER QUERYって何??~日付を月や四半期単位に変換~ | 日付データを簡単に月や四半期単位に変換する方法を解説します |
POWER QUERYって何??~読込んだデータに行番号追加~ | シートに読込んだクエリに行番号を追加する方法を解説します |
文字列を記号毎に分割し、行方向に展開する方法 | コンマなどの記号をもとにして文字列を分割しつつ、列方向ではなく、行方向に展開する方法を解説します |
POWER QUERYって何~既存クエリのステップを一括で削除する方法 | 適用してステップの欄で一気に複数ステップを整理する方法を解説します |
POWER QUERYって何?12~重複のないリストを一瞬で作成 | 一気に重複を排除したリストを作成する方法を解説します |
POWER QUERYって何??11~ユニークな数の集計~ | パワークエリならではの集計方法を1点だけ解説します |
POWER QUERYって何?~ピボット解除(縦横並び替え)と入れ替えの違い~ | 紛らわしい「ピボット解除」と「行列の入れ替え」の違いについて |
POWER QUERYって何??9~WEBページからデータ取得~ | Webページからエクセルデータを直接取得する方法を解説します |
POWER QUERYって何8???~PDFファイルをエクセルに変換~ | PDFファイルからエクセルデータを直接取得する方法を解説します |
POWER QUERYって何?~列の結合/複合キー活用~ | 複数の列を結合して複合キーを作成・活用する方法を解説します |
文字列から空白を取り除く_POWER QUERY | 文字列の前後や、文字列間に入り込んでいる空白を取り除く方法を解説します |
パワークエリエディターで行コピーを行う方法 | M関数を使用して行をコピーして複数にする方法を解説します |
MICROSOFT POWER QUERY~ACCESSとEXCEL連携~ | 特殊な方法でAccessと連携する方法を解説します |
POWER QUERYって???合計を計算する | グループ化による合計金額の計算方法を解説します |
POWER QUERYって何???連番作成 | 連番を作成する方法を解説します |
POWER QUERYって何??パワークエリの使用メリット | パワークエリを使うメリットの一つは履歴の管理が便利な点があげられます |
POWER QUERYって何???~ACCESSとエクセルの連携~ | 通常の方法にてAccessと連携する方法を解説します |
ピボット解除を複数列を軸にして実行する | 複数列を軸にして縦縦並びを縦横並びに変える方法を解説します |
IF 式の中でOR条件とAND条件を使う方法~POWER QUERY~ | ANDとORの指定を小文字で行うのがポイントです |
Works
Advance Technicを駆使したテンプレートを提供していきます
1.スピードメーターグラフ・テンプレート
使い方シートを参照しながら自由にグラフを作成できます

グラフデザイン
このコーナーでは、シンプルに数字の持つ意味合いを表現できるように、伝わるグラフデザインの形にこだわりつつ、”遊び心”を持ったグラフ作成方法を紹介していきます!
1.グラフを魅せるエクササイズ
例えば、積み上げ棒グラフの合計値を示すにはどうしたらよいでしょうか?
このままだと軸を目で追うしかありません
では、それぞれの棒グラフの上に合計値を表示したらどうでしょう?

とても分かり易くなりましたよね!
このコーナーでは、日常的にはなかなか気づくことができない”グラフの見やすさを向上させる手法”を紹介しています。⇒グラフを魅せるエクササイズ
2.スピードメーターグラフ
数字の持つ臨場感を伝えるグラフです
作成する過程で工作のような楽しさも味わうことができます。

3.ワッフルチャート
グラフ機能を使わないグラフです。

4.弾丸チャート
複数の指標を比較する時にお勧めです

5.進化型円グラフ
普通の円グラフにグラデーション加えて数字の持つインパクトを増幅します

IT未経験からDX人材に!
【そのITへ取り組み、まずは身近なエクセル業務の改善、VBA、そしてRPAからははじめてみませんか?】
技術の進化により、実務の現場での「ITの力」の存在感は日々増す一方です
このブログの読者の方の中には、こんな悩みを持つ方もいらっしゃると思います
・会社からDX人材になるように求められているが、どうしたらいいのか分からない
・IT部門やIT関連の打ち合わせが多いが、話しについていけない
・AIやRPAなどによる業務削減が進んでいて、近いうちに自分の仕事もなくなる恐怖がある
・システムエンジニアにキャリアチェンジしたいが、プログラミング知識・経験が全くない
・仕事が忙しく、VBAなどで事務処理を効率化する必要があるが始め方が全く分からない
私は45歳から全く未経験のシステムエンジニアの世界に入りました
もちろんプログラミングの経験などは全くありませんでした
6年のシステムエンジニアとして過ごす中で、後半の3年は「豊富な業務知識と経験」と研修抜きのまま、走りながら身に付けた「RPAやVBAの技術」を活かして「現場のブラックボックス業務」の解消を行うプロジェクトをこなしました
6年のシステムエンジニアとして過ごして感じたことは、ITの世界には「抜け道」があることです
・ノーコードの技術に代表されるように、専門知識があまり必要ない技術が出現している
・ITの分野は細分化されており、どんどん新たな分野が出現している
・人の仕事を代替するRPAの開発などでは、プログラミング技術より業務経験が活きるケースがある
未経験だから・年齢が若くないから・といった理由で、ITの世界では全く通用しないということはありません
逆に未経験だからこそ、若くないからこそ「今から」ITの世界を知る必要があります
何故なら、今の世の中ではITの力は決して欠かせないものだからです
ぜひ、このブログで「ITへの取り組み」を本気ではじめるきっかけになれば幸いです
このブログを活用する上で、1点だけお願いがあります
このブログで記事を読んだら、ぜひ実際に手を動かしてみてください
このブログで紹介する内容は、無料で環境構築の必要がないものです
そして、手を動かす中でITの楽しさを体感して頂きたいと思います!
エクセル業務の改善
まずエクセル業務の改善をするのが何故、ITと関係あるのかを解説したいと思います
ITとは「Information Technology」の略です
日本語に訳すと「情報技術」です
そして、情報の基礎となるのがデータあり、データを扱う典型的な業務がエクセル業務です
エクセル業務はビジネスでつきものです

このエクセル業務を削減すれば、日常業務をかなり削減できるだけでなく、ITに取り組む上で重要な「データ管理」について学ぶことができます
このデータ管理を本格的に行う仕組みがデータベースです
データベースを作成すれば、ファイルやシート間の転記を行ったりする必要はなく、1か所でデータを管理できます
更にデータを様々な方で検索したり、利用することができます
エクセル業務を効率化しようと思ったら、このデータベースの構築が1つの柱になるはずです
本来、Microsoft社にはAccessというデータベースソフトがエクセルとは別にあります
ところが、エクセルは進化を続けており、今ではエクセルもデータベースのように活用できるようになっています
ぜひエクセルをデータベースとして活用してITの見識を深めつつ、業務の削減にお役立ててみてください
Power Query
Power Queryに代表されるモダンエクセルは、エクセルを使いながらAccessなどのデータベースの技術を活用できる画期的な技術です
コードを一から書くなどの小難しい技術は必要なく、スマホ感覚で活用できます!
Power Queryを始める前にまずはテーブル機能に慣れて頂くと、Power Queryをより理解し易くなります
Power Queryは従来のエクセルの使い方の様にファイルやシート単位でデータを扱うのではなく、テーブル単位でデータを扱うのが特徴になっています
⇒コラム:PDFファイルをエクセルに変換するなどのテクニックの紹介
⇒M言語
VBA
プログラミングをはじめるなら、エクセルVBAからはじめるのが一番てっとり早いです
環境構築の必要はほとんどなく、身近なエクセルを動かせるのでプログラミングとは何かを理解し易いと思います
このブログでは楽しくプログラミングが学べるように、VBAで作成したゲームを提供しています
このゲームでは登場人物の動きに合わせてコードも表示されますので、「英語映画の字幕学習」のような感覚で自然とVBAを学べるようにする工夫も行っています
ぜひ一度、ダウンロードして試してみてください
⇒VBA
VBAを学んでいくと、1日要していた仕事を一瞬で処理できるようなマクロを作成することができるようになります
そしてVBAを突き詰めていくと、単に業務削減が行えるだけでなく、新たなキャリアを作る機会も出てきます

・社内で業務改善やDXを推進する部署への移動
・他のプログラミング言語も学んでシステムエンジニアとして転職
・スキルシェアサービスなどで副業
後述するRPAなどでは、RPAと組み合わせてVBAを使う機会も多いです
これはRPAの対象業務や周辺業務がエクセル業務であるケースが多いからです
ですので、VBAを覚えるとRPA関連の仕事や副業を行える機会も出てきます
Excel VBA超入門第1弾エクセルが自動で仕事する!マクロの魔法 文系・非IT職もできるプログラミング
エクセルその他
⇒裏技
RPA
RPA(ロボティクス・プロセス・オートメーション)は人間の代わりに作業を行ってくれる技術で、今後、市場が継続的に拡大していくことが期待されています
RPAはノーコードと言われているように、あまり小難しい技術を必要としませんので、ITが未経験の方でも取り組みやすい分野です
RPAの作成を一言で表現すると、ブロックの積上げです
1つのブロックにはそれぞれ1つの命令が含まれており、ブロックを積み上げるとフローチャートができあがります

ですので、RPAの作成を複数回行って行けば自然とプログラミングの力、的確な業務フローを作る力が身に付いていくというメリットもあります
RPAの案件は実務に直結した小さな案件も多く、プログラミング技術自体よりもIT以外での業務経験が活きるケースがあります

RPA関連のサービスも様々なものが登場しており、これまでITと縁が無かった方でもRPAを学ぶ事で、隙間時間に副業を行うなどの新たなキャリアも描きやすくなってきております
今ではMicrosoft社のPower Automate Desktop(以降PAD)などのRPAが無料で簡単に使えます
RPAは何かを実際に動かして試してみたい方は、ぜひ実際にPADをインストールして手を動かして試してみて欲しいと思います
⇒無料RPA・Power Automate Desktop逆引き辞典
⇒無料RPA・Power Automate Desktop入門

☆★オススメIT学習方法~動画活用~☆★
IT学習のコツは、楽しく手を動かすことです
但し、時間や予算は無限ではありません
限られたリソースの中で、どのような学習方法を構築していくかはとても重要なことです
今回は動画を活用した、効果的な学習方法を紹介します ⇒
☆★オススメIT学習方法~基本情報技術者試験~☆★
基本情報技術者試験はITの登竜門です
この試験に取り組むことで、ITのフィールドで幅広く活用する機会が広がります ⇒記事


データ取得~POWER BIでマイダッシュボード作成・初級_2/8~
【静から動へ!Power BIを活用して簡単・手軽にデータを分析可視化できるダッシュボードを作成しよう!】
前回、Power BIの魅力を紹介するのに使用したレポートを作成する方法を今回からシリーズで解説していきます

今回はPower BIの入り口である、データ取得について解説します
Power BIにとってデータはガソリンみたいなものです
ガソリンがなくてはPower BIは走れません
Power BIは全て「データ取得」というガソリン補給から始まります
インストール
データ取得の前に「Power BI Desktop」をインストールしておきましょう
クラウドのPower BIサービスとは別なので注意しておきましょう
インストーラーのダウンロードはこちらからです↓
使用データ
使用データは1つのエクセル・ファイルです
エクセル・ファイルは3つのシートに分かれています
1つ目のシートは「売上データ」です

データの中味は次の4つです
a:日付、b:客先、c:商品、d:売上金額
2つ目のシートは「客先」です

e:客先、f:都道府県
最後のシートは「地域マスタ」です

g:都道府県、h:地域ブロック
3つのシートのデータ項目(a~h)は共通の項目があるので、次の図のように階層化できるようになっています

データの取得
インストールしたPower BI Desktopを開くと、データ取得の画面が出てきます

こちらをクリックすると、取得するデータの種類を選択する場面が出てきます

こちらで「Excel」を指定し、接続をクリックします

次に開いた画面で取得するファイルを選択します

その後は、取得するシートを指定します
今回の場合は、3つのシートを全て「チェックを入れて」指定します

その後は、ナビゲーター画面の下から「読み込み」ボタンを指定してください

<まとめ>
今回はPower BIの初動であるデータの取得について解説しました
データを取得されたら「少し遅いな」と感じると思います
これはPower BIではデータを取得したり更新したりする場合には、様々な処理が裏で走るからです
次回からその裏の処理も交えながら解説を行っていきたいと思います