Excel办公常用技巧.docx

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列操作），在Ｄ1中输入“=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，复制，选中你要转换的单元格右击选择性粘贴，选中“乘”，确定。

第三种方法：

选中拟改变格式的单列区域，“数据/分列/下一步/下一步/完成”。

第一种吧，简单方便。

第二种适合多个单元格，大量的。