לְהִצטַיֵן

התא מכיל אחד מני הרבה דברים

Cell Contains One Many Things

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

כדי לבדוק אם תא מכיל מחרוזת אחת מני רבות, תוכל להשתמש בנוסחה המבוססת על לחפש , ISNUMBER ו SUMPRODUCT פונקציות. הנוסחה ב- C5, שהועתקה למטה, היא:



= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,A1)))>0

איפה דברים האם ה טווח בשם E5: E7.

הֶסבֵּר

מטרת דוגמה זו היא לבדוק כל תא ב- B5: B14 כדי לראות אם הוא מכיל את כל המיתרים ב טווח בשם דברים (E5: E7). מחרוזות אלו יכולות להופיע בְּכָל מָקוֹם בתא, כך שזו בעיה 'מכילה' מילולית. הנוסחה ב- C5, שהועתקה למטה, היא:





 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

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

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

כאשר פונקציית SEARCH מוצא מחרוזת, הוא מחזיר את המיקום של המחרוזת כמספר. אם חפש לא מצא מחרוזת, הוא מחזיר ערך #! שְׁגִיאָה. המשמעות היא ש- ISNUMBER יחזיר TRUE אם יש התאמה ו- FALSE אם לא.



בדוגמה זו, המטרה היא לבדוק אם יש יותר ממחרוזת אחת, ולכן אנו נותנים לפונקציית SEARCH a רשימת מחרוזות בתוך ה טווח בשם דברים . מכיוון שיש 3 מחרוזות ב דברים ('אדום', 'ירוק' ו'כחול '), SEARCH מחזיר 3 תוצאות ב מַעֲרָך ככה:

סך ימי העבודה בשנה
 
 ISNUMBER ( SEARCH (substring,B5)) // test for substring

מכיוון שלא נמצאו 'אדום' ו'כחול ', החיפוש מחזיר ערך #! שְׁגִיאָה. אולם מכיוון ש'ירוק 'מופיע בסמוך לסוף הטקסט בתא B5, SEARCH מחזיר 23 (כלומר' ירוק 'מתחיל בתו ה -23).

מערך זה מוחזר ישירות ל- הפונקציה ISNUMBER , הממיר את הפריטים במערך ל- TRUE או FALSE:

 
{#VALUE!#VALUE!23}

באופן הגיוני, אם יש לנו אפילו TRUE אחד במערך, אנו יודעים שתא מכיל לפחות אחד מהמחרוזות שאנו מחפשים. הדרך הקלה ביותר לבדוק אם הוא TRUE היא להוסיף את כל הערכים יחד. אנחנו יכולים לעשות את זה עם פונקציית SUMPRODUCT , אבל ראשית עלינו לכפות את ערכי TRUE / FALSE לשניות 1 ו -0 עם a שלילי כפול (--) ככה:

 
 ISNUMBER ({#VALUE!#VALUE!23}) // returns {FALSEFALSETRUE}

זה מניב מערך חדש המכיל 1s ו- 0s בלבד:

כיצד להמיר קובץ txt להצטיין - -
 
--{FALSEFALSETRUE} // coerce to 1s and 0s

אשר מועבר ישירות ל- SUMPRODUCT:

 
{001}

עם רק מערך אחד לעיבוד, SUMPRODUCT מסכם את הפריטים במערך ומחזיר תוצאה. כל תוצאה שאינה אפס פירושה שיש לנו 'להיט', לכן אנו מוסיפים> 0 כדי לאלץ תוצאה סופית של TRUE או FALSE:

 
= SUMPRODUCT ({001}) // returns 1

שים לב שכל שילוב של התאמות יחזיר מספר גדול מאפס ויגרום לנוסחה להחזיר TRUE.

עם רשימה מקודדת

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

 
= SUMPRODUCT ({001})>0 // returns TRUE

פונקציית SUM

מבחינה היסטורית, SUMPRODUCT מופיע לעתים קרובות ב- נוסחאות מערך מכיוון שהוא יכול להתמודד עם מערכים באופן מקורי, ללא שליטה + shift + enter . זה הופך את הנוסחה לידידותית יותר לרוב המשתמשים. ב Excel 365 , המטפל במערכים באופן מקורי , ה פונקציית SUM ניתן להשתמש במקום SUMPRODUCT ללא שליטה + shift + enter:

להפריד מספרים מהטקסט ב- Excel
 
= SUMPRODUCT (-- ISNUMBER ( SEARCH ({'red','blue','green'},B5)))>0

מניעת התאמות שווא

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

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

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

קבצים מצורפים קוֹבֶץ התא מכיל אחד מני הרבה דברים. xlsx הסופר דייב בראנס


^