如何做好EXCEL表格

品质改变世界三一集团2015年泵送春节培训如何做好EXCEL表格2目录第二讲Excel使用技巧第一讲Excel简介第四讲Excel图表制作第三讲Excel函数与公式3目录第二讲Excel使用技巧第一讲Excel简介第四讲Excel图表制作第三讲Excel函数与公式4Excel简介一、Excel界面Excel是微软开发的一套功能完整、操作简易的电子计算表软体，提供丰富的函数及强大的图表制作功能，有助于高效地建立与管理资料。功能区工作表编辑区功能页次快速存取工具列显示比列视图布局工作表切换标签5Excel简介二、认识功能页次Excel中默认功能操作分为8大页次，包括文件、开始、插入、页面布局、公式、数据、审阅和视图。各页次中收录相关的功能群组，方便使用者切换、选用。6Excel简介1、文件功能页次“文件”页次，可以执行与文件有关的命令，例如新建、打开新文件、选项、权限、保存及传送文件、打印等。7Excel简介1、文件功能页次保护工作簿功能：此工作薄是最终状态，只读，不能编辑此工作薄需要密码才能打开，打开后，可编辑此工作表某些项不能编辑此工作薄结构或窗口不能编辑可设置编辑权限，需要联机8Excel简介2、开始功能页次快速设置单元格格式常用字体格式、单元格边框、拼音等设置对齐方式、文字方向、自动换行、合并单元格等设置粘贴选项、格式刷等设置条件格式、表格套用格式、单元格样式设置插入/删除单元格、行、列，单元格大小等设置求和、排序、筛选、查找等设置9Excel简介3、插入功能页次插入迷你图、切片器、超链接等插入图片、形状、剪切画、固定格式图表等绘制柱形图、折线图、饼图等各种类型图表创建数据透视表/图、插入表格等插入文本框、页眉页脚、艺术字、对象等插入公式、符号10Excel简介4、页面布局功能页次缩放打印的宽度、高度、缩放比例设置设置页边距、纸张方向、纸张大小、打印区域、背景等设置主题版式、颜色、字体、效果等网格线、标题打印设置图片的对齐、组合、旋转等设置11Excel简介5、公式功能页次创建、编辑、删除和查找工作薄中使用的名称，可在公式中使用这些名称替代单元格引用插入财务、逻辑、文本、数学等函数显示公式、追踪公式引用的单元格、错误检查、显示监视窗口等指定计算公式的条件、自动或手动等计算12Excel简介6、数据功能页次刷新数据，显示数据连接等从其它地方获取数据数据排序、筛选、高级筛选等将有规律的列分成多个单独的列，删除重复项、设置数据有效性、下拉菜单、模拟分析等创建组、取消组合、分类汇总等13Excel简介7、审阅功能页次简繁字体转换、翻译等拼写检查、信息检索、同义词库等新建批注、批注显示等保护工作表、共享工作薄、修订、保护指定单元格等14Excel简介8、视图功能页次显示标尺、编辑栏、网格线、标题视图选择、自定义视图、全屏显示等显示比列调整、缩放选定区域新建窗口、排列窗口、冻结窗格、拆分窗口、并排查看、同步滚动窗口等设置宏操作：查看、录制等15目录第二讲Excel使用技巧第一讲Excel简介第四讲Excel图表制作第三讲Excel函数与公式16Excel使用技巧1、设置Excel文件自动恢复的保存位置当运行Excel时，出现意外情况，停止工作了，在Excel中可设置自动保存，并设置自动保存恢复的文件位置。选择[文件]/[选项]，打开“Excel选项”对话框，然后在左侧选择“保存”选项，在右侧界面的“自动恢复文件位置”文本框中可以看到恢复文件的默认保存位置，此时在该文本框中可输入常用文件夹路径，保存到常用的文件夹中，方便查找。2、设置工作簿中默认的工作表张数在工作中经常使用的工作表一般在1-2张，其他的工作表用不上，为了让工作界面看起来更加简洁，就需要设置工作薄中默认的工作表张数。Excel工作簿默认的工作表数量是3张，如在实际操作中用不到这么多或需要更多张的工作表，此时可选择[文件]/[选项]组，打开“Excel选项”对话框，选择“常规”选项，在右侧的“包含的工作表数”数值框中即可设置默认工作表张数。17Excel使用技巧3、快速移动/复制单元格快速移动和复制单元格，能有效快速地替换单元格和复制相同的数据，同时也能提高编辑速度。先选定单元格，然后移动鼠标指针到单元格边框上，显示符号时，按下鼠标左键并拖动到新位置，然后释放按键即可移动。若要复制单元格，则在释放鼠标之前按下[Ctrl]键。4、快速修改单元格次序修改单元格次序，能迅速调换不同单元格的次序，在有多类数据的表格中运用最为方便。选定单元格，按下[Shift]键，移动鼠标指针至单元格边缘，显示符号时，进行拖放操作。上下拖拉时鼠标在单元格间边界处会变成一个水平“工”状标志，左右拖拉时会变成垂直“工”状标志，释放鼠标按钮完成操作后，单元格间的次序即发生了变化。18Excel使用技巧5、冻结单元格首行或首列用户在处理数据量较大的表格时能根据自己的需要固定表格的某个区域，方便数据的查看与对比。选择[视图]/[窗口]组，单击“冻结窗格”按钮，在弹出的下拉列表中选择“冻结首行”或“冻结首列”选项，可使用户在滚动工作表其他部分时保持首行或首列可见。6、冻结拆分的窗格为了方便固定和查看所需的单元格数据，可以运用冻结拆分单元格来实现。根据自己的需要选择冻结的单元格区域，选择[视图]/[窗口]组，单击“冻结窗格”按钮，在弹出的下拉列表中选择“冻结拆分窗格”选项，系统自动以被选择的单元格为分界线，将工作表分为4个区域。19Excel使用技巧7、不打印出表格的背景颜色有时在打印表格时会连同表格的背景颜色一起打印出来，这时我们就可以通过设置不打印表格背景颜色。选择[页面布局]/[工作表选项]组，单击右下角的按钮，打开“页面设置”对话框，并选择“工作表”选项卡。选中“打印”栏中的复选框，单击确认即可。在“单色打印”下，字体颜色全部使用黑色打印，单元格底纹填充颜色不会被打印出来。8、将工作表的行号和列标也打印出来有时在打印工作表时需要将行号和列标也显示出来，我们就可以设置将行号和列标一并打印出来。打开“页面设置”对话框，选择“工作表”选项卡，在“打印”栏选中复选框，然后单击确定按钮，再进行打印时便可打印输出行号和列标。20Excel使用技巧9、让粘贴数据随源数据自动更新在编辑表格数据时，有时修改一些统一性数据时，全部修改非常麻烦，这时只需利用“粘贴连接”功能来粘贴修改数据。复制需要的数据，选择需要粘贴的单元格后单击鼠标右键，选择[选择性粘贴]/[粘贴链接]命令。可适用于在Excel之间、外部文件中的数据粘贴，使复制的数据与数据源之间保持关联粘贴，让粘贴的数据始终跟随数据源进行自动更新。10、用下拉列表快速输入数据如果某些单元格区域中要输入的数据很有规律，这时可以设置下拉列表实现输入操作。选择单元格，选择[数据]/[数据工具]组，单击“数据有效性”按钮，在弹出的下拉列表中选择“数据有效性/设置”，在“有效性条件”的“允许”下拉列表框中选择“序列”，在“来源”文本框中输入如“泵车、拖泵、车载泵”等，单击确认即可。21Excel使用技巧11、绘制平直直线在绘制平直直线时，可以运用快捷键来快速绘制。在Excel绘制直线时，按[Shift]键，则绘制出来的直线就是平直的。另外，按下[Shift]键绘制矩形即变为正方形、绘制椭圆形即变为圆形。12、将图表另存为图片文件将图表另存为文件，可以使图表能应用更多的格式进行编辑。打开“另存为”对话框，在“保存类型”下拉列表框中选择“网页（*.htm，*.html）”选项，然后单击保存，之后在保存位置打开工作簿生成的文件夹，打开该文件夹（后缀名为.files），在其中便可找到图表对应的图片（后缀名为.png）。22Excel使用技巧常用快捷键说明Ctrl+Shift+(取消隐藏选定范围内所有隐藏的行。Ctrl+Shift+&将外框应用于选定单元格。Ctrl+Shift_从选定单元格删除外框。Ctrl+Shift+:输入当前时间。Ctrl+;输入当前日期Ctrl+1显示“单元格格式”对话框。Ctrl+Y重复上一个命令或操作（如有可能）。Ctrl+Z使用“撤消”命令来撤消上一个命令或删除最后键入的内容。Alt+Enter可在同一单元格中另起一个新行。Ctrl+9隐藏选定的行。Ctrl+0隐藏选定的列。Ctrl+Alt+V显示“选择性粘贴”对话框。Ctrl+N新建工作薄Ctrl+O打开工作薄23Excel使用技巧案例1工资条打印具体的操作方法如下：Step1：添加“辅助列”列。在单元格E2-E4中输入等差序列1、3、5…；Step2：复制表头到单元格区域A5：D7中，并在单元格E5-E7中输入等差序列2、4、6…；Step3：在E8-E10中输入等差序列3、5、7…；Step4：将“辅助列”按降序排列即可。①②③24目录第二讲Excel使用技巧第一讲Excel简介第四讲Excel图表制作第三讲Excel函数与公式25Excel函数与公式一、Excel常用函数函数：是预定义的公式，通过使用一些参数的特定值来按特定的顺序和结构执行计算。函数的使用方式=函数名称（参数1，参数2，参数3……)函数的嵌套特性•一个函数可以引用其他函数的结果作为参数•Excel最多支持七层嵌套函数常用函数有：•IF•AND\OR•MID\LEFT\RIGHT•财务函数26Excel函数与公式1、IF函数如果指定条件的计算结果为TRUE，IF函数将返回某个值；如果该条件的计算结果为FALSE，则返回另一个值。例如，如果A1大于10，公式=IF(A110,大于10,不大于10)将返回“大于10”，如果A1小于等于10，则返回“不大于10”。IF(logical_test,[value_if_true],[value_if_false])条件为真条件为假27Excel函数与公式2、AND/OR函数AND函数所有参数的计算结果为TRUE时，返回TRUE；只要有一个参数的计算结果为FALSE，即返回FALSE。AND(logical1,[logical2],...)OR函数在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；任何一个参数的逻辑值为FALSE，即返回FALSE。OR(logical1,[logical2],...)28Excel函数与公式3、MID\LEFT\RIGHT函数MID返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。MID(text,start_num,num_chars)LEFT根据所指定的字符数，返回字符串中第一个字符或前几个字符。LEFT(text,[num_chars])RIGHT根据所指定的字符数返回文本字符串中最后一个或多个字符。RIGHT(text,[num_chars])29Excel函数与公式4、财务函数PMT：基于固定利率及等额分期付款方式，返回贷款的每期付款额。FV：基于固定利率及等额分期付款方式，返回某项投资的未来值。PV：返回投资的现值。现值为一系列未来付款的当前值的累积和。NEPR：基于固定利率及等额分期付款方式，返回某项投资的总期数。RATE：返回年金的各期利率。TYPE：数字0或1，用以指定各期的付款时间是在期初还是期末。PMT(rate,nper,pv,[fv],[type])30Excel函数与公式二、Excel常用公式公式：是可以进行以下操作的方程式：执行计算、返回信息、操作其他单元格的内容、测试条件等等。公式始终以等号(=)开头。工作表中可输入的公式类型：=5+2*3将5加到2与3的乘积中。=A1+A2+A3将单元格A1、A2和A3中的值相加。=SQRT(A1)使用SQRT函数返回A1中值的平方根。=TODAY()返回当前日期。=UPPER(hello)使用UPPER工作表函数将文本“hello”转换为“HELLO”。=IF(A10)测试单元格A1，确定它是否包含大于0值。31Excel函数与公式二、Excel常用公式公式的组成：包含下列部分内容或全部内容：函数、引用和常量。①函数：PI()函数返回值PI：3.141