una - excel eliminar duplicados y sumar valores
Excel: ¿cómo transponer las columnas seleccionadas y agrupar por valores repetidos?(Tabla 1D a 2D) (4)
Estoy trabajando con un volcado de datos de transacciones que no se exporta desde el sistema en particular en un formato muy amigable para ningún propósito.
Los datos en Excel se componen de aproximadamente 700,000 filas actualmente, mientras que una vez organizados correctamente solo deberían consistir en tal vez 70,000, donde los valores únicos de una columna en particular se transponen a algunas columnas adicionales.
Actualmente estoy trabajando con algo como este ejemplo (simplificado) a continuación;
Request_ID Status Field_Name Value
01000 Rejected Name John Smith
01000 Rejected Acc Number 123456
01000 Rejected Date 1/12/2015
01000 Rejected Enquiry Type Type 1
01000 Rejected Reason Reason 1
01001 Completed Name Jane Jones
01001 Completed Acc Number 123457
01001 Completed Date 1/12/2015
01001 Completed Enquiry Type Type 2
01001 Completed Reason Reason 2
Las primeras dos columnas ( Request_ID y Status ) son simplemente valores duplicados para atender las numerosas filas de datos relacionadas con cada solicitud individual.
La columna Field_Name es la columna particular que repite el mismo conjunto de valores en filas individuales para cada Request_ID único, y la columna Value enumera el valor correspondiente a cada Field_Name.
Son las columnas 3 y 4 ( Field_Name y Value ) las que me gustaría transponer en filas para producir algo como lo siguiente:
Request_ID Status Name Acc Number Date Enquiry Type Reason
01000 Rejected John Smith 123456 1/12/2012 Type 1 Reason 1
01001 Completed Jane Jones 123457 1/12/2012 Type 2 Reason 2
Como puede ver, el segundo ejemplo anterior es mucho más lógico y con menos datos redundantes, y además permitirá un filtrado y análisis simples.
Entonces, mi primer punto de llamada fue intentar implementar la manipulación anterior insertando los datos en una tabla dinámica, sin embargo, simplemente no podía entender la lógica. O si pudiera colocar los campos en una posición de columna similar, los valores simplemente calcularían un recuento en el cuerpo de la tabla, en lugar del valor en sí (como es la naturaleza de las tablas dinámicas).
¿Es posible usar la función Transpose? (que si es así, estoy más que feliz de investigarme a mí mismo)
¿Hay alguna solución alternativa que no he considerado?
( Nota : hay columnas adicionales en mi volcado de datos que no he incluido en el ejemplo, ya que contienen valores que simplemente se repiten para cada fila relacionada con un ID de solicitud individual, por lo que creo que puedo usar la misma lógica para cualquier solución aplicable encima)
Si puede orientarme en la dirección correcta, o proporcionar alguna orientación, sería muy apreciado ya que estoy perplejo.
Dependiendo de su versión de Excel, puede usar Power Pivot (2010/2013) o Get & Transform (2016) para pivotar los datos de manera apropiada. Sus datos, si no están ya en una tabla, se convertirán en uno.
Para este último, Seleccionar de la From Table abre el Editor de consultas. Después de seleccionar las columnas Nombre de campo y Valor, seleccione Transform ► Pivot Column
Esto abrirá un cuadro de diálogo Columna dinámica. Desea asegurarse de que las selecciones sean las siguientes. También debe seleccionar advanced para llegar a la opción do not aggregate .
Selecciona OK y tienes tus resultados como en tu pregunta. Cuando guarde la consulta, escribirá los resultados en una nueva hoja de trabajo. Tendrá que formatear la columna de fecha correctamente.
No estoy seguro de cómo funcionará esto con 700,000 filas. Es posible que necesite Excel de 64 bits.
Sin embargo, al mirar algunos de los comentarios a otras respuestas, esta solución debería funcionar con números variables de pares Field Name / Value de Field Name / Value .
Suponga que sus datos están en las columnas A: D. Copie la columna Request_ID a Col G. use excel. Elimine la función Duplicates para obtener ID únicos de solicitud. Cree encabezados para todos los posibles nombres de campo como nombre, número de acuse, etc., desde Col H en adelante y use la fórmula siguiente.
H2 =VLOOKUP(G2,$A$2:$D$11,2,FALSE)
I2 =INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$11=$G2)*--($C$2:$C$11=I$1)*ROW($A$2:$A$11)),4))
Arrastre la fórmula I2 para todos los demás nombres de campo.
Aquí hay una hoja de Google
Lo he intentado con la información que ha publicado y funciona bien. Si desea automatizar todo el proceso, puede intentar grabar Macro para todos estos pasos.
Necesidad clásica de la consulta de agregación condicional en SQL. Su ejemplo inicial refleja el modelo Entity-Attribute-Value . Para alinear correctamente las distintas columnas, agregue condicionalmente las columnas Field_Name , devolviendo el valor máximo (solo valor) de Value , agrupado por las columnas que se repiten (agregue a SELECT y GROUP BY para todas las demás columnas de repetición):
Consulta agregada condicional
SELECT Request_ID, Status,
MAX(IIF(Field_Name=''Name'', Value, NULL)) AS Name,
MAX(IIF(Field_Name=''Acc Number'', Value, NULL)) AS [Acc Number],
MAX(IIF(Field_Name=''Date'', Value, NULL)) AS [Date],
MAX(IIF(Field_Name=''Enquiry Type'', Value, NULL)) AS [Enquiry Type]
MAX(IIF(Field_Name=''Reason'', Value, NULL)) AS [Reason]
FROM [Worksheet$]
GROUP BY Request_ID, Status
Si usa Excel para PC, puede usar ACE SQL Engine (archivos .dll de Windows) para ejecutar la consulta SQL en el libro en VBA. Si usa Mac, importe datos en una base de datos como SQLite y ejecute la consulta anterior (reemplace IIF() para las sentencias CASE ). Para más de 700,000 registros, SQL puede ser una solución robusta en comparación con las fórmulas en línea o anidadas for bucles y matrices.
Debajo está la solución de Windows que usa ADO (dos conexiones disponibles) donde los datos residen en una pestaña de DATOS con una pestaña en blanco RESULTADOS usada para los resultados de la consulta.
Sub RunSQL()
Dim conn As Object, rst As Object
Dim strConnection As String, strSQL As String, i As Integer
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
'' CONNECTION STRINGS (DRIVER AND PROVIDER)
'' strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
'' & ActiveWorkbook.FullName ";"
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=''" & ActiveWorkbook.FullName & "'';" _
& "Extended Properties=""Excel 8.0;HDR=YES;"";"
strSQL = " SELECT Request_ID, Status," _
& " MAX(IIF(Field_Name=''Name'', Value, NULL)) AS Name," _
& " MAX(IIF(Field_Name=''Acc Number'', Value, NULL)) AS [Acc Number]," _
& " MAX(IIF(Field_Name=''Date'', Value, NULL)) AS [Date]," _
& " MAX(IIF(Field_Name=''Enquiry Type'', Value, NULL)) AS [Enquiry Type]," _
& " MAX(IIF(Field_Name=''Reason'', Value, NULL)) AS [Reason]" _
& " FROM [DATA$]" _
& " GROUP BY Request_ID, Status;"
'' OPEN CONNECTION AND RECORDSET
conn.Open strConnection
rst.Open strSQL, conn
'' HEADERS
For i = 0 To rst.Fields.Count - 1
Worksheets("RESULTS").Cells(1, i + 1) = rst.Fields(i).Name
Next i
'' DATA ROWS
Worksheets("RESULTS").Range("A2").CopyFromRecordset rst
rst.Close: conn.Close
End Sub
Alternativamente, para el número variable de Field_Name , puede usar la consulta de tabla cruzada única de ACE SQL que evita las posibilidades de valores de codificación rígida, como las condicionales en el agregado anterior. Y como ACE SQL limita las columnas a 255, la consulta siguiente solo puede devolver 253 o menos valores distintos de Field_Name (2 para el grupo repetido por columnas):
Consulta de tabla de referencias cruzadas
strSQL = " TRANSFORM Max(Value)" _
& " SELECT Request_ID, Status" _
& " FROM [DATA$]" _
& " GROUP BY Request_ID, Status" _
& " PIVOT Field_Name;"
Datos
Resultados
