从区域提取去重排序非空列表：Excel 2016公式失效求助

Hey there, let's work through this issue you're facing with that tricky array formula in Excel 2016. First, let's recap your goal: you want to pull a sorted alphabetical list of unique, non-blank values from a specific range. You found a formula that works perfectly in Google Sheets, but it's failing to correctly extract unique values in your 32-bit Excel 2016 (version 16.0.4738.1000).

The formula you're currently using (for reference) #

=INDEX($B$3:$B$7, SMALL(IF(SMALL(IF(COUNTIF($D$2:D2, $B$3:$B$7)+ISBLANK($B$3:$B$7)=0, COUNTIF($B$3:$B$7, "<"&$B$3:$B$7)+1, ""), 1)=IF(ISBLANK($B$3:$B$7), "", COUNTIF($B$3:$B$7, "<"&$B$3:$B$7)+1), ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1), 1), MATCH(MIN(IF(COUNTIF($D$2:D2, $B$3:$B$7)+ISBLANK($B$3:$B$7)>0, "", COUNTIF($B$3:$B$7, "<"&$B$3:$B$7)+1)), INDEX(IF(ISBLANK($B$3:$B$7), "", COUNTIF($B$3:$B$7, "<"&$B$3:$B$7)+1), SMALL(IF(SMALL(IF(COUNTIF($D$2:D2, $B$3:$B$7)+ISBLANK($B$3:$B$7)=0, COUNTIF($B$3:$B$7, "<"&$B$3:$B$7)+1, ""), 1)=IF(ISBLANK($B$3:$B$7), "", COUNTIF($B$3:$B$7, "<"&$B$3:$B$7)+1), ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1), 1), , 1), 0), 1)

Why this isn't working in Excel 2016 #

That formula is extremely nested and was built for even older Excel versions. The issues are two key points:

1. Excel 2016 requires old-style array formulas to be confirmed with Ctrl+Shift+Enter (unlike Google Sheets, which automatically handles array calculations). If you didn't use this shortcut, the formula won't compute correctly.
2. The deep layers of IF, SMALL, and INDEX functions can confuse Excel 2016's calculation engine, causing it to misinterpret the unique value checks.

Reliable fixes for Excel 2016 #

Let's use simpler, more compatible approaches that get the job done without the complexity.

Option 1: Simplified Array Formula (Ctrl+Shift+Enter required)

Assuming your data is in B3:B7 and you want results starting in D3:

=IFERROR(INDEX($B$3:$B$7, MATCH(0, COUNTIF($D$2:D2, $B$3:$B$7)+ISBLANK($B$3:$B$7)+IF(COUNTIF($B$3:$B$7, "<"&$B$3:$B$7)<MIN(IF(COUNTIF($D$2:D2, $B$3:$B$7)+ISBLANK($B$3:$B$7)=0, COUNTIF($B$3:$B$7, "<"&$B$3:$B$7))),1,0), 0)), "")

• Enter this in D3, then press Ctrl+Shift+Enter (you'll see curly braces {} wrap the formula if done correctly)
• Drag the fill handle down until you get empty cells (this means all unique values have been extracted)

Option 2: Easier-to-Maintain Approach with a Helper Column

If array formulas feel too finicky, use a helper column to break down the logic:

1. In cell C3, enter this formula and drag down to C7:

   =IF(B3="","",COUNTIF($B$3:$B$7,"<"&B3)+1)
   

   This assigns an alphabetical "rank" to each non-blank value.
2. In D3, enter this array formula (again, use Ctrl+Shift+Enter) and drag down:

   =IFERROR(INDEX($B$3:$B$7, MATCH(SMALL(IF(COUNTIF($D$2:D2, $B$3:$B$7)+ISBLANK($B$3:$B$7)=0, $C$3:$C$7),1), $C$3:$C$7,0)), "")
   

   This pulls the next lowest-ranked unique value each time you drag it down.

How these solutions work #

Both methods:

• Skip blank values using ISBLANK
• Track already extracted values with COUNTIF($D$2:D2, ...) to ensure uniqueness
• Use alphabetical ranking via COUNTIF($B$3:$B$7,"<"&B3) to sort results
• Handle errors with IFERROR to stop showing #N/A once all values are extracted

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