Excel: как перенести столбцы выбора и группировать повторяющиеся значения? (1D-2D-таблица)

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

Данные в Excel состоят из приблизительно 700 000 строк в настоящее время, тогда как правильно организованный должен состоять только из 70 000, где уникальные значения одного конкретного столбца переносятся в некоторые дополнительные столбцы.

В настоящее время я работаю с чем-то вроде этого (упрощенного) примера ниже;

Request_ID Status Field_Name Value 01000 Rejected Name John Smith 01000 Rejected Acc Number 123456 01000 Rejected Date 1/12/2015 01000 Rejected Enquiry Type Type 1 01000 Rejected Reason Reason 1 01001 Completed Name Jane Jones 01001 Completed Acc Number 123457 01001 Completed Date 1/12/2015 01001 Completed Enquiry Type Type 2 01001 Completed Reason Reason 2 

Первые два столбца ( Request_ID и Status ) являются просто дублируемыми значениями, чтобы обслуживать многочисленные строки данных, относящихся к каждому отдельному запросу.

Столбец Field_Name – это конкретный столбец, который повторяет один и тот же набор значений для отдельных строк для каждого уникального Request_ID, а столбец Value содержит соответствующее значение, относящееся к каждому полю.

Это столбцы 3 и 4 ( Field_Name и Value ), которые я хотел бы транспонировать в строки, чтобы создать что-то вроде ниже:

 Request_ID Status Name Acc Number Date Enquiry Type Reason 01000 Rejected John Smith 123456 1/12/2012 Type 1 Reason 1 01001 Completed Jane Jones 123457 1/12/2012 Type 2 Reason 2 

Как вы можете видеть, второй пример выше намного логичнее и с меньшими избыточными данными – и, кроме того, позволит простую фильтрацию и анализ.

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

Можно ли использовать функцию транспонирования? (если это так, я более чем счастлив расследовать себя)

Есть ли альтернативное решение, которое я не рассматривал?

( Side Note – в моем дампе данных есть дополнительные столбцы, которые я не включил в пример, поскольку они содержат значения, которые просто повторяются для каждой строки, связанной с отдельным Request_ID, поэтому я полагаю, что могу использовать ту же логику для любого применимого решения выше)

Если вы можете указать мне в правильном направлении или дать какое-либо руководство, было бы очень полезно, поскольку я в тупике.

В зависимости от вашей версии Excel вы можете использовать Power Pivot (2010/2013) или Get & Transform (2016) для правильной привязки данных. Ваши данные, если они еще не были в таблице, будут преобразованы в один.

введите описание изображения здесь

Для последнего в разделе «Выбор From Table открывается редактор запросов. После выбора столбцов «Имя поля» и «Значение» выберите « Transform ► « Pivot Column

введите описание изображения здесь

Это вызовет диалог Pivot Column. Вы хотите быть уверенными в том, что они выбраны ниже. Также вы должны выбрать advanced чтобы перейти к опции « do not aggregate .

введите описание изображения здесь

Выберите ОК, и у вас есть результаты, как в вашем вопросе. Когда вы сохраните запрос, он напишет результаты на новый рабочий лист. Вам нужно правильно отформатировать столбец даты.

введите описание изображения здесь

Я не уверен, как это будет работать с 700 000 строк. Возможно, вам понадобится 64-разрядный Excel.

Однако, глядя на некоторые комментарии к другим ответам, это решение должно работать с различным количеством пар Field Name / Value .

Предположим, что ваши данные находятся в столбцах A: D. Копировать столбец Request_ID в Col G. используйте функцию excel Remove Duplicates для получения уникальных идентификаторов запросов. Создайте заголовки для всех возможных имен полей, таких как Name, Acc Number и т. Д. Из Col H onwards и используйте приведенную ниже формулу.

H2 =VLOOKUP(G2,$A$2:$D$11,2,FALSE)

I2 =INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$11=$G2)*--($C$2:$C$11=I$1)*ROW($A$2:$A$11)),4))

Перетащите формулу I2 для всех других имен полей.

Вот Google Sheet

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

Классическая потребность в запросе условной агрегирования в SQL. Ваш первоначальный пример отражает модель объекта-атрибута . Для правильного согласования с отдельными столбцами агрегируйте условно столбцы Field_Name , возвращая значение max (только значение) Value , сгруппированное повторяющимися столбцами (добавьте SELECT и GROUP BY для всех остальных столбцов повтора):

Условный агрегированный запрос

 SELECT Request_ID, Status, MAX(IIF(Field_Name='Name', Value, NULL)) AS Name, MAX(IIF(Field_Name='Acc Number', Value, NULL)) AS [Acc Number], MAX(IIF(Field_Name='Date', Value, NULL)) AS [Date], MAX(IIF(Field_Name='Enquiry Type', Value, NULL)) AS [Enquiry Type] MAX(IIF(Field_Name='Reason', Value, NULL)) AS [Reason] FROM [Worksheet$] GROUP BY Request_ID, Status 

