Excel公式求助：A列按H列最低6值排名出现False和#NUM错误

Let's work through your problem step by step—your current formula is hitting errors because of redundant logic, unhandled text values, and overly nested IF statements. Here's how to fix it:

First, Let's Diagnose the Existing Formula Problems #

Your current A列 formula has a few key issues:

• Redundant checks: You repeat IF(H4=0," ",...) and IF(H4="DQ",...) multiple times, which creates logic gaps and unnecessary complexity.
• #NUM! errors: The SMALL() function breaks when your range includes non-numeric values like "DQ"—it only works with valid numbers, so mixing text and numbers here causes failures.
• False returns: When none of your nested IF conditions match, there's no default fallback value, so Excel returns FALSE instead of a blank.
• No handling for duplicate values: If multiple cells share the same low value, your formula won't correctly assign rankings to all qualifying cells.

Your H列 Formula is Actually Fine #

Quick note: Your H列 formula =IF(G4="DQ","DQ",IF(E4<>0, E4+G4, 0)) works exactly as intended—it correctly returns "DQ" when G4 is "DQ", calculates the sum when E4 isn't 0, and returns 0 otherwise. No changes needed here.

Corrected A列 Formula (Excel 365/2021+) #

This formula uses modern Excel functions to clean up logic, handle text values, and correctly assign rankings to the 6 lowest non-zero, non-"DQ" values:

=IF(H4=0,"",IF(H4="DQ","DQ",LET(
    valid_values, FILTER(H$4:H$34, ISNUMBER(H$4:H$34)*(H$4:H$34>0)),
    value_rank, RANK(H4, valid_values, 1),
    IF(value_rank <= 6, value_rank, "")
)))

How it works: #

1. Basic condition checks: First handles H4=0 (returns blank) and H4="DQ" (returns "DQ"—swap to "" if you want blank instead for these entries).
2. Isolate valid values: Uses FILTER() to pull only numeric values greater than 0 from your H4:H34 range, skipping "DQ" and 0 entirely.
3. Calculate ascending rank: RANK(...,1) assigns a rank where the smallest value gets rank 1, which matches your "lowest 6 values" requirement.
4. Restrict to top 6 ranks: If the value's rank falls between 1-6, it returns the rank; otherwise, it returns a blank cell.

Alternative Formula (Older Excel Versions) #

If you're using an Excel version without LET() or FILTER(), use this array formula (press Ctrl+Shift+Enter after entering it to activate array functionality):

=IF(H4=0,"",IF(H4="DQ","DQ",IF(COUNTIF(H$4:H$34,"<"&H4)+1<=6,COUNTIF(H$4:H$34,"<"&H4)+1,"")))

How it works: #

• COUNTIF(H$4:H$34,"<"&H4)+1 manually calculates the ascending rank by counting how many values are smaller than H4, then adding 1 to get the current value's position.
• It checks if this calculated rank is within 1-6, and returns the rank if true—otherwise, it returns a blank.

Key Tips #

• Use absolute references (H$4:H$34 instead of H4:H34) so the range doesn't shift when you drag the formula down the column.
• Adjust the "DQ" in the formula to "" if you prefer blank cells instead of "DQ" for H列's "DQ" entries.

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