Comparative analysis: concept, types and examples. Calculation of KPI in Excel examples and formulas Carried out by several criteria at once

25.01.2023

Topic: Making decisions on several criteria indicators.

In practice, it is usually necessary to choose a management decision not according to one criterion, but according to several. Therefore, their values in a comparative assessment are multidirectional, i.e. on one indicator, the alternative wins, and on others it loses.

Under these conditions, it is necessary to reduce the considered system of evaluation of indicators to one complex one, on the basis of which a decision will be made.

To build a comprehensive assessment, two problems need to be solved:

The first problem is that the criteria indicators under consideration are of unequal significance;

The second problem is characterized by the fact that the indicators are evaluated in different units of measurement, and in order to build a comprehensive assessment, it is necessary to switch to a single meter.

The first problem is solved by applying one of the four modifications of the method of expert assessments, namely the method of pairwise comparison, which allows us to quantify the significance. The essence of the pairwise comparison method is that an expert (specialist, potential investor, consumer) conducts a pairwise assessment of the criteria indicators under consideration, determining for himself their degree of importance in the form of a score. After that, having carried out the appropriate processing of the information received, the coefficient of significance is calculated for each of the criteria indicators under consideration.

The second problem is solved by using a single meter for private indicators. Most often, a scoring is used as such a meter. In this case, the assessment is carried out in two approaches:

- first approach used in the absence of statistical data on the value of the indicators under consideration;

- second approach used in the presence of statistical data (limits of change) on the value of the indicators under consideration.

When using the first approach to convert to points, the following is done: the best value of the indicator under consideration is taken equal to 1 point, and the worst values are in fractions of this point. This approach is simple, gives an objective assessment, but at the same time does not take into account the best achievements that lie outside the options considered.

To eliminate this shortcoming, information is needed on the limits of change of the indicator under consideration. If available, the second approach is used. In this case, a conversion scale is built to convert to points. In this case, the scoring system is selected using the provisions of the theory of statistics according to the Sturges formula:

n = 1 + 3,322 lg N , Where

N is the number of statistical observations;

n is the accepted scoring system obtained using the rounding rules.

The conversion into points is carried out on the basis of the constructed conversion scale using the tabular data interpolation procedure.

Exercise:

Of the 6 options for alternative solutions, each of which is evaluated by 5 criteria indicators, it is necessary to choose the best option.

Evaluate using 2 approaches:

1) in the absence of statistical data on the value of the indicators under consideration;

2) if available.

Limits of change of indicators are established for the following number of observations (N):

For even variants N = 8;

The assessment of significance should be performed on the basis of a paired assessment according to the performer.

Table 1.

Task options

job number	1	2	3	4	5
No. of alternatives	1,2,3,4,5,6	2,4,8,9,11,15	1,3,5,7,9,10	4,6,8,12,13,14	1,5,10,11,12,15
job number	6	7	8	9	10
No. of alternatives	6,7,10,11,14,15	3,4,5,8,9,10	7,8,9,10,13,15	1,2,3,13,14,15	2,4,5,7,12,13
job number	11	12	13	14	15
No. of alternatives	1,7,8,9,10,11	6,9,12,13,14,15	2,5,7,9,10,11	7,8,9,10,11,12	1,2,3,4,8,9
job number	16	17	18	19	20
No. of alternatives	1,2,3,10,12,13	2,5,7,8,10,15	1,6,7,12,13,14	3,4,5,6,10,14	2,8,11,12,14,15
job number	21	22	23	24	25
No. of alternatives	1,2,6,7,9,10	3,5,8,9,13,14	4,7,8,10,11,12	5,6,7,8,11,13	8,9,10,11,12,13
job number	26	27	28	29	30
No. of alternatives	1,3,4,10,11,15	2,3,5,8,9,15	1,4,7,11,13,15	2,6,7,8,12,14	1,10,11,12,8,4

Table 2.

Initial data:

№№	Alternative Solutions
indicators	A1	A2	A3	A4	A5	A6	A7	A8	A9	A10	A11	A12	A13	A14	A15
X 1	5	10	15	6	11	16	7	14	18	20	19	8	21	13	10
X 2	10	9	8	8	5	7	4	9	5	8	7	7	6	3	2
X 3	4	3	5	10	6	5	11	7	7	9	8	12	8	5	9
X 4	1	2	3	4	4	3	2	1	1	3	4	2	2	4	3
X 5	10	14	13	11	12	20	21	23	17	18	19	24	22	16	18

