使用函数能否实现将模拟表中A表的数据格式转换成B表的格式显示

齐而斋

A表是日常银行导出的数据，使用类似正则函数是否能将A表转化成B表来分类数据呢

hxj119

1. =REDUCE(A2:C2,A3:A5,LAMBDA(x,y,IFNA(VSTACK(x,HSTACK(TEXT(y,"e-m-d"),TEXTSPLIT(REGEXP(OFFSET(y,,1),"([一-龟]+)(?=\d)",2,"\1,"),",","|"))),TEXT(y,"e-m-d"))))

复制代码

无关岁月

1. =REDUCE(A2:C2,A3:A5,LAMBDA(_a,_b,VSTACK(_a,IFNA(HSTACK(_b,WRAPROWS(REGEXEXTRACT(OFFSET(_b,,1),"[一-龟]+|[\d.]+",1),2)),_b))))

复制代码

mjm04

高版本或WPS可用，一步到位的

=REDUCE({"日期","科目","金额"},B3:B5,LAMBDA(_a,_b,VSTACK(_a,HSTACK(EXPAND(OFFSET(_b,,-1),ROWS(WRAPROWS(REGEXP(_b,"[一-龢]+|[\d.?]+"),2,"")),,OFFSET(_b,,-1)),WRAPROWS(REGEXP(_b,"[一-龢]+|[\d.?]+"),2,"")))))

jerrike

1. =LET(a,REGEXP(TEXTJOIN("|",,TEXT(A3:A5,"e/m/d")&"|"&SUBSTITUTE(B3:B5,"|","|"&TEXT(A3:A5,"e/m/d")&"|")),"[一-龟]+|[\d./]+"),MAKEARRAY(COUNTA(a)/3,3,LAMBDA(x,y,INDEX(a,3*x-3+y))))

复制代码

wj65134

1. =REDUCE(A2:C2,A3:A5,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,TEXTSPLIT(SUBSTITUTES(OFFSET(y,,1),{"加";"税"},{"加-";"税-"}),"-","|")),y))))

复制代码

齐而斋

mjm04 发表于 2024-8-13 16:06
高版本或WPS可用，一步到位的

=REDUCE({"日期","科目","金额"},B3:B5,LAMBDA(_a,_b,VSTACK(_a,HSTACK(EX ...

感谢大佬指点，如果A表里面再加入编号，如图例中，也可以转换吗

hugeinfo

=DROP(REDUCE("",C3:C5,LAMBDA(x,y,VSTACK(x,HSTACK(IF(SEQUENCE(ROWS(TEXTSPLIT(y,{"加","税"},"|"))),"")&OFFSET(y,,-2,1,2),TEXTSPLIT(y,{"加","税"},"|"))))),1)

mjm04

齐而斋 发表于 2024-8-13 17:24
感谢大佬指点，如果A表里面再加入编号，如图例中，也可以转换吗

这样

=REDUCE({"日期","编号","科目","金额"},C3:C5,LAMBDA(_a,_b,VSTACK(_a,HSTACK(EXPAND(OFFSET(_b,,-2),ROWS(WRAPROWS(REGEXP(_b,"[一-龢]+|[\d.?]+"),2,"")),,OFFSET(_b,,-2)),EXPAND(OFFSET(_b,,-1),ROWS(WRAPROWS(REGEXP(_b,"[一-龢]+|[\d.?]+"),2,"")),,OFFSET(_b,,-1)),WRAPROWS(REGEXP(_b,"[一-龢]+|[\d.?]+"),2,"")))))

ericxzhou

Python+Pandas:
import pandas as pd

df = pd.read_clipboard()
df['摘要'] = df['摘要'].str.split(r'|')
df = df.explode('摘要')
df['金额'] = df['摘要'].str.extract(r'(\d+(?:\.\d+)?)').astype('float')
df['摘要'] = df['摘要'].str.extract(r'([一-龟]+)')
df