چگونه در اکسل کمتر یا مساوی کنیم. توابع اکسل if (if) و ifs (ifs) برای چندین شرط

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

تابع IF در اکسل

تابع دارای نحو زیر است.

IF( log_expression; value_if_true; [value_if_false])

log_expression شرطی است که باید آزمایش شود. به عنوان مثال، A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.

value_if_true - یک مقدار یا فرمولی که هنگام رخ دادن رویداد مشخص شده در پارامتر اول برگردانده می شود.

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

یک مثال بسیار ساده باید بررسی کنید که آیا فروش تک تک محصولات بیش از 30 واحد است یا خیر. یا نه اگر از آنها بیشتر شد، فرمول باید "OK" را به داخل برگرداند در غیر این صورت- "حذف". در زیر محاسبه با نتیجه است.

فروش اولین محصول 75 است، یعنی. شرطی که بیشتر از 30 باشد برآورده می شود. بنابراین، تابع آنچه را که در فیلد بعدی - "Ok" مشخص شده است، برمی گرداند. فروش محصول دوم کمتر از 30 است، بنابراین شرط (>30) برآورده نمی شود و مقدار جایگزین مشخص شده در قسمت سوم برگردانده می شود. این تمام نکته تابع IF است. با کشش محاسبه به پایین، نتیجه را برای هر محصول می گیریم.

با این حال، این یک نسخه ی نمایشی بود. اغلب، فرمول Excel IF برای بررسی های پیچیده تر استفاده می شود. فرض کنید میانگین فروش هفتگی کالاها و موجودی فعلی آنها وجود دارد. خریدار باید یک پیش بینی از موجودی در 2 هفته انجام دهد. برای انجام این کار، باید دو برابر میانگین فروش هفتگی را از موجودی های فعلی کم کنید.

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

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

فرمول های Excel IF نیز به طور گسترده در فرمول های آرایه ای استفاده می شوند. ما در اینجا خیلی عمیق نمی رویم. برای علاقه مندان، خواندن مقاله در مورد. درست است، محاسبه در آن مقاله دیگر مرتبط نیست، زیرا اکسل 2016 توابع MINESLI و MAXESLI را معرفی کرد. اما نگاه کردن به مثال بسیار مفید است - در موقعیت دیگری مفید خواهد بود.

فرمول IF در اکسل - نمونه هایی از چندین شرط

اغلب تعداد شرایط ممکن 2 (قابل آزمایش و جایگزین) نیست، بلکه 3، 4 یا بیشتر است. در این حالت می توانید از تابع IF نیز استفاده کنید، اما اکنون باید آن را در داخل یکدیگر قرار دهید و تمام شرایط را به نوبت مشخص کنید. مثال زیر را در نظر بگیرید.

بسته به اجرای طرح فروش، به چندین مدیر فروش باید پاداش تعلق گیرد. سیستم انگیزش به شرح زیر است. اگر طرح کمتر از 90% انجام شود، هیچ پاداشی تعلق نمی گیرد، اگر از 90٪ تا 95٪ - پاداش 10٪، از 95٪ تا 100٪ - پاداش 20٪، و اگر از طرح تجاوز کرد، پس 30 درصد همانطور که می بینید در اینجا 4 گزینه وجود دارد. برای نشان دادن آنها در یک فرمول به موارد زیر نیاز دارید ساختار منطقی. اگر شرط اول برقرار باشد، گزینه اول رخ می دهد و در غیر این صورت، اگر شرط دوم برقرار باشد، گزینه دوم رخ می دهد، در غیر این صورت اگر ... و غیره. تعداد شرایط می تواند بسیار زیاد باشد. آخرین مورد در انتهای فرمول نشان داده شده است گزینه جایگزین، که برای آن هیچ یک از شرایط ذکر شده قبلی وجود ندارد (مانند قسمت سوم در فرمول IF معمولی). در نتیجه، فرمول به صورت زیر است:

ترکیب توابع IF به گونه ای کار می کند که وقتی هر شرط مشخصی برآورده می شود، موارد زیر دیگر بررسی نمی شوند. بنابراین، مهم است که آنها را به ترتیب صحیح نشان دهید. اگر شروع به بررسی با B2 کنیم<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.