Table 3

Example:

Four variants of alternative solutions are given, each of which is evaluated by 5 criteria indicators. Based on the conditions of the task, it is necessary to choose the best option.

At the 1st stage, it is necessary to quantify the significance of each indicator. The paired comparison method is used, which is based on expert assessments.

On the basis of this assessment, a table is compiled - a matrix, and the significance coefficient -Kзi is calculated.

The quantitative assessment of the significance of indicators is determined as follows: if, in a pair assessment, an expert (specialist, potential investor, consumer) gave preference to one of the factors, then the number of the factor that is preferred is put in the row and column of the quantitative assessment matrix (see Table 4). ). After that, for each line, the number of preferences given to one or another factor in their pairwise evaluation and their sum (Σпi) is determined. Then the significance coefficient is calculated according to the following formula:

Quantification of the significance of indicators:

Table 4

X1	x2	X3	X4	X5	ΣPi		Kzi
X1	1	1	3	1	5	3		0,2
x2	1	2	2	2	5	3		0,2
X3	3	2	3	4	5	2		0,133
X4	1	2	4	4	5	2		0,133
X5	5	5	5	5	5	5		0,333
∑∑Pi					15	1

First approach.

The first approach of converting to points is characterized by the fact that the best value of the indicator is taken equal to 1 point, the worst is estimated in fractions of this point. This approach is simple, gives an objective comparative assessment, but takes into account the best achievements that are not included in the comparative options.

Index code	Score in points				Kzi	Score in points taking into account Ksi
Index code	A1	A2	A3	A4	Kzi	A1	A2	A3	A4
X1	0,3	0,35	0,7	1	0,2	0,06	0,07	0,14	0,2
x2	0,89	0,45	1	0,89	0,2	0,178	0,09	0,2	0,178
X3	0,91	1	0,64	0,82	0,133	0,121	0,133	0,085	0,110
X4	0,25	0,5	1	0,33	0,133	0,033	0,066	0,133	0,043
X5	1	0,52	0,48	0,61	0,333	0,333	0,173	0,159	0,203
Comprehensive assessment					0,725	0,532	0,717	0,73 4

For example: Х1А1: 6/20=0.3

Х2А1: 8/9=0.89

Conclusion: using the first approach, the best option from the alternatives will be option A4, since it has the highest comprehensive assessment. Next come options A1, A3, A2.

Second approach.

Eliminates the shortcomings of the first approach, but its use requires information on the limits of change of the indicator under consideration. At the same time, a conversion scale is built for conversion into points. The scoring system is chosen on the basis of the provisions of the theory of statistics and depends on the number of observations underlying the formation of the limits of change in indicators.

Suppose, in our example, 8 observations were made (N=8), which made it possible to establish the following limits for the change in qualitative indicators (see Table 3).

In the presence of these indicators, a scale of conversion into points is built.

- Sturges formula,

where N is the number of observations.

Consequently, the assessment of the quality indicator will be made according to a 4-point system, i.e. n = 4.

- range of variation,

where - the maximum and minimum values from the limits of change of i-indicator.

The step of changing the indicator.

The scale of conversion into points is a table in which for each point the limits of change of indicators are indicated. When translating the values of indicators into points on this scale, if the value of the indicator lies within the interval, then the procedure for interpolating tabular data is used.

Scale of conversion to points

Next, the quality indicators of all products are evaluated in points. For example, according to alternative A1: the numerical value of the indicator is taken from the initial data, then using the conversion scale to points, the interval where this value falls is determined. After that, a scoring is given: from the numerical value of the indicator, the lower limit of the change in the indicator in this interval is subtracted by a step and the previous interval is added. According to indicators X4, X5 - from the upper limit of the indicator change in this interval, the numerical value of the indicator is divided by a step and the previous interval is added.

The values obtained are summarized in the following table.

