Excel的使用心得与技巧

函数嵌套实现不带http开头的url的接口类型分类

=IF(COUNT(SEARCH("http*",G27)),LEFT(G27,FIND(":",G27,1)-1),"https")

判断单元格是否包含某个字符

=IF(AND(ISERROR(FIND("刘德华",A1)),ISERROR(FIND("张学友",A1))),"F","T")

获取冒号分割后的内容

=LEFT(F3,FIND(":",F3,1)-1)

Excel下拉菜单并去除空白值

添加下拉菜单,请选择菜单数据 – 数据验证

这里写图片描述

然后去除空值

=OFFSET($A$1,,,COUNTA($A:$A))

解决单元格中不仅可以输入下拉菜单预选内容,也可以输入其他任意值的问题:
选择出错警告对话框,取消勾选输入无效数据时显示出错警告

判断某列的值是否在另一列中

判断F列相对应的行是否存在于C列中

=IF(COUNTIF(C:C,F2)>0,"存在","不在。。。")

查找某列是否有空值

使用if函数

=IF(H3="","--注意此单元格为空--","非空")

然后就可以很显眼的查看该列是否有空值了

批量填充空值为0

需求是10行单元格内容有4行是空值

1
2

3

1

3

2

如何快速的把4行空值填充为0
答案是使用定位功能。
具体见菜单 – 编辑 – 查找 – 定位
这里写图片描述
打开定位选框
这里写图片描述

选择定位条件,勾选空值
这里写图片描述

接着输入0,按下ctrlol + 回车即可

批量添加超链接

比如有一个单元格的内容是 http://t.cn/123
正常情况下它是没法直接点击跳转的
这时我们需要使用函数来实现带上超链接的功能

C2 =HYPERLINK(C1,C1)

这样C2单元格的内容就会带上可以点击的超链接

日期的转换

形如把 20180601 转换成 2018/06/01
刚开始是用修改单元格格式的方法来做
这里写图片描述
发现可以转换成带-号的格式,却没法转换成/的格式
于是改用了TEXT函数即可

=TEXT(A1,"0\/00\/00")

\代表强制加入的意思
如何把日期2017/7/21的形式转换为20170721等这几种形式?

字符串的截取

源数据A1

2016-11-17-----2018-01-22www.uf-club.com
2017-05-04-----2017-05-04web113096.cyberidc.com

目标数据

www.uf-club.com
web113096.cyberidc.com

方法一

看起来很简单,我这里使用了两个函数来实现
首先左边截取B1

LEFT(A1,25)

然后替换空字符串C1

REPLACE(A1,1,25,"")

PS:Excel中好像没有java中的substring函数,没法直接截取

方法二(推荐)

mid函数即可

MID(A1,26,50)

其中A1代表源数据,26代表从第26个字符开始截取,这里刚好是日期结束网址开始处第索引位置,50只是一个概数,就是为了包含不同长度的url,大一点能截取的全面一点。

去除字符串中的中文

使用最多的就是left函数

B1 = LEFT(A1,LEN(A1)*2-LENB(A1))

其中A1就是包含中文和英文的字符串单元格
测试下来发现并没有去除掉中文,于是搜了搜自定义函数

Function tet(s As String) As String 
    Dim str As String 
    str = "" 
    For i = 1 To Len(s) 
        If Strings.Asc(Mid(s, i, 1)) > 0 Then 
            str = str + Mid(s, i, 1) 
        End If 
    Next i 
    tet = str 
End Function

以上函数代码来源于:
Excel去除中文,只留英文和数字
直接在新单元格中调用即可,经测试,可用。

添加自定义函数

打开菜单 工具 宏 Visual Basic 编辑器
这里写图片描述
如果第一次使用这个编辑器,左侧菜单栏那里可能没有模块这个子菜单
这时需要在根菜单VBAProject处右键选择插入 模块即可
然后选中模块,把函数内容输入进去,直接关闭即可调用该自定义函数
使用自定义函数后,复制单元格记得菜单 选择性粘贴 文本或者值,不然把原始数据删掉的话,函数后的数据也会跟着删掉

Excel文件打开后,单击启用内容,报错Visual Basic for Applications(VBA)宏已经损坏且已被删除。
必须保存为.xlsm这样的格式才能有效保存下来代码。

去除超链接

有时从网页复制过来的数据上自带超链接,这时无意中点一下就会打开浏览器,一种简单的方式可以使用格式刷刷以下正常的单元格,再改变这个带超链接的单元格格式,但好像去不掉超链接,只是去掉了超链接样式

方法一

就是先选中要清除超链接的所有单元格,再选择菜单中的清除 删除超链接选项
这里写图片描述

方法二(推荐)

是使用快捷键 command + k
这里写图片描述

自动更正

这里写图片描述

去掉图示这个勾选,作用就是在单元格输入超链接内容不会被自动转为可点击的超链接,但是复制的超链接还是一样能点击跳转

vlookup()函数的使用

a,看一下数据源,目标是在J列填充B列的数字,并且F列对应于A列
这里写图片描述
b,光标放在J2单元格,输入以下函数

=VLOOKUP(F2,A:B,2,0)

简单讲解:
1,选择要填充的列的第一行,输入=vloolup
2,第一个参数填入F2,表示要去和第二个参数的第一列去匹配的一列
3,第二个参数选择用鼠标选择数据源两列,也就是AB两列
4,第三个参数填入2,表示要填充的数据来源于第二个参数选择的第二列
5,第四个参数填入0,表示精确匹配
6,回车,双击黑色十字,单列填充完毕,注意导入的数据格式要是文本

隐藏显示列

1,右键隐藏某一列
2,显示的话,比如D列被隐藏了,那么就选中C列到E列,右键取消隐藏就行

分列功能

1,分列
2,使用公式后如何复制,使用选择性粘贴,不要选择全部,选择值就好

冻结前两行

选择第三行,点击窗口 – 拆分,这时前两行就被置顶了,但是往下滑的时候前两行又多处重复行了

阅读更多
版权声明:本文为博主原创文章,转载请注明出处。 https://blog.csdn.net/diyangxia/article/details/80112430
个人分类: 工具软件
上一篇Gps带你跑出你想要的图案
下一篇MyBatis的初步使用
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