هنگام نوشتن یک فرمول، گیج شدن آسان است، بنابراین بهتر است به راهنمای ابزار نگاه کنید.

در پایان باید تمام براکت ها را ببندید در غیر این صورت اکسل خطا می دهد.

تابع IF اکسل به طور کلی کار خود را به خوبی انجام می دهد. اما گزینه ای که نیاز به نوشتن یک زنجیره طولانی از شرایط دارید چندان خوشایند نیست، زیرا اولاً همیشه امکان نوشتن آن در اولین بار وجود ندارد (یا شرایط به اشتباه نشان داده شده است یا براکت نمی تواند بسته شود). ثانیاً، در صورت لزوم، درک چنین فرمولی می تواند دشوار باشد، به خصوص زمانی که شرایط زیادی وجود دارد و محاسبات خود پیچیده هستند.

MS Excel 2016 یک تابع اضافه کرده است IFS، که کل این مقاله به خاطر آن نوشته شده است. این همان IF است که فقط برای آزمایش بسیاری از شرایط به طور خاص تیز می شود. حالا دیگر نیازی نیست IF را صد بار بنویسید و پرانتزهای باز را بشمارید. کافی است شرایط را فهرست کنید و در آخر یک پرانتز ببندید.

به صورت زیر عمل می کند. بیایید مثال بالا را در نظر بگیریم و از فرمول جدید IFS اکسل استفاده کنیم.

همانطور که می بینید، نوشتن فرمول بسیار ساده تر و واضح تر به نظر می رسد.

ارزش توجه به موارد زیر را دارد. ما همچنان عبارات را به ترتیب صحیح فهرست می کنیم تا از همپوشانی محدوده غیر ضروری جلوگیری کنیم. آخرین شرط جایگزین، برخلاف IF معمولی، نیز باید مشخص شود. IF فقط یک مقدار جایگزین را مشخص می کند که اگر هیچ یک از شرایط ذکر شده برآورده نشود رخ می دهد. در اینجا باید خود شرط را مشخص کنید که در مورد ما B2>=1 خواهد بود. با این حال، می توان با نوشتن TRUE در فیلد شرط، از این امر جلوگیری کرد، که نشان می دهد اگر شرایط ذکر شده قبلی برآورده نشود، TRUE رخ می دهد و آخرین مقدار جایگزین بازگردانده می شود.

IF (expression_logical، value_if_true، value_if_false)

برای انجام این کار باید موارد زیر را انجام دهید:

1. روی نماد درج فرمول (Fx) کلیک کنید.
2. در منوی ظاهر شده، "IF" را انتخاب کنید.

1. در نتیجه، پنجره ای را خواهید دید که در آن باید آرگومان های تابع را مشخص کنید:
   • بیان بولی؛
   • ارزش اگر درست باشد؛
   • ارزش اگر نادرست باشد
2. پس از وارد کردن مقادیر در این فیلدها، یک نتیجه اولیه در سمت راست آنها نمایش داده می شود.
3. برای درج، روی دکمه "OK" کلیک کنید.

نمونه ها

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

1. سلول "A1" را فعال کنید.
2. مراحل توضیح داده شده در بالا را تکرار کنید تا پنجره درج "Arguments" باز شود.
3. کد زیر را در قسمت شرط وارد کنید.

معنی این عبارت به این صورت است: اگر سلول C1 حاوی یک فضای خالی باشد. یعنی چیزی در آن نیست.

1. در قسمت برای حقیقت، موارد زیر را وارد کنید.

این متنی است که در صورت رعایت این شرط نمایش داده می شود.

1. در قسمت "اگر نادرست" موارد زیر را وارد کنید.

اگر شرط نادرست باشد، این متن را خواهیم دید.

1. برای درج فرمول ما، روی دکمه "OK" کلیک کنید.

1. در نتیجه، موارد زیر را مشاهده خواهیم کرد (از آنجایی که سلول خالی است، پیام مناسب است).

1. هر متنی را در سلول C وارد کنید.

1. پس از زدن دکمه Enter نتیجه زیر را مشاهده خواهید کرد.

همانطور که می بینید، ویرایشگر اکسل پیامی را تولید می کند که در صورت وجود نتیجه نادرست شرط، آن را نشان دادیم.