indicator	Score in points				Kzi	Score in points taking into account Ksi
indicator	A1	A2	A3	A4	Kzi	A1	A2	A3	A4
X1	0,2	0,4	1,8	3	0,2	0,04	0,08	0,36	0,6
x2	3	1	3,5	3	0,2	0,6	0,2	0,7	0,6
X3	2,33	2,66	1,33	2	0,134	0,313	0,357	0,179	0,268
X4	0	2,34	4	1,67	0,134	0	0,314	0,536	0,224
X5	3,04	1,44	1,12	1,92	0,334	1,02	0,481	0,374	0,642
Comprehensive assessment					1,973	1,432	2,149	2,334

Conclusion: using the second approach, the best option from the alternatives will be option A4, since it has the highest comprehensive assessment. Next come options A3, A2, A1.

This is a chapter from a book: Michael Girvin. Ctrl+Shift+Enter. Mastering array formulas in Excel.

Selections based on one or more conditions. A number of Excel functions use comparison operators. For example, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, and AVERAGEIFS. These functions make selections based on one or more conditions (criteria). The problem is that these functions can only add, count, and average. And if you want to impose conditions on the search, for example, the maximum value or standard deviation? In these cases, since there is no built-in function, you must invent an array formula. Often this is due to the use of the array comparison operator. The first example in this chapter shows how to calculate the minimum value under one condition.

Let's use the IF function to select the elements of an array that meet a condition. On fig. 4.1 in the left table there is a column with the names of cities and a column with time. It is required to find the minimum time for each city and place this value in the corresponding cell of the right table. The selection condition is the name of the city. If you use the MIN function, you can find the minimum value of column B. But how do you select only those numbers that apply only to Auckland? And how do you copy the formulas down the column? Since Excel does not have a built-in MINESLI function, you need to write an original formula that combines the IF and MIN functions.

Rice. 4.1. Purpose of the formula: to select the minimum time for each city

Download note in format or in format

As shown in fig. 4.2, you should start entering the formula in cell E3 with the MIN function. But you can't put in an argument number1 all values of column B!? You want to select only those values that are related to Auckland.

As shown in fig. 4.3, in the next step, enter the IF function as an argument number1 for MIN. You put an IF inside a MIN.

By positioning the cursor at the point where the argument is entered log_expression function IF (Fig. 4.4), you select the range with the names of cities A3:A8, and then press F4 to make cell references absolute (see, for example, for more details). Then you type the comparative operator, the equals sign. Finally, you'll select the cell to the left of the formula, D3, leaving the reference to it relative. The formulated condition will allow you to select only Aucklands when viewing the range A3:A8.

Rice. 4.4. Create an array operator in an argument log_expression IF functions

So you've created an array operator with a comparison operator. At any time during array processing, the array operator is a comparison operator, so its result will be an array of TRUE and FALSE values. To verify this, select the array (to do this, click on the argument in the tooltip log_expression) and press F9 (Fig. 4.5). Usually you use one argument log_expression, returning either TRUE or FALSE; here, the resulting array will return multiple TRUE and FALSE values, so the MIN function will select the minimum number only for those cities that match the TRUE value.

Rice. 4.5. To see an array of TRUE and FALSE values, click the argument in the tooltip log_expression and press F9

In the second part of our tutorial on the function VLOOKUP(VLOOKUP) in Excel, we will analyze a few examples that will help you direct all the power VLOOKUP to solve the most ambitious Excel tasks. The examples assume that you already have a basic knowledge of how this feature works. If not, you might be interested in starting with the first part of this tutorial, which explains the syntax and basic usage. VLOOKUP. Well, let's get started.

Search in Excel by multiple criteria

Function VLOOKUP in Excel is a really powerful tool for performing searches for a particular value in a database. However, there is a significant limitation - its syntax allows you to search for only one value. What if you want to search by multiple conditions? You will find the solution below.

Example 1: Search by 2 different criteria

Suppose we have a list of orders and we want to find Quantity of goods(Qty.), based on two criteria - Client name(Customer) and The product's name(Product). The matter is complicated by the fact that each of the buyers ordered several types of goods, as can be seen from the table below:

regular function VLOOKUP will not work in this scenario because it will return the first value it finds that matches the given lookup value. For example, if you want to know the quantity of an item Sweets ordered by the buyer Jeremy Hill, write the following formula:

VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

- this formula will return the result 15 corresponding to the product apples, because it's the first value that matches.

There is a simple workaround - create an additional column in which to combine all the desired criteria. In our example, these are the columns Client name(Customer) and The product's name(Product). Don't forget that the merged column must always be the leftmost column in the search range, since it is the left column that the function VLOOKUP looks up when looking for a value.

So, you add an auxiliary column to the table and copy the following formula over all its cells: =B2&C2. If you want the string to be more readable, you can separate the combined values with a space: =B2&” “&C2. After that, you can use the following formula:

VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

Where is the cell B1 contains the concatenated value of the argument lookup_value(lookup_value) and 4 - argument col_index_num(column_number), i.e. the number of the column containing the data to be retrieved.

Example 2: VLOOKUP by two criteria with table being viewed on another sheet

If you need to update the main table (Main table) by adding data from the second table (Lookup table), which is located on another sheet or in another Excel workbook, then you can collect the desired value directly in the formula that you insert into the main table.

As in the previous example, you will need an auxiliary column in the Lookup table with the combined values. This column must be the leftmost column in the search range.

So the formula with VLOOKUP could be like this:

VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=VLOOKUP(B2&" "Orders!$A&$2:$D$2;4;FALSE)

Here, columns B and C contain customer names and product names, respectively, and the link Orders!$A&$2:$D$2 defines a table to look up in another sheet.

To make the formula more readable, you can give the view range a name, and then the formula will look much simpler:

VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=VLOOKUP(B2&" "Orders;4;FALSE)

For the formula to work, the values in the leftmost column of the table being viewed must be combined in the same way as in the search criteria. In the figure above, we combined the values \u200b\u200band put a space between them, just like you need to do in the first argument of the function (B2&” “&C2).

Remember! Function VLOOKUP limited to 255 characters, it cannot search for a value that is more than 255 characters long. Keep this in mind and make sure that the length of the desired value does not exceed this limit.

I agree that adding an auxiliary column is not the most elegant and not always acceptable solution. You can do the same without the helper column, but that would require a much more complex formula with a combination of functions INDEX(INDEX) and MATCH(MATCH).

We extract the 2nd, 3rd, etc. values using VLOOKUP

You already know that VLOOKUP can return only one matching value, more precisely, the first one found. But what if this value is repeated several times in the viewed array, and you want to extract the 2nd or 3rd of them? What if all values? The problem seems intricate, but the solution exists!

Suppose one column of the table contains the names of the customers (Customer Name), and the other column contains the products (Product) that they bought. Let's try to find the 2nd, 3rd and 4th items purchased by a given customer.

The easiest way is to add an auxiliary column before the column customer name and fill it with customer names with the repetition number of each name, for example, John Doe1, John Doe2 etc. We will do the trick with numbering using the function COUNTIF(COUNTIF), given that the customer names are in column B:

B2&COUNTIF($B$2:B2,B2)
=B2&COUNTIF($B$2:B2,B2)

After that you can use the normal function VLOOKUP to find the required order. For example:

We find 2nd Dan Brown:
VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)
We find 3rd item ordered by the customer Dan Brown:
VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

If you are only looking for 2nd repetition, you can do it without the auxiliary column by creating a more complex formula:

IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4,0)+2)&":$C16"),2,FALSE),"")
=IFERROR(VLOOKUP($F$2;INDIRECT("$B$"&(MATCH($F$2;Table4;0)+2)&":$C16");2;TRUE);"")

In this formula:

$F$2- a cell containing the name of the buyer (it is unchanged, please note - the link is absolute);
$B$- column customer name;
Table4- Your table (this place can also be a regular range);
$C16- the end cell of your table or range.

This formula finds only the second matching value. If you need to extract the remaining repetitions, use the previous solution.

If you need a list of all matches - the function VLOOKUP this is not a helper, since it only returns one value at a time - period. But Excel has a function INDEX(INDEX), which can easily cope with this task. How such a formula will look like, you will learn in the following example.

Retrieve all repetitions of the desired value

As mentioned above, VLOOKUP cannot extract all duplicate values from the scanned range. To do this, you need a slightly more complex formula, made up of several Excel functions, such as INDEX(INDEX), SMALL(SMALL) and ROW(LINE)

