سه فرمول فوق ‌العاده اکسل برای انجام کارها به صورت حرفه‌ای

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

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

تا کنون روش های مختلفی برای استفاده بهتر از اکسل را به شما آموخته ایم که شامل موارد زیر بود:

9افزونه جذاب برای راحتی کار با اکسل
آموزش گام به گام برنامه نویسی VBA در اکسل
آموزش استفاده از دستور IF در مایکروسافت اکسل

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

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

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

Conditional Formatting شامل گزینه‌های متفاوتی است که اغلب آنها در این مطلب نمی گجند اما بیشتر این گزینه‌ها به برجسته سازی، رنگ بندی و سایه ‌زنی سلول‌ها بر اساس داده های آنها مربوط می شود.
اغلب کاربران هم در همین حد از قالب بندی شرطی استفاده می کنند که شامل مواردی نظیر رنگ بندی سلول ها بر اساس فرمول های منطقی کمتر یا بیشتر از یک مقدار است. یکی از ویژگی‌های قالب بندی شرطی که کمتر مورد استفاده قرار می‌گیرد، گزینهIcon Sets است که با ارائه مجموعه بزرگی از آیکون ها امکان تبدیل سلول داده به آیکون نمایش داشبورد را فراهم می سازد.

با کلیک روی Manage Rules وارد بخش Conditional Formatting Rules Manager می شوید. بسته به نوع داده انتخابی پیش از کلیک روی مجموعه آیکون، سلول انتخاب شده در پنجره‌ی مدیریت، قوانین را به شکل آیکون مورد نظر در آورید.

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

۱. جستجوی داده ها با استفاده از تابع VLookup

اگر به استفاده از توابع پیشرفته مایکروسافت اکسل علاقه دارید این بخش را از دست ندهید.
احتمالا با VLookup آشنا هستید و می دانید که این تابع به شما اجازه می‌دهد تا در یک لیست به دنبال آیتم خاصی در یک ستون گشته و مقدار موجود در ستون های مختلف آن سطر را به دست آورید.
یکی از محدودیت های این تابع این است که نام آیتم مورد جستجو باید در ستون سمت چپ و داده‌ مورد جستجو باید در سمت راست قرار داشته باشد. اما اگر اینگونه نباشد چه باید کرد؟
در مثال زیر می خواهیم کار انجام شده در تاریخ 6/25/2018 را از بین داده ها پیدا کنیم.

در این صورت باید در مقادیر سمت راست به دنبال آن گشته و مقدار متناظر در سمت چپ را برگردانید، روشی که کاملا برخلاف رویکرد عادی VLookup است.
در صورت مطالعه فروم های حرفه ای اکسل می بینید که به باور بسیاری از کاربران این کار با Vlookup امکان پذیر نیست و بدین منظور باید از ترکیب ایندکس و توابع Match استفاده کنید، هرچند این پیشنهاد کاملا درست نیست.
با قرار دادن یک تابع CHOOSE در Vlookup می توانید اینکار را صورت دهید. در این صورت فرمول اکسل شبیه کد زیر خواهد بود:

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

این معادله بدین معنی است که می خواهیم تاریخ 6/25/2018 را در لیست ورودی تابع Vlookup جستجو کرده و سپس مقدار متناظر را از ایندکس ستون برگردانیم. در این مورد می بینید که ایندکس ستون ۲ است اما در واقع شماره ستون در جدول فوق برابر ۱ است.

مسئولیت اصلی تابع CHOOSE در اینجا دستکاری این دو فیلد است. در این مثال شماره های ایندکس منبع را به محدوده ای از داده ها اختصاص داده اید، به عبارت دقیق تر تاریخ ها را به ایندکس شماره ۱ و وظایف را به ایندکس شماره ۲ منتسب کرده اید. بنابرین زمانی که ۲ را در تابع Vlookup وارد می کنید، در واقع به ایندکس شماره ۲ در تابع CHOOSE اشاره کرده اید.

بنابرین حالا Vlookup از ستون Date استفاده کرده و تاریخ از ستون Task برمیگرداند، هرچند Task در سمت چپ قرار دارد.

۲. فرمول های تو درتو برای تجزیه متون

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

برای استخراج فیلد اول که اولین مقدار از سمت چپ است (نام شخص) باید از تابع 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,LEN(A2)-FIND(";",A2)) و وارد کردن آن به جای مقادیر A2 در تابع فوق است. این کد بخش دوم متن را به صورت صحیح استخراج می کند.
هر بخش های دیگر از متن، نیاز به یک فرمول تو در توی دیگر دارد. حالا یک فرمول RIGHT ایجاد کرده و آن در به جای مقدار «A2» در تابع RIGHT فعلی قرار دهید. در ادامه فرمول شما به شکل زیر درمی آید:

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

حال باید این معادله را در فرمول اصلی 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 را از رشته اصلی استخراج می کند.

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

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

تسلط یافتن بر مایکروسافت اکسل

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

3 Crazy Excel Formulas That Do Amazing Things
دانلود مقاله