选择大于努力，几行VBA代码，分分钟搞定函数难题

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

【置顶公众号】或者【设为星标】及时接收更新不迷路

小伙伴们好，今天来和大家介绍这样一道题目，具有特别的意义。当遇到一道题目时，无论使用何种方法，解决问题都是首要目标。当然，选择的方法不同，解题过程的难易程度也不相同。

今天这道题目就分别提供了公式和VBA两种解决方法，从中大家可以自行体会到这两种方法的难易程度。

题目是这样子的：

有一组90行的数据，记录了运动员6个科目的成绩。现在我们要从中找出每个运动员的总分第一次突破700时，是他的第几次成绩。同时，还要列出对应的每一科成绩出来。

这道题目如果用公式来做，是可以完成的。分两步来完成，先求出第几次，然后再返回成绩。用一对多的查询方法就可以。

怎么样，朋友们有自己的答案了吗？

今天这篇帖子的重点不是介绍公式方法，我们简单介绍一下公式的思路后把公式贴出来给大家。先来看各科成绩。

在单元格L3中输入下列公式，三键回车并向下向右拖曳即可。

=OFFSET($A$1,SMALL(IF(($A$2:$A$90=$J3)*(SUBTOTAL(9,OFFSET($B$1:$G$1,ROW($1:$89),,1,6))>700),ROW($A$2:$A$90)-1),ROW($A$1)),COLUMN(A1))

一句话解释：按照给定的条件（姓名和总分>700）返回对应的行号，最小的那个行号就是第一次总分大于700的那次记录。通过OFFSET函数返回对应的成绩即可。

下面再来看看如何求得第几次。

在单元格K3中输入下列公式，三键回车并向下拖曳即可。

=COUNT(1/(IF(($A$2:$A$90=J3)*(SUBTOTAL(9,OFFSET($B$1:$G$1,ROW($1:$89),,1,6))<700),ROW($A$2:$A$90))*(ROW($A$2:$A$90)<SMALL(IF(($A$2:$A$90=$J3)*(SUBTOTAL(9,OFFSET($B$1:$G$1,ROW($1:$89),,1,6))>700),ROW($A$2:$A$90)),ROW($A$1)))))+1&"次"

一句话解释：按条件（姓名、总分<700、行号要小于第一次超700是的行号）就可以找到在第一次超700的记录之前总共有几次该运动员的记录。加上1就是最终答案。

从上面的内容可以看到了，公式书写起来还是比较复杂的。下面我们来看看VBA代码。

完整的代码如下：

Sub test1()    Dim i As Integer, m As Integer, n As Integer, rng As Range, rng1 As Range, arr()    n = 17    Set rng1 = Range("B1:G1")    For Each rng In Range("J17:J25")        For i = 2 To Cells(Rows.Count - 1, 1).End(xlUp).Row            If rng.Value = Cells(i, 1).Value Then                If Application.WorksheetFunction.Sum(rng1.Offset(i - 1, 0)) <= 700 Then                    m = m + 1                Else                    Exit For                End If            End If        Next        Range("K" & n).Value = m + 1 & "次"        Cells(i, 2).Resize(1, 6).Copy Range("L" & n)        Range("J16:Q25").Borders.LineStyle = xlContinuous        n = n + 1        m = 0    NextEnd Sub

它利用循环，依次先查找姓名，再判断总分是否小于700。如果小于700就计数一次，否则跳出查询下一行。

第5、6行代码：建立循环

第7-13行代码：判断、查找并计数

第15、16行代码：返回次数和成绩

好了，今天的内容就是这些了。朋友们觉得那种方法更适合你呢？

本期内容练习文件提取方式：

链接：https://pan.baidu.com/s/1mg6uJ8Y4FOwFafy0uKs5HQ?pwd=a6yq

提取码：a6yq

好了朋友们，今天和大家分享的内容就是这些了！喜欢我的文章请分享、转发、点赞和收藏吧！如有任何问题可以随时私信我哦！

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

我就知道你“在看”