Practical Excel skills sharing: cleverly use functions to create an automatic statistical purchase, sales and inventory table

In the previous article "Practical Excel Skill Sharing: Several Practical Operations of the "Column Sorting Tool"", we learned the practical operations of several sorting tools. Today we are going to learn four functions. Using them skillfully, we can create a "Purchase, Sales, Inventory and Inbound and Outbound Statistics Table" that can automatically count inventory. It is simply 666. Once you learn this operation, you can be freed from tedious work and enjoy poetry and the distance. Come and take a look!

How to make a purchase, sales and inventory table that can automatically count real-time inventory and the quantity in and out of the warehouse? In fact, it does not require very difficult technology. You only need to master four functions and You can make it yourself if you have some basic Excel editing and typesetting skills.

These four functions are: vlookup, iferror, sumif and if. Next, let the veteran take you step by step to create this entry and exit table.

Function description of "Purchase, Sales, Inventory and Inbound and Outbound Statistics Table":

Real-time statistics function: You only need to record the outbound and inbound flow table according to the prescribed format. Automatically perform real-time statistics on the latest inventory and inbound and outbound quantities.

Smart reminder function: When the inventory of an item is lower than the safety stock quantity, it will be automatically marked to achieve a warning effect.

The composition of the "Purchase, Sales, Inventory and Inbound and Outbound Statistics Table":

According to the most basic requirements, it usually takes three Parts: basic data table (also called basic information table), inbound and outbound record table (also called flow detail table), inventory statistics table (also called result query table). The following describes the methods of these three parts respectively.

1. Basic data table

Design according to the actual needs of the company, grasp a basic principle, the table must be able to reflect all the items attributes, and each attribute is stored in a separate column. The table does not need to be beautiful, and merged cells must not appear.

For example, the following figure is a relatively standardized basic data table:

Note: The serial number is not necessary, just for easy search; statistics are usually Use the product code as the only basis. If by chance your company's products do not have codes, the serial number can be used as the code.

In order to ensure the accuracy of statistical data, when there are new products, records need to be added to the table. If there are obsolete products, there is no need to delete the original records.

2. Inbound and outbound record table

Usually outbound and inbound are divided into two sheets for storage, or they can be combined together Storage, for convenience, we put them together for examples.

The data columns in the table need to contain basic product information as well as the date and quantity of entry and exit. The format is roughly:

In the flow table above , only the blue columns A, E, F, and G need to be recorded in time. Basic information such as classification, name and unit columns are automatically generated through formulas. You must have guessed it, it’s time for vlookup to come on stage!

Yes, this is when vlookup comes into play. From the picture below, you can see that the three columns after the encoding are all obtained using the vlookup function. The formula in cell B2 is: =VLOOKUP($A2, basic data table!$B:$E,COLUMN(B1),0)

Formula interpretation: vlookup requires a total of four parameters. The basic format is

=vlookup (search value, search area, number of columns, exact search)

• The first parameter $A2 represents the content you want to find. Note that because the formula needs to be pulled down to the right, $ is added in front of A to lock the column and prevent it from being pulled down to the right. An error occurred;

• The second parameterBasic data table!$B:$E represents the area to be searched (the basic data table introduced earlier in the article), please note This area starts with the encoding column. Because the encoding is in column B of the basic data table, the area also starts from column B instead of column A. This must be remembered because many novices use vlookup here. A mistake was made;

• The third parameter indicates which column of the search area the returned content is. Because the formula needs to be pulled to the right, we use column(B1)As the number of columns returned.

  The function of column is to get the column number of the parameter. We want to return column C in the basic data table $B:$E, which is column 2. The column number of cell B1 in the entire parameter basic data table is 2, so here we use column(B1 ) indicates the number of columns to be returned. When the formula is pulled to the right, B1 will become C1, and the column number will change from 2 to 3, achieving the purpose of pulling to the right of a formula to complete multiple column references.

• The last parameter 0 indicates exact search.

The last three columns of the table, the date and the quantity in and out of the warehouse, can be recorded according to the actual situation. Under normal circumstances, this flow chart is completed, but in order to use it more intelligently, the vlookup part can also be optimized.

