以下の表に対して、商品ごとの売上の合計をスピルを使って求めていきます。

＞スピルとは

重複を除いた商品一覧を取得

まずは、C列から重複を除いた商品一覧を取得します。
重複を除いた値を取得するにはUNIQUE関数を使えます。

=UNIQUE(C3:C10)

※UNIQUE関数の他の引数についての解説は割愛します。

ただ、この式ですと、セルC3からセルC10までしか抽出することができません。
そのため大きめに範囲を設定する必要があります。

しかし、次のように大きめに設定すると、何も入力がされていないセルが「0」となり、その値まで表示されてしまいます。
また、設定したサイズよりも大きくなってしまった場合は、再度更新する必要があります。

そこで、範囲を自動で変更させる方法があります。
それはOFFSET関数とCOUNTA関数の組み合わせです。

OFFSET関数とCOUNTA関数の使い方は以下になります。

=OFFSET(参照, 行数, 列数, [高さ], [幅])
// 指定した範囲（参照）から、指定した行数・列数を移動したセルの指定した大きさ（[高さ], [幅]）の範囲を返す

=COUNTA(値1, [値2], [値3], …)
// 指定した範囲（値1~）の空白でないセルの数を返す

この２つを組み合わせて次のような式を作ります。

=OFFSET(C3,0,0,COUNTA(C:C)-1)

このようにして、データが存在する範囲のみを取得することができます。
OFFSET関数の第１引数では、「C3」を選択し、「C3」を基準に抽出しています。
第２引数と第３引数に関しては、指定した行数・列数の移動になりますが、今回は移動する必要がないので、０を設定しています。
第４引数の高さに関しては、存在するデータの数を設定したいので、COUNTA関数でC列全体の空白でないセルの数を取得し、項目名のセルの数が不要になるため、「-1」を行っています。

この数式をUNIQUE関数の引数にすることで可変のサイズで取得することができます。

売上の合計

続いては売上の合計を求めていきます。
条件に一致する合計を求めるにはSUMIF関数を使います。

SUMIF関数の使い方は以下になります。

=SUMIF(範囲, 検索条件, [合計範囲])
// 指定した条件に一致するセルの合計を返す

この検索条件を複数指定することでスピルを活用して一括で表示することができます。
また、スピルにより拡張された範囲は数式のセルに「#」を加えることで選択できます。

よって、次のように求めることができます。

=SUMIF(C:C,F3#,D:D)

範囲は列全体を指定しています。
セルG3に関しては、「C列の中でセルF3と一致する行のD列の合計」を表示しています。
セルG4に関しては、「C列の中でセルF4と一致する行のD列の合計」を表示しています。
他に関しても同様です。

これで、新しいデータが追加された場合に関しても自動で反映することができます。

リンクの共有はこちらから行えます。

  リンクコピー    X Facebook はてなブックマーク Pocket