如何编写带过滤条件的列唯一值计数函数？含具体场景问询

Great question! Counting unique values that meet specific filter criteria is a super common task, and the approach depends on the tool you’re using. Let’s break down solutions for two of the most widely used tools: Excel and Python with Pandas.

Excel Solution #

For Modern Excel (365/2021+) #

If you have access to dynamic array functions, this is the cleanest way to get your count of unique Name entries where GroupID = 341:

=COUNTA(UNIQUE(FILTER(A:A, B:B=341)))

Let’s break this down:

• FILTER(A:A, B:B=341): Pulls all values from the Name column (assuming Name is in column A) where the corresponding GroupID (column B) equals 341.
• UNIQUE(...): Removes duplicate names from the filtered list.
• COUNTA(...): Counts the number of non-blank entries in the unique list (since UNIQUE returns an array of unique values).

Pro tip: Replace A:A and B:B with specific ranges (like A2:A1000) to avoid including your header row or empty cells at the bottom of the sheet.

For Older Excel Versions (No Dynamic Arrays) #

If you’re stuck with an older Excel version, use this array formula (enter it with Ctrl+Shift+Enter instead of just Enter):

=SUM(IF(FREQUENCY(IF(B2:B100=341, MATCH(A2:A100, A2:A100, 0)), ROW(A2:A100)-ROW(A2)+1), 1))

How this works:

• The inner IF filters rows where GroupID = 341, then uses MATCH to find the first occurrence of each name.
• FREQUENCY counts how many times each first-occurrence position appears.
• Finally, SUM adds up all the 1s (each representing a unique name in the filtered group).

Python (Pandas) Solution #

If you’re working with data in Python using Pandas, this task is straightforward. Assume you have a DataFrame named df with columns Name and GroupID:

Direct Filter & Count #

# Filter rows where GroupID is 341, then count unique Names
unique_name_count = df[df['GroupID'] == 341]['Name'].nunique()
print(unique_name_count)

Grouped Count (For All GroupIDs) #

If you want to get unique name counts for every GroupID at once (and then grab the value for 341):

# Get unique name counts per GroupID
grouped_unique_counts = df.groupby('GroupID')['Name'].nunique()

# Access the count for GroupID 341
print(grouped_unique_counts[341])

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