3 فرمول کاربردی در اکسل

فرمول های موجود در اکسل تقریباً می‌توانند هر کاری را انجام دهند. در این مقاله از مجله یوآفیس، با ۳ فرمول کاربردی در اکسل آشنا می‌شوید و می‌آموزید که چه اندازه فرمول‌های مایکروسافت اکسل و قالب بندی‌های شرطی می‌توانند قدرتمند باشند.

آشنایی با Microsoft Excel

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

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

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

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

در قالب بندی شرطی..

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

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

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

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

بنابراین باید مجدد، زمان بندی را تنظیم کرد. اما چگونه؟

1. با استفاده از تابع VLookup

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

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

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

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

“= VLOOKUP (DATE (2018،6،25) ، CHOOSE ({1،2} ، E2: E8 ، A2: A8) ، 2،0)”

این تابع به این معنی است که می‌خواهید تاریخ 2013/6/25 را در لیست جستجو پیدا کنید و سپس مقدار مربوطه را از ستون فهرست بازگردانید.

در این مورد، شما متوجه خواهید شد که شاخص همان ستون “2” است، اما همانطور که می‌بینید، ستون جدول فوق در واقع 1 است.

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

در واقع شما شماره‌های مرجع “Index” را به محدوده داده اختصاص می‌دهید، یعنی date را به فهرست شماره 1 و task را به فهرست شماره 2 اختصاص می‌دهید. بنابراین ، وقتی در تابع VLookup “2” را تایپ می‌کنید، در واقع به تابع CHOOSE به شماره 2 اشاره می‌کنید.

بنابراین از این پس تابع Vlookup از ستون Date استفاده می‌کند و داده‌ها را از ستون Task باز می‌گرداند، حتی اگر گزینه Task در سمت چپ باشد.

2. فرمول Nested برای Parse strings

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

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

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

“=LEFT(A2,FIND(“;”,A2,1)-1)”

نحوه عملکرد این تابع به شکل زیر است:

• Text string را از A2 جستجو می‌کند.
• نماد محدود کننده “؛” را پیدا می‌کند.
• سپس یکی از آنها را برای مکان مناسب انتهای آن بخش رشته جدا می‌کند
• و چپ‌ترین متن را تا آن نقطه گرب می‌کند.
• که در این مثال، سمت چپ‌ترین متن “Ryan” است.

3. فرمول Nested در اکسل

ممکن است روش‌های ساده‌تری برای انجام این کار وجود داشته باشد، اما از آنجا که می‌خواهیم سعی کنیم  فرمول Nested Excel را ایجاد کنیم باید از یک رویکرد منحصر به فرد استفاده کنیم.

برای اکسترکت کردن قسمت‌های سمت راست، باید چندین تابع RIGHT را تا آن قسمت از متن که به اولین نماد “؛” می‌رسد را grab کنید، و دوباره تابع LEFT را روی آن انجام دهید. در اینجا نحوه اکسترکت شماره خیابان در آدرس نشان داده شده است:

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

شاید به نظر سخت برسد ، اما ترکیب آن سخت نیست. تنها کاری که باید انجام دهید این است که این تابع را انجام دهید:

RIGHT(A2,LEN(A2)-FIND(“;”,A2))

و آن را در هر مکانی که در تابع LEFT، یک “A2” وجود دارد، وارد کنید. با این کار  قسمت دوم رشته نیز به درستی اکسترکت می‌شود.

به همین ترتیب هر قسمت دیگری از این رشته، که نیاز به ایجاد لانه دیگری دارد عمل کنید. اکنون تنها کاری که باید انجام دهید این است که معادله “RIGHT” را که در قسمت گذشته ایجاد کرده‌اید، grab کنید و آن را در یک فرمول RIGHT جدید با فرمول RIGHT قبلی که در آن “A2” را مشاهده کرده‌اید، ترکیب کنید. بنابراین در نهایت تابع زیر به وجود می‌آید:

(RIGHT((RIGHT(A2,LEN(A2)-FIND(“;”,A2))),LEN((RIGHT(A2,LEN(A2)-FIND(“;”,A2))))-FIND(“;”,(RIGHT(A2,LEN(A2)-FIND(“;”,A2))))))

سپس، شما باید فرمول THAT را بردارید و آن را در فرمول LEFT اصلی و هرجایی که “A2” وجود دارد، قرار دهید.

فرمول نهایی mind-bending به شکل زیر در می‌آید:

“=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 را انتخاب کنید. با این کار پنجره‌ای ظاهر می‌شود که در آن می‌توانید رشته‌ای را بر اساس هر محدود کننده‌ای که می خواهید تقسیم کنید. پس به سادگی نماد “؛” را وارد کنید، و سپس خواهید دید که پیش نمایش داده‌های انتخابی شما بر این اساس تغییر می‌کند.

نتیجه گیری

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