P. Mark de Kansas envió la pregunta de Excel de esta semana:
Las tablas dinámicas de Excel funcionan mejor cuando los datos se dividen en la mayor cantidad de registros posible, pero esta no siempre es la forma más intuitiva de cargar los datos en Excel. Por ejemplo, es intuitivo cargar datos como en la Figura 1, pero la mejor manera de analizar los datos es como en la Figura 2.
Primero, analizaré la forma menos que perfecta de Excel de tratar con múltiples campos de datos. En segundo lugar, haré una demostración de una macro simple y rápida para convertir la figura 1 en la figura 2.
Si sus datos son como la figura 1, durante el paso 3 de 4 del Asistente para tablas dinámicas, es posible arrastrar los 4 campos de cuartos al área de datos de la tabla dinámica, como se muestra a la derecha.
Aquí está el resultado menos que perfecto. De forma predeterminada, Excel tiene varios campos de datos en la página. Puede hacer clic en el botón gris "Datos" y arrastrarlo hacia arriba y hacia la derecha para que los cuartos pasen por la página. Sin embargo, faltan los totales para cada región y los totales del trimestre siempre parecerán fuera de lugar.
Entonces, el P. Mark dio en el clavo cuando dijo que los datos realmente deben estar en el formato de la Figura 2 para poder analizarlos correctamente. A continuación se muestra una macro que moverá rápidamente datos en el formato de la Figura 1 en Sheet1 a Sheet2 en el formato de la Figura 2. Esta macro no es lo suficientemente general para trabajar con ningún conjunto de datos. Sin embargo, debería ser relativamente fácil personalizarlo para su situación particular.
Public Sub TransformData() ' Copyright 1999.com Sheets("Sheet2").Select Range("A1").CurrentRegion.Clear Sheets("Sheet1").Select Range("A1:B1").Copy Destination:=Sheets("Sheet2").Range("A1") Sheets("Sheet2").Select Range("C1").Value = "Qtr" Range("D1").Value = "Sales" Sheets("Sheet1").Select FinalRow = Range("A16000").End(xlUp).Row NextRow = 2 LastRow = FinalRow ' Loop through the data columns For i = 3 To 6 ThisCol = Mid("ABCDEFGHIJK", i, 1) ' Copy the left columns from sheet1 to sheet2 Range("A2:B" & FinalRow).Copy Destination:= _ Sheets("Sheet2").Range("A" & NextRow) ' Copy the header from ThisCol to column C Range(ThisCol & "1").Copy Destination:= _ Sheets("Sheet2").Range("C" & NextRow & ":C" & LastRow) ' Copy the data for this quarter to column D Range(ThisCol & "2:" & ThisCol & FinalRow).Copy _ Destination:=Sheets("Sheet2").Range("D" & NextRow) NextRow = LastRow + 1 LastRow = NextRow + FinalRow - 2 Next i Sheets("Sheet2").Select End Sub
Después de ejecutar esta macro, los datos estarán en el formato más fácil de analizar que se muestra en la figura 2, arriba. Ahora, cuando usa estos datos para una tabla dinámica, tiene el control total de los datos como de costumbre.
