RESUMO

Alguém pode explicar por que o Excel - versões atuais no Mac e no Windows - às vezes se recusa a aceitar, na fórmula de uma célula, isto:

CHOOSECOLS(SomeArray,ColNum)

mas aceita isso:

INDEX(SomeArray,0,ColNum)

Dois exemplos específicos são quando esses formulários são usados ​​em uma função SUMIF() ou COUNTIF(). E quando digo que o Excel se recusa a aceitar o primeiro, quero dizer que nem consigo aceitá-lo como uma fórmula sintaticamente correta. Observo que versões não condicionais dessas funções não mostram o problema, nem funções como MAX(), MIN(), AVERAGE(), etc.

DETALHES

Suponha que eu tenha um array em B5:C10 (suponha que seja dinâmico, então posso me referir a ele como B5# nos exemplos a seguir; mas o problema também existe mesmo com intervalos não dinâmicos), e quero somar todos os itens maior que 0 na coluna 2 (ou seja, em C5:C10). Embora o Excel aceite o seguinte:

=SUMIF(INDEX(B5#,0,2),">0")

recusa-se a aceitar:

=SUMIF(CHOOSECOLS(B5#,2),">0")

No entanto, se eu primeiro extrair a coluna desejada em uma matriz auxiliar enraizada em C16 (digamos), da seguinte maneira:

=CHOOSECOLS(B5#,2)

então o seguinte é aceito como fórmula correta (e fornece o resultado correto):

=SUMIF(C16#,">0")

Tenho uma planilha Excel que mostra isso em ação, mas não tenho certeza da melhor maneira de anexá-la. Se for útil e alguém puder explicar como posso anexar um arquivo .xlsx, eu o fornecerei. Enquanto isso, aqui está uma captura de tela:

No Excel (mas sem o uso de VB) como faço para juntar algumas colunas de uma matriz dinâmica a colunas de outra de modo que o resultado também seja uma matriz dinâmica?

Aqui está um exemplo muito simples. Suponha que eu tenha dois arrays dinâmicos de 2 colunas de igual comprimento. A primeira está em A1:B5, e a segunda está em D2:E6. Eu gostaria de criar um novo e terceiro array dinâmico em G3:H7, de modo que G3:G7 contenha o que estiver em A1:A5 e H3:H7 contenha o que estiver em E2:E6. Aqui está uma ilustração.

 | A | B | C | D | E | F | G | H |
—|———+———+———+———+———+———+———+———|
1| a | 1 |   |   |.  |   |   |   |
2| b | 3 |   | f | 0 |   |   |   |
3| c | 5 |   | g | 2 |   | a | 0 |
4| d | 7 |   | h | 4 |   | b | 2 |
5| e | 9 |   | i | 6 |   | c | 4 |
6|   |   |   | j | 8 |   | d | 6 |
7|   |   |   |   |   |   | e | 8 |
—————————————————————————————————-

Agora eu já sei que posso criar um terceiro array usando as seguintes fórmulas:

G3 = INDEX(A1#,0,1)
H3 = INDEX(D2#,0,2)

No entanto, isso me dá um resultado na forma de dois arrays dinâmicos de 1 coluna, enquanto eu gostaria que fosse um único array dinâmico de 2 colunas.

No momento, minha abordagem é tratar G3:G7 como um array auxiliar e, em seguida, crio minha versão dinâmica final desejada com algo assim:

J3 = OFFSET(G3,0,0,ROWS(A3#),2)

E eu provavelmente poderia até mesmo remendar alguma monstruosidade que eliminasse a necessidade do ajudante. Mas essa é uma forma de operação tão comum que eu esperava que houvesse um método mais elegante e "canônico", talvez usando as próprias novas funções dinâmicas de matriz.

Alguém pode sugerir um? (Mais uma vez, sem Visual Basic, por favor.)

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.

EM RESUMIDO

Alguém pode explicar por que estou vendo símbolos "@" aparecendo, onde não estavam antes, em muitas fórmulas em muitas das minhas planilhas do Excel?

Eles não são @menções, ou parte de referência estruturada ou uso de fórmulas de tabela, nem qualquer um dos outros suspeitos usuais.

TL;DR DETALHES

(NOTA: As informações do sistema estão no final deste post)

Como eu disse, não são @menções e não estou usando referências estruturadas. Além disso, eu não sou e nunca fui um usuário do Lotus, então não coloco símbolos @ em fórmulas da maneira que esses caras às vezes parecem estar programados para fazer. Na verdade, não consigo pensar em nenhuma vez em que usei @ em uma fórmula que não seja como parte do código de formato ocasional para TEST () e não é isso que está acontecendo aqui também.

Exemplo

Aqui está um exemplo (pegado aleatoriamente). Este:

=IFS(DX$33="","",ISNUMBER(CORRESP(DX$33,$AJ$33:$DR$33,0)),PROCH(DX$33,$AJ$33:$DR$1033,ROWS(DX$33:DX36 )+1,0), VERDADEIRO,0)

agora está assim:

=@IFS(DX$33="","",ISNUMBER(CORRESP(DX$33,$AJ$33:$DR$33,0)),PROCH(DX$33,$AJ$33:$DR$1033,@ROWS(DX$33 :DX36)+1,0),VERDADEIRO,0)

Como eu disse, isso é apenas um exemplo aleatório. Existem muitos mais, muitos dos quais não têm nada tão "exótico" quanto IFS (), então não acho que seja essa a causa.

Apenas um efeito de exibição?

Agora, acima, digo "parece" porque acho que é possível que os misteriosos @ não estejam realmente no próprio arquivo e estejam apenas sendo exibidos pelo Excel. Digo isso porque tenho pelo menos um arquivo onde posso ver em seu histórico de versões do DropBox que existe apenas uma única versão - minha (@-free) original. Portanto, começou sem símbolos @ e não mudou (de acordo com a versão do DropBox), mas agora posso ver os símbolos @. Uma explicação é que foi o Excel que mudou, não meu arquivo e, portanto, a alteração é apenas um artefato de exibição. Ah, e remover o @ parece não ter efeito nas fórmulas.

@ é uma coisa real

Por outro lado, para os sorrisos, tentei criar uma nova fórmula:

=@A1

Quando tentei inserir isso, o Excel me avisou que "Esta fórmula não é suportada por algumas versões mais antigas do Excel" e sugeriu que eu a substituísse pela seguinte variação:

=A1

Portanto, esse uso de @ parece ser uma coisa real e que nem sempre existiu. (O que obviamente se aplica ao seu uso em conjunto com [] na referência de linha, mas não é isso (é!?))

De qualquer forma, em resposta ao Excel me perguntando se eu queria deixá-lo remover o @, joguei o cuidado ao vento, cliquei em "Cuide da sua vida!" (também conhecido como "Não"). O Excel obrigado inserindo minha versão @-ified original e, pelo que posso ver, está tudo bem e se comporta exatamente como a versão @-free que o Excel tentou me convencer a usar.

Então?

Alguém pode explicar tudo isso? Talvez tenha havido uma atualização recente do Excel que introduziu uma nova parte da sintaxe @? E por recente, quero dizer nos últimos 2-3 meses no máximo. Tenho arquivos que criei em meados de dezembro que sei que eram @-free naquela época e que agora se tornaram @-ified. Mas, se sim, por que o Excel está apenas vagando por aí alterando arquivos sem me perguntar, especialmente devido ao fato de que, por sua própria admissão, isso pode criar problemas de compatibilidade com versões anteriores? E como é que eu tenho um arquivo do Excel que, por ter apenas uma versão, eu sei que está INALTERADO, mas em virtude da chegada desses símbolos @ parece ter sido ALTERADO?

Estou procurando pistas há mais de uma hora, mas não consigo encontrar nada definitivo. Uma dificuldade é que "@" é um caractere tão amplamente usado que muito do que o Google está lançando é bobagem irrelevante.

Informação do sistema

• Excel para Mac, versão 16.34 (20020900), parte do Office 365
• Mac OS Catalina 10.5.3

THX.