آموزش مرتب سازی و خلاصه کردن داده ها در اکسل با Outline

برگه های اکسل با محتویات زیاد بعضی وقت ها غافلگیر کننده هستن و خواندن اون ها هم سخت میشه. خوشبختانه، اکسل امکان گروه بندی اطلاعات رو به شما میده، و به راحتی به شما اجازه میده تا بخش های مختلف برگه اکسل خودتون رو مخفی یا نمایان کنید. شما همچنین می تونید خلاصه هر گروه رو با استفاده از دستور Subtotal مشاهده کنید و یک طرح کلی از برگه اکسل خود بسازید.

روش گروه بندی ردیف ها یا ستون ها در اکسل

1- ابتدا ردیف ها و یا ستون هایی رو که قصد گروه بندی اون ها رو دارید، انتخاب کنید. در مثال زیر ما ستون های B، C و D رو انتخاب کردیم.

2- ابتدا تب Data و بعد دستور Group رو انتخاب کنید.

3- ردیف ها یا ستون های انتخاب شده تبدیل به یک گروه میشن. در مثال ما ستون های B، C و D تبدیل به یک گروه شدن.

• توجه: برای اینکه داده های اکسل رو از حالت گروه بندی خارج کنید، ابتدا ردیف ها یا ستون های گروه بندی شده رو انتخاب کنید و بعد روی دستور Ungroup کلیک کنید.

روش مخفی کردن و نمایش دادن گروه ها در اکسل

1- برای مخفی کردن یک گروه روی علامت منها (-) که در هر گروه وجود داره، کلیک کنید. این دکمه رو دکمه مخفی کردن جزئیات هم می نامند.

2- گروه مخفی میشه. برای نمایش مجدد یک گروه روی علامت مثبت (+) که در هر گروه مخفی شده وجود داره، کلیک کنید. این دکمه رو دکمه نمایش جزئیات هم می نامند.

ایجاد جمع های فرعی در اکسل

دستور Subtotal در اکسل، به شما امکان میده تا بصورت اتوماتیک یک گروه بسازید، و از توابع عمومی اکسل مثل SUM, COUNT, و AVERAGE برای خلاصه سازی اطلاعات استفاده کنید.

• توجه: برای ایجاد جمع های فرعی، داده های شما باید به درستی مرتب سازی شده باشن، در این زمینه آموزش مرتب سازی داده ها که در درس های قبلی ارائه شد می تونه برای شما مفید باشه.

روش ایجاد subtotal در اکسل

1- ابتدا برگه اکسل خودتون رو بر اساس داده هایی که قصد ایجاد subtotal برای اون ها رو دارید مرتب سازی کنید. در این مثال ما می خواهیم برای ستون T-shirt size این کار رو انجام بدیم، بنابراین ابتدا این ستون رو مرتب سازی می کنیم.

2- تب Data رو انتخاب کرده و روی دستور Subtotal کلیک کنید.

3- صفحه Subtotal نمایان میشه. روی کادر باز شدنی فیلد At each change in کلیک کنید، و ستونی رو که قصد ایجاد subtotal برای اون دارید رو انتخاب کنید. در این مثال ما ستون T-Shirt Size رو انتخاب می کنیم.

4- روی کادر باز شدنی قسمت Use function کلیک کنید. و تابعی که می خواهید از اون استفاده کنید رو انتخاب کنید. در این مثال ما تابع COUNT رو انتخاب می کنیم.

5- در گزینه های قسمت Add subtotal to ستون هایی رو که می خواهید subtotal برای اون ها نمایش داده بشه رو انتخاب کنید. در این مثال ما ستون T-Shirt Size رو انتخاب می کنیم. وقتی کارتون تمام شد OK کنید.

6- برگه اکسل شما گروه بندی میشه، و در زیر هر گروه subtotal (زیر جمع) شما نمایان میشه. در این مثال، داده های ما بر اساس ستون T-shirt size گروه بندی میشن و در زیر گروه تعداد سفارش داده شده از اون نوع تی شرت نمایش داده میشه

مشاهده گروه های اکسل با سطح آنها

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

1- ابتدا روی کوچکترین سطح کلیک کنید تا کمترین اطلاعات ممکن رو ببینید. در این مثال ما سطح 1 رو انتخاب می کنیم، که فقط شامل مقدار grand count میشه که در واقع همان تعداد ستون T-shirts هست.

2- روی سطح بعدی کلیک کنید تا جزئیاتش نمایان بشه. در این مثال ما روی سطح 2 کلیک می کنیم، که شامل ردیف مجموع هر گروه (subtotal) هست، اما سایر جزئیات گروه بندی همچنان مخفی هستن.

3- روی بالاترین سطح کلیک کنید تا تمام جزئیات برگه اکسل شما نمایان بشه. در این مثال ما روی سطح 3 کلیک می کنیم.

• توجه: شما همچنین می تونستید از دکمه های نمایش و مخفی کردن که با علامتهای + و – مشخص شدن استفاده کنید. تا گروه ها رو مخفی یا نمایان کنید.

روش حذف subtotal ها (زیر جمع ها) در اکسل

بعضی وقت ها ممکنه که دیگه نخواهید تا زیر جمع ها رو در برگه اکسلتون داشته باشید، مخصوصا اینکه قصد داشته باشید تا اطلاعات رو با شیوه های مختلف دیگه ای بررسی کنید. در این گونه مواقع شما باید زیر جمع ها رو حذف کنید.

1- ابتدا روی تب Data کلیک کنید و بعد دستور Subtotal رو کلیک کنید.

2- پنجره Subtotal نمایان میشه. روی دکمه Remove All کلیک کنید.

3- تمام برگه از حالت گروه بندی خارج شده و قسمت زیر جمع ها هم حذف میشن.

• توجه: اگه فقط می خواهید گروه ها رو حذف کنید و زیر جمع ها باقی بمونن از دستور Ungroup استفاده کنید. روی این دستور کلیک کنید و از کادر باز شده گزینه Clear Outline رو انتخاب کنید.

ترتیب پیشنهادی آسان پی سی

برای یادگیری اکسل 2016

• 1- شروع کار با اکسل 2016

• 2- کاربرد OneDrive در اکسل 2016

• 3- ایجاد و باز کردن صفحه  کار در اکسل 2016

• 4- ذخیره و اشتراک گذاری صفحه  کارها در اکسل 2016

• 5- مبانی سلول در اکسل 2016

• 6- اصلاح ستون ها، ردیف ها و سلول ها در اکسل 2016

• 7- قالب بندی سلول ها در اکسل 2016

• 8- درک قالب بندی اعداد (number formats) اکسل 2016

• 9- کار با چندین برگه در اکسل 2016

• 10- یافتن و جایگزینی در اکسل 2016

• 11- بررسی املا کلمه در اکسل 2016

• 12- طرح بندی صفحه (Page Layout) و چاپ در اکسل 2016

• 13- معرفی فرمول ها در اکسل 2016

• 14- ایجاد فرمول های پیچیده تر با اکسل 2016

• 15- ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلول های اکسل 2016

• 16- توابع (Functions) اکسل 2016

• 17- نکات پایه ای برای کار با داده ها در اکسل 2016

• 18- منجمد کردن پنجره ها و گزینه های نمایش در اکسل 2016

• 19- مرتب سازی داده ها در اکسل 2016

• 20- فیلتر کردن داده ها در اکسل 2016

• 21- گروه ها و جمع های فرعی در اکسل 2016

• 22- جدول ها (Tables) در اکسل 2016

• 23- نمودارها در اکسل

• 24- فرمت دهی شرطی در اکسل

• 25- ویژگی های Track Changes و نظرات (COMMENT) اکسل

• 26- ویژگی های بازرسی سند (Document Inspector) و Protect Workbook اکسل

• 27- پیوت تیبل (PivotTables)

• 28- ابزارهای پیوت تیبل (PivotTables) در اکسل

• 29- ابزار تحلیل what-if اکسل

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

با این حال، ادغام چندین 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 که در نسخه های 2016 و 2019 به عنوان Get & Transform شناخته می شد، ابزاری برای ترکیب و یکپارچه کردن، مرتب کردن و انتقال داده ها از منابع مختلف به یک فرمت دلخواه شما  مانند Table، Pivot Table یا Pivot Chart است.

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

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

حرف آخر

اکسل نرم افزار قدرتمندی برای مدیریت داده ها و کار با آن ها بوده و همیشه کاربران اکسل به دنبال راه های خوب برای پیشبرد اهدافشان هستند.

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

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

بررسی و گذراندن دوره آموزش اکسل برای یادگیری و همچنین تقویت مهارت کار با نرم افزار اکسل می تواند مفید باشد.