اپراتورهای مقایسه

در زیر لیستی از عملگرهای مقایسه را مشاهده می کنید:

عملگرهای حسابی

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

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

شرایط چندگانه

در صورت تمایل می توانید از تعداد زیادی عبارات بولی استفاده کنید. همه چیز دقیقاً یکسان کار می کند. بیایید به مثالی از شرایط پیچیده تر نگاه کنیم.

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

برای این کار باید مراحل زیر را انجام دهید.

1. به تب "فرمول ها" بروید. بر روی دکمه "درج تابع" کلیک کنید.

1. در نتیجه، کل محتویات سلول به طور خودکار در "سازنده" جایگزین می شود.

1. همه موارد موجود در قسمت "Value_if_false" را حذف کنید.

1. حالا یک عبارت منطقی جدید در این خط نوشته می شود. یعنی یک شرط تو در تو جدید خواهیم داشت. ابتدا شرط "اگر مقدار سلول C1 بزرگتر از 1000 است" را وارد کنید. برای این کار کد زیر را وارد کنید. در پایان حتما یک نقطه ویرگول قرار دهید.

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

1. حالا بیایید پیامی اضافه کنیم که اگر عدد سلول C1 بیشتر از 1000 باشد نمایش داده می شود.

تمام مقادیر متن باید در نقل قول وارد شود.

1. حالا دقیقا به همین ترتیب در صورت عدم وجود این شرط مقدار را وارد می کنیم.

1. در نهایت بر روی دکمه “OK” کلیک کنید.

1. در نتیجه پیامی مبنی بر بزرگتر بودن عدد وارد شده از 1000 مشاهده می کنیم.

1. محتویات سلول C را حذف کنید. نتیجه به شرح زیر است.

ما هر سه نتیجه ممکن را آزمایش کردیم. همه چیز عالی کار می کند.

کپی کردن یک تابع در جداول

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

بیایید به نمونه ای از کپی کردن روی جدول پاداش برای کارکنان در طول تعطیلات نگاه کنیم. برای این کار باید مراحل زیر را انجام دهید.

1. جدول مناسب ایجاد کنید.

1. روی یک سلول خالی در ردیف اول کلیک کنید و در قسمت ورودی "Fx" را انتخاب کنید.

1. در پنجره ای که ظاهر می شود، تابع "IF" را انتخاب کرده و روی دکمه "OK" کلیک کنید.

1. در فیلد اول شرط زیر را وارد کنید.

به این ترتیب بررسی می کنیم که آیا کارمند مرد است یا خیر.

1. اگر درست است، یک عدد وارد کنید. به عنوان مثال 3000. اگر شرط نادرست است، 0 را وارد کنید. این بدان معنی است که دختران نیازی به پاداش ندارند. برای درج، روی دکمه "OK" کلیک کنید.

1. سپس، نشانگر خود را روی گوشه سمت راست پایین سلول نگه دارید. پس از اینکه مکان نما ظاهر خود را به "بلک پلاس" تغییر داد، بدون اینکه انگشت خود را رها کنید، آن را تا آخرین خط بکشید.

1. نتیجه به شرح زیر خواهد بود.

1. اکنون کل جدول را به طور کامل کپی کنید (با استفاده از میانبر صفحه کلید Ctrl + C و Ctrl + V). عنوان را به 8 مارس تغییر دهید.

1. به سلول اول بروید. روی فیلد ورودی فرمول کلیک کنید.

1. حرف "M" را به "F" تغییر دهید.

1. حالا تابع را تا پایین کپی کنید.

1. نتیجه به شرح زیر خواهد بود.

در اینجا می بینیم که کاملا برعکس اتفاق افتاده است. این بدان معنی است که همه چیز به درستی کار می کند.

استفاده از اپراتورهای اضافی

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

اول از همه، جدولی ایجاد کنید که دارای چندین فیلد باشد که بتوانید ردیف ها را با هم مقایسه کنید. در مورد ما، با استفاده از فیلد "وضعیت کارمند"، بررسی خواهیم کرد که چه کسی نیاز به پرداخت پول دارد و چه کسی ندارد.

اپراتور و

