تابع IF در اکسل. مثالها (با چند شرط)

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

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

تابع IF در اکسل (اطلاعات عمومی)

هر برنامه ای، حتی اگر کوچک باشد، لزوماً شامل یک دنباله از اقدامات است که به آن الگوریتم می گویند. ممکن است به این شکل باشد:

1. کل ستون A را برای اعداد زوج بررسی کنید.
2. اگر یک عدد زوج پیدا شد، فلان مقادیر را اضافه کنید.
3. اگر عدد زوج پیدا نشد، کتیبه "یافت نشد" را نمایش دهید.
4. بررسی کنید که عدد حاصل زوج است یا خیر. 
5. اگر بله، آن را به تمام اعداد زوج انتخاب شده در پاراگراف 1 اضافه کنید.

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

نحو تابع IF با یک شرط

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

=IF (logical_expression، value_if_true، value_if_false)

بیایید سینتکس را با جزئیات بیشتری بررسی کنیم:

1. بیان بولی. این خود شرط است که انطباق یا عدم انطباق آن را اکسل بررسی می کند. هر دو اطلاعات عددی و متنی را می توان بررسی کرد.
2. مقدار_اگر_درست. نتیجه ای که اگر داده های در حال بررسی با معیارهای مشخص شده مطابقت داشته باشد در سلول نمایش داده می شود.
3. value_if_false. نتیجه ای که در صورت عدم تطابق داده های بررسی شده با شرایط در سلول نمایش داده می شود.

در اینجا یک مثال برای وضوح وجود دارد.

در اینجا تابع سلول A1 را با عدد 20 مقایسه می کند. این اولین پاراگراف نحو است. اگر محتوا بزرگتر از این مقدار باشد، مقدار "بیشتر از 20" در سلولی که فرمول نوشته شده است نمایش داده می شود. اگر وضعیت با این شرط مطابقت ندارد - "کمتر یا مساوی 20".

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

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

از آنجایی که می خواهیم متن را بررسی کنیم نه عدد، اولین آرگومان B2=”cons.” است.

نحو تابع IF با شرایط چندگانه

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

برای واضح تر شدن، در اینجا نحو آمده است.

=IF(expression_logical، value_if_true، IF(logical_expression، value_if_true، value_if_false))

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

در اینجا یک مثال است

و با کمک چنین فرمولی (در تصویر زیر نشان داده شده است) می توانید عملکرد هر دانش آموز را تجزیه و تحلیل کنید.

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

چگونه می توان عملکرد IF را با استفاده از عملگرهای AND و OR گسترش داد

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

تابع IF با شرط AND

گاهی اوقات لازم است یک عبارت را برای چند شرط به طور همزمان بررسی کنید. برای این کار از تابع AND استفاده می شود که در آرگومان اول تابع نوشته شده است IF. این کار به این صورت است: اگر a برابر یک و a برابر با 2 باشد، مقدار c خواهد بود.

تابع IF با شرط "OR".

تابع OR به روشی مشابه کار می کند، اما در این مورد، تنها یکی از شرایط صادق است. تا حد امکان می توان تا 30 شرایط را از این طریق بررسی کرد. 

در اینجا چند راه برای استفاده از توابع آورده شده است И и OR به عنوان آرگومان تابع IF.

مقایسه داده ها در دو جدول

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

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

فرض کنید دو جدول حاوی مشخصات دو غذاساز داریم. و ما باید آنها را با هم مقایسه کنیم و تفاوت ها را با رنگ برجسته کنیم. این را می توان با استفاده از قالب بندی شرطی و تابع انجام داد COUNTIF. 

جدول ما به این شکل است.

ما محدوده مربوط به مشخصات فنی اولین غذاساز را انتخاب می کنیم.

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

در قالب یک فرمول برای قالب بندی تابع را می نویسیم =COUNTIF (محدوده برای مقایسه؛ خانه اول جدول اول)=0. جدول با ویژگی های غذاساز دوم به عنوان محدوده مقایسه استفاده می شود.

