Gary's Student's questions

我正在尝试帮助同事增强电子表格。我们正在使用 Excel 365。

在单元格C3中，我们有公式：

=FILTER(O1:O51,O1:O51>E1)

它会生成一个动态的结果列，这些结果将根据E1中的值增长或缩小。在C1中，我们有一个对以下单元格求和的公式：

=SUM(C3#)

这一切都有效。

我的同事想把公式放在动态溢出的底部。他还希望公式随着溢出的增长或缩小而自动上下移动。所以在插图中，公式将进入单元格C20。

我不知道如何做到这一点，或者即使它是可能的。

我的建议是不包括公式。首先设置过滤器参数，然后运行这个简短的宏：

Sub dural()
    Dim r As Range, rNext As Range
    Set r = Range("C3#")
    Set rNext = r(r.Count + 1)
    
    rNext.Formula = "=SUM(C3#)"
End Sub

宏会将公式放入正确的单元格中。同事回应“不够自动化”

任何建议都将受到欢迎。

我正在尝试帮助某人使用 VBA 在 Microsoft Word 中自动进行一些非常简单的文本编辑。

一项期望的更改是删除嵌入在数字中的逗号，同时保留其他逗号。例如：

I like apples, pears, and plums.  See 1,234,567

会成为：

I like apples, pears, and plums.  See 1234567

该代码查找一个数字，后跟一个逗号，后跟一个数字。要删除两位数之间的逗号，当前代码循环从0,0到9,9Find/Replace的所有 100 种可能的情况，并对每种情况执行 a 。所以0,0变成00。这是当前代码：

Sub FindAndReplace()

Dim rng As Range, sFind As String, sRep As String
Dim i As Long, j As Long

For i = 0 To 9
    For j = 0 To 9
        sFind = i & "," & j
        sRep = i & j
        For Each rng In ActiveDocument.StoryRanges
            With rng.Find
                .Text = sFind
                .Replacement.Text = sRep
                .Wrap = wdFindContinue
                .Execute Replace:=wdReplaceAll
            End With
        Next rng
        Next j
    Next i
lbl_Exit:
Exit Sub

我确信所有这些循环都是完全没有必要的i，j 这可以通过简单的通配符模式来完成Find，但我对 Word 对象模型的了解不够，无法实现这一点。

更新：

RexEx 解决方案运行良好。我还制定了一个通配符解决方案：

Sub CommaKiller()
    Dim rngStory As Range

        For Each rngStory In ActiveDocument.StoryRanges
            With rngStory.Find
                .MatchWildcards = True
                .Text = "([0-9]),([0-9])"
                .Replacement.Text = "\1\2"
                .Wrap = wdFindContinue
                .Execute Replace:=wdReplaceAll
            End With
        Next rngStory
End Sub

使用 Excel 365

我有一个小的 VBA 用户定义函数，可以根据逗号作为分隔符将字符串解析为子字符串。我正在尝试用工作表公式替换 UDF 以制作无宏的工作簿。

UDF 是：

Public Function parsee(s As String)
    Dim i As Long, arr, a
    
    arr = Split(s, ",")
    ReDim output(1 To 1, 1 To UBound(arr) + 1)
    
    i = 1
    For Each a In arr
        output(1, i) = a
        i = i + 1
    Next a
    
    parsee = output
    
End Function

在 Excel 365 中，这以类似于以下方式溢出TextToColumns：

基于此之前的帖子：

使用 filterxml 从字符串中提取子字符串

我一直在使用：

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s"))

除非输入字符串包含&字符（如A2中的字符串） ，否则此方法的工作方式相同。在这种情况下FILTERXML()返回#VALUE！

是否有一个简单的解决方法可以像任何其他角色一样FILTERXML()接受& ？

使用 Excel 365。

我在A列和B列中有一些文本数据，例如：

101r    MC
101r    TU
101r    VV
101r    VL
w257    HU
w257    QI
w257    JJ
w257    YW
315L    GD
315L    OO
315L    NL
w257    BHU

我想以二维格式总结

在D1我输入公式：

=UNIQUE(FILTER(A:A,LEN(A:A)))

效果很好并且很好地溢出。在E1我输入：

=TRANSPOSE(FILTER(B:B,A:A=D1))

这也很有效并且很好地溢出。然后我手动向下复制E1以获得：

我希望E1公式的传播是动态的。

我试过的：

=TRANSPOSE(FILTER(B:B,A:A=INDEX(D:D,SEQUENCE(COUNTA(D:D)))))
=TRANSPOSE(FILTER(B:B,A:A=INDIRECT("D" & SEQUENCE(COUNTA(D:D)))))

这些公式都没有溢出（甚至根本不起作用）。但是，就其本身而言，以下部分确实有效：

=INDEX(D:D,SEQUENCE(COUNTA(D:D)))

是否可以动态向下拖动E1 ？

我的目标是创建一个跨多个工作表的单个单元格值的表作为溢出数组。

我目前的做法是输入：

=INDIRECT("Sheet" & ROWS($1:1) & "!A1")

并手动向下复制。我第一次尝试：

=INDIRECT("Sheet" & SEQUENCE($Z$100) & "!A1")

其中Z100包含3。这会溢出，但会返回#VALUE！ 于是我倒退了一步，试了试：

=INDIRECT("Sheet" & SEQUENCE(3) & "!A1")这也失败了。我向后退了几步，尝试了一些非常简单的方法：

=INDIRECT("A" & 1)哪个有效
=INDIRECT("A" & ROWS($1:1))，哪个也有效，但因
=INDIRECT("A" & SEQUENCE(1))同样的错误而失败（？我不知道为什么？）。所以：

• 我可以SEQUENCE()在里面工作INDIRECT()吗？
• 如果没有，是否有另一种方法可以在许多工作表中制作一个动态溢出表？

我有一个简短的用户定义函数，给定一个字符串和列中的一组可能的子字符串，将返回找到的第一个子字符串的位置。

我被要求用非 VBA 公式替换 UDF。

Option Explicit
Public Function Locate(s As String, rng As Range) As Variant
    Dim pos As Long, p As Long, cell As Range
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction

    Locate = CVErr(xlErrNA)
    pos = 999999

    For Each cell In rng
        p = InStr(s, cell.Text)
        If p > 0 Then
            pos = wf.Min(pos, p)
        End If
    Next cell

    If pos <> 999999 Then Locate = pos
End Function

这是一个例子：

子字符串在列F中。该函数找到b和q并返回 5，因为q先出现。

我有一个适用于特定实例的公式：

=MIN(FIND({"a","b","c","d","q"},A1 & "abcdq",1))

我一直在尝试找到一种方法来替换{"a","b","c","d","q"}and"abcdq"引用一系列单元格，但我很难过。

在 Excel 365 环境中，我尝试插入动态溢出公式：

Sub WhyDoesThisFail()
    Range("A1").Formula = "=SEQUENCE(10)"
End Sub

奇怪的是我得到：

有一个免费的@，没有溢出。（如果我手动输入公式，我会得到预期的结果。）

同样奇怪的是，如果我输入公式，String我会得到预期的结果！

Sub WhyDoesThisWork()
    Range("A1") = "=SEQUENCE(10)"
End Sub

为什么会.Formula失败？？谁能解释发生了什么事？？

使用最近在 Excel 365 中引入的动态溢出数组，我编写了一个超级简单的公式来将字符串中的字符解析为单个单元格。例如，如果我有：

qwertyuiop$

在A1中，我选择一个单元格并输入：

=MID($A$1,ROW(1:11),1)

并且公式溢出：

这样可以避免输入=MID($A$1,ROWS($1:1),1)和手动向下复制。

我遇到的问题是数字11。我使用11是因为我知道A1的长度是 11。我想更改MID()公式以使其适应A1的长度，而不是我重新输入公式。

我试过了：

=MID($A$1,ROW(1:LEN(A1)),1)

但这失败了。我目前的解决方法是：

=FILTER(MID($A$1,ROW(1:9999),1),MID($A$1,ROW(1:9999),1)<>"")

这似乎是使11成为变量的一种非常愚蠢的方法。

我提供了一个 VBA 解决方案，但被拒绝了。

试图用一个非常简单的COUNITF/COUNTIFS公式来帮助朋友。数据如下：

目标是计算不包括mike、john、judy的数据单元。当前公式：

=COUNTIFS(A2:A13,"<>mike",A2:A13,"<>john",A2:A13,"<>judy")

它返回正确的值 3。问题是他要排除的名字远不止三个。我们想在B列中列出它们，并让公式引用列表（否则公式会变得很大）。我们尝试过：

=SUM(COUNTIF(A2:A13,"<>B2:B4"))

正常输入和数组输入，但它产生 12 ！

奇怪的是，如果我们想包含而不是排除，那么：

=SUM(COUNTIF(A2:A13,B2:B4))

作品。我提供了一个 VBA 解决方案，但被拒绝了。