ecoslymeです。
売上の明細を集計するときに、ピボットテーブルだとうまく表現できない。ピボットテーブルよりもう少し見せ方を変えたい。
例えば、数量、売上、粗利の順に並んで欲しい。それも横に年月の時系列順に並べて言って欲しいという場面に遭遇したことはありませんでしょうか。
もしかして、頑張って手作業で1つずつ力技で済ませていませんか？
毎月もしくは毎週そんなことをしている社会人は多いのではないでしょうか。
力技の方が早い場合もありますが、圧倒的に無駄な時間が積み重なっていきます。
結局目的は数字を集計、加工して資料を作成することではなく、その数字から何が分かるのか？ 次のアクションは何をすればよいのか？ ということです。
こんなデータ集計や加工に時間をかけることが目的ではありません。
今の時代だからこそ、スマートにデータ処理をして無駄な時間を削減していきましょう。
そんな訳で、今回は売上明細の集計などに便利な、Sumif、Sumifs関数についてご紹介します。
一度設定してしまえば、後はずっと使いまわせます。
元データと結果
⇓
Sumif、Sumifs 関数の使い方
下のデータのように、以下のデータを持つ売上明細があるとします。
シート名は「データ」としています。
売上明細
- 年月
- 売上先コード
- 売上先名
- 商品コード
- 商品名
- 売上数量
- 売上金額
- 利益金額
別シートを作成します。ここに目的の集計表を作成します。
出来上がりのイメージは下の表です。シート名は「集計表」とします。
つまり、この空欄の部分に数式を入れていき、自動で数値が抽出されるようにします。
それでは、Sumifs関数についてご紹介していきます。
F3セルに以下の数式が入力されることになります。いきなりですが、結構数式が長いです。
「 =SUMIFS(データ!$F$2:$F$500,データ!$B$2:$B$500,$A3,データ!$D$2:$D$500,$C3,データ!$A$2:$A$500,F$2) 」
以下のように7つのブロックに分かれています。
見づらいので縦に記載します。
| =SUMIFS | ||
| ( データ!$F$2:$F$500 | 合計対象範囲 |
|
| , データ!$B$2:$B$500 | 条件範囲1 |
|
| , $A3 | 範囲1 |
|
| , データ!$D$2:$D$500 | 条件範囲2 |
|
| , $C3 | 範囲2 |
|
| , データ!$A$2:$A$500 | 条件範囲3 |
|
| , F$2 ） | 範囲3 |
|
最初に元データとなる売上明細の数字があるデータ（数量、売上、粗利のいずれか）を選択してから、あとは好きなだけ条件範囲（売上明細）と範囲（集計表）の順番で複数条件設定していけば良いだけです。
今回の例では、条件範囲と範囲を3ペア設定しています。
では、どのように設定していくか具体的に見ていきましょう。
Sumif、Sumifs 関数の設定方法
F3セルに「 =sumifs( 」と入力します。
すると、下記のような画面になります。
合計対象範囲の選択
対象範囲、範囲の選択
売上先コード
まず最初に売上先コードから条件を絞ります。
・条件範囲1
「データ」シートで売上先コードのあるB2セルを選択します。
「Ctrl + Shift + ↓」のショートカットキーを利用して一番下のデータまで選択します。
この時、「F4」を1回押して固定マーク”$“を付けることを忘れないで下さい。また、最後の「 , 」も忘れないで下さい。
以下のようになったはずです。
・範囲1
その後、「集計表」シートで欲しい売上先コードを選択します。基本的には同じ行の売上先コードです。
ここでのポイントは、A3の前に”$“を付けることです。
最後にこの数式を右にコピーしていくため、A列を固定する必要があります。
こちらも忘れずに、最後に「 , 」を付け忘れないようにしましょう。
商品コード
後は上の売上先コードと行うことは一緒です。
・条件範囲2
「データ」シートで売上先コードの行を選択します。D2～D49です。”$“、「 , 」をつけ忘れないで下さい。
以下のようになります。
年月
年月ももちろん要領は同じです。
最後になりますので、気を引き締めて入力していって下さい。
・条件範囲3
数式が長くなってきますので、間違えないように注意して下さい。
年月は「データ」シートのA列になります。
・範囲3
「集計表」シートでは年月はF列になります。
ここで注意なのは、”$“はF2の2の方に付けます。今までの流れだとFの方に”$”を付けていましたが、今度は数字の方に付けます。
F4を2回押せば数字の方に”$”が付きます。
右にコピーした際に一緒に数式が動いて欲しいからです。
これでようやく完成です。
売上、粗利の数式の設定
以上で数量の数式が入力し終わりました。
同じことを売上と粗利でも行いましょう。
………というのは面倒です。ここで楽をします。
数量と違う数式って考えてみると、一番最初に設定した「合計対象範囲」だけですよね。
数量では「データ」シートのF列を設定しましたが、これをG列（売上）もしくはH列（粗利）に修正すればいいですよね。
そういうわけで、今入力した数式を売上と粗利のセルにコピーしてきます。
F4セルをダブルクリックするか、F2ボタンを押してセルの数式を選択して下さい。
このうち、一番最初のブロックにて F ⇒ G へ変更すればOKです。
同様に、粗利でも F ⇒ H へ変更すればOKです。
そうすると以下のようになります。
数字も更新されています。
ここまで出来れば後はもうすぐです。
この数量、売上、粗利の3つの数式をコピーして、下の段に貼り付けていく作業をします。
コピーして、
貼り付け。（たまたま実績が無いので数字は”０”となっていますが、エラーではありません）
これを一番下まで繰り返します。
3つ選択した後に、選択した一番右下のところにカーソルを持って行くとカーソルが矢印から「＋」マークに変わるので、そこで右下をクリックしたまま下へ引っ張って離すと一気にコピーできるので楽です。
年月の数式設定
とりあえず4月分の数式が出来ました。
あと5月、6月、7月、、、
これも一瞬です。4月の数式をコピーして右へコピーすれば終わりです。
そのために、今まで”$“を色々なところに紛れ込ませてきたのです。
やってみましょう。気持ちいいです。
コピーして、
右へ貼り付け。
以上で、集計表の完成です。
今後データが増えてきたことを考えて、”49″行と設定してきた数式を全て”1000″行というように数式を変更しても良いと思います。
最後に
いかがでしたでしょうか？
最初の設定だけが面倒なだけで、後はコピペで大体済みました。
今までこの数字を数式ではなく、手で1つ1つ書いていった人も多いのではないでしょうか。
今回は売上明細で使用しましたが、他にも各課ごとのPL（損益計算書）の各勘定項目（売上、貢献利益、営業利益、、、）の部や事業部の合計、といった場面でも利用できます。
応用を効かせれば、自分の普段利用している集計表にも展開できるかもしれません。
他のアプローチとしては、ピボットテーブルをうまく改変するという方法もありますので、是非こちらもご覧下さい。
使いやすい方、便利な方を習得頂ければと思います。
ecoslymeです。 毎月、会社や学校の月例会議で月次レポートや月報の提出ってありませんか？ その時に毎回、社内のシステムからデータを出力してきて、データを少し加工してから所定のフォーム[…]
皆さんの日々の無駄な業務をどんどん効率化していきましょう！
