جستجو

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

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

خلاصه آمار

تبليغات

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

مطابق راهنمای درون فایل، کد ماکرو را اجرا کنید که در نتیجه آن پنجره‌ی زیر ظاهر می‌گردد. ناحیه مد نظرتان که می‌خواهید عملیات یکپارچه سازی انجام شود (در مثال فوق 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

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

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

 

برای مطلع شدن از جدیدترین مطالب سایت، کانال‌ پی‌سی‌ترفند در تلگرام را با آی‌دی pctarfandir@ دنبال کنید:

          

سوالات خود در زمینه ورد و اکسل و ... را می‌توانید از طریق ایمیل و تلگرام با ما مطرح کنید.

آدرس ایمیل: tarfandha.blog@gmail.com

آی‌دی تلگرام: pctarfand_admin@

نظرات (۲۱)

  1. سلام
    من یک ستون از اسم افراد دارم که تعدادی پروژه به اونها اختصاص دادم یعنی اسمشون چندین بار در یک ستون تکرار شده
    حالا میخوام این تعداد تکرار رو برای هر نفر بدست بیارم و کل این داده رو بصورت یک نمودار دایره ای بر حسب درصد ببینم چه کاری باید انجام بدم

    کلی بخوام بگم 120 تا پروژه (سلول) دارم که بین 12 نفر پخش شده و هر سلول معادل 0.833333333333 می باشد که مجموع بشه 100 درصد
    میخوام بفهمم هر نفر چند درصد پروژه بهش اختصاص داده شده
    • پاسخ:

      سلام
      شما کافیست نام افراد را ستون اول در نظر بگیرید و در ستون دوم روبری هر نام عدد ۰.۸۳ درج کنید و با آموزش گفته شده نام های تکراری را یکپارچه و اعداد را با هم جمع کنید.
      موفق باشید.
  2. صلاح الدین

    سلام فرض کنید یک شیت خرید با اطلاعات کد کالا،اسم و تعداد داریم و شیت دوم کالاهای مصرف شده با همان اطلاعات وجود داره چطور میتونیم در شیت سوم اطلاعات مصرف شده رو رو به روی اطلاعات خرید شده قرار بدیم و در نهایت موجودی بگیریم (با توجه به اینکه اطلاعات در شیت ها هم ردیف نیستند)
    • پاسخ:

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

      سلام
      منظورتان از مرج شدن چیه؟ یعنی 2 ردیف به یک ردیف تبدیل شود؟
  4. سلام وقت به خیر
    فرض کنید در یک شیت لیست کامل محصولات به همراه نام محصول ، بارکد ، تعداد هر محصول و تعداد فروش هر محصول درج شده است ، ما در شیت دوم تمام محصولات را از شیت اول به صورت جدول دسته بندی کرده ایم ، حالا میخوایم گزارش یک ماهه بگیریم که در شیت سوم یک جا کپی میکنیم ، ولی چطوری میتونیم در شیت دوم اقلامی که در یک ماه گرفته شده رو درج کنیم که تعداد هر محصول از شیت سوم درست در جلوی خود محصول در شیت دوم وارد بشه و اشتباه در ردیفهای دیگه و محصولات دیگه وارد نشه ؟؟؟
    • پاسخ:

      سلام
      سوالتان کمی پیچیده شده. در صورت امکان در یک فایل اکسل توضیح دهید.
  5. بهنام حاجیان

    سلام.
    اگر چنین چیزی
    =b2+d2-f2
    =b3+d3-f3
    و به همین ترتیب ...
    تا 100 سطر ( در یک ستون )، داشته باشیم و نخواهیم فرمولها را یک به یک بنویسیم باید چیکار کنیم؟
    • پاسخ:

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

      سلام
      از روش پنجم استفاده کنید.
  7. سلام در مورد جواب سوال من که دیروز جواب دادید ممنون . شاید سوال درست مطرح نشده است . من یک فایل اکسل دارم. که در ستون اول شماره پرسنلی افراد ودر ستون دوم نام افراد ودر ستون سوم میزان حقوق آنها درج شده است . با استفاده از consolidate جمع ستون اول ودوم را به من داده ولی نام پرسنل در ستون دوم نیامده است لطفا راهنمایی فرمائید .
    • پاسخ:

      سلام
      با این روش ها شما فقط یک ستون را می توانید یکپارچه کنید.
  8. سلام. ممنون از راهنمائی و همچنین سایت خوب شما .
    من از روش اول استفاده کردم . ولی ستون دوم من که اسامی فارسی بود را نمی آورد . لطفا راهنمایی بفرمائید . با تشکر.
    • پاسخ:

      سلام
      شاید مشکل از فارسی بودن است. سایر روش ها را امتحان کرده اید؟
  9. سید حیدر حسینی

    متاسفانه در جدول پاشنه ای و راه حل اول داده ها بعد از تغییر بروز نمی شود. آیا راه حلی برای این مشکل وجود دارد ؟
    • پاسخ:

      سلام
      در این روش چنین امکانی وجود ندارد. از سایر روش ها مثلا فرمول نویسی استفاده کنید.
      موفق باشید.
  10. سلام....واقعا ممنون. توضیحاتتون خیلی عالی و کار راه انداز هستن.
  11. واقعا دستتون درد نکنه خیلی وقت بود دنبال این قابلیت اکسل بودم. پیروز باشید
  12. سلام
    آیا بعد از جمع دیتا ها ... تعداد جمع زده شده ها نمایش داده میشود ؟ چون میخوام بعد جمع میانگین بگیرم
    • پاسخ:

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

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

ارسال نظر

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

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

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

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

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

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

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

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