Skip to main content

גלה היום את הגרסה החדשה והחינמית של Kutools עבור Outlook!

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

Author: Xiaoyang Last Modified: 2025-05-30

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

return multiple matching values based on criteria

החזרת מספר ערכים תואמים על סמך מספר קריטריונים בתא אחד

החזרת מספר ערכים תואמים על סמך מספר קריטריונים בעמודה

החזרת מספר ערכים תואמים על סמך מספר קריטריונים בשורה

החזרת מספר ערכים תואמים על סמך מספר קריטריונים בתא אחד

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

שיטה 1: שימוש בפונקציית Textjoin (Excel365 / 2021,2019)

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

הזן או העתק את הנוסחה הבאה לתא ריק, ולאחר מכן לחץ על מקש Enter (Excel 2021 ו-Excel 365) או מקשי Ctrl + Shift + Enter ב-Excel 2019 כדי לקבל את התוצאה:

=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))

Return multiple matching values based on multiple criteria in a single cell with textjoin function

הסבר לנוסחה זו:
  • ($A$2:$A$21=E2)*($B$2:$B$21=F2) בודק אם כל שורה עומדת בשני התנאים: "המוכר שווה ל-E2" ו"החודש שווה ל-F2." אם שני התנאים מתקיימים, התוצאה היא 1; אחרת, היא 0. הסימן * אומר ששני התנאים חייבים להיות נכונים.
  • IF(..., $C$2:$C$21, "") מחזיר את שם המוצר אם השורה מתאימה; אחרת, הוא מחזיר תא ריק.
  • TEXTJOIN(", ", TRUE, ...) משלב את כל שמות המוצרים שאינם ריקים לתוך תא אחד, מופרדים על ידי ", ".
 

שיטה 2: שימוש ב-Kutools for Excel

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

Kutools for Excel מציעה יותר מ-300 תכונות מתקדמות כדי לפשט משימות מורכבות, להגביר יצירתיות ויעילות. משולב עם יכולות AI, Kutools מבצע משימות בצורה מדויקת, מה שהופך את ניהול הנתונים לקל ואינטואיטיבי. מידע מפורט על Kutools for Excel... ניסיון חינם...

לאחר התקנת Kutools for Excel, אנא עשה כך:

  1. בחר את טווח הנתונים שאתה רוצה לקבל את כל הערכים המתאימים בהתאם לקריטריונים.
  2. לאחר מכן, לחץ על Kutools > Merge & Split > Advanced Combine Rows, ראה צילום מסך:
    click Advanced Combine Rows of kutools
  3. בתיבת הדו-שיח Advanced Combine Rows, אנא הגדר את האפשרויות הבאות:
    • בחר את כותרות העמודות המכילות את הקריטריונים התואמים שלך (למשל, מוכר וחודש). לכל עמודה שנבחרה, לחץ על Primary Key כדי להגדיר אותם כתנאי החיפוש שלך.
    • לחץ על כותרת העמודה בה תרצה את התוצאות הממוזגות (למשל, מוצר). מהחלק של Combine, בחר את המפריד המועדף עליך (למשל, פסיק, רווח או מפריד מותאם אישית).
  4. לבסוף, לחץ על כפתור OK.
    specify the options in the dialog box

תוצאה: Kutools ימזג באופן מיידי את כל הערכים התואמים לתוך תא אחד לכל שילוב קריטריונים ייחודי.
Return multiple matching values based on multiple criteria in a single cell with kutools

החזרת מספר ערכים תואמים על סמך מספר קריטריונים בעמודה

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

שיטה 1: שימוש בנוסחת מערך (לכל הגרסאות)

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

1. העתק או הזן את הנוסחה הבאה לתא ריק:

=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")

2. לחץ על מקשי Ctrl + Shift + Enter כדי לקבל את התוצאה התואמת הראשונה, ולאחר מכן בחר את תא הנוסחה הראשון והורד את הגרירה כלפי מטה עד שתראה תא ריק, כעת כל הערכים התואמים מוחזרים כפי שמוצג בסcreenshot הבא:

Return multiple matching values based on multiple criteria in a column with array formula

הסבר לנוסחה זו:
  • $A$2:$A$18=$E$2: בודק אם המוכר מתאים לערך בתא E2.
  • $B$2:$B$18=$F$2: בודק אם החודש מתאים לערך בתא F2.
  • * הוא אופרטור AND לוגי (שני התנאים חייבים להיות נכונים).
  • ROW($C$2:$C$18)-ROW($C$2)+1: יוצר מספר שורה יחסי לכל מוצר.
  • SMALL(..., ROW(1:1)): מביא את השורה התואמת הקטנה ביותר לפי n (ככל שהנוסחה מוזזת למטה).
  • INDEX(...): מחזיר את המוצר מהשורה התואמת.
  • IFERROR(..., ""): מחזיר תא ריק אם אין התאמות נוספות.
 

