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

日記:車が動くエクセルマクロ教材~繰り返し処理~

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

私もプログラミング教育の普及に貢献したいと考えています

 「プログラミング学習」と「エクセルマクロ学習」を兼ねた、教材も作成できたらいいなとも思っています

 エクセルであれば、低価格で作成でき、親御さんもエクセルマクロにも強くなれるのであれば、とても有益だと思います

今回は、先日ある講習で使用としてボツになった学習資料を紹介します

 上のGIF画像では、マクロで画面下の赤い車を「スタート位置」から「Goal位置」まで動かしています

・ピン位置/50~350は左端からの距離です

・車上のボード/車の位置です

ポイント!

Goal1_BeforeとGoal1_Afterのマクロは、同じ内容で赤い車を動かします

但し、次の点が違っています

Before:繰り返し処理を使っていない

After:繰返し処理を使っている

このBeforeとAfterのコードの違いを確認して見て、「繰り返し処理」というものを体感しようというのが演習の趣旨でした

繰返し処理を使ったAfterのコードが次です

 そして、違うマクロでは上のAfterのコードを書き替えて、Goal2まで車が動くという演習でした!

引き続き、教材のブラッシュアップを図りプログラミング教育に貢献できるようになりたい、と思っています!

最後までブログを読んで下さり誠にありがとうございます

参考までにエクセルファイルを添付しておきます

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


にほんブログ村

日記:立体的エクセルスピードメーターグラフ

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

 今日は、題名にあるように立体的なスピードメーターグラフを作成したので、自分の備忘録もかねて記事にします

立体的スピードメーターグラフ

次回、エクセルダッシュボードを作成する時にはこちらのグラフを入れたいと思います 

作成のポイントは3つあります

1.下地の作成

図形の弦から2つの下地を作成します

この時、線を太くしておきます

2.針

スピードメーターの針は、グラフの角度を0にしても作成できることが分かりました

その代わり、針部分の線を太くすれば、本物のメーターの針のように見えます

3.立体的に仕上げる

全体を立体的に魅せるために2種類の「図形の効果」を使用します

1つは透視投影です

2つ目は反射です

これらの2つを組み合わせると立体的に仕上がります

この立体的スピードメーターグラフを活かしたエクセルダッシュボード作成を行ってみます

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


にほんブログ村

#SPILLエラーについて~新関数に独自のエラー

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

 これまで、1つのセルへの入力で複数セルに出力する新関数について、複数の記事を書いてきました

 例えば、下のGIF画像はFILTER関数を使った検索付きのリストの例です

 C列にFILTER関数を仕込んであり、E列で検索文字「鈴木」が設定されるとA列のリストデータから「鈴木」を含むリストデータを出力します

 この新関数では独特のエラー「#SPILL」が出力される時があります

このエラーの意味について、今日は少し解説したいと思います

SPLLLエラー

見慣れないエラーですが、発生理由はシンプルです

 この新関数は複数のセルに出力を行いますが、出力範囲に何らかの入力がある場合にこのエラーは発生します

 

上の画像で言えば、!の文字が新関数の出力をブロックしてるのです

このブロックしている文字を取り除けば、このエラーは解消されます

 尚、ブロックしている文字のフォントが「白色」で人の目では見えない時にも同じエラーが発生するので注意が必要です

では、今回は以上です

最後までブログ記事を見て下さり、大変感謝しております!

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


にほんブログ村

【日記】おすすめ記事ランキング~1~3位まで

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

今回は、3カ月のアクセス結果をもとにしておすすめの記事を紹介します

一位!

フリーアドイン~長い数式を見やすくするツール~

エクセルもスマホのようにアプリを無料でダウンロードできるようになりました

紹介したアプリ(アドイン)は、長くて複雑な数式を解読するためのアプリです

他人が作成したエクセルファイルを引き継いで、困ったことはありませんか?

「IF・・・IF・・・」

IFの連発により、作成した人しか分からない状態になっていることがあります

そんな時、このアプリは有効です

二位!

スライサーのリスト表示順を変える

こちらは、下のGIFのようにスライサー上のリストを変える方法を紹介した記事です

この処理は、ファイルタブから「オプション詳細設定」という普段聞きなれない所で処理します

三位!

読込先の変更~パワークエリ初級講座5回目

パワークエリの処理にて、シートに読込んだデータを接続のみに変えたりする処理をまとめた記事です

