【练习题061】答案：定位和查找替换的巧妙应用

 练习题061：将赠书活动评论整理为规则的表格 

请将源表格数据整理成目标表格

注意：

源数据部分评论下面有作者评论，作者评论部分需删除。

源数据

目标表格：

审题

---

我们先来看一下数据的规律，所有人员的评论都分别依次放在同一列，每个人占七行。分别是：点赞数、空行、用户ID、空行、评论、评论时间、空行。

如果每条评论都是这样就很容易，可以复制到WORD，文本转表格（详见《“偷懒”的技术：打造财务Excel达人》第二章第七节的“利用表格工具多列转一列、一列转多列”）。或者是用INDEX+ROW函数引用，或用本文后面要介绍的查找替换的方法。

但是我们仔细看一下数据会发现：

部分评论后面会有“作者回复”，会多出四行。因而，每一条评论并不都是七行，它是不规律的，只有将作者回复的四行删除后，才是有规律的数据。

那如何才能将作者回复的四行删除呢？

它们没有相同的特征，无法通过筛选来一次性删除。唯一相同的特征就是在“作者回复”这一行的前后。

那有没有办法，通过筛选“作者回复”然后将它的前后几行的数据都选定呢？

有，就是利用“定位-引用”，但需要用辅助列来构造。

具体操作

---

Step 1：选中A列，筛选“作者回复”，然后选中B29单元格，再按住SHIFT选择B列筛选出的最后一个单元格，再按Alt+;选定可见单元格，也就是只选中B29:B801单元格区域中筛选出来的那些单元格。

Step 2：点击A1单元格的筛选按钮，清除筛选，注意不要点击任何单元格，此时在上一步中选中的那些可见单元格仍处于选中状态。输入=SUM（，然后选中A28:A31单元格区域（也就是选定将要删除的“作者回复”前后的单元格），按Ctrl+Enter完成批量输入。

注意

上次选定的那些可见单元格中此时已批量输入了类似=SUM(A28:A31)的公式，并且这些单元格仍处于选中状态。

按F5功能键，定位--引用单元格，可选定前面那些单元格所引用的单元格区域。也就是我们要删除的“作者回复”前后的单元格区域。

最后点击右键，删除整行就可以了。

前面的二个步骤，将作者回复的相应行已删除。删除以后，每一条评论就都占了七行，可以用公式或基本操作来转换。

下面介绍将单列转换为多列。

如果大家对函数比较熟悉的话可以用INDEX，

比如要在D列引用A列各用户的赞数，E列为用户ID，F列为评论内容。那么

D1单元格公式为：

=INDEX($A$1:$A$700,(ROW()-1)*7+1)

E1单元格公式为：

=INDEX($A$1:$A$700,(ROW()-1)*7+3)

F1单元格公式为：

=INDEX($A$1:$A$700,(ROW()-1)*7+5)

然后选定D1:F1单元格直接往下填充公式就是了。

象这么有规律的内容，不必使用函数，可先构造序列，然后往下拖动填充，再查找替换。

具体操作见GIF

再用查找替换将A1、A3、A5、A8、A10、A12......等序列的A替换为=A，即可转换为公式，也就是将上面的序列变为引用。