AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
  • Coding
    • 最新
    • 标签
主页 / computer / 问题 / 1691072
Accepted
Boswser
Boswser
Asked: 2021-12-04 05:07:18 +0800 CST2021-12-04 05:07:18 +0800 CST 2021-12-04 05:07:18 +0800 CST

Excel:如何获得最近 3 个月的平均值,但不使用空白月份?

  • 772

嗨,人们比我更擅长 Excel!感谢您提供的任何帮助。我会尽量简短:

我有一个 3 列表。月份(如 1、2、3 等)、销售人员姓名和销售额。每行是销售人员当月获得的销售额。

它的排列方式如下:前 1000 行都是 Jan/1,接下来的 1000 行都是 Feb/2,等等。

我需要得到每个销售人员过去 3 个月的平均销售额。但是——如果最后 3 个月的其中一个是空白的,我仍然需要 3 个月的平均值。

所以我需要这个公式来识别,例如,一个空白的 9 月结果,然后去获取下一个最近的结果(8 月,7 月,不管它可能是什么),所以它总是平均该销售人员最近 3 个月的结果,并且不只是 1 或 2 个结果。

现在我有一个非常简单的数据透视表。在我意识到这个问题之前,对我的目的来说工作得很好:(

有什么方法可以做到这一点(数据透视表与否)?有超过1000个销售人员,所以手动是不可行的。无论出于何种原因,我有很多人一个月没有数字,所以我需要解决这个问题。

谢谢你的帮助!请让我知道我是否可以包含其他内容或不清楚。

microsoft-excel pivot-table
  • 2 2 个回答
  • 179 Views

2 个回答

  • Voted
  1. Best Answer
    Isolated
    2021-12-04T14:28:47+08:002021-12-04T14:28:47+08:00

    我不知道平均最近三个非空白月份的好方法,但这是一种非常老套的方法。这可能会引发一些更好的方法来实现它。

    在您的原始数据中,创建一个表,然后按月份(降序)和人员排序。

    在此处输入图像描述

    接下来,过滤销售列并取消选择空白。然后将这些内容复制到另一个区域并粘贴。

    然后创建一个“排名”列。单元格 D2 的公式,每个月对每个人进行排名(最近的是 1 等)。

        =IF(B2=B1,D1+1,1)
    

    然后有另一个区域与您不同的人(列 F),以及他们最近 3 个月的占位符列(标记为 1、2 和 3 的列)。

    单元格 G2 公式:

        =SUMIFS($C:$C,$B:$B,$F2,$D:$D,G$1)
    

    将其拖到列标题 3 并向下。最后,创建你的平均公式。

    在此处输入图像描述

    • 1
  2. DMM
    2021-12-05T19:12:48+08:002021-12-05T19:12:48+08:00

    采用一种不同的方法,使原始数据保持不变,无需重新排序、过滤或复制,将 3 个辅助列添加到数据列表中,如下所示。

    添加了 3 个辅助列的数据列表

    Column Row是一个从 1 开始的简单索引系列,Person2列与Person相同,但使用 IF 函数在Sales为零的行中插入空字符串。MCount按Person提供非零销售额的月份计数。

    MCount与包含每个销售人员的一行的结果列表交互,如下所示。

    结果列表

    在结果列表中,Months列仅计算每个销售人员在数据列表中销售额非零的月份数。单元格 I2 中的公式是

    =COUNTIFS(A$2:A$21,H2,$C$2:$C$21,">0")

    RowM1显示数据列表中对应于每个销售人员销售额非零的第一个月的Row值。单元格 J2 中的公式是

    =MATCH(H2,$E$2:$E$21,0)

    列Month1、Month2和Month3是MCount列(数据列表)中的月份数,对应于用于计算 3 个月平均值的第一个月、第二个月和第三个月,而Sales1、Sales2和Sales3是这 3 个月的销售额值个月。平均值是计算的 3 个月平均值。

    单元格 K2、L2 和 M2 的公式分别为

    =I2-1

    =K2+1

    =L2+1

    单元格 N2 中的公式是

    =SUMIFS($C$2:$C$21,$E$2:$E$21,$H2,$F$2:$F$21,K2)

    并依赖于数据列表的MCount列中的值(范围$F$2:$F$21,见下)。单元格 N2 被复制到单元格 O2 和 P2。

    范围 I2:Q2 可以复制到结果列表的所有后续行。

    数据列表 - MCount列

    单元格 F2 中的公式是

    =IF(C2>0,1,0)

    这会将 0 或 1 放入单元格中。0 表示单元格 E2 中的人在单元格 中显示的月份没有销售额B2,而 1 表示这是此人的第一个月的销售额。

    单元格 F3 中的公式要复杂得多,并且是

    =IF(LEN(E3)=0,0,IF(VLOOKUP(E3,H$2:J$3,3,FALSE)=D3,1,1+MAXIFS(F$2:F2,E$2:E2,E3)))

    如果行对应于零销售额,则第一个 IF 的 TRUE 部分仅提供 0 。

    否则,Sales不为零,并触发第二个 IF。在第二个 IF中,在结果表中查找单元格 E3 中 的Person ,如果RowM1值(数据列表中对应于第一个月销售的行号)与单元格 D3 中的Row值匹配,这意味着第 3 行对应于单元格 E3 中标识的人员的第一行销售额非零。

    如果RowM1和Row值不同,则数据表中的第 3 行对应于单元格 E3 中人员的下一个销售月份(第一个月之后)。在这种情况下,月份数是通过将数据列表前面行中单元格 E3 中的销售人员的 F 列中的最大值加 1 获得的。MAXIFS 函数用于确定此最大值。

    单元格 F3 中的公式将复制到数据列表的后续行。在公式中适当使用相对和绝对寻址可确保 MAXIFS 函数使用副本中适当的单元格范围。

    笔记

    1. 使用 MAXIFS 需要 Excel 2019 或更高版本
    2. 假设 (i) 数据按时间顺序排列,并且 (ii) 每个销售人员和月份组合最多有一个非零行销售数据。
    3. 在图像中,蓝色字体表示公式,黑色字体表示静态值。
    • 0

相关问题

  • 带有“和”运算符的 Excel 数据透视表

  • 如何对整列使用 Excel 的 LENGTH 函数?

  • Excel 数组(2 个变量)

  • 如何从 WSL 打开 office 文件

  • VBA根据文件名重命名工作表

Sidebar

Stats

  • 问题 205573
  • 回答 270741
  • 最佳答案 135370
  • 用户 68524
  • 热门
  • 回答
  • Marko Smith

    如何减少“vmmem”进程的消耗?

    • 11 个回答
  • Marko Smith

    从 Microsoft Stream 下载视频

    • 4 个回答
  • Marko Smith

    Google Chrome DevTools 无法解析 SourceMap:chrome-extension

    • 6 个回答
  • Marko Smith

    Windows 照片查看器因为内存不足而无法运行?

    • 5 个回答
  • Marko Smith

    支持结束后如何激活 WindowsXP?

    • 6 个回答
  • Marko Smith

    远程桌面间歇性冻结

    • 7 个回答
  • Marko Smith

    子网掩码 /32 是什么意思?

    • 6 个回答
  • Marko Smith

    鼠标指针在 Windows 中按下的箭头键上移动?

    • 1 个回答
  • Marko Smith

    VirtualBox 无法以 VERR_NEM_VM_CREATE_FAILED 启动

    • 8 个回答
  • Marko Smith

    应用程序不会出现在 MacBook 的摄像头和麦克风隐私设置中

    • 5 个回答
  • Martin Hope
    Saaru Lindestøkke 为什么使用 Python 的 tar 库时 tar.xz 文件比 macOS tar 小 15 倍? 2021-03-14 09:37:48 +0800 CST
  • Martin Hope
    CiaranWelsh 如何减少“vmmem”进程的消耗? 2020-06-10 02:06:58 +0800 CST
  • Martin Hope
    Jim Windows 10 搜索未加载,显示空白窗口 2020-02-06 03:28:26 +0800 CST
  • Martin Hope
    v15 为什么通过电缆(同轴电缆)的千兆位/秒 Internet 连接不能像光纤一样提供对称速度? 2020-01-25 08:53:31 +0800 CST
  • Martin Hope
    andre_ss6 远程桌面间歇性冻结 2019-09-11 12:56:40 +0800 CST
  • Martin Hope
    Riley Carney 为什么在 URL 后面加一个点会删除登录信息? 2019-08-06 10:59:24 +0800 CST
  • Martin Hope
    zdimension 鼠标指针在 Windows 中按下的箭头键上移动? 2019-08-04 06:39:57 +0800 CST
  • Martin Hope
    jonsca 我所有的 Firefox 附加组件突然被禁用了,我该如何重新启用它们? 2019-05-04 17:58:52 +0800 CST
  • Martin Hope
    MCK 是否可以使用文本创建二维码? 2019-04-02 06:32:14 +0800 CST
  • Martin Hope
    SoniEx2 更改 git init 默认分支名称 2019-04-01 06:16:56 +0800 CST

热门标签

windows-10 linux windows microsoft-excel networking ubuntu worksheet-function bash command-line hard-drive

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve