Excelで年間売上を集計をする方法について

Question

いつもお世話になっております。

昨年の商品別売上を集計しています。
Excelで1タブにつき1ヶ月分の商品別売上高のリストが入っているのですが、
これらを全部まとめて集計することは可能でしょうか。

商品名　商品番号　単価　個数　売上高　件数
でリストになっているものを商品管理番号ごとに統合し、
個数・売上高・件数
を合計したリストを作りたいと思っています。

　商品名　商品番号　単価　個数　売上高　件数
　あああ　aaaa　500　5　2500　5
　ああい　aaaa　500　4　2000　3
　ああう　aaaa　500　2　1000　1
　ううう　uuu　800　2　1600　2
　ううえ　uuu　800　5　4000　5
　ううお　uuu　800　3　2400　2
　ううう　uuu　800　2　1600　1
　　↓
　あああ　aaaa　500　11　5500　9
　ううう　uuu　800　12　9600　10

ピポットテーブルを使ってやってみたものが近いのですが、うまく操作出来ずに挫折しました。
商品番号は同じで商品名が異なるものも、同じ商品として集計したいです。
マクロは触ったことが無いので、関数などで出来ればと思っています。
少々面倒でも構いませんので、なにかやり方はありますでしょうか。

よろしくお願いいたします。

Answer

ECマスターズの中野です。

> 昨年の商品別売上を集計しています。
> Excelで1タブにつき1ヶ月分の商品別売上高のリストが入っているのですが、
> これらを全部まとめて集計することは可能でしょうか。

まとめて集計された場合はまず、1枚のシートに
以下の様に1~12月までの売上をご入力ください。

（イメージ）
対象月　　　商品名　商品番号　単価　個数　売上高　件数
2020年1月　あああ　aaaa　500　5　2500　5
2020年1月　　ああい　aaaa　500　4　2000　3
2020年1月　　ああう　aaaa　500　2　1000　1
2020年1月　　ううう　uuu　800　2　1600　2
2020年1月　　ううえ　uuu　800　5　4000　5
2020年1月　　ううお　uuu　800　3　2400　2
2020年1月　　ううう　uuu　800　2　1600　1
2020年2月　あああ　aaaa　500　5　2500　5
2020年2月　　ああい　aaaa　500　4　2000　3
2020年2月　　ああう　aaaa　500　2　1000　1
2020年2月　　ううう　uuu　800　2　1600　2
2020年2月　　ううえ　uuu　800　5　4000　5
2020年2月　　ううお　uuu　800　3　2400　2
2020年2月　　ううう　uuu　800　2　1600　1


そして、ピポットテーブルを使集計する際は、
商品番号で集計してください。

一度操作いただき、またご不明点はお知らせください。

Answer

ありがとうございます。

1枚のシートに全ての月の売り上げを入力しました。
ピポットテーブルを使って商品番号で集計とのことで、

列に値
行に商品番号・商品名・単価
値に個数・売上高・件数

と入れてみたのですが、分かりにくいので
商品番号と商品名と単価も横並びにすることは可能ですか？
商品名や単価を値に入れると合計されてしまいます。

横並びが出来ない場合は、下線の場所を変えることは可能ですか？
緑の枠で1商品ですが、商品番号に下線が引かれていて分かり辛いです。

また、ピポットテーブルを使わない集計方法がありましたらご教示願えますでしょうか。

よろしくお願いいたします。###HR######IMG09ad4777f96898d96aa672fc71ed7c275.jpegIMG0###

Answer

ECマスターズの中野です。

> kami-kyotoさん

お待たせして申し訳ございません。
添付画像確認させていただきました。

> 列に値
> 行に商品番号・商品名・単価
> 値に個数・売上高・件数

> と入れてみたのですが、分かりにくいので
> 商品番号と商品名と単価も横並びにすることは可能ですか？
> 商品名や単価を値に入れると合計されてしまいます。

> 横並びが出来ない場合は、下線の場所を変えることは可能ですか？
> 緑の枠で1商品ですが、商品番号に下線が引かれていて分かり辛いです。

デザイン > レポートのレイアウト > 表形式で表示

を選択し、列に「月」「対象月」、個に「合計/件数」
を選択いただくと商品番号、商品名、単価も横並びに
表示いただけます。

ピポットテーブルのフィールドなどの詳細は
以下のスクリーンショットをご参考に
操作いただきご確認お願いいたします。
https://ec-masters.gyazo.com/665c1c9f58839ed807821b2529f35d75


> また、ピポットテーブルを使わない集計方法がありましたらご教示願えますでしょうか。

集計したい項目を並べてsumif関数で集計可能です。
以下の動画が分かりやすく解説されておりますので
よろしければご視聴くださいませ。

ExcelのSUMIF関数の使い方
https://www.youtube.com/watch?v=5QouWGWxtRk

Answer

SUMIF関数の動画とてもわかりやすかったです。
当初の希望通りの事が出来ました！
ありがとうございます！

