Excel无辅助列按预算与实际值差值降序排序产品

Absolutely, you can sort your products by the variance between budget and actual values directly in column D without adding a helper column. We’ll use a combination of INDEX, MATCH, and LARGE functions to pull off this array-based calculation.

Step-by-Step Formula Implementation #

For cell D2 (the first sorted product), enter this formula:

=INDEX(A$2:A$7,MATCH(LARGE(C$2:C$7-B$2:B$7,ROW(A1)),C$2:C$7-B$2:B$7,0))

Then drag the fill handle down from D2 to D7—this will populate the rest of the sorted product list exactly matching your desired order.

Breakdown of the Formula #

Let’s unpack each part to see how it works:

• C$2:C$7-B$2:B$7: This calculates the variance (Actual - Budget) for each product directly within the formula (no helper column required). In your example, this generates the array {-250, -100, 150, 200, -400, 750}.
• LARGE(..., ROW(A1)): ROW(A1) returns 1 in D2, 2 in D3, and so on as you drag down. This tells LARGE to fetch the 1st largest, 2nd largest, ..., 6th largest variance value from our array.
• MATCH(..., ..., 0): This finds the position of the fetched variance value within our variance array. For example, the largest variance (750) sits at position 6, which maps to Product F.
• INDEX(A$2:A$7, ...): Finally, this returns the product name from column A at the position identified by MATCH.

Adjusting for Budget - Actual Variance #

If you intended to sort by Budget - Actual (prioritizing positive variances first) instead of Actual - Budget, just swap the subtraction order in the formula:

=INDEX(A$2:A$7,MATCH(LARGE(B$2:B$7-C$2:C$7,ROW(A1)),B$2:B$7-C$2:C$7,0))

This would sort your products as Product E → Product A → Product B → Product C → Product D → Product F.

Note for Older Excel Versions #

If you’re using an Excel version before 365/2021, you’ll need to confirm the formula as an array formula by pressing Ctrl+Shift+Enter instead of just Enter. Newer versions handle dynamic arrays automatically.

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