اگر بیانیه - شرایط متعدد در یک فرمول واحد را در تو قرار دهید.

توسط Svetlana Cheusheva ، به روز شده در 20 مارس 2023

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

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

Microsoft Excel محدودیت هایی در سطح IFS تو در تو دارد. در اکسل 2003 و پایین تر ، تا 7 سطح مجاز بود. در اکسل 2007 و بالاتر ، در صورت عملکرد در یک فرمول می توانید حداکثر 64 را لانه کنید.

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

مثال 1. در صورتی که فرمول کلاسیک تو در تو باشد

در اینجا یک نمونه معمولی از اکسل در صورت داشتن شرایط متعدد آورده شده است. به فرض شما لیستی از دانش آموزان در ستون A و نمرات امتحان آنها در ستون B دارید و می خواهید نمرات را با شرایط زیر طبقه بندی کنید:

• عالی: بیش از 249
• خوب: بین 249 تا 200 ، فراگیر
• رضایت بخش: بین 199 تا 150 ، فراگیر
• فقیر: زیر 150

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

=IF(B2>249, "Excellent", IF(B2>=200, "Good", IF(B2>150 ، "رضایت بخش" ، "فقیر")))

و دقیقاً همانطور که باید کار می کند:

درک اگر منطق است

من شنیده ام که برخی از مردم می گویند اگر آنها را دیوانه می کند چند برابر می کند :) سعی کنید با زاویه دیگری به آن نگاه کنید:

آنچه در واقع فرمول در واقع به اکسل می گوید ، ارزیابی Logical_Test از اولین عملکرد است و اگر شرط برآورده شود ، مقدار عرضه شده در آرگومان Value_if_true را برگردانید. اگر شرط اول اگر عملکرد برآورده نشود ، عبارت 2 را آزمایش کنید و غیره.

IF( check if B2>=249, if true - retu "Excellent", or else IF( check if B2>=200, if true - retu "Good", or else IF( check if B2>150 ، اگر درست باشد - "رضایت بخش" را برگردانید ، اگر نادرست است - "فقیر" را برگردانید)))

مثال 2. چند برابر اگر با محاسبات حسابی

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

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

با فرض اینکه کاربر در سلول B8 وارد مقدار شود ، فرمول به شرح زیر است:

=B8*IF(B8>=101, 12, IF(B8>=50, 13, IF(B8>=20, 16, IF( B8>=11, 18, IF(B8>= 1 ، 20 ، "")))))

و نتیجه چیزی شبیه به این خواهد بود:

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

به عنوان مثال ، به جای "کدگذاری سخت" قیمت ها در فرمول ، می توانید سلولهای حاوی آن مقادیر را ارجاع دهید (سلولهای B2 تا B6). این کار کاربران شما را قادر می سازد بدون نیاز به به روزرسانی فرمول ، داده های منبع را ویرایش کنند:

=B8*IF(B8>=101,B6, IF(B8>=50, B5, IF(B8>=20, B4, IF( B8>=11, B3, IF(B8>= 1 ، b2 ، "")))))

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

=IF(OR(B8>200 ، B8<1), "Qty. out of range", B8*IF(B8>=101,12, IF(B8>=50, 13, IF(B8>=20, 16, IF( B8>=11, 18, IF(B8>= 1 ، 20 ، ""))))))

اگر فرمول هایی که در بالا توضیح داده شد در تمام نسخه های اکسل کار می کند. در اکسل 365 و اکسل 2021 ، می توانید از عملکرد IFS برای همین منظور نیز استفاده کنید.

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

اگر - نکات و ترفندها

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

اگر محدودیت باشد

در اکسل 2007 - اکسل 365 ، در صورت توابع می توانید حداکثر 64 لانه کنید. در نسخه های قدیمی Excel 2003 و پایین تر ، در صورت استفاده از توابع ، تا 7 تو در تو در تو استفاده می شود. با این حال ، این واقعیت که شما می توانید IF های زیادی را در یک فرمول لانه کنید ، به این معنی نیست که شما باید. لطفاً به خاطر داشته باشید که هر سطح اضافی فرمول شما را درک و عیب یابی دشوارتر می کند. اگر فرمول شما دارای سطح تو در تو است ، ممکن است بخواهید با استفاده از یکی از این گزینه ها ، آن را بهینه کنید.

