题库相关：选择题练习

咔咔乱坠

练习留档，

碎碎念，结果填入题干好麻烦，用括号用字母都会有bug....

一时间找不到素材了，如果题库相关的问题需要求助，欢迎提问，一起学习交流

碎碎念，感觉要学习一下正则，去个括号要写好一长串的TEXT三兄弟，麻了

6楼有点难度，不定行数，虽说常见，但是数据源不规范还是很头疼，突然想起vlookup查找匹配有多个时，只会返回第一个，而选项中肯定有正确答案，那么第一个符合条件的答案就是正解

常见类型题库基本解了，规范还好说，不规范就就要自己清洗一下数据先，比如题目答案为a，答案为A，再比如答案为(a，不是一个完整的括号

咔咔乱坠

描述：答案在题干内，题目开头是阿拉伯数字，选项A-E或A-D
来源：工作遇到
一列题干加答案，找字母，题干包含正确答案的字母会有bug

1. =DROP(REDUCE(0,A1:A18,LAMBDA(x,y,IF(y<"99",VSTACK(x,LET(选项,OFFSET(y,,,5),答案,CLEAN(TRIM(TEXTBEFORE(TEXTAFTER(y,{"(","（"},-1),{")","）"}))),SUBSTITUTE(y,答案,CONCAT(VLOOKUP(MID(答案,SEQUENCE(LEN(答案)),1)&"*",选项,1,0))))),x))),1)

复制代码

两列题干和答案

1. =DROP(REDUCE(0,A1:A18,LAMBDA(x,y,IF(y<"99",VSTACK(x,IF({1,0},y,LET(选项,OFFSET(y,,,5),答案,CLEAN(TRIM(TEXTBEFORE(TEXTAFTER(y,{"(","（"},-1),{")","）"}))),CONCAT(VLOOKUP(MID(答案,SEQUENCE(LEN(答案)),1)&"*",选项,1,0))))),x))),1)

复制代码

多列题干和答案

1. =REDUCE({"题干","A","B","C","D","E"},A1:A18,LAMBDA(x,y,IF(y<"99",VSTACK(x,HSTACK(y,LET(选项,OFFSET(y,,,5),答案1,CLEAN(TRIM(TEXTBEFORE(TEXTAFTER(y,{"(","（"},-1),{")","）"}))),答案2,VLOOKUP(MID(答案1,SEQUENCE(,LEN(答案1)),1)&"*",选项,1,0),XLOOKUP({"A","B","C","D","E"},LEFT(答案2),答案2,"")))),x)))

复制代码

咔咔乱坠

描述：答案在选项下方，题目开头是阿拉伯数字，选项A-E或A-D
来源：工作遇到
一列题干加答案,找括号,题干包含括号会有bug

1. =DROP(REDUCE(0,A2:A22,LAMBDA(x,y,IF(y<"99",VSTACK(x,LET(选项,OFFSET(y,,,7),答案,CLEAN(TRIM(TEXTAFTER(INDEX(选项,MATCH("正*",选项,)),{":","："},-1))),TEXTJOIN("（"&CONCAT(VLOOKUP(MID(答案,SEQUENCE(LEN(答案)),1)&"*",选项,1,0)),,TEXTSPLIT(y,{"(","（"})))),x))),1)

复制代码

两列题干和答案

1. =DROP(REDUCE(0,A2:A22,LAMBDA(x,y,IF(y<"99",VSTACK(x,IF({1,0},y,LET(选项,OFFSET(y,,,7),答案,CLEAN(TRIM(TEXTAFTER(INDEX(选项,MATCH("正*",选项,)),{":","："},-1))),CONCAT(VLOOKUP(MID(答案,SEQUENCE(LEN(答案)),1)&"*",选项,1,0))))),x))),1)

复制代码

多列题干和答案

1. =REDUCE({"题干","A","B","C","D","E"},A2:A22,LAMBDA(x,y,IF(y<"99",VSTACK(x,HSTACK(y,LET(选项,OFFSET(y,,,7),答案1,CLEAN(TRIM(TEXTAFTER(INDEX(选项,MATCH("正*",选项,)),{":","："},-1))),答案2,VLOOKUP(MID(答案1,SEQUENCE(,LEN(答案1)),1)&"*",选项,1,0),XLOOKUP({"A","B","C","D","E"},LEFT(答案2),答案2,"")))),x)))

复制代码

咔咔乱坠

描述：答案在题干下方，题目开头是阿拉伯数字，选项固定为A-D
来源：https://club.excelhome.net/threa ... tml?_dsign=7eb820be
解法1

1. =LET(c,A:A,a,T(OFFSET(A1,ROW(1:5)*6-6,)),b,INDEX(c,MATCH(a,c,)+5),HSTACK(a,MAP(a,b,LAMBDA(x,y,LET(z,OFFSET(c,MAX((LEFT(x)=LEFT(c))*ROW(c)),,4),TEXTJOIN("+",,IF(ISERR(FIND(LEFT(z),y)),"",z)))))))

复制代码

解法2，更通用

1. =DROP(REDUCE(0,A1:A30,LAMBDA(x,y,IF(LEFT(y)<="9",VSTACK(x,IF({1,0},y,LET(选项,OFFSET(y,,,5),答案,OFFSET(y,5,),TEXTJOIN("+",,VLOOKUP(MID(答案,SEQUENCE(LEN(答案)),1)&"*",选项,1,0))))),x))),1)

复制代码

阿武教程

1. =LET(s,WRAPROWS(A1:A30,6),r,DROP(s,,-1),HSTACK(TAKE(s,,1),BYROW(REPT(r,REGEXTEST(TAKE(s,,-1),LEFT(r))),LAMBDA(x,TEXTJOIN("+",,x)))))
   

复制代码

渣渣

1. =DROP(REDUCE("",A1:A30,LAMBDA(x,z,IF(MOD(ROW(z),6)=1,VSTACK(x,HSTACK(z,TEXTJOIN("+",,FILTER(OFFSET(z,1,,4),ISNUMBER(FIND(LEFT(OFFSET(z,1,,4)),OFFSET(z,5,))))))),x))),1)

复制代码

凤凰涅槃

1. =LET(s,WRAPROWS(A1:A30,6),t,DROP(s,,1),HSTACK(TAKE(s,,1),BYROW(IF(REGEXTEST(t,"["&TAKE(s,,-1)&"].{2,"),t,""),LAMBDA(x,TEXTJOIN("+",,x)))))

复制代码

hcm19522

E3{=INDEX(A:A,SMALL(IF(1-ISERR(-LEFT(A$3:A$19)),ROW($3:$19),99),ROW(A1)))&""

F3:J6=IF(1-ISERR(FIND(F$2,MID($E3,FIND("(",$E3)+1,9))),INDEX(OFFSET($A$1,MATCH($E3,$A:$A,),,5),FIND(F$2,"ABCDE")),"")

咔咔乱坠

描述：答案在题干内，题目开头是阿拉伯数字，选项固定为A-D，但是行数不定
来源：Q群
一列题干加答案，找字母，题干包含正确答案的字母会有bug

1. =DROP(REDUCE(0,A1:A59,LAMBDA(x,y,IF(y<"99",VSTACK(x,LET(选项,TEXTSPLIT(CONCAT(OFFSET(y,1,,9)&" "),," ",1),答案,CLEAN(TRIM(TEXTBEFORE(TEXTAFTER(y,{"(","（"},-1),{")","）"}))),SUBSTITUTE(y,答案,CONCAT(VLOOKUP(MID(答案,SEQUENCE(LEN(答案)),1)&"*",选项,1,0))))),x))),1)

复制代码

两列题干和答案

1. =DROP(REDUCE(0,A1:A59,LAMBDA(x,y,IF(y<"99",VSTACK(x,IF({1,0},y,LET(选项,TEXTSPLIT(CONCAT(OFFSET(y,1,,9)&" "),," ",1),答案,CLEAN(TRIM(TEXTBEFORE(TEXTAFTER(y,{"(","（"},-1),{")","）"}))),CONCAT(VLOOKUP(MID(答案,SEQUENCE(LEN(答案)),1)&"*",选项,1,0))))),x))),1)

复制代码

多列题干和答案

1. =REDUCE({"题干","A","B","C","D"},A1:A18,LAMBDA(x,y,IF(y<"99",VSTACK(x,HSTACK(y,LET(选项,TEXTSPLIT(CONCAT(OFFSET(y,1,,9)&" "),," ",1),答案1,CLEAN(TRIM(TEXTBEFORE(TEXTAFTER(y,{"(","（"},-1),{")","）"}))),答案2,VLOOKUP(MID(答案1,SEQUENCE(,LEN(答案1)),1)&"*",选项,1,0),XLOOKUP({"A","B","C","D"},LEFT(答案2),答案2,"")))),x)))

复制代码

cnmlgb9998

咔咔乱坠 发表于 2024-7-23 16:10
描述：答案在题干内，题目开头是阿拉伯数字，选项固定为A-D，但是行数不定
来源：Q群
一列题干加答案，找 ...

是这个效果吗？？？？？？？？？？？？？


PQ 难道不香？

咔咔乱坠

这个论坛，已经屏蔽的人还能够回我的贴?

一个老陈

cnmlgb9998 发表于 2024-7-23 18:56
是这个效果吗？？？？？？？？？？？？？

是这个效果吗？？？？？？？？？？？？？


Pandas 难道不香？

咔咔乱坠

描述：答案在题干末尾，题目开头是阿拉伯数字，选项A-E或A-D
来源：https://club.excelhome.net/forum.php?mod=viewthread&tid=1563698&highlight=%CC%E2%BF%E2&_dsign=ff10bfff
没有新意，2楼公式都不用改，直接复制粘贴就能出