ピポットテーブルでも商品番号、商品名、単価を横並びにできました。
月ごとの売り上げを表にすることが出来ると知りまして、そちらでも集計データを作ることにしました。

現在ひとつの商品につき表示が3行～になっています。(→画像1)

フィールドの折りたたみをすれば1行になるのですが、
そうすると商品名と単価の表示が無くなってしまいます。(→画像2)

ここに商品名と単価を表示させる方法はありますでしょうか。
商品名が複数ある商品も、商品名ごとの集計は不要なので、どれかひとつの商品名を表示させたいです。
単価は途中で変わっているものもありますが、最終月などの単価を表示させたいです。

商品名が異なっていても商品番号が同じであれば、同じ商品です。
　1月　◯◯ no.1 カバー付き 3枚入り 日本製
　2月　◯◯ no.1 カバー付き 3枚
　3月　◯◯ no.1 3枚入り カバー付き
つまり、1ヶ月の中で別の商品名にはなりません。

希望は、1商品番号1行で、
商品番号　商品名　単価　1月　…　12月　総計
aaa　　　あああ　 500 　500　…　 500 　3000
uuu　　　ううう　 800　 800　… 　800 　8000
と並ばせたいです。(→画像3)

何卒よろしくお願いいたします。###HR######IMG0cac031a28edb449121c7c935684578cc.jpegIMG0### ###IMG1d06f40cc4b9ec525229d49a5735a952d.jpegIMG1### ###IMG28949bf4a068cf66c6343395da52b9f72.jpegIMG2###

Answer

追加で申し訳ないのですが、カテゴリごとの傾向も知りたいので、
カテゴリごとに分類して総計をとる方法もあれば教えていただきたいです。

商品番号　商品名　単価　1月　…　12月　総計
　aaa　　　あああ　 500 　500　…　 500 　3000
　uuu　　　ううう　 800　 800　… 　800 　8000
Aカテ　　　　　　　　　  1300  …　1300　11000
　ooo　　　おおお　 600 　600　…　 600 　4800
　eee　　　えええ　 900　 900　… 　900 　9000
Bカテ　　　　　　　　　　 1500  …　1500　13800

よろしくお願いいたします。

Answer

ECマスターズの中野です。

> ここに商品名と単価を表示させる方法はありますでしょうか。

このままでは集計できかねますので
以下の手順でまずはデータを整理し集計してください。

１．商品名を統一するための項目を追加してください。

２．商品マスターを別のシートに作成してください。

３．１．で作成した項目に商品管理番号で２．の
マスターから商品名を引き当ててください。
（VLOOKUP関数で引当可能です。）

４．もう一度、ピポットテーブルで集計してください。


> 単価は途中で変わっているものもありますが、最終月などの単価を表示させたいです。

商品名同様に、単価の項目を別途追加し、
商品マスターから紐付けていただき、
その後ピポットテーブルで集計してください。


> 追加で申し訳ないのですが、カテゴリごとの傾向も知りたいので、
> カテゴリごとに分類して総計をとる方法もあれば教えていただきたいです。

こちらも上記ご説明した同様の手順で行なっていただけます。
１年分まとめシートにカテゴリーの項目を追加し、
上記ご説明内容と同様にカテゴリ商品マスターから
カテゴリーを引き当て、その後ピポットテーブルで集計してください。


上記ご説明いたしました項目の追加につきましては、
添付画像の黄色いセルをご確認ください。
https://ec-masters.gyazo.com/1302b18ea7b237c976552789c944517a

※添付画像の6行O列、8行O列の様なカテゴリ集計を非表示にしたい場合は、
以下の画像を参考に右クリック→チェックを外すにて非表示が可能です。

ご参照画像
https://ec-masters.gyazo.com/4a6b6c44f1e08f0f609b9f765e64874d

Answer

>２．商品マスターを別のシートに作成してください。

>３．１．で作成した項目に商品管理番号で２．の
>マスターから商品名を引き当ててください。
>（VLOOKUP関数で引当可能です。）

商品マスター＝1つの商品番号に1つの商品名・単価が表になっているデータ
という認識で合っていますでしょうか。


上記認識の上で、商品マスターシートを作り集計データシートにVLLOKUP関数を入力してみたのですが、エラーが出てしまいます。

商品マスター
　A列-商品名　B列-商品番号
集計データ
　A列-月　B列-商品名　C列-追加商品名　D列-商品番号
として、集計データC3セルに
　=VLOOKUP(D3,商品マスター!$A$2:$B$524,1,FALSE)
と入力しましたが、
＃N/A　無効な値のエラー
と出ます。

よろしくお願いいたします。###HR######IMG0d99aea2a6c5b11cd8f2fbe83e08b516b.jpegIMG0### ###IMG1886747c2b960fe6a40ca5ffc1602f01b.jpegIMG1###

Answer

ECマスターズの中野です。

お待たせしております。

> 商品マスター＝1つの商品番号に1つの商品名・単価が表になっているデータ
> という認識で合っていますでしょうか。

はい。ご認識の通りで問題ございません。

