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

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

כשזה מגיע לחיפוש נתונים ב- Excel, יש שתי פונקציות מדהימות שאני מרבה להשתמש בהן - VLOOKUP ו- INDEX (בעיקר בשילוב עם הפונקציה MATCH).

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

אבל מה אם אתה רוצה לחפש את הערך השני, השלישי, הרביעי או ה- N.

ובכן, זה ניתן לביצוע עם קצת עבודה נוספת.

במדריך זה אראה לך דרכים שונות (עם דוגמאות) כיצד לחפש את הערך השני או ה- N ב- Excel.

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

במדריך זה אעסוק בשתי דרכים לחיפוש הערך השני או הערך ה- N ב- Excel:

• שימוש בעמוד עוזרים.
• שימוש בנוסחאות מערך.

נתחיל וצלול ישר פנימה.

שימוש בעמוד עוזר

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

במערך הנתונים לעיל, העובדים קיבלו הדרכה בכלים שונים של Microsoft Office (Excel, PowerPoint ו- Word).

כעת תוכל להשתמש בפונקציית VLOOKUP או בשילוב INDEX/MATCH כדי למצוא את ההכשרה שעובד סיים. עם זאת, הוא יחזיר רק את מופע ההתאמה הראשון.

לדוגמה, במקרה של ג'ון, הוא לקח את כל שלושת האימונים, אך כשאחפש את שמו באמצעות VLOOKUP או INDEX/MATCH, הוא תמיד יחזיר 'Excel', שהוא האימון הראשון לשמו ברשימה .

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

להלן השלבים:

• הכנס עמודה לפני העמודה המפרטת את האימון.
• בתא B2, הזן את הנוסחה הבאה:

  = A2 & COUNTIF ($ A $ 2: $ A2, A2)

• בתא F2, הזן את הנוסחה הבאה והעתק-הדבק עבור כל התאים האחרים:

  = IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")

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

כיצד פועלת נוסחה זו?

נוסחת COUNTIF בעמודת העוזר הופכת את שמו של כל עובד לייחודי על ידי הוספת מספר אליו. לדוגמה, המופע הראשון של ג'ון הופך לג'ון 1, המופע השני הופך לג'ון 2 וכן הלאה.

נוסחת VLOOKUP משתמשת כעת בשמות עובדים ייחודיים אלה כדי למצוא את ההכשרה המתאימה.

שים לב ש $ E2 & COLUMNS ($ F $ 1: F1) הוא ערך החיפוש בנוסחה. זה יוסיף מספר לשם העובד בהתבסס על מספר העמודה. לדוגמה, כאשר נוסחה זו משמשת בתא F2, ערך החיפוש הופך ל"ג'ון 1 ". בתא G2 הוא הופך ל"ג'ון 2 "וכן הלאה.

שימוש בפורמולה של מערך

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

נניח שיש לך את אותו מערך הנתונים כפי שמוצג להלן:

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

= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ""), COLUMNS ($ E $ 1) : E1))), "")

העתק נוסחה זו והדבק אותה בתא E2.

שים לב שזו נוסחת מערך ואתה צריך להשתמש ב- Control + Shift + Enter (החזק את מקשי Ctrl ו- Shift ולחץ על מקש Enter), במקום ללחוץ רק על מקש Enter.

לחץ כאן להורדת קובץ הדוגמה.

כיצד פועלת נוסחה זו?

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

$ A $ 2: $ A $ 14 = $ D2

החלק הנ"ל של הנוסחה משווה כל תא ב- A2: A14 עם הערך ב- D2. במערך נתונים זה בודק אם תא מכיל את השם "ג'ון" או לא.

הוא מחזיר מערך של TRUE של FALSE. אם לתא יש את השם 'ג'ון' זה יהיה נכון, אחרת זה יהיה שקר.

להלן המערך שתקבל בדוגמה זו:

{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}

שים לב שיש לו TRUE במיקום הראשון, השביעי וה- 111, שכן שם מופיע השם ג'ון במערך הנתונים.

IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, "")

נוסחת IF לעיל משתמשת במערך של TRUE ו- FALSE, ומחליפה את TRUE במיקום המופע שלה ברשימה (נתון על ידי ROW ($ A $ 2: $ A $ 14) -1) ו- FALSE ב- "" (החסר). להלן המערך שהתקבל באמצעות נוסחת IF זו:

{1;””;””;””;””;””;7;””;””;””;11;””;””}

הערה מ -1, 7 ו -11 הם עמדת התרחשותו של ג'ון ברשימה.

קטן (אם ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ""), COLUMNS ($ E $ 1: E1))

הפונקציה SMALL בוחרת כעת את המספר הקטן הראשון, השני הקטן, השלישי הקטן ביותר ממערך זה. שים לב שהוא משתמש בפונקציה COLUMNS ליצירת מספר העמודה. בתא E2 הפונקציה COLUMNS מחזירה 1 והפונקציה SMALL מחזירה 1. בתא F2 הפונקציה COLUMNS מחזירה 2 והפונקציה SMALL מחזירה 7.

INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ""), COLUMNS ($ E $ 1: E1) ))

הפונקציה INDEX מחזירה כעת את הערך מהרשימה בעמודה B בהתבסס על המיקום שהוחזר על ידי הפונקציה SMALL. מכאן שבתא E2 הוא מחזיר את 'Excel', שהוא הפריט הראשון ב- B2: B14. בתא F2, הוא מחזיר את PowerPoint, שהוא הפריט השביעי ברשימה.

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

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

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

לחץ כאן להורדת קובץ הדוגמה.