Apeere ti ṣiṣe iṣiro ibamu ni Excel

One of the most common methods used in statistics to study data is correlation analysis, which can be used to determine the influence of one quantity on another. Let’s see how this analysis can be performed in Excel.

akoonu

Idi ti itupale ibamu

Correlation analysis allows you to find the dependence of one indicator on another, and if it is found, calculate olùsọdipúpọ ibamu (degree of relationship), which can take values ​​from -1 to +1:

  • if the coefficient is negative, the dependence is inverse, i.e. an increase in one value leads to a decrease in the other and vice versa.
  • if the coefficient is positive, the dependence is direct, i.e. an increase in one indicator leads to an increase in the second and vice versa.

The strength of the dependence is determined by the modulus of the correlation coefficient. The larger the value, the stronger the change in one value affects the other. Based on this, with a zero coefficient, it can be argued that there is no relationship.

Performing correlation analysis

To learn and better understand correlation analysis, let’s try it for the table below.

Apeere ti ṣiṣe iṣiro ibamu ni Excel

Here are the data on the average daily temperature and average humidity for the months of the year. Our task is to find out if there is a relationship between these parameters and, if so, how strong.

Method 1: Apply the CORREL Function

Excel provides a special function that allows you to make a correlation analysis – CORREL. Its syntax looks like this:

КОРРЕЛ(массив1;массив2).

The procedure for working with this tool is as follows:

  1. We get up in a free cell of the table in which we plan to calculate the correlation coefficient. Then click on the icon "fx (Fi iṣẹ sii)" si osi ti awọn agbekalẹ bar.Apeere ti ṣiṣe iṣiro ibamu ni Excel
  2. In the opened function insertion window, select a category "Iṣiro" (tabi “Atokọ alfabeti ni kikun”), among the proposed options we note “CORREL” ki o si tẹ OK.Apeere ti ṣiṣe iṣiro ibamu ni Excel
  3. The function arguments window will be displayed on the screen with the cursor in the first field opposite “Array 1”. Here we indicate the coordinates of the cells of the first column (without the table header), the data of which needs to be analyzed (in our case, B2:B13). You can do this manually by typing the desired characters using the keyboard. You can also select the required range directly in the table itself by holding down the left mouse button. Then we move on to the second argument “Array 2”, simply by clicking inside the appropriate field or by pressing the key Tab. Here we indicate the coordinates of the range of cells of the second analyzed column (in our table, this is C2:C13). Click when ready OK.Apeere ti ṣiṣe iṣiro ibamu ni Excel
  4. We get the correlation coefficient in the cell with the function. Meaning "-0,63" indicates a moderately strong inverse relationship between the analyzed data.Apeere ti ṣiṣe iṣiro ibamu ni Excel

Method 2: Use “Analysis Toolkit”

An alternative way to perform correlation analysis is to use “Package Analysis”, which must first be enabled. For this:

  1. Lọ si akojọ aṣayan “Faili”.Apeere ti ṣiṣe iṣiro ibamu ni Excel
  2. Yan ohun kan lati inu atokọ ni apa osi "Awọn paramita".Apeere ti ṣiṣe iṣiro ibamu ni Excel
  3. In the window that appears, click on the subsection "Awọn afikun". Then in the right part of the window at the very bottom for the parameter "Iṣakoso" yan Awọn afikun awọn ohun elo Excel ki o si tẹ "Lọ".Apeere ti ṣiṣe iṣiro ibamu ni Excel
  4. In the window that opens, mark "Apo-itupalẹ" ati jẹrisi iṣẹ naa nipa titẹ bọtini OK.Apeere ti ṣiṣe iṣiro ibamu ni Excel

All is ready, "Apo-itupalẹ" activated. Now we can move on to our main task:

  1. Titari bọtini naa "Itupalẹ data", which is in the tab "Data".Apeere ti ṣiṣe iṣiro ibamu ni Excel
  2. A window will appear with a list of available analysis options. We celebrate “Correlation” ki o si tẹ OK.Apeere ti ṣiṣe iṣiro ibamu ni Excel
  3. A window will appear on the screen in which you must specify the following parameters:
    • “Input Interval”. We select the entire range of analyzed cells (that is, both columns at once, and not one at a time, as was the case in the method described above).
    • “Grouping”. There are two options to choose from: by columns and rows. In our case, the first option is suitable, because. this is how the analyzed data is located in the table. If headings are included in the selected range, check the box next to “Labels in the first line”.
    • “Output Options”. You can choose an option “Exit Interval”, in this case the results of the analysis will be inserted on the current sheet (you will need to specify the address of the cell from which the results will be displayed). It is also proposed to display the results on a new sheet or in a new book (the data will be inserted at the very beginning, i.e. starting from the cell (A1). As an example, we leave “New Worksheet” (selected by default).
    • Nigbati ohun gbogbo ba ṣetan, tẹ OK.Apeere ti ṣiṣe iṣiro ibamu ni Excel
  4. We get the same correlation coefficient as in the first method. This suggests that in both cases we did everything right.Apeere ti ṣiṣe iṣiro ibamu ni Excel

ipari

Thus, performing correlation analysis in Excel is a fairly automated and easy-to-learn procedure. All you need to know is where to find and how to set up the necessary tool, and in the case of “solution package”, how to activate it, if before that it was not already enabled in the program settings.

Fi a Reply