カテゴリー別アーカイブ: 日記

日記

スライサー特集

2020年5月14日 analytic-vba コメントをどうぞ

ピボットテーブルを使う上で、スライサーは欠かせない存在になりつつあります

ところが、参考文献は日本語では少ないのが現状です

ですので、これまでスライサーについて書いた記事を以下、紹介します

１．タイムライン＆スライサー

２．スライサーの表示順を変える

３．オシャレなスライサーの使い方

４．オシャレなスライサーの使い方２

5.スマホのようなダッシュボード作成

にほんブログ村

日記

5%値引したらどうなる？

2020年5月2日 analytic-vba コメントをどうぞ

昔、あるEC通販の会社にいた時に、よく会議でこんなやりとりを聞きました

　営業「売上が前期より落ちているから、値引きして売上を回復したい」

　管理「値引きをしたら利益が減ってしまうだろう！」

　営業「違う、売上が増えるから利益が増えるんだよ！」

どちら側も正しいのですが、議論をする上で欠けているものが1つあります

　今、利益率は何％か？

ということです

実はこの手の議論でよく勘違いされていることがあります

「5%利益率を減らしても、5%売上を増加すれば前と同じ利益が確保できる」

これは間違いです。値引きをした分、売上を増やせばいいというのはかなり乱暴な考えです

　値引した時に、前と同じ利益を確保するために、どれくらい売上を増やせばいいかは、「今、利益率は何%か？」によります

以下の表を見てください！この表をもとに２つのケース（①②）を見て見ましょう！

➀値引きをしたら売上を増やしても意味が無くなるケース

売上が100として、利益が10の場合、利益率は10%です

では、利益率が10%の時に、10%値引したらどうなるか？

利益率が上の表でいえば、利益率が10%の行の下、0%になり、いくら売上を増やしても利益は出ません！

➁値引きをしたら途方もないぐらい売上を増やさねければなくなるケース

では、利益率が20%の時に10%値引きして、利益率を10%にした場合はどうか？

値引き前は利益は20です

売上が値引き後もそのまま100の場合、値引き後の利益額は10です

つまり、売上を2倍にしないと、値引き前の利益を確保できません

今、➀と②の２つのケースを見ましたが、２つとも実は以下の数式で表すことができます

値引時・必要売上増加率＝値引き前・利益率　÷　値引き後・利益率

試しに➀と②を上記の数式に当てはめてみましょう

➀の場合、分母が０なのでエラーになります

➁の場合、20%÷10%になり、回答は２になります

どうでしょう？

　この数式で値引き時の必要増加率を計算できる、ということはよくご理解頂けたと思います

そして、この数式では「今、利益率は何％か？」が分子になっています

　とにかく、値引きを考える上では「今、利益率は何％か？」を把握しておくことが重要なのです

　では、次に、この必要増加率を計算する数式を、もっと会議で有効に活用する方法を紹介したいと思います

これは、値引き時の必要増加率の早見表を事前に作成しておくことです

　例えば、表の一番左上に11%という数字がありますが、これは利益率が100%の時に値引きを10%した場合、売上を11%増やさないと値引き前の利益額を確保できないよ、ということです

この表を作成しておけば、冒頭に紹介したような無駄なやりとりをせずに現実的な値引政策を決定していくことが可能になります

ちなみに、この表を見て気づくことがありませんか？

利益率が高いほど、必要売上増加率は低く済むのです

つまり、戦略の自由度が高いのです

　ですから、なるべく利益率は高く保てるよう、早見表を有効活用して値引きの無駄な乱発はなくしていきましょう！

にほんブログ村

日記, 未分類

エクセル上の配色について

2019年7月28日 analytic-vba コメントをどうぞ

先日、仕事の関係で塗り絵のようなエクセルシートを見る機会がありました

確かに、エクセルのシートは行と列の位置で表現されています．そこに色を加えることによって、データの表現方法を加えることができる点は素晴らしいと思う

但し、人によっては情報が多すぎて混乱する場合もあるだろうし、色の統一ルールなど、メンテナンスも大変だと思います

うまく使用しないと業務に混乱を招きかねないです

色使いの是非は別として、混乱を招かない為の便利機能を２点、今回は紹介したいと思います

１．配色（ページレイアウト）

この機能を使うにあたっての基本的な考え方は、色種類をシンプルに使いましょうということです

