求助大神指导数据整理

salen0913

已上传附件，用VBA的方式还是用PQ的方式比较好，两种方式都是什么样的，求助大神给解决下

sobili

5:00这个时间段，耳朵完成流水28300怎么得出来的？

salen0913

sobili 发表于 2022-10-3 21:51
5:00这个时间段，耳朵完成流水28300怎么得出来的？

926050-897750=28300

zpy2

select * from 数据拆分后正则窗口函数计算流水;cli_split_data~数据拆分后正则窗口函数计算流水~/~需要分发;create temp table aa as select id,时间,需要分发,iif(rowid=1,需要分发,0) 累加,'分发' 组 from 数据拆分后正则窗口函数计算流水split where 需要分发!='' union all select rowid,时间,0 需要分发,流水截图 累加,'累加' 组  from 数据拆分后正则窗口函数计算流水 order by id;create temp table bb as select *,regexp2('(\D+)(\d+)',iif(组='分发',需要分发,累加),1) 昵称,regexp2('(\D+)(\d+)',需要分发,2) 完成流水,regexp2('(\D+)(\d+)',累加,2) 累加流水 from aa;

create temp table cc as with recursive under_alice (rowid,时间,昵称,完成流水,累加流水) as (select rowid,时间,昵称,完成流水,累加流水 from bb where rowid=1


un ion

all select a.rowid,a.时间,a.昵称,a.完成流水,iif(a.累加流水='',b.累加流水+a.完成流水,a.累加流水) 累加流水 from bb a join under_alice b on a.rowid-1=b.rowid)select * from under_alice;select 时间,昵称,iif(rowid>1,累加流水-lag(累加流水) over(),完成流水) 完成流水,累加流水 from cc;

hcm19522

K2{=IFERROR(INDEX(A:A,SMALL(IF(LEN(C$2:C$11)-LEN(SUBSTITUTE(C$2:C$11,"/",))+1+(C$2:C$11<>"")>=COLUMN(A:E),ROW($2:$11)),ROW(A1))),"")

L2{=IF(K2="","",INDEX(LEFT(TRIM(MID(SUBSTITUTE(VLOOKUP(K2,IF({1,0},$A$2:$A$11,B$2:B$11&"/"&C$2:C$11),2,),"/",REPT(" ",99)),COLUMN(A:E)*99-98,99)),2),IF(COUNTIF(K:K,K2)=COUNTIF(K$2:K2,K2),1,COUNTIF(K$2:K2,K2)+1)))

huiren_renli

Power Query 和 函数各用一点