多列场景下Pivot操作求助：将指定Driver字段转为列的实现方法

I totally get it—pivoting can feel frustrating when you only need to keep specific driver columns and filter out the rest, especially when you’re used to unpivoting. Let’s walk through solutions for common tools you might be using:

Excel Power Query (Best for Spreadsheet Workflows) #

Since you already know your way around unpivoting, this method will feel familiar, and we’ll add a filter to lock in only capex and opex:

1. Import your data into Power Query: Go to the Data tab > Get Data > From Table/Range.
2. Filter the Driver column: Click the dropdown arrow on the Driver header, uncheck "Select All", then check only "capex" and "opex" before clicking OK.
3. Pivot to your desired columns:
   • Select the Driver column, then head to the Transform tab > Pivot Column.
   • In the pivot dialog box:
     • Choose Value as the Values Column.
     • Under Advanced options, pick "Don't aggregate" (this works because each Region+Driver combo has a unique value).
4. Save your changes: Click Close & Load to bring the transformed table back to your Excel sheet—you’ll have exactly the structure you want!

SQL (For Database Queries) #

If you’re working with a database, conditional aggregation is a clean way to pivot only the drivers you care about:

SELECT
  Region,
  MAX(CASE WHEN Driver = 'capex' THEN Value END) AS Capex,
  MAX(CASE WHEN Driver = 'opex' THEN Value END) AS Opex
FROM your_table_name
WHERE Driver IN ('capex', 'opex') -- Filters out all other drivers
GROUP BY Region;

We use MAX() here (you could also use MIN() or SUM()—all work since each Region+Driver pair has one value) to pull the correct value for each driver per region.

Excel Formula Approach (No Power Query Required) #

If you prefer sticking to formulas (great for quick one-off tasks):

1. First, get a unique list of Regions. If you have Excel 365/2021, use =UNIQUE(A:A) (adjust the range to match your data).
2. For the Capex column, use XLOOKUP to match Region + Driver:

   =XLOOKUP($A2&"capex", $A$2:$A$100&$C$2:$C$100, $B$2:$B$100, "")
   

   If you don’t have XLOOKUP, use INDEX/MATCH:

   =INDEX($B$2:$B$100, MATCH($A2&"capex", $A$2:$A$100&$C$2:$C$100, 0))
   

3. Repeat the formula for the Opex column, just replace "capex" with "opex".
4. If needed, filter out any blank rows (though filtering the Driver column first would prevent these).

内容的提问来源于stack exchange，提问作者Otshepeng Ditshego