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

به منظور آشنایی با موضوع جمع و همچنین تعیین آدرس در فرمول‌های اکسل بهتر است نوشتارهای دیگر مجله فرادرس در این رابطه از جمله جمع در اکسل — از صفر تا صد و آدرس دهی در اکسل با تابع INDIRECT — راهنمای کاربردی را مطالعه کنید. همچنین خواندن مطالب آموزش مقدماتی اکسل (Excel) — به زبان ساده و فرمول نویسی در اکسل – آموزش مقدماتی نیز خالی از لطف نیست.

جمع سفارشی در اکسل

در نوشتارهای دیگر مجله فرادرس با تابع جمع (SUM)، جمع شرطی (SUMIF) و جمع چند شرطی (SUMIFS) آشنا شدید. البته ابزارهایی برای جمع‌بندی در اکسل مانند جدول محوری (PivotTable) نیز در اکسل وجود دارد که به کمک آن می‌توان جمع‌بندی برای یک ناحیه یا جدولی از کاربرگ را به شکل سفارشی انجام داد. ولی موضوعی که در اینجا مورد اشاره قرار می‌گیرد، اندکی با موضوع جمع‌هایی که تا به حال انجام داده‌ایم، تفاوت دارد.

در اینجا بدون هیچ  ستونی به عنوان راهنما (که براساس آن شرط یا ناحیه خاصی از کاربرگ تعیین شود)، می‌خواهیم با مشخص کردن تعداد مشخصی از سلول‌ها در یک ناحیه، جمع منظمی از مقادیر را بدست آوریم. برای روشن شدن موضوع به مثالی که در ادامه خواهد آمد، توجه کنید.

مثال

یک کاربرگ را در نظر بگیرید که مقادیر مربوط به هزینه‌های مربوط به یک شرکت به صورت ماهانه در طول 5 سال در آن ثبت شده است. متاسفانه فردی که این اطلاعات را در این کاربرگ ذخیره کرده، فراموش کرده ستونی را به سال مربوط به هزینه‌ها اختصاص دهد. ولی رییس شرکت از کارشناس حسابداری می‌خواهد که مجموع هزینه‌ها را به تفکیک سال طی گزارشی، محاسبه و ارسال کند. نمونه‌ای از این کاربرگ در تصویر ۱، قابل مشاهده است.

custom sum sheet1
تصویر ۱: هزینه‌ ماهانه شرکت

به نظر شما چگونه باید این کار صورت گیرد؟ اگر عمل جمع را به شکل دستی برای هر ۱۲ عدد اول، دوم، سوم و … انجام دهیم، زمانی زیادی طول خواهد کشید. در عین حال ممکن است هنگام فرمول نویسی برای جمع، دچار خطا شده و یک یا چند سلول را به طور تکراری در عمل جمع دخیل کنیم. شاید بتوان راه حل بهتری برای انجام این عمل به استفاده از توابع SUM و INDIRECT پیدا کرد.

به یاد دارید که تابع SUM عمل جمع را روی یک ناحیه انجام می‌دهد به همین جهت یک تابع برداری است، زیرا مقادیر ورودی آن یک یا چند بردار بوده ولی نتیجه آن می‌تواند به عنوان یک مقدار در سلول ثبت شود.

همینطور تابع INDIRECT برای مشخص کردن آدرس یک سلول به صورت نوشتاری، نقش بازی می‌کند. بنابراین اگر بتوانیم با استفاده از این تابع، ناحیه‌های منظمی از کاربرگ و جدول اطلاعاتی را برای تابع SUM معرفی کنیم، مسئله مربوط به مثال را حل خواهیم کرد.

از طرفی سعی می‌کنیم که فرمول و استفاده از این دو تابع به شکلی باشد که انعطاف در محاسبات نیز وجود داشته باشد. مشخص است که برای مثال مربوطه، باید فاصله بین سلول‌ها در جمع‌بندی (اندازه ناحیه) شامل ۱۲ عدد باشد. ولی فرمول را به شکلی می‌نویسیم که اندازه ناحیه برای جمع‌بندی با یک پارامتر در فرمول مشخص شود تا هر زمان که لازم بود، نواحی مختلف، مثلا با طول ۶ یا ۳ برای محاسبه جمع هزینه‌های شش ماهه یا سه ماهه (فصلی) در نظر گرفته شده و محاسبات طبق آن انجام گیرد.

برای حل این مشکل و مسئله گفته شده در مثال، سه گام اساسی باید برداشته شود. در ادامه این مراحل را مشخص کرده‌ و به صورت گام به گام طی می‌کنیم.

گام اول؛ پیش‌نیازهای کاربرگ برای جمع سفارشی در اکسل

برای استفاده از تابع INDIRECT باید بتوانیم نواحی مربوط به یک ستون را مشخص کنیم. بنابراین در گام اول، یک ستون به قبل از مقادیر عددی مربوط به مثالمان اضافه کرده و در آن شماره ردیف را برای هر یک از ارقام هزینه‌ها ثبت می‌کنیم.

برای انجام این کار روی ستون A به کمک کلیک راست فهرست دستورات را ظاهر کرده و Insert را انتخاب کنید. به این ترتیب یک ستون به قبل از ستون A اضافه شده و مقادیر هزینه به ستون B منتقل خواهند شد. حال شماره ردیف را در ستون A وارد کنید.

البته می‌توانید مقدار ۱ و ۲ را در سطر اول و دوم وارد کرده و با انتخاب هر دوی این سلول‌ها «دستگیره پر کردن» (Fill Handle) را گرفته و تا جایی که احتیاج دارید کشیده سپس رها کنید. توجه داشته باشید که دستگیره پر کردن به شکل یک مربع کوچک در انتهایی ناحیه انتخابی قرار دارد.

FILL HANDLE
دستگیره پر کردن سلول اکسل

از طرفی می‌خواهیم جمع این نواحی منعطف هم باشد به این معنی که هر زمان لازم بود بتوانیم به جای جمع ۱۲ تایی، مجموع ۶تایی یا ۳ تایی از نواحی را مشخص کنیم. به همین علت سلول E2 را برای مشخص کردن طول هر ناحیه در نظر می‌گیریم. مقداری که در این سلول نوشته شده باشد، ابعاد سلول‌ها یا میزان پرش برای رسیدن به ناحیه بعدی را تعیین می‌کند. البته برای معرفی این پارامتر نیز در سلول بالای عنوانی نیز درج کرده‌ایم.

با انجام این کارها، کاربرگ مربوط به تصویر ۱ به شکل جدیدی که در تصویر 2 دیده می‌شود، در خواهد آمد. به این موضوع توجه داشته باشید که در سلول E2 میزان پرش برابر با ۳ در نظر گرفته شده است.

custom sum sheet2
تصویر ۲: کاربرگ جمع سفارشی برای هزینه‌ها با در نظر گرفتن سلولی برای طول ناحیه‌ها

گام دوم: محاسبه نقطه آغاز و پایان جمع سفارشی در اکسل

در این گام، از فرمول‌هایی برای تعیین ناحیه جمع سفارشی استفاده می‌کنیم. البته در این قسمت راهنمای ما ستون شماره ردیف خواهد بود. فرمول زیر را در نظر بگیرید.

$$ \large \mathbb{ =$E$2*(A2-1)+1 }$$

فرض کنید فرمول بالا را در سلول C2 نوشته‌ایم. مقدار این فرمول برای این سلول برابر با ۱ است. در نتیجه می‌توان این مقدار را نقطه آغاز جمع سفارشی در نظر گرفت.

نکته: به آدرس مطلق سلول E2 در فرمول توجه کنید. علامت‌های $ در ابتدا نام ستون و شماره ردیف باعث شده‌اند که نوع ارجاع به این سلول به صورت مطلق (Absolute) باشد.

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

$$ \large \mathbb{= $E$2*A2 }$$

مقدار این فرمول نیز برابر با ۳ خواهد بود. در نتیجه از آن به عنوان نقطه پایانی جمع سفارشی در هر مرحله استفاده خواهیم کرد. فرض کنید این فرمول را در سلول D2 نوشته‌ایم.

اگر این فرمول‌ها را در سلول‌های بعدی (یعنی C3, C4 , … همینطور D2 , D3 , …) کپی کنیم، با توجه به آدرس مطلق برای E2 و آدرس نسبی برای A2، فرمول‌ها در کاربرگ به مانند تصویر زیر در خواهند آمد.

Custom sum sheet3
تصویر ۳: محاسبه نقطه آغاز و پایان جمع سفارشی

گام سوم: محاسبه جمع سفارشی در اکسل

در این گام باید از تابع SUM برای جمع کردن ناحیه مشخص شده استفاده کنیم. البته ناحیه‌ای که باید در جمع به کار برده شود به کمک تابع INDIRECT و نواحی مشخص شده در گام دوم تعیین می‌شوند.

به این ترتیب فرمول یا تابع SUM را به شکل زیر برای سلول F2 می‌نویسیم:

$$ \large \mathbb{ =SUM(INDIRECT(“B”\&C2):INDIRECT(“B”\&D2)) }$$

