Início / computer / Perguntas / 1539270
Accepted
tkp
Asked: 2020-04-07 02:44:06 +0800 CST

Posso usar o operador de intervalo derramado do Excel para extrair uma única coluna 1D de uma matriz dinâmica 2D?

  • 772

Resumo

Com os novos arrays dinâmicos/derramados do Excel, qual é a melhor maneira de extrair uma única coluna derramada 1D de uma matriz derramada 2D?

Detalhes

Suponha que eu tenha uma matriz dinâmica 2D no Excel e ela esteja derramando, então não sei quantas linhas ela tem (e isso pode até mudar conforme os dados que estão alimentando a matriz mudam).

Agora, se eu quiser fazer referência a todo o array, posso fazer isso usando #, o operador de intervalo derramado. Por exemplo, suponha que minha matriz esteja em H3:L12 (embora lembre-se de que, embora tenha atualmente 10 linhas, não posso saber isso, nem posso saber que não mudará). Agora, se eu colocar na célula N8 (digamos), a fórmula =H3#, obterei uma cópia da minha matriz inicial em N8:R17.

Mas suponha que tudo que eu quero é uma cópia da segunda coluna da matriz original, I3:I12. Ou suponha que eu queira o array original, mas depois de aplicar um filtro. Por exemplo, talvez eu queira uma nova matriz que tenha apenas as linhas do original, onde a segunda célula em cada linha é maior que 5. Existe uma maneira sucinta de fazer esse tipo de coisa, talvez usando o operador de intervalo derramado?

É claro que existem maneiras menos sucintas. Por exemplo, embora eu tenha dito que ser um Dynamic Array significa que não sei o número de linhas, na verdade posso descobrir isso usando ROWS(H3#). Eu poderia então usar OFFSET () assim.

=OFFSET(H3,0,ROWS(H3#),1)

E então, para o segundo caso, onde eu quero a matriz completa, mas filtrada de forma que a segunda coluna seja maior que 5, eu poderia ter:

=FILTER(H3#,OFFSET(H3,0,ROWS(H3#),1)>5)

Em nenhuma das fórmulas estou mencionando explicitamente um número de linhas "codificadas", de modo que a natureza "Dinâmica" seja preservada, o que é bom. Mas parece desajeitado ter que sair do meu caminho para extrair esse número de linhas e, em seguida, recorrer a OFFSET (), especialmente considerando que essa é uma função volátil . Não existe um método mais simples e integrado para isso?

THX.

microsoft-excel

2 respostas

  1. Best Answer

    Para retornar a segunda coluna, use a função Index:

    =INDEX(H3#,0,2)

    E você pode usar a FILTERfunção para retornar uma matriz filtrada.

    por exemplo:

    =FILTER(H3#,INDEX(H3#,0,2)>=5)
    • 2

  2. Considere a seguinte função definida pelo usuário:

    Public Function Block2Column(rng As Range) As Variant
    Dim arr, OneCol(), cnt As Long, a

    arr = rng.Value
    cnt = rng.Count
    ReDim OneCol(1 To cnt, 1 To 1)
    i = 1

    For Each a In arr
        OneCol(i, 1) = a
        i = i + 1
    Next a
    Block2Column = OneCol
End Function

    Pode levar várias colunas ou um bloco e criar um único derramamento dinâmico:

    insira a descrição da imagem aqui

    • 1

relate perguntas