Excel技术问题：提取唯一值对应列数据及统计唯一职位编号数量

Hey there! Let's break down your two Excel questions with straightforward, actionable solutions:

I'll cover two approaches—one for modern Excel versions (365/2021) with dynamic arrays, and another for older versions that don't support them.

动态数组方案（Excel 365/2021） #

• Step 1: Pick a blank column (say, Column C) to list unique values. In cell C1, enter:
  =UNIQUE(A:A)
  This will automatically spill all unique text values from Column A into Column C, no dragging needed.
• Step 2: To get the corresponding values from Column B, go to cell D1 (aligned with the first unique value in C1) and enter:
  =XLOOKUP(C:C, A:A, B:B, "No match")
  Again, this will spill the matching B column values for every unique entry in Column C.

兼容旧版Excel方案 #

• Step 1: Extract unique values in Column C. In C1, enter this array formula (press Ctrl+Shift+Enter instead of just Enter):
  =INDEX(A:A, MATCH(0, COUNTIF(C$1:C1, A:A), 0))
  Drag this formula down until you see #N/A errors, then delete those error rows—you'll have your unique list.
• Step 2: Get matching B column values. In D1, enter:
  =VLOOKUP(C1, A:B, 2, FALSE)
  Drag this formula down to match the length of your unique list in Column C.

There are two common use cases here—let's cover both:

给原数据每行添加对应编号的总数量 #

If you want every row in your dump sheet to show how many times its position ID appears in Column A:

• In cell C1 (your new third column), enter:
  =COUNTIF($A:$A, A1)
• Drag this formula down the entire Column C. Now each row's C cell will display the total count of its position ID across the dataset.

生成独立的唯一编号+数量统计列表 #

If you want a clean, separate list of unique position IDs and their respective counts:

• Step 1: In a blank column (e.g., Column C), enter:
  =UNIQUE(A:A)
  This gives you all unique position IDs.
• Step 2: In Column D (next to the first unique ID), enter:
  =COUNTIF($A:$A, C1)
• Drag this formula down Column D to match the length of your unique list in Column C. You'll now have a neat summary of each ID's total count.

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