はじめに

この動画では、エクセルで『商品ごとの粗利益と利益率の管理表』と『カテゴリごとの利益率とグラフ』を作る方法を紹介します。

完成形の確認

まず完成形を確認しましょう

この表に、商品の

• カテゴリと
• 商品名
• 仕入れ値
• 売値

を入れると、自動で粗利益と利益率が算出されます。

同時に隣には、カテゴリごとの利益率とそのグラフが表示されます。これをみることでどのカテゴリの利益率が高いかを一目で把握することができます。

管理表は利益率順で並び替えたり、カテゴリごとに絞り込んで表示することができます。

また、動画の後半では、表のデザインをこのように縞々にして見やすさを整えたり、意図しない文字や数字は入力できないように制限をかける方法についても紹介しますので、是非最後までご覧ください。

それでは早速作成していきます。

項目名

まずは、商品一覧の表の項目名を入力していきます。

セルを確定して、右のタブへ移動したい場合は、Tabキーで右に移動することができます。

• カテゴリ
• 商品名
• 仕入れ値
• 売値
• 粗利益
• 利益率

粗利

次に、粗利益が自動で計算されるように設定していきます。正しく計算されているかを確認するために、仕入れ値と売値の数字を仮でいくつか入れておきます。

粗利益は売値-仕入れ値なので、このように入力します。『=D2-C2』。これで、D2のセル引くC2のセル、つまり売値-仕入れ値が算出されます。

計算式が入力できたら、そのセルをクリックして選択して、右下の小さい四角をクリックしながらドラッグすることで、他の行にも計算式を作成することができます。

このように、連続する数字や計算式を一瞬で作成できる機能をオートフィル機能と言います。

利益率

次に、利益率が自動で計算される計算式を入力していきます。

利益率は、粗利益÷売値×100で計算することができますが、エクセルには数字を×100にして%記号をつけて表示することができる機能があるので、ここでは、粗利益÷売値のみ計算します。

実際に計算式を入力すると『=E2/D2』となります。これで、E2のセル÷D2のセル、つまり、粗利益÷売値が算出されます。

次に、先ほど説明した、数字を×100にして、%記号をつけて表示する機能を適用していきます。

表示を変えたいセルをクリックして選択したら、上のタブの中からホームタブを開いて、数値グループの中の、このパーセントボタンをクリックします。

すると、数字を×100にして、%記号をつけて表示することができました。

このように見た目上の数字を変える機能のことを表示形式機能と言います。

表示形式機能でこのようにパーセントを付けた場合、表示されている桁の1つ下の桁を四捨五入して表示されます。今回の場合、小数点以下第1位が四捨五入されています。

もし小数点以下の表示桁数を変更したい場合は、そのセルをクリックして選択した状態で、先ほどと同じく、ホームタブを開いて数値グループの中のこの2つのボタンをクリックすることで、表示桁数を増やしたり減らしたりすることができます。

今回は、小数点第1位まで表示しておきます。

設定できたら、先ほどと同じようにオートフィル機能を使って他の行にも計算式を作成しておきます。

ピボットテーブル作成の準備

次に集計表を作成していくんですが、今回はカテゴリごとの利益率を表示したいので、カテゴリを適当に入れておきます。

列幅の調整

情報がはみ出した場合は、列の境目をダブルクリックすると自動で幅を調整してくれます。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

任意のファイル名と保存場所を指定します。

ピボットテーブル

次に、集計表を作成していきます。

集計表の元となる表をどこでも良いのでクリックで選択して、上のタブの中から挿入タブを開いて、ピボットテーブルをクリックします。ピボットテーブルとは簡単に集計表を作ることができる機能のことです。

ピボットテーブルをクリックするとピボットテーブル作成の設定画面が表示されます。

設定画面が表示されたら、まず集計の元となるデータの表を選択します。通常は自動で選択されますが、もし適切に選択されていない場合は、表の端をクリックしながら対角側の端までドラッグして、表を選択しなおしてください。

次に、集計表を作成するシートを選択します。今回は集計の元となる表の横に、集計表を作成したいので、『既存のワークシート』を選択して、集計表を配置したいセルを選択します。今回はH1セルを選択します。

すると、場所の欄に、集計表を作成するセルが表示されます。

設定が終わったら、OKをクリックします。

すると、集計表の設定画面が表示されます。

設定画面の上のボックスには、項目名が表示されています。

設定画面の使い方を簡単に紹介すると、上のボックスの項目名を、下のボックスにこのようにドラッグして使います。

• 行は左側の項目、
• 列は上側の項目、
• 値は表示したい項目、
• フィルターは絞り込みたい項目

を設定します。

今回は、左側にカテゴリ名を表示したいので、行のボックスにカテゴリをドラッグで入れます。

