Note: An inventory table should at least include information such as material number, name, quantity, unit price and total amount, and even the simplest inventory table should at least include the above elements.
2
Secondly, create a new table named "Data Table" to store the material numbers and names.
Note: A project number can only correspond to one name.
three
Next, the material number and name are defined as names to facilitate data entry in the inventory summary table.
Step: select A 1:B 1 1 area of data table, and click Formula Definition Name-Create Based on Selection.
four
In the pop-up interface, select the first line and click OK.
Note: The result of the operation is to define the area of A2: A 1 1 as the name, and the area of B2: B 1 1 as the name. Ctrl+F3 can view the name of the definition.
five
Go back to the table of "65438+ 10 month", select the blank area of column B, and click Data-Data Validity-and select the Data Validity button in the drop-down menu.
six
In the pop-up interface, select "Serial" from the allow drop-down menu shown below, check the provide drop-down arrow, and enter: = the material number at the source. Click the OK button after setting.
seven
After the operation is completed, we can quickly enter the material number by clicking the drop-down menu provided by the blank cell in column B.
eight
Select C4 cell, double-click and enter: =IF(B4= "","",VLOOKUP(B4, data table! $ a $1:$ b $11,2)), so that the name corresponding to cell B4 is automatically filled in. Then, the formula of C4 cell is pulled down and filled in.
Note: An IF function is added to the formula here, so that when the cells in column B are empty, the cells in column C are also empty.
nine
Select cell A4, double-click and enter: = if (B4
Note: the meaning of IF function here is the same as that of column C.
After completing the above steps, just select the material number in column B, and the serial number in column A and the name in column C will be automatically generated.
Enter the quantity and unit price of last month's balance under the column of last month's balance, and enter the formula at the amount: =D4*E4. Multiple varieties can be input in parallel.
Enter the quantity and unit price of this month's warehousing under this month's warehousing column, and enter the formula for the amount: =G4*H4.
Enter the quantity and unit price of this month's outbound under this month's outbound column, and enter the formula =J4*K4 for the amount.
Enter the formula =D4+G4-J4 for the number of columns in this month, =F4+I4-L4 for the amount, and = iError (O4/M4, "") for the unit price.
Note: The reason why the unit price is upside down is mainly because the unit prices at the beginning, receipt and delivery may be inconsistent.
During daily entry, issue/receipt can be entered by peers or only one issue/receipt can be entered in one line. If the date is emphasized, you can change the serial number column to date input or add a column of dates.
Last step: Count the balance quantity, amount and average unit price at the end of the month.
First, select the data area, select Insert-Pivot Table, set the contents as shown below, and click OK.
Drag the quantity and amount of the last column into the sum of numbers box, put the material number and name in the row label area, and adjust the PivotTable format and field names appropriately. The results are as follows:
Insert the calculation field of ending unit price, and divide the ending quantity by the ending amount to get the average unit price of the balance. After adding data, you can update the PivotTable data source and refresh it.
Note: Similarly, the PivotTable can also count the receipt quantity, issue quantity and total amount of the current month.
note:
The above is just a simple example of purchase, sale and storage. If there are more complicated situations or more automatic statistics and report results are needed, it is necessary to combine more complicated formulas and pivot tables.