销售管理部EXCEL培训教材.ppt

-excel,销售管理部办公软件培训资料,基础篇 提高篇 技巧篇,目录,1,2,3,基础篇-office软件介绍,1,1989年发布的运行在386机器上的word1.0,开辟了office软件的历史,1992年发布word2.0的用户界面一直沿用到今日,office 97的发布轰动一时 标志着一个新的超丰富生产力应用程序时期的开始,office 2003仍然是目前使用范围最广的一款办公软件,基础篇-office软件介绍,1,office2007带来了全新的操作体验、更美观的效果和更强大的文字数据处理能力,2010年6月18日，微软office2010正式在中国发布，并且office2010迎来了全面的预装时代,基础篇-office软件介绍,1,office（全称：microsoft office），是一套由微软公司开发的办公软件，其常用的组件包含如下,1. word：文字处理程序，也是应用较为广泛的办公组件之一。主要是用来进行文本的输入、编辑、排版、打印等工作。 2. excel：主要是用来进行有繁重计算任务的预算、财务、数据汇总、图表制作、透视图的制作等工作。 3. powerpoint ：演示文稿程序，主要用来制作演示文稿和幻灯片及投影等。 4. outlook ：提供了一个统一的位置来管理电子邮件、日历、联系人和其他个人和项目组信息。 5. access ：数据库管理程序，主要拥有用户界面、逻辑和流程处理，可以存储数据。 6. visio:流程图和矢量绘图软件。,基础篇-基本界面,1,基础篇-基本概念,1,工作簿：（简单地说，一个excel文件就是一个工作簿） 工作表：一个工作簿由若干个工作表组成，最多可以有255张。 单元格：工作表中最基本的元素。 单元格地址：每个单元格都有其唯一的固定地址，由行号和列标构成。 表示：列标+行号 例如：a1、b3、a12 为了区分不同工作表的单元格，要在地址前加工作表名称； 例：sheet1!a1、sheet3!a1 当前单元格（活动单元格）：是指当前正在编辑的单元格。这时输入的数据保存在其中，该地址显示在名称框中。,基础篇-基本概念,1,对于行列数，2003和2007版的excel有较大差距，如果使用的表格行列数过多，请尽量使用高版本的excel，另外根据实际经验，如果将2007版本的excel中较多的行列粘贴到2003版本中，将会出现部分数据丢失的情况,基础篇-选取工作,1,选择某一个单元格：单击或移动光标键。（注意观察名称框的变化） 选择连续的几个单元格：单击第一个单元格，然后再按住 shift 键并单击最后一个单元格；或直接单击第一个单元格并按住左键拖动鼠标。 选择不连续的几个单元格：按住ctrl键并逐个单击要选择的单元格。 注意：选择多个单元格后，可对它们的格式进行统一设置。 选择某一行：单击其行号（拖动鼠标将选择多行） 选择某一列：单击其列标（拖动鼠标将选择多列） 选择所有单元格（整个工作表）：单击左上角的全选按钮。,基础篇-数据的类型,1,文本型（不可计算）：例如业务员的姓名、分公司名称、联系人、联系电话等这些信息是不参与运算的，所以可以将它们作为文本型数据来进行输入。 数值型（可计算）：例如车辆数量等 特殊数值（日期、时间、货币）：例如车辆的入库日期、售价等 公式：由等号（=）开始，由一些运算符号、函数构成的式子。,基础篇-输入文本,1,直接输入（自动左对齐） 注意：对于非字符的数字或日期作为文本输入，则应采用格式： =“83346194“ 或 83346194,基础篇-输入数值,1,直接输入（自动右对齐） 注意：可以设置单元格中数值小数点的位数。 方法：选中单元格后，单击菜单“格式”=“单元格”，或者右键单击单元格并选择“设置单元格格式”，在打开的对话框中进行设置。,在数值中可加逗号。 例如：2，123，456，不会影响运算 在负数前加负号或用一对括号表示一个负数。 例如：-123 等同于（123） 在正数前的“+”号忽略,基础篇-输入日期,1,在工作表的单元格中输入可识别的日期和时间数据时，单元格的格式会自动从“常规”格式转换为相应的“日期”或“时间”格式。 例如：以下是可识别的日期和时间数据： 03/12/13 03-12-13 2003/12/13 2003-12-13 23:00:34 11:00:34 pm 日期和时间的显示格式可以通过菜单“格式”=“单元格”或快捷菜单“设置单元格格式”来设置。 采用12小时制时，应加上 am 和 pm，时间与 am、pm 之间应有空格日期与时间之间应加上空格。 例如：23:00 与 11:00 pm 是等同的。,基础篇-输入货币、公式,1,输入货币 可以设置单元格格式然后直接输入数值。,输入公式 （先输入“=”号，然后输入计算公式，如“=4+10”，然后再回车）,基础篇-单元格其它操作,1,清除内容：与粘贴相同，可以有选择性地操作（清除只是清除该单元格的某一些内容，但是并不会将该单元格删除） 删除：将删除该单元格，邻近的单元格将填补此空位。 插入行、列：选中某一行或列后，单击菜单“插入”=“行/列” 删除行、列：选中某一行或列后，单击菜单“编辑”=“删除” 调整行高列宽：选中某一行或列后，然后按住鼠标拖拉行（列）号之间线条进行调整。如图所示,提高篇-工作表的操作-工作表的选择与重命名,2,工作表的重命名 方法： （1）双击要重命名的工作表标签，然后修改工作表标签名 或者 （2）选定要重命名的工作表标签 ，然后选菜单“格式” “工作表” “重命名” 注意：相应的单元格的标识也有所改变。如 sheet3 改为 aa，则相应的单元格也由 sheet3!a5 改为 aa!a5。,选择工作表 选定单个工作表：单击工作表标签（也即工作表的名称）即可。当一个工作表被选中时，工作表标签就变成白色。 选择多个工作表：按住shift键（选择连续）或ctrl键（选择不连续）并单击工作表的标签。,提高篇-工作表的操作-工作表的插入、删除、移动与复制,2,1. 插入工作表 选定插入工作表的位置，例如sheet1 选菜单“插入” “工作表”，可在 sheet1 之前插入一张新的工作表。 2. 删除工作表 选定工作表，然后用菜单“编辑” “删除工作表” 3. 移动和复制工作表 移动：选中工作表，然后拖动到目的位置。 复制：拖动的同时，要按住ctrl键 注意：以上的操作也可以右键单击工作表标签并从快捷菜单中选择操作。,提高篇-函数概述,2,函数是一些预定义的公式，使用一些被称为参数的特定值按特定的顺序或结构进行计算。excel 提供了300多个函数，包括财务函数、时间与日期函数、数学与三角函数、统计函数、查询与引用函数、数据库函数、文本函数、逻辑函数、信息函数等。,提高篇-函数的结构与输入,2,1函数的结构 函数名（参数1，参数2，参数n） 函数由函数名、左圆括号、以逗号分隔的参数和右圆括号组成。参数是函数的核心，参数可以是数字、文本、逻辑值、数组与函数等，参数间的分隔符一般用逗号，括号必须成对出现，且其前后不能有空格。 2函数的输入 （1）直接输入函数 与单元格输入公式的方法一样。首先输入等号“=”，然后输入函数本身。如：=sum(f2:f4) （2）使用粘贴函数输入 函数种类很多，要直接输入全部函数难度很大，使用粘贴函数输入方法，可以依据各类对话框一步一步地输入比较复杂的函数。 方法：单击“编辑栏”左边的“=”号，左边将显示某个函数名，此时可以单击 并选择您所要的函数，然后按提示进行操作。 （3）在公式中输入函数 有时需进行复杂的运算。例如：要输入下面一个公式： =a1-a3/(average(b2:b5)*100)+100 其步骤是：先选定单元格，在编辑栏中输入“=a1-a3（”，用粘贴函数输入法输入函数average（b2：b5），再输入“*100）+100”，按下enter键，即完成了对混合公式的输入。,提高篇-常用函数,2,sum（）：求和 格式：sum（n1,n2,） 参数：n1,n2,：为1到n个需要求和的参数。 average（）：求平均值 格式：average（n1,n2,） 参数：为需要找出平均值的1到n个参数。 count（）：计数 格式：count（n1,n2,） 参数：为需要计数的1到n个参数。 max（）：求最大值 格式：max（n1,n2,） 参数：为需要找出最大数值的1到n个参数。 min（）：求最小值 格式：min（n1,n2,） 参数：为需要找出最小数值的1到n个参数。,常用函数也可直接在快捷菜单中直接选择使用,提高篇-日期与时间函数,2,year（）：返回日期的年份值 格式：year（serial-number） 参数：日期值 month（）：返回日期的月份值 格式： month（serial-number） 参数：日期值 day（）：返回一个月中第几天的数值，介于1-31之间 格式：day（serial-number） 参数：日期值 now（）：返回当前的日期和时间 格式：day（） 参数：无 today（）：返回当前的日期 格式：today（） 参数：无,提高篇-查找与引用函数,2,vlookup（）：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。 格式：vlookup(lookup_value,table_array,col_index_num,range_lookup) 参数：lookup_value 为需要在数组第一列中查找的数值。lookup_value 可以为数值、引用或文本字符串。 table_array 为需要在其中查找数据的数据表。 col_index_num 为 table_array 中待返回的匹配值的列序号。 range_lookup 为一逻辑值，指明函数 vlookup 返回时是精确匹配还是近似匹配。如果为 true 或省略，则返回近似匹配值；如果 range_value 为 false，函数 vlookup 将返回精确匹配值。如果找不到，则返回错误值 #n/a。,表1,表2,以业务员名称为查找数值在表2中查找其得分并将值返回表1中,附：如有兴趣可以参考学习,提高篇-文本函数,2,left（）：从字符串左侧第一个字符起返回指定 格式：left（text,num_char） 参数：text-字符串 num_char-返回的字符个数,right（）：从字符串右侧第一个字符起返回指定 格式：right（text,num_char） 参数：text-字符串 num_char-返回的字符个数,mid（）：从文本字符串中指定起始位置起返回指定长度的字符 格式：mid（text,start_num,num_chars） 参数：text-字符串 start_num-起始位置 start_num-起始位置,value（）：将一个代表数值的文本字符串转换为数值 格式：value（text） 参数：text-字符串,提高篇-排序,2,排序：是指将数据按从大到小或从小到大的顺序排列。 操作步骤： （1）选定工作表中的任意一个单元格。 （2）单击“数据”菜单中的“排序”命令，弹出一个排序对话框。 （3）在该对话框中，选择要排序的关键字及排序方式。 还可使用“排序”按钮，进行简单的排序，其步骤是： （1）选定要排序列上的任一单元格。 （2）单击常用工具栏上的“升序”或“降序”按钮。 若要取消排序，可使用“撤消”按钮来恢复到排序前的状态。,提高篇-筛选1,2,数据筛选是指在众多的数据中挑选满足给定条件的数据子集（某些行）。在excel2000中提供了“自动筛选”和“高级筛选”命令。 自动筛选的步骤是： （1）在工作表中任选一个单元格。 （2）单击菜单“数据”=“筛选”=“自动筛选” （3）这时可以看到标题行中每个字段名的右端有一个筛选箭头 。,提高篇-筛选2,2,若要恢复原来的数据表格。可任选一个单元格，单击菜单“数据”=“筛选”=“自动筛选”，则解除筛选 若选择“全部显示”，数据全部恢复显示，但自动筛选记号仍然保留。,（4）单击字段名右边的 按钮，选择“自定义”，将得到一个对话框，在对话框中输入相应的选项，可得到相应的筛选结果。,提高篇-分类汇总,2,excel 可自动计算列表中的分类汇总和总计值。当插入自动分类汇总时，excel 将分级显示列表，以便为每个分类汇总显示和隐藏明细数据行。 若要插入分类汇总，请先将列表排序，以便将要进行分类汇总的行组合到一起。然后，为包含数字的列计算分类汇总。,提高篇-数据透视,2,1、建立数据表格。 2、选择要建立数据透视表的区域。 3、选择“数据/数据透视表和图表报告”命令，弹出对话框，从中选择待分析数据的数据类型。（默认第一个） 4、单击“下一步”按钮，弹出对话框，确定要建立数据透视表的数据源区域。 5、单击“下一步”按钮，确定数据透视表的位置。（新建工作表中） 6、选定要显示的数据的版式，将字段拖到标明“行”，“列”，“页”，“数据”的地方，表明该字段出现在相应的位置。 7、单击“完成”出现数据透视表。 8、点击”插入-图表“可直接将生成的数据透视表转换成图的形式 下面以一个示例说明如何将下图中左侧的数据转换成右侧的结果,提高篇-图表,2,利用excel制作图表的步骤 1.创建图表,2.单击“常用”工具栏上的“图表向导”按钮。或者选择菜单“插入图表”命令。打开“图表向导”对话框。,3. 默认“图表类型”为“柱形图”和子图表类型(图 3)，单击下方的“按下不放可查看示例”按钮，可以看到将得到的图表外观的预览。 如果需要生成其他图表类型，则直接在左侧选择即可。,提高篇-图表,2,4. 直接单击“完成”按钮，将在当前工作表中得到生成的图表,5. 对生成的图片进行调整和美化,如何美化图表： 尽量避免使用艳丽的颜色 去除图表的灰色背景和网格线 对不同的数据应使用适当的表格类型 必要的时候需将图表上加入数值标注或显示数据表,技巧篇1-工作簿操作技巧,3,1.打开固定工作簿,如果您有几个固定的工作簿需要同时打开，可以将它们保存为工作区。以后只要将工作区打开，excel就会打开其中的每个工作簿。保存工作区的方法是：打开需要同时使用的多个工作簿，按照使用要求调整好窗口大小和位置。再单击excel“文件”菜单下的“保存工作区”命令，输入工作区名称后单击“保存”按钮。打开工作区的方法是：选择“打开”对话框中的“文件类型”为“工作区”，选中要打开的工作区名称后单击“打开”按钮。 注意：每个工作簿所作的修改必须分别保存，保存工作区并不能保存其中的工作簿。,技巧篇1-工作簿操作技巧,3,2.多工作簿打印,如果要打印的工作簿较多，通常采用逐个打开的方法打印。如果这些工作簿位于一个文件夹内，可以采用下面的方法一次打印：单击“文件”菜单中的“打开”命令，按住ctrl键选中需要打印的所有文件。然后单击“打开”对话框中的“工具”下拉按钮，单击其中的“打印”命令，就可以一次打印多个工作簿了。,技巧篇1-工作簿操作技巧,3,3.快速关闭工作簿,如果您打开了多个excel 工作簿，如果希望不退出excel 快速关闭它们，可以按住shift键打开“文件”菜单，可以看到其中出现了“关闭所有文件”命令。选择该命令以后，excel 会根据打开文件的状态弹出相应的对话框，您只要根据需要选择合适的按钮即可。,单击“工具”菜单中的“加载宏”命令，选中对话框中的“自动保存”命令，单击“确定”按钮后“工具”菜单即可出现一条“自动保存”命令。单击该命令可打开“自动保存”对话框，对“自动保存时间间隔”、“保存选项”等进行设置。 注：该项功能仅适用于excel 2000，2003版本中无此宏,4.自动保存工作簿,技巧篇2-工作表操作技巧,3,如果需要同时打印工作簿中的多张工作表，可以采用下面的方法操作：首先选中要打印的多个工作表（按ctrl+鼠标左键多选），然后单击“文件”菜单中的“打印”命令即可,1.多工作表打印,技巧篇2-工作表操作技巧,3,2.更改标签颜色,excel 2003 允许用户修改工作表标签的颜色，其操作方法是：用鼠标右键单击工作表标签，选择快捷菜单中的“工作表标签颜色”命令。 打开“设置工作表标签”对话框，在调色板中选择一种颜色，然后单击“确定”按钮即可。,要快速隐藏活动单元格所在的行，可以按快捷键ctrl+9(不是小键盘上的数字键，下同)；要隐藏活动单元格所在列，可以按快捷键ctrl+0。,3.隐藏工作表行或列,可以按快捷键ctrl+ pageup / pagedown进行工作表之间的快速切换,4.快速切换工作表,技巧篇2-选中操作技巧,3,5.快速选中特定单元格,选中当前工作表中的任意一个单元格，单击“编辑”菜单下的“定位”命令。在打开的“定位”对话框中单击“定位条件”按钮，根据您要选中的区域类型，在“定位条件”对话框中选择需要选中的单元格类型。例如“常量”，此时可以复选“数字”、“文本”等项目，单击“确定”按钮以后，符合条件的所有单元格均被选中。,技巧篇3-单元格操作技巧,3,excel 允许将矩形区域内的任意单元格合并，具体操作方法是：选中要合并的单元格，单击“格式”工具栏中的“合并及居中”按钮。则所有选中的单元格被合并为一个，而其中的数据则被放到单元格中间。 注意：excel 只把合并区域左上角的数据放入合并后的单元格。为此，输入标题之类的文本不能断续分布在几个单元格中，否则合并过程中会截去一部分数据。,1.合并矩形区域,注意：excel 只把合并区域左上角的数据放入合并后的单元格。为此，输入标题之类的文本不能断续分布在几个单元格中，否则合并过程中会截去一部分数据。,技巧篇3-单元格操作技巧,3,excel单元格的默认情况下没有边框，excel 的“边框”工具经过改进，可以快速设置单元格的边框和线形、颜色和粗细等属性。操作方法是：鼠标右键单击excel 工具栏，选中快捷菜单中的“边框”打开工具栏。若按下其中的“绘图边框网格”按钮，用鼠标拖过某个单元格区域，可以快速应用其中显示的线条样式和颜色。若按下工具栏中的“擦除边框”按钮，然后拖过某个单元格区域，可以快速擦除所有的单元格边框。,2.快速设置边框,3.冻结窗格,选定单元格窗口冻结窗口 此后该单元格的左侧和上侧部分的单元格将被冻结，不会随着滚动条的移动而移动,技巧篇3-单元格操作技巧,3,对于某些不希望别人修改，仅要求其填写要求范围内的表格，可使用“保护单元格功能” 首先选中允许其修改的单元格区域，点击右键-“设置单元格属性”-“保护”，将其中“锁定”前面的对勾去除 在“工具”-“保护”中选择“保护工作表”,4.保护单元格,技巧篇3-公式与函数使用技巧,3,若需要应用公式的单元格分布没有规律，使用拖动法操作往往比较困难，“选择性粘贴”可以轻松解决这个问题。具体作法是：选中公式所在的单元格，然后按下ctrl+c键，在目标单元格点击右键并选“选择性粘贴”选项，在所出现的对话框中再选择“公式”即可,1.多处复制公式,如果某个单元格中的公式已经计算出了结果，而您不想保留公式只保留结果。可以选中公式所在的单元格，然后单击工具栏中的“复制”按钮。接着打开“粘贴”下拉列表，选择其中的“值”即可。,2.值替换公式,技巧篇3-公式与函数使用技巧,3,对于带有嵌套函数的复杂公式来说，一旦出现错误往往很难追踪。excel 专门为此增加了一个“公式求值”功能，您只要选中公式所在的单元格，单击工具栏中的“公式求值”按钮(没有的话可以打开“自定义”对话框中的“命令”选项卡，选择“类别”列表中的“工具”选项，将右边的“公式求值”按钮拖到工具栏)打开对话框。然后单击其中的“求值”按钮，就可以按照公式的执行顺序逐步观察公式的运算结果，从而找到错误的根源。这个功能有点类似编程时的“单步执行”，是公式查错的有力武器。,3.公式查错,技巧篇3-公式与函数使用技巧,3,excel 公式执行后显示计算结果，按ctrl+ 键(位于键盘左上角)，可使公式在显示公式内容与显示公式结果之间切换，方便了公式编辑和计算结果查看。,4.公式与结果切换,对不熟悉excel函数的用户来说，找到适合某项工作的函数并不容易。以下技巧绝对可以帮您的大忙：假如您要完成数据求平均任务，请单击工具栏中的“插入函数”按钮打开对话框，在其中的“搜索函数”框内输入“平均”，然后单击“转到”按钮。对话框就会列出用于求平均的函数，选中其中的某个函数，可以在对话框中看到它的功能说明。单击“有关该函数的帮助”链接，可以打开帮助查看更详细的信息。,5.查找适用函数,技巧篇3-公式与函数使用技巧,3,假如c1单元格包含公式“=sum(a2:b2)”，如果需要改变其中的单元格引用方式，可以使用以下技巧：首先选中c1单元格，然后选中工作表编辑栏中的引用a2:b2，按f4即可将相对引用更改为绝对引用和混合引用。例如，按一次f4将a2:b2改变为$a$2:$b$2，再按一次f4将$a$2:$b$2改变为a$2:b$2。如此反复，即可在相对引用、绝对引用和混合引用之间切换。,6.快速更改引用,7.固定公式地址,excel公式一般使用相对地址引用单元格，若将一个含有地址引用的公式复制到新位置，公式中引用的单元格地址会发生变化。如果复制前将公式的引用修改为绝对引用，则公式粘贴后的地址就可以保持不变。,技巧篇4-快速操作技巧,3,假如一个excel工作表含有大量数据。如果想将含有特定字符的数据行删除，可以对该工作表进行“自动筛选”，将含有的特定字符的行筛选出来并选中。然后单击鼠标右键，选择快捷菜单中的“删除行”命令即可。,1.快速删除特定数据行,若数据输入结束后发现了错误，只要没有敲回车键(即光标没有跳至其他单元格)，按esc就可以撤销刚才的输入。,2.快速撤销输入,选中需要转换行列的区域，单击excel 工具栏中的“复制”按钮。选中转换后行列所在区域左上角的单元格(注意它不能位于转换前的区域内)，单击“编辑”菜单下的“选择性粘贴”命令，在“选择性粘贴”对话框内选中“转置”复选项，单击“确定”即可完成行列的快速转换。,3.行列快速转换,技巧篇4-快速操作技巧,3,如果有一列数据需要分解为多列，可以采用下面的技巧：首先将需要分解的列选中，单击“数据”菜单中的“分列”命令，即可打开“文本分列向导”对话框。选中其中的“分隔符号”或“固定列宽”(前者适用于数据列中带有逗号或制表符等分隔符的情况，后者适用于手工分隔数据的情况)，就可以在向导的提示下完成数据列的分解。,4.快速分解数据列,技巧篇4-快速操作技巧,3,如果您拥有滚轮鼠标，只要将光标放到垂直或水平滚动条上，按住ctrl向下或向上拨动垂直滚轮，即可任意改变工作表的显示比例。,5.快速修改显示比例,excel “插入”菜单没有提供插入时间和日期的命令，使用以下快捷键可以快速插入当前日期或时间：ctrl+；(分号)在单元格内插入当前日期，ctrl+shift+；(分号)插入当前时间，按ctrl+；(分号)以后输入一个空格，再按ctrl+shift+；(分号)，可以插入当前日期和时间。,6.快速插入时间(日期),要想将某一单元格(或区域)的格式(字体、字号、行高、列宽等)应用于多个地点。可以将上述单元格(或区域)选中，然后双击工具栏中的“格式刷”，将光标拖过待设置格式的所有区域，完成后单击“格式刷”退出。,7.重复执行操作,技巧篇4-快速操作技巧,3,excel一般采用文本左对齐、数值右对齐的方法区分文本和数值，许多函数也允许文本格式的数字参与运算。为了杜绝数字格式错误导致的混乱，excel 2003 允许用户将文本格式的数字转换为数值。具体方法是：一旦文本单元格中输入了数字，其左上角就会出现一个绿色的小三角，表示智能标记已经发现了问题。您只要重新选中这个单元格，单元格右边就会显示一个智能标记按钮。单击该按钮即可打开下拉列表，您只要选择其中的 “转换为数字”，就可以将文本格