The merging skills of the Excel country tax form

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.