¿Cómo transponer celdas en una columna en función de valores únicos en otra columna?
Supongamos que tiene un rango de datos que contiene dos columnas, ahora desea transponer celdas en una columna a filas horizontales basadas en valores únicos en otra columna para obtener el siguiente resultado. ¿Tiene alguna buena idea para resolver este problema en Excel?
Transponer celdas en una columna según valores únicos con fórmulas
Transponer celdas en una columna según valores únicos con código VBA
Transponer celdas en una columna según valores únicos con Kutools para Excel
Transponer celdas en una columna según valores únicos con fórmulas
Con las siguientes fórmulas de matriz, puede extraer los valores únicos y transponer sus datos correspondientes en filas horizontales, haga lo siguiente:
1. Ingrese esta fórmula de matriz: = ÍNDICE ($ A $ 2: $ A $ 16, COINCIDIR (0, CONTAR.SI ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) en una celda en blanco, D2, por ejemplo, y presione Mayús + Ctrl + Entrar claves juntas para obtener el resultado correcto, vea la captura de pantalla:
Note: En la fórmula anterior, A2: A16 es la columna de la que desea enumerar los valores únicos y D1 es la celda sobre esta celda de fórmula.
2. Luego arrastre el controlador de relleno hacia las celdas para extraer todos los valores únicos, vea la captura de pantalla:
3. Y luego continúe ingresando 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 recuerda presionar Mayús + Ctrl + Entrar claves para obtener el resultado, vea la captura de pantalla:
Note: En la fórmula anterior: B2: B16 son los datos de la columna que desea transponer, A2: A16 es la columna en la que desea transponer los valores según, y D2 contiene el valor único que extrajo en el Paso 1.
4. Luego arrastre el controlador de relleno a la derecha de las celdas en las que desea enumerar los datos transpuestos hasta que muestre 0, vea la captura de pantalla:
5. Y luego continúe arrastrando el controlador de relleno hasta el rango de celdas para obtener los datos transpuestos como se muestra en la siguiente captura de pantalla:
Transponer celdas en una columna según valores únicos con código VBA
Puede ser que las fórmulas sean complejas para que las entienda, aquí, puede ejecutar el siguiente código VBA para obtener el resultado deseado que necesita.
1. Mantenga pulsado el ALT + F11 teclas para abrir el Microsoft Visual Basic para aplicaciones ventana.
2. Hacer clic recuadro > Móduloy pegue el siguiente código en el Módulo Ventana.
Código de VBA: transponer celdas en una columna según valores únicos en 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 Sub
3. Entonces presione F5 para ejecutar este código, y aparecerá un cuadro emergente para recordarle que seleccione el rango de datos que desea usar, vea la captura de pantalla:
4. Y luego haz clic OK , aparecerá otro cuadro de aviso para recordarle que seleccione una celda para poner el resultado, vea la captura de pantalla:
6. Hacer clic OK , y los datos en la columna B se han transpuesto en función de valores únicos en la columna A, vea la captura de pantalla:
Transponer celdas en una columna según valores únicos con Kutools para Excel
Si tiene Kutools for Excel, combinando el Filas combinadas avanzadas y Células partidas utilidades, puede finalizar rápidamente esta tarea sin fórmulas ni códigos.
| Kutools for Excel : con más de 300 prácticos complementos de Excel, prueba gratuita y sin límite en 30 días. |
Después de instalar Kutools for Excel, haga lo siguiente:
1. Seleccione el rango de datos que desea utilizar. (Si desea conservar los datos originales, primero copie y pegue los datos en otra ubicación).
2. Luego haga clic Kutools > Fusionar y dividir > Filas combinadas avanzadas, ver captura de pantalla:
3. En la Combinar filas según la columna cuadro de diálogo, realice las siguientes operaciones:
(1.) Haga clic en el nombre de la columna en la que desea transponer los datos y seleccione Clave primaria;
(2.) Haga clic en otra columna que desee transponer y haga clic en Combinar luego elija un separador para separar los datos combinados, como espacio, coma, punto y coma.
4. Luego haga clic Ok botón, los datos en la columna B se han combinado en una celda basada en la columna A, vea la captura de pantalla:
5. Y luego seleccione las celdas combinadas y haga clic en Kutools > Fusionar y dividir > Células partidas, ver captura de pantalla:
6. En la Células partidas cuadro de diálogo, seleccione Dividir en columnas bajo el Tipo de Propiedad opción, y luego elija el separador que separa sus datos combinados, vea la captura de pantalla:
7. Luego haga clic Ok y seleccione una celda para poner el resultado dividido en el cuadro de diálogo emergente, vea la captura de pantalla:
8. Hacer clic OK, y obtendrá el resultado que necesite. Ver captura de pantalla:
¡Descargue y pruebe Kutools para Excel ahora!
Demostración: transponga celdas en una columna según valores únicos con Kutools para Excel
Las mejores herramientas de productividad de oficina
Mejore sus habilidades de Excel con Kutools for Excel y experimente la eficiencia como nunca antes. Kutools for Excel ofrece más de 300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haga clic aquí para obtener la función que más necesita...
Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Habilite 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 de la misma ventana, en lugar de en nuevas ventanas.
- ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
