Pretendo transformar esses nomes da lista, em uma matriz de intervalo como essa mostrada na imagem, ao depurar a fórmula quero que ela me mostre exatamente um array de intervalos de linhas e colunas, existe alguma forma de transformar esses nomes em uma gama como esta??
Responder1
Se você quiser a string de texto, use:
="={"""&TEXTJOIN(""",""",,SUBSTITUTE($A$2:$A$5," ","""/""")) & """}"
Se você quiser um array real que será usado em outro lugar, use:
IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TRANSPOSE($A$2:$A$5)," ","</b><b>")&"</b></a>","//b["&ROW($A$1:$A$4)&"]"),"")
Isso criará uma matriz 4x4 que pode ser usada em outra fórmula. Dependendo da versão, usar isso em outra fórmula exigirá o uso de Ctrl-Shift-Enter em vez de Enter ao sair do modo de edição para forçar a fórmula de matriz.
Não conheço nenhuma maneira de fazer o que você deseja F9e mostrar a matriz na barra de fórmulas.
Responder2
Bem, a string literal, sem o erro de digitação "JONH" que não vou replicar, pode ser obtida com a fórmula de Scott Craner ajustada levemente (e usei SEQUENCE()em vez do ROWS()truque, preciso usar as novas funções para me acostumar com eles, hein?), então usado como base para alguns trabalhos com cordas:
="={"""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(B234:B237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(C234:C237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(D234:D237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(E234:E237),SEQUENCE(4)))&"""}"
Tenha em mente que embora forneça a saída literal desejada, a saída éNÃOuma constante de matriz da perspectiva do Excel e NÃO será usada exatamente como F9seria o resultado.
É apenas uma sequência de texto para todos os cuidados do Excel. Portanto, usá-lo dentro de uma fórmula provavelmente resultará em fracasso ou sucesso por puro acaso. Talvez funcionasse bem também, para alguns usos. Mas não é de forma alguma igual ou equivalente ao Excel como a constante da matriz de uso interno destacando algo e clicando em F9cria.
Dito isto, é feio e inchado. Eu não sigo o tópico nos comentários, eu acho, então não vejo por que Scott Craner usou, TRANSPOSE()o que me forçou a fazê-lo também, já que parece que a saída desejada é da esquerda para a direita (comente acima com "A234 = " Jonh",...) então tirando-o e ajustando seu A2:A5 para A1:A4 (ou eliminando uma linha para meus experimentos), obtenho:
=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(($A$234:$A$237)," ","</b><b>")&"</b></a>","//b["&SEQUENCE(1,4)&"]"),"")
para sua fórmula e retrabalhar a minha, depois combiná-la para usar uma única fórmula, obtenho:
="={"""&TEXTJOIN("""\""",,B234:E234)&""","""&TEXTJOIN("""\""",,B235:E235)&""", """&TEXTJOIN("""\""",,B236:E236)&""","""&TEXTJOIN("""\""",,B237:E237)&"""}"
que, aliás, por ser padronizado e repetitivo na construção, pode-se usar os truques antigos CONCATENATE()ou union operatorantigos para construí-lo imensamente com linhas e colunas ajustando-se bem antes de finalmente concatenar, colar especial como o valor e, em seguida, um "=" colocado antes dele para torná-lo uma fórmula. Ainda mais fácil agora. Para dados que não estão em quatro linhas pequenas e com menos de cinco elementos.
Então, isso fornece a string resultante desejada expressa, embora, novamente, não seja um resultado que o Excel veria como sendo o mesmo que a constante da matriz que você vê após usar F9como mencionado.
E a fórmula alterada de Scott Craner daria então o resultado da célula individual descrito no comentário. Que poderia ser usado dentro de uma fórmula para permitir que o Excel criasse aquela constante de matriz interna. Mas NÃO, eu acho, usando a fórmula de string (que é um beco sem saída, em termos de cálculo), mas simusando a fórmula (ajustada) de Scott Craner dentro de qualquer fórmula que você precise na so Excel will evaluate it to the desiredconstante da matrize então, em vez de preencher essas 16 células, passe-as pela cadeia de cálculo dentro dessa fórmula.
Acabei de perceber que é "Scott Tenorman" (ou próximo) em South Park, aquele de quem Cartman... se vinga... Não estou mais vagamente incomodado em dizer "Scott Craner" repetidas vezes!