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

The original title: "never learn the Frequency function, as soon as you learn it, you can't stop." "

Hello, everyone. I'm Xiao Shuang.

In the previous article, we introduced the basic syntax of the Frequency function, which uses the data axis for parsing.

The basic syntax of the Frequency function:

= FREQUENCY (Data_array,Bins_array)

= FREQUENCY (statistical area, segmented point)

Under the "high pressure" that I chased teacher Zhao to continue to write the extension of Frequency function, today he finally "endured" out!

▲ was forced by me, so now let's learn about one of the extended applications of the Frequency function-the number of wins in Dalian.

What do you mean? Let's take a look at some real-life and work cases.

A group of students are waiting in line. Students of the same sex who are lined up in succession must not exceed N at most, or they will have to reposition themselves.

During the review, if an employee has N customer complaints in a row, he or she needs to be warned.

In the competition field, sometimes it is necessary to look at the number of wins in Dalian, which is used to judge the performance of the members.

……

Recently, I had a competition with Miss Zhao-- flip a coin!

A total of ten games, literally I win, miss Zhao wins. )

I recorded the result of my victory or defeat with Excel, and the results are as follows:

Now we need to calculate the number of wins in Dalian.

What would you do if it were you?

Thinking about...

Thinking about...

Thinking about...

From the table, we observe that successive victories are separated into pieces by "defeats".

In fact, it is to take the position of "failure" as a segmented point to calculate how many "wins" there are between the points.

Frequency is a function that can ignore logical values and text, and only perform piecewise statistics on numerical values.

Therefore, we need to compile a set of ordered values according to the order of the field, such as the G column.

Then the serial numbers of victory and defeat are displayed in two columns, such as column I and column J

Enter the formula in the i3 cell and drop down to fill:

= IF (C3 = "win", G3) enter the formula in J3 cell, drop-down fill:

= IF (C3 = "defeat", G3)

Through observation, we can find that in order to calculate the consecutive number of wins in each segment, we only need statistics:

There are several numbers before 2, between 4 and 9, and after 9.

This brings us to the Frequency function, which counts the number of values in segments:

Take the "win" corresponding sequence number as the statistical data point, and the "defeat" sequence number as the segment point, the Frequency function can help us count!

= FREQUENCY (the order of victory, the order of defeat)

The set of values returned by the formula is the number of consecutive wins in their respective intervals, and the maximum value is the number of winning games in Dalian.

According to the previous idea, now let's take a look at how to use the Frequency function to solve the problem of the most Dalian winning times.

Enter the formula in cell E3:

= MAX (FREQUENCY (IF (C3IF C12 = "win", ROW (1:10)), IF (C3IF C12 = "lose", ROW (1:10) perform the array operation according to [CTRL+SHIFT+ENTER], and the result is as follows:

The operation is as fierce as a tiger, and look at the slow analysis below me ~

The formula is as follows:

= MAX (IF (C3IF C12 = "win", ROW (1:10)), IF (C3IF C12 = "lose", ROW (1:10), where:

IF (C3 C12 = "win", ROW (1:10))-- indicates that the row order corresponding to the victory is returned as the statistical region

IF (C3 C12 = "failure", ROW (1:10))-indicates that the row order corresponding to the failure is returned as the segment point value

The next step is to use the Frequency function to find out the number of values in each interval, and the result returns the array {1 / 0 / 0 / 4 / 1}, that is, the number corresponding to each winning streak.

Finally, the maximum value is obtained by using the MAX function, which is the maximum number of winning games in Dalian.

With a little expansion, the formula can also be simplified to:

= MAX (FREQUENCY (ROW (1:11), IF (C3:C12 "win", ROW (1:10)-1 why can it be so simplified?

In fact, the principle of this formula is similar to the previous train of thought.

The dismantling of specific ideas will be left to everyone to think for themselves.

This paper introduces the application of Frequency function in the number of winning games in Dalian:

The syntax rule of the ❶ Frequency function: = Frequency (statistical region, segment point).

Analysis of the number of winning games in ❷: when we encounter a similar problem about the maximum number of consecutive statistics, we can construct a set of ordered values and enter the Frequency function mode to solve it.

The principle of ❸ formula: the segmented point is the serial number of failure, and the statistical area is the serial number of victory.

For the articles related to the basic syntax of Frequency functions, please click here:

This function is easier to use than COUNTIF and helps you work efficiently.

If you often use Excel at work, and occasionally stay up late to work overtime for Excel, you think you can read the tutorials, but you still have a black eye.

At this time, you need to learn Excel systematically!

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.