エクセルでファンチャートを作成する具体的な方法を解説

エクセルで「ファンチャート」を作成後、伸び率の高い製品を見つけ出す方法を解説します。

効果的な販促を計画する場合、

どのような製品が売上を伸ばしているかを特定する必要があります。

なぜなら、

単純に売上が伸びているからといって、やみくもに販促計画を立てるのは失敗だからです。

この記事では、

「ファンチャート」を作成して、各製品を比較することで、正しく分析する方法を解説します。 

こんな人におすすめ↓

※自社製品の売上を分析して、伸びてきている製品を特定したい人

※逆に落ち込んできている製品を特定したい人

※販促計画を立てたい人。

この記事から分かること ↓

※ファンチャートとは何か？注意すべき2つのポイント

　・どの時点から分析するか？

　・どのくらいの期間のデータを対象とするか？

※エクセルでファンチャートを作成する具体的3つの手順

　①データを整理する

　②ファンチャートを作成する

　③作成したファンチャートを整える

　　・「タイトル」と「凡例」を設定する

　　・「データラベル」と「軸」の最小値を設定する

本記事で使用する例題と3つの着目ポイント↓

複数の製品（A商品〜I製品）の売上を分析します。

以下3つのポイントに着目

○現在売上が伸びている製品、減少している製品を特定。

　→グラフ化によって視覚化することで、パッと見ただけでわかります。

○売上が伸びてきている商品の特定は、単純な数量や金額ではわからない。

　→売上金額が少なくても伸びている場合があります。どの時点から比較するかが大切。

　→季節変動が影響があるような商品もあるので、最低2年間の集計データがおすすめ。

○ファンチャート分析には百分率で表示する。

　→比較は「％」が基本。単純な数値の大小をに関係なく比較することができる。

完成形がこれです↓

□ファンチャートとは何か？注意すべき2つのポイント

ファンチャートとは、ある基準となる時点を「100%」とし、それ以降の数値を基準となる時点に対する百分率で表示して、「折れ線グラフ📈」で表したものです。

グラフが扇（ファン）を広げたように見えることから「ファンチャート」と呼ばれています。

ファンチャートでは、数値の伸びや落ち込みなどの変化を「百分率」で表すため、金額の大小に関わらず、グラフの傾きによって視覚化され把握できます。

例えばこんな感じです↓（比較してます）

〇単純な時系列の売り上げグラフ↓

〇ファンチャート↓

わかりやすくするためにちょっとオーバーに作成しています。

単純な時系列のグラフではよくわかりませんが、「ファンチャート」にすると一目でどの製品が伸びているかわかります。

上記でも説明してますが、同じデータから作成したグラフです。

違いは「売り上げ数値のまま」か、「百分率（％）にしている」かの差です。

ポイント1：どのくらいの期間のデータを対象とするか？

分析データは大量にあればある程いいです。

「しかし、そうはいっても分析が大変なので、、、。」と思いますよね。

なので、季節変動が影響するような製品も考慮して、最低でも2年分のデータがあるといいと思います。

ポイント2：どの時点から分析するか？

基準となる時点の売上の状況によっては、伸びや落ち込みが大きく見えたり、小さく見えたりします。

〇伸びや落ち込みが大きく見えるケース　

→  データのスタート時点の売上が悪いケース

〇伸びや落ち込みが小さく見えるケース　

→  データのスタート時点の売上が良いケース

正しい分析ができない可能性があるので、注意しましょう。

□エクセルでファンチャートを作成する具体的3つの手順

ファンチャートを作成するには、「データを整理する」「ファンチャートを作成する」「作成したファンチャートを整える」3つの流れに沿って作成する必要があります。

一つずつ解説します。　

①データを整理する

製品の売上高を使って、売上の伸び率を算出します。

手順①：「伸び率」という項目を作成して数式を入れる。

「数式：（＝B3/＄B3）」

↓

手順②：「ホーム」タブから「％」をクリック

↓

手順③：小数点第一位まで表示する

↓

手順④：セルをコピーして貼り付ける

↓

完成

②ファンチャートを作成する

ファンチャートを作成します。

手順①：「Ctrl」を使って、範囲選択する。

↓

手順②：「挿入」タブから「折線グラフ」を選択する。

↓

手順③：「グラフのデザイン」タブの「行/列の切り替え」タブをクリック

↓

完成

③作成したファンチャートを整える

・「タイトル」と「凡例」を設定する

手順①：「グラフタイトル」をダブルクリックして、タイトル名を打ち変える。

↓

手順②：「デザイン」タブの「グラフ要素を追加」→「凡例」から「右」を選択

・「データラベル」と「軸」の最小値を設定する

手順①：「デザイン」タブの「グラフ要素を追加」→「データラベル」から「左」を選択

↓

手順②：グラフの縦軸をダブルクリックして、最小値を設定する

↓

完成

□まとめ

※ファンチャートとは

→売上の変化を、グラフで視覚化したもの

→「どの時点から分析するか」「どのくらいの期間のデータを対象とするか」2つのポイントに注意

※エクセルでファンチャートを作成する方法

→3つの手順に沿って作成します

　①データを整理する

　②ファンチャートを作成する

　③作成したファンチャートを整える

エクセルでZチャートを作成する具体的な方法と傾向分析方法を解説

Zチャートを利用して、売上の全体的な傾向を把握する方法を解説します。

（エクセルでZチャートの作成方法）

こんな人におすすめ↓

※売上数値から販促計画を立てる人

※売上傾向を把握して新商品を立案する人

この記事から分かること↓

※Zチャート分析の考え方

※エクセルでZチャートを作成する方法

□Zチャートとは？

例えば、

「月々の売り上げ」「売り上げ累計」「年次移動」などの3つのデータをグラフにしてみます。↓

それぞれの項目を「折線グラフ」にて組み合わせることで、「Z」のような形のグラフができました。

これが、「Zチャート」と呼ばれる理由です。

Zチャートで分かることは以下の3つです↓

・単純な売り上げの推移

・当年の売り上げの累計（売り上げ累計）

・直近の売り上げの累計（移動年計）

Zチャートの分析方法

Zチャートの形は大きく3つのパターンに分かれます。

これによって、全体の傾向を判断することができます。

○横ばい型

現状を維持していて、前年から比べても特に変動がない状態です。