به تعداد پرانتزهای باز و بسته توجه داشته باشید. همچنین به یاد داشته باشید که علامت & برای اتصال یا چسباندن دو عبارت متنی به کار می‌رود. بنابراین برای آنکه مثلا آدرس سلول ‌B2 را نشان دهیم عبارت ‘B’ را به محتویات سلول C2 به کمک عملگر & چسبانده‌ایم.

با کپی کردن سلول F2 به سلول‌های پایین نتیجه یعنی مجموع سه ماهه ارقام هزینه حاصل می‌شود. با تغییر مقدار E2 به ۱۲، مجموع هزینه‌های سالانه و با مقدار ۶ برای این سلول، مجموع هزینه‌های شش ماهه حاصل می‌شود. تصویر ۴، نتیجه حاصل را برای مثال گفته شده نشان می‌دهد.

custom sum sheet4
تصویر ۴: جمع سفارشی با استفاده از تابع SUM و INDIRECT در اکسل

نکته: حروف کوچک یا بزرگ تاثیری در مشخص کردن آدرس سلول‌ها ندارند بنابراین اگر برای آدرس سلول آغازی از b و برای آدرس سلول پایانی از B کمک گرفته‌ایم، با خطا مواجه نخواهیم شد. فقط به علامت ” که برای مشخص کردن عبارت متنی در تابع INDIRECT استفاده شده توجه کنید.

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

custom sum sheet5
تصویر ۵: محاسبه جمع سفارشی در اکسل به کمک یک ستون محاسباتی

اگر بخواهید با محاسبات انجام گرفته بیشتر آشنا شده و احتمالا آن‌ها را به دلخواه خود درآورید، می‌توانید فایل مربوط به این کاربرگ‌ها را از اینجا دریافت کنید. البته این فایل در قالب فشرده ثبت شده و کافی است پس از خارج کردن آن از حالت فشرده، کارپوشه custom sum in excel.xlsx را در اکسل باز و استفاده کنید.

خلاصه و جمع‌بندی

در این نوشتار با مسئله جمع سفارشی در اکسل آشنا شدید و با استفاده از فرمول و توابع SUM، همچنین INDIRECT که البته از توابع پرکاربرد در اکسل هستند، نواحی خاصی از کاربرگ را جمع زدید. در این بین با استفاده از مثالی که مطرح شد، فرمولی قابل انعطاف ایجاد کردیم که به واسطه آن می‌توان جمع در نواحی منظمی از کاربرگ را محاسبه کرد. همانطور که مشاهده کردید، این جمع‌بندی متفاوت با جمع شرطی یا استفاده از ابزارهای جمع‌بندی مانند جدول محوری یا محاسبه زیرمجموع (Subtotal) است. حتی این گونه محاسبات توسط فیلترگذاری نیز به سختی صورت می‌گیرد.

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

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

^^

آرمان ری بد (+)

«آرمان ری‌بد» دکتری آمار در شاخه آمار ریاضی دارد. از علاقمندی‌های او، یادگیری ماشین، خوشه‌بندی و داده‌کاوی است و در حال حاضر نوشتارهای مربوط به آمار و یادگیری ماشین را در مجله فرادرس تهیه می‌کند.

بر اساس رای 3 نفر

آیا این مطلب برای شما مفید بود؟

4 نظر در “جمع سفارشی در اکسل — راهنمای کاربردی

  1. سلام.ممنون از مطالب مفیدتون.من میخوام در سلولی در یک شیت دیگ سه تا سلول از شیت قبل رو با هم جمع بزنم.و میخوام فرمول رو وقتی کپی میکنم سلول ها رو سه تا سه تا جمع بزنه.مثلا b3 c3 d3.و وقتی کپی کردم فرمول رو.مجموع e3 f3 g3 رو بهم بده.ممنون میشم کمکم کنید

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

  2. با سلام دو سوال داشتم .
    سوال اول فرمول جمع چندین بلوک سفارشی چجوریه ؟ یعنی مثلا میخوام P18 , P28, P42 و … باهم جمع بشه و در حالی که اعداد داخل همون بلوک ها حاصل جمع سطر یا ستون های دیگه ای هست .

    سوال دوم اینکه فرمول جمع چندین بلوک در شیت های مختلف در یک شیت جدید به چه شکلیه ؟ … یعنی مثلا در هر شیت مطابق سوال اول یک بلوک داریم و حالا در یک شیت میخوایم حاصل جمع تمام بلوک های مورد نظر در شیت های مشابه رو اونجا به ما بده .

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *