arrays - 如何让 Arrayformula 在自动填充时查找 Arrayformula？

问题描述

因此，我正在开发一个签到系统，其中一个人的位置被签入到一张表（GPS 坐标）中，并且可接受的位置列表在另一张表上。它使用边界并测试以查看人在哪个边界内。现在，该过程适用于每个单元格中的各个方程，但在我添加 Arrayformula 时不会自动填充列。因为我需要它是自动的，所以我需要自动填充位置功能才能工作。

我尝试了一些不同的选项，但它们似乎都锁定了 1 个单元格的值或输出错误，或者不自动填充。我假设这是因为我在不同的页面上使用了 2 个不同的数组，但我不知道如何使用或分离 ArrayFormula 来满足我的目的。

//Using Specific Cell Values//

=ARRAYFORMULA(
    IF(B2="",,IF(TEXTJOIN(", ", 1, 
    IF((E2*1>=Locations!D$2:D)*
       (E2*1<=Locations!E$2:E)* 
       (F2*1>=Locations!F$2:F)*
       (F2*1<=Locations!G$2:G), 
          Locations!C$2:C, ))="", "out of range", TEXTJOIN(", ", 1, 
    IF((E2*1>=Locations!D$2:D)*
       (E2*1<=Locations!E$2:E)* 
       (F2*1>=Locations!F$2:F)*
       (F2*1<=Locations!G$2:G), 
           Locations!C$2:C, ))))) 


//Using a definite range...//
 
=ARRAYFORMULA(
    IF(B2:B100="",,IF(TEXTJOIN(", ", 1, 
    IF((E2:E100*1>=Locations!D$2:D)*
       (E2:E100*1<=Locations!E$2:E)* 
       (F2:F100*1>=Locations!F$2:F)*
       (F2:F100*1<=Locations!G$2:G), 
          Locations!C$2:C, ))="", "out of range", TEXTJOIN(", ", 1, 
    IF((E2:E100*1>=Locations!D$2:D)*
       (E2:E100*1<=Locations!E$2:E)* 
       (F2:F100*1>=Locations!F$2:F)*
       (F2:F100*1<Locations!G$2:G), 
           Locations!C$2:C, )))))


//Using Infinite Range//

=ARRAYFORMULA(
    IF(B2:B="",,IF(TEXTJOIN(", ", 1, 
    IF((E2:E*1>=Locations!D$2:D)*
       (E2:E*1<=Locations!E$2:E)* 
       (F2:F*1>=Locations!F$2:F)*
       (F2:F*1<=Locations!G$2:G), 
          Locations!C$2:C, ))="", "out of range", TEXTJOIN(", ", 1, 
    IF((E2:E*1>=Locations!D$2:D)*
       (E2:E*1<=Locations!E$2:E)* 
       (F2:F*1>=Locations!F$2:F)*
       (F2:F*1<Locations!G$2:G), 
           Locations!C$2:C, )))))

也是一个旁注。据我记得，在我创建“IF”函数之前一切正常.

这是工作表的链接。 https://docs.google.com/spreadsheets/d/1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ/edit?usp=sharing

标签： arraysgoogle-sheetsgoogle-sheets-formulaarray-formulas

解决方案

好吧，我想出了一些似乎可行的方法。它确实使用了一个辅助列，但这可能可以通过更多的尝试来消除。

我在您的工作表中添加了一个选项卡 VHCU-GK。生成结果的两个公式在 N1:O1 中。主要公式如下：

=ARRAYFORMULA(IF(VALUE(E2:E)>0,IFERROR((ARRAYFORMULA(IF(VLOOKUP(ARRAYFORMULA(VLOOKUP(--E2:E,SORT({Locations!D2:D11,Locations!C2:C11}),2,1)),{Locations!C2:C11,Locations!E2:E11},2,0)>--E2:E,TRUE,FALSE)))
 *
 (ARRAYFORMULA(IF(VLOOKUP(ARRAYFORMULA(VLOOKUP(--F2:F,SORT({Locations!F2:F11,Locations!C2:C11}),2,1)),{Locations!C2:C11,Locations!G2:G11},2,0)>--F2:F,TRUE,FALSE))),0),""))

我会尽快清理它，并添加一些解释，但它建立在您采用的矩阵方法之上。它只是根据该行的坐标是否落在定义位置的所有四个边界内来返回 true 或 false (1/0)。然后，第二列仅基于一个边界对满足所有四个边界条件的任何行进行查找。我很确定这些可以结合起来，但我想把这个给你，以展示你的问题的一个可能的数组答案。

让我知道我是否遗漏了什么，或者有什么问题。

编辑：

我忽略了包含构成此答案一部分的第二个公式 - 它位于样本表的单元格 O1 中。

={"Valid
Location";
ARRAYFORMULA(IF($N$2:$N=1,
    ARRAYFORMULA(IFERROR(VLOOKUP(--$E$2:$E,SORT({Locations!$D$2:$D$11,Locations!$C$2:$C$11}),2,1),"out of range")),
    IF($N$2:$N=0,
      "out of range",
      "")))}

对于成功通过标准测试（这是第一个公式）的每个数据行，此公式会进行查找以获取位置的名称。如果位置查找失败，则将其标记为“超出范围”。

arrays - 如何让 Arrayformula 在自动填充时查找 Arrayformula？

问题描述

解决方案

推荐阅读