とても綺麗な「Z」の形になっています

この形を「安定している」と考えるか、「停滞いている。伸び悩んでいる。」と考えるかは分析者や企業の判断になります。

○成長型

前年と比べて売り上げが伸び、増加傾向にある状態です。

「Z」のグラフが右上がりになっています。

○衰退型

前年と比べて売り上げが下がり、衰退傾向にある状態です。

「Z」のグラフが右下がりになっています。

□「Zチャート」の具体的な作成方法

以下の手順に沿って解説します。↓

①必要なデータを揃える

　・月々の売り上げデータ

　・売り上げ累計

　・移動年計　

②Zチャートを作成する

③グラフを整える

　・タイトルを設定する

　・縦軸ラベルを設定する

今回の例題↓

商品Aの過去の売り上げからZチャートを作成します。

（2019年4月〜2020年3月までのデータを使用）

完成形がこれです↓

①必要なデータを揃える

Zチャートを作成するには、3つのデータが必要になります。

・月々の売り上げデータ

月々の売上を記録したデータです。

（2019年4月度～2020年3月度）

 ・売り上げ累計

月々の売り上げデータを累積（積み上げ）したデータです。

具体的には、

「その月の売り上げ」に「前の月の売り上げ」を合計した値になります。

月の売り上げが一定の場合は「直線」、売り上げが増加している場合は「お椀型」、逆に減少している場合は「弓形」のグラフになります。

（↑画像は少し極端ですので、あくまでもイメージです。）

手順①：売り上げ累計を求める。（2020年度の5月〜2020年度の3月まで）

・移動年計

その月の売り上げに「過去11ヶ月分」のデータを加えた、「その月の直近の年分の売り上げ」の累計値です。

これにより、大まかな変化の傾向を確認できます。

画像

※分析方法は、上記の「Zチャートの分析方法」を確認ください。

手順①：移動年計を求める。（2020年度の5月〜2020年度の3月まで）

②「Zチャート」を作成する。

①で整理したデータを踏まえて、Zチャートを作成します。

手順①：作った表に「フィルター」をかけて、「2020年度」のみ表示する。

↓

手順②：「挿入」タブから「折れ線/面グラフ」の「マーカー付き折れ線」を選択

③グラフを整える

・タイトルを設定する

手順①：「デザイン」タブの「クイックレイアウト」から「レイアウト1」を選択

↓

手順②：軸ラベルをクリックして入力し直す

・縦軸ラベルを設定する

手順①：縦軸ラベルをダブルクリックして、単位を変える。

（必要なければそのままでもOKです）

□まとめ

※Zチャートは全体の傾向を把握・分析するのに適してる

※Zチャートパターンは3つに分かれる。

→「横ばい型」「成長型」「衰退型」

※Zチャートを作成するには以下の手順が必要

①必要なデータを揃える

　・月々の売り上げデータ

　・売り上げ累計

　・移動年計　

②Zチャートを作成する

③グラフを整える

　・タイトルを設定する

　・縦軸ラベルを設定する

エクセルでレーダチャートを作成し、ユーザーニーズを分析する方法

エクセルでレーダーチャートを作成して，ユーザーニーズを「把握・分析」する方法を解説します。

こんな人におすすめ↓

※新商品を開発した後の結果を分析したい人

※お客様のアンケートなどを分析したい人

この記事で分かること↓

※レーダーチャートについて（分析方法まで）

※エクセルでレーダーチャートを作成する方法

□レーダーチャートとは？

レーダーチャートとは，複数の項目の軸を比較するのに適したグラフです。

グラフの軸・目盛・ラベルが正多角形に配置されます。

こんな感じ↓

蜘蛛の巣のような形をしていることから、「クモノ巣」グラフとも呼ばれます。

データは多角形の上の点で表現され、各データの間を直線で結びます。

複数の項目の値のバランスを見るために利用します。

具体的な分析方法

レーダーチャートは視覚的に見て一瞬で傾向を分析できるのが特徴です。

例えば、以下の表を用いて解説します↓

「デザイン」「味」「持ちやすさ」「値段」「宣伝方法」の5つの項目で「A」「B」「C」の3つの商品を3社で比べています。

パッと見て分かる3つの特徴（青のグラフが自社です）

・①Aは他社と比べて「デザイン性」が低い　←→  「味」は高い

・②Bは他社と比べて「デザイン性」と「味」が低い　←→  「飲み口」は高い

・③Cは他社と比べて「デザイン性」と「宣伝方法」が低い　←→  「味」は高い

ここから分かる分析結果（簡単に）

・①〜③の全てにおいてデザイン性が低い　→  デザイン変更する。

・Bは、「味」が低い　→  味を見直す。  

・Cは、「宣伝方法」が低い　→  宣伝方法を検討する。

よくある項目で商品を比較してみました。

レーダーチャートを作成すると、パッと見ただけでもこれだけの情報がわかります。

□エクセルでレーダーチャートを作成する方法

以下の手順に沿って解説していきます。↓

①データを整える

②レーダーチャートを作成する。

③作成したレーダーチャートを整える

　・系列の順番を入れ替える

　・軸の最大値と最小値を設定する

　・チャートを完成させる

①データを整える

上記でも記載しましたが、レーダーチャートは、複数の項目の値のバランスを見るために利用するのでグラフです。

そのため、全ての項目の値を整える必要があります。

例えば、以下の表を見てください↓

これをレーダーチャートにすると↓

設問の解答が3段階評価（1・2・3）と5段階評価（1・2・3・4・5）の2種類があります。

当たり前の話ですが、3段階評価と5段階評価では、数字の価値が違います。

こうなると正しい分析ができないので、3段階評価を5段階評価に換算します。

変換する具体的な方法はこちら↓

3段階評価を５段階評価に変換します。

1→「1」

2→「3」

3→「5」

これで、数値の価値が平等になります。

データ整理は分析の初歩ですが、結構できてないポイントです。 

②レーダーチャートを作成する

具体的な例題を設定してレーダーチャートの作成手順を解説します。

上記①の「データを整える」は終わっている状態です。

例題↓

※一つの商品を「AからE」の5つの項目で売上を分析し、自社を含めた3社で比較します。

完成形はこんな感じ↓

手順①：範囲選択

↓

手順②：「挿入」タブから「ウォータフォール」の「塗りつぶしレーダー」を選択