Если вы используете Excel для ПК, вы можете использовать ACE SQL Engine (файлы Windows .dll) для запуска SQL-запроса в книге в VBA. Если вы используете Mac, импортируйте данные в базу данных, такую ​​как SQLite, и выполните запрос выше (замените IIF() для операторов CASE ). Для 700 000+ записей SQL может быть надежным решением по сравнению с встроенными формулами или вложенными for циклов и массивов.

Ниже приведено решение Windows с использованием ADO (доступно два подключения), где данные находятся на вкладке DATA с пустой вкладкой RESULTS, используемой для результатов запроса.

 Sub RunSQL() Dim conn As Object, rst As Object Dim strConnection As String, strSQL As String, i As Integer Set conn = CreateObject("ADODB.Connection") Set rst = CreateObject("ADODB.Recordset") ' CONNECTION STRINGS (DRIVER AND PROVIDER) ' strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _ ' & ActiveWorkbook.FullName ";" strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source='" & ActiveWorkbook.FullName & "';" _ & "Extended Properties=""Excel 8.0;HDR=YES;"";" strSQL = " SELECT Request_ID, Status," _ & " MAX(IIF(Field_Name='Name', Value, NULL)) AS Name," _ & " MAX(IIF(Field_Name='Acc Number', Value, NULL)) AS [Acc Number]," _ & " MAX(IIF(Field_Name='Date', Value, NULL)) AS [Date]," _ & " MAX(IIF(Field_Name='Enquiry Type', Value, NULL)) AS [Enquiry Type]," _ & " MAX(IIF(Field_Name='Reason', Value, NULL)) AS [Reason]" _ & " FROM [DATA$]" _ & " GROUP BY Request_ID, Status;" ' OPEN CONNECTION AND RECORDSET conn.Open strConnection rst.Open strSQL, conn ' HEADERS For i = 0 To rst.Fields.Count - 1 Worksheets("RESULTS").Cells(1, i + 1) = rst.Fields(i).Name Next i ' DATA ROWS Worksheets("RESULTS").Range("A2").CopyFromRecordset rst rst.Close: conn.Close End Sub 

В качестве альтернативы, для переменного числа Field_Name , вы можете использовать уникальный кросс-столбельный запрос ACE SQL, который избегает возможности использования жесткого кодирования, такие как условные Field_Name в приведенном выше агрегате. И поскольку ACE SQL ограничивает столбцы до 255, ниже запрос может возвращать только 253 или менее различных значений Field_Name (2 для повторных столбцов groupby):

Запрос кросс-таблицы

 strSQL = " TRANSFORM Max(Value)" _ & " SELECT Request_ID, Status" _ & " FROM [DATA$]" _ & " GROUP BY Request_ID, Status" _ & " PIVOT Field_Name;" 

Данные

Входные данные

Результаты

Выходные данные

Выберите любую ячейку, скажем G6, и введите:

 =INDEX(A:A,(ROWS($1:1)-1)*5+2) 

и копировать. В H6 введите:

 =INDEX(B:B,(ROWS($1:1)-1)*5+2) 

и копировать. Наконец, в I6 введите:

 =OFFSET($D$2,COLUMNS($A:A)-1+(ROWS($1:1)-1)*5,0) 

и скопируйте эту ячейку как вверх, так и вниз:

введите описание изображения здесь

  • Сброс отключен после добавления функции CUBESET
  • Данные диаграммы Excel представлены в днях, я могу показать таблицы данных серии в месяцах
  • Эксел-решатель не будет работать, если ограничение вначале неудовлетворено
  • INDEX / MATCH MAX суммы / разницы
  • Фильтры DataValidation Таблица
  • Excel Column Autofit из Delphi XE2
  • Как я могу создать многозадачное выпадающее меню Excel с условием «If ... Then»?
  • Excel VBA по-прежнему запрашивает пароль даже после кодирования пароля
  • Код Excel VBA не работает
  • Не удалось импортировать 5 ГБ в TXT-файл в PowerPivot 2010
  • Коллекция с названием диапазона Excel 2010 пуста
  • Interesting Posts

    Усреднение нескольких столбцов и игнорирование любых значений 0% excel 2007

    Переключение между книгами в Excel VBA

    Конвертирование XLS в PDF без потери форматирования

    копирование нескольких значений в ряд и вставка его несколько раз в одной строке в excel с использованием VBA

    Как выбрать подмножество наименее напоминающих комбинаций общего вектора?

    Listbox не будет фокусироваться при выборе другой ячейки

    Ошибка определения диапазона строк

    Как изменить определенные значения в именованном диапазоне

    COUNTIF, сколько раз значение появляется в столбце, в зависимости от содержимого значения ячейки

    Как сохранить копию текущего Excel в CSV в новую папку?

    Excel возвращает целую строку, когда ячейка столбца соответствует значению, используя только формулу

    Excel VBA Clear Formula и сохранить перезаписанную стоимость

    Формула для использования пути к файлу, хранящегося в ячейке

    Работа с несколькими Windows; Сохранение одного статического

    Добавление новой строки в VBA

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