Excelで、年間のデータから月間・累月のデータを集計することできますか？

質問

お世話になります。
「ア」「イ」「ウ」「エ」「オ」という５つの支店で、Ａ、Ｂ、Ｃ、Ｄの４種類の商品を売っています。それぞれの支店での商品別の売り上げ個数を集計します。このとき、集計する期間を、年間・月間・今月はじめから今日まで、今年初めから今日まで、といった具合に集計したいのです。データは1枚のシートに、1月1日から日ごとに作成されています。そして、昨年のデータもあります。今日までの売り上げと、昨年の同じ日までの売り上げとも比較したいのです。
特定の日付の売り上げをひっぱってくるところまでは、ＶＬＯＯＫＵＰ関数でできています。
エクセルの数式で、好きな期間の集計データを抽出してくることは可能でしょうか？。
よろしくお願いいたします。

回答No.2

noname#64582 · ベストアンサー

今のデータがどのように入っているか（並べられているか）にもよりますが、複数の関数をかなり組み合わせれば・・・と思います。
か～～なり、ややこしそうですけどね・・。

例えばＡ列に縦に日付（1/1,1/2・・・）と入っていて、Ｂ列＝ア支店Ａ商品売上、Ｃ列＝ア支店Ｂ商品売上…（中略）…、Ｕ列＝オ支店Ｄ商品売上？
vlookupで出来ている、とのことなので上の形式をイメージしました。もしこれなら・・・

素人なりに私が考えそうな策をいくつか。試作してみました。一応出来ました。

【その1】年初から今日まで
各支店各商品ごとに、データとは別の場所に、1/1から今日までの合計を出す。
sum(offset(甲,0,0,counta(乙:丙),1)
甲・乙＝一番上のセル（1/1の売上）、丙＝12/31の売上のセル

【その2】任意の期間の売上集計
まず、データとは別の場所に、抽出用の入力セルを設定。スタート日、エンド日、支店名、商品名。
・index関数
→集計したい日付（スタート日、エンド日）の入っている行（何番目）を取得
→その後、sum関数。引数にoffset関数を使い、可変に。
sum(offset(甲,乙,丙,丁,戌))
甲＝1/1のセル、乙＝スタート日の番目-1、丙＝求めたい支店商品のある列番、丁＝エンド日の番目-スタート日の番目+1、戌＝１

また、丙の列番を取るために別に表でも作って定めておかねばならないですね。支店名と商品名を「＆」で結合して、ア支店のＡ商品なら「アＡ」とでもして、アＡなら列番１とかアＢなら２、オＤなら20が返ってくるようにvlookupでも使いつつ。

【その３】昨年のデータとの比較
昨年データは別シートで同じ形式になっているなら、同様の策を講じれば何とかなりそうですが。
～～～～～～～～～～～～～～～～～～
既に出ていますが、やっぱりアクセスっぽい作業ですね。
でも多少でも参考になってれば幸いです。
わかりにくい書きぶりですみません。

お礼

大変ご丁寧な回答をありがとうございました。offset関数とindex関数、参考になりましたが、ちょっと私には十分には理解できませんでした。難しいですね。
offsetのことは理解できませんでしたが、indexを教えてもらったことを参考に、
=SUM(INDEX(B:B,MATCH(B1,A:A,0),1):INDEX(B:B,MATCH(C1,A:A,0),1))
（Ｂ列＝データ範囲、Ａ列＝日付、Ｂ１＝集計始期のセル、Ｃ１＝集計終期のセル）で、私がしたいことは何とかできました。
本当にありがとうございました。お礼が遅くなってごめんなさい。

回答No.4

noname#153814

１枚のシートに
Ａ１：Ａ１００は月日、Ｂ１：Ｂ１００は支店名、Ｃ１：Ｃ１００は商品名とずらずらと入っているなら、Ｅ１に月日、Ｆ１に支店名、Ｇ１に商品名と記入して、Ｅ２～Ｇ２に検索したい名前を記入します。そして、
=DCOUNTA(A2:C100,A2,E1:G2) で出来ませんか。個数が出るはずです。
金額の合計なら（Ｄ列に金額が入っているとして）、
=SUMPRODUCT((A2:A100=D2)*(B2:B100=E2)*(C2:C100=F2),D2:D100)ではどうでしょう。

お礼

残念ながらデータの形式がおっしゃるようにはなっていませんでした。
ご丁寧な回答をありがとうございました。

回答No.3

shinkami

集計だけで明細の整理が必要なければピボットテーブルだけでよい

月別集計が必要なので年月の列を増やしてこの列に次の式を入力して
表示形式を年月のみにする
A列が日付で10行目の式 ＝A10+1-day(Ａ10)

明細も必要ならオートフィルタを利用して事前に抽出して集計する
・オートフィルター：適当なデーターのセルをクリックして
- メニューバーのデーター→フィルター→オートフィルター

明細つきの集計
先ず集計レベルの低いほうから分類(例 商品-支店-年月）
次にフィルター機能を利用してデータを抽出
・集計：適当なデーターのセルをクリックして
- メニューバーのデーター→集計
- 集計レベルの高いほうからやる
- （現在の集計表と置き換えるのチェック枠を外す）

支店数5、商品種別4であれば手動でも出来ますが、手順をマクロに組み込んでおけば次回から楽できます

-

お礼

ご丁寧な回答をありがとうございました。教えていただいた内容を検証してみましたが、ピポットテーブルは、私には難しすぎました。貴方の方法が最も適切なのかもしれませんが、私には分かりません。
ありがとうございました。

回答No.1

Cupper

ピボットテーブルやアウトライン機能を使うとできそうな気もしますが、
そのような集計は Excel よりも Access の分野かと思います。

何と言っても抽出の条件が多すぎです。
抽出条件を設定するだけで面倒になりますよ。
Access なら初めから抽出条件を選択するだけで欲しい結果を得られるように作りこむことができます。

お礼

Accessはよくわかりません。やはりエクセルではしんどい作業なんですね。それだけ分かっただけでも納得です。
ありがとうございました。