↓

完成

 ③作成したレーダーチャートを整える

・系列の順番を入れ替える

最初に表示されたレーダーチャートは、データの並び順に沿って下から上に重なって表示されています。

競合他社と比べる時に使うことが多いので、自分の会社の表が1番下に表示されたりすると、分析しにくいです。

どんなデータでも自分のデータを1番上に表示して、見やすくしましょう。

手順①：「デザイン」タブの「データの選択」を選択

↓

手順②：「自社」をクリックして、「↓」を2回クリックして1番下に表示

↓

完成

・軸の最大値と最小値を設定する

レーダーチャートの軸のメモリは、自動的に設定されます。

しかし、アンケート調査などの項目ではそれが必ずしも正しいとは限りません。

（例えば、3段階評価もあれば5段階評価もある）

今回は5段階評価に変更してみます。

手順①：「グラフ要素を追加」から「軸」の「その他のオプション」を選択

↓

手順②：「軸の書式設定」から最小値を「1」、最大値を「5」を入力

↓

完成

・チャートを完成させる2つのポイント

見やすいように2つの項目を修正します。

手順①：凡例の位置変更

↓

手順②：「グラフタイトル」を変更する

↓

完成

□まとめ

※レーダーチャートは、視覚的に見て一瞬で傾向を分析できる。

※エクセルでレーダーチャートを作成する手順

①データを整える

②レーダーチャートを作成する。

③作成したレーダーチャートを整える

　・系列の順番を入れ替える

　・軸の最大値と最小値を設定する

　・チャートを完成させる

エクセルでPPM分析をする具体的な方法。3つの手順で解説します。

今回は、エクセルでできるＰＰＭ分析について解説します。

（プロダクト・ポートフォリオ・マネージメント）

こんな人にオススメ↓

※商品企画部門などで、新商品を企画する人

この記事でわかること↓

※ＰＰＭ（プロダクト・ポートフォリオ・マネージメント）分析の考え方

※エクセルで分析するやり方（具体事例）

　→バブルチャート作成方法

□ＰＰＭ（プロダクト・ポートフォリオ・マネージメント）分析とは

商品がライフサイクルのどの段階にあるかを判定する分析方法です。

例えば、

下の表のようなデータがあるとします。↓

2016年～2020年までのある商品の販売データを「シェア率」「売上げ伸び率」「売上金額」の３つのカテゴリーに分けてまとめています。

このデータをもとに、PPM分析を行うとこんな感じになります↓

このグラフをからわかる分析結果は、

①商品の販売開始時はデータが左下にあり（2016年）、そこから徐々にデータが右上に上がっており、かつ円も大きくなっている為（2018年～2019年）、販売が軌道に乗っていることがわかる。

②最新のデータでは（2020年）、円が小さくなり、位置が左下に戻ってきている為、今後の販売を見直す必要がある。

→販売停止？継続？新商品の立ち上げ？等、、、。

このように、データをグラフ化して「４つの領域」に分類することで、現在の商品が市場においてどのようなポジションにあるかを判定します。

「４つの領域」の考え方↓

□エクセルでPPM分析を行うの具体的な方法

上記の内容を踏まえて、具体的にPPM分析を行う手順を解説します。

以下の手順に沿って解説していきます。↓

①分析用データを準備

②バブルチャートを作成

③作成したバブルチャートを整える３つの方法

　・凡例を表示

　・「タイトル」や「軸」などのラベルを表示

　・データラベルを表示

①分析用データを準備

ここでは例題として適当にデータを作成してます。↓

ここから以下「３つの項目」を追加します。

・各製品のマーケットシェア率（「C列」-「D列」）

・各製品の売上げ伸び率（「C列」/「B列」-1）

・各製品の直近12か月の売上げ合計（「C列」と同じ）

追加した３つの項目が重要になります。

②バブルチャートを作成する

バブルチャートを作成するには3つの列を含むセルを範囲指定します。

手順①：範囲指定

↓

手順②：「挿入」タブからグラフを選択

↓

完成

③作成したバブルチャートを整える3つの方法

・凡例を表示

手順①：作成したグラフ選択して、グラフ要素を追加を選択

↓

手順②：「グラフのデザイン」から「データの選択」を選択

↓

手順③：「編集」を選択

↓

手順④：「系列の編集」の「系列名」に「G1」セルを入力

↓

完成

・「タイトル」や「軸」などのラベルを表示

グラフツールの「クイックレイアウト」タブを使って整えます。

（一瞬です）

・データラベルを表示する

バブルチャートでは、X軸、Y軸ともに数値であるため、一つ一つのバブルが何を指しているかわかりません。

そこで、データラベルを活用して名前を表示させます。

手順①：「グラフの要素を追加」から「データラベル」の「中央」を選択

↓

手順②：表示された値を2回クリックして「セルの選択」を選択

↓

手順③：対象のセルを選択（今回は「A７」を選択）

↓

手順④：残りのバブルも同様に手順②～③を繰り返す。

↓

完成

□まとめ

※PPM分析とは

商品がライフサイクルのどの段階にあるかを判定する分析方法

※バブルチャートを作成

「マーケットシェア率」「売上げ伸び率」「売り上げ合計」の３項目から作成

※バブルチャートを見栄え良く整える３つのポイント

・凡例を表示

・「タイトル」や「軸」などのラベルを表示

・データラベルを表示

フィルターで表示されたデータを集計する方法

フィルター機能を使って、表示されたデータのみ集計する方法を解説します。

この記事から分かること↓

※フィルター機能の使い方

※「SUBTOTAL（サブトータル）」関数の使い方

こんな人におすすめ↓

※指定したデータのみ集計したい人

例題：フィルターで表示された金額を合計します↓

完成↓

□フィルター機能の使い方

フィルター機能を使うと、表のデータを絞り込んで表示することができます。

※とても簡単にできます。※

手順①：範囲選択

↓

手順②：ショートカットキー：「Ctrl」＋「Shift」＋「L」

具体的な使い方はこの記事でも紹介しています↓

djiro.hatenablog.com

□「SUBTOTAL（サブトータル）」関数を使ってデータ集計する

「SUBTOTAL（サブトータル）」関数は、集計方法の条件を指定することでさまざまな集計を求めることができます。

「合計」の他にも「平均」や「積」なども1つの求めることができます。

手順①：対象セルに「SUBTOTAL（サブトータル）」関数を入力する。

↓

手順②：フィルター機能にて「合計したい項目」を選択する。

↓

完成

「SUBTOTAL（サブトータル）」関数：11種類の集計方法

「SUBTOTAL（サブトータル）」関数には、11種類の集計方法があります。

集計方法は、「番号」にて指定します。

こんな感じ↓

□上達のためのワンポイント：小計と合計をミスなく求める

Excelで数値データを合計するときはおなじみの「SUM」関数を使います。

しかし、

複数の小計が含まれる表などで「SUM」関数を使うと、全体の合計を求める時に、都度数式を修正しなければなりません。

↑一つひとつ小計を「SUM」関数で求めて、その小計をさらに「SUM」関数で合計を求めなければなりません。

これでも正しい集計が行えますが、合計を求める数式で足し合わせるセルを、間違えないように修正する必要があります。

手間も時間もかかる上に、修正し忘れるミスも起こりがちです。

こうした表での集計にも、「SUBTOTAL」（サブトータル）関数が使えます。

手順①：「SUBTOTAL」関数で各「小計」を求める

↓

手順②：「SUBTOTAL」関数で合計を求める。

※最大のメリット※

「数式をその都度修正しなくていい」というのもメリットですが、「SUBTOTAL」関数を使って合計する最大のメリットは、「項目が増えても自動的に合計してくれる」機能です。

範囲を広げるだけなので、ミスも少なくかなり時短になります。

注意：「SUBTOTAL」関数と「SUM」関数の併用は厳禁

1つの表内で「SUBTOTAL」関数と「SUM」関数を併用するのは厳禁です。

「SUBTOTAL」関数で小計を除外して集計できるのは、「SUBTOTAL」関数が入力されているセルだけ。

小計行にSUM関数が入力されていると、正しく集計されない点には注意が必要です。

□まとめ

※オートフィルター機能：「Ctrl」＋「Shift」＋「L」

※「SUBTOTAL（サブトータル）」関数：表示されているデータを集計する

＝SUBTOTAL（集計方法，範囲）

指定した集計方法で範囲内に表示されたデータだけを対象に集計する。

エクセルでABC分析を行う具体的方法。パレート図作成手順から分析方法まで解説します。

「ABC分析」を使って、優先順位をつける方法を解説します。

こんな人におすすめ↓

※営業成績から顧客のランク付けをして、優先順位をつける

※在庫などの資材管理を経費からランク付けして、予算分配を考える。

この記事で分かること↓

※ABC分析を理解できる

※ピポットテーブルの使い方

※パレート図の作成方法

例題↓

「顧客別売上シート」のデータから、ピポットテーブルを作成します。

それを利用して「パレート図」（ピポットグラフ）を作成します

完成図イメージ↓

□「ABC分析」とは

ABC分析とは、分析対象の数値が高い順にランク付けをして管理・分析することです。

これは、数値が大きいアイテムに対して重点的に経営資源を配分することで、売上や利益を効率的に向上させることができるという考え方に基づいています。

ABC分析は非常にシンプルですが、企業の経営戦略の立案やマーケティングのためのデータ分析の基本であり、初期分析としてよく使う手法でもあります。

簡単な分析方法

分析するのに必要な概念が「累積比率」というものです。

「累積比率」とは、値を上位から累計していき、その結果が全体の何%にあたるかを調べるものです。

この比率によって「A〜C」までランク付けします。

下記に簡単にまとめます↓

○全体の70%を占める項目を「A」

○70%〜90%を占める項目を「B」

○90%〜100%を占める項目を「C」

□パレート図の作成方法

パレート図を作成するには、2つの工程を経て作成します。それが、「ピポットテーブル」と「編集」です。

「ピポットテーブル」は累積比率を示すために作成します。

「編集」は各取引先の売上を棒グラフで表示して、作成した「累積比率」を組み合わせます。

「ピポットテーブル」を作成して、累積比率の軸を作成する

手順①：「顧客別売上」シート表を選択

※ショートカットキー　→ 「Ctrl」＋「A」

↓

手順②：「ピポットテーブル」を選択

↓

手順③：「OK」をクリックして「ピポットテーブル」を挿入

↓

手順④：「ピポットテーブのフィールド」で編集

1，「顧客」を「行」にドラッグ&ドロップ

2，「売上金額」を「値」にドラッグ&ドロップ（売上金額）

3，「売上金額」を「値」にドラッグ&ドロップ（累積比率）

↓

手順⑤：値フィールドの設定

1，　名前の指定：「累積比率」と入力

2，「計算の種類」のタブを選択

3，「計算の種類」タブ：「比率の累計」を選択

↓

手順⑥：右クリックで「並べ替え」→「降順」を選択

↓

完成

「編集」作業を行い，パレート図を作成する

手順①：「挿入」」タブの「ピポットグラフ」を選択

↓

手順②：「組み合わせ」タブ→「累計比率」にチェックを入れる。

※折線グラフに選択する※

↓

手順③：右クリックから「グラフの移動」→「新しいシート」を選択する。

↓

手順④：軸をダブルクリック「軸のオプション」→「最大値」を「1.０」に設定する。

↓

完成

□まとめ

※ABC分析は，「ピポットテーブル」を作成し，「棒グラフ」に組み込んだ「パレート図」を作成して分析する。

※分析の初期段階で活用し、経営資源の分配を計画する。

繰り返し入力不要！便利なプルダウンの使い方（追加と自動反映）

Excel（エクセル）のプルダウン（ドロップダウンリスト）に項目を追加する方法を解説します。

この記事で分かること↓

※2通りのプルダウンの追加方法

※場合に合わせたプルダウンの追加方法の使い分け

※リスト項目の増減を自動反映させる方法

この記事はこんな人にオススメです。↓

※毎回項目を入力するのが面倒なので作業を効率化したい。（入力ミスも減らしたい）

※プルダウンを設定したものの、項目の追加方法がわからない。（入力項目が少ない場合）

※前任者から業務を引き継いだはいいものの、その時からリスト項目が増えすぎて追加できない。（入力項目が多い場合）

□プルダウン（ドロップダウンリスト）に項目を追加する２つの方法

　①選択肢が少ない場合は　→　直接入力方法

