Conteúdo
Ontem na maratona 30 funções do Excel em 30 dias contamos o número de colunas no intervalo usando a função COLUNAS (NUMBERCOLUMN), e agora é hora de algo mais procurado.
No 13º dia da maratona, vamos nos dedicar ao estudo da função TRANSPOR (TRANS). Com esta função, você pode girar seus dados transformando áreas verticais em horizontais e vice-versa. Você tem essa necessidade? Você pode fazer isso usando uma inserção especial? Outras funções podem fazer isso?
Então, vamos nos voltar para informações e exemplos sobre a função TRANSPOR (TRANS). Se você tiver informações ou exemplos adicionais, compartilhe-os nos comentários.
Função 13: TRANSPOR
função TRANSPOR (TRANSPOSE) retorna um intervalo horizontal de células como um intervalo vertical ou vice-versa.
Como a função TRANSPOSE pode ser usada?
função TRANSPOR (TRANSP) pode alterar a orientação dos dados, bem como trabalhar em conjunto com outras funções:
- Altere o layout horizontal dos dados para vertical.
- Mostrar os melhores salários totais nos últimos anos.
Para alterar a orientação dos dados sem criar links para os dados originais:
- Use colar especial (Pasta Especial) > Transpor (Transpor).
Sintaxe TRANSPOSIÇÃO (TRANSP)
função TRANSPOR (TRANSPOSE) tem a seguinte sintaxe:
TRANSPOSE(array)
ТРАНСП(массив)
- ordem (matriz) é a matriz ou intervalo de células a serem transpostas.
Armadilhas TRANSPOSE (TRANSPOSE)
- função TRANSPOR (TRANSPOSE) deve ser inserido como uma fórmula de matriz, pressionando Ctrl + Shift + Enter.
- O intervalo que resultará da transformação pela função TRANSPOR (TRANSPOSE) deve ter o mesmo número de linhas e colunas que o intervalo original tem colunas e linhas, respectivamente.
Exemplo 1: transformando dados horizontais em dados verticais
Se os dados estiverem na horizontal em uma planilha do Excel, você pode aplicar a função TRANSPOR (TRANSPOSE) para convertê-los em uma posição vertical, mas em um local diferente na folha. Por exemplo, na tabela final de benchmarks, um arranjo vertical seria mais conveniente. Usando a função TRANSPOR (TRANSPOSE), você pode fazer referência aos dados horizontais originais sem alterar sua localização.
Para transpor o intervalo horizontal 2 × 4 na faixa vertical 4 × 2:
- Selecione 8 células onde você deseja colocar o intervalo vertical resultante. Em nosso exemplo, serão as células B4:C7.
- Digite a seguinte fórmula e transforme-a em uma fórmula de matriz clicando Ctrl + Shift + Enter.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Chaves serão adicionadas automaticamente no início e no final da fórmula para indicar que uma fórmula de matriz foi inserida.
Em vez de TRANSPOR (TRANSPOSE), você pode usar outra função para transformar os dados, por exemplo, ÍNDICE (ÍNDICE). Ele não exige que você insira uma fórmula de matriz e você não precisa selecionar todas as células na área de destino ao criar a fórmula.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
Exemplo 2: Alterar orientação sem links
Se você deseja apenas alterar a orientação de seus dados sem manter uma referência aos dados originais, pode usar Colar Especial:
- Selecione os dados de origem e copie-os.
- Selecione a célula superior esquerda da área onde deseja colocar o resultado.
- Na guia Avançado Início (Home) clique no menu suspenso de comando colar (Inserir).
- selecionar Transpor (Transpor).
- Exclua os dados originais (opcional).
Exemplo 3: Melhor Salário Total em Anos Passados
função TRANSPOR (TRANSP) pode ser usado em combinação com outros recursos, como nesta fórmula impressionante. Foi postado por Harlan Grove no Excel News Bloc em uma discussão sobre o cálculo do melhor salário total nos últimos 5 anos (seguidos!).
=MAX(MMULT(A8:J8, --(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))-(Number-1)/2)
=МАКС(МУМНОЖ(A8:J8; --(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)
Como você pode ver a figura do formulário no formato do curso – este formato é máximo. Lente A5 de cor Sessão e neste exemplo primeiro há 4 dias, o que é bom para a colisão.
Формула проверяет диапазоны, чтобы увидеть достаточно ли них последовательных столбцов. Результаты проверки (1 de 0) умножаются на значения ячеек, чтобы получить суммарныйтойъёмазыпмарныйтойъёмазыплнойъёмазыплнойъёмазыплнойъёмазыплнойъёмазыплнойъёматы.
Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формула массива получает в одной!
