![编写 Excel 函数来填补数据空白](https://linux22.com/image/1523738/%E7%BC%96%E5%86%99%20Excel%20%E5%87%BD%E6%95%B0%E6%9D%A5%E5%A1%AB%E8%A1%A5%E6%95%B0%E6%8D%AE%E7%A9%BA%E7%99%BD.png)
在 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 相同的方法。