はじめに

この動画では、エクセルで仕切値(しきりね)を管理する方法と代理店ごとの仕切率や商品ごとの仕切率を算出する表とグラフを作成する方法を紹介します。

完成形の確認

まず完成形を確認します。

この仕切値一覧の表で、どの代理店に、何を、いくらの値段で販売したかを記録します。

商品リストのシートに、予め商品の希望小売価格を登録しておくと、仕切値一覧の表に商品名を入れた際に、自動で希望小売価格が表示されるように作成しています。

さらに希望小売価格と仕切値(しきりね)が入力されると、その差額と、希望小売価格分の差額である仕切率を自動で算出することができます。

他にも、仕切値一覧の表は代理店を絞り込んで、商品ごとの比較をすることができますし、商品を絞り込んで代理店ごとの仕切値(しきりね)を比較することもできます。

この仕切値一覧を入力していくと、右側に、代理店ごとの仕切率の平均と、商品ごとの仕切率の平均を表とグラフで表示されます。

また動画の後半では意図しない情報は入力できないように入力制限をかける方法についても紹介しますので、最後までご覧ください。

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

項目名

まずは、仕切値一覧の型となる項目名の欄を入力していきます。

右へ情報を入力していく際は、Tabキーを押すと、入力を確定して選択セルを右に移動することができるので、素早く入力できます。

列幅の調整

列幅が足りず情報が表示しきれていない場合はその列名の右側の境目をダブルクリックすると、情報量に合わせて自動で、列幅を調整することができます。

差額

次に希望小売価格と仕切値(しきりね)の差額を自動で算出できるように設定していくんですが、正しく算出されているかをチェックするために、仮でいくつか情報を入れておきます。

下へ情報を入力していく際は、Enterキーを押すと、入力を確定して選択セルを下に移動することができるので、素早く入力できます。

セルの情報をコピーする際はショートカットキーを使うと素早く入力できます、コピーしたいセルをドラッグで選択した状態で、Ctrlキーを押しながらCを押してコピーして、貼り付けたいセルを選択した状態でCtrlキーを押しながらVを押すと貼り付けることができます。

仮の情報が入力できたら、まず差額が自動で計算されるように設定します。

希望小売価格と仕切値(しきりね)の差額は、希望小売価格-仕切値(しきりね)で算出することができるので、このように入力します。

=C2-D2

すると希望小売価格と仕切値(しきりね)の差額を算出することができました。

他の行にも、この計算式を適用するには、今入力したセルを選択した状態で、右下の小さい四角形をダブルクリックすると、他の列の最終行まで一瞬で計算式を作成することができます。

このように一瞬で他の行にも数式を作成できる機能のことをオートフィル機能と言います。

仕切率

次に希望小売価格分の差額である仕切率を算出していきます。

仕切率は差額÷希望小売価格×100で算出することができます。

F2セルにこのように入力します。

=E2/C2*100

すると仕切率を算出することができました。

エクセルでは、割り算はスラッシュで入力します。

表示形式(%)

次に、表示形式という機能を使って、仕切率の小数点以下の桁を非表示にして、後ろに%の記号を表示させていきます。

このように実際に入力されている情報とは違う、見た目上の表示を設定する機能のことを表示形式機能と言います。

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

すると、小数点以下の桁が非表示になり、後ろに%記号をつけて表示することができました。

ただ、数字が100倍されて表示されています。これは、%で表す数字は100分率なので、表示形式でパーセントを付けると自動で100倍にして表示されることが原因です。なので、先ほど入力した*100を削除しておきます。

編集したいセルをF2キーかダブルクリックで編集モードにして、BackSpaceキーで削除することができます。

これで仕切率を算出することができました。

もし、小数点以下を表示したい場合は、そのセルを選択した状態で、ホームタブの数値グループからこの2つのアイコンをクリックすることで、小数点以下の桁数を増やしたり減らしたりすることができます。これも、表示形式機能の1つです。今回は小数点以下の表示は無しで進めます。

