Tabela dinâmica – Resumindo várias colunas de dados independentes

Question

Eu tenho uma tabela grande contendo entradas diárias em um sistema, que possui as colunas conforme mostrado:

Dia Itemname1 Itemquant1 Itemname2 Itemquant2 .....

Os nomes e quantidades dos itens são independentes um do outro. Em um dia, pode-se receber 10 itens A, e em outro dia, pode-se receber 5 do item D, 6 do item B e 234 do item C. No primeiro caso, apenas 2 colunas terão dados, e no último caso, 6 colunas terão dados. Todas as outras colunas estarão vazias. Um exemplo:

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

Como uma tabela dinâmica seria usada para exibir, digamos, as quantidades de A diariamente durante um ano, ou o número de valores diferentes de zero de A em um ano? Observe que A pode aparecer em múltiplas colunas e há centenas de itens possíveis, portanto não é viável simplesmente criar uma tabela com cada variável em uma coluna.

Tentei somar todas as colunas de várias maneiras, mas acaba retornando os valores de A, dados todos os valores de B, dados todos os valores de C, o que é útil se os valores forem dependentes, mas desorganiza a página uma vez que não estão correlacionados.

Caso isso não seja possível, qual seria um bom método para obter as informações exigidas da planilha?

Answer 1

Você precisa desesperadamente reorganizar seus dados. É realmente uma má ideia ter um número indeterminado de colunas pelos motivos que você está enfrentando agora. Meu conselho é, como outros sugeriram, converter seus dados em uma tabela simples de três colunas com uma linha para cada item diário.

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

Em seguida, você pode usar uma tabela dinâmica para resumir e detalhar os dados conforme desejar.

Então, agora a questão é como reorganizar esses dados em um número indeterminado de colunas. Felizmente, um procedimento VBA bastante simples pode fazer isso. Cole o código abaixo em um novo módulo VBA (pressione Alt+ F11e vá para 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

Certifique-se de ter a planilha com seus dados originais aberta. Em seguida, vá para o editor VBA e execute este código (pressionando F5). Este código produzirá os dados transformados em três colunas em uma nova planilha chamada “Dados Reorganizados”.

Answer 2

Para poder concluir de forma confiável a análise de dados nesse conjunto de dados, você precisa alterar a forma como os dados são configurados/organizados/inseridos.

Isso pode ser feito no Excel, mas também seria um banco de dados muito fácil de configurar.

Tabela para itens (número do item, nome, descrição)
Tabela para veículos (número do veículo, nome, descrição, tipo, marca do modelo)
Tabela de uso (número do item, número do veículo, data, número usado)

Você também pode fazer isso no Excel com planilhas diferentes e usando a validação de dados para fornecer uma lista suspensa do item e do veículo na planilha de uso. Se você não tem experiência em programação essa pode ser a melhor opção.

Desculpe não responder ao seu problema, mas o verdadeiro problema é a organização dos seus dados.

Answer 3

Você primeiro converterá a tabela no formato Day NQ abaixo com dados de amostra como

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

Então, se você girar nesta tabela, deverá obter o resultado. Para converter na tabela acima, você cria uma nova planilha. Suponha que você tenha 10 linhas e 3 pares de quantidades de nomes na planilha principal (assumindo Planilha1 como o nome da planilha e não a linha/coluna do cabeçalho), então você pode usar a fórmula abaixo na nova planilha da linha 1 a 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

Por favor, encontre esta imagem abaixo. Os uns representam a peça que foi reparada e o zero representa a peça que “não foi alterada”. Se é assim que seus dados são representados, você pode usar uma tabela dinâmica simples para resolver seu problema.