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

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

במאמר זה נעמיק בפונקציות החיפוש.

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

זמן קריאה משוער: 18 דקות

פונקציות חיפוש

MAX

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

תחביר

= MAX(number1, [number2], …)

נושאים

• number1:  מספר, תא המכיל מספר, או טווח תאים המכילים מספרים שמהם ברצונך לקבל את המספר הגדול ביותר.
• [number2]:  מספר הוא תא שמכיל מספר או טווח של תאים המכילים מספרים שמהם ברצונך לקבל את המספר הגדול ביותר.

לדוגמה

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

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

הערה: אתה יכול גם להזין מספר באמצעות מרכאות כפולות.

בדוגמה הבאה, התייחסנו לטווח והתוצאה החזירה 1861 כערך הגדול ביותר. אתה יכול גם להתייחס למערך.

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

MAXA

פונקציית האקסל Maxa זה מאוד דומה ל פונקציית אקסל Max.

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

הפונקציה Max מתעלם מערכי לוגיקה וטקסט בזמן הפונקציה Maxa הערך הלוגי נחשב TRUE בתור 1, הערך הלוגי FALSE כ-0 וערכי הטקסט כ-0.

הפונקציה MAXA Excel מחזיר את הערך הגדול ביותר מקבוצה מסופקת של ערכים מספריים, תוך ספירת הטקסט והערך הלוגי FALSE כערך של 0 וספירת הערך הלוגי TRUE כערך של 1.

תחביר

= MAXA(number1, [number2], …)

נושאים

• number1:  מספר (או מערכים של ערכים מספריים), תא המכיל מספר, או טווח של תאים המכילים מספרים שמהם ברצונך לקבל את המספר הגדול ביותר.
• [number2]:  מספר הוא תא המכיל מספר (או מערכים של ערכים מספריים) או טווח של תאים המכילים מספרים שמהם ברצונך לקבל את המספר הגדול ביותר.

בגירסאות הנוכחיות של Excel (Excel 2007 ואילך), ניתן לספק עד 255 ארגומנטים מספריים לפונקציה Maxa, אך ב-Excel 2003 הפונקציה יכולה לקבל עד 30 ארגומנטים מספריים.

אסמפי

אסמפיו 1

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

אסמפיו 2

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

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

דוגמאות נוספות לפונקציה Excel Maxa מסופקים על אתר Microsoft Office .

שגיאת פונקציה MAXA

אם אתה מקבל שגיאה מהפונקציה Maxa של Excel, כנראה שזו השגיאה #VALORE!: מתרחש אם ערכים מסופקים ישירות לפונקציה Maxa הם אינם מספריים.

MAXIFS

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

תחביר

= MAXIFS( max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

נושאים

• max_range:  מערך של ערכים מספריים (או טווח של תאים המכילים ערכים מספריים), שממנו ברצונך להחזיר את הערך המקסימלי אם הקריטריונים מתקיימים.
• criteria_range1:  מערך של ערכים (או טווח של תאים המכילים ערכים) לבדיקה מולם criteria1 .(הכל המערך הזה חייב להיות באותו אורך כמו max_range ).
• criteria1: התנאי לבדיקה ביחס לערכים ב criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: מערכים אופציונליים נוספים של ערכים לבדיקה והתנאים המתאימים לבדיקה.

הפונקציה Maxifs יכול להתמודד עם עד 126 זוגות נושאים criteria_range e criteria.

כל אחד מהקריטריונים המסופקים יכול להיות:

• ערך מספרי (שיכול להיות מספר שלם, עשרוני, תאריך, שעה או ערך לוגי) (לדוגמה, 10, 01/01/2017, TRUE)

או

• מחרוזת טקסט (למשל "שם", "Mercoleשֶׁל")

או

• ביטוי (לדוגמה ">1", "<>0").

ניי criteria הקשור לטקסט אתה יכול להשתמש בתווים כלליים:

• ? כדי להתאים לכל דמות בודדת
• * כדי להתאים לכל רצף של תווים.

אם criteria הוא מחרוזת טקסט או ביטוי, זה חייב להיות מסופק לפונקציה Maxifs במרכאות.

הפונקציה Maxifs זה לא רגיש לאותיות גדולות. לכן, למשל, כאשר משווים ערכים ב criteria_range עם i criteria, מחרוזות הטקסט "TEXT"דואר"text” ייחשב שווה.

הפונקציה Maxifs הוא הוצג לראשונה ב-Excel 2019 ולכן אינו זמין בגרסאות קודמות של Excel.

אסמפי

הגיליון האלקטרוני למטה מציג נתוני מכירות רבעוניים עבור 3 נציגי מכירות.

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

בואו נסתכל על הדוגמאות הבאות.

אסמפיו 1

כדי למצוא את נתון המכירות המקסימלי במהלך הרבעון הראשון:

=MAXIFS( D2:D13, A2:A13, 1 )

מה שנותן את התוצאה $ 456.000.

בדוגמה זו, האקסל Maxifs מזהה שורות שבהן הערך בעמודה A שווה ל-1 ומחזיר את הערך המקסימלי מהערכים המתאימים בעמודה D.

כלומר, הפונקציה מוצאת את המקסימום של הערכים $223.000, $125.000 ו-$456.000 (מהתאים D2, D3 ו-D4).

אסמפיו 2

שוב, באמצעות גיליון הנתונים שלמעלה, נוכל להשתמש גם בפונקציית Maxifs כדי למצוא את נתון המכירות המקסימלי של "ג'ף", במהלך הרבעונים 3 ו-4:

=MAXIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

נוסחה זו מחזירה את התוצאה $ 310.000 .

בדוגמה זו, האקסל Maxifs מזהה את הקווים שבהם:

• הערך בעמודה A גדול מ-2

E

• הערך בעמודה C שווה "ג'ף"

ומחזירה את המקסימום של הערכים המתאימים בעמודה D.

כלומר, נוסחה זו מוצאת את המקסימום של הערכים $310.000 ו-$261.000 (מתאי D8 ו-D11).

התייעצו עם אתר Microsoft Office לפרטים נוספים על דוגמאות פונקציות Excel Maxifs.

שגיאת פונקציה MAXIFS

אם אתה מקבל שגיאה מפונקציית Excel Maxifs, סביר להניח שזה יהיה אחד מהבאים:

#VALUE!: בודק אם מערכים max_range e criteria_range המסופק לא לכולם אותו אורך.

@NAME?: מתרחש אם אתה משתמש בגרסה ישנה יותר של Excel (לפני 2019), שאינה תומכת בתכונה Maxifs.

MIN

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

תחביר

= MIN(number1, [number2], …)

נושאים

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

לדוגמה

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

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

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

MINA

פונקציית האקסל MINA זה מאוד דומה ל פונקציית אקסל MIN.

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

במקרה זה הפונקציה MIN מתעלם מערכי לוגיקה וטקסט בזמן הפונקציה MINA הערך הלוגי נחשב TRUE בתור 1, הערך הלוגי FALSE כ-0 וערכי הטקסט כ-0.

הפונקציה MINA Excel מחזיר את הערך הקטן ביותר מקבוצה מסופקת של ערכים מספריים, תוך ספירת הטקסט והערך הלוגי FALSE כערך של 0 וספירת הערך הלוגי TRUE כערך של 1.

תחביר

= MINA( number1, [number2], ... )

נושאים

• number1:  מספר, תא המכיל מספר, או טווח של תאים (או מערכים של ערכים מספריים) המכילים מספרים שמהם ברצונך לקבל את המספר הקטן ביותר.
• [number2]:  מספר, תא המכיל מספר, או טווח של תאים (או מערכים של ערכים מספריים) המכילים מספרים שמהם ברצונך לקבל את המספר הקטן ביותר.

בגירסאות הנוכחיות של Excel (Excel 2007 ואילך), אתה יכול לספק עד 255 ארגומנטים מספריים לפונקציה MINA, אבל ב-Excel 2003 הפונקציה יכולה לקבל רק עד 30 ארגומנטים מספריים.

אסמפי

אסמפיו 1

התא B1 בגיליון האלקטרוני הבא מוצגת פונקציית Excel MINA, המשמשת לאחזור הערך הקטן ביותר מקבוצת הערכים בתאים A1-A5.

אסמפיו 2

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

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

דוגמאות נוספות לפונקציית Excel MINA מסופקים על אתר Microsoft Office .

שגיאת פונקציה MINA

אם אתה מקבל שגיאה מהפונקציה MINA של Excel, כנראה שזו השגיאה #VALORE!. מתרחש אם הערכים המסופקים לפונקציית MINA אינם מספריים.

MINIFS

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

תחביר

= MINIFS( min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

נושאים

• min_range:  מערך של ערכים מספריים (או טווח של תאים המכילים ערכים מספריים), שממנו ברצונך להחזיר את הערך המקסימלי אם הקריטריונים מתקיימים.
• criteria_range1:  מערך של ערכים (או טווח של תאים המכילים ערכים) לבדיקה מולם criteria1 .(מערך זה חייב להיות באותו אורך כמו min_range ).
• criteria1: התנאי לבדיקה ביחס לערכים ב criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: מערכים אופציונליים נוספים של ערכים לבדיקה והתנאים המתאימים לבדיקה.

הפונקציה Minifs יכול להתמודד עם עד 126 זוגות נושאים criteria_range e criteria.

כל אחד מהקריטריונים המסופקים יכול להיות:

• ערך מספרי (שיכול להיות מספר שלם, עשרוני, תאריך, שעה או ערך לוגי) (לדוגמה, 10, 01/01/2017, TRUE)

או

• מחרוזת טקסט (למשל "שם", "Mercoleשֶׁל")

או

• ביטוי (לדוגמה ">1", "<>0").

ניי criteria הקשור לטקסט אתה יכול להשתמש בתווים כלליים:

• ? כדי להתאים לכל דמות בודדת
• * כדי להתאים לכל רצף של תווים.

אם criteria הוא מחרוזת טקסט או ביטוי, זה חייב להיות מסופק לפונקציה Minifs במרכאות.

הפונקציה Minifs זה לא רגיש לאותיות גדולות. לכן, למשל, כאשר משווים ערכים ב criteria_range עם i criteria, מחרוזות הטקסט "TEXT" ו"טקסט" ייחשבו לאותו דבר.

הפונקציה Minifs הוא הוצג לראשונה ב-Excel 2019 ולכן אינו זמין בגרסאות קודמות של Excel.

אסמפי

הגיליון האלקטרוני למטה מציג נתוני מכירות רבעוניים עבור 3 מוכרים.

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

זה מוצג בדוגמאות הבאות.

אסמפיו 1

כדי למצוא את נתון המכירות המינימלי במהלך הרבעון הראשון:

=MINIFS( D2:D13, A2:A13, 1 )

מה שנותן את התוצאה $ 125.000 .

בדוגמה זו, האקסל Minifs מזהה שורות שבהן הערך בעמודה A שווה ל-1 ומחזיר את הערך המינימלי מהערכים המתאימים בעמודה D.

כלומר, הפונקציה מוצאת את המינימום של הערכים $223.000, $125.000 ו-$456.000 (מהתאים D2, D3 ו-D4).

אסמפיו 2

שוב, באמצעות גיליון הנתונים שלמעלה, נוכל גם להשתמש בפונקציה Minifs כדי למצוא את נתון המכירות המינימלי של "ג'ף" במהלך הרבעונים 3 ו-4:

=MINIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

נוסחה זו מחזירה את התוצאה $261.000 .

בדוגמה זו, האקסל Minifs מזהה את הקווים שבהם:

• הערך בעמודה A גדול מ-2

E

• הערך בעמודה C שווה "ג'ף"

ומחזירה את המינימום של הערכים המתאימים בעמודה D.

כלומר, נוסחה זו מוצאת את המינימום של הערכים $310.000 ו-$261.000 (מתאי D8 ו-D11).

לדוגמאות נוספות של פונקציית Excel Minifs, התייעץ עם אתר Microsoft Office .

שגיאת פונקציה MINIFS

אם אתה מקבל שגיאה מפונקציית Excel Minifs, סביר להניח שזו אחת מהסיבות הבאות:

• #VALORE! -בודק אם מערכים min_range e criteria_range המסופק לא לכולם אותו אורך.
• #NOME? - מתרחש אם אתה משתמש בגרסה ישנה יותר של Excel (לפני 2019), שאינה תומכת בתכונה Minifs.

LARGE

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

תחביר

= LARGE( array, k )

נושאים

• מערך - מערך של ערכים מספריים לחיפוש הערך ה-k' בגודלו.
• K - המדד, כלומר הפונקציה מחזירה את הערך ה-k בגודלוarray פורניטו.

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

לדוגמה

הגיליון האלקטרוני הבא מציג את פונקציית Excel Large, משמש כדי לאחזר את הערכים ה-1, ה-2, ה-3, ה-4 וה-5 בגודלם מקבוצת הערכים בתאים A1-A5.

כמה מחשבות על הגיליון האלקטרוני לדוגמה למעלה:

• בתא B1, כאשר k מוגדר ל-1, הפונקציה Large מבצע את אותה פעולה כמו פונקציית Excel מקסימום ;
• בתא B5, כאשר k מוגדר ל-5 (מספר הערכים במערך המסופק), הפונקציה Large מבצעת את אותה פעולה כמו פונקציית Excel Min .

פרטים נוספים ודוגמאות של פונקציית Excel Large ניתן למצוא ב- אתר Microsoft Office .

שגיאת פונקציה LARGE

אם האקסל Large מחזיר שגיאה, סביר להניח שזו אחת מהאפשרויות הבאות:

• #NUM! – מתרחש אם:
  • הערך המסופק של k קטן מ-1 או גדול ממספר הערכים במערך שסופק
    או
    L 'array בתנאי ריק.
• #VALUE! – מתרחש אם ה-k שסופק אינו מספרי.

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

ניתן להשיג את הפתרון על ידי המרת כל ערכי המערך לערכים מספריים. 

SMALL

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

תחביר

= SMALL( array, k )

נושאים

• array - מערך של ערכים מספריים לחיפוש הערך ה-k' בגודלו.
• K - המדד, כלומר הפונקציה מחזירה את הערך ה-k בגודלוarray פורניטו.

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

לדוגמה

הגיליון האלקטרוני הבא מציג את פונקציית Excel Small, משמש כדי לאחזר את הערכים ה-1, ה-2, ה-3, ה-4 וה-5 הקטנים ביותר מקבוצת הערכים בתאים A1-A5.

בדוגמה יש צורך לשקול כי::

• בתא B1, שבו k מוגדר ל-1, הפונקציה Small מבצע את אותה פעולה כמו פונקציית Excel Min ;
• בתא B5, כאשר k מוגדר ל-5 (מספר הערכים ב-array מסופק ), הפונקציה Small מבצע את אותה פעולה כמו פונקציית המקס של אקסל .

פרטים נוספים ודוגמאות של פונקציית האקסל Small מסופקים על אתר Microsoft Office .

שגיאת פונקציה SMALL

אם האקסל SMALL מחזיר שגיאה, סביר להניח שזו אחת מהאפשרויות הבאות:

• #NUM! – מתרחש אם:
  • הערך המסופק של k קטן מ-1 או גדול ממספר הערכים במערך שסופק
    או
    המערך שסופק ריק.
• #VALUE! – מתרחש אם ה-k שסופק אינו מספרי.

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

ניתן להגיע לפתרון על ידי המרת כל הערכים שלarray בערכים מספריים. 

קריאות קשורות

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

Ercole Palmeri