Как я могу преобразовать данные сводного стиля в данные списка?
По сути, у меня есть данные, расположенные как сводная таблица в примере с синей таблицей, и я хотел бы сделать «обратный свод», чтобы отформатировать его, как в зеленой таблице. Это не настоящая сводная таблица, но она устроена таким же образом. В идеале я хотел бы просто использовать формулы, но если это невозможно, какой лучший метод в VBA для достижения этого? Мой фактический набор данных довольно велик (~ 350 строк на ~ 100 столбцов) и должен постоянно обновляться, поэтому он должен быть полностью автоматизирован. Любое руководство очень ценится!
Я пробовал несколько встроенных функций Excel, но не могу найти что-то, что соответствует моему случаю.
Я бы использовал Power Query и развернул бы эти столбцы.
Извините, я должен был упомянуть, что у меня Excel 2016 года и, к сожалению, у меня нет доступа к этим функциям!
Ответы 1
Рассматривали ли вы «Отменить поворот» в Excel PowerQuery, как объясняется в этой статье?
Это работает только в том случае, если таблица не отформатирована как сводная таблица Excel, иначе вы получите сообщение об ошибке «Таблица не может перекрывать диапазон, содержащий отчет сводной таблицы, результаты запроса, защищенные ячейки или другую таблицу».
Вот пример данных, которые ранее были сведены:
+--------+------+------+------+
| SKU | 1 | 7 | 8 |
+--------+------+------+------+
| 006101 | 0.87 | 0.87 | 0.87 |
| 006309 | 0.87 | 0.87 | 0.87 |
| 006507 | 0.87 | 0.87 | 0.87 |
| 006705 | 0.87 | 0.87 | 0.87 |
+--------+------+------+------+
После загрузки в PowerQuery вы выбираете столбцы, которые хотите развернуть, затем переходите к «Преобразованию» и выбираете «Отменить сводные столбцы» в группе «Любой столбец». Я выбрал столбцы 1, 7, 8.
Это результирующий М-код:
let
Source = Excel.CurrentWorkbook(){[Name = "MyDataSource"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"1", type number}, {"7", type number}, {"8", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SKU"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "ST"}})
in
#"Renamed Columns"
И это результат, когда вы загружаете данные обратно в Excel (вы также можете просто создать соединение в PowerQuery, если хотите обработать его дальше):
+--------+----+-------+
| SKU | ST | Value |
+--------+----+-------+
| 006101 | 1 | 0.87 |
| 006101 | 7 | 0.87 |
| 006101 | 8 | 0.87 |
| 006309 | 1 | 0.87 |
| 006309 | 7 | 0.87 |
| 006309 | 8 | 0.87 |
| 006507 | 1 | 0.87 |
| 006507 | 7 | 0.87 |
| 006507 | 8 | 0.87 |
| 006705 | 1 | 0.87 |
| 006705 | 7 | 0.87 |
| 006705 | 8 | 0.87 |
+--------+----+-------+
После публикации этого я увидел, что вы заявили, что у вас нет доступа к PowerQuery. PowerQuery был включен в Excel с вашей версии, которая, как вы говорите, 2016 года. (microsoft.com/en-us/microsoft-365/blog/2015/09/10/…)
Всегда пожалуйста. Рад слышать, что у вас все получилось!
=TEXTSPLIT(TEXTJOIN(";",0,A2:A5&","&B1:F1&","&B2:F5),",",";")будет работать на примере. Не уверен, что это работает с большим набором данных