چگونه چند فایل اکسل را باهم ادغام کنیم؟

هنگام کار با مایکروسافت اکسل، ممکن است پیش بیاید که بخواهید  درگیر ادغام چند فایل اکسل شوید. کاملاً طبیعی‌ست که کار با یک فایل تنها نسبت به جا به جایی در چندین منبع و سوئیچ بین فایل‌ها آسان‌تر است.

با این حال، ادغام چندین workbook در یک فایل می‌تواند تبدیل به فرآیند پیچیده، دشوار و طولانی شود. به خصوص اگر فایل‌هایی که قرار است باهم ادغام شوند، خودشان دارای چندین worksheet باشد، ممکن است شما از خیر ادغام چند فایل اکسل بگذرید.

 

اما در این مقاله، با چند روش کلیدی و مثال‌های خوب، راه ادغام چند فایل اکسل را هموار کرده و قدم بزرگی برای ذخیره زمان و مدیریت هر چه بهتر فایل‌ها خواهید برداشت.

ادغام دو فایل اکسل از طریق کپی شیت‌ها

اگر فقط با چند فایل اکسل برای ادغام روبرو هستید، می‌توانید به آسانی شیت‌ها را از یک فایل به فایل دیگر کپی کنید یا انتقال دهید. در زیر مراحل و نحوه ادغام چند فایل اکسل را خواهیم داشت:

1. دفتر کار (workbook)هایی که قصد ادغام آن‌ها را دارید، باز کنید.
2. کاربرگ(worksheet)هایی را که می‌خواهید در دفترکار اصلی کپی کنید را انتخاب کنید.

برای انتخاب چند برگ، می‌توانید از یکی از تکنیک‌های زیر استفاده کنید:

• برای انتخاب شیت‌های مجاور، روی اولین برگه‌ای که می خواهید کپی کنید کلیک کنید، کلید Shift را فشار داده و نگه دارید. سپس روی آخرین برگه کلیک کنید. با این کار همه کاربرگ‌ها در این بین انتخاب می‌شوند.
• برای انتخاب شیت‌هایی که در کنار هم نیستند، کلید Ctrl را نگه داشته و روی هر برگه صفحه به صورت جداگانه کلیک کنید.
• بعد از انتخاب تمام کاربرگ‌ها، بر روی یکی از تب‌های انتخاب شده راست کلیک کرده و سپس بر روی Move یا Copy کلیک نمایید.

4. در فرایند ادغام چند فایل اکسل، بعد از این‌که با منوی move or copy روبرو شدید، کارهای زیر را انجام دهید:

• از لیست کشویی Move selected sheets to book دفترکار نهایی که قصد ادغام فایل ها را دارید، انتخاب کنید.
• مکان دقیق حضور کاربرگ را انتخاب کنید، به عنوان مثال در نمونه زیر، گزینه Move to end انتخاب شده است.
• اگر قصد دارید که کاربرگ‌های اصلی در فایل مبدا باقی بمانند، create copy را انتخاب کنید.
• برای اتمام کار ادغام و ترکیب چند فایل در اکسل، دکمه Ok را فشار دهید.

نتیجه نهایی در عکس زیر قابل مشاهده است.

نکته قابل توجه این است که اگر بخواهید دستی، شیت‌ها را کپی کنید، ممکن است با محدودیت‌های اکسل در انتقال شیت‌های دارای جدول روبرو شوید. در چنین مواردی یا باید جدول را به یک محدوده تبدیل کنید یا از یکی از روش‌های زیر که این محدودیت را ندارند، استفاده کنید.

ادغام چند فایل اکسل با VBA

اگر دستی در ماکرو نویسی داشته باشید، بدون شک ادغام چند فایل اکسل با VBA پیشنهاد خیلی خوبی خواهد بود. کد زیر، تمام برگه‌های فایل‌های اکسل را در یک workbook کپی می‌کند. اما حتماً توجه داشته باشید که هیچ کدام از فایل‌ها هنگام فرایند ادغام چه از لحاظ فیزیکی یه از چه لحاظ حافظه باز نباشند. در چنین موردی در صورت عدم رعايت با خطای run-time روبرو خواهید شد.

 

 

کد ادغام چند فایل اکسل

Sub MergeExcelFiles()

    Dim fnameList, fnameCurFile As Variant

    Dim countFiles, countSheets As Integer

    Dim wksCurSheet As Worksheet

    Dim wbkCurBook, wbkSrcBook As Workbook

 

    fnameList = Application.GetOpenFilename(FileFilter:=”Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm”, Title:=”Choose Excel files to merge”, MultiSelect:=True)

 

    If (vbBoolean <> VarType(fnameList)) Then

 

        If (UBound(fnameList) > 0) Then

            countFiles = 0

            countSheets = 0

 

            Application.ScreenUpdating = False

            Application.Calculation = xlCalculationManual

 

            Set wbkCurBook = ActiveWorkbook

 

            For Each fnameCurFile In fnameList

                countFiles = countFiles + 1

 

                Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

 

                For Each wksCurSheet In wbkSrcBook.Sheets

                    countSheets = countSheets + 1

                    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)

                Next

 

                wbkSrcBook.Close SaveChanges:=False

 

            Next

 

            Application.ScreenUpdating = True

            Application.Calculation = xlCalculationAutomatic

 

            MsgBox “Processed “ & countFiles & ” files” & vbCrLf & “Merged “ & countSheets & ” worksheets”, Title:=”Merge Excel files”

        End If

 

    Else

        MsgBox “No files selected”, Title:=”Merge Excel files”

    End If

End Sub

اضافه کردن ماکرو به workbook

برای اضافه کردن این ماکرو به workbook باید Alt+F11 را همزمان فشار دهید تا ویرایشگر VisualBasic باز شود.

 

در ادامه بر روی This workbook کلیک راست کرده و از منوی باز شده مسیر Insert>Module را طی کنید. در پنجره‌ی کد باز شد، کد بالا را  Pasteنماید. برای استفاده از ماکرویی که برای ادغام چند فایل اکسل نوشته و اضافه شده باید به طور همزمان Alt + F8 را فشار دهید. سپس MergeExcelFiles را انتخاب کرده و بر روی Run  کلیک نمایید.

نتیجه کار ادغام چند فایل اکسل با استفاده ماکرو نویسی در شکل زیر قابل مشاهده است:

 

ادغام داده‌ها در یک شیت واحد

گاه پیش می‌آید که قصد ادغام مجموعه داده‌ها در درون یک شیت واحد داشته باشید. اکسل با ویژگی‌های منحصر به فردش این امکان را در اختیار شما قرار می‌دهد.

اما به خاطر داشته باشید که شیت‌هایی که می‌خواهید آن‌ها را با هم ادغام کنید، باید حتماً دارای طرح‌بندی یا Layout یکسان در هدر‌ها و نوع داده‌ باشند و همچنین خبری از هیچ سطر و ستون خالی نباشد.

سپس یک کاربرگ جدید ایجاد کرده و در شیت جدید به تب Data رفته و بر روی گزینه Consolidate  کلیک کنید.

با استفاده از داده‌های زیر، نحوه ادغام به آسانی قابل مشاهده خواهد بود.

در ادامه، از نوار مربوطه Sum را انتخاب کرده و با استفاده از Refrences به شیتی که می‌خواهید داده‌هایش را به شیت مورد نظر اضافه کنید، دسترسی خواهید داشت. بعد از این مرحله داده‌های مدنظر را انتخاب کنید.

این روند را برای تمامی مجموعه داده‌هایی که قصد ادغام آن‌ها را دارید، ادامه دهید. در نظر داشته باشید که برای دسترسی به شیت‌های دیگر از دفترکارها می‌توانید در ویندوز از Browse استفاده کرده و در سیستم عامل مک، از Select استفاده کنید.

برای تغییر داده‌ها در صفحه جدید همزمان با تغییر در صفحات دیگر باید تیک گزینه Create links to source data را فعال کنید.

همچنین می‌توانید در مورد این‌که کدام یک از labelها همراه داده‌ها به صفحه جدید انتقال پیدا کنند، تصمیم بگیرید. در مثال زیر با فرض فعال بودن هر دوی این گزینه‌ها بر روی OK کلیک کرده و نتیجه زیر قابل مشاهده است.

متاسفانه این روش برای ادغام سلول‌های دارای متن مناسب نبوده و فقط برای انتقال و ادغام سلول دارای داده‌های عددی کاربرد دارد.

در قسمت‌های قبل، در مورد ادغام چند فایل اکسل با استفاده ماکرو نویسی توضیح داده شد، بنابراین می‌توانید برای انتقال داده‌های متنی از VBA استفاده کنید.

ادغام چند فایل اکسل با Addins

Addins اکسل کاملاً رایگان بوده و به صورت کامل در دسترس کاربران اکسل است.  با دانلود و نصب Addins می‌توانید چند فایل اکسل را با هم ادغام کرده و به صورت یک کاربرگ مشاهده کنید. بعد از نصب این Addins در قسمت Data اکسل، گزینه ای به نام RDB Merge Addins  اضافه می گردد.

افزونه RDBMerge با برخورداری از ظاهر ساده و گرافیکی باعث سهولت درک منطق عملکرد آن شده است. بدین ترتیب کاربران از استفاده از روش‌های دیگری نظیر نوشتن کدها و ماکروها یا استفاده از کوئري‌ها بی نیاز می‌شوند.

از سوی دیگر، سهولت طراحی فرم هایی که داده‌های آن‌ها به وسیله این افزونه قابل جمع آوری است، باعث می شود تا کاربران با برخورداری از دانش مقدماتی اکسل، بتوانند پروژه های بزرگ جمع آوری داده را در زمان بسیار کوتاهی انجام دهند.

همچنین با استفاده از گزارشی که این افزونه در بخش LogSheet خود ارائه می دهد، یافتن خطاهای احتمالی را بسیار آسان شده است.

روش‌های ادغام جدول‌ها در اکسل

روش‌های زیادی برای ادغام چند فایل در اکسل وجود دارد. از فرمول نویسی و استفاده از توابع جستجو مثل

Vlookup و VBA، ابزارهایی مانند Consolidate و افزونه‌های آماده موجود برای این کار گرفته تا ادغام جدول‌ها در اکسل با استفاده از Power query  می‌توان به ترکیب جداول پرداخت. ابزار power query که در نسخه های ۲۰۱۶ و ۲۰۱۹ به عنوان Get & Transform شناخته می‌شد، ابزاری برای ترکیب و یکپارچه کردن، مرتب کردن و انتقال داده‌ها از منابع مختلف به یک فرمت دلخواه شما  مانند Table، Pivot Table یا Pivot Chart است.

باید در نظر داشته باشید که پاور کوئری یکی از ابزارهای اکسل است که از نسخه ۲۰۱۶ به بعد موجود بوده و نیازی به انجام کار اضافه‌ای نیست.

اما برای نسخه‌های اکسل ۲۰۱۰ و ۲۰۱۳ می‌تواند به عنوان افزونه Add in به اکسل اضافه شود. برای استفاده باید حداقل یک ستون مشترک به عنوان کلید و مشخصه بین جداول وجود داشته باشد. همچنین جدول نهایی به صورت خودکار آپدیت نشده و باید زمان آپدیت و بروز رسانی اطلاع داده شود.

حرف آخر

اکسل نرم افزار قدرتمندی برای مدیریت داده‌ها و کار با آن‌ها بوده و همیشه کاربران اکسل به دنبال راه‌های خوب برای پیشبرد اهدافشان هستند.  در این مقاله به بررسی روش‌های نحوه ادغام چند فایل اکسل پرداخته شده و تمامی بایدها و نباید‌های استفاده از آن روش نیز به صورت کاملاً مشخص اشاره شد.

 

در نظر داشته باشید که با توجه به کاربری مدنظر خودتان و این‌که با کدام یک از روش‌ها برای ادغام فایل‌های خودتان راحت‌تر هستید، تصمیم به ادغام بگیرید. بررسی و گذراندن دوره آموزش اکسل برای یادگیری و همچنین تقویت مهارت کار با نرم افزار اکسل می‌تواند مفید باشد.