اکسل هزاران کارکرد دارد ، اما بیشتر افراد مانند جمع و متوسط به اصول اولیه می چسبند. در حالی که این موارد برای کارهای ساده خوب کار می کنند ، سه کارکرد وجود دارد که با تلاش بسیار کمتری سناریوهای پیچیده تری را انجام می دهند. توالی ، اجازه می دهد ، و لامبدا معمولاً مورد استفاده قرار نمی گیرد ، اما آنها مشکلات خاصی را حل می کنند که در غیر این صورت نیاز به راه حل های ناخوشایند یا فرمول های طولانی دارند که حفظ آن دشوار است.
اکسل هزاران کارکرد دارد ، اما بیشتر افراد مانند جمع و متوسط به اصول اولیه می چسبند. در حالی که این موارد برای کارهای ساده خوب کار می کنند ، سه کارکرد وجود دارد که با تلاش بسیار کمتری سناریوهای پیچیده تری را انجام می دهند. توالی ، اجازه می دهد ، و لامبدا معمولاً مورد استفاده قرار نمی گیرد ، اما آنها مشکلات خاصی را حل می کنند که در غیر این صورت نیاز به راه حل های ناخوشایند یا فرمول های طولانی دارند که حفظ آن دشوار است.
هنگام استفاده از آنها ، می توانید راه حل های پویا و خود را بسازید که به جای ایجاد چندین ستون یاور یا کپی کردن فرمول ها در ده ها سلول ، به طور خودکار به روز می شوند. این که آیا شما در حال تولید داده های پی در پی ، مدیریت محاسبات پیچیده یا ایجاد توابع سفارشی قابل استفاده مجدد هستید ، این موارد از جمله توابع اکسل است که می تواند باعث صرفه جویی در کار شما شود.
4
عملکرد دنباله داده ها را به صورت خودکار تولید می کند
دنباله های شماره و تاریخ دینامیکی ایجاد کنید
دنباله آرایه هایی از اعداد پی در پی ایجاد می کند بدون اینکه به صورت دستی هر مقدار تایپ کنید. اگر به لیستی از شناسه های کارمند ، شماره فاکتورها یا محدوده تاریخ نیاز دارید ، این عملکرد به راحتی آن را کنترل می کند.
نحو ساده است:
= دنباله (ردیف ، [ستون] ، [شروع] ، [مرحله])
بیایید پارامترها را تجزیه کنیم:
- ردیف ها: تعیین می کند که چند عدد را به صورت عمودی می خواهید.
- ستون ها: گسترش افقی را کنترل می کند – آن را برای یک ستون واحد خالی کنید.
- شروع: شماره اولیه خود را تنظیم می کند ، به صورت پیش فرض 1.
- مرحله: افزایش بین اعداد را تعیین می کند ، همچنین به طور پیش فرض به 1.
با توجه به یک مجموعه داده فروش ، دنباله برای ایجاد شماره های مرجع مفید است. به عنوان مثال ، فرمول زیر اعداد 1 تا 32 را تولید می کند.
= دنباله (32)
به همین ترتیب ، اگر نیاز به شروع از 1001 دارید ، می توانید استفاده کنید:
= دنباله (32 ، 1 ، 1001)
با توالی تاریخ مفید است. فرمول زیر دوازده تاریخ متوالی از اول ژانویه ایجاد می کند. این ضرب و شتم به صورت دستی برای گزارش های ماهانه یا جدول زمانی پروژه وارد تاریخ می شود.
= دنباله (12 ، 1 ، تاریخ (2025 ، 1 ، 1) ، 1)
شما همچنین می توانید فقط با ترکیب دنباله با سایر کارکردهای تاریخ در اکسل ، مانند روز کاری ، برای سناریوهای برنامه ریزی پیچیده تر ، روزهای کاری ایجاد کنید.
آرایه های توالی بزرگ می توانند صفحه گسترده شما را کند کنند. از تولید بیش از 10،000 مقدار به طور هم زمان خودداری کنید مگر اینکه کاملاً لازم باشد. اگر به مجموعه داده های عظیم احتیاج دارید ، آنها را در تکه های کوچکتر یا استفاده از منابع داده خارجی در نظر بگیرید.
3
بگذارید فرمول های پیچیده قابل حفظ باشد
محاسبات تکراری را از بین ببرید و خوانایی را بهبود بخشید
بگذارید نام را به مقادیر در یک فرمول اختصاص دهد. از این رو ، محاسبات تکراری را از بین می برد و کار را قابل خواندن می کند. به جای اینکه چندین بار همان عبارت را بنویسید ، یک بار آن را تعریف می کنید و آن را با نام ارجاع می دهید.
نحو از این الگوی پیروی می کند:
= LET (Name1 ، Value1 ، [Name2 ، Value2 ، …] ، محاسبه)
می توانید با اضافه کردن جفت های ارزش بیشتر ، چندین متغیر را تعریف کنید. محاسبه در انتها از این متغیرهای نامگذاری شده برای تولید نتیجه استفاده می کند.
با نگاهی به مجموعه داده فروش ، فرض کنید کمیسیون فروشنده را با پاداش محاسبه می کنید. بدون اجازه ، شما می نویسید:
= if (G2*0.05> 500 ، G2*0.05*1.1 ، G2*0.05)
محاسبه کمیسیون B2*0.05 دو بار ظاهر می شود. با اجازه ، تمیزتر می شود:
= LET (کمیسیون ، G2*0.05 ، if (کمیسیون> 500 ، کمیسیون*1.1 ، کمیسیون))
این همان محاسبه را انجام می دهد اما یک بار “کمیسیون” را در ابتدا تعریف می کند. شما فقط باید نرخ کمیسیون را در یک مکان تغییر دهید.
برای تجزیه و تحلیل حاشیه سود پیچیده ، بگذارید حتی مفیدتر باشد. مثال زیر هر مؤلفه را به وضوح تعریف می کند.
= اجازه دهید (درآمد ، G2 ، هزینه ها ، L2 ، حاشیه ، (هزینه های درآمد)/درآمد ، اگر (حاشیه> 0.3 ، “بالا” ، اگر (حاشیه> 0.15 ، “متوسط” ، “کم”))
این فرمول حاشیه سود را به عنوان درصد محاسبه می کند ، سپس آن را به عنوان بالا (بالاتر از 30 ٪) ، متوسط (15-30 ٪) یا پایین (زیر 15 ٪) طبقه بندی می کند. هر مؤلفه یک نام واضح دارد و منطق را به راحتی دنبال می کند.
این رویکرد پیچیدگی فرمول را به نصف کاهش می دهد در حالی که صفحات گسترده شما را برای اشکال زدایی و اصلاح بعداً آسانتر می کند.
2
Lambda عملکردهای سفارشی قابل استفاده مجدد ایجاد می کند
برای منطق تکراری تجارت ، توابع سفارشی ایجاد کنید
Lambda توابع سفارشی را ایجاد می کند که می توانید به طور مکرر در کتاب کار خود استفاده کنید. به جای کپی کردن فرمول ها در همه جا ، شما یک عملکرد واحد ایجاد می کنید که ورودی ها را می پذیرد و نتایج محاسبه شده را باز می گرداند.
نحو این است:
= lambda (پارامتر 1 ، [پارامتر 2 ، …] ، محاسبه)
پارامترها مانند دارندگان مکان عمل می کنند. محاسبه از این پارامترها برای تولید خروجی استفاده می کند.
فرض کنید شما اغلب نمرات عملکرد وزنی را محاسبه می کنید. می توانید یک تابع لامبدا مانند موارد زیر ایجاد کنید:
= لامبدا (فروش ، سهمیه ، وزن ، (فروش/سهمیه)*وزن)
این یک عملکرد قابل استفاده مجدد ایجاد می کند که سه ورودی را به خود اختصاص می دهد: فروش واقعی ، سهمیه فروش و یک عامل وزنه برداری. با تقسیم فروش بر اساس سهمیه و ضرب در وزن ، نمره عملکرد وزنی را برمی گرداند. این عملکرد را با استفاده از مدیر نام اکسل “Performancescore” نامگذاری کنید.
برای نامگذاری عملکرد Lambda خود ، به فرمول> مدیر Name> New بروید.
اکنون می توانید این عملکرد را در هر نقطه از کتاب کار خود فراخوانی کنید.
= Performancescore (B2 ، C2 ، 0.7)
این عملکرد نمره عملکرد را با استفاده از مبلغ فروش ارائه شده ، سهمیه و ضریب وزن محاسبه می کند.
برای تجزیه و تحلیل قلمرو ، می توانید تابعی را ایجاد کنید که مناطق را بر اساس درآمد طبقه بندی می کند:
= lambda (درآمد ، اگر (درآمد> 100000 ، “بالا” ، اگر (درآمد> 50000 ، “متوسط” ، “کم”))
این عملکرد درآمد را به سه ردیف طبقه بندی می کند: مبلغ بیش از 100000 دلار ، متوسط با 50،000 تا 100،000 دلار و برای هر چیزی زیر 50،000 دلار کم است. شما می توانید آن را “درآمد” نامگذاری کنید و از آن در کل برگه های خود استفاده کنید:
= درآمد (J2)
عملکرد Lambda با سایر کارکردها نیز کار می کند ، و به شما امکان می دهد با استفاده از نامهای توصیفی به جای منابع سلول رمزنگاری ، فرمول هایی مانند انسان بنویسید.
شما می توانید با استفاده از پیشوندهایی مانند “fn_” برای همه توابع سفارشی (به عنوان مثال ، “fn_performancescore”) توابع Lambda خود را در Name Manager سازماندهی کنید. پیدا کردن آنها آسانتر می شود و از درگیری با دامنه های معمولی نامگذاری شده جلوگیری می کند.
1
من این توابع را برای ایجاد راه حل های قدرتمند ترکیب می کنم
ایجاد ابزارهای جامع تجزیه و تحلیل تجارت
هنگامی که از دنباله استفاده می کنید ، اجازه می دهید و لامبدا با هم ، مشکلاتی را حل می کنند که در غیر این صورت به چندین ستون یاور یا فرمول های آرایه نیاز دارند. این ترکیب راه حل های پویا و قابل حفظ را ایجاد می کند.
بیایید با استفاده از داده های فروش یک ابزار پیش بینی فروش را در نظر بگیریم. فرمول زیر پیش بینی فروش 12 ماهه را برای یک مبلغ فروش اولیه محاسبه می کند. این کار با تعریف دو متغیر کلیدی با LET شروع می شود. این مقدار را از سلول G2 به عنوان نقطه شروع فروش پایه می گیرد.
= let (base_sale ، g2 ، growth_rate ، l2 ، projectmonthly ، lambda (ماه ، base_sale * (1 + رشد_rate)^ماه) ، projectMonthly (دنباله (12)))
سپس نرخ رشد ماهانه از L2 0.04 (4 ٪) طول می کشد. می توانید این مقدار را به مدل سناریوهای مختلف تغییر دهید. سپس یک مینی عملکرد قابل استفاده مجدد به نام ProjectMonthly را تعریف می کند. این عملکرد فروش پیش بینی شده را برای یک ماه معین بر اساس نرخ فروش و نرخ رشد محاسبه می کند.
علاوه بر این ، عملکرد ProjectMonthly را صدا می کند و دنباله (12) را به آن منتقل می کند. این مجموعه ای از اعداد را از 1 تا 12 ایجاد می کند و Lambda به طور خودکار محاسبه خود را برای هر عدد در آن دنباله اعمال می کند.
در اینجا یک ماشین حساب پاداش عملی وجود دارد که پاداش ها را بر اساس دستیابی به هدف محاسبه می کند.
= lambda (فروش ، هدف ، let (نسبت ، فروش/هدف ، اگر (نسبت> = 1.2 ، فروش*0.08 ، اگر (نسبت> = 1 ، فروش*0.05 ، 0)))
کوچک شروع کنید ، سپس پیچیدگی ایجاد کنید
این کارکردها وقتی با اندیشه ترکیب می شوند بهترین کار را می کنند. با برنامه های کاربردی ساده شروع کنید – دنباله استفاده از داده های آزمون ، اجازه دهید تا محاسبات تکراری را تمیز کنید و لامبدا برای قوانین تجاری که به طور مکرر استفاده می کنید. هنگامی که با هر عملکرد به صورت جداگانه راحت هستید ، فرصت های طبیعی برای ترکیب آنها را در راه حل های پیچیده تر پیدا خواهید کرد.
منحنی یادگیری شیب دار نیست ، اما بازپرداخت قابل توجه است. صفحات گسترده شما قابل اطمینان تر ، آسان تر برای حسابرسی و تغییر در هنگام تغییر نیازهای تجاری ساده تر می شوند. این همان چیزی است که این سه کارکرد را برای هر کسی که به طور مرتب با داده ها کار می کند ، به ویژه ارزشمند می کند.