PotAto
2022-08-01T02:29:41+00:00
我有一个表格，每日新建一个sheet更新数据，然后做了一个日报表，统计每天数据较昨天变化，如下图
[img]https://img.nga.178.com/attachments/mon_202208/10/-7Q2q-d1coK1uT3cSsg-dc.jpg.medium.jpg[/img][img]https://img.nga.178.com/attachments/mon_202208/10/-7Q2q-hu3rK1jT3cSsg-dc.jpg.medium.jpg[/img]
现在我觉得每天替换函数中的文件名(如今天要把上图函数中的“0809”替换成“0810”)太麻烦，而且怕某天出错。因为今天的日期也可以用函数直接得出来，也与我sheet的命名规则对得上，如下图，那么，有什么办法能让日报表能直接自动引用当天及前一天的数据呢
[img]https://img.nga.178.com/attachments/mon_202208/10/-7Q2q-2uzxK1sT3cSsg-dc.jpg.medium.jpg[/img]
组合使用indirect，address，today这三个函数
要灵活[s:ac:goodjob]
[quote][pid=631173799,33007645,1]Reply[/pid] Post by [uid=41091840]光辉余烬[/uid] (2022-08-10 10:35):
组合使用indirect，address，today这三个函数
要灵活[s:ac:goodjob][/quote]谢谢大佬，研究下
不过你可能需要注意，跨文件的excel的公式必须打开才会更新。比如你昨天公式里的today在今天被引用的时候还是0809。
=today()-1
然后单元格格式里设置mmdd
想了一下，好象会有问题
你用today函数，打开之前表格就炸了。。。
你需要复制粘贴“仅数值”
[img]https://img.nga.178.com/attachments/mon_202208/10/-7Q2q-5xv5ZkT3cSy0-hy.jpg[/img]
=SUMIF(OFFSET(INDIRECT(ADDRESS(1,1,,,CONCATENATE(IF(MONTH(TODAY())<10,"0"&MONTH(TODAY()),MONTH(TODAY())),IF(DAY(TOSUMIF(OFFSET(INDIRECT(ADDRESS(1,1,,,CONCATENATE(IF(MONTH(TODAY())<10,"0"&MONTH(TODAY()),MONTH(TODAY())),IF(DAY(TODAY())<10,"0"&MONTH(TODAY()),DAY(TODAY()))))),,,9999),"合 计",OFFSET(INDIRECT(ADDRESS(1,1,,,CONCATENATE(IF(MONTH(TODAY())<10,"0"&MONTH(TODAY()),MONTH(TODAY())),IF(DAY(TODAY())<10,"0"&MONTH(TODAY()),DAY(TODAY()))))),,5,9999))DAY())<10,"0"&MONTH(TODAY()),DAY(TODAY()))))),,,9999),"合计",OFFSET(INDIRECT(ADDRESS(1,1,,,CONCATENATE(IF(MONTH(TODAY())<10,"0"&MONTH(TODAY()),MONTH(TODAY())),IF(DAY(TODAY())<10,"0"&MONTH(TODAY()),DAY(TODAY()))))),,5,9999))
原公式，看着一堆，其实都是简单堆叠，应该有更简单的方法，期待大神
用的时候注意"合计"里的空格和你的是否一致
新建一个sheet用来存放上一日的，可以用vba也可以手动粘贴上一日的过去，获取今天用today()
[quote][pid=631187769,33007645,1]Reply[/pid] Post by [uid=41091840]光辉余烬[/uid] (2022-08-10 11:23):
[img]https://img.nga.178.com/attachments/mon_202208/10/-7Q2q-5xv5ZkT3cSy0-hy.jpg[/img]
=SUMIF(OFFSET(INDIRECT(ADDRESS(1,1,,,CONCATENATE(IF(MONTH(TODAY())<10,"0"&MONTH(TODAY()),MONTH(TODAY())),IF(DAY(TOSUMIF(OFFSET(INDIRECT(ADDRESS(1,1,,,CONCATENATE(IF(MONTH(TODAY())<10,"0"&MONTH(TOD[/quote]感谢大佬，条理很清晰，会了会了[s:ac:闪光]
感觉楼上写得也太复杂了,脑壳痛:
=SUMIF(INDIRECT(TEXT(TODAY()-1,"MMdd")&"!A2",TRUE):INDIRECT(TEXT(TODAY()-1,"MMdd")&"!A9999",TRUE),"合计",INDIRECT(TEXT(TODAY()-1,"MMdd")&"!I2",TRUE):INDIRECT(TEXT(TODAY()-1,"MMdd")&"!I9999",TRUE))
如果你数据超过了9999行,就把公式里面的2个9999改成你需要的行数