تابع IF در اکسل — به زبان ساده

اکسل یکی از محصولات محبوب شرکت مایکروسافت محسوب می‌شود، بطوری که اکثر شرکت‌ها در فرم‌های استخدامی، تسلط کار با اکسل را یکی از اولویت‌های جذب کارمندان محسوب می‌کنند. از طرفی توابع IF در اکسل برای کسانی که به تازگی با این نرم‌افزار سروکار پیدا کرده‌اند، کمی پیچیده به نظر می‌رسد. در این نوشتار از مجله فرادرس با تکیه بر این تابع سعی می‌کنیم مثال‌هایی ارائه دهیم که به کمک آن‌ها، تسلط بیشتری بر فرمول نویسی و همچنین استفاده از تابع if در اکسل کسب کنید. فرض بر این است که با مفاهیم اولیه صفحه گسترده اکسل مانند سلول، سطر، ستون و آدرس سلول‌ها به همراه فرمول نویسی ساده آشنایی دارید.

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

تابع IF در اکسل

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

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

کلیک کنید

در نسخه‌های جدید از این نرم‌افزار، بیش از یک میلیون سطر و بیش از شانزده‌ هزار ستون در هر کاربرگ (worksheet) وجود دارد که نشانگر قابلیت انجام بیش از شانزده میلیارد محاسبه و فرمول است. همچنین وجود بی‌نهایت کاربرگ در یک پرونده اکسل (کارپوشه) از امکاناتی است که هر کاربری را مجاب به استفاده از اکسل می‌کند. از طرفی قابلیت ایجاد ارتباط و فرمول بین سلول‌های یک یا چند کاربرگ نیز در اکسل به محبوبیت آن اضافه کرده است.

برای شروع کار مقدمه‌ای از نحوه فرمول نویسی در اکسل ارائه می‌کنیم تا در ادامه هنگام استفاده و به کارگیری تابع IF در اکسل دچار مشکل نشویم.

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

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

=1+2

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

به محض فشردن کلید Enter، محاسبات انجام شده و در سلول A1 مقدار 3 را مشاهده می‌کنید. از آنجایی که این فرمول، ارتباطی بین سلول‌های دیگر کاربرگ ایجاد نکرده است، به آن فرمول یا «سلول مستقل» (Independent Cell) می‌گوییم. در مقابل ممکن است به جای آنکه از مقادیر استفاده کنیم، آدرس سلول‌هایی که مقدار مورد نظر در آن قرار گرفته است، مبنای محاسبات قرار گیرند. در این صورت اگر کاربرگی به مانند شکل زیر داشته باشیم، فرمولی که باید در سلول A3‌ برای جمع سلول‌های A1 و A2 بنویسیم به مانند «A1+A2=» خواهد بود.

به این ترتیب با تغییر مقدار سلول‌های A1 یا A2 و یا هر دو، مقدار سلول A3‌ نیز به تناسب آن‌ها تغییر خواهد کرد و همیشه مجموع مقادیر این دو سلول را نشان می‌دهد. با این کار یک فرمول یا سلول وابسته (Dependent Cell) ایجاد کرده‌ایم که پیش‌نیازهای (Precedent) آن سلول‌های A1 و A2 هستند.

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

کلیک کنید

درج تابع در اکسل

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

خوشبختانه بسیاری از محاسبات ریاضی و حتی متنی (مثلا جایگزینی یک حرف در یک عبارت) به کمک توابع اکسل قابل اجرا هستند. پس بهتر است با نحوه درج و استفاده از توابع در اکسل نیز آشنا شویم. فرض کنید قرار است حاصل جمع سلول‌های A1 تا A100 را در سلول B1 محاسبه کنیم. کافی است که در سلول B1 از تابع sum استفاده کرده و به عنوان پارامترهای این تابع ناحیه مورد نظر را معرفی کنیم. تصویر زیر این کار را نمایش داده است.

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

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

کلیک کنید

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

کاربرد تابع IF در اکسل

اغلب در اکسل، محاسباتی که ممکن است به صورت مشکل یا پیچیده توسط روش‌های ریاضی انجام شوند، در قالب یک تابع مورد استفاده قرار می‌دهند. مثلا تابع sum عمل جمع را انجام می‌دهد، تابع average میانگین‌گیری می‌کند و یا تابع count‌ سلول‌های عددی در یک ناحیه را می‌شمارد. پس به نظر می‌رسد که نحوه محاسبه در چنین توابعی معلوم است ولی در این میان، تابع IF و در حقیقت توابع منطقی (Logical Functions) با دیگر توابع اکسل تفاوت مهمی دارند.

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

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

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

کلیک کنید

برای مثال در تعیین مثبت بودن سود، می‌خواهیم مشخص کنیم که اگر درآمد (فروش - خرید) بزرگتر از صفر است، مقدار متنی «سود-ده» در یک سلول ثبت شده و در غیر اینصورت مقدار «زیان-ده» نشان داده شود. بهتر است این مثال‌ها را یک به یک پیگیری کرده و محاسبات را به کمک تابع IF‌ در اکسل انجام دهیم. ولی قبل از هر چیز بهتر است با یک نمودار گردشی (Flow Chart) نحوه عملکرد تابع IF در اکسل را دقیق‌تر بررسی کنیم زیرا تابع IF در اکسل درست به مانند تابع IF‌ در بیشتر زبان‌های برنامه‌نویسی عمل می‌کند.

واضح است که در ورودی، باید مقداری که قرار است براساس آن شرط مشخص شود، قرار گیرد. عبارت شرطی یا مقایسه‌ای در قسمت لوزی ثبت شده و محاسباتی اختیاری نیز که در مستطیل‌ها مشخص شده‌اند در صورت صحیح بودن شرط (True) یا نادرست بودن آن (False) انجام می‌شوند. در انتها نیز نتیجه تابع IF در خروجی ظاهر می‌شود. در اکسل نیز هر کدام از شکل‌ها در تصویر بالا در تابع IF نیز به کار می‌روند. حال به بررسی نحوه ثبت و محاسبه تابع IF در اکسل می‌پردازیم.

تابع IF‌ در اکسل

فرض کنید سلول فعال، A1‌ باشد. به منظور درج تابع IF در اکسل باید از برگه Formula گروه Logical را انتخاب و روی IF‌ کلیک کنید. با این کار پنجره‌ای به صورت زیر ظاهر می‌شود.

به این ترتیب در حقیقت ما سلول A1 را به عنوان سلولی که باید خروجی در آن قرار گیرد انتخاب کرده‌ایم که در نمودار گردشی به صورت بیضی (خروجی) نمایش داده شده است. حال کافی است که پارامترهای تابع IF در اکسل را بهتر بشناسیم.

فیلم آموزش اکسل Microsoft Excel 2013 در فرادرس

کلیک کنید

اولین پارامتر به نام Logical_test است که مشخصا همان لوزی در نمودار گردشی است. این پارامتر باید یک مقدار منطقی (Logical) را مشخص کند که پاسخ آن صحیح (True) یا غلط (False) است. پارامتر بعدی یعنی Value_if_true نیز نقش مستطیل‌های سمت راست و Value_if_false نقش مستطیل سمت چپ را در نمودار گردشی ایفا می‌کند. محاسبات و عملیاتی که باید هنگام محقق شدن شرط انجام شود را در پارامتر Value_if_true وارد کرده و در صورتی که شرط محقق نشود، محاسبات مورد نظر را در پارامتر Value_if_false وارد می‌کنیم.

مثال ۱- مشخص کردن مثبت بودن عدد ۵

این مثال به ساده‌ترین شکل نشان می‌دهد که چگونه باید پارامترهای تابع IF در اکسل را تکمیل کرد. فرض کنید سلول A1 فعال است. تصویر زیر پارامترها را در این حالت به خوبی نشان داده است. توجه داشته باشید که در اینجا تابع IF باعث ایجاد سلول وابسته نشده است.

همانطور که دیده می‌شود، پارامتر اول به صورت 0<5 نوشته شده است که یک گزاره منطقی است. با توجه به ارزش آن که در سمت راست کادر (TRUE) دیده می‌شود، عدد ۵ مثبت است. به همین علت مقداری که در پارامتر دوم تابع IF‌ ثبت شده درون سلول به عنوان نتیجه نهایی دیده خواهد شد که مقدار متنی «مثبت» است.

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

کلیک کنید

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

=IF(5>0,"مثبت","منفی")

نکته: در این حالت بین پارامترهای تابع علامت «,» ظاهر شده است که باعث شده هر پارامتر از پارامتر دیگر به راحتی تشخیص داده شود. اگر لازم باشد می‌توانید همین عبارت را نیز در سلول وارد کنید تا محاسبات مربوط به تابع IF در اکسل مطابق با این مثال، انجام شود.

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

=IF(5>0, ,)