שיטה 2: שימוש בפונקציית Filter (Excel365 / 2021)

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

העתק או הזן את הנוסחה הבאה לתא ריק, לאחר מכן לחץ על מקש Enter, כל הרשומות התואמות מוחזרות על סמך הקריטריונים המרובים.

=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")

Return multiple matching values based on multiple criteria in a column with filter function

הסבר לנוסחה זו:
  • FILTER(...) מחזיר את כל הערכים מ-C2:C18 כאשר שני התנאים מתקיימים.
  • (A2:A18=E2)*(B2:B18=F2): מערך לוגי שבודק התאמה למוכר ולחודש.
  • "אין התאמה": הודעה אופציונלית אם לא נמצאו ערכים.

החזרת מספר ערכים תואמים על סמך מספר קריטריונים בשורה

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

שיטה 1: שימוש בנוסחת מערך (לכל הגרסאות)

נוסחאות מערך מסורתיות מאפשרות לחלץ מספר ערכים תואמים באמצעות פונקציות INDEX, SMALL, IF ו-COLUMN. בניגוד לחלץ בצורה אנכית (מבוססת עמודה), אנו מותאמים את הנוסחה כדי להחזיר תוצאות בשורה.

1. העתק או הזן את הנוסחה הבאה לתא ריק:

=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")

2. לחץ על מקשי Ctrl + Shift + Enter כדי לקבל את התוצאה התואמת הראשונה, ולאחר מכן בחר את תא הנוסחה הראשון והמשך לגרור את הנוסחה ימינה לאורך העמודות כדי לאחזר את כל התוצאות.

Return multiple matching values based on multiple criteria in a row with array formula

הסבר לנוסחה זו:
  • $A$2:$A$18=$E$2: בודק אם המוכר מתאים.
  • $B$2:$B$18=$F$2: בודק אם החודש מתאים.
  • *: AND לוגי - שני התנאים חייבים להיות נכונים.
  • ROW($C$2:$C$18)-ROW($C$2)+1: יוצר מספרי שורות יחסיים.
  • COLUMN(A1): מכוון איזו התאמה להחזיר, תלוי כמה רחוק הנוסחה מוזזת ימינה.
  • IFERROR(...): מונע שגיאות לאחר שכל ההתאמות נגמרות.
 

שיטה 2: שימוש בפונקציית Filter (Excel365 / 2021)

העתק או הזן את הנוסחה הבאה לתא ריק, לאחר מכן לחץ על מקש Enter, כל הערכים התואמים מוחזרים וממוקמים בשורה. ראה צילום מסך:

=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))

Return multiple matching values based on multiple criteria in a row with filter function

הסבר לנוסחה זו:
  • FILTER(...): מאחזר ערכים תואמים מעמודה C על סמך שני התנאים.
  • (A2:A18=E2)*(B2:B18=F2): שני התנאים חייבים להיות נכונים.
  • TRANSPOSE(...): ממיר את המערך האנכי שהוחזר על ידי FILTER למערך אופקי.

🔚 מסקנה

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

  • עבור משתמשים עם Excel 365 או Excel 2021, פונקציית FILTER מציעה פתרון מודרני, דינמי ואלגנטי שמזער את המורכבות.
  • עבור משתמשים עם גרסאות ישנות יותר, נוסחאות מערך נשארות כלים חזקים, אך דורשים מעט יותר הכנה ותשומת לב.
  • כמו כן, אם ברצונך לקבץ את התוצאות לתוך תא אחד או להעדיף פתרון ללא קוד, פונקציית TEXTJOIN או כלים צד שלישי כמו Kutools for Excel יכולים להקל משמעותית על התהליך.

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

