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

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

נוסחה כללית:

ארגומנטים

ערך החזרה:

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

כיצד עובדת נוסחה זו

נניח שברצונך לבדוק אם התא B3 מכיל אחד מהערכים בטווח E3:E5, אך בו זמנית להוציא את הערכים בטווח F3:F4, השתמש בנוסחה הבאה

לחץ על Enter כדי לקבל את תוצאת הבדיקה.

הסבר

חלק 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) בודק אם התא מכיל ערכים ב-E3:E5

SEARCH פונקציית: הפונקציה SEARCH מחזירה את המיקום של התו הראשון של מחרוזת הטקסט בתוך מחרוזת אחרת, אם הפונקציה SEARCH מוצאת את הטקסט המתאים, היא מחזירה את המיקום היחסי, אם לא, היא מחזירה שגיאה #VALUE!. למשל, כאן הנוסחה SEARCH($E$3:$E$5,B3) תחפש כל ערך בטווח E3:E5 בתא B3, ותחזיר את מיקום כל מחרוזת טקסט בתא B3. היא תחזיר תוצאה מסוג מערך כך: {1;7;12}.

פונקציית ISNUMBER: הפונקציה ISNUMBER מחזירה TRUE כאשר התא הוא מספר. לכן ISNUMBER(SEARCH($E$3:$E$5,B3)) תחזיר תוצאה מסוג מערך כך: {true,true,true} מכיוון שהפונקציה SEARCH מצאה 3 מספרים.

--ISNUMBER(SEARCH($E$3:$E$5,B3)) ממיר את הערך TRUE ל-1, ואת הערך FALSE ל-0, כך שהנוסחה משנה את תוצאת המערך ל-{1;1;1}.

SUMPRODUCT פונקציית: משמשת להכפלת טווחים או לסיכום מערכים יחד ומחזירה את סכום המכפלות. הפונקציה SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) מחזירה 1+1+1=3.

לבסוף, השווה את הנוסחה השמאלית SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) ל-0, כל עוד תוצאת הנוסחה השמאלית גדולה מ-0, התוצאה תהיה TRUE, אחרת היא תחזיר FALSE. כאן היא מחזירה TRUE.

חלק 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) בודק אם התא אינו מכיל ערכים ב-F3:F4

הנוסחה SEARCH($F$3:$F$4,B3) תחפש כל ערך בטווח E3:E5 בתא B3, ותחזיר את מיקום כל מחרוזת טקסט בתא B3. היא תחזיר תוצאה מסוג מערך כך: {#VALUE!;#VALUE!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)) תחזיר תוצאה מסוג מערך כך: {false;false} מכיוון שהפונקציה SEARCH לא מצאה מספרים.

--ISNUMBER(SEARCH($F$3:$F$4,B3)) ממיר את הערך TRUE ל-1, ואת הערך FALSE ל-0, כך שהנוסחה משנה את תוצאת המערך ל-{0;0 }.

SUMPRODUCT function: is used to multiples ranges or sum arrays together and returns the sum of products. The SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) returns 0+0=0.

לבסוף, השווה את הנוסחה השמאלית SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) ל-0, כל עוד תוצאת הנוסחה השמאלית שווה ל-0, התוצאה תחזיר TRUE, אחרת היא תחזיר FALSE. כאן היא מחזירה TRUE.

חלק 3: הכפלת שתי הנוסחאות

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

בנוסחה זו, 1 ו-0 מטופלים כערכי אמת ושקר.

קובץ דוגמה

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

נוסחאות קשורות

• בדוק אם תא מכיל טקסט מסוים
  כדי לבדוק אם תא מכיל כמה טקסטים בטווח A אך לא מכיל את הטקסטים בטווח B, ניתן להשתמש בנוסחת מערך המשלבת את הפונקציות COUNT, SEARCH ו-AND ב-Excel.
• בדוק אם תא מכיל אחד מדברים רבים
  מדריך זה מספק נוסחה לבדיקה אם תא מכיל אחד ממספר ערכים ב-Excel, ומסביר את הארגומנטים בנוסחה וכיצד הנוסחה פועלת.
• בדוק אם תא מכיל אחד מהדברים
  נניח שב-Excel יש רשימת ערכים בעמודה E, ברצונך לבדוק אם התאים בעמודה B מכילים את כל הערכים בעמודה E, ולהחזיר TRUE או FALSE.
• בדוק אם תא מכיל מספר
  לפעמים, ייתכן שתרצה לבדוק אם תא מכיל תווים מספריים. מדריך זה מספק נוסחה שתחזיר TRUE אם התא מכיל מספר, ו-FALSE אם התא אינו מכיל מספר.