如何在含文本与数字的列中运用IF函数实现特定SLA判定逻辑

Hey there! Let's break down why your current formula isn't working and get you a correct solution that matches your rules perfectly.

What Was Wrong With Your Original Formula? #

Your formula had two key issues that threw off the results:

• Incorrect cell referencing: Writing M2="Days" checks if the text in cell M2 is exactly "Days", not if the numeric value in your Days column is greater than 0. You need to reference the cell holding the actual number for Days (not the column header).
• Wrong logical operator: Using & to combine conditions doesn’t work for "and" logic in Excel—it’s meant for text concatenation. You need the AND() function to link multiple required conditions together.

The Correct Formula #

First, let’s assume your data is structured with these columns (swap the cell references to match your actual spreadsheet):

• Days value: A2
• Hours value: B2
• Minutes value: C2

Since any of your three rules being true means "大于SLA", we can use the OR() function to simplify the formula (it checks if any condition is met):

=IF(OR(A2>0, AND(B2=1, C2>0), B2>1), "大于SLA", "小于等于SLA")

How This Works #

Let’s break down the logic step by step:

• OR() wraps all three of your rules, so if any rule is true, it returns "大于SLA":
  1. A2>0: Checks if Days is greater than 0
  2. AND(B2=1, C2>0): Checks if Hours equals 1 and Minutes is greater than 0 (uses AND() to ensure both conditions are satisfied)
  3. B2>1: Checks if Hours is greater than 1
• If none of the rules are true, it defaults to "小于等于SLA"

Quick Adjustment Tip #

If your Days, Hours, and Minutes values live in different columns (e.g., Days in D2, Hours in E2, Minutes in F2), just replace the cell references in the formula to match your actual column positions.

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