فرمول نویسی در conditional formatting در اکسل

با فرمول نویسی در conditional formatting در اکسل به بسیاری از کارهای روزمره خود ، سرعت ببخشید. ابزار Conditional formatting در اکسل یک ابزار کاربردی برای تییر قالب یک سری سلول بر اساس یک شرط است. تصور کنید خودتان بتوانید یک سری شرط در این ابزار تعیین کنید و با استفاده از شروط تعیین شده قالب سلول ها را تغییر دهید.

ترکیب if و conditional formatting در اکسل یک ترکیب بسیار عالی برای مغایرت گیری و ایجاد شرط های منطقی در ابزار کاندیشنال فرمتینگ است . در این آموزش با برسی مثال هایی از فرمول نویسی در conditional formatting در اکسل این ترفند جذاب را با زبانی ساده به شما آموزش می دهیم.

فرمول نویسی در conditional formatting برای افرادی که در اکسل حرفه ای عمل می کنند کاربرد زیادی دارد . فرض کنید یک لیست بسیار طولانی از پرسنل با مشخصات فردی آنها دارید . می خواهید چک کنید که آیا کد ملی افراد به درستی وارد شده یا نه؟ فرمول نویسی در conditional formatting می تواند این برسی را به راحتی انجام دهد.

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

آموزش تصویری فرمول نویسی در conditional formatting

در آموزش تصویری فرمول نویسی در conditional formatting که به صورت ویدئو تهیه شده است شما با سه مثال مختلف و کاربردی فرمول نویسی در این ابزار را یاد می گیرید. یکی از گام های مهم در آموزش گام به گام اکسل ، یادگیری درست این روش است.

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

ترکیب if و conditional formatting

ترکیب if و conditional formatting یکی از مهمترین ترکیب ها برای فرمول نویسی در conditional formatting است. کاندیشنال فرمتینگ فرمول های منطقی را قبول می کند و فرمول IF در اکسل ، یکی از کاربردی ترین فرمول ها برای ترکیب با این ابزار است.

در کجای ابزار Conditional formatting می توان فرمول نوشت؟ برای وارد کردن فرمول به این ابزار وارد سربرگ Home می شویم و بر روی ابزار Conditional formatting کلیک می کنیم . در منوی باز شده بر روی New Rule کلیک کنید.

پس از کلیک برروی گزینه new rule پنجره زیر برای شما باز می شود. در ادامه به آموزش new rule در اکسل می پردازیم.

در پنجره New formatting rule بر روی گزینه آخرکه با فلش قرمز رنگ مشخص شده است کلیک کنید . در مستطیل سبز رنگ می توانید فرمول خود را بنویسید و با استفاده از قسمت Format می توانید استایل مورد نظر خودتان را ایجاد کنید . مهم ترین نکته در این جا همان متن بالای مستطیل سبز رنگ است. در این متن تاکید شده فرمت ایجاد شده زملنی بر روی سلول اجرا می شود که جواب فرمول شما True باشد.

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

مثال 1 در فرمول نویسی در conditional formatting

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

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

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

فرمول شرط ما به این صورت است که اگر سول A4 بزرگتر یا مساوی عدد 10 بود مقدار True برگرداند و اگر کمتر از 10 بود مقدار False برگرداند. در نهایت فرمول به صورت زیر نوشته می شود.

=IF(A4>=10,TRUE,FALSE)

اکنون فرمول ساخته شده را کپی می کنیم و سپس محدوده مورد نظر که در این جا نمرات هستند را انتخاب می کنیم وپنجره New rule را باز می کنیم. فرمول را در محل مربوطه به همراه مساوی پیست می کنیم. از قسمت format هم فرمت دلخواه خود را ایجاد می کنیم.

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

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

مثال 2 در فرمول نویسی در conditional formatting

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

فرض کنید در یک ستون ما تعدادی کد ملی داریم می خواهیم به صورت اتوماتیک چک شود که این کد ملی وارد شده ده رقم هست یا نیست.

مانند مثال قبل در سلول D4 شروع به فرمول نویسی می کنیم. می گوییم اگر تعداد کاراکتر های درون سلول C4 مساوی با 10 بود کاری نکن (False) در غیر این صورت ، یعنی یا بزگتر و یا کوچکتر از عدد 10 بود ، برای ما سلول را قرمز کن (True) . پس فرمول به صورت زیر نوشته می شود.

=IF(LEN(C4)=10,FALSE,TRUE)

فرمول را همانند مثال قبل در پنجره new rule پیست می کنید و فرمت مورد نظر را ایجاد می کنید . نکته ی مهم در این مثال این است که فرمول برای سلول های خالی هم مقدار True را بر می گرداند . برای این که از این اتفاق جلوگیری کنید می توانید ازتابع if چند شرطی در اکسل استفاده کنید.

مثال 3 در فرمول نویسی در conditional formatting

در این مثال می خواهیم با استفاده از فرمول نویسی در conditional formatting یک چک لیست بسازیم . برای ایجاد چک لیست به چک باکس احتیاج داریم که باید از سربرگ Developer  و قسمت Insert ایجاد شود.

در صورتی که شما این سربرگ را مشاهده نمی کنید به منوی File بروید و بر روی قسمت Option کلیک کنید. در پنجره Option قسمت custom ribbon تیک گزینه Developer را بزنید و بر روی Ok کلیک کنید.

قبل از انجام هر گونه عملیات نیاز هست که چک باکس ها با یک سلول لینک شوند. برای لینک کردن آنها بر رویچک باکس راست کلیک می کنیم و به قسمت Format control می رویم و در سربرگ Control قسمت Cell link سلول مورد نطر را انتخاب می کنیم و بر روی Ok کلیک می کنیم.

زمانی که چک باکس تیک داشته باشد در سلول G4 ، مقدار True برگردانده می شود و زمانی که تیک برداشته شود مقدار False را بر می گرداند. با همین دو مقداری که در سلول های ستون G ایجاد می شود می توانیم فرمول IF را ایجاد کنیم.

=IF(G4=TRUE,TRUE,FALSE)

فرمول را مانند مثال های قبل به new rule منتقل می کنیم و فرمت دلخواه را ایجاد می کنیم. در این مثال با تیک داشتن چک باکس فرمت بر روی ردیف مورد نظر ایجاد می شود و با برداشتن تیک فرمت به حالت قبل باز می گردد.

جمع بندی فرمول نویسی در conditional formatting

فرمول نویسی در conditional formatting یکی از قدرتمند ترین ترفند ها برای ساخت گزارشات حرفه ای به شمار می رود . همچنین در حل مغایرت های بوجود آوده در اکسل نیز می تواند بزرگترین نقش را ایفا کند.

شما عزیزان برای یادگیری بهتر فرمول نویسی در conditional formatting می توانید ویدئوی آموزشی را مشاهده کنید همچنین برای کسب اطلاعات بیشتر می توانید به مقاله مربوط به ابزار conditional Formatting در سایت رسمی آموزش مایکروسافت اکسل مراجعه کنید.

سوالات متداول