As a rule, the effectiveness of large-scale, complex operations cannot be characterized using one indicator W, it has to be assisted by other, additional W 1 , W 2 ,…, W; some of them it is desirable to do more, others - less. For example, when evaluating the performance of an enterprise, it is necessary to take into account a number of indicators:

• full range of products

  cost price, etc.

When analyzing a combat operation, in addition to the main indicator, the mathematical expectation of damage caused to the enemy, a number of additional ones have to be taken into account:

own losses

operation time,

ammunition consumption, etc.

This multiplicity of performance indicators, some of which are desirable to maximize and others to be minimized, is characteristic of any complex task of operations research. At the same time, the wording “achieving the maximum effect at a given cost” or “achieving a given effect at a minimum cost” is correct. In the general case, there is no solution that would turn one indicator W 1 into a maximum and at the same time into a maximum (or minimum) another indicator W 2 ; Moreover, such a solution does not exist for several indicators. However, a quantitative analysis of the effectiveness can be useful in the case of several indicators, as well. it allows you to discard obviously irrational solutions that are inferior to the best options in all respects.

Consider an example. Let the combat operation Q be analyzed, evaluated by two indicators:

W- the probability of completing a combat mission;

S- the cost of funds spent.

It is desirable to turn the first indicator to a maximum, and the second - to a minimum.

Suppose 20 different solutions x 1 , x 2 ,…, x 20 are offered. For each of them, the values ​​of both indicators W and S are known (see Fig. 1.1).

The figure shows that some solutions can be immediately discarded. What options should be preferred when evaluating the effectiveness of the two indicators. Obviously, those that lie simultaneously on the right and on the lower border of the region (in Fig. 1.1 - dotted line). That. four options remain X 16 , X 17 , X 19 , X 20 . Of these, X 16 is the most efficient, but relatively expensive; X 20 is the cheapest, but not as effective. It is up to the decision maker to figure out at what price we can pay for a certain increase in efficiency, or, conversely, at what efficiency we are willing to sacrifice so as not to incur too large material losses.

S x

x

x x x

x x x

x x

x x x

x x

x x

x

x x

Due to the fact that a comprehensive assessment of the operation by several indicators at once is difficult, in practice several indicators are combined into one generalized indicator. Often, a fraction is taken as such a criterion; in the numerator put those indicators W 1 ,…, W , which it is desirable to increase, and in the denominator, those that it is desirable to reduce:

U=
(4)

A common disadvantage of a criterion of type (4) is that the lack of efficiency in one indicator can always be compensated for by another (for example, a low probability of completing a combat mission due to low ammunition consumption, etc.).

Often composite criteria are assumed as a "weighted sum" of individual performance indicators:

U=α
+α
+…+α
(5)

where α are positive or negative coefficients.

Positive ones are set at those indicators that it is desirable to maximize; negative - at those that it is desirable to minimize. The absolute values ​​of the coefficients correspond to the degree of importance of the indicators. A criterion of the form (5) has the same drawback (the possibility of mutual compensation of heterogeneous indicators) and can lead to incorrect recommendations. However, in those cases when α i are not chosen arbitrarily, but are chosen so that the composite criterion is the best performed its function in a way, it is possible to obtain results of limited value with its help.

In some cases, a problem with several indicators can be reduced to a problem with a single indicator, if one (main) indicator of efficiency W 1 is singled out and it is sought to be maximized, and only some restrictions of the form are imposed on the remaining auxiliary indicators W 2, W 3, ...:

W
; …W
; W
; …W

These restrictions will then be included in the set of given conditions a 1 , a 2 ,… .

With this formulation of the problem, all performance indicators, except for one, the main one, are transferred to the category specified operation conditions. Solutions that do not fit within the specified boundaries are immediately discarded. The recommendations received will obviously depend on how the constraints on the supporting indicators are chosen. To determine how much this affects the final recommendations for choosing a solution, vary the limits within reasonable limits.

There is another way to build a compromise solution, which can be called " successive concessions". Let the performance indicators be arranged in descending order of importance: first the main W 1 , then other auxiliary ones: W 2 , W 3 ,… . For simplicity, we will assume that each of them must be converted to a maximum (if this is not the case, it is enough to change the sign of the exponent). The procedure for constructing a compromise solution is as follows. First, a solution is sought that maximizes the main efficiency indicator W 1 . Then, based on practical considerations and the accuracy with which the initial data are known (often small), some “concession” ΔW 1 is assigned, which we agree to allow in order to turn the second indicator W 2 into a maximum. We impose a restriction on the indicator W 1 so that it is not less than W 1 * - ∆W 1 (W 1 * is the maximum possible value of W 1), and with this restriction we are looking for a solution that maximizes W 2 .

