タグ別アーカイブ: 分析

【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言語が本当に有効なのはこういった複雑な分析が必要な場面だと思います

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


にほんブログ村

お仕事の依頼

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

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

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

 

 

現場発のDXを応援!

実務・現場でのデータ活用を、豊富な経験とモダンエクセルを始めとした実践的なエクセル力で全力応援!

現場の言葉でデータを語ります

 コロナ禍をきっかけとして、ビジネスの現場はオフィスからリモート中心に移行しています。リモート・ワークへの働き方の移行は、実務・現場での「データ」との向き合い方を大きく変えようとしています。リモート・ワークでは、人間の勘ペーパー業務に依存せず、データを重視した働き方が求められています。実務の現場でデータ活用する際に問題となるのは、現場には企業のIT部門やトップコンサルタントの目が届かない、もしくは、理解が困難な世界があることです。Excellent仕事術は、これまでの豊富な経験を活かして実務の現場を明確に見える化し、データ活用の下地を作ります!

何よりもデータを最重視します

 データは事実を雄弁に語ります。データを有効に活用すれば、ビジネスは必ず成長し、個々の生活も豊かになるはずです。但し、データの活用の仕方では事実が正しく見えなくなったり、時間を膨大に浪費してしまい、成果が全くでないこともあります。Excellent仕事術ではデータを最重視し、実務の現場を正しい方向へと導きます!

これまでの豊富な経験を活かします

 Excellent仕事術・ガッツ鶴岡はこれまで、創業型のベンチャーから時代の寵児となった企業まで幅広い環境で働いてきました。業界も「ビックデータ重視」のEC通販から、「人間の勘重視」の食品業界まで、とても幅広く関わらせて頂きました。いずれの環境・業界でも重視してきたのは「データ」「エクセル」です。これまでの経験を活かして、新たなデータ活用の世界を生み出していきます!

新たなエクセル活用術を広めます

 エクセルが誕生してから、20年の月日が経ちます。エクセルはもはや表を作成するだけのツールではありません。データベース/パワークエリを作成したり、ダッシュボードなども作成することができます。

 Excellent仕事術では、これまで海外のエクセル活用事例も数多く研究してきました。海外ではエクセルの機能を実践的に活用している事例が沢山あります。Excellent仕事術では海外の事例を参考にしながら、モダンエクセルなども活用した「初心者から上級者」まで幅広く活用できる「実践的なエクセル術」を研究しながら広めていきます

全力で汗を搔きます

 実務の現場で重要なのは「汗を搔いて体験すること」です。理屈だけでは現場は簡単には変わりません。まずは自らが変わり、率先して「汗を搔く」を重視して実務・現場の「データ活用」を応援していきます!

お仕事の依頼

エクセル新機能一覧

モダンエクセル

モダンエクセルのメインは、Power Queryという技術になります!

 多くのプログラミング言語学習において、まずは最初に「Hellow Word!」を表示するとこから体験します!

Arduinoなどのマイコン工作では「Lチカ」体験です

Power Queryでは、次のような「ETL」体験からスタートです!

Extract ➡ データの抽出

Transform ➡ データの変換

Load ➡ データの読込

まずは、1度「ETL」を体験してみませんか!

必ず、今までは感じなかった新たな発見があるはずです!

初級講座 :データの取得(抽出など)、読込など

中級講座:データの結合などの変換

上級講座:縦横並びの変換などの特殊な変換処理

コラム:PDFファイルをエクセルに変換するなどのテクニックの紹介

M言語

エクセルダッシュボード

 読者の方には、エクセルで数値資料を作成する機会が多い方もいらっしゃると思います。

 実は、数字は見た目です。そして、第一印象で決まります。特に、一目で「数字が細かく詰まっている」と分かる資料はまともに見てもらえません。そして、その資料作成には、膨大な労力がかかってしまっています。

 では、最小の労力で、最大の効果を発揮する資料を作成するにはどうしらいいのか?、その答えは、このダッシュボード術の中にあります。このダッシュボード術は、まるで車や飛行機のコクピットの中にいるような感覚で、一目で【数字を魅せる資料】を作成します。

 このダッシュボード術は、VBAの技術はあまり必要としません。関数の使用も、ごく簡単な関数の組み合わせのみです。一番必要となるのは日本ではあまり紹介されたこなかった、新たなエクセル機能の活用です

エクセルダッシュボード

