修正ファイル備忘記録2019/07/21ABC分析:エクセルでパレート図を描いてみよう!(その1)皆さん、こんにちは。(^▽^)/多くの方は、休日でのんびりした時間をお過ごしでしょうか?昨日、書いていましたように、今日は、「ABC分析」の練習データをアップさせていただきましたので、生徒さん向けに解き方の手順を書いてみたいと思います。色んなやり方がありますので、以下は一例です。1.下の練習用データを開きます。2.ABC分析は売上高の高い順に並べ替えておく必要がありますので、B列を降順にソートします。※セルB4をクリックして、「並べ替えとフィルター」の「降順」をクリックします。3.次に、売上高累計を求めます。(1)セルC4をクリックして、「Σ」ボタンをクリックします。※「=SUM(B4)」と表示されたかと思います。※合計をする数値が左側にしかなかったからですね。(2)そのまま、「:」キーを押します。※「=SUM(B4:B4)」と表示されたかと思います。「:」を使用したのは、〇~〇という範囲指定にしたかったからです。(3)引数の最初の「B4」内をクリックして、「F4」キーを押します。※「F4」キーを押したのは、先頭のセルB4を絶対参照にしたかったからです。(4)セルに数式を確定するために「✔」をクリックします。[Enter]を押さずに、「✔」をクリックしたのは、後で数式をコピーするために、セル移動をさせたくなかったからです。(5)セルC4のフィルハンドルをダブルクリックします。※これで、C列に「=SUM($B$4:B4)」、「=SUM($B$4:B5)」、「=SUM($B$4:B6)」、・・・という数式が入ります。※最後のフィルハンドルをダブルクリックは、ご存知のように、一気に数式をコピーしたかったからです。脱線しますが、最後のフィルハンドルをダブルクリックという操作はエクセル初級のテキストに記載してあり、エクセルを学習された生徒さんならどなたでもご存知だと思いますが、「10年以上エクセルを使っています」と豪語されたビジネスマンの方が意外とご存じなかったりしましたので、ご自分の知識に自信を持たれてください。(^^)これで、売上高累計が求まりました。最後のC11の6,248,000は、B列の売上高の合計と一致するはずですので、B列を選択(列番号をクリックでOK)してみて、画面下部のステータスバーに「合計:6,248,000」と表示されたのを確認してみてください。4.次に、売上累計比率を求めます。これは、簡単だと思いますが、セルD4に「=C4/$C$11」という数式を入れれば良いですよね?C11の数値が売上高の合計だったことは先ほど確認していただきましたので、各商品の売上高を総額で割れば、比率が求まります。総額のセルは絶対参照にしたいので、「F4」キーを使っていますね。あ、3で補足をし忘れましたが、各セルは、予め書式設定をしておきましたので、C列はカンマ表示、D列は%表示で、小数第1位までの表示になったかと思います。5.次に、パレート図を描くこととは関係がありませんが、E列のランクを出してみます。70%以下ならA,70%超90%以下ならB、90%超ならCという数式を入れる訳です。ここで、全商1級で出題される数式の書き方で書いてみたいと思います。(1)セルE4を選択後、「fx」をクリックして、「IF」関数を選びます。(2)論理式に、「もしD4が70%以下なら」という数式を入れます。(D4<=70%)(3)[Tab]キーで「値が真の場合」に移動し、「A」と入力します。(4)[Tab]キーで「値が偽の場合」に移動します。(5)次は、70%超90%以下ならという条件を追加する必要があるので、関数を選択する場所から、再度「IF」関数を選びます。(関数を選択する場所とは、×や✔やfxがある左側です。現在、IFと表示されているはずですが、▼からIFを選びます)これで、新たなIF関数のダイアログが開いて来ました。ここで安心する訳には行きません。(笑)70%超90%以下という条件を指定するために、「AND」関数を登場させる必要があります。(6)そこで、「論理式」にカーソルがある状態で、またまた、関数を選択する場所から「AND」関数を選びます。一覧にANDがなかったら、その他の関数をクリックして探してください。これで、AND関数のダイアログが表示されました。(7)「論理式1」に「D4>70%」と入力します。セル番地は直接セルをクリックでOKです。(8)[Tab]キーで「論理式2」に移動し、「D4<=90%」と入力します。これで、D4が70%超かつ90%以下という条件が設定できました。(9)ここで、先程のIF関数に戻る必要があるので、数式バーの2つ目の「IF」の文字列内をクリックします。これで、2つ目のIF関数のダイアログに戻って来ました。(10)「値が真の場合」の欄内をクリックし、「B」と入力します。これで、「もし、D4が70%以下じゃなくて、70%超90%以下なら「B」と表示してね。(11)[Tab]キーで「値が偽の場合」の欄に移動し、「C」と入力します。最後は、「上記の何れでもなかったら(つまり、D4が90%超なら)「C」と表示してね。」という命令を出したことになります。~!これでやっと数式が完成です。(笑)(12)OKをクリックします。(13)セルE4のフィルハンドルをダブルクリックして、表は完成です。ここまでで、疲れちゃいましたよね。(;^_^Aこれで、C商品とH商品とA商品が重点管理商品だということが分かりますが、こ~んな面倒な数式を入れるのはウンザリですよね?ということで、「ランク」の列は求めなくても、グラフで求めてみることにしましょう。長くなってしまったので、続きは明日にさせていただきますね。(;^_^Aちなみに、これでも、3つの関数がネスト(入れ子)されたわけですが、全商1級の問題は、多いと関数が10個くらいネストします。ほ~んと、ウンザリですが、それを高校生の子が学習しているんだと思うと驚きです。上級のテキストまで学習していただいても載せていないような関数もたくさん登場します。こんな問題は、本当に序の口の簡単な部類で、もう、ウンザリするほどの問題が次々と登場します。(;^_^A今どきの高校生は大変だなぁと思う反面、自分自身も、今どきの商業高校生になってみたくなります。(笑)1年生から、本当にたくさんの資格にチャレンジするんですね?多い月は、毎週何らかの検定試験を受けているようです。今は、小寺先生と一緒に月1(1~2時間)くらいのペースで勉強していますが、10個程度の関数のネストを学習すると、このあたりの問題は非常に簡単な部類に属します。昔、「ソルバーって何ですか?」って質問したら、講師から「ソルのバーですよ」って答えられたという笑い話まで思い出すほど、「ソルバー」とか、この「ABC分析」とか可愛いもんだよ。(笑)と話し合ったりしています。お時間のある方は、是非、下のファイルをダウンロードしてチャレンジしてください。「サイトマップ」よりご覧いただけます。管理人の趣味ブログ単発の貸出はしておりません。約18畳:1室50,000円/月)新規開校のお教室様は、利益が出るまでご相談に応じさせていただきます。お問い合わせよりお願いたします。編集トップへ戻る閉じる。