Excel办公常用技巧.docx

1、Excel办公常用技巧Excel常用的一些使用技巧 以下是我收集整理的一些电子表格的使用技巧和方法，希望这些方法能够为各位同事和朋友们带来一些方便。1 编辑技巧 （1）分数的输入 如果直接输入“1/5，系统会将其变为“1月5日，解决办法是：先输入“0，然后输入空格，再输入分数“1/5。 （2）序列“001的输入 如果直接输入“001，系统会自动判断001为数据1，解决办法是：首先输入“（西文单引号），然后输入“001。 （3）日期的输入 如果要输入“4月5日，直接输入“4/5，再敲回车就行了。如果要输入当前日期，按一下“Ctrl+；键。（4）多张工作表中输入相同的内容 几个工作表中同一位置填入

2、同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2.来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。 （5）不连续单元格填充同一数据 选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数

3、据。 （6）在单元格中显示公式 word中“Ctrl+”，或公式，显示公式；。2、单元格内容的合并在C行后插入一个空列（如果D列没有内容，就直接在D列操作），在1中输入“=B1&C1，D1列的内容就是B、C两列的和了。3、条件显示我们知道，利用If函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入60以下的分数时，能显示为“不及格；输入60以上的分数时，显示为“及格。这样的效果，利用IF函数可以很方便地实现。 假设成绩在A2单元格中，判断结果在A3单元格中。那么在A3单元格中输入公式： =if（A260，“不及格，“及格） 同时，在IF函数中还可以嵌套IF函数或其它

4、函数。 例如，如果输入： =if（A260，“不及格，if（A2=90，“及格，“优秀） 就把成绩分成了三个等级。 如果输入=if（A260，“差，if（A2=70，“中，if（A20，SUM（A1：A5），0） 此式就利用了嵌套函数，意思是，当A1至A5的和大于0时，返回这个值，如果小于0，那么就返回0。 还有一点要提醒你注意：以上的符号均为半角，而且IF与括号之间也不能有空格。5、批量删除空行 我们可以利用“自动筛选功能，把空行全部找到，然后一次性删除。 做法：先在表中插入新的一个空行，然后按下Ctrl+A键，选择整个工作表，用鼠标单击“数据菜单，选择“筛选项中的“自动筛选命令。这时在每一

5、列的顶部，都出现一个下拉列表框，在典型列的下拉列表框中选择“空白，直到页面内已看不到数据为止。word中如何快速插入时间：方法一：点击word文档工具栏上的“日期和时间”，可以直接插入。格式可以自己选择。当然你也可以勾选上“自动更新”，那么每次你打开文档后显示的都是当前最新日期。方法二：键盘上同时按下Alt，Shift，D，可以以域的形式插入时间和日期。右键编辑它，以后可以随时更新域。方法三：利用word自带的提示功能添加日期，输入年份如2014，会提示按“ENTER”键自动补上日期。EXCEL表格中如何屏蔽公式在编辑栏中的显示选中目标单元格，按鼠标右键，选中【设置单元格格式】对话框。在弹出的

6、【设置单元格格式】对话框中，选择【保护】选项中的【隐藏】选框。选择【审阅】选项下的【保护工作表】选项。在弹出的【保护工作表】中输入自己设置的密码，按【确定】按钮。在弹出的【确认密码】中输入刚刚设置的密码，单击【确定】按钮即可。WPS表格中重复数据如何显示出来先选定数据找到“数据”选项，在数据的下拉菜单中看到“高亮重复项”，点击“高亮重复项”Excel如何去除单元格中的空格Trim()函数用来删除文本的前导空格和尾部空格。Trim(D8)用来删除D8单元格中文本的前后空格。substitute()函数用来去除中间的空格怎样提取EXCEL单元格中的部分内容？提取公式？举例，要从excel第一列中截

7、取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），意思

8、是提取A1单元格中从左起第1位字符起到第10位字符结束，即截取了CD:7.382mg。同理，如果要从单元格内右起第1位开始截取字符，则用RIGHT公式，如图所示，即=RIGHT（A1，15），意思是提取A1单元格中从右起第1位字符起到第15位字符结束，即截取了TTX:0.2456mg/L。Excel实战用法(精)让不同类型数据用不同颜色显示“格式条件格式”命令，打开“条件格式”对话框。 建立分类下拉列表填充项 执行“数据有效性”命令，打开“数据有效性”对话框。在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框中，输入“工业企业”，“商业企业”，“个体企业”序列

9、(各元素之间用英文逗号隔开)，确定退出。 让数据按需排序 如果你要将员工按其所在的部门进行排序，这些部门名称既的有关信息不是按拼音顺序，也不是按笔画顺序，怎么办?可采用自定义序列来排序。 1.执行“格式选项”命令，打开“选项”对话框，进入“自定义序列”标签中，在“输入序列”下面的方框中输入部门排序的序列(如“机关,车队,一车间,二车间,三车间”等)，单击“添加”和“确定”按钮退出。 2.选中“部门”列中任意一个单元格，执行“数据排序”命令，打开“排序”对话框，单击“选项”按钮，弹出“排序选项”对话框，按其中的下拉按钮，选中刚才自定义的序列，按两次“确定”按钮返回，所有数据就按要求进行了排序。

10、把数据彻底隐藏起来 1.选中需要隐藏内容的单元格(区域)，执行“格式单元格”命令，打开“单元格格式”对话框，在“数字”标签的“分类”下面选中“自定义”选项，然后在右边“类型”下面的方框中输入“；”(三个英文状态下的分号)。 2.再切换到“保护”标签下，选中其中的“隐藏”选项，按“确定”按钮退出。 3.执行“工具保护保护工作表”命令，打开“保护工作表”对话框，设置好密码后，“确定”返回。 提示：在“保护”标签下，请不要清除“锁定”前面复选框中的“”号，这样可以防止别人删除你隐藏起来的数据。 让“自动更正”输入统一的文本 1.执行“工具自动更正”命令，打开“自动更正”对话框。 2.在“替换”下面的

11、方框中输入“pcw”(也可以是其他字符，“pcw”用小写)，在“替换为”下面的方框中输入“电脑报”，再单击“添加”和“确定”按钮。 3.以后如果需要输入上述文本时，只要输入“pcw”字符?组合函数：CONCATENATE如何把文件夹内文件名批量导出到txt生成清单第一步，新建一个txt格式的记事本文件。第二步，在记事本文件中输入：DIR *.* /B LIST.TXT第三步，将此记事本文件后辍名，由txt改为bat。会弹出重命名对话框，单击“是”。第四步，双击文件“新建文本文档.bat”即可生成list.txt文件。打开txt文件就可以看到当前文件夹内的所有文件名列表。（温馨提示：你也可以把文

12、件“新建文本文档.bat”放在其他文件夹里运行，获取当前文件夹下面的所有文件名哦！）控制特定单元格输入文本的长度 单击“数据”菜单的“有效性”选项。在“设置”-“有效性条件”-“允许”-“文本长度”。然后在“数据”下拉菜单中选择“等于”，且“长度”为“4”。成组填充多张表格的固定单元格 单击第一个工作表的标签名“Sheet1”，然后按住Shift键，单击最后一张表格的标签名“Sheet3”(如果我们想关联的表格不在一起，可以按住Ctrl键进行点选)。在需要一次输入多张表格内容的单元格中随便写点什么，我们发现，“工作组”中所有表格的同一位置都显示出相应内容了。 我们需要将多张表格中相同位置的数据

13、统一改变格式该怎么办呢?首先，我们得改变第一张表格的数据格式，再单击“编辑”菜单的“填充”选项，然后在其子菜单中选择“至同组工作表”。这时，Excel会弹出“填充成组工作表”的对话框，在这里我们选择“格式”一项，点“确定”后，同组中所有表格该位置的数据格式都改变了。 改变文本的大小写 ”=UPPER(源数据格)”，将文本全部转换为大写；“=LOWER(源数据格)”，将文本全部转换成小写；“=PROPER(源数据格)”，将文本转换成“适当”的大小写，如让每个单词的首字母为大写等。提取字符串中的特定字符 如果我们想快速从A4单元格中提取称谓的话，最好使用“=RIGHT(源数据格，提取的字符数)”函