When we enter the product code, we may enter it incorrectly (or enter a new code that is not in the basic data table), and we will get some garbled codes:

The effect does not look very beautiful, so you need to use another function iferror to cooperate with vlookup to solve this problem. The formula is modified to: =IFERROR(VLOOKUP($A2, basic data table!$ B:$E,COLUMN(B1),0),"Please check if the encoding is wrong!")

This may be the first time for some friends to see iferror Let’s briefly introduce this function:

=iferror(formula, content displayed when the formula result is wrong), the formula only requires two parameters, the first parameter is a formula, the second parameter The first parameter is what needs to be displayed when the formula result is wrong. In this example, the first parameter is vlookup. When the result of vlookup is correct, iferror has no effect. But when the result of vlookup is wrong, the required content will be displayed. In this example, a string of text is displayed: Encoding has Please check by mistake! Note: If the content to be displayed is text, it must be enclosed in quotation marks.

3. Inventory statistics table

The function of this inventory statistics table is to display the inventory status of all products in real time. It is roughly as follows Information: cumulative outbound quantity, cumulative inbound quantity, current inventory quantity; if a shortage prompt is required, a safety stock quantity and whether it is out of stock are also required.

This statistical table does not need to be created separately. You only need to add the contents just listed after the basic data table. The format is as shown below:

You can see that six columns have been added to the back of the basic data table. Only the initial inventory and safety stock quantity need to be entered. The cumulative outbound quantity, cumulative inbound quantity and whether it is out of stock are all required. This is achieved through formulas. Here is a brief explanation of these fields:

Initial inventory: It can also be called inventory carryover. The original inventory is recorded when this entry and exit statistics table is enabled.

Cumulative outbound quantity (column G): Use the formula =SUMIF(Inbound and outbound record table!A:A,B2,Inbound and outbound record table!F:F)Statistical results:

Formula analysis: sumif function requires three parameters, the basic structure is =SUMIF (condition area, condition, summation area)

• The first parameterInbound and outbound record table!A:A represents the condition column;

• The second parameterB2Indicates the conditions that the previous condition column should meet (corresponding to the item code of the line);

• The third parameterInbound and outbound record table!F:F indicates that the conditions are met of are summed in this column.

In the same way, replace the third parameter Inbound and outbound record table!$F:$F with Inbound and outbound record table!$G:$GGet the cumulative warehousing quantity (column H):

Current inventory quantity: Use the initial inventory - the cumulative outgoing quantity to accumulate the warehousing quantity;

Safety stock quantity: In this example, it is set to 50, which can be determined according to the situation of each product. This item needs to be entered manually.

Whether it is out of stock: The IF function is used here, the formula is: =IF(I2>J2,"","Out of stock")

The basic format of the If function is if (condition, the result required when it is established, the result required when it is not established);

The condition in this example is I2> J2, that is, it is judged that when the current inventory quantity is greater than the safety stock quantity, a blank is obtained. Otherwise, the word "out of stock" is obtained.

At the same time, conditional formatting is set for this column. When there is a shortage, use color to get an eye-catching effect.

The setting method is to select column k and click [Conditional Formatting] → [Highlight Cell Rules] → [Equals]:

on the left Enter the word "out of stock" in the box, select the desired effect on the right, and confirm.

At this point, an automatic statistical entry and exit table can be easily implemented! With this tool, you no longer have to worry about miscalculating the warehouse inventory of thousands of items. Once you find that there is a shortage, you can tell the procurement to buy it, and the efficiency has also improved!

Finally, let me explain that the design ideas for similar inbound and outbound statistics tables are roughly the same. Some optimizations can be made based on the actual application. If there is unit price and other information, it can be done in the basic data table. Add it, and then use quantity * unit price to get the amount.

It is a good idea to use data validity to standardize data entry. For example, if the encoding is required to be unique, you can set validity to prevent repeated entry (if you don't know how to do this, you can leave a message).

Set formula protection to prevent misoperations from destroying formulas and affecting the accuracy of data, etc...

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: cleverly use functions to create an automatic statistical purchase, sales and inventory table. For more information, please follow other related articles on the PHP Chinese website!