متن فیلم

در ویدیوی قبلی ما یاد گرفتیم که ابزارهای فیلترکردن و مرتب‌سازی به ما کمک می‌کنند که اطلاعات نمایش داده شده و نحوه نمایش اونها رو در برگه‌های کاری خودمون کنترل کنیم، در این ویدیو ما در مورد نحوه استفاده از برخی از رایج‌ترین توابعی که یک تحلیلگر داده ممکنه از اونها استفاده کنه، یعنی توابع IF، IFS، COUNTIF و SUMIF، بحث می‌کنیم. ابتدا، بیایید نحوه استفاده از تابع IF را بررسی کنیم. تابع IF یکی از پرکاربردترین توابع منطقی در اکسل است. تابع IF شما را قادر می‌سازد تا به صورت منطقی یک مقدار را با معیارهایی که در تابع تعیین کرده‌اید مقایسه کنید و سپس بر اساس اینکه نتیجه مقایسه منطقی درست یا نادرست است، نتیجه‌ای را برگردانید. و این مقادیر می‌توانند مقادیر متنی یا مقادیر عددی باشند. یک تابع IF اساساً می‌گوید: "اگر چیزی درست است، یک مقدار را برگردان یا کاری انجام بده، اما اگر درست نیست، یک مقدار متفاوت را برگردان یا کار دیگری انجام بده". برای مثال، در کاربرگ فروش اسباب‌بازی خودرو، اگر می‌خواستیم ستونی داشته باشیم که ثبت کند سفارش ارسال شده است یا خیر، می‌توانستید یک ستون جدید در سمت راست ستون موجود اضافه کنید - بیایید آن را ارسال شده بنامیم. و سپس فرمولی را که در سلول H2 مشاهده می‌کنید وارد کنید. این فرمول می‌گوید - اگر متن در G2 می‌گوید "ارسال شده"، "بله" را برگردانید، و اگر اینطور نیست، "خیر" را برگردانید. سپس می‌توانید از Fill Handle برای کپی کردن این فرمول در پایین ستون استفاده کنید. می‌توانید ببینید که اکثر سلول‌ها می‌گویند "بله"، اما برخی دیگر نه، زیرا سفارش به دلایلی ارسال نشده است. همچنین می‌توانیم از تابع IF برای تأکید بر اندازه یک سفارش استفاده کنیم. بنابراین، اگر یک ستون جدید به سمت راست «فروش» اضافه کنیم و نام آن را «۳ هزار به اضافه یا منها» بگذاریم، سپس فرمولی را که در سلول F2 مشاهده می‌کنید وارد کنید. این فرمول می‌گوید - اگر سفارش بیش از سه هزار باشد، متن «بیش از ۳ هزار» را برگردانید، اما اگر نباشد، متن «زیر ۳ هزار» را برگردانید. و می‌توانیم فرمول را در پایین ستون کپی کنیم. در حالت ایده‌آل، شما فقط از تابع IF برای اعمال یک یا دو شرط استفاده می‌کنید، اما ممکن است سناریوهایی وجود داشته باشد که بخواهید چندین شرط را اعمال کنید. در این موارد، می‌توانید از قابلیت‌های «تودرتو»ی توابع برای گرد هم آوردن چندین عبارت IF در یک فرمول استفاده کنید. این توابع «توابع IF تو در تو» (nested) نامیده می‌شوند. به عنوان مثال، اگر ستون دیگری را در اینجا برای اندازه سفارش اضافه کنیم و سپس فرمولی را که در سلول F2 مشاهده می‌کنید وارد کنید، می‌توانید ببینید که این فرمول شامل چندین تابع IF است. برای هر شرط، یکی برای بزرگ، یکی برای متوسط و یکی برای کوچک مورد نیاز است و به سه مجموعه پرانتز نیاز دارد. بنابراین، این یک فرمول نسبتاً طولانی و پیچیده است، اما کار می‌کند. باز هم، می‌توانیم فرمول را در ستون پایین کپی کنیم. اگرچه اکسل از نظر فنی از تودرتو کردن حداکثر ۶۴ تابع IF مختلف در یک فرمول پشتیبانی می‌کند، اما این یک روش توصیه شده نیست. داشتن چندین تابع IF در یک فرمول واحد می‌تواند مدیریت آن را بسیار چالش برانگیز کند. برای مثال، فرض کنید با فرمولی مانند این مواجه می‌شوید که مدتی است از آن استفاده نکرده‌اید، یا حتی بدتر از آن، توسط شخص دیگری ایجاد شده است؛ فهمیدن چگونگی و چرایی استفاده از آن می‌تواند بسیار دشوار باشد. همچنین، اگر شرط‌های شما افزایش یابد، باید شرط‌های بیشتری را به فرمولی که از قبل کاملاً پیچیده و طولانی است اضافه کنید، که این کار فقط مسائل را پیچیده‌تر می‌کند. برای حل این مشکل، تابع جدیدی به نام IFS توسعه داده شده است. تابع IFS فقط در اکسل ۲۰۱۹، اکسل برای مایکروسافت ۳۶۵ و اکسل برای وب پشتیبانی می‌شود. همانطور که از نامش پیداست، این تابع می‌تواند جایگزین چندین تابع IF تو در تو شود که در یک فرمول واحد استفاده می‌شوند تا مسائل را ساده‌تر کند. یک ستون دیگر برای اندازه سفارش اضافه کنیم، اما این بار از تابع IFS استفاده خواهیم کرد. همانطور که در سلول G2 مشاهده می‌کنید، این فرمول فقط یک مجموعه پرانتز به جای سه دارد و فقط از یک تابع به جای سه استفاده می‌کند. بیایید آن فرمول را در پایین ستون نیز کپی کنیم. حالا بیایید به مثال دیگری از تابع IF نگاهی بیندازیم، ما آن را با قالب‌بندی شرطی نیز ترکیب خواهیم کرد. به برگه فروش خودرو می‌رویم... و یک ستون جدید در سمت راست ستون Year Resale Value اضافه می‌کنیم و آن را "Retention%" می‌نامیم. سپس، فرمولی را که در سلول G2 مشاهده می‌کنیم وارد می‌کنیم که "Year Resale Value" را بر "Retail Price" اصلی تقسیم می‌کند. ما باید این را به صورت درصد قالب‌بندی کنیم و سپس می‌توانیم آن را در پایین ستون کپی کنیم. در مرحله بعد، یک ستون برای برجسته کردن مقدار Retention برای هر خودرو اضافه خواهیم کرد. فرمولی که در اینجا در سلول H2 اضافه می‌کنیم، از تابع IF برای بیان این موضوع استفاده می‌کند که اگر درصد در ستون قبلی بیشتر از ۶۹٪ باشد، آن را به عنوان «خوب» علامت‌گذاری کنید، اما اگر اینطور نیست، آن را به عنوان «ضعیف» علامت‌گذاری کنید. یک بار دیگر، فرمول را در پایین ستون کپی می‌کنیم. همچنین می‌توانیم از قالب‌بندی شرطی برای برجسته کردن درصدهای ارزش نگهداری بیشتر استفاده کنیم. H2 را انتخاب می‌کنیم و در تب Home، روی قالب‌بندی شرطی کلیک می‌کنیم و یک قانون جدید ایجاد می‌کنیم. شرط موجود در قانون ما فقط سلول‌هایی را قالب‌بندی می‌کند که حاوی یک مقدار متنی خاص هستند... و آن مقدار کلمه «خوب» است. و اگر با آن شرط مطابقت داشته باشد، آن را با یک فونت سبز تیره قالب‌بندی کرده و سلول را با رنگ سبز کم‌رنگ پر می‌کنیم. بیایید آن قالب‌بندی شرطی را در بقیه ستون کپی کنیم. می‌توانید ببینید که سلول‌هایی که حاوی کلمه «خوب» (GOOD) هستند، اکنون همانطور که تعریف کردیم قالب‌بندی شده‌اند، اما سلول‌هایی که حاوی کلمه «ضعیف» (Poor) هستند، اینطور نیستند. بیایید یک قانون قالب‌بندی شرطی دیگر اضافه کنیم. این بار، مدیریت قوانین را انتخاب می‌کنیم، زیرا قرار است یک قانون دیگر به قانون موجود خود اضافه کنیم. این قانون جدید مانند قانون قبلی خواهد بود، با این استثنا که به جای آن، به دنبال تطابق با کلمه « ضعیف » بگردید و سلول‌های منطبق را با متن قرمز و پس‌زمینه صورتی قالب‌بندی کنید. و یک بار دیگر، آن را در ستون پایین کپی می‌کنیم. اکنون تمام سلول‌هایی که حاوی کلمه « ضعیف » هستند، به صورت متن قرمز با پس‌زمینه صورتی قالب‌بندی می‌شوند. حال نگاهی سریع به نحوه استفاده از تابع COUNTIF می‌اندازیم. COUNTIF یکی از توابع آماری ارائه شده در اکسل است. می‌توانید از آن برای شمارش تعداد سلول‌هایی که یک معیار خاص را برآورده می‌کنند، استفاده کنید؛ مانند تعداد مواردی که نام یک کارمند در لیستی از فاکتورهای فروش ظاهر می‌شود، یا تعداد مواردی که یک شماره قطعه خاص در لیستی از سفارشات خرید ظاهر می‌شود. بیایید به برگه کار فروش اسباب‌بازی خودرو برویم. فرض کنید می‌خواهید بدانید که چه تعداد از سفارشات فروش در لیست به مشتریان مستقر در بریتانیا ارسال شده است. فرمولی را که در سلول AD7 می‌بینید وارد می‌کنیم. توجه داشته باشید که وقتی از متن به عنوان معیار استفاده می‌کنیم، باید متن را داخل علامت نقل قول قرار دهیم. بنابراین 6 سفارش فروش در بریتانیا وجود دارد. و اگر می‌خواستید همین مورد را برای مشتریان فرانسوی کشف کنید، کافیست فرمول موجود را ویرایش کنید، یا آن را کپی کرده و سپس ویرایش کنید. می‌توانید ببینید که ۱۴ سفارش برای مشتریان فرانسوی وجود دارد. توجه داشته باشید که این بار متن وارد شده با حروف کوچک است و هنوز هم کار می‌کند؛ بنابراین نام‌ها در این تابع به حروف کوچک و بزرگ حساس نیستند. و بیایید همین کار را برای مشتریان ایالات متحده انجام دهیم؛ ۴۱ سفارش برای مشتریان مستقر در ایالات متحده وجود دارد. همچنین یک تابع جدیدتر به نام COUNTIFS وجود دارد که معیارهایی را برای سلول‌هایی در چندین محدوده اعمال می‌کند تا تعداد مواردی را که همه معیارها برآورده شده‌اند، بشمارد. این امر نیاز به استفاده از چندین تابع COUNTIF را در یک فرمول طولانی و پیچیده از بین می‌برد. تابع COUNTIFS فقط در اکسل ۲۰۱۹، اکسل برای مایکروسافت ۳۶۵ و اکسل برای وب پشتیبانی می‌شود. حال بیایید نگاهی سریع به نحوه استفاده از تابع SUMIF بیندازیم که یک تابع ریاضی بسیار رایج در اکسل است. شما از تابع SUMIF برای جمع کردن مقادیر در یک محدوده مشخص که معیارهای خاصی را برآورده می‌کند، استفاده می‌کنید. به عنوان مثال، ممکن است بخواهید فقط حقوق‌هایی را که بیشتر از یک سطح حقوق مشخص هستند، جمع کنید، یا ممکن است بخواهید مجموع کل فروش‌های یک دسته محصول خاص را پیدا کنید. فرمولی را که در سلول AD10 مشاهده می‌کنید، وارد می‌کنیم. این فرمول هر یک از سفارشات فروشی را که مجموع آنها بیش از ۳۰۰۰ دلار است، جمع می‌کند. دوباره توجه کنید که از آنجا که ما از یک عملگر حسابی، یعنی عملگر «بزرگتر از» (greater than)، استفاده کرده‌ایم، باید معیار را داخل علامت نقل قول قرار دهیم. اگر معیاری را مشخص کنیم که فقط یک عدد باشد، آن را داخل علامت نقل قول قرار نمی‌دهیم. بنابراین، مجموع کل سفارش‌هایی که بیش از ۳۰۰۰ دلار بوده‌اند، تقریباً ۴۷۰،۰۰۰ دلار است. همچنین می‌توانید هنگام جستجوی تطابق‌های جزئی از کاراکترهای عمومی مانند «علامت سؤال» (?) و «ستاره» (*) استفاده کنید و همچنین می‌توانید مشخص کنید که مقادیر از ستونی متفاوت از ستونی که معیارها را در آن مشخص کرده‌اید، استخراج شوند. به عنوان مثال، اگر فرمولی را که در سلول AD13 مشاهده می‌کنید وارد کنیم، تمام فروش‌های خودرو در ستون E را فقط برای محصولاتی که در ستون «خط تولید» (productline) به «خودرو» (Cars) ختم می‌شوند، جمع می‌کند. همچنین یک تابع جدیدتر به نام SUMIFS وجود دارد که می‌توانید از آن برای جمع کردن سلول‌ها بر اساس چندین معیار استفاده کنید. این امر نیاز به استفاده از چندین تابع SUMIF در یک فرمول طولانی و پیچیده را از بین می‌برد. تابع SUMIFS فقط در اکسل ۲۰۱۹، اکسل برای مایکروسافت ۳۶۵ و اکسل برای وب پشتیبانی می‌شود. در این ویدیو، نحوه استفاده از توابع IF، IFS، COUNTIF و SUMIF را یاد گرفتیم. در ویدیوی بعدی به نحوه استفاده از توابع مرجع VLOOKUP و HLOOKUP خواهیم پرداخت.