تابع IF در اکسل یکی از پرکاربردترین توابع منطقی است که به شما کمک می‌کند بر اساس شرایط مشخص، نتایج متفاوتی نمایش دهید. با استفاده از این تابع می‌توانید داده‌ها را بررسی و تعیین کنید که اگر یک شرط برقرار بود، چه نتیجه‌ای نمایش داده شود و اگر برقرار نبود، چه اتفاقی بیفتد. برای مثال، می‌توانید با استفاده از تابع IF نمره قبولی یا مردودی دانشجویان را مشخص، وضعیت پرداخت فاکتورها را بررسی یا اطلاعات یک جدول را بر اساس معیارهای دلخواه دسته‌بندی کنید. در این مطلب از آکادمی همراه اول، با نحوه استفاده از تابع IF در اکسل، ساختار فرمول، مثال‌های کاربردی و ترکیب آن با توابعی مانند AND و OR آشنا می‌شوید تا بتوانید محاسبات و تحلیل داده‌های خود را ساده‌تر و دقیق‌تر انجام دهید.
تابع IF در اکسل چیست؟ فرمول + مثال‌های کاربردی
فهرست مطالب
فهرست مطالب
تابع IF در اکسل چیست؟
تابع IF در اکسل برای بررسی شرط‌های منطقی و برگرداندن نتایج بر اساس آن‌ها استفاده می‌شود. این تابع یک مقدار را بررسی می‌کند و اگر شرط برقرار باشد، یک نتیجه و در صورت برقرار نبودن آن، نتیجه‌ای دیگر را نمایش می‌دهد. برای مثال می‌توانیم شرط کنیم اگر نمره دانش‌آموزان در جدول اکسل ۱۰ و بیشتر از آن بود، در سلول مقابل نمره کلمه قبول و اگر کمتر از ۱۰ بود کلمه مردود نوشته شود. تابع IF برای تحلیل داده‌ها، اعمال قوانین و ایجاد فرمول‌های پویا بسیار کاربردی است و می‌توان آن را با توابعی مانند AND (و)، OR (یا) و NOT (نقیض) ترکیب کرد.
وبینار معرفی مسیر شغلی مهندس داده
(کمتر از ۵ رای)
۴۶۴ نفر
۶۰ ساعت و ۲۵ دقیقه
رایگان
ساختار و آرگومان‌های تابع IF در اکسل
تابع IF یک شرط منطقی را بررسی می‌کند و بر اساس نتیجه آن، خروجی متفاوتی برمی‌گرداند. ساختار تابع IF در اکسل به شکل زیر است:
این پارامترها شامل موارد زیر می‌شود:
- condition (شرط): شرط منطقی‌ای که باید بررسی شود. برای مثال، آیا مقدار سلول A1 بزرگتر از ۱۰ است؟
- value_if_true (مقدار در صورت درست بودن شرط): نتیجه‌ای که در صورت برقرار بودن شرط نمایش داده می‌شود.
- value_if_false (مقدار در صورت نادرست بودن شرط): نتیجه‌ای که در صورت برقرار نبودن شرط نمایش داده می‌شود.
مثال تابع IF در اکسل
فرض کنید می‌خواهیم بر اساس نمره‌های دانش‌آموزان یک کلاس، مشخص کنیم که هر دانش‌آموز در آزمون قبول شده است یا مردود. می‌توانیم به‌جای بررسی نمره‌ی تک‌تک دانش‌آموزان به‌صورت دستی، شرط موردنظر را با استفاده از تابع IF برای یک سلول تعریف کنیم و سپس آن را برای سایر سلول‌ها نیز به کار ببریم.
مرحله اول: وارد کردن داده‌ها در اکسل
ابتدا نرم‌افزار Microsoft Excel را اجرا و اطلاعات موردنیاز به ثبت برسانید. مثلا نام دانش‌آموزان را در ستون A و نمره‌ی هر دانش‌آموز را در ستون B وارد کنید. هدف ما این است که با توجه به نمره‌ی هر دانش‌آموز، مشخص کنیم که آیا در آزمون قبول شده است یا مردود.
مرحله دوم: انتخاب سلول و وارد کردن فرمول IF
اکنون سلولی را انتخاب کنید که می‌خواهید نتیجه‌ی قبولی یا مردودی دانش‌آموز در آن نمایش داده شود. برای مثال، به سلول C2 بروید و فرمول زیر را در آن وارد کنید:
این فرمول بررسی می‌کند که آیا نمره‌ی ثبت‌شده در سلول B2 بزرگتر یا مساوی ۳۵ است یا خیر.
- اگر شرط برقرار باشد (نمره بزرگتر یا مساوی ۳۵ باشد)، عبارت Pass (قبول) نمایش داده می‌شود.
- اگر شرط برقرار نباشد (نمره کمتر از ۳۵ باشد)، عبارت Fail (مردود) نمایش داده خواهد شد.
مرحله سوم: اعمال فرمول در سایر سلول‌ها و مشاهده نتیجه
سلول C2 را انتخاب کنید و نشانگر ماوس را روی گوشه‌ی پایین سمت راست سلول قرار دهید. زمانی که نشانگر به شکل علامت + تغییر کرد، سلول را به سمت پایین بکشید تا فرمول به سایر سلول‌های ستون C نیز اعمال شود. به این ترتیب، اکسل به‌صورت خودکار نمره‌ی هر دانش‌آموز را بررسی می‌کند و با توجه به شرط تعیین‌شده، وضعیت قبولی یا مردودی او را نمایش می‌دهد.
استفاده از عملگرهای مقایسه در تابع IF
آرگومان “logical_test” در تابع IF شامل یک عبارت منطقی است که نتیجه آن می‌تواند TRUE (درست) یا FALSE (نادرست) باشد. جدول زیر چند نمونه از عملگرهای مقایسه‌ای پرکاربرد در اکسل را نشان می‌دهد.
| عملگر | مفهوم |
| A1>75 | بررسی اینکه آیا مقدار سلول A1 بزرگتر از ۷۵ است. |
| A1=100 | بررسی اینکه آیا مقدار سلول A1 برابر با ۱۰۰ است. |
| A1<=100 | بررسی اینکه آیا مقدار سلول A1 کوچک‌تر یا مساوی ۱۰۰ است. |
| “A1=”red | بررسی اینکه آیا مقدار سلول A1 برابر با «Red» است. |
| “A1<>”red | بررسی اینکه آیا مقدار سلول A1 با «Red» برابر نیست. |
| A1<B1 | بررسی اینکه آیا مقدار سلول A1 کوچک‌تر از مقدار سلول B1 است. |
| “”=A1 | برای بررسی خالی بودن سلول به کار می‌رود. |
| “”<>A1 | برای بررسی خالی نبودن سلول مورد استفاده می‌گیرد. |
| ()A1<TODAY | بررسی اینکه آیا مقدار سلول A1 از تاریخ امروز قدیمی‌تر است. |
مثال زیر نمونه‌ای از بکارگیری آرگومان بزرگتر در اکسل است.
فرض کنید می‌خواهید با تابع IF بررسی کنید که آیا مقدار ستون مربوط به مجموع آمار فروش فروشندگان بیشتر از ۵۰۰ است یا خیر. اگر شرط برقرار باشد، عبارت «بله» و در غیر این صورت، عبارت «خیر» در ستون‌های جدول نمایش داده می‌شود. برای این کار مراحل زیر را دنبال کنید:
۱. سلول C2 را انتخاب کنید.
۲. عبارت “IF=” را تایپ کنید.
۳. در فهرست پیشنهادها، روی تابع IF دوبار کلیک کنید.
۴. شرط را به‌صورت B2>500 وارد کنید.
۵. یک ویرگول “,” یا “;” تایپ کنید.
۶. مقداری را که در صورت درست بودن شرط باید نمایش داده شود، یعنی “بله”، وارد کنید.
۷. دوباره یک ویرگول “,” یا “;” تایپ کنید.
۸. مقداری را که در صورت نادرست بودن شرط باید نمایش داده شود، یعنی “خیر”، وارد کنید.
۹. کلید Enter را فشار دهید. فرمول نهایی باید به شکل زیر باشد:
از آنجا که مقدار سلول B2 برابر با ۵۰۰ است، شرط بزرگتر بودن از ۵۰۰ برقرار نیست. بنابراین، تابع عبارت «خیر» را نمایش می‌دهد.
برای بررسی مجموع آمار سایر فروشندگان نیز می‌توانید فرمول را با استفاده از علامت (+) در گوشه سلول اول به ردیف‌های دیگر کپی کنید.
به این ترتیب، هر ردیف مشخص می‌کند که آیا مجموع آمار فروش مربوطه بیشتر از ۵۰۰ است یا خیر.
تابع IF تو در تو در اکسل
از تابع IF تو در تو در اکسل زمانی استفاده می‌کنیم که بخواهیم چند شرط مختلف را بررسی کنیم و بر اساس نتیجه‌ی هر شرط، خروجی متفاوتی نمایش دهیم. به زبان ساده، تابع IF تو در تو از ترکیب چند تابع IF در یک فرمول تشکیل شده است و به ما امکان می‌دهد بیش از یک شرط را بررسی کنیم و برای هر حالت، نتیجه‌ی متناسب با آن را نمایش دهیم. ساختار این تابع به شکل زیر است:
- condition1: شرط اول که باید بررسی شود.
- Value if true1: مقداری که در صورت درست بودن شرط اول نمایش داده می‌شود.
- Condition 2: شرط دوم که در صورت نادرست بودن شرط اول بررسی می‌شود.
- Value if true2: مقداری که در صورت درست بودن شرط دوم نمایش داده می‌شود.
- Value if false2: مقداری که در صورت نادرست بودن هر دو شرط نمایش داده می‌شود.
در ادامه مثالی از کاربرد این تابع و نحوه استفاده از آن را بررسی می‌کنیم.
مرحله اول: وارد کردن داده‌ها
فرض کنید جدولی از دانش‌آموزان و نمره‌های آن‌ها در اختیار داریم و می‌خواهیم بر اساس نمره‌ی هر دانش‌آموز، رتبه‌ی قبولی او را مشخص کنیم.
مرحله دوم: وارد کردن فرمول IF تو در تو
فرمول زیر را در سلول موردنظر وارد کنید:
این فرمول نمره‌ی دانش‌آموز را در سلول B2 بررسی می‌کند و بر اساس مقدار آن، یکی از نتایج زیر را نمایش می‌دهد:
- نمره ۶۰ یا بیشتر: رتبه‌ی اول
- نمره ۳۰ تا کمتر از ۶۰: رتبه‌ی دوم
- نمره ۱۰ تا کمتر از ۳۰: رتبه‌ی سوم
- نمره کمتر از ۱۰: مردود
به این ترتیب، اکسل با بررسی شرط‌ها به‌ترتیب، رتبه‌ی مناسب هر دانش‌آموز را به‌صورت خودکار تعیین می‌کند.
پیام اسفندیاری
وبینار معرفی مسیر شغلی تحلیل‌گر داده
(۴۵ رای)
۱۶۷۹ نفر
۷۴ ساعت و ۴ دقیقه
رایگان
ترکیب تابع IF با AND و OR
تابع IF در اکسل، به ما امکان می‌دهد بر اساس شرایط مشخص، نتایج متفاوتی را نمایش دهیم. با این حال، گاهی لازم است چند شرط را به‌طور هم‌زمان بررسی یا نتیجه‌ی یک شرط منطقی را معکوس کنیم. در چنین مواقعی، می‌توانیم از ترکیب تابع IF با توابع AND، OR و NOT کمک بگیریم.
تابع AND زمانی استفاده می‌شود که بخواهیم برقرار بودن تمام شرط‌ها را بررسی کنیم. تابع OR زمانی کاربرد دارد که برقرار بودن حداقل یکی از شرط‌ها کافی باشد. همچنین، تابع NOT نتیجه‌ی یک شرط منطقی را معکوس می‌کند. ترکیب این توابع با IF به ما کمک می‌کند فرمول‌های دقیق‌تر و کاربردی‌تری بسازیم، داده‌ها را بر اساس معیارهای مختلف بررسی کنیم و بسیاری از محاسبات و تصمیم‌گیری‌ها را به‌صورت خودکار انجام دهیم. در ادامه این توابع را توضیح می‌دهیم.
ترکیب تابع IF همراه AND
ترکیب توابع IF و AND در اکسل به ما امکان می‌دهد چند شرط را به‌طور هم‌زمان بررسی کنیم. اگر تمام شرط‌های مشخص‌شده برقرار باشند، یک نتیجه نمایش داده می‌شود. اما اگر حتی یکی از شرط‌ها برقرار نباشد، نتیجه‌ی دیگری نمایش داده خواهد شد. ساختار این فرمول به شکل زیر است:
بخش‌های این تابع به شکل زیر معنی می‌شود:
- AND(condition 1, condition 2): بررسی می‌کند که آیا تمام شرط‌های مشخص‌شده برقرار هستند یا خیر.
- Value if true: مقداری که در صورت برقرار بودن تمام شرط‌ها نمایش داده می‌شود.
- Value if false: مقداری که در صورت برقرار نبودن حداقل یکی از شرط‌ها نمایش داده می‌شود.
مثال زیر نحوه استفاده از این تابع را نشان می‌دهد.
مرحله اول: وارد کردن داده‌ها در اکسل
ابتدا داده‌های موردنظر را در کاربرگ اکسل وارد کنید. برای این مثال، فرض کنید ستون A شامل نمره‌های درس اول و ستون B شامل نمره‌های درس دوم است.
مرحله دوم: وارد کردن فرمول IF همراه با AND
در مرحله بعد، به سلول C2 بروید و فرمول زیر را وارد کنید تا بررسی شود که آیا نمره‌های هر دو درس بیشتر از ۹۰ هستند یا خیر.
این فرمول بررسی می‌کند که آیا مقدار سلول‌های A2 و B2 هر دو بیشتر از ۹۰ هستند یا خیر.
- اگر هر دو شرط برقرار باشند، عبارت «هست» نمایش داده می‌شود.
- اگر حتی یکی از شرط‌ها برقرار نباشد، عبارت «نیست» را مشاهده خواهید کرد.
مرحله سوم: کپی کردن فرمول در سایر سلول‌ها و مشاهده نتایج
فرمول واردشده در سلول C2 را با استفاده از علامت (+) در گوشه سلول، فرمول به سمت پایین بکشید تا در سایر سلول‌های ستون C نیز اعمال شود.به این ترتیب، اکسل به‌طور خودکار نمره‌های هر دو درس را بررسی می‌کند و بر اساس شرایط تعیین‌شده، نتیجه‌ی مربوط به هر ردیف را نمایش می‌دهد.
مراحل استفاده از تابع IF همراه با OR در اکسل
تابع OR چند شرط منطقی را بررسی می‌کند و اگر حداقل یکی از آن‌ها درست باشد، مقدار TRUE (درست) را برمی‌گرداند. این تابع را می‌توان برای مقایسه‌ی مقادیر عددی و متنی به کار برد. برای مثال، بررسی اینکه آیا مقدار سلول A1 بزرگتر از ۹۰ است یا اینکه مقدار آن برابر با «Yes» است.
در این حالت ساختار فرمول به شکل زیر است:
فرض کنید می‌خواهیم بررسی کنیم که آیا نمره‌ی دانش‌آموز در درس اول یا درس دوم بیشتر از ۹۰ است یا خیر. برای این کار می‌توانیم از تابع OR استفاده کنیم.
اگر نمره‌ی دانش‌آموز در حداقل یکی از این دو درس بیشتر از ۹۰ باشد، فرمول عبارت Pass (قبول) را نمایش می‌دهد؛ در غیر این صورت، عبارت Fail (مردود) نمایش داده می‌شود.
مرحله اول: وارد کردن داده‌ها
ابتدا داده‌های موردنظر را در کاربرگ اکسل وارد کنید. برای مثال: فرض کنید ستون A شامل نمره‌های درس اول و ستون B شامل نمره‌های درس دوم است.
مرحله دوم: وارد کردن فرمول IF همراه با OR
اکنون در سلول C2 فرمول زیر را وارد کنید تا بررسی شود که آیا نمره‌ی دانش‌آموز در هر یک از دو درس بیشتر از ۹۰ است یا خیر.
این فرمول بررسی می‌کند که آیا نمره‌ی ثبت‌شده در سلول A2 یا B2 بیشتر از ۹۰ است یا خیر. اگر حداقل یکی از دو شرط برقرار باشد، عبارت «هست» و اگر هیچ‌کدام از شرط‌ها برقرار نباشد، عبارت «نیست» نمایش داده می‌شود.
مرحله سوم: کپی کردن فرمول در ردیف‌های دیگر
فرمول واردشده را با استفاده از علامت + گوشه سلول به سمت پایین بکشید تا در سایر ردیف‌ها نیز اعمال شود.
به این نکته توجه کنید که در اکسل، ترکیب توابع IF و OR برای بررسی مقادیر عددی کاربرد زیادی دارد. هنگام استفاده از این فرمول، توجه داشته باشید که مقادیر متنی یا ورودی‌های غیرعددی ممکن است در برخی شرایط باعث شوند نتیجه‌ی بررسی شرط منطقی غیرمنتظره باشد. بنابراین، برای دستیابی به نتایج دقیق، مطمئن شوید که سلول‌های موردنظر فقط شامل مقادیر عددی هستند.
مراحل استفاده از تابع IF همراه با NOT در اکسل
ترکیب توابع IF و NOT برای معکوس کردن یک شرط منطقی پیش از بررسی نتیجه‌ی آن استفاده می‌شود. اگر شرط داخل تابع NOT نادرست (FALSE) باشد، این تابع نتیجه‌ی درست (TRUE) را برمی‌گرداند و تابع IF مقدار مربوط به درست بودن شرط را نمایش می‌دهد. در غیر این صورت، تابع IF مقدار مربوط به نادرست بودن شرط را برمی‌گرداند. ساختار فرمول در این حالت به شکل زیر است:
فرض کنید می‌خواهیم بررسی کنیم که آیا رنگ یک توپ قرمز نیست. برای این کار مراحل زیر را دنبال می‌کنیم:
مرحله اول: وارد کردن داده‌ها در اکسل
ابتدا داده‌های موردنظر را در کاربرگ اکسل وارد کنید.برای مثال: فرض کنید ستون A شامل رنگ توپ‌ها است.
مرحله دوم: وارد کردن فرمول IF همراه با NOT
در سلول B1، فرمول IF را همراه با تابع NOT وارد کنید تا بررسی شود که آیا رنگ توپ قرمز نیست یا خیر. اگر رنگ توپ قرمز نباشد، عبارت «انتخاب شد» و اگر قرمز باشد، عبارت «رد شد» نمایش داده می‌شود.
این فرمول بررسی می‌کند که آیا مقدار سلول A1 با عبارت «قرمز» متفاوت است یا خیر.
مرحله سوم: اعمال فرمول در ردیف‌های دیگر و مشاهده نتایج
فرمول واردشده را با استفاده از علامت + گوشه سلول به سمت پایین بکشید تا در سایر ردیف‌ها نیز اعمال شود. سپس نتایج را بررسی کنید.
به این ترتیب، ترکیب توابع IF و NOT به ما امکان می‌دهد شرط مشخصی را بررسی کنیم و نتیجه را بر اساس معکوس کردن آن شرط تعیین کنیم. در این مثال، هدف این است که مشخص کنیم آیا رنگ توپ قرمز نیست یا خیر.
مهدی ناصری
داستان سرایی داده
(کمتر از ۵ رای)
۱۲۶ نفر
۳۰۵ ساعت و ۱۳ دقیقه
ترکیب تابع IF با AND و OR
برای پیاده‌سازی منطق‌های پیچیده‌تر، می‌توانید توابع “AND” و “OR” را درون تابع “IF” ترکیب کنید.
مثلا اگر هدف این باشد که افرادی که در رتبه A یا B دارند و نمره آن‌ها حداقل ۸۰ است، با حرف X مشخص شوند، فرمول زیر در سلول F5 وارد و سپس با کپی کردن آن به سلول‌های پایین‌تر، وضعیت سایر افراد نیز بررسی می‌شود:
این فرمول بررسی می‌کند که آیا فرد در گروه A یا B قرار دارد و نمره او حداقل ۸۰ است یا خیر. اگر هر دو شرط برقرار باشند، علامت “x” در سلول نمایش داده می‌شود و در غیر این صورت، سلول خالی می‌ماند.
ترکیب تابع IF با توابع دیگر اکسل
در بخش قبلی ترکیب تابع IF با AND، OR و NOT را توضیح دادیم. اما این تابع را می‌توان بر اساس نیاز با توابع دیگر در اکسل ترکیب کرد. در این بخش، با نحوه ترکیب تابع IF با توابع دیگر در اکسل آشنا می‌شویم. ترکیب این تابع با توابعی مانند VLOOKUP، SUM و COUNT به ما کمک می‌کند فرمول‌های پیشرفته‌تر و کاربردی‌تری داشته باشیم.
ترکیب IF با VLOOKUP
برای ایجاد یک تابع جست‌وجو شرطی، ابتدا از تابع IF() استفاده کنید و تابع VLOOKUP() را در آن قرار دهید تا بر اساس یک شرط، نتایج متفاوتی برگردانده شود. برای مثال، می‌توان با استفاده از این ترکیب بررسی کرد که آیا یک محصول، با توجه به تعداد موجودی آن، در انبار موجود است یا خیر. برای انجام این کار، مراحل زیر را دنبال کنید:
- مرحله اول: ابتدا تابع IF() را بنویسید: =IF()
- مرحله دوم: تابع VLOOKUP() را در تابع IF() قرار دهید تا جست‌وجو بر اساس شرط موردنظر انجام شود.
فرض کنید فهرستی از سفارش‌های محصولات و زمان ثبت هر سفارش در اختیار داریم. می‌خواهیم بررسی کنیم که آیا یک محصول مشخص، قبل از ساعت ۱۲ ظهر سفارش داده شده است یا خیر.
در گام اول یک ستون را انتخاب کرده و فرمول زیر را در آن وارد کنید:
در این فرمول، تابع “VLOOKUP()” محصول موردنظر، یعنی موز، را در ستون A جست‌وجو می‌کند و زمان ثبت سفارش مربوط به آن را از ستون C برمی‌گرداند.
سپس تابع “IF()” بررسی می‌کند که آیا زمان ثبت سفارش، قبل از ساعت ۱۲ ظهر است یا خیر.
اگر زمان سفارش قبل از ساعت ۱۲ باشد، عبارت «قبل از ظهر» نمایش داده می‌شود. در غیر این صورت، عبارت «بعد از ظهر» نمایش داده خواهد شد.
همان‌طور که مشاهده می‌کنید، با ترکیب توابع “VLOOKUP()” و “IF()” می‌توان زمان تحویل یا ثبت سفارش محصولات موردنظر را به‌راحتی بررسی و مدیریت کرد.
تابع COUNTIF در اکسل
تابع COUNTIF تابعی داخلی در اکسل و ترکیبی از تابع IF و COUNT است. تابع “COUNTIF” تعداد سلول‌هایی را در یک محدوده مشخص می‌شمارد که شرط خاصی را برآورده کنند. این تابع برای شمارش شرطی داده‌ها بر اساس تاریخ، متن یا اعداد استفاده می‌شود.
با وجود کاربردهای فراوان، این تابع یک شرط را بررسی می‌کند و ساختار آن به شکل زیر است:
- “range” (محدوده): محدوده سلول‌هایی که می‌خواهید بررسی شوند.
- “criteria” (شرط): شرطی که سلول‌ها باید داشته باشند تا در شمارش لحاظ شوند.
برای مثال در جدول زیر، مجموعه‌ای از اطلاعات مربوط به فروش را در اختیار داریم.
با استفاده از تابع “COUNTIF” و نوشتن سه فرمول مختلف، می‌توانیم محاسبات زیر را انجام دهیم:
- شمارش تمام فروش‌هایی که مبلغ آن‌ها بیشتر از ۱۰۰ دلار است.
- شمارش تمام فروش‌هایی که توسط شخصی به نام «صابر» انجام شده‌اند.
- شمارش تمام فروش‌هایی که در اصفهان ثبت شده‌اند.
فرمول‌های مورد استفاده در سلول‌های “G4” تا “G6” به شکل زیرند:
این فرمول تعداد سلول‌های محدوده “D5:D16” را که مقدار آن‌ها بیشتر از ۱۰۰ است، محاسبه می‌کند.
این فرمول تعداد دفعاتی را می‌شمارد که نام صابر در محدوده “A2:A8” ثبت شده است.
این فرمول تعداد سلول‌های محدوده “B2:B8” را که مقدار آن‌ها برابر با “اصفهان” است، محاسبه می‌کند.
تابع SUMIF در اکسل
تابع “SUMIF” برای جمع‌کردن مقادیری در اکسل استفاده می‌شود که شرط مشخصی را برآورده کنند. این تابع برای محاسبه مجموع داده‌ها بر اساس شرط‌هایی مانند تاریخ، متن یا عدد به کار می‌رود.
نکته مهم این است که تابع “SUMIF” فقط می‌تواند یک شرط را بررسی کند. اگر بخواهید مجموع مقادیر را بر اساس چند شرط محاسبه کنید، باید از تابع “SUMIFS” کمک بگیرید.
ساختار کلی تابع SUMIF به شکل زیر است:
- “range” (محدوده): محدوده سلول‌هایی که شرط موردنظر در آن‌ها بررسی می‌شود.
- “criteria” (شرط): شرطی که مشخص می‌کند کدام سلول‌ها باید در محاسبه لحاظ شوند.
- “sum_range” (محدوده جمع): محدوده‌ای که مقادیر آن با توجه به شرط تعیین‌شده جمع می‌شوند. واردکردن این آرگومان اختیاری است.
تابع “SUMIF” ابتدا شرط تعیین‌شده را روی سلول‌های محدوده “range” بررسی می‌کند. سپس مقادیر متناظر با سلول‌هایی را که شرط را برآورده می‌کنند، در محدوده “sum_range” با یکدیگر جمع می‌کند.
فرض کنید مجموعه‌ای از اطلاعات مربوط به فروش در اختیار داریم و می‌خواهیم با استفاده از تابع “SUMIF”، سه محاسبه مختلف انجام دهیم:
- محاسبه مجموع فروش‌هایی که توسط شخصی به نام صابر ثبت شده است.
- محاسبه مجموع فروش‌هایی که در اصفهان ثبت شده‌اند.
- محاسبه مجموع فروش‌هایی که مبلغ آن‌ها بیشتر از ۱۰۰ دلار است.
فرمول‌های مورد استفاده در سلول‌های “D4” تا “D6” به شکل زیر هستند:
این فرمول نام صابر را در محدوده “A2:A8” جست‌وجو و سپس مجموع مبالغ فروش متناظر با این نام را از محدوده “C2:C8” محاسبه می‌کند.
این فرمول سلول‌های محدوده “B2:B8” را بررسی می‌کند و هر جا مقدار “اصفهان” وجود داشته باشد، مبلغ فروش متناظر با آن را از محدوده “C2:C8” در محاسبه مجموع لحاظ می‌کند.
این فرمول تمام مقادیر موجود در محدوده “C2:C8” را بررسی می‌کند و مجموع مقادیری را محاسبه می‌کند که بیشتر از ۱۰۰ هستند.
در این مثال، آرگومان “sum_range” وارد نشده است. بنابراین، اکسل مقادیر همان محدوده “C2:C8” را که شرط را برآورده می‌کنند، جمع می‌کند.
تابع AVERAGEIF در اکسل
تابع AVERAGEIF میانگین مقادیری را محاسبه می‌کند که شرط تعیین‌شده را برآورده کنند. این تابع سلول‌های خالی را در محاسبه میانگین لحاظ نمی‌کند. حتی اگر شرط تعیین‌شده را برآورده کنند. به علاوه، این تابع در تمام نسخه‌های اکسل قابل استفاده است.
ساختار کلی این تابع به شکل زیر است:
- range” (محدوده): محدوده سلول‌هایی که شرط موردنظر در آن‌ها بررسی می‌شود.
- “criteria” (شرط): شرطی که مشخص می‌کند کدام سلول‌ها باید در محاسبه میانگین لحاظ شوند.
- “average_range” (محدوده محاسبه میانگین): محدوده‌ای که میانگین مقادیر آن محاسبه می‌شود. واردکردن این آرگومان اختیاری است. اگر آن را مشخص نکنید، اکسل میانگین مقادیر موجود در محدوده “range” را محاسبه می‌کند.
فرض کنید جدولی از اطلاعات مربوط به خانه‌ها در اختیار داریم که شامل قیمت و تعداد اتاق‌خواب هر خانه است.
فرمول‌های واردشده در سلول‌های “H5” تا “H8” به شکل زیر هستند:
این فرمول میانگین قیمت خانه‌هایی را محاسبه می‌کند که مقدار قیمت آن‌ها در محدوده “C5:C15” بیشتر از صفر باشد.
این فرمول فقط قیمت خانه‌هایی را در محاسبه میانگین لحاظ می‌کند که بیشتر از ۲۰۰ باشند.
در این فرمول، اکسل ابتدا محدوده “D5:D15” را بررسی می‌کند و خانه‌هایی را که حداقل ۲ اتاق‌خواب دارند، مشخص می‌کند. سپس میانگین قیمت این خانه‌ها را از محدوده “C5:C15” محاسبه می‌کند.
این فرمول میانگین قیمت خانه‌هایی را محاسبه می‌کند که تعداد اتاق‌خواب آن‌ها ۳ یا بیشتر باشد.
خطاهای رایج در استفاده از تابع IF
هنگام کار با تابع IF، برخی اشتباهات ممکن است باعث ایجاد خطا یا نمایش نتیجه‌ی نادرست شوند. در ادامه، مهم‌ترین آن‌ها را بررسی می‌کنیم:
اشتباه اول: قرار دادن عدد داخل علامت نقل‌قول
فرمول زیر را در نظر بگیرید:
در این فرمول، عدد ۵۰ داخل علامت نقل‌قول قرار گرفته است. بنابراین، اکسل آن را به‌عنوان متن و نه یک مقدار عددی، در نظر می‌گیرد. از آنجا که اکسل در مقایسه‌های معمول، متن را بالاتر از اعداد رتبه‌بندی می‌کند، این شرط برای مقادیر عددی B2 می‌تواند نتیجه‌ی نادرستی ایجاد کند و همواره عبارت Low را برگرداند. روش صحیح نوشتن فرمول در ادامه اماده است:
اعداد را بدون علامت نقل‌قول وارد کنید. اما متن‌ها باید داخل علامت نقل‌قول دوتایی قرار بگیرند.
اشتباه دوم: استفاده از == به‌جای =در اکسل
برای بررسی برابری دو مقدار از یک علامت مساوی (=) استفاده می‌شود، نه دو علامت مساوی (==).فرمول زیر نادرست است:
این فرمول باعث می‌شود اکسل پیام خطای مربوط به ساختار فرمول را نمایش دهد. روش صحیح نوشتن این فرمول در ادامه آمده است:
اشتباه سوم: ذخیره شدن اعداد به‌صورت متن
گاهی اوقات اعداد به‌دلیل وارد کردن اطلاعات از منابع خارجی یا فایل‌های دیگر، به‌صورت متن ذخیره می‌شوند.برای مثال، ممکن است سلول B2 حاوی متن “۱۲ باشد؛ یعنی عدد ۱۲ به‌صورت متن ذخیره شده باشد.در چنین شرایطی، فرمول زیر ممکن است نتیجه‌ی غیرمنتظره‌ای ایجاد کند:
به‌صورت متن ذخیره شده باشد، اکسل ممکن است آن را در مقایسه با عدد ۵۰ بزرگتر در نظر بگیرد؛ در نتیجه، عدد ۱۲ به‌اشتباه قبول تشخیص داده شود. برای حل این مشکل، ابتدا مقادیر متنی را به اعداد واقعی تبدیل و سپس از فرمول‌نویسی در اکسل استفاده و فرمول را اجرا کنید.
احمد حاجی تراب
مبانی مهندسی داده
(۱۰۷ رای)
۶۰۴ نفر
۲۳۷ ساعت و ۲۰ دقیقه
اشتباه چهارم: استفاده از متن TRUE و FALSE به‌جای مقادیر منطقی
فرمول زیر را در نظر بگیرید:
این فرمول کلمات TRUE و FALSE را به‌صورت متن برمی‌گرداند. بنابراین، خروجی آن‌ها مقادیر منطقی واقعی نیستند و ممکن است هنگام استفاده در فرمول‌های دیگر، نتیجه‌ی متفاوتی بدهند. اگر می‌خواهید صرفاً بررسی کنید که آیا مقدار B2 حداقل ۵۰ است یا خیر، نیازی به استفاده از تابع IF ندارید.
کافی است بنویسید:
این فرمول مستقیماً یکی از دو مقدار منطقی TRUE یا FALSE را برمی‌گرداند.
اشتباه پنجم: فراموش کردن پرانتز پایانی
هر بار که تابع IF را باز می‌کنید، باید پرانتز مربوط به آن را نیز ببندید.در فرمول‌های تو در تو، هر تابع IF یک پرانتز باز دارد و باید پرانتز بسته‌ی متناظر خود را داشته باشد.اگر یکی از پرانتزهای پایانی را فراموش کنید، اکسل ممکن است خطای فرمول نمایش دهد. برای جلوگیری از این مشکل، هنگام نوشتن فرمول‌های طولانی، تعداد پرانتزهای باز و بسته را بررسی کنید. اکسل معمولاً جفت پرانتزها را با رنگ‌های متفاوت مشخص می‌کند تا تشخیص آن‌ها آسان‌تر شود.
مسیر یادگیری علم داده با آکادمی همراه اول
اگر به دنیای داده‌ها، تحلیل اطلاعات و هوش مصنوعی علاقه‌مند هستید، یادگیری علم داده می‌تواند نقطه شروعی برای ورود به یکی از حوزه‌های پرتقاضای بازار کار باشد. در آکادمی همراه اول، می‌توانید با طی کردن یک مسیر آموزشی هدفمند، از مفاهیم پایه تحلیل داده تا مهارت‌های کاربردی موردنیاز این حوزه پیش بروید و دانش خود را برای ورود به بازار کار توسعه دهید. با انتخاب مسیر یادگیری مناسب، تمرین مستمر و کار روی پروژه‌های عملی، می‌توانید قدم‌به‌قدم مهارت‌های لازم برای تبدیل شدن به یک متخصص علم داده را کسب کنید.
تفاوت تابع IF با IFS در اکسل
تابع IFS در اکسل جایگزینی ساده‌تر برای IF تو در تو است که امکان بررسی چندین شرط و نمایش نتایج متفاوت را فراهم می‌کند. با این تفاوت که نوشتن و خواندن فرمول‌های آن ساده‌تر است.
معمولاً زمانی از تابع IF تو در تو (Nested IF) استفاده می‌کنیم که بخواهیم چند شرط را بررسی کنیم و برای هر شرط، نتیجه‌ی متفاوتی نمایش دهیم. در این روش، چند تابع IF داخل یکدیگر قرار می‌گیرند و فرمولی به شکل “IF(IF(IF()))” ایجاد می‌شود. بااین‌حال، نوشتن و ویرایش چنین فرمول‌هایی گاهی دشوار و زمان‌بر است.
برای ساده‌تر کردن این فرایند، تابع IFS معرفی شده است. این تابع در نسخه‌های Excel 365، Excel 2021 و Excel 2019 به شما اجازه می‌دهد چندین شرط را در قالب یک فرمول بنویسید و نتیجه‌ی مربوط به اولین شرط درست را دریافت کنید.
مهم‌ترین مزیت تابع IFS این است که می‌توانید چندین شرط را در یک فرمول واحد بنویسید. در این تابع، هر شرط در کنار نتیجه‌ی مربوط به آن قرار می‌گیرد. به همین دلیل، نوشتن، خواندن و ویرایش فرمول بسیار ساده‌تر می‌شود.
برای مثال فرض کنید می‌خواهیم میزان تخفیف را بر اساس تعداد مجوزهایی که یک کاربر در اختیار دارد، تعیین کنیم. با این فرض که هرچه تعداد مجوزهای کاربر بیشتر باشد، تخفیف بیشتری به او تعلق می‌گیرد. در این حالت می‌توانید از فرمول زیر را وارد کنید:
این فرمول شرایط را به‌ترتیب بررسی می‌کند:
- اگر مقدار سلول B2 بیشتر از ۵۰ باشد، تخفیف ۴۰ درصد خواهد بود.
- اگر مقدار سلول B2 بیشتر از ۴۰ باشد، تخفیف ۳۵ درصد خواهد بود.
- اگر مقدار سلول B2 بیشتر از ۳۰ باشد، تخفیف ۳۰ درصد خواهد بود.
- اگر مقدار سلول B2 بیشتر از ۲۰ باشد، تخفیف ۲۰ درصد خواهد بود.
- اگر مقدار سلول B2 بیشتر از ۱۰ باشد، تخفیف ۱۵ درصد خواهد بود.
- اگر مقدار سلول B2 بیشتر از ۵ باشد، تخفیف ۵ درصد خواهد بود.
- اگر هیچ‌کدام از شرط‌های قبلی برقرار نباشند، مقدار صفر برگردانده می‌شود.
در این فرمول، “TRUE” در انتهای عبارت نقش شرط پیش‌فرض را دارد؛ بنابراین، اگر هیچ‌یک از شرط‌های قبلی درست نباشند، عدد صفر برگردانده می‌شود.
همین فرمول را می‌توان با استفاده از چند تابع IF تو در تو نیز نوشت:
هر دو فرمول نتیجه‌ی یکسانی تولید می‌کنند. اما فرمول IFS کوتاه‌تر و خواناتر است و در صورت نیاز به تغییر شرایط، ویرایش آن نیز آسان‌تر خواهد بود. به‌علاوه پیچیدگی کمتری دارد و نیازی به قرار دادن چندین تابع IF درون یکدیگر نیست. به کمک این تابع می‌توانید شرط‌های مختلف را به‌ترتیب بررسی کنید و نتیجه‌ی مربوط به اولین شرط درست را دریافت کنید. به‌طور کلی، زمانی که با چندین شرط و نتیجه‌ی متفاوت سروکار دارید، تابع IFS می‌تواند فرمول‌نویسی در اکسل را ساده‌تر و قابل‌فهم‌تر کند.
چه زمانی نباید از تابع IF استفاده کنیم؟
یکی از پرکاربردترین توابع اکسل است و در انواع کاربرگ‌ها و تقریباً در همه حوزه‌های کاری مورد استفاده قرار می‌گیرد. بااین‌حال، پرکاربرد بودن این تابع به این معنا نیست که همیشه بهترین گزینه برای انجام هر کاری است. اکسل توابع متعددی دارد که می‌توانند بر اساس شرایط مشخص، عملیات مختلفی را انجام دهند. بسته به نوع نیازتان، می‌توانید از گزینه‌های زیر استفاده کنید:
- تابع IFS: برای بررسی چند شرط مختلف، بدون نیاز به تو در تو کردن چند تابع IF.
- تابع SWITCH: برای مقایسه یک مقدار با چند گزینه مشخص و برگرداندن نتیجه متناسب با آن.
- تابع CHOOSE: برای برگرداندن یک مقدار بر اساس شماره موقعیت یا اندیس آن.
- توابع XLOOKUP و VLOOKUP: برای جست‌وجوی مقادیر در جدول‌ها؛ این توابع در بسیاری از موارد جایگزین مناسب‌تری برای استفاده از چند تابع IF تو در تو هستند.
- توابع COUNTIFS و SUMIFS: برای شمارش یا جمع‌زدن مقادیر بر اساس یک یا چند شرط، بدون نیاز به استفاده از تابع IF.
- تابع FILTER: برای استخراج و نمایش ردیف‌هایی از داده‌ها که با معیارهای مشخص مطابقت دارند.
پیش از آنکه سعی کنید تابع IF را برای انجام کاری به کار ببرید که برای آن مناسب نیست، بهتر است ابتدا با توابع داخلی اکسل آشنا شوید و بررسی کنید که آیا تابع دیگری می‌تواند همان کار را ساده‌تر، سریع‌تر و با فرمولی خواناتر انجام دهد یا خیر.
مسعود اسدپور
تحلیل شبکه‌های اجتماعی
(۹۴ رای)
۱۰۵۸ نفر
۱۲۳۶ ساعت و ۴۰ دقیقه
نتیجه‌گیری
تابع IF در اکسل یکی از ابزارهای کاربردی برای بررسی شرایط و تصمیم‌گیری خودکار بر اساس داده‌هاست. با استفاده از این تابع می‌توانید نتایج مختلفی را بر اساس درست یا نادرست بودن یک شرط نمایش دهید و کارهایی مانند تعیین وضعیت قبولی یا مردودی، بررسی مقادیر و دسته‌بندی اطلاعات را ساده‌تر انجام دهید. همچنین، با ترکیب تابع IF با توابعی مانند AND و OR یا استفاده از چند تابع IF به‌صورت تو در تو، می‌توانید شرط‌های پیچیده‌تری را بررسی کنید. اگر می‌خواهید کار با داده‌ها را در اکسل سریع‌تر و دقیق‌تر انجام دهید، یادگیری نحوه استفاده از تابع IF و تمرین با مثال‌های مختلف، نقطه شروع مناسبی برای شما خواهد بود.
پرسش‌های متداول (FAQ)
در این بخش به تعدادی از سوالات متداول در مورد تابع IF در اکسل پاسخ می‌دهیم.
فرمول تابع IF در اکسل چیست؟
تابع IF برای بررسی یک شرط و نمایش نتیجه بر اساس درست یا نادرست بودن آن استفاده می‌شود. ساختار کلی این تابع به شکل زیر است:
چگونه در تابع IF چند شرط تعریف کنیم؟
برای بررسی چند شرط در اکسل، می‌توان تابع IF را با توابع منطقی AND و OR ترکیب کرد یا از چند تابع IF تو در تو استفاده کرد.
تفاوت IF و IFS در اکسل چیست؟
تابع IF برای بررسی یک شرط و تعیین نتیجه بر اساس درست یا نادرست بودن آن استفاده می‌شود. در مقابل، تابع IFS امکان بررسی چندین شرط را در یک فرمول فراهم می‌کند و نتیجه مربوط به اولین شرط درست را برمی‌گرداند.
زمانی که فقط یک شرط دارید، استفاده از IF ساده‌تر است. اما اگر بخواهید چندین شرط و نتیجه متفاوت را بررسی کنید، IFS معمولاً خواناتر و مدیریت آن آسان‌تر است. البته تابع IFS در نسخه‌های قدیمی اکسل در دسترس نیست.
چرا تابع IF در اکسل نتیجه اشتباه می‌دهد؟
نمایش نتیجه نادرست در تابع IF می‌تواند دلایل مختلفی داشته باشد. برخی از رایج‌ترین آن‌ها عبارت‌اند از: استفاده نادرست از عملگرهای مقایسه، ذخیره شدن اعداد به‌صورت متن، اشتباه در نوشتن فرمول، استفاده نادرست از شرط‌ها.برای رفع مشکل، ساختار فرمول، نوع داده‌های سلول‌ها و عملگرهای مقایسه را بررسی کنید
چگونه در تابع IF متن نمایش دهیم؟
برای نمایش متن در خروجی تابع IF، کافی است عبارت موردنظر را داخل علامت نقل‌قول دوتایی قرار دهید.
آیا می‌توان چند تابع IF را با هم ترکیب کرد؟
بله. در اکسل می‌توان چند تابع IF را درون یکدیگر قرار داد. به این روش IF تو در تو (Nested IF) گفته می‌شود و زمانی کاربرد دارد که بخواهیم بیش از دو نتیجه احتمالی را بررسی کنیم.
منابع:
