如何统计列A每个值对应的列B唯一值数量？

Hey there! Let's figure out how to count the number of unique values in Column B for each distinct value in Column A. I’ll walk you through solutions using three common tools—Excel, Python (Pandas), and SQL—so you can choose what works best for your setup.

First, let’s use a sample dataset to make things concrete:

输入数据：

A	B
X	1
X	2
X	1
Y	3
Y	3
Z	4

预期输出：

A	Unique_B_Count
X	2
Y	1
Z	1

选项1：数据透视表（最简单） #

This is the most straightforward way if you’re working with a spreadsheet:

• Select your entire dataset (including headers).
• Go to the Insert tab and click PivotTable. Choose where you want to place the pivot table (new sheet or existing sheet).
• In the PivotTable Fields pane:
  • Drag Column A to the Rows area.
  • Drag Column B to the Values area.
• Click the dropdown arrow on the value field (it’ll say "Count of B" by default) → select Value Field Settings.
• Under "Summarize value field by", choose Count Distinct and click OK.

You’ll get a clean table showing each value in A and the number of unique B values linked to it.

选项2：公式（Excel 365/2021+） #

If you prefer using formulas to get results directly next to your data:

1. First, extract unique values from Column A using UNIQUE(A:A) (put this in, say, cell C2).
2. In cell D2, use this formula to count unique B values for each A:

   =COUNT(UNIQUE(FILTER(B:B, A:A=C2)))
   

3. Drag the formula down to apply it to all unique A values.

Note: If you’re using an older Excel version without UNIQUE or FILTER, you can use this array formula (enter with Ctrl+Shift+Enter):

=SUMPRODUCT(($A$2:$A$7=C2)/COUNTIFS($A$2:$A$7,$A$2:$A$7,$B$2:$B$7,$B$2:$B$7))

If you’re working with data in Python, Pandas makes this task super simple:

import pandas as pd

# Load your data (replace with your file path or data source)
df = pd.read_csv("your_data.csv")
# Or if you have a DataFrame already, skip the above line

# Group by Column A and count unique values in Column B
unique_counts = df.groupby("A")["B"].nunique().reset_index()

# Rename the column for clarity
unique_counts.columns = ["A", "Unique_B_Count"]

# Print or export the result
print(unique_counts)
# unique_counts.to_csv("result.csv", index=False)

Let me break this down:

• groupby("A") groups the DataFrame by each unique value in Column A.
• ["B"].nunique() calculates the number of unique values in Column B for each group.
• reset_index() turns the grouped index back into a regular column for easier reading.

If your data is stored in a database, you can use a simple SQL query to get the result:

SELECT 
    A, 
    COUNT(DISTINCT B) AS Unique_B_Count
FROM 
    your_table_name
GROUP BY 
    A;

This query groups the table by Column A, then uses COUNT(DISTINCT B) to count only unique entries in Column B for each group. The AS keyword renames the result column for better readability.

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