شروع جشنواره نوروزی و تخفیف های ویژه 1402 (جهت مشاهده تخفیف ها اینجا کلیک کنید)
سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

تابع MOD در اکسل یک تابع کاربردی

تابع MOD در اکسل
۴.۲/۵ - (۱۲ امتیاز)

کاربرد تابع MOD چیست؟

تابع MOD در اکسل باقیمانده تقسیم رو برمیگردونه. این تابع یکی از توابعی هست که در ترکیب با سایر توابع، خیلی مفید و کاربردی است و برای حل مسائل مختلف میشه از این تابع استفاده کرد. مثلا پیدا کردن اعدادی با مضرب خاص، انجام عملیات خاص روی n امین داده در یک محدوده و … . در این مقاله به تشریح اجزای این تابع و تشریح چند مثال کاربردی می پردازیم:

تابع دو آرگومان الزامی داره:

Number : عددی که میخوایم تقسیم کنیم (مقسوم)

Divisor: عددی که Number رو به اون تقسیم میکنیم (مقسوم علیه)

مثلا Mod (10,3) مقدار ۱ رو برمیگردونه چون باقیمانده تقسیم عدد ۱۰ به ۳ برابر است با ۱. به شکل ۱ دقت کنید. توابع موجود در اکسل برای محاسبه اجزای تقسیم و آرگومان ها رو می بینید. (تابع MOD در اکسل برای محاسبه باقیمانده تقسیم و تابع QUOTIENT برای خارج قسمت).

توابع مشخص کننده اجزای تقسیم

شکل ۱ – توابع مشخص کننده اجزای تقسیم

در شکل ۲ باقیمانده تقسیم عدد ۱۰ بر اعداد ۲ تا ۶ رو می بینید:

باقیمانده تقسیم عدد ده به مقسوم علیه های مختلف

شکل ۲- باقیمانده تقسیم عدد ده به مقسوم علیه های مختلف با استفاده از تابع MOD در اکسل

سه چیز که در مورد تابع MOD باید بدونیم:

  • نتیجه تابع MOD در اکسل با مقسوم علیه هم علامت هست.
  • اگر مقسوم علیه ۰ باشه، خروجی تابع، خطای #DIV/0! خواهد بود. چون عدد رو نمیتونیم به صفر تقسیم کنیم.
  • اگر مقسوم یا مقسوم علیه، متنی باشه خروجی تابع MOD خطای #VALUE! خواهد بود.

استفاده از تابع MOD در فرمول نویسی

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

استفاده از تابع MOD در اکسل برای محاسبه جمع هر N ردیف یا ستون

در اکسل تابعی برای محاسبه جمع دو به دو، سه به سه و … وجود نداره. بعبارت دیگه میخوایم اعدادی که در ردیف های ۲، ۴، ۶ و … هستن رو جمع بزنیم.برای این مار از تابع MOD استفاده میکنیم و فرمول مورد نظر رو می نویسیم:

جمع ردیف های زوج:

SUMPRODUCT((MOD(ROW(range),۲)=۰)*(range))

جمع ردیف های فرد:

SUMPRODUCT((MOD(ROW(range),۲)=۱)*(range))

