《2022年精品精品EXCEL公式及函数的高级应用.docx》由会员分享，可在线阅读，更多相关《2022年精品精品EXCEL公式及函数的高级应用.docx（31页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精选学习资料 - - - - - - - - - EXCEL 公式及函数的高级应用 1 公式和函数是Excel 最基本、最重要的应用工具,是Excel 的核心,因此,应对公式和函数娴熟把握,才能在实际应用中得心应手；2.1.1 数组公式及其应用数组公式就是可以同时进行多重运算并返回一种或多种结果的公式；在数组 公式中使用两组或多组数据称为数组参数,数组参数可以是一个数据区域,也可以是数组常量；数组公式中的每个数组参数必需有相同数量的行和列；2.1.1.1 数组公式的输入、编辑及删除1数组公式的输入数组公式的输入步骤如下：（1）选定单元格或单元格区域；假如数组公式将返回一个结果,单击需要输 入数
2、组公式的单元格；假如数组公式将返回多个结果,就要选定需要输入数 组公式的单元格区域；（2）输入数组公式；（3）同时按“Crtl+Shift+Enter” 组合键,就 Excel 自动在公式的两边加上大括号 ；特殊要留意的是,第（ 3）步相当重要,只有输入公式后同时按“ Crtl+Shift+Enter” 组合键,系统才会把公式视为一个数组公式；否就,假如只按 Enter 键,就输入的只是一个简洁的公式,也只在选中的单元格区 域的第 1 个单元格显示出一个运算结果；在数组公式中,通常都使用单元格区域引用,但也可以直接键入数值数组,这样键入的数值数组被称为数组常量；当不想在工作表中按单元格逐个输入
3、 数值时,可以使用这种方法；假如要生成数组常量,必需按如下操作：1 / 17 名师归纳总结 - - - - - - -第 1 页,共 17 页精选学习资料 - - - - - - - - - （1）直接在公式中输入数值,并用大括号“（2）不同列的数值用逗号“, ” 分开；（3）不同行的数值用分号“ ；” 分开；输入数组常量的方法： ” 括起来；例如,要在单元格 A1:D1 中分别输入 10,20,30 和 40 这 4 个数值,就可采用下述的步骤：（1）选取单元格区域A1:D1,如图 2-1 所示；A1:D1 图 2-1 选取单元格区域（2）在公式编辑栏中输入数组公式“=10,20,30,40
4、” ,如图 2-2 所示；图 2-2 在编辑栏中输入数组公式（3）同时按 Ctrl+Shift+Enter组合键,即可在单元格A1、B1、C1、D1中分别输入了 10、20、30、40,如图 2-3 所示；假如要在单元格 A1、B1、C1、D1、A2、B2、C2、D2中分别输入 10、20、30、40、50、60、70、80,就可以采纳下述的方法：图 2-3 同时按 Ctrl+Shift+Enter组合键,得到数组常量2 / 17 名师归纳总结 - - - - - - -第 2 页,共 17 页精选学习资料 - - - - - - - - - （1）选取单元格区域 A1:D2,如图 2-4 所
5、示；图 2-4 选取单元格区域 A1:D2 （2）在编辑栏中输入公式“=10,20,30,40；50,60,70,80” ,如图 2-5 所示；图 2-5 在编辑栏中输入数组公式（3）按 Ctrl+Shift+Enter组合键,就在单元格A1、B1、C1、D1、A2、B2、C2、D2中分别输入了 10、20、30、40 和 50、60、70、80,如图 2-6 所示；图 2-6 同时按 Ctrl+Shift+Enter输入公式数组的方法组合键,得到数组常量例如,在单元格 A3:D3 中均有相同的运算公式,它们分别为单元格 A1:D1 与单元格 A2:D2中数据的和,即单元格 A3 中的公式为“
6、=A1+A2” ,单元格 B3中的公式为“=B1+B2” , ,就可以采纳数组公式的方法输入公式,方法如下：（1）选取单元格区域 A3:D3,如图 2-7 所示；（2）在公式编辑栏中输入数组公式“=A1:D1+A2:D2” ,如图 2-8 所示；3 / 17 名师归纳总结 - - - - - - -第 3 页,共 17 页精选学习资料 - - - - - - - - - 图 2-7 选取单元格区域 A3:D3 图 2-8 在编辑栏中输入数组公式（3）同时按 Ctrl+Shift+Enter组合键,即可在单元格A3:D3 中得到数组公式“ =A1:D1+A2:D2” ,如图 2-9 所示；图 2
7、-9 同时按 Ctrl+Shift+Enter用 Excel 做个工程倒计时牌我们常常看到这样的工程倒计时牌 : 组合键,得到数组公式4 / 17 名师归纳总结 - - - - - - -第 4 页,共 17 页精选学习资料 - - - - - - - - - 这多么鼓励人心.她如擂起阵阵战鼓,鼓励人们奋进. 能否在 EXCEL 中制成这样的广告呢 . 能.而且仍很很简洁；第一制表如下 : 在单元格 B3 中,输入系统时间 :B3: =TODAY 在单元格 D3 中,输入方案终止日期；在单元格 C3 中,有两种方案 : 一是从开头就进行倒计时,二是距终止日期为如干天进行倒计时 设 25 天 ；
8、当从开头就进行倒计时 :C3: =D3-B3 ；当距终止日期为如干天进行倒计时 :C3: =IFD3-B326,D3-B3,” ” 5 / 17 名师归纳总结 - - - - - - -第 5 页,共 17 页精选学习资料 - - - - - - - - - 为了在 B3 中显现 “今日是 2006 年 8 月 15 日工程仍有 ”,可在 B3 中进行单元格格式:在类型 T中如下图输入 “今日是 2006 年 8 月 15 日工程仍有 ”；下一步利用图表进行作图 : 选中 A2:D3 图表向导 条形图 积累条形图6 / 17 名师归纳总结 - - - - - - -第 6 页,共 17 页精选
9、学习资料 - - - - - - - - - 1下一步 数据区域系列产生在列下一步 输入标题挑选网格线勾销图例 数据标志显示V值 下一步直至完成如图 : 7 / 17 名师归纳总结 - - - - - - -第 7 页,共 17 页精选学习资料 - - - - - - - - - 对座标轴进行格式 :刻度 /数字 /对齐如下 : 8 / 17 名师归纳总结 - - - - - - -第 8 页,共 17 页精选学习资料 - - - - - - - - - 同时清除分类轴；9 / 17 名师归纳总结 - - - - - - -第 9 页,共 17 页精选学习资料 - - - - - - - -
10、- 其余格式略；EXCEL 数据分析处理 1 来源：未知 196112005-11-9【字体 :大 中 小 】切换为繁體中文Excel 供应了强大的数据分析处理功能,利用它们可以实现对数据的排序、分类汇总、挑选及数据透视等操作；在进行数据分析处理之前,第一必需留意以下几个问题：（1）防止在数据清单中存在有空行和空列；（2）防止在单元格的开头和末尾键入空格；（3）防止在一张工作表中建立多个数据清单,每张工作表应仅使用一个数据 清单；（4）在工作表的数据清单应与其他数据之间至少留出一个空列和一个空行,以便于检测和选定数据清单；（5）关键数据应置于数据清单的顶部或底部；2.3.1 数据排序2.3.1
11、.1 数据排序的规章Excel 答应对字符、数字等数据按大小次序进行升序或降序排列,要进行排 序的数据称之为关键字；不同类型的关键字的排序规章如下：数值：按数值的大小；字母：按字母先后次序；10 / 17 名师归纳总结 - - - - - - -第 10 页,共 17 页精选学习资料 - - - - - - - - - 日期：按日期的先后；汉字：按汉语拼音的次序或按笔画次序；规律值：升序时 FALSE排在 TRUE前面,降序时相反；空格：总是排在最终；2.3.1.2 数据排序步骤（1）单击数据区中要进行排序的任意单元格；（2）单击【数据】菜单,挑选【排序】项,系统将弹出【排序】对话框,如图 2
12、-35 所示；图 2-35 【排序】对话框（3）在【排序】对话框中用下拉列表框挑选要排序的关键字,关键字有“ 主要关键字” 、“ 次要关键字” 和“ 第三关键字” ,依据需要分别挑选不同的关键字；（4）单击【确定】按钮,数据就按要求进行了排序；当只有一个关键字时,可以单击工具栏上的升序按钮或降序按钮,进行自动排序；11 / 17 名师归纳总结 - - - - - - -第 11 页,共 17 页精选学习资料 - - - - - - - - - 2.3.1.3 自定义排序在有些情形下,对数据的排序次序可能特别特殊,既不是按数值大小次序、也不是按汉字的拼音次序或笔画次序,而是依据指定的特殊次序,如
13、对总公 司的各个分公司依据要求的次序进行排序,按产品的种类或规格排序等等,这时就需要自定义排序；利用自定义排序方法进行排序,第一应建立自定义序列,其方法可参阅第 1 章的有关内容；建立好自定义序列后,即可对数据进行排序,方法是：单击 数据区中要进行排序的任意单元格,单击【数据】菜单,挑选【排序】项,在弹出的【排序】对话框中单击【选项】按钮,系统弹出【排序选项】对话 框,如图 2-36 所示,在【自定义排序次序】的下拉列表中,挑选前面建立的 自定义序列,然后单击【确定】按钮,即可对数据进行自定义排序；图 2-36 【排序选项】对话框2.3.2 数据的查找与挑选企业的治理人员常常需要在数据库或数据
14、清单众多的数据中找出需要的数 据,Excel 供应了功能强大的数据查找与挑选工具；数据查找是指从原始数 据中提取满意条件的数据记录,源数据不会转变,也不会被隐匿；数据挑选 是指把数据库或数据清单中全部不满意条件的数据记录隐匿起来,只显示满 足条件的数据记录；常用的数据查找与挑选方法有：记录单查找、自动挑选 和高级挑选；12 / 17 名师归纳总结 - - - - - - -第 12 页,共 17 页精选学习资料 - - - - - - - - - 下面结合实例说明各种查找方法的详细应用；【例 211】图 2-37 为某公司的部分商品销售记录清单；图 2-37 某公司的商品销售明细清单依据图 2
15、-37 中的有关资料,可以分别采纳记录单查找、自动挑选或高级挑选的方式查找或挑选所需要的信息,如下所述：用 EXCEL 制作新年万年历来源：佚名 283642022-1-16【字体 :大 中 小 】切换为繁體中文Excel 标志设计笔记本广告设计新年伊始,制作一个新年万年历送给别人新年的祝愿,这份心愿是特别重要的；制作新年万年历有许多方法,但用 Office 的 Excel 表格制作你又试过没？下面给大家介绍用Excel 制作万年历；今日我们介绍用 Excel 制作万年历的方法；这个新年万年历可以显示当月的月历,仍可以随便查阅任何日期所属的月历,特别便利；假如你情愿,仍可以让它在特殊的日子里显
16、示不同的提示文字,一起来试试吧！本文所涉及到的函数有：1、AND logical1,logical2, . 13 / 17 名师归纳总结 - - - - - - -第 13 页,共 17 页精选学习资料 - - - - - - - - - 2、DATE year,month,day 3、DAY serial_number 4、IF Logical,Value_if_true,Value_if_false 5、INT number 6、MONTH serial_number 7、NOW 8、OR logical1,logical2, . 1、启动 EXCEL2003 ,新建一个工作表,取名储存（
17、如万年历 .xls ）,并在相应的单元格中,输入如图 1 所示的文本；2、同时选中 B1 、C1 、D1 单元格,按 “格式 ”工具栏上的 “合并及居中 ”按钮,将其合14 / 17 名师归纳总结 - - - - - - -第 14 页,共 17 页精选学习资料 - - - - - - - - - 并成一个单元格,并输入公式：=TODAY （）；选中 B1 （合并后的）单元格,执行“格式 单元格 ”命令,打开 “单元格格式 ”对话框（如图 2）,在 “数字 ”标签中的 “分类 ”下面选中 “日期 ”选项,再在右侧“类型 ”下面选中 “二 一年三月十四日”选项, “确定 ”退出,将日期设置成中文
18、形式；留意： TODAY （）函数用于提取当前系统日期,请将系统日期肯定要调整精确哟；3、选中 F1 单元格,输入公式：=IFWEEKDAYB1,2=7,日,WEEKDAYB1,2；选中H1 单元格,输入公式：=NOW （）；选中 F1 单元格,打开 “单元格格式 ”对话框,在 “数字 ”标签中的 “分类 ”下面选中 “特殊 ”选项,再在右侧“类型 ”下面选中 “中文小写数字 ”选项, “确定 ”退出,将 “星期数 ”设置成中文小写形式；选中 H1 单元格,打开 “单元格格式 ”对话框,在 “数字 ”标签中的 “分类 ”下面选中 “时间 ”选项,再在右侧“类型 ”下面选中一款时间格式,“确定
19、”退出；15 / 17 名师归纳总结 - - - - - - -第 15 页,共 17 页精选学习资料 - - - - - - - - - 留意：上述前面一个公式的含义是：假如（IF）当前日期（ B1）是星期 “ 7”（WEEKDAYB1,2=7）,就在 F1 单元格中显示 “日”,否就,直接显示出星期的数值（WEEKDAYB1,2）；上述其次个函数（NOW （）用于提取当前系统日期和时间,也请将系统日期和时间调整精确；4、在 I1、I2 单元格分别输入 1900 、 1901 ,然后同时选中 I1、I2 单元格,用 “填充柄”向下拖拉至 I151 单元格,输入 1900 2050 年份序列；
20、同样的方法,在 J1 至 J12 单元格中输入 112 月份序列；5、选中 D13 单元格,执行 “数据 有效性 ”命令,打开 “数据有效性 ”对话框（如图3）,按 “答应 ”右侧的下拉按钮,选中 =$I$1:$I$151 , “确定 ”退出；“序列 ”选项,在 “来源 ”下面的方框输入：同样的操作,将F15 单元格数据有效性设置为“ =$J$1:$J$12”序列；16 / 17 名师归纳总结 - - - - - - -第 16 页,共 17 页精选学习资料 - - - - - - - - - 留意：经过这样的设置以后,当我们选中D15 （或 F15 ）单元格时,在单元格右侧显现一个下拉按钮,
21、按此下拉按钮,即可挑选年份（或月份）数值,快速输入需要查询 的年、月值；6、选中 A2 单元格（不肯定非得是A2 哟）,输入公式：=IFF13=2,IFORD13/400=INTD13/400,ANDD13/4=INTD13/4,D13/100 INTD13/100,29,28,IFORF13=4,F13=6,F13=9,F13=11,30,31,用于猎取查询“月份”所对应的天数（28、 29 、30 、 31）；留意：上述函数的含义是：假如查询“月份 ”为 “ 2月”（F13=2 ）时,并且 “年份 ”数能被 400 整除 D13/400=INTD13/400,或者（ OR） “年份 ”能被 4 整除,但不能被 100整除 ANDD13/4=INTD13/4,D13/100 INTD13/100,就该月为 29 天（也就是我们通常所说的 “闰年 ”）,否就为28 天；假如 “月份 ”不是 2 月,但是 “ 4、6、9、11” 月,就该月为30 天；其他月份天数为31 天；17 / 17 名师归纳总结 - - - - - - -第 17 页,共 17 页