excel中以指定分隔符从右往左截取字符&多重判断

从A串中提取从"."开始的字符串B,可以使用find函数来对"."的首次出现进行定位,这类似于各种语言中的indexOf功能。find是从左往右查找的,在EXCEL中并没有从右往左查找,类似lastIndexOf的函数。


在EXCEL想要从右往左截取字符,可使用公式:TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",LEN(A1))),LEN(A1)))。


例:已知A1=http://www.163.com/sports/Arsenal.html,要获取Arsenal.html字符串。


公式=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))),LEN(A1)))。


解释:
1. REPT(" ",LEN(A1))的意思是得到一空格串,长度为A1的长度。
    REPT函数可可以按照定义的次数重复现实文本,相当于复制文本。


2. SUBSTITUTE(A1,"/",REPT(" ",LEN(A1)))
     将A1中的字符"/"用多个空格串(第1步所得)来替换。此时,可获得的文本如下:

http:                                                                            www.163.com                                      sports                                      Arsenal.html


   SUBSTITUTE(text,old_text,new_text,instance_num) 函数是在文本字符串中用 new_text 替代 old_text。


3. 在以指定空格串代替所有分隔符后,就可以使用Right函数,从右往左取子串了。即
    =RIGHT(SUBSTITUTE(A2,"/",REPT(" ",LEN(A2))),LEN(A2))
    此时得到的字串如:

                                      Arsenal.html

 

4. 最后使用Trim函数将空格删除,可得到结果:Arsenal.html
    =TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",LEN(A2))),LEN(A2)))

 

来源:http://blog.sina.com.cn/s/blog_67532f7c01019gvk.html

 

面对多重判断,excel有四法

 

一般来说,我们经常会碰到这样的问题,即

面对多重判断,excel有四法

 

需要按照这张mapping表进行数值判断,下面有四种方法:

 

1 经典法:if多重判断

这个就不用多说了,一般来说,我们这个函数一般写为:

面对多重判断,excel有四法

 

当然这里面and与函数有点多余,所以我们也可以写为

面对多重判断,excel有四法

 

当时这个也有缺点,因为经常很多人会把临界点搞错,比如A1=5000的情况是D,还是C,所以会经常弄混的。最重要的,多重只能到7重,而且重数多了,if会写的非常乱

 

2 vlookup函数妙用法

啊,vlookup函数呀,这好像和多重判断不搭界呀,其实,vlookup函数有精确匹配和近似匹配,我们一般用的是精确匹配,而这里用的是近似匹配(原理在于近似匹配采用的是返回小于 lookup_value 的最大数值,所以一定要升序排列哦)。

 

 面对多重判断,excel有四法

而这里函数写为

=VLOOKUP(E2,B2:D5,3,TRUE)

就能返回各个相应的值,这个比if函数简单,而且可以支持7重以上。

但是缺点是一旦不是>=,而是>的下限形式,会比较麻烦。

 

3 VBA进阶1:select case函数

大家去查查外面的VBA书籍,这个说的比较多,举个例子吧

我要对所有的上市公司进行判断,要按照利润区间分为

面对多重判断,excel有四法

 

这个大家就不要用if写了,9重的if第一不支持(号称excel 2007 if函数只支持7重),第二即使写出来,过了一段时间,自己写的自己也不认识了。所以这边用VBA写了一个例子

Function profitrank(income) As String

Select Case income

    Case Is <= 0

        profitrank = "<=0"

    Case Is <= 5000

        profitrank = "0-5千万"

    Case Is <= 10000

        profitrank = "5千万-1亿"

    Case Is <= 20000

        profitrank = "1亿-2亿"

    Case Is <= 30000

        profitrank = "2亿-3亿"

    Case Is <= 40000

        profitrank = "3亿-4亿"

    Case Is <= 50000

        profitrank = "4亿-5亿"

    Case Is <= 100000

        profitrank = "5亿-10亿"

    Case Else

        profitrank = ">10亿"

    End Select

End Function

这个函数,我定义为profitrank函数,这个大家可以copy到自己的excel中使用

 

4 VBA进阶2:switch函数

用过access的人都知道,它里面的switch比较好用,语法就是

Switch(条件1,”结论1”,条件2,”结论2”,条件3,”结论3”,…..)

比if函数好多了,可惜的是excel工作表竟然不知道switch函数,但是老天有眼,excel的VBA函数却可以支持switch,所以有些高手就做了些为国为名的好事(这里得感谢外国高手们,这帮人呀,牛)

其函数可以直接写作

=Switch2(A1<1000,"D",A1<5000,"C",A1<10000,"B",TRUE,"A")

 

比较if函数,是不是即简洁又清晰

 

 面对多重判断,excel有四法

目前代码如下:

Function Switch2(Test1 As String, Result1 As String, _
Optional Test2 As String, Optional Result2 As String, _
Optional Test3 As String, Optional Result3 As String, _
Optional Test4 As String, Optional Result4 As String, _
Optional Test5 As String, Optional Result5 As String, _
Optional Test6 As String, Optional Result6 As String, _
Optional Test7 As String, Optional Result7 As String, _
Optional Test8 As String, Optional Result8 As String, _
Optional Test9 As String, Optional Result9 As String, _
Optional Test10 As String, Optional Result10 As String, _
Optional Test11 As String, Optional Result11 As String, _
Optional Test12 As String, Optional Result12 As String, _
Optional Test13 As String, Optional Result13 As String, _
Optional Test14 As String, Optional Result14 As String)

Switch2 = Switch(Test1, Result1, _
Test2, Result2, _
Test3, Result3, _
Test4, Result4, _
Test5, Result5, _
Test6, Result6, _
Test7, Result7, _
Test8, Result8, _
Test9, Result9, _
Test10, Result10, _
Test11, Result11, _
Test12, Result12, _
Test13, Result13, _
Test14, Result14)
End Function

这个大家也可以copy到excel中,非常方便,目前代码里面是支持14个条件的,大家可以任意扩充)

 

5 结论:if如果做多重判断,是不方便了,如果考虑到简单实用,建议用vlookup,如果还想多做VBA的学习,那还是用后面的两个VBA方法。


来源:http://blog.sina.com.cn/s/blog_603d40f70100esta.html

 

其他:1、excel自定义函数添加,在Excel工作表界面下按<ALT+F11>组合键打开VBA编辑器,单击“插入”→“模块”,并在右边的代码窗口输入自定义函数的程序代码。单击窗口右上角的“关闭”按钮关闭VBA编辑器,返回excel工作表界面就可使用。

          来源:http://www.ittribalwo.com/article/1274.html

 

          2、在使用公式或函数后,为何双击单元格右下角“十”字,没有作用?

                 如果因为特殊原因 不可以双击填充 则选择要填充的区域  按下ctrl+D 进行填充。

          来源:http://www.excelpx.com/thread-329199-1-1.html

  • 1
    点赞
  • 12
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
Excel是一款功能强大的电子表格软件,其中有很多实用的函数可以方便我们进行数字、文本和日期等信息处理。而截取最后一个分隔符右边的字符串是我们需要进行字符串处理时经常遇到的需求。在Excel中,我们可以使用一些函数来实现这个功能。 1. 使用RIGHT函数 RIGHT函数Excel中计算最右边字符函数,我们可以利用它返回从最右边字符开始向指定个数的字符。语法如下: RIGHT(text,num_chars) 其中text是需要截取的文本,num_chars是需要截取字符数量。我们可以使用FIND函数查找最后一个分隔符所在的位置,然后再利用RIGHT函数进行截取。具体实现如下: =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"/","*",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))) 其中,A1是需要截取字符串。这个公式通过FIND函数和SUBSTITUTE函数找到最后一个分隔符的位置,然后用LEN函数求出字符串总长度减去最后一个分隔符的位置,作为RIGHT函数的num_chars参数。这样就可以截取最后一个分隔符右边的字符串了。 2. 使用TEXT函数 TEXT函数可以将一个数值或日期格式化为字符串,我们可以利用它进行字符串处理。在这个场景下,我们可以先将字符串逆序,并使用TEXT函数截取分隔符边的字符,然后再逆序回来即可。具体实现如下: =REVERSE(TEXT(LEFT(REVERSE(A1),FIND("/",REVERSE(A1))-1),"$0.00")) 其中,A1是需要截取字符串。这个公式先将字符串逆序,用TEXT函数截取逆序后的字符串的最后一个分隔符边的字符(上述公式中为/),然后再逆序回来。这样就可以得到最后一个分隔符右边的字符串了。 总之,截取最后一个分隔符右边的字符串是Excel中常见的需求之一,通过使用一些函数,我们可以轻松地实现这个功能。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值