איחוד ומיזוג קבצים וטבלאות באקסל
במדריך זה תוכלו ללמוד כיצד ניתן לאחד מידע מטבלאות שונות או מקבצים שונים ולרכז את כל המידע במקום אחד.
ראשית נפתח בהסבר למושג "איחוד מידע", שהוא כשלעצמו מושג נרחב וזאת, מאחר והמידע יכול להיות מאורגן בצורות שונות. כמו כן, הצורה בה נרצה להציג את המידע לאחר שאיחדנו אותו יכולה להראות במספר צורות שונות.
בנוסף, ישנם תרחישים שנרצה לאחד מידע באופן חד פעמי ולפעמים באופן קבוע, יש תרחישים בהם נרצה שהטבלה המאוחדת תתעדכן אוטומטית, כאשר המידע מהטבלאות השונות מתעדכן כל הזמן, ויש מקרים שלא נרצה זאת. ישנם תרחישים שנרצה לאחד קבצים חדשים שמגיעים באופן שוטף ממקורות שונים אחת ל X זמן ולפעמים נפנה לאותם קבצים.
כאמור אלו הם רק חלק מהתרחישים האפשריים ולא נוכל לסקור כאן את כולם, אבל כן נסביר כיצד ניתן להתמודד עם משימות נפוצות שמצריכות איחוד טבלאות ונציג מספר שיטות אפשריות לפתרון.
את המושג "איחוד מידע" ניתן לחלק לשתי צורות עיקריות:
האחת, מיזוג ברמת העמודות והשנייה, איחוד ברמת השורות.
איחוד ברמת העמודות שיותר נכון לכנות אותו: "מיזוג ברמת העמודות", אומר מיזוג של שתי טבלאות או יותר שיש להן עמודה משותפת כלשהי שדרכה נמזג את כל העמודות יחד לטבלה אחת.
איחוד ברמת השורות אומר: איחוד של שתי טבלאות או יותר בעלות שדות משותפים, ובכך נאחד את כל המידע מהטבלאות השונות לטבלה אחת ארוכה.
במדריך זה נסביר כיצד לאחד ולמזג שורות ועמודות הן באמצעות פתרונות נוסחתיים, כלומר שילוב של פונקציה אקסלית אחת או יותר, והן באמצעות תוספים מובנים שבתוכנת האקסל.
חשוב לדעת !!
מיזוג ברמת העמודות לרוב יותר פשוט לביצוע מאשר איחוד ברמת השורות, וזאת מאחר וישנם הרבה יותר תרחישים לצורה בה המידע יאוחד ברמת השורות. כאשר לתרחישים רבים אין פתרון מובנה שיטפל באיחוד השורות, ופתרון נוסחתי עלול להיות לא גמיש מספיק ומאוד מורכב. במקרים כאלו תוכלו לבדוק אודות תוכנת ״קומבט״ – איחוד קבצי אקסל, שהיא תוכנה שפותחה במיוחד עבור כל סוג דרישה לאיחוד מידע, מטבלאות שונות או מקבצים שונים ברמת השורות.
מיזוג ברמת העמודות:
בגיליון הבא נדגים כיצד ניתן למזג טבלאות ברמת העמודות.
בשתי הטבלאות הבאות יש מידע על לקוחות.
בטבלה מספר 1 קיים מידע אודות: שם לקוח, ת.ז, רחוב, עיר וטלפון ובטבלה מספר 2 , קיים מידע אודות: שם לקוח, ת.ז, אימייל, תאריך לידה וסטטוס משפחתי.
כפי שניתן להבחין מדובר באותם הלקוחות, רק שבכל אחת מהטבלאות יש לנו עליהם מידע חלקי, מה שמוביל לטבלה התחתונה שהיא טבלת המיזוג של שתי הטבלאות שנוצרה ע"י צירוף העמודות מהטבלה השנייה לראשונה, וזאת בכדי שנקבל שורה אחת ארוכה שמספרת לנו יותר מידע אודות הלקוח.
כאמור, המשימה היא צירוף חמשת העמודות מהטבלה הראשונה לשנייה לכדי טבלה אחת ממוזגת של כל העמודות.
להלן פתרון הסוגיה בדבר מיזוג העמודות:
ניתן לבחור כיצד לבנות את הטבלה המאוחדת: כהמשך לטבלה מספר 1 או 2 או לחלופין לבנות טבלה חדשה, בדוגמא זו נבנה את הטבלה המאוחדת כטבלה חדשה.
בשלב הראשון, נסדר את העמודות בסדר הרצוי, נעתיק את כותרות חמשת העמודות מהטבלה הראשונה ונציב אותן בתחתית הגיליון, ושלושת העמודות מהטבלה השנייה נציב ליד. ניתן לסדר את העמודות בסדר אותו נרצה, כמו למשל, להחליף בין רחוב ועיר.
לאחר שנמקם את סדר העמודות כפי שנרצה, נזהה את העמודה המשותפת לשתי הטבלאות, שהיא זו שתעזור לחבר בין שתי הטבלאות ולשייך כל מידע שבעמודה לשורה הרלוונטית.
עמודה משותפת היא עמודה שקיימת גם בגיליון "לקוחות1" וגם בגיליון "לקוחות2". במקרה זה יש שתי עמודות משותפות: העמודה "שם לקוח" וגם העמודה "ת.ז" נמצאות בשתי הגיליונות.
מאפיין נוסף וחשוב מאוד הוא: שהעמודה המשותפת חייבת להיות חד-ערכית, משמע שלכל שורה יהיה ערך ייחודי שלא חוזר פעמיים בכדי שניתן יהיה לזהות את השורה באופן ייחודי.
עמודת ת.ז היא עמודה מעולה עבור תפקיד העמודה המשותפת מאחר ולא ניתן שיהיו שני אנשים בעלי אותו מספר תעודת זהות.
אם לא קיימת עמודת ת.ז, ויש עמודות אחרות עם מספר ייחודי כמו: מק"ט או מספר מזהה כלשהוא אז עמודות אלו מצוינות גם כן. ערכים שיהיו פחות טובים ומצריכים חשדנות וזהירות הם שדות כגון: מספר טלפון, מאחר ותמיד יכול להיות תרחיש שאחד מהלקוחות ברשימה החליף את מספר הטלפון, ומספר זה הועבר למישהו אחר שבמקרה נמצא גם ברשימה זו , נכון שזה תרחיש קלוש ברשימות של 10 שורות, אבל ברשימות של מאה אלף שורות תרחיש מסוג זה קרה בעבר וזה בהחלט יכול לקרות שוב.
עוד פחות טובה מזו היא עמודת "שם לקוח", כי ברגע שכמות הנתונים בטבלה עולה על עשרות ומאות של שורות, מבחינה סטטיסטית יתחילו לעלות גם הסבירויות של אנשים בעלי שם זהה כמו "משה כהן" או "אבי לוי".
אם אין אף עמודה משותפת בכדי לבצע מיזוג, אז למעשה מבחינה הגיונית, אין שום קשר בין הטבלאות הללו ולכן לא קיימת דרך ממוחשבת בכלל ואקסלית בפרט בכדי לחבר בניהן.
לאחר שנבחר את העמודה "ת.ז" כעמודה המשותפת, נבחר בטבלה אחת מבין השתיים לטבלה שסדר השורות שלה הוא זה שיוצג בטבלה המאוחדת, כלומר: בטבלה מספר אחת שם הלקוח הראשון הוא אבי ובטבלה מספר שתיים שם הלקוח הראשון הוא אורן, בדוגמא הזו, נבחר בסדר של טבלה מספר אחת, אבי יהיה הלקוח הראשון.
שימו לב! מאחר והחלפנו בין עמודה עיר ורחוב, הסדר שלהם בטבלה השתנה, ולכן בכדי לסדר את העמודות לפי הסדר הרצוי, נשתמש בנוסחת HLOOKUP אותה נציב בתא D22 , לאחר מכן, נגרור למטה והצידה בכדי למלא את התאים בערכים הרצויים.
לאחר שיש לנו את העמודה המשותפת בטבלה חדשה, כל מה שאנחנו צריכים לעשות זה לבחור באיזו פונקציה להשתמש.
הפונקציות המתאימות למשימה הן שילוב של INDEX ו-MATCH (בכל המקרים). במקרה שהעמודה המשותפת היא העמודה הראשונה בגיליון החיפוש, אז ניתן יהיה להשתמש בנוסחת VLOOKUP.
בדוגמא זו נשתמש בפונקציות INDEX ו-MATCH ונגרור לכל התאים הרלוונטים:
פתרון פונקציונאלי מובנה
בכדי לפתור זאת באמצעות פתרון פונקציונלי מובנה, נשתמש ב POWER QUERY
לשונית: "נתונים" – בתווית "קבל והמר נתונים"
לפני שנשתמש בכלי הזה, ראשית יש להמיר את טווח הנתונים שלנו לאובייקט של טבלה: נסמן את טווח הטבלה – לחיצה על כפתור "עצב כטבלה"- בחירה של העיצוב נדרש.
אותו הדבר נעשה עבור הטבלה השנייה.
רצוי לתת לטבלאות שמות בעלי משמעות כלשהי בכדי שאחר כך נוכל להבדיל בניהם, במקרה זה, נקרא לטבלאות בשמות הכותרות שלהם "טבלה 1" ו"טבלה 2".
נסמן תא אחד אקראי בתוך הטבלה, ונלחץ על כפתור "מתוך טבלה / טווח".
כעת נפתח עורך השאילתות POWER QUERY, מאחר ואנו לא צריכים לבצע שינוי במידע שלנו, נלחץ על כפתור "סגור וטען ב..".
בחלונית "ייבוא נתונים" אנו נבחר "צור חיבור בלבד" ונלחץ "אישור"
נעשה את אותו התהליך גם עבור הטבלה השנייה.
כפי שניתן לראות בצד שמאל של המסך "שאילתות וחיבורים" יש שתי טבלאות tbl_1 ו- tbl_2 שזה החיבור בין שתי הטבלאות שלנו.
נלחץ על כפתור: "קבל נתונים"- שלב שאילתות- מזג
במסך "מיזוג" שנפתח נסמן את שתי הטבלאות שלנו "tbl_1" , tbl_2"" לאחר מכן נלחץ על העמודה המשותפת לשתי הטבלאות, במקרה הזה עמודת "ת.ז".
לאחר מכן, נבחר בשדה "סוג הצירוף" ב- צירוף חיצוני שמאלי ונלחץ על אישור
לאחר שלחצנו על "אישור", ניתן לראות שנפתח לנו שוב חלון "POWER QUERY" בעמודה האחרונה של הטבלה נמצאת טבלה מס' 2 בה נמצאים כל הנתונים מטבלה זו.
בכדי לבחור את הנתונים אותם נרצה לראות נלחץ על כפתור "הרחבה" (מסומן בצילום מסך מטה באדום) וכאן נבחר את השדות אותם נרצה לראות כגון: אימייל, תאריך לידה וכו' ונלחץ על כפתור "אישור".
לאחר מכן יתווספו השדות הרלוונטים, נלחץ על כפתור "סגור וטען".
ניתן לראות כי תוצאות השאילתא הן למעשה איחוד של שתי הטבלאות
איחוד ברמת השורות:
איחוד ברמת השורות היא למעשה סיטואציה בה מספר מקורות מידע חולקים שדות משותפים וכל אחד ממקורות המידע הללו ישתבץ כהמשך לשורות של מקור המידע הקודם.
בדוגמה שלפנינו: טבלה מספר 1 וטבלה מספר 2 חולקות את אותן שדות בדיוק, שם לקוח, ת.ז וכו'. בנוסף אנו יכולים להבחין כי סדר השדות הוא זהה, כלומר השדה הראשון: "שם לקוח" נמצא בשתי הטבלאות כשדה הראשון לאחר מכן שדה ת.ז וכו'
עוד דבר שניתן להבחין בו בדוגמא זו הוא ששמות השדות הם זהים, כלומר כתוב לנו "שם לקוח" בטבלה הראשונה ו"שם לקוח" גם בטבלה השנייה. ישנם מקרים בהם שמות השדות אינם זהים, למשל, בטבלה אחת כתוב "שם לקוח" ובטבלה השנייה "שם ה-לקוח" וכו'.
איחוד ברמת השורות- פתרון נוסחתי:
דבר ראשון, נבחר את הכותרות ואת הסדר שאנו רוצים שיופיעו בטבלה המרכזת.
נוכל לבחור רק חלק מהשדות אבל לצורך הדוגמה אנו נבחר את כולן.
נעתיק ונדביק:
גם כאן, בדומה לדוגמת המיזוג ברמת העמודות, אנו יכולים לבצע את המשימה עם מספר נוסחאות שונות ובאופן כללי, יש מספר דרכים לבצע את המשימה באמצעות נוסחאות.
בדוגמת המיזוג ברמת העמודות השתמשנו בנוסחת HLOOKUP בכדי ליצור את הטבלה הראשונה, כאן נשתמש בנוסחאות INDEX וMATCH – בשביל לבצע את אותה המשימה. לנוחיותכם, ניתן יהיה לבחור בשיטה (נוסחה) המתאימה לכם.
בתא 16 Aנכתוב את נוסחת INDEX ולאחר מכן נסמן את השורה הראשונה בטבלה 1, יש לנעול את הטווח הנ"ל (סימן ה-$$), נשאיר רק את העמודות והשורות פתוחות בכדי שברגע שנגרור למטה הטווח יסמן את השורה הבאה.
נכתוב את נוסחת MATCH ונסמן את הכותרת עם נעילת שורות בלבד, ללא נעילת עמודות, טווח הכותרות יהיה עם נעילה מלאה.
כפי שניתן לראות נקבל את הערכים מהטבלה הראשונה, נכתוב תנאי ואת הנוסחה עבור הטבלה השנייה.
בכדי שהאקסל יביא לנו מידע מהטבלה השנייה אנו צריכים לרשום לפני האינדקס נוסחת ,IF שם נשאל האם השורה מכילה מידע או לא.
נכתוב את פונקציית IF ואז את נוסחת COUNTA ונשאל האם השורה הזו גדולה מ 0, אם כן משמע יש מידע ואם לא, אז אין מידע.
אם יש מידע, חשב את נוסחת INDEX, MATCH של הטבלה הראשונה שכבר כתבנו ואם אין, עכשיו נכתוב את ה INDEX MATCH הנוסף עבור הטבלה השנייה.
ה-INDEX, MATCH של הטבלה השנייה יהיה בטכניקה קצת שונה מה- INDEX, MATCH הראשון מאחר וברגע שמתחילה הטבלה השנייה, הנוסחה צריכה להיות מספיק חכמה בכדי שהיא תפנה אל השורה הראשונה של הטבלה השנייה, אבל לצורך ההמחשה נכתוב אותה באופן זהה לזה של הטבלה הראשונה, ונראה מה יקרה.
כפי שניתן לראות, אנחנו לא מקבלים ערכים מאחר וברגע שמסתיימת הטבלה הראשונה, מסתיימת גם הטבלה השנייה, זאת מכיוון שאנחנו מפנים אל שורה מחוץ לטבלה.
נבצע את ההתאמות הדרושות:
- נפנה אל כל הטבלה ולא רק לשורה אחת , ננעל אותה גם עבור השורה וגם עבור העמודה.
- במקום הספרה 1 שמייצגת את מספר השורה היחסי של האינדקס, נרשום נוסחה שתחזיר סדר מספרים רץ מ 1 ועד סוף הטבלה, אבל רק מהרגע שמסתיימת הטבלה הראשונה. אחת הדרכים לעשות זאת היא באמצעות פונקציית ROWS עם טווח מתרחב אשר תחזיר לנו את מס' השורות אשר עשינו מתחילת הטבלה פחות פונקציית COUNTA שתספור כמה שורות יש לנו בטבלה הראשונה, ברגע שהטבלה תגמר ותתחיל הטבלה השנייה, המספר הזה יהיה 1 ובשורה שלאחר מכן יהיה 2 וכו'…
מספר הערות לנוסחה הזו:
1. בשיטה זו, אין בעיה אם סדר העמודות שלכם שונה בין הטבלאות, למשל אם בטבלה המאוחדת היינו מחליפים בין שדה "עיר" לשדה "רחוב" מאחר ונוסחת MATCH מחפשת לפי שם השדה.
2. בשיטה זו, כן משנה אם שמות השדות שלכם שונה בין הטבלה המאוחדת לטבלת המקור מאחר וכאמור נוסחת MATCH מחפשת לפי שם מדויק.
3. כאן אני מציג לכם דוגמה ששלושת הטבלאות נמצאות באותו גיליון בשביל שיהיה לכם נוח לצפות במסך אחד בכל הטבלאות, אבל אין להשתמש בטכניקה הזו כאשר הטבלה המאחדת נמצאת מתחת לאחת הטבלאות כי אם אני אמשיך לגרור את נוסחה כלפי מטה יהיו לנו שגיאות.
4. הנוסחה הזו יכולה לתת מענה טוב לאיחוד של שתי טבלאות אולי מקסימום 3, יותר מזה, הנוסחה שתאלצו לכתוב תהיה ארוכה, מסורבלת ולא גמישה לשינויים ולבדיקות.
5. נוסחאות מסוג זה שרצות על אלפי שורות יהיו כבדות עבור מחשב ממוצע, ככה שאם לפניכם 100,000 שורות ויותר אל תנסו לגשת בשיטה זו.
איחוד ברמת השורות פתרון פונקציונלי מובנה:
הדרך לשימוש בפתרון זה, כמעט זהה לדרך שביצענו זאת במיזוג ברמת העמודות.
בשלב הראשון, נסמן את טווח שתי הטבלאות ונלחץ על כפתור "עצב טבלה"
ניתן לכל אחת מהטבלאות שמות בעלי משמעות (ראה הסבר למיזוג טבלאות פתרון פונקציונלי)
נסמן תא בתוך הטבלה–> נתונים–> "מתוך טבלה/ טווח"–> "סגור וטען ב"–> "צור חיבור בלבד"
אותו הדבר נעשה גם עבור הטבלה השניה.
בשלב הבא: קובץ- "קבל נתונים"- "שלב שאילתות" – "צרף"
בשלב הבא: קובץ- "קבל נתונים"- "שלב שאילתות" – "צרף"
במסך הבא בוחרים בשתי הטבלאות ולוחצים על אישור
כפי שניתן לראות השאילתא המוצגת היא טבלה אחת מאוחדת של שתי הטבלאות.
נלחץ על "סגור וטען"— סגור וטען
לאחר מכן תפתח לנו טבלה באקסל מאוחדת של שתי הטבלאות.