Collocation of data validity and conditional summation in WPS

As shown in Figure 1 and Figure 2, the menu sheet is the name and unit price of a regular dish, the "detail" worksheet is the name and quantity of the dish you buy daily, four vegetables per day, the name and number of dishes, and the G column is the result of the calculation.

Figure 1

Figure 2

The normal way is to daily vegetables single-name into the cell, and then set the formula for each cell (that is, per vegetable) multiplied by the corresponding unit price in the menu sheet, and then summarized. The formula is as follows:

=C2* Menu! B3+D2* Menu! B4+E2* Menu! B6+F2* Menu! B10

The above operating methods have three disadvantages:

Hand-input all dishes single-name

Manually find the price of the menu name

Each exercise with a different formula, that is, a daily need to re-enter the formula

Is there any way to solve these repetitive tasks? That is, you don't have to input the menu every day, and you don't have to enter the formula every day to complete all the requirements. Yes, the first problem can be solved by using data validation, and the array formula solves two other problems.

Data availability and array formulas are widely used and flexible to use. Data validation simplifies the input of some cells that have fixed input items through a drop-down selection. The array formula can simplify the lengthy formula to the most refined, and can complete a lot of ordinary formulas can not complete the worksheet, it and the definition of names and data validation tools, such as the use of, more powerful function.

The following starts with the combination of data validation and array formulas to show the method of making accounts.

First step: Define names and set data validation

1. Activate "menu" worksheet;

2. Click insert/name/definition to open the Define Name dialog box;

3. Enter "Menu" in the Name box, enter the "= menu in the reference location" box, $A $ $A $, and then click Add.

Note: The reference to the A1:A10 area here needs to be used as an absolute reference.

Step two: Set data validation

1. Activate the "detail" worksheet, select B1:e1 area;

2. Click "Data"/"effectivity" on the menu to open the Data Validation dialog box;

3. On the Settings tab, in the Allow list, select Series, type = menu in the source text box, and then click OK.

Note: The equal sign must be in Half-width state input.

After you return to the worksheet, you can find that each cell that you want to enter data has produced a Drop-down menu from which to select the menu name

When you make a schedule every day, you can just copy the first line to produce the same Drop-down menu. Of course, you can also design the first day of the table-style when the next area of a one-time copy, so that all odd rows have a drop-down list to choose from.

Step three: Function nesting and array formulas

1. To F1 cell input the following array formula

=if (MOD (ROW (), 2), "Vegetable Price", SUM (IF (OFFSET (c1,-1,,, 4) = menu! A$1:A$10,C1:F1) * Menu! B$1:B$10))

Note: This is an array formula, so you can not hit enter directly, must be entered in the form and press Shift+ctrl+enter end.

2. Move the cursor to the lower-right corner of the F1 cell, and when the cross cursor appears, drag down, fill to complete the operation of the number of days data.

Note: As can be seen from fig. 3, the flower extension "{}" is automatically generated at the end of the formula, which is the characteristic of the array formula.

Figure 3

Formula Explanation: The MoD function is used to return the remainder of the division of two numbers, which is used to return the line number of the current row. In this example, the MoD fits the row function to determine the parity of the row in which the formula is located. For odd rows, the formula returns the result "menu", while even rows return the total purchase price for the day.

If the third parameter is used to calculate the daily menu, it first uses the offset function to refer to the name of the day, then compares it with the name of the menu sheet, and then references the unit price of the names to the number, multiplied by the sum function.

3. This example formula uses the array to solve the odd behavior "vegetable price", the even line calculates the question of the vegetable price, and realizes the automatic lookup corresponding unit price. However, you can use the lookup function to simplify the equation. The formula is as follows:

=if (Istext (C1), "Vegetable Price", SUM (OFFSET (c1,-1,,, 4), Menu! A$1:B$10) *c1:f1))

Note: Based on the lookup feature, the data for the menu worksheet needs to be sorted in ascending order of column A.