|
各位朋友，元旦快乐！ 2018年，不可阻挡地来了，你准备好了吗？ 在新年的第1天，抢个早给大家发今年的第1篇文章，希望这是一个新的起点，我们一起从这里全新开始。 下面的30个例子是前面讲解过的Excel公式练习中的示例，初步展示了Excel公式与函数的美妙与强大。在这里，只给出了具体的公式，不作过多的说明，其运算原理可点击每个示例后面的链接来查看。
示例1：将列数据转换为行数据 使用公式将工作表内一列中的重复项依次移到一行中。具体如下图1所示，要将工作表：
转换成图2所示的工作表：
在单元格B2中输入数组公式： =IFERROR(INDEX(数据!$B$2:$B$7,SMALL(IF(数据!$A$2:$A$7=$A2,ROW(数据!$A$2:$A$7)-ROW(数据!$A$2) 1),COLUMNS($B2:B2))),'') 然后向下和向右拖动至单元格区域B2:E4。
示例2：将矩形数据区域转换成一行或者一列 将工作表中矩形数据区域转换成一行或者一列。例如将下图3所示的名为Data的区域：
转换成如图4所示单独的一列：
或者如图5所示单独的一行：
按行顺序将区域中的数据转换成单独的一列 在单元格B2中输入公式，然后下拉至单元格B17，如下图6所示。
按列顺序将区域中的数据转换成单独的一列 在单元格B2中的公式如下，然后拖动下拉至单元格B17。
按行顺序将区域中的数据转换成单独的一行 在单元格B2中的公式如下，然后向右拖动拉至单元格Q2。
按列顺序将区域中的数据转换成单独的一行 在单元格B2中的公式如下，然后向右拖动拉至单元格Q2。
一键直达 -> Excel公式练习4：将矩形数据区域转换成一行或者一列 示例3：获取列中的非空单元格 尝试使用一个公式，来消除指定单元格区域中的空单元格，即获得的值中不包括空单元格，如下图10所示。
选择单元格区域C1:C7，输入数组公式： =IFERROR(INDEX(A1:A7,SMALL(IF(A1:A7<>'',ROW(A1:A7)),ROW(A1:A7))),'')
示例4：求连续数据之和的最大值 求连续N个数据中所有连续M个数据之和的最大值。如图11所示，在单元格B5中，给出了一个求连续几年薪水之和的最大值的公式。示例中是每连续4年（由单元格A5指定）薪水之和的最大值。 工作表的单元格A10中是2008年至2011年的薪水之和、B10是2009年至2012年的薪水之和，……，依此类推。其中单元格B10中的值就是所有连续4连薪水之和的最大值。 在工作表中，将单元格A5命名为“Number”。我们可以修改单元格A5中的年数，从而求出指定年数的薪水之和的最大值。
单元格B5中的数组公式为： =MAX(MMULT(A8:J8,--(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number 1))-(Number-1)/2)<Number/2))) 一键直达 -> Excel公式练习3：求连续数据之和的最大值
示例5：找出符合多列条件的值 工作表中有多列，其中有些列的值是相同的，我们要找到同时符合几列条件的值。例如下图12所示的工作表，列A中是学生姓名，有重复，列B中是科目，列C中是考试成绩。现在，我们需要找出某学生的某科目的成绩。例如，张三的物理成绩。
在单元格H4中输入数组公式： =INDEX(C:C,MATCH(H2&I2,$A$1:$A$15&$B$1:$B$15,0)) 结果如下图13所示：
示例6：判断单元格区域中是否有重复值 如下图14所示的工作表单元格区域A1:A9，我们将其命名为Data。
如果区域Data中有重复值，则返回False；如果区域Data中的值都不一样，则返回True。上图14所示的区域Data中明显有多个重复值，因此应该返回False。
使用下面的数组公式来判断单元格区域Data中是否有重复值： =MAX(COUNTIF(Data,Data))=1 一键直达 -> Excel公式练习6：判断单元格区域中是否有重复值
示例7：统计单元格区域中不重复值的数量 统计单元格区域中有多少个不重复的值。如下图15所示的工作表：
将单元格区域A1:A6命名为Data，要使用公式求出区域Data中有多少个不重复的值。
在某单元格中输入下面的数组公式： =SUM(1/COUNTIF(Data,Data)) 一键直达 -> Excel公式练习7：统计单元格区域中不重复值的数量
示例8：获取单元格区域中的不重复值 在一个单元格区域中含有重复值，使用公式来获取该区域中的不重复值。 例如，下图16所示的工作表单元格区域A1:A13，将其命名为Data。在该区域中，含有很多重复值。现在要获取该区域中的不重复值。
在单元格区域C1:C13中输入下面的数组公式： =INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT('1:'&ROWS(Data))),MATCH(Data,Data,0),''),ROW(INDIRECT('1:'&ROWS(Data))))) 一键直达 -> Excel公式练习8：获取单元格区域中的不重复值
示例9：获取当前单元格所在列的列字符 使用一个公式来获取当前单元格所在列的列字符，例如当前单元格为B2，其所在列为列B。
获取当前单元格所在列的列字符的公式如下： =LEFT(ADDRESS(ROW(),COLUMN(),2),FIND('$',ADDRESS(ROW(),COLUMN(),2))-1) 一键直达 -> Excel公式练习9：获取当前单元格所在列的列字符
示例10：判断两个单元格区域是否有重复值 如下图18所示的工作表，使用公式来判断单元格区域A1:A3与C1:C3中是否有重复值。
从工作表中可以明显看出，这两个区域中都含有“Excel”，因此有重复值。
在单元格E1中输入下面的数组公式： =OR(TRANSPOSE(A1:A3)=C1:C3) 其值为TRUE，表明两个区域中存在重复值。 一键直达 -> Excel公式练习10：判断两个单元格区域是否有重复值
示例11：颠倒单元格区域中的数据 使用公式将单元格区域中的数据颠倒过来。例如，下图19所示工作表中的单元格区域Data（即A1:A7），使用公式将原来处于区域Data中第一个单元格A1中的数据放置到最后一个单元格，本例中为单元格C7，将区域Data中第二个单元格A2中的数据放置到倒数第二个单元格C6，……，依此类推，直至将区域Data中最后一个单元格A7中的数据放置到第一个单元格C1。
在单元格C1输入公式： =INDEX(Data,ROWS(Data)-ROW(A1) 1,1) 然后，下拉至单元格C7。 一键直达 -> Excel公式练习11：颠倒单元格区域中的数据
示例12：获取单元格中字符串的最后一个单词 使用公式来获取字符串的最后一个单词。如下图20所示，提取列A单元格中字符串的最后一个单词，将其放置到列C相应的单元格中。
在单元格C1中输入数组公式： =RIGHT(A1,MATCH('',MID(A1,LEN(A1)-ROW(INDIRECT('1:' & LEN(A1))) 1,1),0)-1) 向下拖动至单元格C2。 一键直达 -> Excel公式练习12：获取单元格中字符串的最后一个单词
示例13：统计单元格区域中无效数据数量 如下图21所示的工作表，使用公式来统计单元格区域C2:C6中没有出现在单元格区域A2:A9中的数据的数量。
也就是说，单元格区域C2:C6中凡是没有出现在单元格区域A2:A9中的数据，都是无效数据。从工作表中可以明显看出，单元格区域C2:C6中的“SX006”没有出现在单元格区域A2:A9中，因此“SX006”为无效数据，即单元格区域C2:C6中的无效数据为1。
在单元格E1中输入下面的数组公式： =SUM(1*ISNA(MATCH(C2:C6,A2:A9,0))) 其值为1，表明单元格区域C2:C6中没有出现在单元格区域A2:A9中的数据数为1。 一键直达 -> Excel公式练习13：统计单元格区域中无效数据数量
示例14：使用数组公式创建日历 如何使用数组公式创建如下图22所示的日历？
在单元格B1中输入日期时，显示该日期所在月的日历，如下图23所示。
获取当月日历的数组公式： =IF(DAY(calendar)>15,IF(ROW()=6,'',calendar),IF(DAY(calendar)<15,IF(ROW()>9,'',calendar),calendar)) 其中，calendar是一个命名公式，即： =week*7 weekday B3-B2 其中，week和weekday都是命名数组，即： week：={0;1;2;3;4;5} weekday：={1,2,3,4,5,6,7} 一键直达 -> Excel公式练习14：使用数组公式创建日历
示例15：求2018年母亲节的日期 使用公式求出2018年母亲节的日期。
公式为： =DATE(2018,5,1) IF(1<WEEKDAY(DATE(2018,5,1)),7-WEEKDAY(DATE(2018,5,1)) 1,1-WEEKDAY(DATE(2018,5,1))) (2-1)*7 一键直达 -> Excel公式练习15：求2018年母亲节的日期
示例16：求包含文本内容的单元格中的数字之和 单元格中的数据包含文本和数字（如图24），如何使用公式求出该单元格中的数字之和？
数组公式如下： =SUM(IFERROR(1*MID(A1,ROW(1:10),1),0)) 一键直达 -> Excel公式练习16：求包含文本内容的单元格中的数字之和
示例17：提取字符串中的数字 单元格中的数据包含文本和数字（如图25），如何使用公式提取出该单元格中的数字？
数组公式如下： =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:10),1)),0),255)
示例18：获取每行中第一个非空单元格 如何使用公式获取每行中第一个非空单元格？例如下图26所示工作表，要求使用公式根据上半部分的表格求各项目的开工日期。
也就是说，要求出单元格区域B2:G6的每行中第一个非空单元格对应的B1:G1中的日期。
在单元格B9中的数组公式如下： =IF(COUNTA(B2:G2),INDEX($B$1:$G$1,MATCH(TRUE,B2:G2<>'',0)),'') 下拉至单元格B13。 一键直达 -> Excel公式练习18：获取每行中第一个非空单元格
示例19：获取单元格区域中最长内容的单元格数据 如下图27所示的工作表，在单元格区域A1:A7中有一组数据，如何使用公式获取该区域中最长内容的单元格数据？即单元格A2包含的文本“excelperfect”。
在单元格C3中的数组公式如下： =INDEX(A1:A7,MATCH(MAX(LEN(A1:A7)),LEN(A1:A7),0)) 一键直达 -> Excel公式练习19：获取单元格区域中最长内容的单元格数据
示例20：对不同的班级分别排序 如下图28所示的工作表，在单元格区域A1:C9中有一组学生成绩数据，如何使用公式分别对不同班级的学生根据成绩高低排序？
在单元格D2中的公式如下： =SUMPRODUCT(--($B$2:$B$9=B2),--($C$2:$C$9>C2)) 1 下拉至单元格D9。 一键直达 -> Excel公式练习20：对不同的班级分别排序
示例21：在单元格列区域中输入连续的数字 使用公式在工作表任意单元格列区域中输入连续的数字，如下图29所示，在单元格区域A2:A7中输入1～6。要求：不能单独删除或修改该单元格区域中的任何一个数字；不能在该单元格区域中添加或删除行；在该区域最开始的单元格A2之前插入一行时，数字序号不变；将该区域复制到其它地方时，数字序号不变。
选择单元格区域A2:A7，输入数组公式： =ROW(A2:A7)-ROW(A2) 1 一键直达 -> Excel公式练习21：在单元格列区域中输入连续的数字
示例22：将表转换成单列 使用公式将如图30所示的工作表中的表区域A2:C5转换到列E中，如单元格区域E1:E12。
选择单元格E1中，输入公式： =INDEX($A$2:$C$5,INT((ROWS(E$1:E1)-1)/3) 1,MOD((ROWS(E$1:E1)-1),3) 1) 然后，拖动至单元格E12，即可得到正确的结果。
示例23：求指定日期在哪个季度 如图31所示的工作表，在单元格区域A2:A5中输入了日期，要求在列B相应的单元格中得出该日期所在的季度。例如，2017年8月9日为第3季度，在单元格B2中的数字就为3。
选择单元格区域B2:B5，输入数组公式： =CEILING(MONTH(A2:A5),3)/3 一键直达 -> Excel公式练习23：求指定日期在哪个季度
示例24：统计两个日期之间有多少个星期一在1日 使用公式求两个指定日期之间日期为1日的星期一数，如图32所示。
在单元格C2中的公式： =SUMPRODUCT(--(TEXT(ROW(INDIRECT(C2& ':' & C3)),'aaaa d')='星期一 1')) 一键直达 -> Excel公式练习24：统计两个日期之间有多少个星期一在1日
示例25：生成在1至10之间且没有重复值的随机数 使用公式生成位于两个值之间且没有重复值的随机数。如下图33所示，生成在1至10之间且没有重复值的随机数。
在单元格B3中的数组公式： =SMALL(IF(COUNTIF(B$2:B2,ROW($1:$10))<>1,ROW($1:$10)),1 INT(RAND()*(10-ROW() ROW(B$3)))) 拖至单元格B12。 一键直达 -> Excel公式练习25：生成在1至10之间且没有重复值的随机数
示例26：比较两列的值并提取不相同的数据 如下图34所示，使用公式提取列C中西区超市有而列A中东区超市没有的水果。
在单元格A10中的数组公式： =IFERROR(INDEX($C$2:$C$6,SMALL(IF(ISNA(MATCH($C$2:$C$6,$A$2:$A$5,0)),ROW($C$2:$C$6)-ROW($C$2) 1),ROWS(A$10:A10))),'') 向下拖至单元格中没有数据为止。 一键直达 -> Excel公式练习26：比较两列的值并提取不相同的数据
示例27：从数据区域中提取满足多条件的值 如下图35所示，单元格区域A8:D18中是销售数据，使用公式提取张三在2017年5月1日至2017年12月1日之间的销售数据。
在单元格F9中的数组公式： =IFERROR(INDEX(A$9:A$18,SMALL(IF($A$9:$A$18>=$B$3,IF($A$9:$A$18<=$C$3,IF($C$9:$C$18=$D$3,ROW($A$9:$A$18)-ROW($A$9) 1))),ROWS(F$9:F9))),'') 向右与向下拖至单元格中没有数据为止。 一键直达 -> Excel公式练习27：从数据区域中提取满足多条件的值
示例28：获取指定班级的学生姓名 如下图36所示，在单元格D2中指定班级名称，要获取数据区域A1:B10中该班级学生姓名，如何编写公式？
在单元格D5中的数组公式： =IFERROR(INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=D$2,ROW($A$2:$A$10)-ROW(A$2) 1),ROWS(G$4:G4))),'') 向下拖至单元格中没有数据为止。 一键直达 -> Excel公式练习28：获取指定班级的学生姓名
示例29：总是获取某列数值中的最后5个数值之和 如下图37所示，在单元格区域A1:A10中有一系列数值，但有些单元格为空，使用公式求该区域最后5个数值之和，不计空格。
在单元格C1中的数组公式： =SUM(INDEX($A$1:$A$10,LARGE(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)-ROW($A$1) 1),5)):INDEX($A$1:$A$10,MATCH(9.99E 307,$A$1:$A$10))) 当单元格区域A1:A10中的数值改变时，单元格C1中的值随之更新。 一键直达 -> Excel公式练习29：总是获取某列数值中最后5个数值之和
示例30：分别求不同班级大于90分的学生数 如下图38所示，在列A中是班级，列B中是各班级的成绩，要求各班级大于90分的人数，即列C中得出的数字。
在单元格C4中的公式： =IF(A5<>'',COUNTIF(INDEX($B$3:B4,MATCH('做',$A$3:A4)):B4,'>'&$C$1),'') 向下拖至单元格C19。 一键直达 -> Excel公式练习30：分别求不同班级大于90分的学生数 接下来，我将与大家一起来发现Excel公式与函数之美。 本文为原创文章，转载请联系我（xhdsxfjy@163.com）或者注明出处。 欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。 |
|
|
