タグ別アーカイブ: 分析

分布図の作成及びグループ化~データを見やすく分解する

ピボットテーブルグループ化機能を使うと、数字が見やすくなるため重宝しておりますが、Power BIでも同じようなことが行えます

今回は、Power BIにおけるグループ化機能を量/ヒストグラムと質に分けて解説します

分布の把握は数字分析の基本なので、分析を行う必要のある方には特に有用な情報だと思います

量によるグループ化

今回は下の画像のデータからヒストグラムを作成したいと思います

売上金額の範囲区分を一定の金額で設定し、レコード数(行数)747件の件数を範囲区分別に縦棒グラフで表示します

まず、レポート画面のフィールド欄で「売上金額」上で右クリックします

すると「新しいグループ」という表示が見えますので、こちらをクリックします

「新しいグループ」をクリックした後は、次の画像の画面が開きます

ピンのタイプはデフォルトで「ピンのサイズ」になっていますが、こちらは「ピンの数」に変更します

ここで「ピンの数」とは縦棒の数になります

OKボタンを押すと、新たなフィールドができます

こちらをX軸に配置し、Y軸に売上金額のカウントを配置します

すると、縦棒グラフがヒストグラムとなります

ヒストグラムのデータ区分の範囲はピンのサイズとなります

ちなみにY軸を合計に変えるとデータ範囲別に合計金額を表示することができます

質によるグループ化

こちらのグループ化はレポート管理画面の一つ下のデータ管理画面で行います

下の画像がグループ化を行うデータです

まず、グループ化の対象になる「都道府県」の列を選択します

すると上のタブが「列ツール」に切り替わり、「データグループ」が表示されます

ここで「データグループ」の▼マークをクリックすると次の画面が表示されます

「新しいデータグループ」をクリックすると表示が次のように切り替わります

ここでグループ化するものは、Ctrlキーを押しながら選択し、グループ化をクリックします

この選択、グループ化の処理によりグループ化が実施されます

仮に、この状態のまま画面右下のOKボタンを次のような列ができます

ですので、列名およびグループ名はOKボタンを押す前に修正しておきます

<まとめ>

今回は量と質の双方の観点からグループ化を行う方法を解説しました

Power BIではグループ化により、分析可視化だけでなく、分析作業も行えます

ヒストグラムについては、X軸の範囲が良く見えない点について不便に感じる方もいらっしゃると思います

その場合には、「その他のビジュアルの取得」から「histogramのアプリ」を探す方法もあります

ヒストグラム/histogramは複数種類がありますので、ぜひ試してみてください

上の画像の「Histogram Chart」ならばX軸も下の画像のように明確に表示されます

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

散布図&近似曲線&相関係数の表示

散布図は数あるグラフの中でも相関関係を見るのに一番適しています

そして、散布図がダッシュボード内にあれば数字の深い洞察が行えるようになります

今回は散布図の作り方だけでなく、相関関係を解釈し易いように「近似曲線を追加」する方法と相関係数の計算方法を解説します

散布図

散布図はX軸とY軸の2つの軸から構成されます

上の画像では、X軸に身長、Y軸に体重を配置しています

ここで、散布図ならではの注意点があります

X軸とY軸ともに数字は「集計しない」を選択しておきましょう

近似曲線の追加

近似曲線の追加は下の画像の黄色く印をつけた箇所から行います

近似曲線はPower BIでは「傾向線」という名前になっています

上の画像の「追加」をクリックすれば、近似曲線が散布図に追加されます

線の種類や色を調整したい時には下の画像の箇所にて調整します

相関係数

相関係数はクイックメジャーで計算し、「カード」で表示します

まず「新しいメジャー」の右横のマークをクリックします

クイックメジャーの画面が開いたら計算の種類を選択します

今回は「相関係数」を選択します

計算の種類を選択したら、上の散布図のX軸とY軸と同様の設定を行います

クイックメジャーの設定後は、新しいフィールドが設定されます

<まとめ>

散布図は数字の背後にある相関関係を明らかにするのに有効なグラフです

ダッシュボード内の仲間に散布図を加えることで、数字の解像度がグッとあがります!

そして、近似曲線を追加することで散布図の背後にある相関関係の解釈を行い易くなります

ぜひ、深く数字を分析したい時にはこの散布図と近似曲線を有効活用してください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

