两个单元格多数据求占比

p58535860 · 发表于 2024-4-28 23:47

如何在两个单元格多数据求占比

计划小文 · 发表于 2024-4-29 08:17

=CONCAT(CHOOSECOLS(WRAPROWS(TEXTSPLIT(E3," ",,TRUE),2),1)&TOCOL(TEXT(--TEXTSPLIT(E3,TEXTSPLIT(E3,{1,2,3,4,5,6,7,8,9,0},,1),,1)/SUM(--TEXTSPLIT(E3,TEXTSPLIT(E3,{1,2,3,4,5,6,7,8,9,0},,1),,1)),"00.00%")))

p58535860 · 发表于 2024-4-29 08:52

计划小文发表于 2024-4-29 08:17
=CONCAT(CHOOSECOLS(WRAPROWS(TEXTSPLIT(E3," ",,TRUE),2),1)&TOCOL(TEXT(--TEXTSPLIT(E3,TEXTSPLIT(E3,{1, ...

老师，是G列单元格每行除以E列每行，对应百分比。怎么弄了

魚。。 · 发表于 2024-4-29 09:17

本帖最后由魚。。于 2024-4-29 09:20 编辑

=TEXTJOIN(REPT(" ",{2,8;2,1;2,0}),,LET(魚,TEXTSPLIT(G3," ","条",1),TEXT(IFERROR(魚/TEXTSPLIT(E3," ","条",1),魚),"[>]0%")))

复制代码

计划小文 · 发表于 2024-4-29 09:32

p58535860 发表于 2024-4-29 08:52
老师，是G列单元格每行除以E列每行，对应百分比。怎么弄了

=CONCAT(CHOOSECOLS(WRAPROWS(TEXTSPLIT(B3," ",,TRUE),2),1)&TOCOL(TEXT(--TEXTSPLIT(D3,TEXTSPLIT(D3,{1,2,3,4,5,6,7,8,9,0},,1),,1)/TEXTSPLIT(B3,TEXTSPLIT(B3,{1,2,3,4,5,6,7,8,9,0},,1),,1),"00.00%")))这个应该可以

mykitami · 发表于 2024-4-29 09:38

本帖最后由 mykitami 于 2024-4-29 09:42 编辑

=LET(a,TEXTSPLIT(E3," ","条",1),b,TEXTSPLIT(G3," ","条",1),TEXTJOIN(CHAR(10),,TAKE(b,,1)&" "&MAP(TAKE(b,,1),TAKE(b,,-1),LAMBDA(x,y,TEXT(ROUND(y/VLOOKUP(x,a,2,),2),"#%")))))

ggw092 · 发表于 2024-4-29 10:06

=LET(L,SUBSTITUTE(TEXTSPLIT(G3,," "),"条",""),CONCAT(IFERROR(TEXT(" "&L/SUBSTITUTE(TEXTSPLIT(E3,," "),"条",""),"0.00%")&CHAR(10),L)))

复制代码

p58535860 · 发表于 2024-4-29 16:14

魚。。发表于 2024-4-29 09:17

老师，请问下，如果是是G列单元格每行对应+E列每行求和如何写公式

hbzql · 发表于 2024-4-29 16:49

="汽车 "&TEXTJOIN({" 摩托车 "," 拖拉机 "},1,IFERROR(TEXT(ROUND(RIGHT(TEXTSPLIT(G3,"条"),2)/RIGHT(TEXTSPLIT(E3,"条"),2),2),"0%"),""))

魚。。 · 发表于 2024-4-29 17:42

本帖最后由魚。。于 2024-4-29 17:44 编辑

p58535860 发表于 2024-4-29 16:14
老师，请问下，如果是是G列单元格每行对应+E列每行求和如何写公式

=TEXTJOIN(IF({1,0}," ",CHAR(10)),,LET(魚,TEXTSPLIT(G3," ","条",1),IFERROR(魚+TEXTSPLIT(E3," ","条",1),魚)))

复制代码

		自动登录	找回密码
密码			免费注册

[求助] 两个单元格多数据求占比

评分

评分

评分

评分

评分