Excel技术求助：按问题分类统计客户端ID的出现次数

Hey there! Let’s sort out this problem for you—since you’ve already hunted around for solutions without luck, let’s jump straight into practical, efficient ways to count how often each Client ID pops up per Issue, especially with your 78k-row dataset. Here are three reliable approaches:

Excel handles 78k rows easily, and a pivot table is the fastest way to get your counts:

• First, make sure your data is clean (no random empty rows or garbled entries) and select columns A and B (Issue and Client ID).
• Insert a pivot table:
  1. Go to the Insert tab → click PivotTable. Confirm your data range is correct, then choose where to place the table (a new worksheet works best to avoid cluttering your original data).
  2. In the PivotTable Fields panel:
     • Drag Issue to the Rows area.
     • Drag Client ID to the Rows area (place it directly under Issue to group Client IDs under each issue).
     • Drag Client ID again to the Values area—by default, it’ll count occurrences, but if not, click the value field and select Count.
  3. You’ll instantly see a breakdown of how many times each Client ID appears per Issue. Click the "Occurrences" column header to sort in descending order, so you can spot frequent repeat cases right away.

If you’d rather use a formula (though it might be slower for 78k rows), you can add a new column (say, D) with:
=COUNTIFS(A:A, A2, B:B, B2)
Drag this formula down every row, and it’ll show how many times the current row’s Issue + Client ID combo appears in the dataset.

If Excel feels sluggish with 78k rows, Python’s Pandas library will crunch this data in seconds. Here’s how:
First, install Pandas if you haven’t already (run pip install pandas in your terminal/command prompt). Then use this code:

import pandas as pd

# Load your data—swap "your_data.xlsx" for your file path (use read_csv if it's a CSV)
df = pd.read_excel("your_data.xlsx")

# Group by Issue and Client ID, then count occurrences
client_counts = df.groupby(["Issue", "Client ID"]).size().reset_index(name="Occurrences")

# Sort by occurrence count (descending) to prioritize frequent repeats
sorted_counts = client_counts.sort_values(by="Occurrences", ascending=False)

# Save the results to a new Excel file for your report
sorted_counts.to_excel("issue_client_violation_counts.xlsx", index=False)

# Preview the top results
print(sorted_counts.head())

This will generate a clean, sorted report ready for your analysis.

If your ticket data is stored in a database (like MySQL, SQL Server, or PostgreSQL), run this query to get your counts instantly:

SELECT
    Issue,
    Client_ID,
    COUNT(*) AS Occurrences
FROM ticket_data
GROUP BY Issue, Client_ID
ORDER BY Occurrences DESC;

You can export the query results to a CSV or Excel file to build your violation report.

Any of these methods should give you the exact counts you need to identify repeat violations. Let me know if you need help troubleshooting any step!

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