[發問] 請教EXCEL ,報表內 單一產品 當月銷售金額加總

athenahu

請教
我有一個訂單報表 中間部分欄位省略不key

A欄       E欄            M欄
日期  產品:T恤     總價
1/1   產品:胸章
2/1
3/1
4/1
以此類推

我的需要的是一個公式 但我設很久 都不對
譬如整個2月份報表 要列出 單一產品"T恤"每筆總價的加總是多少,就是單一產品銷售額月加總
我不會設定指定月份在函數裡


麻煩各位大大解惑 贈點!

yen956

回復 1# athenahu
試試看：
1. 在欄N(新增補助列【月份】)
公式：=MONTH(A2)
向下拉

2. 選取全部範圍
按 Ctrl+Shift+F3
勾選【頂端列】
(即批次建立名稱)

3.在欄O
公式：=SUMPRODUCT((月份=N2)*1,(產品=E2)*1,總價)
向下拉


單一產品當月銷售金額加總.7z
http://www.mediafire.com/download/6dorsjj1ggdk004/%E5%96%AE%E4%B8%80%E7%94%A2%E5%93%81%E7%95%B6%E6%9C%88%E9%8A%B7%E5%94%AE%E9%87%91%E9%A1%8D%E5%8A%A0%E7%B8%BD.7z

athenahu

回復 2# yen956

yes大大你好,
我沒有要把這個產品報表做同一張資料工作表裡
我不太知道怎麼表達我的需求
我把我正在做的工作表上傳 你下載看看
這個EXCEL裡面有三張工作表
分別是年度訂單表,產品月報表,訂單總報表
我現在要整理的是產品月報表這張工作表 他是需要連結年度訂單表的資料!
但我的公式一直設不好,上網爬文了很多卻都沒辦法!
麻煩您替我看看! 該如何設公式?

檔案網址:http://cht.tw/h/o6tqs

yen956

回復 3# athenahu
試試看:

1. Option Explicit            '強制宣告變數
   
2. Private Sub CommandButton1_Click()
   
3.     Dim sh1, sh2 As Worksheet
   
4.     Dim i, lastRow, 月 As Integer
   
5.     Dim 總價 As Double
   
6.     Set sh1 = Sheets("年度訂單表")
   
7.     Set sh2 = Sheets("產品月報表")
   
8.    
   
9.     lastRow = sh2.[A65536].End(xlUp).Row    '取得A欄 最下面非空白格 的列號
   
10.     sh2.[B2].Resize(lastRow, 12) = ""  '清除 產品月報表
    
11.     sh2.[N2].FormulaR1C1 = "=MATCH(R[-1]C,R2C1:R" & lastRow & "C1,0) + 1"  '寫入公式, 查詢 品項 的列號
    
12.    
    
13.     lastRow = sh1.[A65536].End(xlUp).Row    '取得A欄 最下面非空白格 的列號
    
14.     For i = 3 To lastRow
    
15.         月 = Month(sh1.Cells(i, 1)) + 1
    
16.         sh2.[N1] = sh1.Cells(i, 5)    '年度訂單表的品項 寫入sh2.[N1], 供MATCH公式查詢
    
17.         總價 = sh1.Cells(i, 12)
    
18.         
    
19.         sh2.Cells(sh2.[N2], 月) = sh2.Cells(sh2.[N2], 月) + 總價  '統計單一產品當月總價
    
20.     Next
    
21. End Sub

複製代碼

單一產品當月銷售金額加總0.7z
http://www.mediafire.com/download/zu1r2orgkrpyohc/%E5%96%AE%E4%B8%80%E7%94%A2%E5%93%81%E7%95%B6%E6%9C%88%E9%8A%B7%E5%94%AE%E9%87%91%E9%A1%8D%E5%8A%A0%E7%B8%BD0.7z

Hsieh

回復 3# athenahu

產品月報表!B2公式
=SUMPRODUCT((MONTH(OFFSET(年度訂單表!$A$3,,,COUNT(年度訂單表!$A:$A),))=COLUMN(A$1))*(OFFSET(年度訂單表!$E$3,,,COUNT(年度訂單表!$A:$A),)=$A2)*OFFSET(年度訂單表!$L$3,,,COUNT(年度訂單表!$A:$A),))
向右向下複製

下載 (96.57 KB)

2014-5-9 09:18

athenahu

真的謝謝各位大大 我解決了~ 天知道我這兩天就花了多少時間在公式上 謝謝!

athenahu

回復 5# Hsieh

大大你好,那如果是要做產品月報的總成本呢
表格排列都跟總收入一樣
總成本                                                                                                
            月份
品項"         1月          2月          3月          4月          5月          6月          7月          8月          9月          10月          11月          12月
T恤                                                                                                
徽章                                                                                               
胸章                                                                                               
手環                                                                                               
大圖輸出                                                                                               
印刷品                                                                                               
贊助                                                                                               

我有套用你給的總收入的公式 但不知道怎麼修改成 金額得出來是總成本的金額

Hsieh

回復 7# athenahu

總成本在AE欄?
=SUMPRODUCT((MONTH(OFFSET(年度訂單表!$A$3,,,COUNT(年度訂單表!$A:$A),))=COLUMN(A$1))*(OFFSET(年度訂單表!$E$3,,,COUNT(年度訂單表!$A:$A),)=$A2)*OFFSET(年度訂單表!$AE$3,,,COUNT(年度訂單表!$A:$A),))

athenahu

回復 8# Hsieh


    呵呵 謝謝大大~我後來有設對了! 謝謝你的幫助!