Función TRANSPOSE de Excel

¿Qué hace la función de transposición en Excel?

La función de transposición en Excel se usa para rotar los valores o cambiar los valores de filas a columnas y de columnas a filas, ya que esta función toma una matriz de celdas como argumento, por lo que para ejecutarla debemos presionar CSE o Control + Shift + Enter, primero seleccione el tamaño exacto del rango que necesita transponer y luego use la fórmula de transposición.

Sintaxis

A continuación se muestra la fórmula TRANSPOSE en Excel.

Argumentos

matriz: Requerido. Un rango de celdas que desea transponer.

Es una función de matriz .

¿Cómo utilizar la función TRANSPOSE en Excel? (con ejemplos)

Puede descargar esta plantilla de Excel de la función TRANSPOSE aquí - Plantilla de Excel de la función TRANSPOSE

Ejemplo 1

Los datos originales que desea transponer están en A3: B8.

  • Paso 1: Seleccione el rango donde desea su valor transpuesto, aquí, D6: I7.

  • Paso 2: Ahora, escriba la fórmula TRANSPONER en la región seleccionada y presione CTRL + MAYÚS + ENTRAR (o COMANDO + MAYÚS + ENTRAR en Mac).

= TRANSPONER (A3: B7)

Obtendrá la salida transpuesta en D6: I7 como se muestra a continuación.

En esto, D3: I4 es la salida transpuesta de copiar y pegar y D6: I7 es la salida de transposición.

Observe que la función TRANSPOSE de Excel es

{= TRANSPONER (A3: B7)}

Las llaves {} que se muestran arriba indican que la función se ha ingresado como una fórmula de matriz.

Ahora, si tiene que cambiar cualquier valor en los datos originales, digamos B8 = SUM (B3: B7), la salida transpuesta, donde ha utilizado TRANSPOSE, se actualizará automáticamente, pero no en el caso en el que haya utilizado copy- opción de pegar.

También se puede utilizar junto con otras funciones para obtener el resultado deseado. Veamos algunos ejemplos de esto.

Ejemplo # 2

Suponga que tiene una lista de estudiantes y sus calificaciones como se muestra a continuación. Algunos de los estudiantes no dieron exámenes. Por lo tanto, la columna de calificaciones se deja en blanco para esos estudiantes.

Desea transponer estos datos y, cuando la celda está vacía, desea poner "Ausente" en esa celda.

Puede hacerlo utilizando Transpose junto con la función SI en Excel. Puede usar la siguiente fórmula TRANSPOSE en Excel para hacerlo:

= TRANSPONER (SI (B3: D10 = ””, ”AUSENTE”, B3: D10))

'SI (B3: D10 = ”“, “AUSENTE”' significa que si alguna celda en el rango B3: D10 está vacía, colocará “Ausente” en su lugar y transpondrá el rango dado.

Suponga que en esta lista, si algún estudiante ha obtenido menos de 70 puntos o no ha aparecido en el examen se considera APROBADO, puede cambiar la fórmula TRANSPOSE como:

= TRANSPONER (SI (B3: D10 <70, "FALLO", B3: D10)

y obtendrá el resultado final como se muestra a continuación.

Ejemplo # 3

A veces es necesario agregar algunos caracteres a los existentes junto con la transposición. Suponga que tiene una lista de ID en B4: B7 como se muestra a continuación.

Desea transponer los identificadores y agregarle el prefijo "ID". Puede hacerlo usando la Fórmula en Excel:

= TRANSPONER ("ID" y B4: B7)

Esto transpondrá los datos y agregará el prefijo a cada uno de los contenidos de la celda.

También puede agregar cualquier valor como sufijo usando la fórmula TRANSPONER anterior. Esto puede ser útil cuando se usa la función Transponer para concatenar las palabras dadas en diferentes filas en una sola celda. Para hacerlo, puede seguir los siguientes pasos:

  • Escriba la sintaxis: CONCATENAR (TRANSPONER (B4: B7 & ”,”). No presione Intro.
  • Seleccione la función de transposición de Excel como se muestra a continuación.

  • Presione la tecla F9.

  • Eliminar {} de la fórmula

  • Presione Entrar.

Concatenará las celdas en una sola celda.

Ejemplo # 4

Suponga que tiene una biblioteca de datos en la que los diversos temas están dispuestos en un estante y hay diferentes estantes como se muestra a continuación.

Es posible que desee recuperar la ubicación de cualquiera de los sujetos (proporcionada en H3: H11) utilizando estos datos (B4: E6).

Puedes hacerlo usando la sintaxis

= ÍNDICE ($ B $ 4: $ B $ 6, COINCIDIR (1, MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPONER (COLUMNA ($ C $ 4: $ E $ 6) ^ 0)), 0))

Veamos cómo funciona esta fórmula TRANSPOSE en Excel.

  • - ($ C $ 4: $ E $ 6 =)

Cuando se da en H3, será - ($ C $ 4: $ E $ 6 = H3).

Esto forma una matriz de 1 y 0 para indicar la presencia o ausencia del valor. Para Biología en H3, formará la matriz como: {1,0,0; 0,0,0; 0,0,0}

  • TRANSPONER (COLUMNA ($ C $ 4: $ E $ 6) ^ 0))

Crea una matriz de 3 filas en una columna y una potencia de 0 asegura que los números se conviertan a 1. El resultado de esta función es {1,1,1}

  • MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPONER (COLUMN ($ C $ 4: $ E $ 6) ^ 0))

El MMULT en Excel multiplica la salida de A y B.

MMULT ({1,0,0; 0,0,0; 0,0,0}, {1,1,1}) da la salida {1; 0; 0}

  • COINCIDIR (1, MMULT (), 0)

Coincidirá con la salida de C con 1. MATCH (1, {1; 0; 0}, 0) devuelve la posición 1.

  • ÍNDICE ($ B $ 4: $ B $ 6, COINCIDIR (, 0))

Identificará el valor de la celda para la cual la función de coincidencia especificó la posición. ÍNDICE ($ B $ 4: $ B $ 6, 1) devolverá A1.

De manera similar, para "Geografía" como asignatura, será:

  1. - ($ C $ 4: $ E $ 6 = D6) devuelve {0,0,0; 0,0,0; 0,1,0}
  2. TRANSPONER (COLUMN ($ C $ 4: $ E $ 6) ^ 0)) devuelve {1,1,1}
  3. MMULT ({0,0,0; 0,0,0; 0,1,0}, {1,1,1}) devuelve {0; 0; 1}
  4. COINCIDIR (1, {0; 0; 1}, 0) devuelve 3
  5. ÍNDICE ($ B $ 4: $ B $ 6, 3) devuelve A3.

Cosas para recordar

  1. La opción de copiar y pegar crea duplicados.
  2. Esta función vincula los datos a la fuente.
  3. Si el número de filas y columnas seleccionadas no es igual a las columnas y filas de los datos de origen, dará un error #VALUE
  4. Una vez que se ha ingresado, no se puede cambiar ninguna celda individual que sea parte de esta función.
  5. Presione CTRL + MAYÚS + ENTRAR para ingresar la Fórmula en Excel.