お仕事の依頼

 実務を担う現場の方が使い慣れたエクセル。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合格

大学在学中にプロボクサーとしてデビュー

現在もボクシングジムで練習を続けている

 

 

IT未経験からDX人材に!

【そのITへ取り組み、まずは身近なエクセル業務の改善、VBA、そしてRPAからははじめてみませんか?】

技術の進化により、実務の現場での「ITの力」の存在感は日々増す一方です

このブログの読者の方の中には、こんな悩みを持つ方もいらっしゃると思います

・会社からDX人材になるように求められているが、どうしたらいいのか分からない

IT部門やIT関連の打ち合わせが多いが、話しについていけない

AIRPAなどによる業務削減が進んでいて、近いうちに自分の仕事もなくなる恐怖がある

・システムエンジニアにキャリアチェンジしたいが、プログラミング知識・経験が全くない

仕事が忙しく、VBAなどで事務処理を効率化する必要があるが始め方が全く分からない

私は45歳から全く未経験のシステムエンジニアの世界に入りました

もちろんプログラミングの経験などは全くありませんでした

 6年のシステムエンジニアとして過ごす中で、後半の3年は「豊富な業務知識と経験」と研修抜きのまま、走りながら身に付けた「RPAVBAの技術」を活かして「現場のブラックボックス業務」の解消を行うプロジェクトをこなしました

 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で作成したゲームを提供しています

https://youtu.be/2MoC-zCCN8k

 このゲームでは登場人物の動きに合わせてコードも表示されますので、「英語映画の字幕学習」のような感覚で自然と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を学ぶ事で、隙間時間に副業を行うなどの新たなキャリアも描きやすくなってきております

RPA HACK フリーランス

 今ではMicrosoft社のPower Automate Desktop(以降PAD)などのRPAが無料で簡単に使えます

 RPAは何かを実際に動かして試してみたい方は、ぜひ実際にPADをインストールして手を動かして試してみて欲しいと思います

無料RPA・Power Automate Desktop逆引き辞典

無料RPA・Power Automate Desktop入門

☆★オススメIT学習方法~動画活用~☆★

IT学習のコツは、楽しく手を動かすことです

但し、時間や予算は無限ではありません

限られたリソースの中で、どのような学習方法を構築していくかはとても重要なことです

今回は動画を活用した、効果的な学習方法を紹介します ⇒記事

☆★オススメIT学習方法~基本情報技術者試験~☆★

基本情報技術者試験はITの登竜門です

この試験に取り組むことで、ITのフィールドで幅広く活用する機会が広がります ⇒記事

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村  

【M言語実践】顧客の2回目のリピート率を分析する方法

 パワークエリはとても便利ですが、M言語となると使い道がよく分からないと思います

今回は、M言語を使用した実践的な分析手法を紹介したいと思います

私は以前、EC通販会社に勤めていました

 EC通販では顧客の顔が見えないので、データからどういう顧客がいて・どういう行動をしているのか・を分析する必要があります

様々な分析を行いましたが、指標として一番重視していたのが「顧客の2回目リピート率」です

顧客の2回目のリピート率が何故重要なのか?

 これには様々な理由がありますが、一番の理由は2回目のリピート率を少しでも改善すれば、売上が長期的に増加するからです

読者の方でも外食をした際に「あの店には2度と行かない・・・」という経験をした方も多いと思います

 逆に同じ店で2度食事をして、その店に慣れてくると3回目、4回目とリピートする意欲する気が高くなると思います

 ビジネスでも「2回目のリピート」というハードルをクリアすることの意義はとても大きいのです

ただし、普通にエクセルで2回目のリピート率を算出しようとするとかなり面倒です

私は一時期、IF関数で注文回数を付与して、2回目の注文だけシートを分けたりしていました

ましてや、2回目のリピートの有無別に顧客分析をしようとするとかなりハードルが高いです 

ところが、M言語を使えば2回目のリピート率は意外と簡単に算出できます!

ポイント

今回使用するデータは会員別の注文データです

 上の画像では2回目の注文があった会員に黄色の印を付けましたが、2回目の注文があった会員もいれば、そうでない会員もいます

ここからまず、会員番号毎に注文回数を付与します

そして、上の画像の表から次のような表を作成します

上の画像では、その前の画像の表から注文回数を「1回」に絞り込んであります

