מצא את ההופעה האחרונה של ערך חיפוש רשימה ב- Excel

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

לאחרונה עבדתי על קביעת סדר היום לפגישה.

הייתה לי רשימה ב- Excel שבה הייתה לי רשימה של אנשים והתאריכים שבהם הם שימשו 'יו"ר הפגישה'.

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

הסיבה לכך הייתה שהייתי צריך להבטיח שמישהו שיושב בראש לאחרונה לא יוקצה שוב.

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

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

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

אבל אתה נמצא בקטע Formula Hack, והנה אנו גורמים לקסם לקרות.

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

מצא את ההתרחשות האחרונה - שימוש בפונקציה MAX

קרדיט לטכניקה זו מגיע למאמר מאת MVP Excel של צ'רלי קיד.

להלן נוסחת האקסל שתחזיר את הערך האחרון מהרשימה:

= INDEX ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ROW ($ A $ 2: $ A $ 14)*($ D $ 3 = $ A $ 2: $ A $ 14))-1))

כך פועלת נוסחה זו:

• הפונקציה MAX משמשת לאיתור מספר השורות של השם התואם האחרון. לדוגמה, אם השם הוא גלן, הוא יחזיר 11, כפי שהוא בשורה ה -11. מאז שהרשימה שלנו מתחילה מהשורה השנייה ואילך, 1 נחסכה. אז מיקום ההתרחשות האחרונה של גלן הוא 10 ברשימה שלנו.
• SUMPRODUCT משמש כדי להבטיח שלא תצטרך להשתמש ב- Control + Shift + Enter, מכיוון ש- SUMPRODUCT יכול להתמודד עם נוסחאות מערך.
• הפונקציה INDEX משמשת כעת לאיתור התאריך לשם התואם האחרון.

מצא את ההתרחשות האחרונה - שימוש בפונקציית Lookup

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

= חיפוש (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)

כך פועלת נוסחה זו:

• ערך החיפוש הוא 2 (תראה מדוע … המשך לקרוא)
• טווח החיפוש הוא 1/($ A $ 2: $ A $ 14 = $ D $ 3) - זה מחזיר 1 כאשר הוא מוצא את השם המתאים ושגיאה כאשר הוא לא מוצא. אז בסופו של דבר תקבל מערך. לדוגמה, מערך החיפוש הוא גלן, המערך יהיה {#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)

תן לי גם להראות לך דרך אחרת לעשות זאת.

אנו יכולים ליצור פונקציה מותאמת אישית (נקראת גם פונקציית User Defined) באמצעות VBA.

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

יצרתי נוסחה פשוטה (הדומה מאוד לנוסחת VLOOKUP).

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

זו הנוסחה שתתן לך את התוצאה:

= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)

הנוסחה לוקחת שלושה טיעונים:

• ערך חיפוש (זה יהיה השם בתא D3)
• טווח חיפוש (זה יהיה הטווח בעל השמות והתאריכים - A2: B14)
• מספר עמודה (זו העמודה שממנה אנו רוצים את התוצאה)

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

להלן הקוד לנוסחה:

'זהו קוד לפונקציה שמוצאת את המופע האחרון של ערך חיפוש ומחזירה את הערך המתאים מהעמודה שצוין' קוד שנוצר על ידי Sumit Bansal (https://trumpexcel.com) פונקציה LastItemLookup (Lookupvalue כמחרוזת, LookupRange As טווח, ColumnNumber כשלם) 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) יציאה מהפונקציה סיום אם הבא פונקציית סיום

להלן השלבים למיקום הקוד הזה בעורך VB:

1. עבור לכרטיסייה מפתחים.
2. לחץ על האפשרות Visual Basic. פעולה זו תפתח את עורך ה- VB בקצה האחורי.
3. בחלונית סייר הפרויקט בעורך VB, לחץ באמצעות לחצן העכבר הימני על כל אובייקט עבור חוברת העבודה שבה ברצונך להכניס את הקוד. אם אינך רואה את סייר הפרוייקטים עבור לכרטיסייה תצוגה ולחץ על סייר הפרוייקטים.
4. עבור אל הוספה ולחץ על מודול. זה יכניס אובייקט מודול לחוברת העבודה שלך.
5. העתק והדבק את הקוד בחלון המודול.

כעת הנוסחה תהיה זמינה בכל גליון העבודה של חוברת העבודה.

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