ちなみに、ここでいう色種類とは

　①赤系統と黒系統⇒これは別種類

　②濃い赤、薄い赤⇒これは同種類

ということです

色の種類をたくさん使うと、情報が多すぎてみた人は混乱しやすくなります。

この配色機能では、色種類をシンプルに組み合わせた、色種類パターンが複数、事前に登録されています

配色機能の中では、下の画像のように様々な色種類パターンを選べます

興味のある方は様々なパターンを選んでみて、実際に各セルでどんな色が選べるようになっているか、試してみましょう

ちなみに、オレンジという敗色を選ぶとこんな感じです

２．カラーモデル

色って３要素から出来きているのをご存知ですか？

興味のある方は適当なセルで以下をクリックしてみてください

その後、出てくる画面でユーザー設定をクリックしましょう

この画面で分かることは、色には赤、緑、青，３要素の組み合わせから構成されるカラーモデルがあり、それぞれの要素は定量化できるということです

この定量化する仕組みを使えば、色のメンテナンスが人によって違うということはなくなります

例えば、条件付き書式設定でもこの３要素のカラーモデルは活用できます

この３要素を明確に定量化して定義しておけば、複数人で色をメンテナンスする時にも、人によっては濃い赤、薄い赤などの色表現の違いが起こらなくなります

＜まとめ＞

先日、Power Pointの使いかたについて講義を受ける機会がありましたが、１の配色機能、並びに同機能の考え方は紹介されていました。このブログでも同機能については別記事で詳しく紹介しているので、興味のある方は覗いてみてください

にほんブログ村

日記

Power Queryって？？

2019年5月7日 analytic-vba コメントをどうぞ

Power Queryって言葉は知っているけど、具体的には何も分からないという方も多いと思います

私も色々と研究してみました

私の個人的な感想は

”本来は表計算ソフトであるエクセルにAccessのデータベース管理機能を加えたもの”

という感じでした

早速、”論より証拠！”、ということで実際にPower Queryで処理するところを、概要だけになってしまいますがお見せしたいと思います

＜実施すること＞

・１つのフォルダに、販売個数データに関する３つファイルが入っている

・３つのファイルにデータを１つにまとめた上で、価格表を参照して販売金額を算出して表にまとめる

・しかも、クリック処理だけで行う

＜手順＞

・ファイル結合

ファイルが入っているフォルダを指定してファイルを結合する

するとPowerQueryエディタ上でこんな感じになっています

・横持ちのデータを縦持ちに変える

PowerQueryエディタ上にある”列のピボット解除”をクリックすることにより、データの構造自体を一発で変えます

・PowerQueryエディタ上で縦持ちになったデータを価格表とマージします

・PowerQueryエディタ上で掛け算（販売個数X価格）を行いシート上に読み込む

最後までクリックだけで処理を完結することができました

＜まとめ＞

いかがでしたでしょうか？本来は関数などを使いながら、手間暇がかかった処理がクリックだけで簡単に出来そうなのが伝わりましたでしょうか？

今回は概要だけお見せしましたが、もっと詳細な解説ができるように準備を進めていきます！　（Power Queryって２？？はこちらから）

にほんブログ村

日記

日記：究極の連動型グラフ

2019年5月6日 analytic-vba コメントをどうぞ

よく会議に出ると”部門別”、”会社別”グラフなどのグラフが数多く並んでいるのを目にします。同じ形式のグラフが並んでいるだけですが、見る側にとっては小さくて見づらく、作成する方にとっては手間です。そこでスライサー―など、ある種の指定方法と連動して表示する値が変わるグラフなどが登場してくるわけですが、今回はこれまでと違った嗜好の連動型グラフを紹介します

今回紹介する連動型グラフはクリックしたり、何かテキストを入力することもなく、カーソルの置き場所を変えるだけでグラフで表示される値が変わります

今回は詳細な解説はできませんが、”ポイント”を３つだけに絞って概要を解説させて頂きます

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

１．HyperLink関数とマクロの連動

実は上のGIFの見出し（東京、大阪、名古屋）にはHyperLink関数が入力されています。

　HYPERLINK(名古屋())

このHyperLink関数からマクロを動かしています

ちなみにHyperLink関数はIFERROR関数（エラーが出た際の値を事前指定した値に置き換える関数）の中に組み込まれていますが、HyperLink関数単独の場合には以下の様な表示になります

