Excel 2013论坛不工作在一些单元格 [英] Excel 2013 Forumla Not Working in some Cells

问题描述

因此我试图在Excel 2013中完成一些工作，在获得初始公式后，我将其拖动到其他几个单元格中。我完成了所有标准的引用锁定，甚至将确切的公式复制到具有复制粘贴的新单元格公式窗口中。该公式在第一个单元格中工作，而不是那些被拖动的单元格。它也可以在看似随机的单元格中粘贴（见屏幕截图）。

公式在下面...这是一个全新的工作表，单元格尚未初始化直到公式被粘贴。这是Excel 2013的问题吗？由于该公式完美地在一些糊状物中工作，而不在其他方面，我不认为是这个问题。我在几个单元格之后，将浆料中的单元格格式更改，看看是否改变了结果。

更新：我添加了false值，IF在零单元格上评估为false ...为什么在某些情况下是正确的，而在其他的仍然是一个谜。

  = MIN（IF（'Metrics'！$ DL $ 2：$ DL $ 1725 = $ L $ 2& $ O $ 2，'Metrics'！$ DH $ 2：$ DH $ 1725））
  

解决方案

问题是，正确评估它的公式应该被设置为一个数组（ctrl-shift-enter）。
但是，Excel 2013似乎随机将公式识别为数组公式，有时候不会。因此，公式偶尔会在全新的未初始化单元格中使用/显示标准公式以及正确的结果。其他时候，它仍然会显示相同的公式，但会产生不正确的结果，因为它不被评估为数组。

任何没有神奇地转换为数组公式，当更改为数组时，将使用正确的值进行更新。

只有手动更改的单元格反映 {= MIN（IF（... ）...））} 数组公式的指示。公式产生正确结果的单元格仍显示为非数组公式。

SO I'm trying to get some work done in Excel 2013, and after getting an initial formula to work, I dragged it across several other cells. I did all the standard locking of references and such, even copied the exact formula into the new cell formula window with copy paste. The formula works in the first cell, not in those it's dragged over. It also works in seemingly random cells when pasted around (see screen shot).

Formula is below... this was a brand new sheet, the cells had not been initialized until the formula was pasted. Is this an issue with Excel 2013? Since the formula works in some pastes perfectly, and not in others, I don't think that's the issue... I changed the cell format after the pastes in a couple cells to see if that changed results at all - it does not.

Update: I added in the false value, and the IF is evaluating to false on the zero cells... why it's correctly true in some, and false in others is still a mystery.

=MIN(IF('Metrics'!$DL$2:$DL$1725=$L$2&$O$2,'Metrics'!$DH$2:$DH$1725))

解决方案

The issue is that for the formula to correctly evaluate it should be set as an array (ctrl-shift-enter). However, Excel 2013 seems to randomly recognize the formula as an array formula, and sometimes not. Thus the formula occasionally will work, in a brand new, uninitialized cell, using/displaying the standard formula, and a correct result. Other times it will still display the same formula, but produce incorrect results since it is not being evaluated as an array.

Any of the cells that did not magically convert to an array formula, when changed to array, will update with correct values.

Only manually changed cells reflect the {=MIN(IF(...)...))} indication of an array formula. Cells in which the formula produced correct results still display as a non-array formula.

这篇关于Excel 2013论坛不工作在一些单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！