其他
8.查找内容【使用查找或按行、按列查找功能可以实现不同工作表的数据的联动,相当于使用Acess数据库!】【使用LOOKUP、VLOOKUP、HLOOKUP必须要升序排列!】【查找类的函数都可以使用通配符】在某行/列或其一部分查找内容,返回与“第一个找到的单元格”对应的另一区域的单元格内容:=LOOKUP(需查内容,某行/列或其一部分,对应的行/列或其一部分)【需查内容:可以是单元格、文本、数值】【若数值不按升序排列,会以二分法查找数值,具体见此网页】【LOOKUP只能模糊查找,VLOOKUP、HLOOKUP可以模糊查找、精确查找】8.1按列查找内容在某区域查找内容,返回与“第一个找到的单元格”相同行,且指定列的单元格内容:=VLOOKUP(需查内容,数据区,需返回的数据区的行数,k)【虽然是数据区,其实只是查找第一列的内容而已】【需查内容:可以是单元格、文本、数值】【需返回的数据区的行数:指被查找的数据区的第几行】【k为0/FALSE时精确查找(完全一致),k为1/TRUE时模糊查找(包含即可)】【模糊查找数值有个“隐藏功能”,若没有完全一样的可以返回比该数值小且相差最小的数,但要求第一列升序排列】8.2按行查找内容在某区域查找内容,返回与“第一个找到的单元格”相同列,且指定行的单元格内容=HLOOKUP(需查内容,数据区,需返回的数据区的列数,k)【虽然是数据区,其实只是查找第一行的内容而已】【需查内容:可以是单元格、文本、数值】【需返回的数据区的行数:指被查找的数据区的第几行】【k为0/FALSE时精确查找(完全一致),k为1/TRUE时模糊查找(包含即可)】【模糊查找数值有个“隐藏功能”,若没有完全一样的可以返回比该数值小且相差最小的数,但要求第一列升序排列】8.3查找相对位置在某行/列或其一部分查找内容,返回“第一个找到的单元格”在该区域的第几个:=MATCH(需查内容,某行/列或其一部分,k)【k为0精确查找,对查找区域的数值排列无要求;k为1查找小于该数值的最大值,要求升序排列;k为-1查找大于该数值的最小数,要求降序排列】【需查内容:可以是单元格、文本、数值】【若无查找内容则返回“错误”】8.4查找绝对位置某单元格的行号:=ROW(单元格)【若空则返回公式所在单元格,若是区域则返回该区域第一行所在单元格】某区域的行数:=ROWS(数据区)【{1,2,3;4,5,6}有一个;,表明有2行】某单元格的列号:=COLUMN(单元格)【若空则返回公式所在单元格,若是区域则返回该区域第一列所在单元格】某区域的行数:=COLUMNS(数据区)【{1,2,3;4,5,6}有2个,,表明有3行】返回单元格的位置:ADDRESS(行号,列号,k,m,外部数据表名)【k为1或省略时绝对引用,k为2时仅绝对引用行号,k为3时仅绝对引用列号,k为4时相对引用】【m为1/TRUE/省略时显示为A1样式,m为0/FALSE时显示为R1C1样式】【可用于其他函数的“单元格”,查找各种单元格的位置,如最小值的位置】8.5定位函数【定位函数可与查找位置函数结合使用】在某区域的第j行第k列的单元格的内容:=INDEX(数据区,j,k)在多个区域中第n个区域的第j行第k列的单元格的内容:=INDEX((数据区1,数据区2,数据区3,…),j,k,n)与某单元格距离j行k列的单元格的内容:=OFFSET(单元格,j,k)【j、k取正数时为向下、向右】生成以某单元格为起点的数据区:OFFSET(单元格,j,k,m,n)【起点为与该单元格距离j行k列的单元格,区域有m行、n列】定位第k个单元格的内容:=CHOOSE(k,单元格1,单元格2,单元格3,…)【常与IF、余数MOD函数嵌套】选择第k个数据区:CHOOSE(k,数据区1,数据区2,数据区3,…)转置数据区:TRANSPOSE(数据区)【即每个单元格的相对行号、列号互换】连续数据区的个数:=AREAS((数据区1,数据区2,数据区3,…))【需要两个括号,因为此函数只能输一个数据区,需要一个括号把各数据区合并起来】8.6二次定位函数INDIRECT函数:假设A10单元格内容为B2,B2单元格内容为11=INDIRECT(A10):返回A10内容所示单元格的内容,即11,相当于二次引用=INDIRECT(“A10”):返回A10内容,即B2,相当于一次引用=INDITECT(“A”&B2):返回A11内容【可用于引用有规律的行/列数的数值,免于使用Word替换功能】8.7总结返回单元格内容:LOOKUP、INDEX、OFFSET、CHOOSE、INDIRECT返回位置(数值):MATCH、ROW、COLUMN返回位置(单元格):ADDRESS返回数据区:IF、OFFSET、CHOOSE、TRANSPOSE需要单元格:LOOKUP、MATCH、ROW、COLUMN、OFFSET、CHOOSE、INDIRECT需要数据区:LOOKUP、MATCH、ROWS、COLUMNS、INDEX、CHOOSE、TRANSPOSE需要位置(行号、列号):ADDRESS、INDEX、OFFSET9.时间函数今天是哪天(年月日):=TODAY某日期在哪年:=YEAR(日期)某日期在哪月:=MONTH(日期)某日期在哪天:=DAY(日期)某日期在周几:=WEEKDAY(日期,k)【k为1或省略则周日是第1天,k为2则周一是第1天】某日期是那一年的第几周:=WEEKNUM(日期,k)【k为1或省略则周日是第1天,k为2则周一是第1天;1月1日所在周为第1周,之后的第1个周日/周一起算第2周】现在的时刻(年月日时分秒):=NOW【随时变化】某时刻所在几时:=HOUR(时刻)某时刻所在几分:=MINUTE(时刻)某时刻所在几秒:=SECOND(时刻)两日期相差的天数:=DAYS(结束日期,开始日期)两日期相差的天数(按1年360天算):=DAYS360(结束日期,开始日期)输入日期格式:=DATE(年,月,日)【用于在其他函数中嵌套日期格式】输入时刻格式:=TIME(时,分,秒)【用于在其他函数中嵌套时间格式】10.t检验成组t检验求P值:=T.TEST(数据区1,数据区2,k,2)【k取1为单侧检验,k取2为双侧检验】成组t’检验求P值:=T.TEST(数据区1,数据区2,k,3)【k取1为单侧检验,k取2为双侧检验】配对t检验求P值:=T.TEST(数据区1,数据区2,k,1)【k取1为单侧检验,k取2为双侧检验】已知P值求t值:=