　②選択肢が多い場合は　　→　リスト入力方法

①選択肢が少ない場合は　→　直接入力方法

例えば、

性別（男・女）や血液型（A型・B型・O型・AB型）など、選択肢の数が少なく、選択肢の変更も考えにくい様な場合は、直接入力する設定方法が一番簡単です。

手順①：リスト設定したいセルを範囲選択

↓

手順②：「データ」タブから「データの入力規則」を選択

↓

手順③：「入力値の種類」から「リスト」を選択

↓

手順④：「元の値」ボックスにリストしたい項目を記入

※注意※

複数項目を入れる場合は、必ず「，（カンマ）」で区切りましょう。

「、（テン）」で区切ると文字として扱われ連続して表示されます。

○：「男，女」

× ：「男、女」

↓

完成

②選択肢が多い場合は　　→　リスト入力方法

 例えば、

社員名簿や取引先のリストなど、とにかく項目にするリストが多い時は、あらかじめ別枠にリストを作成してその範囲を選択すると効率的です。

①のように直接入力は時間が掛かってしまいますので、、、。

手順①：別枠に「リスト」を作成

↓

手順②：リスト設定したいセルを範囲選択

↓

手順③：「データ」タブから「データの入力規則」を選択

↓

手順④：「入力値の種類」から「リスト」を選択

↓

手順⑤：「元の値」ボックスを一度クリックして、範囲選択

※ワンポイント※

ポップアップウィンドウ（「データの入力規則」の画面）を移動して、作成したリストが見える状態にすると作業しやすくなります。

↓

完成

□上達の為のワンポイトアドバイス：リスト項目の増減を自動反映させる方法

リストの中の項目が増えた際に、追加して打ち込んでいくのはとても大変です。

できることなら自動的に反映してほしいですよね。

そんな時は、「OFFSET（オフセット）」関数と「COUNTA（カウントエー）」関数を組み合わせて使うとできます。

※「OFFSET（オフセット）」関数※

＝OFFSET(基準, 行数, 列数, [高さ], [幅])

・基準：どこのセルから始めるかを指定します。
・行数：基準のセルから移動したい行数を指定します。
・列数：基準のセルから移動したい列数を指定します。
・高さ：移動したセルから参照するセルの高（行）さを指定します。
・幅　：移動したセルから参照するセルの幅（列）を指定します。

※「COUNTA（カウントエー）」関数※

＝COUNTA（範囲）

 指定された範囲で値のあるセルが何個あるかを返す関数

上記の関数の詳しい説明はまた別の機会で、、、。

取り合えず、

具体的な方法を紹介します。↓

手順①：リスト作成

↓

手順②：「データ」タブから「データの入力規則」を選択

↓

手順③：「入力値の種類」から「リスト」を選択

↓

手順④：数式を入力

↓

完成

□まとめ

※選択肢が少ない場合は「直接入力」

※選択肢が多い場合は「リスト入力」

※自動で反映させたい場合は「OFFSET（オフセット）」関数と「COUNTA（カウントエー）」関数を組み合わせる。

データ入力が一瞬で終わる！別の表からデータを自動的に取得するする方法（HLOOKUP関数）

「HLOOKUP（エイチルックアップ）」関数を使って、別表からデータを取得する方法を解説します。 

こんな人におすすめ↓

※従業員の勤務時間を管理した表を作成したい。

※曜日ごとに「始業時間」と「終了時間」を記載した営業カレンダー表を作成したい。

□「VLOOKUP（ブイルックアップ）」関数との違い

前回、「VLOOKUP（ブイルックアップ）」関数について解説しましたが、できることは基本的に同じです。

違いは、マスターデータの検索方向です。

VLOOKUPの「V」は「Vertical（垂直）」を表しているので、縦方向に（行方向）に検索したい場合に使います。

例えば、

商品マスターや社員マスターなどは、縦方向（行方向）にデータを管理することが多いです。 

djiro.hatenablog.com

それに比べて、

HLOOKUPの「H」は「Horizonatal（水平）」を表しているので、横方向に（列方向）に検索したい場合に使います。

例えば、

年月や曜日などの時間軸で管理する場合などは、横方向（列方向）に検索したい場合に使います。

□曜日ごとに「始業時間」と「就業時間」を記載したカレンダーを作成する。

今回は、「HLOOKUP（エイチルックアップ）」関数を利用して、

営業時間の「始業時間」と「終業時間」を取得して、営業カレンダーに自動的に反映させていきます。

完成形はこんな感じです↓

手順①：C3セルに数式を記入

※「HLOOKUP（エイチルックアップ）」関数の引数は4つです。

・引数1：探す値（曜日）

→探したい値を選択。

・引数2：範囲（営業時間）

→探しに行く範囲を選択。この範囲の中から探す値を見つけるイメージです。 

・引数3：取得列番号（始業時刻行）

→取得したい列番号を指定します。

・引数4：FALSE

 ↓

手順②：フィルハンドをダブルクリック

↓

手順③：「始業時間」の数式をコピーして「終業時間」のセルに貼り付け

※注意※

 この時にそのままコピーしてしまうと、数式がずれてしまいます。

これを防ぐために、「＄（ドル）」マークを活用します。

引数1（B3）と引数2（G2：M4）に「＄（ドル）」マークをつけます。

引数３を「３」に修正 

↓

手順④：フィルハンドをダブルクリック

↓

完成

□まとめ

※「HLOOKUP（エイチルックアップ）」関数：別表からデータを持ってくる（横方向）

＝HLOOKUP（検索値，別表の範囲，行番号，検索の型）

→「検索値」のデータを「別表の範囲」の中で横方向に検索し、見つかったら「行番号」の値を取得する。

※4つ目の引数は「TRUE」か「FALSE」のどちらかを指定

TRUE：近い値を検索（類似一致）

FALSE：正確な値を検索（完全一致）

特に気にせずに、「FALSE」を指定する。

データ入力が一瞬で終わる！別の表からデータを自動的に取得する方法（VLOOKUP関数）

「VLOOKUP（ブイルックアップ）」関数を使って、別表からデータを取得する方法について解説します。

この記事を読むと↓

※マスターと呼ばれる一覧を作成することによって、そこからデータを取得できるようになり、最終的にはデータ入力作業が自動で一瞬で終わるようになります。

こんな人におすすめ↓

