EXCEL实例教程学习教案

1、会计学1EXCEL实例教程实例教程第一页，共66页。第1页/共66页第二页，共66页。第2页/共66页第三页，共66页。工作簿文件的默认扩展名：.xlsx（未启用宏），是基于(jy)XML的新的文件格式启用宏的Excel工作簿文件扩展名：.xlsm模板文件扩展名：.xltx默认情况下，可包含三个工作表-03的扩展名：.xls 07、10、13的扩展名：.xlsx（建议使用07以上版本）工作表的标签为Sheet1、Sheet2等，标签可设置颜色工作表的有关操作(1) 插入 (2)删除(shnch) (3)重命名(4) 移动或复制 (5)保护工作表(6) 隐藏工作表（2）工作表）工作表（Sprea

2、dsheet） ：第3页/共66页第四页，共66页。1、单元格的访问方式(fngsh)：（单元格的地址） A1方式(fngsh)： 列号行号，如B3 R1C1方式(fngsh)：R行C列，如R3C32、列数、行数列标：A,BAAZZ,AAAXFD，共有16384列行号：1,2,3.1048576行最后一个(y )单元格的地址：XFD1048576第4页/共66页第五页，共66页。1矩形(jxng)区域 的种表示： A2:D5 A5:D2 D5:A2 D2:A5 2单行、单列、若干行、若干列区域: 6:6 H:H 3:5 D:F 3两区域公共部分：(交) (B4:K6D1:F8) (空格)4多区

3、域： (并) A1:B5, K8, 9:9, H:J (逗号)第5页/共66页第六页，共66页。单元格区域的表示单元格区域的表示(biosh)方法方法第6页/共66页第七页，共66页。正确正确(zhngqu)(zhngqu)答案：答案：C C如果当前工作表abcc.xlsxSheet2中的单元格E37的内容为公式： =SUM(Sheet3!D5,G8,练习题_函数.xlsxSheet2!B3)，则单元格E37的内容涉及_个工作表? A）1 B）2 C）3 D）4第7页/共66页第八页，共66页。第8页/共66页第九页，共66页。第9页/共66页第十页，共66页。不同的数据类型在单元格内默认(m

4、rn)的对齐方式不同，数值型右对齐，文本型左对齐，逻辑型居中。第10页/共66页第十一页，共66页。除了默认日期效果外，如果想让日期数据显示为其他状态，可以先以Excel可以识别的最简易的形式输入日期，然后通过设置单元格格式(g shi)来使其一次性显示为所需要格式(g shi)。第11页/共66页第十二页，共66页。通过选择性粘贴达到特定目的：直接使用复制粘贴功能复制数据是按原格式进行复制。除此之外可以使用选择性粘贴来达到待定目的，实现格式、公式的复制，复制进行计算(j sun)等等。通过数据复制进行数据运算通过数据复制进行数据运算第12页/共66页第十三页，共66页。通过(tnggu)选择

5、性粘贴达到特定目的：直接使用复制粘贴功能复制数据是按原格式进行复制。除此之外可以使用选择性粘贴来达到待定目的，实现格式、公式的复制，复制进行计算等等。第13页/共66页第十四页，共66页。数据有效性的设置是让制定单元格所输入的数据满足一定(ydng)的要求。 可以有效地防止在单元格中输入无效的数据。1，设置数据有效性2，设置鼠标指向时显示提示 信息3，自定义数据输错后的警告 提示信息第14页/共66页第十五页，共66页。 设置(shzh)数据有效性为可选择序列有些单元格中需要输入的数据有几个固定值可选，此时不必手动输入，而通过数据有效性功能来设置可选择序列(xli)，从而实现选择输入。第15页

6、/共66页第十六页，共66页。第16页/共66页第十七页，共66页。第17页/共66页第十八页，共66页。、trim、find、searchntext统计类函数Sum、count、average、max、min、int、mod、round、stedvSumif、sumifs、countif、countifs、averageif、averageifs第18页/共66页第十九页，共66页。（一）统计(tngj)类函数（1）取整函数）取整函数(hnsh)INT函数函数(hnsh)功能：功能：用于将制定数值向下取整为最接近的整数语法：INT(number)第19页/共66页第二十页，共66页。（2）M

7、OD函数函数(hnsh)功能功能(gngnng)：用于求两个数值相除后的余数，其结果的正负号与除数相同：用于求两个数值相除后的余数，其结果的正负号与除数相同语法：MOD(number，divisor)（一）统计类函数第20页/共66页第二十一页，共66页。（3） SUMIF函数函数(hnsh)功能：用于按照指定条件对若干单元格、区域或引用功能：用于按照指定条件对若干单元格、区域或引用(ynyng)求和求和语法：SAMIF(range，criteria,sam_range)（一）统计类函数第21页/共66页第二十二页，共66页。（4） SUMIFS函数函数(hnsh)功能：对某一区域内满足功能：