最近は、パワークエリ関連記事へのアクセスが増えています

中でも、この記事が一番アクセスがあります

パワークエリの特徴の一つは、行数の制限がないことです

この「接続のみ」を選んで、読込作業をすることで行数の制限から解放されます

以上、3カ月のアクセスからおすすめ記事をまとめました

これからも、有用な記事を発信していけるように精進していきます!

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


にほんブログ村

Sで終わる関数は素晴らしい~AverageIfs関数~

エクセル関数はとても進化しています

XLOOKUP関数も同様ですが、新エクセル関数は絶対に活用した方がいいです!

IFS関数、COUNTIFS関数、そしてAVERAGEIFS関数は、複数条件を簡単に作成できるので便利です

上記3つの関数ともに関数の書き方は同様です

1.文法

AVERAGEIFS関数の書き方は以下になります

=AVERAGEIFS(平均算出範囲,条件範囲1,条 件1,条件範囲2,条件2,・・・)

2.事例解説

今回、AVERAGEIFS関数を試してみるデータは以下です

条件設定と平均値算出は以下の画面で行います

部門と受注日が抽出条件になります

数式は以下の様に設定してあります

複数条件はB列/条件範囲1,⇒C列/条件範囲2の流れで設定しています

この複数条件設定を他の方法で行おうとしたら、関数ではなくピボットテーブルで算出した方が早いですね

関数1つで複数条件を算出できるのは、もの凄い進歩です

最後に補足ですが、部門のリストデータはUNIQUE関数を活用しています

UNIQUE関数で部門の列(東京、東京・・、大阪、大阪)から重複を削除した上で、「#」にてリストとして再利用しています

UNIQUE関数とリストの組み合わせについて深く知りたい方はこちらを参照してください

  ⇒リストをUNIQUE関数でより便利に

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


にほんブログ村

Sで終わる関数は素晴らしい!~Countifs関数~

前回は、IFS関数を紹介しました

今回はCOUNTFIFS関数を紹介します

本当にSで終わる関数は素晴らしいです

数式の書き方はIFS関数とほぼ同じです

今回は下の事例で解説を行います

この表から最高得点・科目と最低得点・科目の組み合わせが当てはまる人数をCountif関数とCountifs関数、の両方で求めてみます

.Countif関数のケース

Countif関数は1つしか条件式を設定することができません

なので、一旦、複合キーを作成して検索条件にするしかありません

数式は次のような書き方になります

Countif関数では、検索条件範囲/H4:H8と検索条件/D12との組み合わせを、1つしか使えないのです

=COUNTIF(H4:H8,D12)

2.Countifs関数のケース

Countifs関数の場合は複合キーを使用する必要はありません

Counfifs関数の場合、複数の検索条件範囲と検索条件の組み合わせを設定できるからです

以下が、実際に上の画面で使用した数式です

=COUNTIFS(F4:F8,B16,G4:G8,C16)

数式の書き方は、前回に紹介したIFS関数と同じ様に、検索条件範囲⇒検索条件の記述を条件がある数だけ繰り返すだけで済みます

<まとめ>

Countif関数を使いたい時に、複数条件がある場合には複合キーを作成しなくてはならないので、いつも面倒くさい思いをしていました

Countif関数を使えば、条件がいくつあろうとあまり手間ではありません

Sで終わる関数は本当に便利ですね

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


にほんブログ村

もしもし問題を解決する~IFS関数&LET関数~

 エクセルの関数を覚えていくと、そのままエクセル作業の効率は上がっていきます

そして、エクセル関数の使用方法が習熟してくると必ず次の問題が起こります

関数を組み合わせている内に、長く複雑な数式になってします

 長く、複雑な数式で一番やっかいなことの1つは、IF関数の中に更にIF関数を入れていくもしもし問題です

 条件が増えれば増えるだけやっかいな数式になり、作った人以外は誰も直せなくなります

 今回は、この問題をIFS関数とLET関数を使って解決していきます

まず、今回の取り組むテーマについてお話します

下の表は個人の成績表です

 この表からMAX関数を使って、各人の最高得点を出力しておき、更にIF関数を使って、最高得点科目を出します

 このテーマを解決するのに、従来のIF関数を使用した場合には、下の画像のように2つのIF関数を組み合わせねばなりません