結果として、会員番号の列は会員番号が重複なく並んでいます

そして「2回目判定用」なる列が追加されています

こちらは注文回数の差分です

こちらの差分は注文回数を「1回」に絞り込んだ場合にはになります

この列が1の会員は2回目の注文があった会員です

 つまり、「2回目判定用」の列の合計を「注文回数」の列の合計で割れば2回目のリピート率が出るという仕掛けになります

(注)尚、上記の画像のデータ以前に注文履歴は無いという前提で解説を行わせて頂きます。ですので、本当は初回の注文ではないのでは?という疑問は持つ必要はありません

注文回数の付与

ポイントで解説した通り、まずは会員番号別に注文回数を付与します

こちらについての詳細については、過去の記事をご確認をお願いします

大きく分けて2つ行うことがあります

1つ目は、グループ化です

会員ID別(会員番号別)に「すべての行」でグループ化を行い、会員ID別にテーブルを作成します

次にカスタム列・作成画面にて、テーブル別にM関数/Table.AddIndexColumnを使用して連番を付与します

すると、注文回数が連番で付与されます

注文回数の差分の算出

こちらについても詳細は過去の記事をご参照願います

この差分を算出するには、パワークエリでは本来は困難な「セル単位」や「行単位」の処理に踏み込む必要があります

ポイントとなるのは、こちらも「連番」です

上の画像のように連番をつけると、連番と画面左の行番号とが対応するようになります

ちなみに、M言語は0ベースなので、実際にM言語で使用する1行目は0になります

ここから波括弧:{}を使用します

波括弧は行番号を表します

例えば、注文回数{0}とした場合には、注文回数列の1行目のデータなります

この波括弧と連番をうまく組み合わせることで、注文回数の差分を算出します

上の画像にて黄色の印を付けた波括弧内は、注文回数の各行の1つ下の行番号の値になります([注文回数]の前のDataは前ステップの名称になります)

つまり上の画像内では、注文回数列をA列だとすると<=A3-A2>と同様の計算が行われています

ちなみに、try~otherwiseとすることでエラーを回避しています

注文回数の絞り込み

 上記まで行ったところで、エディタを確認すると以下の画像のような状態になっています

ここから注文回数の列にフィルターをかけます

こうすれば注文回数の列は「1」のみ、2回目判定用の列は0か1になります

ここまでくれば、後は各列を合計して2回目のリピート率を算出するだけです

合計にはM関数/List.Sum関数を使用します

 上の画像では、2つの合計値を/で割り算をすることでリピート率を算出しています

%の表示にしたい場合には、List.Sum関数でリスト化した内容をテーブル化した上で%に型式を変えます

<まとめ>

今回はM言語の仕組みを使用して、2回目のリピート率を算出しました

2回目のリピート率を算出するのに、主に2つのM言語の技術を活用しました

1つ目はグループ化を通じて各グループ毎に連番を付与する仕組みです

2つ目は注文回数の差分を連番と波括弧を組み合わせて算出する仕組みです

 この2つの技術を通じて、注文回数と2回目の注文有無を0か1で表現できるようにしました

 今回は2回目のリピート率を算出しましたが、本当に大事なのは2回目のリピートがあった会員とそうでない会員との違いは何かを調べることです

 今回作成したクエリを複製して途中のステップを削除すれば、会員番号別に注文の有無が表示できます

 他のデータを会員別に紐づければ、2回目の注文があった会員とそうでない会員の購入している商品の違いなども調べることができるはずです

 更に注文回数の差分を抽出する仕組みを応用して、初回から2回目までの日数なども調べることができます

M言語が本当に有効なのはこういった複雑な分析が必要な場面だと思います

 特にグループ化から連番を付与する仕組みと差分を算出する仕組みは、分析作業にて使う場面も多いと思いますので、しっかり活用できるようにしておきましょう!


にほんブログ村

7月17日勉強会_報告

先週の7月17日にモダンエクセルの勉強会を行いました

多くの皆さんにご出席頂き、誠にありがとうございました

①主催者スピーチ:M言語と分析術について

M言語を活用した操作にて、顧客の2回目のリピート率を簡単に集計する方法を解説しました

アイコン

0717勉強会資料 839.14 KB 13 downloads

...

②パワークエリ・高速化術

今回は先日、Akira Takaoさんからご紹介いただいたTable.Addkeyを簡単に解説しました

