Welcome，2018！30个例子，让你领略Excel公式与函数之美汇总整理Excel公式练习的30个例子以及

各位朋友，元旦快乐！

2018年，不可阻挡地来了，你准备好了吗？

在新年的第1天，抢个早给大家发今年的第1篇文章，希望这是一个新的起点，我们一起从这里全新开始。

下面的30个例子是前面讲解过的Excel公式练习中的示例，初步展示了Excel公式与函数的美妙与强大。在这里，只给出了具体的公式，不作过多的说明，其运算原理可点击每个示例后面的链接来查看。

示例1：将列数据转换为行数据

使用公式将工作表内一列中的重复项依次移到一行中。具体如下图1所示，要将工作表：

图1

转换成图2所示的工作表：

图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。

一键直达 -> Excel公式练习1：将列数据转换为行数据

示例2：将矩形数据区域转换成一行或者一列

将工作表中矩形数据区域转换成一行或者一列。例如将下图3所示的名为Data的区域：

图3

转换成如图4所示单独的一列：

图4

或者如图5所示单独的一行：

图5

按行顺序将区域中的数据转换成单独的一列

在单元格B2中输入公式，然后下拉至单元格B17，如下图6所示。

图6

按列顺序将区域中的数据转换成单独的一列

在单元格B2中的公式如下，然后拖动下拉至单元格B17。

图7

按行顺序将区域中的数据转换成单独的一行

在单元格B2中的公式如下，然后向右拖动拉至单元格Q2。

图8

按列顺序将区域中的数据转换成单独的一行

在单元格B2中的公式如下，然后向右拖动拉至单元格Q2。

图9

一键直达 -> Excel公式练习4：将矩形数据区域转换成一行或者一列

示例3：获取列中的非空单元格

尝试使用一个公式，来消除指定单元格区域中的空单元格，即获得的值中不包括空单元格，如下图10所示。

图10

选择单元格区域C1:C7，输入数组公式：

=IFERROR(INDEX(A1:A7,SMALL(IF(A1:A7<>"",ROW(A1:A7)),ROW(A1:A7))),"")

一键直达 -> Excel公式练习2：获取非空单元格

示例4：求连续数据之和的最大值

求连续N个数据中所有连续M个数据之和的最大值。如图11所示，在单元格B5中，给出了一个求连续几年薪水之和的最大值的公式。示例中是每连续4年（由单元格A5指定）薪水之和的最大值。

工作表的单元格A10中是2008年至2011年的薪水之和、B10是2009年至2012年的薪水之和，……，依此类推。其中单元格B10中的值就是所有连续4连薪水之和的最大值。

在工作表中，将单元格A5命名为“Number”。我们可以修改单元格A5中的年数，从而求出指定年数的薪水之和的最大值。

图11

单元格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中是考试成绩。现在，我们需要找出某学生的某科目的成绩。例如，张三的物理成绩。

图12

在单元格H4中输入数组公式：

=INDEX(C:C,MATCH(H2&I2,$A$1:$A$15&$B$1:$B$15,0))

结果如下图13所示：

图13

一键直达 -> Excel公式练习5：找出符合多列条件的值

示例6：判断单元格区域中是否有重复值

如下图14所示的工作表单元格区域A1:A9，我们将其命名为Data。

图14

如果区域Data中有重复值，则返回False；如果区域Data中的值都不一样，则返回True。上图14所示的区域Data中明显有多个重复值，因此应该返回False。

使用下面的数组公式来判断单元格区域Data中是否有重复值：

=MAX(COUNTIF(Data,Data))=1

一键直达 -> Excel公式练习6：判断单元格区域中是否有重复值

示例7：统计单元格区域中不重复值的数量

统计单元格区域中有多少个不重复的值。如下图15所示的工作表：

图15

将单元格区域A1:A6命名为Data，要使用公式求出区域Data中有多少个不重复的值。

在某单元格中输入下面的数组公式：

=SUM(1/COUNTIF(Data,Data))

一键直达 -> Excel公式练习7：统计单元格区域中不重复值的数量

示例8：获取单元格区域中的不重复值

在一个单元格区域中含有重复值，使用公式来获取该区域中的不重复值。

例如，下图16所示的工作表单元格区域A1:A13，将其命名为Data。在该区域中，含有很多重复值。现在要获取该区域中的不重复值。

图16

在单元格区域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。

图17

获取当前单元格所在列的列字符的公式如下：

=LEFT(ADDRESS(ROW(),COLUMN(),2),FIND("$",ADDRESS(ROW(),COLUMN(),2))-1)

一键直达 -> Excel公式练习9：获取当前单元格所在列的列字符

示例10：判断两个单元格区域是否有重复值

如下图18所示的工作表，使用公式来判断单元格区域A1:A3与C1:C3中是否有重复值。

图18

从工作表中可以明显看出，这两个区域中都含有“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。

图19

在单元格C1输入公式：