ولی اگر می‌خواهید توسط این تابع فقط صحت شرط مورد بررسی قرار گیرد، بهتر است به عنوان پارامترهای دوم و سوم مقادیر TRUE‌ یا FALSE را وارد کنید.

 =IF(5>0, TRUE, FALSE)

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

این بار حالتی را در نظر بگیرید که مقداری (مثلا نمره آزمون یک دانش آموز) در سلول A1 نوشته شده و می‌خواهیم نتیجه مردود یا قبولی او را با توجه به بزرگتر یا مساوی بودن با ۱۰ یا کوچکتر از آن، مشخص کنیم. این نتیجه قرار است در سلول B1 ظاهر شود. فرمولی که برای این سلول به کار خواهیم برد، به شکل زیر خواهد بود.

 =IF(A1>=10, "مردود","قبول")

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

به این ترتیب اگر نمرات دانش آموزان دیگر در ادامه لیست و در سلول‌های A2 به بعد قرار گرفته باشد، کافی است با کپی کردن سلول B1 در سلول‌های B2 به بعد، نتیجه قبولی یا مردودی آن‌ها را نیز در این سلول‌ها، مشخص کنید. چنین وضعیتی را در تصویر زیر مشاهده می‌کنید.

مثال ۲- نشانگر سود-ده یا زیان-ده بودن شرکت

تراز هزینه‌ها و درآمدهای یک شرکت طی یک سال در سلول D14 مشخص شده است. می‌خواهیم به کمک تابع IF‌ در سلول E2 وضعیت شرکت را با دو عبارت «سود-ده»‌ یا «زیان‌-ده» مشخص کنیم. کاربرگ به صورت زیر تهیه شده است. توجه داشته باشید که اختلاف مجموع درآمدها و هزینه‌ها در سلول D14 توسط فرمول تفریق و تابع SUM به صورت زیر مشخص شده است.

=SUM(B2:B13)-SUM(C2:C13)

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

کلیک کنید

به این ترتیب، سلول E2 به عنوان یک سلول وابسته درآمده و پیش‌نیاز آن سلول D14 است. البته چون سلول D14 نیز به سلول‌های B2‌ تا C13 وابسته است، با تغییر هر یک از آن‌ها ممکن است نتیجه ظاهر شده در سلول D14 تغییر کند.

نکته: توجه داشته باشید که تابع IF در اکسل فقط قادر به تعیین مقدار سلول است و نمی‌تواند در مورد رنگ سلول یا تغییر قلم محتویات سلول تاثیرگذار باشد. به منظور تغییر قالب سلول به صورت شرطی باید از دستور Conditional Formatting در اکسل استفاده کنید.

مثال ۳- محاسبات عددی بر اساس شرط با تابع IF

فرض کنید قرار است میزان مالیات حقوق کارکنان را محاسبه کنیم. همچنین در نظر بگیرید که اگر مثلا فردی حقوقی بیش از ۱۰۰ هزار تومان داشته باشد، میزان درآمدی که بیش از این مبلغ دریافت کرده، شامل ۱۰٪ مالیات خواهد شد. در غیر اینصورت مقدار مالیات او صفر خواهد بود.

در تصویر زیر، اطلاعات مربوط به حقوق ۵ کارمند در کاربرگ اکسل و در ستون A آورده شده است. قرار است مالیات هر یک را در ستون B نمایش دهیم. محاسبات را برای سلول B2 انجام می‌دهیم و با توجه به یکسان بودن فرمول و تابع به کار رفته، آن را برای سلول‌های B3 تا B5 نیز کپی می‌کنیم.

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

نکته: برای کپی کردن این فرمول و تابع نوشته شده در سلول B2 به سلول‌های دیگر می‌توانید «دستگیره پر کردن» (Fill Handle) را بکشید تا در سلول‌های پایین هم همین فرمول به کار رود. با انتخاب هر سلول، دستگیره پر کردن به صورت یک مربع کوچک در پایین و انتهای هر سلول ظاهر می‌شود. با قرار دادن نشانگر ماوس روی آن، نشانگر به علامت + تغییر شکل می‌دهد. در این هنگام باید عمل کشیدن و رها کردن (Drag and Drop) را انجام دهید.

با اجرای این عملیات، نتیجه به صورت زیر خواهد بود.

مثال ۴- تعیین وضعیت بازنشستگی

لیستی از کارکنان یک شرکت در اختیارمان قرار دارد که شامل سن و سابقه کار آن‌ها است. مدیریت می‌خواهد افرادی که بالای ۶۰ سال هستند را شناسایی کرده و از بین آن‌ها، کسانی که بیش از ۳۰ سال سابقه دارند را بازنشسته کند تا برای جوانان امکان ارائه خدمت بازتر شود و از نیروهای جوان در سازمان بهره ببرد.

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

این نمودار، نشان می‌دهد که سن و سابقه به عنوان ورودی به تابع داده می‌شود. از طرفی برای انجام این محاسبات احتیاج به دو شرط به صورت تو در تو (Nested) داریم زیرا دو لوزی‌ که بیانگر شرط‌ها هستند در نمودار دیده می‌شود.

مراحل اجرای این محاسبات به کمک تابع IF در اکسل به صورت گام به گام در تصاویر زیر دیده می‌شود. واضح است که ابتدا وضعیت سن مورد بررسی قرار گرفته و کسانی که سنی بیش از ۶۰ سال دارند برای شرط بازنشستگی، سابقه کار آن‌ها نیز مورد تحلیل قرار می‌گیرد. در انتها نیز برای کسانی که آماده بازنشستگی هستند عبارت بازنشسته و برای کسانی شرایط بازنشستگی را ندارد عبارت شاغل در سلول متناظرشان از ستون C ثبت می‌شود.

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

همانطور که در پایین و سمت چپ پنجره مشاهده می‌کنید، به نظر می‌رسد که برای نفر اول، مقدار وضعیت بازنشستگی برابر با «شاغل»‌ است. ولی ما می‌خواهیم فرمولی کلی بنویسیم که برای هر تغییری در سن و سابقه به درستی عمل کند. بنابراین در گام سوم عبارت شرطی یا IF دوم را به کار می‌بریم. این عمل نشان می‌دهد که اگر سنی فردی بزرگتر یا مساوی با ۶۰ باشد، پارامتر دوم تابع IF‌ مقدار لازم را مشخص می‌کند و باید با استفاده از یک شرط دیگر وضعیت سابقه او را هم مورد بررسی قرار دهیم.

برای درج تابع IF دوم بعد از انتخاب پارامتر دوم (که در گام شماره ۳ مشخص شده) از کادر نام (Name Box) تابع مورد نظر را انتخاب می‌کنیم.

در پنجره پارامترهای IF‌ دوم، پارامترها را به صورتی که در شکل زیر دیده می‌شود، تکمیل می‌کنیم. از آنجایی که هم برای IF‌ اول و هم IF‌ دوم همه پارامترها کامل شده‌اند، می‌توانیم دکمه OK را در پنجره پارامترها یا Function Arguments کلیک کنیم.

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

کلیک کنید

اگر در پنجره IF‌ اول پارامترها تکمیل نشده باشند، بهتر است با کلیک روی تابع IF‌ اول در نوار فرمول، پنجره را تبدیل به پنجره پارامترهای IF اول بکنید. باز هم به مانند مثال قبل، می‌توانید فرمول مربوط به سلول C2 را در سلول‌های بعدی کپی کنید تا لیستی از کارمندان شاغل و آماده بازنشستگی تهیه شود.

همانطور که دیده می‌شود، مشخصات مربوط کارمندهای ردیف دوم و سوم، به علت اینکه سنشان بیشتر از ۶۰ سال نیست، مورد بررسی در IF دوم قرار نمی‌گیرند و مقدار تابع برایشان «شاغل» است. با توجه به مشخصات کارمندان مربوط به ردیف پنجم نیز با توجه به اینکه در شرط IF اول صادق است ولی در IF دوم صدق نمی‌کند،‌ مقدار وضعیت بازنشستگی همچنان «شاغل» خواهد بود.

برعکس این موضوع مربوط به کارمندی است که مشخصاتش در سطر ششم قرار گرفته، سابقه او بیش از ۳۰ سال است ولی به علت اینکه در شرط اول (IF‌ اول) صدق نکرده است، آماده بازنشستگی نیست. فقط تنها شخصی که می‌توان او را بازنشسته معرفی کرد، فردی است که اطلاعاتش در سطر چهار کاربرگ قرار دارد. او هم سنش بیشتر از ۶۰ سال است و سابقه‌ای بیش از ۳۰ سال دارد.

البته توجه داشته باشید از آنجایی که به همراه علامت بزرگتر، علامت مساوی نیز در شرط قرار گرفته است، فردی که سنش دقیقا ۶۰ و سابقه‌اش هم ۳۰ سال باشد، بازنشسته تلقی خواهد شد.

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