基于条件的相邻列众数查找公式实现求助

Alright, let's tackle this conditional mode problem you're facing. The INDEX/MODE/MATCH combo can be finicky here because MODE doesn’t natively handle filtered results—let’s walk through reliable solutions tailored to your Excel version.

Solution 1: Excel 365 / Excel 2021 (Dynamic Array Support) #

If you have access to dynamic arrays, this is the cleanest approach using FILTER + MODE.SNGL:

=MODE.SNGL(FILTER(B:B, A:A = D1))

How it works: #

• FILTER(B:B, A:A = D1): This pulls all values from column B where the corresponding value in column A matches your criteria (stored in cell D1, e.g., "BLUE").
• MODE.SNGL: Computes the most frequent value from the filtered list. If there are multiple values with the same highest frequency, it returns the first one in the list.

Bonus: Return all modes (if multiple exist)

If you want to see every value that’s a mode, use MODE.MULT instead—it will spill all results into adjacent cells below:

=MODE.MULT(FILTER(B:B, A:A = D1))

Solution 2: Older Excel Versions (No Dynamic Arrays) #

For versions without FILTER, you’ll need an array formula that combines INDEX, MODE, IF, and MATCH. Here’s the fix for your earlier failed attempt:

=IFERROR(INDEX(B:B, MODE(IF(A:A = D1, MATCH(B:B, B:B, 0)))), "No match")

Critical note for older Excel: #

You must enter this as an array formula by pressing Ctrl + Shift + Enter instead of just Enter. Excel will wrap the formula in curly braces {} automatically (don’t type them manually).

How it works: #

• IF(A:A = D1, MATCH(B:B, B:B, 0)): This returns the position of each value in column B (within column B) only if the corresponding column A value matches your criteria. Non-matching rows return FALSE.
• MODE: Finds the most frequent position number from the filtered list—this corresponds to the most common value in column B for your criteria.
• INDEX(B:B, ...): Pulls the actual value from column B using that frequent position.
• IFERROR: Adds a fallback message if there are no matching rows (avoids #N/A errors).

Pro Tips to Avoid Issues #

• Limit your range references: Instead of using entire columns (A:A, B:B), use your actual data range (e.g., A1:A100, B1:B100) to boost performance.
• Check data types: Ensure your criteria (e.g., "BLUE") and column A values are the same data type (all text or all numbers)—mixed types can break the match.
• Handle ties: If multiple values have the same highest frequency, MODE.SNGL and the array formula will return the first occurrence of the mode.

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