> 上記認識の上で、商品マスターシートを作り集計データシートにVLLOKUP関数を入力してみたのですが、エラーが出てしまいます。

添付画像を確認させていただきました。
商品マスターシートのA列とB列を以下の様に
入れ替えて再度VLOOKUP関数で再度引き当ててください。

※VLOOKUP関数の仕組み上、検索値となるデータは、
検索する範囲の中で1番左端の列である必要がございます。

A列 商品名
B列 商品番号

↓

A列 商品番号
B列 商品名

分かりやすい動画がございましたので
こちらをご視聴いただき再度操作くださいませ。

https://www.youtube.com/watch?v=brRd8RHWFT4

Answer

なんとか形にすることが出来ました。

＞※VLOOKUP関数の仕組み上、検索値となるデータは、
＞検索する範囲の中で1番左端の列である必要がございます。

「範囲内のどこを検索値とするか」を指定するところがない
=検索値は範囲内の左端の列
ということですね。

詳しく教えていただきとても助かりました。
紹介していただいた動画を確認して勉強したいと思います。

ありがとうございました！

Answer

ECマスターズの中野です。

> 「範囲内のどこを検索値とするか」を指定するところがない
> =検索値は範囲内の左端の列
> ということですね。

はい、左様でございます。
無事に集計いただけたとの事、ご報告いただきありがとうございます。

またお困りの際はお気軽にご連絡くださいませ。

Answer

すみません。
解決済みにしてしまったのですが、もう一点教えていただきたいことがございます。

集計表(画像 表)を元に、グラフを作成しました。
積み上げ棒グラフでカテゴリごとのおおまかな割合が分かるようなものです。

月ごとの集計は見やすいのですが、年間を通じた売上推移の比較がしにくいな、と思い
月間合計を折れ線グラフで表そうとすると、(画像 現在)のようになってしまいます。

(画像 理想)のようにする方法を教えていただけますでしょうか。

よろしくお願いいたします。###HR######IMG01c1f70fa9cfe476b6ba17ee000457495.jpegIMG0### ###IMG109db0c91181aea6bd1aadf6cb7253d19.jpegIMG1### ###IMG24e7a4b4b159565e42cd97493fbdf4b17.jpegIMG2###

Answer

ECマスターズの中野です。

再度ご連絡いただいた件につきまして、
順番に回答させていただきますので
しばらくお待ちくださいませ。

Answer

ECマスターズの中野です。

お待たせして申し訳ございません。
添付画像を確認させていただきました。

おそらく「表」に空白の列がございますので、
この空白列を削除して再度折れ線グラフを作成いただけますでしょうか。

ご参照：折れ線グラフ 作成方法 
https://www.nojima.co.jp/support/koneta/59243/

Answer

表に空白列がないと、

1月1月　2月2月　3月3月
とならずに
1月　1月　2月　2月　3月　3月
と等間隔で並んでしまいます。

また、折れ線グラフが
19年1月→20年1月→19年2月→20年2月
と繋がってしまいます。

理想は、前のレスに添付の画像通り
19年1月→19年2月→19年3月
20年1月→20年2月→20年3月
と繋がってほしいです。

これをどちらも解消する方法はありますでしょうか。

(元の表に要素をいくつか追加したので若干変わっていますがやりたいことは同じです)

何度も申し訳ありませんが、何卒よろしくお願いいたします。###HR######IMG0e126f422a1011d7e542d56e2feb23021.jpegIMG0###

Answer

ECマスターズの中野です。

いただいたご返信につきまして、
実際のデータを拝見して回答させていただきたく存じますが、
データをお送りいただけますでしょうか。

「投稿内容について報告する」より、
お送りいただきます様よろしくお願いいたします。
https://ec-masters.gyazo.com/222d6e51c5b5cbd6b7f917f5977c9712

お手数おかけいたしますがよろしくお願いいたします。

Answer

Excelをお送りさせていただきました。

よろしくお願いいたします。

Answer

ECマスターズの中野です。

データをお送りいただきありがとうございます。
順番に回答させていただいておりますので、
しばらくお待ちいただければと思います。

どうぞよろしくお願いいたします。

Answer

ECマスターズの中野です。

>　kami-kyotさん

お待たせしております。

1/20 11:15に添付いただいた画像「理想」も確認いたしますと、
折れ線グラフは総額合計を結んでおりましたので、
元の表を少し変更し、折れ線グラフを別途作成いたしました。

各項目の数字を確認させていただきましたが、
2019年、2020年月ごとの売上の把握、そして
2019年2020年の売上推移を比較された場合は、
添付ファイルの様にグラフをそれぞれ分けた方が
把握しやすくなるかと存じます。

当初の理想の形とは異なりますが、
ご確認いただけますと幸いです。

お送りいただいたExcelデータは細かな情報が
記載されておりますので、別途メールでお送りさせていただきます。
よろしくお願いいたします。

Answer

ありがとうございます。

確かに、無理に1つのグラフにまとめるよりも分かりやすいですね。
こちらで、まとめようと思います。

お手数おかけいたしました。
とても助かりました。
ありがとうございました！