Application of the automatic calculation tool evaluate() in Excel function learning

Recently, I received help from a classmate Zhou who works in a express delivery company. The main reason is that he encountered some troubles when calculating the volume of the package.

Let’s see if there are good solutions to the problems encountered by Zhou.

The following table shows the size data of express parcels compiled by Mr. Zhou recently. One of the important tasks is to calculate the volume of the parcel through length*width*height. This problem is also an automatic calculation problem in Excel that we often encounter.

# Zhou said that he can actually do it himself, but the method is clumsy and primitive.

1. Excel column data calculation volume

The method Zhou used himself is to divide the data into columns. Since the three figures of length, width and height are equal, They are separated by asterisks, so the volume can be calculated by placing the numbers in three cells in columns.

Operation steps

1. Select the data in column G and click [Column] in the [Data] tab

2. The column sorting wizard dialog box appears. We need a total of 3 steps to complete the data sorting. The first step is to choose the column separation method: [Delimiter], [Fixed Width]. There are asterisks in Mr. Zhou’s table to separate the data. You can use the delimiter to separate columns, so we select [Delimiter] and click [OK] 】.

Note: [Separator] method is mainly used when there are obvious character separations. [Fixed width] is mainly used when there is no character separation or If there is no obvious pattern, manually set the width of column characters.

3. Click [Next] to enter the second step of the text column wizard. Here we can choose the separator symbol, which can be the TAB key, semicolon, comma, space, or other customizations. Since there is no asterisk in the default options, we check other and then enter the asterisk.

After the input is completed, you can see in the data preview below that the asterisk characters in the data have turned into vertical lines, and the column separation has been completed.

#4. Click [Next], the column data format is normal, just click [Finish].

A prompt appears: Data already exists here. Replace it?

Since the content of column G before column separation contains length, width and height data, after column separation, column G is replaced with "long".

Click [OK] directly to see the column results.

#5. Easily calculate the package volume based on the length, width and height.

Classmate Zhou feels that this is not the best solution, because the number of table columns is fixed, and the data is already related to other tables, and the data is inserted after the data is divided into columns. With 2 new columns, wouldn’t the data be messed up?

2. Extract numbers to calculate volume

Let’s try to use text functions to solve it. (High energy ahead, you only need to understand it here, mainly to highlight the simplicity of the third method)

Since we want to calculate the volume of the package, then we only You need to extract the length, width and height data in column G separately and then multiply them together.

Extract length data:

Function formula: =LEFT(G2,FIND("*",G2,1)-1)

Extract width data:

Function formula:

=MID(G2,FIND(" *",G2,1) 1,FIND("-",SUBSTITUTE(G2,"*","-",2))-1-FIND("*",G2,1))

Extract height data:

Function formula:

=RIGHT(G2,LEN(G2)-FIND("-",SUBSTITUTE(G2,"*","-",2),1))

Finally, we merge the three function formulas into nested statistics to obtain the volume of the package.

Okay, I know that the function formula above is too complicated and no one wants to learn it, so I haven’t done too much function analysis for you. It’s simple and crude. Here’s what I’ll give you We solemnly recommend the simplest method: macro table function.

3. EVALUATE function calculates volume

How to use the EVALUATE function. First, let’s understand the meaning of EVALUATE. In fact, EVALUATE is a macro table function. Macro table functions are also called Excel version 4.0 functions. They need to be defined by a name (and enable macros) or used in the macro table. Most of these functions have been gradually replaced by built-in functions and VBA functions, but you can’t learn it in one minute. VBA, you can learn macro table functions. The most common application of function EVALUATE is the automatic calculation of Excel formulas.

Let’s start the operation demonstration:

1. Select column G and click [Name Manager] in the [Formula] option.

The following dialog box pops up:

2. Click [New], and select [New Name] Enter the name TJ in the dialog box, and enter the function formula

=EVALUATE(Sheet1!$G$2:$G$44)/1000/1000(Note: Due to the previous The unit is centimeters. I want to convert the statistical results into cubic meters, so I need to divide 1000000) and click [OK]. Finally close the name manager.

Formula analysis:

Since the data in column G is length*width*height, * means multiplication in excel. The data in column G itself can be regarded as a formula, and we only need to get the result of this formula. The function of EVALUATE is to get the value of the formula in the cell, so in the picture above, you will find that the parameter in the EVALUATE function has only one data area.

3. The time to witness the miracle has arrived. Enter the two letters TJ in cell H2 to quickly get the volume information!

Isn’t this a simple and fast method that does not require auxiliary columns great! Simply the best of 3! Classmate Zhou’s problem finally has a perfect solution.

Related learning recommendations: excel tutorial

The above is the detailed content of Application of the automatic calculation tool evaluate() in Excel function learning. For more information, please follow other related articles on the PHP Chinese website!