طریقه یکپارچه سازی ردیف‌های تکراری و جمع کردن مقادیر آن‌ها در اکسل

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

فرض کنید لیستی که قرار است شما اطلاعات آن‌را یکپارچه کنید شامل نام نرم‌افزارها و تعداد سفارشات آن‌ها است و مشابه تصویر زیر قرار است ردیف‌های تکراری را یکپارچه و تعداد سفارشات نرم‌افزارهای مشابه را با هم جمع کنید:

روش اول) یکپارچه سازی ردیف‌های تکراری بوسیله‌ی ابزار Consolidate

برای استفاده از ابزار فوق مراحل زیر را طی کنید:

۱- در سلولی که می‌خواهید نتیجه عملیات یکپارچه‌سازی در آن نمایش داده شود کلیک کنید.

۲- مطابق تصویر زیر از تب Data روی دکمه Consolidate کلیک کنید.

۳- در کادر باز شده Consolidate، در قسمت Function تابع مورد نظرتان را انتخاب کنید. چون در این مثال قرار است مجموع تعداد سفارشات محاسبه شود، بنابراین تابع Sum را انتخاب کنید. البته بسته به هدف‌تان امکان استفاده از ۱۰ تابع دیگر نیز وجود دارد.

۴- روی دکمه کلیک کرده و محدوده‌ای که می‌خواهید عملیات روی آن انجام شود را انتخاب کنید. سپس دکمه Add را فشار دهید تا محدوده انتخاب شده به لیست منابع (All Resources) اضافه شود.

۵- چون می‌خواهید بر اساس تکرار نام‌های مشابه در ستون‌ چپ یکپارچه‌سازی انجام شود گزینه ‌Left column را از قسمت Use labels in انتخاب نمایید. همچنین چون ردیف اول نام ستون‌ها است گزینه Top row را انتخاب کنید تا تکرار آن بررسی نشود.

۶- پس انجام تنظیمات فوق روی دکمه Ok کلیک کنید. نتیجه کار را در تصویر زیر مشاهده می‌کنید:

برای حالتی که داد‌ها در شیت‌های مختلف هستند هم می‌توان از ابزار Consolidate استفاده کرد. نحوه استفاده از آن در این فایل pdf بصورت یک مثال آموزش داده شده است

روش دوم) ماکرونویسی

توجه: قبل از استفاده از کد ماکروی زیر از اطلاعات خود کپی تهیه کنید.

های Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

در پنجره جدید باز شده کدهای زیر را کپی کنید.

Sub CombineRows()
'pctarfand.ir & tarfandha.blog.ir'
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
    Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub

پس از فشردن دکمه یا کلید F5 کد اجرا می‌شود که در نتیجه آن پنجره‌ی زیر ظاهر می‌گردد. ناحیه مد نظرتان که می‌خواهید عملیات یکپارچه سازی انجام شود (در مثال فوق A1:B14) را انتخاب کنید و روی دکمه Ok کلیک کنید.

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

روش سوم) ابزار PivotTable

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

برای استفاده از ابزار PivotTable، تمام اطلاعات همراه با عنوان ستون‌ها را انتخاب نمایید، سپس به تب Insert بروید و بر روی دکمه PivotTable کیلک کنید.

پنجره‌ای باز خواهد شد که بصورت خودکار، محدوده انتخاب شده در قسمت Select table or range اضافه شده است. تیک گزینه‌ی Existing Worksheet را بزنید و مکانی که می‌خواهید نتایج در آن درج شود را در قسمت Location وارد کنید.

پس از کلیک روی دکمه Ok، در سمت راست کادر PivotTable Field List ظاهر می‌شود. در قسمت Choose fields to add to report، عنوان ستون‌ها درج شده است که بصورت خودکار پس از تیک زدن آن‌ها به دو قسمت Row Labels و Values اضافه می‌شوند. اگر اضافه نشدند بصورت دستی آن‌ها را به بخش Row Labels و Values درگ کنید. پس از انجام اینکار در مکانی که قبلا مشخص کردید نتایج کار ظاهر می‌شود که در آن همراه با یکپارچه شدن ردیف‌های تکراری، جمع مقادیر آن‌ها نیز محاسبه می‌شود. البته قابلیت محاسبه جمع کل و همچنین فیلتر کردن نام محصولات نیز در این ابزار وجود دارد.

روش چهارم) با استفاده از فرمول نویسی

با استفاده از توابع Countif و Sumif که به ترتیب در اینجا و اینجا معرفی شده‌اند نیز می‌توانید عملیات فوق را انجام داد. البته با استفاده از این توابع تنها می‌توان مقادیر ردیف‌های تکراری را جمع کرد و نمی‌توان ردیف‌های تکراری حذف نمود. برای مثال قبل پس از پیست فرمول زیر در سلول C2، برای سایر سلول‌ها نیز اعمال نمودیم.

=IF(COUNTIF(A$2:A2,A2)=COUNTIF($A$2:$A$14,A2),SUMIF($A$2:$A$14,A2,$B$2:$B$14),"")

نتیجه کار را در تصویر زیر مشاهده می‌کنید.

روش پنجم) با استفاده از افزونه‌ Kutools for Excel

نحوه دانلود و استفاده از این افزونه را می‌توانید در اینجا مشاهده کنید.

البته این افزونه علاوه بر تجمیع مقادیر ردیف‌های تکراری، این امکان را نیز فراهم می‌کند که بتوان این مقادیر را با هم ترکیب کرد بصورتیکه بین آن‌ها فاصله یا ویرگول یا نقطه ویرگول یا بدون هیچ کاراکتر جداکننده‌ای قرار داشته باشد مشابه تصویر زیر: