excel01

フリマのお金で友達と揉めないために!売上をサクッと集計できるSUBTOTAL関数のポイント【Excel女子】

2017/9/14

TOP > BLOG > フリマのお金で友達と揉めないために!売上をサクッと集計できるSUBTOTAL関数のポイント【Excel女子】

こんにちは、ヴィーナス・ブラスト(Venus Blast)の広報ミナコです! 最近はメルカリが女子の間でも大流行。個人同士で物を売ったり買ったりするのが当たり前になってきましたね。

そんな中、アプリだけじゃなく「フリーマーケットへ友達と一緒に出店している」という方もいるのでは? そこで今回は、フリマ出店時の売上管理を簡単にしてくれるExcelのお役立ち関数をご紹介します!

 

Excelを使えばフリマの売上管理はもっと楽になる!

フリーマーケットと言えど、お金がからめば商売です! 誰の物がいくら売れたのかがはっきりしていないと、後からケンカの火種になる可能性も……これじゃあせっかくの楽しいフリマが台無しですよね? そんな時こそExcel女子の腕の見せ所です!

 

【準備編】Google フォームで簡易レジ作成!

まずはいつ、誰が、いくらで商品を売ったのかをはっきりさせるための準備に取りかかりましょう。ノートに手書きしてもいいですが、それだと後でデータ入力の手間が増えちゃうかも? Excel女子にそんな非効率作業はNG! ここは、便利なWebサービスを使って、簡易的な管理フォームを作ってしまいましょう。

利用するのはGoogleフォーム。本当はExcelのフォーム機能を自作してもいいのですが、出先にパソコンを持っていくのはちょっと面倒ですよね? Googleフォームなら、スマートフォンから操作ができてしまうので非常に便利です。以下で作成の手順を見ていきましょう。

  1. Googleドライブにアクセスして、新規ボタンから「その他」>「Googleフォーム」をクリック

    1

  2. それぞれの項目を作成します。今回は最小限の項目で十分なので、「商品」「販売価格」「出品者」「販売者」の4つを用意。後半のふたつは、プルダウン式で選択できるようちょっとだけ工夫もしてみました

    1

  3. あとは商品を販売する都度、このフォームにデータを入力していけばいいだけです

    1

  4. フリマが終了する頃には、こんな感じで売上データができあがりました!

    1

 

それぞれの出品者の売上合計をSUBTOTALで求めよう!

売上データが出揃ったら、次はいよいよ集計です。まずは先ほどのデータを扱い易いようにExcelへコピー。

1

次に、とりあえず全体でいくら売り上げたのかを計算してみましょう。ここで使う関数は、Excel初心者の方でもすぐに思いつきますよね? とりあえずSUMを使って、販売価格を範囲指定し、一気に合計を求めてみましょう。

=SUM(C2:C14)

1

さて、次はいよいよ売上の分配です。当然、出品したアイテムごとの売上を集計しなくてはなりません。やり方はいろいろありますが、今回は手っ取り早くフィルター機能を使って出品者を絞り込みます。

1

ここでひとつ問題が……。合計の欄を見ていただくと分かるとおり、本来であればミナコが出品した商品の売上金額は「1800円+4200円+1200円+800円=8000円」のはずです。しかし、合計欄は先ほどと同様、26800円になってしまっています。この理由は、SUMだとフィルターで隠れているセル内の数値も合計してしまうからです。

そんな問題を解決してくれるのがSUBTOTALです。この関数は、フィルターで表示されているセルのみを合計してくれる機能を持っているため、フィルターを変える度に合計を計算し直してくれます。合計のセル(C15)に、以下の関数を入れてみましょう。

=SUBTOTAL(9,C2:C14)

1

このように、SUBTOTALとフィルター機能を組み合わせれば、出品者ごとの売上合計が簡単に集計できるようになります。

1

SUBTOTALの解説

ここで少しだけSUBTOTALについての解説です。この関数は、以下のような値を入力して使います。

=SUBTOTAL(集計方法,参照範囲

参照範囲についてはSUMと同じ。合計したい数値のある範囲を指定して入力します。今回の場合ですと「C2:C14」ですね。問題は集計方法の部分。ここは、どのような数や値を集計するのかを設定する場所です。今回の「9」は合計値を求めるという機能を持った集計方法に当たります。

ワンポイントコラム!
フィルターに対応するのはSUBTOTALとAGGREGATEの2種類のみ

上記でもご紹介したとおり、SUBTOTALはフィルターで表示されたセルのみを計算してくれる便利な関数です。Excelの中でこうした機能を持つ関数は珍しく、あとはAGGREGATE(セルの合計や個数などの集計値を求める関数)くらいしかありません。

こうして考えると、さまざまな関数を使い、より複雑な集計を行う場合には、できるかぎりフィルター機能は組み合わせないほうが無難とも言えます。データ処理ではなく、閲覧用のシート作成といった用途で利用するほうが、面倒がないかもしれませんね。

 

SUBTOTALは職場でも大活躍!

「事業所ごとの売上合計を出して欲しい」
「担当者の平均取引額が知りたい」
「商品ごとの販売個数を表示したい」

職場でこうした指示を上司から受けた際には、ぜひ今回ご紹介しているSUBTOTALを活用してみましょう。すでにご紹介した合計値(9)だけでなく、以下のような集計だって思いのままです。

集計方法 集計機能 Excel関数で言うと?
1 or 101 平均 AVERAGE関数
2 or 102 数値の個数 COUNT関数
3 or 103 データの個数 COUNTA関数
4 or 104 最大値 MAX関数
5 or 105 最小値 MIN関数
6 or 106 範囲掛け算 PRODUCT関数

その他にも、集計方法にはまだまだ種類がありますので、業務内容に合わせて使い分けてみてください。

 

フィルター機能と組み合わせない場合の活用方法

先ほどはフィルター機能と組み合わせた集計方法をご紹介しましたが、SUBTOTALはそれぞれの小計を計算し、最後に合計を出す時にも使われています。ちょっと分かりにくいので、実際の例で見てみましょう。

1

上記は支店の商品販売一覧データです。合計は分かっているものの、支店ごとの小計が分かりません。そこで、以下のように表を修正しました。

1

お気づきの方も多いかもしれませんが、このままだと小計に入力された数値も一緒に集計されてしまい、合計の金額が合わなくなってしまいます。そこで活用したいのがSUBTOTALです。

1

上記の赤枠の部分では、先ほどまでSUMによる計算が行われていました。これをSUBTOTALに変えると、参照範囲内のSUBTOTALを無視した状態で集計が行われます。間に小計を挟んだとしても、合計が狂うようなことはありません。

少し回りくどいですか? ポイントは、小計を挟む場合にはSUMではなくSUBTOTALを使おう! ということ。これさえ覚えておけば、正確な集計表が作れますよ!

***

今回は主にSUBTOTALの使い方についてご紹介しました。データの作り方次第では、SUMでも集計はもちろんできます。しかし、面倒な作業を少しでも楽にしたいのであれば、こうした“かゆいところに手が届く”関数を覚えておくのが大切ですね。一緒にフリマに出店した友達とお金で揉めないためにも、普段の業務で「この子、デキるな!」と思われるためにも、ぜひ覚えておきましょう!

それではまた次回!

  • facebook
  • twitter