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

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

נוסחה כללית:

ארגומנטים

ערך החזרה:

הנוסחה מחזירה ערך לוגי, FALSE: התא לא מכיל את כל הדברים; TRUE: התא מכיל את כל הדברים.

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

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

לחץ על Enter גרור את הידית למלא את התאים שברצונך לבדוק. FALSE מצביע על כך שהתא אינו מכיל את כל הערכים בטווח E3:E5, בעוד ש-TRUE מצביע על כך שהתא המקביל מכיל את כל הערכים.

הסבר

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

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

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

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

פונקציית COUNTA: מחזירה את מספר התאים שאינם ריקים. COUNTA($E$3:$E$5) מחזירה 3, אז תוצאת SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) שווה ל-3, הנוסחה השלמה תחזיר TRUE, אחרת תחזיר FALSE.

הערות:

הנוסחה =SUMPRODUCT(--ISNUMBER(SEARCH(דברים,טקסט)))=COUNTA(דברים) אינה בודקת בדיוק. ראה צילום מסך:

קובץ דוגמה

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

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

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