קבל ערכי חיפוש מרובים בתא בודד (ללא ובלי חזרה)

האם נוכל לחפש ולהחזיר מספר ערכים בתא אחד ב- Excel (מופרדים בפסיק או ברווח)?

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

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

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

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

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

אז בואו נתחיל!

חיפוש והחזרת ערכים מרובים בתא אחד (באמצעות נוסחה)

אם אתה משתמש ב- Excel 2016 או גירסאות קודמות, עבור לסעיף הבא שבו אני מראה כיצד לעשות זאת באמצעות VBA.

עם מנוי Microsoft 365, ל- Excel שלך יש כעת הרבה יותר פונקציות ותכונות חזקות יותר שאינן קיימות בגרסאות קודמות (כגון XLOOKUP, מערכים דינמיים, פונקציות UNIQUE/FILTER וכו ').

אז אם אתה משתמש ב- Microsoft 365 (שנקרא בעבר Office 365), תוכל להשתמש בשיטות המכוסות בסעיף זה כדי לחפש ולהחזיר מספר ערכים בתא אחד בודד ב- Excel.

וכפי שתראו, זו נוסחה ממש פשוטה.

למטה יש לי מערך נתונים שבו יש לי את שמות האנשים בעמודה A ואת ההכשרה שהם לקחו בעמודה B.

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

עבור כל אדם, אני רוצה לברר איזה הכשרה סיימו. בעמודה D, יש לי את רשימת השמות הייחודיים (מעמודה A), ואני רוצה לחפש ולחלץ במהירות את כל האימונים שכל אדם עשה ולקבל אותם בערכה אחת (מופרדת בפסיק).

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

= TEXTJOIN (",", TRUE, IF (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, ""))

לאחר הזנת הנוסחה בתא E2, העתק אותה לכל התאים שבהם ברצונך לקבל את התוצאות.

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

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

המבחן הלוגי בנוסחת IF (D2 = $ A $ 2: $ A $ 20) בודק אם תא השם D2 זהה לזה בטווח A2: A20.

הוא עובר בכל תא בטווח A2: A20, ובודק אם השם זהה בתא D2 או לא. אם זה אותו שם, הוא מחזיר TRUE, אחרת הוא מחזיר FALSE.

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

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

מכיוון שאנו רק רוצים לקבל את האימון של בוב (הערך בתא D2), עלינו לקבל את כל האימון המתאים לתאים שמחזירים TRUE במערך הנ"ל.

זה נעשה בקלות על ידי ציון [value_if_true] חלק מנוסחת IF כטווח שיש בו את האימון. זה מוודא שאם השם בתא D2 תואם את השם בטווח A2: A20, נוסחת ה- IF תחזיר את כל האימון שהאדם לקח.

ובכל מקום שהמערך מחזיר FALSE, צייננו את הערך [value_if_false] כ- "" (ריק), כך שהוא מחזיר ריק.

החלק IF בנוסחה מחזיר את המערך כפי שמוצג להלן:

{"Excel"; ""; ""; "PowerPoint"; ""; "" ""; "" "" ";" ";" "" "" "" "" "" "" "" "; ""; ""; ""}

היכן שיש בו את שמות האימון של בוב לקח וריק בכל מקום שהשם לא היה בוב.

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

ואת זה ניתן לעשות בקלות באמצעות נוסחת TEXTJOIN החדשה (זמינה ב- Excel2021-2022 ו- Excel ב- Microsoft 365)

נוסחת TEXTJOIN לוקחת שלושה ארגומנטים:

• המפריד - שהוא "," בדוגמה שלנו, מכיוון שאני רוצה שהאימון יופרד בפסיק ובדמות רווח
• TRUE - שאומר לנוסחת TEXTJOIN להתעלם מתאים ריקים ולשלב רק תאים שאינם ריקים
• נוסחת ה- If שמחזירה את הטקסט שצריך לשלב

אם אתה משתמש ב- Excel ב- Microsoft 365 שכבר מכיל מערכים דינמיים, תוכל פשוט להזין את הנוסחה לעיל וללחוץ על enter. ואם אתה משתמש ב- Excel2021-2022, עליך להזין את הנוסחה והחזק את מקש Ctrl ו- Shift ולאחר מכן הקש Enter

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

קבל ערכי חיפוש מרובים בתא בודד (ללא חזרה)

מכיוון שנוסחת UNIQUE זמינה רק ב- Excel ב- Microsoft 365, לא תוכל להשתמש בשיטה זו ב- Excel2021-2022

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

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

אתה יכול להשתמש בנוסחה שלהלן כדי לעשות זאת:

= TEXTJOIN (",", TRUE, UNIQUE (IF (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, "")))

הנוסחה לעיל פועלת באותו אופן, עם שינוי קל. השתמשנו בנוסחת IF בתוך הפונקציה UNIQUE כך שבמקרה שיש חזרות בתוצאת if formula, הפונקציה UNIQUE תסיר אותה.

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

חפש והחזר ערכים מרובים בתא אחד (באמצעות VBA)

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

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

להלן קוד VBA שיכול לעשות זאת:

'Code by Sumit Bansal (https://trumpexcel.com) Function SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber as Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns (1) .Cells .Count If LookupRange.Cells (i, 1) = Lookupvalue ואז Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Result, Len (Result) - 1) פונקציית סיום

היכן לשים את הקוד הזה?

1. פתח חוברת עבודה ולחץ על Alt + F11 (פעולה זו פותחת את חלון עורך VBA).
2. בחלון זה של עורך VBA, משמאל, ישנו סייר פרויקטים (שבו מופיעים כל חוברות העבודה ודפי העבודה). לחץ באמצעות לחצן העכבר הימני על כל אובייקט בחוברת העבודה שבה אתה רוצה שהקוד הזה יעבוד, ועבור אל הוספה -> מודול.
3. בחלון המודול (שיופיע מימין), העתק והדבק את הקוד לעיל.
4. עכשיו אתה מוכן. עבור לכל תא בחוברת העבודה והקלד = SingleCellExtract וחבר את ארגומנטים הקלט הדרושים (כלומר LookupValue, LookupRange, ColumnNumber).

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

פונקציה זו פועלת באופן דומה לפונקציית VLOOKUP.

זה לוקח 3 ארגומנטים כתשומות:

1. ערך חיפוש - מחרוזת שעלינו לחפש במגוון תאים.
2. LookupRange - מערך תאים שממנו אנו צריכים להביא את הנתונים ($ B3: $ C18 במקרה זה).
3. טור מספר - זהו מספר העמודה של הטבלה/המערך שממנו יש להחזיר את ערך ההתאמה (2 במקרה זה).

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

זכור: שמור את חוברת העבודה כחוברת עבודה מאקרו (.xlsm או .xls) כדי להשתמש שוב בנוסחה זו. כמו כן, פונקציה זו תהיה זמינה רק בחוברת עבודה זו ולא בכל חוברות העבודה.

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

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

קבל ערכי חיפוש מרובים בתא בודד (ללא חזרה)

קיימת אפשרות שיש לך חזרות בנתונים.

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

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

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

'Code by Sumit Bansal (https://trumpexcel.com) Function MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber as Integer) Dim i as Long Dim Result As String For i = 1 To LookupRange.Columns (1) .Cells .Count If LookupRange.Cells (i, 1) = Lookupvalue Then For J = 1 to i - 1 If LookupRange.Cells (J, 1) = LookupReal.Cells (J, ColumnNumber) = LookupRange.Cells (i, ColumnNumber) ואז עבור לדלג על End if End אם התוצאה הבאה J התוצאה = התוצאה & "" & LookupRange.Cells (i, ColumnNumber) & "," דלג: סוף אם הבא i MultipleLookupNoRept = שמאל (תוצאה, Len (תוצאה) - 1) סוף פוּנקצִיָה

לאחר שתמקם קוד זה בעורך VB (כפי שמוצג למעלה בהדרכה), תוכל להשתמש ב- MultipleLookupNoRept פוּנקצִיָה.

להלן תמונת מצב של התוצאה שתקבל עם זה MultipleLookupNoRept פוּנקצִיָה.

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

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

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