③質疑応答

 勉強会後、参加者の方から「パワークエリやM言語は使いこなせば便利なのはわかっているけど、皆さん試行錯誤されているのですね」という声を頂きました

やはりモダンエクセルはまだ歴史が浅いので、操作方法等ががまだ浸透していないところがあります

 例に挙げれば、今回も話題に出た「パワークエリとxls型式が相性が悪いため、xlsの型式のファイルをxlsx形式に保存し直す必要性」等です

勉強会の意義は、そんな実際に活用してみての気づきが共有できる点だと思います

今後も継続して開催していきます!


にほんブログ村

モダンエクセル・勉強会_0717

7月10日(土)に引き続き「モダンエクセルを実際に使いこなしている方々」にお集まり頂き、「モダンエクセル活用の場を広げる」ための意見交換をしたいと思います!

・主催:Excellent仕事術/当ブログ管理人

・日時:2021年7月17日19時半~20時半(今回は60分です)

・場所:オンライン(ZOOM)

・内容:①主催者発表/約20分、②意見交換及び質疑応答

①主催者発表:パワークエリ/M言語を使用した分析術について

・費用:参加費用は無料です

・申込:以下参照

-記事下のフォームに記入して頂いた後に、ZOOMのURLを送ります

*すぐにURLを送れない事もありますので、その点はご容赦ください

– 先着20名まで

-参加者はモダンエクセルを使いこなしている方に限定させて頂きます

-勉強会当日は、基本的にマイクはオフにして頂きます(オンにする際は、こちらから指示します)

*録画はご遠慮願います、後、途中の入退出もご遠慮ください(やむを得ない事情が発生した場合には主催者にご連絡ください)

*基本は顔出しでお願いします

-頂いた個人情報について、今回開催する勉強会の連絡等の目的には使用せず、厳重に管理致します

参加申し込みの程、お待ちしております

尚、勉強会に関する問い合わせはこちらからお願いします


にほんブログ村

【分析】Power Queryで手軽にABC分析

 

 ABC分析は「重点分析」とも言われ、分析手法の中でも最も基本的なものです。ところが、実際にエクセルでABC分析用の表作成を行おうとすると「データのバケツリレー」の手間を要します。データのバケツリレーは「列から列」へと続き、数式を細かく設定する手間もかかります。今回紹介する方法であれば、Power Queryエディタ画面(以降、エディタ)でシンプルに処理していけます!

以下が、通常のABC分析の表を作成するときの手順です

①重点管理する項目(金額など)を降順で並び替え

②①を累計で表示 ➡「=」から始まる数式を入力

③②を比率に変換➡ 事前に累計の値を算出 ➡累計の値で各行を割り算

④③をランク付け➡IF式を入力

もし、データの追加などがあったら、①~④の修正作業が必要です

もちろん、データ自体が変われば、①~④の更新処理が必要です

Power Queryであれば、一度、クエリを作成してしまえばデータ更新時に「ソース変更」「クエリの更新」をクリック処理をするだけで済みます

今回使用するデータと行いたいこと

使用データ

今回は、下の画像にある2つの列から構成されるデータを使用します

行いたいこと

前述の使用データの各行に、次の内容でランクを付けます

A ➡全体・販売個数の50%以下

B ➡ 全体・販売個数の80%以下

C ➡ 全体・販売個数の80%超

今回のポイント

今回使用する主な技術は、主に以下の1~3の内容です

1と2は過去に紹介した内容です

最後の3.List関数が今回の一番のポイントになります

1.並び替え/降順

まずは元データを降順で並び替えます

2.条件列の追加

「列の追加タブ」の「条件列」メニューでランク付けを行います

3.List関数

このList関数で累計値と比率を計算します

List関数は2種類使用します

まず1つ目は、List.Select関数です

リストから条件に沿う値を抽出し、リストを再作成します

書き方は次の通りです

List.Select(リスト,条件)

上の画像では、1~4の値からなるリストから、2超の値を抽出した上でリストを再作成しています

2つ目は、List.Sum関数です

こちらの関数は、直感的にも分かり易いと思います

List.Sum()の丸括弧の中に入れたリスト内の各値を合計します

但し、丸括弧の中に入るのはあくまでリストであり、列ではないことに注意しましょう!

