Excel办公常用技巧

Excel常用的一些使用技巧

以下是我收集整理的一些电子表格的使用技巧和方法，希望这些方法能够为各位同事和

朋友们带来一些方便。

1编辑技巧

（1）分数的输入

如果直接输入“1/5”，系统会将其变为“1月5日”，解决办法是：先输入“0”，然后输入空格，

再输入分数“1/5”。

（2）序列“001”的输入

如果直接输入“001”，系统会自动判断001为数据1,解决办法是：首先输入s（西文单引

号），然后输入“001”。

（3）日期的输入

如果要输入"4月5直接输入“4/5”，再敲回车就行了。如果要输入当前H期，按一卜“Ctrl+：

”键。

（4）多张工作表中输入相同的内容

儿个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击

窗口左下角的Sheet1>Sheet2……来直接选择需要输入相同内容的多个工作表，接着在其中

的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之

中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作

表，解除这些工作表的联系，否则在•张表单中输入的数据会接着出现在选中的其它工作表

内。

（5）不连续单元格填充同一数据

选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。

在编辑区中输入数据，然后按住Ctrl键，同时敲一下同车，在所有选中的单元格中都出现

了这一数据。

（6）在单元格中显示公式

word中“CM+、”,或公式，显示公式;。

2、单元格内容的合并

在C行后插入一个空列（如果D列没有内容,就直接在D列操作），在DI中输入&BI&C1”,

D1列的内容就是B、C诙列的和了。

3、条件显示

我们知道，利用If函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生

成绩时，希望输入60以下的分数时，能显示为“不及格”；输入60以上的分数时，显示为“及

格”。这样的效果，利用IF函数可以很方便地实现。假设成绩在A2单元格中，判断结果在

A3单元格中。那么在A3单元格中输入公式：=if（A2<60,“不及格”，“及格”）同时，在

IF函数中还可以嵌套IF函数或其它函数。

例如，如果输入:=if（A2V60,“不及格"，if（A2<=90,“及格”，“优秀”））就把成

绩分成了三个等级。

如果输入=if（A2V60,“差”，if（A2<=70,“中”，if（A2<90,“良”,“优"））；就

把成绩分为了四个等级。

再比如，公式：=if（SUM（Al：A5>0,SUM（Al：A5）,0）此式就利用了嵌套函

数，意思是，当A1至A5的和大于。时，返回这个佰，如果小于0,那么就返回0。还有

一点要提醒你注意：以上的符号均为半角，而且IF与括号之间也不能有空格。

5、批量删除空行

我们可以利用“自动筛选”功能，把空行全部找到，然后一次性删除。做法：先在表中插入

新的一个空行，然后按下GH+A键，选择整个工作表，用鼠标单击“数据”菜单，选择“筛选

”项中的“自动筛选”命令。这时在每一列的顶部，都出现•个下拉列表框，在典型列的下拉

列表框中选择“空白"，直到页面内已看不到数据为止.

word中如何快速插入时间:

方法一：点击word文档工具栏上的“日期和时间”，匕以直接插入。格式可以自己选择。

当然你也可以勾选上“自动更新”，那么每次你打开文档后显示的都是当前最新日期。

方法二：键盘上同时按下Ah,Shift,D,可以以域的形式插入时间和口期。右键编辑它，

以后可以随时更新域。

方法三：利用word自带的提示功能添加日期，输入年份如2014,会提示按“ENTER”键自

动补上日期。

EXCEL表格中如何屏蔽公式在编辑栏中的显示

选中目标单元格，按鼠标右键，选中【设置单元格格式】对话框。

在弹出的【设置单元格格式】对话框中，选择【保护】选项中的【隐藏】选框。

选择【审阅】选项下的【保护工作表】选项。

在弹出的【保护工作表】中输入自己设置的密码，按【确定】按钮。

在弹出的【确认密码】中输入刚刚设置的密码，单击【确定】按钮即可。

WPS表格中重复数据如何显示出来

先选定数据

