Open a blank worksheet, copy the name and expenditure amount of the filled-in total wages to columns A and B of the table, and enter the face value of each RMB in cells C 1 to O 1 respectively. As shown in the figure below:

Enter the formula =INT(B2/C$ 1) in the C2 cell? This means that several bills of 100 yuan are needed to calculate the salary in B2 cell. After the formula is entered, select the C2 cell, drag the fill handle in the lower right corner with the mouse, and copy it down to the C20 cell.

So 100 denomination sheets have come out.

Then enter the formula = int (($ b2-sum product ($ c $1:c $1* $ c2: c2))/d $1+0.001) in the D2 cell? The SUMPRODUCT function is used here, and its definition is "Multiply the corresponding elements between the arrays in a given array group and return the sum of the products". Please note here that you must add a 0.00 1 at the end, because EXCEL will use scientific counting methods when calculating, which will cause slight errors in the data results, and wrong results will appear when truncating and rounding with INT function.

Select D2 cell, drag it to the right and copy it to O2 cell, then select D2:O2 at the same time, and drag it down and copy it to other cells.

So the main part is completed, and finally a summary is made at the bottom of the table.

Enter the formula =SUM(B2:B20) in the B22 box to calculate the total amount.

Enter the formula =SUM(C2:C20) in the C2 1 box to calculate the total number of hundred-dollar bills, and copy this formula to the right of the O2 1 box.

Enter the formula =C2 1*C 1 in the C22 cell to calculate the total amount of the hundred-dollar face value, and copy this formula into the O22 cell on the right.

Tip: Set the cell format of C2:O20 area to "Accounting Only", select "None" as the currency symbol, and keep the decimal place of "0". The value of 0 in the table will be changed to "-"to avoid displaying useless zero values.