求满足特定条件的Excel IF公式，用于扑克训练表格行高亮

Let's break down how to get that single --> marker working correctly to highlight the next row that needs your answer. First, let's clarify the core rules we need to enforce:

• Only one row shows --> at a time
• The arrow appears on the first unanswered row, but only if the row above it has been answered (with "Yes" or "No")
• The first question row should show the arrow initially (since there's no prior answer row to check)

First, Let's Fix Your Formula Errors #

Your second formula had a couple of syntax and logic issues:

1. The COUNTIF condition had broken quoting ("<>%"-->" is invalid Excel syntax)
2. You were checking for existing arrows below the current row instead of ensuring no arrows exist anywhere in the column
3. You only checked for "Yes" instead of accounting for both "Yes" or "No" (or any valid filled answer)

Correct Formulas for Each Row #

Let's assume your setup is:

• Questions start at row 3, go down to row 49
• Column N is where you enter "Yes"/"No" answers
• Column G is where the --> marker appears

For the first question row (G3):

This is your initial starting point. We show the arrow if the row is unanswered and no other marker exists in the column:

=IF(AND(N3="", COUNTIF($G$3:$G$49, "-->")=0), "-->", "")

(We skip checking N2 here since it's likely a header row, empty by default)

For all subsequent rows (G4 to G49):

For these rows, the arrow should only appear if three conditions are met:

1. The current row's answer cell (e.g., N4 for G4) is empty
2. The row above's answer cell (e.g., N3 for G4) is filled (with "Yes" or "No")
3. No other row in column G already has the arrow

Here's the formula for G4 (adjust row numbers for each subsequent row):

=IF(AND(N4="", N3<>"", COUNTIF($G$3:$G$49, "-->")=0), "-->", "")

Optional: Strict Answer Validation #

If you want to ensure the above row's answer is only "Yes" or "No" (not random text), modify the condition to explicitly check valid answers:

=IF(AND(N4="", OR(N3="Yes", N3="No"), COUNTIF($G$3:$G$49, "-->")=0), "-->", "")

How This Works #

• The absolute reference $G$3:$G$49 ensures we scan the entire marker column, so only one row can ever display --> at a time
• Checking Nk-1<>"" (or the strict OR condition) guarantees we only move the arrow to the next row once the previous question is completed
• Checking Nk="" ensures we never mark a row that's already been answered

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