excel常用函数整理(可检索)
目录:
一、数字函数
1.1 sum
1.2 sumif
1.3 sumifs
1.4 sumproduct
1.5 abs
二、统计函数
2.1 count
2.2 counta
2.3 countif
2.4 countifs
2.5 max
2.6 min
2.7 trimmean
三、日期和时间函数
3.1 year
3.2 week
3.3 date
3.4 today
3.5 now
3.6 weekday
3.7 datedif
3.8 workday
3.9 networkdays
四、文本函数
4.1 mid
4.2 len
4.3 trim
4.4 proper
4.5 lower
4.6 upper
4.7 text
4.8 search
五、查找和引用函数
5.1 vlookup
5.2 hookup
5.3 index
5.4 column
5.5 row
5.6 match
5.7 offset
5.8 indirect
六、逻辑函数
6.1 and
6.2 or
6.3 if
6.4 iferror
文档已上传:https://github.com/bigwhite2020/bigwhite2022/tree/main/excel函数
一、数字函数
1.1 sum
1) 作用:求和
2)函数说明:sum(muber1,number2,...)
3)举例说明:计算所有学生的总分
4)使用方法:SUM(C2:C10)
5) 计算结果:632
1.2 sumif
1) 作用:条件求和
2)函数说明:sumif(range,criteria,sum_range)
3)举例说明:计算一班的所有学生的总分
4)使用方法:SUMIF(D2:D10,"一班",C2:C10)
5) 计算结果:376
1.3 sumifs
1) 作用:多条件求和
2)函数说明:sumifs(sum_range,criteria_range,criteria,...)
3)举例说明:计算一班所有男生的总分
4)使用方法:SUMIFS(C2:C10,D2:D10,"一班",E2:E10,"男")
5) 计算结果:228
1.4 sumproduct
1) 作用:计算对应数组元素的乘积和
2)函数说明:sumproduct(array1,array2,array3,...)
3)举例说明:计算数组1与数组2的乘积和
4)使用方法:SUMPRODUCT(F2:G4,H2:I4)
5) 计算结果:67
备注:F列与H列相乘+G列与I列相乘
1.5 abs
1) 作用:绝对值
2)函数说明:abs(number)
3)举例说明:计算F5的绝对值
4)使用方法:ABS(F6)
5) 计算结果:100
二、统计函数
2.1 count
1) 作用:计算引用单元格的数字个数
2)函数说明:count(value1,value2,...)
3)举例说明:计算学生的总数量(通过序号)
4)使用方法:COUNT(A2:A10)
5) 计算结果:9
2.2 counta
1) 作用:计算非空单元格的数字个数
2)函数说明:counta(value1,value2,...)
3)举例说明:计算学生的总数量(通过姓名或序号)
4)使用方法:COUNTA(B2:B10)
5) 计算结果:9
2.3 countif
1) 作用:指定条件下的单元格计数
2)函数说明:countif(range,criteria)
3)举例说明:计算一班学生的总数量
4)使用方法:COUNTIF(D2:D10,"一班")
5) 计算结果:5
2.4 countifs
1) 作用:多条件单元格计数
2)函数说明:countifs(criteria_range,criteria,...)
3)举例说明:计算一班学生考试分数及格的人数
4)使用方法:COUNTIFS(D2:D10,"一班",C2:C10,">60")
5) 计算结果:3
2.5 max
1) 作用:返回引用单元格中的最大值
2)函数说明:max(number1,number2,...)
3)举例说明:计算最高得分
4)使用方法:MAX(C2:C10)
5) 计算结果:99
2.6 min
1) 作用:返回引用单元格中的最小值
2)函数说明:min(number1,number2,...)
3)举例说明:计算最低得分
4)使用方法:MIN(C2:C10
5) 计算结果:35
2.7 trimmean
1) 作用:返回修剪的平均值
2)函数说明:trimmean(array,percent)
3)举例说明:计算班级的平均值(除去最高与最低值)
4)使用方法:TRIMMEAN(C2:C10,2/9)
5) 计算结果:71.14
备注:去除数量=总数目*比例
三、日期和时间函数
3.1 year
1) 作用:引用日期的年份
2)函数说明:year(serial_number)
3)举例说明:获取一个指定日期F8的年信息
4)使用方法:YEAR(F8)
5) 计算结果:2023
3.2 week
1) 作用:引用日期的月份
2)函数说明:month(serial_number)
3)举例说明:获取一个指定日期F8的月信息
4)使用方法:MONTH(F8)
5) 计算结果:3
3.3 date
1) 作用:三个单独的值组合成日期
2)函数说明:date(year,month,day)
3)举例说明:快速组成一个日期信息
4)使用方法:DATE(2023,3,27)
5) 计算结果:2023/3/27
3.4 today
1) 作用:今天的日期
2)函数说明:today()
3)举例说明:查看今天的日期信息
4)使用方法:today()
5) 计算结果:2023/3/4
3.5 now
1) 作用:当前的日期和时间
2)函数说明:now()
3)举例说明:查看当前的日期+时间的信息
4)使用方法:NOW()
5) 计算结果:2023/3/4 15:38
3.6 weekday
1) 作用:引用日期对应一周中的第几天
2)函数说明:weekday(serial_number,return_type)
3)举例说明:查看2023年劳动节是当周的第几天
4)使用方法:WEEKDAY("2023/5/1",1)
5) 计算结果:2
备注:type=1为默认,1-7代表星期天到星期六;type=2,1-7代表星期一到星期天
3.7 datedif
1) 作用:两个日期之间的年/月/日间隔
2)函数说明:datedif(start_date,end_date,unit)
3)举例说明:查看2023年劳动节到国庆节相差几天
4)使用方法:DATEDIF("2023/5/1","2023/10/1","D")
5) 计算结果:153
备注:unit可以为Y,M,D分别代表年月日
3.8 workday
1) 作用:从起始日期开始的多少天对应的工作日
2)函数说明:workday(start_date,days,holidays)
3)举例说明:查看2023年元旦后的100个工作日
4)使用方法:WORKDAY("2023/1/1",100)
5) 计算结果:2023/5/19
备注:holidays可以配置去除的节假日
3.9 networkdays
1) 作用:计算起始日期到结束日期的工作日天数
2)函数说明:networkdays(start_date,end_date,holidays)
3)举例说明:查看2023年元旦到五一间的工作日数
4)使用方法:NETWORKDAYS("2023/1/1","2023/5/1")
5) 计算结果:86
四、文本函数
4.1 mid
1) 作用:在指定位置截取文本
2)函数说明:mid(text,start_num,num_chars)
3)举例说明:截取“漩涡鸣人”名字2-4个字符
4)使用方法:MID(B2,2,2)
5) 计算结果:涡鸣
备注:参数说明:text,开始字符,截取长度
4.2 len
1) 作用:计算文本的长度
2)函数说明:len(text)
3)举例说明:计算“宇智波佐助”名字总长度
4)使用方法:LEN(B5)
5) 计算结果:5
4.3 trim
1) 作用:删除文本中的空格
2)函数说明:trim(text)
3)举例说明:F9字符删除前后的空格的处理
4)使用方法:TRIM(F9)
5) 计算结果:待测字符-前后存在空格
4.4 proper
1) 作用:首字母大写
2)函数说明:proper(text)
3)举例说明:将G5首字母大写
4)使用方法:PROPER(G5)
5) 计算结果:Abcd全小写字符
4.5 lower
1) 作用:将文本转换小写
2)函数说明:lower(text)
3)举例说明:将G6内字母转为小写
4)使用方法:LOWER(G6)
5) 计算结果:abcd全大写字符
4.6 upper
1) 作用:将文本转换大写
2)函数说明:upper(text)
3)举例说明:将G5内字母转为大写
4)使用方法:UPPER(G5)
5) 计算结果:ABCD全小写字符
4.7 text
1) 作用:将数值格式转化为文本
2)函数说明:Text(value,format_text)
3)举例说明:将C2的数字转为文本,并展示2位小数
4)使用方法:TEXT(C2,"00.00")
5) 计算结果:98.00
4.8 search
1) 作用:返回指定内容在文本中的位置
2)函数说明:search(find_text,with_text,[start_num])
3)举例说明:查看日向雏田中“向”字的位置
4)使用方法:SEARCH("向",B4)
5) 计算结果:2
五、查找和引用函数
5.1 vlookup
1) 作用:垂直查找(按列号查找)
2)函数说明:vlookup(lookup_value,table_array,col_index_num,range_lookup)
3)举例说明:查看(B列的)"风波水门"的成绩(D列内)
4)使用方法:VLOOKUP("风波水门",B:C,2,0)
5) 计算结果:99
备注:第三个参数与相对的列数,第三个参数,如果为0或FASLE,用精确匹配方式,而为无序查找,如果为TRUE或被省略,则使用近似匹配方式,同时要求查询区域的首列按升序排序
5.2 hookup
1) 作用:水平查找(按行号查找)
2)函数说明:hookup(lookup_value,table_array,row_index_num,range_lookup)
3)举例说明:查看(13行的)"excel函数"往下数5行(含13行)的函数名字
4)使用方法:HLOOKUP("excel函数",13:26,5)
5) 计算结果:sumproduct
5.3 index
1) 作用:按行、列号查找
2)函数说明:index(array,row_num,column_num)
3)举例说明:返回数组1+数组2联合区域内第二行,第三列的值
4)使用方法:INDEX(F2:I4,2,3)
5) 计算结果:4
5.4 column
1) 作用:返回列号
2)函数说明:column(reference)
3)举例说明:返回B5的列数
4)使用方法:返回B5的列数
5) 计算结果:2
5.5 row
1) 作用:返回行号
2)函数说明:row(reference)
3)举例说明:返回B5的行数
4)使用方法:ROW(B5)
5) 计算结果:5
5.6 match
1) 作用:在数值中查找目标值的位置
2)函数说明:match(lookup_value,lookup_array,match_type)
3)举例说明:返回"宇智波佐助"在学生中的顺序
4)使用方法:MATCH("宇智波佐助",B2:B10,0)
5) 计算结果:4
5.7 offset
1) 作用:返回指定偏移的值
2)函数说明:offset(reference,rows,cols,height,width)
3)举例说明:以F1为基点偏移后计算数组2内所有数组的和
4)使用方法:SUM(OFFSET(F1,1,2,3,2))
5) 计算结果:27
5.8 indirect
1) 作用:间接引用
2)函数说明:indirect(ref_text,a1)
3)举例说明:返回H6单元格引用的单元格的值
4)使用方法:INDIRECT(H6)
5) 计算结果:漩涡鸣人
六、逻辑函数
6.1 and
1) 作用:如果所有参数均为True,则返回True
2)函数说明:and(logic1,logic2,...)
3)举例说明:多个条件看是否均满足
4)使用方法:AND(1>2,3<5)
5) 计算结果:FALSE
6.2 or
1) 作用:如果任一参数均为True,则返回True
2)函数说明:or(logic1,logic2,...)
3)举例说明:多个条件看是否有一个满足
4)使用方法:OR(1>2,3<5)
5) 计算结果:TRUE
6.3 if
1) 作用:按指定逻辑条件,返回对应的值
2)函数说明:if(logic_text,value_if_true,value_if_false)
3)举例说明:判断旋涡鸣人的成绩是否及格,响应返回
4)使用方法:IF(C2>60,"及格","不及格")
5) 计算结果:及格
6.4 iferror
1) 作用:如果公式计算错误,则返回指定的值,否则返回公式的结果
2)函数说明:iferror(value,value_if_error)
3)举例说明:当计算结果有错误时,得到相应的返回
4)使用方法:IFERROR(G8/G7,"计算错误error")
5) 计算结果:计算错误error
上一篇: 如何在Excel中计算四分位数?
下一篇: 玩转 R 语言基础运算:实战案例解析
推荐阅读
-
Excel 常用函数
-
[EXCEL]常用的 50 个函数和基本操作(文本函数)
-
excel 函数公式大全,最常用的 6 个公式
-
excel常用函数整理(可检索)
-
Excel - 常用函数
-
(Excel)常用函数公式及操作技巧之六:汇总计算与统计(一)
-
F#探险之旅(二):函数式编程(上)-函数式编程范式简介 F#主要支持三种编程范式:函数式编程(Functional Programming,FP)、命令式编程(Imperative Programming)和面向对象(Object-Oriented,OO)的编程。回顾它们的历史,FP是最早的一种范式,第一种FP语言是IPL,产生于1955年,大约在Fortran一年之前。第二种FP语言是Lisp,产生于1958,早于Cobol一年。Fortan和Cobol都是命令式编程语言,它们在科学和商业领域的迅速成功使得命令式编程在30多年的时间里独领风骚。而产生于1970年代的面向对象编程则不断成熟,至今已是最流行的编程范式。有道是“*代有语言出,各领风骚数十年”。 尽管强大的FP语言(SML,Ocaml,Haskell及Clean等)和类FP语言(APL和Lisp是现实世界中最成功的两个)在1950年代就不断发展,FP仍停留在学院派的“象牙塔”里;而命令式编程和面向对象编程则分别凭着在商业领域和企业级应用的需要占据领先。今天,FP的潜力终被认识——它是用来解决更复杂的问题的(当然更简单的问题也不在话下)。 纯粹的FP将程序看作是接受参数并返回值的函数的集合,它不允许有副作用(side effect,即改变了状态),使用递归而不是循环进行迭代。FP中的函数很像数学中的函数,它们都不改变程序的状态。举个简单的例子,一旦将一个值赋给一个标识符,它就不会改变了,函数不改变参数的值,返回值是全新的值。 FP的数学基础使得它很是优雅,FP的程序看起来往往简洁、漂亮。但它无状态和递归的天性使得它在处理很多通用的编程任务时没有其它的编程范式来得方便。但对F#来说这不是问题,它的优势之一就是融合了多种编程范式,允许开发人员按照需要采用最好的范式。 关于FP的更多内容建议阅读一下这篇文章:Why Functional Programming Matters(中文版)。F#中的函数式编程 从现在开始,我将对F#中FP相关的主要语言结构逐一进行介绍。标识符(Identifier) 在F#中,我们通过标识符给值(value)取名字,这样就可以在后面的程序中引用它。通过关键字let定义标识符,如: let x = 42 这看起来像命令式编程语言中的赋值语句,两者有着关键的不同。在纯粹的FP中,一旦值赋给了标识符就不能改变了,这也是把它称为标识符而非变量(variable)的原因。另外,在某些条件下,我们可以重定义标识符;在F#的命令式编程范式下,在某些条件下标识符的值是可以修改的。 标识符也可用于引用函数,在F#中函数本质上也是值。也就是说,F#中没有真正的函数名和参数名的概念,它们都是标识符。定义函数的方式与定义值是类似的,只是会有额外的标识符表示参数: let add x y = x + y 这里共有三个标识符,add表示函数名,x和y表示它的参数。关键字和保留字关键字是指语言中一些标记,它们被编译器保留作特殊之用。在F#中,不能用作标识符或类型的名称(后面会讨论“定义类型”)。它们是: abstract and as asr assert begin class default delegate do donedowncast downto elif else end exception extern false finally forfun function if in inherit inline interface internal land lazy letlor lsr lxor match member mod module mutable namespace new nullof open or override private public rec return sig static structthen to true try type upcast use val void when while with yield 保留字是指当前还不是关键字,但被F#保留做将来之用。可以用它们来定义标识符或类型名称,但编译器会报告一个警告。如果你在意程序与未来版本编译器的兼容性,最好不要使用。它们是: atomic break checked component const constraint constructor continue eager event external fixed functor global include method mixinobject parallel process protected pure sealed trait virtual volatile 文字值(Literals) 文字值表示常数值,在构建计算代码块时很有用,F#提供了丰富的文字值集。与C#类似,这些文字值包括了常见的字符串、字符、布尔值、整型数、浮点数等,在此不再赘述,详细信息请查看F#手册。 与C#一样,F#中的字符串常量表示也有两种方式。一是常规字符串(regular string),其中可包含转义字符;二是逐字字符串(verbatim string),其中的(")被看作是常规的字符,而两个双引号作为双引号的转义表示。下面这个简单的例子演示了常见的文字常量表示: let message = "Hello World"r"n!" // 常规字符串let dir = @"C:"FS"FP" // 逐字字符串let bytes = "bytes"B // byte 数组let xA = 0xFFy // sbyte, 16进制表示let xB = 0o777un // unsigned native-sized integer,8进制表示let print x = printfn "%A" xlet main = print message; print dir; print bytes; print xA; print xB; main Printf函数通过F#的反射机制和.NET的ToString方法来解析“%A”模式,适用于任何类型的值,也可以通过F#中的print_any和print_to_string函数来完成类似的功能。值和函数(Values and Functions) 在F#中函数也是值,F#处理它们的语法也是类似的。 let n = 10let add a b = a + blet addFour = add 4let result = addFour n printfn "result = %i" result 可以看到定义值n和函数add的语法很类似,只不过add还有两个参数。对于add来说a + b的值自动作为其返回值,也就是说在F#中我们不需要显式地为函数定义返回值。对于函数addFour来说,它定义在add的基础上,它只向add传递了一个参数,这样对于不同的参数addFour将返回不同的值。考虑数学中的函数概念,F(x, y) = x + y,G(y) = F(4, y),实际上G(y) = 4 + y,G也是一个函数,它接收一个参数,这个地方是不是很类似?这种只向函数传递部分参数的特性称为函数的柯里化(curried function)。 当然对某些函数来说,传递部分参数是无意义的,此时需要强制提供所有参数,可是将参数括起来,将它们转换为元组(tuple)。下面的例子将不能编译通过: let sub(a, b) = a - blet subFour = sub 4 必须为sub提供两个参数,如sub(4, 5),这样就很像C#中的方法调用了。 对于这两种方式来说,前者具有更高的灵活性,一般可优先考虑。 如果函数的计算过程中需要定义一些中间值,我们应当将这些行进行缩进: let halfWay a b = let dif = b - a let mid = dif / 2 mid + a 需要注意的是,缩进时要用空格而不是Tab,如果你不想每次都按几次空格键,可以在VS中设置,将Tab字符自动转换为空格;虽然缩进的字符数没有限制,但一般建议用4个空格。而且此时一定要用在文件开头添加#light指令。作用域(Scope)作用域是编程语言中的一个重要的概念,它表示在何处可以访问(使用)一个标识符或类型。所有标识符,不管是函数还是值,其作用域都从其声明处开始,结束自其所处的代码块。对于一个处于最顶层的标识符而言,一旦为其赋值,它的值就不能修改或重定义了。标识符在定义之后才能使用,这意味着在定义过程中不能使用自身的值。 let defineMessage = let message = "Help me" print_endline message // error 对于在函数内部定义的标识符,一般而言,它们的作用域会到函数的结束处。 但可使用let关键字重定义它们,有时这会很有用,对于某些函数来说,计算过程涉及多个中间值,因为值是不可修改的,所以我们就需要定义多个标识符,这就要求我们去维护这些标识符的名称,其实是没必要的,这时可以使用重定义标识符。但这并不同于可以修改标识符的值。你甚至可以修改标识符的类型,但F#仍能确保类型安全。所谓类型安全,其基本意义是F#会避免对值的错误操作,比如我们不能像对待字符串那样对待整数。这个跟C#也是类似的。 let changeType = let x = 1 let x = "change me" let x = x + 1 print_string x 在本例的函数中,第一行和第二行都没问题,第三行就有问题了,在重定义x的时候,赋给它的值是x + 1,而x是字符串,与1相加在F#中是非法的。 另外,如果在嵌套函数中重定义标识符就更有趣了。 let printMessages = let message = "fun value" printfn "%s" message; let innerFun = let message = "inner fun value" printfn "%s" message innerFun printfn "%s" message printMessages 打印结果: fun value inner fun valuefun value 最后一次不是inner fun value,因为在innerFun仅仅将值重新绑定而不是赋值,其有效范围仅仅在innerFun内部。递归(Recursion)递归是编程中的一个极为重要的概念,它表示函数通过自身进行定义,亦即在定义处调用自身。在FP中常用于表达命令式编程的循环。很多人认为使用递归表示的算法要比循环更易理解。 使用rec关键字进行递归函数的定义。看下面的计算阶乘的函数: let rec factorial x = match x with | x when x < 0 -> failwith "value must be greater than or equal to 0" | 0 -> 1 | x -> x * factorial(x - 1) 这里使用了模式匹配(F#的一个很棒的特性),其C#版本为: public static long Factorial(int n) { if (n < 0) { throw new ArgumentOutOfRangeException("value must be greater than or equal to 0"); } if (n == 0) { return 1; } return n * Factorial (n - 1); } 递归在解决阶乘、Fibonacci数列这样的问题时尤为适合。但使用的时候要当心,可能会写出不能终止的递归。匿名函数(Anonymous Function) 定义函数的时候F#提供了第二种方式:使用关键字fun。有时我们没必要给函数起名,这种函数就是所谓的匿名函数,有时称为lambda函数,这也是C#3.0的一个新特性。比如有的函数仅仅作为一个参数传给另一个函数,通常就不需要起名。在后面的“列表”一节中你会看到这样的例子。除了fun,我们还可以使用function关键字定义匿名函数,它们的区别在于后者可以使用模式匹配(本文后面将做介绍)特性。看下面的例子: let x = (fun x y -> x + y) 1 2let x1 = (function x -> function y -> x + y) 1 2let x2 = (function (x, y) -> x + y) (1, 2) 我们可优先考虑fun,因为它更为紧凑,在F#类库中你能看到很多这样的例子。 注意:本文中的代码均在F# 1.9.4.17版本下编写,在F# CTP 1.9.6.0版本下可能不能通过编译。 F#系列随笔索引页面
-
整理JS一些常用的工具函数