※商品マスターから商品単価を取得したい。

（一つずつ入力するのが面倒。）

※役職や技能等級リストから時給を取得して給与計算したい。

□具体的な使い方①：商品マスターから商品単価を取得する

（※マスター：基本情報をまとめたもの）

販売データにある商品をもとに、商品マスターを参照して、単価を取得していきます。

2つの表の間でデータを参照する時によく使うのが、「VLOOKUP（ブイルックアップ）」関数です。

この関数は、別の表から条件に一致するデータを取得する関数です。

手順①：C3セルを選択して数式を入れる

※「VLOOKUP（ブイルックアップ）」関数の引数は4つです。

・引数1：探す値（商品名）

→探したい値を選択。

・引数2：範囲（商品一覧）

→探しに行く範囲を選択。この範囲の中から探す値を見つけるイメージです。 

・引数3：取得列番号（商品単価列）

→取得したい列番号を指定します。

・引数4：FALSE

↓

手順②：フィルハンドを使って数式をコピー

↓

完成

□上達のためのワンポイント

引数2は列で選択

「VLOOKUP（ブイルックアップ）」関数の2つ目の引数は、必ず列で指定しましょう。

これはマスターの項目が追加になった時に、自動的に反映できるようにするためです。

引数は3の番号に注意

「VLOOKUP（ブイルックアップ）」関数の3つ目の引数の番号は、2つ目の引数で選択した範囲の中での列番号です。

「A列」＝1、「B列」＝2というわけではありません。

□具体的な使い方②：商品カテゴリー（項目）を追加してみる

具体例①のシートに、もう一つカテゴリーを追加してみます。

手順①：マスターに商品カテゴリーを追加。

↓

手順②：C3のセルに打ち込んだ数式をD3セル（商品カテゴリー）にコピー。

※注意１※

この時にそのままコピーしてしまうと、数式がずれてしまいます。

これを防ぐために、「＄（ドル）」マークを活用します。

引数1（B3）と引数2（H：J）に「＄（ドル）」マークをつけます。

※注意２※

引数３は「２→３」へ変更する。

↓

手順③：フィルハンドをダブルクリック

↓

完成

□まとめ

※「VLOOKUP（ブイルックアップ）」関数は別表からデータを持ってくる機能。

＝VLOOKUP（検索値，別表の範囲，列番号，検索の型）

※4つ目の引数は「TRUE」か「FALSE」のどちらかを指定

TRUE：近い値を検索（類似一致）

FALSE：正確な値を検索（完全一致）

特に気にせずに、「FALSE」を指定する。

5分で分かる！Excel（エクセル）「ソルバー機能」の使い方を解説（具体例付き）

「ソルバー機能」について解説します。

ソルバー機能とは、別名を最適化分析ツールとも言います。

数式の計算結果を目標値として設定して、その結果を得るために複数の制約条件を指定し任意のセルを変化させて最適値を求める機能です。

この記事で分かること↓

※「ソルバー機能」を使えるようにする方法

※具体例を交えた使い方

※エラーが出た時の対処方法

こんな人におすすめ↓

※営業職で目標数値が決まっていて、達成するためにどの商品をどのくらい売ればいいか計算したい。

※仕入れ担当で、予算を最小限に商品を入荷するときのそれぞれの個数が知りたい。

今回は、解説にあたって例題を設定します。

Q：目標金額に対して、プランAとプランBの適切な料金を計算したい。

条件↓

・プランA （月額1000円以下）

・プランB （月額1500円以上　かつ　プランAの価格の3倍以下）

・利用者数はあらかじめ決まっている。

　→プランA：5000人

　→プランB：2000人

・売上目標は5000万円

この条件を基に解説します。

□「ソルバー機能」を有効にする

「ソルバー機能」は、初期状態では有効化されていません。

Excel（エクセル）のオプションから追加する必要があります。

①「ファイル」タブから「オプション」タブを選択

↓

②「アドイン」タブから「ソルバーアドイン」をチェック

↓

③完成

□最適な価格を求める

2つのプラン（プランA、プランB）と目標数値（5000万円）を基に、2つのプランの料金を算出します。

数式にて分かるところは整理

今回の例で言えば、売り上げを求める数式は設定できるので先に数式にて設定しておきましょう。

こうすることで、計算したい料金が決まることで自動的に合計数値を算出してくれます。

・プラン別の年間売上（D2、D3）には、「利用者数×料金×12」

・目標には（D4）には、「２つのプランの年間売上合計数式を入れる」

ソルバーのパラメーターで条件を整理

目標と条件を踏まえて、「ソルバー」機能で各プランの最適な月額を計算します。

①「データ」タブ　→ 「ソルバ」タブを選択

↓

②目標や変更対象セルなど条件を設定する

※今回設定したい条件を数式にて表現

・プランAは1000円以下　→  「＄C＄2＜＝1000」　

・プランBは1500円以上　→  「＄C＄3＞＝1500」

・プランBはプランAの3倍以下　→  「＄C＄3 ＜＝　＄C＄3＊3」

上記の式を追加します。

※全ての条件を追加し終わったら、「キャンセル」で終了します。

↓

③完成

※解が見つかったかどうかを確認します↓

残念ながら例題の条件に合う「最適な料金」は見つかりませんでした。
 

□上達のワンポイント：解が見つからなかった場合こそチャンス

もし解が見つからなかった場合は、「実行可能解が見つかりませんでした」というエラーメッセージが出ます。

目標数値、販売単価、条件が多いなど設定に無理があったことを示します。

解が見つかるまで試してみましょう。

適切な数値を設定するチャンスになります。

↓

目標数値を「5000万」から「7000万」に変更してみる。

※最適解が見つかりました 

目標数値を7000万円にした場合

→プランA：料金538円

→プランB：料金1572円

□まとめ

※ソルバーが使えるように設定する。

　「ファイル」→「オプション」→「アドイン」→「ソルバーアドイン」の順で設定

※条件を設定（数式にて表現）

　・プランAは1000円以下　→  「＄C＄2＜＝1000」　

　・プランBは1500円以上　→  「＄C＄3＞＝1500」

　・プランBはプランAの3倍以下　→  「＄C＄3 ＜＝　＄C＄3＊3」

※エラーが出た時こそチャンス

　適切な数値を見直すチャンス。何度かトライして適切な数値を見つける。

