جمع سفارشی در اکسل – راهنمای کاربردی
جمع سفارشی در اکسل
در اینجا بدون هیچ ستونی به عنوان راهنما (که براساس آن شرط یا ناحیه خاصی از کاربرگ تعیین شود)، میخواهیم با مشخص کردن تعداد مشخصی از سلولها در یک ناحیه، جمع منظمی از مقادیر را بدست آوریم. برای روشن شدن موضوع به مثالی که در ادامه خواهد آمد، توجه کنید.
مثال
یک کاربرگ را در نظر بگیرید که مقادیر مربوط به هزینه‌های مربوط به یک شرکت به صورت ماهانه در طول 5 سال در آن ثبت شده است. متاسفانه فردی که این اطلاعات را در این کاربرگ ذخیره کرده، فراموش کرده ستونی را به سال مربوط به هزینه‌ها اختصاص دهد. ولی رییس شرکت از کارشناس حسابداری می‌خواهد که مجموع هزینه‌ها را به تفکیک سال طی گزارشی، محاسبه و ارسال کند. نمونه‌ای از این کاربرگ در تصویر ۱، قابل مشاهده است.
به نظر شما چگونه باید این کار صورت گیرد؟ اگر عمل جمع را به شکل دستی برای هر ۱۲ عدد اول، دوم، سوم و ... انجام دهیم، زمانی زیادی طول خواهد کشید. در عین حال ممکن است هنگام فرمول نویسی برای جمع، دچار خطا شده و یک یا چند سلول را به طور تکراری در عمل جمع دخیل کنیم. شاید بتوان راه حل بهتری برای انجام این عمل به استفاده از توابع SUM و INDIRECT پیدا کرد.
به یاد دارید که تابع SUM عمل جمع را روی یک ناحیه انجام می‌دهد به همین جهت یک تابع برداری است، زیرا مقادیر ورودی آن یک یا چند بردار بوده ولی نتیجه آن می‌تواند به عنوان یک مقدار در سلول ثبت شود.
همینطور تابع INDIRECT برای مشخص کردن آدرس یک سلول به صورت نوشتاری، نقش بازی می‌کند. بنابراین اگر بتوانیم با استفاده از این تابع، ناحیه‌های منظمی از کاربرگ و جدول اطلاعاتی را برای تابع SUM معرفی کنیم، مسئله مربوط به مثال را حل خواهیم کرد.
از طرفی سعی میکنیم که فرمول و استفاده از این دو تابع به شکلی باشد که انعطاف در محاسبات نیز وجود داشته باشد. مشخص است که برای مثال مربوطه، باید فاصله بین سلولها در جمع‌بندی (اندازه ناحیه) شامل ۱۲ عدد باشد. ولی فرمول را به شکلی می‌نویسیم که اندازه ناحیه برای جمع‌بندی با یک پارامتر در فرمول مشخص شود تا هر زمان که لازم بود، نواحی مختلف، مثلا با طول ۶ یا ۳ برای محاسبه جمع هزینه‌های شش ماهه یا سه ماهه (فصلی) در نظر گرفته شده و محاسبات طبق آن انجام گیرد.
برای حل این مشکل و مسئله گفته شده در مثال، سه گام اساسی باید برداشته شود. در ادامه این مراحل را مشخص کرده و به صورت گام به گام طی میکنیم.
گام اول؛ پیش‌نیازهای کاربرگ برای جمع سفارشی در اکسل
برای استفاده از تابع INDIRECT باید بتوانیم نواحی مربوط به یک ستون را مشخص کنیم. بنابراین در گام اول، یک ستون به قبل از مقادیر عددی مربوط به مثالمان اضافه کرده و در آن شماره ردیف را برای هر یک از ارقام هزینه‌ها ثبت میکنیم.
برای انجام این کار روی ستون A به کمک کلیک راست فهرست دستورات را ظاهر کرده و Insert را انتخاب کنید. به این ترتیب یک ستون به قبل از ستون A اضافه شده و مقادیر هزینه به ستون B منتقل خواهند شد. حال شماره ردیف را در ستون A وارد کنید.
البته می‌توانید مقدار ۱ و ۲ را در سطر اول و دوم وارد کرده و با انتخاب هر دوی این سلولها «دستگیره پر کردن» (Fill Handle) را گرفته و تا جایی که احتیاج دارید کشیده سپس رها کنید. توجه داشته باشید که دستگیره پر کردن به شکل یک مربع کوچک در انتهایی ناحیه انتخابی قرار دارد.
از طرفی میخواهیم جمع این نواحی منعطف هم باشد به این معنی که هر زمان لازم بود بتوانیم به جای جمع ۱۲ تایی، مجموع ۶تایی یا ۳تایی از نواحی را مشخص کنیم. به همین علت سلول E2 را برای مشخص کردن طول هر ناحیه در نظر می‌گیریم. مقداری که در این سلول نوشته شده باشد، ابعاد سلولها یا میزان پرش برای رسیدن به ناحیه بعدی را تعیین می‌کند. البته برای معرفی این پارامتر نیز در سلول بالای عنوانی نیز درج کرده‌ایم.
با انجام این کارها، کاربرگ مربوط به تصویر ۱ به شکل جدیدی که در تصویر 2 دیده میشود، در خواهد آمد. به این موضوع توجه داشته باشید که در سلول E2 میزان پرش برابر با ۳ در نظر گرفته شده است.
گام دوم: محاسبه نقطه آغاز و پایان جمع سفارشی در اکسل
مطلبی که در بالا مطالعه کردید بخشی از مجموعه مطالب «چگونه در اکسل جمع کنیم؟» است. در ادامه، می‌توانید فهرست این مطالب را ببینید:
- جمع در اکسل — از صفر تا صد
- انواع روش های جمع در اکسل – آموزش 13 روش + مثال
- جمع ستون در اکسل — ۵ روش کاربردی به زبان ساده
- جمع زدن چند سطر در اکسل – 4 روش کاربردی
- جمع محدوده در اکسل – آموزش تصویری و ساده
- جمع کردن اعداد در اکسل — آموزش سریع روش ها و فرمول ها + فیلم آموزشی
- جمع زدن اتوماتیک در اکسل – توضیح کامل جمع خودکار
- جمع سفارشی در اکسل — راهنمای کاربردی(همین مطلب)
- نوشتن فرمول جمع در اکسل — به زبان ساده + مثال
- تابع SUMIF در اکسل — آموزش با مثالهای کاربردی
- جمع شرطی در اکسل – آموزش تابع و فرمول به زبان ساده
- جمع در اکسل با شرط رنگ سلول – آموزش تصویری و ساده
- توابع شمارشی و جمع بندی پیشرفته در اکسل (+ دانلود فیلم آموزش گام به گام)
- تابع جمع بندی AGGREGATE در اکسل — راهنمای کاربردی
- جمع فیلتر در اکسل با 4 روش کاربردی
- جمع سلول های خاص در اکسل با 4 روش کاربردی
- جمع ساعت در اکسل – آموزش 9 روش کاربردی
مطالب مجله فرادرس را در جستجوی گوگل سریع‌تر ببینید
سلام با فرض اینکه تعدادی داده داریم و میخواهیم مثلا عدادی ک جمع آنها مثلا ۵۰ میشود برایمان هایلات کند راهی وجود دارد؟
با سلام
می خواهم سلوهای اکسل که بصورت
2A
3B
4A
3B
6C
5A
3B
2C
8A
1B
3C
میباشد که برنامه جمع بزند و بصورت
19A
10B
11C
نمایش دهد
چه جوری میشه وقتی یک ستون رو مخفی میکنیم از حاصل جمع ما کم بشه
جمع داده هایی که در ستون دیگر مشترک هستند و مقدار عددی متفاوت دارن،واطلاعی از مکان انها نداریم. اکسل پیدا کنه بعد جمع کنه
سلام
فرمول جمع کردن در یک ستون هر 10 یا هر 20 ردیفی را می‌خواهم ممنون میشم
با سلام
مشكلي در اكسل دارم كه اگر امكان دارد راهنمايي بفرمايد. يك ستون دارم كه داخل آن هم عدد هست و هم متن. فرمول SUM و ساير فرمولها براي جمع اعداد در آن كار نمي كند. چون كه مابين اعداد حروف هم مي باشند. بنابراين چكار مي توانم انجام دهم كه در يك ستون فقط بيايد و اعداد را باهم جمع يا تفريق كند.
با تشكر از راهنمايي شما
سلام من فاکتورهای فروش رو ثبت میکنم و هر بار مقادیر را جمع میزنم مقدار متفاوتی نشون میده و هر دفعه بیشتر از پیش بدون تغییر اعداد. اعداد من ممیز دارند و بعضی از سلولها خودکار با فرمت تکست تغییر میکنند.از فرمول ساده ی =و fxاستفاده میکنم
ممنون که راهنمایی میفرمایید خیلی بابت این موضوع اذیت میشم
سلام یه سوال داشتم. توی اکسل یه جدول دارم که اقلام موجود تو شرکت دسته بندی شده. دسته بندیش با حروف انگلیسیه. چطور میتونم ببینم چندتا مثلا a وجود داره چندتا b و وقتی اقلام موجود هر حرف رو اصافه میکنم تعداد روبروی این حروف هم زیاد بشن؟
سلام دوست و همراه گرامی،
به منظور جمع شرطی در اکسل بهتر است مطلب جمع در اکسل — از صفر تا صد را مطالعه کنید. در بخش جمع شرطی دقیقا به موضوع مورد نظر شما اشاره شده است.
تندرست و پیروز باشید.
سلام.ممنون از مطالب مفیدتون.من میخوام در سلولی در یک شیت دیگ سه تا سلول از شیت قبل رو با هم جمع بزنم.و میخوام فرمول رو وقتی کپی میکنم سلول ها رو سه تا سه تا جمع بزنه.مثلا b3 c3 d3.و وقتی کپی کردم فرمول رو.مجموع e3 f3 g3 رو بهم بده.ممنون میشم کمکم کنید
سلام و درود بر شما مخاطب گرامی،
اتفاقا در متنی که پیشرو دارید، دقیقا به همین موضوع اشاره شده است. به فرمولها و توابعی که گفته شده توجه کنید. پاسخ شما در متن موجود است.
از اینکه همراه مجله فرادرس هستید بسیار خرسندیم.
موفق و تندرست و شاد باشید.
با سلام دو سوال داشتم .
سوال اول فرمول جمع چندین بلوک سفارشی چجوریه ؟ یعنی مثلا میخوام P18 , P28, P42 و … باهم جمع بشه و در حالی که اعداد داخل همون بلوک ها حاصل جمع سطر یا ستون های دیگه ای هست .
سوال دوم اینکه فرمول جمع چندین بلوک در شیت های مختلف در یک شیت جدید به چه شکلیه ؟ … یعنی مثلا در هر شیت مطابق سوال اول یک بلوک داریم و حالا در یک شیت میخوایم حاصل جمع تمام بلوک های مورد نظر در شیت های مشابه رو اونجا به ما بده .
سلام دوست عزیز و همراه مجله فرادرس
در مورد نحوه اجرای تابع جمع در اکسل برای ناحیه‌های همجوار یا غیرهمجوار بهتر است نوشتار دیگری از مجله فرادرس با عنوان جمع در اکسل به زبان ساده را مطالعه کنید.
شاد، تندرست و پیروز باشید.