نرم‌افزار Excel یکی از نرم‌افزارهای قدرتمند مجموعه‌ی آفیس مایکروسافت است که در آن می‌توانید از توابع و ابزارهای زیادی برای پردازش داده و نمایش نتیجه‌ی تحلیل و بررسی‌ها استفاده کنید. یکی از ابزارهای مفید برای زیباتر و کاربردی‌تر کردن ورک‌شیت‌های اکسل، Conditional Formatting نام دارد که ظاهر سلول‌ها را با توجه به داده و فرمول‌ها و روابط متنوع تنظیم می‌کند.
در ادامه به معرفی این قابلیت و همین‌طور راهکاری برای جستجو کردن مقدار روبروی مقداری خاص در جداول با استفاده از VLookup می‌پردازیم که هر دو مورد از موارد پرکاربرد در اکسل هستند. با سیاره‌ی آی‌تی همراه شوید.
فرمت شرطی یا Conditional Formatting و تغییر ظاهر سلول‌ها با فرمول‌های مختلف
یکی از مهم‌ترین ابزارهای مفید و کاربردی نرم‌افزار Excel، فرمت کردن شرطی یا Conditional Formatting نام دارد که به کمک آن می‌توانید ظاهر سلول‌ها را با توجه به محتوا و فرمولی دلخواه تنظیم کنید. به عنوان مثال می‌توانید سلولی که عدد ۰ در آن قرار دارد را قرمز و سلولی که حاوی عدد بزرگ‌تر از ۱۵ است را سبز کنید یا حالت‌های پیچیده‌تری مثل مشخص کردن چند بازه و انتخاب یک رنگ برای هر بازه‌ی اعداد را در نظر بگیرید.
برای شروع کار با ابزار Conditional Formatting اکسل، روی گزینه‌ی مربوط به آن در تب Home از نوار ابزار بالای صفحه کلیک کنید و در بخش Style دکمه‌ی مربوط به آن را مشاهده خواهید کرد.
با کلیک روی گزینه‌ی Conditional Formatting یک منوی کرکره‌ای باز می‌شود و تعدادی رنگ‌بندی و استایل از پیش‌تعریف‌شده در اختیار شما قرار می‌گیرد. به عنوان مثال اگر Data Bars را انتخاب کنید و چند سلول را انتخاب کنید، نمودار داخل هر سلول بسته به عدد داخل سلول، کوچک یا بزرگ خواهد بود و البته رنگ آن قابل انتخاب است. به همین ترتیب می‌توانید از Icon Sets استفاده کنید که بسته به عدد داخل سلول، فلش رنگی با جهت‌های مختلف نمایش می‌دهد.
علاوه بر گزینه‌های از پیش‌تعریف‌شده و در واقع ساده، می‌توانید روی گزینه‌ی New Rule کلیک کنید و رابطه‌ای دلخواه تعریف کنید. به همین صورت می‌توانید روی Manage Rules کلیک کنید و سپس در پنجره‌ی Conditional Formatting Rules Manager، قواعدی که قبلاً برای سلول‌ها و ظاهرشان تعریف کرده‌اید را ویرایش کنید.
همان طور که در تصویر زیر مشاهده می‌کنید، در پنجره‌ی Conditional Formatting Rules Manager لیستی از تمام قواعد تعریف شده برای تغییر ظاهر سلول‌ها موجود است. می‌توانید قاعده‌ای را انتخاب کرده و روی دکمه‌ی Edit Rule بالای لیست کلیک کنید و آن را ویرایش کنید و می‌توانید با استفاده از دکمه‌ی Delete Rule آن را حذف کنید. به علاوه دکمه‌ی ایجاد قاعده‌ی جدید یا New Rule نیز بالای لیست موجود است.
حین ایجاد قانونی جدید برای تنظیم ظاهر سلول‌ها یا به زبان دیگر، Format سلول، انواع و اقسام قواعد در دسته‌های مختلف وجود دارد. حالت پیش‌فرض گزینه‌ی Format all cells based on their values است که ظاهر سلول‌ها را بر اساس محتویاتشان تنظیم می‌کند اما گزینه‌های دیگری نیز وجود دارد. به عنوان مثال برای فرمت کردن سلول‌هایی که مقداری خاص دارند از گزینه‌ی Format only cells that contain استفاده کنید و برای فرمت کردن سلول‌هایی که یونیک یا غیرتکراری و یا برعکس، مقدار تکراری دارند، از Format only unique or duplicate values استفاده کنید.
پس از انتخاب نوع قانون فرمت کردن سلول‌ها، می‌بایست مشخصات ظاهری و روابط موردنظر را وارد کنید که تنوع زیادی در این بخش وجود دارد و کارهای متنوعی می‌توان انجام داد.
استفاده از تابع VLookup در اکسل
یکی از کاربردی‌ترین توابع اکسل، VLookup نام دارد که شاید در نگاه اول پیچیده به نظر برسد اما در کاربردهای بسیار زیادی می‌توان از آن استفاده کرد.
با تابع VLookup می‌توانید لیستی که در یک ستون قرار گرفته را سرچ کنید و داده‌ای که روبروی سلول موردبحث و در ستون دیگری قرار دارد را استخراج کنید. دقت کنید که لیستی که در آن سرچ می‌کنید می‌بایست در سمت چپ قرار داشته باشد و البته اگر صفحه‌ی اکسل شما به صورت راست به چپ تعریف شده، در سمت راست قرار داشته باشد.
به عنوان مثال اگر بخواهید تاریخ نوشتن مقاله‌ای با عنوان خاص را در یک فایل اکسل مثل نمونه‌ی زیر پیدا کنید، می‌توانید به سادگی از تابع VLookup استفاده کنید چرا که ترتیب به همین صورت است اما اگر بخواهید برعکس این کار را انجام بدهید و به عبارت دیگر تاریخ را مشخص کنید و کاری که در آن روز صورت گرفته را پیدا کنید چطور؟ می‌بایست ستون عنوان مقالات و کارها در سمت راست قرار گرفته باشد، درست برعکس حالتی که در ایجاد این صفحه‌ی اکسل در نظر گرفته شده است:
برخی کاربران حرفه‌ای Excel سریعاً می‌گویند که باید جای دو ستون را تغییر بدهید و یا از ترکیب توابع Index و Match استفاده کنید اما یک راهکار ساده‌ی دیگر هم برای این نوع سرچ کردن وجود دارد و آن استفاده کردن از تابع Choose است.
به عنوان مثال اگر از فرمول زیر استفاده کنید، ابتدا تاریخ موردنظر در لیست تاریخ‌ها که ستون Date است جستجو می‌شود و سپس مقدار روبروی این تاریخ از سلول‌های موجود در ستون A استخراج می‌شود.
=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)
تابع CHOOSE در حقیقت ستون E را به عنوان اولین رنج با عدد ۱ و ستون A را به عنوان دومین ستون با عدد ۲ در نظر می‌گیرد و زمانی که در تابع VLookup از عدد ۲ و سپس ۰ استفاده می‌کنید، به این معنی است که به ستون A اشاره کرده‌اید.
به این ترتیب با استفاده از VLookup و Choose می‌توانید مقدار سلول روبرو یا قبل از سلولی را به سادگی پیدا کنید و جهت مهم نیست.
makeuseofسیاره‌ی آی‌تی