תוכן
כל פונקציות החיפוש וההחלפות הקלאסיות VPR (VLOOKUP), GPR (HLOOKUP), יותר חשוף (התאמה) ולאלו כמוהם יש תכונה אחת חשובה - הם מחפשים מההתחלה ועד הסוף, כלומר משמאל לימין או מלמעלה למטה בנתוני המקור. ברגע שנמצאת ההתאמה המתאימה הראשונה, החיפוש מפסיק ונמצאת רק המופע הראשון של האלמנט שאנו צריכים.
מה לעשות אם אנחנו צריכים למצוא לא את ההתרחשות הראשונה, אלא את ההתרחשות האחרונה? למשל, העסקה האחרונה ללקוח, התשלום האחרון, ההזמנה האחרונה וכו'?
שיטה 1: מציאת השורה האחרונה עם נוסחת מערך
אם בטבלה המקורית אין עמודה עם תאריך או מספר סידורי של שורה (הזמנה, תשלום...), אז המשימה שלנו היא, למעשה, למצוא את השורה האחרונה שעונה על התנאי הנתון. ניתן לעשות זאת באמצעות נוסחת המערך הבאה:
כאן:
- פונקציה IF (אם) בודק את כל התאים בעמודה אחד אחד לקוח ומציג את מספר השורה אם הוא מכיל את השם שאנו צריכים. מספר השורה על הגיליון ניתן לנו על ידי הפונקציה קו (שׁוּרָה), אבל מכיוון שאנחנו צריכים את מספר השורה בטבלה, אנחנו צריכים בנוסף להחסיר 1, כי יש לנו כותרת בטבלה.
- ואז הפונקציה מקס (מקס) בוחר את הערך המקסימלי מהקבוצה שנוצרה של מספרי השורות, כלומר המספר של השורה האחרונה של הלקוח.
- פונקציה מדד (אינדקס) מחזירה את תוכן התא עם המספר האחרון שנמצא מכל עמודת טבלה נדרשת אחרת (קוד הזמנה).
את כל זה יש להזין בתור נוסחת מערך, כלומר:
- ב-Office 365 עם העדכונים האחרונים מותקנים ותמיכה במערכים דינמיים, אתה יכול פשוט ללחוץ זן.
- בכל שאר הגרסאות, לאחר הזנת הנוסחה, תצטרך ללחוץ על קיצור המקלדת Ctrl+משמרת+זן, שיוסיף לו אוטומטית פלטה מתולתלת בשורת הנוסחאות.
שיטה 2: חיפוש הפוך עם הפונקציה החדשה LOOKUP
כבר כתבתי מאמר ארוך עם סרטון על תכונה חדשה נוף (XLOOKUP), שהופיע בגרסאות האחרונות של Office כדי להחליף את ה-VLOOKUP הישן (VLOOKUP). בעזרת BROWSE המשימה שלנו נפתרת באופן די אלמנטרי, כי. עבור פונקציה זו (בניגוד ל-VLOOKUP), אתה יכול להגדיר במפורש את כיוון החיפוש: מלמעלה למטה או מלמטה למעלה - הארגומנט האחרון שלה (-1) אחראי לכך:
שיטה 3. חפש מחרוזת עם התאריך האחרון
אם בנתוני המקור יש לנו עמודה עם מספר סידורי או תאריך שממלא תפקיד דומה, אז המשימה משתנה - אנחנו צריכים למצוא לא את השורה האחרונה (הנמוכה ביותר) עם התאמה, אלא את השורה עם האחרונה ( מקסימום) תאריך.
כבר דנתי בפירוט כיצד לעשות זאת באמצעות פונקציות קלאסיות, ועכשיו בואו ננסה להשתמש בכוח של פונקציות המערך הדינמי החדש. ליתר יופי ונוחות, אנו גם ממירים את הטבלה המקורית לטבלה "חכמה" באמצעות קיצור מקלדת Ctrl+T או פקודות בית - עיצוב כטבלה (בית - עיצוב כטבלה).
בעזרתם, "הזוג הרוצח" הזה פותר את הבעיה שלנו בחן רב:
כאן:
- תפקוד קודם סנן (לְסַנֵן) בוחר רק את השורות האלה מהטבלה שלנו בעמודה לקוח - השם שאנחנו צריכים.
- ואז הפונקציה GRADE (סוג) ממיין את השורות שנבחרו לפי תאריך בסדר יורד, כאשר העסקה האחרונה נמצאת בראש.
- פונקציה מדד (אינדקס) מחלץ את השורה הראשונה, כלומר מחזיר את המסחר האחרון שאנחנו צריכים.
- ולבסוף, הפונקציה החיצונית FILTER מסירה את העמודה הראשונה והשלישית הנוספות מהתוצאות (קוד הזמנה и לקוח) ומשאיר רק את התאריך והסכום. לשם כך, נעשה שימוש במערך של קבועים. {0;1;0;1}, מגדיר אילו עמודות אנחנו רוצים (1) או לא רוצים (0) להציג.
שיטה 4: מציאת ההתאמה האחרונה ב-Power Query
ובכן, למען השלמות, בואו נסתכל על פתרון לבעיית החיפוש ההפוכה שלנו באמצעות תוסף Power Query. בעזרתה הכל נפתר מהר מאוד ויפה.
1. בואו נמיר את הטבלה המקורית שלנו לטבלה "חכמה" באמצעות קיצור מקשים Ctrl+T או פקודות בית - עיצוב כטבלה (בית - עיצוב כטבלה).
2. טען אותו לתוך Power Query עם הכפתור מתוך טבלה/טווח כרטיסייה נתונים (נתונים - מטבלה/טווח).
3. אנו ממיינים (באמצעות הרשימה הנפתחת של המסנן בכותרת) את הטבלה שלנו בסדר תאריך יורד, כך שהעסקאות האחרונות יהיו בראש.
4… בכרטיסייה טרנספורמציה לבחור צוות לפי קבוצה (שינוי - קבוצה לפי) והגדר את הקיבוץ לפי לקוחות, וכפונקציית צבירה, בחר באפשרות כל השורות (כל השורות). אתה יכול לתת שם לעמודה החדשה בכל מה שתרצה - למשל פרטים.
לאחר הקיבוץ נקבל רשימה של שמות ייחודיים של לקוחותינו ובעמודה פרטים – טבלאות עם כל העסקאות של כל אחת מהן, כאשר השורה הראשונה תהיה העסקה האחרונה, וזה מה שאנחנו צריכים:
5. הוסף עמודה מחושבת חדשה בעזרת הכפתור עמודה מותאמת אישית כרטיסייה הוסף עמודה (הוסף עמודה - הוסף עמודה מותאמת אישית)והזן את הנוסחה הבאה:
כאן פרטים – זו העמודה שממנה אנו לוקחים טבלאות לפי לקוחות, ו 0 {} הוא מספר השורה שאנו רוצים לחלץ (מספור השורה ב-Power Query מתחיל מאפס). אנחנו מקבלים עמודה עם רשומות (שיא), כאשר כל ערך הוא השורה הראשונה מכל טבלה:
נותר להרחיב את התוכן של כל הרשומות עם הכפתור עם החצים הכפולים בכותרת העמודה עסקה אחרונה בחירת העמודות הרצויות:
... ולאחר מכן מחק את העמודה שאינה נחוצה עוד פרטים על ידי לחיצה ימנית על הכותרת שלו - הסר עמודות (הסר עמודות).
לאחר העלאת התוצאות לגיליון דרך בית — סגור וטען — סגור וטען פנימה (בית - סגור וטען - סגור וטען ל...) נקבל טבלה כל כך יפה עם רשימה של עסקאות אחרונות, כפי שרצינו:
כאשר אתה משנה את נתוני המקור, אסור לשכוח לעדכן את התוצאות על ידי לחיצה ימנית עליהן - הפקודה עדכן ושמור (לְרַעֲנֵן) או קיצור מקלדת Ctrl+אחר+F5.
- הפונקציה LOOKUP היא צאצא של VLOOKUP
- כיצד להשתמש בפונקציות המערך הדינמי החדשות SORT, FILTER ו-UNIC
- מציאת התא האחרון הלא ריק בשורה או בעמודה עם הפונקציה LOOKUP