求助 - Excel統計問題

[atie]

謝謝說明，我會試試看的。

主要是要統計每日開機的機台有幾台。

因為有時機台每日生產的產品有兩種、三種的產品。

現場人員在填表格時會將生產機台與對應生產產品都寫在生產統計單上，

但我主要是只想要知道今天有幾台機器有開機生產，

因為單子上會記錄著重覆的機台編號，很難去計算。

也試很多的公式，但都無法正確得知，所以才會請教諸位論友。

[getter]

作者: atie

謝謝說明，我會試試看的。

主要是要統計每日開機的機台有幾台。

因為有時機台每日生產的產品有兩種、三種的產品。

現場人員在填表格時會將生產機台與對應生產產品都寫在生產統計單上，

但我主要是只想要知道今天有幾台機器有開機生產，

因為單子上會記錄著重覆的機台編號，很難去計算。

也試很多的公式，但都無法正確得知，所以才會請教諸位論友。

使用函數的也是有缺點的 ...

比方說有些情況是有公式的函數限制 ... 比方說長度或是深度的限制 ...
目前迪西比較確定的式 IF 有巢狀深度的限制，一格儲存格最多 7 層，如 IF( IF( IF( ... )))。
超過無法結束與計算。新版的 Excel 2007 或 2010 還有沒有這種限制就不清楚了。
本次的這種 if + if +if ... 就不知道限制何時會出現限制了。

就變成要使用 VBA 來開發「自訂函數」來使用，用法也會像 Excel 內建函數的用法
以這次的例子來說 ...

理論上 VBA 可以把這個阿姨要的功能的函數寫的不用指定條件 ...
動作上就是
1.選擇範圍
2.排除重複項目
3.統計項目
4.回饋資料

迪西目前懂的 VBA 部份，可以處理 2~4，1 就搞不出來，因為選擇範圍的話
有分 A1:B2 或是 A1,A5,C3,B4:B16 ... 諸多類型，這裡迪西就卡住了 ...
所以放棄 ...

若真的要使用「自訂函數」的話，阿姨還要會插入 VBA 「自訂函數」才有用，所幸
這不難很簡單，網路上都找的到教學。比較麻煩的是，有些公司會把 Excel 的 book
作安全鎖定，只開放局部輸入或是安全性限制。

[atie]

我也是卡關了.............

卡了近一個星期了...........

試這近一個星期了，套入公式後去驗證，都是錯誤.......

[getter]

為自己的Excel加入自定義函數

可以參考以下：
Excel 2010 可以自訂公式（函數）來補足內建公式（函數）無法處理的情形
為自己的 Excel 2003 加入自定義函數
如何在另一個活頁簿中使用自訂函式
Excel 2003 自訂函數_簡單說明
如何讓 Excel 2010 的自訂函數顯示說明文字

以阿姨提的例子 ... 迪西也寫了一個「自訂函數」 ...

用法就是跟一般的函數差不多，經過測試，無法接受「連續型的位址 (A1:A8)」 ...
只要輸入的參數其中有一個「連續型的位址」是就會有問題了 ＃VALUE!，如下：

A9=COUNT_NR(A1:A8)
A9=COUNT_NR(A1,A2, ...,B1:C3, ...)
A9=COUNT_NR(A1:A8,B1,C3,A18)

值與儲存格混合的也不行，一樣會有 ＃VALUE!
A9=COUNT_NR(1,1,2,3,5,4,4,1,B1,C2,D6)

只要拿掉這個「連續型的位址 (A1:A8)」就正常了，如下：

A9=COUNT_NR(A1,A2,A3,A4,A5,A6,A7,A8)
A9=COUNT_NR(1,1,2,3,5,4,4,1)


把以下的方塊中的 VBA 程式碼，貼到工作表「巨集/VBA編輯器中」，可以參考上述連結
的文章，程式效果與 IF、COUNTIF + IF、COUNTIF ... 相當類似

