Excel含空值公式单元格的唯一值列表生成公式修改求助

Let's break down why your current formula starts the unique list at B3 instead of B2, and how to adjust it to work properly with empty cells in column A.

The Root Problem #

Your original formula doesn't exclude empty cells (created by ="") from its calculations. Empty text ("") is treated as the smallest possible value in text comparisons, so the formula first tries to match these empty cells in column A. This leads to an empty result in B2, and only moves to actual product names when it gets to B3.

Modified Formula (Works with Empty Cells) #

Replace your existing formula in B2:B10 with this array formula (press Ctrl+Shift+Enter after entering if you're on Excel 2019 or earlier; for Excel 365/2021, regular Enter works thanks to dynamic array support):

=IFERROR(INDEX($A$2:$A$10,MATCH(TRUE,COUNTIF(B$1:B1,$A$2:$A$10)=0,$A$2:$A$10<>"",0)),"")

How This Fix Works #

Here's the breakdown of key changes:

1. $A$2:$A$10<>"": This adds a critical condition to skip empty cells in column A entirely. We only want to process cells that contain actual product names.
2. MATCH(TRUE, ... ,0): Instead of matching a numeric sum, we’re looking for the first cell that meets two criteria:
   • The value hasn’t already been added to the unique list (checked via COUNTIF(B$1:B1,$A$2:$A$10)=0).
   • The cell isn’t empty ($A$2:$A$10<>").

Simplified Alternative for Modern Excel #

If you’re using Excel 365 or 2021, you can ditch the complex array formula entirely with built-in dynamic array functions. This will automatically ignore empty cells and spill the unique list down:

=UNIQUE(FILTER($A$2:$A$10,$A$2:$A$10<>""))

Just enter this in B2—no need to drag the formula down manually.

Expected Result #

After applying either formula, your spreadsheet will look like this:

The unique list now starts correctly at B2, ignoring the empty cells in column A.

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