Excel fast l count the number of red data in all lines [commonly used in teaching]

Case scenario

Suppose there is one Excel Data sheet for , The row and column dimensions of the data table are relatively large （ There are more columns , There are quite a lot of lines ）, Our common Excel The method has been unable to meet our business and teaching analysis needs , Here's how to quickly apply , Solve problems in life .

The goal is 1

Give a data table as follows , We need to fill the cells with colors , Count the number of columns according to each row , Finally, it is shown in our data sheet .

First, fill the data font in the cells with color , Turn red

Using shortcut keys ：Ctrl+H, Select the fill format from the cell

Of course , Here you can also use the format flexibly , Suppose our original table is not marked , Only data , Then we can according to the range of data , The type of text , And other data constraints to replace and normalize it

Click Replace All

The goal is 2

Right click on the Sheet1 View code or alt+F11 open VBA namely Visual Basic for Applications, Click Insert >>> modular

Copy the following code into the box ：

Function CountRed1(rng As Range) As Integer

Dim rng1 As Range

CountRed1 = 0

For Each rng1 In rng

If rng1.Font.Color = 255 Then CountRed1 = CountRed1 + 1

Next rng1

End Function

This is success ！！！！

Then back Excel Custom insert a row , Used for counting numbers , Input =CountRed1（）, Fill in the brackets and click the range you want to count

Then enter and the result will appear ！

summary

This technique is very convenient , Little buddy who can't program , Although we can use python To achieve , But for beginners Xiaobai ,python It may not be a simple and efficient way , Sometimes choice is more than effort