Сводная таблица - суммирование нескольких независимых столбцов данных

Question

У меня есть большая таблица, содержащая ежедневные входы в систему, в которой есть столбцы, как показано:

День Itemname1 Itemquant1 Itemname2 Itemquant2 .....

Названия и количество товаров не зависят друг от друга. В один день можно получить 10 предметов A, а в другой день - 5 предметов D, 6 предметов B и 234 предметов C. В первом случае данные будут иметь только 2 столбца, а в В последнем случае 6 столбцов будут иметь данные. Все остальные столбцы будут пустыми. Пример:

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

Как будет использоваться сводная таблица для отображения, скажем, количества A ежедневно в течение года или числа ненулевых значений A в году? Обратите внимание, что A может отображаться в нескольких столбцах, и существуют сотни возможных элементов, поэтому просто создать таблицу с каждой переменной в столбце невозможно.

Я попытался добавить все столбцы несколькими способами, но в итоге он возвращает значения A, учитывая все значения B, учитывая все значения C, что полезно, если значения являются зависимыми, но загромождает страницу так как они некоррелированы.

Если это невозможно, каким будет хороший способ получить информацию, требуемую из электронной таблицы?

Answer 1

Чтобы когда-либо иметь возможность надежно завершить анализ данных на этом наборе данных, вам нужно изменить способ настройки / организации / ввода данных.

Это может быть сделано в Excel, но это будет довольно простая в настройке база данных.

Таблица для элементов (номер элемента, имя, описание)
Таблица для транспортных средств (номер автомобиля, имя, описание, тип, модель)
Таблица использования (номер изделия, номер автомобиля, дата, номер)

Вы также можете сделать это в Excel с различными листами и с помощью проверки данных, чтобы дать им раскрывающийся список предметов и транспортных средств в листе использования. Если у вас нет опыта программирования, это может быть лучшим вариантом.

Извините, что не ответил на вашу проблему, но реальная проблема заключается в организации ваших данных.

Answer 2

Сначала вы преобразуете таблицу в формат «День NQ» ниже, с образцами данных в виде

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

Тогда, если вы развернетесь на этой таблице, вы должны получить результат. Чтобы преобразовать в таблицу выше, вы создаете новый лист. Предположим, у вас есть 10 строк и пара 3name name на главном листе (при условии, что Sheet1 является именем листа, а не строки / столбца заголовка), и вы можете использовать приведенную ниже формулу в новом листе от 1 до 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 3

Я бы разрешил это с помощью Power Query Add-In. Для этого требуется несколько шагов, но не требуется никакого кода или изменений в структуре входных данных.

Я создал прототип, который вы можете просмотреть или скачать - его демонстрация Power Query - суммирование нескольких независимых столбцов на моем One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

По сути, моя техника заключалась в том, чтобы построить 2 предварительных запроса, один для разворачивания значений "N", а другой для разворачивания значений Q (преобразования нескольких столбцов в несколько строк), а затем заключительный запрос для объединения двух наборов данных обратно.

В этот момент у вас есть нормализованная таблица, которая идеально подходит для исследования путем фильтрации или использования сводной таблицы и / или сводной диаграммы.

Answer 4

Вам крайне необходимо реорганизовать ваши данные. Это действительно плохая идея иметь неопределенное количество столбцов по причинам, с которыми вы сейчас сталкиваетесь. Мой совет, как другие предлагали, преобразовывать ваши данные в простую таблицу из трех столбцов со строкой для каждого ежедневного элемента.

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

Затем вы можете использовать сводную таблицу для суммирования и детализации данных по своему усмотрению.

Итак, теперь вопрос заключается в том, как организовать реорганизацию этих данных в неопределенном количестве столбцов. К счастью, довольно простая процедура VBA может сделать это. Вставьте приведенный ниже код в новый модуль VBA (нажмите Alt+F11, затем перейдите к « 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

Обязательно откройте рабочий лист с исходными данными. Затем перейдите в редактор VBA и запустите этот код (нажав F5). Этот код выведет преобразованные данные в три столбца на новом листе с именем "Реорганизованные данные".

Answer 5

Пожалуйста, найдите это изображение ниже. Те представляют часть, которая была обслужена, и ноль представляет часть, которая была "не изменена". Если так представлены ваши данные, вы можете использовать простую сводную таблицу для решения вашей проблемы.