☰

编写 Excel 函数来填补数据空白

tag-icon • tag-icon microsoft-excel charts
编写 Excel 函数来填补数据空白

在 Excel 中，我目前有以下散点图及其对应于某个系列的数据集：

在此处输入图片描述

我正在尝试创建某种函数，使我能够为当前存在的数据缺口插入点；最终目标是为散点图创建一条平滑的曲线。从概念上讲，我尝试创建的函数具有以下形式：

鉴于：

n is an integer, x = 3 + 20n, y = 3 + 20(n + 1)    

=IF(B(3 + 20n) < cell < B(3 + 20(n+1)), add (y-x)/19 to previous value, do nothing)

也就是说，我在第 23、43、63 列等处有数据点，并试图通过为每个箱添加一个唯一的间隔来填补空白。此间隔是通过取每个箱的下限和上限并除以 19 获得的。换句话说，每个箱应如下所示：

在此处输入图片描述

我必须对大约 1000 个单元格执行此操作，因此手动执行此操作非常繁琐。如果能提供一些有关如何创建此类函数的指导，使我能够一次性执行所有操作，我将不胜感激；此外，如果有更好的方法，任何信息都将大有帮助。

答案1

首先添加一个额外的列。在此列中，您可以使用以下公式：

=IF(MOD((ROW()-3),20)=0,B3,INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+3))+  
(INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+23))-INDIRECT("B"&  
(QUOTIENT((ROW()-3),20)*20+3)))/20*(ROW()-(QUOTIENT((ROW()-3),20)*20+3)))

此公式的工作原理：

MOD(ROW()-3),20)=0

检查行是否为 23,43,63，...如果是，则取相邻单元格，如果不是，则执行下一个公式：

INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+3))

这使用函数 indirect 来计算第一个单元格的地址，该单元格的行小于当前行，并且也是 23,43,63。它从当前行中减去 3，然后除以 20 并四舍五入为较小的整数。然后它乘以 20 并加 3 以获得正确的行号。然后将其连接到 B 以形成所需单元格的地址。

+(INDIRECT("B"&(QUOTIENT((ROW()-3),20)*20+23))-INDIRECT("B"&  
(QUOTIENT((ROW()-3),20)*20+3)))/20*(ROW()-(QUOTIENT((ROW()-3),20)*20+3))

这只是一个形式为插值公式：

 y_int = y_start + [(y_end-y_start)/(x_end-x_start)]*(x_int-x_start)

y_start 就是我上面解释过的那个。然后你得到 y_end，它几乎是一样的，只是你加了 23 而不是 3 来得到下一个值。然后你除以 20，这是两个后续值之间的差。x_int-x_start 的公式如下：

ROW()-(QUOTIENT((ROW()-3),20)*20+3)

这将获取当前行并从中减去较低的 23,43,63..，使用与我查找 y_start 相同的方法。

相关内容