Excel2010。シナリオを知ると色々シミュレーションできます。

Excel2010。シナリオを知ると色々シミュレーションできます。

シナリオ

Excelの講座をアレコレ行っていると、色んな機能を、何気なく教えたくなっちゃうものでして、
お店を経営している方とか、これから起業をとかの人がいたりすると、
何気なく話し始めちゃうのが、今回紹介するシナリオ。

このシナリオは、
MOS（マイクロソフト オフィス スペシャリスト）のExcel 2010 エキスパートの試験範囲でもあります。

シナリオ。日頃使うことはないと思いますので、どんな事をしてくれるのかというと、
簡単にいうと、シミュレーションなのです。

例えば、通常この価格なんだけど、キャンペーンを行うので、
キャンペーン価格をこのぐらいで設定したとしたら、いったいどのぐらいの利益が出るのか？
どのぐらいの売上が計上できるのか？というのが瞬時に切り替えられるのです。

別にキャンペーン価格を一つならいいですが、
キャンペーン価格を決める時には、様々な価格案について色々試行錯誤を繰り返すわけですね。
それで、結果的にどれが、more betterなのかを見つけやすくなるのです。

それでは、やっていくことにします。
まず、次のような表があります。

現在の価格を通常価格として、キャンペーン期間価格というのを設定した場合、
売上予測がどうなるのか？を見ることが出来るのがシナリオなんですね。
確かに、一つなら、価格をキャンペーン期間価格で入力しちゃえば、
売上予測はわかりますが、元の通常価格に戻す場合、
いちいち、入力しなおさなくちゃいけませんし、
さらに、プランを増やした場合は、煩雑になって、入力ではわかりにくくなります。

そこで、今回は、【通常価格】というシナリオと、【キャンペーン】というシナリオの
2つのシナリオを作成してみることにします。

まずは、価格のところを範囲選択しましょう。B7:B9とB13:B15を選択します。
そうしましたら、
データタブのWhat-If 分析ボタンの▼をクリックして、【シナリオの登録と管理】をクリックしましょう。

そうすると、シナリオの登録と管理ダイアログボックスが表示されます。

まずは、追加ボタンをクリックしましょう。シナリオの追加ダイアログボックスが表示されます。
変化させるセルに、先ほど範囲選択した、B7:B9とB13:B15が既に入力されていますね。

シナリオ名に、通常価格と入力をして、OKボタンをクリックしましょう。

それぞれの数字は、現在の価格ですので、そのまま使います。
引き続き、キャンペーンのシナリオを作成しますので、ここは、追加ボタンをクリックしましょう。

シナリオの追加ダイアログボックスが再び表示されますので、
今度は、キャンペーンという名前で入力したら、OKボタンをクリックしましょう。

B7を680、B8を880、B9を980、B13を120、B14を250、B15を180と入力します。
ここを、変えるわけですね。

今回は、2つのシナリオですので、ここでOKボタンをクリックしましょう。

シナリオの登録と管理ダイアログボックスが表示されます。
ここに、先程設定をしました、2つのシナリオを確認することができます。

キャンペーンを選択して、表示ボタンをクリックしてみましょう。
そうすると、価格が、キャンペーンで入力した価格に変わり、当然、数式が入っているセルは、
それに連動して、売上予測が変わったことがわかりますね。

通常価格にする場合は、通常価格を選択して、表示ボタンをクリックしますと、
通常価格に戻ります。

ですので、通常価格のシナリオを作成しておかないと、元に戻せなくなりますので、ご注意ください。
しかし、価格を100円程度しか変えていないのに、
通常価格と比べてキャンペーンの合計金額の差が結構発生しますよね。

こういうことがシナリオ使ったシミュレーションでわかるわけですね。

そして、このシナリオには、レポート機能もありますので、それも紹介しましょう。
シナリオの登録と管理ダイアログボックスの情報ボタンをクリックしましょう。

レポートの種類はシナリオの情報にチェックがついていることを確認して、
結果を出力するセルには、B4:D4を範囲選択しましょう。
そして、OKをクリックしましょう。