ちなみにセルF4では、前述のようにMAX関数を使用しています

 筆者も5科目でテーマを作りたかったのですが、数式が複雑すぎて説明に困ると考えたので3科目にしました

さて、ここでIFS関数をIF関数の代わりに使用してみます

こちらはIFS関数は1つ使用するだけですみます

IFS関数は複数の条件式があるときに便利な関数です

IF関数の場合ですと

 =IF(条件式,出力/条件式に合致,出力/条件式に合致しない)

という数式の書き方になりますが

IFS関数の場合ですと、

 =IFS(条件式1,出力/条件式1に合致, 条件式2,出力/条件式2に合致,・・・,TRUE,出力/左記の条件式にいずれも合致しない場合)

という数式の書き方になります

この数式のポイントは2つあります

①条件式と出力の並べ方

 条件式⇒条件式に合う場合の出力⇒出力⇒条件式に合う場合の出力

 という並び順になり、IF関数の場合の一)条件式、ⅱ)出力(合致)ⅲ)出力(合致しない)の場合と違うこと

➁条件式に合致しないもの

 これは英語で言えば、ELSEのことですが、IFS関数では

 「,TRUE,出力(いずれにも合致しない)」

と書きます

さて、ここでLET関数を使って、IFS関数をもっとシンプルにします

LET関数とは関数の中に変数を使うことができる関数です

変数とは方程式のXと考えて頂ければ幸いです

方程式もXを使うことで式がシンプルになっています

 今回は上の表でIFS関数の外で求める最高得点(MAX関数の出力結果)を方程式に組み込みます

まず、LET関数の文法について解説します

=LET(変数名(X),変数の値(X=?),実際に変数を使った式)

変数(X)を最初に宣言するのがポイントです

今回の事例では、最高得点をMaxの名で宣言して、実際にMAX関数で最高得点を算出しておきます

そして次に、IFS関数にて変数:Maxを条件式内に使います

セルを通じて参照するよりずっと分かり易い数式になっているはずです

LET関数を使わない場合には、上の数式のようにF4セルを参照し、F4に最高得点が入っている、という意味合いを確認せねばなりません

<まとめ>

今回は、IFS関数を使い、複数条件が存在する数式を分かり易くする方法について紹介しました

更に、LET関数でIFS関数の中味を方程式のXのように分かり易くする方法についても触れました

IFS関数もLET関数も最新の関数です

ぜひ、この新たな2つの関数を有効活用して、「エクセル業務」の効率を向上させていきましょう!

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


にほんブログ村

おすすめ記事_2020年10月10日

最新のエクセル技術を紹介した、おすすめ記事を紹介します

1.スピードメーターグラフテンプレート(散布図グラフ使用)

これまでも円グラフとドーナツグラフを組み合わせたスピードメーターグラフは紹介してきました

今回、紹介するのは針部分に散布図グラフを使用した、よりリアルなスピードメーターグラフのテンプレートです⇒記事

2.XLOOKUP関数を組み合わせて集計上手になる

マトリクス表に関する処理は手間がかかるものです

2つの軸に合うもの選択するのに、目で照合したりする必要がありました

例えば、ある部署の売上成績と前期比から、2つの条件に合うランクを選択する場合などです

ところが、XLOOKUP関数を組み合わせれば、2次元のランク付けを自動化できるのです⇒記事

3.Power Queryで複合キーをうまく活用する

集計上手になるコツのひとつが複合キーをうまく使いこなせることです

例えば、条件1と条件2がともにBの場合は〇を出力する、などのケースです

この場合は複合キーをもとにした別表を作成し、VLOOKUP関数で参照して〇を出力します

今回はPower Queryを使用してもっとスマートに行える方法を紹介します⇒記事

<まとめ>

エクセルが誕生して20年、その間にエクセル機能は目覚ましい進歩を遂げました

そのエクセル機能・進化の果実はしっかり活用していきましょう!

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


にほんブログ村

スライサー特集

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

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

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

1.タイムライン&スライサー




2.スライサーの表示順を変える

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

4.オシャレなスライサーの使い方2

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

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


にほんブログ村

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

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

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

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

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

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

 今、利益率は何%か?

ということです

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

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

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

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

以下の表を見てください!この表をもとに2つのケース(①②)を見て見ましょう!

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

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

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

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

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

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

値引き前は利益は20です

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

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

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

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

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

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

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

どうでしょう?

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

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

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

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

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

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

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

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

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

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

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

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


にほんブログ村