いまさら聞けないExcelの使い方講座

【Excel】消費税率が異なる商品の売上記録を作成するには？エクセルで条件によって表示内容を変えるテク

トップスタジオ

2019年10月9日 06:55

消費税率が変わり売上記録の作成が大変！

　いよいよ今月から、軽減税率制度がスタートしましたね。商品によって消費税率が異なったり、同じ商品でも、例えば飲食料品では、店内飲食とテイクアウトで税率が異なったりするので、混乱しているという人は多いのではないでしょうか。

　今回は、Excelで売上記録を作成している飲食店を想定して、消費税率が異なる商品の売上金額や売上数量を管理する例を考えてみます。実際には、今回解説するようなExcelで売上記録を管理している飲食店は少ないかもしれませんが、Excelのエクササイズとして、一緒に学んでいきましょう。

❶IF関数で条件によって異なる消費税率を求める

　Excelを使って、毎日の売上記録を作成している飲食店の例を考えてみます。この飲食店では、すべてのメニューに対して、店内飲食（イートイン）とテイクアウトが可能だとします。この表では、F列の「消費税率」、G列の「消費税」、H列の「税込価格」が未入力になっているので、これらの空欄を埋めて表を完成させてみましょう。

　まず、表について説明します。来店客が商品を購入したら、A～D列に「時刻」「商品ID」「商品名」「定価」（①）を入力します。来店客が店内で飲食した場合、E列の「区分」にはイートインを表す「E」を、テイクアウトした場合は「T」を入力します（②）。F列には区分によって変わる「消費税率」、G列には「消費税」の金額、H列には「税込価格」を求めて表示します。

　ではまず、表の中で空欄になっている列のうち、F列の「消費税率」を求めてみましょう。ここではIF関数を使います。IF関数の書式は次のとおりです。

　最初の引数の「論理式」には表示内容を変更する条件を入力します。2つ目の引数「真の場合」には、条件に当てはまる場合に表示する内容を、3つ目の引数「偽の場合」には、条件に当てはまらない場合に表示する内容を指定します。

　今回の例で、イートインの場合の消費税率は10％、テイクアウトの場合の消費税率は8％になります。言い換えると、E列の「区分」が「E」の場合、消費税率は10％、そうでない場合は8％になります。これを数式で表現すると、セルF3には「=IF(E3="E",10%,8%)」（③）と入力すればよいことになります。

　数式が入力できたら［Enter］キーを押すと、計算結果（④）が表示されます。セルE3の値が「E」なので消費税率は10％となり、正しい値が表示されたことがわかりますね。

　この数式をF列の他のセルにもコピーしましょう。セルF3の右下にマウスポインターを合わせるとマウスポインターの形が変わる（⑤）ので、そのまま下方向にドラッグ（⑥）します。

　すると、F列の「消費税率」がすべて求められました（⑦）。

　続けて、G列の「消費税」を求めます。消費税は「定価×消費税率」という式で求められますよね。よって、セルG3に「=D3*F3」（⑧）と入力して［Enter］キーを押します。

　セルG3（⑨）に消費税が求められました。セルG3の数式をG列の他のセルにもコピーしましょう。セルG3の右下にマウスポインターを合わせるとマウスポインターの形が変わる（⑩）ので、そのまま下方向にドラッグ（⑪）します。

　G列の「消費税」がすべて求められました（⑫）。

　最後にH列の「税込価格」を求めます。税込価格は「定価＋消費税」という式で求められますよね。よって、セルH3に「=D3+G3」（⑬）と入力して［Enter］キーを押します。

　セルH3（⑭）に税込価格が求められましたね。セルH3の数式をH列の他のセルにもコピーしましょう。セルH3の右下にマウスポインターを合わせるとマウスポインターの形が変わる（⑮）ので、そのまま下方向にドラッグ（⑯）します。

　H列の「税込価格」がすべて求められました（⑰）。

　これで売上記録の表が完成しましたね。この項では、IF関数を使い、「区分」によって「消費税率」に表示する値を変える例を解説しました。次の項では一歩進んで、COUNTIFS関数を使い、それぞれの「区分」ごとに商品別の売上数量を算出してみましょう。

❷COUNTIFS関数で区分ごとに商品別の売上数量を求める

　この項では、イートイン・テイクアウトの区分ごとに商品別の売上数量を求め、表にまとめてみましょう（①）。

　ここではCOUNTIFS関数を使います。COUNTIFS関数は、指定した複数の条件に当てはまる値の個数をカウントする関数で、書式は次のとおりです。

　1つ目の引数「条件範囲1」には、「条件1」を検索する範囲を指定し、2つ目の引数には「条件1」を指定します。3つ目の引数「条件範囲2」には、「条件2」を検索する範囲を指定し、4つ目の引数には「条件2」を指定します。すると、「条件1」と「条件2」の両方を満たす値の個数が求められます。

　ではまず、セルK3に店内で飲食（イートイン）されたブレンドコーヒーの売上個数を求めてみましょう。ここに入力する式は、「=COUNTIFS($C$3:$C$11,J3,$E$3:$E$11,"E")」（②）になります。ここでは、「商品名」のセル範囲C3:C11（③）からはセルJ3（④）の「ブレンドコーヒー」を検索し、「区分」のセル範囲E3:E11（⑤）からはイートインを表す「E」（⑥）を検索し、その両方に当てはまる値の個数を求めたいので、このような式になります。

　③と⑤では、あとで数式をコピーした時に値がずれないように、絶対参照で「$C$3:$C$11」および「$E$3:$E$11」と記述します。反対に④では、数式をコピーした時にセルの位置関係もコピーしたいので、相対参照で「J3」と記述します。

　数式を入力できたら、［Enter］キーを押すと計算結果（⑦）が表示されます。店内で飲食（イートイン）されたブレンドコーヒーの数量は「1」ということがわかります。

　セルK3の数式をK列の他のセルにもコピーしましょう。セルK3の右下にマウスポインターを合わせるとマウスポインターの形が変わる（⑧）ので、そのまま下方向にドラッグ（⑨）します。

　店内で飲食（イートイン）された商品の売上数量がすべて求められましたね（⑩）。

　同様にして今度は、テイクアウトされた商品の売上数量を求めていきます。セルL3に、今度は「=COUNTIFS($C$3:$C$11,J3,$E$3:$E$11,"T")」（⑪）と入力します。最後の引数に「T」を指定することを忘れないでくださいね。数式の組み立て方は先ほどと同じなので、詳しい解説は省略します。

　数式を入力できたら、［Enter］キーを押すと計算結果（⑫）が表示されます。テイクアウトされたブレンドコーヒーの数量は「3」ということがわかります。

　先ほどと同様に、オートフィルで、セルL3の数式をL列の他のセルにコピー（⑬）すれば、表の完成です。