《2023年ecel常用函数笔记.pdf》由会员分享，可在线阅读，更多相关《2023年ecel常用函数笔记.pdf（29页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、清洗解决类：3-1 0 关 联 匹 配 类 1-141)VI o o k up()VLO 0 K U P(Io o k u p _value,t a ble_array,c o 1 _ index_ n u m,L range_ 1 ooku P)。V L OOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配)v lo o ku p就是竖直查找，即列查找。通俗的讲，根据查找值参数，在查找范围的第一列搜索查 找值，找到该值后，则返回值为：以第一列为准，往后推数查找列数值的这一列所相应的值。以后几乎都使用精确匹配,最后项的参数一定要选择为fals e o1 :d J。，百 除&用公-Mic
2、cowft ExclJ jfw BA K is s n MC 赢 m UB 宓*用寰中也 *夕.曰式 元缗72062052092Q iphCne5 iM乐phono 小米28note2K3 900 800 700 eoo6252簿5296仪施797170夕60灯“82”81?3938277506257eo926989$987778610090A分箱商iphont5 ip4ip441、D 3、香 蕉 或41 0说明仅在sum_r a nge参数中的单元格满足所有相应的指定条件时，才对该单元格求和。例 如，假设一个公式中包含两个criter i a _rang e 参数。假 如cr i t e r
3、ia_ r ange 1的第一 个 单 元 格 满 足cri t erial,ffij cri t eria_rang e 2的第一个单元格满足cri t e ra 2,则sum_r a n g e的第一个单元格计入总和中。对于指定区域中的其余单元格，依此类推。sum_range中 包 含T RUE的单元格计算为l;sum_rang e中包含FALSE的单元格计算为0(零)。与S UM IF函数中的区域和条件参数不同,SUMI F S函数中每个crite r i a _r a n g e参 数包含的行数和列数必须与sum_range参数相同。您可以在条件中使用通配符，即问号(？)和 星 号(*
4、)。问号匹配任一单个字符;星 号匹配任一字符序列。若要查找实际的问号或星号，请在字符前键入波形符()后 巴 词 事 国 O G -3 例)WPS X 鲂 新 建Microsoft Excel 97-2003工作表.xlsE2&/k =SUMIFS(A2:A7,B2:B7,=花*。C2:C7,=2)=SUM|FS(A 2：A 7,B 2：B 7,=花*,C2:C7,2)计 算 以“花”开头并由销售人员A B C DEF1已销售数 0里产品销售人员编辑栏210花生3201320 花生2430 胡豆3520 胡豆2615 瓜子3710 瓜子22售出的产品的总量(20)=SUM I F(B 2:B7,
5、=花*，A 2:A 7)计算以“花”开头总销售量(3 0)=SUM(A 2:A 7)计算以销售产品总量2 0)SUMPROD UCT在给定的几组数组中，将数组间相应的元素相乘，并返回乘积之和。SUMPRO DUCT(arrayl,array2,array3,.)数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE!。函数SUM PRODUCT将非数值型的数组元素作为0解决。*臼 笆 鼻 国 .W 我的WPS X 回 新 建Microsoft Excel 97-2003F 2 ，Q fx =SUMPR0 D UC T(A 2:B 4,C 2:D 4)A B C D
6、E _ _ _ _ F1Array 1Array 1Array 2Array 223427_1 5 6 l3866741953=SUM PRO D UCT(A2:B4,C2：D 4)两个数组的所有元素相应相乘，然后把乘积相加，即 3*2+4*7 +8*6+6*7+1*5 4-9*3,(1 56)=SUM PRODUCT(A 2：A4,B 2：B 4)相称于计算 3*4+8*6+1*9=6921)Co u nt/Count i f/Co u ntifsC 0 U NTIF S(CRITER I A_ R A NG E 1,C R ITER I A 1,C R IT E R I A _ R ANG
7、E2,C R IT E R IA 2)将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。crite r ia_rangel 在其中计算关联条件的第一个区域。c r it e r ia l 条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单 元格进行计数。如，条件可以表达为41、41、D 3、香 蕉 或41。c r i t eria_ r a nge2,cr i t eri a 2,.附加的区域及其关联条件。最 多 允 许127个区域/条件对。每个区域的条件一次应用于一个单元格。若所有的第一个单元格都满足其关联条件，则计数增长l o假如所有的第二个单元格都满足其关联条件，则
8、 计数再增长1 ,依此类推，直 到计算完所有单元格。若条件参数是对空单元格的引用，COUNTIES会将该单元格的值视为0(零)。您可以在条件中使用通配符，即 问 号(？)和星号(*)。问号匹配任一单个字符;星号匹 配任一字符序列。若要查找实际的问号或星号，请在字符前键入波形符()。D 2 ，Q A =C 0 UNTI F S(A 2:A 7,2)ABCDEF1数据|数据r2 I2 0 1 1/3/71 3 134 2 0 1 1/3/646 2 0 1 1/3/5582 0 1 1/3/461。2 0 1 1/3/371 2 2 0 1 1/3/2=COUNTIFS(A2:A7,2)计算 2
9、到 10 之间(不涉及 2 和 1 0)有 几个数包含在单元格A 2到A 7中(3)=COUNT I FS(A2:A7,2 0 23 3-4)计算单元格 A2 到 A7 中包含小于1 2的数，同时在单元格B 2 到B7中包含晚于20 2 3-3-4的日期的个数(3)=COUNTIF(A2:A7,5 )计算A2:A 7单元格内容大于5的 个 数(4)=COUNT(B2：B 4)计算B 2：B4包含数字的单元格数量(3)=COUNT(B 2:B4,1)。在上面基础上，再加一函 数COUNT在计数时，将把数字、日期、或以文本代表的数字计算在内;但是错误值 或其他无法转换成数字的文字将被忽略。假如参数
10、是一个数组或引用,那么只记录数组或引用中的数字；数组或引用中的空白单 元格、逻辑值、文字或错误值都将被忽略。假如要记录逻辑值、文字或错误值，请使用函数 Count A,返回非空单元格数22)Ma x/M in返回数组或引用区域的最大/小值23)R a nkRANK(n u mb e r,ref,or d er)返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值(假如列表 已排过序，则数字的排位就是它当前的位置)。Number 为需要找到排位的数字。Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。O rder 为一数字，指明排位的方式。假 如orde
11、r为0（零）或省略，对数字的排位是基于r e f为按照降序排列的列表。假 如o r d e r不为零，对数字的排位是基于ref为按照升序排列的列表。说明函 数RANK对反复数的排位相同。但反复数的存在将影响后续数值的排位。例如，在一列整数里，假 如 整 数1 0出现两次，其 排 位 为5,则1 1的 排 位 为7（没有排位为6的数 值）。多 巴 回 会9。我的WPS X组 新 建Mi c r cD2 9力c =RA NK(A 3,A 2:A 7,1)A B1数据2 _3 _ _ _ _42 2011/3/77 2011/3/66 2011/3/55678 2011/3/410 2011/3/3
12、12 2011/3/2=RANK（A3,A 2:A 7,1）返回A3在A2:A 7中升序排列的排名（2）=RANK（A 3,A 2:A7）返回A3在A2:A 7中降序排列的排名（5）2 4）RAND/RAND B E TWEEN常用随机抽样，前者返回o 1之间的随机值，后者可以指定范围。2 5）A v erage/Av e r a g e a/Ave r ag e if/Averag e i f sAVERAGEA（valuel,v al u e 2说明 参数必须为数值、名称、数组或引用。包含文本的数组或引用参数将作为0（零）计算。空 文 本（）也 作 为0（零）计算。假如在平均值的计算中不能
13、包含文本值，请 使 用 函 数A V E R A G E,包 含TRU E的参数作为1计 算;包 含F A LS E的参数作为0计算。D29 fx=A VERA GEA(B 2:B 7)1234567A数据24681 01 2B数据CDE2 0 1 1/3/72 0 1 1/3/62 0 1 1/3/52 0 1 1/3/42 0 1 1/3/32 0 1 1/3/2=AV E RAG E A（B2：B7）对B2:B7的时间数据按照通用数值计算平均值AVERAGE I F（range,c r i t e r i a zaver a ge_rang e）ra n g e 要计算平均值的一个或多个
14、单元格，其中涉及数字或包含数字的名称、数组或引用。c ri t eria 数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计 算平均值。如，条件可以表达为41、4 1、D 3、香 蕉 或4 1。avera g e _ra n ge 要计算平均值的实际单元格集。若忽略，则使用range A 7 二|q A =A VERA GEIF(B 2:B 4,2 31 0 0 )A B C D E财产值佣金1 1 0,0 0 07,1 0 02 1 0,0 0 01 4,1 0 031 0,0 0 02 1,1 0 04 1 0,0 0 02 8,1 0 0公式说 明（结 果）1 4 1 0
15、 0侬的讨 有00值10 所于,1均工 求龙123,平a=AVERAGE I F(B2:B 5,23100)=AVE RAGE I F(A2:A5,2 50 1 00 ,B2：B5)求所有财产值大于 250,1 0 0 的佣金的 平 均 值(24,600)A V ER A G EIFS(averag e _ r a ng e,criteri a _ran g e 1 ,c r iteria 1 ,c r i teri a _r a ng e 2,criteria 2)F4-fx =AVERAGEIFS(D2:D5,D2:D5,不及格，D2:D5,MO)/A B C D EFGH1学生第一次测试
16、成 结第二;欠测 试成绩最终测试 成绩2张三7087853李四8481884 1王五9866不及格86.515赵六5574不及格=AVERAGEIFS(D2:D5,D 2:D 5,不及格,D 2:D 5,80)计算所有学生最终测验成绩超过 8 0分的分数的平均值(86.5)。标记为“不及格”的分数不计算在内，由于它不是一个数字 值。=AVERAGEIFS(D2:D 5,D2:D5,80)同样得出结果(8 6.5)是由于文本值 当 作0,不大 于8026)Qu a r tile先普及四分位数概念：四分位数(Q uartile)是指在记录学中把所有数值由小到大排列并提成四等份，处在三个分割点位置的
17、数值。多应用于记录学中的箱线图绘制。求法：实 例 1数据总量：6,47,49,1 5,4 2,41,7,39,4 3,40,36由小到大排列的结果：6,7,15,3 6,3 9,40,41,42,4 3,4 7,49一 共1 1项Q1 的位置=(1 1+1)X 0.2 5=3,Q 2 的位置=(11+1)X 0.5=6,Q3 的位置=(11+1)X 0.75=9Q1=15,Q2=40,Q 3=43实例2数据总量：7,1 5,3 6,39,40,41一共6项数列项为偶数项时，四分位数Q 2为该组数列的中数，(n+l)/4=7/4 =1.75,Q 1在第一与第二个数字之间，3(n+1 )4=2皿=
18、5.2 5,Q 3在第五与第六个数字之间，Q1=0.7 5*1 5+0.25*7=1 3,Q 2=(36+39)/2=37.5,Q3=0.2 5*41+0.7 5*4 0=40.2 5.QUART I L E(arra y,q u art)返回数据集的四分位数。四分位数通常用于在销售额和测量数据中对总体进行分组。例 如，可以使用函数QUART I L E求 得 总 体 中 前25%的收入值。Array 需规定得四分位数值的数组或数字型单元格区域。Q u a r t决定返回哪一个四分位值。如果qurart等于的 数 QUARTILE返回0最小值1第一个四分位数(第2 5 个百分点值)2中 分 位
19、 数(第 5 0 个百分点值)3第三个四分位数(第7 5 个百分点值)4最大值注意：若数组为空，则QUARTILE返回错误值#NUM!。若 qu a rt V 0 或 q u a rt 4,则 QUAR TILE 返回错误值#N U M!。若q u a r t不为整数，将被截尾取整。当qua r t 分别等于0、2和4 时，函 数MIN、MED I A N和MAX返回的值与函数QUARTILE返回的值相同。=QUART I LE(A1:A 4,3)求出 Q3(9.75)B1A fxB=Q U AR T IL E(A1:A4,3)C D E1112|9.7529364627）Std e v估算样
20、本的标准偏差。标准偏差反映相对于平均值（m e an）的离散限度。S TDEV（n u mberl,number?,.）说明函 数STDEV假设其参数是总体中的样本。如果数据代表全部样本总体，则应该使用函数Stdeva来计算 标准偏差。此处标准偏差的计算使用“无偏差”或“n-1”方法。函 数STDEV的计算公式如下：占-(NJ(-1)忽略逻辑值（TRUE或FALSE和文本。如果不能忽略逻辑值和文本，请使用Stdeva工作表函数。&H *iJ h!*7 r*w X 型新建Bl ，fx=S T DEV（A1:A4）/A B C D1|12l 2.87228132312 93 Z iT4 62 8）
21、S ubst 0 1 a I返回数据清单或数据库中的分类汇总。通常，使 用“数据”菜 单 中 的“分类汇总”命令可以容易地创建带有分类汇总的数据清单。一旦创建了分类汇总，就可以通过编辑SUBTOTAL函数对该数据清单进行修改。S U B TO T AL(f unction_n u m,refl,re f2,.)Fu n ctio n _ n u m 为1到1 1之间的数字，指定使用何种函数在数据清单中进行 分类汇总计算。F u n ctio n _ n u m的数1AVERAGE2COUNT3COUNTA4w c5WIN6PRODUCT7STDEV8STDEVP9SUM10VAR11VARP2
22、 9)In t/Roun dI n t向下取整ro u n d四舍五入取整时间序列类：3 0-3 53 0)Year返回某日期相应的年份。返 回 值 为19 0 0到 9 9 9 9之间的整数。YEA R(s erial_ n u mb e r)S eria l_ n umber表达一个日期值，其中包含要查找的年份。应 使 用DATE 函数来输入 日期，或者将日期作为其他公式或函数的结果输入。例如，使用 DATE(202 3,5,2 3)输 入2 0 2 3年5月2 3日。假如日期以文本的形式输入，则会出现问题。说明WPS表格可将日期存储为可用于计算的序列号。默认情况下，189 9年1 2月3
23、1 日的序列号是1，而2023年1月1日的序列号是 3 9448,这是由于它距 1 899年 12月3 1日有3 9 4 48天。31)Mon t h返回日期中的月3 2)Da y返回日期中的日(第几号)33)Weekday=W e ekd a y(指定期间,参数)返回指定期间为一周中的第几天,参数为1代表从星期日开始算作第一天，参数为2代表从 星期一开始算作第一天(中西方差异)。我们中国用2为参数即可。B1 ，力c =W EEK DAY(Al,2)A B C D1|2 0 1 8/3/5 1 1 134)W e e k num=Wee k n um(指定期间，参数)返回一年中的第几个星期，后
24、面的参数类同weekday,意思是从周日算还是周一。3 4)Dat e=Date(年，月，日)时间转换函数，等于将year(),m 0 n t h(),day()合并35)Now=Now()返回当前日期和时间所相应的序列号。假如在输入函数前，单元格的格式为“常规”，则结果 将设为日期格式。3 6)Toda y=T o d a y()返回今天的日期37)D atedifDATEDIF函数，重要用于计算两个日期之间的天数、月数或年数。其返回的值是两个日期之 间的年月日间隔数。D ATEDIF(S t ar t _D a t e,End_Date,Unit)Start_Date:为一个日期，它代表时
25、间段内的第一个日期或起始日期。E nd_ D ate:为一个日期，它代表时间段内的最后一个日期或结束日期。U n it:为所需信息的返回类型。Y：计算两 个日期 间隔的 年数计算出生 日期为1 9 7 5-1-3 0 人的年龄=D A T E D I F (1 9 7 5-1 -3 0”,TO D A Y(),Y)4 3M:计 算两个1 1期间 隔的月 份数计算日期 为 1 9 7 5-1-3 0 与 当前日期 的间隔月 份数=D A TE D I F (1 9 7 5 -1-3 0”,TOD A Y(),“心5 1 9D :计算两 个日期 间隔的 天数计算日期 为 1 9 7 5-1-3 0
26、和当 前日期的 间隔天数=D A TE D I F(/Z1 9 7 5 T-3 0”,TOD A Y(),D )1 5 8 0 3YD：忽略年 数差，计算两 个日期 间隔的 天数计算日期 为 1 9 7 5-1 -3 0 和 当前日期 的不计年 数的间隔 天数=D A TE D I F C 1 9 7 5-1-3 0 ,TOD A Y。，YD)9 7”MD”:忽略 年数差 和月份 差，计 算两个 H期间 隔的天计算日期 为 1 9 7 5 -1-3 0 和当前日 期的不计 月份和年 份的间隔 天数D A TE D I F (1 9 7 5 T-3 0”,TOD A Y(),“M D7数YM：忽略相 差年 数，计 理两个 H期间 隔的月 份数计算I I 期 为 1 9 7 5-1-3 0 和当 前日期的 不计年份 的间隔月 份数=D A TE D I F C1 9 7 5-1-3 0 ,TOD A Y(),YM)3