As a rule, the effectiveness of large-scale, complex operations cannot be characterized using one indicator W; it must be assisted by other, additional W 1, W 2,..., W; Some of them it is desirable to do more, others - less. For example, when assessing the performance of an enterprise, a number of indicators have to be taken into account:
full volume 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, it is necessary to take into account a number of additional ones:
own losses
operation execution time,
ammunition consumption, etc.
This multiplicity of performance indicators, some of which are desirable to maximize and others to minimize, is characteristic of any complex operations research problem. In this case, the correct formulation is “achieving maximum effect at given costs” or “achieving a given effect at minimal costs.” In the general case, there is no solution that would turn one indicator W 1 into a maximum and at the same time another indicator W 2 into a maximum (or minimum); Moreover, such a solution does not exist for several indicators. However, quantitative analysis of effectiveness can also be useful in the case of several indicators, because it allows you to discard in advance clearly irrational solution options that are inferior to the best options in all respects.
Let's look at an example. Let us analyze the combat operation Q, assessed by two indicators:
W– probability of completing a combat mission;
S- cost of funds spent.
It is advisable to turn the first indicator to a maximum, and the second to a minimum.
Suppose there are 20 different solution options x 1, x 2,…, x 20. For each of them, the values of both indicators W and S are known (see Fig. 1.1).
The figure shows that some solution options can be immediately discarded. Which options should be preferred when assessing effectiveness by two indicators. Obviously those that lie simultaneously on both the right and lower borders of the region (in Fig. 1.1 - dotted line). That. There are four options left: X 16, X 17, X 19, X 20. Of these, X 16 is the most effective, but relatively expensive; X 20 is the cheapest, but not as effective. The decision maker’s job is to figure out at what price we can pay for a certain increase in efficiency or, conversely, what efficiency we are willing to sacrifice in order not to incur too large material losses.
Sx 
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 an operation using 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 general disadvantage of a criterion of type (4) is that a lack of effectiveness 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 to be a “weighted sum” of individual performance indicators:
U=α 
+α 
+…+α 
(5)
where α are positive or negative coefficients.
Positive ones are set for those indicators that it is desirable to maximize; negative – for 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 type (5) has the same drawback (the possibility of mutual compensation of heterogeneous indicators) and can lead to incorrect recommendations. However, in cases where α i are not chosen arbitrarily, but are selected so that the composite criterion is the best 
performed its function, 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 one indicator if one singles out one (main) efficiency indicator W 1 and strives to maximize it, and impose only some restrictions of the form on the remaining auxiliary indicators W 2, W 3,...
W 
;
…
W 
; W 
; ...W 
These restrictions will then be included in the complex of specified conditions a 1 , a 2 ,… .
With this formulation of the problem, all performance indicators, except one, the main one, are transferred to the category given operation conditions. Solution options that do not fit within the given boundaries are immediately discarded. The recommendations obtained will obviously depend on how the limits for the supporting indicators are chosen. To determine how much this influences the final recommendations for choosing a solution, vary the restrictions within reasonable limits.
There is another possible way to build a compromise solution, which can be called “ method of successive concessions" Let the performance indicators be arranged in order of decreasing importance: first the main W 1, then other auxiliary ones: W 2, W 3,…. For simplicity, we will assume that each of them needs to be turned 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), a certain “concession” ΔW 1 is assigned, which we agree to allow in order to maximize the second indicator W 2 . We impose a limitation on the indicator W 1 so that it is no less than W 1 * - ∆W 1 (W 1 * is the maximum possible value of W 1), and with this limitation we look for a solution that turns W 2 to a maximum.
Next, a “concession” is again assigned in the W 2 indicator, at the cost of which W 3 can be maximized, etc. This method is good because it is immediately clear at what cost “concession” in one indicator is gained in another. At the same time, the freedom to choose a solution, acquired at the cost of even minor concessions, can turn out to be significant, because 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 quantitatively substantiating a decision based on several indicators remains not fully defined, and the final choice of solution is determined by the volitional act of the “commander.” The researcher’s job is to provide the “commander” with a sufficient amount of data, allowing him to comprehensively assess the advantages and disadvantages of each solution option and, based on them, make the final choice.
1. In the additional column in which we will indicate the rating, insert the RANK function (write in the cell =RANK and select the proposed EXCEL function from the list, click fx in the formula bar)
2. Fill in the arguments in the window that opens: “Number” - indicate the first value in our table in the same line where the formula is located.
3. “Link” - indicate the entire data array, i.e. range with all numbers (sales values).
4. Fix the boundaries of this range (press F4 on the keyboard) so that when dragging in the future the address of the range does not “move” and press OK.
5. We stretch the formula down to all cells of the “rating” column.
When using this function, the rating is calculated automatically, and if you change any value, the rating will be automatically recalculated.
If you liked the material or even found it useful, you can thank the author by transferring a certain amount using the button below:
(to transfer using a card, click on VISA and then “transfer”)
This is a chapter from the book: Michael Girvin. Ctrl+Shift+Enter. Mastering array formulas in Excel.
Samples based on one or more conditions. A number of Excel functions use comparison operators. For example, SUMISF, SUMIFS, COUNTIFS, 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 find the average. What if you want to impose conditions on the search, for example, maximum value or standard deviation? In these cases, since there is no built-in function, you must invent an array formula. This is often due to the use of the array comparison operator. The first example in this chapter shows how to calculate the minimum value given one condition.
Let's use the IF function to select array elements that meet a condition. In Fig. 4.1 in the left table there is a column with the names of cities and a column with time. You need to find the minimum time for each city and place this value in the corresponding cell of the right table. The sampling 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 are specific to Auckland? And how do you copy the formulas down the column? Since Excel does not have a built-in MINESLI function, you will need to write an original formula that combines the IF and MIN functions.
Rice. 4.1. Purpose of the formula: select the minimum time for each city
Download the note in format or 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 specific to Auckland.
As shown in Fig. 4.3, in the next step, enter the IF function as an argument number1 for MIN. You put IF inside MIN.
By placing the cursor where the argument is entered log_expression IF function (Fig. 4.4), you select the range with the names of the cities A3:A8, and then press F4 to make the cell references absolute (for more details, see, for example,). 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 Auckland when viewing the range A3:A8.
Rice. 4.4. Create an array operator in argument log_expression IF functions
So you've created an array operator using the comparison operator. At any time during array processing, the array operator is a comparison operator, so its result will be an array consisting of the values TRUE and FALSE. To verify this, select the array (to do this, click on the argument in the tooltip log_expression) and press F9 (Fig. 4.5). Typically 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 consisting of TRUE and FALSE values, click the argument in the tooltip log_expression and press F9
It is a method of comparing two or more research objects (phenomena, objects, ideas, results, etc.). As a result of this analysis, the advantages and disadvantages of the compared objects are identified for the purpose of classification. Benchmarking is used in various disciplines. The meaning and examples are discussed in the article.
Benchmarking plan
Comparison is one of the simplest ways through which a person understands the world around him. The whole point is to identify the advantages and disadvantages of the compared objects. The objects of research must be homogeneous, that is, they must belong to the same genus, category. After the comparison objects are selected (there can be two or more), the type of comparison is selected. For example, dynamic, spatial, etc. The next stage is to decide on how many indicators the objects will be evaluated and compared. And the final stage of the comparative analysis method is the selection of a comparison scale.
Disciplines that are based on the comparative method
A number of disciplines that relate to various spheres of human life are based on comparative analysis. For example, comparative anatomy, which is one of the biological disciplines, studies the patterns of structure and development of both the organs themselves and their systems. And there are quite a lot of such disciplines, in addition to comparative anatomy, using the comparative method of analysis as the basis of knowledge: comparative theology, comparative historical linguistics, comparative literary criticism, comparative mythology, comparative political science, comparative law, comparative psychology, comparative religious studies, comparative criminal law, comparative philosophy.
Advantages and disadvantages of the comparison method
Comparative analysis is universal, it belongs to general scientific research methods, it is a logical technique in understanding the world around us. It allows you to identify common features and characteristics of the analyzed objects, identify differences, and identify the distinctive features of specific objects from among those common to all analyzed objects (phenomena, ideas, results, etc.). The main disadvantage of the method under discussion is that all the results obtained can be questioned due to their subjectivity: what seems different to one person is either invisible to another or is not a distinctive feature at all. In other words, the inability of the analyzer to objectively assess the properties of the object under study, compare them with the properties of other objects and take into account all aspects of the process of identifying common and different characteristics is the main drawback.
Classification
Comparative analysis can be divided into:
- Quantitative - quantitative characteristics are analyzed.
- Qualitative - analysis of characteristics that determine the quality of the studied object, phenomenon, result.
- Retrospective - past changes that influence the current situation are analyzed.
- Applied - the result of an object’s activity is studied.
- Research - this type of analysis is used in analytical sciences.
- Descriptive - initially explores the structure of an object (phenomenon), gradually moving to an analysis of functions and goals.
- General - based on general systems theory.
- Structural - this type of analysis studies the structure of the analyzed objects.
- Microsystem - used to study a specific system.
- Macrosystemic - the role of specific systems in the variety of similar systems interconnected by common characteristics is analyzed.
- Vital - studies the development and its main stages of the analyzed system.
- Genetic - studies heredity, its mechanism, genetic systems.
- Other types.
Comparison in economics
Most often, comparative analysis is used as a method of processing economic information. This analysis is used to obtain reliable knowledge about a certain period of time, to choose the path of further development, to identify the most acceptable option and to solve many other economic problems and issues. The comparative analysis method is effective and indicative at all levels. It plays an invaluable role in assessing the potential of enterprises.
Varieties of comparison method related to economic activity
In relation to research in the economic field of activity, there are the following types of comparative analysis:
- Horizontal and vertical. Horizontal comparative analysis specializes in determining the absolute and relative deviation between indicators that are compared with each other. Vertical analysis of calculating the specific gravity of parts allows us to study economic phenomena, namely their structure.
- Dynamic and static. The dynamic method is also called temporary; it allows you to study changes in the analyzed processes (phenomena, objects) over time. Static, or spatial, studies the level of the same indicator over a certain time for different objects of study.
- One-dimensional and multidimensional. The essence One-dimensional analysis consists in the fact that either several objects are analyzed using one indicator, or one object is analyzed using different indicators. The multivariate method allows you to study several objects using several indicators.
- According to the comparison base. On This point needs to be discussed in a little more detail. The fact is that this type includes several subparagraphs.
- The first of them is a comparison of expected data with period data considered as reference. In other words, a forecast of the results that should be obtained is made, then the obtained result is compared with the reporting data of a certain period.
- Comparison of planned indicators with actually obtained indicators.
- Comparison of actual data obtained with standard values of indicators established by standards.
- When the industry average values of the analyzed indicators are known, the actual data obtained are compared with the industry averages.
- Comparison of actual data with data obtained in the previous period (last year/month/half-year).
- Comparison of the obtained data with data obtained from leading enterprises that are leaders in the industry.
- If any factor has been changed or the production situation has changed, then a comparison is made of the data that was obtained before and after the changes were made.
- And finally, a comparative analysis of different options for management solutions is carried out.
Results
Benchmarking results represent the data that is obtained through the comparison process. That is, all distinctive features, as well as similar ones, are usually summarized in tables. Based on the values entered in these tables, conclusions are drawn about the disadvantages and advantages of a particular analyzed object/effect/phenomenon, etc. In other words, conclusions drawn on the basis of the identified distinctive features are the result of a comparative analysis.
Comparison of corporate governance models
A comparative analysis of corporate governance models used in different countries of the world consists of comparing these models with each other according to various criteria. One of the most important criteria is hiring employees. There are two options: quick hiring for a short/long period of time and long-term hiring for a short/long period. In the second case, hiring workers, as a rule, consists of several stages: a candidate for a position undergoes an interview (possibly more than one), then begins training, then undergoes an internship to consolidate acquired skills and gain experience using knowledge in practice, then passes an exam, according to the results of which the candidate receives or does not receive the desired position. Such a criterion as decision-making can also be of a different nature - either decisions are made individually or collectively. Responsibility, as well as decision-making, can be collective or individual.
Indicators of financial and economic activity of the enterprise
A comparative analysis of indicators of the financial and economic activity of an enterprise begins with the determination of these same indicators. There are four main large groups of indicators, which, in turn, are divided into several smaller ones. Indicators characterizing the economic potential of an enterprise include the assets of the enterprise (i.e., property owned by the enterprise and cash), fixed capital (in other words, the part of capital that more than once takes part in the production activities of the enterprise) and others indicators. The overall results of the enterprise's activities are assessed by total expenses, which include the costs of maintaining equipment and its operation, and shop expenses. Operational efficiency can be assessed by profit, return on sales, assets, and equity. The financial situation is characterized by such parameters as solvency (general and current), market and financial and economic stability.
Examples not related to the economic sphere of activity
As mentioned above, comparative analysis is used in many areas of human activity. Not only economic efficiency can be assessed using this method. An example of comparative analysis is consideration of programs of political, economic, social reforms. It is much easier to make the right decision by comparing the projects being accepted with foreign experience and the experience of previous years. Studying management methods, legislation, structures of state systems, as well as comparing them with the experience of other states allows us to adopt positive experience and effectively implement it in our country both to improve the lives of citizens and to improve the state itself.
Conclusion
So, the concept of “comparative analysis” applies to many sciences. This method is one of the main ways to understand the world around us. Since time immemorial, humanity has used comparison as the most effective way to study phenomena, results, and objects. Due to its versatility, comparative analysis is widely used in various fields of human activity.
Let's say you want to sum values with more than one condition, such as the sum of sales of a product in a specific region. This is a good use of the SUMIFS function in a formula.
Notice this example where we have two conditions: sales amount required meat(from column C) to South areas (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 a value of 14,719.
Let's look at 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 uses as input.
Then you need to find the data that meets the two conditions and enter the first condition, specifying for the function the location of the data (a2: A11), and also that the condition is “South”. Note 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 the arguments for the second condition - the range of cells (C2:C11) that contain the word "Meat" as well as the word itself (enclosed in quotes) so that Excel can match them. Complete the formula by closing the closing parenthesis ) , and then press Enter. The result is 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 ( Autocomplete formulas will appear below the formula, and the list of arguments will be listed in the required order.
Take a look at the image of the formula autocomplete and argument list in our example sum_range is D2:D11, the column of numbers that need to be summed. criteria_range1- a2. A11 - data column containing condition1"Southern".
As you enter the formula, the remaining arguments (not shown here) will appear in Formula AutoComplete; range_conditions2 is the range C2:C11, which is the column of data that contains condition2- "Meat".
If you click SUMIFS When you autocomplete a formula, an article will open 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 values and formulas or add your own to see instant results change.
Copy all the cells from the table below and paste them into cell A1 of a new Excel sheet. You can adjust the width of the columns to make the formulas appear better.
Region | Salesman | Sales |
|
|---|---|---|---|
|
West |
|||
|
Oriental |
Pesotsky |
||
|
Northern |
Pesotsky |
Dairy |
|
|
Marinova |
|||
|
Oriental |
Pesotsky |
||
|
Northern |
