HOW TO AUTOMATICALLY SPLIT 1563 IN EXCEL INTO SEVERAL 100, SEVERAL 50, SEVERAL 5, SEVERAL 15?

Enter the following formula in columns b, c, d, e, f, and g in column a:

Cell B2: "=int(A2 100)".

C2 cell: "=int(mod(a2,100) 50)".

Cell D2: "=int(mod(a2,50) 20)".

cell e2: "=int((mod(a2,50)-d2*20) 10)".

F2 cell: "=int((mod(a2,10)) 5)".

G2 cell: "=(mod(a2,5))".

The "" sign is not included.

Let your first column be the data and the second column be the formula"=int(a1/100)"It is how many 100s, and then divide the remainder by dividing by 100 by 50 to round up, which is how many 50s, and so on.

Suppose a2 is the salary amount, and b2 is the number of 100 yuan = int(a2 100).

c2 finds the number of 50 yuan = --int((int(a2)-b2*100)>=50).

d2 finds the number of 20 yuan = int((int(a2)-b2*100-c2*50) 20).

e2 finds the number of 10 yuan = int((int(a2)-b2*100-c2*50-d2*20) 10).

F2 finds the number of 5 yuan =--(int(a2)-int(a2 10)*10)>=5).

g2 finds the number of 2 sheets =int((int(a2)-int(a2 10)*10-f2*5) 2).

h2 finds the number of 1 yuan = int(a2)-int(a2 10)*10-f2*5-g2*2

i2 finds the number of 5 angle sheets =--(int(a2*10)-int(a2)*10)>=5).

j2 finds the number of 2 angle sheets =int((int(a2*10)-int(a2)*10-i2*5) 2).

k2 finds the number of 1 corner sheets = int(a2*10)-int(a2)*10-i2*5-j2*2

That's all right. Let's say your data is in column A, and the maximum number is in thousands.

Then I set b, c, d, e, and f to 100 yuan, 50 yuan, 10 yuan, 5 yuan, and 1 yuan.

Column b formula: =left( a2,2).

Formula for column C: =if(int(right($a 2,2))>=50,1,0).

Formula for column d: =if(right($a 2,2)-50>0,int((right($a 2,2)-50) 10),int(right($a 2,2) 10)).

Formula for column e: =if(right(a2,1)-5>0,1,0).

Formula for column f: =if(right($a 2,1)-5>0,int((right($a 2,1)-5)),int(right($a 2,1)))).

Formulas can be copied by dragging them down.

Example a1=1563

b1=int(a1/100)

c1=int((a1-b1*100) 50)d1=int((a1-b1*100-c1*50) 5)e1=a1-b1*100-c1*50-d1*5, so column b is the number of 100.

Column C is the number of 50.

Column d is the number of 5.

Column e is the number of 1.

What are your requirements for the spin-off?

c1 set it yourself.

d1.

if(lookup($a$1-sum($c$1:c1),,1,lookup($a$1-sum($c$1:c1),,a$1-sum($c$1:c1))

Copy the formula to the right.

I want to copy the formula below and modify it myself.

If the right copy is too long, the mantissa will be abnormal, adjust it yourself.

A is listed as data.

Column B is left blank to hide.

C1 input. iferror(if(lookup($a1-sum($b1:b1),,1,lookup($a1-sum($b1:b1),,a1-sum($b1:b1)),"")

The formula pulls the fill hard to the right, and then pulls down the fill.

=if(($a3-sum($b3:b3))>100,100,if(($a3-sum($b3:b3))>50,50,if(($a3-sum($b3:

b3))>20,20,if(($a3-sum($b3:b3))>10,10,if(($a3-sum($b3:b3))>0,$a3-sum($b3:

b3),"")))

Once you're done, you can pull it down or to the right.

The rules of 189 splitting What are the rules, for example, 189, which can be split into 100, 50, 30, 9, and 100, 50, 20, 10, 9, or 100, 50, 10, 10, 10, 9 in these cases?

Provide a screenshot of the source file and the style of the header, so that it is easy to write**.

How excel automatically splits a ** into multiple orders ** method is as follows:

1. Operation method: below"sheet"Right-click and select"Move or copy the worksheet"In"Establish a copy"tick on it.

The whole ** is copied and completed. Then delete the whole column that you don't want: use the mouse in"a2"Right-click and select"Delete"and select again"Whole column"I just deleted the A2 column. And so on.

2. Procedural method, program the VBA applet, and then extract the data to the location of the person you need according to your requirements.

below"sheet"Right-click and select"Move or copy the worksheet"In"Establish a copy"tick on it.

The whole ** is copied and completed.

Then delete the whole column that you don't want: use the mouse in"a2"Right-click and select"Delete"and select again"Whole column"I just deleted the A2 column.

And so on.

Pro, as shown in the figure below, C1 pastes this formula, and pulls it down to C10; Then select C1:C10 and pull 100 columns to the right as a whole.

indirect(address((column(a$1)-1)*10+row($a1),1))

What does this mean?

Press f9 to refresh the changes.

Select A1:A9, enter =rand() and press Ctrl+Enter to finish;

Select B1:B9 and enter =A1 SUM(A$1:A$9)*100 Press Ctrl+Enter to finish.

First page settings: You select each set of data in the Classification Summary (remember to sort by club class first) and then select each set of data in Classification Summary.

Furthermore, you can make a pivot table in the Data menu and set it to your request in the wizard.

Manually select the workbook to be split, for example, the selected workbook is named "Workbook A to be split", split all the worksheets in the workbook into separate workbooks, and use the name of the worksheet as the name of the new workbook, and store it in the folder named "Split - Workbook A to be split - Obtained workbook" under the directory where the workbook is located.

1. First of all, you need to choose whether you need to quantify the split worksheet.

2. Secondly, select the workbook that needs to be split. Only one can be selected.