找到“数据”选项，在数据的下拉菜单中看到“高亮重复项”，点击“高亮重复项”

Excel如何去除单元格中的空格

Trim。函数用来删除文本的前导空格和尾部空格。

Trim(D8)用来删除D8单元格中文本的前后空格。

subsliIule()函数用来去除中间的空格

怎样提取EXCEL单元格中的部分内容？提取公式？

举例，要从excel第•列中截取CD的数值7.382,即A1单元格中第4位参数到第8位参数

之间位数为5位(小数点也占一位)的一段参数“(cd:7.382mg/Lm)

在一空白单元格里输入MID公式,二MID(text,start-num,nuin-chars)o在这里的例子里，

text就是要截取的单元格地址即Al,start-num是A1中被截取的7.382所在的开始位数，即

第4位，num-chars即7.382的总位数，共5位，所以在空白单元格内输入MID公式，=MID

(Al,4,5)o按ENTER回车，即可得到7.382。

如果要从单元格内左起第1位开始截取字符，则用LEFT公式，如图所示，HP=LEFT：A1,

10),意思是提取A1单元格中从左起第1位字符起到第10位字符结束，即截取了

CD:7.382mgo

同理，如果要从单元格内右起第1位开始截取字符，则用RIGHT公式，如图所示，BP=RIGHT

(Al,15),意思是提取Al单元格中从右起第I位字将起到第15位字符结束，即截取了

TTX:0.2456mg/Lo

1.执行“工具一自动更正”命令，打开“自动更正”对话框。

2.在“替换”下面的方框中输入“pew”（也可以是其他字符，“pew”用小

写），在“替换为”下面的方框中输入“《电脑报》”，再单击“添加”和“确

定”按钮。

3.以后如果需要输入上述文本时，只要输入“pew”字符？

组合函数：CONCATENATE

如何把文件夹内文件名批量导出到txt生成清单

第一步，新建一个txt格式的记事本文件。

第二步，在记事本文件中输入：DIR*.*/B>LIST.TXT

第三步，将此记事本文件后辍名，由txt改为bat。会弹出重命名对话框，单击“是”。

第四步，双出义件“新建义本义档.bat”即可生成list,txt义件。打开txt义件就可以看

到当前文件夹内的所有文件名列表。（温馨提示：你也可以把文件“新建文本文档.bat”放

在其他文件夹里运行，获取当前文件夹下面的所有文件名哦！）

控制特定单元格输入文本的长度

单击“数据”菜单的“有效性”选项。在“设置”-“有效性条件”-“允许”

-“文本长度”。然后在“数据”下拉菜单中选择“等于"，且“长度”为“4”。

成组填充多张表格的固定单元格

单击第一个工作表的标签名"Sheet1",然后按住Shift键，单击最后一张

表格的标签名“Sheet?，"（如果我们想关联的表格不在一起，可以按住Ctrl健进

行点选）。在需要一次输入多张表格内容的单元格中随便写点什么，我们发现，

“工作组”中所有表格的同一位置都显示出相应内容了。

我们需要将多张表格中相同位置的数据统一改变格式该怎么办呢?首先，我

们得改变第一张表格的数据格式，再单击“编辑”菜单的“填充”选项，然后在

其子菜单中选择“至同组工作表”。这时，Excel会弹出“填充成组工作表”的

对话框，在这里我们选择“格式”一项，点“确定”后，同组中所有表格该位置

的数据格式都改变了。

改变文本的大小写

"二UPPER（源数据格）”，将文本全部转换为大写；“二LOWER（源数据格；”

将文本全部转换成小写；“二PROPER(源数据格)”，将文本转换成“适当”的大

小写，如让每个单词的首字母为大写等。

提取字符串中的特定字符

如果我们想快速从A4单元格中提取称谓的话，最好使用JR1GHT(源数据格,

提取的字符数)”函数，它表示“从A4单元格最右侧的字符开始提取2个字符”

输入到此位置。当然，如果你想提取姓名的话，则要使用“-LEFT(源数据格，提

取的字符数)”函数了。还有一种情况，我们不从左右两端开始，而是直接从数