新しくシナリオ情報というシートに、シナリオ情報の詳細が表示されます。
これで、比較も容易になります。シナリオ。知っておいて損はないスキルだと思いますね。

Excel。どっちかの条件が合致した場合。IF関数だけじゃ面倒です。IF+OR関数

Excel。どっちかの条件が合致した場合。
IF関数だけじゃ面倒です。

IF+OR関数

Excelのネスト。関数の入れ子。関数の組み合わせの中でも、
初心者の方が、難しいとおっしゃるものの一つに、このIF+OR関数があります。
仕事でつかえるExcel講座でも、おなじみの内容を今回は紹介してみましょう。

OR関数は、AとBという条件のどちらか一方を満たしていればOKと判定する関数です。

下記の表があります。

やりたいことは、I列の判定に、
「前期中間と前期期末のどちらか一方が80点以上ならば優秀。そうでなければ空白」
という判定をしたいわけです。

そこで、あえてというか、OR関数を知らない場合、
IF関数だけを使って、求めたらどうなるのかを、やってみましょう。

I5をクリックして、まずは、IF関数のダイアログボックスを表示しましょう。

まずは、
論理式は、G5>=80
真の場合には、すでに条件を満たしましたので、”優秀”と入力します。
偽の場合は、もう一方が満たしているかどうかを判断しますので、
IF関数を再び挿入していきます。
IFのネストをしていきます。

名前ボックスの▼をクリックして、IF関数を選択しましょう。

そうすると、IFの中のIF関数ダイアログボックスが表示されてきます。

論理式は、H5>=80。
真の場合は、”優秀”
偽の場合は、””
ですね。
これでOKボタンをクリックして、完成ですね。

IF＋IFのネストでも、算出は出来ますが、ちょっとイメージしてみましょう。
２つの条件ではなくて、これが4つ５つとなったとしたら、
それだけIF関数を使用した煩雑なネスト構造をもつ、数式が出来上がってしまいます。

確かに、算出されますので、かまわないといえば、かまわないのですが、
さらに条件が増えるという場合。
そして、ネスト構造は、Excel2003までのバージョンでは、６階層しかできません。

それに、数式自体も、たった２つの条件だけなのに、
=IF(G5>=80,"優秀",IF(H5>=80,"優秀",""))
と、ひと目には、分かりにくい構造になっています。
これでは、修正が発生した場合は大変ですね。

そこで、今回のテーマであります。IF関数とOR関数のコンビを使ってみましょう。
このOR関数は単独で使用することは、まず無いと考えられます。というのも、
TUREかFALSEを返す。
つまり、その条件が成立するか？しないか？ということだけを判定する関数なのです。

OR関数を使ったことが無い方も多くいらっしゃいますので、
講座の際には、一度使い方もかねて、まずはOR関数だけで作ってみたいと思います。

では、先ほど、算出したI列のデータは削除しておきましょう。
そして、I5をクリックして、OR関数のダイアログボックスを表示させましょう。

論理式のボックスに、条件を入力していくことになりますので、
論理式1には、G5>=80。
論理式2には、H5>=80。
という様に入力していくわけです。それでは、OKボタンをクリックしてみましょう。

算出した結果は、合致しないので、FALSEと表示されました。
これでは、なんだかわからないので、
IF関数と組み合わせて、分かりやすいようにするわけです。

それでは、やっと本題である、IF+OR関数を作成していくことにしましょう。
では、先ほど、算出したI列のデータは削除しておきましょう。

I5をクリックして、IF関数のダイアログボックスを表示させましょう。

では、早速、論理式を、と展開していくところなのですが、
ココにノウハウがありまして、論理式を作り始める。
すなわち、OR関数を入れていくわけなのですが、
そうすると、このダイアログボックスの真の場合と偽の場合が空欄のまま、進んでしまい、
OR関数のダイアログボックスのOKをクリックしてしまうと、
真と偽の場合が空欄のままため、エラーが出てしまいます。

IF関数のダイアログボックスに、戻ってくることを忘れてしまう危険性を下げたいわけですね。

