small函数和row组合（校运会班级报名，同一个同学有几个项目，该怎样用Excel提取出来）

本文目录

• 校运会班级报名，同一个同学有几个项目，该怎样用Excel提取出来
• Excel表格中，如何自动提取大于某一数值并且最接近它的第一位数值及相关信息呢
• Excel中什么是“万金油”函数
• Excel定位函数和添加字符的复合使用难题

校运会班级报名，同一个同学有几个项目，该怎样用Excel提取出来

感谢邀请，其实此问题就是典型的“一对多”查询，详情请看下文：

Excel函数公式：含金量超高的“一对多”查询实用技巧解读

实际工作中，一个人往往含有多种角色，也可能干多种类型的工作，如何快速的查找对应的全部角色或全部工作的类型，是每位办公白领必备的技能之一。

一、INDEX+SMALL+IF+ROW组合函数法。

目的：查询销售员的销售情况。

实际效果：

从最终的效果图中我们可以看出，单击销售员的姓名，系统自动检索销售地区和销售额。而且当有多条记录时，“地区”和“销售额”全部予以显示。一瞬间得到了该销售员的销售业绩情况。那么，该如何去实现呢？

步骤：

（一）、高亮度显示该销售员的销售记录。

方法：

1、选中目标单元格。

2、【开始】-【条件格式】-【新建规则】，选中【选择规则类型】中的【使用公式确定要设置格式的单元格】，并在【为符合此公式的值设置单元格格式】中输入：=($H$4=$B3)。

3、单击右下角【格式】-【填充】，选取填充色，并【确定】-【确定】完成设置。

4、单击销售员姓名下拉列表，可以看到关于该销售员的所有销售记录都被高亮度显示。

（二）、查询销售员对应的销售地区。

方法：

1、在目标单元格中输入公式：=INDEX(E:E,SMALL(IF(B$3:B$9=H$4,ROW($3:$9),4^8),ROW(A1)))&““。

2、Ctrl+Shift+Enter填充。

3、拖动填充柄填充。

（三）、查询销售员在对应地区的销售额。

方法：

1、选中目标单元格。

2、输入公式：=IFERROR(LOOKUP(1,0/(($B$3:$B$9=$H$4)*($E$3:$E$9=$I4)),$C$3:$C$9),““)。

3、Ctrl+Enter填充。

二、COUNTIF+VLOOKUP+ROW组合函数法。

目的：查询销售员的销售情况。

实际效果：

眼尖的同学们肯定已经注意到了，最终的效果和用INDEX函数等实现的是一样的，但是数据源中多了一个“辅助列”。那么辅助列有什么作用呢？整体又该如何去实现呢？

步骤：

（一）、添加辅助列。

方法：

1、单击选中【姓名】所在列。

2、快捷键：Ctrl+Shift++添加列，输入列标。

3、选中添加列的所有目标单元格，并输入公式：=COUNTIF($C$3:C3,$I$4)，Ctrl+Enter填充。

备注：

公式：=COUNTIF($C$3:C3,$I$4)的主要作用是对出现的姓名进行累加统计。暨第一次出现为1，第二次出现为2，第三次出现为3……。

（二）、高亮度显示该销售员的销售记录。

方法同上，不在赘述。

（三）、查询销售员对应的销售地区。

方法：

1、选定目标单元格。

2、输入公式：=IFERROR(VLOOKUP(ROW(1:1),$B$3:$F$9,5,0),““)。

3、Ctrl+Enter填充。

（四）、查询销售员在对应地区的销售额。

方法：

1、选中目标单元格。

2、输入公式：=IFERROR(LOOKUP(1,0/(($C$3:$C$9=$I$4)*($F$3:$F$9=$J4)),$D$3:$D$9),““)。

3、Ctrl+Enter填充。

4、数据查询。

结束语：

本文主要讲解了“一对多”查询的两种方法，都是使用相关函数组合使用得来的结果。

如果大家在学习的过程中有困难，建议先学习单个基础函数，然后进行组合使用。

欢迎大家在留言区留言讨论。

Excel表格中，如何自动提取大于某一数值并且最接近它的第一位数值及相关信息呢

在这里提供一种较为简单的方法：

思路：成绩与平均成绩比较，如果大于平均值，取成绩；如果小于平均值，直接取成绩的最大值，这样就生成了一个数组，然后取这组数组的最小值即可

这种方法最大的好处：无需排序，无需复杂计算，直接得到结果！