次に、カテゴリごとの利益率を知りたいので、値のボックスに利益率をドラッグで入れます。

ピボットテーブルの表示形式

すると、カテゴリごとの利益率が表示されるのですが、2点、訂正が必要です。

1つは×100にする前の状態で表示されていることです。

これは、集計表の元となる表の利益率の数字の表示だけが×100になっていて、実態は×100になっていないことが原因です。

2つ目は、利益率の平均ではなく合計を表示していることです。今回はカテゴリごとの利益率を表示したいので、各商品の利益率の合計ではなく、平均を表示する必要があります。

まずは、×100にして表示する設定を行っていきます。

先ほどは上のホームタブから、パーセントのボタンを押すことで、表示形式を変えることができましたが、ピボットテーブルで作成した、数字の表示形式を変える場合は、設定方法が変わります。

ピボットテーブルのフィールド設定メニューを操作していきます。

もし、この設定メニューが表示されていない場合は、ピボットテーブルで作成した表を選択すると表示されます。それでも表示されない場合は、上のピボットテーブル分析タブを開いて、フィールドリストをクリックすると表示することができます。

ピボットテーブルの設定メニューを表示した状態で、表示形式を変えたい項目をクリックして、『値フィールドの設定』をクリックします。

すると、値フィールドの設定メニューが表示されるので、『表示形式』をクリックします。

すると、どのような表示にするかの選択画面が表示されるので、パーセンテージを選択します。

小数点以下の桁数を指定できるので、今回は1に設定しておきます。

設定出来たら、OK、OKをクリックすると、利益率の数字を×100にして、更に単位のパーセントをつけて表示することができました。

ピボットテーブルの値フィールドの設定

次に、利益率が合計して表示されているのを、平均に変えていきます。

今、ここに『合計/利益率』と表示されています。これは利益率を合計して表示しているという意味になります。

これを平均に変えるには、先ほどと同じように、ピボットテーブルのフィールド設定メニューから、集計方法を変更したい項目をクリックし、『値フィールドの設定』をクリックします。

すると集計方法のタブで集計方法を選択することが出来るので、平均をクリックして選択します。

平均を選択できたら、OKをクリックします。

これでカテゴリごとの利益率を平均で表示することができました。

ピボットテーブルのグラフ

次に、この集計表を元にグラフを作ります。表のどこかを選択した状態で、上のピボットテーブル分析タブを開いてピボットグラフをクリックします。

するとどのようなグラフを作成するかの選択画面が表示されるので、今回は縦棒グラフを選択し、OKをクリックします。するとグラフを作成することができました。

グラフをドラッグすることで、位置を動かしたり、四隅をドラッグすることでサイズを整えることができます。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを上書き保存しておきましょう。

ピボットテーブルの注意点

ここで、ピボットテーブルの注意点をお伝えしておきます。

ピボットテーブルで作成した集計表は、元となるデータが更新されても自動では反映されません。

• ピボットテーブルで作成した集計表を選択して、ピボットテーブル分析から更新をクリックするか
• ファイル保存して、閉じて開き直すことで

反映されるんですが、

実際にこのまま商品を追加して、更新ボタン押しても反映されません。

原因はさきほどピボットテーブルの設定で集計表を作成した際に、集計の元となる表の範囲を決めてしまっていることです。

情報を追加した際に、自動で集計の元となる表の範囲を広げるには、テーブルという設定をする必要があります。

テーブル

テーブルというのは表という意味で、これを設定することで、情報が追加された際に、参照範囲を広げたり、表のデザインを縞々にして見やすくしたり、データを並び替えたり・絞り込んだりできるようになります。

テーブルを設定するには、設定したい表のどこかを選択して、Ctrlキーを押しながらTキーで設定することができます。

Ctrlキーを押しながらTキーを押すと、このように、テーブルを設定する範囲を選択するメニューが表示されます。基本的にはこのように自動で選択されますが、希望通り選択されていない場合は、表の端をクリックしながら、対角側の端までドラッグして、選択しなおしてください。

先頭の行は見出しなので「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認したら、OKをクリックします。

すると、縞々デザインになり、テーブルを設定することができました。

新たに商品が追加されても、ピボットテーブルの集計表のどこかをクリックして、ピボットテーブル分析タブを開いて、更新をおすと、集計表に反映されるようになりました。

もしこのテーブルのデザインを細かく変更したい場合は、テーブルデザインタブを開いて、テーブルスタイルのオプションとテーブルスタイルのグループから変更することができます。

また、項目名の下三角形をクリックすることで、データを並び替えたり、絞り込んだりできるようになっています。

見た目向上

次に、集計の元となる表が更に見やすくなるようにデザインを整えていきましょう。

表示形式(管理表)