ترتیب توخالی در صورتی که توابع مهم باشد

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

Let's see how it works in practice. With B2 equal to 274, the nested IF formula below evaluates the first logical test (B2>249) ، و "عالی" را برمی گرداند زیرا این آزمون منطقی درست است:

=IF(B2>249, "Excellent", IF(B2>=200, "Good", IF(B2>150 ، "رضایت بخش" ، "فقیر")))

حال ، بیایید ترتیب IF را معکوس کنیم:

=IF(B2>150, "Satisfactory", IF(B2>200, "Good", IF(B2>249 ، "عالی" ، "فقیر")))

فرمول شرط اول را آزمایش می کند و از آنجا که 274 بیشتر از 150 است ، نتیجه این آزمایش منطقی نیز صحیح است. در نتیجه ، فرمول بدون آزمایش شرایط دیگر "رضایت بخش" باز می گردد.

می بینید ، تغییر ترتیب IF توابع نتیجه را تغییر می دهد:

منطق فرمول را ارزیابی کنید

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

For example, the evaluation of the first logical test of the nested IF formula shown in the screenshot below will go as follows: B2>249; 274>249 ؛درست است، واقعی؛عالی

در صورت توابع ، پرانتز تو در تو را متعادل کنید

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

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

با متن و اعداد متفاوت رفتار کنید

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

Right: =IF(B2>249 ، "عالی" ، ...)

Wrong: =IF(B2>"249" ، "عالی" ، ...)

آزمون منطقی فرمول دوم حتی اگر مقدار B2 از 249 بیشتر باشد ، کاذب باز می گردد. چرا؟از آنجا که 249 یک عدد است و "249" یک رشته عددی است که دو چیز متفاوت است.

فضای یا استراحت های خط را اضافه کنید تا خواندن آن در تو در تو آسان شود

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

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

گزینه های دیگری برای تو در تو در اکسل

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

1. برای آزمایش چندین شرایط و بازگشت مقادیر مختلف بر اساس نتایج آن تست ها ، می توانید به جای IFS تو در تو از عملکرد انتخاب کنید.
2. یک جدول مرجع و استفاده از VLOOKUP را با مطابقت تقریبی همانطور که در این مثال نشان داده شده است بسازید: Vlookup به جای اینکه در اکسل قرار بگیرد.
3. اگر با توابع منطقی یا / و ، همانطور که در این مثالها نشان داده شده است استفاده کنید.
4. از یک فرمول آرایه مانند نشان داده شده در این مثال استفاده کنید.
5. اگر اظهارات را با استفاده از تابع Concatenate یا اپراتور Concatenate (&) ترکیب کنید ، چندین ترکیب را ترکیب کنید. یک مثال فرمول را می توان در اینجا یافت.
6. برای کاربران با تجربه Excel ، بهترین گزینه برای استفاده از چندین تو در تو در صورت توابع ممکن است یک عملکرد صفحه سفارشی با استفاده از VBA ایجاد کند.

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

کتاب کار را برای بارگیری تمرین کنید

شما همچنین ممکن است به آن علاقه مند باشید

• اکسل اگر عملکرد: فرمول برای اعداد ، متن ، تاریخ ، سلول های خالی
• اکسل اپراتورهای منطقی: برابر ، برابر با ، بیشتر از ، کمتر از
• با استفاده از توابع منطقی در اکسل: و ، یا ، XOR و نه
• Vlookup با IF بیانیه در اکسل
• اکسل: اگر سلول حاوی آن باشد ، شمارش ، جمع ، برجسته ، کپی یا حذف
• نحوه استفاده از Countifs و Countif با معیارهای متعدد