مطالب برتر
حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال پی‌سی‌ترفند در تلگرام:
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
روش چهارم) با استفاده از فرمول نویسی
با استفاده از توابع 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
نحوه دانلود و استفاده از این افزونه را میتوانید در اینجا مشاهده کنید.
البته این افزونه علاوه بر تجمیع مقادیر ردیفهای تکراری، این امکان را نیز فراهم می‌کند که بتوان این مقادیر را با هم ترکیب کرد بصورتیکه بین آنها فاصله یا ویرگول یا نقطه ویرگول یا بدون هیچ کاراکتر جداکننده‌ای قرار داشته باشد مشابه تصویر زیر:
برای مطلع شدن از جدیدترین مطالب سایت، کانال پی‌سی‌ترفند در تلگرام را با آی‌دی pctarfandir@ دنبال کنید:
|
سوالات خود در زمینه ورد و اکسل و ... را میتوانید از طریق ایمیل و تلگرام با ما مطرح کنید. آدرس ایمیل: tarfandha.blog@gmail.com آی‌دی تلگرام: pctarfand_admin@ |
نظرات (۲۲)
-
الهام
سلام
اطلاعات چند دسته محصول در یک شیت اکسل با 320 row به من داده شده تا در همان شیت، محصولات یکسان دردیف های پشت سرهم قرار داده شوند.
ممنون میشم راهنمایی ام کنید که به چه صورت میتونم این کار را انجام بدهم؟-
پاسخ:
-
-
علی
سلام
من یک ستون از اسم افراد دارم که تعدادی پروژه به اونها اختصاص دادم یعنی اسمشون چندین بار در یک ستون تکرار شده
حالا میخوام این تعداد تکرار رو برای هر نفر بدست بیارم و کل این داده رو بصورت یک نمودار دایره ای بر حسب درصد ببینم چه کاری باید انجام بدم
کلی بخوام بگم 120 تا پروژه (سلول) دارم که بین 12 نفر پخش شده و هر سلول معادل 0.833333333333 می باشد که مجموع بشه 100 درصد
میخوام بفهمم هر نفر چند درصد پروژه بهش اختصاص داده شده-
پاسخ:
سلامشما کافیست نام افراد را ستون اول در نظر بگیرید و در ستون دوم روبری هر نام عدد ۰.۸۳ درج کنید و با آموزش گفته شده نام های تکراری را یکپارچه و اعداد را با هم جمع کنید.موفق باشید.
-
-
صلاح الدین
سلام فرض کنید یک شیت خرید با اطلاعات کد کالا،اسم و تعداد داریم و شیت دوم کالاهای مصرف شده با همان اطلاعات وجود داره چطور میتونیم در شیت سوم اطلاعات مصرف شده رو رو به روی اطلاعات خرید شده قرار بدیم و در نهایت موجودی بگیریم (با توجه به اینکه اطلاعات در شیت ها هم ردیف نیستند)-
پاسخ:
-
-
مجید
سلام
وقت بخیر
ببخشید من دو ستون دارم یکی اسم افراد و دیگری کد آن ها ست من میخواهم کد افراد مشابه با یکدیگر مرج شو و ستون دوم تغییری نکند
چطور باید این کار را انجام دهم
فقط میخواهم ستون کدهای مشابه با یکدیگر مرج شوند و ستون اسامی بدون تغییر بماند-
پاسخ:
سلاممنظورتان از مرج شدن چیه؟ یعنی 2 ردیف به یک ردیف تبدیل شود؟
-
-
سلام وقت به خیر
فرض کنید در یک شیت لیست کامل محصولات به همراه نام محصول ، بارکد ، تعداد هر محصول و تعداد فروش هر محصول درج شده است ، ما در شیت دوم تمام محصولات را از شیت اول به صورت جدول دسته بندی کرده ایم ، حالا میخوایم گزارش یک ماهه بگیریم که در شیت سوم یک جا کپی میکنیم ، ولی چطوری میتونیم در شیت دوم اقلامی که در یک ماه گرفته شده رو درج کنیم که تعداد هر محصول از شیت سوم درست در جلوی خود محصول در شیت دوم وارد بشه و اشتباه در ردیفهای دیگه و محصولات دیگه وارد نشه ؟؟؟-
پاسخ:
سلامسوالتان کمی پیچیده شده. در صورت امکان در یک فایل اکسل توضیح دهید.
-
-
بهنام حاجیان
سلام.
اگر چنین چیزی
=b2+d2-f2
=b3+d3-f3
و به همین ترتیب ...
تا 100 سطر ( در یک ستون )، داشته باشیم و نخواهیم فرمولها را یک به یک بنویسیم باید چیکار کنیم؟-
پاسخ:
-
-
محمد
عالی بود
کارم راه افتاد -
سلام وقت بخیر . جناب توی این آموزش یک پارچه کردن عداد رو آموزش دادین ، اما ما نیاز داریم که حروف یک پارچه بشند ... نمیخوایم جمع بشند میخوایم کنار هم قرار بگیرند ... دقیقتر توضیح بدم اینطوری میشه که . از کامنت های اینستاگرام خروجی گرفتیم و هر شخص 5 نفر رو داخل یک کامنت تگ کرده ، میخوایم 5 کامنت یک شخص داخل یک سلول کنار هم قرار بگیرند ...
-
پاسخ:
سلاماز روش پنجم استفاده کنید.
-
-
امیر حسن زاده
ممنون کارم راه افتاد -
حمید
سلام در مورد جواب سوال من که دیروز جواب دادید ممنون . شاید سوال درست مطرح نشده است . من یک فایل اکسل دارم. که در ستون اول شماره پرسنلی افراد ودر ستون دوم نام افراد ودر ستون سوم میزان حقوق آنها درج شده است . با استفاده از consolidate جمع ستون اول ودوم را به من داده ولی نام پرسنل در ستون دوم نیامده است لطفا راهنمایی فرمائید .-
پاسخ:
سلامبا این روش ها شما فقط یک ستون را می توانید یکپارچه کنید.
-
-
حمید
سلام. ممنون از راهنمائی و همچنین سایت خوب شما .
من از روش اول استفاده کردم . ولی ستون دوم من که اسامی فارسی بود را نمی آورد . لطفا راهنمایی بفرمائید . با تشکر.-
پاسخ:
سلامشاید مشکل از فارسی بودن است. سایر روش ها را امتحان کرده اید؟
-
-
سپیده
این مطلب عالی بود. خیلی کار من راه انداخت. ممنون -
سید حیدر حسینی
متاسفانه در جدول پاشنه ای و راه حل اول داده ها بعد از تغییر بروز نمی شود. آیا راه حلی برای این مشکل وجود دارد ؟-
پاسخ:
سلام
در این روش چنین امکانی وجود ندارد. از سایر روش ها مثلا فرمول نویسی استفاده کنید.
موفق باشید.
-
-
سارا
سلام....واقعا ممنون. توضیحاتتون خیلی عالی و کار راه انداز هستن. -
سجاد
با سپاس،
روش اول عالی، کارم رو خیلی راحت کرد -
RB
THANKS A TON BUDDY -
yousefi
very like -
علی
عالی
دمتون گرم
کامل کامل -
بهزاد
واقعا دستتون درد نکنه خیلی وقت بود دنبال این قابلیت اکسل بودم. پیروز باشید -
ایمان
سلام
آیا بعد از جمع دیتا ها ... تعداد جمع زده شده ها نمایش داده میشود ؟ چون میخوام بعد جمع میانگین بگیرم-
پاسخ:
سلام
چرا از تابع Average برای محاسبه میانگین استفاده نمی‌کنید؟
-
-
یوسف
مدیریت محتر وب سایت ترفندهابا سلام وتحتواقعا جا داره که از راهنمایی ارزنده شما بزرگواران تشکر بکنیم و امیدواریم که همیشه سربلند و پایدار باشید -
امید رضا ایزدی
با تشکر از سایت بسیار بسیار خوبتون ان شاء الله که خداوند هر چه در دل دارید نصیبتون گرداند باز هم ممنون