据中间提取几个字符。比如我们要想从A5单元格中提取“武汉”两个字时，就

只须在目标单元格中输入“=MID(A5,4,2)”就可以了。意思是：在A5单元格中

提取第4个字符后的两个字符，也就是第4和第5两个字。

Excel公式大全

1、查找重复内容公式：假设数字在A列，数字由第二行开始，在B2输入公式：

=IF(COUNTIF(A:A,A2)>L"重复7")把鼠标放在B2单元格的右下角变成黑十字时按鼠标左

键向下拉,再以B列排序或筛选，将标有“重复”的行删除即可。

点击工具栏中的开始一条件格式一突出显示单元格规则-重复值

2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6z"2009/8/30"zFALSE))/360z0)o

3、从输入的18位身份证号的出生年月计算公式：

，，

=CONCATENATE(MID(E2/7Z4),7,MID(E2,11Z2),7",MID(E2,13,2))O

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歹U、M列和N列分别存放着学生对、平

时总评〃、''期中〃、''期末〃三项成绩；

6、最高分：=MAX(K2:K56)—求K2到K56区域(55名学生)的最高分；

7、最低分：=MIN(K2:K56)—求K2到K56区域(55名学生)的最低分；

14、根据出生口期自动计算周岁：=TRUNC((DAYS360(D3ZNOW()))/360z0)

15、在Word中三个小窍门:

①连续输入三个〃可得一条波浪线。

②连续输入三个''■"可得一条直线。

连续输入三个♦〃可得一条双直线。

一、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如：

点击菜单栏•开始•样式模块中的条件格式•新建规则•仅在公式确定要设置格式的单元格。

二、EXCEL中如何控制每列数据的长度并避免重复录入

用数据有效性定义数据长度。

用鼠标选定你要输入的数据范围，点“数据"有效性"设置"，“有效性条件”设成“允许””

文本长度””等于(具体条件可根据你的需要改变)。

还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点”确定，

一个工作薄中有许多工作表如何快速整理出一个目录工作表

1、用宏3.0取出各工作表的名称，方法：

Ctrl+F3出现自定义名称对话框，取名为X,在''引用位置”框中输入:

=MID(GET.WORKBOOK(1)/FIND("]"ZGET.WORKBOOK(1))+1/100)

确定

2、用HYPERLINK函数批量插入连接，方法：

在目录工作表(一般为第一个sheet)的A2单元格输入公式：

',,,

=HYPERLINK(#"8dNDEX(X/R0W())&"!Al"zINDEX(X/R0W()))

将公式向下填充，直到出错为止，目录就生成了

第1步文档说明：下面文档共有17个sheet页,其中sheet2-sheetl7是内容页,sheet1作为

目录页，根据内容页数量在目录页建立序号01~16

第2步选中B2单元格，同时按住键盘上的“CtH+k”，弹出【插入超链接】

第3步选择“本文档中的位置”，用鼠标选中“第一章”，单击确定

第4步重复以上步骤，完成所有目录链接

第1步单击B1单元格，切换到“公式”选项卡，单击“定义名称”，弹出【新建名称】对

话框，在“名称”文本框中输入“目录”，在“引用位置”文本框输入以下公式：

=INDEX(GET.WORKBOOK⑴,ROW(A1))&T(NOW())

注意：GET.WORKBOOK函数是宏表函数，可以提取当前工作簿中的所有工作表名,需要先

定义名称后使用。

第2步在B1单元格中输入公式：

=IFERROR(HYPERLINK(目录&”!A1",MID(目录,FIND(T,目录)+1,99)),"")

第3步双击B1右下角向下复制，如下图效果，单击目录中的工作表名称，就会自动跳转

到相应工作表。我们只要在做好的目录中鼠标点击，就会快速到达所在表格位置。

excel中如何根据身份证号计算年龄

例如，身份证在A2,在B2输入公式：

=(NOWQ-DATE(MID(A2f7,4),MID(A2zllr2),MID(A2,13,2)))/365

NOW()提取当前日期

DATE(MID(A2,7,4),MID(A2,1L2),MID(A2,13,2)从身份证提取出生日期

一年当365天计算，实际年龄结果可能会可能会有几天的误差。

办公技巧

Word绝招：

一、输入三个“二”，回车，得到一条双直线；

二、输入三个，回车，得到一条波浪线；

三、输入三个“*”或或“#"，回车，惊喜多多；

在单元格内输入二now()显示日期

在单元格内输入二CH00SE(WEEKDAY(I3,2),〃星期一〃，〃星期二〃，〃星期三〃，〃星期

四〃，〃星期五〃，〃星期六〃，〃星期日〃)显示星期几

excel怎么把日期显示为星期儿

选中A1,鼠标右键，选择【设置单元格格式】，在【自定义】中输入aaaa,这

样A1单元格将返回如“星期三”这种表示方式。

选中A1,鼠标右键，选择【设置单元格格式】，在【自定义】中输入dddd,这

样A1单元格将返回如“Wednesday”这种英文表示方式。

选中A1,鼠标右键，选择【设置单元格格式】，在【自定义】中输入“周aaa”，

这样Al单元格将返回如“周三”这种表示方式。

如果用公式二TEXT(Al,"aaaa〃),一样可以达到更改格式显示的目的。

Excel表格“今天星期几”函数

返回中文的“星期几”=TEXT(TODAYO/^aaaO

返回英文的“星期几”=TEXT(TODAY(),〃dddd〃)

多少天之后是“星期几”，只要在公式中“today()+n”即可

从身份证号码判断性别

在要计算性别的单元格b2输入公式IF(M0D(MID(A2,17,1),2)=1,“男公女”)

MID(B2,17,1)是截取第17位的字符,MOD(MID(B2,17』),2)函数是取除2的余数,

MOD(MID(B2,17,1),2)=I判断是否等于1,如果等于1是奇数

为男，否则为女。

如何在Excel中不允许输入重复值(数据)？

假如要在A2:A!0单元格区域中禁止录入重夏的数据

可以选中单元格区域A2:A10,然后单击“数据”选项K下的“数据工具”组中的“数据有

效性”命令按钮。

在弹出的“数据有效性”对话框下的“设置”选项卡“有效性条件允许”中设置“自定义”，

然后在公式区域选择框中输入公式;COUNTIF

($A$2:$A$10,A2)=l,然后单击“确定”按钮。

原埋分析：通过用COUNTIF函数判断指定的单元格区域中的数据的个数只能是I个，不能

多于1，假如满足条件则可以输入，不满足条件就禁止

输入。

如果要在多列的单元格区域中禁止录入重复值，原理与以上的步骤类似，只是将统计个数的

单元格区域改为多行多列即可。如下图所示，公式

为

=COUNTIF($A$2:$D$9,A2)=I

数据有效性:

控制单元格输入文本长度

选中区域一数据一数据有效性…允许一文本长度

可防止身份证/银行卡号/手机号码输入多一位或者少一位数

序列

选中区域一数据一数据有效性一序列一在“来源”输入(A,B,C,D)

注意逗号要半角格式

从身份证号码提取出生日期

在C2单元格中输入

=MID(B2,7,4)&--"&MID(B2,11,2)&"-"&MID(B2,13,2)

:FIND(“龙”,C6)

在c6中查找龙，结果显示龙在c6中的位数，查不到显示#VALUE!

培训协议服务期限完成提醒

思路：过期了可以显示“协议期限完成”

没过期的，显示“没过期”

=IFERR0R(IF(DATEDIF(B2,TODAY。,"d")>=0,”协议期限完成没过期”)

分析：DATEDIF(B2,TODAY(),“d”)可以求出今天到B2的日期共多少天，如果“今天”在

B2之前，就会产生错误值，如果“今天”在B2之后，

这个函数就会算出B2距离“今天”有多少天。当这个天数20时，就会执行IF函数，显示

“协议期限完成"，当DATEDIF运行是一个错误值时，

这时候就执行IFERROR函数，显示“没过期”。

COUNTIF函数

是一个数数的函数，可以用于数出参数1区域内，符合参数2的条件的单元格个数。

如：=countif(F3:F10,“女”)

统计F3:F10中女的个数。

统计一定区间的个数

如：统计9月流动表“9月在职”工作表中，

250岁以上的人数：

=COUNTIF(F:F,',>=50")

20岁W年龄<40岁的人数：

=COUNTIF

(F:F,">=20")-COUNTIF(F:F；'>=40")

Excel中如何把数据彻底隙藏起来？

选中不想让别人看到的部分.

单击WPS表格旁边的倒三角，选择【格式】，【单元格】

在【数字】下面找到【自定义】，在右边类型框中在英文状态下输入【；；；】

再切换到【保护】，勾选【隐藏】，单击确定。

再单击倒三角，依次选择【工具】、【保护】、【工作表格】

设置好保护密码，确定。

【Excel2010技巧】利用自动更正输入相同文字

选择【文件】一【选项】

点击【选项】进入菜单，选择【校对】

在【校对】里面点击【自幼更正选项】按钮，

按图中所示，输入pew,和语文我爱你，我要学习它"。点击【添加】

在Excel中自定义函数

Excel函数虽然丰富，但并不能满足我们的所有需要。我们可以自定义一个函数，来完

成一些特定的运算。下面，我们就来自定义一个计

算梯形面积的函数：

1.执行“工具一宏一VisualBasic编辑器”菜单命令(或按“Ak+F11”快捷键)，打开Visual

Basic编辑窗口。

2.在窗口中，执行“插入一模块”菜单命令，插入一个新的模块一一模块lo

3.在右边的“代码窗口”中输入以下代码：

FunctionV(a,b,h)V=h*(a+b)/2EndFunction

4.关闭窗口，自定义函数完成.

以后可以像使用内置函数一样使用自定义函数。

提示：用上面方法自定义的函数通常只能在相应的工作簿中使用。

输入上一个单元格相同的数据：Ctrl+D,Ctrl+R(横向)

多个单元格输入相同数据：按住“Ctrl”键用鼠标点击要输入相同数据的单元格，选择这些

单元格。然后输入文本数据“马六”。此时按住“

Ctrl+Enter"键即可输入相同内容。

怎么在excel中快速查找重复记录

使用条件格式可以做到，方法如下：

I.选择你要查找重复记录的列

2.点击工具栏中的开始一条件格式f突出显示单元格规则一重复值

3.弹出的窗口直接点击“确定”

4.此时重复记录已经成为红色文本和填允的了

以上方法在用过之后，如果想去掉这些红色的模式，也一样要在条件格式功能中清除,

方法是：

1.选择所有你要清除格式的单元格

2.点击工具栏中的开始一条件格式一清除规则一清除所选单元格的规则

假设数字在A歹U，数字由第二行开始，在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,ll,2)J/",MID(a2,13,2)),这里注意公式内的a2是表

示从a2单元格中获取身份证号码并得出结果。

1、重复性检查公式:顾名思义检查表格中是否存在重复性的东西，举例说明：里面的单元格

大家可自行切换

C58-|FlF(8UNTIF($A$2:$A$73,A58A1J本记录重复*

1、重复性检查公式xk.

2、数据透视：是一种可以快速汇总大量数据的交互式。起到一个求和的作用，把一行行表

格里面的东西整合到一个表格里面。

点击插入一数据透视一数据透视表即可简单方便。注意表格中，标题行一定要完善好，且

不能存在合并单元格

选择要添加到报表的字段:固r

求和喷付款金额[日历天

门店________品类汇总徜售时间

1—丁.•——--.---一-二-:■00001空调29288大区

00002水洗21539公司

患电0

0000352454a门店

00004数码26987品类

00005电脑9736匕品牌

00006通讯8934匚单品

00007小家电14446匚明细商品组

「明细渠道