这个问题可以用数据透视表、VBA、Power Query、Power Pivot等多种功能解决,下面我使用被称为“Excel 20年来最大革新”的Power Pivot功能来演示一下解决方案。
先将数据源与结果展示如下:
选中数据源区域,点击【Power Pivot】选项卡下面的【添加到数据模型】,在弹出的【创建表】对话框中,将【我的表具有标题】前面的对勾勾选上,并点击确定。
选中刚刚创建的表,在【表格工具——设计】选项卡中将表名称按自己习惯命名,这里我命名【表1】。
选中E1单元格,然后依次点击【数据选项卡——获取外部数据——现有连接】
在弹出的对话框中选择如图所选的【表1】,并点击【打开】。
在弹出的【导入数据】对话框中选择【表】,并点击【确定】。
在新导入的表上点击鼠标右键,在弹出的右键菜单里面依次选择【表格——编辑DAX】。
在弹出的【编辑DAX】对话框中,【命令类型】选择【DAX】,在空白处输入DAX表达式(表达式放于文末),并点击【确定】。
至此已得到最终结果。
例如:对第5行数据的日期进行了修改;增加了第11行数据。
在结果表上点击鼠标右键,在弹出的右键菜单中点击【刷新】。
大约1-2秒后即可得到重新整理后的结果。
用Power Pivot解决本问题,有三大优点:
1.一劳永逸:仅需编写一次DAX表达式,可无数次复用,若使用普通函数公式,每次都要重新编辑。
2.一键刷新:当数据源发生增减或修改时,仅需鼠标点击几下即可实现整个数据源的重新整理,比输入公式速度快成千上万倍,对于常规重复性操作可以大大提高效率。
3.智能扩展:数据源区域会随着数据的增加或删除而自动智能扩展,若使用公式每次还需要重新选择数据源或拖拽公式。
附:DAX表达式
DAX表达式可以在DaxStudio(如下图)中编写和调试,调试无误后再粘贴至编辑DAX对话框中。
EVALUATE
ADDCOLUMNS (
VALUES ( '表1'[用户昵称] ),
'连续坚持天数', COUNTROWS ( FILTER ( '表1', '表1'[用户昵称] = EARLIER ( '表1'[用户昵称] ) ) ),
'从哪天开始的', CALCULATE (
MIN ( '表1'[日期] ),
FILTER ( '表1', '表1'[用户昵称] = EARLIER ( '表1'[用户昵称] ) )
)
)
「精进Excel」系头条签约作者,关注我,如果任意点开三篇文章,没有你想要的知识,算我耍流氓!
联系客服