搜索
学习概要•了解EXCEL的基础及应用知识•学习EXCEL的函数知识•掌握EXCEL在会计信息系统中的应用EXCEL认识•窗口界面–标题栏–菜单栏–工具栏–名称框及编辑栏–工作表区域–滚动条和按钮–状态栏等EXCEL认识工作对象：工作簿工作表单元格单元格操作：按住ctrl或者shift可以同时及连续选定工作表和单元格；插入、合并、拆分单元格录入技巧输入文本（不是数字、公式、日期、时间、逻辑值）可直接输入，如身份证号码作为文本输入，前面需要加上单引号“‘”，否则默认为数字型；输入特殊字符选择“插入”|“特殊符号”；输入数字直接输入，一般忽略数字前面的正号，英文状态下的“.”作为小数点；输入日期和时间可以直接输入（同时在一个单元格输入日期和时间需要用空格隔开），也可以通过函数输入，或者按住ctrl+;显示当天日期，按住ctrl+shift+:输入系统当天时间。分行显示文本，需要按住ALT键同时按回车键输入真分数时，可进行格式设置，也可输入“0”，再输入空格和这个真分数。快速填充数据，利用填充柄。•数据的编辑–查找替换数据–定义数据输入的有效性[应用一]下拉菜单输入的实现例1：直接自定义序列有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的等级时我们只输入四个值：优秀，良好，合格，不合格。我们希望Excel2000单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。先选择要实现效果的行或列；再点击数据\有效性，打开数据有效性对话框；选择设置选项卡，在允许下拉菜单中选择序列；在数据来源中输入优秀，良好，合格，不合格（注意要用英文输入状态下的逗号分隔！）；选上忽略空值和提供下拉菜单两个复选框。点击“输入信息”选项卡，选上“选定单元格显示输入信息”，在“输入信息”中输入“请在这里选择”。例2：利用表内数据作为序列源。有时候序列值较多，直接在表内打印区域外把序列定义好，然后引用。先在同一工作表内的打印区域外要定义序列填好(假设在在Z1：Z8），如“单亲家庭，残疾家庭，残疾学生，特困，低收人，突发事件，孤儿，军烈属”等，然后选择要实现效果的列（资助原因）；点击“数据\有效性”，打开“数据有效性”对话框；选择“设置”选项卡，在“允许”下拉菜单中选择“序列”；“来源”栏点击右侧的展开按钮（有一个红箭头），用鼠标拖动滚动条，选中序列区域Z1：Z8（如果记得，可以直接输入=$Z$1:$Z$8；选上“忽略空值”和“提供下拉菜单”两个复选框。点击“输入信息”选项卡，选上“选定单元格显示输入信息”，在“输入信息”中输入“请在这里选择”。例3：横跨两个工作表来制作下拉菜单用INDIRECT函数实现跨工作表在例2中，选择来源一步把输入=$Z$1:$Z$8换成=INDIRECT(“表二!$Z$1:$Z$8”),就可实现横跨两个工作表来制作下拉菜单。总账中可以应用在哪个方面？凭证中科目的引用[应用三]数据唯一性检验员工的身份证号码应该是唯一的，为了防止重复输入，可以用“数据有效性”来提示。选中需要建立输入身份证号码的单元格区域（如B2至B14列），执行“数据→有效性”命令，打开“数据有效性”对话框，在“设置”标签下，按“允许”右侧的下拉按钮，在随后弹出的快捷菜单中，选择“自定义”选项，然后在下面“公式”方框中输入公式：=COUNTIF(B:B,B2)=1，确定返回。以后在上述单元格中输入了重复的身份证号码时，系统会弹出提示对话框，并拒绝接受输入的号码。•单元格引用与数据链接–引用：相对引用、绝对引用和混合引用–链接：同一工作簿不同工作表间链接：=sheet1!A5不同工作簿的工作表间链接:=[工作簿1.xls]sheet1!A5•高级应用–数据排序和筛选方法/步骤首先我们选中相关内容后，在数据中点击筛选，点击自动筛选，然后我们可以看到，每行都会出现三角形选项按键，点击，会依次显出来升序排列，将序排列，全部，自定义，前十个等。之后自己可以根据不同需要选择不同选项，其中自定义选项又另有乾坤，点中后会弹出一对话框，名为自定义自动筛选方式，第一行即为显示行,接着是两个输入框。其中第一个输入框又为选项框，分等于，不等于，大于，大于或等于，小与，小于或等于，始于，并非起始于，止于，并非结束与，包含，不包含。自己可根据自己所需的关系函数进行选择。EXCEL函数与公式•求和EXCEL函数与公式•函数选择平均数函数见上表SUM和SUBTOTAL函数企业数据分析中不可缺少的要用到求和公式，大家所熟知的便是SUM函数，但是sum函数并非求和的万能式，区分1月2月3月4月5月6月CIRCA202122232425S/T303132333435A组小计505254565860CIRCA404142434445S/T505152535455B组小计9092949698100CIRCA606162636465S/T707172737475C组小计130132134136138140CIRCA808182838485S/T909192939495D组小计170172174176178180总计440448456464472480上述表格中小计和合计的部分已经用sum公式求出结果了，下面大家尝试一下将A组小计那一行删掉，结果会怎么样？区分1月2月3月4月5月6月CIRCA202122232425S/T303132333435CIRCA404142434445S/T505152535455B组小计9092949698100CIRCA606162636465S/T707172737475C组小计130132134136138140CIRCA808182838485S/T909192939495D组小计170172174176178180总计#REF!#REF!#REF!#REF!#REF!#REF!总计一栏中出现了#REF!的字样，在Excel中出现这个字样的时候说明其公式参照的单元格不存在，这是sum求和公式一个弱点，另外，总计中的公式是一个一个选择相加的，现在只有A,B,C,D四组数量不多所以在求总计的时候还没什么感觉，但是当小计超过20个甚至100个的时候，一个一个相加显然不是明智之举，此时subtotal函数将显示出重要的作用，下面我们来简单学习一下subtotal函数subtotal函数是一种分类汇总函数，不仅仅是简单的求和公式，其公式构造如下：'=SUBTOTAL(function_num,ref1,ref2….)Function_num为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。Function_num（包含隐藏值）为1到11之间的自然数，用来指定分类汇总计算使用的函数1、AVERAGE2、2COUNT3、3COUNTA4、MAX5、MIN6、PRODUCT7、STDEV8、STDEVP9、SUM10、VAR11、VARPFunction_num（忽略隐藏值）函数101AVERAGE102COUNT103COUNTA104MAX105MIN106PRODUCT107STDEV108STDEVP109SUM110VAR111VARP当function_num为从1到11的常数时，SUBTOTAL函数将包括通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您要分类汇总列表中的隐藏和非隐藏值时，请使用这些常数。当function_num为从101到111的常数时，SUBTOTAL函数将忽略通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您只分类汇总列表中的非隐藏数字时，使用这些常数。区分1月2月3月4月5月6月CIRCA202122232425S/T303132333435A组小计505254565860CIRCA404142434445S/T505152535455B组小计9092949698100CIRCA606162636465S/T707172737475C组小计130132134136138140CIRCA808182838485S/T909192939495D组小计170172174176178180总计440448456464472480通过上述剖析图可以看出来，小计中的公式没什么太大区别，但是总计中是直接选择了所有的区域(红色框框)，而并非一个一个进行的选择。区分1月2月3月4月5月6月CIRCA202122232425S/T303132333435CIRCA404142434445S/T505152535455B组小计9092949698100CIRCA606162636465S/T707172737475C组小计130132134136138140CIRCA808182838485S/T909192939495D组小计170172174176178180总计440448456464472480上面简单的将两个求和函数sum和subtotal进行了比较，下面还有几点希望大家注意一下：1.习惯用subtotal函数的人一般在使用subtotal函数单元格的上面一行保持一行空行，为了方便以后添加，并且函数自动相加，前提是在最初设置subtotal函数的时候就把那行空行包含在内并且以后每次添加都是在空行的上面添加行，这样就不用每次添加后对函数再进行调整。2.很多数据分析的时候sum和subtotal是结合使用的，单调的使用任何一种都有可能耗费操作者的时间。•自行设定公式•选中单元格中输入“=”号，然后进行加减乘除四则运算的编写•如：D7单元格的值是D4、D5的和减去D6。•设置如下：在D7单元格中输入“=D4+D5-D6”SUMIF函数sumif函数的用法是根据指定条件对若干单元格、区域或引用求和。sumif函数语法是：SUMIF(range，criteria，sum_range)sumif函数的参数如下：第一个参数：Range为条件区域，用于条件判断的单元格区域。第二个参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。第三个参数：Sum_range为实际求和区域，需要求和的单元格、区域或引用。当省略第三个参数时，则条件区域就是实际求和区域。criteria参数中使用通配符（包括问号(?)和星号(*)）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符(~)。选中F3单元格，输入公式：=SUMIF(B2:B19,E3,C2:C19)，输入公式后，按键盘上ctrl+shift+enter组合键•IF函数•IF（逻辑表达式，成立时的返还值，不成立时的返还值）•注意：一般可以嵌套使用，即：•IF（逻辑表达式，成立时的返还值，IF（逻辑表达式，成立时的返还值，不成立时的返还值））数据透视“数据透视”功能能够将筛选、排序和分类汇总等操作依次完成，并生成汇总表格，是Excel强大数据处理能力的具体体现。数据透视是一种可以快速汇总大量数据的交互式方法。使用数据透视表可以深入分析数值数据，并且可以回答一些预计不到的数据问题。处理数据假设存在一个包含销售数字的Excel工作表。它展示了数千行有关两个国家/地区的销售人员及其每天销售量的数据。有大量数据需要处理—这些数据逐行列出并分成多列。您如何从工作表中获取信息并了解所有数据的意义？您可以使用数据透视表。它们能够将数据转换为短小简洁的报表，并准确地告诉您需要了解的内容。查看源数据在开始使用透视数据表之前，观察一下Excel工作表，以确保它为报表做好了准备。当创建数据透视表时，源数据的每列都将成为一个字段并且可在报表中使用。字段概括了源数据中的多行信息。查看源数据报表字段的的名称来自源数据中的列标题。因此，确保具有源数据中工作表第一行各列的名称。标题下的其余各行应该包含同一列中的类似项。例如，文本应该位于一列，数字在另一列，而日期又在另一列。换句话说，包含数字的列不