לאחרונה, דנו בשימוש בפונקציית FILTER.XML לייבוא נתוני XML מהאינטרנט - המשימה העיקרית שעבורה נועדה פונקציה זו, למעשה. עם זאת, לאורך הדרך צץ עוד שימוש בלתי צפוי ויפה בפונקציה הזו - לחלוקה מהירה של טקסט דביק לתאים.
נניח שיש לנו עמודת נתונים כזו:
כמובן, מטעמי נוחות, ארצה לחלק אותו לטורים נפרדים: שם חברה, עיר, רחוב, בית. אתה יכול לעשות זאת בכמה דרכים שונות:
- השתמש טקסט לפי עמודות מהכרטיסייה נתונים (נתונים - טקסט לעמודות) ולעבור שלושה שלבים מנתח טקסט. אבל אם הנתונים ישתנו מחר, תצטרך לחזור על כל התהליך שוב.
- טען את הנתונים האלה לתוך Power Query וחלק אותם שם, ולאחר מכן העלה אותם בחזרה לגיליון, ולאחר מכן עדכן את השאילתה כאשר הנתונים משתנים (וזה כבר יותר קל).
- אם אתה צריך לעדכן תוך כדי תנועה, אז אתה יכול לכתוב כמה נוסחאות מורכבות מאוד כדי למצוא פסיקים ולחלץ את הטקסט ביניהן.
ואתה יכול לעשות את זה בצורה אלגנטית יותר ולהשתמש בפונקציה FILTER.XML, אבל מה זה קשור לזה?
הפונקציה FILTER.XML מקבלת כארגומנט הראשוני שלה קוד XML - טקסט המסומן בתגיות ותכונות מיוחדות, ולאחר מכן מנתחת אותו לרכיבים שלה, מחלצת את קטעי הנתונים הדרושים לנו. קוד ה-XML בדרך כלל נראה בערך כך:
ב-XML, כל רכיב נתונים חייב להיות מוקף בתגים. תג הוא טקסט כלשהו (בדוגמה למעלה הוא מנהל, שם, רווח) המוקף בסוגריים זווית. תגיות תמיד מגיעות בזוגות - פתיחה וסגירה (עם קו נטוי נוסף להתחלה).
הפונקציה FILTER.XML יכולה לחלץ בקלות את התוכן של כל התגים שאנו צריכים, למשל, שמות כל המנהלים, ו(הכי חשוב) להציג את כולם בבת אחת ברשימה אחת. אז המשימה שלנו היא להוסיף תגיות לטקסט המקור, ולהפוך אותו לקוד XML המתאים לניתוח שלאחר מכן על ידי הפונקציה FILTER.XML.
אם ניקח את הכתובת הראשונה מהרשימה שלנו כדוגמה, אז נצטרך להפוך אותה למבנה הזה:
התקשרתי לתגית הפתיחה והסגירה הגלובלית של כל הטקסט t, והתגים הממסגרים כל אלמנט הם s., אבל אתה יכול להשתמש בכל ייעוד אחר - זה לא משנה.
אם נסיר כניסות ומעברי שורות מהקוד הזה - לגמרי, אגב, אופציונלי ומוסיפים רק לשם הבהירות, אז כל זה יהפוך לשורה:
וכבר ניתן להשיג אותו בקלות יחסית מכתובת המקור על ידי החלפת פסיקים בה בכמה תגים באמצעות הפונקציה תחליף (תחליף) והדבקה עם הסמל & בתחילת ובסוף תגי הפתיחה והסגירה:
כדי להרחיב את הטווח המתקבל אופקית, אנו משתמשים בפונקציה הסטנדרטית TRANSP (לְשַׁרבֵּב), עוטפים את הנוסחה שלנו בה:
תכונה חשובה של כל העיצוב הזה היא שבגרסה החדשה של Office 2021 ו-Office 365 עם תמיכה במערכים דינמיים, אין צורך בתנועות מיוחדות לקלט - פשוט היכנס ולחץ על זן – הנוסחה עצמה תופסת את מספר התאים שהיא צריכה והכל עובד ברעש גדול. בגרסאות קודמות, בהן עדיין לא היו מערכים דינמיים, תצטרך לבחור תחילה מספר מספיק של תאים ריקים לפני הזנת הנוסחה (אפשר עם שוליים), ולאחר יצירת הנוסחה, ללחוץ על קיצור המקלדת. Ctrl+משמרת+זןכדי להזין אותו כנוסחת מערך.
ניתן להשתמש בטריק דומה בעת הפרדת טקסט הדבוק יחד לתא אחד באמצעות מעבר שורה:
ההבדל היחיד עם הדוגמה הקודמת הוא שבמקום פסיק, כאן אנו מחליפים את תו מעבר השורה הבלתי נראה Alt + Enter, אותו ניתן לציין בנוסחה באמצעות הפונקציה CHAR בקוד 10.
- הדקויות של עבודה עם מעברי שורה (Alt + Enter) באקסל
- חלקו טקסט לפי עמודות באקסל
- מחליף טקסט ב-SUBSTITUTE