Excel case sharing: How to use Excel to create an electronic inventory ledger? (Summary of inbound and outbound ledgers)

Many warehouse managers have to summarize the inventory ledger balances every month. If they don’t know how to design the inventory ledger form, they have to work overtime every day. In fact, using three functions in excel, you can design the warehouse entry and exit in a few seconds. Ledger template solves the problem! Today, this article will share with you an article on how to use Excel to create an electronic inventory ledger. The author's explanation is from simple to deep, which is very helpful for beginners to learn. Come and take a look!

#Today I would like to share with you a case that a student encountered regarding the summary statistics of inventory ledger balances.

Briefly introduce this student’s problems and needs.

This trainee’s job is mainly to statistically manage the inventory accounts of various types of products. Now you need to summarize the balance quantities in the product inventory information tables of a large number of models into a worksheet.

As shown below:

For example, GHE, AA, and 600 are three different models of products. The last line in column E is the latest balance of the product. Condition. (Note: The formats in each product model table are consistent)

Now you need to use the product model as a row in the summary table to count the final balance of each product.

As shown in the figure below:

There are actually two problems when we want to achieve such a demand.

• 1. Reference the data in each product balance detail worksheet to the summary table.

• 2. How to return the final balance in the product model table.

Let’s take these two questions to solve the needs of this student.

1. Since references are used, we must be able to use the indirect function, whose main function is to return the reference specified by the text string.

Example:

#A column is the name of the worksheet, by combining the text characters in cell A2 with !B2 merge builds a reference. I believe you must be familiar with cross-table matching in your daily work. The target cell references are composed of worksheet name, exclamation mark, and cell name , such as: GHE!B2.

Here we can directly return the contents of cell B2 in the GHE worksheet through the INDIRECT(A2&"!B2") function formula.

Let’s see if the content of cell B2 in the GHE worksheet is GHE.

We see that the content of cell B2 in the GHE worksheet is indeed GHE.

2. The second question is how to return the final balance.

Taking the GHE worksheet as an example, our ultimate goal is to return the contents of cell E7 in the table, and it needs to follow the number of rows Change with change.

Seeing this, I believe many people will think of using the offset function to complete it

The function of the Offset function is to use the specified reference as the reference system and obtain a new reference through the given offset. The reference returned can be a cell or a range of cells. And you can specify the number of rows or columns to return. Reference serves as the reference region of the offset reference system. Reference must be a reference to a cell or a connected range of cells; otherwise, the OFFSET function returns the #VALUE! error value.

As shown below:

Function formula: =OFFSET(B2,5,3,1,1)

Meaning: Using B2 where the GHE worksheet is located as the reference cell, offset 3 columns to the right and 5 rows down to return the final balance of cell E7. 5 in the function formula means the fifth row, 3 means the third column, and the last two parameters are 1, which means only the content of one cell is returned.

Next we only need to nest the function formula with the indirect function formula in the first step:

=OFFSET(INDIRECT(A2&"!B2"), 5,3,1,1)

The static data return is done, so how can it change at any time as the number of rows changes?

Because the date in column A in the table corresponds to the balance in column E, here we cleverly replace the number of rows with the count function, and use the count function to count the number of numerical cells in column A as the second parameter of OFFSET parameters. In this way, we can count the corresponding final balance data at any time as the number of rows changes.

The function formula is: COUNT(INDIRECT(A2&"!A:A")) 1, the reason for adding 1 is because there are only 4 cells in column A of the GHE worksheet that are numerical values , and in the previous case we needed to shift downward by 5 lines, so we need to add 1 to this.

Final function formula:

=OFFSET(INDIRECT(A2&"!B2"),COUNT(INDIRECT(A2&"!A:A")) 1,3,1, 1)

Let’s briefly summarize and sort out:

The main difficulty in this case is how to reference the last row of values ​​in the specified column, here We used the offset and indirect functions to complete the reference of the specified data, and at the same time completed the dynamic update and search of the specified cells through the count function. Finally, it is possible to quickly count product names and return the final balance of the corresponding inventory ledger.

Related learning recommendations: excel tutorial

The above is the detailed content of Excel case sharing: How to use Excel to create an electronic inventory ledger? (Summary of inbound and outbound ledgers). For more information, please follow other related articles on the PHP Chinese website!