Invoicing management, in many companies will be involved, the need for a clear summary analysis of the goods out of the warehouse, warehousing, inventory details, today teach you to use Excel pivot table, easy to make a small system of invoicing management
1. Form design
There are many friends who may put a table in the warehouse, and then put a table out of the warehouse
This design is not recommended, because it is complicated to perform various calculations across tables and requires a solid function formula to build a purchase, sale and inventory, we can change the table design to the following style:
We record the details of inbound and outbound in a table, and then one of the fields is used to mark the inbound or outbound warehousing
2. Create a pivot table
Then we select the entire column of data A:E and insert the pivot table:
Then we put the items in the row tab
Place the type in the column tab
Put the quantity in the value to get the following effect
Because we select the data source of the entire column, we filter the column labels and remove the blank items:
3. Calculate inventory data
Since the totals on the right are not needed, we click on Design, Totals inside, and only enable the column to remove it
Then we place the cursor in the inbound location, click on the analysis of the pivot table, and select the calculated fields, items, and sets
Select a calculated item
Customize a name, for example, for Inventory, and then enter the formula is
= Inbound - Outbound
When the data of our original table is updated, refresh it, pivot table, and automatically update the warehousing and inventory
Have you learned this little trick? Try it!