仕切率を算出できたら、こちらも先ほどと同じように右下の小さい四角形をダブルクリックして、他の列の最終行まで計算式を作成することができます。

保存

ここでCtrlキーを押しながらSを押してファイルを保存しておきます。

この動画では

ファイル名は仕切値管理表として、

保存場所はデスクトップにしておきます。

ピボットテーブル

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

今回は、代理店ごとの仕切率と、商品ごとの仕切率を集計したいので、仮で、代理店名と商品名を入力しておきます。

列名の境目をダブルクリックして列幅を調整しておきます。

ここでもショートカットキーを使います。Ctrlキーを押しながらCでコピーして、Ctrlキーを押しながらVで貼り付けます。

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

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

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

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

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

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

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

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

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

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

を設定します。

まずは、左側に代理店名を表示したいので、行のボックスに代理店をドラッグで入れます。

次に、代理店ごとの仕切率を知りたいので、値のボックスに仕切率をドラッグで入れます。

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

すると、代理店ごとの仕切率が表示されるのですが、2点、訂正が必要です。

1つは仕切率が100倍にする前の状態で表示されていることです。

これは、集計表の元となる表の仕切率の数字の表示だけが100倍になっていて、実際は100倍になっていないことが原因です。

2つ目は、仕切率の平均ではなく合計を表示していることです。今回は代理店ごとの仕切率を表示したいので、代理店の仕切率の合計ではなく、平均を表示する必要があります。

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

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

ピボットテーブルの表示形式を変えるには、表示形式を変えたい項目の見出し欄をダブルクリックします。

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

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

ここで、小数点以下の桁数を指定できるので、表示したい方は調整してください。今回は0のままで進めます。

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

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

次に、仕切率が合計して算出されているのを、平均に変えていきます。

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

これを平均に変えるには、先ほどと同じように、項目の見出し欄をダブルクリックして、値フィールドの設定メニューを表示します。集計方法を変えるには、集計方法タブの値フィールドの集計の欄の設定を変更します。現在、合計が選択されているので、これを平均に変えます。この状態で、OKをクリックすると仕切率の平均が算出されます。

これで、代理店ごとの仕切率を表示することができました。

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

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

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

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

商品ごとの集計

同じように商品ごとの集計とグラフも作成します。

集計の元となる表を選択して、上の挿入タブからピボットテーブルをクリックします。

集計の元となるデータが選択されていることを確認したら、ピボットテーブルを配置するシートを既存のワークシートに変更して、配置するセルをクリックして入力します。今回は、H13?14?セルを入力して、OKをクリックします。

次は、商品ごとの仕切率を算出したいので、行のボックスに商品名を入れて、値の欄には仕切率を入れます。

先ほどと同じく仕切率が100倍にする前の数字が合計して表示されているので、項目の見出し欄をダブルクリックして『表示形式』をパーセンテージに変更します。

同時に、集計方法も平均に変えておきます。

これでOKをクリックすると、商品ごと仕切率を表示することができました。

グラフも作成します。

表のどこかを選択した状態で、上のピボットテーブル分析タブからピボットグラフをクリックします。

集合縦棒グラフが選択されていることを確認したらOKをクリックします。

ドラッグで位置やサイズを調整します。

これで、代理店ごとの仕切率と商品ごとの仕切率を算出する表とグラフの完成です。

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

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

ピボットテーブルで作成したデータは、集計の元となるデータの情報を更新しても、自動では反映されないです。

反映させるには、

• 一度エクセルファイルを閉じて、開き直すか
• 集計表を選択した状態で、ピボットテーブル分析タブから、更新をクリックすることで、

反映させることができます。

保存

ここでCtrlキーを押しながらSを押してファイルを保存しておきます。

テーブル

次に、仕切値(しきりね)の管理表を代理店ごとに絞り込んだり、商品ごとに絞り込んだりできるように設定していきます。

絞り込みができるようにするにはテーブルという機能を使います。

