Excel阵列公式 [英] Excel Array Formula

问题描述

我以前创建了一个Excel数组公式，如下所示：

I've previously created an Excel array formula such as follows:

X13: =SUM(IF($F13>H13:R13,1,0))+IF(F13>F9,1,0)+SUM(IF($F13>T13:U13,1,0))+IF(T13>U13,1,0)

在向下应用此公式并按 Ctrl + Shift + 输入，数组公式将在下面的行中生成公式，如下所示：

When applying this formula down a column and hitting Ctrl+Shift+Enter, the array formula produced formulas in the rows below as follows:

Y13: =SUM(IF($F14>H14:R14,1,0))+IF(F14>F10,1,0)+SUM(IF($F14>T14:U14,1,0))+IF(T14>U14,1,0)
Z13: =SUM(IF($F15>H15:R15,1,0))+IF(F15>F11,1,0)+SUM(IF($F15>T15:U15,1,0))+IF(T15>U15,1,0)
...

但是，现在我要更新公式，当我按 Ctrl + Shift + Enter 时，我得到

However, now that I'm trying to update the formula, when I hit Ctrl+Shift+Enter, I get the original formula showing up in all cells.

X13: =SUM(IF($F13>H13:R13,1,0))+IF(F13>F9,1,0)+SUM(IF($F13>T13:U13,1,0))+IF(T13>U13,1,0)
Y13: =SUM(IF($F13>H13:R13,1,0))+IF(F13>F9,1,0)+SUM(IF($F13>T13:U13,1,0))+IF(T13>U13,1,0)
Z13: =SUM(IF($F13>H13:R13,1,0))+IF(F13>F9,1,0)+SUM(IF($F13>T13:U13,1,0))+IF(T13>U13,1,0)
...

如何修复

推荐答案

不需要数组公式。此公式将执行完全相同的功能，而不是数组公式：

There's no need for an array formula. This formula will perform the exact same functionality and it is not an array formula:

=(F13>F9)+COUNTIF(H13:R13,"<"&$F13)+COUNTIF(T13:U13,"<"&$F13)+(T13>U13)

这篇关于Excel阵列公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！