Aggregate function עם תנאי על אקסל / MaxIF

By | 1 בפברואר 2016

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

לכל מי שכותב SQL, ברור מאליו שאפשר לעשות SUM, MIN, MAX רק על חלק מהרשומות. בשביל זה בדיוק, מתחת ל- SELECT יש WHERE. אבל, אם אוסף הרשומות שלנו נמצא על אקסל, כיצד נעשה את החיבור או הבאת הממוצע, או המקסימום רק על חלק מהרשומות?

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

  1. sumif / averageif מאפשרות הכנסת תנאי פשוט יחסית. 
  2. לא לכל הפונקציות יש חלופה המאפשרת סינון רשומות לפני ביצוע הפונקציה. למשל אין maxif.

לאקסל יש פתרון וותיק לעניין זה אך נוכחתי לדעת שרבים אינם מכירים אותו. לא מצאתי שם טוב יותר ולכן אסתפק בשם פתרון ctrl+shift+enter או בקיצור CSE (באזז וורד שהמצאתי כרגע אך יש לו סיכוי טוב לכבוד את עולם ה- IT).

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

בצילום המסך שלמטה, עמודה A מכילה תאריכי מסחר במט"ח ועמודה B אפשר לראות את שער הדולר בכל תאריך מסחר. תא D2 מייצג את התאריך עבורו נרצה את שער הדולר האחרון, עמודה E2 מכילה את התאריך האחרון בו היה מסחר ולבסוף, תא F2 מייצג את שער הדולר הידוע בתאריך שביקשנו.

maxif1

 

הפונקציה MAX לעיל, תא E2, היא המעניינת בגיליון זה. באמצעות פונקציה זו מגיע תאריך המסחר האחרון. כלומר, חיפשנו את התאריך 3/1/2016. בתאריך זה אין שער דולר כלל, לכן הגיע התאריך האחרון בו יש שער דולר ידוע, ה- 31/12/2015.

בתוך הפונקציה MAX אנו מחפשים ערך מרבי אשר עונה על התנאי שקבוצת הנתונים (data set) בתוכה מבוצע החיפוש היא זו המקיימת את התנאי שווה או קטנה מ- 3/1/2016. כלומר הפונקציה מחפשת בכל התאריכים (A2:A23), מסננת רק את אלו שקטנים או שווים ל- 3/1/2016 (A2:A4) ומתוכן מוציאה תאריך גדול ביותר (31/12/2015).

ממש כאילו היינו כותבים:

select [תאריך] from a2:a23 where [תאריך]<= D2

הפונקציה הייתה פועלת באותו אופן בדיוק גם אם התאריכים לא היו ממוינים, כלומר אין כאן הליכה לאורך התאריכים ומציאת ערך קרוב ביותר כמו שעושה VLOOKUP במצב TRUE (חפש ערך קרוב ביותר) אלא, יש בפועל מעבר על כל הנתונים כמו SELECT.

איך מבוצע הקסם?

לאחר שכתבתי את הפונקציה המתוארת לעיל בתא E2, במקום לחיצה על ENTER או על אחד החיצים כמקובל באקסל בסוף עריכת תא, לחצתי על CSE – Ctrl+Shif+Enter. במצב זה אקסל מוסיף בעצמו סוגריים מסולסלות מסביב לפונקציה:

MAxIf2

ואקסל מתייחס לביטוי ממש כמו שאילתא על בסיס נתונים או בשפת האקסל – Array Formula.

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

שתי דוגמאות לשימושים נוספים:

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

חושבים על דוגמאות נוספות עבורן אפשר להשתמש בזה?

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


Optin Architect

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

excel_people_graph

 

 

Share

כתיבת תגובה

האימייל לא יוצג באתר.