Excel办公常用技巧.docx
- 文档编号:3813343
- 上传时间:2022-11-25
- 格式:DOCX
- 页数:13
- 大小:77.35KB
Excel办公常用技巧.docx
《Excel办公常用技巧.docx》由会员分享,可在线阅读,更多相关《Excel办公常用技巧.docx(13页珍藏版)》请在冰豆网上搜索。
Excel办公常用技巧
Excel常用的一些使用技巧
以下是我收集整理的一些电子表格的使用技巧和方法,希望这些方法能够为各位同事和朋友们带来一些方便。
1编辑技巧
(1)分数的输入
如果直接输入“1/5",系统会将其变为“1月5日",解决办法是:
先输入“0",然后输入空格,再输入分数“1/5"。
(2)序列“001"的输入
如果直接输入“001",系统会自动判断001为数据1,解决办法是:
首先输入“'"(西文单引号),然后输入“001"。
(3)日期的输入
如果要输入“4月5日",直接输入“4/5",再敲回车就行了。
如果要输入当前日期,按一下“Ctrl+;"键。
(4)多张工作表中输入相同的内容
几个工作表中同一位置填入同一数据时,可以选中一张工作表,然后按住Ctrl键,再单击窗口左下角的Sheet1、Sheet2......来直接选择需要输入相同内容的多个工作表,接着在其中的任意一个工作表中输入这些相同的数据,此时这些数据会自动出现在选中的其它工作表之中。
输入完毕之后,再次按下键盘上的Ctrl键,然后使用鼠标左键单击所选择的多个工作表,解除这些工作表的联系,否则在一张表单中输入的数据会接着出现在选中的其它工作表内。
(5)不连续单元格填充同一数据
选中一个单元格,按住Ctrl键,用鼠标单击其他单元格,就将这些单元格全部都选中了。
在编辑区中输入数据,然后按住Ctrl键,同时敲一下回车,在所有选中的单元格中都出现了这一数据。
(6)在单元格中显示公式
word中“Ctrl+`”,或公式,显示公式;。
2、单元格内容的合并
在C行后插入一个空列(如果D列没有内容,就直接在D列操作),在D1中输入“=B1&C1",D1列的内容就是B、C两列的和了。
3、条件显示
我们知道,利用If函数,可以实现按照条件显示。
一个常用的例子,就是教师在统计学生成绩时,希望输入60以下的分数时,能显示为“不及格";输入60以上的分数时,显示为“及格"。
这样的效果,利用IF函数可以很方便地实现。
假设成绩在A2单元格中,判断结果在A3单元格中。
那么在A3单元格中输入公式:
=if(A2<60,“不及格",“及格")同时,在IF函数中还可以嵌套IF函数或其它函数。
例如,如果输入:
=if(A2<60,“不及格",if(A2<=90,“及格",“优秀"))就把成绩分成了三个等级。
如果输入 =if(A2<60,“差",if(A2<=70,“中",if(A2<90,“良",“优")))就把成绩分为了四个等级。
再比如,公式:
=if(SUM(A1:
A5>0,SUM(A1:
A5),0)此式就利用了嵌套函数,意思是,当A1至A5的和大于0时,返回这个值,如果小于0,那么就返回0。
还有一点要提醒你注意:
以上的符号均为半角,而且IF与括号之间也不能有空格。
5、批量删除空行
我们可以利用“自动筛选"功能,把空行全部找到,然后一次性删除。
做法:
先在表中插入新的一个空行,然后按下Ctrl+A键,选择整个工作表,用鼠标单击“数据"菜单,选择“筛选"项中的“自动筛选"命令。
这时在每一列的顶部,都出现一个下拉列表框,在典型列的下拉列表框中选择“空白",直到页面内已看不到数据为止。
word中如何快速插入时间:
方法一:
点击word文档工具栏上的“日期和时间”,可以直接插入。
格式可以自己选择。
当然你也可以勾选上“自动更新”,那么每次你打开文档后显示的都是当前最新日期。
方法二:
键盘上同时按下Alt,Shift,D,可以以域的形式插入时间和日期。
右键编辑它,以后可以随时更新域。
方法三:
利用word自带的提示功能添加日期,输入年份如2014,会提示按“ENTER”键自动补上日期。
EXCEL表格中如何屏蔽公式在编辑栏中的显示
选中目标单元格,按鼠标右键,选中【设置单元格格式】对话框。
在弹出的【设置单元格格式】对话框中,选择【保护】选项中的【隐藏】选框。
选择【审阅】选项下的【保护工作表】选项。
在弹出的【保护工作表】中输入自己设置的密码,按【确定】按钮。
在弹出的【确认密码】中输入刚刚设置的密码,单击【确定】按钮即可。
WPS表格中重复数据如何显示出来
先选定数据
找到“数据”选项,在数据的下拉菜单中看到“高亮重复项”,点击“高亮重复项”
Excel如何去除单元格中的空格
Trim()函数用来删除文本的前导空格和尾部空格。
Trim(D8)用来删除D8单元格中文本的前后空格。
substitute()函数用来去除中间的空格
怎样提取EXCEL单元格中的部分内容?
提取公式?
举例,要从excel第一列中截取CD的数值7.382,即A1单元格中第4位参数到第8位参数之间位数为5位(小数点也占一位)的一段参数。
(cd:
7.382mg/Lm)
在一空白单元格里输入MID公式,=MID(text,start-num,num-chars)。
在这里的例子里,text就是要截取的单元格地址即A1,start-num是A1中被截取的7.382所在的开始位数,即第4位,num-chars即7.382的总位数,共5位,所以在空白单元格内输入MID公式,=MID(A1,4,5)。
按ENTER回车,即可得到7.382。
如果要从单元格内左起第1位开始截取字符,则用LEFT公式,如图所示,即=LEFT(A1,10),意思是提取A1单元格中从左起第1位字符起到第10位字符结束,即截取了CD:
7.382mg。
同理,如果要从单元格内右起第1位开始截取字符,则用RIGHT公式,如图所示,即=RIGHT(A1,15),意思是提取A1单元格中从右起第1位字符起到第15位字符结束,即截取了TTX:
0.2456mg/L。
Excel实战用法(精)
让不同类型数据用不同颜色显示
“格式→条件格式”命令,打开“条件格式”对话框。
建立分类下拉列表填充项
执行“数据→有效性”命令,打开“数据有效性”对话框。
在“设置”标签中,单击“允许”右侧的下拉按钮,选中“序列”选项,在下面的“来源”方框中,输入“工业企业”,“商业企业”,“个体企业”……序列(各元素之间用英文逗号隔开),确定退出。
让数据按需排序
如果你要将员工按其所在的部门进行排序,这些部门名称既的有关信息不是按拼音顺序,也不是按笔画顺序,怎么办?
可采用自定义序列来排序。
1.执行“格式→选项”命令,打开“选项”对话框,进入“自定义序列”标签中,在“输入序列”下面的方框中输入部门排序的序列(如“机关,车队,一车间,二车间,三车间”等),单击“添加”和“确定”按钮退出。
2.选中“部门”列中任意一个单元格,执行“数据→排序”命令,打开“排序”对话框,单击“选项”按钮,弹出“排序选项”对话框,按其中的下拉按钮,选中刚才自定义的序列,按两次“确定”按钮返回,所有数据就按要求进行了排序。
把数据彻底隐藏起来
1.选中需要隐藏内容的单元格(区域),执行“格式→单元格”命令,打开“单元格格式”对话框,在“数字”标签的“分类”下面选中“自定义”选项,然后在右边“类型”下面的方框中输入“;;;”(三个英文状态下的分号)。
2.再切换到“保护”标签下,选中其中的“隐藏”选项,按“确定”按钮退出。
3.执行“工具→保护→保护工作表”命令,打开“保护工作表”对话框,设置好密码后,“确定”返回。
提示:
在“保护”标签下,请不要清除“锁定”前面复选框中的“∨”号,这样可以防止别人删除你隐藏起来的数据。
让“自动更正”输入统一的文本
1.执行“工具→自动更正”命令,打开“自动更正”对话框。
2.在“替换”下面的方框中输入“pcw”(也可以是其他字符,“pcw”用小写),在“替换为”下面的方框中输入“《电脑报》”,再单击“添加”和“确定”按钮。
3.以后如果需要输入上述文本时,只要输入“pcw”字符?
组合函数:
CONCATENATE
如何把文件夹内文件名批量导出到txt生成清单
第一步,新建一个txt格式的记事本文件。
第二步,在记事本文件中输入:
DIR*.*/B>LIST.TXT
第三步,将此记事本文件后辍名,由txt改为bat。
会弹出重命名对话框,单击“是”。
第四步,双击文件“新建文本文档.bat”即可生成list.txt文件。
打开txt文件就可以看到当前文件夹内的所有文件名列表。
(温馨提示:
你也可以把文件“新建文本文档.bat”放在其他文件夹里运行,获取当前文件夹下面的所有文件名哦!
)
控制特定单元格输入文本的长度
单击“数据”菜单的“有效性”选项。
在“设置”-“有效性条件”-“允许”-“文本长度”。
然后在“数据”下拉菜单中选择“等于”,且“长度”为“4”。
成组填充多张表格的固定单元格
单击第一个工作表的标签名“Sheet1”,然后按住Shift键,单击最后一张表格的标签名“Sheet3”(如果我们想关联的表格不在一起,可以按住Ctrl键进行点选)。
在需要一次输入多张表格内容的单元格中随便写点什么,我们发现,“工作组”中所有表格的同一位置都显示出相应内容了。
我们需要将多张表格中相同位置的数据统一改变格式该怎么办呢?
首先,我们得改变第一张表格的数据格式,再单击“编辑”菜单的“填充”选项,然后在其子菜单中选择“至同组工作表”。
这时,Excel会弹出“填充成组工作表”的对话框,在这里我们选择“格式”一项,点“确定”后,同组中所有表格该位置的数据格式都改变了。
改变文本的大小写
”=UPPER(源数据格)”,将文本全部转换为大写;“=LOWER(源数据格)”,将文本全部转换成小写;“=PROPER(源数据格)”,将文本转换成“适当”的大小写,如让每个单词的首字母为大写等。
提取字符串中的特定字符
如果我们想快速从A4单元格中提取称谓的话,最好使用“=RIGHT(源数据格,提取的字符数)”函数,它表示“从A4单元格最右侧的字符开始提取2个字符”输入到此位置。
当然,如果你想提取姓名的话,则要使用“=LEFT(源数据格,提取的字符数)”函数了。
还有一种情况,我们不从左右两端开始,而是直接从数据中间提取几个字符。
比如我们要想从A5单元格中提取“武汉”两个字时,就只须在目标单元格中输入“=MID(A5,4,2)”就可以了。
意思是:
在A5单元格中提取第4个字符后的两个字符,也就是第4和第5两个字。
Excel公式大全
1、查找重复内容公式:
假设数字在A列,数字由第二行开始,在B2输入公式:
=IF(COUNTIF(A:
A,A2)>1,"重复","")把鼠标放在B2单元格的右下角变成黑十字时按鼠标左键向下拉,再以B列排序或筛选,将标有“重复”的行删除即可。
点击工具栏中的开始→条件格式→突出显示单元格规则→重复值
2、用出生年月来计算年龄公式:
=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式:
=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。
1、求和:
=SUM(K2:
K56) ——对K2到K56这一区域进行求和;
2、平均数:
=AVERAGE(K2:
K56) ——对K2K56这一区域求平均数;
3、排名:
=RANK(K2,K$2:
K$56) ——对55名学生的成绩进行排名;
4、等级:
=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))
5、学期总评:
=K2*0.3+M2*0.3+N2*0.4——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩;
6、最高分:
=MAX(K2:
K56)——求K2到K56区域(55名学生)的最高分;
7、最低分:
=MIN(K2:
K56)——求K2到K56区域(55名学生)的最低分;
14、根据出生日期自动计算周岁:
=TRUNC((DAYS360(D3,NOW()))/360,0)
15、在Word中三个小窍门:
①连续输入三个“~”可得一条波浪线。
②连续输入三个“-”可得一条直线。
连续输入三个“=”可得一条双直线。
1、excel中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色比如:
点击菜单栏-开始-样式模块中的条件格式-新建规则-仅用公式确定要设置格式的单元格。
二、EXCEL中如何控制每列数据的长度并避免重复录入
用数据有效性定义数据长度。
用鼠标选定你要输入的数据范围,点"数据"->"有效性"->"设置","有效性条件"设成"允许""文本长度""等于""5"(具体条件可根据你的需要改变)。
还可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点"确定"。
一个工作薄中有许多工作表如何快速整理出一个目录工作表
1、用宏3.0取出各工作表的名称,方法:
Ctrl+F3出现自定义名称对话框,取名为X,在“引用位置”框中输入:
=MID(GET.WORKBOOK
(1),FIND("]",GET.WORKBOOK
(1))+1,100)
确定
2、用HYPERLINK函数批量插入连接,方法:
在目录工作表(一般为第一个sheet)的A2单元格输入公式:
=HYPERLINK("#'"&INDEX(X,ROW())&"'!
A1",INDEX(X,ROW()))
将公式向下填充,直到出错为止,目录就生成了
第1步文档说明:
下面文档共有17个sheet页,其中sheet2~sheet17是内容页,sheet1作为目录页,根据内容页数量在目录页建立序号01~16
第2步选中B2单元格,同时按住键盘上的“Ctrl+k”,弹出【插入超链接】
第3步选择“本文档中的位置”,用鼠标选中“第一章”,单击确定
第4步重复以上步骤,完成所有目录链接
第1步单击B1单元格,切换到“公式”选项卡,单击“定义名称”,弹出【新建名称】对话框,在“名称”文本框中输入“目录”,在“引用位置”文本框输入以下公式:
=INDEX(GET.WORKBOOK
(1),ROW(A1))&T(NOW())
注意:
GET.WORKBOOK函数是宏表函数,可以提取当前工作簿中的所有工作表名,需要先定义名称后使用。
第2步在B1单元格中输入公式:
=IFERROR(HYPERLINK(目录&"!
A1",MID(目录,FIND("]",目录)+1,99)),"")
第3步双击B1右下角向下复制,如下图效果,单击目录中的工作表名称,就会自动跳转到相应工作表。
我们只要在做好的目录中鼠标点击,就会快速到达所在表格位置。
excel中如何根据身份证号计算年龄
例如,身份证在A2,在B2输入公式:
=(NOW()-DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)))/365
NOW()提取当前日期
DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)从身份证提取出生日期
一年当365天计算,实际年龄结果可能会可能会有几天的误差。
办公技巧
Word绝招:
一、输入三个“=”,回车,得到一条双直线;
二、输入三个“~”,回车,得到一条波浪线;
三、输入三个“*”或“-”或“#”,回车,惊喜多多;
在单元格内输入=now() 显示日期
在单元格内输入=CHOOSE(WEEKDAY(I3,2),"星期一","星期二","星期三","星期四","星期五","星期六","星期日") 显示星期几
excel怎么把日期显示为星期几
选中A1,鼠标右键,选择【设置单元格格式】,在【自定义】中输入aaaa,这样A1单元格将返回如“星期三”这种表示方式。
选中A1,鼠标右键,选择【设置单元格格式】,在【自定义】中输入dddd,这样A1单元格将返回如“Wednesday”这种英文表示方式。
选中A1,鼠标右键,选择【设置单元格格式】,在【自定义】中输入“周aaa”,这样A1单元格将返回如“周三”这种表示方式。
如果用公式=TEXT(A1,"aaaa"),一样可以达到更改格式显示的目的。
Excel表格“今天星期几”函数
返回中文的“星期几”=TEXT(TODAY(),"aaaa")
返回英文的“星期几”=TEXT(TODAY(),"dddd")
多少天之后是“星期几”,只要在公式中“today()+n”即可
从身份证号码判断性别
在要计算性别的单元格b2输入公式IF(MOD(MID(A2,17,1),2)=1,"男","女")
MID(B2,17,1)是截取第17位的字符,MOD(MID(B2,17,1),2)函数是取除2的余数,MOD(MID(B2,17,1),2)=1判断是否等于1,如果等于1是奇数
为男,否则为女。
如何在Excel中不允许输入重复值(数据)?
假如要在A2:
A10单元格区域中禁止录入重复的数据
可以选中单元格区域A2:
A10,然后单击“数据”选项卡下的“数据工具”组中的“数据有效性”命令按钮。
在弹出的“数据有效性”对话框下的“设置”选项卡“有效性条件允许”中设置“自定义”,然后在公式区域选择框中输入公式=COUNTIF
($A$2:
$A$10,A2)=1,然后单击“确定”按钮。
原理分析:
通过用COUNTIF函数判断指定的单元格区域中的数据的个数只能是1个,不能多于1,假如满足条件则可以输入,不满足条件就禁止
输入。
如果要在多列的单元格区域中禁止录入重复值,原理与以上的步骤类似,只是将统计个数的单元格区域改为多行多列即可。
如下图所示,公式
为
=COUNTIF($A$2:
$D$9,A2)=1
数据有效性:
控制单元格输入文本长度
选中区域---数据---数据有效性---允许---文本长度
可防止身份证/银行卡号/手机号码输入多一位或者少一位数
序列
选中区域---数据---数据有效性---序列---在“来源”输入(A,B,C,D)
注意逗号要半角格式
从身份证号码提取出生日期
在C2单元格中输入
=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)
=FIND("龙",C6)
在c6中查找龙,结果显示龙在c6中的位数,查不到显示#VALUE!
培训协议服务期限完成提醒
思路:
过期了可以显示“协议期限完成”
没过期的,显示“没过期”
=IFERROR(IF(DATEDIF(B2,TODAY(),"d")>=0,"协议期限完成",),"没过期")
分析:
DATEDIF(B2,TODAY(),“d”)可以求出今天到B2的日期共多少天,如果“今天”在B2之前,就会产生错误值,如果“今天”在B2之后,
这个函数就会算出B2距离“今天”有多少天。
当这个天数≥0时,就会执行IF函数,显示“协议期限完成”,当DATEDIF运行是一个错误值时,
这时候就执行IFERROR函数,显示“没过期”。
COUNTIF函数
是一个数数的函数,可以用于数出参数1区域内,符合参数2的条件的单元格个数。
如:
=countif(F3:
F10,“女”)
统计F3:
F10中女的个数。
统计一定区间的个数
如:
统计9月流动表“9月在职”工作表中,
≥50岁以上的人数:
=COUNTIF(F:
F,">=50")
20岁≤年龄<40岁的人数:
=COUNTIF
(F:
F,">=20")-COUNTIF(F:
F,">=40")
Excel中如何把数据彻底隐藏起来?
选中不想让别人看到的部分。
单击WPS表格旁边的倒三角,选择【格式】,【单元格】
在【数字】下面找到【自定义】,在右边类型框中在英文状态下输入【;;;】
再切换到【保护】,勾选【隐藏】,单击确定。
再单击倒三角,依次选择【工具】、【保护】、【工作表格】
设置好保护密码,确定。
【Excel2010技巧】利用自动更正输入相同文字
选择【文件】---【选项】
点击【选项】进入菜单,选择【校对】
在【校对】里面点击【自动更正选项】按钮,
按图中所示,输入pcw,和语文我爱你,我要学习它”。
点击【添加】
在Excel中自定义函数
Excel函数虽然丰富,但并不能满足我们的所有需要。
我们可以自定义一个函数,来完成一些特定的运算。
下面,我们就来自定义一个计
算梯形面积的函数:
1.执行“工具→宏→VisualBasic编辑器”菜单命令(或按“Alt+F11”快捷键),打开VisualBasic编辑窗口。
2.在窗口中,执行“插入→模块”菜单命令,插入一个新的模块——模块1。
3.在右边的“代码窗口”中输入以下代码:
FunctionV(a,b,h)V=h*(a+b)/2EndFunction
4.关闭窗口,自定义函数完成。
以后可以像使用内置函数一样使用自定义函数。
提示:
用上面方法自定义的函数通常只能在相应的工作簿中使用。
输入上一个单元格相同的数据:
Ctrl+D,Ctrl+R(横向)
多个单元格输入相同数据:
按住“Ctrl”键用鼠标点击要输入相同数据的单元格,选择这些单元格。
然后输入文本数据“马六”。
此时按住“
Ctrl+Enter”键即可输入相同内容。
怎么在excel中快速查找重复记录
使用条件格式可以做到,方法如下:
1.选择你要查找重复记录的列
2.点击工具栏中的开始→条件格式→突出显示单元格规则→重复值
3.弹出的窗口直接点击“确定”
4.此时重复记录已经成为红色文本和填充的了
以上方法在用过之后,如果想去掉这些红色的模式,也一样要在条件格式功能中清除,方法是:
1.选择所有你要清除格式的单元格
2.点击工具栏中的开始→条件格式→清除规则→清除所选单元格的规则
假设数字在A列,数字由第二行开始,在B2输入公式:
=IF(COUNTIF(A:
A,A2)>1,"重复","")
把鼠标放在B2单元格的右下角变成黑十字时按鼠标左键向下拉
求Excel中根据出生年月计算年龄的公式
=year(now())-year(存放出生年月数据所在单元格)+1,公式的意思是,用今年的年份减去出生的年份,再加上1,即为年龄。
B列公式:
=INT((TODAY()-A2)/365)
Excel自动快速计算出生年月公式
在需要显示出身年月日的单元格中输入公式=CONCATENATE(MID(a2,7,4),"/",MID(a2,11,2),"/",MID(a2,13,2)),这里注意公式内的a2是表示从a2单元格中获取身份证号码并得出结果。
1、重复性检查公式:
顾名思义检查表格中是否存在重复性的东西,举例说明:
里面的单元格大家可自行切换
2、数据透视:
是一种可以快速汇总大量数据的交互式。
起到一个求和的作用,把一行行表格里面的东西整合到一个表格里面。
点击插入—数据透视—数据透视表即可简单方便。
注意表格中,标题行一定要完善好,且不能存在合并单元格
3、时间转换函数,例子上图:
4、文本格式转化为数值格式:
3种方法,大家可以举一反三,比如添加其他的复合也可以采取这种方式:
第一种方法:
第二种方法:
在任一单元格输入1,复制,选中你要转换的单元格右击选择性粘贴,选中“乘”,确定。
第三种方法:
选中拟改变格式的单列区域,“数据/分列/下一步/下一步/完成”。
第一种吧,简单方便。
第二种适合多个单元格,大量的。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 办公 常用 技巧