Função Excel TRANSPOSE

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)

Você pode baixar este modelo do Excel da função TRANSPOSE aqui - modelo do Excel da função TRANSPOSE

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 é

{= TRANSPOR (A3: B7)}

As chaves {} mostradas acima indicam que a função foi inserida como uma Fórmula de Matriz.

Agora, se você tiver que alterar qualquer valor nos dados originais, diga B8 = SUM (B3: B7), a saída transposta, onde você usou o TRANSPOSE, será atualizada automaticamente, mas não no caso em que você usou 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á a saída 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 uma matriz 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á:

  1. - ($ C $ 4: $ E $ 6 = D6) retorna {0,0,0; 0,0,0; 0,1,0}
  2. TRANSPOSE (COLUNA ($ C $ 4: $ E $ 6) ^ 0)) retorna {1,1,1}
  3. MMULT ({0,0,0; 0,0,0; 0,1,0}, {1,1,1}) retorna {0; 0; 1}
  4. MATCH (1, {0; 0; 1}, 0) retorna 3
  5. INDEX ($ B $ 4: $ B $ 6, 3) retorna A3.

Coisas para lembrar

  1. A opção de copiar e colar cria duplicatas.
  2. Esta função vincula os dados à fonte.
  3. 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
  4. Depois de inserida, nenhuma célula individual que faça parte desta função pode ser alterada.
  5. Pressione CTRL + SHIFT + ENTER para inserir a Fórmula no excel.