آموزش تابع MOD در اکسل
معرفی تابع MOD
تابع MOD یکی از توابع ریاضی اکسل است که باقیمانده تقسیم یک عدد بر عدد دیگر را محاسبه میکند. این تابع زمانی کاربرد دارد که بخواهید زوج یا فرد بودن اعداد، بخشپذیری، تعداد باقیمانده کالاها، الگوهای تکرارشونده یا دورههای زمانی را بررسی کنید.
برای مثال، اگر عدد ۱۷ را بر ۵ تقسیم کنیم، حاصل تقسیم برابر ۳ و باقیمانده برابر ۲ است. تابع MOD همین باقیمانده را برمیگرداند.
=MOD(17;5)
نتیجه این فرمول عدد ۲ خواهد بود.
تابع MOD فقط قسمت باقیمانده را محاسبه میکند و حاصل اصلی تقسیم را نمایش نمیدهد.
کاربردهای اصلی تابع MOD
- محاسبه باقیمانده تقسیم دو عدد
- تشخیص زوج یا فرد بودن اعداد
- بررسی بخشپذیری یک عدد بر عدد دیگر
- محاسبه تعداد کالاهای باقیمانده پس از بستهبندی
- ایجاد الگوهای دورهای و تکرارشونده
- رنگآمیزی یکی در میان یا دورهای ردیفها در قالببندی شرطی
- تقسیم کارکنان، سفارشها یا محصولات به چند گروه
- کنترل دورههای هفتگی، ماهانه یا شیفتهای کاری
- شناسایی هر چندمین ردیف در یک جدول
- محاسبه باقیمانده مجموع چند مقدار
ساختار (Syntax)
ساختار انگلیسی تابع MOD به شکل زیر است:
=MOD(number, divisor)
نمایش آرگومانها به زبان فارسی:
=MOD(عدد; مقسومعلیه)
تابع MOD دقیقاً دو آرگومان دارد. در نسخههایی از اکسل که جداکننده آرگومانها ویرگول است باید از ویرگول استفاده کنید. در بسیاری از تنظیمات منطقهای فارسی، جداکننده آرگومانها نقطهویرگول است.
منطق محاسبه MOD را میتوان با رابطه زیر توضیح داد:
=number-divisor*INT(number/divisor)
آرگومانها
number / عدد مورد تقسیم
عدد یا آدرس سلولی است که میخواهید باقیمانده تقسیم آن را محاسبه کنید. این آرگومان میتواند عدد مثبت، منفی، اعشاری یا نتیجه یک فرمول دیگر باشد.
divisor / مقسومعلیه
عددی است که آرگومان اول بر آن تقسیم میشود. این مقدار نباید صفر باشد؛ زیرا تقسیم بر صفر در اکسل امکانپذیر نیست.
هر دو آرگومان ضروری هستند و حذف هرکدام باعث ایجاد خطا میشود.
مثالهای ساده و پایه
محاسبه باقیمانده یک تقسیم ساده
اگر در سلول A2 عدد ۲۳ و در سلول B2 عدد ۷ قرار داشته باشد، فرمول زیر باقیمانده تقسیم را محاسبه میکند:
=MOD(A2;B2)
نتیجه برابر ۲ است؛ زیرا با تقسیم ۲۳ بر ۷، مقدار ۲ باقی میماند.
تشخیص عدد زوج
هر عددی که باقیمانده تقسیم آن بر ۲ برابر صفر باشد، عددی زوج است.
=MOD(A2;2)=0
اگر عدد موجود در A2 زوج باشد، نتیجه TRUE و در غیر این صورت FALSE خواهد بود.
تشخیص عدد فرد
برای تشخیص اعداد فرد میتوان بررسی کرد که باقیمانده تقسیم عدد بر ۲ برابر ۱ باشد.
=MOD(A2;2)=1
این فرمول برای اعداد صحیح مثبت، مقدار TRUE را برای اعداد فرد برمیگرداند.
بررسی بخشپذیری
برای بررسی اینکه عدد موجود در A2 بر ۵ بخشپذیر است یا خیر، از فرمول زیر استفاده کنید:
=MOD(A2;5)=0
نتیجه TRUE نشان میدهد که عدد بدون باقیمانده بر ۵ تقسیم میشود.
کار با اعداد منفی
علامت نتیجه تابع MOD با علامت مقسومعلیه یکسان است. بنابراین نتیجه فرمولهای زیر ممکن است در نگاه اول متفاوت از انتظار شما باشد:
=MOD(-10;3)
نتیجه فرمول بالا برابر ۲ است.
=MOD(10;-3)
نتیجه فرمول بالا برابر منفی ۲ است.
مثالهای کاربردی و واقعی
محاسبه تعداد کالاهای باقیمانده پس از بستهبندی
فرض کنید تعداد کل کالاها در B2 و ظرفیت هر بسته در C2 قرار دارد. برای محاسبه تعداد کالاهایی که پس از تکمیل بستهها باقی میمانند، از فرمول زیر استفاده کنید:
=MOD(B2;C2)
اگر ۱۲۵ کالا داشته باشید و در هر بسته ۱۲ کالا قرار گیرد، ۵ کالا باقی خواهد ماند.
ایجاد برنامه شیفت چرخشی
فرض کنید شماره روز در A2 قرار دارد و شیفتها باید به ترتیب صبح، عصر و شب تکرار شوند:
=CHOOSE(MOD(A2-1;3)+1;"صبح";"عصر";"شب")
تابع MOD شماره روز را به یک چرخه سهتایی تبدیل میکند و CHOOSE نام شیفت مناسب را نمایش میدهد.
مشخص کردن ماه هر فصل
اگر یک تاریخ در A2 قرار داشته باشد، فرمول زیر مشخص میکند تاریخ مربوط به ماه اول، دوم یا سوم فصل است:
=MOD(MONTH(A2)-1;3)+1
رنگآمیزی ردیفهای زوج
فرمول زیر را میتوان در بخش Conditional Formatting وارد کرد تا ردیفهای زوج رنگآمیزی شوند:
=MOD(ROW();2)=0
رنگآمیزی هر سومین ردیف
اگر محدوده جدول از ردیف دوم شروع شده باشد، فرمول زیر ردیفهای ۲، ۵، ۸ و ردیفهای بعدی با همین الگو را مشخص میکند:
=MOD(ROW()-ROW($A$2);3)=0
محاسبه باقیمانده مجموع فروش
برای جمع کردن مقادیر B2 تا B10 و سپس محاسبه باقیمانده تقسیم مجموع بر ۷ از فرمول زیر استفاده کنید:
=MOD(SUM(B2:B10);7)
ترکیب MOD با AND
فرمول زیر بررسی میکند که عدد A2 هم زوج باشد و هم بزرگتر از ۱۰:
=AND(MOD(A2;2)=0;A2>10)
تنها زمانی که هر دو شرط برقرار باشند، نتیجه TRUE خواهد بود.
ترکیب MOD با OR
فرمول زیر بررسی میکند که عدد A2 بر ۵ یا ۷ بخشپذیر باشد:
=OR(MOD(A2;5)=0;MOD(A2;7)=0)
اگر حداقل یکی از شرطها برقرار باشد، نتیجه TRUE خواهد بود.
ترکیب MOD با COUNTIF
فرض کنید اعداد اصلی در ستون A قرار دارند. ابتدا در سلول C2 باقیمانده تقسیم هر عدد بر ۷ را محاسبه کرده و فرمول را به پایین کپی کنید:
=MOD(A2;7)
سپس برای شمارش اعدادی که بر ۷ بخشپذیر هستند، تعداد صفرهای ستون کمکی را بشمارید:
=COUNTIF(C2:C100;0)
ترکیب MOD با XLOOKUP
فرض کنید باقیماندههای صفر تا ۴ در محدوده E2:E6 و نام گروههای متناظر در F2:F6 نوشته شدهاند. فرمول زیر هر عدد را بر اساس باقیمانده تقسیم بر ۵ به یک گروه اختصاص میدهد:
=XLOOKUP(MOD(A2;5);$E$2:$E$6;$F$2:$F$6;"یافت نشد")
ترکیب تابع MOD با فرمولهای دیگر
- ترکیب با IF برای نمایش پیام متناسب با بخشپذیری عدد
- ترکیب با SUM برای محاسبه باقیمانده مجموع یک محدوده
- ترکیب با AND برای بررسی همزمان چند شرط
- ترکیب با OR برای بررسی چند حالت جایگزین
- ترکیب با COUNTIF و یک ستون کمکی برای شمارش باقیماندههای مشخص
- ترکیب با XLOOKUP برای گروهبندی دادهها بر اساس باقیمانده
- ترکیب با ROW برای ایجاد الگو در ردیفهای جدول
- ترکیب با MONTH و DATE برای ساخت دورههای زمانی
- ترکیب با CHOOSE برای ایجاد برنامههای چرخشی
نمایش زوج یا فرد بودن با IF
=IF(MOD(A2;2)=0;"زوج";"فرد")
نمایش وضعیت بخشپذیری
=IF(MOD(A2;5)=0;"بخشپذیر است";"بخشپذیر نیست")
شمارهگذاری چرخشی از ۱ تا ۴
فرمول زیر اعداد ۱ تا ۴ را بهصورت تکرارشونده تولید میکند:
=MOD(ROW()-ROW($A$2);4)+1
استفاده با SUMPRODUCT بدون ستون کمکی
برای شمارش تعداد اعداد بخشپذیر بر ۷ در محدوده A2:A100 میتوان از فرمول زیر استفاده کرد:
=SUMPRODUCT(--(MOD(A2:A100;7)=0))
استفاده با LET برای خوانایی بیشتر
در نسخههای جدید اکسل میتوان نتیجه MOD را در یک متغیر ذخیره کرد تا فرمول خواناتر شود:
=LET(r;MOD(A2;7);IF(r=0;"تقسیم کامل";r))
خطاهای رایج و روش رفع آنها
۱. خطای #DIV/۰!
این خطا زمانی رخ میدهد که مقسومعلیه صفر یا سلول مربوط به آن خالی باشد.
برای جلوگیری از خطا، قبل از اجرای MOD مقدار مقسومعلیه را بررسی کنید:
=IF(B2=0;"مقسومعلیه نامعتبر است";MOD(A2;B2))
۲. خطای #VALUE!
این خطا معمولاً زمانی ایجاد میشود که یکی از آرگومانها شامل متن غیرقابل تبدیل به عدد باشد. مطمئن شوید سلولها دارای داده عددی هستند.
اگر عدد بهصورت متن ذخیره شده است، میتوانید آن را با VALUE تبدیل کنید:
=MOD(VALUE(A2);B2)
۳. استفاده از جداکننده اشتباه
بسته به تنظیمات منطقهای سیستم، اکسل ممکن است از نقطهویرگول یا ویرگول بهعنوان جداکننده آرگومانها استفاده کند. اگر اکسل فرمول را نپذیرفت، جداکنندهها را مطابق تنظیمات نسخه خود تغییر دهید.
۴. دریافت نتیجه غیرمنتظره برای اعداد منفی
نتیجه MOD همعلامت با مقسومعلیه است. اگر همیشه به یک باقیمانده مثبت نیاز دارید، از قدر مطلق مقسومعلیه استفاده کنید:
=MOD(A2;ABS(B2))
۵. مشکلات دقت در اعداد اعشاری
اکسل اعداد اعشاری را بهصورت دودویی ذخیره میکند. در بعضی محاسبات ممکن است نتیجهای بسیار نزدیک به صفر، اما نه دقیقاً صفر، ایجاد شود. برای کنترل این مشکل نتیجه را گرد کنید:
=ROUND(MOD(A2;0.1);6)
۶. کپی نادرست آدرس سلولها
هنگام کپی کردن فرمول ممکن است آدرس مقسومعلیه نیز تغییر کند. اگر مقسومعلیه در یک سلول ثابت مانند B1 قرار دارد، از مرجع مطلق استفاده کنید:
=MOD(A2;$B$1)
۷. استفاده از درصد بهجای MOD
علامت درصد در اکسل برای تبدیل عدد به درصد استفاده میشود و عملگر باقیمانده تقسیم نیست. برای محاسبه باقیمانده باید از تابع MOD استفاده کنید.
نکات حرفهای و ترفندهای مهم
- برای تشخیص بخشپذیری، نتیجه MOD را با صفر مقایسه کنید.
- برای محاسبه زوج یا فرد بودن اعداد صحیح مثبت، مقسومعلیه را ۲ قرار دهید.
- برای ساخت گروههای تکراری، شماره ردیف یا شناسه را بر تعداد گروهها تقسیم کنید.
- اگر مقسومعلیه در یک سلول ثابت قرار دارد، مرجع آن را با علامت دلار ثابت کنید.
- برای جلوگیری از خطای تقسیم بر صفر، قبل از اجرای MOD مقدار مقسومعلیه را با IF بررسی کنید.
- برای دادههای اعشاری حساس، از ROUND در کنار MOD استفاده کنید.
- در فرمولهای طولانی نسخههای جدید اکسل، LET میتواند خوانایی و نگهداری فرمول را بهتر کند.
- در Microsoft ۳۶۵ میتوانید MOD را روی یک محدوده اجرا کنید تا نتایج بهصورت پویا در چند سلول منتشر شوند.
- برای شمارش نتایج MOD بدون ستون کمکی میتوان از SUMPRODUCT استفاده کرد.
- در محاسبات مالی و شناسههای طولانی، محدودیت دقت ۱۵ رقم اکسل را در نظر بگیرید.
اجرای MOD روی یک محدوده در اکسل جدید
در نسخههای دارای آرایه پویا، فرمول زیر باقیمانده تمام اعداد محدوده را نمایش میدهد:
=MOD(A2:A10;3)
تقسیم دادهها به پنج گروه
فرمول زیر برای هر ردیف یک شماره گروه از ۱ تا ۵ تولید میکند:
=MOD(ROW()-ROW($A$2);5)+1
تفاوت تابع MOD با توابع مشابه
تفاوت MOD و QUOTIENT
تابع MOD باقیمانده تقسیم را برمیگرداند، اما QUOTIENT قسمت صحیح حاصل تقسیم را نمایش میدهد.
برای عدد ۱۷ و مقسومعلیه ۵، فرمول زیر عدد ۲ را برمیگرداند:
=MOD(17;5)
فرمول زیر قسمت صحیح حاصل تقسیم، یعنی ۳، را نمایش میدهد:
=QUOTIENT(17;5)
تفاوت MOD و INT
تابع INT یک عدد را به سمت پایین گرد میکند، در حالی که MOD باقیمانده تقسیم را به دست میآورد. اکسل در منطق داخلی MOD از INT برای تعیین بخش صحیح تقسیم استفاده میکند.
تفاوت MOD با ISEVEN و ISODD
توابع ISEVEN و ISODD مستقیماً زوج یا فرد بودن عدد را بررسی میکنند، اما MOD انعطاف بیشتری دارد و میتواند بخشپذیری بر هر عددی را کنترل کند.
=ISEVEN(A2)
=ISODD(A2)
تفاوت MOD و MROUND
تابع MROUND عدد را به نزدیکترین مضرب یک مقدار گرد میکند، اما MOD فاصله باقیمانده تا یک مضرب کامل را محاسبه میکند.
=MROUND(A2;5)
تفاوت MOD و علامت درصد
علامت درصد مقدار را بر ۱۰۰ تقسیم کرده و آن را بهصورت درصد نمایش میدهد. این علامت در اکسل عملگر باقیمانده نیست و جایگزین MOD نمیشود.
سازگاری با نسخههای مختلف اکسل
تابع MOD یکی از توابع قدیمی و استاندارد اکسل است و در نسخههای مختلف از جمله Excel ۲۰۰۳، Excel ۲۰۰۷، Excel ۲۰۱۰، Excel ۲۰۱۳، Excel ۲۰۱۶، Excel ۲۰۱۹، Excel ۲۰۲۱، Excel ۲۰۲۴، Microsoft ۳۶۵، Excel for Mac و Excel for the web پشتیبانی میشود.
خود تابع MOD در نسخههای قدیمی نیز قابل استفاده است، اما برخی توابع ترکیبی محدودیت نسخه دارند:
- XLOOKUP در Microsoft ۳۶۵، Excel ۲۰۲۱، Excel ۲۰۲۴ و نسخه وب پشتیبانی میشود.
- LET در Microsoft ۳۶۵ و نسخههای جدید دائمی اکسل در دسترس است.
- انتشار خودکار نتایج آرایهای در چند سلول به نسخههای دارای Dynamic Arrays نیاز دارد.
- در نسخههای قدیمیتر میتوانید از MOD همراه با IF، SUMPRODUCT، INDEX و MATCH استفاده کنید.
- نوع جداکننده آرگومانها به نسخه اکسل وابسته نیست و معمولاً توسط تنظیمات منطقهای سیستم تعیین میشود.
سؤالات پرتکرار درباره تابع MOD
تابع MOD چه چیزی را محاسبه میکند؟
این تابع باقیمانده تقسیم یک عدد بر عدد دیگر را برمیگرداند.
چگونه با MOD عدد زوج را تشخیص دهیم؟
باقیمانده تقسیم عدد بر ۲ را با صفر مقایسه کنید:
=MOD(A2;2)=0
چگونه عدد فرد را تشخیص دهیم؟
برای اعداد صحیح مثبت، باقیمانده تقسیم بر ۲ باید برابر ۱ باشد:
=MOD(A2;2)=1
آیا MOD با اعداد اعشاری کار میکند؟
بله، هر دو آرگومان میتوانند اعشاری باشند. با این حال ممکن است به دلیل نحوه ذخیره اعداد اعشاری، لازم باشد نتیجه را با ROUND گرد کنید.
اگر مقسومعلیه صفر باشد چه اتفاقی میافتد؟
اکسل خطای #DIV/۰! نمایش میدهد. بهتر است قبل از محاسبه، صفر نبودن مقسومعلیه را بررسی کنید.
آیا MOD برای اعداد منفی قابل استفاده است؟
بله، اما علامت نتیجه با علامت مقسومعلیه یکسان خواهد بود.
چگونه بررسی کنیم یک عدد بر عدد دیگری بخشپذیر است؟
اگر نتیجه MOD برابر صفر باشد، تقسیم بدون باقیمانده انجام شده است:
=MOD(A2;B2)=0
آیا میتوان از MOD در قالببندی شرطی استفاده کرد؟
بله، MOD برای رنگآمیزی ردیفهای زوج، فرد یا هر چندمین ردیف بسیار مناسب است.
چگونه باقیمانده ساعتهای یک روز را محاسبه کنیم؟
اگر تعداد کل ساعتها در A2 باشد، فرمول زیر ساعتهای باقیمانده پس از تکمیل روزهای ۲۴ ساعته را نمایش میدهد:
=MOD(A2;24)
جمعبندی و پیشنهاد یادگیری بعدی
تابع MOD ابزاری ساده اما بسیار کاربردی برای محاسبه باقیمانده تقسیم است. با استفاده از این تابع میتوانید زوج یا فرد بودن اعداد، بخشپذیری، باقیمانده بستهبندی، شیفتهای چرخشی، گروهبندی دادهها و الگوهای تکراری را مدیریت کنید.
MOD در ترکیب با توابع IF، AND، OR، SUM، COUNTIF، SUMPRODUCT، XLOOKUP، CHOOSE و LET کاربردهای پیشرفتهتری پیدا میکند. برای ادامه یادگیری پیشنهاد میشود توابع QUOTIENT، INT، ISEVEN، ISODD، ROW، IF، SUMPRODUCT و XLOOKUP را نیز بررسی کنید.