ما از تابع قدیمی استفاده خواهیم کرد که در آن بررسی کردیم که آیا کارمند مرد است یا خیر. حال باید تغییرات زیر را اعمال کنید.

1. یک پرانتز به شرط اضافه کنید و حرف "I" را در کنار آن اضافه کنید.
2. شرط قدیمی اولی خواهد بود و دومی بعد از نقطه ویرگول خواهد بود.

1. این فرمول را تا انتها کپی کنید.

1. در نتیجه شاهدیم که آن دسته از کارمندانی که جزو کارکنان اصلی نیستند از پاداش تعطیلات محروم خواهند شد. با اینکه مرد هستند.

اپراتور یا

همین اثر را می توان با استفاده از عملگر OR به دست آورد.

برای انجام این کار، تغییرات زیر را در فرمول اعمال کنید:

1. حرف "F" را به "M" تغییر دهید.
2. وضعیت کارمند را به "کارگر پاره وقت" تغییر دهید.
3. 0 را در قسمت true قرار دهید و 3000 را برای یک رویداد نادرست قرار دهید.

1. بیایید فرمول را تا آخرین خط کپی کنیم.

1. نتیجه دقیقاً یکسان بود. واقعیت این است که عملگرهای "AND" و "OR" دقیقا مخالف یکدیگر هستند. بنابراین، نشان دادن صحیح مقادیر در فیلدهای true و false بسیار مهم است. اشتباه نکنید.

1. برای بررسی، می توانید وضعیت یک کارمند را به "Primary Staff" تغییر دهید.
2. بلافاصله بعد از این خواهید دید که عدد 3000 مقابل نام خانوادگی او ظاهر می شود.

تابع SUMIF

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

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

توابع مختلف زیادی در اکسل وجود دارد که با بررسی شرایط منطقی کار می کنند. به عنوان مثال، اینها توابع IF، COUNTIF، SUMIF و غیره هستند. در صورت نیاز به دریافت پاسخ مثبت، شرایط منطقی را می توان در فرمول های معمولی نیز مشخص کرد: بلهیا خیر. به عنوان مثال، با پرسیدن شرایط منطقی ساده، می توانید به سوالات زیر پاسخ دهید:

• 5 بیشتر از 8 است؟
• آیا محتوای سلول A5 کمتر از 8 است؟
• یا شاید برابر با 8؟

مقایسه اپراتورها در اکسل

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

نحوه تنظیم شرط در اکسل

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

=A1=B1- اگر مقادیر سلول های A1 و B1 مساوی باشند، این شرط TRUE برمی گردد یا در غیر این صورت FALSE. با تنظیم این شرط، می توانید رشته های متنی را به روشی که به حروف بزرگ و کوچک حساس نیست مقایسه کنید. برای مثال، با مقایسه "JANUARY" و "January" فرمول TRUE برمی گردد.

=A1>B1- اگر مقدار سلول A1 بزرگتر از B1 باشد، فرمول زیر TRUE را برمی گرداند. در غیر این صورت، فرمول FALSE برمی گردد. چنین مقایسه هایی را می توان هنگام کار با متن نیز تنظیم کرد.

به عنوان مثال، اگر سلول A1 حاوی مقدار «نارنجی» و B1 حاوی «هندوانه» باشد، فرمول FALSE را برمی‌گرداند زیرا «هندوانه» به ترتیب حروف الفبا کمتر از «نارنجی» است. هر چه پایین تر، بیشتر.

=A1<=B1 – اگر مقدار سلول A1 کمتر یا مساوی با مقدار سلول B1 باشد، فرمول TRUE را برمی گرداند. در غیر این صورت نتیجه FALSE خواهد بود.

=A1<>B1- اگر مقادیر سلول های A1 و B1 برابر نباشند، فرمول TRUE را برمی گرداند. در غیر این صورت - FALSE.

توابع بولی در اکسل وجود دارد TRUE()و دروغ ()، که هیچ استدلالی ندارند. این ویژگی ها در درجه اول برای ارائه سازگاری با صفحات گسترده دیگر وجود دارند. شما می توانید مقادیر TRUE و FALSE را مستقیماً در سلول ها یا فرمول ها بدون استفاده از فرم نشانه گذاری تابع وارد کنید، اکسل همه چیز را به خوبی درک می کند.

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