החזרת האחרון בתור - גוגל שיטס

מי שמתעסק בניתוח מידע באופן קבוע, בוודאי שאל את עצמו אחת מהשאלות הבאות:

- כיצד להחזיר תמיד את השורה האחרונה בטבלה, או מספר קבוע של שורות אחרונות ?

- כיצד לקבל תמיד את שלושת השורות בעלות הערך הכי גבוה/נמוך במערך?

אם שאלות אלו רלוונטיות... הכירו את הפונקציה:

=ARRAY_CONSTRAIN()

הפונקציה ARRAY_CONSTRAIN מחזירה מספר שורות מסויים, או מספר טורים מסויים מתוך מערך,

לדוגמה:

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

=ARRAY_CONSTRAIN(R1:W12,3,3)

כיצד ניתן לקבל את השורות שאנחנו רוצים ולא רק את השורות הראשונות?

*** באקסל הפונקציה TAKE עושה את אותו הדבר רק בצורה קלה יותר

דוגמה ראשונה:

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

זו הטבלה, עם תיאור השכר בעמודה F.

ראשית נעתיק את הכותרת ואז נשתמש בפונקציה SORT כך:

=sort(A2:F,6,0)

הנוסחה אומרת לנו על הטווח A2:F מיין את הנתונים לפי הטור השישי (F) והחזר נתונים מהגדול לקטן.

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

עכשיו נעטוף את הנוסחה הקודמת בפונקציהARRAY_CONSTRAIN כך:

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

=ARRAY_CONSTRAIN(SORT(A2:F,6,0),3,6)

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

---

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

נוסיף טור עזר שממספר את הטבלה לפי השורות כך:

=ArrayFormula(if(A2:A<>"",row(A2:A)-1,""))

הנוסחה אומרת אם יש שם בטור A החזר את מספר השורה, אם לא - החזר כלום.

(מאחר והתחלנו את הספירה משורה שניה והפונקציה ROW מחזירה לנו את המספר 2, נפחית ממנה 1 כדי להתחיל מהמספר 1)

מאחר ובינתיים יש טווחים ריקים אחרי שורה 50 נשתמש בפונקציה FILTER להחזיר את התאים המלאים בלבד כך:

=filter(A2:G,F2:F<>"")

הנוסחה אומרת לסנן את כל טווח A2:G הטבלה בתנאי שטור השכר (F) אינו ריק.

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

=SORT(filter(A2:G,F2:F<>""),7,0)

הנוסחה באדום היא הקודמת, ובשחור עוטפת אותה הנוסחה שממיינת את הנתונים לפי טור 7 (המספרים העוקבים שיצרנו בטור G)

הכל מסודר עכשיו נשאר רק להוסיף את ARRAY_CONSTRAIN כדי לקבל את שלושת השורות האחרונות וללא טור המספרים כך:

=ARRAY_CONSTRAIN(sort(filter(A2:G,F2:F<>""),7,0),3,6)

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

בהצלחה!!

---