=INDEX(Data,ROWS(Data)-ROW(A1)+1,1)

然后，下拉至单元格C7。

一键直达 -> Excel公式练习11：颠倒单元格区域中的数据

示例12：获取单元格中字符串的最后一个单词

使用公式来获取字符串的最后一个单词。如下图20所示，提取列A单元格中字符串的最后一个单词，将其放置到列C相应的单元格中。

图20

在单元格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中的数据的数量。

图21

也就是说，单元格区域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所示的日历？

图22

在单元格B1中输入日期时，显示该日期所在月的日历，如下图23所示。

图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），如何使用公式求出该单元格中的数字之和？

图24

数组公式如下：

=SUM(IFERROR(1*MID(A1,ROW(1:10),1),0))

一键直达 -> Excel公式练习16：求包含文本内容的单元格中的数字之和

示例17：提取字符串中的数字

单元格中的数据包含文本和数字（如图25），如何使用公式提取出该单元格中的数字？

图25

数组公式如下：

=1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:10),1)),0),255)

一键直达 -> Excel公式练习17：提取字符串中的数字

示例18：获取每行中第一个非空单元格

如何使用公式获取每行中第一个非空单元格？例如下图26所示工作表，要求使用公式根据上半部分的表格求各项目的开工日期。

图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”。

图27

在单元格C3中的数组公式如下：

=INDEX(A1:A7,MATCH(MAX(LEN(A1:A7)),LEN(A1:A7),0))

一键直达 -> Excel公式练习19：获取单元格区域中最长内容的单元格数据

示例20：对不同的班级分别排序

如下图28所示的工作表，在单元格区域A1:C9中有一组学生成绩数据，如何使用公式分别对不同班级的学生根据成绩高低排序？

图28

在单元格D2中的公式如下：

=SUMPRODUCT(--($B$2:$B$9=B2),--($C$2:$C$9>C2))+1

下拉至单元格D9。

一键直达 -> Excel公式练习20：对不同的班级分别排序

示例21：在单元格列区域中输入连续的数字

使用公式在工作表任意单元格列区域中输入连续的数字，如下图29所示，在单元格区域A2:A7中输入1～6。要求：不能单独删除或修改该单元格区域中的任何一个数字；不能在该单元格区域中添加或删除行；在该区域最开始的单元格A2之前插入一行时，数字序号不变；将该区域复制到其它地方时，数字序号不变。

图29

选择单元格区域A2:A7，输入数组公式：

=ROW(A2:A7)-ROW(A2)+1

一键直达 -> Excel公式练习21：在单元格列区域中输入连续的数字

示例22：将表转换成单列

使用公式将如图30所示的工作表中的表区域A2:C5转换到列E中，如单元格区域E1:E12。

图30

选择单元格E1中，输入公式：

=INDEX($A$2:$C$5,INT((ROWS(E$1:E1)-1)/3)+1,MOD((ROWS(E$1:E1)-1),3)+1)

然后，拖动至单元格E12，即可得到正确的结果。

一键直达 -> Excel公式练习22：将表转换成单列

示例23：求指定日期在哪个季度

如图31所示的工作表，在单元格区域A2:A5中输入了日期，要求在列B相应的单元格中得出该日期所在的季度。例如，2017年8月9日为第3季度，在单元格B2中的数字就为3。

图31

选择单元格区域B2:B5，输入数组公式：

=CEILING(MONTH(A2:A5),3)/3

一键直达 -> Excel公式练习23：求指定日期在哪个季度

示例24：统计两个日期之间有多少个星期一在1日

使用公式求两个指定日期之间日期为1日的星期一数，如图32所示。

图32

在单元格C2中的公式：

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(C2& ":" & C3)),"aaaa d")="星期一 1"))

一键直达 -> Excel公式练习24：统计两个日期之间有多少个星期一在1日

示例25：生成在1至10之间且没有重复值的随机数

使用公式生成位于两个值之间且没有重复值的随机数。如下图33所示，生成在1至10之间且没有重复值的随机数。

图33

在单元格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中东区超市没有的水果。

图34

在单元格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日之间的销售数据。

图35

在单元格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中该班级学生姓名，如何编写公式？

图36

在单元格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个数值之和，不计空格。

图37

在单元格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中得出的数字。

图38

在单元格C4中的公式：

=IF(A5<>"",COUNTIF(INDEX($B$3:B4,MATCH("做",$A$3:A4)):B4,">"&$C$1),"")

向下拖至单元格C19。

一键直达 -> Excel公式练习30：分别求不同班级大于90分的学生数

接下来，我将与大家一起来发现Excel公式与函数之美。

本文为原创文章，转载请联系我（xhdsxfjy@163.com）或者注明出处。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎关注[完美Excel]微信公众号：

方法1—在微信“添加朋友”或者通讯录中搜索“完美Excel”或者“excelperfect”后点击关注。

方法2—扫一扫下面的二维码