כיצד להשתמש בפונקציה AGGREGATE ב- Excel? | (עם דוגמאות)

פונקציה AGGREGATE ב- Excel

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

תחביר

ישנם שני תחביבים לנוסחת AGGREGATE:

  1. תחביר הפניה

= AGGREGATE (function_num, options, ref1, ref2, ref [3],…)

  1. תחביר מערך

= AGGREGATE (function_num, options, array, [k])

Function_num הוא מספר המציין פונקציה ספציפית בה אנו רוצים להשתמש, זהו מספר שבין 1-19

אפשרות: זהו גם ערך מספרי שנע בין 0 ל -7 וקובע אילו ערכים יש להתעלם בזמן חישובים

Ref1, ref2, ref [3]:  הוא הארגומנט תוך כדי שימוש בתחביר ההתייחסות, זהו ערך מספרי או ערכים עליהם אנו רוצים לבצע את החישוב, לפחות שני ארגומנטים נדרשים ארגומנטים למנוחה הם אופציונליים.

מערך: הוא מערך ערכים עליו אנו רוצים לבצע את הפעולה, הוא משמש בתחביר מערך של פונקציית AGGREGATE ב- Excel

K: הוא ארגומנט אופציונלי והוא ערך מספרי, הוא משמש כאשר משתמשים בפונקציה כמו LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC או QUARTILE.EXC ב- Excel.

דוגמאות

אתה יכול להוריד תבנית Excel זו לפונקציה AGGREGATE כאן - תבנית Excel לפונקציה AGGREGATE

דוגמה - מספר 1

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

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

בעמודה C כל הערכים ניתנים ולא נצטרך להתעלם מערכים כלשהם, לכן נבחר באפשרות 4 (התעלם מכלום)

ובחירת טווח הערכים C1: C8 כמערך של ערכים מספריים

מכיוון ש- ' k' הוא ארגומנט אופציונלי והוא משמש כאשר משתמשים בפונקציה כמו LARGE, SMALL ב- Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC או QUARTILE.EXC אך במקרה זה אנו מחשבים את הממוצע ולכן אנו ישמיט את הערך של k.

אז הערך הממוצע הוא

באופן דומה, לטווח D1: D8, שוב נבחר באפשרות 4.

לטווח E1: E8, תא E6 מכיל ערך שגיאה, אם נשתמש באותה נוסחה AGGREGATE נקבל שגיאה, אך כאשר נעשה שימוש באופציה מתאימה, AGGREGATE ב- Excel נותן את הממוצע של הערכים הנותרים תוך הזנחת השגיאה ערך ב- E6.

על מנת להתעלם מערכי השגיאה, יש לנו אפשרות 6.

באופן דומה, לטווח G1: G8 נשתמש באפשרות 6 (התעלם מערכי השגיאה)

כעת, לטווח H3 אם שמנו ערך 64 ונסתיר את השורה השלישית ונשתמש באפשרות 5, כדי להתעלם מהשורה הנסתרת, ה- AGGREGATE ב- Excel ניתן את הערך הממוצע לערכים מספריים גלויים בלבד.

פלט ללא הסתרת שורה 3

פלט לאחר הסתרת שורה 3

יישום הנוסחה AGGREGATE לפעולות אחרות, יש לנו

דוגמה - מספר 2

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

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

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

לכן, החלפת נוסחת SUM בפונקציה AGGREGATE בקוד אפשרות 5 (תוך התעלמות מהשורות והערכים הנסתרים) יש לנו,

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

סך ההכנסות שנוצרו עבור ערוץ ישיר:

סך ההכנסות שנוצרו לערוץ אורגני:

סך ההכנסות שנוצרו עבור ערוץ בתשלום:

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

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

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

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

לכן, במקרה זה, נשתמש באפשרות 7 שמתעלמת מערכי השגיאה ובמקביל מהשורות הנסתרות

דברים שיש לזכור

  • הפונקציה AGGREGATE אינה מזהה את הפונקציה _ ערך מספר גדול מ- 19 או פחות מ -1 ובדומה לכך עבור מספר אפשרות היא אינה מזהה את הערכים הגדולים מ- 7 ופחות מ- 1, אם אנו מספקים ערכים אחרים היא נותנת #VALUE ! שְׁגִיאָה
  • זה תמיד מקבל את הערך המספרי ומחזיר תמיד ערך מספרי כפלט
  • ל- AGGREGATE ב- Excel יש מגבלה; הוא מתעלם רק מהשורות הנסתרות אך אינו מתעלם מהעמודות הנסתרות.