Gary's Student's questions

Estou tentando ajudar um colega com um aprimoramento de uma planilha. Estamos usando o Excel 365.

Na célula C3 temos a fórmula:

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

Ele produz uma coluna dinâmica de resultados que aumentará ou diminuirá dependendo do valor em E1 . Em C1 temos uma fórmula que soma as células abaixo:

=SUM(C3#)

Tudo isso funciona.

Meu colega quer colocar a fórmula no fundo do derramamento dinâmico. Ele também quer que a fórmula se mova automaticamente para cima ou para baixo à medida que o derramamento aumenta ou diminui. Então, na ilustração, a fórmula iria para a célula C20 .

Eu não tenho idéia de como fazer isso ou mesmo se é possível.

Minha sugestão foi não incluir a fórmula. Primeiro defina o parâmetro de filtro e execute esta macro curta:

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

A macro colocará a fórmula na célula correta. Colega respondeu "não é automático o suficiente"

Quaisquer sugestões serão bem-vindas.

Estou tentando ajudar alguém a automatizar uma edição de texto muito simples no Microsoft Word usando o VBA.

Uma alteração desejada para remover vírgulas incorporadas em números, deixando outras vírgulas sozinhas. Por exemplo:

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

se tornaria:

I like apples, pears, and plums.  See 1234567

O código procura por um dígito seguido por uma vírgula seguida por um dígito. Para remover uma vírgula entre dois dígitos, o código atual percorre todos os 100 casos possíveis de 0,0 a 9,9 e faz um Find/Replaceem cada caso. Então 0,0 se torna 00 . Segue o código atual:

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

Estou certo de que todo esse looping é completamente desnecessário ie j isso pode ser feito com um padrão curinga simples Find, mas não sei o suficiente sobre o modelo Word Object para implementar isso.

ATUALIZAR:

A solução RexEx funcionou muito bem. Eu também trabalhei com uma solução curinga:

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

Usando o Excel 365

Eu tenho uma pequena função definida pelo usuário VBA para analisar uma string em substrings com base na vírgula como separador. Estou tentando substituir o UDF por uma fórmula de planilha para criar uma pasta de trabalho livre de macros.

A 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

No Excel 365, isso se espalha de maneira análoga a TextToColumns:

Baseado neste post anterior:

extrair substrings da string usando filterxml

Eu tenho usado:

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

Isso funciona da mesma maneira, a menos que a string de entrada contenha um caractere & (como a string em A2 ) . Neste caso FILTERXML()retorna #VALOR!

Existe uma solução fácil FILTERXML()para aceitar o & como qualquer outro personagem ??

Usando Excel 365.

Eu tenho alguns dados de texto nas colunas A e B como:

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

que eu quero resumir em formato 2-D

em D1 eu entro na fórmula:

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

que funciona e cai bem. Em E1 eu coloco:

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

que também funciona e se espalha bem. Em seguida, copio manualmente E1 para baixo para obter:

Gostaria que a propagação da fórmula E1 fosse dinâmica.

O que eu tentei:

=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)))))

Nenhuma dessas fórmulas se espalha (ou sequer funciona) . No entanto, por si só, a seguinte parte funciona:

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

É possível arrastar E1 para baixo dinamicamente ??

Meu objetivo é criar uma tabela com o valor de uma única célula em várias planilhas como uma matriz de derramamento.

Minha prática atual é inserir:

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

e copie manualmente para baixo. Eu tentei primeiro:

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

onde Z100 contém 3 . Isso se espalha, mas retorna #VALUE! Então dei um passo para trás e tentei:

=INDIRECT("Sheet" & SEQUENCE(3) & "!A1")que também falhou. Dei vários passos para trás e tentei algo realmente simples:

=INDIRECT("A" & 1)que funciona
=INDIRECT("A" & ROWS($1:1)), que também funciona, mas
=INDIRECT("A" & SEQUENCE(1))falha com o mesmo erro (? Não faço ideia do porquê?) . Então:

• Posso fazer SEQUENCE()trabalhos dentro INDIRECT()?
• Se não, existe outra maneira de fazer uma tabela dinâmica do valor em várias planilhas?

Eu tenho uma função curta definida pelo usuário que, dada uma string e um conjunto de possíveis substrings em uma coluna, retornará a posição da primeira substring encontrada.

Pediram-me para substituir o UDF por uma fórmula não VBA.

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

Aqui está um exemplo:

As substrings estão na coluna F . A função encontra b e q e retorna 5 porque q vem primeiro.

Eu tenho uma fórmula que funciona para uma instância específica:

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

Tenho tentado encontrar uma maneira de substituir o {"a","b","c","d","q"}e "abcdq"por uma referência a um intervalo de células, mas estou perplexo.

Em um ambiente do Excel 365, tento inserir uma fórmula dinâmica de derramamento:

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

Estranhamente eu recebo:

Há um @ gratuito e nenhum derramamento. ( Se eu inserir a fórmula manualmente, obtenho o resultado esperado. )

Igualmente estranho, se eu inserir a fórmula como um String, obtenho o resultado esperado!

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

Por que .Formulafalha?? Alguém pode explicar o que está acontecendo??

Usando as matrizes dinâmicas de derramamento introduzidas recentemente no Excel 365, escrevi uma fórmula super simples para analisar os caracteres em uma string em células únicas. Por exemplo, se eu tiver:

qwertyuiop$

em A1 , escolho uma célula e digito:

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

e a fórmula cai:

Isso evita inseri =MID($A$1,ROWS($1:1),1)-lo e copiá-lo manualmente para baixo.

O problema que tenho é com o número 11 . Eu uso 11 porque sei que o comprimento de A1 é 11. Gostaria de alterar a MID()fórmula para ajustá-la ao comprimento de A1 , em vez de digitar novamente a fórmula.

Eu tentei:

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

mas isso falha. Minha solução atual é:

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

o que parece ser uma maneira realmente idiota de transformar 11 em uma variável.

Ofereci uma solução VBA, mas foi rejeitada.

Tentar ajudar um amigo com uma COUNITF/COUNTIFSfórmula bem simples. Os dados são como:

O objetivo é contar as células de dados excluindo mike , john , judy . Fórmula atual:

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

que retorna o valor correto 3. O problema é que ele tem muito mais que três nomes para excluir. Gostaríamos de listá-los na coluna B e fazer com que a fórmula se refira à lista ( caso contrário, a fórmula se tornará enorme ). Nós tentamos:

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

entrada normal e entrada de matriz, mas produz 12 !

Estranhamente, se quisermos incluir em vez de excluir , então:

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

funciona. Ofereci uma solução VBA, mas foi rejeitada.