某人答题情况分为题目编号、答案两列。
D | E | |
1 | Form Question | Form Answer |
2 | 1 | All of the Above |
3 | 2 | A;B;C |
4 | 3 | B;C |
5 | 4 | All of the Above |
答案一般由分号分隔的选项组成,如果是字符串“All of the Above”,则对应字典表 Sheet2 里同题号的全部选项。
A | B | |
1 | 1 | A |
2 | 1 | b |
3 | 1 | c |
4 | 1 | d |
5 | 2 | a |
6 | 2 | b |
7 | 2 | c |
8 | 2 | d |
9 | 3 | a |
10 | 3 | b |
11 | 3 | c |
12 | 3 | d |
13 | 4 | a |
14 | 4 | b |
15 | 4 | c |
16 | 4 | d |
17 | 4 | e |
现在要将此人的答案拆成一个个单独的选项。
A | B | |
1 | Question | What I want |
2 | 1 | A |
3 | 1 | b |
4 | 1 | c |
5 | 1 | d |
6 | 2 | A |
7 | 2 | B |
8 | 2 | C |
9 | 3 | B |
10 | 3 | C |
11 | 4 | a |
12 | 4 | b |
13 | 4 | c |
14 | 4 | d |
15 | 4 | e |
使用 SPL XLL,输入公式:
=spl("=dt=?1,dc=?2,E@b(dt.news(if(~(2)==$[All of the Above],dc.select(~(1)==dt.~(1)).(~(2)), ~(2).split($[;]));dt.~(1),~))",D2:E5,Sheet2!A2:Sheet2!B18)
E@b 将表格转为序列。~(1) 表示序列当前成员的的第 1 个子成员。$[] 是字符串标志。