For example, the formula below finds all repetitions of the value from cell F2 in the range B2:B16 and returns the result from the same rows in column C.

(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"") )
(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,"");ROW()-3)),"") )

Enter this array formula into multiple adjacent cells, such as the cells F4:F8 as shown in the figure below. The number of cells must be equal to or greater than the maximum possible number of repetitions of the searched value. Don't forget to click Ctrl+Shift+Enter to enter the array formula correctly.

If you're interested in understanding how it works, let's dive into the details of the formula a bit:

Part 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")
IF($F$2=B2:B16;ROW(C2:C16)-1;"")

$F$2=B2:B16– compare the value in cell F2 with each of the values in the range B2:B16. If a match is found, then the expression STRING(C2:C16)-1 returns the number of the corresponding line (value -1 allows you to not include the header line). If there are no matches, the function IF(IF) returns an empty string.

Function result IF(IF) there will be such a horizontal array: (1,"",3,"",5,"","","","","","",12,"","","" )

Part 2:

ROW()-3
STRING()-3

Here the function ROW(LINE) acts as an additional counter. Since the formula is copied into cells F4:F9, we subtract the number 3 from function result to get value 1 in a cell F4(line 4, subtract 3) to get 2 in a cell F5(line 5, subtract 3) and so on.

Part 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,"");ROW()-3))

Function SMALL(SMALL) returns nth the smallest value in the data array. In our case, which position (from the smallest) to return is determined by the function ROW(LINE) (see Part 2). So, for a cell F4 function SMALL((array),1) returns 1st(smallest) array element, i.e. 1 . For cell F5 returns 2nd the smallest element in the array, that is 3 , and so on.

Part 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
INDEX($C$2:$C$16;SMALL(IF($F$2=B2:B16;ROW(C2:C16)-1;"");ROW()-3))

Function INDEX(INDEX) simply returns the value of a specific cell in an array C2:C16. For cell F4 function INDEX($C$2:$C$16,1) will return apples, For F5 function INDEX($C$2:$C$16;3) will return Sweets and so on.

Part 5:

IFERROR()
IFERROR()

Finally, we put the formula inside the function IFERROR(IFERROR), because you are unlikely to be pleased with the error message #N/A(#N/A) if the number of cells into which the formula is copied is less than the number of duplicate values in the range being viewed.

2D search by known row and column

Performing a 2D search in Excel involves searching for a value by a known row and column number. In other words, you are extracting the cell value at the intersection of a particular row and column.

So, let's turn to our table and write a formula with a function VLOOKUP, which will find information about the cost of lemons sold in March.

There are several ways to perform a 2D search. Check out the options and choose the one that suits you best.

VLOOKUP and MATCH functions

You can use a bunch of functions VLOOKUP(VLOOKUP) and MATCH(MATCH) to find the value at the intersection of the fields The product's name(string) and Month(column) of the array in question:

VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

The formula above is a regular function VLOOKUP, which looks for an exact match of the value "Lemons" in cells A2 through A9. But since you don't know which column the March sales are in, you won't be able to set the column number for the third function argument. VLOOKUP. Instead, the function is used MATCH to define this column.

MATCH("Mar",$A$1:$I$1,0)
MATCH("Mar";$A$1:$I$1;0)

Translated into human language, this formula means:

Looking for characters "Mar" - argument lookup_value(lookup_value);
Looking in cells from A1 to I1 - argument lookup_array(lookup_array);
Returning exact match - argument match_type(match_type).

Using 0 in the third argument, you say functions MATCH look for the first value that exactly matches the value you are looking for. This is equivalent to the value FALSE(FALSE) for the fourth argument VLOOKUP.

This is how you can create a two-way search formula in Excel, also known as two-dimensional search or bidirectional search.

SUMPRODUCT function

Function SUMPRODUCT(SUMPRODUCT) returns the sum of the products of the selected arrays:

SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX and MATCH functions

In the next article I will explain these functions in detail, so for now you can just copy this formula:

INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9.0),MATCH("Mar",$A$1:$I$1.0))
=INDEX($A$2:$I$9;MATCH("Lemons";$A$2:$A$9;0);MATCH("Mar";$A$1:$I$1;0))

