统计函数countif_使用COUNTIF函数检查中奖号码

统计函数countif

Today, we’ll see a little magic from the COUNTIF function – it can do the work of seven formulas in one! We'll use it to check winning numbers in an imaginary lottery.

今天，我们将从COUNTIF函数中看到一点魔力-它可以将七个公式合二为一！我们将使用它来检查假想彩票中的中奖号码。

The COUNTIF function can check a range of cells, and see if it contains a specific value. For example, type your lucky number in cell D2, and use a COUNTIF formula to see if that number is found in the list of winning numbers in cells I2:N2.

COUNTIF函数可以检查单元格范围，并查看其是否包含特定值。例如，在单元格D2中键入您的幸运数字，并使用COUNTIF公式查看该数字是否在单元格I2：N2中的中奖号码列表中找到。

=COUNTIF(I2:N2,D2)

= COUNTIF(I2：N2，D2)

The number, 26, is found once in the range I2:N2, so the COUNTIF result is 1. Your lucky number is a winner!

该数字26在I2：N2范围内被发现一次，因此COUNTIF结果为1。您的幸运数字是赢家！

检查多个数字 (Check Multiple Numbers)

What if we have multiple lucky numbers to check – not just one number? In the next screen shot, there are six numbers to check, entered in cells A2:F2. The winning numbers are still listed in cells I2:N2.

如果我们要检查多个幸运数字-而不仅仅是一个数字，该怎么办？在下一个屏幕快照中，在单元格A2：F2中输入了六个要检查的数字。中奖号码仍列在单元格I2：N2中。

We could create a similar COUNTIF formula, to check each cell, to see if it is a winner. Here is the formula in B5 – the column references in I2:N2 are locked, so that reference won’t change when the formula is copied across to cell G5.

我们可以创建一个类似的COUNTIF公式来检查每个单元格，看看它是否是赢家。这是B5中的公式– I2：N2中的列引用已锁定，因此将公式复制到单元格G5时，引用不会更改。

=COUNTIF($I2:$N2,D2)

= COUNTIF($ I2：$ N2，D2)

In cell H5, use the SUM function to find the total number of cells that match the winning numbers.

在单元格H5中，使用SUM函数查找与获胜数字匹配的单元格总数。

=SUM(B5:G5)

= SUM(B5：G5)

That shows a total of 1, so only one of the six lucky numbers is a winner.

这表明总数为1，因此六个幸运数字中只有一个是获胜者。

一步获得总计数 (Get the Total Count in One Step)

Instead of creating 6 COUNTIF formulas, and a SUM, we could use an array-entered COUNTIF formula, to get the answer in one step.

代替创建6个COUNTIF公式和一个SUM，我们可以使用数组输入的COUNTIF公式来一步一步地得到答案。

In the next screen shot, the following formula is array-entered in cell B5 – press Ctrl + Shift + Enter, instead of just pressing Enter.

在下一个屏幕截图中，在单元格B5中输入了以下公式–按Ctrl + Shift + Enter ，而不是仅按Enter。

=SUM(COUNTIF(B2:G2,I2:N2))

= SUM(COUNTIF(B2：G2，I2：N2))

If you change the lucky number in cell E2 to 22, instead of 7, the number of winners changes to 2, instead of 1. I’ve added conditional formatting in cells B2:G2, to colour the winning numbers yellow.

如果将单元格E2中的幸运数字更改为22，而不是7，则中奖者的数量更改为2，而不是1。我在单元格B2：G2中添加了条件格式，以将中奖数字涂成黄色。

With cells B2:G2 selected, the conditional formatting formula is the same as our earlier formula:

选择单元格B2：G2后，条件格式公式与我们之前的公式相同：

=COUNTIF($I2:$N2,B2)

= COUNTIF($ I2：$ N2，B2)

检查失败的球队 (Check Losing Teams)

The same technique works with text too, and the range that’s being checked can be vertical, instead of horizontal.

相同的技术也适用于文本，所检查的范围可以是垂直的，而不是水平的。

In the next screen shot, 6 people have picked the teams that they think will win the four games being played today. When each game is finished, the losing team’s name is entered in column H.

在下一个屏幕快照中，有6个人选择了他们认为将赢得今天四场比赛的球队。每场比赛结束后，输掉球队的名称会在H列中输入。

A SUM/COUNTIF array formula calculates how many losing teams each person picked. So far, 3 games are finished, and it’s looking good for Al. He hasn’t picked any losing teams yet!

SUM / COUNTIF数组公式可计算每个人选择的失败球队数。到目前为止，已经完成了3场比赛，对于Al来说看起来不错。他还没有挑选任何失败的球队！

Here is the formula array-entered in cell F2 (Ctrl + Shift + Enter), and copied down to cell F8.

这是在单元格F2中输入的公式数组(Ctrl + Shift + Enter)，并向下复制到单元格F8中。

=SUM(COUNTIF(B2:E2,$H$2:$H$5))

= SUM(COUNTIF(B2：E2，$ H $ 2：$ H $ 5))

Excel仪表板课程推荐 (Excel Dashboard Course Recommendation)

For a limited time, Mynda Treacy from My Online training Hub is opening her Excel Dashboard Course.

在有限的时间内，我的在线培训中心的Mynda Treacy正在开设她的Excel仪表板课程。

The course is video based, delivered online and is available 24/7. You'll receive comprehensive workbooks and sample dashboards to keep, and there’s even an option to download the videos.

该课程以视频为基础，在线提供，并且全天24/7可用。您会收到综合的工作簿和示例仪表板，以备保留，甚至还可以选择下载视频。

I've been through this course, and highly recommend it. The previous classes were very successful, and you can read the glowing reviews from the students, who loved all the techniques that they learned in the course, and are using them to impress their colleagues.

我已经完成了这门课程，并强烈推荐它 。以前的课程非常成功，您可以阅读学生的热烈评论，他们喜欢他们在课程中学到的所有技术，并用它们给同事留下深刻的印象。

Click here to find out the details, read the student comments, and watch the 'behind the scenes' video that shows you what you'll receive as a member.

单击此处了解详细信息，阅读学生的评论，并观看“幕后花絮”视频，该视频向您展示您将获得的会员资格。