מיזוג קבצי אקסל ע"י השוואת נתונים

[בערל]

יש לי שתי טבלאות נתונים בשתי קבצים נפרדים של אקסל.
האם יש אפשרות למזג אותם ע"י השוואת נתונים של עמודה מסוימת?

לדוגמה:
בקובץ מספר 1 יש לי:
עמודה A - שם פרטי
עמודה B - שם משפחה
עמודה C - מספר טלפון

בקובץ מספר 2 יש לי:
עמודה A - מספר טלפון
עמודה B - כתובת
עמודה C - עיר

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

יש אפשרות למיזוג הנתונים של קובץ מספר 1 לקובץ מספר 2 אבל רק בתנאי שמספר הטלפון זהה?

צריך משהו שיעבור על כל שורה בנפרד בקובץ מספר 2
ישלוף את המספר מעמודה A
יחפש אותו בקובץ מספר 1
אם קיים - מעביר את הנתונים מעמודות B, C לעמודות D, E בקובץ מספר 1
אם לא קיים - ממשיך הלאה לשורה הבאה...

אודה לעזרת מומחי האקסל כאן
תודה רבה!

[dovid]

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

כעת לחץ לחיצה כפולה על כל שדה בשני הטבלאות שאתה רוצה שיוצג,
כשאתה גומר לחץ בכרטסת עיצוב על "תצוגה".

[איש ימיני]

@בערל
קודם שאתה עושה את זה שים לב שאין לך כפילויות במספר טלפון.

[בערל]

@dovid אמר במיזוג קבצי אקסל ע"י השוואת נתונים:

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

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

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

פתח אקסס, צור מסד חדש איפה שבא לך.

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

אח"כ בכרטסת יצירה בחר בעיצוב שאילתה, בתיבה הנפתחת לחץ לחיצה כפולה על כל אחת מהשניים שייבאת וסגור.

כעת יש לך שתי תיבות שמייצגות את הטבלאות.

כעת בחר בטבלה של הקובץ העיקרי (זה שאתה רוצה את כל שורותיו ולא רק את התואמים)

את השדה של הטלפון וגרור אותו לעבר השדה המקביל בטבלה הנוספת.

אחרי הגרירה יהיה חוט שחור, לחת עליו כפול ובחר באפשרות השניה.

כעת לחץ לחיצה כפולה על כל שדה בשני הטבלאות שאתה רוצה שיוצג,

כשאתה גומר לחץ בכרטסת עיצוב על "תצוגה".

אנסה ואעדכן

אף פעם לא נגעתי באקסס

[בערל]

@איש-ימיני אמר במיזוג קבצי אקסל ע"י השוואת נתונים:

@בערל
קודם שאתה עושה את זה שים לב שאין לך כפילויות במספר טלפון.

מדובר בID יחודי לכל שורה.

[אוריי]

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

עריכה: הסבר בפוסט הבא

[אוריי]

ואפרט יותר מה הכלי הזה עושה
גילוי נאות: הוא עובד רק בגוגל שיטס ואינו נתמך באקסל אופיס של מיקרוסופט

יש להכניס את הטבלאות

בתא d1 של גיליון 1 יש להכניס את הנוסחה הזו

=FILTER('גיליון2'!B:B,C1='גיליון2'!A:A)

ובתא e1 של גיליון 1 יש להכניס את הנוסחה הזו

=FILTER('גיליון2'!C:C,C1='גיליון2'!A:A)

ופשוט לגרור לכל אורך הטבלה

והנה התוצאה

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

@בערל , בהצלחה!!

לדוגמא שלחו בקשה בקישור הבא ואני יפתח (כדי שלא יחסם בנט פריי)

[UA]

פוסט זה נמחק!

[בערל]

@dovid @אוריי

ממש תודה רבה לכם
ניסיתי כעת את שני הדרכים ושניהם עובדים מעולה ב''ה ועושים בדיוק את מה שרציתי

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

@אוריי
איך זה שאין את האופציה באקסל?
תמיד חשבתי שאקסל יותר משוכלל, לא?

[אוריי]

@בערל אמר במיזוג קבצי אקסל ע"י השוואת נתונים:

@אוריי
איך זה שאין את האופציה באקסל?
תמיד חשבתי שאקסל יותר משוכלל, לא?

@בערל יש את זה באופיס בענן של מייקרוסופט (שזה בתשלום)

