חיפוש מילות מפתח בטקסט מקור הוא אחת המשימות הנפוצות ביותר בעבודה עם נתונים. בואו נסתכל על הפתרון שלו בכמה דרכים באמצעות הדוגמה הבאה:
נניח שיש לך ולי רשימה של מילות מפתח – שמות מותגי רכב – וטבלה גדולה של כל מיני חלקי חילוף, שבה תיאורים יכולים לפעמים להכיל אחד או כמה מותגים כאלה בו-זמנית, אם חלק החילוף מתאים ליותר מאחד. מותג רכב. המשימה שלנו היא למצוא ולהציג את כל מילות המפתח שזוהו בתאים שכנים באמצעות תו מפריד נתון (לדוגמה, פסיק).
שיטה 1. Power Query
כמובן, ראשית אנו הופכים את הטבלאות שלנו לדינמיות ("חכמות") באמצעות קיצור מקלדת Ctrl+T או פקודות בית - עיצוב כטבלה (בית - עיצוב כטבלה), תן להם שמות (לדוגמה בוליםи חלפים) וטען אחד אחד לעורך Power Query על ידי בחירה בכרטיסייה נתונים - מטבלה/טווח (נתונים - מטבלה/טווח). אם יש לך גרסאות ישנות יותר של Excel 2010-2013, שבהן Power Query מותקן כתוסף נפרד, הלחצן הרצוי יהיה בכרטיסייה שאילתת כוח. אם יש לך גרסה חדשה לגמרי של Excel 365, אז הכפתור מתוך טבלה/טווח התקשר לשם עכשיו עם עלים (מתוך גיליון).
לאחר טעינת כל טבלה ב-Power Query, נחזור ל-Excel עם הפקודה בית — סגור וטען — סגור וטען ל... — צור חיבור בלבד (בית - סגור וטען - סגור וטען ל... - צור רק חיבור).
עכשיו בואו ניצור בקשה כפולה חלפיםעל ידי לחיצה ימנית עליו ובחירה בקשה כפולה (שאילתה כפולה), ולאחר מכן שנה את שם בקשת ההעתקה שהתקבלה ל את התוצאות ונמשיך לעבוד איתו.
ההיגיון של הפעולות הוא הבא:
- בכרטיסיה מתקדם הוספת עמודה לבחור צוות עמודה מותאמת אישית (הוסף עמודה - עמודה מותאמת אישית) והזן את הנוסחה = מותגים. לאחר לחיצה על OK נקבל עמודה חדשה, שבה בכל תא תהיה טבלה מקוננת עם רשימה של מילות המפתח שלנו - מותגי יצרני רכב:
- השתמש בלחצן עם החצים הכפולים בכותרת העמודה שנוספה כדי להרחיב את כל הטבלאות המקוננות. במקביל, השורות עם תיאורים של חלקי חילוף יוכפלו בכפולה של מספר המותגים, ונקבל את כל הזוגות-שילובים האפשריים של "חלקי חילוף-מותג":
- בכרטיסיה מתקדם הוספת עמודה לבחור צוות עמודה מותנית (עמודה מותנית) והגדר תנאי לבדיקת התרחשות של מילת מפתח (מותג) בטקסט המקור (תיאור חלק):
- כדי להפוך את רישיות החיפוש ללא רגישות, הוסף ידנית את הארגומנט השלישי בשורת הנוסחאות Compare.OrdinalIgnoreCase לפונקציית בדיקת התרחשות טקסט.מכיל (אם סרגל הנוסחאות אינו גלוי, ניתן להפעיל אותו בכרטיסייה סקירה):
- אנחנו מסננים את הטבלה המתקבלת, משאירים רק כאלה בעמודה האחרונה, כלומר תואמים ומסירים את העמודה המיותרת התרחשויות.
- קיבוץ תיאורים זהים עם הפקודה לפי קבוצה כרטיסייה טרנספורמציה (שינוי - קבץ לפי). כפעולת צבירה, בחר כל השורות (כל השורות). בפלט, אנו מקבלים עמודה עם טבלאות, המכילה את כל הפרטים עבור כל חלק חילוף, כולל מותגי יצרניות הרכב שאנו צריכים:
- כדי לחלץ ציונים עבור כל חלק, הוסף עמודה מחושבת נוספת בכרטיסייה הוספת עמודה - עמודה מותאמת אישית (הוסף עמודה - עמודה מותאמת אישית) והשתמש בנוסחה המורכבת מטבלה (הן ממוקמות בעמודה שלנו פרטים) והשם של העמודה שחולצה:
- אנו לוחצים על הכפתור עם חיצים כפולים בכותרת העמודה המתקבלת ובוחרים את הפקודה חלץ ערכים (חלץ ערכים)לפלט חותמות עם כל תו מפריד שתרצה:
- הסרת עמודה מיותרת פרטים.
- כדי להוסיף לטבלה המתקבלת את החלקים שנעלמו ממנה, שבהם לא נמצאו מותגים בתיאורים, אנו מבצעים את הליך שילוב השאילתה תוֹצָאָה עם בקשה מקורית חלפים לַחְצָן לשלב כרטיסייה עמוד הבית (בית - מיזוג שאילתות). סוג חיבור - חיבור חיצוני ימינה (חיבור חיצוני ימני):
- כל שנותר הוא להסיר את העמודות הנוספות ולשנות את שמותיהם-להזיז את הנותרות - והמשימה שלנו נפתרה:
שיטה 2. נוסחאות
אם יש לך גרסה של Excel 2016 ומעלה, אז ניתן לפתור את הבעיה שלנו בצורה מאוד קומפקטית ואלגנטית באמצעות הפונקציה החדשה לְשַׁלֵב (TEXTJOIN):
ההיגיון מאחורי נוסחה זו הוא פשוט:
- פונקציה לחפש (למצוא) מחפש את המופע של כל מותג בתורו בתיאור הנוכחי של החלק ומחזיר את המספר הסידורי של הסמל, שמתחיל ממנו נמצא המותג, או את השגיאה #VALUE! אם המותג אינו בתיאור.
- לאחר מכן באמצעות הפונקציה IF (אם) и EOSHIBKA (שגיאה) אנו מחליפים את השגיאות במחרוזת טקסט ריקה "", ואת המספרים הסידוריים של התווים בשמות המותג עצמם.
- המערך המתקבל של תאים ריקים ומותגים שנמצאו מורכב למחרוזת אחת באמצעות תו מפריד נתון באמצעות הפונקציה לְשַׁלֵב (TEXTJOIN).
השוואת ביצועים ו-Power Query Buffering for Speedup
לבדיקת ביצועים, ניקח טבלה של 100 תיאורי חלקי חילוף כנתונים ראשוניים. על זה נקבל את התוצאות הבאות:
- זמן חישוב מחדש לפי נוסחאות (שיטה 2) – 9 שניות. כאשר אתה מעתיק לראשונה את הנוסחה לכל העמודה ו-2 שניות. בפעולה חוזרת (החצצה משפיעה, כנראה).
- זמן העדכון של שאילתת Power Query (שיטה 1) גרוע בהרבה - 110 שניות.
כמובן, הרבה תלוי בחומרה של מחשב מסוים ובגרסה המותקנת של Office ועדכונים, אבל התמונה הכללית, לדעתי, ברורה.
כדי להאיץ שאילתת Power Query, הבה נאחסן את טבלת החיפוש בולים, מכיוון שהוא לא משתנה בתהליך ביצוע השאילתה ואין צורך לחשב אותו מחדש כל הזמן (כפי שעושה דה-פקטו Power Query). לשם כך אנו משתמשים בפונקציה טבלה. מאגר משפת Power Query המובנית M.
כדי לעשות זאת, פתח שאילתה את התוצאות ועל הכרטיסייה סקירה לחץ על הכפתור עורך מתקדם (תצוגה - עורך מתקדם). בחלון שנפתח, הוסף שורה עם משתנה חדש מארקי 2, שתהיה גרסה מאוחסנת של ספריית יצרן הרכב שלנו, ותשתמש במשתנה החדש הזה מאוחר יותר בפקודת השאילתה הבאה:
לאחר חידוד כזה, מהירות העדכון של הבקשה שלנו עולה בכמעט פי 7 - עד 15 שניות. דבר אחר לגמרי 🙂
- חיפוש טקסט מעורפל ב-Power Query
- החלפת טקסט בכמות גדולה בנוסחאות
- החלפת טקסט בכמות גדולה ב-Power Query עם פונקציית List.Accumulate