テーブルというのは日本語で表という意味です。表にテーブルを設定することで、データを絞り込むことができるようになるだけでなく、並び替えたり、表のデザインを簡単に整えることができるようになります。

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

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

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

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

これで、項目欄の横にある下三角形からデータを絞り込んだり、並び替えたりできるようになります。

表のデザインを変更したい場合は、その表内のどこかを選択した状態で、上のテーブルデザインタブから、テーブルスタイルのオプションやテーブルスタイルから変更することができます。

今回は、そのままのデザインで進めます。

表示形式(￥)

次に、通貨を表す数字に、このように円マークと3桁ごとのカンマをつけていきます。

実際に円マークを付けたりしてしまうと、このように自動計算が出来なくなってしまうので、実際には円マークやカンマが付いていない状態にする必要があります。ここでも表示形式機能を使っていきます。

まず、表示形式を変えたいセルを選択します。

今回であればC列～E列をドラッグで選択します。

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

保存

ここでCtrlキーを押しながらSを押してファイルを保存しておきます。

入力規則

代理店名

次に、代理店名と商品名をこのように選択肢から入力できるようにして、その選択肢以外からは入力できないように制限をかける設定していきます。

絞り込みの機能や集計機能は、このように情報が完全に一致していない状態だと正しく機能しないため、そのような制限をかけることで、正しく機能するようにします。

データの入力に制限をかけるには、データの入力規則という機能を使います。

まずは、代理店から設定していきます。データの入力規則で選択肢からのみ入力できるように制限をかけるには、まずその選択肢のリストを用意します。

下のプラスボタンをクリックして、新しいシートを作成して、このシートにリストを作成していきます。

シートをダブルクリックして、シート名を分かりやすいように変えておきましょう。

ここでは、代理店リストという名前にします。

ついでに、仕切値(しきりね)一覧のシートは仕切値(しきりね)一覧という名前に変えておきます。

次に代理店リストのシートに、代理店名を縦に並べていきます。

代理店名が一致していないと、不具合が起きるので、ここでは仮で入力した代理店名をコピーして使います。代理店名をCtrlキーを押しながらCを押してコピーします。代理店リストのシートを開いて、貼り付けたいセルを選択した状態で、アプリケーションキーを押してからVを押すことで値のみ貼り付けることができます。

同時押しではなく、1つずつ順番に押してください。

値というのはセル内の情報のことです。

逆に値以外というのは、セルの書式のことを指します。セルの書式というのはセルの色や文字のサイズなど、見た目のことです。

Ctrlキーを押しながらVで貼り付けると、このように書式まで貼り付けられてしまいます。

そこで、アプリケーションキーを押してからVを押すことで、貼り付け先のセルの書式を変えずに貼り付けることができます。

列名の境目をダブルクリックして、列幅を調整しておきます。

代理店名を並べたら、このリストからのみ入力できるように入力規則の設定をしていきます。

入力規則の設定を行うには、まず入力規則の設定をしたいセルを選択します。

ここでは元のシートに戻って、A列をクリックします。項目欄には制限をかけないので、Ctrlキーを押しながらクリックで選択を解除します。

入力規則の設定をするセルを選択できたら、上のデータタブから、データの入力規則の、このアイコンの部分をクリックします。

するとデータの入力規則の設定メニューが開きます。

設定のタブでどのように入力制限をかけるかを設定していきます。

今回は、先ほど作成したリストを参照して、そのリスト以外の入力を制限したいので、入力値の種類はリストを選択します。次に、元の値のボックス内をクリックして、カーソルを入れたら、先ほど作成したリストのシートを開いて、そのリストを選択します。ここでは後から、代理店が追加されても自動で、参照できるように列ごと選択します。

すると元の値欄に、参照セルを入力することができます。

ドロップダウンリストから選択するにチェックが入っていると、制限をかけるだけでなく、選択肢から入力ができて便利なので、チェックが入っていることを確認して、OKをクリックします。

するとこのように選択肢から入力できるようになり、それ以外の情報を入力しようとすると、このようにエラーが出て入力できないように設定することができました。

選択肢は、Altキーを押しながらカーソルキーの下を押すことでも表示できます。選択肢が表示された状態で、カーソルキーの上と下で選択して、Enterで入力を確定することができます。うまく使いこなせば素早く入力していくことができます。

Ctrlキーを押しながらZキーを押して元に戻します。

商品名

商品名も同じように設定していきます。

下のプラスボタンをクリックして新しいシートを作成し、シート名を商品リストとしておきます。

仕切値一覧の順番にしておきたいので、シート名をドラッグして、並び替えておきます。

商品リストのシートを開いたら、商品名を縦に並べていきます。

商品リストもコピーで作成します。商品名をドラッグで選択したらCtrlキーを押しながらCでコピーします。商品リストのシートを開いて、貼り付けたいセルを選択した状態で、アプリケーションキーを押してからVを押すことで値のみ貼り付けることができます。

仕切値(しきりね)一覧のシートを開いて、商品名の列を選択したら、Ctrlキーを押しながら項目名をクリックして、項目名のみ選択を解除します。

コピーされている範囲が表示されていて、選択範囲が分かりにくいので、Escキーでコピーを解除しておきます。

選択できたら、上のデータタブからデータの入力規則を開きます。

設定タブから入力値の種類はリストを選択して、元の値のボックスをクリックして、カーソルを入れたら、商品リストのシートを開いて、商品名一覧が入力されている列を選択して参照します。

OKをクリックすると、商品名にもデータの入力規則を設定することができました。

このように選択肢が用意されて、この選択肢以外は入力できないようになっています。

Ctrlキーを押しながらZキーを押して元に戻します。

保存

ここでCtrlキーを押しながらSを押してファイルを保存しておきます。

VLOOKUP関数

次に、商品名を入れると、希望小売価格が自動で入力されるように設定します。

希望小売価格を自動で入力できるようにしておくことで、手間とミスを減らすことができます。

商品名を入れると、希望小売価格が自動で入力されるようにするには、まずこのように希望小売価格の表を用意します。先ほど、作成した商品リストに付け足す形で作っていきます。

これもコピーで作ります。Ctrlキーを押しながらCでコピーして、アプリケーションを押してからVで値のみ貼り付けます。

• 29800
• 1500
• 4980

このままでは、何の数字か分からないので、項目名を付けます。1行目を右クリックして、挿入で行を追加します。追加できたら、商品名・希望小売価格と入力します。

希望小売価格には、表示形式機能で、円マークと3桁ごとのカンマを付けておきます。

更に、列名の境目をダブルクリックして列幅を調整しておきます。

次に、仕切値(しきりね)一覧シートの希望小売価格の欄のここではC2を選択した状態で、このような式を入力します。

