כיצד למזג קבצי אקסל מרובים: מדריך מלא שלב אחר שלב

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

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

מתי למזג ומתי לחבר

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

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

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

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

שיטות מקוריות של Excel לשילוב גיליונות וחוברות עבודה

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

העברה או העתקה של גיליונות בין חוברות עבודה

אידיאלי לקיבוץ טאבים מקבצים מרובים לתוך חוברת עבודה אחת. עובד ללא תלות בתוספים וזה מהיר עבור כמה קבצים.

1. פתחו את ספרי המקור והיעד. הנח את עצמך על הסדין שאתה רוצה לשאת.
2. לחץ לחיצה ימנית על לשונית הגיליון ובחר "העבר או העתק". ניתן גם לעבור אל דף הבית > תאים > עיצוב.
3. בחר את ספר היעד (קיים או "ספר חדש"). החלט את מיקום ההכנסה ביחס לעלים קיימים.
4. בחר "צור עותק" כדי למנוע אובדן הגיליון המקורי, ואשר באמצעות אישור. חזור על התהליך עבור כל הגיליונות שאתה צריך.
5. כדי להזיז מספר כרטיסיות בו זמנית, בחר מספר כרטיסיות באמצעות Shift או Ctrl וחזר על התהליך. זה קיצור דרך שחוסך הרבה זמן.

איחוד לפי תפקיד

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

