お世話になります。
「ア」「イ」「ウ」「エ」「オ」という5つの支店で、A、B、C、Dの4種類の商品を売っています。それぞれの支店での商品別の売り上げ個数を集計します。このとき、集計する期間を、年間・月間・今月はじめから今日まで、今年初めから今日まで、といった具合に集計したいのです。データは1枚のシートに、1月1日から日ごとに作成されています。そして、昨年のデータもあります。今日までの売り上げと、昨年の同じ日までの売り上げとも比較したいのです。
特定の日付の売り上げをひっぱってくるところまでは、VLOOKUP関数でできています。
エクセルの数式で、好きな期間の集計データを抽出してくることは可能でしょうか?。
よろしくお願いいたします。
No.2ベストアンサー
- 回答日時:
今のデータがどのように入っているか(並べられているか)にもよりますが、複数の関数をかなり組み合わせれば・・・と思います。
か~~なり、ややこしそうですけどね・・。
例えばA列に縦に日付(1/1,1/2・・・)と入っていて、B列=ア支店A商品売上、C列=ア支店B商品売上…(中略)…、U列=オ支店D商品売上?
vlookupで出来ている、とのことなので上の形式をイメージしました。もしこれなら・・・
素人なりに私が考えそうな策をいくつか。試作してみました。一応出来ました。
【その1】年初から今日まで
各支店各商品ごとに、データとは別の場所に、1/1から今日までの合計を出す。
sum(offset(甲,0,0,counta(乙:丙),1)
甲・乙=一番上のセル(1/1の売上)、丙=12/31の売上のセル
【その2】任意の期間の売上集計
まず、データとは別の場所に、抽出用の入力セルを設定。スタート日、エンド日、支店名、商品名。
・index関数
→集計したい日付(スタート日、エンド日)の入っている行(何番目)を取得
→その後、sum関数。引数にoffset関数を使い、可変に。
sum(offset(甲,乙,丙,丁,戌))
甲=1/1のセル、乙=スタート日の番目-1、丙=求めたい支店商品のある列番、丁=エンド日の番目-スタート日の番目+1、戌=1
また、丙の列番を取るために別に表でも作って定めておかねばならないですね。支店名と商品名を「&」で結合して、ア支店のA商品なら「アA」とでもして、アAなら列番1とかアBなら2、オDなら20が返ってくるようにvlookupでも使いつつ。
【その3】昨年のデータとの比較
昨年データは別シートで同じ形式になっているなら、同様の策を講じれば何とかなりそうですが。
~~~~~~~~~~~~~~~~~~
既に出ていますが、やっぱりアクセスっぽい作業ですね。
でも多少でも参考になってれば幸いです。
わかりにくい書きぶりですみません。
大変ご丁寧な回答をありがとうございました。offset関数とindex関数、参考になりましたが、ちょっと私には十分には理解できませんでした。難しいですね。
offsetのことは理解できませんでしたが、indexを教えてもらったことを参考に、
=SUM(INDEX(B:B,MATCH(B1,A:A,0),1):INDEX(B:B,MATCH(C1,A:A,0),1))
(B列=データ範囲、A列=日付、B1=集計始期のセル、C1=集計終期のセル)で、私がしたいことは何とかできました。
本当にありがとうございました。お礼が遅くなってごめんなさい。
No.4
- 回答日時:
1枚のシートに
A1:A100は月日、B1:B100は支店名、C1:C100は商品名とずらずらと入っているなら、E1に月日、F1に支店名、G1に商品名と記入して、E2~G2に検索したい名前を記入します。そして、
=DCOUNTA(A2:C100,A2,E1:G2) で出来ませんか。個数が出るはずです。
金額の合計なら(D列に金額が入っているとして)、
=SUMPRODUCT((A2:A100=D2)*(B2:B100=E2)*(C2:C100=F2),D2:D100)ではどうでしょう。
No.3
- 回答日時:
集計だけで明細の整理が必要なければピボットテーブルだけでよい
月別集計が必要なので年月の列を増やしてこの列に次の式を入力して
表示形式を年月のみにする
A列が日付で10行目の式 =A10+1-day(A10)
明細も必要ならオートフィルタを利用して事前に抽出して集計する
・オートフィルター:適当なデーターのセルをクリックして
- メニューバーのデーター→フィルター→オートフィルター
明細つきの集計
先ず集計レベルの低いほうから分類(例 商品-支店-年月)
次にフィルター機能を利用してデータを抽出
・集計:適当なデーターのセルをクリックして
- メニューバーのデーター→集計
- 集計レベルの高いほうからやる
- (現在の集計表と置き換えるのチェック枠を外す)
支店数5、商品種別4であれば手動でも出来ますが、手順をマクロに組み込んでおけば次回から楽できます
-
ご丁寧な回答をありがとうございました。教えていただいた内容を検証してみましたが、ピポットテーブルは、私には難しすぎました。貴方の方法が最も適切なのかもしれませんが、私には分かりません。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBA 毎日取得するデータを順番に反映していく方法 6 2023/08/26 16:22
- Excel(エクセル) EXCEL値貼り付け(ある条件のもと自動化) 5 2023/06/06 12:21
- 会計ソフト・業務用ソフト Excelで売上げデータの中の任意の商品の合計を出したい 3 2023/01/18 18:19
- Excel(エクセル) EXCELピボットテーブル関数について 2 2023/04/10 20:35
- その他(Microsoft Office) ピボットテーブルへの集計フィールド挿入 1 2023/02/26 11:33
- 高校 日商簿記3級の勉強中なのですが 精算表が完成せず困っています。 こちらの問題の回答を教えていただきた 2 2023/03/02 09:07
- Excel(エクセル) エクセルで年月ごとの売り上げを集計する方法を教えてください 7 2022/06/01 17:06
- その他(ビジネス・キャリア) 事業復活支援金の事前確認での売上高チェックについて 2 2022/03/26 13:47
- Excel(エクセル) 年齢ごとの商品の販売個数を集計しようとしています。 a列が日付、b列が年齢、c列が販売個数のばあいで 4 2022/09/26 22:35
- その他(データベース) pythonでsqlight勉強中、クエリー結果の利用法教えて下さい 1 2022/04/28 20:38
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【エクセル】オートフィルタで...
-
エクセルで特定の色の数字だけ...
-
フィルタをしても最下行を常に...
-
ピボットテーブルでは時間の表...
-
マクロでピボットテーブルを作...
-
スナックの経費をエクセルの集...
-
VLOOKUP関数とCOUNT関数等の組...
-
VBAで重複する項目を1つにまと...
-
EXCELで増減率を計算したいです
-
ピボットの集計方法「合計」初...
-
エクセルの集計結果が間違って...
-
ExcelVBAでの転記処理エラーに...
-
エクセル2003店舗ごとの集計方法
-
EXCEL 重複データの集計の仕方
-
エクセル、リソース不足エラー...
-
エクセルのピボットテーブルで...
-
EXCELで0を除いた平均値...
-
エクセルのピボットテーブルを...
-
EXCELで月別集計、グラフで実績...
-
Excelの集計結果だけをコピー貼...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【エクセル】オートフィルタで...
-
エクセルで特定の色の数字だけ...
-
ピボットテーブルでは時間の表...
-
フィルタをしても最下行を常に...
-
VBAで重複する項目を1つにまと...
-
アンケートの集計
-
ピボットを更新すると数式が入...
-
複数のピボットテーブルを一括...
-
ピボットの集計方法「合計」初...
-
EXCEL 重複データの集計の仕方
-
いい機能だけど、毎回めんどく...
-
エクセル、リソース不足エラー...
-
excelで集計の合計を降順に並べ...
-
エクセルの小計を自動的に色づ...
-
VLOOKUP関数とCOUNT関数等の組...
-
ピボットテーブルで同じデータ...
-
エクセル 関数を使った横方向の...
-
Excel : 二通りの合計が合わな...
-
ピボットテーブルのページエリ...
-
EXCELで増減率を計算したいです
おすすめ情報