如何编写满足指定单元格判断规则的MS Excel公式

Let's break down your requirements and build the right formula for cell D2:

The Requirements Recap #

• If C2 is empty → D2 shows 0
• If B2 and C2 have identical content → D2 shows 1
• If B2 and C2 have different content (and C2 isn't empty) → D2 shows -0.33

Solution 1: Using IFS (Excel 2019/365+) #

This is the cleanest approach for newer Excel versions, since IFS lets you list conditions in order without messy nested IFs:

=IFS(C2="", 0, B2=C2, 1, TRUE, -0.33)

How it works:

1. First checks if C2 is blank → returns 0 if true
2. Next checks if B2 matches C2 → returns 1 if true
3. The final TRUE acts as a "catch-all" for all remaining cases (B2≠C2 and C2 isn't empty) → returns -0.33

Solution 2: Nested IF (Compatible with All Excel Versions) #

If you're using an older Excel version that doesn't support IFS, use a nested IF instead—it works reliably across all versions:

=IF(C2="", 0, IF(B2=C2, 1, -0.33))

How it works:

1. The outer IF first checks if C2 is blank → returns 0 immediately if that's true
2. If C2 isn't blank, the inner IF checks if B2 equals C2 → returns 1 if they match, -0.33 if they don't

Bonus: Case-Sensitive Comparison #

By default, Excel's = operator ignores case (e.g., "Apple" and "apple" are considered equal). If you need strict case-sensitive matching, replace B2=C2 with the EXACT function:

// For IFS version
=IFS(C2="", 0, EXACT(B2,C2), 1, TRUE, -0.33)

// For nested IF version
=IF(C2="", 0, IF(EXACT(B2,C2), 1, -0.33))

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