Shulou(Shulou.com)11/24 Report--

The original title: "this function is 3 times easier than COUNTIF to help you work efficiently and get off work at the end of the day!" "

Lao Wang, a classmate I haven't seen for a long time, suddenly found me.

He is a teacher of the people, and every time he takes a test, he wants to know quickly about the grades of the students in his class:

How many people are there with scores below 60, 60-80, 80-90 and above?

As shown in the following figure, count the number of people at each level according to the scores in column C.

What would you do if it were you?

For students with a certain foundation, the first reaction may be to use the COUNTIF function.

Let's first take a look at what COUNTIF does.

The COUNTIF function method uses the COUNTIF function, and we need to enter the following formula in column G:

Enter the formula in the ❶ G3 cell to find the number of people whose scores are less than 60.

= COUNTIF (CRV C, "59", C RV C, "79", C RV C, "89") obviously, with the COUNTIF function, we need to write formulas one by one. How troublesome!

Now I have to come up with my "trick"-- the FREQUENCY function.

You can count the number of people by stages with only one formula.

Next let's take a look at how it works.

The FREQUENCY function method selects the G3:G6 cell and enters the formula in the formula bar:

= FREQUENCY (C3:C18, {59 Magazine 79 Magazine 89}) press the [CTRL+SHIFT+ENTER] triple key to do the array operation, and the results are all out.

Isn't it super simple!

Now we know how the FREQUENCY function works, but what exactly is it?

It looks so complicated! Don't worry! Next we will explain its grammatical rules.

The basic syntax is as follows:

= FREQUENCY (Data_array, Bins_array)

= FREQUENCY (statistical region, boundary point value of the group)

The FREQUENCY function takes two arguments, which are used to calculate the frequency at which values appear in a region, and then return a vertical array.

Let's take a look at the formula of the previous case:

We can understand the formula FREQUENCY (C3:C18, {59pr 79je 89}) as:

The value to be counted is in the cell range C3:C18, which is used as the first parameter: the value is distributed on a numerical axis

Then, according to the three values in the array, {59, 79, 89}, as the segmentation point, it is the second parameter: they divide the data on the numerical axis into four intervals.

The figure is as follows:

Finally, the FREQUENCY function will help us count the number of values in each interval range, and return a vertical array, that is: {2bot 11 * 2}.

Isn't it easy!

When we need to count the numbers in segments, it is more efficient and concise to use the FREQUENCY function to solve the problem.

Do you think the FREQUENCY function can only be used to count? Then you're wrong!

Because the FREQUENCY function also ignores logical values, its "packet boundary value" can be out of order, allowing repetition.

When the segment point appears for the first time, the statistical number of the segment point is returned, the repeated segment point returns 0, and the last one returns the number that is greater than the maximum value of the segment point.

Here is a simple example to verify this feature:

So, it can also:

❶ calculates the number of unrepeated values

❷ statistics maximum number of consecutive times

❸ Statistical text Distribution Frequency

❹ finds the value closest to the target value

……

To take a simple example, it can do Chinese-style ranking.

In other words, no matter how many juxtaposed 1st place, the subsequent ranking is still 2nd, that is, the juxtaposed ranking does not occupy the ranking.

Enter the following formula in the D3 cell, then press the [CTRL+SHIFT+ENTER] triple key and drop down to fill.

= SUM (--(FREQUENCY (Cymbatis 3cong Che 11Magi if (C$3:C$11 > = C3mai Che 3RU Che 11)) > 0)

A brief analysis:

IF (C$3:C$11 > = C _ 3 ~ C _ 3 _ C$3:C$11 _ 11). If the value of the C$3:C$11 region is greater than or equal to C _ 3, the corresponding value will be returned, otherwise the logical value False will be returned.

Taking advantage of the characteristic that FREQUENCY ignores logical values, the values of cells greater than or equal to C3 are used as segmented points.

Because the repeated segmentation points return 0, the non-zero number of the returned result is the number of unrepeats greater than or equal to C3.

Finally, we use the SUM function to find the number of non-zeros, which is the ranking of C3 in C$3:C$11.

Let's make a summary of the FREQUENCY function:

❶ when we need to segment the number of values, we can use the FREQUENCY function

The result returned by ❷ FREQUENCY is an array and needs to be entered in the form of an array formula.

Press [CTRL+SHIFT+ENTER] three keys

Each segment point in the "Boundary value of a packet" in ❸ is grouped according to the rule of opening left and closing right.

Such as the formula:

The statistical range of = FREQUENCY (C3:C18, {59 Magi 79 Magi 89}) is: the numerical value ≤ 59 J 59 89.

The ❹ FREQUENCY function ignores whitespace, text, and logical values, and its "grouping boundary value" can be out of order, allowing repetition of segmented points.

❺ returns the statistical number of repeated segmented point data in "Boundary value of a packet" only when the segmented point appears for the first time

The repeated segment points then return 0, and the last one returns the number that is greater than the maximum value of the segment.

Due to the limited space of the article, today we will learn the basic usage of the Frequency function for a while, and we will continue to discuss more about its usage.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhao Jiaoyang & Xiao Shuang, Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.