そこで、今回は、先に、真の場合が”優秀”。偽の場合は””。と入力しておきましょう。
こうしておけば、OR関数のOKボタンをクリックしても、エラーを防止することができます。

では、論理式のボックスをクリックしてOR関数のダイアログボックスを表示しましょう。

OR関数は、名前ボックスの▼をクリックしてその一覧の中にあればクリックしましょう。

なんで、ネストにすると、論理式4が飛び出しちゃうんだか？？
論理式には、それぞれ、先ほどもやりましたように、
論理式1には、G5>=80。
論理式2には、H5>=80。
と入力をして、OKボタンをクリックすると完成します。

条件数が増えれば増えるほど、IF関数だけでは煩雑になるので、
OR関数の有効性を確認することが出来ますね。

機会があれば是非、使ってみましょう。

Excel。両方の条件が合致した場合。IF関数だけじゃ面倒です。IF+AND関数

Excel。両方の条件が合致した場合。
IF関数だけじゃ面倒です。

IF+AND関数

Excelのネスト。関数の入れ子。
関数の組み合わせの中でも、初心者の方が、難しいとおっしゃるものの一つに、

このIF+AND関数があります。

仕事でつかえるExcel講座でも、おなじみの内容を今回は紹介してみましょう。

AND関数は、AとBという条件をどちらも満たしているものを判定する関数です。

下記の表があります。

やりたいことは、D列の判定に、
「前期中間と前期期末の両方共が80点以上ならば優秀。そうでなければ空白」
という判定をしたいわけです。

そこで、あえてというか、AND関数を知らない場合、
IF関数だけを使って、求めたらどうなるのかを、やってみましょう。

D5をクリックして、まずは、IF関数のダイアログボックスを表示しましょう。

まずは、
論理式は、B5>=80
真の場合には、80以上なので、次のIF関数を挿入します。これは、後程。
偽の場合は、80以上でないので、空白を意味する””を入力します。
では、真の場合の作業に入りましょう。
IFのネストをしていきます。
名前ボックスの▼をクリックして、IF関数を選択しましょう。

そうすると、IFの中のIF関数ダイアログボックスが表示されてきます。

論理式は、C5>=80。
真の場合は、”優秀”
偽の場合は、””
ですね。
これでOKボタンをクリックして、完成ですね。

IF＋IFのネストでも、算出は出来ますが、ちょっとイメージしてみましょう。
２つの条件ではなくて、これが4つ５つとなったとしたら、
それだけIF関数を使用した煩雑なネスト構造をもつ、数式が出来上がってしまいます。

確かに、算出されますので、かまわないといえば、かまわないのですが、
さらに条件が増えるという場合。
そして、ネスト構造は、Excel2003までのバージョンでは、６階層しかできません。
それに、数式自体も、たった２つの条件だけなのに、
=IF(B5>=80,IF(C5>=80,"優秀",""),"")
と、ひと目には、分かりにくい構造になっています。
これでは、修正が発生した場合は大変ですね。

そこで、今回のテーマであります。
IF関数とAND関数のコンビを使ってみましょう。
このAND関数は単独で使用することは、まず無いと考えられます。
というのも、TUREかFALSEを返す。
つまり、その条件が成立するか？しないか？ということだけを判定する関数なのです。

AND関数を使ったことが無い方も多くいらっしゃいますので、
講座の際には、一度使い方もかねて、まずはAND関数だけで作ってみたいと思います。

では、先ほど、算出したD列のデータは削除しておきましょう。
そして、D5をクリックして、AND関数のダイアログボックスを表示させましょう。

論理式のボックスに、条件を入力していくことになりますので、
論理式1には、B5>=80。
論理式2には、C5>=80。
という風に入力していくわけです。それでは、OKボタンをクリックしてみましょう。

算出した結果は、合致しないので、FALSEと表示されました。
これでは、なんだかわからないので、IF関数と組み合わせて、分かりやすいようにするわけです。