２．マクロとグラフ・データ元の連動

１のマクロの中の記述にて[REGION]という見慣れない文字が出てきましたが、これはマクロと連動するグラフ・データ元の一部範囲の名前です

マクロでREGIONで名前管理された範囲の値（東京、大阪、名古屋）を書き換えるようになっています

下の画像で言えば黄色のハイライトが付いた”大阪”のセルのところです

更にHLOOKUP関数を使い、M4セル（名前）の値が書き換えられたら、上の画像の赤い矢印で示された値が左から参照されて表示されるようになっています

つまり、マクロを通じて➀名前の書換（地域指定）⇒②表の左から名前を通じて該当する値を参照⇒③グラフの元・データとして表示（下の画像の黄色の部分）するような仕組みになっています

３．条件付き書式により、見出し（東京、大阪、名古屋）の色を変える

２で出て来た名前（REGION）と連動して該当の見出しの色が変わるように条件付き書式のルールを設定します

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

今回はポイントだけの説明になってしまいましたが、なかなか斬新な内容だったのではないかと思います

今回紹介した３つのポイントについては、連動型グラフに限らず、他の使い道も色々とあるのではないかと思います

ぜひ、自分なりの楽しい使い方を開発してみてください

にほんブログ村

日記

この１年で見られた記事

2019年4月27日 analytic-vba コメントをどうぞ

平成もとうとうおわり。そして１０連休の初日。細々と続けてきたblog記事の振り返りをしたいと思います。これからはPower Queryの時代ですね

●この１年で見られた記事

１．スピードメーターグラフを作成しよう

見る人に”数字のインパクト”を与えるという点では最高のグラフだと思います

そして、作成する側としては”どうやって作成するのだろう？”という好奇心もそそるものがあると思います

作成の仕方自体はとてもシンプルです

下の円グラフのグレーの部分の色を透明にしたら”黄色の針”が残ります。

この針を動かす仕組みを作成していきます

記事はこちらから⇒スピードメーターグラフを作成しよう

２．Pivot&Table

エクセルのピボットテーブルとテーブルを使いこなせば、データの活用の仕方が格段に進化します

もし、日常的に行数が多い表のある列に対して、2行目から最終行まで同じ数式を打ち込んでいる人がいたらぜひテーブル機能を使ってください。１行分だけ数式を打ち込めば全ての行に数式が反映されます

ピボットテーブルについては、使い方自体の提案をしています。

従来のピボットテーブルはあくまでデータを集計する為に使われていると思いますが、実はピボットテーブルそのものをビジュアル化することができます。

もし、ピボットテーブルで集計したデータを表に打ち込んで前期比などを計算している人がいたら、ぜひブログ記事を見てみてください。そのままピボットテーブル上で前期比を計算しつつ、プレゼン用の表にする方法を解説しています

記事はこちらから⇒Pivot&Table

３．エクセルダッシュボード

あまり日本では馴染みがないと思いますが、海外ではVBAと同じくらい講座があります。

エクセルダッシュボードとは何かというと、一言で言えば、飛行機のコクピットのように一目で組織の目標達成に必要なデータを把握できるようにするものです

詳細は各ブログ記事を参照して頂くとして、ここではダッシュボード関連記事の中でもアクセス数の多かったものを紹介します

➀スマホのようなダッシュボード

最終行を探す旅を続けさせるような資料ではなく、見たいデータのみを切り分けて表示できるようにしたダッシュボードです⇒ブログ記事

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

オプションボタンを活用して、”今、何を説明しているのか？”を明確にして聞き手を迷わせないダッシュボードです⇒ブログ記事

下のGIFでは３社の経営指標をオプションボタンの指定により、入れ替わり表示しています

●これから書きたい記事

エクセルを軸としたAccessとの連携、もしくはRPAとの連携については物凄い可能性を感じています

特に連携技術の一つとしてPower Queryについては特に注目しています

（一部は日記として記事を書いています⇒ブログ記事

関数やVBAについてあまり知らないユーザーでも、Power Queryで業務を大幅に改善できる可能性を秘めています

こちらについては今現在、記事を準備しているところです

後、スピードメーターグラフについては、デザイン性の高いグラフの作成の仕方についても解説記事を書こうと思っています（テンプレートはこちらからダウンロードできます⇒ブログ記事

以上、長文になりましたが、最後まで読んで頂きありがとうございました

