ניתוח קורלציות באקסל. דוגמה לביצוע ניתוח מתאם

ניתוח מתאם הוא שיטת מחקר נפוצה המשמשת לקביעת רמת התלות של הערך הראשון ב-1. בגיליון האלקטרוני יש כלי מיוחד המאפשר ליישם מחקר מסוג זה.

המהות של ניתוח מתאם

יש צורך לקבוע את הקשר בין שתי כמויות שונות. במילים אחרות, הוא מגלה לאיזה כיוון (קטן / גדול יותר) הערך משתנה בהתאם לשינויים בשנייה.

מטרת ניתוח המתאם

התלות נוצרת כאשר מתחיל זיהוי מקדם המתאם. שיטה זו שונה מניתוח רגרסיה, שכן יש רק אינדיקטור אחד המחושב באמצעות מתאם. המרווח משתנה מ-1+ ל-1. אם הוא חיובי, אז עלייה בערך הראשון תורמת לעלייה ב-2. אם שלילי, אז עלייה בערך ה-1 תורמת לירידה ב-2. ככל שהמקדם גבוה יותר, ערך אחד חזק יותר משפיע על השני.

חשוב! במקדם ה-0, אין קשר בין הכמויות.

חישוב מקדם המתאם

בואו ננתח את החישוב על מספר דוגמאות. לדוגמה, ישנם נתונים טבלאיים, שבהם ההוצאה על קידום פרסום והיקף המכירות מתוארים לפי חודשים בעמודות נפרדות. על סמך הטבלה, נברר את רמת התלות של נפח המכירות בכסף שהוצא על קידום פרסום.

שיטה 1: קביעת מתאם באמצעות אשף הפונקציות

CORREL – פונקציה המאפשרת ליישם ניתוח מתאם. טופס כללי - CORREL(massiv1;massiv2). הוראות מפורטות:

1. יש צורך לבחור את התא שבו מתוכנן להציג את תוצאת החישוב. לחץ על "הוסף פונקציה" הממוקם משמאל לשדה הטקסט כדי להזין את הנוסחה.

2. אשף הפונקציות נפתח. כאן אתה צריך למצוא קורל, לחץ עליו ואז על "אישור".

3. חלון הטיעונים נפתח. בשורה "Array1" עליך להזין את הקואורדינטות של המרווחים של ה-1 מבין הערכים. בדוגמה זו, זו העמודה 'ערך מכירות'. אתה רק צריך לבחור את כל התאים שנמצאים בעמודה זו. באופן דומה, עליך להוסיף את הקואורדינטות של העמודה השנייה לשורה "Array2". בדוגמה שלנו, זו העמודה עלויות פרסום.

4. לאחר הזנת כל הטווחים, לחץ על כפתור "אישור".

המקדם הוצג בתא שצוין בתחילת פעולותינו. התוצאה המתקבלת היא 0,97. אינדיקטור זה משקף את התלות הגבוהה של הערך הראשון בשני.

שיטה 2: חשב מתאם באמצעות Analysis ToolPak

קיימת שיטה נוספת לקביעת מתאם. כאן נעשה שימוש באחת הפונקציות שנמצאות בחבילת הניתוח. לפני השימוש בו, עליך להפעיל את הכלי. הוראות מפורטות:

1. עבור אל הקטע "קובץ".

2. חלון חדש ייפתח, בו תצטרך ללחוץ על הקטע "הגדרות".
3. לחץ על "תוספות".
4. אנו מוצאים את האלמנט "ניהול" בתחתית. כאן עליך לבחור "תוספות Excel" מתפריט ההקשר וללחוץ על "אישור".

5. נפתח חלון תוספות מיוחד. הצב סימן ביקורת ליד הרכיב "חבילת ניתוח". אנו לוחצים על "אישור".
6. ההפעלה הצליחה. כעת נעבור ל-Data. הופיע בלוק "ניתוח", שבו אתה צריך ללחוץ על "ניתוח נתונים".
7. בחלון החדש שיופיע, בחר ברכיב "Correlation" ולחץ על "אישור".

8. חלון הגדרות הניתוח הופיע על המסך. בשורה "מרווח קלט" יש צורך להזין את הטווח של כל העמודות הלוקחות חלק בניתוח. בדוגמה זו, אלו הן העמודות "ערך מכירה" ו"עלויות פרסום". הגדרות תצוגת הפלט מוגדרות בתחילה לגיליון עבודה חדש, מה שאומר שהתוצאות יוצגו בגיליון אחר. לחלופין, אתה יכול לשנות את מיקום הפלט של התוצאה. לאחר ביצוע כל ההגדרות, לחץ על "אישור".

הציונים הסופיים יצאו. התוצאה זהה לשיטה הראשונה - 0,97.

הגדרה וחישוב של מקדם מתאם מרובה ב- MS Excel

כדי לזהות את רמת התלות של מספר כמויות, משתמשים במקדמים מרובים. בעתיד, התוצאות מסוכמות בטבלה נפרדת, הנקראת מטריצת המתאם.

מדריך מפורט:

1. בסעיף "נתונים", אנו מוצאים את בלוק "ניתוח" הידוע כבר ולוחצים על "ניתוח נתונים".

2. בחלון שמופיע, לחץ על האלמנט "Correlation" ולחץ על "אישור".
3. בשורה "מרווח קלט" אנו נוהגים במרווח עבור שלוש עמודות או יותר של טבלת המקור. ניתן להזין את הטווח באופן ידני או פשוט לבחור אותו עם ה-LMB, והוא יופיע אוטומטית בשורה הרצויה. ב"קיבוץ" בחר את שיטת הקיבוץ המתאימה. ב"פרמטר פלט" מציין את המיקום שבו יוצגו תוצאות המתאם. אנו לוחצים על "אישור".

4. מוּכָן! נבנתה מטריצת המתאם.

מקדם מתאם זוג באקסל

בואו להבין איך לצייר נכון את מקדם המתאם הזוגי בגיליון אלקטרוני של Excel.

חישוב מקדם מתאם זוג באקסל

לדוגמה, יש לך ערכי x ו-y.

X הוא המשתנה התלוי ו-y הוא המשתנה. יש צורך למצוא את הכיוון והחוזק של הקשר בין אינדיקטורים אלה. הדרכה שלב אחר שלב:

1. בוא נמצא את הערכים הממוצעים באמצעות הפונקציה לֵב.

2. בואו לחשב כל אחד х и xavg, у и ממוצע באמצעות האופרטור «-».

3. נכפיל את ההפרשים המחושבים.

4. אנו מחשבים את סכום האינדיקטורים בעמודה זו. המונה הוא התוצאה שנמצאה.

5. חשב את המכנים של ההפרש х и x-ממוצע, y и y-בינוני. לשם כך נבצע את הריבוע.

6. באמצעות הפונקציה AUTOSUMMA, מצא את האינדיקטורים בעמודות המתקבלות. אנחנו עושים כפל. שימוש בפונקציה שורש ריבוע את התוצאה.

7. אנו מחשבים את המנה באמצעות הערכים של המכנה והמונה.

8. CORREL היא פונקציה משולבת המאפשרת למנוע חישובים מורכבים. אנו עוברים אל "אשף הפונקציות", בוחרים ב-CORREL ומציינים את מערכי האינדיקטורים х и у. אנו בונים גרף המציג את הערכים שהתקבלו.

מטריצה ​​של מקדמי מתאם זוגי באקסל

בואו ננתח כיצד לחשב את המקדמים של מטריצות זוגיות. לדוגמה, יש מטריצה ​​של ארבעה משתנים.

הוראות שלב אחר שלב:

1. אנו עוברים ל"ניתוח נתונים", הממוקם בגוש "ניתוח" של הכרטיסייה "נתונים". בחר מתאם מהרשימה שמופיעה.
2. הגדרנו את כל ההגדרות הדרושות. "מרווח קלט" - המרווח של כל ארבע העמודות. "מרווח פלט" - המקום בו אנו רוצים להציג את הסכומים. אנו לוחצים על כפתור "אישור".
3. במקום הנבחר נבנתה מטריצת מתאם. כל מפגש של שורה ועמודה הוא מקדם מתאם. המספר 1 מוצג כאשר הקואורדינטות תואמות.

פונקציית CORREL לקביעת קשר ומתאם באקסל

CORREL – פונקציה המשמשת לחישוב מקדם המתאם בין 2 מערכים. בואו נסתכל על ארבע דוגמאות לכל היכולות של פונקציה זו.

דוגמאות לשימוש בפונקציית CORREL באקסל

דוגמה ראשונה. ישנה לוחית עם מידע על השכר הממוצע של עובדי החברה במהלך אחת עשרה שנים ושער החליפין של $. יש צורך לזהות את הקשר בין שתי הכמויות הללו. הטבלה נראית כך:

אלגוריתם החישוב נראה כך:

הציון המוצג קרוב ל-1. תוצאה:

קביעת מקדם המתאם של השפעת הפעולות על התוצאה

דוגמה שנייה. שני מציעים פנו לשתי סוכנויות שונות בבקשת עזרה בקידום של חמישה עשר יום. מדי יום נערך סקר חברתי, שקבע את מידת התמיכה לכל פונה. כל מרואיין יכול לבחור באחד משני המועמדים או להתנגד לכולם. יש לקבוע עד כמה כל קידום פרסום השפיע על מידת התמיכה במועמדים, איזו חברה יעילה יותר.

באמצעות הנוסחאות שלהלן, אנו מחשבים את מקדם המתאם:

• =CORREL(A3:A17;B3:B17).
• =CORREL(A3:A17;C3:C17).

תוצאות:

מהתוצאות שהתקבלו מתברר כי מידת התמיכה במועמד הראשון גדלה עם כל יום של קידום פרסום, לפיכך, מקדם המתאם מתקרב ל-1. כאשר הפרסום הושק, למועמד השני היה מספר רב של אמון, ועבור 1 ימים הייתה מגמה חיובית. אחר כך ירדה מידת האמון ועד היום החמישה עשר היא ירדה מתחת לאינדיקטורים הראשוניים. ציונים נמוכים מצביעים על כך שהקידום השפיע לרעה על התמיכה. אל תשכח שגורמים נלווים אחרים שאינם נחשבים בצורת טבלה יכולים גם הם להשפיע על האינדיקטורים.

ניתוח הפופולריות של התוכן לפי מתאם של צפיות ופוסטים חוזרים בסרטון

דוגמה שלישית. אדם לקידום סרטונים משלו באירוח סרטונים ביוטיוב משתמש ברשתות חברתיות כדי לפרסם את הערוץ. הוא שם לב שיש קשר מסוים בין מספר הפוסטים מחדש ברשתות החברתיות לבין מספר הצפיות בערוץ. האם ניתן לחזות ביצועים עתידיים באמצעות כלי גיליונות אלקטרוניים? יש צורך לזהות את הסבירות של יישום משוואת הרגרסיה הליניארית כדי לחזות את מספר הצפיות בסרטון בהתאם למספר הפוסטים החוזרים. טבלה עם ערכים:

כעת יש צורך לקבוע את נוכחות הקשר בין 2 אינדיקטורים לפי הנוסחה שלהלן:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"קשר ישיר חזק";"קשר הפוך חזק");"קשר חלש או לא")' class='formula'>

אם המקדם המתקבל גבוה מ-0,7, אז מתאים יותר להשתמש בפונקציית הרגרסיה הליניארית. בדוגמה זו, אנו עושים:

עכשיו אנחנו בונים גרף:

אנו מיישמים את המשוואה הזו כדי לקבוע את מספר הצפיות ב-200, 500 ו-1000 שיתופים: =9,2937*D4-206,12. אנו מקבלים את התוצאות הבאות:

פונקציה תַחֲזִית מאפשר לך לקבוע את מספר הצפיות כרגע, אם היו, למשל, מאתיים וחמישים פוסטים חוזרים. אנו מיישמים: 0,7;PREDICTION(D7;B3:B8;A3:A8);"הערכים אינם קשורים")' class='formula'>. אנו מקבלים את התוצאות הבאות:

תכונות של שימוש בפונקציית CORREL באקסל

לפונקציה זו יש את התכונות הבאות:

1. תאים ריקים אינם נלקחים בחשבון.
2. תאים המכילים מידע בוליאני וסוג טקסט אינם נלקחים בחשבון.
3. שלילה כפולה "-" משמשת כדי להסביר ערכים לוגיים בצורה של מספרים.
4. מספר התאים במערכים הנחקרים חייב להתאים, אחרת ההודעה #N/A תוצג.

הערכת המובהקות הסטטיסטית של מקדם המתאם

כאשר בודקים את המשמעות של מקדם מתאם, השערת האפס היא שלמדד יש ערך של 0, בעוד שהחלופה לא. הנוסחה הבאה משמשת לאימות:

סיכום

ניתוח מתאם בגיליון אלקטרוני הוא תהליך פשוט ואוטומטי. כדי לבצע את זה, אתה רק צריך לדעת היכן נמצאים הכלים הדרושים וכיצד להפעיל אותם דרך הגדרות התוכנית.