Excel（実践２）目標売上表1

前回の記事では成績表をテーマにExcelの関数やグラフなど多くの機能についてご説明しました
今回は目標売上表1をテーマにExcelの基本操作方法や相対参照と絶対参照に関する考え方についてご説明します
かなり色々な内容が盛り込まれているのでしっかり読んでください
また、読むだけでなく自分で実際にやってみてください

目標売上表1の作成
オートフィル2
相対参照と絶対参照

目標売上表1の作成

以下のような目標売上表1を作成していきます
ただし、C8～E10は空白にしておきます（あとで計算式で埋めるためです）
この表は2017年度のA店、B店、C店の1月～3月売上実績をまとめた表と、
2018年度のA店、B店、C店の1月～3月の売上目標をまとめた表です

まずB2～E5までマウスをドラッグして選択し、一度マウスから手を放します
次に、マウスをB7まで移動し、キーボードのCtrlキーを押さえた状態でB7～E10までマウスをドラッグして選択し、再度マウスから手を放します（キーボードのCtrlキーは押さえたままです）
そして、マウスをB11まで移動し、そのままB11～C11までマウスをドラッグして選択すると以下の状態になります

このように離れたセルを同時に選択する場合はキーボードのCtrlキーを押さえたままマウスでセルを選択するので覚えておきましょう
次にホームタブのリボンのフォントグループの「罫線」ボタンから「格子」を選択します

次にホームタブのリボンのフォントグループの「塗りつぶしの色」からB2～E2を水色、B7～E7をピンクにそれぞれ塗りましょう

B2のセルに「2017年度実績」と入力すると、B2のセルの幅よりも文字が長いためC2まではみ出てしまいました

Excelの列の幅を変えたい時は列と列の間にマウスを移動するとポインターの形が左右の矢印の形に変わるので、その状態でマウスを左右に引っ張ると左側の列の幅を変えることが出来ます
また、その状態でダブルクリックすると左側の列の中で一番長い文字が綺麗に収まる幅に自動で調整されます（行の幅を変更するときも操作は同じです）

残りのセルに対して以下のように数字や文字を入力してください
ただし、B4～B5とB9～B10は空白にしておいてください

オートフィル2

前回の記事で「オートフィル」をご説明しましたが、オートフィルには数式をコピーする以外にもう１つ重要な使い方があり、ある決められた規則で並んでいるデータの場合、オートフィルで縦や横に引っ張ると、規則に沿ってその続きのデータが自動的に並べられるのです
実際に上の表を例にオートフィルを実行してどうなるか見てみましょう
B3のセルを選択し、そのままオートフィルで下へ引っ張ってみるとB4に「2月売上」B5に「3月売上」が自動的に入力されました
今回のようなセルの中身が数字を含んだ文字の場合、自動的に連番を埋めてくれるのです

セルの中身が数字の場合は1つのセルを選択しただけではオートフィルで下に引っ張っても同じ数字が並ぶだけで連番を埋めてはくれません

セルの中身が数字の場合は2つ以上のセルを選択した状態でオートフィルで下に引っ張ると連番を埋めてくれます

ちなみにセルの中身が数字の場合で2つの数字が連番ではない時はExcelが勝手にどのような数字の並びか予想してその続きをオートフィルで埋めてくれます

セルの中身が数字を含まない文字でもオートフィルが自動で規則性を見出して続きを埋めてくれる場合があります

しかしセルの中身が数字を含まない文字でオートフィルが自動で規則性を見いだせずに続きを埋めてくれない場合もあります、この違いはなんでしょう？（以下はドレミファソラシドのドを意図した例）

Excelにはあらかじめ「ユーザー設定リスト」というオートフィルが自動入力する値のリストが登録されており、そのリストに含まれる値だった場合は引っ張ると自動的に入力してくれるのです
では「ユーザー設定リスト」の中身を見てみましょう
Excelの「ファイル」タブをクリックし左下の「その他」から「オプション」をクリックします

「Excelのオプション」画面が開くので「詳細設定」という項目を選び、画面を下の方へスクロールすると「ユーザー設定リストの編集」というボタンがあるのでクリックしましょう

オートフィルで自動入力される値のユーザー設定リストの中身が表示されます

もしこのリストに自分で項目を追加したい場合は、右側の「リストの項目」欄に値をカンマ「 , 」でそれぞれ区切って記入し、右の「追加」ボタンをクリックして「OK」をクリックすると登録できます

ちゃんとユーザー設定リストの追加項目が反映されているか確認してみましょう

相対参照と絶対参照

ここまでで目標売上表1はC8～E10以外のセルが完成しました

C8～E10のセルは2017年度のA店、B店、C店の1月～3月売上実績であるC3～E5の値をもとにして、その1.5倍の数値を2018年度目標として入力しようとしています
まずはC8の数式を2017年度のA店1月売上（C3）と2018年度目標の前年比率（C11）を掛けて「=C3*C11」のようにしましょう
ちなみに、Excelでは掛け算はアスタリスクという記号「 * 」を使用します

C3の値（30）とC11の値（1.5）を掛けてC8に正しい値（45）が入力されました
しかしC8をオートフィルで下へ引っ張ってC9とC10を入力するとどちらも0になってしまいました

C9をダブルクリックして数式の中身を見ると青枠がC4（2017年度のA店2月売上）を示しているのは良いのですが赤枠の2018年度目標の前年比率まで下がってしまったためC4とC12（空白）を掛けて0になっていたのでした
これは前回の記事のRANK関数で山下達郎さんの順位が1位になった現象と同じです

このような問題を解決するためにはセルの場所を示す方法として「相対参照」と「絶対参照」という考え方を理解する必要があります

相対参照とは自分がいる場所から見た相対的な（比べた）位置を示すことで、例えば以下の教室の緑の人から見た黄色の人は「右上」にいます

この黄色の人はそのままの席で、緑の人が左に1つまたは前に1つ移動したとします
その場合、黄色の人の席を先ほどの相対的な位置（右上）で示すと間違いになります

絶対参照とは自分がいる場所に関係なく教室の絶対的な位置を示すことで、黄色の人は緑の人がどこの席にいたとしても「一番前の右から二番目」にいます

では目標売上表1に話を戻しますが、C8の式「=C3*C11」はアクティブセルのC8からみた相対的な位置のC3とC11を掛けていました（つまりC8はC3とC11を相対参照していました）
そしてオートフィルでC8を下に引っ張るとC9の式「=C4*C12」C10の式「=C5*C13」となって0となっていたのです
本来であればC11は絶対参照として、アクティブセルがどこにいてもC11を示す必要があったのです
Excelでは指定のセルに対してドルマーク記号の「 $ 」を列番号と行番号の左に付けて「$列番号$行番号」とすることでそのセルを絶対参照にすることが出来ます
つまりC8の式を「=C3*$C$11」とするとアクティブセルのC8から見てC3は相対参照、C11は絶対参照となり、オートフィルでC8を下に引っ張るとC9の式は「=C4*$C$11」C10の式は「=C5*$C$11」となり正しい値が入力されます