时间戳

首页 > 解决方案 > 5 个特定单元格的平均值，包括活动行上面的 2 个/下面的 2 个跳过空白而不是包含在范围内

excel - 5 个特定单元格的平均值，包括活动行上面的 2 个/下面的 2 个跳过空白而不是包含在范围内

问题描述

我的 Excel 技能相对有限，所以我不确定这是否可以用公式来完成，但我希望它可以作为我的宏技能去录制宏和进行基本编辑。

基本上我有一张年表，包括前一年的 12 月和次年的 1 月。此处链接的表格是其外观的快照，2-1-2 日平均价格 ，日期列是 A 列。

本质上，我需要在 5 个可能不连续的日子里从每日平均列中输入 5 天的平均值。我需要完全跳过空白单元格，即周六和周日，并且不将它们包括在任何范围内，但这是我遇到麻烦的地方，它还需要跳过某些工作日，即未公布价格的公共假期。我还需要能够将其复制/拖动到列中的所有单元格，例如 2017 年 12 月至 2019 年 1 月，每年滚动计算，考虑到每年都会发生变化的公共假期，或者能够每年进行简单的编辑。

我输入了一个嵌套的 If 语句，该语句返回 1 表示周六、周日或公共假期以外的任何内容，而返回 0 表示所有内容。由于知识有限，而且我找不到简单的东西来从每日平均列中排除空白，这是/是我最有希望的选择，但我找不到我需要的确切公式来平均一个单元格活动行，以及基于不同列的值上下的前 2 个。

我在这个网站和 其他地方找到了形式的公式， =IFERROR(SUM(A10;B13;C5;D6;D8)/((A10<>0)+(B13<>0)+(C5<>0)+(D6<>0)+(D8<>0));0)但 问题是它基于整个范围的平均值，而不仅仅是我需要的五个。我还发现这并没有做太多，因为它似乎有类似的最终结果。=AVERAGEIF(B1:B1:B3:B3:B5:B5:B7:B7,">0")=AVERAGEIF($A$1:$A$500,D1,$B$1:$B$500)SUMPRODUCT

任何帮助将不胜感激，因为我竭尽全力试图使其工作/花时间自动化整个工作簿，这取决于这项工作并且不会告诉我的经理我的时间被浪费了。

提前感谢您，并为篇幅道歉。

问候，

西蒙

标签： excelexcel-formularolling-average

解决方案

您可以将以下数组公式（crtl+shift+enter）放入G3（假设基于屏幕截图）然后复制下来：
{=AVERAGE(IF(ROW(F3:$F$90)<=SMALL(IF(F3:$F$90<>"";ROW(F3:$F$90));MIN(COUNT(F3:$F$90);5));IF(F3:$F$90<>"";F3:$F$90)))}

一些评论：

  • 5 在公式中是硬编码的。最好把它放在某个单元格中，这样你也可以计算其他天的平均值；
  • 我假设数据在F3:$F$90；
  • 它还将计算空白行的平均值，因此如果您希望平均值也为空白行，则必须添加一个附加值；
  • 该公式也适用于最后一个值，如果少于 5 个，它将只取所有可用的值。

推荐阅读