小技集

トップ > 小技集 > 記事
小技集一覧へ
限定コンテンツ一覧へ



2023年3月23日【ID:0】

【Excel】スピルを使って要素単位で自動集計


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

>スピルとは

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

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

=UNIQUE(C3:C10)

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

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

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

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

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

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

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

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

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

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

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


売上の合計

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

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

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

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

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

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

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

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


パソコンで開く場合は、記事の最後に「リンクコピー」があるためご活用ください。


メンバー募集
ExcelVBA学習ロードマップ配布 ExcelVBA学習ロードマップ配布





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

  リンクコピー Twitter Facebook はてなブックマーク Pocket
トップ > 小技集 > 記事
小技集一覧へ
限定コンテンツ一覧へ


- 人気の記事 -



- メンバー限定 [一覧] -



サイト累計閲覧数

5353968

有料動画講座
(買い切り)

Excel完全制覇


ちょっとした機能 便利ツール
【小技集】

【ExcelVBA】VBAを使ってシートの初期設定を自動化

【Excel】グラフにデータを瞬時に追加(離れている範囲でもOK)

【Excel】○○IF(S)関数で使える条件式

【Excel】テーブルを使わずに自動で拡張する範囲設定

【Excel】誤って上書き保存しないための対策

【Excel】各商品の最終購入日を表から抽出する

【Excel】マクロ不要!セルの内容でメール送信!

【Excel】セル単位でパスワードを設定する

【Excel】カレンダーに「休」を表示(祝日などを考慮)

【ExcelVBA】簡単なマウス操作のみで販売数を入力

【Excel】VLOOKUP関数で項目名から値を抽出

【ExcelVBA】複数シートの表を1つにまとめる

【Excel】分布を視覚化するには「ヒストグラム」

【Excel】数式が入力されているセルを自動で色付けする

【Excel】表の各行に空の行を挿入

【Excel】○○IF(S)関数で便利な「*」と「?」とは

【Excel】数式で年齢を求める方法【2選】

【ExcelVBA】現在の日時を取得

【Excel】フリガナを瞬時に自動設定

【Excel】最も頻繁に出現する値を抽出

【Excel】価格の下三桁を480円または980円にする

【Excel】スクロールバーを活用して行の色付け

【Excel】条件付き書式で休日を色付け

【Excel】該当する氏名をカンマ区切りで抽出

【Windows】読めない漢字を入力する





一覧ページへ

トップ > 小技集 > 記事
小技集一覧へ
限定コンテンツ一覧へ

メンバーに加入すると多くの特典が得られます

・全作品ダウンロード
・コンテンツ閲覧管理
・チャットサポート

 メンバー募集は
こちら 


※メンバーページと連携すると表示されなくなります