In the previous article " Practical Excel skills sharing: How to ignore hidden columns for sum? 》, we learned how to ignore hidden columns for summation. Today we are going to talk about data grouping and numbering, and introduce how to quickly realize automatic grouping and numbering of 16,000 rows of data in 3 seconds. Come and learn!
There are 16,000 people participating in the "Social Security Withholding and Payment Agreement". Each 16 people need to be set as a group. The duplicate numbers for rows 1-16 are 1 and 1. The repeat numbers for lines 17-32 are 2,..., and the repeat numbers for lines 15985-16000 are 1000. how to do?
The above is a real problem that I helped a friend solve some time ago. I called it data group duplication numbering. 16,000 rows of data, numbered from 1 to 1,000. If you use the method of inputting numbers and pasting them, the workload will be large and error-prone. Based on this, I will share with you two methods to achieve automatic grouping and numbering of 16,000 rows of data in 3 seconds.
Method 1: Functional method
1. Operation steps
(1) Edit the "Continuous Repeat Numbering" formula. Enter the formula in cell A1: =IF(MOD(ROW(A1),16)=0,ROW(A1)/16,INT(ROW(A1)/16) 1). As shown in the figure below:
Note: All numbers, symbols, and punctuation in the formula must be entered in the "English input method" state
(2 ) to quickly select the "Continuous Repeat Numbering" area. Select and click cell A1 with the mouse; enter A16000 in the Excel address bar; hold down the "Shift" key without letting go, and then press the "Enter" key. After completing the above three steps, you can quickly select the area that needs to be repeatedly numbered. As shown in the figure below:
# (3) Quickly fill in the formula. After selecting the "Continuous Repeat Numbering" area, in the "Home" tab, click the "Fill" tab and select the "Down" option to complete the automatic filling of the formula. The result of "Continuous Repeat Numbering" is as shown in the figure below:
Note: Many friends are used to dragging the mouse to fill in the formula. Since there are as many as 16,000 rows of numbers here, Filling by dragging the mouse will be time-consuming and is not recommended.
2. Function explanation
A total of 4 functions are used in the formula. Let's first take a look at the respective functions of these four functions.
ROW() function. The ROW() function returns the row number of any cell in the row, such as: ROW(A13)=13, ROW(B13)=13.
INT() function. Rounding functions, such as: INT (0.1)=0, INT (2)=2, INT (3.7)=3, INT (-1.1)=-2. That is: when x ≥ 0, INT (x) = the integer part of the x value (not rounded);
When x
MOD() function. Find the remainder after dividing two numbers, such as: MOD(1,16)=1, MOD(16,16)=0. When MOD(x,y)=0, x is an integer multiple of y. (Note: The first parameter is the dividend and the second parameter is the divisor)
IF() function. The IF() function has three parameters, namely: IF (logical judgment expression, result 1, result 2). When the logical judgment expression is established (that is, true: TRUE), the IF() function returns result 1; when the logical judgment expression The formula does not hold (that is, it is false: FALSE), and the IF() function returns the result 2.
Then let’s understand the meaning of the entire formula.
=IF(MOD(ROW(A1),16)=0,ROW(A1)/16,INT(ROW(A1)/16) 1)
IF first parameterMOD(ROW(A1),16)=0: Determine whether the remainder after dividing the row number of the cell by 16 is equal to 0, that is, whether the row number can be divisible by 16. Obviously, 16, 32, etc. can be divisible by 16, the remainder = 0, and the condition is true; 15, 17, etc. cannot be divisible by 16, the remainder ≠ 0, and the condition does not hold.
IF second parameterROW(A1)/16: When the first parameter condition is true, the number is equal to the quotient of the row number divided by 16. For example:
A16, number = ROW(A16)/16=16/16=1
A32, number = ROW(A32)/16=32/16=2
……
IF The third parameter INT(ROW(A1)/16) 1: When the first parameter is not true, the number is equal to the quotient of the row number divided by 16, rounded Add 1 more. For example:
A15, number = INT(ROW(A15)/16) 1= INT (15/16) 1=INT( 0.9375) 1=0 1=1
A17, number = INT(ROW(A17)/16) 1= INT (17/16) 1=INT( 1.0625) 1=1 1=2
……
Method 2: VBA method
1. Operation steps
(1) Enter the VBA editing window. Press the Alt F11 key combination (or click the "Visual Basic" button on the "Development Tools" tab) to enter Visual Basic in Excel.
(2) Select the "Module" command in the "Insert" menu, and then enter the following code in the right window:
Sub rep()
Dim i%
For i = 1 To 1000
Sheet2.Range("A" & (16 * i - 15) & ":A" & (16 * i)) = i
Next i
End Sub(3) Press the F5 key (or click the Quick Tool After running the above program, you can quickly generate consecutive repeat numbers in cells A1:A16000. The operation process takes less than one second, as shown in the figure below.
2. Code explanation
For i = 1 To 1000: Used to extract the specified number Value range. If the number value is 2 to 25, write For i = 2 To 25.
Sheet2: Used to specify the worksheet that needs to be numbered. sheet2 does not refer to the name of the worksheet, but refers to the second sheet (from left to right) of the Excel workbook. If you need to generate a number in the first sheet, just change the code to sheet1, and so on in other cases. .
Range("A" & (16 * i - 15) & ":A" & (16 * i)): Cell range and rules for specifying numbers, meaning Starting from cell A1 to cell A (16 * i) , every 16 cells are numbered.
"A" refers to the column number that requires a production number. If you need to generate a number in column B or C, write "B" or "C";
If you need to generate a number in column B or C, When the mth cell of a column starts to generate numbers, you only need to replace 16 * i – 15 with 16 * i m-16, 16 * i It becomes 16 * i m-1.
If you need to number every 5 cells and start numbering from B1, you can write Range("B" & (5 * i - 4) & ":B" & ( 5 * i))
Key review
Quickly select an area. Use the mouse to select the cell in the upper left corner of the candidate area (such as: A1); enter the cell in the lower right corner of the candidate area (such as: B16) in the Excel address bar; hold down the "Shift" key without letting go, and then Press the "Enter" key. After completing the above three steps, you can quickly select an area.
Skillful use of Excel functions is the key. There are many beginners who have mastered a large number of basic Excel functions, but they just don’t know how, when to use them, and which ones to use. I suggest that everyone regards the basic functions of Excel as the "material" for our cooking, and the mathematical laws and logical relationships hidden in events as the "tools" for cooking. Think more and practice diligently. Then when you encounter problems, you will be "comfortable". Come on".
Related learning recommendations: excel tutorial
The above is the detailed content of Practical Excel skills sharing: 16,000 rows of data are automatically grouped and numbered. For more information, please follow other related articles on the PHP Chinese website!