باید مطمئن شوید که آدرس ها مطلق هستند (با علامت دلار در جلوی نام سطرها و ستون ها). 0= را بعد از فرمول اضافه کنید تا اکسل به دنبال مقادیر دقیق باشد.

پس از آن، باید قالب بندی سلول ها را تنظیم کنید. برای انجام این کار، در کنار نمونه، باید روی دکمه "Format" کلیک کنید. در مورد ما، ما از پر استفاده می کنیم، زیرا برای این منظور راحت ترین است. اما شما می توانید هر قالبی را که می خواهید انتخاب کنید.

ما یک نام ستون را به عنوان یک محدوده اختصاص داده ایم. این بسیار راحت تر از وارد کردن محدوده به صورت دستی است.

تابع SUMIF در اکسل

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

1. یک ردیف با مجموع درآمد همه فروشندگان اضافه کنید و پس از وارد کردن فرمول، روی سلولی که حاوی نتیجه است کلیک کنید. 
2. دکمه fx را پیدا می کنیم که در کنار خط فرمول ها قرار دارد. بعد، پنجره ای ظاهر می شود که در آن می توانید عملکرد لازم را از طریق جستجو پیدا کنید. پس از انتخاب اپراتور، باید روی دکمه "OK" کلیک کنید. اما ورودی دستی همیشه امکان پذیر است.
   

   

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

   

4. اولین آرگومان یک محدوده است. در اینجا سلول هایی را وارد می کنید که می خواهید مطابقت با معیارها را بررسی کنید. اگر در مورد ما صحبت کنیم، اینها جایگاه کارمندان است. محدوده D4:D18 را وارد کنید. یا فقط سلول های مورد نظر را انتخاب کنید.
5. در قسمت "معیارها" موقعیت را وارد کنید. در مورد ما - "فروشنده". به عنوان محدوده جمع بندی، سلول هایی را نشان می دهیم که حقوق کارمندان در آنها ذکر شده است (این کار به صورت دستی انجام می شود و آنها را با ماوس انتخاب می کنیم). روی "OK" کلیک کنید و دستمزد محاسبه شده تمام شده همه کارمندان فروشنده را دریافت می کنیم.

موافق باشید که بسیار راحت است. مگه نه؟

تابع SUMIFS در اکسل

این تابع به شما امکان می دهد مجموع مقادیری را که شرایط چندگانه را برآورده می کنند تعیین کنید. مثلاً به ما تکلیف شد که کل حقوق همه مدیران شاغل در شعبه جنوب شرکت را تعیین کنیم.

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

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

بیایید به استدلال های اصلی با جزئیات بیشتری نگاه کنیم:

1. محدوده جمع. سلول هایی که باید جمع شوند.
2. محدوده شرط 1 - محدوده ای که برای انطباق با یک معیار خاص بررسی می شود. 
3. شرط 1 خود شرایط است.
4. محدوده معیار 2 دومین محدوده ای است که با معیار بررسی می شود.
5. شرط 2 شرط دوم است.

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

تابع COUNTIF در اکسل

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

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

   

3. پس از آن تعداد فروشندگان شاغل در این سازمان را داریم. با شمارش تعداد سلول هایی که کلمه "فروشنده" در آنها نوشته شده است به دست آمد. همه چیز ساده است. 

تابع COUNTSLIM در اکسل

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

1. محدوده شرط 1. این محدوده ای است که با معیار اول آزمایش می شود.
2. شرط 1. به طور مستقیم اولین معیار.
3. محدوده شرط 2. این محدوده ای است که در برابر معیار دوم آزمایش می شود. 
4. شرط 2.
5. شرایط محدوده 3.

و به همین ترتیب.

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

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

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

عملکرد در دستان راست IF و تغییرات آن به شما امکان می دهد یک برگه اکسل را به یک برنامه کامل تبدیل کنید که می تواند بر روی الگوریتم های پیچیده عمل کند. درک نحوه عملکرد تابع IF اولین گام برای یادگیری ماکروها است - گام بعدی در کار انعطاف پذیرتر با صفحات گسترده. اما این در حال حاضر یک سطح حرفه ای تر است.