החלפת טקסט בכמות גדולה בנוסחאות

נניח שיש לך רשימה שבה, בדרגות שונות של "פשוטות", נכתבים נתונים ראשוניים - למשל, כתובות או שמות חברות:

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

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

מה לעשות? אל תחליף ידנית את הטקסט העקום 100500 פעמים בטקסט הנכון דרך התיבה "מצא והחלף" או על ידי לחיצה Ctrl+H?

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

לרוע המזל, עם השכיחות הברורה של משימה כזו, ל-Microsoft Excel אין שיטות מובנות פשוטות לפתור אותה. מלכתחילה, בואו נבין כיצד לעשות זאת עם נוסחאות, מבלי לערב "ארטילריה כבדה" בצורה של פקודות מאקרו ב-VBA או Power Query.

מארז 1. החלפה מלאה בתפזורת

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

נניח שיש לנו שתי טבלאות:

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

לנוחות:

• שתי הטבלאות מומרות לדינמיות ("חכם") באמצעות קיצור מקשים Ctrl+T או צוות הכנס טבלה (הכנס טבלה).
  
• בלשונית שמופיעה בנאי (לְעַצֵב) השולחן הראשון בשם נתוניםוטבלת ההתייחסות השנייה - החלפות.

כדי להסביר את ההיגיון של הנוסחה, נלך קצת מרחוק.

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

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

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

אם יש לך גרסאות קודמות של Excel, לאחר לחיצה על זן נראה רק את הערך הראשון ממערך התוצאה, כלומר שגיאה #VALUE! (#ערך!).

אתה לא צריך לפחד 🙂 למעשה, הנוסחה שלנו עובדת ועדיין תוכל לראות את כל מערך התוצאות אם תבחר את הפונקציה שהוזנה בשורת הנוסחאות ותלחץ על המקש F9(רק אל תשכח ללחוץ Escכדי לחזור לנוסחה):

מערך התוצאות המתקבל אומר שבשם החברה העקום המקורי (GK Morozko OAO) מכל הערכים בעמודה למצוא מצא רק את השני (מורוזקו), והחל מהתו הרביעי ברציפות.

כעת נוסיף פונקציה לנוסחה שלנו נוף(הבט מעלה):

לפונקציה זו יש שלושה ארגומנטים:

1. ערך רצוי - אתה יכול להשתמש בכל מספר גדול מספיק (העיקר שהוא חורג מהאורך של כל טקסט בנתוני המקור)
   
2. Viewed_vector – הטווח או המערך שבו אנו מחפשים את הערך הרצוי. הנה הפונקציה שהוצגה קודם לכן למצוא, שמחזיר מערך {#VALUE!:4:#VALUE!}
   
3. וֶקטוֹר_תוצאות – הטווח שממנו נרצה להחזיר את הערך אם הערך הרצוי נמצא בתא המתאים. להלן השמות הנכונים מהעמודה תחליף טבלת ההתייחסות שלנו.
   

התכונה העיקרית והלא ברורה כאן היא שהפונקציה נוף אם אין התאמה מדויקת, תמיד מחפש את הערך הקטן ביותר (הקודם) הקרוב ביותר. לכן, על ידי ציון מספר גדול (לדוגמה, 9999) כערך הרצוי, נכריח נוף מצא את התא עם המספר הקטן ביותר (4) במערך {#VALUE!:4:#VALUE!} והחזר את הערך המתאים מווקטור התוצאה, כלומר שם החברה הנכון מהעמודה תחליף.

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

זה הכל. מקווה שהבנת את ההיגיון.

נותר להעביר את הנוסחה המוגמרת לתא הראשון B2 של העמודה תוקן – והמשימה שלנו נפתרה!

כמובן, עם טבלאות רגילות (לא חכמות), גם הנוסחה הזו עובדת מצוין (רק אל תשכחו את המפתח F4 ותיקון הקישורים הרלוונטיים):

מארז 2. החלפה חלקית בתפזורת

המקרה הזה קצת יותר מסובך. שוב יש לנו שתי טבלאות "חכמות":

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

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

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

העבודה העיקרית כאן נעשית על ידי פונקציית הטקסט הסטנדרטית של Excel תחליף (תחליף), שיש לו 3 טיעונים:

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

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

וניתן לראות בבירור (ראה #N/A שגיאות בתמונה הקודמת) שלנוסחה כזו, עם כל האלגנטיות שלה, יש כמה חסרונות:

• פונקציה SUBSTITUTE הוא תלוי רישיות, אז "Spb" בשורה הלפני אחרונה לא נמצא בטבלת ההחלפה. כדי לפתור בעיה זו, אתה יכול להשתמש בפונקציה ZAMENIT (החלף), או להביא מראש את שני הטבלאות לאותו רישום.
• אם הטקסט תקין בתחילה או בתוכו אין שבר להחליף (שורה אחרונה), אז הנוסחה שלנו זורקת שגיאה. ניתן לנטרל את הרגע הזה על ידי יירוט והחלפת שגיאות באמצעות הפונקציה טעות (IFERROR):

  

• אם הטקסט המקורי מכיל כמה קטעים מהספרייה בבת אחת, אז הנוסחה שלנו מחליפה רק את האחרונה (בשורה ה-8, Ligovsky «שדרה« שונה ל "pr-t", אבל "S-Pb" on "רחוב. פטרבורג" כבר לא, כי "S-Pb” נמצא גבוה יותר בספרייה). ניתן לפתור בעיה זו על ידי הפעלה מחדש של הנוסחה שלנו, אך כבר לאורך העמודה תוקן:

  
  

לא מושלם ומסורבל במקומות, אבל הרבה יותר טוב מאותה החלפה ידנית, נכון? 🙂

PS

במאמר הבא, נבין כיצד ליישם החלפה בתפזורת כזו באמצעות פקודות מאקרו ו-Power Query.

• כיצד פועלת הפונקציה SUBSTITUTE כדי להחליף טקסט
  
• מציאת התאמות טקסט מדויקות באמצעות הפונקציה EXACT
• חיפוש והחלפה תלויי רישיות (VLOOKUP תלוי רישיות)