Named ranges and the intersection operator

If you're not into all those complex Excel formulas, you might like this visual and memorable way:

When you enter a name, Microsoft Excel will show a tooltip with a list of matching names, just like when you enter a formula.

Click Enter and check the result

In general, whichever of the above methods you choose, the result of a two-dimensional search will be the same:

Using multiple VLOOKUPs in one formula

It happens that the main table and the lookup table do not have a single column in common, and this prevents you from using the usual function VLOOKUP. However, there is another table that does not contain the information we are interested in, but has a common column with the main table and the lookup table.

Let's take a look at the following example. We have a Main table with a column SKU (new), where you want to add a column with the corresponding prices from another table. In addition, we have 2 lookup tables. The first one (Lookup table 1) contains updated numbers SKU (new) and product names, and the second (Lookup table 2) - product names and old numbers SKU (old).

To add prices from the second lookup table to the main table, you must perform an action known as double VLOOKUP or nested VLOOKUP.

Write a function VLOOKUP, which finds the product name in the table lookup table 1 using SKU, as the desired value:
VLOOKUP(A2,New_SKU,2,FALSE)
=VLOOKUP(A2;New_SKU;2;FALSE)
Here New_SKU- named range $A:$B in the table lookup table 1, A 2 - this is column B, which contains the names of the goods (see the picture above)
Write a formula to insert prices from a table lookup table 2 based on well-known product names. To do this, paste the formula you created earlier as the lookup value for the new function VLOOKUP:
VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

Here price- named range $A:$C in the table lookup table 2, A 3 is column C containing prices.

The figure below shows the result returned by the formula we created:

Dynamic substitution of data from different tables using VLOOKUP and INDIRECT

First, let's clarify what we mean by "Dynamic substitution of data from different tables" to make sure we understand each other correctly.

There are situations when there are several sheets with data of the same format, and it is necessary to extract the necessary information from a certain sheet, depending on the value that is entered in a given cell. I think it's easier to explain this with an example.

Imagine that you have sales reports for several regions with the same products and in the same format. You want to find sales figures for a specific region:

If you have only two such reports, then you can use a disgracefully simple formula with functions VLOOKUP And IF(IF) to select the desired report to search:

VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

$D$2 is a cell containing the name of the product. Note that we use absolute references here to avoid changing the lookup value when copying the formula to other cells.
$D3 is a cell with the name of the region. We are using an absolute column reference and a relative row reference because we plan to copy the formula to other cells in the same column.
FL_Sales And CA_Sales– the names of the tables (or named ranges) that contain the corresponding sales reports. You can, of course, use the usual sheet names and cell range references, for example 'FL Sheet'!$A$3:$B$10, but named ranges are much more convenient.

However, when there are many such tables, the function IF is not the best solution. Instead, you can use the function INDIRECT(INDIRECT) to return the desired search range.

As you probably know, the function INDIRECT is used to return a link given by a text string, which is exactly what we need now. So, boldly replace in the above formula the expression with the function IF to link with function INDIRECT. Here is a combination VLOOKUP And INDIRECT works great with:

VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=VLOOKUP($D$2;INDIRECT($D3&"_Sales");2;FALSE)

$D$2- this is a cell with the name of the product, it is unchanged due to the absolute link.
$D3 is the cell containing the first part of the region name. In our example, this FL.
_Sales- the common part of the name of all named ranges or tables. When combined with the value in cell D3, it forms the fully qualified name of the required range. Below are some details for those who are new to the function INDIRECT.

How INDIRECT and VLOOKUP work

First, let me remind you the syntax of the function INDIRECT(INDIRECT):

A1 if the argument is TRUE(TRUE) or not specified;
R1C1, If FALSE(LIE).

So let's get back to our sales reports. If you remember, then each report is a separate table located on a separate sheet. For the formula to work correctly, you must name your tables (or ranges), and all names must have a common part. For example, like this: CA_Sales, FL_Sales, TX_Sales and so on. As you can see, all the names contain “_Sales”.

Function INDIRECT connects the value in column D and the text string "_Sales", thereby telling VLOOKUP in which table to search. If cell D3 contains the value “FL”, the formula will search the table FL_Sales, if “CA” is in the table CA_Sales and so on.