14、数，它表示“从A4单元格最右侧的字符开始提取2个字符”输入到此位置。当然，如果你想提取姓名的话，则要使用“=LEFT(源数据格，提取的字符数)”函数了。还有一种情况，我们不从左右两端开始，而是直接从数据中间提取几个字符。比如我们要想从A5单元格中提取“武汉”两个字时，就只须在目标单元格中输入“=MID(A5,4,2)”就可以了。意思是：在A5单元格中提取第4个字符后的两个字符，也就是第4和第5两个字。Excel公式大全1、查找重复内容公式：假设数字在A列，数字由第二行开始，在B2输入公式：=IF(COUNTIF(A:A,A2)1,重复,)把鼠标放在B2单元格的右下角变成黑十字时按鼠标左键向下拉

15、，再以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) 对K2 K56这一区域求平均数；3、排名： =RANK(K2，K$2:K$56) 对55名学生的成绩进行排名；4

16、、等级： =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中三个小窍门：连续输入三个“”可得一条波浪线。连续输入三个“-”可得一条直线。连续输入三个“=”可得

17、一条双直线。1、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如：点击菜单栏-开始-样式模块中的条件格式-新建规则-仅用公式确定要设置格式的单元格。二、EXCEL中如何控制每列数据的长度并避免重复录入用数据有效性定义数据长度。用鼠标选定你要输入的数据范围，点数据-有效性-设置，有效性条件设成允许文本长度等于5（具体条件可根据你的需要改变）。还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点确定。一个工作薄中有许多工作表如何快速整理出一个目录工作表1、用宏3.0取出各工作表的名称，方法：Ctrl+F3出现自定义名称对话框，取名为X，在“引用位置”框中输

18、入：=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页，其中sheet2sheet17是内容页，sheet1作为目录页，根据内容页数量在目录页建立序号0116第2步 选中B2单元格，同时按住键盘上的“Ctrl+k”，弹出【插入超链接】第3步 选择“本文档中的位置

