我有一张 Excel 表，其中 H 列中有 4 个值组。因此 H7 到 H10 包含与一个样本相关的 4 个值。H11 到 H14 包含与另一个样本相关的 4 个值。
在第 I 列中，我有一个公式来计算 H 中 4 个值中的 2 个的平均值以生成结果。我选择 H7 到 H10 中最接近的两个值。因此 I7 将包含公式“=AVERAGE(H7:H8)”。但如果假设 H8 和 H9 之间的距离比 H7 和 H8 之间的距离更近，我可能会单击 I7，然后更改范围。我必须手动选择最接近的两个连续值。
在 M 列中，我有一个公式来计算 H 中 4 个值中的 2 个值的标准差以生成结果。因此 M7 将包含公式“=STDEV(H7:H8)”。但是，如果我单击 I7 来更改计算平均值的范围，我必须记住在 M7 中也更改为相同的范围，以便根据 H 列中的相同两个值计算平均值和标准差。
有没有办法让 M7 中的标准差公式根据我在 I7 中计算平均值时使用的数值范围自动更新？
答案1
这将做到这一点：
=STDEV(INDIRECT(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(I7),")",""),"=AVERAGE(","")))
FORMULATEXT以文本格式提取公式，
SUBSTITUTE删除不必要的部分，只留下H7:H8
INDIRECT从字符串到实际引用的转换，
STDEV执行计算
答案2
实际上，您可以将引用指定为另一列中的文本，例如 N7：
="H7:H8"
然后在I7
=AVERAGE(INDIRECT(N7))
在 M7 中
=STDEV(INDIRECT(N7))
如果您可能选择两个不连续的引用，那么您将需要两个辅助列，例如 N7 和 O7，它们分别包含文本值="H7"和。="H10"
然后例如
=AVERAGE(INDIRECT(N7),INDIRECT(O7))
当然，在后一种方法中，您不妨将您选择的两个数字直接放入 N7 和 O7 中，就像和一样，=H7并=H10删除 INDIRECT 函数。