Function COUNT_NR(ParamArray argList() As Variant)
  ' 函數(1,2,3, ...) 或是 函數(A1,B2,C3, ...) ok
  ' ＃VALUE! 異常終止 類型：
  ' 1.引數其中之一只要有 A1:A3 這種連續型位址
  '   函數(A1:A8)
  '   函數(A1,A2, ...,B1:C3, ...)
  '   函數(A1:A8,B1,C3,A18)
  ' 2、值與儲存格混核的類型
  '   函數(1,1,2,3,5,4,4,1,B1,C2,D6)
  '   函數(A1,A2, ...,1,2,3, ...)
  
  Dim i, j, Count As Variant
  
  For i = LBound(argList) To UBound(argList)
    If i = UBound(argList) Then
       Exit For
    End If
    For j = i + 1 To UBound(argList)
      If argList(i) = "" Then
         Exit For
      End If
      If argList(i) = argList(j) Then
         argList(j) = ""
      End If
    Next j
  Next i

 
  Count = 0
  For Each arg In argList
    If arg <> "" Then
       Count = Count + 1
    End If
  Next arg
  
  COUNT_NR = Count
    
End Function

[atie]

非常謝謝您抽出時間為我解答，

感恩您的協助......

[getter]

基本上 Excel 的內建公式與 VBA 自訂公式 ... 各有千秋

怎麼說... 內建公式比較簡單易上手 ... 自訂公式需要有一定程度的程式設計的基礎
以本樓來說兩者都可以達到效果 ... 但是若是某個概念去做的話，如 SUM() 來說
VBA 自訂公式，就很糟糕 ... 主要問題不是演算結果，而是 () 的輸入範圍的關係。

以內建公式來說 SUM () 也好或是其他的公式，很多都是可以接受 A1:B3 這類的
連續範圍的輸入，有的甚至混合其他的如資料+儲存格+連續範圍，都 OK。
這點反觀 VBA 自訂公式，就迪西以目前網路上找到的結果是，如前述那樣的狀況，
且無比較理想或適當的範例可以求解。

以此樓的問題來說，若以內建公式的話，對於「資料的選擇輸入範圍」比較理想，
比較不方便的地方，是要不斷的修改 COUNTIF 的條件 ... 可以利用記事本，先編
公式好了在貼過來。
若是以 VBA 自訂公式的話，這個「資料的選擇輸入範圍」，要先按住「ctrl」一個
一個點，資料範圍少就算了 ... 很多且多到一個量以上 ... 就知道痛苦了 ...。

基本在很多狀況是混用的 ... 能解決問題比較重要 ...

[getter]

那個 VBA 迪西這兩天找了一個範例，僅只能使用一組 連續參照的範例，
被迪西意外想到以某種方式實現，在 (值) 的輸入上可以像 SUM () 一樣
有彈性的使用了。

初步已在 Excel 2003/2010 上面跑過 ... 效果對不對就 ... 再討論

COUNT_NRN 以 (數值) 為條件的統計計算方式，功能、使用方式跟 COUNT 差不多

Function COUNT_NRN(ParamArray Value() As Variant)
  '═════════════════════════
  '     COUNT_NRN ver. 1a 
  '     動作1：資料選擇範圍
  '
  ' 以 (數值) 為條件的計算資料出現的次數，
  ' 當有重複的（值）時後，該值只計算一次。
  ' 使用方式、功能類似於 COUNT 內建函數。
  '
  ' Value：可以是 (數值) 、單一參照值、連續參照值，
  ' 數量尚最少 1 個，多個資料時，以 "," 區分。
  '═════════════════════════
  Dim i, j, Count, arg, Value_new() As Variant
  On Error Resume Next
  
  Count = 0
  For i = LBound(Value) To UBound(Value)
    Err.Clear
    For Each arg In Value(i)
      Count = Count + 1
    Next arg
  Next i
  Err.Clear
  
  ReDim Value_new(Count - 1)  
  j = 0
  For i = LBound(Value) To UBound(Value)
    Err.Clear
    For Each arg In Value(i)
      Value_new(j) = arg
      j = j + 1
    Next arg
    If Err.Number = 92 Then Value_new(j - 1) = Value(i)
  Next i
  Err.Clear

  For i = LBound(Value_new) To UBound(Value_new)
    If WorksheetFunction.IsNumber(Value_new(i)) = False Then Value_new(i) = ""
    If i = UBound(Value_new) Then Exit For
    For j = i + 1 To UBound(Value_new)
      If Value_new(i) = "" Then Exit For
      If Value_new(i) = Value_new(j) Then Value_new(j) = ""
    Next j
  Next i

  Count = 0
  For Each arg In Value_new
    If arg <> "" Then Count = Count + 1
  Next arg
  COUNT_NRN = Count
