☰

数组中的 IF 和 ISBLANK

tag-icon • tag-icon microsoft-excel worksheet-function
数组中的 IF 和 ISBLANK

说到高级 Excel，我是个菜鸟。我目前使用这个在网上找到并修改过的公式到我的电子表格中：

=IF(ROWS(G$7:G7)>$D$2,"",
    INDEX(Test!G$2:G$999999,
          SMALL(IF((Test!$A$2:$A$999999>=$B$2)*(Test!$A$2:$A$999999<=$B$3),
          ROW(Test!$A$2:$A$999999)-ROW(Test!$A$2)+1),ROWS(G$7:G7))))

本质上，我从另一张工作表中提取信息，对应于特定的日期范围。我将单元格范围保留为 row999999，这样我就不必在输入月份/年份的新数据时更新公式。

如果是从空白单元格中提取数据，我想将其留空 - 目前它显示为零。

我尝试在前面放置一个 IF 或 ISBLANK，但它会使所有单元格（甚至包含数据的单元格）变为空白。

答案1

你应该能够IF(ISBLANK(...))在你的函数周围放置一个INDEX。像这样：

=IF(ROWS(G$7:G7)>$D$2,"",
    IF(ISBLANK(INDEX(Test!G$2:G$999999,
                     SMALL(IF((Test!$A$2:$A$999999>=$B$2)*(Test!$A$2:$A$999999<=$B$3),
                     ROW(Test!$A$2:$A$999999)-ROW(Test!$A$2)+1),ROWS(G$7:G7)))),"",
       INDEX(Test!G$2:G$999999,
             SMALL(IF((Test!$A$2:$A$999999>=$B$2)*(Test!$A$2:$A$999999<=$B$3),
             ROW(Test!$A$2:$A$999999)-ROW(Test!$A$2)+1),ROWS(G$7:G7)))))

答案2

关于 ”我保留单元格范围为 row999999，这样我就不必在几个月/几年内输入新数据时更新公式“我担心这是一个糟糕的想法。

我知道您认为通过设置这样的上限参考是在“规避风险”，但实际上这样做弊大于利。

COUNTIF例如，与SUMIF、COUNTIFS或不同SUMIFS，数组公式会计算传递给它们的所有单元格，无论从技术上讲是否超出了这些范围内最后使用的单元格。

因此，例如，如果您的数据只有第 1000 行，那么通过引用 999,999 行，您实际上会迫使 Excel 计算比实际需要多近一百万个单元格，从而产生一个资源消耗惊人的公式。而这还只是该公式的一个实例。

使用某些函数，您可以引用整个列而不会损害性能，但使用对数组进行操作的函数则不行，例如AGGREGATE、SUMPRODUCT和任何需要CSE( CTRL+SHIFT+ENTER) 的构造。

因此，您应该为所引用的结束行选择一个适当低但足够的上限，或者更好的是，使您的范围动态化，以便它们随着数据的扩展/收缩而自动调整。

问候

相关内容