在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：
eg， IF（ISBLANK（B2），NA（），B2） code>

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屋！