=VLOOKUP(

商品名のB2をクリックすると角括弧@商品名と入力されます。

そのままカンマを入力したら、商品リストのシートの商品リストと希望小売価格をドラッグで選択して

カンマ2カンマ0かっこ閉じ

と入力します。

※=VLOOKUP([@商品名],商品リスト!A2:B4,2,0)

これで、商品名を変えると、商品名に応じて自動で希望小売価格が表示されるようになりました。

VLOOKUP関数は、一致するワードを検索して、その行の右側の情報を参照することができる関数です。

VLOOKUPのVはVerticalのことで、垂直・縦という意味です。LOOKUPは探すという意味なので、VLOOKUPは縦に検索するという意味です。

1つ目の引数には、検索したいワードを指定します。今回は商品名で検索したいので、角括弧、あっと、商品名、角括弧閉じ、という形式で入力しています。角括弧、あっと、項目名、角括弧閉じという形は、テーブルを設定している表で利用することができる参照方法で、同じ行のその項目の値を参照するという意味になります。つまりC2セルからであればB2セルを参照しますし、C3セルからであればB3セルを参照します。

2つ目の引数には、検索先と参照先の表を指定します。今回は、商品名を検索して、その希望小売価格を参照したいので、先ほど作成した商品リストの表を指定しています。

3つ目の引数には、検索・参照先の表の左から何列目を参照するかを入力します。今回の場合、希望小売価格は左から2列目にあるので、2と入力しています。

4つ目の引数には、検索を「完全一致」で行うか「近似値」で行うかを指定します。今回は完全に一致する商品名を検索したいので、「0」を入力しています。代わりにFALSEと入れても同じ動作をします。ちなみに近似値で検索したい場合は「1」または「TRUE」と入力するのですが、使うシチュエーションはあまり無いので、VLOOKUP関数の4つ目の引数には『0を入れる』と覚えておくと良いと思います。

ここで、VLOOKUPを使う際の注意点を2つ紹介します。

• 1つ目は、2つ目の引数である検索・参照先は、検索したい列が一番左側にくるように指定します。理由はVLOOKUP関数が一番左側の列しか検索できない関数だからです。
• 2つ目の注意点は、検索・参照先の表の検索の列は重複(じゅうふく)しないように作ることです。このように検索列が重複(じゅうふく)した場合は、上にあるデータが参照されます。

VLOOKUP関数を使う際は以上の2点に気を付けてください。

数式を入力できたら、そのセルを選択して右下の小さい四角をダブルクリックして、他の行にも適用します。

参照先を絶対参照に設定する

するとここで、エラーが発生します。エラーが発生しているセルの数式を確認すると、このように参照先の範囲が変わってしまっています。

これは、セルを相対的に参照してしまっていることが原因です。

ここで、相対参照と絶対参照について理解する必要があります。

絶対参照と相対参照の原理について解説します。

今回の場合であれば、2つ目の引数に指定している、検索・参照先のセルが、相対参照になっているため、他の行から参照した際に、参照先がズレてエラーが起こっています。参照元のシートと参照先のシートが別のシートなので、複雑ですが、原理は同じです。

2つ目の引数である検索・参照先のセルの、行番号の手前にドル記号をつけて、行を絶対参照に設定します。

編集セルをダブルクリックかF2キーで編集モードにして、シフトキーを押しながら4でドル記号を入力します。

=VLOOKUP([@商品名],商品リスト!A$2:B$4,2,0)

Enterで確定すると自動でオートフィル機能が適用されて、他の行にも＄記号が入力されています。

これで商品名に応じて、自動で希望小売価格を表示することができました。

商品名を追加しても、このように自動で希望小売価格が表示されます。

Ctrlキーを押しながらZキーで元に戻します。

保存

ここでCtrlキーを押しながらSを押してファイルを保存しておきます。

商品の増減に対応する

次に、VLOOKUP関数の参照先である商品リストに商品が追加された際に、追加された商品も自動で参照されるように設定します。

現在の状態では、商品リストに商品が追加されても、新しい商品は参照されないので、新しく追加された商品の商品名を入力しても、希望小売価格が表示されません。

毎回、数式を編集して、検索・参照先の範囲を選択し直すのは効率が悪いので、商品が追加されても自動でその商品を含めた範囲が参照されるように設定する必要があります。

そこで、商品が追加された際に、追加された商品も自動で参照されるようにする、2つの方法を紹介します。

列指定

1つは列ごと参照する方法です。

2つ目の引数である、検索・参照先の指定をセルの範囲ではなく、列ごと指定することで、その列にいくつ情報が追加されても、参照された状態になります。

テーブル

2つ目の方法は、参照先の表にテーブルを設定して、そのテーブルを参照する方法です。

表にテーブルを設定しておくと、情報が追加された際に、自動でテーブルの範囲が広がるので、参照元から、そのテーブルを参照しておけば、情報が追加されても、自動で追加された情報も参照することができます。

今回は、後者のテーブルを設定する方法を紹介します。※テーブルを設定してテーブルを参照することで後にメリットがあります

まず参照先の表にテーブルを設定します。テーブルを設定したい表を選択した状態で、Ctrlキーを押しながらTを押します。テーブルを設定したい範囲が選択されていることを確認します。範囲が正しく選択されていない場合は、ドラッグで選択し直します。正しく範囲が選択されていることと、先頭行をテーブルの見出しとして使用するにチェックが入っていることを確認したらOKをクリックしてテーブルを設定します。

テーブルには名前をつけることができます。テーブルを参照する際はテーブル名を指定するので、分かりやすいようにテーブル名を変えておきます。テーブルで作成した表のどこかを選択した状態で、上のテーブルデザインタブを開いて、ここからテーブル名を設定することができます。ここでは、『商品リスト』という名前に設定しておきます。

次に、仕切値(しきりね)一覧シートを開きます。希望小売価格のVLOOOKUP関数の2つ目の引数を訂正します。C2セルをF2キーかダブルクリックで編集モードにして、2つ目の引数をドラッグで選択して、Ctrlキーを押しながらXで切り取ります。そのまま、商品リストのシートをクリックして開いて、見出し欄を除くデータをドラッグで範囲選択します。

すると先ほど設定したテーブル名が入力されます。このままEnterで確定すると、テーブルを参照することができます。

新しい商品を追加すると

• DDDD
• 9800

このように参照先のテーブルの範囲が広がるので、

仕切値(しきりね)一覧の表に新しい商品を追加しても、このように希望小売価格が自動で表示されるようになります。

保存

ここでCtrlキーを押しながらSを押してファイルを保存しておきます。

データの入力規則の修正(リスト)

次にデータの入力規則でリスト指定した参照先を、追加された情報も自動で参照されるようにしつつ、先頭行を除いて参照する方法を紹介します。

現在、商品名の欄のデータの入力規則では、商品リストシートのA列にある情報を参照して、A列にある情報のみ入力できるように設定しています。

そのため、先ほど先頭行の項目欄に追加した「商品名」という文字も入力できるようになってしまっています。

よって、列全て参照するという方法を使わず、追加された情報も自動で参照されるように設定したいです。

データの入力規則でリスト指定した参照先に情報が追加された際に、追加された情報も自動で参照されるように設定するには、次の条件を満たす必要があります。

①つは、参照先にテーブルが設定されていること

②つ目は、参照先のセルの範囲に名前が定義されていることです

参照元のセルと参照先のセルが、同じシート内にある場合は①番のみで大丈夫ですが、

参照元のセルと参照先のセルが、別シートになっている場合は①番と②番の両方が満たされている必要があります。

今回は、参照元のセルと参照先のセルが別シートなので、両方満たしている必要があります。

参照先にテーブルの設定は先ほど行ったので①の条件は満たしていますが、②は満たしていないので、参照先のセルの範囲に名前を定義していきます。

商品リストのシートを開いて、商品名のデータを見出しを除く端から端までドラッグで選択します。選択できたら、左上にある名前ボックスを編集することで、名前を定義することができます。

ここでは『商品名リスト』と定義します。入力したらEnterで確定します。

これで、今、選択した範囲に『商品名リスト』と名前を定義することができました。

次に、データの入力規則で今、定義した名前を参照し直します。

仕切値(しきりね)一覧のシートを開いて、B列を選択して、Ctrlキーを押しながら、商品名をクリックして、選択から解除します。選択できたら、上のデータタブからデータの入力規則を開きます。データの入力規則を開いたら、元の値の欄を書き換えます。=の後にセルの範囲に定義した名前をいれます。ここでは商品名リストとなります。

元の値のボックス内を削除して、F3キーを押すと定義した名前の一覧が表示されるので、その名前をダブルクリックすることで、瞬時に入力することができます。

入力できたらOKをクリックすると、『商品名』は選択肢から外れて、商品リストに商品が追加されると、このように選択肢が自動で追加されるようになりました。

保存

ここでCtrlキーを押しながらSを押してファイルを保存しておきます。

終わりに

以上が、エクセルで仕切値(しきりね)を管理する方法と代理店ごとの仕切率や商品ごとの仕切率を算出する表とグラフを作成する方法でした。