Блог

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

Моя таблица Excel выглядит следующим образом :

 Company | Department   | Area   | Project1_B | Project1_A | Project2_B | Project2_A | Project3_B | Project3_A

Company1  Department1    Area1    High         Medium                                 High         Low
Company1  Department1    Area2                               Low          Medium
Company2  Department1    Area1    Medium       Medium        High         Low
Company2  Department2    Area1                                                        Low          High
 

По сути, для каждой строки (представляющей область в организации) существуют статусы до (b) и после (a) для ряда проектов (projects1-100)

Не все строки будут иметь значения для каждого проекта, как показано выше, но каждый проект всегда будет иметь «пару» (т.Е. Статус до и после).

Результат, которого я пытаюсь достичь, выглядит следующим образом :

 Company   |  Department  |  Area   |  Project_Name   |  Before   |  After
Company1     Department1    Area1     Project1          High        Medium
Company1     Department1    Area1     Project3          High        Low
Company1     Department1    Area2     Project2          Low         Medium
Company2     Department1    Area1     Project1          Medium      Medium
Company2     Department1    Area1     Project2          High        Low
Company2     Department1    Area1     Project2          High        Low
Company2     Department2    Area1     Project3          Low         High
 

Где транспонируются только «непустые» записи. Для каждого проекта, над которым работала область, должна быть строка.

Я пробовал различные методы транспонирования и VLOOKUPS, но безрезультатно. Это расстраивает, потому что я подозреваю, что это довольно простое решение, но любые поиски, которые я сделал, не совсем то, что я ищу!

Возможно ли то, что я хочу сделать, с помощью формулы или VB, или мне лучше сбросить данные в базу данных и попробовать это?

Спасибо за любую помощь,

Одним из способов этого является использование VBA.

 Option Explicit

Sub TransposeData()
Dim FromSheet As Worksheet
Dim ToSheet As Worksheet
Dim FromColumn As Integer
Dim FromRow As Integer
Dim ToRow As Integer

Set FromSheet = Sheets("Sheet1")
Set ToSheet = Sheets("Sheet2")

ToRow = 2
For FromRow = 2 To FromSheet.UsedRange.Rows.Count
  For FromColumn = 4 To FromSheet.UsedRange.Columns.Count Step 2
    If FromSheet.Cells(FromRow, FromColumn) <> "" Then
       ToSheet.Cells(ToRow, 1) = FromSheet.Cells(FromRow, 1)              ' Column A: Company
       ToSheet.Cells(ToRow, 2) = FromSheet.Cells(FromRow, 2)              ' Column B: Dept
       ToSheet.Cells(ToRow, 3) = FromSheet.Cells(FromRow, 3)              ' Column C: Area
       ToSheet.Cells(ToRow, 4) = FromSheet.Cells(1, FromColumn)           ' Column D: Project before (from heading)
       ToSheet.Cells(ToRow, 5) = FromSheet.Cells(1, FromColumn   1)       ' Column E: Project after (from heading)
       ToSheet.Cells(ToRow, 6) = FromSheet.Cells(FromRow, FromColumn)     ' Column F: Data - Before
       ToSheet.Cells(ToRow, 7) = FromSheet.Cells(FromRow, FromColumn   1) ' Column G: Data - After
       ToRow = ToRow   1
     End If
   Next FromColumn
 Next FromRow
End Sub
 

Предполагая, что повторяющиеся строки в ваших результатах (5 и 6) являются ошибкой, это относительно простая операция сводки (без агрегации и после отмены запроса) с использованием Power Query.

Если вы измените исходную таблицу, a Refresh обновит таблицу результатов.

• С выделенной ячейкой в таблице: Data => Get amp; Transform => From Table/Range
• В пользовательском интерфейсе PQ выберите первые три столбца: "Company", "Department", "Area" , затем Unpivot other columns
• Результирующий столбец атрибутов будет иметь Project1_B, etc .
  • Разделите этот столбец на подчеркивание
  • Замените B с Before и A с After
• Выберите Attribute.2 столбец и сводную
  • Столбец значений = значение
  • Дополнительные параметры «Не агрегировать»

MCode

 let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Department", type text}, {"Area", type text}, {"Project1_B", type text}, {"Project1_A", type text}, {"Project2_B", type text}, {"Project2_A", type text}, {"Project3_B", type text}, {"Project3_A", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company", "Department", "Area"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "Project_Name"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","B","Before",Replacer.ReplaceValue,{"Attribute.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","A","After",Replacer.ReplaceValue,{"Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"
 

Исходные данные

Результаты