מאמרים נוספים בנושא:

  • החזרת מספר ערכי חיפוש בתא אחד מופרד בפסיק
  • ב-Excel, אנחנו יכולים ליישם את הפונקציה VLOOKUP כדי להחזיר את הערך התואם הראשון מהתאים בטבלה, אבל לפעמים, אנחנו צריכים לחלץ את כל הערכים התואמים ואז להפריד אותם עם תו מפריד מסוים, כגון פסיק, מקף וכו' לתוך תא אחד כפי שמוצג בסcreenshot הבא. כיצד ניתן לקבל ולהחזיר מספר ערכי חיפוש בתא אחד מופרד בפסיק ב-Excel?
  • Vlookup והחזרת מספר ערכים תואמים בבת אחת בגיליון Google
  • הפונקציה Vlookup הרגילה בגיליון Google יכולה לעזור לך למצוא ולהחזיר את הערך התואם הראשון על סמך נתון נתון. אבל, לפעמים, ייתכן שתצטרך לבצע vlookup ולהחזיר את כל הערכים התואמים כפי שמוצג בסcreenshot הבא. האם יש לך דרכים טובות וקלות לפתור את המשימה הזו בגיליון Google?
  • Vlookup והחזרת מספר ערכים מרשימת נפתחת
  • ב-Excel, כיצד תוכל לבצע vlookup ולהחזיר מספר ערכים מתאימים מרשימת נפתחת, כלומר כאשר אתה בוחר פריט אחד מרשימת הנפתחת, כל הערכים הקשורים לו מוצגים בבת אחת כפי שמוצג בסcreenshot הבא. במאמר זה, אני אציג את הפתרון שלב אחר שלב.
  • Vlookup והחזרת מספר ערכים בצורה אנכית ב-Excel
  • באופן רגיל, אתה יכול להשתמש בפונקציית Vlookup כדי לקבל את הערך התואם הראשון, אבל, לפעמים, אתה רוצה להחזיר את כל הרשומות התואמות על סמך קריטריון מסוים. במאמר זה, אני אדבר על איך לבצע vlookup ולהחזיר את כל הערכים התואמים בצורה אנכית, אופקית או לתוך תא אחד.
  • Vlookup והחזרת נתוני התאמה בין שני ערכים ב-Excel
  • ב-Excel, אנחנו יכולים ליישם את הפונקציה Vlookup הרגילה כדי לקבל את הערך התואם על סמך נתון נתון. אבל, לפעמים, אנחנו רוצים לבצע vlookup ולהחזיר את הערך התואם בין שני ערכים כפי שמוצג בסcreenshot הבא, כיצד תוכל להתמודד עם המשימה הזו ב-Excel?

כלי הפרודוקטיביות הטובים ביותר ל-Office

🤖 Kutools AI Aide: מהפכה בניתוח נתונים באמצעות: ביצוע חכם |צור קוד |צור נוסחאות מותאם אישית |נתח נתונים וייצר תרשימים |הפעל פונקציות משופרות…
פיצ'רים פופולריים: מצא, הדגש או סמן כפילויות | מחק שורות ריקות | שלב עמודות או תאים מבלי לאבד נתונים |   עיגול ...
חיפוש מתקדם: VLookup עם מספר קריטריונים | VLookup לערכים מרובים | חיפוש במספר גליונות | התאמה עמומה ....
רשימת נפתחת מתקדמת: צור במהירות רשימה נפתחת | רשימה נפתחת תלויה | רשימה נפתחת עם בחירה מרובה ....
מנהל עמודות: הוסף מספר עמודות מסוים | העבר עמודות | החלף מצב תצוגה של עמודות מוסתרות | השווה טווחים ועמודות ...
פיצ'רים נבחרים: מיקוד רשת | תצוגת עיצוב | שורת נוסחאות משופרת | נהל חוברת עבודה וגליון עבודה | ספריית טקסט אוטומטי (Auto Text) | בורר תאריך | מיזוג נתונים | הצפן/פענח תאים | שלח דואר אלקטרוני לפי טבלה חד-ממדית | סינון מתקדם | סינון מיוחד (סנן תאים עם גופן מודגש/נטוי/קו חוצה...) ...
15 ערכות כלים מובילות:12 כלי טקסט (הוסף טקסט, מחק תווים מסוימים, ...) | מעל50 סוגי תרשימים (תרשים גאנט, ...) | מעל40 נוסחאות חכמות (חישוב גיל על פי תאריך לידה, ...) |19 כלי הוספה (הכנס קוד QR, הכנס תמונה מנתיב, ...) |12 כלי המרה (המרה למילים, המרת מטבע, ...) |7 כלי מיזוג ופיצול (מיזוג מתקדם של שורות, פיצול תאים, ...) | ...ועוד

שדרג את כישורי ה-Excel שלך עם Kutools for Excel ותחווה יעילות שלא הכרת. Kutools for Excel מציע מעל300 פיצ'רים מתקדמים לשיפור הפרודוקטיביות ולחסוך זמן. לחץ כאן כדי לקבל את הפיצ'ר שהכי נחוץ לך...

Office Tab מביא ממשק טאב ל-Office והופך את העבודה שלך להרבה יותר קלה

  • אפשר עריכה וקריאה בטאבים ב-Word, Excel, PowerPoint
  • פתח וצור מסמכים מרובים בטאבים חדשים באותו חלון, במקום בחלונות חדשים.
  • הגדל את הפרודוקטיביות שלך ב-50% וצמצם מאות קליקים של עכבר בכל יום!