MS Excel LINEST、TREND、LOGEST 和 GROWTH 函数中的缺失值 [英] Missing values in MS Excel LINEST, TREND, LOGEST and GROWTH functions

问题描述

我在 Excel 2003 中使用 GROWTH(或 LINEST 或 TREND 或 LOGEST，都犯同样的问题)函数.但有一个问题，如果缺少某些数据，该函数拒绝给出结果:

I'm using the GROWTH (or LINEST or TREND or LOGEST, all make the same trouble) function in Excel 2003. But there is a problem that if some data is missing, the function refuses to give result:

您可以在此处下载文件.

1. 我不想要摆脱缺失值的明显解决方法 - 这意味着删除列并且这也会损坏图表，并且它会在我有更多的其他表中产生问题行和不同列中的缺失数据.其他明显的解决方法是将一个数据用于回归，将另一个用于图形，但同样，这很烦人，只会在工作表中造成混乱！！

1. I don't want the obvious workaround of getting rid of the missing value - that would mean to delete the column and that would also damage the graph, and it would make problems in my other tables where I have more rows and missing data in different columns. Other obvious workaround is to use one data for regression and the other for graph, but again, this is annoying and only makes mess in the sheet!!

有什么办法可以告诉excell——这个值是NA吗?

Is there any way to tell excell - this value is NA?

另一个想法是跳过表达式中的缺失值.是否可以寻址一组不连续的单元格?就像我的例子中的 =GROWTH($B2:$AH2; $B1:$AH1; B1) 一样，使用类似的东西:

Another idea would be to skip the missing value(s) in the expression. Is it possible to address a set of cells that is not continuous? Like instead of =GROWTH($B2:$AH2; $B1:$AH1; B1) as in my example, use something like:

=GROWTH({$B2:$I2,$K2:$AH2}; {$B1:$I1,$K1:$AH1}; B1)

我当然希望避免编写自己的表达式.我需要向我的同事解释如何做到这一切，但它会复杂得多.我想要一个简单而优雅的解决方案.

I'd of course like to avoid writing my own expressions. I need to explain this to my colleagues how to do all this and it would much more complicated. I want an easy and elegant solution.

推荐答案

我知道这是旧的...但如果您或其他人可能仍在寻找答案，您是否尝试过使用 FORECAST 函数?它将计算缺失值的趋势(只要没有任何#N/A"单元格).

I know that this is old...but in case you or someone else might still be looking for an answer, have you tried using the FORECAST function? It will calculate the trend with missing values (as long as there aren't any "#N/A" cells).

就我而言，我需要创建一个带有缺失值的无间隙图，但我还需要根据数据计算趋势.因此，首先我会将图形链接到一个数据集，该数据集为每个缺失值放置了一个 #N/A:例如，IF(ISBLANK(B2),NA(),B2)

In my case, I needed to create a gapless graph with missing values, but I also needed to calculate a trend from the data. So first I'd link the graph to a dataset that placed an #N/A for every missing value: e.g., IF(ISBLANK(B2),NA(),B2)

然后我会用原始数据计算预测数字:=FORECAST(B1,$B2:$AH2,$B1:$AH1)

But then I'd calculate the forecast numbers with the original data: =FORECAST(B1,$B2:$AH2,$B1:$AH1)

除非我遗漏了什么，否则应该照顾它.您基本上最终会得到两行相同的数字，但其中一行包含用于 FORECAST 计算的空白，而另一行将每个空白替换为图表的 NA().

Unless I'm missing something, that should take care of it. You basically end up with two rows of identical numbers, but one has blanks for the FORECAST calculation, and the other replaces each blank with an NA() for the graph.

这篇关于MS Excel LINEST、TREND、LOGEST 和 GROWTH 函数中的缺失值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！