توضیح تابع MOD در اکسل – از صفر تا صد با مثال

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

معرفی سینتکس تابع Mod و کاربرد اصلی آن

تابع Mod در اکسل برای به دست آوردن باقیمانده تقسیمِ مقسوم بر مقسوم‌علیه استفاده می‌شود. در ریاضیات به این عملیات هم‌نهشتی یا «Modulus» می‌گویند و نام تابع Mod از این عبارت گرفته شده است. در فرمول زیر سینتکس تابع Mod را می‌بینید:

MOD(number, divisor)

در این سینتکس آرگومان اول مقسوم و آرگومان دوم مقسوم‌علیه است. برای مثال MOD(10,3)  برابر با «۱» است چرا که حاصل تقسیم ۱۰ بر ۳ باقیمانده ۱ خواهد داشت (10=(3*3)+1 ). همینطور حاصل فرمول MOD(10,5)  برابر صفر خواهد بود چرا که ۱۰ بر ۵ بخش‌پذیر است و باقیمانده‌ای ندارد.

هنگام کار با تابع Mod به سه نکته زیر دقت کنید:

1. علامتِ مقداری که تابع Mod برمی‌گرداند، برابر با علامت مقسوم‌علیه خواهد بود.
2. اگر مقسوم‌علیه صفر باشد، تابع Mod خطای #DIV/0!   را برمی‌گرداند چرا که تقسیم بر صفر ممکن نیست.
3. اگر مقسوم الیه داده‌ای از جنس متن باشد، تابع Mod #VALUE!  را برمی‌گرداند.

تابع Mod برای انجام محاسبات روی سلول‌ها

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

جمع سلول‌هایی مشخص

برای جمع زدن یکی در میان مقادیر وجود در ردیف‌ها، تابع Mod را ا توابع ROW و SUMPRODUCT ترکیب می‌کنیم. برای جمع زدن ردیف‌های فرد از فرمول زیر استفاده می‌کنیم:

SUMPRODUCT((MOD(ROW(range),2)=0)*(range))

و برای جمع زدن ستون‌های زوج فرمول زیر مناسب است:

SUMPRODUCT((MOD(ROW(range),2)=1)*(range))

فرض کنید قصد داریم سلول‌های B2 تا B7 را جمع بزنیم. برای شماره‌های زوج، فرمول به شکل زیر نوشته می‌شود:

=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=0)*($B$2:$B$7))

و برای ردیف‌های فرد فرمول به شکل زیر تغییر می‌کند:

=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=1)*($B$2:$B$7))

تصویر زیر حاصل این فرمول‌ها را نشان می‌دهد:

در این فرمول‌ها توابع Mod و ROW در کنار هم، تعیین می‌کنند کدام ردیف‌ها با هم جمع زده شوند و در نهایت تابع SUMPRODUCT این جمع را انجام می‌دهد. به طور دقیق‌تر تابع ROW، آرایه‌ای را از شماره ردیف، به عنوان آرگومان اول یا مقسوم در تابع Mod تعریف می‌کند. تابع Mod شماره های موجود در این آرایه را بر دو تقسیم می‌کند. اگر شماره ردیف زوج باشد، حاصل تابع صفر می‌شود، چرا که اعداد زوج بر دو بخش‌پذیرند. در غیر این صورت نتیجه «۱» می‌شود، چرا که حاصل تقسیم اعداد فرد بر ۲، باقی‌مانده «۱» دارد. عبارات «=0 » و «=1» به این معناست که هر وقت حاصل تابع Mod مساوی صفر یا یک شد مقدار «True» یا یک را برگردان. در غیر این صورت مقدار «False» یا صفر برگردانده می‌شود.

می‌توانید به جای تابع SUMPRODUCT از ترکیب تابع SUM و IF نیز استفاده کنید. به این ترتیب از تابع IF برای سنجش شرط مدنظر و از تابع SUM برای جمع زدن استفاده می‌کنیم: این این فرمول آرایه‌ای خواهد بود لذا با زدن Ctrl + Shift + Enter  وارد می‌شود:

=SUM(IF(MOD(ROW($B$2:$B$7),2)=0,$B$2:$B$7,0))

عموما استفاده از تابع SUMPRODUCT بهتر است. استفاده از تابع ROW در یک فرمول آرایه‌ای، سرعت اکسل شما را پایین می‌آورد. چرا که با هربار ایجاد تغییر در فایل اکسل، فرمول دوباره محاسبه می‌شود.

جمع سلول‌ها با فاصله مشخص در ردیف‌ها

برای جمع زدن هر Nامین سلول (سومین، چهارمین و...) در ردیف، می‌توانید از فرمول کلی زیر استفاده کنید:

SUMPRODUCT((MOD(ROW(range)-ROW(first_cell)+1,n)=0)*(range))

برای مثال برای جمع زدن هر سومین سلول با جمع زدن سلول‌ها به صورت دو تا در میان، فرمول به شکل زیر جایگذاری می‌شود:

=SUMPRODUCT((MOD(ROW($C$2:$C$10)-ROW($C$2)+1,3)=0)*($C$2:$C$10))

در مقایسه با مثال قبل ین فرمول کمی پیچیده‌تر است و از منطق فیلتر کردن استفاده می‌کند.

MOD(ROW($C$2:$C$10)-ROW($C$2)+1,3)=0

ابتدا ردیف اول را از تمامی ردیف‌های دیگر در بازه Mod نظر کم می‌کند تا آرایه‌ای از شماره ردیف‌ها به طور نسبی پیدا کند {0;1;2;3;4;5;6;7;8} . سپس اعداد این آرایه را با یک جمع می‌زند تا از یک شروع شوند: {1;2;3;4;5;6;7;8;9} . آرایه‌ به دست آمده در آٰرگومان مقسوم تابع Mod قرار می‌گیرد، که پس از تقسیم شدن تک تک اعداد این آرایه بر ۳ آرایه مقابل به دست می‌آید: {1;2;0;1;2;0;1;2;0}

دقت کنید که عدد صفر در هر سومین ردیف به دست می‌آید و عبارت MOD=0  یعنی هر وقت حاصل تابع Mod برابر صفر شد مقدار TRUE را برگردان. با این کار، آرایه به صورت {0,0,1,0,0,1,0,0,1}  غیر می‌کند.

در نهایت تابع SUMPRODUCT این آرایه را در سلول‌های C2 تا C10 ضرب می‌کند و سپس اعداد حاصل را با هم جمع می‌کند. از جایی که حاصل ضرب در صفر، برابر صفر است،  اعداد «1» در آرایه، معادل هر سلول سوم، از این محاسبه بیرون می‌آیند.

جمع سلول‌ها با فاصله مشخص در ستون‌ها

برای جمع زدن هر Nامین ستون، تنها کافیست در فرمول‌های بخش قبل تابع ROW را با تابع COLUMN جایگزین کنیم

SUMPRODUCT((MOD(COLUMN(range)-COLUMN(first_cell)+1,n)=0)*(range))

برای مثال برای جمع زدن هر چهارمین ستون در بازه B2:I12 ، فرمول بالا به شکل زیر جایگذاری می‌شود:

=SUMPRODUCT((MOD(COLUMN($B2:$I2)-COLUMN($B2)+1,4)=0)*($B2:$I2))

یادگیری فرمول‌های کاربردی اکسل با فرادرس

اکسل توابع بسیاری دارد که با آشنایی و تسلط بر بخش کاربردی این توابع می‌توانید سطح استفاده خود را با اکسل بهبود ببخشید. فرادرس فیلم‌های آموزشی متنوعی برای آموزش استفاده از توابع اکسل و فرمول نویسی و ترکیب این توابع دارد و می‌تواند در مسیر آموزش تا تبدیل شدن به کاربر حرفه‌ای اکسل همراه شما باشد:

• فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل فرادرس

• فیلم آموزش مقدماتی برنامه نویسی وی بی ای در اکسل فرادرس

• فیلم آموزش ابزارهای کاربردی اکسل فرادرس

• فیلم آموزش ترفند‌های کاربردی اکسل فرادرس

برای مشاهده سایر فیلم‌های آموزشی فرمول نویسی در اکسل نیز به لینک زیر مراجعه کنید:

• مجموعه آموزش توابع و فرمول در اکسل فرادرس – مقدماتی تا پیشرفته

ادغام محتوای سلول‌ها با تابع Mod

اگر اطلاعات مربوط به یک مفهوم در سلول‌های مختلف پخش شده باشد، می‌توانیم با استفاده از تابع Mod در اکسل محتوای هر N سلول را ادغام کنیم. برای مثال جزئیات مدنظر ما در سه سلول مختلف در ستون B پخش شده است و قصد داریم محتوای این سه سلول را در یک سلول ادغام کنیم. برای این کار از فرمول زیر استفاده می‌کنیم:

