جستجو

ترفندهای کامپیوتری

حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
ربات پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfand_bot

خلاصه آمار

تبليغات

پربحث ترين ها

۴

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

اکسل ماکرونویسی

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

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

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

روش اول) یکپارچه سازی ردیف‌های تکراری بوسیله‌ی ابزار 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

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

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

منبع: extendoffice.com


روش سوم) ابزار 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),"")

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

منبع: mrexcel.com


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

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

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

 

عضویت در خبرنامه:

نظرات (۴)

  1. واقعا دستتون درد نکنه خیلی وقت بود دنبال این قابلیت اکسل بودم. پیروز باشید
  2. سلام
    آیا بعد از جمع دیتا ها ... تعداد جمع زده شده ها نمایش داده میشود ؟ چون میخوام بعد جمع میانگین بگیرم
    • پاسخ:

      سلام
      چرا از تابع Average برای محاسبه میانگین استفاده نمی‌کنید؟
  3. مدیریت محتر وب سایت ترفندها
    با سلام وتحت
    واقعا جا داره که از راهنمایی ارزنده شما بزرگواران تشکر بکنیم و امیدواریم که همیشه سربلند و پایدار باشید 
  4. امید رضا ایزدی

    با تشکر از سایت بسیار بسیار خوبتون ان شاء الله که خداوند هر چه در دل دارید نصیبتون گرداند باز هم ممنون

ارسال نظر

قبل از ارسال نظر به نکات زیر توجه کنید:

۱- با توجه با اینکه نظرات خصوصی شما امکان نمایش در سایت را ندارد، بنابراین هنگام ارسال نظر، گزینه "" را انتخاب نکنید. چون تنها راه پاسخگویی به آن از طریق ایمیل شما است که با توجه به مشغله کاری فرصت ارسال پاسخ از طریق ایمیل وجود ندارد.

۲- قبل از مطرح کردن سوالتان، مطلب فوق را با دقت مطالعه کنید. اگر پاسخ سوالتان را پیدا نکردید در سایت جستجو کنید، ممکن است در مطلب دیگر پاسخ سوالتان را پیدا کنید.

۳- تنها به سوالاتی پاسخ داده می‌شود که از نحوه حل آن‌ها اطلاع داشته باشم.

۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده می‌شود و به آن‌ها پاسخی داده نمی‌شود.

الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،

ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی
X بستن