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

فرمول کلی تابع MAXIFS در اکسل

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

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

که در آن هر یک از آرگومان‌ها به شکل زیر تعریف می‌شوند.

• آرگومان اجباری MAX_range : محدوده سلول جدول داده‌ها که می‌خواهیم بزرگ‌ترین عدد را در آن تعیین کنیم.
• آرگومان اجباری Criteria_range1 : محدوده از سلول جدول داده‌ها که با اولین شرط «Criteria1» برای تعیین بزرگ‌ترین عدد بررسی می‌شود.
• آرگومان اجباری Criteria1 : شرطی که برای اولین محدوده از سلول‌ها استفاده می‌کنیم. این شرط می‌تواند یک عدد یا عبارت متنی باشد.
• آرگومان اختیاری Criteria_range2 / criteria2 : سایر محدوده‌های سلولی و شرط‌های مربوط به آن‌ها که بر حسب نیاز تعریف می‌شوند. در این بخش می‌توانیم تا ۱۲۶ جفت محدوده سلول و شرط را تعریف کنیم.

البته این فرمول در نسخه‌ ۲۰۱۹ و نسخه ۲۰۲۱ اکسل و همچنین مایکروسافت اکسل ۳۶۵ در ویندوز و مک به‌ شکل تعریف شده وجود دارد و در صورت نیاز می‌توانیم آن‌ها را آموزش ببینیم. بنابراین در این نسخه‌ها نیاز به فرمول‌نویسی دستی نداریم. اما حتی در این موارد نیز برای جلوگیری از ایجاد پیام‌های خطای محاسباتی با این تابع نیاز به یادگیری اصول اولیه فرمول‌نویسی داریم. بنابراین پیش از ورود به بحث اصلی پیشنهاد می‌کنیم فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل در فرادرس را مشاهده کنید.

تعیین بزرگ‌ترین عدد با تابع MAXIFS همراه یک شرط

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

مثال

فرض‌ می‌کنیم جدولی شامل اسامی و اندازه قد دانش‌آموزان ورزشکار رشته‌های مختلف داریم. حال می‌خواهیم قدبلندترین بازیکن رشته فوتبال را در این جدول پیدا کنیم. همانند تصویر، اعداد مربوط به قد بازیکنان در محدوده سلول‌های D2 تا D11 و رشته ورزشی در سلول‌های B2 تا B11 قرار دارند. این دو محدوده به ترتیب نشان‌دهنده آرگومان max_range و criteria_range1 در فرمول تابع MAXIFS هستند. همچنین اولین شرط یا آرگومان Criteria1 نیز عبارت «Football» است. بنابراین فرمول‌ صحیح به شکل زیر درمی‌آید.

=MAXIFS(D2:D11, B2:B11, "football")

برای ساده‌تر کردن فرمول می‌توانیم کلمه «Football» را در یک سلول دلخواه، به عنوان مثال G1، بنویسیم و نام سلول را به‌جای کلمه درج کنیم. در این حالت فرمول نهایی به شکل زیر تغییر می‌کند.

=MAXIFS(D2:D11, B2:B11, G1)

تعیین بزرگ‌ترین عدد با تابع MAXIFS همراه چند شرط

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

مثال

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

• Max_range : محدوده سلول شامل مقادیر عددی مربوط به قد ورزشکاران یعنی D2:D11.
• Criteria_range1 : محدوده سلول شامل مقادیر عددی مربوط به رشته ورزشی، یعنی B2:B11.
• Criteria1 : اولین شرط مورد نظر یعنی عبارت «Basketball».
• Criteria_range2 : محدوده سلول شامل مقادیر عددی رده تحصیلی دانش‌آموزان، یعنی C2:C11.
• Criteria2 : دومین شرط مورد نظر یعنی عبارت «Junior».

بنابراین فرمول کلی به شکل زیر خواهد بود.

=MAXIFS(D2:D11, B2:B11, "basketball", C2:C11, "junior")

که بعد از ساده‌سازی آن و تعریف عبارت‌های «Basketball» و «Junior» در سلول‌های G1 و G2، فرمول به صورت نهایی زیر درمی‌آید.

=MAXIFS(D2:D11, B2:B11, G1, C2:C11, G2)

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

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

=MAXIFS($D$2:$D$11, $B$2:$B$11, G1, $C$2:$C$11, G2)

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

مطلب آدرس‌دهی نسبی و مطلق در مجله فرادرس راهنمای خوبی برای آشنایی بیشتر با این روش ارجاع سلول است.

یک روش میانبر برای تعیین بزرگ‌ترین عدد همراه چندین شرط در اکسل، استفاده از تابع INDEX و تابع MATCH به شکل ترکیبی است. در این حالت با نوشتن فرمول‌نویسی تودرتو، تابع MAXIFS به‌عنوان آرگومان اول و تابع MATCH به‌عنوان آرگومان جستجو کننده مقادیر عددی در محدوده سلول‌ها به‌عنوان آرگومان دوم استفاده می‌شود. بنابراین در مثال قبلی فرمول به شکل زیر تغییر می‌کند.

=INDEX($A$2:$A$11, MATCH(MAXIFS($D$2:$D$11, $B$2:$B$11, G1, $C$2:$C$11, G2), $D$2:$D$11, 0))

بعد از اجرای فرمول، نتیجه بلندقدترین بازیکن بسکتبال در جدول «Liam» است.

مطلب آدرس‌دهی نسبی و مطلق در مجله فرادرس راهنمای خوبی برای آشنایی بیشتر با این روش ارجاع سلول است.

استفاده از تابع MAXIFS همراه با عملگرهای منطقی

عملگرهای منطقی در نرم‌افزار اکسل پنج دسته هستند.

• بزرگ‌تر از با علامت «<»
• کوچک‌تر از با علامت «>»
• بزرگ‌تر یا مساوی با علامت «=<»
• کوچک‌تر یا مساوی با علامت «=>»
• نامساوی با علامت «<>»

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

1. عملگر منطقی همراه یک عدد یا متن را باید داخل علامت نقل قول،(” “)، استفاده کنیم. به‌عنوان مثال در فرمول، عبارت “>=14” را می‌نویسیم.
2. در صورت وجود یک سلول مرجع یا تابع در فرمول، ترکیبی از علامت نقل قول و علامت «&» را برای اتصال دو بخش استفاده می‌کنیم. به‌عنوان مثال عبارت  “>”&B1 یا <“&TODAY() را می‌نویسیم.

مثال

برای درک بهتر نحوه فرمول‌نویسی با عملگرهای منطقی، در جدول داده‌های مثال قبل ابتدا ستون سن افراد (ستون C) را اضافه می‌کنیم. حال اگر بخواهیم بلندقدترین دانش‌آموز ورزشکار پسر در محدوده سنی بین ۱۳ و ۱۴ سال را مشخص کنیم، دو شرط اول و دوم در فرمول، عبارت‌های “>=13” و “<=14” خواهند بود. همچنین، محدوده سلول مورد نظر برای جستجوی بزرگ‌ترین عدد برای هر دو شرط C2 تا C11 است. بنابراین فرمول به شکل زیر نوشته می‌شود.

=MAXIFS(D2:D11, C2:C11, ">=13", C2:C11, "<=14")

اگر بخواهیم فرمول را ساده‌تر کنیم، با تعریف سلول‌های دلخواه G1 و H1 برای اعداد ۱۳ و ۱۴ فرمول نهایی به شکل زیر تغییر می‌کند.

=MAXIFS(D2:D11, C2:C11, ">="&G1, C2:C11, "<="&H1)

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

=MAXIFS(D2:D11, B2:B11, "<>volleyball")

=MAXIFS(D2:D11, B2:B11, "<>"&G1)

چگونه فرمول‌نویسی حرفه‌ای اکسل را با فرادرس یاد بگیریم؟

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

همچنین در دو مجموعه فیلم آموزش زیر قابلیت انتخاب بیشتری برای یادگیری مفاهیم مورد نظر وجود دارد.

استفاده از تابع MAXIFS همراه با کاراکترهای وایلد کارت

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

مثال

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

=MAXIFS(D2:D11, B2:B11, "*ball")

در این فرمول با توجه به مشترک بودن عبارت «ball» آن را در شرط می‌آوریم و به‌جای بقیه بخش‌ها علامت ستاره را می‌نویسیم. با این کار، فرمول در جستجوی خود برای پیدا کردن بلندقدترین افراد، همه رشته‌های ورزشی که در انتهای آن‌ها این عبارت وجود دارد را بررسی می‌کند. البته برای ساده‌تر شدن فرمول هم می‌توانیم با نوشتن عبارت «ball» در یک سلول دلخواه مانند G1 آن را به شکل زیر تغییر دهیم.

=MAXIFS(D2:D11, B2:B11, "*"&G1)

در این مطلب نحوه استفاده از سایر کاراکترهای وایلد کارت را توضیح داده‌ایم.

تعیین بزرگ‌ترین عدد همراه شرط در بین داده‌ها با فرمت تاریخ

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

مثال

اگر ستون مربوط به سن دانش‌آموزان در جدول‌ داده‌های مثال قبل را با تاریخ تولد جایگزین کنیم، برای محاسبه بلندقدترین فرد متولد سال ۲۰۰۴ فقط کافی است تغییری کوچک در فرمول ایجاد کنیم. در این حالت افراد متولد از ابتدا تا انتهای سال را با تعریف شرط تاریخ تولد بزرگ‌تر یا مساوی اول ژانویه ۲۰۰۴ و کوچک‌تر یا مساوی با ۳۱ دسامبر ۲۰۰۴ تفکیک می‌کنیم. البته در این حالت بر خلاف فرمول‌نویسی شرطی با اعداد، لازم است تاریخ‌ها را داخل علامت نقل قول بنویسیم. بنابراین فرمول به دو صورت زیر تعریف می‌شوند.

=MAXIFS(D2:D11, C2:C11, ">=1-Jan-2004", C2:C11, "<=31-Dec-2004")

=MAXIFS(D2:D11, C2:C11, ">=1/1/2004", C2:C11, "<=12/31/2004")

البته برای رفع پیام خطاهای احتمالی و جلوگیری از تفسیر نادرست محاسباتی، بهتر است از تابع DATE استفاده کنیم.

=MAXIFS(D2:D11, C2:C11, ">="&DATE(2004,1,1), C2:C11, "<="&DATE(2004,12,31))

اگر بخواهیم فرمول ساده‌تر شود، با نوشتن عدد ۲۰۰۴ در سلول دلخواه G1، آن را در فرمول جایگزین سال تولد می‌کنیم.

=MAXIFS(D2:D11, C2:C11, ">="&DATE(G1,1,1), C2:C11, "<="&DATE(G1,12,31))

تعیین بزرگ‌ترین عدد با تابع MAXIFS همراه چند شرط و تابع OR

تابع MAXIFS در اکسل به‌ شکل خودکار محاسبات را با منطق AND انجام می‌دهد. یعنی بزرگ‌ترین عدد را با در نظر گرفتن درست بودن تمام شرایط در محدوده سلول تعیین می‌کند. اما در برخی موارد لازم است شرایط با منطق تابع OR ارزیابی شوند. یعنی درست بودن فقط یکی از شرط‌ها برای پیدا کردن بزرگ‌ترین عدد کافی است. بنابراین فرمول‌نویسی به شکل دیگری انجام می‌شود. برای آشنایی بیشتر با این نوع فرمول‌نویسی حرفه‌ای پیشنهاد می‌کنیم فیلم آموزش استفاده از توابع و فرمول در اکسل در فرادرس را مشاهده کنید.

مثال

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

=MAX(MAXIFS(C2:C11, B2:B11, "basketball"), MAXIFS(C2:C11, B2:B11, "football"))

نکات مهم در استفاده از تابع MAXIFS

در انتهای بحث چند نکته هنگام فرمول‌نویسی با تابع MAXIFS در اکسل را یادآوری می‌کنیم. رعایت موارد زیر از سردرگمی برای برطرف کردن پیام‌های خطای احتمالی در زمان اجرای فرمول جلوگیری می‌کند.

1. تابع MAXIFS برای تعیین بزرگ‌ترین عدد در محدوده مشخصی از جدول داده‌های اکسل همراه با تعریف یک یا چند شرط قابل استفاده است.
2. به‌طور پیش‌فرض این تابع با منطق AND کار می‌کند. یعنی بزرگ‌ترین عدد را فقط در صورت درست بودن همه شرط‌های تعریف شده باز می‌گرداند.
3. تعداد سطرها و ستون‌های تعریف شده در آرگومان‌های max_range و criteria_range در فرمول‌نویسی با تابع MAXIFS لازم است یکسان باشند. در غیر این صورت فرمول پیام خطای #VALUE! را برمی‌گرداند.
4. تابع MAXIFS به بزرگ‌ یا کوچک بودن حروف در عبارت‌های متنی حساس نیست.
5. هنگام کپی کردن فرمول تابع MAXIFS در سلول‌های دیگر، لازم است محدوده‌های تعریف شده را با آدرس‌دهی مطلق و علامت «$» قفل کنیم.
6. عبارات متنی و تاریخ‌ها در فرمول‌نویسی با این تابع را باید در داخل علامت نقل قول بنویسیم.
7. اگر یک عدد، تاریخ یا متن را همراه عملگرهای منطقی استفاده می‌کنیم، کل عبارت را در داخل علامت نقل قول می‌نویسم.
8. در صورت استفاده هم‌زمان از توابع و عملگرهای منطقی در فرمول تابع MAXIFS، فقط عملگر منطقی را در داخل علامت نقل قول می‌نویسیم.

جمع‌بندی

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