بسیاری از آنها برای جداول اساسی از اکسل استفاده می کنند ، اما شما باید فراتر از مبالغ ساده باشید. اگر فرمول های شما در حال دست و پنجه نرم شدن هستند ، توابع مشروط گزینه ای باهوش تر و پنهان در چشم انداز هستند. آنها منطق پیچیده ای را اداره می کنند و تصمیمات خود را به صورت خودکار انجام می دهند بدون اینکه شما را ملزم به عبور از منحنی یادگیری شیب دار کنید.
بسیاری از آنها برای جداول اساسی از اکسل استفاده می کنند ، اما شما باید فراتر از مبالغ ساده باشید. اگر فرمول های شما در حال دست و پنجه نرم شدن هستند ، توابع مشروط گزینه ای باهوش تر و پنهان در چشم انداز هستند. آنها منطق پیچیده ای را اداره می کنند و تصمیمات خود را به صورت خودکار انجام می دهند بدون اینکه شما را ملزم به عبور از منحنی یادگیری شیب دار کنید.
6
اگر و ifs
تصمیمات ساده و پیچیده را اداره کنید
عملکرد IF اساسی ترین تصمیم گیرنده اکسل است. این یک شرط را ارزیابی می کند و اگر شرط صحیح باشد ، یک مقدار را برمی گرداند ، دیگری اگر نادرست باشد – مانند اکسل می پرسد “چه می شود؟” و بر این اساس پاسخ می دهد.
در اینجا یک مثال عملی است. فرض کنید شما داده های عملکرد کارمندان را مدیریت می کنید و نیاز به طبقه بندی خودکار رتبه بندی ها دارید. هرکسی با نمره بالاتر از 3.5 باید به عنوان “رضایت بخش” پرچم گذاری شود ، در حالی که دیگران “نیاز به بهبود” دارند. شما می توانید از عملکرد IF همانطور که در فرمول زیر نشان داده شده است برای رسیدگی به صدها نفر از کارمندان استفاده کنید.
= if (C2> 3.5 ، “رضایت بخش” ، “نیاز به بهبود”)
اما آنچه در مورد نتایج متعدد ، به عنوان سنتی تو در صورتی که اظهارات می توانند کابوس شوند ، همانطور که در شکل زیر نشان داده شده است ، چیست.
= if (c2> = 4.5 ، “عالی” ، if (c2> = 3.5 ، “خوب” ، if (c2> = 2.5 ، “متوسط” ، “ضعیف”))
این جایی است که IFS عقل شما را نجات می دهد. عملکرد IFS به طور موثری چندین سناریو را مدیریت می کند. برای محاسبات پاداش کارمندان بر اساس ردیف عملکرد ، می توانید از عملکرد IFS همانطور که در فرمول زیر نشان داده شده است استفاده کنید.
= ifs (ac2> = 4 ، “5،000 $” ، ac2> = 3 ، “3،000 $” ، ac2> = 2 ، “1000 $” ، درست ، “0 $”)
هر شرط به ترتیب ارزیابی می شود تا زمانی که فرد درست نشود. تفاوت اساسی در این است که اگر تصمیمات باینری را اداره می کند ، در حالی که IFS منطق پیچیده و چند لایه را بدون نیاز به پرانتزهای تودگی مدیریت می کند.
هر دو کارکرد تصمیم گیری را به صورت خودکار انجام می دهند ، خواه شما در حال طبقه بندی عملکرد فروش ، تعیین صلاحیت تعطیلات یا پرچم گذاری هر پروژه به تأخیر بیفتد. این توابع همچنین در صورت نیاز به بررسی اینکه آیا دو مقدار در اکسل برابر هستند ، مفید هستند یا خیر. آنها داده های استاتیک را به برگه های پاسخگو تغییر می دهند.
5
سوئیچ
تطبیق ارزش را ساده می کند
عملکرد سوئیچ در صورت نیاز به تطبیق با مقدار دقیق گزینه بهتری است ، زیرا بیشتر از یا کمتر از منطق نیست. در عوض ، این شامل جستجوی مستقیم است. هر جفت ارزش ارزش مانند یک فرهنگ لغت عمل می کند و نحو خود را ساده می کند.
= سوئیچ (lookup_value ، مقدار 1 ، نتیجه 1 ، مقدار 2 ، نتیجه 2 ، default_result)
کدهای بخش کارمندان را در صفحه گسترده HR در نظر بگیرید. اگر قرار بود از عملکرد IF استفاده کنید ، فرمول شما طولانی و پیچیده می شود ، همانطور که در مثال زیر نشان داده شده است.
= if (b2 = “hr” ، “منابع انسانی” ، اگر (b2 = “it” ، “فناوری اطلاعات” ، اگر (b2 = “fin” ، “مالی” ، “ناشناخته”)))
درعوض ، می توانید از Switch برای کارآمدتر استفاده کنید.
= سوئیچ (Q2: Q3004 ، “فروش” ، “S” ، “تولید” ، “P” ، “IT/IS” ، “IT” ، “ناشناخته”)
اما سوئیچ محدودیت های خود را دارد. با کارتهای وحشی یا دامنه کار نمی کند. به عنوان مثال ، اگر به منطق “بزرگتر از” یا “حاوی” نیاز دارید ، باید با توابع IF یا IFS به آن بپردازید.
گرچه یکی از مزیت های بزرگی که به دست می آورید خوانایی و نگهداری است. هنگامی که از عملکرد سوئیچ در اکسل برای جستجوی پیچیده استفاده می کنید ، فرمول های شما به خود مدار می شوند. هرکسی که صفحه گسترده شما را مرور می کند ، بلافاصله می فهمد که هر کد چه چیزی را نشان می دهد ، زیرا در صورت بیانیه رمزگشایی هیچ گونه رمزنگاری وجود ندارد.
4
عملکرد را انتخاب کنید
مقادیر را بر اساس موقعیت انتخاب می کند
آثار مانند لیست شماره را انتخاب کنید. شما یک شماره موقعیت را ارائه می دهید ، و مقدار مربوطه را از گزینه های از پیش تعریف شده خود باز می گرداند. این روش اکسل است که می گوید: “شماره 3 مورد را از این لیست به من بدهید.”
نحو ساده است. آرگومان اول شماره موقعیت است و به دنبال آن لیست مقادیر شما است. حرکت با مثال صفحه گسترده کارمند ، می توانید از این عملکرد برای تبدیل نمرات عددی به توضیحات قابل خواندن استفاده کنید:
= انتخاب کنید (AB2: AB3004 ، “بسیار فقیر” ، “فقیر” ، “رضایت بخش” ، “خوب” ، “خیلی خوب”)
این فرمول تعداد ستون AB را می گیرد و درجه کارمند مربوطه را برمی گرداند. موقعیت یک “بسیار فقیر” می دهد ، موقعیت دو به “ضعیف” می دهد و غیره.
انتخاب به ویژه با گزارش سه ماهه خوب است. برای نمودارها و ارائه ها می توانید شماره های چهارم را به برچسب های مناسب تبدیل کنید:
= انتخاب (E2 ، “Q1 2024” ، “Q2 2024” ، “Q3 2024” ، “Q4 2024”)
شما می توانید آن را با سایر توابع برای نتایج پویا ترکیب کنید ، مانند استفاده از روز هفته با تبدیل تاریخ تبدیل به نام های روز یا ماه با اختصار ماه.
با این حال ، محدودیت دارد. فقط موقعیت های پی در پی را از یک مورد استفاده کنید. شما نمی توانید از شماره ها پرش کنید یا از صفر استفاده کنید. اگر داده های شما از این الگوی پیروی نمی کنند ، سوئیچ یا عملکرد بهتر کار می کنند.
هنگامی که شما نیاز به استفاده از عملکرد انتخاب Excel برای جستجوی موقعیت های مبتنی بر موقعیت دارید ، در صورت بیانیه ها تمیزتر از تو در تو است. فرمول های شما قابل پیش بینی تر می شوند و به دیگران اجازه می دهند دقیقاً ببینند که هر موقعیت بدون رمزگشایی منطق پیچیده چیست.
3
و یا
منطق پیچیده را بسازید
و یا توابع سناریوهای چند شرط را که اگر اظهارات نتوانند مدیریت کنند ، اداره می کنند. و به هر شرط نیاز دارد تا درست باشد. به عنوان مثال ، صلاحیت ارتقاء کارکنان ممکن است در نظر گرفتن هر دو دوره تصدی بیش از دو سال و رتبه عملکرد بالاتر از 3.5 ، همانطور که در فرمول زیر نشان داده شده است.
= if (و (c2> 2 ، d2> 3.5) ، “واجد شرایط” ، “واجد شرایط بودن”)
اما ، یا رویکرد متضاد را اتخاذ می کند – فقط به یک شرط نیاز دارد تا درست باشد. فرض کنید شما باید صلاحیت پاداش را بررسی کنید. فقط کارمندان فعال ممکن است در صورت عملکرد خوب واجد شرایط باشند:
= if (یا (k2 = “فعال” ، ac2> = 1) ، “جایزه واجد شرایط” ، “بدون پاداش”)
این توابع هنگام ترکیب قدرتمند می شوند. در مثال زیر ، منطق تصویب تعطیلات را در نظر می گیریم که در آن کارکنان به تأیید مدیر و یا 30+ روز تصدی یا وضعیت اضطراری نیاز دارند:
= if (و (g2 = “تأیید شده” ، یا (H2> = 30 ، i2 = “اضطراری”)) ، “تعطیلات اعطا شده” ، “تعطیلات انکار”)
تفاوت اصلی این است که و محدود کننده است. همه شرایط باید برآورده شود. در مقایسه ، یا مجاز است ، اجازه می دهد تا هر شرایطی باعث موفقیت شود.
2
sumifs ، countifs و Anadifs
داده ها را به صورت مشروط تجزیه و تحلیل کنید
این سه کارکرد با استفاده از شرایط متعدد به طور همزمان ، داده های خام را به بینش های عملی تغییر می دهند. آنها می توانند برای جدا کردن مجموعه داده های بزرگ بدون نیاز به جداول محوری استفاده شوند.
به عنوان مثال ، HR می خواهد مبلغ حقوق و دستمزد را برای بخش های خاص ، مبلغ مربوط به سطح خاص شغلی یا میانگین رتبه بندی عملکرد توسط تیم تجزیه و تحلیل کند. این توابع با سهولت چنین تجزیه و تحلیل چند معیار را انجام می دهند.
Sumifs مقادیری را اضافه می کند که معیارهای مختلفی را برآورده می کند. هنگام محاسبه کل حقوق برای کارمندان بخش فناوری اطلاعات با بیش از سه سال تجربه ، فرمول می شود:
= sumifs (e: e ، b: b ، “it” ، d: d ، “> 3”)
نحو از یک الگوی منطقی پیروی می کند: دامنه جمع ، معیارهای 1 ، معیارها 1 ، معیارهای 2 ، معیارها 2. برای محاسبات خاص می توانید حداکثر 127 جفت شرط را اضافه کنید.
از طرف دیگر ، Countifs سلولهایی را که شرایط مختلفی را رعایت می کنند ، شمارش می کند. برای تعیین تعداد کارمندان بازاریابی با رتبه بندی عملکرد بالاتر از 4.0 ، از فرمول زیر استفاده خواهیم کرد.
= Countifs (B: B ، “بازاریابی” ، F: F ، “> 4”)
این فرمول مجریان بالا را توسط بخش مشخص می کند و به مشخص کردن نامزدهای بالقوه ارتقاء یا نیازهای آموزشی در سراسر سازمان کمک می کند.
میانگین ها به طور متوسط بر اساس معیارهای متعدد محاسبه می کنند. برای یافتن متوسط حقوق کارمندان سطح ارشد در امور مالی ، ما استفاده خواهیم کرد:
= متوسط (E: E ، B: B ، “مالی” ، C: C ، “ارشد”)
فرمول فوق متوسط حقوق کارمندان سطح ارشد در بخش دارایی را محاسبه می کند.
فایده این است که ما معیارهای پویا را دریافت می کنیم. سلولهای مرجع به جای اینکه مقادیر کدگذاری شده را تغییر دهند ، به طور خودکار به روز می شوند. از این رو ، هنگام ترکیب محدوده تاریخ با کدهای بخش ، بررسی های سه ماهه آسان می شود.
این توابع تجمیع مشروط همچنین نیاز به فیلتر دستی را هنگامی که می خواهید سلولهایی را با متن خاص در اکسل بشمارید ، با استفاده از فرمول Countif یا عملکرد SUMIFS برای تجزیه و تحلیل داده های پیچیده.
1
iferror و ifna
از پیام های خطای زشت جلوگیری کنید
هیچ چیز یک صفحه گسترده حرفه ای مانند #div/0 را خراب نمی کند! یا #N/A خطاهای پراکنده در داده های شما. توابع IFError و IFNA شما را تشویق می کند تا خطاهای متداول اکسل را عیب یابی کنید و پیام های خطای زشت را با گزینه های معنی دار تر جایگزین کنید.
IFERROR هر خطایی را به خود جلب می کند و مقدار انتخاب شده شما را جایگزین می کند. به عنوان مثال ، هنگام محاسبه نسبت کارآیی کارمندان ، تقسیم توسط صفر خطاها ایجاد می کند. به جای نشان دادن #div/0! ، می توانید نمایش چیز مفیدی را انتخاب کنید:
= ifError (C2/D2 ، “داده در دسترس نیست”)
این فرمول بخش تلاش می کند اما در صورت بروز خطاها ، “هیچ داده ای در دسترس” را نشان می دهد ، که این یک ارائه مختصر تر است.
از طرف دیگر ، IFNA به طور خاص خطاهای #N/A را از توابع جستجو هدف قرار می دهد. هنگامی که ما با استفاده از VLOOKUP اطلاعات را جستجو می کنیم ، داده های گمشده خطاهای #N/A ایجاد می کنند. برای جلوگیری از این امر ، می توانیم از عملکرد IFNA همانطور که در فرمول زیر نشان داده شده است استفاده کنیم.
= ifna (Vlookup (A2 ، A2: AG3004 ، 4 ، FALSE) ، “کارمند یافت نشد”)
این فرمول شناسه کارمند را در ستون A2 در محدوده لیست کارمندان شما جستجو می کند و نام را از ستون چهارم برمی گرداند. هنگامی که شناسه کارمند در پایگاه داده شما وجود ندارد ، به جای نمایش #N/A ، پیام کاربر پسند “کارمند پیدا نشده است” را نشان می دهد.
تفاوت اصلی این است که IFERROR همه چیز را مانند خطاهای تقسیم ، خطاهای مرجع و خرابی های جستجو به دست می آورد. IFNA فقط خطاهای #N/A را انجام می دهد ، اجازه می دهد انواع دیگر خطای به طور عادی نمایش داده شوند.
می توانید هنگام تهیه صفحه گسترده برای گزارش های خودکار از این توابع استفاده کنید. به جای اینکه قبل از ارسال خلاصه های ماهانه ، خطاها را به صورت دستی بررسی کنید ، فرمول های شما می توانند استثنائات را با لطف انجام دهند.
هنگامی که به طور مرتب از توابع مشروط استفاده می کنید ، برگه های پاسخگو ایجاد می کنید که به جای ایجاد جداول استاتیک ، با تغییر داده ها سازگار هستند. آنها کار دستی تکراری را از بین می برند و صفحات گسترده شما را به جای ذخیره فقط داده ها برای تصمیمات تجاری واقعی مفید می کنند.