Excel办公常用技巧

1、Excel 常用的一些使用技巧以下是我收集整理的一些电子表格的使用技巧和方法， 希望这些方法能够为各位同事和 朋友们带来一些方便。1 编辑技巧（1）分数的输入 如果直接输入 “1/5"，系统会将其变为 “1月 5 日"，解决办法是：先输入 “0，" 然后输入空格， 再输入分数 “1/5。"（2）序列 “ 001的" 输入 如果直接输入 “001，" 系统会自动判断 001 为数据 1，解决办法是：首先输入 “'（" 西文单引 号），然后输入 “001。"（3）日期的输入 如果要输入 “4月 5日 "

2、;，直接输入 “45/"，再敲回车就行了。 如果要输入当前日期， 按一下 “Ctrl+； "键。（4）多张工作表中输入相同的内容 几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住 Ctrl 键，再单击 窗口左下角的 Sheet1、 Sheet2来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据， 此时这些数据会自动出现在选中的其它工作表之 中。输入完毕之后，再次按下键盘上的 Ctrl 键，然后使用鼠标左键单击所选择的多个工作 表，解除这些工作表的联系， 否则在一张表单中输入的数据会接着出现在选中的其它工作表 内。（5）不连

3、续单元格填充同一数据 选中一个单元格，按住 Ctrl 键，用鼠标单击其他单元格，就将这些单元格全部都选中了。 在编辑区中输入数据，然后按住 Ctrl 键，同时敲一下回车，在所有选中的单元格中都出现 了这一数据。（6）在单元格中显示公式 word 中“ Ctrl+ ”，或公式，显示公式；。2、单元格内容的合并在 C 行后插入一个空列 （如果 D 列没有内容， 就直接在 D 列操作），在 1 中输入 “=B1&C1"， D1 列的内容就是 B 、C 两列的和了。3、条件显示 我们知道，利用 If 函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生 成绩时， 希望输入

4、60以下的分数时， 能显示为 “不及格 "；输入 60以上的分数时， 显示为 “及 格" 。这样的效果，利用 IF 函数可以很方便地实现。 假设成绩在 A2 单元格中， 判断结果在 A3 单元格中。那么在 A3 单元格中输入公式： =if （A2<60 ，“不及格 "，“及格"） 同时，在 IF 函数中还可以嵌套 IF 函数或其它函数。例如，如果输入： =if （A2<60，“不及格 "，if（A2<=90，“及格 "，“优秀"） 就把成 绩分成了三个等级。如果输入 =if（A2<60，“差"

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

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

7、入年份如2014，会提示按“ ENTER ”键自动补上日期。EXCEL 表格中如何屏蔽公式在编辑栏中的显示 选中目标单元格，按鼠标右键，选中【设置单元格格式】对话框。 在弹出的【设置单元格格式】对话框中，选择【保护】选项中的【隐藏】选框。 选择【审阅】选项下的【保护工作表】选项。 在弹出的【保护工作表】中输入自己设置的密码，按【确定】按钮。 在弹出的【确认密码】中输入刚刚设置的密码，单击【确定】按钮即可。WPS 表格中重复数据如何显示出来先选定数据 找到“数据”选项，在数据的下拉菜单中看到“高亮重复项”，点击“高亮重复项”Excel 如何去除单元格中的空格Trim() 函数用来删除文本的前导空

8、格和尾部空格。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 所在的

9、开始位数， 即 第 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位字

10、符结束，即截取了 TTX:0.2456mg/L 。Excel 实战用法 （精）让不同类型数据用不同颜色显示 “格式条件格式”命令，打开“条件格式”对话框。建立分类下拉列表填充项 执行“数据有效性”命令，打开“数据有效性”对话框。在“设置”标签 中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框 中，输入“工业企业”，“商业企业”，“个体企业”序列 （ 各元素之间用 英文逗号隔开 ） ，确定退出。让数据按需排序 如果你要将员工按其所在的部门进行排序， 这些部门名称既的有关信息不是 按拼音顺序，也不是按笔画顺序，怎么办 ?可采用自定义序列来排序。1. 执行“格式选项”命令，打开

11、“选项”对话框，进入“自定义序列”标 签中，在“输入序列”下面的方框中输入部门排序的序列（如“机关 , 车队, 一车间,二车间,三车间”等 ） ，单击“添加”和“确定”按钮退出。2. 选中“部门”列中任意一个单元格，执行“数据排序”命令，打开“排 序”对话框，单击“选项”按钮，弹出“排序选项”对话框，按其中的下拉按钮， 选中刚才自定义的序列，按两次“确定”按钮返回，所有数据就按要求进行了排 序。把数据彻底隐藏起来1. 选中需要隐藏内容的单元格 （区域） ，执行“格式单元格” 命令，打开“单 元格格式”对话框，在“数字”标签的“分类”下面选中“自定义”选项，然后 在右边“类型”下面的方框中输入“

12、；” （ 三个英文状态下的分号 ） 。2. 再切换到“保护”标签下，选中其中的“隐藏”选项，按“确定”按钮退 出。3. 执行“工具保护保护工作表”命令，打开“保护工作表”对话框，设 置好密码后，“确定”返回。提示：在“保护”标签下，请不要清除“锁定”前面复选框中的“”号， 这样可以防止别人删除你隐藏起来的数据。让“自动更正”输入统一的文本1. 执行“工具自动更正”命令，打开“自动更正”对话框。2. 在“替换”下面的方框中输入“ pcw”（ 也可以是其他字符，“ pcw”用小 写） ，在“替换为”下面的方框中输入“电脑报”，再单击“添加”和“确 定”按钮。3. 以后如果需要输入上述文本时，只要输

13、入“ pcw”字符?组合函数： CONCATENATE如何把文件夹内文件名批量导出到 txt 生成清单第一步，新建一个 txt 格式的记事本文件。第二步，在记事本文件中输入： DIR *.* /B >LIST.TXT 第三步，将此记事本文件后辍名，由 txt 改为 bat 。会弹出重命名对话框，单击“是”。第四步，双击文件“新建文本文档 .bat ”即可生成 list.txt 文件。打开 txt 文件就可以看 到当前文件夹内的所有文件名列表。 （温馨提示：你也可以把文件“新建文本文档 .bat ”放 在其他文件夹里运行，获取当前文件夹下面的所有文件名哦！） 控制特定单元格输入文本的长度单

14、击“数据”菜单的“有效性”选项。在“设置” - “有效性条件” -“允许” - “文本长度”。然后在“数据”下拉菜单中选择“等于”，且“长度”为“ 4”。 成组填充多张表格的固定单元格单击第一个工作表的标签名“ Sheet1”，然后按住 Shift 键，单击最后一张 表格的标签名 “Sheet3”（如果我们想关联的表格不在一起， 可以按住 Ctrl 键进 行点选 ） 。在需要一次输入多张表格内容的单元格中随便写点什么，我们发现， “工作组”中所有表格的同一位置都显示出相应内容了。我们需要将多张表格中相同位置的数据统一改变格式该怎么办呢?首先，我们得改变第一张表格的数据格式，再单击“编辑”菜单的

15、“填充”选项，然后在 其子菜单中选择“至同组工作表”。这时， Excel 会弹出“填充成组工作表”的 对话框，在这里我们选择“格式”一项，点“确定”后，同组中所有表格该位置 的数据格式都改变了。改变文本的大小写”=UPPER源（ 数据格 ） ”，将文本全部转换为大写；“ =LOWER源（数据格 ）”， 将文本全部转换成小写；“ =PROPER源（数据格 ） ”，将文本转换成“适当”的大 小写，如让每个单词的首字母为大写等。提取字符串中的特定字符如果我们想快速从 A4单元格中提取称谓的话， 最好使用“=RIGHT（源数据格， 提取的字符数 ） ”函数，它表示“从 A4单元格最右侧的字符开始提取

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

17、;") 把鼠标放在 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 这

18、一区域进行求和；2、平均数： =AVERAGE(K2:K56) 对 K2 K56 这一区域求平均数；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

19、到 K56 区域( 55 名学生)的最高分；7、最低分： =MIN(K2:K56) 求 K2 到 K56 区域( 55 名学生)的最低分；14 、根据出生日期自动计算周岁： =TRUNC(DAYS360(D3,NOW( )/360,0)15 、在 Word 中三个小窍门：连续输入三个 “”可得一条波浪线。连续输入三个 “-”可得一条直线。连续输入三个 “=”可得一条双直线。excel 中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如： 点击菜单栏 -开始 -样式模块中的条件格式 -新建规则 -仅用公式确定要设置格式的单元格。二、 EXCEL中如何控制每列数据的长度并避免重复录入用

20、数据有效性定义数据长度。用鼠标选定你要输入的数据范围，点"数据"->" 有效性"->"设置"，"有效性条件 "设成"允许文本长度 ""等于""5" (具体条件可根据你的需要改变)。还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点"确定 "。一个工作薄中有许多工作表如何快速整理出一个目录工作表1、用宏 3.0 取出各工作表的名称，方法：Ctrl+F3 出现自定义名称对话框，取名为 X，在 “引用位置 ”框

21、中输入： =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

22、 作为 目录页，根据内容页数量在目录页建立序号 0116第 2 步 选中 B2 单元格，同时按住键盘上的“ Ctrl+k ”，弹出【插入超链接】第 3 步 选择“本文档中的位置”，用鼠标选中“第一章”，单击确定第 4 步 重复以上步骤，完成所有目录链接 第 1 步单击 B1 单元格，切换到“公式”选项卡，单击“定义名称”，弹出【新建名称】对 话框，在“名称”文本框中输入“目录”，在“引用位置”文本框输入以下公式：=INDEX(GET.WORKBOOK(1),ROW(A1)&T(NOW()注意： GET.WORKBOOK 函数是宏表函数，可以提取当前工作簿中的所有工作表名,需要先定义名称

23、后使用。第 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)/365NOW() 提

24、取当前日期DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2) 从身份证提取出生日期一年当 365 天计算，实际年龄结果可能会可能会有几天的误差。办公技巧Word绝招 :一、输入三个“ =”，回车，得到一条双直线；二、输入三个“ ”，回车，得到一条波浪线；三、输入三个“ * ”或 “-”或 “#”，回车，惊喜多多； 在单元格内输入 =now() 显示日期"," 星期aaaa，这dddd，这aaa”，在单元格内输入 =CHOOSE(WEEKDAY(I3,2)星,"期一"," 星期二 "," 星期

25、三 四"," 星期五 "," 星期六"," 星期日 ")显示星期几excel 怎么把日期显示为星期几选中 A1，鼠标右键，选择【设置单元格格式】，在【自定义】中输入 样 A1 单元格将返回如“星期三”这种表示方式。选中 A1，鼠标右键，选择【设置单元格格式】，在【自定义】中输入样 A1 单元格将返回如“ Wednesday”这种英文表示方式。选中 A1，鼠标右键，选择【设置单元格格式】，在【自定义】中输入“周这样 A1 单元格将返回如“周三”这种表示方式。如果用公式 =TEXT(A1,"aaaa") ，一

26、样可以达到更改格式显示的目的。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

27、 的 余 数 ， MOD(MID(B2,17,1),2)=1 判断是否等于 1 ，如果等于 1 是奇数为男，否则为女。如何在 Excel 中不允许输入重复值(数据)？假如要在 A2:A10 单元格区域中禁止录入重复的数据 可以选中单元格区域 A2:A10 ，然后单击“数据”选项卡下的“数据工具”组中的“数据有 效性”命令按钮。在弹出的 “数据有效性” 对话框下的 “设置” 选项卡“有效性条件允许”中设置 “自定义”， 然后在公式区域选择框中输入公式 =COUNTIF($A$2:$A$10,A2)=1 ，然后单击“确定”按钮。原理分析： 通过用 COUNTIF 函数判断指定的单元格区域中的数据的

28、个数只能是 1 个，不能 多于 1，假如满足条件则可以输入，不满足条件就禁止输入。如果要在多列的单元格区域中禁止录入重复值， 原理与以上的步骤类似， 只是将统计个数的 单元格区域改为多行多列即可。如下图所示，公式为 =COUNTIF($A$2:$D$9,A2)=1数据有效性： 控制单元格输入文本长度 选中区域 -数据-数据有效性 -允许 -文本长度 可防止身份证 / 银行卡号 /手机号码输入多一位或者少一位数序列选中区域 -数据-数据有效性 -序列 -在“来源”输入( A,B,C,D) 注意逗号要半角格式从身份证号码提取出生日期在 C2 单元格中输入=MID(B2,7,4)&"

29、;-"&MID(B2,11,2)&"-"&MID(B2,13,2) =FIND（" 龙 ",C6） 在 c6 中查找龙，结果显示龙在 c6 中的位数，查不到显示 #VALUE!培训协议服务期限完成提醒 思路：过期了可以显示“协议期限完成”没过期的，显示“没过期” =IFERROR(IF(DATEDIF(B2,TODAY(),"d")>=0, " 协议期限完成 ", ),"没过期 ") 分析： DATEDIF(B2,TODAY(), “ d”)可以求出今天到

30、 B2 的日期共多少天，如果“今天”在 B2 之前，就会产生错误值，如果“今天”在 B2 之后，这个函数就会算出 B2 距离“今天”有多少天。当这个天数 0 时，就会执行 IF 函数，显示 “协议期限完成”，当 DATEDIF 运行是一个错误值时，这时候就执行 IFERROR 函数，显示“没过期”。COUNTIF 函数 是一个数数的函数，可以用于数出参数 1 区域内，符合参数 2 的条件的单元格个数。 如： =countif(F3:F10, “女” )统计 F3:F10 中女的个数。 统计一定区间的个数 如：统计 9月流动表“ 9 月在职”工作表中，50 岁以上的人数： =COUNTIF(F:

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

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