Evaluation of the operation on several indicators. Comparative analysis: concept, types and examples Carried out according to several criteria at once

25.01.2023

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

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 lgN, 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:

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

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

№ tasks
No. of alternatives
№ tasks
No. of alternatives
№ tasks
No. of alternatives
№ tasks
No. of alternatives
№ tasks
No. of alternatives
№ tasks
No. of alternatives

Table 2.

Initial data:

Alternative Solutions

indicators

A6

X 1

X 2

X 3

X 4

X 5

It is a method of comparing two or more research objects (phenomena, objects, ideas, results, etc.). As a result of such an analysis, the advantages and disadvantages of the compared objects are revealed for the purpose of classification. Comparative analysis is applied in various disciplines. The meaning and examples are discussed in the article.

Benchmarking Plan

Comparison is one of the simplest ways by which a person learns about the world around him. The whole point is to identify the advantages and disadvantages of the compared objects. The objects of study must be homogeneous, that is, they must belong to the same genus, category. After the objects of comparison are selected (there may 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 objects will be evaluated and compared. And the final stage of the comparative analysis method is the choice of the comparison scale.

Disciplines that are based on the comparative method

A number of disciplines are based on comparative analysis, which relate to various spheres of human life. 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 religion, comparative criminal law, comparative philosophy.

Advantages and disadvantages of the comparison method

Comparative analysis is universal, refers to general scientific methods of research, it is a logical technique in the knowledge of the world around. It allows you to highlight the common features, features of the analyzed objects, identify differences, 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 to be a difference to one person is either imperceptible 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 a major drawback.

Classification

Comparative analysis can be divided into:

Quantitative - quantitative characteristics are analyzed.
Qualitative - analysis of the characteristics that determine the quality of the object under study, phenomenon, result.
Retrospective - analyzes past changes that affect the current situation.
Applied - the result of the object's activity is studied.
Research - this type of analysis is used in the 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 deals with the study of the structure of the analyzed objects.
Microsystem - used to study a specific system.
Macrosystemic - analyzes the role of specific systems in a variety of similar systems interconnected by common features.
Vital - deals with the study of development and its main stages of the analyzed system.
Genetic - the study of 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 method of comparative analysis is effective and indicative at all levels. It plays an invaluable role in assessing the potential of enterprises.

Varieties of the comparison method related to economic activity

With regard 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 deviations between indicators that are compared with each other. The vertical analysis of the calculation of the specific weight 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 the changes in the analyzed processes (phenomena, objects) over time. Static, or spatial, studies the level of the same indicator for a certain time for different objects of study.
One-dimensional and multidimensional. essence One-dimensional analysis consists in the fact that either several objects are analyzed according to one indicator, or one object is analyzed according to different indicators. The multidimensional method allows you to explore several objects according to several indicators.
Based on comparison. On This point needs to be a little more detailed. The fact is that this type includes several subparagraphs.

The first of them is a comparison of the expected data with the data of the period considered as a reference. In other words, a forecast is made of the results that should be obtained, then the result is compared with the reporting data of a certain period.
Comparison of planned indicators with actually received indicators.
Comparison of the data obtained after the fact with the standard values of the indicators established by the norms.
When the industry average values of the analyzed indicators are known, the actually obtained data are compared with the industry average.
Comparison of actual data with data obtained in the previous period (last year/month/half year).
Comparison of the received data with the data received at the leading enterprises, which are leaders in the industry.
If any factor was changed or the production situation was changed, then a comparison is made of the data that were obtained before and after the changes were made.
And, finally, a comparative analysis of different options for management decisions is made.

Results

Benchmarking results are the data that is obtained from the comparison process. That is, all the distinguishing features, as well as similar ones, are summarized, as a rule, in tables. Based on the values entered in these tables, conclusions are drawn about the disadvantages and advantages of this or that analyzed object/effect/phenomenon, etc. In other words, the conclusions made 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 the hiring of employees. There are two options: quick hire for a short/long period of time and long-term hire for a short/long term. In the second case, hiring employees, as a rule, consists of several stages: a candidate for a position passes an interview (perhaps more than one), then proceeds to training, then undergoes an internship to consolidate the acquired skills and gain experience in using knowledge in practice, then an exam is taken, according to the results of which the candidate gets or does not get 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 and 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 definition of these same indicators. There are four main large groups of indicators, which, in turn, are divided into several smaller ones. The 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, part of the capital that more than once takes part in the production activities of the enterprise) and others indicators. The overall results of the company's activities are evaluated by total costs, which include the cost of maintaining the equipment and its operation, shop expenses. Performance can be assessed by profit, return on sales, assets, equity. The financial position is characterized by such parameters as solvency (general and current), market and financial and economic stability.

Examples not related to the economic field of activity

As mentioned above, comparative analysis is used in many areas of human activity. Not only economic efficiency can be assessed by this method. An example of a comparative analysis is the consideration of programs of political, economic, social reforms. It is much easier to make the right decision by comparing the accepted projects with foreign experience, the experience of previous years. The study of management methods, legislation, structures of state systems, as well as their comparison with the experience of other states, allows you to adopt positive experience and effectively implement it in your country both to improve the lives of citizens and to improve the state itself.

Conclusion

So, the concept of "comparative analysis" refers to many sciences. This method is one of the main knowledge of the surrounding world. Since time immemorial, comparison has been used by mankind as the most effective way to study phenomena, results, objects. Due to its versatility, comparative analysis is widely used in various fields of human activity.

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

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 in January.

PROBLEM: How to summarize data by multiple criteria??

DECISION: 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)