מיזוג שתי רשימות ללא כפילויות

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

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

שיטה 1: הסר כפילויות

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

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

שיטה 1א. טבלת ציר

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

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

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

שיטה 2: נוסחת מערך

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

לאחר מתן השם, הנוסחה שאנו צריכים תיראה כך:

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

ההיגיון כאן הוא כזה:

• הנוסחה INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) בוחרת את כל האלמנטים הייחודיים מהרשימה הראשונה. ברגע שהם נגמרים, היא מתחילה לתת שגיאה #N/A:

  

• הנוסחה INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2; 0)) מחלצת את האלמנטים הייחודיים מהרשימה השנייה באותו אופן.
• מקוננות זו בזו שתי פונקציות IFERROR מיישמות את הפלט תחילה מהייחודיות מהרשימה-1, ולאחר מכן מהרשימה-2 בזו אחר זו.

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

בגרסה האנגלית של Excel, נוסחה זו נראית כך:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($E$1:E1, List2), 0)) ), "") 

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

שיטה 3. Power Query

אם ברשימות המקורות שלך יש מספר רב של אלמנטים, למשל כמה מאות או אלפים, אז במקום נוסחת מערך איטית, עדיף להשתמש בגישה שונה מהותית, כלומר בכלי תוסף Power Query. תוספת זו מובנית ב- Excel 2016 כברירת מחדל. אם יש לך Excel 2010 או 2013, תוכל להוריד ולהתקין אותם בנפרד (בחינם).

אלגוריתם הפעולות הוא כדלקמן:

1. פתח כרטיסייה נפרדת של התוסף המותקן שאילתת כוח (אם יש לך Excel 2010-2013) או פשוט עבור לכרטיסייה נתונים (אם יש לך אקסל 2016).
2. בחר את הרשימה הראשונה ולחץ על הכפתור מתוך טבלה/טווח (מטווח/טבלה). כשנשאל לגבי יצירת "טבלה חכמה" מהרשימה שלנו, אנו מסכימים:

   

3. חלון עורך השאילתות נפתח, שבו תוכל לראות את הנתונים הטעונים ואת שם השאילתה לוח 1 (אתה יכול לשנות את זה לשלך אם אתה רוצה).
4. לחץ פעמיים על כותרת הטבלה (מילה רשימת 1) ושנה את שמו לכל אחר (לדוגמה אֲנָשִׁים). מה בדיוק לקרוא לא חשוב, אבל יש לזכור את השם המומצא, כי. יהיה צורך להשתמש בו שוב מאוחר יותר בעת ייבוא ​​הטבלה השנייה. מיזוג שתי טבלאות בעתיד יעבוד רק אם כותרות העמודות שלהן תואמות.
5. הרחב את הרשימה הנפתחת בפינה השמאלית העליונה לסגור ולהוריד ולבחור סגור וטען פנימה… (סגור וטען ל...):

   

6. בתיבת הדו-שיח הבאה (זה עשוי להיראות קצת אחרת - אל תיבהל), בחר פשוט צור קשר (יצירת חיבור בלבד):

   

7. אנו חוזרים על כל ההליך (נקודות 2-6) עבור הרשימה השנייה. בעת שינוי שם של כותרת עמודה, חשוב להשתמש באותו שם (People) כמו בשאילתה הקודמת.
8. בחלון Excel בכרטיסייה נתונים או בכרטיסייה שאילתת כוח בחרו קבל נתונים - שלב בקשות - הוסף (קבל נתונים - מיזוג שאילתות - הוסף):

   

9. בתיבת הדו-שיח שמופיעה, בחר את הבקשות שלנו מהרשימות הנפתחות:

   

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

    

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

    

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

• כיצד לאסוף טבלאות מרובות מקבצים שונים באמצעות Power Query
• חילוץ פריטים ייחודיים מרשימה
• כיצד להשוות שתי רשימות זו לזו עבור התאמות והבדלים