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

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

День Itemname1 Itemquant1 Itemname2 Itemquant2 …..

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

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

  • Как расширить все столбцы в электронной таблице Excel?
  • Постоянно вставляйте строки из верхней части страницы в Excel
  • Отображение негативных меток времени в Excel 2007chart
  • Почему Excel считает CSV-файлы SYLK?
  • Формула Excel для среднесуточной прибыли
  • Когда Excel решает перегрузить текст в соседние ячейки или вырезать их на границе?
  • Диспетчер сценариев Excel
  • Применение формулы к диапазону ячеек без перетаскивания
  • 5 Solutions collect form web for “Сводная таблица – суммирование нескольких независимых столбцов данных”

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Давайте будем гением компьютера.