سه فرمول مهم در مایکروسافت اکسل که کارهای شگفت‌انگیزی انجام می‌دهند

به‌کمک ابزارهای قدرتمند نرم افزار مایکروسافت اکسل (Microsoft Excel) می‌توان فرمول‌های پیچیده و شگفت‌انگیزی خلق کرد؛ در ادامه، برخی کاربردهای شگفت‌انگیز ابزارها و فرمول‌های اکسل را که ممکن است به‌اندازه‌ی کافی از آن‌ها آگاه نباشید، مطرح می‌کنیم.

نرم افزار مایکروسافت اکسل (Microsoft Excel) از قدرتمندترین ابزارهای صفحه گسترده (اسپریدشیت) است که مجموعه‌ی بسیار موثر از ویژگی‌ها و ابزارها را در خود دارد. در این مقاله، می‌آموزید که فرمول‌ها و قالب بندی شرطی (Conditional Formatting) در مایکروسافت اکسل چه قدرتی دارند. در این میان، سه مثال کاربردی نیز ارائه می‌کنیم.

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

قالب بندی شرطی با فرمول‌ها

یکی ابزارهایی که اغلب افراد به اندازه‌ی کافی از آن استفاده نمی‌کنند، قالب بندی شرطی یا Conditional Formatting است. با استفاده از قوانین، فرمول‌ها و تنظیماتی بسیار ساده در مایکروسافت اکسل، می‌توانید یک صفحه گسترده را به یک داشبورد خودکار تبدیل کنید.

برای دسترسی به قالب بندی شرطی، بر روی برگ (تب) Home کلیک کنید. سپس بر روی آیکون نوار ابزار Conditional Formatting (قالب بندی شرطی) کلیک کنید.

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

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

زمانی که بر روی گزینه‌ی Manage Rules کلیک کنید، بخش مدیریتی Conditional Formatting Rules Manager برای شما باز می‌شود. بسته به داده‌هایی که قبل از انتخاب icon set، انتخاب کرده‌اید؛ سلول مشخص شده را با همان icon set که انتخاب کردید، مشاهده می‌کنید.

زمانی که بر روی Edit Rule کلیک کنید، پنجره‌ای برای شما باز می‌شود؛ در این مکان می‌توانید فرمول منطقی و معادلات را ایجاد کنید که آیکون داشبورد مورد نظر شما را نمایش خواهد داد.

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

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

شما می‌توانید با استفاده از این قابلیت اکسل، زمان خود رابهتر مدیریت کنید.

کاربرد تابع VLookup اکسل

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

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

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

در مثال زیر، چگونه می‌توانیم از میان داده‌های موجود task یا کاری را که در تاریخ ۲۰۱۸/۶/۲۵ اجرا شده است، پیدا کنیم؟

"=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)"

در این حالت، شما در میان مقادیری در سمت راست در حال جستجو هستید و می‌خواهید داده‌ی متناظری را از سمت چپ (جهت مخالف) برگردانید.

اگر تالارهای گفتگوی علاقه‌مندان اکسل مایکروسافت را بخوانید، مشاهده می‌کنید که بسیاری از افراد می‌گویند این کار با تابع VLookup امکان پذیر نیست؛ و اینکه باید از ترکیبی از توابع Index  و Match  برای انجام این کار استفاده کنید. اما این گفته‌ها کاملا صحیح نیست.

شما می‌توانید با اضافه کردن یک تابع CHOOSE به تابع VLookup، این کار را انجام دهید. در این حال، فرمول اکسل به این صورت خواهد بود:

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

در این دستور، شما اشاره کردید که ستون ایندکس ستون شماره ۲ است؛ اما آن‌گونه که در جدول بالا می‌بینید، ستون مورد نظر در واقع ستون شماره یک است.

درست است؛ اما کاری که شما با استفاده از تابع CHOOSE انجام می‌دهید، دستکاری دو فیلد است.