仕入れ値と売値と粗利益の数字に円マークと3桁ごとのカンマをつけていきます。

先ほど使った表示形式の機能を使います。

まず、表示形式を変えたい、C列～E列をドラッグで選択します。

選択ができたら、上のホームタブを開いて、数値グループのこの通貨のアイコンをクリックします。すると円マークと3桁ごとにカンマがつきました。

保存

ここで一旦、Ctrlキーを押しながらSボタンを押してファイルを上書き保存しておきましょう。

使いやすさ向上

入力規則

次に

• 仕入れ値と
• 売値

欄に数字以外の情報を入力できないように設定していきます。

入力制限をかけることで、このように数字以外の情報が入ってしまい、計算エラーが起こらないようにすることができます。

入力制限をかけるには、まず、入力制限をかけるセルを選択します。

今回の場合であれば、C列からD列をドラッグで選択します。C1とD1は数字ではないので、Ctrlキーを押しながらドラッグして選択から外します。

入力制限をかけるセルを選択できたら、上のデータタブを開いて、データ入力規則の上半分のアイコンの部分をクリックします。

データの入力規則では、データの入力に制限をかけることができます。

データの入力規則メニューを開いたら、入力制限のルールを設定していきます。

今回は数字以外の入力を制限したいので、入力値の種類を整数に設定します。次に、数字の範囲を設定することができます。

• 仕入れ値と
• 売値

の2つはマイナスになることは無いので、データを次の値以上、最小値を0に設定します。

これで、0以上の数字のみ入力を許可するという意味になります。

この状態でOKをクリックします。すると

• 0以上の数字と
• 数字

以外を入力しようとすると、このようにエラーを表示して、入力できないように制限をかけることができました。

リスト

次にカテゴリ名の欄を、指定した文字以外は入力できないように設定していきます。

ピボットテーブルで作成した集計表は、カテゴリ名が一言一句等しくなければ、正しく計算されないので、入力制限をかけることで、集計ミスを減らすことができます。

まずは、入力を許可する文字のリストを作ります。下にあるプラスボタンを押して、新しいシートを作成します。シート名をダブルクリックして、シート名を分かりやすく変えておきます。ここでは『カテゴリリスト』という名前にします。

次に入力を許可する文字をリスト化していきます。

• カメラ
• オーディオ
• テレビ
• ビデオカメラ
• DVDプレーヤー

今回はこの5つのみ入力を許可します。

リストを作成できたら、元のシートに戻り、入力制限をかけていきます。

まずは、制限をかけるセルを選択します。A列をクリックして選択します。先ほどと同じく、A1は制限をかける必要が無いのでCtrlキーを押しながらA1セルをクリックして、選択から外します。

制限をかけるセルを選択できたら、上のデータタブからデータの入力規則をクリックします。

今回は自身で作成したリストを参照してその文字いがいは入力できないように設定したいので、『入力値の種類』は『リスト』を選択します。

元の値では、カテゴリリストのセルを参照します。元の値のボックスをクリックして、カーソルを入れたら、カテゴリリストのシートを開き、リストを選択します。ここではA列をクリックします。すると、カテゴリリストのシートのA列を参照することができます。

ドロップダウンリストから選択するにチェックを入れておくと、このように文字を直接入力しなくても、選択肢から入力できるようになり、便利なので、チェックを入れておきます。

設定ができたら、OKをクリックします。

すると、このようにリストの文字を選択できるようになり、リスト以外の文字を入力しようとすると、このようにエラーが出て、入力出来ないように制限をかけることができました。

仮データの削除

完成したら、仮で入れた

• カテゴリ
• 仕入れ値
• 売値

の数字を削除しておきましょう。

削除したいデータをドラッグで選択してDeleteキーで削除します。

すると、粗利益と利益率にエラーが発生しました。エラーが発生すると、ピボットテーブルの集計表を更新した時にこのようにエラーが発生してしまうので、最初の1行以外は削除しておきましょう。粗利益と利益率は、商品が追加されるたびに自動で、計算式が追加されるので、最初の1行だけ残っていれば大丈夫です。

テーブルの範囲変更

テーブルの設定範囲が気になる方は、この右下をクリックしながらドラッグすることで、変更することができます。

保存

ここで、Ctrlキーを押しながらSを押してファイルを上書き保存しておきましょう。

便利なテクニック Ctrl+↓

最後に便利なテクニックを紹介します。

このようにデータが増えて来た際に、一瞬で次の入力欄に飛ぶには、表のどこかをクリックで選択して、Ctrlキーを押しながら下キーを押すと、連続するデータの端まで移動することができます。このテクニックを使って素早く入力するようにしましょう。

まとめ

以上がエクセルで『商品ごとの粗利益と利益率の管理表』と『カテゴリごとの利益率とグラフ』を作成する方法でした。