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

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

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

عملگرهای منطقی در اکسل

عملگرهای منطقی در اکسل
۵/۵ - (۱۲ امتیاز)

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

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

  • برابر با
  • برابر نیست با
  • بزرگتر از/ کوچکتر از/ بزرگتر یا مساوی/ کوچکتر یا مساوی

مروری بر عملگرهای منطقی در اکسل

عملگرهای منطقی در اکسل برای مقایسه دو داده مورد استفاده قرار میگیرن. این عملگرها، گاهی اوقات عملگرهای صفر و یک نامیده میشن. چون خروجی اونها در هر صورت یا True هست یا  False.

شش عملگر منطقی در اکسل به شرح زیر وجود دارن:

شرح عملگر مثال خروجی فرمول
برابر با =

=A1=B1

اگر محتوای سلول A1 با B1 برابر باشه، True و اگر برابر نباشه False خواهد بود
مخالف با <>

=A1<>B1

اگر محتوای سلول A1 با B1 برابر نباشه، True و اگر برابر باشه False خواهد بود
بزرگتر از >

=A1>B1

اگر محتوای سلول A1 بزرگتر از B1 باشه، True و اگر کوچکتر باشه False خواهد بود
کوچکتر از <

=A1<B1

اگر محتوای سلول A1 کوچکتر از B1 باشه، True و اگر بزرگتر باشه False خواهد بود
بزرگتر یا مساوی >=

=A1>=B1

اگر محتوای سلول A1 بزگتر یا مساوی با B1 باشه، True و در غیر اینصورت False خواهد بود
کوچکتر یا مساوی <=

=A1<=B1

اگر محتوای سلول A1 کوچکتر یا مساوی با B1 باشه، True و در غیر اینصورت False خواهد بود

 

در تصویر زیر نتایج بررسی و مقایسه دو سری داده رو با عملگرهای تشریح شده می بینیم:

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

شکل ۱- نتایج منطقی با استفاده از عملگرهای منطقی در اکسل

استفاده از عملگر منطقی “مساوی” در اکسل

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

=A1=B1

اگر محتوای سلول A1 و B1 برابر باشه، خروجی True خواهد بود

=A1=”اکسل پدیا”

اگر داخل سلول A1 نوشته شده باشه “اکسل پدیا”، خروجی True خواهد بود. در غیر اینصورت False

=A1=TRUE

اگر داخل سلول A1 نوشته شده باشه  True، خروجی True خواهد بود. در غیر اینصورت False

=A1=(B1/2)

اگر مقدار سلول A1 با مقدار سلول B1 تقسیم بر ۲ برابر باشد، خروجی True خواهد بود

مثال اول: استفاده از عملگر مساوی در مقایسه داده از جنس تاریخ

همونطور که قبلا صحبت کردیم محاسبات مربوط به تاریخ و زمان (مخصوصا تاریخ شمسی) در اکسل منطق خاص خودشو داره. وقتی که داریم راجع به مقایسه دو تاریخ (میلادی) صحبت میکنیم، اگر در دو سلول نوشته باشیم ۱۲/۰۹/۲۰۵۰ و بنویسیم A1=B1 نتیجه فرمول True خواهد بود. اما اگر این مسئله رو بخوایم به این صورت بنویسیم ۱۲/۰۹/۲۰۵۰A1= یا “۱۲/۰۹/۲۰۵۰A1=” False خواهد بود. چرا؟

همونطور که میدونیم تاریخ میلادی در اکسل به عنوان یک عدد ذخیره میشه و مبنای محاسبات زمان، تاریخ ۰۱/۰۱/۱۹۰۰ هست (که مقدار آن برابر هست با ۱) و عدد متناظر با تاریخ ۲۰۵۰/۱۲/۰۹ برابر است با ۵۵۱۳۱. از طرفی مقدار “۱۲/۰۹/۲۰۵۰” یک مقدار متنی هست. پس یک مقدار متنی نمیتونه با عدد ۵۵۱۳۱ برابر باشه، پس نتیجه فرمول False خواهد بود. برای اینکه بتونیم نتیجه درست بگیریم از این مقایسه، باید مقدار تاریخ رو در یک تابع Datevalue بذاریم

