Question

< 坚固 > 解决了第一个问题,但另一个小问题 < /坚固 >

下面的代码固定了我的错误, 但我需要它返回“ 全部选择 ” 如果下载设定为100%, 并且是一个学生的名字。

=IF(B31 = "All", IF(D31 = Master Data !AC1, FILTER({ Master Data !AC2:AC, Master Data !AF2:AF, Master Data !AH2:AH, Master Data !AI2:AI}, Master Data !AC2:AC <> "" ), IF(ISNUMBER(MATCH(D31, Master Data !AC2:AC, 0)), FILTER({ Master Data !AC2:AC, Master Data !AF2:AF, Master Data !AH2:AH, Master Data !AI2:AI}, Master Data !AC2:AC = D31 ), "Select a student name" ) ), IF(B31 = "<100%", FILTER({ Master Data !AC2:AC, Master Data !AF2:AF, Master Data !AH2:AH, Master Data !AI2:AI}, ( Master Data !AI2:AI < 1) * (NOT(ISBLANK( Master Data !AC2:AC))) ), "Invalid selection for B31" ) )

我在 Google 工作表中创建一个仪表板。我有一个用于过滤列表的下列公式。除了。除了。 B31 = “ lt; 100% ” 以外, 一切工作都正常。当 B31 = “ lt; 100% ” 和 D31 = master Data 时。 AC1 只有当整个页面下的任何行中没有任何内容时, 它才会正确返回。我需要能够随着数据的变化而将结尾保持开放, 并且我需要能够添加这些新数据行, 而不必更改代码。本代码的每个其它部分都工作良好, 并返回列表, 即使下面的行里有东西, 只要它不在返回的值范围内。我尝试了如此多的工作间隔, 并且是这里的总的零点。帮助我理解为什么代码中只有这部分返回了这个错误, 但代码的其余部分不会受到下面有数据的行的影响。这可以被固定吗?

对不起,我不能分享表因为它有学生信息。

=IF(
    B31 = "All",
    IF(
        D31 =  Master Data !AC1,
        FILTER(
            { Master Data !AC2:AC,  Master Data !AF2:AF,  Master Data !AH2:AH,  Master Data !AI2:AI},
             Master Data !AC2:AC <> ""
        ),
        IF(
            ISNUMBER(MATCH(D31,  Master Data !AC2:AC, 0)),
            FILTER(
                { Master Data !AC2:AC,  Master Data !AF2:AF,  Master Data !AH2:AH,  Master Data !AI2:AI},
                 Master Data !AC2:AC = D31
            ),
            "Select a student name"
        )
    ),
    IF(
        B31 = "<100%",
        IF(
            D31 =  Master Data !AC1,
            FILTER(
                { Master Data !AC2:AC,  Master Data !AF2:AF,  Master Data !AH2:AH,  Master Data !AI2:AI},
                 Master Data !AI2:AI < 1
            ),
            "Select  All  to filter by student name"
        ),
        "Invalid selection for B31"
    )
)

我尝试了多种方法来用相同的结果来修正代码并测试代码的该部分, 使用镜头和助手行帮助和显示不匹配的行。即使是用于过滤 & lt; 100% 的最基本版本也造成了同样的问题。

< a href=\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Answer 1

尝试 :

=LET(r, CHOOSECOLS( Master Data !AC2:AI,1,4,6,7), n, INDEX(r,,1), p, INDEX(r,,4),
 IFERROR(FILTER(r, IF(B31="All", n<>"", p<1), 
 IF(ISNUMBER(MATCH(D31, n, )), n=D31, n<>"")), "no data for this selection"))

友情链接