Excel文本排行的问题

2019-06-18 工作记录

初始

手头有一批数据要处理,表头和内容不太匹配。

  • 原始数据大概是这个样子
项目 价格 类别 描述
尺子 描述:量东西用的 价格:¥2 类别:学习
本子 描述:写作业用 价格:¥8
Tip 价格:¥18 描述:hello world 类别:娱乐
  • 目标期望是这个样子
项目 价格 类别 描述
尺子 价格:¥2 类别:学习 描述:量东西用的
本子 价格:¥8 描述:写作业用
Tip 价格:¥18 类别:娱乐 描述:hello world

试错的过程

刚开始想到的是使用excel的排序进行处理
首先进行的是转置
然后对每个单元格按照表头关键字添加了数字,进行辅助排序
等进行排序的时候,发现只有前面几列数量排序了,而后面的完全没有动

针对排序不动的问题,试了半天,也没有试出个所以然来
数据量太大,排查起来也不太方便,到后面排查出问题所在也不知还要多长时间
这个方向上果断选择了放弃

新的方法

其实之前使用过这个方法,但操作起来太繁琐,一般不太愿意尝试。
使用Excel排序的办法受阻后,第二天想到了这个办法

  • 1.准备好源数据
项目 价格 类别 描述
尺子 描述:量东西用的 价格:¥2 类别:学习
本子 描述:写作业用 价格:¥8
Tip 价格:¥18 描述:hello world 类别:娱乐
  • 2.去除内容,保留关键字 (目的是做位置映射)

使用替换(关键字*)或其他方法都可以

项目 价格 类别 描述
尺子 描述: 价格: 类别:
本子 描述: 价格:
Tip 价格: 描述: 类别:
  • 3. 获得关键字的位置

新建个Sheet,将源数据的表头和第一列复制过来

在新Sheet中B2中填写下面的公式,随机填充所有格子:
=MATCH(B$1,'去除内容,保留关键字'!2:2,0)
上述公式得到的结果为数字:

项目 价格: 类别: 描述:
尺子 3 4 2
本子 3 #N/A 2
Tip 2 4 3
  • 4. 将关键字的位置转化为Excel单元格的位置

同样新建个Sheet,将源数据的表头和第一列复制过来

同第3步一样,在新Sheet中按行填写如下公式:
=SUBSTITUTE(ADDRESS(1,获得关键字的位置!B2,4),"1","")&ROW()
上述公式得到的结果为字母列、数字行:

项目 价格: 类别: 描述:
尺子 C2 D2 B2
本子 C3 #N/A B3
Tip B4 D4 C4
  • 5.根据字母列、数字行重新取源文件的内容

公式如下:
=INDIRECT("源数据!"&转化为excel位置!B2)

最终输出结果:

项目 价格: 类别: 描述:
尺子 价格:¥2 类别:学习 描述:量东西用的
本子 价格:¥8 #N/A 描述:写作业用
Tip 价格:¥18 类别:娱乐 描述:hello world

事后总结

新的办法有点复杂,最关键的是:
1.内容中带有关键字,没有的话就没有办法区分的
2.使用关键字作映射,这个可能理解起来有点麻烦

能使用Excel自带的排序是最好的
但是关于excel为什么不能正确排序
由于数据量大、单元格内容复杂等原因实在不好排查
现在是没有时间去弄清楚的,最重要的是可以解决问题。

新的办法是可以将3、4、5全部合并的
但合并后公式太长,阅读和理解起来都太复杂
个人也不是太喜欢
这种分步骤的办法还有另外一个好处是排错方便
某一步出错了,打开相应的Sheet作修改就好了

Comments
Write a Comment