یکی از توابع مفید اکسل برای محاسبات ریاضی، تابع MOD است. تابع MOD یا باقیمانده در اکسل برای محاسبه کردن باقیمانده تقسیم یک عدد بر عددی دیگر به کار می‌رود. می‌توانید از این تابع در ترکیب با توابعی نظیر MATCH و SMALL و INDEX استفاده کنید و فرمول‌های جالب و کاربردی بنویسید.
به عنوان مثال اگر ۳ را بر ۲ تقسیم کنیم و هدف رسیدن به یک عدد اعشاری نباشد، باقیمانده ۱ است. لذا خروجی تابع MOD عدد ۱ خواهد بود. در ادامه به سینتکس و روش نوشتن فرمول به کمک تابع MOD در اکسل می‌پردازیم و چند مثال ساده را بررسی می‌کنیم.
محاسبه کردن باقیمانده‌ی تقسیم در محاسبات آماری و ریاضی مختلف کاربرد دارد. شاید قبلاً با تابع MOD در ریاضیات و برنامه‌ها روبرو شده باشید و برایتان سوال شده که Mod چیست در ریاضی؟ به طور کلی در ریاضیات و در برخی از زبان‌های برنامه نویسی برای محاسبه باقیمانده از تابع MOD استفاده می‌شود و در اکسل نیز به همین صورت است.
فرمول تابع MOD در اکسل
سینتکس کلی تابع MOD در اکسل به صورت زیر است که در آن آرگومان اول عددی است که قرار است تقسیم شود و آرگومان دوم، عددی است که عدد اول بر آن تقسیم می‌شود:
=MOD(a,b)
به عنوان مثال حاصل تقسیم عدد ۲۰ بر عدد ۳، عدد ۶ است و باقیمانده نیز ۲ است. لذا خروجی فرمول زیر عدد ۲ است:
=MOD(20,3)
دقت کنید که تابع MOD در تقسیم کردن بر عدد منفی نیز کاربرد دارد و خروجی آن عدد منفی خواهد بود. نکته‌ی دیگر این است که عدد دوم نباید صفر باشد چرا که تقسیم کردن بر 0 امکان‌پذیر نیست و نرم‌افزار Excel ارور #DIV/0! در سلول نمایش می‌دهد.
استفاده از تابع MOD برای بهینه کردن بسته‌بندی
شاید به نظر برسد که تابع MOD بیشتر برای برای برنامه‌نویسی و کارهای خاص مفید است اما کاربردهای عملی آن زیاد است. به عنوان مثال فرض کنید که اجناس مختلفی دارید که قرار است بسته‌بندی و توزیع شود. تعداد اجناس دقیقاً مضربی از ظرفیت هر بسته یا کارتن نیست و در نتیجه بعضی از کارتن‌ها کاملاً پر نمی‌شود.
برای توزیع بسته‌بندی بهینه، ابتدا اطلاعات مسأله را به صورت شناسه‌ی محصول در ستون A و تعداد محصول در ستون B وارد می‌کنیم. در ردیف عنوان ستون‌های C تا F نیز ابعاد کارتن‌های مختلف وارد شده است. ستون G نیز برای محاسبه کردن بسته‌بندی بهینه است.
برای محاسبه کردن باقیمانده‌ی چیدن اولین محصول در بسته‌بندی با ظرفیت کارتن در ستون C از فرمول زیر استفاده می‌کنیم:
=MOD(B3,C2)
با توجه به اینکه می‌خواهیم فرمول فوق را برای سایر کارتن‌ها و محصولات نیز تکرار و کپی کنیم، لازم است که بعضی از ارجاع‌ها در فرمول فوق به صورت مطلق باشد و نه نسبی، به عبارت دیگر:
- با کپی کردن فرمول نباید C2 به C3 و C4 تغییر کند. لذا شماره ردیف را با علامت $ به ارجاع مطلق تبدیل می‌کنیم.
- با کپی کردن فرمول فوق نباید نام ستون در آدرس دادن به سلول B2 به C2 و D2 و غیره تغییر کند، لذا از نماد $ قبل از نام ستون B استفاده می‌کنیم.
با این دو تغییر، فرمول به صورت زیر خواهد شد:
=MOD($B3,C$2)
اکنون کلید Ctrl + Enter را بزنید تا خروجی فرمول در سلول فعلی ظاهر شود. عدد ۲۴ بر ۱۰ تقسیم می‌شود و باقیمانده عدد ۴ است.
اکنون با درگ کردن مربع کوچک و راست پایین سلول به سمت سلول F، فرمول در ردیف ۱ تکرار می‌شود. با توجه به اینکه ارجاع‌ها را بهینه کرده‌ایم، در هر سلول باقیمانده تقسیم کردن تعداد اجناس بر ظرفیت کارتن‌ها نمایش داده می‌شود.
می‌توانید بلافاصله مربع کوچک راست و پایین سلول F را به سمت پایین بکشید تا در تمام سلول‌های ۴ ستون ظرفیت کارتن‌ها، باقیمانده تقسیم‌ها درج شود.
مسأله بعدی پیدا کردن ظرفیت بهینه کارتن برای بسته‌بندی هر محصول است. هر چه باقیمانده کوچک‌تر باشد، طبعاً کارتن انتخاب شده برای بسته‌بندی محصول بهتر است. برای شناسایی کوچک‌ترین عدد در چند سلول از تابع SMALL استفاده می‌کنیم. به عنوان مثال کوچک‌ترین عدد بین سلول‌های C3 تا F3 به صورت زیر شناسایی می‌شود:
=SMALL(C3:F3,1)
گام بعدی پیدا کردن موقعیت کوچک‌ترین عدد است که با تابع XMATCH صورت می‌گیرد:
=XMATCH(SMALL(C3:F3,1),C3:F3)
بعد از پیدا کردن موقعیت کوچک‌ترین عدد باقیمانده، نوبت به پیدا کردن ظرفیت کارتن مربوطه است. به عنوان مثال اگر کوچک‌ترین عدد 0 باشد، به این معنی است که تعداد محصول دقیقاً مضربی از ظرفیت کارتن است و سوال این است که ظرفیت کارتن برای چنین حالتی چقدر است؟
برای پیدا کردن ظرفیت کارتن مربوط به حالتی که باقیمانده حداقل است، از تابع INDEX استفاده می‌کنیم. اما دقت کنید که به ردیف ظرفیت کارتن‌ها به صورت مطلق ارجاع بدهید:
=INDEX($C$2:$F$2,1,XMATCH(SMALL(C3:F3,1),C3:F3))
خروجی فرمول فوق ظرفیت کارتنی است که در بسته‌بندی حداقل باقیمانده را خواهد داشت.
اکنون ردیف اول کامل است و می‌توان همه سلول‌ها را انتخاب کرد و سپس مربع کوچک و راست پایین آخرین سلول را گرفت و به سمت پایین کشید تا کپی کردن فرمول در ردیف‌های بعدی انجام شود.
به این ترتیب در ستون G ظرفیت بهینه کارتن برای بسته‌بندی هر یک از محصولات ظاهر می‌شود:
نکته: ممکن است بعضی از محصولات در صورت بسته‌بندی در ۲ نوع کارتن باقیمانده‌ای نداشته باشند یا باقیمانده یکسان باشد. تابع XMATCH فقط اولین کارتن با چنین شرایطی را شناسایی می‌کند.
مزیت این جدول و فرمول‌نویسی به کمک تابع MOD در این است که هر بار قبل از توزیع کالا، با وارد کردن و تغییر دادن تعداد محصولات، کارتن مناسب برای بسته‌بندی به راحتی مشخص می‌شود.
howtogeekسیاره‌ی آی‌تی