Next, a “concession” is again assigned in the W 2 indicator, at the cost of which W 3 can be maximized, and so on. This method is good because it is immediately clear at the price of what “concession” in one indicator a gain in another is acquired. At the same time, the freedom to choose a solution, acquired at the price of even minor concessions, may turn out to be significant, since in the region of the maximum, the efficiency of the solution usually changes very little.

One way or another, with any method of formalization, the task of quantitative justification of the decision by several indicators remains not completely defined, and the final choice of the decision is determined by the will of the "commander". The task of the researcher is to provide the “commander” with a sufficient amount of data that allows him to comprehensively assess the advantages and disadvantages of each solution and, based on them, make the final choice.

Conditional formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (4)

VLOOKUP for two or more criteria

Surely everyone who is familiar with the VLOOKUP function knows that it searches for given values ​​exclusively in the left column of the specified table (more about VLOOKUP can be found in the article: How to find a value in another table or VLOOKUP strength). Also, many people know that VLOOKUP only searches based on a single value.

Did the article help? Share the link with your friends! Video lessons

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:a lpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

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, during 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
	A1	A2	A3	A4		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 change of the indicator 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
	A1	A2	A3	A4		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.

Let's assume that you have the following report on the sales of sales representatives:

From it you need to find out how much pencils sold by sales representative Ivanov v January.

PROBLEM: How to summarize data by multiple criteria??

SOLUTION: Method 1:

BDSUMM(A1:G16;F1;I1:K2)

In English version:

DSUM(A1:G16,F1,I1:K2)

HOW IT WORKS:

From the database we specified A1:G16 function BDSUMM retrieves and sums column data Quantity(argument " Field" = F1) according to the given in cells I1:K2 (Seller = Ivanov; Products = Pencils;Month = January) criteria.

CONS: The list of criteria should be on the sheet.

NOTES: The number of summation criteria is limited by RAM.

APPLICATION AREA: Any version of Excel

Method 2:

SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

In English version:

SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

HOW IT WORKS:

The SUMPRODUCT function generates arrays of TRUE and FALSE values, according to selected criteria, in Excel memory.

If the calculations were performed in the cells of the sheet (for clarity, I will demonstrate the entire operation of the formula as if the calculations were taking place on the sheet, and not in memory), then the arrays would look like this:

It is obvious that if, for example, D2=Pencils, then the value will be TRUE, and if D3=Folders, then FALSE (since the product selection criterion in our example is the value Pencils).

Knowing that TRUE is always equal to 1 and FALSE is always equal to 0, we continue to work with arrays as with the numbers 0 and 1.
Multiplying the obtained values ​​of the arrays with each other sequentially, we get ONE array of zeros and ones. Where all three selection criteria were met, ( IVANOV, PENCILS, JANUARY) i.e. all conditions were TRUE, we get 1 (1*1*1 = 1), but if at least one condition was not met, we get 0 (1*1*0 = 0 ; 1*0*1 = 0 ; 0*1* 1 = 0).

Now it remains only to multiply the resulting array by the array containing the data that we need to sum up as a result (range F2:F16) and, in fact, sum up what is not multiplied by 0.

Now compare the arrays obtained with the help of the formula and with the step-by-step calculation on the sheet (highlighted in red).

I think everything is clear :)

MINUSES: SUMPRODUCT - "heavy" array formula. When calculating on large data ranges, the recalculation time increases noticeably.

NOTES

APPLICATION AREA: Any version of Excel

Method 3: Array Formula

SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

In English version:

SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

HOW IT WORKS: Just like Method #2. There are only two differences - this formula is entered by pressing Ctrl+Shift+Enter instead of just pressing Enter and the array of 0's and 1's is not multiplied by the summation range, but is selected using the IF function.

MINUSES: Array formulas when calculating on large data ranges noticeably increase the recalculation time.

NOTES: The number of processed arrays is limited to 255.

APPLICATION AREA: Any version of Excel

Method 4:

SUMIFS(F2:F16,B2:B16,I2,D2:D16,J2,A2:A16,K2)

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