לאחרונה, חבר שלי פנה אלי בבקשה לעזור ביצירת כל הביטויים האפשריים המורכבים מקבוצה של מילים נתונות. בעיות מסוג זה יכולות להתעורר בעת חיבור רשימות של מילות מפתח וביטויים לפרסום מקוון וקידום קידום אתרים, כאשר צריך לעבור על כל התמורות האפשריות של מילים בשאילתת חיפוש:
במתמטיקה, פעולה זו נקראת מכפלה קרטזית. ההגדרה הרשמית היא כדלקמן: המכפלה הקרטזיאנית של קבוצות A ו-B היא קבוצת כל הזוגות, שהרכיב הראשון שלה שייך לקבוצה A, והרכיב השני שייך לקבוצה B. יתרה מכך, המרכיבים של קבוצות יכולים להיות שניהם מספרים וטקסט.
בתרגום לשפה אנושית, זה אומר שאם בקבוצה A יש לנו, למשל, את המילים "לבן" ו"אדום", ובקבוצה B "BMW" ו"מרצדס", אז אחרי המכפלה הקרטזיאנית של שתי הקבוצות הללו. get on the output הוא קבוצת כל הגרסאות האפשריות של ביטויים, המורכבת מהמילים של שתי הרשימות:
- BMW לבן
- אדום bmw
- מרצדס לבנה
- מרצדס אדומה
... כלומר בדיוק מה שאנחנו צריכים. בואו נסתכל על כמה דרכים לפתור משימה זו באקסל.
שיטה 1. נוסחאות
נתחיל עם נוסחאות. הבה נניח שכנתונים ראשוניים יש לנו שלוש רשימות של מילים מקוריות בעמודות A, B ו-C, בהתאמה, ומספר האלמנטים בכל רשימה יכול להשתנות:
ראשית, בואו נעשה שלוש עמודות עם מדדים, כלומר מספרים סידוריים של מילים מכל רשימה בכל הצירופים האפשריים. שורת היחידות הראשונה (E2:G2) תוכנס ידנית, ולשאר נשתמש בנוסחה הבאה:
ההיגיון כאן פשוט: אם האינדקס בתא הקודם העליון כבר הגיע לסוף הרשימה, כלומר שווה למספר האלמנטים ברשימה המחושבים על ידי הפונקציה COUNT (COUNTA), ואז נתחיל מחדש את המספור. אחרת, אנו מעלים את המדד ב-1. שימו לב במיוחד לתיקון החכם של הטווחים עם סימני דולר ($) כך שתוכלו להעתיק את הנוסחה למטה וימינה.
עכשיו כשיש לנו את המספרים הסידוריים של המילים שאנחנו צריכים מכל רשימה, אנחנו יכולים לחלץ את המילים עצמן באמצעות הפונקציה מדד (אינדקס) לשלוש עמודות נפרדות:
אם לא נתקלת בפונקציה הזו בעבודה שלך בעבר, אז אני ממליץ לך בחום ללמוד אותה לפחות באלכסון - היא עוזרת במצבים רבים ומועילה לא פחות (ואפילו יותר!) VPR (VLOOKUP).
ובכן, לאחר מכן, נותר רק להדביק את השברים המתקבלים שורה אחר שורה באמצעות סמל השרשור (&):
... או (אם יש לך את הגרסה העדכנית ביותר של Excel) עם הפונקציה השימושית לְשַׁלֵב (TEXTJOIN), שיכול להדביק את כל התוכן של התאים שצוינו דרך תו מפריד נתון (רווח):
שיטה 2. באמצעות Power Query
Power Query הוא תוסף רב עוצמה עבור Microsoft Excel המבצע שתי משימות עיקריות: 1. טעינת נתונים לאקסל כמעט מכל מקור חיצוני, ו-2. כל מיני טרנספורמציות של טבלאות טעונות. Power Query כבר מובנה ב-Excel 2016-2019, ועבור Excel 2010-2013 הוא מותקן כתוסף נפרד (תוכל להוריד אותו מהאתר הרשמי של מיקרוסופט בחינם). אם עדיין לא התחלת להשתמש ב-Power Query בעבודה שלך, אז הגיע הזמן לחשוב על זה, כי טרנספורמציות כמו אלה שתוארו למעלה נעשות שם בקלות ובטבעיות, בכמה תנועות בלבד.
ראשית, בואו נטען את רשימות המקורות כשאילתות נפרדות ב-Power Query. לשם כך, עבור כל טבלה, בצע את השלבים הבאים:
- בואו נהפוך שולחנות ל"חכמים" עם כפתור עיצוב כטבלה כרטיסייה עמוד הבית (בית - עיצוב כטבלה) או קיצור מקלדת Ctrl+T. כל שולחן יקבל שם אוטומטית טבלה 1,2,3…, אשר, עם זאת, ניתן לשנות אם תרצה בכרטיסייה בנאי (לְעַצֵב).
- לאחר הגדרת התא הפעיל בטבלה, לחץ על הלחצן מהשולחן (מהטבלה) כרטיסייה נתונים (תַאֲרִיך) או בכרטיסייה שאילתת כוח (אם הוא מותקן כתוסף נפרד עבור Excel 2010-2013).
- בחלון עורך השאילתות שנפתח, בחר את הפקודה בית - סגור וטען - סגור וטען פנימה... (בית — סגור&טען — סגור&טען ל..) ואז האפשרות פשוט צור קשר (צור רק חיבור). זה ישאיר את הטבלה הטעונה בזיכרון ויאפשר גישה אליה בעתיד.
אם אתה עושה הכל נכון, הפלט בלוח הימני צריך להיות שלוש בקשות במצב חיבור בלבד עם שמות הטבלה שלנו:
כעת לחץ לחיצה ימנית על השאילתה הראשונה ובחר את הפקודה קישור (התייחסות)כדי ליצור עותק שניתן לעדכן שלו, ולאחר מכן להוסיף עמודה נוספת לנתונים באמצעות הפקודה הוספת עמודה ž – עמודה מותאמת אישית (הוסף עמודה -ž עמודה מותאמת אישית). בחלון קלט הנוסחה, הזן את שם העמודה החדשה (לדוגמה, Fragment2) וביטוי פשוט ביותר כנוסחה:
=טבלה2
… כלומר, במילים אחרות, שם השאילתה השנייה:
לאחר לחיצה על OK נראה עמודה חדשה, שבכל תא שלה תהיה טבלה מקוננת עם ביטויים מהטבלה השנייה (תוכל לראות את תוכן הטבלאות הללו אם תלחץ ברקע התא ליד המילה טבלתי):
נותר להרחיב את כל התוכן של הטבלאות המקוננות הללו באמצעות הכפתור עם החצים הכפולים בכותרת העמודה המתקבלת ובטל את הסימון השתמש בשם העמודה המקורי בתור קידומת (השתמש בשם העמודה המקורי בתור קידומת):
... ואנו מקבלים את כל השילובים האפשריים של אלמנטים משתי הקבוצות הראשונות:
יתר על כן, הכל דומה. הוסף עוד עמודה מחושבת עם הנוסחה:
=טבלה3
..., ואז הרחב שוב את הטבלאות המקוננות - ועכשיו כבר יש לנו את כל האפשרויות האפשריות לשינוי מילים משלושת הקבוצות, בהתאמה:
נותר לבחור את כל שלוש העמודות משמאל לימין, והחזק Ctrl, ושרשר את תוכנם מופרד על ידי רווחים באמצעות הפקודה מיזוג עמודות (מיזוג עמודות) מהכרטיסייה טרנספורמציה (שינוי צורה):
את התוצאות המתקבלות ניתן לפרוק בחזרה לגיליון עם הפקודה המוכרת כבר בית - סגור וטען - סגור וטען פנימה... (בית — סגור&טען — סגור&טען ל..):
אם בעתיד משהו ישתנה בטבלאות המקור שלנו עם פרגמנטים, אז זה יספיק רק לעדכן את השאילתה שנוצרה על ידי לחיצה ימנית על הטבלה המתקבלת ובחירה בפקודה עדכן ושמור (לְרַעֲנֵן) או על ידי לחיצה על קיצור המקלדת Ctrl+אחר+F5.
- מה זה Power Query, Power Pivot, Power Map ו-Power BI ומדוע הם צריכים משתמש אקסל
- יצירת תרשים גנט ב-Power Query
- 5 דרכים להשתמש בפונקציית INDEX