Excel操作小技巧课件

1、Excel操作小技巧操作小技巧侯庆芳2015年11月13日 单元格边线小技巧单元格边线小技巧 单元格查找技巧单元格查找技巧 巧用巧用“=”号，效率翻倍号，效率翻倍 横向筛选横向筛选 合并单元格合并单元格 自动调整打印区域自动调整打印区域 视图调整技巧视图调整技巧 巧用复制粘贴巧用复制粘贴 快速跳转列快速跳转列 短公式的设置短公式的设置 Excel隐藏小功能隐藏小功能 常用快捷键常用快捷键一.单元格边线小技巧1.最适合列宽 选取表格所有列，双击任一列的边线就会自动调整为最适合的列宽3一.单元格边线小技巧2.调整为固定列宽选取表格所有列，调整任一列，所有列的列宽也会调整为同样大小。4一.单元格边线

2、小技巧3.快速跳转 双击单元格的边线，就会快速跳转到表格的边缘位置5一.单元格边线小技巧4.两列互换。 按shift不松，拖动表格边线，可以快速让两列互换。6一.单元格边线小技巧5.快速插入空行/列 A、按shift不松，拖动行号右下角，可以快速插入空行。 按shift不松，拖动列号右下角，可以快速插入空列。 B、从第6行向下选取10行，点插入即可插入10空行。 从第6行向下选取10行，点插入即可插入10空行。7一.单元格边线小技巧6.设置立体界面 左上粗深边线右下浅或白边线，可以让界面向外突出，反之而可以产生凹陷效果。8一.单元格边线小技巧7.设置细边线 表格设置成虚线，打印出来的是超细表格

3、线。9一.单元格边线小技巧8.快速添加外边框 一个excel表格，如果加上粗外框就会立即美观好多10二二.单元格查找技巧单元格查找技巧1.选取表中所有公式 选取工作表中所有的公式，别以为只有定位功能可以实现。在查找窗口中输入 = 号，点击“查找全部”，然后再按ctrl+a即可选取表中所有公式。112.批量删除或隐藏带颜色的行 打开格式下拉 - 点击“从单元格选择格式”并刷向黄色单元格 - 查找全部 - 按 ctrl+a 全选 - 按ctrl+9隐藏或右键菜单中点删除。12二二.单元格查找技巧单元格查找技巧3.多列按区间筛选【例】如下图所示表中，要求选取16月各产品销量数量80的单元格，并填充绿

4、色背景。13二二.单元格查找技巧单元格查找技巧3.多列按区间筛选14操作步骤：A.选取数据区域，CTRL+F打开查找窗口；B.查找框中输入*，点“查找全部”；C.点标题“值”排序；D.选取第一个大于80的行，按shift选中最后一行；E.关闭“查找”窗口，填充绿色。二二.单元格查找技巧单元格查找技巧4.查找所有合并单元格 如需要查找工作表中的合并单元格，只需要先任选2个空单元格合并，然后从查找窗口中选取该合并单元格，再点查找即可。15二二.单元格查找技巧单元格查找技巧5.删除括号中的内容 如果要删除一列字符串中括号内的内容。只需要查找 (*) 然后替换为空即可。16二二.单元格查找技巧单元格查

5、找技巧1.隔行填充 ctrl+g定位条件空值 - 在编辑栏中输入=A2，按ctrl+enter完成填充17三.巧用“=”，效率翻两倍2.多列转一列 =第2列的第一个单元格，向下向右复制，最后公式转换成数值。18三.巧用“=”，效率翻两倍3.一列转多列 如果转换成每5行一列，在第2列输入=A6，然后复制19三.巧用“=”，效率翻两倍4.图表上添加动态说明 选取图表的标题，然后在编辑栏中输入=D1。20三.巧用“=”，效率翻两倍四四.excel里横向筛选的解决方案里横向筛选的解决方案【例】下图所示。要求筛远产品C数量大于50的月份。21【解】excel里既然提供了竖向筛选，那么我们想办法把表格倒置

6、一下，原来的行变成列，列变成行。完成转置有两种方法。 A.选择粘贴转置。这种方法一次性的，数据源数据变化后再筛 选还需要重复操作。不可取。 B.函数转置。transpose函数可以把一个区域进行行列转换。即 然是公式，原表变化，公式结果当然也会跟着变化。这种方法 是我们所要的。操作步骤： 步骤1: 新建一个工作表，选取一个和原表行列相同大小的区域 如本例原表是7列6行的区域，那么在新工作表中选取 7行6列的区域，当然选更多的行列数也行。 步骤2：选取区域后，在编辑栏中输入=TRANSPOSE(Sheet2!A1:G6),光标放在公式结尾处，按ctrl+shift同时再按 回车键，再同时松开三键

7、结束公式输入。结果如下图所示。22四四.excel里横向筛选的解决方案里横向筛选的解决方案步骤3：我们用公式备份了一个倒置的表，在这个备份表里你就可以利用自动筛选进行筛选，等同于在原表中横向筛选。筛选效果如下图所示。23四四.excel里横向筛选的解决方案里横向筛选的解决方案五.合并单元格1.跨列合并 单行合并：工具栏-【 合并后居中 】/【 合并单元格 】 快捷键-【 】/【 】 多行合并，你还在一行一行的合并吗？如果是看看下面的方法：24Alt+H+M+CAlt+H+M+MAlt+H+M+A2.合并单元格字符 两个单元格的字符强烈要求合并到一起，公式=B2&C2。25五.合并单元格3.合并

8、多个单元格字符 选取区域 - 开始 - 填充 - 两端对齐 合并的结果为：中国china台湾taiwan26五.合并单元格4.合并单元格，且内容都要保留 合并2个空单元格D2:D3，然后用格式刷，b列2个单元格会合并而且下面的单元格会保留内容。27五.合并单元格六六.自动调整打印区域自动调整打印区域1.选取区域 - 页面布局 - 设置打印区域。28u数据区域第一行避免空行数据区域第一行避免空行u数据区域第一列避免空列数据区域第一列避免空列u数据区域第一行数据区域第一行/列无合并单元格列无合并单元格2.公式 - 名称管理器：打印选取print_area，然后在下面的文本框输入以下公式：=OFFS

9、ET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)29六六.自动调整打印区域自动调整打印区域COUNTA(Sheet1!$A:$A)： 统计有多少统计有多少行行COUNTA(Sheet1!$1:$1)： 统计有多少列统计有多少列OFFSET（A1,行数行数,列数列数)： 以以A1为顶点根据行和为顶点根据行和 列数生成一个动态区域列数生成一个动态区域效果如下面动态图所示，添加新的列或行后，打印区域也会自动调整大小（注意虚线变化）30六六.自动调整打印区域自动调整打印区域PS:所添加内容第一行所添加内容第一行/第一列必须不为空值第一列必

10、须不为空值1.对比两个excel文件2.对比两个工作表3.工作表翻看4.表格翻看最后一行5.标题行显示6.打印顶端标题行31视图 - 全部重排 - 水平/垂直重排视图 - 新建窗口 - 全部重排 - 水平/垂直重排按ctrl+pgup（pgdn） 键可以快速翻看工作表双击表格中任一个单元格的下边线，即可跳到最后一行。视图 - 冻结窗格 - 冻结首行插入-页眉页脚-工作表-顶端标题行/左端标题列七.视图调整技巧7.报表日期修改 【例】一个文件有上百张月报表，到下一个月时需要把所有报表的日期更改为本月。32七.视图调整技巧【解】把第2N张表的日期所在单元格设置公式，用=号引用第1个月报表的日期，这

11、样以后只需要修改第1个报表的日期，其他的都会跟着自动变化了。8.整列添加空格 开始 - 点“向右缩进”。33七.视图调整技巧Ctrl+Alt+Tab八八.巧用复制粘贴巧用复制粘贴1.把公式转换为数值 342.复制格式 选以A1:C2向下拖动复制 - 仅填充格式，可以隔行填充颜色。Alt+E+S:调用选择性粘贴对话框调用选择性粘贴对话框3.把列宽调整一致4.行列转置5.批注选择性复制35八八.巧用复制粘贴巧用复制粘贴Alt+E+S:调用选择性粘贴对话框调用选择性粘贴对话框6.基本运算复制1000所在单元格，然后选取A列粘贴 - 选择性粘贴 - 运算：加。即可让A列值全加上1000。5.添加图表系

12、列 在图表上添加新系列，只需要复制 - 粘贴即可。36八八.巧用复制粘贴巧用复制粘贴6.自定义柱子图形复制插入到excel中的图形，再选中图表中的柱子，ctrl+v后可以把图形显示到柱子上。37八八.巧用复制粘贴巧用复制粘贴九.快速跳转列1.设置方法l 选取“大长表的包括标题行的前两行，公式 - 根据所选内容创建。（excel2003版，插入 - 名称 - 指定）38l 在弹出的窗口中，选中“首行2.使用方法 打开左上的地址框，选取列标题，即可跳转到指定的列。39九.快速跳转列3.删除名称方法 如果需要删除定义的名称，可以打开定义名称窗口，按shift键选取连续的名称（按ctrl键选不连续的）

13、，点击删除按钮即可批量删除。40九.快速跳转列41excel短公式看起来清爽，只是对新手来说有点难懂。什么样的公式最好的，主要从以下几点来判断：l 是否容易理解。l 是否易修改，易维护，公式能否表格的变化而自动调整。l 计算量尽可能的少，不会拖慢表格。十十.短公式让你随心所欲短公式让你随心所欲十十.短公式让你随心所欲短公式让你随心所欲1.隔列求和如下图所示，需要对112月份的计划和实际数进行求和(为方便截图)长公式：=B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3短公式：=SUMIF(B$2:Y$2,Z$2,B3:Y3)422.总计行如下图所示，需要对下表设置总计行。长

14、公式：C27 =C5+C8+C11+C14+C17+C20+C23+C26短公式：C27 =SUM(C2:C26)/243十十.短公式让你随心所欲短公式让你随心所欲3.多表求和如下图所示，需要对131日的报表进行汇总l 长公式：=1!B4+2!B4+3!B4+4!B4+5!B4+6!B4+7!B4+8!B4+9!B4+10!B4+11!B4+12!B4+13!B4+14!B4+15!B4+16!B4+17!B4+18!B4+19!B4+20!B4+21!B4+22!B4+23!B4+24!B4+25!B4+26!B4+27!B4+28!B4+29!B4+30!B4+31!B4l 短公式：=SU

15、M(1:31!B4)44十十.短公式让你随心所欲短公式让你随心所欲4.计算完成率如下表在计算完成率时，如果实际和计划数有一方为空或0，则公式返回空。l 长公式：D2 =IF(OR(B2=0,C2=0),B2/C2)l 短公式：D2 =IF(B2*C2,B2/C2,)45十十.短公式让你随心所欲短公式让你随心所欲5.奖励封顶如下图所示的“实际奖励”计算表中，如果“应奖励”数大于“最高奖励”，则按“最高奖励”金额，如果小于则按“应奖励”金额。l长公式：D2 =IF(B2=C2,D2,B2)l短公式：D2 =MIN(B2,C2)46十十.短公式让你随心所欲短公式让你随心所欲十一.Excel隐藏很深的

16、小功能2.隐藏0值。隐藏0值在实际工作中会经常用到，但我们必须 文件 - 选项 - 高级 - 使劲向下翻才能找到“在具有零值的单元格显示零”。471.数字设置为整数。 ctrl+1打开设置单元格格式窗口 - 数字 - 小数位置0。 （替换方法：按ctrl+shift+1组合键设置为货币型整数）3.给excel文件添加打开密码excel升级到10版后，添加保护密码依旧隐藏的很严实。【文件】 - 【信息】 - 【保护工作簿 】- 【用密码进行加密】48十一.Excel隐藏很深的小功能4.一次性删除所有工作表批注 选取单元格区域后可以批量删除批注，但如果表格范围大或需要删除批注的工作表很多，就需要使

17、用文档检查功能。49十一.Excel隐藏很深的小功能5.为批注或图形添加图片背景 批注也可以添加图片作为背景，但excel把这个功能隐藏的真够深的。50十一.Excel隐藏很深的小功能6.输入网址后禁止自动添加超链接如果在excel中输入http:/，会自动添加超链接。想禁止添加，你需要执行 文件 - 选项 - 校对 - 自动更正选项 - 键入时自动套用格式 - Internet及网络路径替换为超链接。51十一.Excel隐藏很深的小功能7.调整行间距 使用文档拼音功能52十一.Excel隐藏很深的小功能53Ctrl+1 0Ctrl+A ZCtrl+其他键F1 F12 十二十二.常用快捷键常用

18、快捷键54（1）F1:显示帮助。 Ctrl+F1：显示或隐藏功能区。 Alt+F1：创建当前范围内数据图表。 Alt+Shift+F1:插入新工作表。（2）F2：输入。 Shift+F2：添加单元格批注。 Ctrl+F2：打印预览。（3）F3：显示粘贴名称。 Shift+F3：插入函数。（4）F4：重复上一步。 Ctrl+F4=Ctrl+W 关闭当前表格。（5）F5：定位功能。可联合Ctrl+Enter使用快速填充数据。（6）F6=F10：调出快捷功能。 Ctrl+F6：切换下一工作簿。十二十二.常用快捷键常用快捷键55（7）F7：拼写检查。（8）F8：扩展选定区域。（9）F9：计算所有打开的

19、工作簿。 Ctrl+F9：将工作簿最小化为图标。（10）F10=F6：调出快捷功能，可与Alt+H联合使用。 Shift+F10=单击右键。（11）F11：创建数据图表。 Shift+F11：插入新表。（12）F12：另存为。十二十二.常用快捷键常用快捷键56（1）ctrl+1 显示“单元格格式”对话框 （2）ctrl+2 应用或取消加粗格式设置应用或取消加粗格式设置 （3）ctrl+3 应用或取消倾斜格式设置 （4）ctrl+4 应用或取消下划线 （5）ctrl+5 应用或取消删除线 （6）ctrl+6 在隐藏对象和显示对象之间切换 （7）ctrl+7 显示或隐藏“常用”工具栏 （8）ctrl+8 显示或隐藏大纲符号 （9）ctrl+9 隐藏选定的行 （10）ctrl+0 隐藏选定的列十二十二.常用快捷键常用快捷键573.Ctrl+AZCtrl+A 选择整个工作表 C