¿Cómo transponer celdas de una columna según los valores únicos de otra columna?
Supongamos que tiene un rango de datos con dos columnas y desea transponer los valores de una columna en filas horizontales, agrupados según los valores únicos de la otra columna, para obtener el resultado que se muestra a continuación. ¿Se le ocurre alguna forma eficaz de lograrlo en Excel?
Transponer celdas de una columna en función de valores únicos con fórmulas
Transponer celdas de una columna en función de valores únicos con código VBA
Transponer celdas de una columna en función de valores únicos con Kutools para Excel
Transponer celdas de una columna en función de valores únicos con fórmulas
Con las siguientes fórmulas matriciales, podrá extraer los valores únicos y transponer sus datos correspondientes en filas horizontales. Siga estos pasos:
1. Introduzca esta fórmula matricial: =ÍNDICE($A$2:$A$16;COINCIDIR(0;CONTAR.SI($D$1:$D1;$A$2:$A$16);0)) en una celda vacía, por ejemplo D2, y pulse Mayús + Ctrl + Intro simultáneamente para obtener el resultado correcto. Vea la captura de pantalla:
Nota: En la fórmula anterior, A2:A16 es el rango de la columna del que desea obtener los valores únicos, y D1 es la celda situada justo encima de aquella en la que se introduce la fórmula.
2. A continuación, arrastre el controlador de relleno hacia abajo hasta las celdas necesarias para extraer todos los valores únicos. Vea la captura de pantalla:
3. A continuación, introduzca esta fórmula en la celda E2:=IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0) y recuerde pulsar Mayús + Ctrl + Intro para obtener el resultado. Vea la captura de pantalla:
Nota: En la fórmula anterior: B2:B16 es la columna de datos que desea transponer, A2:A16 es la columna en función de la cual desea transponer los valores, y D2 contiene el valor único que ha extraído en el paso 1.
4.A continuación, arrastre el controlador de relleno hacia la derecha hasta las celdas en las que desee mostrar los datos transpuestos, hasta que aparezca 0. Consulte la captura de pantalla:
5. A continuación, arrastre el controlador de relleno hacia abajo por el rango de celdas para obtener los datos transpuestos, tal como se muestra en la siguiente captura de pantalla:
Transponer celdas de una columna en función de valores únicos con código VBA
Es posible que las fórmulas le resulten difíciles de entender. En ese caso, puede ejecutar el siguiente código VBA para obtener el resultado deseado.
1. Mantenga pulsadas las teclas ALT + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.
2. Haga clic en Insertar > Módulo y pegue el siguiente código en la ventana del Módulo.
Código VBA: Transponer celdas de una columna en función de valores únicos de otra columna:
Sub transposeunique()
'updateby Extendoffice
Dim xLRow As Long
Dim i As Long
Dim xCrit As String
Dim xCol As New Collection
Dim xRg As Range
Dim xOutRg As Range
Dim xTxt As String
Dim xCount As Long
Dim xVRg As Range
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If (xRg.Columns.Count <> 2) Or _
(xRg.Areas.Count > 1) Then
MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
Exit Sub
End If
Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
If xOutRg Is Nothing Then Exit Sub
Set xOutRg = xOutRg.Range(1)
xLRow = xRg.Rows.Count
For i = 2 To xLRow
xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
Next
Application.ScreenUpdating = False
For i = 1 To xCol.Count
xCrit = xCol.Item(i)
xOutRg.Offset(i, 0) = xCrit
xRg.AutoFilter Field:=1, Criteria1:=xCrit
Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
If xVRg.Count > xCount Then xCount = xVRg.Count
xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Next
xOutRg = xRg.Cells(1, 1)
xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
xRg.Rows(1).Copy
xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
xRg.AutoFilter
Application.ScreenUpdating = True
End Sub3. A continuación, pulse la tecla F5 para ejecutar este código. Aparecerá un cuadro de diálogo que le pedirá que seleccione el rango de datos que desea utilizar. Vea la captura de pantalla:
4. A continuación, haga clic en el botón Aceptar. Aparecerá otro cuadro de diálogo que le pedirá que seleccione una celda donde colocar el resultado. Vea la captura de pantalla:
6. Haga clic en el botón Aceptar y los datos de la columna B se transpondrán según los valores únicos de la columna A. Vea la captura de pantalla:
Transponer celdas de una columna en función de valores únicos con Kutools para Excel
Si dispone de Kutools para Excel, al combinar las utilidades Combinar filas avanzado y Dividir celdas, podrá completar esta tarea rápidamente sin necesidad de fórmulas ni código.
Tras instalar Kutools para Excel, siga estos pasos:
1. Seleccione el rango de datos que desea utilizar. (Si quiere conservar los datos originales, cópielos y péguelos primero en otra ubicación.)
2. A continuación, haga clic en Kutools > Combinar y dividir > Combinar filas avanzado. Vea la captura de pantalla:
3. En el cuadro de diálogo Combinar filas basado en columna, realice las siguientes operaciones:
(1.) Haga clic en el nombre de la columna en función de la cual desea transponer los datos y seleccione Clave principal;
(2.) Haga clic en otra columna cuyos datos desee transponer, pulse Combinar y elija un separador para los datos combinados, como un espacio, una coma o un punto y coma.
4. A continuación, haga clic en el botón Aceptar y los datos de la columna B se combinarán en una sola celda según la columna A. Vea la captura de pantalla:
5. A continuación, seleccione las celdas combinadas y haga clic en Kutools > Combinar y dividir > Dividir celdas. Vea la captura de pantalla:
6. En el cuadro de diálogo Dividir celdas, seleccione Dividir en columnas en la opción Tipo y, a continuación, elija el separador que utilizó para combinar los datos. Vea la captura de pantalla:
7. A continuación, haga clic en el botón Aceptar y seleccione una celda en la que colocar el resultado dividido en el cuadro de diálogo que aparece. Vea la captura de pantalla:
8. Haga clic en Aceptar y obtendrá el resultado que necesita. Vea la captura de pantalla:
¡Descargue y pruebe gratis Kutools para Excel ahora!
Demostración: Transponer celdas de una columna en función de valores únicos con Kutools para Excel
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos