Aggregate
פונקציית AGGREGATE ב-Excel היא כלי עוצמתי, שכמעט אף אחד לא מכיר…
זו פונקציה גמישה שמאפשרת לבצע חישובים נפוצים כמו SUM, MIN, AVERAGE, בפונקציה אחת,
אבל בניגוד לפונקציות הייעודיות, היא יודעת להתעלם משורות מוסתרות, מערכי שגיאה, מסיכומי ביניים ועוד.
למעשה, היא יכולה להחליף 19 פונקציות שונות, ולתת לכל אחת מהן ערך מוסף!
תחביר
= AGGREGATE (function num, options, range, [k])
מספר הפונקציה
לכל אחת מ-19 הפונקציות שנכללות בפונקציה AGGREGATE ישנו מספר:
| מספר | פונקציה |
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV.S |
| 8 | STDEV.P |
| 9 | SUM |
| 10 | VAR.S |
| 11 | VAR.P |
| 12 | MEDIAN |
| 13 | MODE.SNGL |
| 14 | LARGE |
| 15 | SMALL |
| 16 | PERCENTILE.INC |
| 17 | QUARTILE.INC |
| 18 | PERCENTILE.EXC |
| 19 | QUARTILE.EXC |
כך שאם ארצה לחשב ממוצע, לדוגמה, אבחר במספר 1, ואם ארצה לחשב סכום, אבחר במספר 9.
אפשרויות
הארגומנט השני של הפונקציה הוא הארגומנט המהפכני,
שמאפשר להתעלם בחישוב מערכים שונים כמו שורות מוסתרות, סיכומי ביניים וגורמים נוספים שקיימים בטבלת הנתונים שלנו, ומשנים את אופן החישוב:
תוכלו לבחור בכל אחת משמונה האפשרויות המוצעות, בהתאם לצורך.
טווח
זוהי הטבלה שעליה נערוך את החישובים.
K
ארגומנט אופציונלי, שמתאים לחלק מהפונקציות, כמו LARGE או SMALL, ואומר מהו הערך הסידורי שאותו אנחנו מחפשים,
למשל, אם בפונקציה LARGE, נבקש למצוא את הערך השלישי הגדול ביותר, במקום K נכתוב 3
אז עכשיו, בואו נראה מה יש לנו
טבלת נתונים שמכילה מידע ממגרש המכוניות שלי, כאשר עבור כל רשומה מידע רב לכל כלי רכב:
על מסד הנתונים הזה ארצה לערוך חישובים.
דוגמאות
אחת התכונות החשובות היא היכולת להתעלם משורות מוסתרות בחישובים, למשל – חישוב ממוצע על השורות הגלויות בלבד (בדומה ל SUBTOTAL, אבל משוכלל יותר)
אז בואו נראה
אתחיל להקליד את הפונקציה, ומיד תפתח לי רשימת אפשרויות לארגומנט הראשון. מכיוון שאני רוצה לחשב את הממוצע, אבחר בארגומנט הראשון. בפונקציה עצמה יופיע לי המספר 1:
בשלב השני אבחר מהם התנאים לחישוב:
במקרה שלי, אני רוצה להתעלם משורות מוסתרות, ולכן אבחר באפשרות החמישית:
בשלב השלישי אבחר בטווח שעליו אני מעוניינת לערוך את החישוב, וכך תיראה הפונקציה:
=AGGREGATE(1,5,Data[מחיר מכירה])
כעת נוכל לסנן את הטבלה, והתוצאה תשתנה בהתאם:
ובואו נראה דוגמה נוספת
נניח שבטבלת הנתונים יש לי סיכומי ביניים (במאמר מוסגר אגיד שסיכומי ביניים הם דבר שאסור שיהיה בטבלה תקנית, משום שהוא מונע ניתוח מידע. עוד על טבלה תקנית, במאמר הזה),
ואני רוצה לחשב את הסכום הכולל, ללא סיכומי הביניים. כך עושים זאת:
=AGGREGATE(9,0,Data[מחיר מכירה])
כלומר – בחרתי ב-9 כדי לסכום, בארגומנט השני בחרתי ב-0 כדי להתעלם מסיכומי הביניים, ולבסוף סימנתי את הטווח הרצוי, שהוא מחיר המכירה.
כעת החישוב יתעלם מסיכומי הביניים, ויסכום את הנתונים הגולמיים בלבד.
הערה חשובה – לא ניתן ליצור סיכומי ביניים על טבלה חכמה.