98%的人都不会使用的函数公式 EXCEL中的各种公式单个使用时都不复杂,但如何将公式灵活运用才是最终目的,如何将多个函数进行组合使用,这是很多人都不擅长的地方。本文通过一个实际展示一下EXCEL多函数组合使用的技巧,希望能给大家带来一些借鉴。 如下图所示,资料收集时,B列中的单元格包括了多种信息,现在希望将B列中的信息拆开,用右边的形式来表示。 解决方法: 选中D2:G5单元格,输入以下公式后按'CTRL + ENTER'组合键: = SUBSTITUTE(TRIM(MID(SUBSTITUTE($B2,CHAR(10),REPT(' ',99)),1+(COLUMN(A:A)-1)*99,99)),D$1&':',) 处理过程如下所示: 这个组合公式有点复杂,我们可以采用剥洋葱的方法一点点地分析它的处理过程。 对字符串进行处理时,一般都是处理单行字符串,所以要想办法将单元格的字符先处理成单行,再想办法进行各种替换处理。这里使用了CHAR、REPT、COLUMN、MID、TRIM、SUBSTITUTE等组合函数。 我们先从REPT和CHAR进行分析: CHAR(10)代表回车键; REPT(' ',99)的返回值是99个' '; 然后用SUBSTITUTE进行初步的替换处理:= SUBSTITUTE($B2,CHAR(10),REPT(' ',99)),这个函数表示将单元格B2中的回车键用多个''来替换,将B2处理成一个单行字符串,返回值为: '项目名称: 新建驴肉工厂 项目经理:鲁达 投资额:500万 地址:太平洋'。 (COLUMN(A:A)用来取得列数的值 1+(COLUMN(A:A)-1)*99返回值为1 MID(SUBSTITUTE($B2,CHAR(10),REPT(' ',99)),1+(COLUMN(A:A)-1)*99,99)表示从上面处理过的字符串的第1个字符开始返回99个字符,返回值为'项目名称: 新建驴肉工厂 '。 再用TRIM处理字符串的空格: = TRIM(MID(SUBSTITUTE($B2,CHAR(10),REPT(' ',99)),1+(COLUMN(A:A)-1)*99,99)) 表示删除上面字符串的空格,返回值为'项目名称: 新建驴肉工厂' 最后再用一次SUBSTITUTE: SUBSTITUTE(TRIM(MID(SUBSTITUTE($B2,CHAR(10),REPT(' ',99)),1+(COLUMN(A:A)-1)*99,99)),D$1&':',) 表示将D1中的'项目名称:'用空值代替。 其他单元格中的公式分析过程同上。 熟练掌握了单个函数公式的使用,尝试将这些函数组合起来,学以致用,不断总结,终会成为EXCEL高手的。 |
|