如何通过公式筛选总问题数Top5公司的Top3问题？

Hey there! Let’s work through this problem step by step. I’ll focus on Excel (including 365’s dynamic functions) and Google Sheets since those are the most common tools for this kind of table analysis.

Core Approach #

First, we need to:

1. Identify the top 5 companies with the highest total problem counts (you already specified Company 4, 3, 6, 7, 1, but let’s build formulas to automate this for future use).
2. Calculate the total occurrence of each issue type across these 5 companies.
3. Extract the top 3 issues from that aggregated count.

Excel Implementation #

Step 1: Calculate Total Problems per Company #

Add an auxiliary column (e.g., column Z) to compute the total problems for each company. In cell Z2, use:

=SUM(B2:Y2)

Drag this formula down to apply it to all rows in your table.

Step 2: Get Top 5 Companies #

If you’re using Excel 365 (with dynamic arrays), use this one-liner to get the top 5 companies sorted by total problems:

=TAKE(SORT(HSTACK(A2:A100, Z2:Z100), 2, -1), 5, 1)

• HSTACK combines company names and their total problem counts into a single range.
• SORT orders the range by the total count (column 2) in descending order (-1).
• TAKE grabs the first 5 rows and only the company name column (column 1).

For older Excel versions, use this formula in a cell and drag down 5 times:

=INDEX(A:A, MATCH(LARGE(Z:Z, ROW(A1)), Z:Z, 0))

Step 3: Aggregate Issue Counts for Top 5 Companies #

For Excel 365, use BYCOL to calculate total occurrences of each issue across the top 5 companies:

=BYCOL(B2:Y100, LAMBDA(col, SUMIF(A2:A100, @$AA$2:$AA$6, col)))

• Replace $AA$2:$AA$6 with the range where your top 5 companies are listed.
• This will spill a row of totals, one for each issue type.

For older Excel, use this formula in the cell below your first issue (e.g., B100) and drag across all issue columns:

=SUMPRODUCT((A:A=$AA$2)+(A:A=$AA$3)+(A:A=$AA$4)+(A:A=$AA$5)+(A:A=$AA$6), B:B)

Step 4: Extract Top 3 Issues #

For Excel 365, use another dynamic array formula:

=TAKE(SORT(HSTACK(B1:Y1, $B$100:$Y$100), 2, -1), 3, 1)

• This combines issue names with their aggregated counts, sorts descending, and takes the top 3 issue names.

For older Excel, drag this formula down 3 times:

=INDEX($B$1:$Y$1, MATCH(LARGE($B$100:$Y$100, ROW(A1)), $B$100:$Y$100, 0))

Google Sheets Implementation #

Step 1: Get Top 5 Companies #

Use QUERY to fetch the top 5 companies in one go:

=QUERY(A:Z, "SELECT A WHERE Z IS NOT NULL ORDER BY Z DESC LIMIT 5", 1)

• Replace Z with your total problems column.
• The 1 keeps the header row; remove it if you don’t need headers.

Step 2: Aggregate Issue Counts & Extract Top 3 #

First, convert your wide table to a long format (easier for aggregation), then use QUERY to sum counts and pull the top 3 issues:

=QUERY(
  {FLATTEN(A2:A100&"|"&B1:Y1), FLATTEN(B2:Y100)},
  "SELECT SPLIT(Col1, '|')[1], SUM(Col2) 
   WHERE SPLIT(Col1, '|')[0] MATCHES '"&TEXTJOIN("|", TRUE, $AA$2:$AA$6)&"' 
   GROUP BY SPLIT(Col1, '|')[1] 
   ORDER BY SUM(Col2) DESC 
   LIMIT 3",
  0
)

• FLATTEN converts your wide columns into a single list of "Company|Issue" pairs and their counts.
• SPLIT separates the company and issue names.
• TEXTJOIN combines your top 5 companies into a regex pattern for matching.

Quick Notes #

• If multiple issues have the same total count, the formulas will return the first occurrence. To handle ties, adjust the sort logic (e.g., add issue name as a secondary sort key).
• Make sure to tweak all cell ranges (like A2:A100, B2:Y100) to match your actual table dimensions.

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