それでは、やっと本題である、IF+AND関数を作成していくことにしましょう。
では、先ほど、算出したD列のデータは削除しておきましょう。

D5をクリックして、IF関数のダイアログボックスを表示させましょう。

では、早速、論理式を、と展開していくところなのですが、
ココにノウハウがありまして、論理式を作り始める。
すなわち、AND関数を入れていくわけなのですが、そうすると、
このダイアログボックスの真の場合と偽の場合が空欄のまま、進んでしまい、
AND関数のダイアログボックスのOKをクリックしてしまうと、
真と偽の場合が空欄のままため、エラーが出てしまいます。

IF関数のダイアログボックスに、戻ってくることを忘れてしまう危険性を下げたいわけですね。

そこで、今回は、先に、真の場合が”優秀”。偽の場合は””。と入力しておきましょう。
こうしておけば、AND関数のOKボタンをクリックしても、エラーを防止することができます。

では、論理式のボックスをクリックしてAND関数のダイアログボックスを表示しましょう。

AND関数は、名前ボックスの▼をクリックしてその一覧の中にあればクリックしましょう。

なんで、ネストにすると、論理式4が飛び出しちゃうんだか？？
論理式には、それぞれ、先ほどもやりましたように、
論理式1には、B5>=80。
論理式2には、C5>=80。
と入力をして、OKボタンをクリックすると完成します。

条件数が増えれば増えるほど、IF関数だけでは煩雑になるので、
AND関数の有効性を確認することが出来ますね。

機会があれば是非、使ってみましょう。

Excel。ゴールシークを使って数値を予想してみよう。

Excel。ゴールシークを使って数値を予想してみよう。

ゴールシーク

仕事でつかえるExcel講座で、最近ビックデータからの流れなのか、
Excel2010の分析機能である、What-If分析に関して、ご質問が多くなってきたように感じます。
そこで、今回は、What-If分析の中の、【ゴールシーク】に関して、ご紹介していきましょう。

この【ゴールシーク】は、数式の計算結果を指定して、その結果をどうやったら出来るのか？
を逆算する機能です。
ゴールシークを知っているだけでも、販売店さんなどは、重宝するのでないでしょうかね？
それに、この【ゴールシーク】は
、MOS（マイクロソフト オフィス スペシャリスト）のExcel2010　Expertの
試験範囲でもありますので、
Expert。すなわち上級資格の取得を考えている人も必須の機能といえます。

それでは、早速ご紹介。
下記の表があります。

この日の売上予測の売上金額を10万円にするとしたら、
あと、いったいどれだけのA定食を販売したらいいのでしょうか？
というケースがあったとします。
そこで、登場するのが、ゴールシークなのです。
このゴールシーク。馴染みがありませんので、どこのタブに入っているのかというと、
データのデータツールにある、

What-If分析

の中にあります。

では、どうやって使うのか、やってみることにしましょう。
まずは、希望の数値のセル。すなわち、D4をクリックします。
ここの数値を10万円にしたい訳ですね。
そして、ゴールシークをクリックします。

数式入力セルは、クリックしてあったD4がすでに入力されています。
目標値は、10万円にしたいので、100000と入力します。
変化させるセルは、A定食の数量なのでC7をクリックします。
なお、クリックしますと、絶対参照が設定されて、$C$7と変わりますが、
絶対参照設定のままでOKです。

あとは、OKボタンをクリックして、出来上がりです。

さらに、OKをクリックして終了。

結果が、小数点で表示されてきましたので、
A定食のC7と合計のC10に桁区切りのカンマのボタンを押しましょう。

これで完成ですね。

A定食を30食販売することができれば、目標達成することがわかりました。
このように、ゴールシークを知っているだけで、
このようなちょっと、高度な計算でも、比較的簡単に算出してくれるわけです。

今回のゴールシークは、
MOS（マイクロソフト オフィス スペシャリスト）のExcel2010　Expertの試験範囲でもありますので、
よく確認をしておきましょう。

資格取得の有無にかかわらず、知っておいて損はない機能ですので、覚えておきたいところです。