详细步骤如下：

步骤一：使用if函数生成我们思路中的数组

{=IF(D3:D8-AVERAGE(D3:D8)》0,D3:D8,MAX(D3:D8))}得到数组{75;91;80;91;91;87}

公式解读：如果成绩大于均值，那么取成绩值，否则取最大的成绩值

（备注：选择公式，按F9可查看公式的计算结果）

步骤二：使用函数min求最小值

{=MIN(IF(D3:D8-AVERAGE(D3:D8)》0,D3:D8,MAX(D3:D8)))} 因含有数组，Ctrl+Shift+Enter三键结束

综上，{=MIN(IF(D3:D8-AVERAGE(D3:D8)》0,D3:D8,MAX(D3:D8)))}，一个简单的公式轻松搞定！

更多原创教程请关注“Excel轻松一课”，大家一起学习，共同进步！

Excel中什么是“万金油”函数

您好，这里是“E图表述”为您讲述的Excel各种知识。

学会Index+Small+Row的“万金油”函数

1、提取不重复的值

还记得我们第二次数组教学篇中的Small+Row函数的返回值吗？如果忘记了，建议还是先点下面的链接看看。如果你还记得，可以忽略下方的链接继续读。

千呼万唤，“万金油”函数终于登场了。这是一份模拟数据，

如果我们现在需要统计各销售员的销售数量，常规来说，如果有了销售员的明细，我们用Sumif函数就可以很方便地汇总出来销售数量，但是我们可以看出，销售员的姓名是无序而且重复的，如果不借助辅助列，我们就需要用到数组函数来提取销售员姓名的唯一值了。

函数：单元格A12

{=IFERROR(INDEX($B$2:$B$8,SMALL(IF(MATCH($B$2:$B$8,$B$2:$B$8,0)=ROW($B$2:$B$8)-1,ROW($B$2:$B$8)-1),ROW(A1))),““)}

函数说明：

利用Match函数找到第一次出现的值的行号，形成一个数组数据，再利用Small函数顺序的找到出现的行号，用Index函数引用姓名，最后用Iferror函数屏蔽错误值。

函数运行过程：

2、提取重复值

上面我们提取了不同姓名的销售员，那么我们如果要提取相同姓名的销售员的销售记录又该如何呢？

函数：单元格A12

{=IFERROR(INDEX(A$2:A$8,SMALL(IF(COUNTIF($B$2:$B$8,$B$2:$B$8)=2,ROW($A$2:$A$8)-1),ROW(A1))),““)}

函数说明：

我们将第一例中的Match部分，替换上Countif函数，如果出现两次就是我们需要的记录了，其余函数部分同上例。

函数运行过程：

3、提取满足条件的明细

还是上面的数据，这次我们来提取销售员“赵”，卖的“中”码货物的明细。

函数：单元格A13

{=IFERROR(INDEX(A$2:A$8,SMALL(IF(($B$2:$B$8=$B$11)*($C$2:$C$8=$C$11),ROW($A$2:$A$8)-1,99^9),ROW(A1))),““)}

函数说明：

和上例不同的地方是，我们唯一改动的就是条件部分，用两个条件相乘，我们要知道逻辑值是可以参与计算的，True=1、False=0，所以函数中的两个条件相乘就形成了由0.1组成的数组数据。剩下的运算道理和第一例完全一样。

函数运行过程：

作者云：

强大的“万金油”函数，其实也就是这些主要的功能了。本来还想举几个例子，但是除了条件变换了，其他的内容都是一样的，而且有的例子没有必要非使用“万金油”函数，巧妙的使用其它函数结合数组数据一样可以达到效果。

如果上面的内容对您还有帮助，或者觉得作者比较用心。可以关注、评论、留言、转发“E图表述”，便于您继续观阅和浏览往期的“Excel干货分享”。微信公众号：“E图表述”或者“Excel_Easy”

Excel定位函数和添加字符的复合使用难题

描述不太明确啊，我感觉就是先判断单元格是不是网址，然后判断网址后有没有斜杠，有的话就不用加，没的话就加，可以用这个公式试试，

IF(LEFT(单元格位置,3)=“www“,IF(RIGHT(单元格位置,1)=“/“,单元格位置,单元格位置&“/“),单元格位置)

提起单元格的前三个字符判断是不是三个w如果是就是网址

然后提起网址最后一位判断是不是斜杠，是的话就返回本身，不是的话就加一个