End Function

COUNT_NRA 以 (字串) 為條件的統計計算方式，功能、使用方式跟 COUNTA 差不多

Function COUNT_NRA(ParamArray Value() As Variant)
  '═════════════════════════
  '     COUNT_NRA ver. 1a
  '     動作1：資料選擇範圍
  '
  ' 以 (字串) 為條件的計算資料出現的次數，
  ' 當有重複的（值）時後，該值只計算一次。
  ' 使用方式、功能類似於 COUNTA 內建函數。
  '
  ' Value：可以是 (字串) 、單一參照值、連續參照值，
  ' 數量尚最少 1 個，多個資料時，以 "," 區分。
  '═════════════════════════
  Dim i, j, Count, arg, Value_new() As Variant
  On Error Resume Next
  
  Count = 0
  For i = LBound(Value) To UBound(Value)
    Err.Clear
    For Each arg In Value(i)
      Count = Count + 1
    Next arg
  Next i
  Err.Clear
  
  ReDim Value_new(Count - 1)  
  j = 0
  For i = LBound(Value) To UBound(Value)
    Err.Clear
    For Each arg In Value(i)
      Value_new(j) = arg
      j = j + 1
    Next arg
    If Err.Number = 92 Then Value_new(j - 1) = Value(i)
  Next i
  Err.Clear
  
  For i = LBound(Value_new) To UBound(Value_new)
    If WorksheetFunction.IsNonText(Value_new(i)) Then Value_new(i) = ""
    If i = UBound(Value_new) Then Exit For
    For j = i + 1 To UBound(Value_new)
      If Value_new(i) = "" Then Exit For
      If Value_new(i) = Value_new(j) Then Value_new(j) = ""
    Next j
  Next i

  Count = 0
  For Each arg In Value_new
    If arg <> "" Then Count = Count + 1
  Next arg
  COUNT_NRA = Count
End Function

COUNT_NR2 以 (數值 + 字串) 為條件的統計計算方式，功能、使用方式跟 COUNT、或 COUNTA 差不多

Function COUNT_NR2(ParamArray Value() As Variant)
  '═════════════════════════
  '     COUNT_NR2 ver. 1a
  '     動作1：資料選擇範圍
  '
  ' 以 (數值 + 字串) 為條件的計算資料出現的次數，
  ' 當有重複的（值）時後，該值只計算一次。
  ' 使用方式、功能類似於 COUNT、或 COUNTA 內建函數。
  '
  ' Value：可以是 (數值) 或 (字串)、單一參照值、連續參照值，
  ' 數量尚最少 1 個，多個資料時，以 "," 區分。
  '═════════════════════════
  Dim i, j, Count, arg, Value_new() As Variant
  On Error Resume Next
  
  Count = 0
  For i = LBound(Value) To UBound(Value)
    Err.Clear
    For Each arg In Value(i)
      Count = Count + 1
    Next arg
  Next i
  Err.Clear
  
  ReDim Value_new(Count - 1)  
  j = 0
  For i = LBound(Value) To UBound(Value)
    Err.Clear
    For Each arg In Value(i)
      Value_new(j) = arg
      j = j + 1
    Next arg
    If Err.Number = 92 Then Value_new(j - 1) = Value(i)
  Next i
  Err.Clear
  
  For i = LBound(Value_new) To UBound(Value_new)
    If WorksheetFunction.IsLogical(Value_new(i)) Then Value_new(i) = ""
    If i = UBound(Value_new) Then Exit For
    For j = i + 1 To UBound(Value_new)
      If Value_new(i) = "" Then Exit For
      If Value_new(i) = Value_new(j) Then Value_new(j) = ""
    Next j
  Next i
  
  Count = 0
  For Each arg In Value_new
    If arg <> "" Then Count = Count + 1
  Next arg
  COUNT_NR2 = Count
