Excel 整理英语选择题格式

原来的格式是一个问题多行，包括问题、选项、答案、解释：

根据题主的说法，唯一的规律就是问题的序号，甚至问题序号后的标点都不是一致的，有点是顿号，有的是圆点。

我们的目标是把这些问题整理成一个问题一行的格式：

这一类的问题需要从序号入手，同一问题下的问题、选项、答案、解释都给一个相同的序号，然后我们分组合并文本就好了。

Power Query中的解法

第一步：整理问题序号

我们先把第一列复制，然后从数字到非数字拆分复制列：

然后删除后面非数字列，设置数字列的数据类型为整数：

替换error为null

向下填充：

这样问题的序号我们就整理好了。

第二步：逆透视

选中除问题序号之外的所有列，逆透视：

逆透视有个好处，会自动去掉null值。

第三步：分组合并

用问题序号分组，之后选所有行，这时each 后面是“_”，我们修改each 后面的内容：

Text.Combine([值]," ")

这句的意思是合并分组后对应表中的值列中的文本。

然后把这个结果加载到Excel工作表中就可以了。

Power Query中错误值也可以用作运算，我们故意设置整数格式，这样其他的文本就会出错，然后再统一替换错误值为null。

如果问题下面的选项、答案、解释是非常规律的结构，比如每个问题都占4行，这样我们就可以用序号直接计算出每个问题的序号，然后再根据序号来分组合并。计算要用到的函数是Number.IntegerDivide函数，如果在Excel中就是INT函数。

Excel公式解法

如果你有Textjoin函数就可以尝试用Excel公式来解决这个问题

第一步：序号

写一个公式整理好问题序号：

=IF(MAX(IFERROR(--MID('Sheet2 (2)'!$A2,1,ROW($1:$99)),0))>0,MAX(IFERROR(--MID('Sheet2 (2)'!$A2,1,ROW($1:$99)),0)),Q1)

就是从第一列中提取出问题序号：

MAX(IFERROR(--MID('Sheet2 (2)'!$A2,1,ROW($1:$99)),0))

如果这一行的序号大于零，就是序号本身，如果没序号就取上一行的序号。

第二步：合并文本

做一列题号，然后引用原表数据，如果问题序号与题号相等，就取对应区域的数据：

=TEXTJOIN(" ",TRUE,IF('Sheet2 (2)'!$Q$2:$Q$477=S2,IF('Sheet2 (2)'!$A$2:$P$477<>"",'Sheet2 (2)'!$A$2:$P$477,""),""))

如果这个区域的数据不为空就是数据本身，否则为空，如果缺少这一步，就会多出很多的0，数组运算默认空值是0。

这样这个问题就解决了。