にほんブログ村

日記

意外なエクセル術　～グラデーショングラフ～

2018年10月8日 analytic-vba コメントをどうぞ

過去にも数字を魅せるグラフ術の中で紹介していますが、

好評なので、要約版として記事を書きます

とてもインパクトがあって数字の持つ意味がそのまま伝わってきそうです

作り方自体は実はそれほど難しくはありません

２つのグラフを重ねることで”透明な部分”と”透明でない部分”を生み出します

エクセルには標準機能で、グラフを重ねる機能があります

下の図の「追加」を押すと、グラフの元データを追加することができます

上の図の画面は、グラフの上で右クリックし「データの選択」をクリックすると表示することができます

組み合わせる２つのグラフは、以下の画像のような感じになります

ちなみに左のグラフはドーナツグラフです。20個均等になるようにグラフのパイを作成します

右側のグラフは、下の図のように図形のぬりつぶしを使います

いかがでしょうか？作成の仕方のイメージはできたでしょうか？

もし、分からないことがあればぜひ、こちらの記事を参照してみてください！

では！また次回

にほんブログ村

日記

RPAとエクセルとの融合の可能性

2018年8月26日 analytic-vba コメントをどうぞ

今日は今、話題のRPAについて思いつくまま書きたいと思う

RPAの仕事をしていて、気づいた点は大きく３つある

１．万能ではない、少なくともAIではないし、ユーザーがシナリオを書けるというのは幻想

良く落ちるし、万能ではない。RPAのデモなんかの時には動画を用意している人も多いのでは・・・

それでもRPAを使うのは、一度タクシーの便利さを知ってしまったしまったと同じこと

落ちると分かっていても、あの面倒くさい処理が少しでも軽減できれば・・・と思うのは

弱い人間の常なのだと思います。

後、AIと勘違いしている人が多い。これは全く勉強不足

更に、よくユーザーが設定できる、というのも幻想。

「我が社ではユーザー主導でRPAを導入・・・」なんていうのは、IT部門とユーザー部門の中間に専門部署が

実はあったりする。後、会社によってITリテラシーも違うし

２．やっぱりエクセルは便利

話しを分かり易くする為に２つ、実際にあった話しをします

ある人がエクセルのデータを消去する仕組みをRPAで作成していました。

どうやってやるかというと””（空白）をセルに埋め込むシナリオを書くのです

当然、シナリオ作成するのに時間もかかるし、実際に処理も何十秒かはかかる

RPAでVBAを動かすなら、シナリオもVBAの記入もすぐだし、処理自体もすぐです。

あらゆるRPAのデモを行ったり、見たりしていて気づいたのは、人の心を動かすのは

RPAが人の代わりに行う点ではなく、その処理スピードです

つまり、処理スピードがないとRPAの魅力は半減するのです

処理スピードを上げる為にもVBAとの連携を進める必要があります

ちなみに、RPAの魅力の一つは開発も修正もすぐに出来ること。繰返しになってしまうが、

VBAを使えば更に開発スピードを上げることができる

話しを基に戻して実際にあった話の２つ目をすると

ある基幹システムからエクセルをアウトプットした後、ファイル名をつけるRPAを作成していたところ

エクセルファイル（保存を有効にする、の制限がかかっている）を保存する処理がどうしても安定しない

そこで、ダウンロードした後（これまたファイル名が変わる）にＲＰＡによりフォルダ内のファイル名を取得し、

ＶＢＡでファイルを名前をつけてコピーする処理に変えたところ、相当な進歩を見せた

ＶＢＡといってもネットから2行ぐらいコピーしたもの・・・それでも効果は絶大

ＲＰＡはＶＢＡを動かすもの・・・と割り切ってもいいくらいの確信を得た

３．ＲＰＡの醍醐味は業務改善

これまたエクセルの話し・・・

ＲＰＡを動かすには投入データが必要です。私は基本的にはエクセルで作成しています

投入データは当然、RPAでも読み取れるようにシンプルなものしなければなりません

当然、ユーザー（業務側）は悩みます。

ああしよう、こうしよう、そういえば、そもそもこれは要らないよね・・・

そう、ついついエクセル管理表などはメタボになりやすいのです

RPAがメタボに気づくいい機会になるのです

とりとめなく書き連ねましたが、少しでも皆さんのお役にたてば光栄です

にほんブログ村

日記, 未分類

