日期范围跨表查询求和

ipqjj

日期范围跨表查询求和

新建 Microsoft Office Excel 工作表.rar (18.13 KB, 下载次数: 29)

2024-6-28 17:14 上传

点击文件名下载附件

良良

H11=SUM(SUMIFS(OFFSET(INDIRECT(TEXT(ROW($1:$5),"[dbnum"&3*INT(ROW(A3)/3)-2&"]")&"月!A:A"),,MOD(ROW(A3),3)+1),INDIRECT(TEXT(ROW($1:$5),"[dbnum"&3*INT(ROW(A3)/3)-2&"]")&"月!A:A"),">="&H$5,INDIRECT(TEXT(ROW($1:$5),"[dbnum"&3*INT(ROW(A3)/3)-2&"]")&"月!A:A"),"<="&H$6))，三键。

ipqjj

非常感谢！数据正确，有一点就是表数据改变后查询结果不变。
继续请教：表中列与查询不是连续的怎么处理

hugeinfo

ipqjj 发表于 2024-6-29 10:58
非常感谢！数据正确，有一点就是表数据改变后查询结果不变。
继续请教：表中列与查询不是连续的怎么处理

用pq将数据合并，然后逆透视转置成一维表   生成合并汇总辅助表   然后用sumifs直接求和即可

或者用函数  wps或者365
=REDUCE(0,VSTACK(SUBSTITUTE(TEXT(ROW($1:$12),"[dbnum1]"),"一十","十"),ROW($1:$12))&"月",LAMBDA(x,y,SUM(VSTACK(x,IFNA(SUM(OFFSET(INDIRECT(y&"!A1"),2,MATCH(G11,INDIRECT(y&"!2:2"),)-1,100)*(INDIRECT(y&"!A3:A102")>=$H$5)*(INDIRECT(y&"!A3:A102")<=$H$6)),0)))))   如果数据范围超过100  可以自行调整范围即可

良良

ipqjj 发表于 2024-6-29 10:58
非常感谢！数据正确，有一点就是表数据改变后查询结果不变。
继续请教：表中列与查询不是连续的怎么处理

=SUM(SUMIFS(OFFSET(INDIRECT(TEXT(MOD(ROW($1:$24)-1,12)+1,"[dbnum"&INT(ROW($12:$35)/12)*3-2&"]d")&"月!A:A"),,COLUMN($A:$Y)),INDIRECT(TEXT(MOD(ROW($1:$24)-1,12)+1,"[dbnum"&INT(ROW($12:$35)/12)*3-2&"]d")&"月!A:A"),">="&H$5,INDIRECT(TEXT(MOD(ROW($1:$24)-1,12)+1,"[dbnum"&INT(ROW($12:$35)/12)*3-2&"]d")&"月!A:A"),"<="&H$6)*COUNTIF(OFFSET(INDIRECT(TEXT(MOD(ROW($1:$24)-1,12)+1,"[dbnum"&INT(ROW($12:$35)/12)*3-2&"]d")&"月!A2"),,COLUMN($A:$Y)),G11))

博爱风雨同舟

=REDUCE(0,VSTACK(TEXT(SEQUENCE(5),"[DBNUM1]")&"月",SEQUENCE(5)&"月"),LAMBDA(X,Y,X+SUM((INDIRECT(Y&"!A3:A50")>=$H$5)*(INDIRECT(Y&"!A3:A50")<=$H$6)*(INDIRECT(Y&"!B2:D2")=G11)*(INDIRECT(Y&"!B3:D50")))))

ipqjj

良良 发表于 2024-6-29 18:05
=SUM(SUMIFS(OFFSET(INDIRECT(TEXT(MOD(ROW($1:$24)-1,12)+1,"[dbnum"&INT(ROW($12:$35)/12)*3-2&"]d")&" ...

数据可变，值翻倍了

ipqjj

博爱风雨同舟 发表于 2024-6-29 19:00
=REDUCE(0,VSTACK(TEXT(SEQUENCE(5),"[DBNUM1]")&"月",SEQUENCE(5)&"月"),LAMBDA(X,Y,X+SUM((INDIRECT(Y&"! ...

我这显示有错，无数值

良良

ipqjj 发表于 2024-6-30 08:37
数据可变，值翻倍了

？

ericxzhou

Python+Pandas:
import pandas as pd

f = pd.ExcelFile('pandas exercise/sample296.xlsx')
lst = []
for sheet in f.sheet_names:
    if sheet != '汇总查询':
        df = pd.read_excel(f, sheet, header=1, index_col=0)
        df = df.stack()
        lst.append(df)
df = pd.concat(lst).sort_index()
df.loc['2024-2-5':'2024-5-31'].groupby(level=1).sum()