第9章Excel基本操作与数据输入1.ppt

EXCEL基础知识基础知识 EXCEL2007工作界面工作界面 基本数据输入基本数据输入输入文本型数据输入文本型数据设置数据输入限制条件设置数据输入限制条件数据序列的自动填充数据序列的自动填充等差与等比序列的自动填充等差与等比序列的自动填充数据输入技巧数据输入技巧输入数值型数据输入数值型数据公式中的运算符公式中的运算符公式的基础知识公式的基础知识公式中的运算顺序公式中的运算顺序公式的基本操作公式的基本操作公式的应用公式的应用Excel实用技巧：常用函数实用技巧：常用函数技巧与提高技巧与提高公式的输入公式的输入公式的编辑公式的编辑引言引言 Excel2007的用途的用途 EXCEL2007的用途的用途创建统计表格创建统计表格 进行数据计算进行数据计算建立多样化的统计图表建立多样化的统计图表打印数据打印数据分析与筛选数据分析与筛选数据操作要求：制作右图表格，并统计出平均成绩、男生总成绩、女生总成绩、最高分和最低分。学生已有WORD2007的基础，制作上图表格，目的一在于加强学生的OFFICE知识融会贯通；目的二在于让学生找到操作过程中的难点，从而引入新课内容。与EXCEL2003进行比较u工作簿工作簿：Excel中用于储存数据的电子表格就是工作簿，其扩展名为“.xlsx”，启动Excel 2007后系统会自动生成一个工作簿。u工作表工作表：是显示在工作簿中由单元格、行号、列标以及工作表标签组成的表格。行号显示在工作表的左侧，依次用数字1、21048576表示；列标显示在工作表上方，依次用字母A、BXFD表示。默认情况下，一个工作簿包括3个工作表，用户可根据实际需要添加或删除工作表。u单元格与活动单元格单元格与活动单元格：它是电子表格中最小的组成单位。工作表编辑区中每一个长方形的小格就是一个单元格，每一个单元格都用其所在的单元格地址来标示，并显示在名称框中。工作表中被黑色边框包围的单元格被称为当前单元格或活动单元格，用户只能对活动单元格进行操作。u工作表标签工作表标签：工作表是通过工作表标签来标识的，单击不同的工作表标签可在工作表之间进行切换。 1.新建工作簿； 2.插入工作表； 3.删除工作表； 4.重命名工作表； 5.设置工作表标签； 6.移动或复制工作表（同一工作簿/不同工作簿）； 7.并排比较工作表； 8.同时显示多个工作簿 9.拆分与冻结窗口； 10.添加批注，设置批注格式 11.保护工作簿与工作表； 12.设置允许用户编辑区域；工作簿、工作表的基本操作工作簿、工作表的基本操作 1.新建工作簿新建工作簿 方法同新建WORD文档 2.插入工作簿插入工作簿 默认情况下，工作簿包括3个工作表。但在实际应用中，所需工作表的数目可能各不相同，有时需要向工作簿添加一个或多个工作表。 方法一： 2.插入工作簿插入工作簿 方法三、方法四： 方法二： 3.删除工作簿删除工作簿 方法同插入工作表提示：若删除的工作表为空工作表，则系统会直接将其删除。 4.重命名工作表重命名工作表 方法一：双击要重命名的工作表标签，输入新的工作表名称； 方法二：右击要重命名的工作表标签，要快捷菜单中选择“重命名”，输入新的工作表名称。 5.设置工作表标签颜色设置工作表标签颜色右击工作表标签，在快捷菜单中选择工作表标签颜色，设置颜色。6. 移动或复制工作表（同一工作簿/不同工作簿）移动：选择要移动的工作表，沿工作表标签行拖动选定的工作表标签即可。复制：选择要复制的工作表，按住CTRL键的同时拖动工作表，到目的地释放鼠标，然后松开CTRL键。在工作簿内移动或复制工作表注意：源工作表名称、复制后的工作表名称6. 移动或复制工作表（同一工作簿/不同工作簿）方法同在工作簿内的移动或复制。需要注意的是，要求源工作簿和目标工作簿均打开。在工作簿间移动或复制工作表提示：在左图所示对话框中，选择“建立副本”复选卡，可以实现复制工作表的操作。7. 并排比较工作表并排比较工作表使同一工作簿的不同工作表或不同工作簿的工作表在窗口中同时显示，以便进行比较。方法：视图窗口：新建窗口，EXCEL将建立一个新窗口，并在新窗口中显示当前活动工作表。然后在当前工作簿窗口中单击要进行并排比较的工作表标签，切换至该工作表，在“窗口”组中单击“并排查看”按钮，则两个表并排显示即可。8. 同时显示多个工作簿同时显示多个工作簿打开要同时显示的多个工作簿，然后在视图选项卡的“窗口”组中单击“全部重排”按钮，打开“重排窗口”对话框，在对话框中设置排列方式。9. 拆分与冻结窗口拆分与冻结窗口折分窗口：独立地显示并滚动工作表中的不同部分。方法：选择要拆分的某一单元格位置，单击视图：窗口：拆分按钮。思考：取消拆分的方法是怎样的？9. 拆分与冻结窗口拆分与冻结窗口冻结窗口：如果要在工作表滚动时保持行列标志或其他数据可见，可以通过冻结窗口功能来固定显示窗口的顶部和左侧区域。方法：选择要拆分的某一单元格位置，单击视图：窗口：冻结窗格按钮，选择冻结选项。思考：取消冻结的方法是怎样的？10. 添加批注添加批注批注：简短的、注释性的文字说明。方法:SHIFT+F2 审阅批注新建批注 思考：批注的格式设置、删除的方法是怎样的？11. 设置保护工作簿和工作表设置保护工作簿和工作表作用：限制对工作簿和工作表进行访问，防止其他人更改工作表中的部分或全部内容，查看隐藏的数据行或列、查阅公式等。利用这些限制，可以防止其他人添加或删除工作簿中的工作表，或者查看其中的隐藏工作表。11. 设置保护工作簿和工作表设置保护工作簿和工作表设置保护工作簿思考：取消保护的方法是怎样的？设置方法：审阅：更改：保护工作簿保护工作簿保护结构和窗口保护结构和窗口对话框输入密码确定11. 设置保护工作簿和工作表设置保护工作簿和工作表思考：取消保护的方法是怎样的？设置方法：审阅：更改：保护工作表保护工作表对话框，选择“保护工作表及锁定的单元格内容”复选框，输入密码。选择“选定锁定单元格”、“选定未锁定的单元格”12.设置允许用户编辑区域设置允许用户编辑区域 设置方法：先设置区域再设置保护工作表。新建空白工作簿，新建空白工作簿， 1.新建工作簿考勤表； 2.输入如图数据； 3.工作表更名为“2012年4月”； 4.冻结第1，2行与A、B列； 5.为“旷工天数”添加批注，内容为“公司将辞退当月旷工天数超过5天的员工”；给电器销量统计表设置工作簿、工作表保护、允许用户编辑区域（G3：G18）。9.2.1 输入文本型数据 文本型数据是指由汉字、英文或数字组成的文本串，如“季度1”、“AK47”等都属于文本型数据。单击要输入文本的单元格，然后直接输入文本内容，输入的内容会同时显示在编辑栏中，输入完毕后，按【Enter】键或单击编辑栏中的“输入”按钮确认输入。 当输入的文本型数据的长度超出单元格的长度时，如果当前单元格右侧的单元格为空，则文本型数据会扩展显示到其右侧的单元格中；如果当前单元格右侧的单元格中有内容，则超出部分会被隐藏。此时单击该单元格，可在编辑栏中查看其全部内容。 9.2.2 输入数值型数据 在Excel中，数值型数据包括数值、日期和时间，它是使用最多，也是最为复杂的数据类型，一般由数字09、正号、负号、小数点、分数号“/”、百分号“%”、指数符号“E”或“e”、货币符号“$”或“￥”和千位分隔符“，”等组成，Excel自动将数值型数据沿单元格右侧对齐。 1输入负数 如果要输入负数，必须在数字前加一个负号“”，或在数字两端添加圆括号。 2输入分数 分数的格式通常为“分子/分母”，如果要在单元格中输入分数，应先输入“0”和一个空格，然后再输入分数值；如果不输入“0”直接输入分数值，Excel会将该数据作为日期格式处理。 3输入小数 如果要输入小数，直接在相应位置插入小数点即可。当输入的数据量较大，且具有相同的小数位数时，可以利用“自动插入小数点”功能输入小数。 4数值格式设置 当输入的数据位数较多时，如果输入的数据是整数，则数据会自动转换为科学计数表示方法。如果输入的是小数，在单元格能够完全显示，则不会进行任何调整；如果小数不能完全显示，系统会根据情况进行四舍五入调整。 通过单击“开始”选项卡上“数字”组中的“增加小数位数”按钮 和“减少小数位数”按钮 ，可调整当前单元格中的小数位数。 利用“开始”选项卡上“数字”组中的按钮 ，可以为数值设置会计数字格式、百分比样式和千位分隔样式。利用“数字格式”下拉列表还可以为单元格快速设置各种特定格式。 5输入日期和时间 在Excel中可以使用多种格式输入日期，可以用斜杠“/”或者“-”来分隔日期中的年、月、日部分。如果省略年份，则系统以当前的年份作为默认值，显示在编辑栏中。 在Excel中输入时间时，可以用冒号“：”分开时间中的时、分、秒。系统默认的时间是按24小时制的方式输入，因此，如果要以12小时制的方式输入，应该在输入的时间后面键入一个空格，然后输入“AM”或“PM”。如果缺少空格，则所键入的数据会被当作文本型数据处理。 9.2.3 设置数据输入限制条件 设置某些区域的输入限制条件 输入前有输入提示，出错有错误提示 实例：设置员工人事档案中年龄列的有效性（允许18-60之间的整数）9.2.3 设置数据输入限制条件 单击设置了数据有效性的单元格，会显示输入信息提示 当在设置了数据有效性的单元格中输入了不符合条件的数据时，会出现“错误”提示框 运用后的效果选择要设置数据有效性的单元格，单击“数据”选项卡上“数据工具”组中的“数据有效性”按钮，在打开的“数据有效性”对话框的各选项卡中进行设置并确定即可。 制作如下EXCEL工作簿，进行如下设置：1.给当日营业额添加货币符号;2.在B9单元格设置数据有效性,允许日期为”2012-4-1至2012-4-30之间3.出错警告: 样式:停止, 标题: 输入无效日期; 错误信息:请确认后,再次输入 填充一系列数字、日期或其他项目初始选择扩展序列1，2，34，5，6，9：0010：00，11：00，12：00MonTue,Wed,Thu星期一星期二，星期三，星期四JanFeb,Mar,Apr一月，四月七月，十月，一月Jan99，Apr99Jul99,Oct9,Jan91月15日，4月15日7月15日，10月15日1999，20002001，2002，20031月1日，3月1日5月1日，7月1日，9月1日，text1,textAtext2,textB,text3,textC产品1产品2，产品3，表 可扩展的填充序列9.3.1 数据序列的自动填充 方法1利用填充柄快速填充数据 填充柄是位于选定单元格或单元格区域右下角的黑色小方块。将鼠标指针移动到填充柄上时，鼠标指针会由白色的空心十字形变为黑色的实心十字形。 如果希望在一行或一列相邻的单元格中输入相同的数据，可首先在一个单元格中输入数据，然后向上、下、左或右拖动填充柄即可。 2利用“填充”列表快速填充 利用“填充”列表可以将当前单元格或单元格区域中的内容向上、下、左、右相邻单元格或单元格区域做快速填充。 3多组序列的自动填充 在单元格中输入序列的初始值，然后同时选中单元格，向指定方向拖动该单元格区域右下角的填充柄，释放鼠标后，Excel将自动填充序列的其他值。 9.3.2 等差序列与等比序列的自动填充 1等差序列 对于等差序列的自动填充，可以采用输入等差序列的前两组或前两个数值，以确定序列的首项和步长值，然后再拖动填充柄向上、下、左、右进行填充。 知识点：等差数列，如果一个数列从第二项起，每一项与它的前一项的差等于同一个常数，这个数列就叫做等差数列，这个常数叫做等差数列的公差。2等比序列 在单元格中输入等比序列的第一个数据，然后拖动鼠标选中要填充等比序列的单元格区域，单击“开始”选项卡上“编辑”组中的“填充”按钮 ，在展开的列表中单击“系列”项， 选中“等比序列”单选钮，在“步长值”编辑框中输入数值，然后单击“确定”按钮。 知识点：等比数列，如果一个数列从第2项起，每一项与它的前一项的比等于同一个常数，这个数列就叫做等比数列。操作要求：1.制作如右图的员工工资表要求:员工编号用序列填充;2.设置C3:C11单元格的数据有效性,文本长度大于1,出错警告内容为:姓名长度出错.3.设置D3:E11转换为货币格式. 操作要求： 计算出1季度商品销售情况表中的利润。 操作结果： 9.5.1 公式中的运算符 1算术运算符 算术运算符有6个。其作用是进行基本的数学运算，并产生运算结果。 2比较运算符 比较运算符也有6个。它们的作用是比较两个值，并得出一个逻辑值，即“TRUE（真）”或“FALSE（假）”。 3文本运算符 文本运算符只有一个。是用文本运算符（&），可加入或连接一个或多个文本串，以产生一个长文本。例如：“世界人民”&“热爱和平”就会产生“世界人民热爱和平”。 4引用运算符 引用运算符有3个。它们的作用是将单元格区域进行合并运算。 思考：是否与WORD中含义相同？ 比如，对于A1=B1+C1+D1+E1+F1公式，如果使用引用运算符，就可以把这一公式写成：A1=SUM(B1:F1) 9.5.2 公式中的运算顺序 (运算符优先级） 通常情况下，如果公式中只用了一种类型的运算符，Excel会按照运算符的特定顺序从左向右计算公式；如果公式中用到了多种类型的运算符，Excel会按照一定的优先级由高到低进行运算。 9.5.3 输入公式 对于简单的公式，可以在单元格中直接输入：首先单击需输入公式的单元格，接着输入=（等号），然后输入公式内容，最后单击编辑栏上的“输入”按钮或按【Enter】键完成输入，可在当前单元格中得到计算结果。 9.5.4 编辑公式 在单元格中输入公式后，如果发现错误，可以对其进行修改，也可以将公式删除。 要修改公式，可单击含有公式的单元格，然后在编辑栏中进行修改，修改完毕按【Enter】键即可。 要删除公式，可单击含有公式的单元格，然后按【Delete】键。 删除公式 默认情况下，单元格中只显示公式计算的结果，而公式本身则只显示在编辑栏中。为了方便检查公式的正确性，可以设置在单元格中显示公式。显示公式 9.5.5 移动与复制公式 1移动公式 要移动公式，应首先选中包含公式的单元格，然后将鼠标指针移到单元格的边框线上，当鼠标指针呈十字箭头形状时，按住鼠标左键不放，将其拖到目标单元格后释放鼠标即可。 思考：快捷键ctrl+c与ctrl+v可以使用吗？2复制公式 先选中含有公式的单元格，然后将鼠标移动到单元格的右下角的填充柄 处，此时光标呈黑色十字形状，按住鼠标左键不放并拖动，将其拖到目标单元格后释放鼠标即可快速复制公式。 在Excel2007中，使用公式计算出结果后，可以设置删除该单元格中的公式，并保留结果。删除公式 删除G4单元格中的公式，保留值。操作方法：选择单元格；复制；选择性粘贴对话框中选择数值；确定操作要求：用公式计算出年度考核总分、排名、应获年终奖金此道题里面的排名、应获年终奖金两项，涉及到函数的应用。引入下节内容函数基本知识EXCEL2007将组合具有特定功能的一组公式在一起以形成函数。与直接使用公式进行计算相比较，使用函数进行计算的速度更快，同时减少了错误的发生。函数一般包含3个部分：等号、函数名和参数，如=SUM(A1：F10)，表示对A1：F10单元格区域内所有数据求和。嵌套函数：在某些情况下，需要将某个公式或函数的返回值作为另一个函数的参数来使用，这就是函数的嵌套使用。如=if(G3100,SUM(E3:E7),0)9.6.1函数分类 财务函数：财务函数：可以进行一般的财务计算。时间和日期函数：时间和日期函数：可以在公式中分析和处理日期值和时间值。数学和三角函数：数学和三角函数：可以处理简单和复杂的数学运算。统计函数：统计函数：用于对数据进行统计分析。查找和引用函数：查找和引用函数：在工作表中查找特定的数值或引用的单元格。数据库函数：数据库函数：分析工作表中的数值是否符合特定条件。文本函数：文本函数：可以在公式中处理文字串。逻辑函数：逻辑函数：可以进行真假值判断，或者进行复合检验。信息函数：信息函数：用于确定存储在单元格中的数据类型。工程函数：工程函数：用于工程分析。多维数据集函数：多维数据集函数：主要用于返回多维数据集的重要性能指标、属性、层次结构中三位成员或组等。9.6.2 函数应用方法 1.熟悉函数的名称和使用方法，即可直接在单元格中输入函数。使用函数时，应先在单元格中输入等号“=”进入公式编辑状态，然后输入函数名称，再输入一对括号，在括号内可输入一个或多个参数，参数之间要用逗号分隔。 例如：=SUM（A1:B5,C1:E5），表示计算A1到B5单元格区域、C1到E5单元格区域的总和。其中SUM为“求和”函数，A1:B5和C1:E5是SUM函数的参数。2.如果对函数还不太熟悉，最好使用Excel 2007提供的来输入函数。 单击要输入函数的单元格，再单击编辑栏上的“插入函数”按钮，打开“插入函数”对话框，在“或选择类别”下拉列表框中选择函数类别，在“选择函数”列表框中选择所需函数，并单击“确定”按钮，在打开的“函数参数”对话框中分别对参数进行设置，最后确定即可。 知识提高 常用函数应用举例9.7.1 引用单元格和单元格区域 要在公式中引用单元格，只需单击要在其中输入公式的单元格，接着输入等号（=），然后直接输入该单元格地址或单击该单元格，最后按【Enter】键即可。 如果要引用单元格区域，应先输入单元格区域起始位置的单元格地址，再输入冒号（:），最后输入单元格区域结束位置的单元格地址。 9.7.2 引用不同工作表中的单元格 要引用同一工作簿中不同工作表中的单元格或单元格区域，应先输入工作表名称，然后输入惊叹号“!”，再输入单元格地址。例如：Sheet2!C3。 9.7.3 引用不同工作簿中的单元格 要引用不同工作簿中的单元格或单元格区域，应先输入方括号“”，再在方括号“”中输入工作簿名称，然后输入工作表名称，再输入惊叹号“!”，最后输入单元格地址。 工作簿名称工作簿名称.xlsx工作表名称！单元格地址工作表名称！单元格地址 9.7.4 相对引用、绝对引用与混合引用 1相对引用 相对引用即是指引用单元格的相对地址，其引用形式为直接用列标和行号表示单元格，例如A1。如果公式所在单元格的位置改变，引用也随之改变。默认情况下，公式使用相对引用。 在复制公式时，由于公式所在单元格的位置改变，Excel会自动调整复制公式的引用，以便引用相对于当前公式位置的其他单元格。 2绝对引用 绝对引用即是指引用单元格的精确地址，与包含公式的单元格位置无关，其引用形式为在列标和行号的前面都加上“$”符号。不论公式复制或移动到什么位置，引用的单元格地址都不会改变。 $B$4 2绝对引用 举例： 用函数计算成绩表中的排名。在区域里面用到绝对引用。分析：此处如果用相对引用，会出现什么情况？ 3混合引用 混合引用既是指既包含绝对引用又包含相对引用的引用，如B$5或$B5等，用于表示列变行不变或列不变行变的引用。 如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。 操作要求：利用函数求出总分、排名、等级等各项。保留两位小数。