如何在Google Sheets中使用ARRAYFORMULA批量判断单元格值是否处于指定区间？

Hey there! I get why this is tripping you up—let's sort out that formula right away.

The Problem with AND() in ARRAYFORMULA #

The core issue here is that AND() is an aggregate function, not an array-aware function. When you pass ranges like F2:F>0 and F2:F<3 to AND(), it doesn't check each cell individually. Instead, it combines all the results into a single TRUE/FALSE value for the entire range, which is why your condition always returns FALSE.

Two Working Solutions #

You can replace AND() with one of these approaches to get per-cell interval checks:

1. Use Logical Multiplication (*)

In Google Sheets, TRUE is treated as 1 and FALSE as 0. Multiplying two boolean results together acts exactly like an AND check—only when both conditions are TRUE (1*1=1) does it evaluate to TRUE.

Here's your revised formula:

=ARRAYFORMULA(IF(ROW(G:G)=1,"Classe",IF((F2:F>0)*(F2:F<3),"ok",1)))

2. Use Nested IF Functions

If you prefer a more explicit structure, you can nest IF statements to check each condition sequentially:

=ARRAYFORMULA(IF(ROW(G:G)=1,"Classe",IF(F2:F>0,IF(F2:F<3,"ok",1),1)))

Bonus: Handle Empty Cells (Optional) #

If you want to leave empty cells in column F blank in column G instead of returning 1, add an extra check:

=ARRAYFORMULA(IF(ROW(G:G)=1,"Classe",IF(F2:F="","",IF((F2:F>0)*(F2:F<3),"ok",1))))

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