オプションボタンを活用したダッシュボード

グラフデザイン

 人は見た目が9割と言いますが、グラフも同じです。このコーナーでは、伝わりやすいグラフデザインを手軽に作成できる手法を解説します。グラフデザインの種類は「スピードメーター」「温度計」「画像入り」など、実に様々なデザインを揃えています!

手っ取り早くテンプレートを使って、グラフを作成したいという方のために、テンプレートを集めたコーナーも設けています!

 ⇒グラフデザイン

新ピボットテーブル術~レポート力向上~

 ピボットテーブルはデータを抽出する為の機能として良く知られています。表は別に作成しておき、ピボットテーブルで抽出した数字を、別途作成しておいた表に転記するような使い方をよく目にします。

 実は、ピボットテーブルから直接、魅力的な表作成を行うこともできるんです。下の画像のような、魅力的な表も、ピボットテーブルから直接作成できます。このコーナーでは、テーブル機能とあわせて、ピボットテーブル本来の力を活かした技術を紹介していきます。

新ピボットテーブル

エクセル裏技

 エクセルには日本ではまだあまり知られていない機能が沢山あります。下のGIF画像をご覧ください。実はエクセルでタイマーも作れてしまうのです。しかも、ほんの数行のコードを書くだけで作成することができます。この位はまだ序の口です。このコーナーでは、隠れたエクセルの裏技をどんどん紹介していきます。そして、どんどん業務の省力化と、差別化を行っていきましょう!

 ⇒裏技

VBA

いよいよプログラミング教育が日本でも本格化しています

このExcellent仕事術では、これまでになかった手法でエクセルVBAを広めていきます

RPA 

 RPA(ロボティクス・プロセス・オートメーション)が急速にビジネスの現場に浸透してきています。

 人間が行っていたエクセル作業をRPAが代わりに行ってくれるから⇒これからはあんまりエクセルの技術習得にそんなに力を入れなくていいや・・・そんな風に考えている方もいらっしゃるかと思います。

 実はRPAの浸透により、逆にエクセルの存在感が増してきています。その理由は、“RPAはデジタルデータにより動く労働者”、という点にあります。デジタル労働者はデジタルデータがないと働きません。

 多くの業務の現場では、デジタルデータは、エクセルにより作成されています。つまり、RPAをエクセルとどう連携するかが、RPA活用の鍵になっていきます。

 このコーナーではこの”RPAとエクセルとの連携”をテーマに、各種連携手法を紹介していきます。⇒RPA


アプリ/アドイン

 エクセルでもスマホのように、アプリ/アドインをダウンロードすることができます

 ダウンロードできるアプリの内容は、グラフを自動作成するものから、長い数式を解読するものまで多岐に亘ります

ぜひ、下の記事を参照してご自身のニーズにあったものを見つけてみてください

アプリ

エクセル分析

エクセルには、ソルバーをはじめとする優れた分析機能があります

 ソルバーは、人間が手動で計算したら2、3日は要するようなシミュレーションを一瞬で終了してくれる優れものです

 このブログでは、エクセルの便利機能を組み合わせて、「ABC分析を効率的に実施する方法」などの最新のエクセル分析術を紹介していきます!

エクセル分析

エクセルと英語力

実は、英語力とエクセル力は相関関係にあります

エクセルを作成したのは、英語圏の企業です

エクセルを使う上で、英語の概念が必ず登場します

ですので、英語ができた方がエクセルの上達も早いです

 そもそもエクセルと英語、どちらもビジネスをしていく上ではとても重要なスキルです

 このブログでは、英語力の向上について貢献できるような記事をアップしていきます

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

にほんブログ村

7月17日勉強会_報告

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

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

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

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

アイコン

0717勉強会資料 839.14 KB 12 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へ

にほんブログ村

ソルバーによるシミュレーション~設定~

こんにちは、Excellent仕事術ガッツ鶴岡です

さて、あなたの周りで数字に強いと言わている人はどんな人でしょうか?

「数字が頭に入っている」

「計算が早い」

この数字が強い人の定義には、様々な意見があります

 一つだけ確実に言える事は、組織の目標達成に向けたシミュレーションができている人は最強に「数字に強い人」です

「目標達成には商品Aを最低でも○○円以上販売することが条件」

「仮に利益率が□□%以下だとした場合には・・・」

 しかも、前提条件や制約条件を「数字」をまじえて言われると凄い説得力があります

 実は、エクセルにはソルバーという強烈なシミュレーションマシンがあるのをご存知でしょうか?

