O que a função Transpor faz no Excel?
A função transpor no Excel é usada para girar os valores ou mudar os valores de linhas para colunas e colunas para linhas, já que esta função leva uma matriz de células como um argumento, então para executá-la precisamos pressionar CSE ou Control + Shift + Enter, primeiro selecione o tamanho exato do intervalo que precisa ser transposto e, em seguida, use a fórmula de transposição.
Sintaxe
Abaixo está a Fórmula TRANSPOSE em excel.
Argumentos
array: Obrigatório. Um intervalo de células que você deseja transpor.
É uma função de array .
Como usar a função TRANSPOSE no Excel? (com exemplos)
Exemplo 1
Os dados originais que você deseja transpor estão em A3: B8.
- Passo 1: Selecione o intervalo onde deseja o valor transposto, aqui, D6: I7.
- Etapa 2: Agora, digite a Fórmula TRANSPOSE na região selecionada e pressione CTRL + SHIFT + ENTER (ou COMMAND + SHIFT + ENTER no Mac).
= TRANSPOR (A3: B7)
Você obterá a saída transposta em D6: I7, conforme mostrado abaixo.
Nesse caso, D3: I4 é a saída transposta de copiar e colar e D6: I7 é a saída de transposição.
Observe que a função TRANSPOSE do Excel é
(= TRANSPORAR (A3: B7))
As chaves () mostradas acima indicam que a função foi inserida como uma Fórmula de Matriz.
Se você tiver que alterar qualquer valor nos dados originais, digamos B8 = SUM (B3: B7), a saída transposta, onde você usou o TRANSPOSE, será automaticamente atualizada, mas não no caso em que você usou a cópia- opção de colar.
Também pode ser usado junto com outras funções para obter a saída desejada. Vamos ver alguns exemplos disso.
Exemplo # 2
Suponha que você tenha uma lista de alunos e suas notas, conforme mostrado abaixo. Alguns dos alunos não deram exames. Portanto, a coluna de notas é deixada em branco para esses alunos.
Você deseja transpor esses dados e, quando a célula está vazia, deseja colocar “Ausente” nessa célula.
Você pode fazer isso usando Transpose junto com a função IF no Excel. Você pode usar a seguinte Fórmula TRANSPOSE no Excel para fazer isso:
= TRANSPOR (SE (B3: D10 = ””, ”AUSENTE”, B3: D10))
'IF (B3: D10 = ”“, “AUSENTE”' significa que se qualquer célula no intervalo B3: D10 estiver vazia, ela colocará “Ausente” em seu lugar. Ele irá transpor o intervalo dado.
Suponha que nesta lista, se algum aluno tiver pontuado menos de 70 marcas ou não tiver aparecido no exame é considerado FALHA, você pode alterar a Fórmula TRANSPOSE como:
= TRANSPOR (SE (B3: D10 <70, ”FALHA”, B3: D10)
e você obterá o resultado final, conforme mostrado abaixo.
Exemplo # 3
Às vezes, é necessário adicionar alguns caracteres aos existentes junto com a transposição. Suponha que você tenha uma lista de IDs em B4: B7, conforme mostrado abaixo.
Você deseja transpor os ids e adicionar o prefixo “ID” a ele. Você pode fazer isso usando a Fórmula no Excel:
= TRANSPOR (“ID” e B4: B7)
Isso irá transpor os dados e adicionar o prefixo a cada conteúdo da célula.
Você também pode adicionar qualquer valor como sufixo usando a Fórmula TRANSPOSE acima. Isso pode ser útil ao usar a função Transpor para concatenar as palavras fornecidas em linhas diferentes em uma única célula. Para fazer isso, você pode seguir as seguintes etapas:
- Digite a sintaxe: CONCATENATE (TRANSPOSE (B4: B7 & ”,”). Não pressione Enter.
- Selecione a função de transposição do Excel, conforme mostrado abaixo.
- Pressione a tecla F9.
- Remova () da fórmula.
- Pressione Enter.
Ele vai concatenar as células em uma única célula.
Exemplo # 4
Suponha que você tenha uma biblioteca de dados em que os vários assuntos estão organizados em uma prateleira, e existem diferentes prateleiras, conforme mostrado abaixo.
Você pode querer recuperar a localização de qualquer um dos assuntos (fornecido em H3: H11) usando esses dados (B4: E6).
Você pode fazer isso usando a sintaxe
= ÍNDICE ($ B $ 4: $ B $ 6, CORRESPONDÊNCIA (1, MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPOSE (COLUNA ($ C $ 4: $ E $ 6) 0)), 0))
Vamos ver como funciona esta Fórmula TRANSPOSE no Excel.
- - ($ C $ 4: $ E $ 6 =)
Quando é dado em H3, será - ($ C $ 4: $ E $ 6 = H3).
Isso forma uma matriz de 1 e 0 para indicar a presença ou ausência do valor. Para Biologia em H3, formará a matriz como: (1,0,0; 0,0,0; 0,0,0)
- TRANSPOSE (COLUNA ($ C $ 4: $ E $ 6) 0))
Ele cria um array de 3 linhas em uma coluna e uma potência de 0 garante que os números sejam convertidos em 1. A saída desta função é (1,1,1)
- MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPOSE (COLUNA ($ C $ 4: $ E $ 6) 0))
O MMULT no excel multiplica a saída de A e B.
MMULT ((1,0,0; 0,0,0; 0,0,0), (1,1,1)) dá a saída (1; 0; 0)
- CORRESPONDÊNCIA (1, MMULT (), 0)
Ele combinará a saída de C com 1. MATCH (1, (1; 0; 0), 0) retorna a posição 1.
- ÍNDICE ($ B $ 4: $ B $ 6, CORRESPONDÊNCIA (, 0))
Ele identificará o valor da célula para a qual a função de correspondência especificou a posição. INDEX ($ B $ 4: $ B $ 6, 1) retornará A1.
Da mesma forma, para “Geografia” como assunto, será:
- - ($ C $ 4: $ E $ 6 = D6) retorna (0,0,0; 0,0,0; 0,1,0)
- TRANSPOSE (COLUNA ($ C $ 4: $ E $ 6) 0)) retorna (1,1,1)
- MMULT ((0,0,0; 0,0,0; 0,1,0), (1,1,1)) retorna (0; 0; 1)
- MATCH (1, (0; 0; 1), 0) retorna 3
- INDEX ($ B $ 4: $ B $ 6, 3) retorna A3.
Coisas para lembrar
- A opção de copiar e colar cria duplicatas.
- Esta função vincula os dados à fonte.
- Se o número de linhas e colunas selecionadas não for igual às colunas e linhas dos dados de origem, ocorrerá um erro #VALUE.
- Depois de inserida, nenhuma célula individual, que faz parte dessa função, não pode ser alterada.
- Pressione CTRL + SHIFT + ENTER para inserir a Fórmula no excel.
