این فرمول های مایکروسافت اکسل می توانند بهره وری صفحه گسترده شما را افزایش دهند و کارها را کمی سرگرم کننده تر کنند.
فرمول های مایکروسافت اکسل تقریباً هر کاری را می توانند انجام دهند. در این مقاله با سه مثال مفید خواهید آموخت که فرمول های مایکروسافت اکسل و قالب بندی شرطی چقدر می توانند قدرتمند باشند.
آشنایی با مایکروسافت اکسل
ما چندین روش مختلف را برای استفاده بهتر از اکسل توضیح داده‌ایم، از جمله اینکه کجا می‌توان قالب‌های عالی قابل دانلود اکسل را پیدا کرد و چگونه از اکسل به عنوان ابزار مدیریت پروژه استفاده کرد.
بخش عمده ای از قدرت اکسل در پشت فرمول ها و قوانین اکسل نهفته است که به شما کمک می کند تا داده ها و اطلاعات را به طور خودکار دستکاری کنید، صرف نظر از اینکه چه داده هایی را در صفحه گسترده وارد می کنید.
بیایید بررسی کنیم که چگونه می توانید از فرمول ها و ابزارهای دیگر برای استفاده بهتر از Microsoft Excel استفاده کنید.
قالب بندی شرطی با فرمول های اکسل
یکی از ابزارهایی که مردم به اندازه کافی از آن استفاده نمی کنند، Conditional Formatting است. با استفاده از فرمول های اکسل، قوانین، یا فقط چند تنظیمات بسیار ساده، می توانید یک صفحه گسترده را به یک داشبورد خودکار تبدیل کنید.
برای رسیدن به Conditional Formatting کافیست روی تب Home کلیک کنید و روی نماد نوار ابزار Conditional Formatting کلیک کنید.
در زیر قالب بندی شرطی، گزینه های زیادی وجود دارد. بسیاری از این موارد خارج از محدوده این مقاله خاص هستند، اما اکثر آنها در مورد برجسته کردن، رنگ آمیزی یا سایه زدن سلول ها بر اساس داده های درون آن سلول هستند.
این احتمالاً رایج‌ترین استفاده از قالب‌بندی شرطی است – مواردی مانند قرمز کردن سلول با استفاده از فرمول‌های کمتر یا بیشتر. درباره نحوه استفاده از دستورات IF در اکسل بیشتر بیاموزید.
یکی از ابزارهای قالب بندی شرطی که کمتر مورد استفاده قرار می گیرد، گزینه Icon Sets است که مجموعه ای عالی از نمادها را ارائه می دهد که می توانید از آنها برای تبدیل سلول داده اکسل به نماد نمایش داشبورد استفاده کنید.
هنگامی که روی Manage Rules کلیک می کنید، شما را به Conditional Formatting Rules Manager هدایت می کند.
بسته به داده‌هایی که قبل از انتخاب مجموعه نماد انتخاب کرده‌اید، سلولی را که در پنجره مدیر نشان داده شده است را با مجموعه نمادهایی که انتخاب کرده‌اید مشاهده خواهید کرد.
وقتی روی Edit Rule کلیک می کنید، کادر گفتگوی جایی که جادو اتفاق می افتد را می بینید.
اینجاست که می توانید فرمول منطقی و معادلات را ایجاد کنید که آیکون داشبورد مورد نظر شما را نمایش می دهد.
این داشبورد نمونه زمان صرف شده برای کارهای مختلف را در مقایسه با زمان بودجه نشان می دهد. اگر بیش از نیمی از بودجه را پیش ببرید، یک چراغ زرد نشان داده می شود. اگر بودجه شما کاملاً زیاد است، قرمز می شود.
همانطور که می بینید، این داشبورد نشان می دهد که بودجه بندی زمانی موفقیت آمیز نیست.
تقریباً نیمی از زمان بیش از مبالغ بودجه ای صرف می شود.
وقت آن است که دوباره تمرکز کنید و زمان خود را بهتر مدیریت کنید!
1. با استفاده از تابع VLookup
اگر می‌خواهید از توابع پیشرفته‌تر مایکروسافت اکسل استفاده کنید، در اینجا چند مورد برای شما وجود دارد که می‌توانید امتحان کنید.
احتمالاً با تابع VLookup آشنا هستید، که به شما امکان می‌دهد فهرستی را برای یک مورد خاص در یک ستون جستجو کنید و داده‌ها را از یک ستون دیگر در همان ردیف آن مورد برگردانید.
متأسفانه، این تابع مستلزم آن است که موردی که در لیست جستجو می‌کنید در ستون سمت چپ باشد و داده‌هایی که به دنبال آن هستید در سمت راست باشد، اما اگر آنها تغییر کنند چه می‌شود؟
در مثال زیر، اگر بخواهم وظیفه ای را که در تاریخ 25/6/2018 انجام دادم از داده های زیر پیدا کنم، چطور؟
در این حالت، شما در حال جستجو در مقادیر سمت راست هستید و می خواهید مقدار مربوطه را در سمت چپ برگردانید.
اگر انجمن‌های مایکروسافت اکسل طرفدار کاربر را بخوانید، افراد زیادی را خواهید دید که می‌گویند این کار با VLookup امکان‌پذیر نیست. برای این کار باید از ترکیبی از توابع Index و Match استفاده کنید. این کاملا درست نیست.
می‌توانید با قرار دادن یک تابع CHOOSE در آن، VLookup را به این روش کار کنید. در این مورد، فرمول اکسل به شکل زیر خواهد بود:
"=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)"
این تابع به این معنی است که شما می خواهید تاریخ 25/6/2013 را در لیست جستجو پیدا کنید و سپس مقدار مربوطه را از فهرست ستون برگردانید.
در این مورد، متوجه خواهید شد که شاخص ستون “2” است، اما همانطور که می بینید، ستون در جدول بالا در واقع 1 است، درست است؟
این درست است، اما کاری که شما با تابع “CHOOSE” انجام می دهید، دستکاری دو فیلد است.
شما اعداد «شاخص» مرجع را به محدوده‌ای از داده‌ها اختصاص می‌دهید – تاریخ‌ها را به فهرست شماره 1 و وظایف را به فهرست شماره 2 اختصاص می‌دهید.
بنابراین، وقتی “2” را در تابع VLookup تایپ می کنید، در واقع به Index شماره 2 در تابع CHOOSE اشاره می کنید. باحال، درسته؟
VLookup اکنون از ستون Date استفاده می کند و داده ها را از ستون Task برمی گرداند، حتی اگر Task در سمت چپ باشد.
حالا که این نکته کوچک را می دانید، فقط تصور کنید چه کارهای دیگری می توانید انجام دهید!
اگر می‌خواهید سایر کارهای پیشرفته جستجوی داده را انجام دهید، این مقاله در مورد یافتن داده‌ها در اکسل با استفاده از توابع جستجو را بررسی کنید.
2. فرمول تودرتو برای تجزیه رشته ها
در اینجا یک فرمول دیوانه وار اکسل برای شما وجود دارد! ممکن است مواردی وجود داشته باشد که داده‌ها را از یک منبع خارجی شامل رشته‌ای از داده‌های محدود شده به مایکروسافت اکسل وارد کنید.
هنگامی که داده ها را وارد کردید، می خواهید آن داده ها را به اجزای جداگانه تجزیه کنید. در اینجا نمونه ای از اطلاعات نام، آدرس و شماره تلفن است که با علامت “;” مشخص شده است. شخصیت.
در اینجا نحوه تجزیه و تحلیل این اطلاعات با استفاده از فرمول اکسل آورده شده است (ببینید آیا می توانید به صورت ذهنی این جنون را دنبال کنید یا خیر):
برای فیلد اول، برای استخراج سمت چپ ترین آیتم (نام شخص)، به سادگی می توانید از یک تابع LEFT در فرمول استفاده کنید.
"=LEFT(A2,FIND(";",A2,1)-1)"
در اینجا نحوه عملکرد این منطق آمده است:
- رشته متن را از A2 جستجو می کند
- “;” را پیدا می کند نماد جداکننده
- یک را برای مکان مناسب انتهای آن بخش رشته کم می کند
- سمت چپ ترین متن را تا آن نقطه می گیرد
در این مورد، سمت چپ ترین متن «رایان» است. ماموریت انجام شد.
3. فرمول تودرتو در اکسل
اما بخش های دیگر چطور؟
ممکن است راه‌های ساده‌تری برای انجام این کار وجود داشته باشد، اما از آنجایی که می‌خواهیم دیوانه‌کننده‌ترین فرمول اکسل تودرتوی ممکن را ایجاد کنیم (که در واقع کار می‌کند)، از یک رویکرد منحصربه‌فرد استفاده می‌کنیم.
برای استخراج قسمت‌های سمت راست، باید چندین تابع RIGHT را در تودرتو قرار دهید تا بخش متن را تا اولین “;” بالا ببرید. نماد، و دوباره تابع 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” را که در بخش آخر ایجاد کردید، بردارید و آن را در یک فرمول RIGHT جدید با فرمول RIGHT قبلی که در آن جاگذاری شده است، جایی که “A2” را مشاهده می کنید، قرار دهید. در اینجا به نظر می رسد.
(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 را انتخاب کنید.
با این کار پنجره ای ظاهر می شود که در آن می توانید رشته را با هر جدا کننده ای که می خواهید تقسیم کنید. به سادگی “;” را وارد کنید و خواهید دید که پیش نمایش داده های انتخابی شما بر این اساس تغییر می کند.
با چند کلیک، می‌توانید همان کار فرمول دیوانه‌وار بالا را انجام دهید… اما لذت این کار کجاست؟
دیوانه شدن با فرمول های مایکروسافت اکسل
خب! حالا شما مالک آن هستید. فرمول‌های بالا ثابت می‌کنند که یک فرد در هنگام ایجاد فرمول‌های مایکروسافت اکسل برای انجام وظایف خاص، چقدر می‌تواند از برتری بالایی برخوردار باشد.
گاهی اوقات آن فرمول های اکسل در واقع ساده ترین (یا بهترین) راه برای انجام کارها نیستند. بیشتر برنامه نویسان به شما می گویند که آن را ساده نگه دارید، و این در مورد فرمول های اکسل مانند هر چیز دیگری صادق است.
اگر واقعاً می‌خواهید با استفاده از اکسل جدی شوید، باید راهنمای مبتدیان ما برای استفاده از Microsoft Excel را بخوانید. همه چیزهایی که برای افزایش بهره وری خود با اکسل نیاز دارید را دارد. پس از آن، برای راهنمایی بیشتر، حتماً با برگه تقلب توابع ضروری اکسل ما مشورت کنید.
اعتبار تصویر: kues/Depositphotos