　目標数値を5000万円から7000万円に変更

　他にも

　利用者数を変更したり、プランの条件を変更したり、目的に応じて変更できます。

日付から「年」「月」「日」「曜日」を個々に取得する方法

企業内の売り上げやコスト計算など、金額を計算する時には必ず「締め日」に基づいて計算すると思います。

その時に、データの日付から「年」「月」「日」「曜日」などを個別に取り出すことができれば、スムーズに計算することができます。

今回は、関数を用いて日付から各要素を個別に取り出す方法解説します。

□日付を扱う関数

日付から「年」「月」「日」を順番に取得していきます。

以下３つの関数を使用します。引数は全て一つだけで、「日付」を指定します。

・「年」を取得：「YEAR（イヤー）」関数を使用。

数式　→　＝YEAR（A2）

・「月」を取得：「MONTH（マンス）」関数を使用。

数式　→　＝MONTH（A2）

・「日」を取得：「DAY（デイ）」関数を使用。

数式　→　＝DAY（A2）

□曜日を取得する

日付から「曜日」を取得するには、「TEXT（テキスト）」関数を使用します。

TEXT（テキスト）関数は数値を文字列に変換するための関数で、表示形式を指定することで変換した文字列を表示することができます。

今回の例でいうと、

「曜日」を表示したいので「月、火、水、・・・」といった形に表示できるように指定します。

具体的な使い方はこんな感じです↓

この他にも様々な表示形式があります。

こんな感じです。↓

□上達のためのワンポイント：月末の日付の求め方

意外とよく使うのが、月末の日付の求め方です。主に二つありますので解説します。

①DATE関数を使う

DATE関数を使って、年と月を引数に入れます。月は１ヵ月増やして翌月にします。

日付は「１」を指定します。これで翌月1日の日付が算出できるので、そこから1を引きます。

②「EOMONTH（イーオーマンス）」関数を使う方法

EOMONTHは「End Of Month」（月末）の略で、末日を直接求められる関数です。

引数には、基準となる日付と、そこから何ヵ月後の末日を算出するかを指定します。

例えば、

翌月の末日なら「1」、該当月の末日なら「0」という風に指定します。

□まとめ

※「YEAR（イアー）」関数：日付の「年」を表示

＝YEAR（日付）

※「MONTH（マンス）」関数：日付の「月」を表示

＝MONTH（日付）

※「DAY（デイ）」関数：日付の「日」を表示

＝DAY（日付）

※「TEXT（テキスト）」関数：数値を書式付きの文字列に変換

＝TEXT（数値, ”表示形式"）

エクエルで２つのデータの相関を調べる調べる方法

エクセルの機能を活用すれば、集計したデータを元に分析することも可能です。

しかし、

分析手法については、統計の知識が必要ですし、もっと突き詰めて統計分析したい場合は、それ専用のソフトもあります。

エクセルでできる分析手法はあくまでも限りがあり、完璧ではないので、そこは踏まえた上で正しく活用していく必要があります。

今回は、仕事の現場でもよく使う「相関」について解説していきます。

□相関関係とは？

相関とは、複数の数値の関係を判断するためのデータ分析手法です。

具体的な例をあげてみるとイメージしやすいです。

例：「降水量」が多いと「傘の販売本数」増える？

例：「夏」になると「ビール」が売れる？

例：「肌の白い人」は必ず「日焼け止め」を塗っている？

・・・。

つまり、

「Aが増加したらBも増加した」のように、AとBの事柄に何らかの関連性があることを言います。

しかし、ここで注意が必要ですが、

「相関関係」があるからといって「因果関係」があるとは限らないということです。

よく同義で扱っている場面を見ますので少し解説いたしますが、「因果関係」とは、Aが原因でBが変化していることを言います。

上記の例でいうと、

仮に、「降水量」が多ければ「傘の販売本数」が増える。という相関があったとしても、

「傘の販売本数」が増えたのたのは、「降水量」が多いからだとはなりません。

もしかしたら、

傘を売っている店が一つしかなかったのかもしれなし、時間帯が会社から帰る夕方からだったからかもしれないし、値下げしたからかもしれません。

つまり、

「因果関係」があるということは、原因と結果が決まっていることを指します。

「降水量」と「傘の販売本数」は何かしらの関係はあるものの、直接影響しているかは不明ですので、相関関係はあるけど因果関係はないということになります。

イメージとしては、相関関係の中に因果関係が含まれているイメージです。

□データ分析ツールを使えるようにする

はじめに、エクセルの「データ分析」機能を使えるようにします。

①「ファイル」を選択し「オプション」をクリック

↓

②「アドイン」を選択して「設定」をクリック

↓

③「分析ツール」をチェックして「OK」をクリック

↓

④完成

「データ」タブにて「データ分析」機能が使えるようになりました。

□2つのデータの相関を調べる

「降水量」と「傘の販売本数」の相関を調べてみます。

①「データ分析」を選択して「相関」をクリック

↓

②入力範囲を選択

※注意：「先頭行をラベルとしてしよう」にチェックを入れる。

↓

③新規シートに相関係数が出力される

ここで相関係数の表の見方を説明します。

分析した相関係数の数値は「0.7875〜」となっています。

相関は、「ー1.0 〜 ＋1.0」の範囲の数値（これを相関係数という）によって、数値が正の場合は「正の相関がある」、負の場合は「負の相関がある」と言います。

・正の相関：Aが増えればBも増える

・負の相関：Aが減ればBも減る

簡単に判断基準を数値で示すと↓

・0.7〜1.0：強い正の相関

・0.4〜0.7：正の相関

・-0.4〜0.4：関係がない

・-0.7〜0.4：負の相関

・-1.0〜0.7：強い負の相関

こんな感じになります。

なので、今回は「強い正の相関がある」と言えます。

注意：今回はエクセルでの「相関関係」を分析する手法を解説しています。

　　　出てくる数値はサンプルですのでご注意ください。

□まとめ

※エクセルでデータ分析するには「分析ツール」を使う

※「相関関係」と「因果関係」は違う

売上グラフの中に目標値を表示する方法

資料を作る際にグラフを活用するのですが、目標値をグラフに反映させることができなくて苦労したのを覚えています。

そもそも、グラフを作成する目的は、現状の状態を把握し今後の行動に生かすために、視覚的にわかりやすくすることです。