=IF(MOD(ROW()-1, 3)=0, CONCATENATE(B2, "-", B3, "-", B4), "")

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

اگر مشابه بخش قبل از فرمول زیر استفاده کنید، دیگر نیازی به اصلاح منفی یک برای به دست آوردن شماره ردیف نسبی نیاز نخواهید داشت:

IF(MOD(ROW()-ROW($B$2)+1, 3)=0, CONCATENATE(), "")

در این صورت فارغ از اینکه جدول شما از کدام ردیف اکسل شروع شود پاسخ فرمول درست خواهد بود.

شمارش اعداد فرد یا زوج با تابع Mod

ساده‌ترین راه تشخیص یک عدد زوج تقسیم آن بر ۲ است. اگر باقیمانده‌ این تقسیم برابر با صفر شود این عدد زوج تشخیص داده خواهد شد و برای اعداد فرد نیز این باقیمانده باید برابر با یک شود. با استفاده از تابع Mod این باقیمانده‌ها را به‌راحتی می‌یابیم. سپس از تابع SUMPRODUCT برای شمارش سلول‌های فیلتر شده استفاده می‌کنیم. برای شمارش اعداد فرد فرمول زیر را در نظر بگیرید:

SUMPRODUCT((MOD(range,2)=1)*1)

برای شمارش اعداد زوج فرمول زیر را در نظر بگیرید:

SUMPRODUCT((MOD(range,2)=0)*1)

برای اعداد فردی که در سلول‌های A2 تا A8 قرار دارند، فرمول‌های بالا به شکل زیر جایگذاری می‌شوند:

=SUMPRODUCT((MOD(A2:A8,2)=1)*1)

و برای اعداد زوج فرمول به شکل زیر خواهد بود:

=SUMPRODUCT((MOD(A2:A8,2)=0)*1)

استفاده از تابع Mod برای برجسته کردن سلول‌ها

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

برجسته کردن اعداد زوج و فرد

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

=MOD(A2,2)=1

برای برجسته کردن اعداد زوج فرمول زیر مناسب خواهد بود:

=MOD(A2,2)=0

در این مثال A2 بتدایی‌ترین سلول شروع داده‌هاست.

برجسته کردن اعداد اعشاری

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

=MOD(A2,1)=0

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

=MOD(A2,1)>0

برجسته کردن مضرب‌های یک عدد

برای برجسته کردن مضرب‌های یک مقدار مشخص تنها کافیست آن مضارب را بر عدد مدنظر تقسیم کنیم. اگر حاصل این تقسیم برابر با صفر باشد مضرب به درستی پیدا شده است. برای این کار فرمول زیرا در نظر بگیرید که محتوای سلول A3 را تقسیم بر ۱۰ می‌کند:

=MOD(A3,10)=0

در این فرمول A3 اولین سلولی است که این قانون را در آن اعمال می‌کنیم. همچنین می‌توانید عدد مدنظر خود را در سلولی خالی وارد کنید. برای مثال سلول C1 را انتخاب می‌کنیم. سپس در فرمول، در آرگومان دوم به این سلول ارجاع می‌دهیم:

=MOD(A3, $C$1)=0

تابع Mod و اعتبارسنجی داده‌ها

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

برای ایجاد قانون اعتبارسنجی داده روی «DATA» و سپس «Data Validation» کلیک کنید در بخش «Settings» پنجره باز شده روی «Custom» بزنید و در جعبه «Allow» فرمول مدنظر خود را در بخش فرمول وارد کنید. برای جزئیات بیشتر از انجام این کار، توصیه می‌کنیم مطلب Data Validation در اکسل را مطالعه کنید که بیشتر در مجله فرادرس به آن پرداخته‌ایم.

مثلا فرض کنید قصد داریم تنها اعداد حسابی را در سلول‌های A2 تا A8 وارد کنیم. ابتدا این سلول‌ها را انتخاب کنید و فرمول زیر را در بخش فرمول پنجره Data Validation وارد کنید:

=MOD(A2,1)=0

به طرز مشابه می‌توانید ورودی داده را به اعداد زوج یا فرد محدود کنید برای مثال برای اعداد فرد فرمول زیر را وارد کنید:

=MOD(A2,2)=1

و برای اعداد زوج فرمول زیر مناسب خواهد بود:

=MOD(A2,2)=0

در این مثال سلول  A2 اولین سلول بازه انتخابی است.