excel - 超出嵌套 IF 和公式的可接受范围不起作用

Question

要检查的值

 |       | Northing | Easting |
 |-------|----------|---------|
 | Inst1 | 41345    | 33467.8 |
 | inst2 | 41600.5  | 33607.2 |
 | Inst3 | 41900.8  | 33740.2 |
 | Inst4 | 41933.4  | 33780   |
 | Inst5 | 41829.5  | 33694.6 |
 | Inst6 | 41449.9  | 33539   |

坐标范围

 |    | Northing |          | Easting |          |
 |----|----------|----------|---------|----------|
 | T1 | 41158.68 | 41396.88 | 33357.6 | 33517.57 |
 | T2 | 41307.9  | 41456.6  | 33384.2 | 33580.5  |
 | T3 | 41372.1  | 41517.5  | 33411.3 | 33607.5  |
 | T4 | 41431.6  | 41572.7  | 33435.8 | 33632.5  |
 | T5 | 41482.9  | 41654.6  | 33472.3 | 33654.2  |
 | S1 | 41564.9  | 41701.2  | 33493.1 | 33688.7  |
 | S2 | 41611.5  | 41762.3  | 33520.2 | 33708.3  |
 | S3 | 41672.7  | 41841.6  | 33555.5 | 33734.1  |
 | S4 | 41752.2  | 41897.9  | 33580.6 | 33767.6  |
 | S5 | 41809.3  | 41941.7  | 33600.1 | 33791.7  |
 | S6 | 41854.6  | 41998.7  | 33625.4 | 33810.7  |
 | T6 | 41914.8  | 42055.4  | 33650.7 | 33836.1  |
 | T7 | 41971.5  | 42137.4  | 33687.2 | 33859.9  |

嵌套 IF 未显示正确的值，不能超出第 48 行。

如何包含范围 M41:Q53？

当前公式如下

=IF(N41<=$H$41<=O41 & P41<=$I$41<=Q41,M41,IF(N42<=$H$41<=O42 & 
P42<=$I$41<=Q42,M42,IF(N43<=$H$41<=O43 & 
P43<=$I$41<=Q43,M43,IF(N44<=$H$41<=O44 & 
P44<=$I$41<=Q44,M44,IF(N45<=$H$41<=O45 & 
P45<=$I$41<=Q45,M45,IF(N46<=$H$41<=O46 & 
P46<=$I$41<=Q46,M46,IF(N47<=$H$41<=O47 & 
P47<=$I$41<=Q47,M47,IF(N48<=$H$41<=O48 & P48<=$I$41<=Q48,M48,"Not 
here"))))))))

Answer 1

在比较坐标时，坐标系的选择并没有太大的改变逻辑。 :-)

始终检查一个点是否在多边形内可能很棘手(但并非不可能)，但是像这样的普通 ol' 矩形很简单。如果您打算比较多个坐标，那么 nested If's就是行不通。 (事实上​​，应该始终避免使用它们!)

对于我的 quick-n-dirty 示例，我将您的数据放入 Columns vs Rows 中，而不是并排放置。



公式为H6是:

=IF(AND(H$3>=MIN($C6:$D6),H$3<=MAX($C6:$D6),H$4>=MIN($E6:$F6),H$4<=MAX($E6:$F6)),"Match","-")

基本上它只是检查:

是 Northing To Match 大于或等于 到`NorthingStart & NorthingEnd 的最小值？

是 Northing To Match 小于或等于 `NorthingStart 和 NorthingEnd 的最大值？

如果 是 到这两个点Northing to Match 是 在指定的矩形内。

还有许多其他方法也可以解决这个问题。哪一个是正确的主要取决于您要比较的数据量，以及它是否是持续的需求(是否需要考虑不可预见的情况)......

同样的事情也可以在 的帮助下使用并行数据集来完成。数组公式 .

延伸阅读:

Introduction to Coordinate Geometry (许多方便的链接)

SE:解析几何:How to check if a point is inside a rectangle?

维基百科:Intersection_theory

How to check if two given line segments intersect?

还有一个切线(没有双关语)，但是因为我提到它并且只是为了好玩，所以简要解释一下:

如何检查给定点是否位于多边形内部或外部？

1. Draw a horizontal line to the right of each point and extend it to infinity

2. Count the number of times the line intersects with polygon edges.

3. A point is inside the polygon if either count of intersections is odd or
   point lies on an edge of polygon.  If none of the conditions is true, then 
   point lies outside.

How to handle point g in the above figure?

Note that we should returns true if the point lies on the line or same as one of the vertices of the given polygon. To handle this, after checking if the line from p to extreme intersects, we check whether p is colinear with vertices of current line of polygon. If it is colinear, then we check if the point p lies on current side of polygon, if it lies, we return true, else false. _(Source)