19、”，用鼠标选中“第一章”，单击确定第4步 重复以上步骤，完成所有目录链接第1步单击B1单元格，切换到“公式”选项卡，单击“定义名称”，弹出【新建名称】对话框，在“名称”文本框中输入“目录”，在“引用位置”文本框输入以下公式：=INDEX(GET.WORKBOOK(1),ROW(A1)&T(NOW()注意：GET.WORKBOOK函数是宏表函数，可以提取当前工作簿中的所有工作表名,需要先定义名称后使用。第2步在B1单元格中输入公式：=IFERROR(HYPERLINK(目录&!A1,MID(目录,FIND(,目录)+1,99),)第3步 双击B1右下角向下复制，如下图效果，单击目录中的工作表名称

20、，就会自动跳转到相应工作表。我们只要在做好的目录中鼠标点击，就会快速到达所在表格位置。excel中如何根据身份证号计算年龄例如，身份证在A2，在B2输入公式：=(NOW()-DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)/365NOW()提取当前日期DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)从身份证提取出生日期一年当365天计算，实际年龄结果可能会可能会有几天的误差。办公技巧Word绝招:一、 输入三个“=”，回车，得到一条双直线； 二、 输入三个“”，回车，得到一条波浪线； 三、 输入三个“*”或 “-”或 “#

21、”，回车，惊喜多多；在单元格内输入=now（） 显示日期在单元格内输入=CHOOSE(WEEKDAY(I3,2),星期一,星期二,星期三,星期四,星期五,星期六,星期日) 显示星期几excel怎么把日期显示为星期几选中A1，鼠标右键，选择【设置单元格格式】，在【自定义】中输入aaaa，这样A1单元格将返回如“星期三”这种表示方式。选中A1，鼠标右键，选择【设置单元格格式】，在【自定义】中输入dddd，这样A1单元格将返回如“Wednesday”这种英文表示方式。选中A1，鼠标右键，选择【设置单元格格式】，在【自定义】中输入“周aaa”，这样A1单元格将返回如“周三”这种表示方式。如果用公式=T

22、EXT(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中不

23、允许输入重复值（数据）？假如要在A2:A10单元格区域中禁止录入重复的数据可以选中单元格区域A2:A10，然后单击“数据”选项卡下的“数据工具”组中的“数据有效性”命令按钮。在弹出的“数据有效性”对话框下的“设置”选项卡“有效性条件允许”中设置“自定义”，然后在公式区域选择框中输入公式=COUNTIF($A$2:$A$10,A2)=1，然后单击“确定”按钮。原理分析：通过用COUNTIF函数判断指定的单元格区域中的数据的个数只能是1个，不能多于1，假如满足条件则可以输入，不满足条件就禁止输入。如果要在多列的单元格区域中禁止录入重复值，原理与以上的步骤类似，只是将统计个数的单元格区域改为多行多列

24、即可。如下图所示，公式为=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!培训协议服务期限完成提醒思路：过期了可以显示“协议期限完成”没过期的，显示“没过期”=IF

25、ERROR(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中女的个数。统计一

26、定区间的个数如：统计9月流动表“9月在职”工作表中，50岁以上的人数：=COUNTIF(F:F,=50)20岁年龄40岁的人数：=COUNTIF(F:F,=20)-COUNTIF(F:F,=40)Excel中如何把数据彻底隐藏起来？选中不想让别人看到的部分。单击WPS表格旁边的倒三角，选择【格式】，【单元格】在【数字】下面找到【自定义】，在右边类型框中在英文状态下输入【；】再切换到【保护】，勾选【隐藏】，单击确定。再单击倒三角，依次选择【工具】、【保护】、【工作表格】设置好保护密码，确定。【Excel2010技巧】利用自动更正输入相同文字选择【文件】-【选项】点击【选项】进入菜单，选择【校对】

27、在【校对】里面点击【自动更正选项】按钮，按图中所示，输入pcw，和语文我爱你，我要学习它”。点击【添加】在Excel中自定义函数Excel函数虽然丰富，但并不能满足我们的所有需要。我们可以自定义一个函数，来完成一些特定的运算。下面，我们就来自定义一个计算梯形面积的函数：1.执行“工具宏Visual Basic编辑器”菜单命令(或按“Alt+F11”快捷键)，打开Visual Basic编辑窗口。2.在窗口中，执行“插入模块”菜单命令，插入一个新的模块模块1。3.在右边的“代码窗口”中输入以下代码：Function V(a,b,h)V = h*(a+b)/2End Function4.关闭窗口，

28、自定义函数完成。以后可以像使用内置函数一样使用自定义函数。提示：用上面方法自定义的函数通常只能在相应的工作簿中使用。输入上一个单元格相同的数据：Ctrl+D，Ctrl+R（横向）多个单元格输入相同数据：按住“Ctrl”键用鼠标点击要输入相同数据的单元格，选择这些单元格。然后输入文本数据“马六”。此时按住“Ctrl+Enter”键即可输入相同内容。怎么在excel中快速查找重复记录使用条件格式可以做到，方法如下：1.选择你要查找重复记录的列2.点击工具栏中的开始条件格式突出显示单元格规则重复值3.弹出的窗口直接点击“确定”4.此时重复记录已经成为红色文本和填充的了以上方法在用过之后，如果想去掉这

29、些红色的模式，也一样要在条件格式功能中清除，方法是：1.选择所有你要清除格式的单元格2.点击工具栏中的开始条件格式清除规则清除所选单元格的规则假设数字在A列，数字由第二行开始，在B2输入公式：=IF(COUNTIF(A:A,A2)1,重复,)把鼠标放在B2单元格的右下角变成黑十字时按鼠标左键向下拉求Excel中根据出生年月计算年龄的公式=year(now()-year(存放出生年月数据所在单元格）+1，公式的意思是，用今年的年份减去出生的年份，再加上1，即为年龄。B列公式:=INT(TODAY()-A2)/365)Excel自动快速计算出生年月公式在需要显示出身年月日的单元格中输入公式=CON

30、CATENATE(MID(a2,7,4),/,MID(a2,11,2),/,MID(a2,13,2),这里注意公式内的a2是表示从a2单元格中获取身份证号码并得出结果。1、重复性检查公式:顾名思义检查表格中是否存在重复性的东西，举例说明：里面的单元格大家可自行切换2、数据透视：是一种可以快速汇总大量数据的交互式。起到一个求和的作用，把一行行表格里面的东西整合到一个表格里面。点击插入数据透视数据透视表即可 简单方便。注意表格中，标题行一定要完善好，且不能存在合并单元格3、时间转换函数，例子上图：4、文本格式转化为数值格式：3种方法，大家可以举一反三，比如添加其他的复合也可以采取这种方式：第一种方法：第二种方法：在任一单元格输入1，复制，选中你要转换的单元格右击选择性粘贴，选中“乘”，确定。第三种方法：选中拟改变格式的单列区域，“数据/分列/下一步/下一步/完成”。第一种吧，简单方便。第二种适合多个单元格，大量的。