לְהִצטַיֵן

מצא והחליף מספר ערכים

Find Replace Multiple Values

נוסחת Excel: מצא והחלף מספר ערכיםנוסחה כללית | _+_ | סיכום

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





= SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2))

היכן 'מצא' הוא טווח בשם E5: E8, ו'החלפה 'הוא הטווח בשם F5: F8. ראה להלן מידע כיצד להפוך את הנוסחה לקלה יותר לקריאה.

הַקדָמָה

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





הֶסבֵּר

בליבה, הנוסחה משתמשת בפונקציה SUBSTITUTE לביצוע כל החלפה, עם דפוס בסיסי זה:

 
= SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2)), INDEX (find,3), INDEX (replace,3)), INDEX (find,4), INDEX (replace,4))

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



כיצד להזין פונקציית if ב- Excel
 
= SUBSTITUTE (text,find,replace)

אז, כדי להפעיל את ההחלפה הראשונה (חפש 'אדום', החלף ב'ורוד ') אנו משתמשים:

 
 INDEX (find,1) // first 'find' value  INDEX (replace,1) // first 'replace' value

בסך הכל, אנו מפעילים ארבעה תחליפים נפרדים, וכל תחליף הבא מתחיל בתוצאה מהמחליף הקודם:

אם גדול או שווה ל-
 
= SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1))

מעברי שורות לקריאה

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

 
= SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2)), INDEX (find,3), INDEX (replace,3)), INDEX (find,4), INDEX (replace,4))

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

שורות שורות נוספו בשורת הנוסחאות לקריאה ותחזוקה

אגב, יש א קיצור מקשים להרחבה וקריסה של שורת הנוסחאות.

עוד החלפות

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

שימושים אחרים

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

הסופר דייב בראנס


^