☰

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

tag-icon • tag-icon microsoft-excel
Сводная таблица — суммирование нескольких независимых столбцов данных

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

День Имя элемента1 Количество элементов1 Имя элемента2 Количество элементов2 .....

Названия и количества товаров не зависят друг от друга. В один день можно получить 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, что полезно, если значения зависимы, но загромождает страницу, поскольку они не коррелируют.

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

решение1

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

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). Этот код выведет преобразованные данные в три столбца на новом рабочем листе с именем «Реорганизованные данные».

решение2

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

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

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

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

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

решение3

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

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

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

решение4

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

Связанный контент