では、今回のポイントを紹介したところで本格的な解説を始めます

尚、解説はエディタ内のみの内容になります

並び替え

まずは、エディタ内で販売個数を降順で並び替えます

累計値

算出ロジック

List関数を組み合わせて累計値を計算するのが、今回の記事の最大ポイントです

累計値を計算するロジックについては、次の画像を基にして解説します

通常のABC分析では、N行目のとN-1行目の累計値の合計を計算します

今回の記事では、上の画像のようにⅰ)N行目の値以上の値のリストを作成、ⅱ)ⅰのリストを合計、というⅰ)⇒ⅱ)の流れで行います

List.Select関数

まず、カスタム列・作成画面で「販売個数」列を挿入してA列を作成してみます

この処理だと下の画像のように、販売個数と同じ列ができるだけです

では、カスタム列・作成画面で前ステップ名(カスタム列を作成するステップの1つ前)を入れてみましょう

この段階では、前述の「降順に並び替えられたステップ」が前ステップです

下の画像が、前ステップ名を入れたカスタム列・作成画面の画像です

上の画像の内容でカスタム列を作成すると、次の画像のように各行にてリストが作成されます

このリストの中味は、一律に販売個数の列の内容です

この各行のリストから、前述のロジックの通り「各行の値以上」のリストを各行に再作成していきます

こちらのリストの再作成は、List.Select関数で行います

条件式は、一部、過去の記事で解説したカスタム関数の内容を使います

上の画像のように、「(x)=>」にてxを変数として宣言し、「x>=」を条件式とします

この内容でカスタム列を作成すると、行毎に作成されるリストの内容が変わっています

例えば、上の画像のように2番目の行であれば。2つの値しかリストの中にありません

では、前述の紹介したロジックの通りにリストが再作成できたので、List.Sumの解説に移ります

List.Sum関数

では、List.Select関数で作成したリストをList.Sum関数で合計し、累計を作成します

上の画像のようにカスタム列・作成画面に数式をセットしてOKボタンを押すと、累計の列がエディタ内に追加されます

比率

では、累計を計算したので「比率 =各行の累計値÷販売個数の合計値」 を計算します

販売個数の合計値の算出には、前述のList.Sum関数を使います

ちなみに、上の画像の「追加されたカスタム」とは前ステップ名です

ランク付け / 条件列

では、最後にランク付けを行います

ランク付けは「列のの追加タブ」にある「条件列」で行います

記事の冒頭にあったように、ランク付けの条件を「条件列の追加」画面に設定します

比率が0.5以下であれば ➡ A

比率が0.8以下であれば ➡ B

とします

上記の条件以外はCとして出力するようにします

この条件列を作成すれば、ABC用の表作成は完成です

<まとめ>

今回は、Power QueryでABC分析の表を作成しました

一番のポイントは、List関数を2つ組み合わせて「累計」の列を作成する点です

List関数の中には、常に「リスト」を指定します

M言語では、「リスト」と「」は明確に違います

今回の内容では、List関数の丸括弧の中は「ステップ名[列名]」で指定しました

この「ステップ名」を指定する場合があることを強く意識しておけば、後はそれ程難しい点はありません

 私は以前、企画の仕事をしている時にABC分析表を毎月作成しておりましたが、面倒で仕方ありませんでした

 今回、紹介したPower Queryの仕組みであれば、一度クエリを作成してしまえば、毎月ほぼ「ソース変更」「クエリ更新」をクリック処理で行うだけで済みます

分析で一番大事なのは、数字を解釈することです

 今回紹介した仕組みで、表作成を効率化して有意義な分析を行えるようにしていきましょう!

次回からはRFM分析の解説をはじめます!

https://analytic-vba.com/power-query/analysis/recency-group/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

【分析】RFM分析の「R」を集計する

RFM分析は顧客を次の3つの指標で分類して、顧客別に施策を講じる手法です

Recency ➡ いつ?、Frequency ➡ 頻度?、Monetary ➡ いくら?