End Function

COUNT_NRS 以 (數值 + 字串) 為條件的統計計算方式，以 (模式) 選擇條件的統計計
算方式，基本用法 OUNT_NRS(模式,數值,數值,…)
模式一定要有，不可以略過 0~2
0 表示以（數值）計算。
1 表示以（字串）計算。
2 表試以（數值 + 字串）計算

後面的 數值 部份則可以如 SUM 一樣的選取。

Function COUNT_NRS(ByVal mode As Byte, ParamArray Value() As Variant)
  '═════════════════════════
  '     COUNT_NRS ver. 1b
  '     動作1：資料選擇範圍
  '
  ' 以 (模式) 選擇條件的計算資料出現的次數，
  ' 當有重複的（值）時後，該值只計算一次。
  ' 使用方式、功能類似於 COUNTIF 內建函數。
  ' 引數的使用方式 類似於 函數(模式,值,值,…)
  ' Mode：
  '       0 表示以（數值）計算。COUNT_NRS(0,A1:B8,111,566)
  '       1 表示以（字串）計算。COUNT_NRS(1,A1:B8,C1,D5,"ABC","AAV")
  '       2 表試以（數值 + 字串）計算。COUNT_NRS(2,A1:B8,C1,D5,111,"AAV")
  ' Value：可以是 (數值) 或 (字串)、單一參照值、連續參照值，
  ' 數量尚最少 1 個，多個資料時，以 "," 區分。
  '═════════════════════════
 Dim i, j, Count, arg, Value_new() As Variant
  On Error Resume Next
  
  Count = 0
  For i = LBound(Value) To UBound(Value)
    Err.Clear
    For Each arg In Value(i)
      Count = Count + 1
    Next arg
  Next i
  Err.Clear
  
  ReDim Value_new(Count - 1)  
  j = 0
  For i = LBound(Value) To UBound(Value)
    Err.Clear
    For Each arg In Value(i)
      Value_new(j) = arg
      j = j + 1
    Next arg
    If Err.Number = 92 Then Value_new(j - 1) = Value(i)
  Next i
  Err.Clear

  For i = LBound(Value_new) To UBound(Value_new)
    Select Case mode
      Case 0
        GoTo term_1
      Case 1
        GoTo term_2
      Case 2
        GoTo term_3
      Case Else
        GoTo term_3
    End Select
term_1:
    If WorksheetFunction.IsNumber(Value_new(i)) = False Then Value_new(i) = ""
    GoTo term_Next
term_2:
    If WorksheetFunction.IsNonText(Value_new(i)) Then Value_new(i) = ""
    GoTo term_Next
term_3:
    If WorksheetFunction.IsLogical(Value_new(i)) Then Value_new(i) = ""
    GoTo term_Next
term_Next:
    If i = UBound(Value_new) Then Exit For
    For j = i + 1 To UBound(Value_new)
      If Value_new(i) = "" Then Exit For
      If Value_new(i) = Value_new(j) Then Value_new(j) = ""
    Next j
  Next i
  
  Count = 0
  For Each arg In Value_new
    If arg <> "" Then Count = Count + 1
  Next arg
  COUNT_NRS = Count
End Function