EXCEL常用技巧分享.docx
- 文档编号:25395784
- 上传时间:2023-06-08
- 格式:DOCX
- 页数:14
- 大小:362.02KB
EXCEL常用技巧分享.docx
《EXCEL常用技巧分享.docx》由会员分享,可在线阅读,更多相关《EXCEL常用技巧分享.docx(14页珍藏版)》请在冰豆网上搜索。
EXCEL常用技巧分享
EXCEL使用技巧:
1.指定单元格指定位置字符的替换
REPLACE函数的功能为使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。
函数:
=REPLACE(A1,1,1"")解:
用“空格”去替换A1列中从第一位开始数的1个数字,
Replace-REPLACE函数语法
REPLACE(old_text,start_num,num_chars,new_text)
Old_text 是要替换其部分字符的文本。
Start_num是要用new_text替换的old_text中字符的位置。
Num_chars是希望REPLACE使用new_text替换old_text中字符的个数。
New_text是要用于替换old_text中字符的文本。
Replace-REPLACE函数示例
A
1
数据
2
abcdefghijk
3
2009
4
123456
公式
说明(结果)
=REPLACE(A2,6,5,"*")
从第六个字符开始,替换5个字符(abcde*k)
=REPLACE(A3,3,2,"10")
用10替换2009的最后两位(2010)
=REPLACE(A4,1,3,"@")
用@替换前三个字符(@456)
2.批量将一列数值修改单位?
3.计算区域中满足给定条件的单元格的个数。
COUNTIF(range,criteria)语法
Range 为需要计算其中满足条件的单元格数目的单元格区域。
Criteria 为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。
例如,条件可以表示为32、"32"、">32"或"apples"。
=countif(指定区域,条件)
如:
=countif(A:
A,A1)
4.excel中查找两列中的相同数据
有A,B两列数据,假设A列数据从第2行到21000行,B列数据比A列的多,从第2行到22000行,在空白列如C列的C2输入:
=IF(ISERROR(VLOOKUP(B2,A$2:
A$21000,1,0)),"不重复","重复")
将上述公式复制或填充到A22000,
再通过自动筛选功能筛选出"重复"的数据.(编辑栏>数据>筛选>自动筛选>在下拉框中选择"重复">将筛选出的"重复"数据复制到其他空白表中.)
5.找出两列表中相同的数据
确保这些数都是数字或都是文本(
假设这些数据在A、B列。
在C1输入=match(B1,A$1:
A$100,)
公式向下复制。
公式返回的数字表示B列的数据在A列中的行数;返回错误表示A列没有。
如果B列真的是文本,公式改为
=match(--B1,A$1:
A$100,)
6.把一串字符改成日期
=TEXT(F4,"#-00-00")
20101015
2010-10-15
7.如何全选工作表中有数据的单元格?
将光标插入任何一个有数据的单元格上。
用快捷键ctrl+A.
备注:
如果光标停在空白处,此快捷键选择的是整下工作表。
CTRL+*是选定当前区域
8.Excel使用技巧:
分列功能的妙用!
我们可以看下面这个例子,原始数据中含有编号,而且随着行数的增加,编号位数也在增加,
图1 Excel表格例子
我们可以分列功能将编号和文本分开。
选中此列,单击“数据”菜单中的“分列”命令,弹出“文本分列向导”对话框。
图2 Excel文本分列向导
在对话框中提供了两类数据分割方法,一是按固定宽度,一是按分隔符号。
将姓与名分开的问题就可以通过选择“固定宽度”项来解决,当前这个例子可以选择“分隔符号”项,单击“下一步”。
图3 Excel文本分列向导
这里要选择分隔符号,因为例子中的编号和文本中间都有“.”,所以我们可以选择“其他”复选框,并在后面的文本框中输入“.”,再单击“下一步”。
9.合并功能。
1)用函数:
Concatenate
假设“方”所在列为A列,“佩娜”所在列为B列,并从第一行开始。
现选中C1单元格,点击菜单插入→函数,在弹出窗口中,函数分类选“文本”,函数名选Concatenate,确定。
在接下来的Text1栏中输入A1,Text2栏中输入B1,确定。
这样A1和B1即合并成C1单元格,为“方佩娜”。
2)用公式
方
佩娜
=M15&N15
方佩娜
方
佩娜
=M16&""&N16
方佩娜
方
佩娜
=M17&","&N17
方,佩娜
备注:
“”“,”等所有符号的引用需切换到英文状态下
3)在某一列中数实前面添加某个固定单词
=“维新康l"&C1
维新康是想要添加的固定单词,C1是指想要合并的数据
10.提取生日,如何通过出生日期计算年龄
居民身份证号码是从事人事管理方面的人员经常接触到的一种特殊数据,原为15位,在21世纪以后都统一升级为18位,其编码规则按排列顺序从左至右依次如下。
15位:
6位数字地址码,6位数字出生日期码,3位数字顺序码。
18位:
6位数字地址码,8位数字出生日期码,3位数字顺序码和1位校验码。
在Excel中,利用提取字符串函数可以提取身份证相关的信息供人事人员做进一步处理,包括以下内容:
●从身份证号码中提取生日
●从身份证号码中提取性别
1)如果同是15位或者同是18位,可以通过“分列”来完现生日的提取。
2) 同时有15位和18位。
假下面的公式将提取出生日信息,如图中D2
有15位身份证的员工都是在21世纪前出生的,因此需要对从15位身份证提取出来的6位生日前置文“19”来生成完整的日期格式,而对于18位身份证则直接取得8位日期值即可
=IF(LEN(B2)=15,19,"")&MID(B2,7,6+(LEN(B2)=18)*2)
11.取一串数字中的某一位
请问如一个数1234.56,取任意一位数字,有函数吗?
比如取出中间的数字3,而不是30
=MID(A1,3,1)假如数据存在A1。
3代表取数超始位,1代表从起使位算起,取1位数。
3)通过出生日期计算年龄
公式:
datedif(开始,结束日期,”结果”)
备注:
”结果”=Y,M,D,YM,YD,MD
12.批量填充相同的数据。
选中单元格:
输入数据:
使用快捷键:
Ctrl+回车:
13.Excel下拉列表的创建方法与应用
1)最简单的下拉列表
也许你不常用这个方法,但它可能是最简单的。
当我们在某一列中前几行已经有输入过一些数据,比如在A1:
A3中,分别输入A、B与C,然后我们要在A4单元输入数据时,按Alt+向下键(↓),(或者鼠标右键,从菜单中选择“从下拉列表中选择…”),就可以出现一个之前输入数据的不重复的下拉列表,可以让我们选择!
这个小技巧可以帮助你快速输入同列中,已经输入过的数据,也可以算是最简单的下拉列表吧!
2)使用数据有效性创建下拉列表
使用数据有效性创建下拉列表,应该是最常用的一种方法。
通过数据有效性可以在单元格中提供一个下拉箭头,单击下拉箭头会弹出下拉列表。
通过这种方法,可以创建出很多很有用的下拉列表,其核心主要是公式的应用。
简单的就是再利用名称引用一个数据区域来创建下拉列表。
具体的实现方法:
在空白单元格输入内容-数据–有效性–设置–序列,在来源中输入包含选项数据的某个多行一列的单元格区域地址。
14.九九乘法表:
公式:
=$A2*B$1(如下图)
步骤:
光标放在B2,输入公式“=”,点击单元格A1,按F4切换到$A2状态(表示锁定A列)*点击单元格B1,按F4切换。
结果:
补充:
某个指定区域需要填充相同的公式时,只需点击带公式的单元格,当光标变成“+”号时,双击。
即可。
15.自定义序列:
按所需顺序排列
工具—选项—自定义序列----输入序列号
16.单元格引用:
绝对引用:
指引用工作表中固定的单元格
绝对引用格式为:
$列标签$行标签(比如:
$A$3,$AB$55)
复制公式时其相对位置不会发生改变
混合引用:
包含一个绝对引用和一个相对引用(比如:
$A3,A$3)
几种引用间的切换
公式中可同时包含相对引用、绝对引用、混合引用三种单元格引用
三种引用之间的快速切换用功能键F4实现
即F4绝对引用定行混合引用定列混合引用相对引用
17,常用快捷键
插入系统日期:
ctrl+;
插入系统时间:
ctrl+shift+;
插入:
长按shift,将光标移动到插入的地址
复制并插入:
长按ctrl+shift,将光标移动到插入的地址
向下重复填充:
Ctrl+D选定一个单元格或选定更多的空白格
向右重复填充:
Ctrl+R
插入超链接:
Ctrl+K
撤销:
Ctrl+Z
引用切换:
F4。
在相对引用(如A1)和绝对引用($A$1)间切换
F11:
快速贴入图表
CTRL+F11:
快速插入工作表,表名:
Macro1
SHIFT+F11:
快速插入工作表,表名:
Sheet
Shift+ctrl+PageDown:
选定当前工作表和下一张工作表
Shift+ctrl+PageUP:
选定当前工作表和上一张工作表
Ctrl+PageDown:
移动到下一张工作表
Ctrl+PageUP:
移动到上一张工作表;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 常用 技巧 分享