周玉敏老师--Excel数据分析高级应用-（1）认识Excel

重庆邮电大学经济管理学院 周玉敏  要求掌握基本的Excel操作，例如：单元格格式的设 置、简单的公式使用等。  对基本概念熟悉，如：绝对引用、相对应用。  以应用案例的方式讲解Excel的相关知识。  理论学时16学时、实验学时16学时  推荐 教材 民兵爆破地雷教材pdf初中剪纸校本课程教材衍纸校本课程教材排球校本教材中国舞蹈家协会第四版四级教材 ： 电子工业出版社 《Excel与管理数据分析（第3版）》杜茂康 主编  推荐学习网站： http://club.excelhome.net/forum.php 1. Excel基础知识 2. 数据输入和表格设计 3. 函数的使用 4. 图表的制作 5. 数据分析  Excel的发展历史 你认为利用Excel能做哪些工作？ ◦ 画表格 ◦ 计算：求和、计算平均值 结论：Excel易学、功能简单 咱们做一点测试题吧 你能得多少 分呢？ 1．启动Excel的方法 通过Windows任务栏选择“开始”按钮 通过Excel 快捷方式 双击Ｅxcel文件的名字或图标 ２、退出Ｅxcel的方法 单击Excel 窗口右上角的关闭按钮 选择“文件”菜单中的“退出”菜单项。 按快捷键Alt+F4。 3、Excel界面 功能区 工作表区  功能区的主要控件 ◦ Excel 2007用功能区取代了早期版本中的菜单、 ◦ 工具栏和大部分任务窗格，包括按钮、库和对话框内容  选项卡 ◦ 功能区由“开始、插入、页面布局、公式、数据”等选项卡组成。 各选项卡是面向任务的，每个选项卡以特定任务或 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 为主题组织 其中的功能控件。 ◦ 能区中的选项卡是动态的，为了减少屏幕混乱，某些选项卡平时是 隐藏的，在执行相应的操作时，它们会自动显示出来。  组 ◦ Excel 2007将以前版本中那些隐藏在菜单和工具栏中的命令和功 能，以面向任务的方式放置在不同的逻辑组中，每个逻辑组能够完 成某种类型的子任务。  快速访问工具栏 ◦ 其中包含一组独立于当前所显示的选项卡的命令，无论用户选择哪 个选项卡，它将一直显示，为用户提供操作的便利。用户可以根据 需要设置其 中的工具按钮.  工作表区的主要对象  工作表 ◦ 就是人们常说的电子表格，是Excel 中用于存储和处理数据的主要 文档。由一些横向和纵向的网格组成，横向的称为行，纵向的称 为列。Excel 2007的一个工作表最多可有1,048,576 行、 16,384 列数据（而Excel 2003的工作表最多有65 536行、256 列）。当前正在使用的工作表称为活动工作表。  工作表标签 ◦ 每个工作表有一个名称，体现在工作表标签上。单击工作表标签 按钮可使对应的工作表成为活动工作表，双击工作表标签按钮可 改变它们的名称。  插入工作表标签按钮 ◦ 在默认情况下，Excel 只打开3个工作表：Sheet1，Sheet2， Sheet.单击插入新工作表按钮就会后面插入一个新工作表sheet4 ，再单击一次就会插入新工作表sheet5……  行号、列标题 ◦ 工作表由1048576行组成，每行的编号称为行号． ◦ 工作表的每列用英文字母标志，称为列标题  单元格 ◦ 工作表中的网格，它用列标题和行号定位。如A1就表示Ａ列第１ 行对应的单元格  单元格区域 ◦ 是指多个连续单元格的组合。表示形式如下： 左上角单元格:右下角单元格  例如A2:B3代表一个单元格区域，包括A2、B2、A3、B3 ◦ 只包括行号或列号的单元格区域代表整行或整列。如，1:1表示 第一行的全部单元格组成的区域，1:5则表示由第1到5行全部单 元格组成的区域；A:A表示第一列全部单元格组成的区域，A:D则 表由A、B、C、D四列的全部单元格组成的区域。  编辑栏 ◦ 用于显示、修改活动单元格的内容  全选按钮 ◦ 用于选定整个工作表所有的单元格。  工作表查看方式 ◦ 工作表下边框的右侧提供的三个按钮用于切换工作表的查 看方式  工作表缩放 【例1.1】某单位的工资数据如下表所示，计算每个职工的总 工资。假设个人所得税的税率为：收入1600以下，所得税 率为0%；收入1600~2500，所得税率为5%；收入 2500~3500，所得税率为8%；收入3500~4500，所得税 率为12%；收入4500以上，所得税率为15%。计算各位职 工应缴纳的所得税，以及缴税后的实际收入。 姓名 职称 日期 基本工资 水费 电费 气费 奖金 张贴民 高工 2009-3-1 2389 244.32 91.2 210.9 871 李虎 高工 2009-3-1 2389 155.48 59.88 218.9 900 王景 工程师 2009-3-1 1579 244.89 201 171.3 529 ……  在EXCEL中可以轻松制作出例1-1的报表，如图所示 输入A1：H9中 的原始档案数据 1、在I2中输入工资 计算公式：=D2- E2-F2-G2+H2 2、向下复制此公式 到I9 1、在H2中输入计 算公式：=I2-K2、 向下复制此公式到 H9 1、在K2中输入工资 计算公式：=I2*J2 2、向下复制此公式 到K9 输入J列的 税率 1.3.1 工作薄与工作表的关系  工作簿 ◦ 在Excel中创建的文件叫做工作簿，扩展名是.xlsx ◦ 工作薄由工作表组成，新建工作薄只有默认的３个工作表。 但可以根据需要增加，个数不限。 ◦ 新建第一个工作薄的默认名：book1.xlsx。  工作表 ◦ 由一些横向和纵向的网格组成的表格。一个工作表最多有 1,048,576行、16,384列。 ◦ 工作表由工作表标签区别，如sheet1、sheet2 ◦ 工作薄与工作表的关系 1、工作薄与 模板 个人简介word模板免费下载关于员工迟到处罚通告模板康奈尔office模板下载康奈尔 笔记本 模板 下载软件方案模板免费下载  模板 模板即模型、样板，是一种已经建立好的特殊工作薄，已 在其中的工作表中设计好了许多功能，如单元格中的字 体、字型，工作表的样式和功能（如财务计算，学生成 绩统计，会议安排等。  工作薄创建 Excel总是根据模板创建工作薄，创建的工作薄具有与模板 结构、内容和功能。在没有指定建立工作薄模板的情况下， Excel会据默认模板建立工作薄，这种工作薄在默认情况 下只有Sheet1、Sheet2和Sheet3三个工作表。 2、新建默认的空工作薄，三种方法 ◦ 启动Excel，Excel就会自动建立一个名为Boolk1.xlsx; ◦ 在已启动Excel的情况下，按Ctrl+N； ◦ 单击功能区左上角的Office 按钮 ，然后从弹出的文件菜 单中选择“新建” ； 3、根据旧工作薄建立工作薄 （1）单击Office 按钮 ，从弹出的文件菜单中选择“新建 ”。Excel 将弹出“新建工作薄”对话框。 （2）单击“根据已有内容新建…”，然后在弹出的“根据 现有工作薄新建”对话框找到并选择已有工作薄的名称， 单击该对话框中的“新建”按钮。 4、根据模板建立工作薄  Excel 2007模板类型  已安装的模板：安装Excel时安装在本地计算机中 的模板；  我的模板：用户自已创建的模板；  在线模板：微软公司网站上的在线工作薄模板， 在用这类模板创建新工作薄时，Excel会自动从微 软的网站中下载相关模板到用户的本地计算机中， 并用它创建新工作薄。当然，前提是用户计算机 与Internet网络相连接（即Online，在线）。 5、在线模板示例 下面的图表是用Microsoft Office Online”中的“预算”模 板类中的“商务旅行预算”模板建立的工作薄。在该工作 薄中，只需要输入D列的费用和F列的数字，Excel就会自 动计算出H列的费用，这一功能是由“商务旅行模板”提 供的。 5、在线模板示例 下图是用“Microsoft Office Online”中的“库存控制”模 板类中的“设备资产 清单 安全隐患排查清单下载最新工程量清单计量规则下载程序清单下载家私清单下载送货清单下载 ”模板建立的工作薄。在该工作 薄中预设了许多功能，在L、N、P、Q、R列中都预设了 计算公式，只要填定了其余列的数据，Excel就会自动计 算出这些列的数据。 5、在线模板的建立方法 (1) 确定计算机已连接上了Internet网络 (2) 单击“Office按钮”→新建→Microsoft Office Online.将连接到微软的在线模板网站。 (3)从微软网站的在线模板网站中选择需要的模板， 就会从网站中将选择的模板下载到本机中，同时将 应用。 1、保存文件 ◦ 方法一 单击“快速访问工具栏”中的 按钮保存文件。 ◦ 方法二 单击Office 按钮，从弹出的文件菜单， 选择“另存为”菜 单项，然后在“另存为”对话框中输入文件名。 2、打开文件 ◦ 单击Office 按钮，从弹出的文件菜单， 选择“打开”菜 单项，然后在“打开”对话框中选择要打开文件的名称。 １、公式的概念 ◦ Excel的公式由运算符、数值、字符串、变量和函数组成。 ◦ 公式必须以等号“=”开头，即在Excel的单元格中，凡是 以等号开头的输入数据都被认为是公式。在等号的后面可 以跟数值、运算符、变量或函数，在公式中还可以使用括 号。 ◦ 例如： =10+4*6/2+(2+1)*50 就是公式，可以在任何单元格中输入此公式．Excel会把公式的 计算结果显示在相应的单元格中． 2、运算符 ◦ 在Ｅxcel公式中可以使用运算符。 运算符 运算功能 优先级 ( ) 括号 1 - 负号 2 % 百分号 3 ^ 乘方 4 *与/ 乘、除法 5 +与- 加、减法 6 & 文本连接 7 =、<、>、 <=、>=、<> 等于、小于、大于、小于 等于、大于等于、不等于 8 3、说明 ◦ 括号的运算级别最高，在Excel的公式中只能使用小括号， 无中括号和大括号。小括号可以嵌套使用，当有多重小括 号时，最内层的表达式优先运算。同等级别的运算符从左 到右依次进行。 ◦ “&”为字符连接运算，其作用是把前后的两个字符串连接 为一串。  例如，"ABC"&"DEF"的结果为“ABCDEF”，"财务"&"经济"的 结果为“财务经济”。 1、引用的概念 ◦ 引用即在公式中用到了其他单元格在表格中的位置。引用 的作用在于标识工作表中的单元格或单元格区域，并指明 公式中所使用的数据的单元格位置。 ◦ 引用不同工作簿中的单元格称为链接。 2、引用的类型 ◦ A1引用 ◦ R1C1引用 3、相对引用 ◦ 相对引用也称 为相对地址引 用，是指在一 个公式中直接 用单元格的列 标与行号来取 用某个单元格 中的内容。  相对引用的例子 ◦ 【例1.2】某班某次期末考试成绩如图1.13所示，计算各 同学的总分，即F列的数据。 在F2计算公式 =C2+D2+E2 然后向下复制该 公式！ 4、绝对引用 ◦ 绝对引用总是在指定位置引用单元格。如果公式所在单元 格的位置改变，绝对引用保持不变。 ◦ 绝对引用的形式是在引用单元格的列号与行号前面加“$” 符号。  比如，$A$1就是对A1单元格的绝对引用。  绝对引用案例 ◦ 【例1.3】某品牌 皮鞋批发商3月份 的销售数据如图所 示，每双皮鞋的单 价相同，计算各皮 鞋代销商场应支付 的总金额， 。 在D6输入公式： =$c$3*C6 然后向下复制此公式！ 5、混合引用 ◦ 混合引用具有绝对列和相对行，或是绝对行和相对列。比如， $A1、$B1 ◦ 案例 6、三维引用 ◦ 对同一工作薄内不同工作表中相同引用位置的单元格或区域 的引用称为三维引用。引用形式为： Sheet1:Sheetn!单元格（区域） ◦ 例如，Sheet1:sheet8!C5和Sheet1:Sheet8!B2:D6都是三 维引用，前者包括Sheet1~sheet8这8个工作表中每个工作 表的C5单元格，后者包括此8个工作表中每个工作表的 B2:D6区域。 7、内部引用与外部引用 1）引用相同工作表中的单元格，例： =G3+G5+G10*10 2）引用同一工作簿不同工作表中的单元格，例 =Sheet1!G3+Sheet2!G5+Sheet2!E27 3）引用已打开的不同工作簿中的单元格，例： =[Book1]Sheet1!$IL$4+[Book2]Sheet2!$E$7 4）引用未打开的不同工作薄中的单元格，例如： ='C:\dk\[Book1.xlsx]Sheet2'!$B$4+'C:\dk\[Book1. xlsx]Sheet1'!$C$6 5）同一公式中存在几中不同的引用，例： =[Book1]Sheet1!$A$4+Sheet1!G7+F9