استفاده از توابع منطقی (Logical Functions) در اکسل (+ دانلود فیلم آموزش گام به گام)

توابع منطقی (Logical Functions) در اکسل بسیار محبوب و همچنین مفید هستند. با استفاده از آن‌ها می‌توان مقادیر سلول‌های دیگر را بررسی کرده و اقدامات خاصی را بسته به نتیجه بررسی اجرا کرد. بدین ترتیب می‌توانیم وظایف مختلف را در صفحه گسترده به صورت خودکار اجرا کنیم.

شیوه استفاده از تابع IF

تابع IF تابع اصلی اکسل است و از این رو تابعی است که باید ابتدا با آن آشنا شویم. از این تابع در بخش‌های مختلف این مقاله بارها استفاده خواهیم کرد. در ادامه نگاهی به ساختار تابع IF خواهیم داشت و سپس برای آن مثال‌هایی ارائه می‌کنیم.

تابع IF سه جزء اطلاعاتی دارد:

=IF(logical_test، [value_if_true]، [value_if_false])

• تست منطقی (logical_test): این شرطی است که تابع مورد بررسی قرار می‌دهد.
• مقدار خروجی در صورت درست بودن (value_if_true): عملی که در صورت برقرار بودن شرط یا درست بودن، باید اجرا شود.
• مقدار خروجی در صورت نادرست بودن (value_if_false): عملی که در صورت برقرار نبودن شرط یا نادرست بودن آن باید اجرا شود.

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

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

در ادامه برخی مثال‌های عملی آن‌ها را بررسی می‌کنیم.

مثال اول برای تابع IF: مقادیر متنی

در این مثال، می‌خواهیم بررسی کنیم که آیا یک سؤال برابر با عبارت خاصی است یا نه. تابع IF حساس به بزرگی/کوچکی حروف نیست و از این رو توجهی به استفاده از حروف بزرگ یا کوچک در متون نمی‌کند.

از فرمول زیر در ستون C برای نمایش «No» در صورتی استفاده می‌شود که ستون B شامل متن «Completed» باشد و زمانی که هر چیز دیگری باشد عبارت «Yes» نمایش می‌یابد.

=IF(B2="Completed"،"No"،"Yes")

با این که تابع IF حساس به بزرگی/کوچی حروف نیست؛ اما متن باید دقیقاً مطابقت داشته باشد.

مثال دوم برای تابع IF: مقادیر عددی

تابع IF برای مقایسه مقادیر عددی نیز به خوبی ‌استفاده می‌شود.

فرمول زیر بررسی می‌کند که آیا سلول B2 شامل عددی بزرگ‌تر یا مساوی 75 است یا نه. اگر چنین باشد کلمه «Pass» نمایش می‌یابد و در غیر این صورت به نمایش عبارت «Fail» می‌پردازد.

=IF(B2>=75،"Pass"،"Fail")

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

در مثال زیر می‌خواهیم در صورتی که مشتری مقدار معینی هزینه کرده باشد، به وی 10% تخفیف بدهیم. ما از مقدار 3000 پوند به این منظور استفاده می‌کنیم.

=IF(B2>=3000،B2*90%،B2)

بخش B2*90% در فرمول روشی است که با آن 10 درصد از مقدار سلول B2 کم می‌شود. البته روش‌های مختلفی برای انجام این کار وجود دارد.

نکته مهم این است که می‌توانید از هر فرمولی در بخش‌های value_if_true یا value_if_false استفاده کنید. اجرای فرمول‌های مختلف بسته به مقادیر سلول‌های دیگر، یک مهارت اصلی است که باید داشته باشید.

مثال سوم برای تابع IF: مقادیر تاریخ

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

فرمول زیر در ستون C استفاده می‌شود. ما بررسی می‌کنیم که آیا تاریخ سرسید در سلول B2 کمتر از تاریخ امروز است یا نه. تابع TODAY تاریخ روز جاری را از روی ساعت رایانه بازگشت می‌دهد.

=IF(B2<TODAY()،"Overdue"،B2-TODAY())

فرمول‌های IF تو در تو چه هستند؟

احتمالاً عبارت IF-های تو در تو را قبلاً شنیده‌اید. این بدان معنی است که می‌توانیم یک تابع IF را درون تابع IF دیگری بنویسیم. این کار زمانی مفید خواهد بود که قرار است بیش از دو کار انجام یابد.

یک تابع IF قادر به انجام دو اقدام است که یکی value_if_true و دیگری value_if_false است. اما اگر تابع IF دیگری را در بخش value_if_false جاسازی کنیم، در این صورت می‌توانیم اقدام دیگری را اجرا کنیم.

مثال زیر را در نظر بگیرید که می‌خواهیم در صورت بزرگ‌تر بودن مقدار سلول B2 از 90 عبارت «Excellent»، در صورت بزرگ‌تر یا مساوی بودن با 75 عبارت «Good» و در صورت پایین‌تر بودن از این مقدار عبارت «Poor» نمایش یابد.

=IF(B2>=90،"Excellent"،IF(B2>=75،"Good"،"Poor"))

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

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

تابع‌های منطقی AND و OR

تابع‌های AND و OR زمانی استفاده می‌شوند که بخواهیم بیش از یک مقایسه در فرمول خود داشته باشیم. تابع IF به تنهایی می‌تواند تنها یک شرط یا مقایسه را مدیریت کند. مثالی را در نظر بگیرید که در آن می‌خواهیم برحسب این که مشتری مقدار معینی هزینه کرده باشد و این که چند سال مشتری ما بوده باشد، 10 درصد تخفیف به وی بدهیم. تابع‌های AND و OR به تنهایی مقادیر True یا False بازگشت می‌دهند.

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

کلیک کنید

تابع AND در صورتی مقدار TRUE بازگشت می‌دهد که همه شرایط آن برقرار باشند و در غیر این صورت مقدار FALSE بازگشت می‌دهد تابع OR در صورتی مقدار TRUE بازگشت می‌دهد که یک یا همه شرایط برقرار باشند و در صورتی مقدار FALSE بازمی‌گردد که هیچ کدام از شرایط برقرار نباشند.

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

=AND(logical1، [logical2] ...)

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

مثالی از تابع AND

تابع AND در ادامه برای بررسی این نکته استفاده می‌شود که آیا مشتری دست‌کم 3000 پوند هزینه کرده و آیا دست‌کم سه سال مشتری ما بوده است یا نه؟

=AND(B2>=3000،C2>=3)

می‌توانید ببینید که این تابع در مورد Matt و Terry مقدار False بازگشت می‌دهد، زیرا اگر چه هر دو آن‌ها یکی از معیار ها را دارند؛ اما در مورد تابع AND باید هر دو معیار را همزمان داشته باشند.

مثالی از تابع OR

تابع OR که در زیر می‌بینید بررسی می‌کند که آیا مشتری دست‌کم 3000 پوند هزینه کرده و یا به مدت سه سال مشتری ما بوده است یا نه؟

=OR(B2>=3000،C2>=3)

در این مثال، فرمول مورد نظر در مورد Matt و Terry مقدار TRUE بازگشت می‌دهد. تنها در مورد Julie و Gillian مقدار FALSE بازگشت می‌یابد زیرا فاقد هر دو شرط هستند.

استفاده از AND و OR به همراه تابع IF

از آنجا که تابع AND و OR وقتی به تنهایی استفاده می‌شوند، مقدار TRUE یا FALSE بازگشت می‌دهند، به ندرت از آن‌ها بدین شکل استفاده می‌کنیم.

بلکه به طور معمول از این تابع‌ها به همراه تابع IF یا درون یک ویژگی اکسل مانند «قالب‌بندی شرطی» (Conditional Formatting) یا «اعتبار سنجی داده» (Data Validation) استفاده می‌کنیم تا برخی اقدامات تکراری را در صورت TRUE ارزیابی شدن فرمول اجرا کنیم.

در فرمول زیر تابع AND درون تست منطقی یک تابع IF دیگر به صوت تو در تو آمده است. اگر تابع AND به صورت TRUE باشد، در این صورت 10% تخفیف روی مقدار ستون B اعمال می‌شود، در غیر این صورت هیچ تخفیفی ارائه نمی‌شود و مقدار ستون B در ستون D ارائه می‌شود.

=IF(AND(B2>=3000،C2>=3)،B2*90%،B2)

تابع XOR

علاوه بر تابع OR معمولی یک تابع OR انحصاری نیز وجود دارد. این تابع XOR نام دارد. تابع XOR در نسخه 2013 اکسل معرفی شده است. درک این تابع ممکن است به کمی تلاش نیاز داشته باشد، بنابراین یک مثال ارائه شده است.

ساختار تابع XOR همانند تابع OR است:

=XOR(logical1، [logical2] ...)

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

• در صورتی که هر یک از شرط‌ها به صورت TRUE باشند، نتیجه TREU خواهد بود.
• در صورتی که هر دو شرط TRUE باشند، یا هیچ کدام TRUE باشند، نتیجه FALSE خواهد بود.

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

• اگر تعداد فردی از شرط‌ها مقدار TRUE داشته باشند، نتیجه TRUE خواهد بود.
• اگر تعداد زوجی از شرط‌ها TRUE باشند، یا همه شرط‌ها FALSE باشند، نتیجه FALSE خواهد بود.

در ادامه مثالی از تابع XOR ارائه می‌کنیم.

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

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

تابع XOR برای اجرای این منطق بسیار عالی است. فرمول زیر که در ستون E وارد شده است تابع XOR را نشان می‌دهد که به همراه تابع IF برای نمایش Yes یا No در صورت برقرار بودن تنها یکی از شرط‌ها استفاده می‌شود.

=IF(XOR(B2>=3000،C2>=3000)،"Yes"،"No")

تابع NOT

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

تابع NOT مقدار آرگومان خود را معکوس می‌کند. بنابراین اگر مقدار منطقی آن TRUE باشد، مقدار FALS بازگشت می‌دهد و اگر مقدار منطقی آن FALSE باشد، مقدار TRUE بازمی‌گرداند.

توضیح این تابع با ارائه برخی مثال‌ها ساده‌تر خواهد بود. ساختار تابع NOT به صورت زیر است:

=NOT(logical)

مثال اول برای تابع NOT

در این مثال، تصور کنید یک اداره مرکزی در لندن داریم و می‌خواهیم اداره‌های محلی دیگری نیز در شهرهای مختلف تأسیس کنیم. ما می‌خواهیم در صورتی که اداره مورد نظر هر چیزی به جز اداره مرکزی لندن باشد عبارت «Yes» و اگر لندن باشد مقدار «No» نمایش یابد.

تابع Not در تست منطقی تابع IF زیر قرار می‌گیرد تا نتیجه TRUE را معکوس کند.

=IF(NOT(B2="London")،"Yes"،"No")

این وضعیت با استفاده از عملگر منطقی NOT به صورت مثال زیر نیز قابل دستیابی است:

=IF(B2<>"London"،"Yes"،"No")

مثال دوم برای تابع NOT

تابع NOT زمانی مفید است که مشغول کار با تابع‌های اطلاعات در اکسل باشیم. این گروه از تابع‌ها در اکسل به بررسی چیزی می‌پردازند و سپس در صورت موفقیت مقدار TRUE و در غیر این صورت مقدار FALSE بازگشت می‌دهند.

برای نمونه تابع ISTEXT بررسی می‌کند که آیا محتوای یک سلول عبارت متنی است یا نه و در صورتی که چنین باشد مقدار TRUE و در غیر این صورت مقدار FALSE بازگشت می‌دهد. تابع NOT به این دلیل مفید است که می‌تواند نتیجه این تابع‌ها را معکوس کند.

در مثال زیر می‌خواهیم 5 درصد از مقداری که فروشنده به فروش می‌رساند را به یک فروشنده پرداخت کنیم. اما اگر نتوانسته باشد چیزی بفروشد کلمه «None» در سلول نمایش می‌یابد و این وضعیت خطایی در فرمول ایجاد می‌کند.

اگر تابع ISTEXT برای بررسی وجود متن استفاده شود. این تابع در صورت وجود متن مقدار TRUE بازگشت می‌دهد و از این رو تابع NOT این وضعیت را به FALSE عوض می‌کند و IF محاسبات خود را اجرا می‌کند.

=IF(NOT(ISTEXT(B2))،B2*5%،0)

سخن پایانی

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

اگر این مطلب برای شما مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:

• آموزش‌‌های مجموعه نرم‌ افزاری آفیس
• مجموعه آموزش‌های نرم‌افزار اکسل
• مجموعه آموزش مهارت‌‌های اساسی کامپیوتر
• آموزش اکسل (Microsoft Office Excel 2016)
• آموزش کاربرد اکسل (Excel) در مهندسی صنایع
• آموزش مقدماتی اکسل (Excel) — به زبان ساده
• 1۶ فرمول اکسل که به حل مشکلات روزمره کمک می‌کنند

==