אבל גוגל שחררו לנו את זה בחינם.... (חלק מהתחרות )

חוץ מזה תמיד ששאלו אותי מה יותר טוב

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

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

[OdedDvir]

@בערל אמר במיזוג קבצי אקסל ע"י השוואת נתונים:

איך זה שאין את האופציה באקסל?

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

אפשר גם להוריד כתוסף לגרסאות יותר ישנות, אך שים לב שלא תמיד ניתן להעביר את הקובץ בין גרסאות שונות של אקסל.

[אוריי]

@בערל אמר במיזוג קבצי אקסל ע"י השוואת נתונים:

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

אתה יכול פשוט לכתוב בטבלה השניה בעמודה נפרדת את הנוסחה הבאה

=COUNTIF('גיליון1'!C:C,A1)

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

ואז מה שיוצא 1 זה אומר שהוא נכנס לטבלה הראשית ומה שיוצא 0 הוא לא נמצא בטבלה הראשית
כמובן שאתה יכול לסנן את העמודה רק לפי 0

בהצלחה

[WWW]

@בערל יש לאקסל תכונה מיוחדת למיזוג קבצים.
וכמו ש @OdedDvir כתב, ב2016 יש את זה ודאי.
אבל אני חושב שהיה גם לפני.
הרשת מלא עם מדריכים לזה, עשיתי את זה פעם בצורה מאד מתקדמת עם אשף (זה היה באופיס 365).

[dovid]

@www לומר "אפשר" זה תגובה בלתי מועילה.
לא בא לך להפשיל שרוולים? אז אל תגיב בכלל.

[בערל]

אני כעת נזקק שוב לפעולת איחוד כזאת

הבעיה היא שיש כפילויות ב-ID
כלומר יש ID שקיימות פעמיים עם נתונים שונים.

@dovid @OdedDvir מהי המדיניות של השאילתא הנ''ל באקסס במקרה כזה?
מדלג על הכפילויות? בוחר באקראיות? בוחר בראשון?

עריכה:
בדקתי וגיליתי את הלא הצפוי בכלל
הוא פשוט שכפל לי את השורות שקיימות בהם נתונים שונים...

[OdedDvir]

@בערל בצירוף טבלאות, אם מזהה חוזר על עצמו יותר מפעם אחת, הצירוף יבוצע לכל רשומה.
לדוגמא:

טבלה A (השדה הראשון זה המפתח)
1 אברהם
2 יצחק
2 יעקב

טבלה B
1 כהן
2 לוי

הצירופים שיתקבלו הם
אברהם כהן
יצחק לוי
יעקב לוי

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

[OdedDvir]

@בערל אמר במיזוג קבצי אקסל ע"י השוואת נתונים:

הבעיה היא שיש כפילויות ב-ID
כלומר יש ID שקיימות פעמיים עם נתונים שונים.

נשמח אם תפרש איך זה קרה: האם זו טעות בנתונים או בכוונה תחילה?
ומה אתה מבקש להשיג. אולי נמצא פתרון לבעיה.

[בערל]

@odeddvir

בקיצור שאילתת אקסס לא רלוונטית בשבילי הפעם
וגם הרעיון של @אוריי בגוגל שיטס כנראה לא תועיל

אני יסביר יותר את המקרה:
יש לי קובץ אחד עם:

• ID
• שם פרטי
• שם משפחה
• כתובת
• טלפון נייח

קובץ שני עם:

• ID
• שם פרטי
• שם משפחה
• כתובת
• פלאפון נייד

בקובץ מספר שתיים יש הרבה כפילויות
חלקם עם שינוי באיות השם וחלם כי יש להם שני פלאפונים ניידים

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

אפשרי?

[בערל]

@odeddvir אמר במיזוג קבצי אקסל ע"י השוואת נתונים:

נשמח אם תפרש איך זה קרה: האם זו טעות בנתונים או בכוונה תחילה?
ומה אתה מבקש להשיג. אולי נמצא פתרון לבעיה.

פירטתי עכשיו שוב
הקובץ השני הוא קיבוץ של הרבה קבצים קטנים וקשה מאוד לסנן כפילויות בגלל שינוי איות וכו'

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

תודה רבה!

[OdedDvir]

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

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

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

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

אני יכול לבחור את הרשומה הראשונה
אני יכול לבחור את הרשומה האחרונה
אני יכול לעבור אחת אחת ולמחוק...