エクセルデータを使った売上分析について

質問

お知恵を拝借したいと思います。

現在、仕事の一環で去年と今年で商品別の売上増減を調べようと思いますが
品目数が多く関数等で簡単に分析できればと考えております。

データは「商品コード」「商品名」「売上」です。

去年と今年の売上データを添付画像の様に並べて見ましたが廃番になったり
新商品が出たりしておりますので「商品コード」で単純に並べ替えても増減が分かりにくく
困っております。

希望としては商品コードごとの去年と今年の増減が出る様な方法がエクセルで簡単に出来れば
助かります。

出来れば関数で出来ればありがたいです。
よろしくお願い申し上げます。

回答No.1

kuma310min · ベストアンサー

X列に結果を表示させる場合、

X4セルに、
=IF(COUNTIF(A:A,M4)=0,"対象なし",W4-VLOOKUP(M4,A:K,11,TRUE))
下に必要なだけコピー。

で、画像のような感じには表示可能です。
（今年になって廃盤になった商品については、考慮していませんが）

お礼

追加で質問を致しましたがあれこれ考えた結果。
"11"は列番号
＃Ｎ/Ａが出るエラーは関数中の「ＴＲＵＥ」を「ＦＡＬＳＥ」に変えて見ましたら
うまく行きました。
理由は分かりませんがクマ様のベースデータがあればこそです。
ありがとうございました！

補足

早々のご回答誠にありがとうございます。
早速試してみましたが気になる点がございます。

上記関数の中の"11"は行番号ですか？
実際のデータは600行ぐらいあるので"600"と打ち込むと
＃ＲＥＦ！とエラーが出てしまうのです。

また"11"と打ち込んだ際に明らかに同じ商品コードなのに
差額が出ず＃Ｎ/Ａと表示されてしまうのですが何故でしょうか？

ご教授頂けますと幸いに存じます。

回答No.4

KURUMITO

お示しの表がシート1に有るとして去年と今年の表が有りますね。
売上の比較は例えばシート2で分かり易いようにまとめて表示できるようにするためにシート1では作業列を作って対応します。
Ｌ4セルには次の式を入力して下方に℃ラグコピーします。

=IF(OR(A4="",B4=""),"",IF(AND(COUNTIF(M:M,A4)>0,COUNTIF(A$4:A4,A4)=1),MAX(L$3:L3)+1,""))

Ｐ4セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(M4="",N4=""),"",IF(COUNTIF(A:A,M4),INDEX(L:L,MATCH(M4,A:A,0)),""))

これらの作業列が目障りでしたらそれらの列を選択して右クリックし「非表示」を選択すればよいでしょう。

シート2に分かり易い表を作るとしてＡ2セルに商品番号、Ｂ2セルに商品名、Ｃ2セルに去年の売上高、Ｄ2セルに今年の売上高とそれぞれ文字を入力します。
Ａ3セルには次の式を入力してＤ3セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$L:$L),"",IF(COLUMN(A1)<=3,INDEX(Sheet1!$A:$C,MATCH(ROW(A1),Sheet1!$L:$L,0),COLUMN(A1)),IF(COLUMN(A1)=4,INDEX(Sheet1!$O:$O,MATCH(ROW(A1),Sheet1!$P:$P,0)),"")))

これで今年も、去年も商品番号のあるものについての売上の比較を表として見ることができます。

補足

回答ありがとうございます。
シートを分けると見易くなりますね。
大変参考になりました！
またよろしくお願い申し上げます。

回答No.3

picopico_7

No.2の者です。

画像を添付しなおします。
スミマセン。

回答No.2

picopico_7

No.1の方が書かれている通りX列にVlookUp関数式を入れれば昨年の数字をX列に表示させることが出来るのでそこから増減を出すことが出来ると思います。
また、セルL４に添付した画像の通りの関数式(=MATCH($M4,$A$4:$A$11,FALSE))を入れてセルL11までコピペすると2013年の表にないものは「#N/A」が表示されるのでオートフィルターで抽出すれば廃盤になった製品をピックアップすることは出来ると思います。

説明がヘタですみません。
分かりづらかったら補足書いてください。

この投稿のマルチメディアは削除されているためご覧いただけません。

お礼

ありがとうございました！

補足

早々のご回答誠にありがとうございます。
分かりづらくありませんよ(笑)
助かります！