תקציר: במדריך זה, תלמד כיצד להשתמש בנוסחת Excel כדי למצוא את המופע האחרון של פריט ברשימה.
לאחרונה עבדתי על סדר יום לפגישה.
יש לי רשימה באקסל עם רשימה של אנשים והתאריכים שהם היו "יושבי ראש".
אני גם צריך לדעת מתי אדם היה לאחרונה "יושב ראש הישיבה" עקב כפילות ברשימה (מה שאומר שאדם היה יושב ראש הישיבה מספר פעמים).
הסיבה לכך היא שעליי לוודא שהיושב-ראש האחרון לא יועבר מחדש.
אז החלטתי להשתמש בקסם של פונקציית Excel כדי לעשות את העבודה.
להלן התוצאה הסופית, אני יכול לבחור שם מהתפריט הנפתח וזה נותן לי את תאריך ההופעה האחרונה של השם הזה ברשימה.
אם יש לך הבנה טובה של פונקציות Excel, אתה יודע שאין פונקציית Excel שיכולה לעשות זאת.כאן אנו עושים ניסים לקרות.
במדריך זה, אני אראה לך שלוש דרכים לעשות זאת.
תוכן
מצא את המופע האחרון - באמצעות הפונקציה MAX
הנה נוסחת האקסל שתחזיר את הערך האחרון ברשימה:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
כך פועלת הנוסחה הזו:
- הפונקציה MAX משמשת למציאת מספר השורה של השם האחרון התואם.לדוגמה, אם השם הוא גלן, הוא יחזיר 11 כי הוא נמצא בשורה 11.מכיוון שהרשימה שלנו מתחילה בשורה השנייה, 1 מופחת.לכן, המופע האחרון של גלן הוא 10 ברשימה שלנו.
- SUMPRODUCT משמש כדי לוודא שלא תצטרך להשתמש ב-Control + Shift + Enter מכיוון ש-SUMPRODUCT יכול להתמודד עם נוסחאות מערך.
- הפונקציה INDEX משמשת כעת כדי למצוא את התאריך של השם האחרון התואם.
מצא את ההתרחשות האחרונה - באמצעות פונקציית LOOKUP
הנה נוסחה נוספת שעושה את אותה עבודה:
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
כך פועלת הנוסחה הזו:
- ערך חיפוש הוא 2 (תראה למה.. המשך לקרוא)
- טווח הבדיקה הוא 1/($A$2:$A$14=$D$3) - הוא מחזיר 1 כאשר הוא מוצא שם תואם, ושגיאה אחרת.אז אתה מסיים עם מערך.例如，查找值為 Glen，數組將為 {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}。
- הארגומנט השלישי ([result_vector]) הוא הטווח שבו הוא נותן את התוצאה, במקרה זה התאריך.
הסיבה שהנוסחה הזו עובדת היא שפונקציית LOOKUP משתמשת בטכניקת התאמה משוערת.זה אומר שאם הוא יכול למצוא התאמה מדויקת, הוא יחזור, אבל אם לא יצליח, הוא יסרוק את כל המערך עד הסוף ויחזיר את הערך הבא בגודלו שהוא קטן מערך הבדיקה.
במקרה זה, ערך הבדיקה הוא 2, ובמערך שלנו, אנחנו פשוט מקבלים 1 או שגיאה.אז הוא סורק את כל המערך ומחזיר את המיקום של ה-1 האחרון - הערך התואם האחרון עבור השם.
מצא אירוע אחרון - שימוש בפונקציה מותאמת אישית (VBA)
תן לי להראות לך גם דרך אחרת.
אנו יכולים ליצור פונקציות מותאמות אישית (הידועות גם כפונקציות מוגדרות על ידי משתמש) באמצעות VBA.
היתרון ביצירת פונקציות מותאמות אישית הוא קלות השימוש.אתה לא צריך לדאוג לגבי יצירת נוסחאות מורכבות בכל פעם מכיוון שרוב העבודה מתרחשת ב-VBA backend.
יצרתי נוסחה פשוטה (בדומה לנוסחת VLOOKUP).
כדי ליצור פונקציות מותאמות אישית, אתה צריך קוד VBA בעורך VB.אני אתן לך את הקוד ואת השלבים להכנסתו לעורך VB מאוחר יותר, אבל תחילה אני אראה לך איך זה עובד:
הנה הנוסחה שתיתן לך את התוצאה:
=LastItemLookup($D$3,$A$2:$B$14,2)
הנוסחה לוקחת שלושה פרמטרים:
- מצא את הערך (זה יהיה השם בתא D3)
- מצא את הטווח (זה יהיה הטווח עם שם ותאריך - A2:B14)
- מספר עמודה (זו העמודה שעבורה אנחנו רוצים את התוצאה)
לאחר שתיצור את הנוסחה ושמת את הקוד בעורך VB, תוכל להשתמש בו כמו כל פונקציית גליון עבודה רגילה אחרת של Excel.
הנה הקוד של הנוסחה:
פונקציה LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells(i, 1) Then LastItemLookup = LookupRange.Cells(i, ColumnNumber) Exit Function End If Next i End Function
להלן השלבים להכנסת קוד זה לעורך VB:
- עבור ללשונית מפתחים.
- לחץ על אפשרויות Visual Basic.פעולה זו תפתח את עורך VB ב-backend.
- בחלונית Project Explorer של עורך VB, לחץ באמצעות לחצן העכבר הימני על כל אובייקט בחוברת העבודה שבו ברצונך להוסיף קוד.אם אינך רואה את Project Explorer, עבור ללשונית View ולחץ על Project Explorer.
- עבור אל הוספה ולחץ על מודולים.פעולה זו תוסיף אובייקט מודול עבור חוברת העבודה שלך.
- העתק והדבק את הקוד בחלון המודול.
כעת הנוסחה תהיה זמינה בכל הגיליונות של חוברת העבודה.
שים לב שתצטרך לשמור את חוברת העבודה בפורמט XLSM מכיוון שהיא מכילה פקודות מאקרו.כמו כן, אם ברצונך שהנוסחה הזו תהיה זמינה בכל חוברות העבודה שבהן אתה משתמש, תוכל לשמור אותה בחוברת העבודה המאקרו האישית שלך או ליצור ממנה תוספת.