Excel2010。SUMIF関数では大変なので、小計で集計。その１

Excel2010。SUMIF関数では大変なので、
小計で集計。その１

小計

Excel。ビッグデータが花盛りの昨今、仕事で、店舗ごとに集計したいんだけど、
というリクエストがあって、よくご質問に対応したり、講座でもご紹介しているものの中に、

小計という機能があります。

Excel2003では、集計なんですが、Excel2007以降では小計になっていますが、
ここでは、小計でいきたいと思います。

今回の、小計は、非常に便利なのですが、最初にある処理をしないで始めると、
結果が、【大惨事】になってしまいます。
その点も含めて、早速、ご紹介していきましょう。

まず、このようなデータがあります。データの件数は、284件です。

このデータを基に、店舗別に金額の合計を算出したいのが今回の目的です。
以前紹介した、SUMIF関数やDSUM関数を使ってもいいのですが、
それだと、店舗数分作業しないといけないわけです、店舗数が少なければいいでしょうけど、
多くなると、これらの関数を使用するのは、ちょっと効率が悪いわけですね。

そこで、登場するのがデータタブにある、小計というわけです。

操作自体は、簡単なのですが、先ほども書きましたように、最初が肝心なのです、
それは、

【合計する基準を並び替えすること】。

つまり、今回は、店舗で並び替える必要があるのです。ここがポイントになります。

それでは、B1の店舗のセルをクリックして、データタブの並び替えとフィルターにある、
昇順ボタンをクリックしましょう。
別に降順でも構いません。
ちなみに、昇順とは、小さい順。五十音順。A～Z順ですね。

すると、さいたま～宇都宮～と並んだのが確認できますね。

そうしましたら、A1のセルをクリックしてデータタブの小計のボタンをクリックします。
A1じゃなくても、この表の中のどのセルでもアクティブにしていればOKですが、
わかりやすく、左上のセルであるA1を使っております。

クリックすると、集計の設定ダイアログボックスが表示されます。
小計を押したのに、集計？わかりにくい気もしますが…

グループの基準は、今回並び替えたフィールドが、基準ですよね。ですので、店舗。
今回は、店舗ごとに集計したいわけですから。
集計方法は、今回は、合計。
集計するフィールドは、金額にチェックマークがついていることを確認します。
なお、この集計するフィールドは、Excelが勝手に判断しますので、
最初から最後まで、確認する癖をつけておきましょう。
でないと、関係ないフィールドの集計をしちゃいます。

あとは、そのままOKボタンをクリックします。コレで完成。

行番号の左側に1～3のアウトラインが表示されましたね。
このボタンをクリックするとデータが折り畳まれます。
では、今の3の状態は、全展開です。それでは、2をクリックしてみましょう。

おおっ、店舗ごとに集計されているのがわかりますね。
ちなみに、集計という文字が店舗のセルに表示されていますが、
作業上、邪魔な場合は、空白で置換しちゃえばいいでしょう。

それでは、今度は、1をクリックしてみましょう。

おおっ、今度は、全店舗集計ですね。
SUMIF関数やDSUM関数を使うよりも、楽に店舗ごとに集計ができましたね。

それでは、ここで、なぜ、最初に、

基準で並び替えないといけないのか

をやってみることにしましょう。

まず、データがあります。A1をクリックしておきます。

このまま、データタブの小計をクリックしたら、どうなるのでしょうか？
まず、集計の設定ダイアログボックスが表示されますので、先ほどと同じ設定をしましょう。

それでは、OKボタンをクリックしたらどうなったでしょうか？

あれれ？集計しているけど、一行毎に？？
実は、この小計。基準であるデータが同じものが続いている間は、
それがグループだと判断してくれて、集計してくれるというわけです。
ですので、並び替えをして、データをまとめておきませんと、
このようなことになってしまう訳ですね。

ということで、最初に基準で並び替えることが重要になるわけです。
余談ですが、データベース関係は、元に戻すボタンが使えなくなることがありますので、
元のデータを残しておくことをお勧めします。

それか、通し番号を振っておくといいでしょう。