スプレッドシートで売上を関数で集計する

スプレッドシートに関数を入力する方法が分かっても、各関数の使い方が分からないと実際には使えません。今回は売り上げた商品、購入者、価格、が入力されたシートを使って、商品別売上合計と購入者別売上合計を計算します。

集計できる表を作る

関数の使用例として、スプレッドシートに入力された売上(日付、商品名、金額)を日付別と商品名別にそれぞれ集計する場合を挙げます。使用するのはunique関数とsumif関数です。

スプレッドシートで売上を集計する例

ここでさらっと、スプレッドシートに入力された売上(日付、商品名、金額)と書きましたが、これが重要です。日付別に集計するなら日付欄を、商品名別に集計するなら商品名欄を、用意しましょう。

また日付も商品名も統一性のある書き方をしましょう。2015/10/06と2015年10月6日が混在したり、飴とアメが混在したり、同じものが違う記載がされているとうまく集計できません。誤入力を防ぐには入力を選択式にするもご参照ください。

上図では、A1~C10に売上(日付、商品名、金額)が入力されています。黄色のセルは、1つ下のセルに入力されている関数を表示しています。C13~C15に日付別の売上合計額と、C17~C19に商品名別の売上合計額が計算されています。

unique関数とは

unique関数は、指定した1列(もしくは1行)の範囲から重複する項目を除くリストを得る関数です。

以前掲載した関数の入力手順に従ってA13にunique関数を入力してみます。

  1. 「=」を入力する
  2. 「=unique()」と関数名と()を追記する
  3. 「=unique(A2:A10)」と計算の元になる引数を指定する

unique関数の引数は1列(もしくは1行)の範囲です。このためA列の2~10を表す「A2:A10」を入力しています。このように入力すると、A2~A10の値から重複を除いた「2015/09/22、2015/09/23、2015/09/24」がA13から下に出力されます。

同様にB17に=unique(B2:B10)と入力すると、商品名から重複を除いた「あめ、飲み物、クッキー」がB17から下に出力されます。

sumif関数とは

sumif関数は、特定の条件に該当する数値の合計を求める関数です。

sumif関数も以前掲載した関数の入力手順に従ってC13に入力してみます。

  1. 「=」を入力する
  2. 「=sumif()」と関数名と()を追記する
  3. 「=sumif(A2:A10,A13,C2:C10)」と計算の元になる引数をコンマで区切って指定する

=sumif(A2:A10,A13,C2:C10)の意味は、「A2~A10のセルの内、A13に等しいセルに対応する、C2~C10のセルの数値を合計する」です。このため、A2~A10に2015/09/22と入力されているA2、A3、A4に対応するC2、C3、C4の数値の合計600が計算されています。

sumif関数の引数は3つあり前から、条件に該当するか判別する範囲、条件、合計する数値の範囲、です。条件に該当するか判別する範囲と、合計する数値の範囲は対応している必要があります。今回の例では、どちらも2~10になっています。

sumif関数をコピーする

次にsumif関数を下にコピーして、2015/09/23、2015/09/24の合計も計算します。

この時にそのままコピーすると、C14には=sumif(A3:A11,A14,C3:C11)という式が入力されてしまいます。ここで問題となるのは、2つ目の引数はA14に変わってほしいけれども、1つ目の引数はA2:A10、3つ目の引数はC2:C10と変わってほしくないのに変わってしまっていることです。

このような場合に絶対参照を使用します。変えたくないA2:A10をA$2:A$10とすることで、上下方向にコピーしてもA$2:A$10のままにします。

※横方向にコピーするとB$2:B$10というように変わります。横方向にコピーしても変わらないようにするためには、$A$2:$A$10とアルファベットの前に$をつけます。

同様にC2:C10にも絶対参照を使用して、C13の式を=sumif(A$2:A$10,A13,C$2:C$10)と書き換えてから、C14、C15にコピーすることで目的の2015/09/23、2015/09/24に対応する売上の集計を行うことができます。商品名別に集計する場合も同様に絶対参照を使用しています。