خیلی وقت ها محاسباتی که در اکسل انجام میدیم، مقایسه محتویات دو سلول با هم هست. اکسل چندین نوع عملگر برای انجام محاسبات مختلف داره. برای این موضوع هم شش عملگر منطقی در اکسل در نظر گرفته که بهشون عملگرهای مقایسه ای هم گفته میشه. در این آموزش سعی میکنیم منطق این عملگرها رو به خوبی درک کنیم و بهینه ترین فرمول ها رو برای تحلیل داده هامون بنویسیم.
عملگرهای منطقی که اکسل برای انجام مقایسه در اختیار کاربران قرار میده به صورت زیر هست:
- برابر با
- برابر نیست با
- بزرگتر از/ کوچکتر از/ بزرگتر یا مساوی/ کوچکتر یا مساوی
مروری بر عملگرهای منطقی در اکسل
عملگرهای منطقی در اکسل برای مقایسه دو داده مورد استفاده قرار میگیرن. این عملگرها، گاهی اوقات عملگرهای صفر و یک نامیده میشن. چون خروجی اونها در هر صورت یا 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
مثال سوم: استفاده از عملگر مساوی در مقایسه داده صفر و یک
همیشه گفته میشه که مقدار عددی True و False برابر است با یک و صفر. در حالیکه این موضوع همیشه درست نیست. یعنی اگر بخواهیم مقدار عددی عبارت های False/True رو برگردونیم باید تغییراتی انجام بدیم.
برای اینکه مقادیر False/True یه عنوان عدد در نظر گرفته بشن و مقایسه عددی داشته باشیم، باید از دو علامت – استفاده کنیم به اینصورت که یا پشت سلول=A2=–B2، یا پشت False/True ، =A2=–TRUE میذاریم. این دو علامت منفی، مقدار عددی رشته های متنی رو فراخوانی میکنند.
اولین علامت منفی مقادیر منطقی رو به ۰ و ۱- تبدیل میکنه و علامت منفی دوم، مقدار منفی رو به مثبت تبدیل میکنه. به شکل شماره ۴ دقت کنید.
شکل ۴- مقایسه رشته های منطقی در اکسل
وقتی از داده های منطقی صفر و یک در فرمول نویسی استفاده میکنیم، باید این علامت – – رو حتما قبلش بیاریم که به عدد تبدیل بشه و در محاسبات شناخته بشه.
استفاده از عملگر “مخالف یا برابر نبودن” در اکسل
وقتی میخوایم بررسی کنیم که دو داده با هم برابر نباشن، از این عملگر (<>) استفاده میکنیم. منطق عملکرد این عملگر دقیقا مثل عملگر = هست و تنها تفاوت این هست که مخالف بودن رو چک میکنه.
عملکرد این عملگر رو در کنار استفاده از تابع 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 و … بسیار مهم هست.
شکل ۶- استفاده از عملگر منطقی در فرمت دهی شرطی
چند تا مقاله مرتبط که پیشنهاد میکنم حتما بخونی:
سلام چطوری میتونم یه ستون که تعداد زیادی عدد داره اعداد تکراری رنگی کنه ؟؟؟؟
درود
ستون رو انتخاب کنید و بعد:
Home/ Conditional Formatting/ Highlight cells …../ Duplicate value
سلام . خسته نباشید . با کدوم تابع میتونم عملیات زیر رو انجام بدم : یه جدول دارم که ستون اول اسم روستاها و ستون دوم فاصله تا مرکز شهر رو داره . میخوام تابع کاری بکنه که اسم هر روستایی رو که نوشتم جلوش فاصله رو بیاره .
درود
تابع vlookup میتونه این کار و بکنه
سلام
من میخوام با توجه به یک سلول اینطور بنویسم که اگه سلول مورد نظر کوچکتر از ۵۰ بود بنویسه مردود
بالاتر از ۷۰ بود بنویسه قبول
بین ۵۰ تا ۷۰ بنویسه نیاز به تلاش بیشتر
با استفاده از IF تو در تو میخواستم انجام بدم
ولی خطایvalue میده
ایراد کارم کجاست ؟
مرسی
درود
برای اینکه مشخص بشه که مشککل فرمولتون کجاست باید فرمول رو بذارید تا بررسی بشه
با سلام
لطفا فرمول با شرط زیر روجهت سلول M47 برام بنویسید . بسیار سپاسگزارم
اگر مقدار سلول M47 مساوی یا بیشتر از ۱۶۰ بود نتیجه بشود ۱۰۰
اگر مقدار سلول M47 کمتر از ۱۶۰ و بیشتر یا مساوی ۱۵۵ بود نتیجه بشود ۹۵
اگر مقدار سلول M47 کمتر از ۱۵۵ و بیشتر یا مساوی ۱۵۰ بود نتیجه بشود ۹۰
اگر مقدار سلول M47 کمتر از ۱۵۰ و بیشتر یا مساوی ۱۴۵ بود نتیجه بشود ۸۵
درود بر شما
زحمت بکشید این مقالات رو مطالعه بفرمایید و شروع کنید به نوشتن فرمول. اشکال داشتید مطرح کنید. هدف اکسل پدیا آموزش هست
https://excelpedia.net/nested-if-functions/
https://excelpedia.net/and-or-function/
https://excelpedia.net/vlookup-interval-search/
با عرض سلام وخسته نباشید
میخواستم اعداد سلول هایی که در ستون Lهستند وقتی بزرگتر از اعداد ستون Mهستند را بشماره.عددها بسیار زیاد هستند.
(COUNTIF(L:L,”>”&M:M این فرمولو نوشتم جواب نمیده.مگراینکه همشون بزرگتر باشند.لطفا راهنماییم کنین.
درود بر شما
این فرمول رو بصورت آرایه ای ثبت کنید:
سلام. من داخل سلول” ۱۹۰۸ Total”حروف را میخواهم جدا کنم فقط عدد بماند .لطفا راهنمایی کنید
دروود بر شما
میتونید از تابع find استفاده کنید و جای اسپیس رو پیدا کنید و بعد تفکیک.
مقاله جدا کردن عدد از متن و بخونید و ایده بگیرید
اگر هم ابزاری میخواید انجام بشه، با text to column, flash fill میتونید انجام بدید
سلام وقت بخیر
مهندس سوالی داشتم از شما، ممنون میشم کمکم کنید.
مسئله به این صورت است که میخواهم با گذاشتن چند شرط، قسمتی از داده های یک ستون رو انتخاب کرده و سپس از اون قسمت انتخاب شده چارک ها (Quartile) را بدست بیارم.
داده ها بدین صورت است که دبی جریان ورودی به یک منطقه به صورت متوالی هر ۱۵ ثانیه به مدت یک ماه ثبت شده است، حال من میخوام که چارک داده های دبی جریان رو در بازه ای مشخص از زمان بدست بیارم.
ممنون میشم کمکم کنید
درود بر شما
تابع quartile در اکسل برای محاسبه چارک هاست. از همین استفاده کنید. آرگومان اول داده ها و آرگومان دوم چارک رو تعیین میکنه
سلام مرسی که قابل دونستید
ولی باید عرض کنم که سوال من نحوه جداسازی داده ها بود با گذاشتن شرط، نه این که چطوری چارک بگیرم
ممنون میشم در مورد این که چطوری میتونم فرمولی بنویسم که بشه باهاش قسمتی از داده ها رو جدا کرد، که بعد بشه روی اون قسمت، چارک ها رو بدست بیاریم
مرسی