שימוש ב- Analysis ToolPak כדי לבצע ניתוח נתונים מורכבים

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

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

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

  1. לחץ על הכרטיסיה קובץ, לחץ על אפשרויות ולאחר מכן לחץ על הקטגוריה תוספות.

    אם אתה משתמש ב- Excel 2007, לחץ על לחצן Microsoft Office תמונת לחצן Office ולאחר מכן לחץ על אפשרויות Excel.

  2. בתיבה נהל, בחר בתוספות של Excel‏ ולאחר מכן לחץ על עבור.

    אם אתה משתמש ב- Excel עבור Mac, בתפריט הקובץ עבור אל כלים > תוספות של Excel.

  3. בתיבה תוספות, בחר בתיבת הסימון Analysis ToolPak ולחץ על אישור.

    • אם Analysis ToolPak אינו מופיע בתיבה תוספות זמינות, לחץ על עיון כדי לאתר אותו.

    • אם אתה מקבל הודעה ש- Analysis ToolPak אינו מותקן כרגע במחשב שלך, לחץ על כן כדי להתקין אותו.

הערה: כדי לכלול פונקציות Visual Basic for Application (VBA)‎ עבור Analysis ToolPak, באפשרותך לטעון את תוספת Analysis ToolPak - VBA באותו אופן שבו טענת את Analysis ToolPak. בתיבה תוספות זמינות, בחר בתיבת הסימון Analysis ToolPak - VBA.

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

Anova: גורם יחיד

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

Anova: דו-גורמי עם שכפול

כלי ניתוח זה יעיל כאשר נתונים מסווגים לפי שני ממדים שונים. למשל, בניסיון שמטרתו למדוד את גובהם של צמחים, ייתכן שהצמחים מקבלים סוגים שונים של דשן (לדוגמה, A‏, B‏, C) וכן גדלים בטמפרטורות שונות (לדוגמה, נמוכה, גבוהה). עבור כל אחד מששת הזוגות האפשריים של {מדשן, טמפרטורה}, יש לנו מספר שווה של תצפיות לגבי גובה הצמח. באמצעות כלי Anova זה, באפשרותנו לבחון:

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

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

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

הגדרת טווח הקלט עבור כלי anova

Anova: דו-גורמי ללא שכפול

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

פונקציות גיליון העבודה CORREL ו- PEARSON מחשבות את מקדם המתאם בין שני משתני מידה כאשר המידות בכל משתנה מובחנות עבור כל אחד מ- N הנושאים. (כל תצפית חסרה עבור כל נושא גורמת להתעלמות מאותו נושא בניתוח.) כלי הניתוח 'מתאם' שימושי במיוחד כאשר יש יותר משני משתני מידה עבור כל אחד מ-N הנושאים. הכלי מספק טבלת פלט, מטריצת התאמה, המציגה את הערך CORREL (או PEARSON) החל על כל זוג אפשרי של משתני מידה.

מקדם המתאם, בדומה לשונות המשותפת, מודד את המידה שבה שני משתני מידה "משתנים יחד". להבדיל משונות משותפת, מקדם המתאם כפוף לקנה מידה כך שהערך שלו אינו תלוי ביחידות שבהן שני משתני המידה מבוטאים. (לדוגמה, אם שני משתני המדידה הם עובי וגובה, ערך מקדם המתאם לא ישתנה אם העובי מומר מפאונד לקילוגרמים.) הערך של כל מקדם מתאם חייב להיות בטווח שבין ‎-1 לבין ‎+1.

ניתן להשתמש בכלי ניתוח המתאם כדי לבחון כל זוג של משתני מידה במטרה לבדוק אם שני משתני המידה נוטים לנוע יחדיו — כלומר, אם ערכים גדולים של משתנה אחד נוטים להשתייך לערכים גדולים של השני (התאמה חיובית), אם ערכים קטנים של משתנה אחד נוטים להשתייך לערכים גדולים של השני (התאמה שלילית) או אם ערכים של שני המשתנים נוטים שלא להשתייך (התאמה קרובה ל- 0 (אפס)).

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

כלי השונות המשותפת מחשב את הערך של פונקציית גיליון העבודה COVARIANCE.P עבור כל זוג של משתני מידה. (שימוש ישיר בפונקציה COVARIANCE.P במקום בכלי השונות המשותפת הוא חלופה סבירה כאשר יש רק שני משתני מידה, כלומר, N=2.) הערך באלכסון של טבלת הפלט של כלי השונות המשותפת בשורה i, עמודה i הוא השונות המשותפת של משתנה המידה i-th עם עצמו. זוהי רק שונות האוכלוסיה עבור משתנה זה, כפי שמחשבת פונקציית גיליון העבודה VAR.P.

באפשרותך להשתמש בכלי השונות המשותפת כדי לבחון כל זוג משתני מידה במטרה לבדוק אם שני משתני המידה נוטים לנוע יחד — כלומר, אם ערכים גדולים של משתנה אחד נוטים להיות משויכים לערכים גדולים של השני (שונות משותפת חיובית), אם ערכים קטנים של משתנה אחד נוטים להיות משויכים לערכים גדולים של השני (שונות משותפת שלילית) או אם ערכים של שני המשתנים נוטים שלא להיות משויכים (שונות משותפת קרובה ל- 0 (אפס)).

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

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

הערה: ערכים של 0.2 עד 0.3 מהווים קבועים הגיוניים להחלקה. ערכים אלה מציינים שיש להתאים את התחזית הנוכחית ב- 20 עד 30 אחוזים עבור השגיאה בתחזית הקודמת. קבועים גדולים יותר מפיקים תגובה מהירה יותר, אך עשויים לגרום להשלכות לא יציבות. קבועים קטנים יותר עשויים לגרום להשהיות ארוכות עבור ערכי תחזית.

כלי הניתוח F-Test Two-Sample for Variances מבצע בדיקת F של שני מדגמים כדי להשוות את השונות של שתי אוכלוסיות.

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

הכלי מחשב את הערך f של F-statistic (או F-ratio). ערך f הקרוב ל- 1 מספק הוכחה לכך שהשונויות באוכלוסייה המשמשות כבסיס שוות. בטבלת הפלט, אם f < 1,‏ “P(F <= f) one-tail” מספק את ההסתברות של צפייה בערך F-statistic נמוך מ-f כאשר השונויות באוכלוסייה שוות ו-“F Critical one-tail” מספק את הערך הקריטי הקטן מ- 1 עבור רמת המובהקות שנבחרה, Alpha. אם f > 1,‏ “P(F <= f) one-tail” מספק את ההסתברות של צפייה בערך F-statistic גבוה מ-f כאשר השונויות באוכלוסייה שוות, ו-“F Critical one-tail” מספק את הערך הקריטי הגדול מ- 1 עבור Alpha.

כלי הניתוח Fourier Analysis פותר בעיות במערכות ליניאריות ומנתח נתונים תקופתיים באמצעות שיטת Fast Fourier Transform (FFT)‎ להמרת נתונים. כלי זה גם תומך בהמרות הפוכות, בהן ההופכי של נתונים שהומרו מחזיר את הנתונים המקוריים.

טווחי קלט ופלט עבור ניתוח Fourier

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

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

עצה: ב- Excel 2016, באפשרותך ליצור היסטוגרמה או תרשים Pareto.

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

נוסחה לחישוב ממוצעים נעים

כאשר:

  • N הוא מספר התקופות הקודמות שיש לכלול בממוצע הנע

  • A j הוא הערך הממשי בזמן j

  • F j הוא הערך החזוי בזמן j

כלי הניתוח Random Number Generation ממלא טווח במספרים אקראיים עצמאיים הנלקחים מאחת ממספר התפלגויות. ניתן לאפיין את הנושאים באוכלוסיה באמצעות התפלגות הסתברות. לדוגמה, ניתן להשתמש בהתפלגות רגילה לאפיון האוכלוסיה של גובה של פרטים, או להשתמש בהתפלגות Bernoulli של שתי תוצאות אפשריות לאפיון האוכלוסיה של תוצאות הטלת מטבע.

כלי הניתוח Rank and Percentile מפיק טבלה הכוללת את הדירוג הסודר ואת הדירוג באחוזים של כל ערך בערכת נתונים. באפשרותך לנתח את המעמד היחסי של ערכים בערכת נתונים. כלי זה משתמש בפונקציות גליון העבודה RANK.EQ וPERCENTRANK.INC. אם ברצונך להסביר ערכים מסידרת ערכים, השתמש בפונקציה RANK.EQ, אשר מתייחסת לערכים מסידרת ערכים כבעלי אותו דירוג, או בפונקציה RANK.AVG, המחזירה את ממוצע הדירוג עבור הערכים מסידרת ערכים.

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

הכלי Regression משתמש בפונקציית גיליון העבודה LINEST.

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

כלי הניתוח Two-Sample t-Test בודקים את השוויון של ממוצעי האוכלוסיה העומדים בבסיס כל מדגם. שלושת הכלים משתמשים בהנחות שונות: שהשונויות של האוכלוסיה הן שוות, שהשונויות של האוכלוסיה אינן שוות וששני המדגמים מייצגים תצפיות של לפני טיפול ולאחר טיפול באותם נושאים.

עבור שלושת הכלים שלהלן, ערך מסוג t-Statistic‏, t, מחושב ומוצג כ- "t Stat" בטבלאות הפלט. בהתאם לנתונים, הערך t יכול להיות שלילי או חיובי. בהנחה של ממוצעי אוכלוסיה שווים בבסיס, אם t < 0‏, ‎"P(T <= t) one-tail"‎ נותן את ההסתברות שיאובחן ערך מסוג t-Statistic, שהינו שלילי יותר מ- t‏. אם t >=0‏, ‎"P(T <= t) one-tail"‎ נותן את ההסתברות שיאובחן ערך מסוג t-Statistic, שהינו חיובי יותר מאשר t‏. "t Critical one-tail" נותן את ערך החיתוך, כך שההסתברות של אבחנה בערך מסוג t-Statistic שהינו גדול או שווה ל- "t Critical one-tail" היא Alpha.

"P(T <= t) two-tail" נותן את ההסתברות שיאובחן ערך מסוג t-Statistic שהינו גדול יותר בערך מוחלט מאשר t‏. "P Critical two-tail" נותן את ערך החיתוך, כך שההסתברות של ערך t-Statistic מאובחן שהינו גדול יותר בערך מוחלט מאשר "P Critical two-tail" היא Alpha.

מבחן t: שני מדגמים מזווגים עבור ממוצעים

באפשרותך להשתמש במבחן מדגמים מזווגים כאשר מתקיים זיווג טבעי של תצפיות במדגמים, כגון בעת שקבוצת מדגם נבחנת פעמיים — לפני ואחרי ניסוי. כלי ניתוח זה והנוסחה שלו מבצעים Two-Sample Student's t-Test מזווג כדי לקבוע אם יש סבירות שתצפיות שנלקחו לפני טיפול ותצפיות שנלקחו לאחר טיפול הגיעו מהתפלגויות בעלות ממוצעי אוכלוסיה שווים. צורת t-Test זו אינה מניחה כי השונויות של שתי האוכלוסיות שוות.

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

נוסחה לחישוב שונות במאגר

מבחן t: שני מדגמים בהנחת שוויון שונויות

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

מבחן t: שני מדגמים בהנחת אי-שוויון שונויות

כלי ניתוח זה מבצע t-Test עם שני מדגמים של סטודנט. צורה זו של מבחן T מניחה ששתי קבוצות הנתונים נלקחו מהתפלגויות עם שונויות שאינן שוות. הוא מוכר בשם מבחן T של שונות לא שווה עם שני מדגמים (הטרוסדסטי). בדומה למקרה של Equal Variances שלעיל, באפשרותך להשתמש במבחן T זה כדי לקבוע אם סביר להניח ששתי הדגימות הגיעו מהתפלגויות עם ממוצעי אוכלוסייה שווים. השתמש במבחן זה כאשר קיימים נושאים מובחנים בשתי הדגימות. השתמש במבחן Paired, המתואר להלן, כאשר קיימת קבוצה אחת של נושאים ושתי הדגימות מייצגות מדידות עבור כל נושא לפני ואחרי טיפול.

הנוסחה שלהלן משמשת לקביעת הערך הסטטיסטי t.

נוסחה לחישוב הערך t

הנוסחה שלהלן משמשת לחישוב דרגות החופש, df. מאחר שתוצאת החישוב אינה מספר שלם בדרך כלל, הערך של df מעוגל למספר השלם הקרוב ביותר כדי לקבל ערך קריטי מטבלת t. פונקציית גליון העבודה T.TEST ב- Excel משתמשת בערך df המחושב מבלי לעגל אותו, מאחר שניתן לחשב ערך עבור T.TEST עם df שאינו מספר שלם. עקב גישות שונות אלה לקביעת דרגות החופש, התוצאות של T.TEST ושל כלי t-Test זה יהיו שונות במקרה של שונות לא שווה (Unequal Variances).

נוסחה להערכת דרגות חופש

כלי הניתוח z-Test: Two Sample for Means מבצע z-Test של שני מדגמים עבור ממוצעים בעלי שונות ידועה. כלי זה משמש לבדיקת ההיפותזה הריקה שאין הבדל בין שני ממוצעי אוכלוסיה נגד היפותזה חלופית חד-צדדית או דו-צדדית. אם השונות אינה ידועה, יש להשתמש במקום זאת בפונקציית גיליון העבודה Z.TEST.

בעת שימוש בכלי z-Test, יש להקפיד להבין את הפלט. "P(Z <= z) one-tail" הוא, למעשה, P(Z >= ABS(z))‎, ההסתברות של z-value הרחק מ- 0 באותו כיוון של ערך z המאובחן כאשר אין הבדל בין ממוצעי האוכלוסיה. "P(Z <= z) two-tail" הוא, למעשה, ‎P(Z >= ABS(z) or Z <= -ABS(z))‎, ההסתברות של z-value הרחק מ- 0 בכל כיוון מאשר ערך z המאובחן כאשר אין הבדל בין ממוצעי האוכלוסיה. התוצאה הדו-זנבית היא רק התוצאה החד-זנבית כאשר היא מוכפלת פי 2. ניתן להשתמש בכלי z-Test גם עבור מקרים בהם ההיפותזה הריקה היא שקיים ערך ספציפי שאינו אפס עבור ההבדל בין שני ממוצעי האוכלוסיה. לדוגמה, ניתן להשתמש בבדיקה זו כדי לקבוע את הבדלי הביצועים בין שני דגמי מכוניות.

זקוק לעזרה נוספת?

ניתן לשאול תמיד מומחה ב- Excel Tech Community, לקבל תמיכה בקהילת Answers או להציע תכונה חדשה או שיפור ב- Excel User Voice.

למידע נוסף

יצירת היסטוגרמה ב- Excel 2016

יצירת תרשים Pareto ב- Excel 2016

צפה בסרטון וידאו כדי להתקין ולהפעיל את התוספות Analysis Toolpak ו- Solver

פונקציות הנדסה (הפניה)

פונקציות סטטיסטיות (הפניה)

מבט כולל על נוסחאות ב- Excel

כיצד להימנע מנוסחאות שגויות

חיפוש ותיקון שגיאות בנוסחאות

קיצורי מקשים ומקשים ייעודיים של Excel

פונקציות Excel (בסדר אלפביתי)

פונקציות Excel (לפי קטגוריה)

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×