意外とあまり知られていないようですが、ソルバーはとても便利なんです。

 ソルバーを使いこなせば、プレゼンや会議中に、その場で複雑なシミュレーションを実施することができます

そして、その活用機会も、給与昇給額、商品別必要生産数など様々な分野であります!

しかも操作はとても簡単なんです!

ぜひ、ソルバーを使いこなして「数字が強い」人と言われるようになりましょう!

 これから、実際にソルバーを使用してどの程度のシミュレーションができるのかを見て頂きます

 ソルバーはマクロでも動かすことができます

「実演」は、マクロで行います

実演する、シミュレーションの内容は次の通りです

①今期の売上総利益が「675」⇒来期は「1000」にしたい

②部門がA、B、Cの3部門があり、それぞれ利益率違う

「利益率が一番高いA部門」の売上増加率は「?」%必要か?

シミュレーション実施前のエクセル画面

では目標の「1000」を上の画面の左上①に設定し、「目標・売上総利益」ボタンを押してソルバーを実行します

 上のGIFでは見えていませんが、マクロを実行した後にソルバーが起動しています

 しばらく計算に時間を要しますが、計算が終わった後はスピードメーターの針が振れているのが分かると思います

 上の例では何も制約条件を設けていませんが、実際にはA部門の増加率は”50%まで”、などの条件を追加できます。

では、本格的なソルバーの解説に入ります!

今回は、まずソルバーをご自身のエクセルで活用できるようにしましょう!

 通常のエクセル画面では、ソルバーは使用できるようになっていませんので設定が必要になります

 ソルバーの設定は、次のショートカットキーを押すところから開始になります!

  “Altキー+TI”

このショートカットキーを押せば次の画面が出てきます(ちなみにIはアルファベットのIです)

*他にはファイルタブからオプションを選択してから設定する方法もあります)

ソルバー設定

ソルバーアドインにチェックを入れてOKボタンを押しましょう

そうすれば、データタブの下から”ソルバー”が選べるようになっています

では、次回はソルバーを使って簡単なシミュレーションを行ってみましょう

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

にほんブログ村

ソルバーによるシミュレーション2~基本操作~

こんにちは、Excellent仕事術ガッツ鶴岡です

前回はエクセル画面にソルバータブを設定しました。

ところで、ソルバーとは何でしょう?

英語では、「解決」という意味になります

今回は”ソルバーとは何か”について触れた後、簡単な操作を実際にしてみましょう!

簡単な操作をしてみた後、「ソルバー」の意味を実感して頂けると幸いです!

1.ソルバーとは何か?

まずはソルバーの画面を見てみましょう!

データタブから「ソルバー」をクリックしてみましょう

すると、次のような画面が開きます

大きく分けて3つの構成になっています

 ①シミュレーションの目的設定

 ②①を達成する為に変動させる「値」及び「値範囲」の設定

 ③①を達成する際の制約条件の設定

つまり、③の設定条件を設定した上で、②の値や「値範囲」を動かしながら➀の値をシミュレーションしようということになります

ここまでの解説で、ソルバーについてのイメージが少し沸いてきましたでしょうか?

2.実際に操作する

では、2つのセルを乗じた値を最大化するシミュレーションを行いましょう

下の図のようにB8セル(茶色)に、B3(黄色)とB4(緑)のセルを乗じる数式(B3*B4)を入れておきます

上の図でB8セル(茶色)が1.で解説したシミュレーションの①目的になります

では、こちらを実際にソルバー画面に設定します

目標値は「最大」「最小」「指定値」とありますが、今回は最大で設定します

次に、B3:B4(黄色~緑)を1.②の変動する値範囲として「変数セルの変更」に設定します

1.①目的と②変動させる「値」「値範囲」を設定したところで、最後に制約条件も作成します

制約条件については、次の画像の箇所から設定します

追加をクリックすれば下の画像のようなダイアログボックスが開きます

今回は、変動する値範囲それぞれに「4以下」という制約条件を設定します

➀~③を設定するとソルバーの設定画面が以下のようになっているはずです

ではソルバーの解決ボタンをクリックしましょう!

変動する値の制約条件に応じた16(4x4)が算出されましたでしょうか?

今回の解説は以上になります

では次回からは他の条件でソルバーを活用してみましょう!

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

にほんブログ村