RPAの可能性～RPAをマクロ代わりに使う～

2018年6月8日 analytic-vba コメントをどうぞ

今、RPAのセミナーにいくとどこも満員です

但し、セミナーで説明される内容と違い

実際にはエクセルマクロ（VBA)をRPA内で操作している例も大分ありそうです

なら、

RPAを使う意味合いはあるのか？まずはVBAを覚えた方が良いのではとおもう方も、もしかしたらいらっしゃるかもしれません

RPA機種にもよりますが、最近、エクセル操作についても改革をもたらせるのではないかと考えはじめました

それは、RPAは簡単に”キー操作”をPCに送れるからです

（下はUipathの例、Send Hotkey)

実はエクセルはショートカットキー操作だけで相当な操作ができます

ある意味、記録マクロよりも手軽です

それをRPAで他のメニューとあわせて、もっと手軽に行ってしまおうということです

これから、数式も含めてA２からB2の範囲をC2に値コピーを行ってみます

実はRPAのメニュー内では値コピーは難しいのです

ちなみに

Alt+H,V,V

の順番でキー操作をPCに送れば行えてしますのです

ここまででピントこないという方は一度、エクセル上Altキーをおしてみてください

実はキー操作だけであらゆることが行えることが実感できると思います

ちなみにRPAのシナリオはこんな風に地道にキー操作を一つ一つ積み上げいきます

そしてシナリオを実行すると以下のようになります

簡単なマクロならマクロを覚えるより簡単かもしれません

最後に追加ですが

マクロを実行すると元にもどせませんが、RPAならCtrl+Zで戻せます

にほんブログ村

にほんブログ村

日記

カーソルの位置を行列双方向でハイライトする

2018年2月21日 analytic-vba コメントをどうぞ

行列双方向で大きな表を扱っていると、今、どこを操作しているのかわからなくなり、迷子になることがあります

昨日、twitterで見つけた記事↓から、この迷子問題に対する大いなる示唆を得ました

https://hamachan.info/win7/Excel/active.html

こちらの記事を元にして、作成したのが下の表です

勤務スケジュール内で、カーソルを置いた行列がハイライトされつつ、勤務者の顔が表示され、ダイナミックに動きます

！今回のポイント

・条件付き書式の使い方

ⅰ）Cell関数とColumn関数、Row関数

Cell関数はセルの情報を返す関数です

このCell関数と列番号と行番号を返すColumn関数と、Row関数を組み合わせて条件付き書式の条件にします

ⅱ）OR条件

ハイライトしたいのは、カーソルを置いた行と列の位置です

こちらを「カーソルを置いた行」もしくは「カーソルを置いた列」

と解釈し直します

ⅰ）ⅱ）を組み合わせて設定した条件付き書式の画面が次の画像です

・イベントプロシージャ―

条件付き書式を設定しただけでは、うまくハイライトされません

カーソルの位置を変えた時に、更新処理が強制的に行われるようにイベントプロシージャ―を入れます

ⅰ）コードを表示

ⅱ）更新処理の組み入れ

下の「Worksheet」⇒「SelectionChange」から更新を行うコードを入力します

Application.ScreenUpdating = True

上のコードを入力することで、カーソルの位置を変更すると強制的に画面更新が行われ、条件付き書式もうまく稼働します

・画像のダイナミック表示

更に、２つの仕掛けを付け加えて”選択したセルにある名前”と対応する画像を表示できるようにします

こちらは、名前機能とカメラ機能を3段階で活用します

セル内の名前⇒別シートにコピー⇒別シートをカメラで参照

１．別シート（計算シート）に選択したセルの内容をコピーする

条件付き書式を強制的に稼働させるために作成したイベントプロシージャ―内に以下のコードを入れます

Worksheets(“計算シート”).Range(“C3”).Value = Target.Value

すると、計算シートにカーソルを置いたセルにある内容がコピーされます

以下は、実際に設定したコードです

複数セルが選択されると、条件付き書式がうまく動かない為、制御を入れています

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
If Target.Count > 1 Then
MsgBox “複数のセルは選択できません！”, vbCritical
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If

Worksheets(“計算シート”).Range(“C3”).Value = Target.Value

End Sub

２．名前とカメラ機能を組み合わせて画像をダイナミック表示する

①下のような表を作成し、各画像が入ったセルに「人の名前」と同じ名前（名前管理）をつける