如何用DMAX函数查找列表次高值及分性别取多阶最高值的问题解决

Hey there! Let's work through your DMAX-related questions step by step—they're common pitfalls, so you're not alone here.

1. How to Use DMAX to Find the Second Highest Value in a List #

To pull the second highest value with DMAX, you need to set up a condition range that filters out the highest value, then find the max of the remaining values. Here's how:

• First, define your data range (e.g., A1:B10, where column B holds your numeric values) and make sure it has a clear column header (like "Value" in cell B1).
• Create a condition range:
  • In a blank cell (say, D1), type the exact column header from your data ("Value").
  • In D2, enter this formula to generate a "less than highest value" condition:

    ="<"&MAX(B2:B10)
    

  This combines the "<" operator with the highest value from your list, creating a text condition like "<151".
• Finally, use the DMAX function:

  =DMAX(A1:B10, "Value", D1:D2)
  

  This will return the highest value that's smaller than the overall maximum—aka your second highest value.

2. Fixing the 0 Return Issue When Using a Formula as a Condition in DMAX #

I’ve run into this exact problem before! The root cause is how Excel interprets your condition cell: if you just reference the highest value formula directly (e.g., =<G1), Excel converts it to a boolean TRUE/FALSE instead of keeping it as a text-based comparison condition. Here's the fix:

Step 1: Get the Gender-Specific Highest Value #

First, confirm your working highest value formula (e.g., for females):

=DMAX(A1:B10, "Value", F1:F2)

Where F1 is "Gender" and F2 is "F". Let's say this is stored in cell G1.

Step 2: Build a Proper Text Condition for Second/Third Highest #

For multi-condition filtering (e.g., female + less than highest value), your condition range needs to include both criteria in adjacent columns (since DMAX treats same-row conditions as "AND"):

• In cells I1:J2, set up your condition range:
  • I1: "Gender" | J1: "Value"
  • I2: "F" | J2: ="<"&G1
    The ="<"&G1 formula is critical—it creates a text string like "<151" instead of calculating a boolean value, which DMAX can properly interpret.

Step 3: Use DMAX for Second/Third Highest #

• For female second highest:

  =DMAX(A1:B10, "Value", I1:J2)
  

• For female third highest, update the condition to exclude the first two values:
  • Change J2 to ="<"&DMAX(A1:B10, "Value", I1:J2) (reference the second highest value) or use ="<"&LARGE(IF(A2:A10="F", B2:B10),2) (array formula, press Ctrl+Shift+Enter in older Excel versions).

Quick Checks to Avoid Future Issues #

• Ensure your condition range headers exactly match your data range headers (case doesn't matter, but spelling does!).
• Double-check the condition cell's format is set to "General" or "Text"—numeric format can mangle text-based conditions.

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