今回は、Recencyを顧客別に集計する方法を解説させて頂きます

 このRecencyとは、会員IDが含まれる注文データ内に出現する「顧客ID別の最終注文日」のことです

 まず何故、このRecency/最終注文日を分析するかという話しをしたいと思います

 例として最終注文日が1日前の顧客グループと、1年前の顧客グループを比較して考えて見ましょう

 最終注文日が1日前の顧客グループであれば、集計日段階では「稼働」の状態であり、こちらからアクションをしなくても再度リピートしてもらえる可能性が高いです

 一方、1年前の顧客グループに対しては「休眠」している可能性が高く、何らかのアクションをしないと、再度リピートしてもらえる可能性が低いです

 このように、顧客の最終日がいつか?によりアクションをすべき内容が違ってくるのです

今回はこの「最終注文日」をPower Queryでサクッと集計する方法を解説します

 こちらの「最終注文日」を集計する処理は、ピボットテーブルでも集計は可能ですが、Power Queryで行うと大きなメリットがあります

 Power Queryでは集計内容をテーブルに直接読込を行うことができるため、直接データを編集することができます

 一方、ピボットテーブルだとシートの別な場所にコピーしないとデータの編集が行えないのです

今回の使用データと行いたい事

今回の解説で使用するデータは、次の画像の注文データです

注文データは、注文日が2021年1月から3月までの期間で集計されています

 ですので、注文データ内では1つの顧客IDに対して、上の画像のように複数の注文日が存在したりしています

 これらの「1顧客IDに対して複数存在する注文日」の中から「最終注文日」を顧客IDごとに集計することが今回行いたい事です

 例えば、上の画像であれば「顧客ID / CO6324」の最終注文日「2021/03/12」を集計します

最終注文日の集計

では、実際に使用データから最終注文日を顧客ID毎に集計します

解説は、エディタ上から始めます

画像に alt 属性が指定されていません。ファイル名: エディタ-2.jpg

 こちらの集計処理は、過去の記事でも紹介したグループ化により一瞬で終了します

まずは「ホーム」タブの「グループ化」をクリックします

するとグループ化・画面が開くので、次の画像のように各項目を設定します

グループ化項目:顧客IDの列

新しい列名:最終日

操作:最大

列:注文日

この上記の設定により、顧客ID別に最大/最終の注文日が集計されます

 例えば、3つ注文履歴がある顧客ID:C06234であれば下のGIF画像のように「2021/03/12」のみが集計されます

<まとめ>

 今回は、グループ化機能により注文データから顧客ID毎に最終注文日を集計しました

 グループ化・画面にて集計方法(操作)を「最大」で指定すれば、瞬時に最終注文日を集計できます

尚、「最終注文日」を集計した後は「最終注文日からの経過日数」を集計します

こちらについては、起算日を指定する必要があると思います

起算日の指定方法は主に2つあります

本日の日付を指定するM関数を使用する ➡ DateTime.LocalNow

・カスタム列・作成画面で日付を指定する ➡ #date(年,月,日)

今回は、#dateで日付を指定してみます

 上のGIF画像ではカスタム列・作成画面にて[最終注文日]から2021年5月14日の日付を差し引いています

 ちなみに、新たに作成された列は小数点が付いているので、後で整数に直す必要があります

  

 顧客ID別に「最終注文日からの日数」を集計し、過去の記事で紹介したABC分析の内容と顧客IDと紐づければ、顧客ランクとRecencyの関係性を分析することができます!

ぜひ実践してみてください!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

XxXLookup関数で集計上手になる!

読者の皆さんがこれまで使用したエクセル関数の中で、一番使用頻度が高かったのはどの関数でしょうか?

多くの皆さんはVLOOKUP関数と答えるのではないでしょうか?

ところが、XLOOKUP関数の登場により、関数の使用シーンがこれから大きく変わっていきそうです

まだ使用できるエクセルバージョンは少ないかもしれませんが、それだけXLOOKUP関数の登場はインパクトがあります

特にXLOOKUP関数と他の関数を組み合わせることにより、データ集計の効率を格段に向上させることができそうです

今回は、XLOOKUP関数同士を組み合わせた例を2つ紹介します

どちらの例も、表を2次元で処理できるので、利用シーンが多そうです

①マトリクス表から2つの条件に合致するものを抽出する

条件を2つ(条件①部門x条件➁四半期間)をリストで指定し、2つの条件に一致する値を抽出する

➁マトリクス表でランク付けをする

事業の売上額と成長率、在庫商品の在庫額と回転率、などなど二次元でランク付けすることはよくあります

ところが、これまでは単に関数を使用するだけでは、ランク付けは自動ではできませんでした

