Excel 2013：如何用可下拉填充的公式提取列向量唯一值

Hey there! Since Excel 2013 doesn’t include the handy modern UNIQUE() function, we’ll use a reliable combination of older functions that works perfectly for your scenario—and I’ll break down every part so you understand exactly what’s happening.

The Formula to Use #

Assuming your source data is in A1:A7 and you want the unique values in column B, enter this formula in B1:

=IFERROR(INDEX($A$1:$A$7, MATCH(0, COUNTIF($B$1:B1, $A$1:$A$7), 0)), "")

After typing the formula, press Ctrl+Shift+Enter (this is critical for array formulas in Excel 2013—don’t just press Enter!). Then you can drag the fill handle down column B to populate all unique values.

What This Does with Your Sample Data #

For your example values 981、981、19018、8313、8842、8842、8314:

• B1 will return 981 (the first unique value)
• B2 will return 19018 (the next value that hasn’t appeared in B1 yet)
• B3 will return 8313
• B4 will return 8842
• B5 will return 8314
• Any cells below B5 will show a blank (thanks to IFERROR) instead of an error.

Breaking Down the Formula (So You Understand It) #

Let’s unpack each part step by step:

1. COUNTIF($B$1:B1, $A$1:$A$7): This counts how many times each value in A1:A7 has already appeared in the cells above (and including) the current cell in column B. For B1, this checks against an empty range, so all values return 0. For B2, it checks against B1, so the first two 981s return 1, and all others return 0.
2. MATCH(0, ..., 0): This finds the position of the first value in A1:A7 that hasn’t been counted yet (i.e., the first 0 from the COUNTIF result).
3. INDEX($A$1:$A$7, ...): This pulls the value from A1:A7 at the position found by MATCH.
4. IFERROR(..., ""): This replaces any #N/A errors (which happen when all unique values are extracted) with a blank cell for cleaner output.

Key Notes for Success #

• Use absolute references ($A$1:$A$7) for your source data so the range doesn’t shift when you drag the formula down.
• Use the mixed reference ($B$1:B1) so the formula expands the checked range as you drag down (it will check B1, then B1:B2, then B1:B3, etc.).
• Always confirm array formulas with Ctrl+Shift+Enter in Excel 2013—this tells Excel to process the formula across the entire range, not just a single cell.

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