見やすくを前提に、「現状」と「目標値」が一目で分かるグラフの作成方法を具体的に解説します。

□目標値を入れたグラフを作成

エクセルでは、さまざまなグラフを簡単に作成できます。更に、編集機能も多彩で、項目ごとに形式を変更したりもできます。

今回は「売上データ」から「棒グラフ」を作成して、目標値である「ターゲット」のみ折線グラフに変更します。

①「Ctrl」＋「A」を押して表全体を選択

↓

②「挿入タブ」の「縦棒／横棒グラフの挿入」から「集合縦棒」をクリック

※ここで前段階の準備として、

グラフの表端にくるデータに目標数値を入力しておきます。

今回のグラフだと、「4月」と「3月」になります。

この時はまだ棒グラフになっていますが問題ありませんので、大丈夫です。

↓

③ターゲットを「折れ線」に変更する

売上実績の「棒グラフ」と比較しやすいように、表示形式を変更します。

グラフを選択した状態で、「デザイン」タブから「グラフの種類の変更」を行います。

↓

④「データの選択」をクリックして、「データ　ソースの選択」を表示

↓

⑤「非表示および空白のセルの設定」を表示

↓

⑥「データ要素を線で結ぶ」を選択して「ＯＫ」をクリック

□表示調整と近似曲線の追加

①～⑥までの工程で作成したグラフを見やすく整えます。

⑦グラフの色を変更する

編集したいグラフを選択して、ダブルクリックをすると、「データの要素の書式設定」が表示されます。

（※グラフを右クリックして「データ系列の書式設定」をクリックでも可能です）

ここから、色や形などの変更を行うことができます。

↓

⑧近似曲線を追加して、グラフを両端まで伸ばす

「近似曲線の追加」を選択して、「近似曲線の書式設定」で調整します。

↓

⑨グラフアイコンを選択

「前方補外」　→　0.5区間

「後方補外」　→　0.5区間

↓

⑩塗りつぶしアイコンを選択

色　→　赤

幅　→　2.25

実線/点線　→　実線

（※見やすいように自分で調整できます。今回は適当に設定してます。）

一致する値によって表示を変える方法。

関数を利用して、一致する値によって表示を変える方法を記載します。

これ迄に、

複数シートのデータを一つにまとめたり↓

djiro.hatenablog.com

 二つのシートから差異を見つけて表示したり↓ 

djiro.hatenablog.com

 と、二つ以上のシートを比べて何かを作業を行う方法をいくつか解説してきました。

エクセルでは比較したり、置き換えたりする方法がたくさんあり、状況に合わせて使い分ける事ができると、それだけで作業効率がグンっと上がります。

 今回は、別のシートを作らずに一致するデータを取得する方法を解説します。

一致させるデータが少ない時は、別シートにマスター表を作らずに、関数のみで分岐処理を作成すると便利です。

□「SWITH（スウィッチ）」関数を活用しよう

「SWITH（スウィッチ）」関数を使って、一致するデータを取得します。

SWITH（スウィッチ）関数はExcel2019で、新しく追加された関数で、条件式の値と戻り値を幾つでも設定できます。

具体的な使い方を説明していきます。

・役職コード（B列）のセルの値が「a」に一致する場合に「部長」と表示

・役職コード（B列）のセルの値が「b」に一致する場合に「次長」と表示

・役職コード（B列）のセルの値がどれにも一致しない場合に「ー」と表示

以上の3つの条件で数式を設定します。

数式はこうなります↓

※注意点として、

Excel2016以前のバージョンの場合は、「SWITH（スウィッチ）」関数がありませんので、「IF（イフ）」関数の中に「IF（イフ）」関数を入れて数式を作る必要があります。

こんな感じです↓

①「C2」セルに数式を入力

＝SWITCH（B2，“A“，“部長”，“B“，“次長“，“ー“）

↓

②フィルハンドをダブルクリックでコピー

↓

③完成

 

□まとめ

※SWITH（スウィッチ）関数：それぞれの値に一致するかで別々の結果を返す

　＝SWITH（式，値1，結果1，値2，結果2・・・・・規定値）

　複数の「値」のうち、最初に一致した値に対応する「結果」を返す

誰が見ても分かる様にセルに説明を表示させる方法

経費の申請書やアンケートシートなど、不特定多数のメンバーに配布して記入してもらう場合があります。

そんなデータでは、記入者が間違えないように、メモを表示する場合があります。

私の場合は、会議資料などでも使ったりしています。

メモの書き方自体はよく知られているのですが、ここでは「常に表示させる方法」なども合わせて解説します。

こうすることで、注意喚起になり作業してもらえるので、間違いも少なくなり、後でチェックするにも効果的です。

□コメントの設定方法

①メモを追加したいセルをクリックして選択

↓

②「Shift」＋「F2」で吹き出しを作成

※注意点として、

セルを右クリックして「新しいメモ」をクリックしても追加できます。

↓

③コメントを書き込む

↓

④完成

□メモを常に表示する方法

メモは通常、対象のセルにカーソルを合わせた時のみ表示されます。

この時に、人によってはメモのコメントを見落とすなど、作業者によって差異が出てきます。

場合によっては、常に表示させておくことも必要です。

※方法

①「校閲」タブ　→ 「すべてのメモを表示」をクリック

※因みに、記載したメモをそのまま印刷することもできます。

①「ページレイアウト」タブをクリックし、ページ設定グループにある「ページ設定」をクリック

↓

②「シート」タブの「コメントとメモ」を選択

↓

③完成

見えている画面の通り印刷できます。コメントシートで見せたいセルが重ならないように注意しましょう。

□コメントを削除する方法

一応、コメントを削除する方法も記載しておきます。

コメントが必要なくなった場合は、対象セルを右クリックして「メモの削除」を選択します。

□上達のためのワンポイント

コメントの枠は、図形と同じように移動・拡大・縮小が可能です。

「Alt」キーを押しながら作業すると、セルの角にピッタリ揃って綺麗に表示できます。

□まとめ

※「Shift」＋「F2」：コメントを追加。

※「Alt」キーを使うとコメント枠の移動・拡大・縮小が綺麗に変更できる。

※「すべてのコメントの表示」でコメントを常に表示できる。

※右クリックして「コメントの削除」でコメントん削除ができる。