现在位置:首页 > Excel相关 > Excel函数
  • 函数365之COLUMN函数:欲问列数何人知,函数COLUMN来相告

    Excel函数2017-4-120评论375
    函数名称:COLUMN 函数作用:COLUMN函数用于返回参数引用的单元格区域所在的列号。 函数语法:COLUMN(单元格引用或区域引用) 注意事项: 1、COLUMN函数最多只有一个参数,且为可选参数,当省略该参数时,表示返回函数所在单元格的所在列号,例如=COLUMN(),公式如果在A10单元格,则结果为1,公式如果在F10,则结果为6; 2、COLUMN函数的参数可以是单个的单元格引用,或者区域引用,但是不能多个不连续单元格或不连续的区域引用; 函数应用: 实例1、在B1单元格输入公式,公式右拉后得到1,2,3,4,…,10的一个序列。 函数公式:B1单元格输入 =COLUMN(A1) 或者 =COLUMN()-1,公式右拉即可 解释说明: 1、凡是与右拉序列有关的第一时间考虑COLUMN函数,我们都知道相对引用单元格A1右拉后会依次得到B1,C1,D1…单元格,所以我们只要依次返回这些单元格所在的列号就可以得到我们要的结果,而返回单元格所在列号正是COLUMN函数的拿手好戏; 2、因为公式是从B1单元格开始的,如果我们直接返回当前单元格所在行号COLUMN()那么将会得到2,而要求是从1开始,所以公式也可以写成=COLUMN()-1。 3、如果要构造下拉序列的请用ROW函数。 实例2、用公式构造如下图第一行等差数列,首项是2,公差是4(也就是后面一个数字比前面一个数字大4)。 函数公式:B1单元格输入 =4*COLUMN(A1)-2,公式右拉即可 解释说明: 1、这里就不说解说原理了,直接上公式定理,要右拉公式构造“首项是M,公差是N的等差数列”,直接套用公式“=N*COLUMN(A1)+(M-N)”,万能公式,不灵可以随时找我。 2、同样的,如果要下拉公式构造“首项是M,公差是N的等差数列”,直接套用公式“=N*ROW(A1)+(M-N)”。 实例3、在B2单元格输入公式,公式右拉下拉后生成如下图的九九乘法表。 函数公式:B2单元格输入 =IF(AND(ROW(A1)>=COLUMN(A1),ROW(A1)<10),ROW(A1)&"x"&COLUMN(A1)&"="&ROW(A1)*COLUMN(A1),""),公式右拉下拉即可 解释说明: 1、通过观察上面的九九乘法表会有一个规范,第1个乘数最大只能是9,而且每个单元格的等式第1个乘数都是大于等于第2个乘数的,所以AND(ROW(A1)>=COLUMN(A1),ROW(A1)<10)才有等式,否则为空; 2、分别用ROW函数和COLUMN函数构造等式的第1个和第2个乘数,然后用连接符号&连接等式与结果。 实例4、根据下表数据按照指定姓名查找对应月份的数值。 函数公式:=VLOOKUP($A9,$A$2:$F$6,COLUMN(B1),0),公式右拉即可 解释说明:本题主要考的是VLOOKUP函数第3参数返回列数,这里需要依次返回第2,3,4,5,6列,所以用COLUMN(B1)表示,右拉即可获得我们需要的数值,另外还要注意查找条件必须要列绝对引用或绝对引用。
  • 函数365之ROW函数:行有行规之ROW有ROW法

    Excel函数2017-4-110评论434
    函数名称:ROW     函数作用:ROW函数用于返回参数引用的单元格区域所在的行号。 函数语法:ROW(单元格引用或区域引用) 注意事项: 1、ROW函数最多只有一个参数,且为可选参数,当省略该参数时,表示返回函数所在单元格的所在行号,例如=ROW(),公式如果在A10单元格,则结果为10,公式如果在F15,则结果为15; 2、ROW函数的参数可以是单个的单元格引用,或者区域引用,但是不能多个不连续单元格或不连续的区域引用; 函数应用: 实例1、在A2单元格输入公式,公式下拉后得到1,2,3,4,…,10的一个序列。 函数公式:A2单元格输入 =ROW(A1) 或者 =ROW()-1,公式下拉即可 解释说明: 1、凡是与下拉序列有关的第一时间考虑ROW函数,我们都知道相对引用单元格A1下拉后会依次得到A2,A3,A4…单元格,所以我们只要依次返回这些单元格所在的行号就可以得到我们要的结果,而返回单元格所在行号正是ROW函数的拿手好戏; 2、因为公式是从A2单元格开始的,如果我们直接返回当前单元格所在行号ROW()那么将会得到2,而要求是从1开始,所以公式也可以写成=ROW()-1。 实例2、用公式构造如下图A列等差数列,首项是2,公差是3(也就是后面一个数字比前面一个数字大3)。 函数公式:A2单元格输入 =3*ROW(A1)-1,公式下拉即可 解释说明:这里就不说解说原理了,直接上公式定理,要下拉公式构造“首项是M,公差是N的等差数列”,直接套用公式“=N*ROW(A1)+(M-N)”,万能公式,不灵可以随时找我。 实例3、下表是某工厂员工工资条的部分信息,要求根据ABCD四列数据提取信息到FGHI列,效果如图所示。 函数公式:F2单元格输入 =INDEX(A:A,3*ROW(A1)-1),公式右拉下拉即可 解释说明: 1、本题主要考的我们对INDEX函数的掌握和对等差序列的构造能力,我们可以看到我们需要的信息分别在第2,5,8,11…行,这是一个首项是2,公差是3的等差序列,看实例2; 2、根据数据所在的行号返回所在的内容这是INDEX函数的拿手好戏,所以直接 =INDEX(A:A,3*ROW(A1)-1)即可,注意A:A不要用绝对引用。 实例4、根据下表A列数据提取相应的数值,效果如B列所示。 函数公式:B2单元格输入 =LOOKUP(9^9,--LEFT(A2,ROW($1:$99))),公式下拉即可 解释说明: 1、首先通过观察我们发现每个字符串都是数字在最左边,所以我们可以用LEFT函数依次从左边截取1,2,3,4…99个字符,1-99可以表示成ROW($1:$99),这里1:99表示整行引用,第1到第99行; 2、然后我们将LEFT函数的结果用双负号“--”将文本型的数字转换成数值型的数字,将文本转换成错误值; 3、最后我们利用LOOKUP函数二分法查找的特性,在--LEFT(A2,ROW($1:$99))里面查找9^9,因为都没有一个数字比9^9大,所以最终的结果会取到--LEFT(A2,ROW($1:$99))里面的最后一个数字; 实例5、根据下表A、B、C三列数据,然后按照指定姓名查找对应的科目及分数,效果如F列和G列所示。 函数公式:F2单元格输入 =IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$11=$E$2,ROW($2:$11),9^9),ROW(A1))),""),公式右拉下拉即可 解释说明: 1、用IF判断$A$2:$A$11区域中的每个单元格是否与指定姓名E2相等,如果相等则返回单元格所在相应的行号ROW($2:$11),否则返回9^9; 2、然后用SMALL函数将IF的所有结果从小到大取值,再用INDEX函数根据SMALL的值返回所在行的内容; 3、为避免出现错误值,INDEX外面还要嵌套一个IFERROR纠错函数。
  • 函数365之SUM函数:虽然千变万化,一心一意只为求和

    Excel函数2017-4-100评论347
    函数名称:SUM 函数作用:SUM函数主要用于计算所有参数之和。 函数语法:SUM(参数1,参数2,参数3,...,参数255) 注意事项: 1、SUM函数有1-255个参数,除第一参数是必选参数之外,其他的都是可选参数。 2、SUM函数的参数支持绝大多数数据类型,例如:SUM(A1,A3,B4:B10),其参数包括单个单元格和单元格区域;SUM(99,A1:A99,1+2),其参数包括数字,单元格区域,表达式;SUM(A:A,sheet2!A:A,{1,2}),其参数包括整列引用,跨表整列引用,常量数组。 3、SUM函数在求和计算时会忽略单元格引用的文本、逻辑值和文本型数字,但是直接写在参数中的文本、逻辑值和文本型数字将不会被忽略。 =SUM(1,"你好"),结果为#VALUE!错误值,因为这里文本作为一个参数进行求和计算将无法计算; =1+"你好",结果为#VALUE!错误值; =SUM(A1:A2),注:其中A1=1,A2=你好,结果为1,此时虽然A2是一个文本,但是作为单元格引用,求和时将被忽略; =SUM(1,TRUE,1>2),结果为2,此处TRUE将被当做1进行计算,1>2结果为FALSE将会被当做0进行计算; =SUM(1,"2"),结果为3,文本型的数字2将会被转换成数值型的数字参与计算; =SUM(1,{TRUE}),结果为1,此处忽略常量数组里面的逻辑值。 函数应用: 实例1、根据下表按数据,计算所有公司1月销量总和。 函数公式:B12单元格输入 =SUM(B2:B11),结果为5546 解释说明:一般计算多个单元格的和直接用SUM函数,求和的区域为B2:B11单元格区域。 实例2、根据下表数据快速对每个公司和每个月的总销量进行求和。 操作步骤:选中B2:G12单元格区域(该区域包括第12行的求和行和G列的求和列),然后按“Alt+=”快捷键,或者点“开始”选项卡右侧的“自动求和”即可对选中区域的行列分别进行求和。 实例3、下表是某公司部分业务员的销售记录,要求按照每个区域在合并的单元格用公式对该区域的总销量进行计算。 函数公式:选中D2:D14单元格区域,输入 =SUM(C2:$C$14)-SUM(D3:$D$14),然后按CTRL+回车即可。 解释说明: 1、本题主要考的是逆推思维,例如计算A区域的原理就是用全部区域的总和SUM(C2:$C$14),然后减去除去A区域之外其他区域的总和SUM(D3:$D$14),就得到了A区域的总和; 2、这里涉及到了另外一个知识点,那就是合并单元格只有最左上角一个单元格有值,其他为空; 3、因为本题有合并单元格,且合并的单元格大小不一样,所以公式没办法直接下拉,我们需要先选定区域,然后按CTRL+回车键填充公式。 实例4、下表是某公司部分业务人员的销量信息,要求按照指定区域计算总的销量。 函数公式:F2单元格输入 =SUM((A2:A11=E2)*C2:C11),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、本题主要考的是逻辑值TRUE在与数值相乘的时候等同于1,FALSE在与数值相乘的时候等同于0。 2、判断A列区域的单元格分别与B区域是否相等A2:A11=E2,如果相等返回TRUE,不相等返回FALSE,然后分别与对应的销量相乘然后用SUM函数将所有值求和。 实例5、根据下表按数据,按照指定班级与性别计算学生人数。 函数公式:H2单元格输入 =SUM((A2:A11=F2)*(C2:C11=G2)),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、本题主要考的是逻辑值TRUE与FALSE在相乘的时候分别等同于1和0,所以当两个条件同时为TRUE相乘=1,只要有任意一个条件不成立为FALSE时相乘=0。 2、判断A列区域的单元格分别与1班是否相等A2:A11=F2,判断C列区域的单元格是否为“女”字C2:C11=G2,然后分别对应相乘然后相加。 实例6、根据下表按数据,计算销量小于300或者大于700的销量总和。 函数公式:公式 =SUM(((B2:B11<300)+(B2:B11>700))*B2:B11),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、本题主要考的是逻辑值TRUE与FALSE在相加的时候分别等同于1和0; 2、因为比较运算符的运算优先级大于算术运算符,所以这里注意判断条件B2:B11<300和B2:B11>700都要分别加上括号。
  • 函数365之COUNTIFS函数:我就是COUNTIF函数的超级加强版,有了我再多条件都不怕不怕啦

    函数名称:COUNTIFS 函数作用:COUNTIFS函数对区域中满足单个或多个指定条件的单元格进行计数。 函数语法:COUNTIFS(条件区域1,条件1,条件区域2,条件2,条件区域3,条件3,...,条件区域127,条件127) 注意事项: 1、第1参数为必选参数,表示在其中计算关联条件的第一个区域; 2、第2参数为必选参数,表示需要与第1参数条件区域相对应的条件,条件的形式为数字、表达式、单元格引用或文本; 3、第3参数到第254参数为可选参数,分别表示第2个条件区域,第2个条件,...,第127个条件区域,第127个条件,所以如果出现均为两两出现的条件对; 4、COUNTIFS的条件之间为并列关系,并且条件支持通配符。 函数应用: 实例1、根据下表按数据,按照指定公司与区域计算人员个数。 函数公式:G2单元格输入 =COUNTIFS(A2:A11,E2,B2:B11,F2),结果为4。 解释说明:因为是要根据两个条件计算人数,所以本题用COUNTIFS函数,公司名称(A列)和区域名称(B列)分别是计数的两个条件区域,对应E2和F2条件。 实例2、根据下表数据计算大于等于500并且小于900的数字的个数。 函数公式:C2单元格输入 =COUNTIFS(A2:A11,E2,B2:B11,F2),结果为5 解释说明:大于等于500并且小于900属于两个条件,所以用多条件计数函数COUNTIFS,第1个条件区域与第2个条件区域均为A列,注意条件因为有比较运算符,所以需要加双引号""。 实例3、下表是某工厂一个时间段内的入职记录,要求按照指定的开始日期和结束日期分别计算营销部和人资部的入职人数。 函数公式:B7单元格输入 =COUNTIFS($B$1:$K$1,">="&$B$5,$B$1:$K$1,"<="&$D$5,$B$3:$K$3,A7),公式下拉即可 解释说明: 1、本题主要考的是以行作为区域进行计算,第1行为条件区域,而且这个区域对应两个条件,一个是开始日期,一个是结束日期,第3行也是条件区域,对应部门; 2、这里因为公式要下拉,所以要注意单元格区域与单元格的绝对引用关系,另外还要注意第2行姓名是干扰信息,与计数无关。 实例4、下表是某公司部分业务人员的人员名单,要求按照指定公司名称和区域以及姓名包含的字符统计人数。 函数公式:H2单元格输入 =COUNTIFS(A2:A11,E2,B2:B11,F2,C2:C11,"*"&G2&"*"),结果等于3 解释说明: 1、本题主要考的是通配符的运用,“*”表示一个或多个任意字符,所以“"*"&H2&"*"”表示包含H2单元格字符的任意内容。 2、因为“*”属于文本,所以与单元格连接要加双引号和&连接符,如果是直接统计包含“雪”字的话可以直接写成“*雪*”。
  • 函数365之SUMIFS函数:我就是SUMIF函数的超级加强版,有了我再多条件都不怕不怕啦

    函数名称:SUMIFS 函数作用:SUMIFS函数可以用于对区域中符合一个或多个指定条件的值求和。 函数语法:SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,条件区域3,条件3,...,条件区域127,条件127) 注意事项: 1、第1参数为必选参数,表示对一个或多个单元格求和,包括数字或包含数字的名称、区域或单元格引用,忽略空白和文本值; 2、第2参数为必选参数,表示在其中计算关联条件的第一个区域; 3、第3参数为必选参数,表示需要与第2参数条件区域相对应的条件,条件的形式为数字、表达式、单元格引用或文本; 4、第4参数到第255参数为可选参数,分别表示第2个条件区域,第2个条件,...,第127个条件区域,第127个条件,所以如果出现均为两两出现的条件对; 5、SUMIFS的条件之间为并列关系,并且条件支持通配符。 函数应用: 实例1、根据下表按数据,按照指定日期与区域计算销量总和。 函数公式:G2单元格输入 =SUMIFS(C2:C11,A2:A11,E2,B2:B11,F2),结果为342。 解释说明:因为是要根据两个条件进行求和,所以本题用SUMIFS函数,要求和的是销量,所以销量(C列)是求和区域,销售日期(A列)和区域(B列)分别是求和的两个条件区域,对应E2和F2条件。 实例2、根据下表数据计算销量大于500并且小于900的销量总和。 函数公式:C2单元格输入 =SUMIFS(A2:A11,A2:A11,">500",A2:A11,"<900"),结果为2697 解释说明:大于500并且小于900属于两个条件,所以用多条件求和函数SUMIFS,这里求和区域与条件区域为A列,第1个条件区域与第2个条件区域也均为A列,注意条件因为有比较运算符,所以需要加双引号""。 实例3、下表是某工厂一个时间段内的产量统计,要求按照指定的开始日期和结束日期分别计算A班和B班的产量总和。 函数公式:B7单元格输入 =SUMIFS($B$3:$K$3,$B$1:$K$1,">="&$B$5,$B$1:$K$1,"<="&$D$5,$B$2:$K$2,A7),公式下拉即可 解释说明: 1、本题主要考的是以行作为区域进行计算,这里第3行为求和区域,第1行为条件区域,而且这个区域对应两个条件,一个是开始日期,一个是结束日期,第2行也未条件区域; 2、这里因为公式要下拉,所以要注意单元格区域与单元格的绝对引用关系。 实例4、下表是某公司部分业务人员的销量统计,要求按照指定公司名称和区域以及姓名包含的字符统计销量总和。 函数公式:I2单元格输入 =SUMIFS(D2:D11,A2:A11,F2,B2:B11,G2,C2:C11,"*"&H2&"*"),结果等于2244 解释说明: 1、本题主要考的是通配符的运用,“*”表示一个或多个任意字符,所以“"*"&H2&"*"”表示包含H2单元格字符的任意内容。 2、因为“*”属于文本,所以与单元格连接要加双引号和&连接符,如果是直接统计包含“雪”字的话可以直接写成“*雪*”。
  • Excel基本框架介绍(入门必看,高手请无视之)

    1、Office版本 目前可用的Excel版本是Excel2003、Excel2007、Excel2010、Excel2013、Excel2016、Excel365,其中Excel2003只支持打开xls格式的工作簿,不支持打开更高格式xlsx、xlsm格式的工作簿,个人建议大家使用2010或2013版本,比较稳定。 2、工作簿格式 我们在工作中最常见的工作簿格式有三种,分别是xls、xlsx、xlsm,其中xls格式是Excel2003开始就有的格式,而xlsx格式和xlsm格式都是从Excel2007版本之后才开始有的格式,xlsx格式禁止保存宏代码,xlsm格式是启用宏的工作簿,可以保存宏代码。 3、名称框 显示单元格地址,也可以重新定义单元格地址或单元格区域的名称。 4、编辑栏 显示单元格内容,可以在此处编辑或修改单元格内容,单元格真正内容以编辑栏显示的内容为准。例如下图如果你直接看C1单元格是看不到任何内容的,但是如果你看编辑栏你就会发现其实C1单元格并不是空白的,它有内容,至于它是如何隐藏的?方法很多,我们以后再说。 5、行号 1-1048576的数字(2003版的是1-65536),分别表示当前行是工作表的第几行,我们判断一个工作簿的格式除了看格式后缀之外我们还可以看工作表的最末行的行数进行判断。 6、列标 A-XFD的字母,分别对应数字1-16384(2003版的是A-IV,分别对应数字1-256),表示当前列是第几列。 7、单元格 每一个单元格地址都是行号与列号的交叉点,例如第16行第4列(D列)的交叉点就是单元格地址是D16,也可以表示为R16C4(这里R表示行是ROW的缩小,C表示列,是COLUMN的缩写)。 8、选项卡 在2003中是功能菜单模式,从2007版本开始升级为功能选项卡模式,可以在此处找到EXCEL的各种功能按钮。 9、设置 工作簿的左上角“文件”处就是EXCEL的设置按钮,在2007表格中此处是一个office图标,点击此处可以对EXCEL进行各种基本设置。 10、插入函数 在“公式”选项卡中有一个函数库,这个功能可以帮助我们快速选择我们需要的函数并且录入,另外我们最常用的插入函数其实是在名称框与编辑栏中间的那个“fx”的图标,如下图。
  • 函数365之DCOUNT函数:这就是个不正经的条件计数函数

    函数名称:DCOUNT 函数作用:返回列表或数据库中满足指定条件的记录字段(列)中包含数字的单元格的个数。 函数语法:DCOUNT(数据区域,计数字段名,计数条件) 注意事项: 1、第1参数为数据区域,数据区域必须包含行标题(也就是字段名称); 2、第2参数为计数的字段名称,可以是单元格引用或者用双引号的文本,如果省略该参数,则表示数据区域范围内容所有字段均被统计; 3、第3参数为计数的条件,必须包含和数据区域相同的字段名称以及一个或多个和数据区域内相同或包含的内容作为条件,否则无法计数。 函数应用: 实例1、下表为某公司部分业务员的销量,按要求计算销量大于500的业务员人数。 函数公式:E2单元格输入 =DCOUNT(A1:B11,B1,D1:D2),结果等于5 解释说明: 1、根据上表我们可以看出数据区域为A1:B11单元格区域,计数的区域是B列,字段名称就是B1单元格,计数条件就是D1:D2单元格区域(其中D1条件字段名,D2为条件); 2、根据上面分析套用DCOUNT函数即可,由于我们只对销售数量进行计数,所以数据区域可以只选择B1:B11,公式可以写成=DCOUNT(B1:B11,B1,D1:D2); 3、注意数据区域必须从第1行标题行开始,计数的字段名称(B1)以及条件的字段名称(D1)必须一致,否则无法计算。 实例2、下表为某班级部分学生的考试分数,按要求计算A组大于60分的学生人数。 函数公式:G2单元格输入 =DCOUNT(A1:C11,C1,E1:F2),结果等于4 解释说明: 1、根据上表我们可以看出数据区域为A1:C11单元格区域,计数的区域是C列,字段名称就是C1单元格,计数条件就是E1:F2单元格区域(其中E1:F1条件字段名,E2:F2为条件); 2、根据上面分析套用DCOUNT函数,由于我们只对组别和分数进行计数,所以数据区域可以只选择B1:C11,公式可以写成=DCOUNT(B1:C11,C1,E1:F2); 3、注意这里是有两个条件的计数,但是计数字段只能写C1,而不能写B1:C1。 实例3、下表为某工厂连续10天的产量数据,按要求计算产量低于20或者产量高于100的生产次数。 函数公式:E2单元格输入 =DCOUNT(A1:B11,B1,D1:D3),结果等于3 解释说明: 1、根据上表我们可以看出数据区域为A1:B11单元格区域,计数的区域是B列,字段名称就是B1单元格,计数条件就是D1:D3单元格区域(其中D1条件字段名,D2:D3为条件); 2、根据上面分析套用DCOUNT函数,由于我们只对产量进行计数,所以数据区域可以只选择B1:B11,公式可以写成=DCOUNT(B1:B11,B1,D1:D3); 3、注意同一个条件字段有两个条件在同一列,那么这两个条件就是或者的关系。 实例4、下表为某公司部分员工的年龄信息,按要求计算年龄大于30岁且小于等于40岁的员工人数。 函数公式:F2单元格输入 =DCOUNT(A1:B11,B1,D1:E2),结果等于4 解释说明: 1、根据上表我们可以看出数据区域为A1:B11单元格区域,计数的区域是B列,字段名称就是B1单元格,计数条件就是D1:E2单元格区域(其中D1:E1条件字段名,D2:E2为条件); 2、根据上面分析套用DCOUNT函数,由于我们只对产量进行计数,所以数据区域可以只选择B1:B11,公式可以写成=DCOUNT(B1:B11,B1,D1:E2); 3、注意同一个条件字段有两个条件在同一行,那么这两个条件就是并且的关系。
  • 函数365之EXACT函数:能鉴别孙悟空与六耳猕猴的函数

    函数名称:EXACT 函数作用:EXACT函数用于比较两个字符串:如果它们完全相同,则返回TRUE;否则返回FALSE。 函数语法:EXACT(参数1,参数2) 注意事项: 1、EXACT的两个参数都是必选参数,参数可以是数值、文本、单元格引用或者函数公式; 2、EXACT区分大小写,但忽略格式上的差异。 函数应用: 实例1、判断下面A列和B列数据是否相等,如果相等则返回TRUE,不相等返回FALSE,如C列所示。 函数公式:C2单元格输入 =EXACT(A2,B2),公式下拉即可 解释说明: 1、有人说比较两个单元格是否相等,那我用公式“=A2=B2”不就可以了吗?这可不行,因为这个公式是无法区分大小写字母的,它会认为“ABC”与“ABc”是一样的; 2、所以为了区分大小写字母,我们需要用EXACT函数,数据1和数据2分别作为它的两个参数。 实例2、根据下表计算“A商品”的销量总和,注意区分字母大小写,效果如E2单元格所示。 函数公式:E2单元格输入 =SUMPRODUCT(EXACT(A2:A11,D2)*B2:B11) 解释说明: 1、因为要区分字母的大小写,所以我们不能直接用SUMIF进行条件求和; 2、我们可以用EXACT函数将A2:A11区域的每一个单元格都和“A商品”比较EXACT(A2:A11,D2),如果完全一样就返回TRUE,否则返回FALSE; 3、将EXACT的结果乘以对应的销售数量,在乘法里,TRUE=1,FALSE=0,所以,你懂的… 4、SUMPRODUCT函数的作用是让EXACT(A2:A11,D2)与B2:B11依次相乘然后将它们相加。
  • 函数365之IF函数:爱情没有如果,函数却有IF

    函数名称:IF 函数作用:根据判断条件是否成立返回相应的结果,每个IF会有两种可能的结果。 函数语法:=IF(逻辑判断,逻辑判断成立/TRUE时的值,逻辑判断不成立/FALSE时的值) 注意事项: 1、第1参数为必选参数,表示计算结果可能为TRUE或FALSE的任意值或表达式; 2、当第1参数的结果为0时,等同于FALSE;当第1参数的结果为非0数值时,等同于TRUE; 3、第2参数为可选参数,该参数表示当第1参数为TRUE返回的结果; 4、第3参数为可选参数,该参数表示当第1参数为FALSE返回的结果; 5、Excel2003版本只支持7层IF的嵌套,Excel2007及以上的版本支持64层IF的嵌套。 函数应用: 实例1、根据下表B列的分数判断是否及格,当分数大于等于60分显示“及格”,否则显示“不及格”,如C列所示。 函数公式:C2单元格输入 =IF(B2>=60,"及格","不及格"),公式下拉即可 解释说明: 1、当需要根据一个条件判断返回两种不同的结果时,我们选择用IF函数; 2、第1参数将B2单元格与60分做比较,用了比较运算符大于等于号“>=”,如果成立返回TRUE,不成立则返回FALSE; 3、根据语法当第1参数为TRUE,则IF结果为第2参数,如果第1参数为FALSE,则IF结果为第3参数,分别对应我们IF公式里面的“及格”和“不及格”。 4、本题公式也可以写成=IF(B2<60,"不及格","及格"),具体逻辑可以自行判断一下。 实例2、根据下表B列的分数判断等级,当分数大于大于90分时为A级,大于70分小于等于90分时为B级,大于等于60分小于等于70分时为C级,小于60分时为D级,如C列所示。 函数公式:C2单元格输入 =IF(B2>90,"A",IF(B2>70,"B",IF(B2>=60,"C","D"))),公式下拉即可 解释说明: 1、根据逻辑关系我们先判断是否大于90,如果成立则IF结果为A(第2参数),否则就再嵌套一个IF继续判断(第1个IF的第3参数); 2、剩下的都是小于等于90的,我们再判断是否大于70,如果成立则IF结果为B(第2个IF的第2参数),否则就再嵌套一个IF继续判断(第2个IF的第3参数); 3、剩下的都是小于等于70的,我们再判断是否大于等于60,如果成立则IF结果为C(第3个IF的第2参数),否则结果就为D(第3个IF的第3参数); 4、注意参数为文本要用双引号,所以ABCD都用了双引号,本题也可以写成=IF(B2<60,"D",IF(B2<=70,"C",IF(B2<=90,"B","A")))。 实例3、下表为某班部分学生的体育考试成绩,根据规定,男同学必须60分以上女同学必须50分以上才能及格,否则就要重考,请用函数判断这些学生是否及格还是需要重考,如D列所示。 函数公式:D2单元格输入 =IF(OR(AND(B2="男",C2>60),AND(B2="女",C2>50)),"及格","重考"),公式下拉即可 解释说明: 1、根据题目要求男同学必须大于60分,所以这里就有两个条件,一个条件是男同学,另一个条件是大于60分,两个条件要同时成立才及格,所以用AND对两个条件进行判断“AND(B2="男",C2>60)”,同理对于女同学的判断“AND(B2="女",C2>50)”; 2、不管是男同学满足大于60分或者是女同学满足大于50分最终结果都是及格,相当于两个条件满足一个,所以用OR函数判断“OR(AND(B2="男",C2>60),AND(B2="女",C2>50))”; 3、根据最终OR的判断用IF函数,如果OR结果为TRUE则IF结果为“及格”,如果OR结果为FALSE则IF结果为“重考”; 4、本题公式也可以写成=IF(OR(AND(B2="男",C2<=60),AND(B2="女",C2<=50)),"重考","及格")。 实例4、根据下表A、B、C列数据,然后按照指定客户统计该客户销售总和。 函数公式:F2单元格输入 =SUM(IF(B2:B11=E2,C2:C11,0)),数组公式CTRL+SHIFT+回车键三键结束公式。 解释说明: 1、首先判断姓名列B2:B11区域内与我们要统计的客户姓名E2是否相等,如果相等则返回销量列C2:C11对应的值,否则就返回结果0; 2、然后用SUM函数对IF的所有结果进行求和,就可以得到答案,这不是SUMIF,这是SUM+IF 实5、根据下表的信息统计年龄小于30岁的人数。 函数公式:D2单元格输入 =SUM(IF(B2:B11<30,1,0)),数组公式CTRL+SHIFT+回车键三键结束公式。 解释说明: 1、首先判断年龄列B2:B11区域内的数值是否小于30,如果小于则返回1,表示1个符合的人员,否则就返回结果0; 2、然后用SUM函数对IF的所有结果进行求和,就可以得到答案,这不是COUNTIF,这是SUM+IF 实例6、根据下表的姓名和对应的学号,然后按照指定学号查找对应的姓名。 函数公式:E2单元格输入 =VLOOKUP(D2,IF({1,0},B2:B11,A2:A11),2,0) 解释说明: 1、前面语法说过,IF的第1参数0相当于FALSE,1相当于TRUE,所以IF({1,0},B2:B11,A2:A11)会分别取得学号列和姓名列然后重新构建一个学号在左姓名在右的数组区域,然后就可以通过VLOOKUP函数进行查找了。 2、这里公式也可以写成=VLOOKUP(D2,IF({0,1},A2:A11,B2:B11),2,0)。 实例7、根据下表的姓名和对应的科目,然后根据指定的姓名查找所有对应的科目,如E列所示。 函数公式:E2单元格输入 =IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$11=$D$2,ROW($2:$11),9^9),ROW(A1))),""),数组公式CTRL+SHIFT+回车键三键结束公式,公式下拉 解释说明: 1、首先判断姓名列A2:A11区域内与我们要查找的姓名D2是否相等,如果相等则返回该单元格所在的行号ROW($2:$11),否则就返回结果9^9(这是一个极大的数,超出工作表最大行数的数); 2、IF的结果有两种,一种是符合条件的姓名/科目所在的行号的数值,另一种是9^9,我们用SMALL函数将IF的所有结果按从小到大依次取出来; 3、然后用INDEX函数以SMALL的结果作为行号来返回引用结果,如果满足条件的就会返回正确结果,不满足条件的INDEX(B:B,9^9)将会返回#REF!错误值,因为超出了引用; 4、最后用IFERROR函数对错误值进行纠错,将错误值变成空格。
  • 函数365之COUNTA函数:色即是空,似空非空

    函数名称:COUNTA 函数作用:COUNTA函数用于计算区域中不为空的单元格的个数。 函数语法:COUNTA(参数1,参数2,参数3,…,参数255) 注意事项: 1、COUNTA函数支持255个参数,其中第1个为必选参数,第2-255个为可选参数,参数可是是数字、文本或者单元格引用; 2、COUNTA函数可对包含任何类型信息的单元格进行计数,包括错误值和空文本("")。 函数应用: 实例1、根据下表A2:A8单元格区域中有多少个非空单元格。 函数公式:=COUNTA(A2:A8),结果等于6 解释说明: 1、统计非空单元格个数用COUNTA函数,参数为A2:A8; 2、为什么7个单元格,我们肉眼看到有两个单元格是没内容的空单元格,但是COUNTA的结果却是6呢?那是因为A6并不是真空,属于假空单元格,里面有一个空格,我们用LEN函数统计字符个数就可以发现。 实例2、统计下面考勤表出勤情况,“√”表示出勤,空白表示缺勤,效果如H列所示。 函数公式:=COUNTA(B2:G2),公式下拉即可 解释说明:根据题目的意思非空单元格的个数=出勤天数,所以我们只需要统计区域内非空单元格的个数即可得到出勤天数,用COUNTA函数。 实例3、根据A列姓名制作数据下拉框,效果如下表C2单元格所示,这里A列数据是动态增加的,当增加的时候数据下拉框也要增加,要求写出操作步骤。 操作步骤: 1、点击“公式—名称管理器—新建”,名称就叫“姓名”(这里只要不含特殊字符,叫什么都可以),引用位置输入公式=OFFSET(Sheet4!$A$1,1,,COUNTA(Sheet4!$A:$A)-1),然后点确定,如下图; 2、选中C2单元格,选择“数据—数据有效性—设置—(允许)序列”,来源处输入“=姓名”,如下图: 解释说明: 1、由于A列数据是动态增加的,所以区域不固定,这时候我们可以通过OFFSET函数来返回一个引用区域; 2、OFFSET函数第3参数表示的是引用区域的高度,也就是有多少个单元格,而我们的区域只需要有内容的单元格,所以用COUNTA函数统计非空单元格个数即可,这里-1是因为标题占了一个非空单元格。 3、也许有人会觉得干嘛要这么复杂呢,我们定义名称的时候直接把引用范围设置大一点就好了,例如公式就写“=A2:A10000”,这里我们可以动手比较一下两种方法就知道了。