8、对某一区域内满足(mnz)多重条件的单元格求和。（多重条件的单元格求和。（07新增函数）新增函数）语法： SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, )（一）统计类函数第22页/共66页第二十三页，共66页。（一）统计(tngj)函数（5） AVERAGEIF函数函数(hnsh)功能：用于返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。功能：用于返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。语法： AVERAGEIF(range,criteria,average

9、_range)可以与round函数一起使用第23页/共66页第二十四页，共66页。（一）统计(tngj)函数（6） AVERAGEIFS函数函数(hnsh)功能：用于返回某个区域内满足功能：用于返回某个区域内满足多重条件多重条件的所有单元格的平均值（算术平均值）。的所有单元格的平均值（算术平均值）。（07新增函数）新增函数）语法：AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2)第24页/共66页第二十五页，共66页。（一）统计(tngj)函数（7） MAX函数函数(hnsh)功能：用于返回

10、数据集中的最大数值。功能：用于返回数据集中的最大数值。语法： MAX(num1,num2,)第25页/共66页第二十六页，共66页。（一）统计(tngj)函数（4） COUNTA函数函数(hnsh)功能：用于返回包含任何值（包括数字、文本或逻辑数字）的参数列表中的单元格个数或项数。功能：用于返回包含任何值（包括数字、文本或逻辑数字）的参数列表中的单元格个数或项数。语法： COUNTA(value1,value2,)第26页/共66页第二十七页，共66页。（一）统计(tngj)函数（8） COUNTIF函数函数(hnsh)功能：用于计算区域中满足给定条件的单元格个数。功能：用于计算区域中满足给定

11、条件的单元格个数。语法： COUNTIF(range,criteria)第27页/共66页第二十八页，共66页。（一）统计(tngj)函数（9） COUNTIFS函数函数(hnsh)功能：用于计算某个区域中满足多重条件的单元格数目。（功能：用于计算某个区域中满足多重条件的单元格数目。（07新增函数新增函数）语法： COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,)第28页/共66页第二十九页，共66页。（二）文本(wnbn)函数（1） FIND函数函数(hnsh)功能：功能：用于在第二个文本串中定位第一个文本串，并返回第

12、一个文本串的 起始位置。语法： FIND(find_text,within_text,start_num)第29页/共66页第三十页，共66页。（二）文本(wnbn)函数（2） LEFT函数函数(hnsh)功能：用于返回文本字符串中的第一个字符或前几个字符。功能：用于返回文本字符串中的第一个字符或前几个字符。语法： LEFT(text,num_chars)第30页/共66页第三十一页，共66页。（二）文本(wnbn)函数（3）MID函数函数(hnsh)功能：用于返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。功能：用于返回文本字符串中从指定位置开始的特定数目的字符，该数目由用

13、户指定。语法： MID(text,star_num, num_chars)num_chars：要返回的字符数。如果num_chars 超过或等于文本的字符数（包括 start 处的字符），将返回字符串中从 start_num到字符串结束的所有字符。第31页/共66页第三十二页，共66页。（二）文本(wnbn)函数（4）RIGHT函数函数(hnsh)功能：用于根据所指定的字符数返回文本字符串中最后一个或多个字符。功能：用于根据所指定的字符数返回文本字符串中最后一个或多个字符。语法： RIGHT(text,num_chars)第32页/共66页第三十三页，共66页。（二）文本(wnbn)函数（5）

14、 TEXT函数函数(hnsh)功能：用于将数值转换为按指定数字格式表示的文本。功能：用于将数值转换为按指定数字格式表示的文本。语法：TEXT(value, format_text)第33页/共66页第三十四页，共66页。（三）逻辑(lu j)函数（1） IF函数函数(hnsh)功能：用于根据指定条件来判断其真（功能：用于根据指定条件来判断其真（TRUE）、假（）、假（FALSE），从而返回其相对应的内容，最常用的函数之一。），从而返回其相对应的内容，最常用的函数之一。语法：IF(logical_text,value_if_true, value_if_fulse)第34页/共66页第三十五页，