=J11=DATEVALUE(“۲۰۵۰/۱۲/۹”)

ایسه تاریخ میلادی با عملگر منطقی

شکل ۲- مقایسه تاریخ میلادی با عملگر منطقی

مثال دوم: استفاده از عملگر مساوی در مقایسه داده متنی

استفاده از عملگر مساوی در مورد داده های متنی نکته خاصی نداره و براحتی میتونه مورد استفاده قرار بگیره. اما چیزی که باید بهش توجه کنیم، حروف کوچک و بزرگ در زبان انگلیسی هست. در زبان انگلیسی، اگر دو داده “orange” و “Orange” رو مقایسه میکنیم، خروجی True خواهد بود. بعبارتی فرقی بین حروف کوچک و بزرگ نخواهد بود. اما اگر برامون مهم باشه و بخوایم دو داده رو با هم مقایسه کنیم که بین حروف کوچک و بزرگ تفاوت قائل بشه، باید از تابع Exact استفاده کنیم. این تابع به حروف کوچک و بزرگ حساسیت داره.

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

شکل ۳- مقایسه داده متنی با عملگر منطقی و تابع Exact

مثال سوم: استفاده از عملگر مساوی در مقایسه داده صفر و یک

همیشه گفته میشه که مقدار عددی True  و False برابر است با یک و صفر. در حالیکه این موضوع همیشه درست نیست. یعنی اگر بخواهیم مقدار عددی عبارت های False/True رو برگردونیم باید تغییراتی انجام بدیم.

برای اینکه مقادیر False/True یه عنوان عدد در نظر گرفته بشن و مقایسه عددی داشته باشیم، باید از دو علامت – استفاده کنیم به اینصورت که یا پشت سلول=A2=–B2، یا پشت False/True ، =A2=–TRUE میذاریم. این دو علامت منفی، مقدار عددی رشته های متنی رو فراخوانی میکنند.

اولین علامت منفی مقادیر منطقی رو به ۰ و ۱- تبدیل میکنه و علامت منفی دوم، مقدار منفی رو به مثبت تبدیل میکنه. به شکل شماره ۴ دقت کنید.

مقایسه عبارات Logical

شکل ۴- مقایسه رشته های منطقی در اکسل

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

 

استفاده از عملگر “مخالف یا برابر نبودن” در اکسل

وقتی میخوایم بررسی کنیم که دو داده با هم برابر نباشن، از این عملگر (<>) استفاده میکنیم. منطق عملکرد این عملگر دقیقا مثل عملگر = هست و تنها تفاوت این هست که مخالف بودن رو چک میکنه.

عملکرد این عملگر رو در کنار استفاده از تابع NOT در شکل ۵ بررسی کنید:

عملگر مخالف بودن NOT تابع توضیح

=A1<>B1

=NOT(A1=B1)

اگر سلول A1 با B1 برابر نباشه، خروجی فرمول True خواهد بود، در غیر اینصورت False

=A1<>”اکسل پدیا”

=NOT(A1=”اکسل پدیا”)

اگر سلول A1  برابر با “اکسل پدیا” نباشه، خروجی فرمول True خواهد بود، در غیر اینصورت False

=A1<>TRUE

=NOT(A1=TRUE)

اگر سلول A1  برابر با TRUE نباشه، خروجی فرمول True خواهد بود، در غیر اینصورت False

=A1<>(B1/2)

=NOT(A1=B1/2)

اگر مقدار سلول A1 با مقدار سلول B1 تقسیم بر ۲ برابر نباشه، خروجی True خواهد بود

در مورد عملگرهای بزرگتر و کوچتر و …. هم  به همین صورت توضیحات قبل عمل میکنیم.

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

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

  • استفاده از عملگرهای منطقی در آرگومان های توابع اکسل

یکی از پرکاربردترین حالت استفاده از این عملگرها، به کاربردن آن به عنوان شرط منطقی یا logical test در تابع IF هست. به ساختار تابع IF دقت کنید:

=IF(A1>=B1, “قبول”, “مردود”)

اگر مقدار موجود در سلول A1 بزرگتر یا مساوی B1 باشه، عبارت “قبول” به عنوان خروجی فرمول خواهد بود در غیر اینصورت “مردود”.

 

  • استفاده از عملگرهای منطقی در اکسل برای فرمت دهی شرطی Conditional Formatting

استفاده از عملگرهای منطقی در اکسل برای فرمت دهی شرطی هم یکی دیگه از کاربردهای این عملگرهاست. چون خروجی این عملگرها True/False هست، این عبارات میتونه در قسمت فرمول نویسی در این ابزار مورد استفاده قرار بگیره. با نتیجه True، فرمت اعمال میشه، و با خروجی False فرمت اعمال نمیشه.

به یک مثال ساده درباره این موضوع دقت کنید:

یک مجموعه عدد داریم، میخوایم اگر عدد کوچکتر یا مساوی ۲۰ بود، کل ردیف نارنجی بشه و اگه بزرگتر از ۳۰ بود، کل ردیف سبز بشه. برای این کار محدوده مورد نظر رو انتخاب میکنیم و از مسیر Home/ Conditional formatting/ New Rule گزینه Use a formula to determine which cells to format رو انتخاب میکنیم و new rule رو میزنیم و طبق شکل ۵ شرط ها رو مینویسیم و فرمت مورد نظر رو انتخاب میکنیم.

فرمت دهی شرطی

شکل ۵- استفاده از عملگر منطقی در فرمت دهی شرطی

نکته:
برای رنگی شدن کل ردیف مورد نظر، به آدرس دهی $ در فرمول نوشته شده و محدوده Applies To در شکل ۵ دقت کنید.

 

برای مشاهده انواع حالت های ارجاع شرط با عملگرهای منطقی در فرمول، به شکل ۶ دقت کنید. چهار حالت ارجاع شرط به فرمول تشریح شده است. استفاده صحیح این چند حالت مخصوصا در توابع Countif و Sumif و … بسیار مهم هست.

فرمول نویسی و Logical Testsشکل ۶- استفاده از عملگر منطقی در فرمت دهی شرطی

چند تا مقاله مرتبط که پیشنهاد میکنم حتما بخونی:

133

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

دیدگاه کاربران
  • بهار ۱۹ فروردین ۱۴۰۲ / ۱۰:۱۲ ق٫ظ

    سلام چطوری میتونم یه ستون که تعداد زیادی عدد داره اعداد تکراری رنگی کنه ؟؟؟؟

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

      درود
      ستون رو انتخاب کنید و بعد:
      Home/ Conditional Formatting/ Highlight cells …../ Duplicate value

  • میثم ۱۷ خرداد ۱۴۰۱ / ۱۰:۲۷ ق٫ظ

    سلام . خسته نباشید . با کدوم تابع میتونم عملیات زیر رو انجام بدم : یه جدول دارم که ستون اول اسم روستاها و ستون دوم فاصله تا مرکز شهر رو داره . میخوام تابع کاری بکنه که اسم هر روستایی رو که نوشتم جلوش فاصله رو بیاره .

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

      درود
      تابع vlookup میتونه این کار و بکنه

  • محمد عارف ۱۰ مهر ۱۳۹۹ / ۷:۱۰ ب٫ظ

    سلام
    من میخوام با توجه به یک سلول اینطور بنویسم که اگه سلول مورد نظر کوچکتر از ۵۰ بود بنویسه مردود
    بالاتر از ۷۰ بود بنویسه قبول
    بین ۵۰ تا ۷۰ بنویسه نیاز به تلاش بیشتر

    با استفاده از IF تو در تو میخواستم انجام بدم
    ولی خطایvalue میده
    ایراد کارم کجاست ؟
    مرسی

    • آواتار
      حسنا خاکزاد ۲۸ مهر ۱۳۹۹ / ۱۲:۱۳ ب٫ظ

      درود
      برای اینکه مشخص بشه که مشککل فرمولتون کجاست باید فرمول رو بذارید تا بررسی بشه

  • hamid ۱۳ بهمن ۱۳۹۸ / ۱۰:۴۹ ب٫ظ

    با سلام

    لطفا فرمول با شرط زیر روجهت سلول M47 برام بنویسید . بسیار سپاسگزارم

    اگر مقدار سلول M47 مساوی یا بیشتر از ۱۶۰ بود نتیجه بشود ۱۰۰
    اگر مقدار سلول M47 کمتر از ۱۶۰ و بیشتر یا مساوی ۱۵۵ بود نتیجه بشود ۹۵
    اگر مقدار سلول M47 کمتر از ۱۵۵ و بیشتر یا مساوی ۱۵۰ بود نتیجه بشود ۹۰
    اگر مقدار سلول M47 کمتر از ۱۵۰ و بیشتر یا مساوی ۱۴۵ بود نتیجه بشود ۸۵

  • میلاد ۱۹ آبان ۱۳۹۸ / ۱:۲۲ ق٫ظ

    با عرض سلام وخسته نباشید
    میخواستم اعداد سلول هایی که در ستون Lهستند وقتی بزرگتر از اعداد ستون Mهستند را بشماره.عددها بسیار زیاد هستند.
    (COUNTIF(L:L,”>”&M:M این فرمولو نوشتم جواب نمیده.مگراینکه همشون بزرگتر باشند.لطفا راهنماییم کنین.

    • آواتار
      حسنا خاکزاد ۱۴ آذر ۱۳۹۸ / ۱۲:۲۹ ب٫ظ

      درود بر شما
      این فرمول رو بصورت آرایه ای ثبت کنید:

  • علی ۲ شهریور ۱۳۹۸ / ۱۱:۴۵ ق٫ظ

    سلام. من داخل سلول” ۱۹۰۸ Total”حروف را میخواهم جدا کنم فقط عدد بماند .لطفا راهنمایی کنید

    • آواتار
      حسنا خاکزاد ۵ شهریور ۱۳۹۸ / ۱۰:۳۱ ق٫ظ

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

      اگر هم ابزاری میخواید انجام بشه، با text to column, flash fill میتونید انجام بدید

  • جواد نوروزی ۴ اردیبهشت ۱۳۹۸ / ۸:۰۴ ب٫ظ

    سلام وقت بخیر
    مهندس سوالی داشتم از شما، ممنون میشم کمکم کنید.
    مسئله به این صورت است که میخواهم با گذاشتن چند شرط، قسمتی از داده های یک ستون رو انتخاب کرده و سپس از اون قسمت انتخاب شده چارک ها (Quartile) را بدست بیارم.
    داده ها بدین صورت است که دبی جریان ورودی به یک منطقه به صورت متوالی هر ۱۵ ثانیه به مدت یک ماه ثبت شده است، حال من میخوام که چارک داده های دبی جریان رو در بازه ای مشخص از زمان بدست بیارم.
    ممنون میشم کمکم کنید

    • آواتار
      حسنا خاکزاد ۴ اردیبهشت ۱۳۹۸ / ۸:۱۰ ب٫ظ

      درود بر شما
      تابع quartile در اکسل برای محاسبه چارک هاست. از همین استفاده کنید. آرگومان اول داده ها و آرگومان دوم چارک رو تعیین میکنه

      • جواد نوروزی ۴ اردیبهشت ۱۳۹۸ / ۸:۳۲ ب٫ظ

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

ارسال دیدگاه

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

توسط
تومان