The merging skills of the Excel country tax form

One, fixed a more content of the form, for example, fixed government tax forms, and then the state tax of the enterprise name and the state tax table data copied to the local government rent form, in the name bar selected then use the COUNTIF formula, look for duplicate name enterprises, marked red, and then selected in-place taxes on the enterprise name, sorted by cell color, So the name of the red Enterprise will be concentrated together, can be selected in other adjacent columns plus color callout, because only these colored will have the national tax data, the other color-free tax data is not the IRS number, and then select the original state tax enterprise list, sorted by color, delete duplicate enterprises. The remaining is the list of all enterprises.

Second, the list of enterprises to find a blank column use the lookup function to find out the tax income in the IRS form (limited to the color in the local government list), for example: VAT, corporate income tax. There is no content in the area at this point will appear error prompts, you can use if plus isna to screen errors, if it is 2007 and above version, directly with Iferror can. =iferror (VLOOKUP) (A3, IRS! A:d,column (B1), "" ")

Third, copy, paste selectively, the value, on the line.

If you repeat the above, you will be able to extract the value from all the IRS forms to the tax form.

This kind of benefit is concise, not easy to make mistakes. In addition to the actual operation of the calculation of VAT local retention used to round to retain two decimal places, there is no value where there will be error prompts. You can use if (a1= "," ", ROUND (a1*25%,2)) to screen, and also easy to sum.

Wordy for a long time, maybe everyone can not see very clearly, the picture is done, too big, can't pass. In fact, a lot of practice is only the use of their own slowly will be summed up.