Board logo

標題: [發問] 如何讓公式產生的明細表可以加總 [打印本頁]
作者: s7659109    時間: 2013-9-12 09:13     標題: 如何讓公式產生的明細表可以加總

為何公式產生的數值無法加總，因來源總表欄是屬於
數值，飾選過來確不能加總，原因為何，公式應如何修正。
作者: p212    時間: 2013-9-12 10:47

本帖最後由 p212 於 2013-9-12 10:52 編輯

回復 1# s7659109
以「A001」工作表之H3儲存格公式為例，請修改成
{=--(INDEX(總表!H:H,SMALL(IF(總表!$B$1:$B$21=$A$1,ROW(總表!$B$1:$B$21),4^8),ROW(1:1)))&"")}
之後向下複製公式即可，請參考Hsieh超版的指導 http://forum.twbts.com/thread-1277-1-1.html
作者: s7659109    時間: 2013-9-12 13:44

但如果公式一直往下拉，會出現錯誤(#VALUE!),會影
響加總。
作者: p212    時間: 2013-9-12 17:27

本帖最後由 p212 於 2013-9-12 17:33 編輯

回復 3# s7659109
以「A001」工作表之H3儲存格公式為例，請修改成
{=IFERROR(--(INDEX(總表!H:H,SMALL(IF(總表!$B$1:$B$21=$A$1,ROW(總表!$B$1:$B$21),4^8),ROW(1:1)))&""),"")}
或
{=IFERROR((INDEX(總表!H:H,SMALL(IF(總表!$B$1:$B$21=$A$1,ROW(總表!$B$1:$B$21),4^8),ROW(1:1)))&"")*1,"")}
應可修正「公式一直往下拉，會出現錯誤#VALUE!」的問題，請參考！
作者: Hsieh    時間: 2013-9-12 18:53

回復 1# s7659109

VBA輔助來的容易
  1. Sub ex()
  2. Dim Ay(2), Ary()
  3. Set dic = CreateObject("Scripting.Dictionary")
  4. With Sheets("總表")
  5.     For Each a In .Range(.[B3], [B3].End(xlDown))
  6.       If IsEmpty(dic(a.Value)) Then
  7.          ar = Array(a.Value, "", "進貨", "", "", "出貨", "", "")
  8.          Ay(0) = ar
  9.          Ay(1) = .[A2:H2].Value
  10.          Ay(2) = a.Offset(, -1).Resize(, 8).Value
  11.          dic(a.Value) = Ay
  12.          Else
  13.          Ary = dic(a.Value)
  14.          ReDim Preserve Ary(UBound(Ary) + 1)
  15.          Ary(UBound(Ary)) = a.Offset(, -1).Resize(, 8).Value
  16.          dic(a.Value) = Ary
  17.         End If
  18.       Next
  19. End With
  20. For Each ky In dic.keys
  21.    With Sheets(ky)
  22.      .[A1].Resize(UBound(dic(ky)) + 1, 8) = Application.Transpose(Application.Transpose(dic(ky)))
  23.      .Cells(.Rows.Count, 1).End(xlUp).Offset(1) = "合計"
  24.      .Cells(.Rows.Count, 8).End(xlUp).Offset(1) = "=SUM(R1C:R[-1]C)"
  25.    End With
  26. Next
  27. End Sub
複製代碼
作者: s7659109    時間: 2013-9-13 10:06

1.因的office是2003無法使用iferror的函數，請問要如何修改，另a001與其他a002、a003的欄位都一樣，可否將a001的公式一次套用到a002、a003。
2.如何工作表擴大到50個或200(a001~a200)個做法是否也一樣。
作者: s7659109    時間: 2013-9-13 10:10

有關利用vb的部分，是在工作底稿下按右鍵進入檢示程式碼，直接貼上存檔，我試著貼上無反映，為何？
作者: p212    時間: 2013-9-13 10:24

本帖最後由 p212 於 2013-9-13 10:37 編輯

回復 6# s7659109
若是Excel 2003，公式會變得很長
{=IF(ISERROR(--(INDEX(總表!H:H,SMALL(IF(總表!$B$1:$B$21=$A$1,ROW(總表!$B$1:$B$21),4^8),ROW(1:1)))&"")),"",--(INDEX(總表!H:H,SMALL(IF(總表!$B$1:$B$21=$A$1,ROW(總表!$B$1:$B$21),4^8),ROW(1:1)))&""))}
或
{=IF(ISERROR((INDEX(總表!H:H,SMALL(IF(總表!$B$1:$B$21=$A$1,ROW(總表!$B$1:$B$21),4^8),ROW(1:1)))&"")*1),"",(INDEX(總表!H:H,SMALL(IF(總表!$B$1:$B$21=$A$1,ROW(總表!$B$1:$B$21),4^8),ROW(1:1)))&"")*1)}
請參考！
註：請按 [回覆] 按鈕回映問題，答覆者才會得到通知，謝謝！
作者: s7659109    時間: 2013-9-13 14:10

謝謝提醒!以後我會記住。



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)