در واقع، شما شماره‌ی ایندکس‌ها را به محدوده‌ای از داده‌ها ارجاع می‌دهید. در این مثال، فیلد date (تاریخ) index شماره یک و فیلد task (کار) index شماره ۲ است. به همین خاطر، زمانی که شما عدد ۲ را در تابع VLookup  نوشتید، در واقع به index  شماره ۲ در تابع CHOOSE  اشاره کرده‌اید.اکنون تابع VLookup  از ستون Date استفاده می‌کند و داده‌ای را از ستون Task برمی‌گرداند؛ اگرچه این ستون در سمت چپ قرار دارد.

اکنون که شما این کاربرد خوب را می‌دانید، تصور کنید که با کمک این تابع چه کارهای دیگری می‌توانید انجام بدهید!

فرمول‌های تو در تو برای تجزیه (پارس) رشته‌ها

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

زمانی که شما داده‌هایی از یک منبع خارجی (منبع غیر از اکسل) که شامل رشته‌ای از داده‌های محدود شده است، به اکسل مایکروسافت وارد (import) می‌کنید؛ ممکن است مواردی پیش بیاید.

پس از آن که شما داده‌های رشته‌ای را وارد کردید، می‌خواهید که داده‌ها را به اجزای تکی و منحصر به‌فردی تجزیه کنید. در این بخش با مثالی این مورد را توضیح می‌دهیم. در این مثال، اطلاعاتی شامل نام، آدرس و شماره تلفن که با کاراکتر سمیکالن (;) مجدود شده‌اند، ارائه شده است.

شما می‌توانید با استفاده از فرمول‌های اکسل، این اطلاعات را تجزیه یا اصطلاحا پارس (parse) کنید. برای فیلد اول، به منظور استخراج سمت چپ ترین آیتم (نام افراد)، ممکن است تنها از تابع LEFT  در فرمول استفاده کنید؛ که به صورت زیر است:

"=LEFT(A2,FIND(";",A2,1)-1)"

نحوه‌ی عملکرد منطقی این دستور به‌صورت زیر است:

• متن رشته را در A2 جستجو می‌کند.
• نماد محدودکننده‌ی سمیکالن (;) را پیدا می‌کند.
• برای موقعیت مناسب اخرین کاراکتر در بخش رشته یک واحد کسر می‌کند.
• از سمت چپ‌ترین بخش متن تا نقطه‌ی مورد نظر را در بر می‌گیرد.

در این مثال، سمت چپ‌ترین بخش متن کلمه‌ی Ryan است.

فرمول‌های تو در تو در اکسل

در این بخش می‌خواهیم پیچیده‌ترین فرمول‌های تو در توی ممکن را در اکسل ایجاد کنیم؛ باد توجه داشته باشید که راه‌های آسان‌تری نیز برای انجام این کارها وجود دارد. اما همه‌ی این فرمول‌های تو در تو و پیچیده از یک رویکرد واحد استفاده می‌کنند.

برای استخراج بخش‌هایی از متن از سمت راست، باید چندین تابع RIGHT  را به صورت تو در تو استفاده کنید که قسمت متن را تا رسیدن به نماد سمیکالن (;) در بر بگیرید. سپس تالع LEFT  را مجددا بر روی آن اجرا کنید. به عنوان مثال، برای استخراج بخش شماره خیابان در فیلد آدرس، باید از دستور زیر استفاده کنید:

"=LEFT((RIGHT(A2,LEN(A2)-FIND(";",A2))),FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))),1)-1)"

شاید عجیب به نظر برسد، اما اتصال مجدد این بخش‌ها به یکدیگر کار دشواری نیست. همه‌ی کاری که باید انجام داد، این است که تابع RIGHT  زیر را بگیریم و در جایگاه A2 قرار بدهیم.

RIGHT(A2,LEN(A2)-FIND(";",A2))

این تابع به درستی دومین بخش رشته را استخراج می‌کند.

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

فرمول نهایی به صورت زیر است:

"=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),FIND(";",(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),1)-1)"

این فرمول به درستی Portland, ME 04076 را از رشته‌ی اصلی استخراج می‌کند.

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

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

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

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