Esta fórmula pode ser usada para o seu exemplo. Ele pode ser usado para criar uma nova tabela em que o "quebra de linha é adicionado" antes de cada valor na última coluna.
=IFERROR(INDEX($A$1:$D$20,TRUNC((ROW()-ROW($F$1)+2)/2,0),COLUMN()-COLUMN($F$1)+1+MOD((ROW($F$1)-ROW()),2)*(COLUMNS($A$1:$D$20)-1)),"")
$A$1:$D$20
é a tabela de origem
$F$1
é a primeira célula da nova tabela
Para intervalos usados acima: Insira a fórmula na célula F1 e estenda até H40. Você pode ajustar a fórmula substituindo o intervalo e iniciando o endereço da célula no bloco de notas (CTL + H é seu amigo).
Como funciona?
- Veja a ajuda do Excel da função INDEX.
- O parâmetro de linha é:
TRUNC((ROW()-ROW($F$1)+2)/2,0)
, coloque-o em qualquer célula e estenda-se - ele retorna números aumentando em 1 a partir de 1. Esse valor é igual a cada duas linhas. -
COLUMN()-COLUMN($F$1)+1+MOD((ROW($F$1)-ROW()),2)*(COLUMNS($A$1:$D$20)-1))
é um pouco confuso, mas consiste em duas partes. Primeiro:COLUMN()-COLUMN($F$1)+1
retorna apenas o índice da linha na tabela de resultados. Segundo:MOD((ROW($F$1)-ROW()),2)*(COLUMNS($A$1:$D$20)-1))
é "ativo" somente em linhas pares (porqueMOD((ROW($F$1)-ROW()),2)
=1
, na linha ímpar éMOD((ROW($F$1)-ROW()),2)
=0
e multiplica a última parte(COLUMNS($A$1:$D$20)-1)
que representa a contagem de linhas na tabela de origem menos 1 Então, quando esta fórmula está na linha par (na primeira coluna), o parâmetro da coluna para a função INDEX é"column index in result table"+1*"column count in source table -1")
o que é1+1*(4-1)
e seu índice da última coluna. - A função IFERROR é usada para substituir códigos de erro (quando a função INDEX está fora do intervalo) por
""
representa a célula em branco.
PS: Quando você estica a tabela de resultados para a mesma contagem de colunas que a tabela de origem, obtém valores indesejados nesta última coluna (ela pode ser evitada usando mais uma função IF, mas a fórmula de resultado será muito mais complexa / confuso)
PPS: Desculpe por mau Inglês