excel - 从具有混合数据类型的范围中返回仅包含数字的数组

Question

• 与下面截图相关，公式

  =IF(ISNUMBER($A$1:$A$5),$A$1:$A$5)
  

  将评估为以下数组

  {1;FALSE;2;44644;3}
  

  但我只需要它返回数字

  {1;2;3}
  

• 如何实现(去掉日期和 bool 值)？

• 请注意，ISNUMBER 已经去掉了错误值等等。

在 VBA 中的使用

• 我想通过评估第二个过程中的正确公式来简化第一个过程中的循环和诸如此类的东西。

正确

Sub CorrectVBA()
' Result:
' 1
' 2
' 3
   
    Const rgAddress As String = "A1:A5"
    
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    Dim rg As Range: Set rg = ws.Range(rgAddress)
    Dim Data As Variant: Data = rg.Value
    
    Dim Arr() As Double
    Dim Item As Variant
    Dim r As Long
    Dim n As Long
    
    For r = 1 To UBound(Data, 1)
        Item = Data(r, 1)
        If WorksheetFunction.IsNumber(Item) Then
            If Not IsDate(Item) Then
                n = n + 1
                ReDim Preserve Arr(1 To n)
                Arr(n) = Item
            End If
        End If
    Next r
       
    If n = 0 Then Exit Sub
       
    For n = 1 To UBound(Arr)
        Debug.Print Arr(n)
    Next n
       
End Sub

错误

• 在这种特殊情况下，我想做这样的事情。
• 这是错误的，因为公式是错误的。

Sub WrongVBA()
' Result:
'  1
' False
'  2
'  44644
'  3
    
    Const rgAddress As String = "A1:A5"
    
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim Data As Variant
    Data = ws.Evaluate("IF(ISNUMBER(" & rgAddress & ")," & rgAddress & ")")
    
    Dim r As Long
    For r = 1 To UBound(Data, 1)
        Debug.Print Data(r, 1)
    Next r
   
End Sub

最后一句话

• 谢谢大家。
• 我决定客观地接受 JvdV 的回答，因为如果还没有的话，Office 365 正在成为一种标准，它的新功能让事情变得更加容易。
• 就个人而言，最有用的答案是 T.M. 和 Ron Rosenfeld 的答案。
• 另外，感谢 Can.U 和 Domenic。

Answer 1

通过公式并不容易，因为 Excel 无法区分 44644 是整数还是日期，除非您查看 cel 的格式。我能想到的唯一合理的方法是使用 CELL() ，根据 this SO上的旧帖子。它不能自己正确返回一个数组，所以我想出了以下使用 BYROW() :

C1 中的公式:

=FILTER(A1:A5,BYROW(SEQUENCE(5,,0),LAMBDA(a,CELL("format",OFFSET(A1,a,0))="G"))*ISNUMBER(A1:A5))

这可以过滤掉日期(给定数据中的格式)，因为不格式化为日期的单元格将返回“G”(“常规”，请参阅指向ms 文档)。我有限的测试表明，这可以根据问题从等式中过滤掉 dates。

---

注意:更具体地说，您可以从等式中排除任何具有您所显示的特定格式('mm/dd/yyyy')的单元格:

=FILTER(A1:A5,BYROW(SEQUENCE(5,,0),LAMBDA(a,LEFT(CELL("format",OFFSET(A1,a,0)))<>"D"))*ISNUMBER(A1:A5))

---

编辑:上述解决方案将是 ms365 独有的。对于 Excel 2019，一种方法是:

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,IF(ISNUMBER(A1:A5),A1:A5,""))&"</s></t>","//s")

• 这是一个 CSE 输入的公式。如果正确完成，返回的数组将是 {1;2;44644;3} ;
• 我们也可以使用 =FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,A1:A5)&"</s></t>","//s[.*0=0]")然而，由于 TEXTJOIN()有一个限制，我认为事先将任何非数字处理为空字符串是明智的；
• 在这个版本的 Excel 中没有办法(我能想到)排除日期。