Afaik, using POWER QUERY will be the easiest approach. To perform the task using POWER QUERY, follow the steps:
- 首先将源范围转换为表并相应命名，在本例中我将其命名为
Table1
- Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
- The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Omschrijving"}, "Attribute", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Attribute", "Omschrijving", "Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Attribute", "Some Header"}})
in
#"Renamed Columns"
- Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result. (Refer .Gif below)
See the .Gif --> I have used a shortcut to open the POWER QUERY window --> CTRL+A (to select the data) --> then hit ALT+A+PT and press ok.
An alternative approach, using New MS365 Excel Functions viz. TAKE( ), DROP( ), TOCOL( ), HSTACK( ), SEQUENCE( ), FILTER( )
• Formula used in cell L2
=LET(
a,A1:I10,
b,TAKE(DROP(a,1),,1),
c,DROP(TAKE(a,1),,1),
d,DROP(a,1,1),
e,TOCOL(IFNA(b,SEQUENCE(,COLUMNS(d)))),
f,TOCOL(IFNA(c,SEQUENCE(ROWS(d)))),
g,TOCOL(d),
h,HSTACK(f,e,g),
FILTER(h,INDEX(h,,3)<>0))
编辑 : 7/21/2023
A more simple & sleek approach using HSTACK( ) & TOCOL( )
• Formula used in cell A12
=HSTACK(
TOCOL(IF(B2:I10="",NA(),B1:I1),3),
TOCOL(IF(B2:I10="",NA(),A2:A10),3),
TOCOL(B2:I10,1)
)