尚、これからの解説は既にXLOOKUP関数を使用したことがある人を主として想定しています

但し、そうでない方もサンプルファイルをダウンロードすることで、そのままサンプルファイルの使用例を他の目的に利用していくことも想定しています!

1.そもそもXLOOKUP関数とは?

2つの事例を紹介する前に少しXLOOKUP関数について簡単におさらいをしておきましょう

サンプルファイルでは1シート目の内容です

サンプルの内容は部門毎に対応する部門長を別表から抽出するものです

XLOOKUP関数も、VLOOKUP関数と同じように検索値を基にして表からデータを抽出します(事例では北海道を検索値として、部門長の木村を抽出)

但し、VLOOKUP関数で行っていたような

「○○から右に何番目」

といった列数の指定が無く、検索する範囲(サンプルの部門)、戻り範囲(部門長)を指定しするだけです

つまり、 「○○から右に何番目」 の縛りがなく、検索値が参照表の左に無くてもいいし、列方向でなく行方向でもデータを抽出できます

引数は以下のような形で指定します

=XLOOKUP(検索値、検索値範囲、戻り範囲、【省略可】)

2.マトリクス表から2つの条件に合致するものを抽出する
XLOOUP関数の3番目の引数、戻り範囲にXLOOKUP関数を入れるのがポイントになります

仮に戻り範囲にXLOOKUP関数を入れなければ、いわゆるスピる(値があふれ出す)状態になります

以下が検索の軸を部門にし、戻り範囲を表の全範囲にした場合です

列方向でスピります

・=XLOOKUP(I3,B3:B9,C3:F9)*3番目の引数を全範囲

以下が期間を軸に、戻り範囲を表の全範囲にした場合です

行方向でスピります

・=XLOOKUP(I4,C2:F2,C3:F9)*3番目の引数を全範囲

この2つのスピりの交点が求まれば正解の値です

ですので、以下のように3段階でXLOOKUP関数を組み合わせます

A.部門の検索を行うXLOOKUP関数を作成

B.但し、3番目の引数に期間を検索するXLOOKUP関数を入れる

C.3番目の引数になるXLOOKUP関数は”期間の選択”に備えて戻り範囲=全範囲

最終的にはこのようになります

=XLOOKUP(I3,B3:B9,XLOOKUP(I4,C2:F2,C3:F9))

数式の内容はサンプルファイルにて、実際に入力されている内容を確認してくみてください

3.マトリクス表でランク付けをする

2.で行ったXLOOKUPによる関数では、4番目以降の引数を省略していました

ここで、5番目の引数を登場させます

VLOOKUP関数でも4番目の引数で検索方法を指定しています

FALSE=完全一致

TRUE≒完全一致

XLOOKUP関数では以下のように検索方法を指定できます

0⇒完全一致

-1⇒検索値の次に小さい値

1⇒検索値の次に大きい値

今回使用するの-1、検索値の次に小さい値です

以下の表で言えば、もし売上が30なら30より小さい値の0が該当し、前期比が15%であれば、10%が該当します

つまり、2.で行ったXLOOKUP関数の組み合わせに、検索方法-1を組み合わせれば2次元でのランク付けは、簡単に自動化できるのです

式は以下のようになります

=XLOOKUP(C5,$H$4:$H$6,XLOOKUP(D5,$I$3:$K$3,$I$4:$K$6,,-1),,-1)

企業ではABC分析の延長で、2つの変数(例:売上、利益率)で事業や商品を評価する機会が多いので、このXLOOKUP関数の組み合わせを行う機会は多いと思います

<まとめ>

今回は大きく分けて2つの事を解説しました

ⅰ)XLOOKUP関数を行列、双方向で組み合わせる(スピるの組み合わせ)

ⅱ)XLOOKUP関数の検索方法を工夫し、自動でランク付けをする

このⅰ)ⅱ)ができれば、データ集計がとても楽に行えます

ぜひ、有効活用していきましょう!

このブログではFILTER関数など、XLOOKUP関数と同じ新たなタイプの関数の解説も行っていますので、そちらもぜひ参照してみてください

尚、XLOOKUP関数が使えるエクセルバージョン(2020年9月19日時点)は限られており、OFFICE365ユーザー向けとなっています

2019バージョンユーザーなどは使用できないので、その点はまだ不便ではあります

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村