The result of the functions VLOOKUP And INDIRECT will be the following:

If the data is located in different Excel books, then you need to add the name of the book before the named range, for example:

VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=VLOOKUP($D$2;INDIRECT($D3&"Workbook1!_Sales");2;FALSE)

If the function INDIRECT refers to another workbook, that workbook must be open. If it is closed, the function will report an error. #ref!(#REF!).

Let's say you want to sum values with more than one condition, such as the sum of a product's sales in a particular region. This is a good use case for the SUMIFS function in a formula.

Pay attention to this example where we have two conditions: the amount of sales is required meat(from column C) to South area (from column a).

Here is a formula that can be used for acomplish:

=SUMIFS(D2:D11; a2: A11; "South"; C2: C11; "meat")

The result is the value 14,719.

Consider a more detailed representation of each part of the formula.

SUMIFS is an arithmetic formula. Calculates the numbers, which in this case are in column D. The first step is to specify the location of the numbers.

=SUMIFS(D2:D11,

In other words, you want the formula to sum the numbers in that column if they meet certain conditions. This cell range is the first argument in this formula, the first data element that the function takes as input.

Then you need to find the data that meets the two conditions and enter the first condition, specifying the location of the data (a2:A11) for the function, as well as what the condition is "South". Notice the commas between the individual arguments.

=SUMIFS(D2:D11,A2:A11,"South";

The quotation marks around the word "South" indicate that this is text data.

Finally, you enter arguments for the second condition - the range of cells (C2:C11) that contain the word "Meat" and the word itself (enclosed in quotation marks) so that Excel can match them. End the formula by closing the closing parenthesis ) , and then press Enter. The result is once again at 14,719.

=SUMIFS(D2:D11,A2:A11,"South",C2:C11,"Meat")

As you type the SUMIFS function in Excel, if you don't remember these arguments, Help is ready. After entering the formula = SUMIFS ( Formula autocomplete will appear below the formula, and the list of arguments will be listed in the correct order.

Take a look at the image of autocomplete formulas and list of arguments in our example sum_range is D2:D11, the column of numbers to be summed. criteria_range1- a2. A11 - data column containing condition1"South".

As you type the formula, the rest of the arguments (not shown here) will appear in formula completion; condition_range2 is the range C2:C11, which is the data column in which the condition2- "Meat".

If click SUMIFS formula completion will open an article where you can get more information.

Try to practice

If you want to experiment with the SUMIFS function, here are some sample data and a formula that uses the function.

In this Excel for the web workbook, you can work with sample data and formulas right here. Change the values and formulas or add your own to see how the results change instantly.

Copy all the cells from the table below and paste them into cell A1 of a new Excel sheet. You can adjust the column widths to make the formulas display better.

Region	Salesman

West
Oriental	Pesotsky
Northern	Pesotsky	Dairy
	Marinova


Oriental	Pesotsky
Northern

In this article, you will learn about 5 useful properties of pivot tables that will help you quickly and in detail analyze the situation (using the example of customer base analysis). You will learn:

1. How to group data;
2. What indicators can be calculated when summarizing data;
3. How to simultaneously calculate several indicators for one parameter when summarizing data;
4. What additional calculation options can you use when summarizing data?
5. About the possibility of sorting.

And based on this analysis, we will touch on the most powerful technique for planning sales promotions in the FMCG markets.

Let's start with a pivot table. Let's take a simple table of sales to customers by day.

Place the cursor in the upper left corner of our table, then go to the "Insert" menu and click on the "Pivot Table" button:

In the Create PivotTable dialog box, click OK:

We got a pivot table on a new sheet:

1st useful feature of a pivot table for business analysis - data grouping

So, we have shipments to customers by day, we want to understand in what range of shipments we have the maximum sales. To do this, we need to group shipments into ranges.

Drag the "Shipping amount" field to the "Line names" area of the pivot table (hold down the "Shipping_amount" field with the left mouse button and drag it to the "Line names" section of the pivot table):

We have displayed all shipments in the left column of the summary. Now we set the cursor to our shipments (as in the figure):

Go to the Excel menu "Data" and click on the "Group" button

In the dialog box that appears, set the grouping step to "5000" (you can enter any) and click "OK"

We get grouped sales volumes with a given step:

In order to the group looked nice and perceived, press again "Group" button and manually set equal values, for the value "starting from" - "-15,000" (below the minimum value, a multiple of 5000) "to" - "45,000" (greater than the maximum group, a multiple of 5000).

We get grouped data by shipment amount:

2nd useful feature of pivot tables for business analysis -
the ability to calculate various summary parameters by fields from the source table

So, shipments have been grouped, now let's see what sales volumes fall on each range of shipments. To do this, let's sum the shipments in the summary.

Left-click the "Shipping_amount" field and drag it to the summary "Values" field:

The summary by default calculated "Quantity by field Shipment_amount", i.e. the number of records in our original table on the "Data" sheet. Because Since our table contains information on sales to customers by day, our indicator "Quantity in the field Shipment_amount" is the number of shipments to customers.

As a result, in the pivot table we see the number of shipments to customers in different shipment ranges:

How can we get the amount of shipments from the number of shipments?

We left-click on the field "Quantity by field Shipment amount" in the area of the pivot table "Values", and in the menu that opens, select "Parameters of value fields ..."

In the window that opens, select the data reduction operation we are interested in (Sum, quantity, average, maximum, minimum ...). Select the operation we need "sum" and click "OK".

We get the total sales volume for each shipment range:

Those. we see how much sales fall on shipments in the range from 0 to 5,000 rubles, from 5,000 to 10,000 rubles. etc. And it is clear that the maximum volume of shipments falls on the range from zero to 5000 rubles.

3 property - the ability for one field to calculate various data reduction operations

Now we would like to see how many shipments and what average shipments we have in each of the ranges. To do this, we use the pivot table to calculate the number of shipments and average shipments.

In the area of \u200b\u200bthe pivot table "Values" we drag the field "Shipping_amount" 2 more times and in the parameters of the value field for the second select "quantity" and for the third field select "average".

We get for each range of shipments the sales volume, the number of shipments and the average shipment:

Now we can see in which range of shipments the maximum sales volume and the maximum number of shipments. In our example, this is for the range from 0 to 5000 rubles. and the volume of sales and the number of shipments as much as possible.

4th property of pivot tables - the ability to carry out additional calculations

For clarity of data analysis, let's add 2 more parameters - "Share by sales volume for each group" and "Share of the number of shipments for each group".

To do this, in the field of the pivot table "Values" drag the field "Shipping amount" 2 more times

Moreover, for one parameter in the menu "Parameters of the field of values" () we will select the operation "sum", and for the second operation "quantity".

We get the following table:

Now once again we go to the "Parameters of the value fields" and enter the tab "Additional calculations":

Select in the field "Additional calculations" the item "Share of the total amount"

We get a table in which for each range of shipments to customers we see the volume of sales, the number of shipments, the average shipment, the share of sales for each group and the share of the number of shipments for each group:

5 useful property - sorting

Now, for clarity, from the maximum to the minimum group by sales volume, we will sort. To do this, place the cursor in the field with the volume of sales by groups and click on the "sort from maximum to minimum" button:

It can be seen that the maximum group in terms of sales volume and the number of shipments is the group "from 0 to 5000 rubles." average sales in this group are 1971 rubles.

Note! The average shipment across all customers is significantly different from 86% of shipments. Moreover, it differs significantly

for all groups, the average shipment is 2,803 rubles. (in line total).
And for 86% of shipments, 1,971 rubles.

This is a serious difference, and if we stimulate sales based on 86% of shipments and the average for them - 1,971 rubles, then our actions will be more accurate, and the effect is much higher, because. We will be able to interest the maximum number of customers.

This example shows a powerful technique for mass market promotion planning and sales forecasting that can help you make a big impact and make a difference.

If you have any questions, please contact.

Accurate predictions for you!

Join us!

Download Free Forecasting and Business Intelligence Apps:

Novo Forecast Lite- automatic forecast calculation V excel.
4analytics- ABC-XYZ analysis and analysis of emissions in Excel.
Qlik Sense Desktop and Qlik ViewPersonal Edition - BI systems for data analysis and visualization.

Test the features of paid solutions: