Tabla dinámica: resumen de varias columnas de datos independientes

Question

Tengo una tabla grande que contiene entradas diarias a un sistema, que tiene las columnas como se muestra:

Día Nombre del artículo1 Cantidad de artículo1 Nombre del artículo2 Cantidad de artículo2 .....

Los nombres de los artículos y las cantidades son independientes entre sí. Un día, uno podría recibir 10 elementos A, y otro día, podría recibir 5 del elemento D, 6 del elemento B y 234 del elemento C. En el primer caso, solo 2 columnas tendrán datos, y en el En el último caso, 6 columnas tendrán datos. Todas las demás columnas estarán vacías. Un ejemplo:

Day N1  Q1  N2  Q2  N3  Q3
1   A   10
2   D   5   B   6   C   234

¿Cómo se usaría una tabla dinámica para mostrar, digamos, las cantidades de A diarias durante un año, o el número de valores distintos de cero de A en un año? Tenga en cuenta que A puede aparecer en varias columnas y que hay cientos de elementos posibles, por lo que no es factible simplemente crear una tabla con cada variable en una columna.

Intenté agregar todas las columnas de varias maneras, pero termina devolviendo los valores de A, dados todos los valores de B, dados todos los valores de C, lo cual es útil si los valores son dependientes, pero abarrota la página. ya que no están correlacionados.

Si esto no es posible, ¿cuál sería un buen método para obtener la información requerida de la hoja de cálculo?

Answer 1

Necesita desesperadamente reorganizar sus datos. Es una muy mala idea tener un número indeterminado de columnas por las razones que estás encontrando ahora. Mi consejo es, como han sugerido otros, convertir sus datos en una tabla simple de tres columnas con una fila para cada elemento diario.

Day  Item  Quant
 1     A     10
 2     D      5
 2     B      6
 2     C    234

Luego, puede utilizar una tabla dinámica para resumir y profundizar en los datos como desee.

Entonces, ahora la cuestión es cómo reorganizar estos datos en un número indeterminado de columnas. Afortunadamente, un procedimiento VBA bastante simple puede lograrlo. Pegue el siguiente código en un nuevo módulo VBA (presione Alt+ F11, luego vaya a Insert> Module).

Sub SalvageMyTable()
Dim sOrig As Worksheet, sOut As Worksheet
Dim arrIn() As Variant
Dim rOut As Range, arrOut() As Variant
Dim i As Long, j As Long, k As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set sOrig = ActiveSheet
'Store original data in an array for faster processing
arrIn = sOrig.UsedRange.Value
'Create maximal output array. This can be resized before outputting the data.
ReDim arrOut(0 To 2, 0 To UBound(arrIn, 1) * (UBound(arrIn, 2) - 1) / 2) As Variant
'Loop through original table and copy values to output array.
For i = LBound(arrIn, 1) + 1 To UBound(arrIn, 1)
    For j = LBound(arrIn, 2) + 1 To UBound(arrIn, 2) Step 2
        If arrIn(i, j) <> "" Then
            arrOut(0, k) = arrIn(i, LBound(arrIn, 2))
            arrOut(1, k) = arrIn(i, j)
            arrOut(2, k) = arrIn(i, j + 1)
            k = k + 1
        Else
            Exit For
        End If
    Next j
Next i
'Resize output array
ReDim Preserve arrOut(0 To 2, 0 To k) As Variant
'Create output sheet and print output there.
Set sOut = Worksheets.Add
sOut.Name = "Reorganized Data"
sOut.Range("A1").Value = "Day"
sOut.Range("B1").Value = "Item"
sOut.Range("C1").Value = "Quantity"
sOut.Range("A2").Resize(k, 3).Value = Application.WorksheetFunction.Transpose(arrOut)
'Reset Excel settings
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Asegúrese de tener abierta la hoja de trabajo con sus datos originales. Luego vaya al editor de VBA y ejecute este código (presionando F5). Este código generará los datos transformados en tres columnas en una nueva hoja de trabajo denominada "Datos reorganizados".

Answer 2

Para poder completar de manera confiable el análisis de datos en ese conjunto de datos, debe cambiar la forma en que se configuran/organizan/ingresan los datos.

Se puede hacer en Excel, pero también sería una base de datos bastante fácil de configurar.

Tabla de artículos (número de artículo, nombre, descripción)
Tabla de vehículos (número de vehículo, nombre, descripción, tipo, marca y modelo)
Tabla de uso (número de artículo, número de vehículo, fecha, número usado)

También puede hacer esto en Excel con diferentes hojas y usar la validación de datos para mostrarles un menú desplegable del artículo y vehículo en la hoja de uso. Si no tienes experiencia en programación, esa podría ser la mejor opción.

Lamento no responder a su problema, pero el verdadero problema es la organización de sus datos.

Answer 3

Primero convertirá la tabla al siguiente formato Día NQ con datos de muestra como

1 A 10
2 D 6
2 B 6
2 C 234

Luego, si gira sobre esta tabla, debería obtener el resultado. Para convertir en la tabla anterior, crea una nueva hoja. Supongamos que tiene 10 filas y un par de cantidad de 3 nombres en la hoja principal (suponiendo que la Hoja 1 sea el nombre de la hoja y no la fila/columna del encabezado), entonces puede usar la siguiente fórmula en una hoja nueva desde la fila 1 a la 30 (10*3)

1    =Sheet1!A1  =Sheet1!B1  =Sheet1!C1
    ...
10   =Sheet1!A10  =Sheet1!B10  =Sheet1!C10
11   =Sheet1!A1  =Sheet1!D1  =Sheet1!E1
    ...
20   =Sheet1!A10  =Sheet1!D10  =Sheet1!E10
21   =Sheet1!A1  =Sheet1!F1  =Sheet1!G1
    ...
30   =Sheet1!A10  =Sheet1!F10  =Sheet1!G10

Answer 4

Encuentre esta imagen a continuación. Los unos representan la pieza que fue reparada y el cero representa la pieza que "no fue cambiada". Si así es como se representan sus datos, puede usar una tabla dinámica simple para resolver su problema.