فرض کنید فروشگاهی یک روز در میون، محصولات رو با درصدی تخفیف می فروشه، حالا میخوایم جمع فروش روزهای با تخفیف و بدون تخفیف رو محاسبه کنیم. (روزهای تخفیف در ردیف های زوج وارد شدن. پس داریم:

محاسبه جمع فروش در روزهای با تخفیف (ردیف های زوج)

شکل ۳-محاسبه جمع فروش در روزهای با تخفیف (ردیف های زوج)

در این نوع فرمول نویسی، ترکیب MOD ROW میاد تعیین میکنه که چه سلوله ایی انتخاب بشن. در واقع تابع Row یک آرایه از شماره ردیف محدوده مورد نظر رو میده. تابه MOD هم هر شماره ردیف رو تقسیم بر ۲ میکنه و باقیمانده رو نمایش میده. اگه باقیمانده ۰ باشه یعنی شماره ردیف زوج بوده، اگه باقیمانده ۱ باشه، شماره ردیف فرد میشه. بعد هم که در تابع Sumproduct خروجی صفر و یک در محدوده اعداد ضرب میشه و نتیجه جمع روی حاصلضرب اعداد در یک ها میشه.

نکته:
بجای تابع Sumproduct میتونیم از تابع IF هم بصورت آرایه ای استفاده کنیم.

 

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

 ولی من ترجیح میدم از همین Sumproduct استفاده کنم. چون تابع Row از دسته توابع Volatile هست و ووقتی این تابع رو در فرمول آرایه ای استفاده میکنیم، کل فرمول Volatile میشه و این به این معنی هست که با هر بار Calculation شیت، اون فرمول هم محاسبه میشه که این موضوع میتونه سرعت محاسبات رو به طرز قابل توجهی پایین بیاره. در مقاله کار با فایل های سنگین در اکسل راجع به فایل سنگین و توابع Volatile بیشتر بخونید.

محاسبه جمع فروش روزهای زوج و فرد (بدون استفاده از شماره ردیف)

شکل ۴- محاسبه جمع فروش روزهای زوج و فرد (بدون استفاده از شماره ردیف)

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

مقایسه این دو نوع فرمول نویسی خیلی کمک میکنه به درک کاربرد Row در این فرمول ها.

نکته:
برای تشخیص زوج و فرد از توابع ISEVEN و ISODD هم میشه استفاده کرد. اما استفاده از منطق MOD ROW برای مضربهای دیگه مثل ۳، ۴ و … نیز کاربرد داره که دیگه تنها معیار زوج و فرد بودن نیست.

 

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

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

=SUMPRODUCT((MOD(ROW(range)-ROW(first_cell)+۱,n)=۰)*(range))

مثال:

مثلا میخوایم داده هایی در هر سه ردیف قرار گرفتن (مجموع شمال، جنوب و غرب در ردیف های ۴، ۷ و ۱۰) رو جمع بزنیم. طبق الگوی بالا، داریم:

محاسبه جمع داده های موجود در هر سه ردیف

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

این فرمول نسبت به فرمول قبلی پیچیدگی بیشتری داره. به این قسمت از فرمول دقت کنید:

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

  • اول از همه کل شماره ردیف ها رو منهای ردیف اول (شروع داده ها) میکنیم که اعداد مرتبط رو بدست بیاریم چون ممکنه جدول وسط یک شیت باشه و از شماره ردیف ۱ شروع نشده باشه. وقتی این کار رو میکنیم آرایه مثل {۰;۱;۲;۳;۴;۵;۶;۷;۸} بدست میاد. برای اینکه اعداد از ۱ شروع بشه، این آرایه رو باضافه یک میکنیم و داریم {۱;۲;۳;۴;۵;۶;۷;۸;۹}.
  • حالا آرایه بدست آمده به عنوان Number در تابع MOD بر ۳ تقسیم میشه و خروجی آرایه مشابه {۱;۲;۰;۱;۲;۰;۱;۲;۰} خواهد بود. در واقع باقیمانده تقسیم اعداد موجود در آرایه مورد نظر بر ۳. عدد صفر نشون میده که شماره ردیف مورد نظر مضرب ۳ هست که باقیمانده تقسیم آن بر عدد ۳، صفر شده است.
  • آرایه بدست آمده ( که باقیمانده تقسیم اعداد به ۳ هست) با عدد صفر مقایسه میشه و هر جا صفر بود، مقدار true یا یک نشان داده میشه. در نتیجه با آرایه ای به صورت {۰,۰,۱,۰,۰,۱,۰,۰,۱} مواجه میشیم.

در نهایت تابع Sumproduct آرایه بدست آمده را در محدوده C2:C10 (نظیر به نظیر) ضرب میکنه.

=Sumproduct ( {۰,۰,۱,۰,۰,۱,۰,۰,۱} * {۱۰۰,۲۵۰,۳۵۰,۳۰۰,۵۰,۳۵۰,۱۵۰,۷۰,۲۲۰} )

سوال:

برای محاسبه جمع داده ها در هر N ستون چه تابعی پیشنهاد میدید؟

پاسخ رو بصورت کامنت در ادامه همین آموزش ثبت کنید تا بررسی بشه.

شمارش تعداد عدد مضرب K در یک محدوده

برای اینکه ببینیم در یک محدوده چه تعداد عدد از یک مضرب خاص وجود داره از الگوی زیر استفاده میکنیم:

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

بدیهی است که اگر K=2 نتیجه تعداد اعداد زوج رو حساب خواهد کرد. یا اگر K=1 تعداد اعداد فرد در یک محدوده به عنوان خروجی نماش داده خواهد شد.

شمارش تعداد اعداد مضرب K در یک محدوده عدد با استفاده از تابع MOD و SUMPRODUCT

شکل ۶- شمارش تعداد اعداد مضرب K در یک محدوده عدد

کاربرد تابع MOD در اکسل برای فرمت دهی داده ها

فرض کنید میخواهیم اعداد اعشاری و اعداد صحیح رو با فرمت از هم متمایز کنیم.

برای این کار باید از فرمت دهی شرطی یا conditional formatting استفاده کنیم. یک راه برای تعیین اعشاری و صحیح بودن عدد استفاده از تابع MOD هست.

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

عددی صحیح است که:

=Mod ( A1 , 1)=0

عددی اعشاری است که:

=Mod ( A1 , 1)>0

این دو فرمول رو داخل ابزار conditional formatting وارد میکنیم مطابق شکل ۷.

فرمت دهی اعداد اعشاری و صحیح با استفاده از تابع MOD و Conditional Formatting

شکل ۷- فرمت دهی اعداد اعشاری و صحیح

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

فرض کنید در محدوده ای در حال ثبت داده ای هستیم و نباید اجازه بدیم که عدد اعشاری وارد سلول ها بشه. برای این کار، مطابق مثال بالا، شرط عدد صحیح بودن رو در ابزار Data validation وارد میکنیم.

محدوده رو انتخاب کرده و از تب data/data validation/settings و در قسمت Custom فرمول صحیح بودن عدد رو می نویسیم. مطابق شکل ۸.

جلوگیری از ثبت داده اعشاری در اکسل با استفاده از تابع MOD و Data Validation

شکل ۸- جلوگیری از ثبت داده اعشاری با استفاده از تابع MOD در اکسل

همین مثال رو میتونیم راجع به اعداد زوج و فرد هم استفاده کنیم. یعنی از ورود داده زوج/فرد جلوگیری کنیم. برای این مسوله چه راه حل هایی پیشنهاد میدید؟

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

133

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • amiri ۱۳ فروردین ۱۴۰۰ / ۵:۰۸ ب٫ظ

    سلام وقتتون بخیر سال نوتون مبارک امیدوارم سالی پراز برکت و شادی داشته باشید
    خیلی ممنونم از زحماتی که می کشید
    من روز گذشته چهار بسته کاربردی از دوره های آموزشیتون رو خریداری کردم و در حین فیلم آموزشی داشبوردسازی به دوتا سوال برخوردم که ممنون میشم پاسخ بدید:
    ۱) خانم مهندس در خلال آموزش از یه قابلیتی استفاده می کردن که طی آن میشد با دابل کلیک بر روی فرمول پاسخ یه بخشی از فرمول را در داخل خود فرمول دید که جالب بود
    ۲) کپی کردن فرمول یک سلول در یک محدوده چند سلولی بدون کپی پیست کردن آن یعنی به صورت درگ کردن که اینم جالب بود
    سپاسگزارم

    • آواتار
      حسنا خاکزاد ۱۴ فروردین ۱۴۰۰ / ۱۱:۲۸ ق٫ظ

      درود بر شما
      ممنون و همچنین
      زحمت بکشید همین مطالب رو در قسمت تیکت ثبت بفرمایید که آرشیو سوالاتتون ذخیره بشه و قابل ردیابی باشه.
      ممنون از شما

      • amiri ۱۶ فروردین ۱۴۰۰ / ۹:۳۴ ب٫ظ

        با سلام مجدد
        چطور میشه به قسمت تیکت دسترسی داشته باشم
        ممنون

        • آواتار
          حسنا خاکزاد ۱۷ فروردین ۱۴۰۰ / ۱۲:۲۹ ب٫ظ

          درود
          در قسمت پروفایل کاربری
          تیکت پشتیبانی

  • Mehdipour ۹ آبان ۱۳۹۹ / ۹:۰۰ ق٫ظ

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

  • مهدی7 ۱۴ اسفند ۱۳۹۷ / ۱:۴۶ ب٫ظ

    با سلام با اکسل چه پروژه هایی ایجاد کرده اید با تشکر

ارسال دیدگاه

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

توسط
تومان