前回の記事では目標売上表1をテーマにExcelの基本操作方法や相対参照と絶対参照に関する考え方について説明しました
今回は目標売上表2をテーマに複合参照という相対参照と絶対参照の中間のような考え方や新しい関数（VLOOKUP、SUMIF）について学習していきましょう
かなり色々な内容が盛り込まれているのでしっかり読んでください
また、読むだけでなく自分で実際にやってみてください
目標売上表2の作成
以下のような目標売上表2を作成していきます
ただし、G3～K11は空白にしておきます（あとで計算式で埋めるためです）
この表はあるスーパーの商品ごとの年間売上目標金額をまとめた表です
消費税は2017年～2018年は8%でしたが2019年～2020年は10%なので消費税を含めた金額を求める際は売上（税抜）に対して2017年～2018年は1.08倍、2019年～2020年は1.1倍して値を求めます
「罫線」や「塗りつぶしの色」はこれまで学習した内容で出来ると思うので説明は省略しますがH2～K2はセルの中で改行されて2段になっています
セルの中で改行するにはAltキーを押さえたままEnterキーを押すと改行出来ます
また、M2～P2は4つのセルが合体して1つの大きなセルの塊になっています
これはM2～P2を選択した状態で「ホーム」タブのリボンの配置グループから「セルを結合して中央揃え」ボタンをクリックすると作れます
セルの結合を解除する場合は、結合しているセルを選択し「セルを結合して中央揃え」ボタンを再度押すと元に戻ります
相対参照と複合参照
G列の「売上（税抜）」はE列の「単価」とF列の「数量」を掛けて求められるので、G3の式を「=E3*F3」としてE3とF3を相対参照した式で求め、オートフィルで下に引っ張ってG11まで埋めましょう
では続いてH列の「売上（税込）2017年度」について考えてみましょう
H列の「売上（税込）2017年度」はG列の「売上（税抜）」にM列の「2017年度の消費税率」を掛けて求められます
仮にH列の「売上（税込）2017年度」の値としてG列の「売上（税別）」の相対参照とM列の「2017年度の消費税率」の相対参照を掛けたらどうなるでしょう
H3の式を「=G3*M4」としてオートフィルで下に引っ張ってみましょう
H3の式を「=G3*M4」としてオートフィルで下に引っ張るとH4以降が全部0になってしまいました
これはH3の式がM4を相対参照にしたため、H4以降の式がM5以降を相対参照したことが原因です
では、H3の式でM4を絶対参照としたらどうなるでしょう
H3の式を「=G3*$M$4」としてオートフィルで下に引っ張ってみましょう
H3の式を「=G3*$M$4」としてオートフィルで下に引っ張るとH4以降も正しい値が埋められていて特に問題はなさそうです
では今度はH3をオートフィルで右に引っ張るとどうなるでしょう
H3の式を「=G3*$M$4」としてオートフィルで右に引っ張ると小数がいっぱい出てきて色々おかしい感じがするのでI3の中身を見てみると本来はI3はG3（250）とN4（1.08）を掛けた値（270）になるはずがH3（270）とM4（1.08）を掛けた値となっています
改めてH3の式を見ると「=G3*$M$4」なので、オートフィルで引っ張るとG3は相対参照なので下にも右にも移動するし、M4は絶対参照なので下にも右にも移動しない設定になっています
今回の場合はG3は下には移動させたいけど右には移動させたくないし、M4は下には移動させたくないけど右には移動させたいので相対参照も絶対参照もどちらも当てはまりません
この場合は複合参照というセルの指定の仕方が適しています
複合参照とはオートフィルで引っ張った場合に縦または横のどちらか片方だけ移動し、もう片方は固定する参照方法です
複合参照は縦に移動し横を固定する場合は「$列番号行番号」、横に移動し縦を固定する場合は「列番号$行番号」という書き方になります
つまり、H3の式は「=$G3*M$4」とするとオートフィルで下や右に引っ張っても正しい値を入力することが出来ます
あとはI3、J3、K3をオートフィルで下に引っ張れば目標売上表2は完成です
相対参照と絶対参照と複合参照はオートフィルを使うためには必須の考え方なので
難しいかも知れませんがしっかり理解するようにしましょう
VLOOKUP関数
Excelの関数の中で比較的よく使うけど比較的難しくて有名なVLOOKUP関数というのがあります
ここではVLOOKUP関数の使い方を学習していきますが、その前にVLOOKUPとはVertical（垂直）なLOOKUP（検索）という英語の略で、Excelの関数名は英語と関係のある名前が付いていることが多いので、関数名は英語の意味とセットで覚えると良いですよ
さて、VLOOKUP関数の使い方ですが「=VLOOKUP(検索したい値 , 検索範囲 , 見つかったセルの左から何列目を表示するか , 完全一致か不完全一致か)」という書き方で、「ある値を特定の範囲内で上から下に検索し、見つかったセルの左から数列目の値を返す」という関数です
言葉では分かりにくいので実際にVLOOKUP関数の使い方の例を見てみましょう
先ほど完成した目標売上表2の下のB13～C14に以下のような「商品コード」と「売上（税抜）」を追加して、B14に3001を入力しましょう
C14の式を「=VLOOKUP(B14 , B3:G11 , 6 , FALSE)」とすると以下のような結果になります
第一引数の値は検索したい値なので、B14（3001）が検索したい値となります
第二引数の値は検索範囲なので、B3～G11が検索範囲となります
第三引数の値は検索で見つかったセルを基準にして左から何列目を表示するかなので、G列（売上（税抜））はB列から数えて6列目なので6となります
第四引数の値は検索としてぴったり同じ値が見つかった場合のみ結果を返す（完全一致 = FALSE）か、ぴったり同じ値が無かった場合は近い値で結果を返す（不完全一致 = TRUE）かなので、今回は完全一致とするためFALSEとなります
上記を要約すると『 B14の値についてB列のB3～B11を上から下へ垂直に検索し、ぴったり同じ値が見つかったセル（B9）の左から6番目（G9）の値を返す 』ということです
分かりにくければB14の値を他の値に変えてC14がどのように変わるか試してみると良いでしょう
第一引数～第三引数までは分かりやすいと思いますが第四引数の不完全一致（TRUE）の意味が分かりにくく、検索してるのにぴったりじゃない値の結果を返すってどんな場面？と疑問に思うかも知れません
VLOOKUP関数の第四引数が不完全一致（TRUE）の場合の使い方の例を見てみましょう
以下は誰かに誕生日プレゼントを買ってあげようとして、予算内でなるべく値段の高いものをプレゼント候補から選ぼうとしています
C3の式は「=VLOOKUP(C2 , B6:C15 , 2 , TRUE)」となり、検索する値はC2（5000）で検索範囲はB6～C15、見つかったセルの左から2列目（C列）の値を返す式になります
予算の5000円に対してぴったりの金額のプレゼント候補はないので、5000円に最も近い3000円の花束が表示されました
しかし、7000円のゲームソフトも5000円に最も近いのにどうして表示されないのでしょうか
実はVLOOKUPの第四引数が不完全一致（TRUE）の場合は「検索する値を超えない範囲で最も近い値を返す」という制約があるので、たとえ予算が6999円だったとしても3000円の花束になります
さらにVLOOKUPの第四引数が不完全一致（TRUE）の場合はもう1つ制約があり、検索範囲の左端の列の値が昇順（小さい順）に並んでいなければ正しい値を返してくれません
このようにVLOOKUP関数の第四引数の不完全一致（TRUE）は検索する値以下で最も大きいものを探すときに有効ですが制約があるので使うときは気を付けなければなりません
また、VLOOKUP関数は第四引数を省略して「=VLOOKUP(C3 , B6:C15 , 2)」のように書くことも出来ますが、その場合は不完全一致（TRUE）を指定したことと同じになります
SUMIF関数
最後にSUMIF関数というSUM関数とIF関数が合わさったような関数を学びましょう
SUMIF関数は特定の条件に合った値の行の指定した範囲の合計を求める関数で「=SUMIF（検索範囲 , 検索条件 , 合計を求める範囲）」という書き方をします
実際の使い方の例を見てみましょう
先ほどの目標売上表2の下のB16～C19に以下のような「商品カテゴリ」と「売上合計（税抜）」を追加して、B17に果物、B18に野菜、B19に魚介を入力しましょう
C17の式を「=SUMIF(C$3:C$11 , B17 , G$3:G$11)」として、オートフィルで下に引っ張ると以下のような結果になります
C17の式はC3～C11の範囲の中でB17（果物）に一致する行（3行目～5行目）を探して、一致する行のG3～G11の範囲の数値の合計（250+600+800=1650）を求めています
なお、C17をオートフィルで下に引っ張ってC18とC19を求める際に第一引数（C$3:C$11）と第三引数（G$3:G$11）は縦に固定するように複合参照としていますが、今回のようにオートフィルで横に引っ張らない場合は複合参照でも絶対参照でもどちらでも構いません
次回は出欠表というテーマでデータの入力規則と条件付き書式について学習しましょう
コメント