1. פתחו כל גיליון מקור וודא שהטבלה נמצאת באותו מיקום ועם אותה צורה. הומוגניות היא הכל כאן.
2. בגיליון היעד, בחר את התא השמאלי העליון שבו ברצונך שהנתונים המאוחדים יופיעו. זה יהיה העוגן.
3. עבור אל נתונים > איחוד. תיפתח התיבה עם רשימת הפונקציות זמין.
4. תחת "פונקציה", בחר כיצד לאחד (סכום, ממוצע וכו'). חשבו האם אתם צריכים סכומים, ממוצעים, מינימום...
5. בכל גליון מקור, בחר את טווח הנתונים. המסלול והטווח יתווספו ל"כל ההפניות".
6. לאחר שהוספת את כל ההפניות, לחץ על אישור. תקבלו טבלה מאוחדת בהתאם למיקום.

איחוד לפי קטגוריה

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

1. פתח את כל גיליונות המקור. בדוק שהתגיות תואמות בדיוק בשם ובכתיב.
2. בחוברת העבודה של היעד, בחר את התא ההתחלתי עבור האיחוד. מקם את הסמן במקום הרצוי לסיכום.
3. עבור אל נתונים > איחוד. בחר את פונקציית האיחוד (סכום, ממוצע...).
4. סמנו היכן התוויות נמצאות ב"השתמש בתוויות ב-": בשורה העליונה, בעמודה השמאלית או בשניהם. זה אומר לאקסל כיצד להתאים קטגוריות.
5. בחר את הטווחים של כל גיליון כולל התוויות שנבחרו. המסלולים יופיעו תחת "כל ההפניות".
6. אשר באמצעות אישור כדי ליצור את גיליון האב. אקסל יצטרף לפי שם קטגוריה, לא לפי תפקיד.

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

הצטרפות ושינוי צורה באמצעות Power Query

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

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

1. נתונים > קבל נתונים > מקובץ > מתיקייה. בחר את התיקייה המכילה מהקבצים שלך.
2. תראה את רשימת הקבצים בתיקייה ובתת-התיקיות. בדוק שהמוצרים שאתה צריך מופיעים.
3. לחץ על "שינוי נתונים" כדי לפתוח את Power Query עם תצוגה של כל הקבצים. זה "חדר המכונות".
4. סנן עמודות כמו סיומת או נתיב תיקייה כדי לשמור רק את הסוגים הנכונים (לדוגמה, .xlsx או .csv). כך תמנעו מערבוב תפוחים עם אגסים.
5. בחר את העמודה תוכן (קובץ בינארי, בדרך כלל הראשון) ועבור אל בית > שילוב קבצים. תיפתח התיבה "שילוב קבצים"..
6. בחר "קובץ לדוגמה" מהתפריט הנפתח אם אינך רוצה להשתמש בראשון. Power Query מנתח אותו כדי לזהות עמודות ומחברים.
7. ניתן לסמן באופן אופציונלי את "דלג על קבצים עם שגיאות" כדי לא לכלול אותם בתוצאה. שימושי אם ישנם קבצים עם מבנה פגום.
8. אתה מקבל. שאילתה שתאחד נתונים תיווצר באופן אוטומטי של כל קובץ בטבלה אחת.

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

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

האם אתה מעוניין למזג שני קבצי CSV באמצעות עמודה משותפת, לדוגמה? "מספר סידורי" כמפתחכאן חשוב להבחין בין "Append" (צירוף) ל-"Merge" (מיזוג):

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

שלבים מומלצים לדוגמה שלך עם שני קבצי CSV (אחד מ-Apple Business Manager עם 13 עמודות ו-8.743 שורות, ואחד מ-MDM עם 24+ עמודות ו-6.012 שורות): ייבוא ​​שני קבצי ה-CSV ל-Power Query, הגדר סוגי נתונים נכונים והשתמש ב-"שילוב שאילתות".

1. נתונים > קבל נתונים > מקובץ > מטקסט/CSV, ייבא את ABM ואת ה-MDM. פתח כל אחד מהם בעורך.
2. ודא ש"מספר סידורי" הוא מאותו סוג בשניהם (טקסט הוא בדרך כלל הבטוח ביותר). שנה סוג במידת הצורך.
3. כאשר שאילתת ABM פעילה, בחר בית > מיזוג שאילתות > מזג שאילתות כחדשות. בחר MDM כטבלה להצטרפות.
4. בחר את העמודה "מספר סידורי" בשתי הטבלאות. קובע את סוג האיחוד:
   • חיצוני שמאלי (שמאל): שומר את כל ה-ABMs ומביא התאמות מה-MDM.
   • פנימי (צומת): רק שורות עם מספר סידורי קיימות בשתיהן.
   • ימין/מלא: גרסאות נוספות, בהתאם לצרכים שלך.
5. הרחב את עמודת הטבלה המתקבלת כדי להביא רק את עמודות ה-MDM שמעניינות אותך (סמן/בטל סימון). אתם נמנעים מרעש ועמודות מיותרות.
6. החל טרנספורמציות נוספות במידת הצורך (הסרת עמודות, נרמול תוויות). לאחר מכן "סגור וטען".

אם ניסית את האפשרות Append וקיבלת הרבה עמודות עם "null", זה היה צפוי: גיבשת מבנים שונים זה מזה. כדי לחצות לפי מפתח, השתמשו בפונקציה Merge עם הפונקציה join המתאימה.זה עובד ב-O365 גם ב-macOS (כולל Apple Silicon) וגם Windows, ויהיה לך גם היתרון של אפשרות לעדכן בלחיצה אחת כאשר מגיעים קבצי CSV חדשים.

מיזוג חוברות עבודה עם מאקרו VBA

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

1. פתח ספר חדש (יעד). לחצו על ALT + F11 כדי לפתוח את עורך ה-VBA.
2. הכנס מודול (Insert > Module) והדבק את הקוד הזה תוך התאמת הנתיב ומסיכת הקובץ במידת הצורך:

Sub UnirHojasDeCarpeta()
  Dim ruta As String, nombre As String
  Dim sh As Worksheet
  ruta = "C:\MisDatos\Origen\"
  nombre = Dir(ruta & "*.xlsx")
  Do While nombre <> ""
    Workbooks.Open Filename:=ruta & nombre, ReadOnly:=True
    For Each sh In ActiveWorkbook.Sheets
      sh.Copy After:=ThisWorkbook.Sheets(1)
    Next sh
    Workbooks(nombre).Close SaveChanges:=False
    nombre = Dir()
  Loop
End Sub

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

פתרונות מקוונים למיזוג קבצי Excel: ASPOSE, DOCSOSO ו-GROUPDOCS

אם אינך רוצה לפתוח את אקסל או שאתה עובד מ... Mac, לינוקס, אנדרואיד o iOS, אתה יכול להשתמש בשירותי היתוך אתרים. הם מהירים ופלטפורמות שונות, אך זכרו את הפרטיות: כשאתם מעלים גיליונות אלקטרוניים עם נתונים רגישים, אתם מסתכנים בחשיפת מידע בשרתים של צד שלישי.

ישנן אפליקציות מקוונות המשלבות מספר קבצי אקסל וגם מאפשרות לשמור אותם במגוון פורמטים. בין הפונקציות האופייניות שלו תמצאו:

• מיזוג גיליונות אלקטרוניים: XLSX, XLS, XLSB, XLSM, XLTX, XLT, XLTM, ODS.
• שמור בפורמט הרצוי: PDF, לשלב את אקסל עם וורד, DOCX, POWERPOINT, PPTX, XLS, XLSX, XLSM, XLSB, XLT, XLTX, XLTM, ODS, OTS, FODS, NUMBERS, ET, CSV, TSV, HTML, WEBP, SVG, TIFF, XPS, ZIP, MHTML, SQL, טקסט, TXT.
• יתרונות נוספים: דרך מהירה למיזוג, שלב פורמטים שונים באחד, שמור בקלות כ-PDF, תמונות או HTML, מיזוג קבצים OpenDocument, בחר את סדר הקבצים המצורפים והחלט אם התוצאה תיכנס מספר גיליונות או בגיליון אחד.

ASPOSE מציעה חבילה של כלי עזר מבוססי-אינטרנט לניהול מסמכים. בכלי האקסל שלך תוכל להעלות קבצים מהמחשב שלך, השתמש ב-Dropbox, או הדבק כתובת URL. אתה יכול לבחור אם ברצונך לשלב מספר גיליונות או לארוז הכל לגיליון אחד; תחת "שמירה בשם", בחר XLSX; לחץ על מיזוג, ובתוך מספר שניות, תוכלו להוריד את התוצאה או לשלוח אותה לדרופבוקסזה תומך גם בעבודה עם פורמטים אחרים של אופיס (Word, PDF, תמונות וכו').

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

GROUPDOCS משלב גם מספר כלי עזר לתיעוד. ניתן לבחור קבצים מהסייר או לגרור ולשחרר בממשק; לאחר מכן לוחצים על כפתור המיזוג, וכשמסיימים, descargas הקובץ הסופי. לפני מחיקת המקור, פתחו את המסמך שנוצר וודאו שהמיזוג הוא כפי שציפיתם; אם לא, תמיד תוכלו פונקציית אקסל מקורית או Power Query, שהן ניתנות לשליטה רבה יותר.

שיטות פשוטות אחרות וכלי "מומחה"

עבור מקרים ספציפיים, ההליך המסורתי פועל: העתק והדבקפתחו כל חוברת עבודה, השתמשו ב-Ctrl + A כדי לבחור, ב-Ctrl + C כדי להעתיק, והדביקו אותה בגיליון היעד. חזרו על הפעולה. זה מהיר אם יש לכם שניים או שלושה קבצים; עם עשרות, זה הופך למייגע.

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

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

1. התקן את היישום ובחר באפשרות "שלב את Excel". זוהי הכניסה לאשף ההיתוך.
2. ייבא קבצים או תיקיות בודדים באמצעות "הוסף קבצים" ו"הוסף תיקיות". הכלי יציג את ההיררכיה.
3. בחר את הקבצים שברצונך לכלול ולחץ על "הבא". כך מכוונים היטב את האצווה.
4. בחרו בין "גליונות עבודה בודדים" (כרטיסיות מרובות) או "גליון עבודה יחיד" והגדירו את נתיב הפלט. אתם מחליטים על המבנה הסופי.
5. לחצו על "מיזוג" והמתנו לסיום הפעולה. לאחר שתסיים, לחץ על אישור ולבדוק את התוצאה.

אם אתם שוקלים שיטות ידניות, זכרו את המגבלות שלהן: הם לא אידיאליים לקבצים ענקיים, הם דורשים שלבים חוזרים, גוזלים זמן, דורשים מיומנות ב-Excel/VBA, ומסתכנים באובדן היררכיות או נתונים אם עושים טעות. עבור עומסי עבודה גדולים, Power Query או תהליך אוטומטי (מאקרו או כלי ייעודי) לרוב אמינים יותר.

הרעיון המרכזי הוא שיש לך מגוון אפשרויות בהתאם למקרה שלך: איחוד לפי תפקיד או קטגוריה אם המבנה מיושר; הזזה/העתקה של גיליונות אם אתם מארגנים מחדש חוברות עבודה; Power Query להוספה או מיזוג לפי מפתח עם עדכונים אוטומטיים; פקודות מאקרו של VBA כשאתם רוצים לבלוע עשרות קבצים בכמויות גדולות; ושירותי אינטרנט או תוכנה ייעודית אם אתם נותנים עדיפות למהירות ואינכם מטפלים בנתונים רגישים. בחירה נכונה תחסוך לכם שעות וכאבי ראש, ותאפשר לכם לשמור על הדוחות שלכם תחת שליטה מבלי להתפשר על ביצועים או איכות נתונים.