15、共66页。（四）日期(rq)函数（1） DATEDIF函数函数(hnsh)功能：用于计算两个日期之间的年数、月数和天数。功能：用于计算两个日期之间的年数、月数和天数。语法：DATEDIF(date1,date2,code)第35页/共66页第三十六页，共66页。（1） DATEDIF函数函数(hnsh)功能：用于计算两个日期之间的年数功能：用于计算两个日期之间的年数(nin sh)、月数和天数。、月数和天数。语法：DATEDIF(date1,date2,code)=DATEDIF(A1,TODAY(),Y)计算年数差=DATEDIF(A1,TODAY(),M)计算月数差=DATEDIF(A1,

16、TODAY(),D)计算天数差Y 时间段中的整年数。 M 时间段中的整月数。 D 时间段中的天数。 MD start_date 与 end_date 日期中天数的差。忽略日期中的月和年。 YM start_date 与end_date 日期中月数的差。忽略日期中的日和年。 YD start_date 与 end_date 日期中天数的差。忽略日期中的年。第36页/共66页第三十七页，共66页。（四）日期(rq)函数（2） NETWORKDAYS函数函数(hnsh)功能：用于返回起始日期和结束日期之间完整的工作日数值。工作日不包括周末和专门制定的假期。功能：用于返回起始日期和结束日期之间完整的工

17、作日数值。工作日不包括周末和专门制定的假期。语法： NETWORKDAYS(start_date,end_date,holidays)第37页/共66页第三十八页，共66页。（四）日期(rq)函数（3） WORKDAY函数函数(hnsh)功能：用于返回某日期之前或之后相隔指定工作日的某一日期的功能：用于返回某日期之前或之后相隔指定工作日的某一日期的日期值日期值。工作日不包括周末和专门制定的假期。工作日不包括周末和专门制定的假期。语法： WORKDAY(start_date,days,holidays)第38页/共66页第三十九页，共66页。（五）查找(ch zho)函数（1） VLOOKUP函

18、数函数(hnsh)功能：用于在表格或数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。功能：用于在表格或数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。语法： VLOOKUP (lookup_value,table_array,col_index_num,range_lookup)建议用column函数混合使用第39页/共66页第四十页，共66页。第40页/共66页第四十一页，共66页。第41页/共66页第四十二页，共66页。第42页/共66页第四十三页，共66页。如何在不破坏(phui)源数据的情况下又能实时的比较随意的获取某些结果？数据透视表是集排序、筛选、组合、

19、汇总等多个功能于一体的集合体它是一种交互式报表它有强大、灵活的处理数据的功能熟练掌握该项功能能给你的工作效率显著(xinzh)的提高数据透视表数据透视表第43页/共66页第四十四页，共66页。假设存在一个包含(bohn)销售数字的 Excel 工作表。它展示了数千行有关两个国家/地区的销售人员及其每天销售量的数据。有大量数据需要处理这些数据逐行列出 并分成多列。您如何从工作表中 获取信息并了解 所有数据的意义？您可以使用数据透视表。它们能够将数据转换为 短小简洁(jinji)的报表，并准确地告诉您需要了解的 内容。第44页/共66页第四十五页，共66页。在开始使用透视数据表之前，请观察(gun

20、ch)一下您的 Excel 工作表，以确保它为报表做好了准备。当您创建数据透视表时，源数据 的每列都将成为一个(y )字段并且可在 报表中使用。字段概括了源数据 中的多行信息。第45页/共66页第四十六页，共66页。报表字段的的名称来自源数据中的列标题(biot)。因此，请确保您具有源数据中工作表第一行各列的名称。标题下的其余(qy)各行应该包含 同一列中的类似项。例如，文本应该位于一列，数字在另一 列，而日期又在另一列。换句话说， 包含数字的列不应该 包含文本，等等。第46页/共66页第四十七页，共66页。最后，要用于数据(shj)透视表的数据(shj)应该不包含空列。最好不要包含(bohn

21、)空行。例如，应该删除用于将 数据块相互隔开的空白行。第47页/共66页第四十八页，共66页。此处介绍如何(rh)开始使用数据透视表。您可以使用此处显示的“创建数据透视表”对话框。1.准备好数据后，请单击数据中的任意位置。2.在“插入”选项卡上的“表”组中，单击 “数据透视表”，然后(rnhu)再次单击“数据透视表”。将打开 “创建数据透视表”对话框。3.已为您选中“选择一个表或区域” 选项。“表/区域”框显示 所选数据的范围，该范围 可根据需要进行修改。第48页/共66页第四十九页，共66页。这是在关闭“创建数据透视表”对话框后，在新工作(gngzu)表中看到的内容。其中一侧是为数据透视表

22、准备的布局区域。另一侧是“数据透视表字段列表”。该列表 显示来自源数据的列标题(biot)。 如前所述，每个标题(biot)都是一个字段： “国家/地区”、 “销售人员”等。第49页/共66页第五十页，共66页。通过将“数据(shj)透视表字段列表”中显示的任何字段移动到布局区域，可创建数据(shj)透视表。为此，请选中字段名称旁边的 复选框，或者(huzh)右键单击字段名称，然后选择 要将该字段移动到的位置。第50页/共66页第五十一页，共66页。现在(xinzi)您已做好构建数据透视表的准备。您为报表选择的字段取决于您要了解的内容。第一个问题：每人的销售量是多少？要得到(d do)此答案，

23、您需要有关 销售人员及其销售量的数据。因此，请在 “数据透视表字段列表”中，选中 “销售人员”和“订购量”字段旁边的复选框。然后，Excel 会将每个字段放置到默认的布局区域。 动画：右键单击鼠标，然后单击“播放”。第51页/共66页第五十二页，共66页。现在(xinzi)您已做好构建数据透视表的准备。您为报表选择的字段取决于您要了解的内容。第一个问题：每人的销售量是多少？要得到答案，您需要(xyo)有关 销售人员及其销售量的数据。 因此，请在“数据透视表字段列表”中，选中 “销售人员”和“订购量”字段旁边的 复选框。然后，Excel 会将各个字段放置(fngzh)到 默认布局区域。 第六节第

24、六节 构建数据透视表构建数据透视表第52页/共66页第五十三页，共66页。图表最左侧的灰色表提供了有关(yugun)报表如何根据所选字段自动显示的的概念视图。“销售人员”字段中的数据（销售人员 的姓名，不包含数字） 在报表的左侧显示为行。“订购量”字段中的数据 （包含数字）正确(zhngqu)显示在 右侧的区域中。以下(yxi)为详细信息。第六节第六节 构建数据透视表构建数据透视表第53页/共66页第五十四页，共66页。在“订购量”字段之前还是(hi shi)之后选中“销售人员”字段旁边的复选框没有关系。Excel 每次都会自动将其放到右侧位置。不含数字的字段将位于左侧，含有(hn yu) 数

25、字的字段将位于右侧，而不管 您选择字段的顺序如何。第六节第六节 构建数据构建数据(shj)(shj)透视表透视表第54页/共66页第五十五页，共66页。就是这样简单。只需单击两次鼠标，您就可以对每位销售人员(rnyun)的销售量一目了然。刚开始的时候，只回答(hud)一两个 问题就停下来也是很不错的；报表未必非要很 复杂才有用。数据透视表可以提供一种快捷的方式来获取 简单的答案。接下来，请不必担心在构建报表时出现错误。 使用 Excel，您可以轻松地进行尝试并查看数据 在不同报表区域的外观。 此处提供关于该主题(zht)的最后几点提示。第六节第六节 构建数据透视表构建数据透视表第55页/共66

26、页第五十六页，共66页。现在，您了解了每位销售人员的销售量。但源数据(shj)展示了销售人员在加拿大和美国这两个国家的相关数据(shj)。因此，您可能要问的另一个问题是： 每位销售人员在每个国家的销售量是多少？要得到答案，您可以(ky)将“国家/地区”字段 作为“报表筛选”添加到数据透视表中。您可以(ky)使用报表 筛选来重点关注报表数据的子集，通常是 产品线、时间跨度或地理区域。第56页/共66页第五十七页，共66页。通过将“国家/地区” 字段用作报表筛选，可以查看(chkn)加拿大或美国各自的报表，也可以查看(chkn)这两个国家的总销售量。该动画说明如何将“国家/地区”字段添加为 报表筛

27、选。右键单击“数据透视表字段列表” 中的“国家/地区”字段，然后单击“添加到报表筛选”， 并且(bngqi)从该处进行添加。动画：右键单击鼠标，然后(rnhu)单击“播放”。第七节第七节 按国家按国家/ /地区查看销售量地区查看销售量第57页/共66页第五十八页，共66页。通过将“国家/地区” 字段用作报表筛选，可以查看(chkn)加拿大或美国各自的报表，也可以查看(chkn)这两个国家的总销售量。 为此(wi c)，请右键单击“数据透视表字段列表” 中的“国家/地区”字段，然后单击“添加到报表筛选” ， 并且从该处进行添加。 第七节第七节 按国家按国家(guji)/(guji)/地区查看销售量地区查看销售量第58页/共66页第五十九页，共66页。由于原始的源数据(shj)具有“订单日期”列信息，因此在“数据(shj)透视表字段列表” 中有一个“订单日期”字段。这意味着您可以按日期查找 每位销售人员的销售量。请观看动画以了解如何将“订单日期”字段添加到报表(bobio)中，然后将日期数据分组 以创建更加便于管理的视图。 动画：右键单击鼠标，然后单击“播放”。第59页/共66页第六十页，共66页。由于原始的源数据具有“订单(dn dn)日期”列信息，因此在“数据透视表字段列表” 中有一个“订单(dn dn)日期”字段。这意味着您可以按日期查找 每位销