بیشتر افراد فکر می کنند سرعت در اکسل به معنای حفظ صدها فرمول و عملکرد است. من هم فکر کردم ، تا زمانی که فهمیدم که فقط سه کارکرد می توانند جای فرمولهایی را که قبلاً از آن استفاده می کردم جایگزین کنند.
بیشتر افراد فکر می کنند سرعت در اکسل به معنای حفظ صدها فرمول و عملکرد است. من هم فکر کردم ، تا زمانی که فهمیدم که فقط سه کارکرد می توانند جای فرمولهایی را که قبلاً از آن استفاده می کردم جایگزین کنند.
3
Xlookup
به یاد دارید وقتی Vlookup شما را فقط برای مشخص کردن شاخص ستون خود ، ستون ها را شمارش کرد؟ یا هنگامی که می خواستید داده ها را در سمت چپ ستون کلید خود جستجو کنید و در نهایت تنظیم مجدد کل برگه خود را انجام دادید؟ آن سردردها از بین رفته اند.
Xlookup اصلاح مدرن اکسل برای همه چیز Vlookup اشتباه شده است. این ساده تر ، انعطاف پذیر تر و در Excel 365 و Excel 2021+ در دسترس است. به جای کشتی گیری با شماره های ستون یا محدودیت های جهت ، شما فقط به آن سه چیز می گویید: چه چیزی را جستجو کنید ، به کجا نگاه کنید و چه چیزی را برگردانید.
در اینجا نحو اساسی وجود دارد:
= Xlookup (lookup_value ، lookup_array ، return_array) = xlookup (O2 ، I2: I200 ، L2: L200)
- تصویر توسط آدا
- تصویر توسط آدا
این کافی است برای جستجوی یک مقدار و بازگشت نتیجه تطبیق. هنگامی که می خواهید ارزش دیگری را جستجو کنید ، نیازی به تغییر سلول مورد نظر خود به عنوان Lookup_value (در مورد من ، O2) در فرمول خود نیست. شما فقط باید شکل موجود در سلول را تغییر دهید (در مورد من ، 3604) و نتایج بلافاصله به روز می شود. اگر اکسل نتواند مسابقه پیدا کند ، نشان دادن #N/A به طور پیش فرض است.
- تصویر توسط آدا
- تصویر توسط آدا
اما Xlookup ترفندهای اضافی دارد که آن را بسیار مفیدتر می کند. در اینجا یک مثال آورده شده است:
= Xlookup (O2 ، I2: I200 ، L2: L200 ، “شکل یافت نشد”) = Xlookup (O2 ، I2: I200 ، L2: L200،0،1 ، -1)
فرمول اول هنگامی که هیچ مسابقه ای وجود ندارد ، یک پیام دوستانه را به جای خطایی نشان می دهد. در همین حال ، مورد دوم به اکسل می گوید اگر هیچ مسابقه ای پیدا نشود ، 0 را برگرداند ، اگر یک مسابقه دقیق وجود نداشته باشد ، به دنبال بزرگترین مقدار بعدی باشید و از پایین به جای بالا به پایین جستجو کنید. آخرین تنظیم (Search_Mode) اگر مقداری که در جستجوی آن هستید ، عالی است و شما فقط مورد آخر را می خواهید.
در اصل ، نحو کامل Xlookup این است:
= Xlookup (lookup_value ، lookup_array ، return_array ، “if_not_found” ، match_mode ، search_mode)
اینجا جایی است که حتی بهتر می شود: Xlookup می تواند چندین نتیجه را به طور همزمان بازگرداند. به عنوان مثال ، به جای کشیدن درآمد فقط ، می توانید درآمد ، هزینه و سود خود را به یکباره بکشید:
= XLOOKUP (O2 ، I2: I200 ، L2: N200)
حتی می توانید آنها را مستقیماً اضافه کنید:
= SUM (XLOOKUP (O2 ، I2: I200 ، L2: N200))
- تصویر توسط آدا
- تصویر توسط آدا
بدون شمارش ستونی ، بدون مراجعه به منابع مطلق ، و محدودیتی در مورد کدام جهت می توانید نگاه کنید. بیایید بگوییم که می خواهید بر اساس شناسه آنها نام و بخش عضو تیم را جستجو کنید:
= XLOOKUP (A2 ، TeamMemberId_Column ، نام: Department_columns)
این فرمول تنها چیزی است که شما نیاز دارید.
در صورت عدم مطابقت دقیق ، می توانید match_mode را روی 1 یا −1 تنظیم کنید ، بنابراین اکسل به دنبال شکل بزرگتر بعدی یا کوچکتر بعدی است. همچنین می توانید مشخص کنید که آیا Excel با تنظیم Search_Mode به 1 یا -1 ، از بالا یا پایین جستجو را شروع می کند. 2
2
sumifs/countifs
اگر هنوز هم داده های خود را هر بار که به کل سریع نیاز دارید یا تعداد آنها را فیلتر می کنید ، خیلی سخت کار می کنید. sumifs و countif ها قهرمانان ناخوشایند هستند ، به ویژه برای گزارش های فروش ، بودجه یا هر مجموعه داده ای که در آن به محاسبات مشروط نیاز دارید.
بیایید تصور کنیم که رئیس شما از شما سؤال می کند ، “کل فروش ما در آسیا برای محصولات بیش از 150 دلار که به صورت آنلاین سفارش داده شده اند چیست؟” به جای ایجاد سه فیلتر و امیدواری که ورق را خراب نکنید ، می توانید با یک خط پاسخ دهید:
= sumifs (sum_range ، معیارها_RANGE1 ، معیارها 1 ، [معیارها_RANGE2 ، معیارها 2] …) = sumifs (sales_column ، region_column ، “آسیا” ، price_column ، “> 150” ، saleschannel_column ، “آنلاین”)
یا در یک مجموعه داده واقعی:
= sumifs (L2: L200 ، A2: A200 ، “آسیا” ، J2: J200 ، “> 150” ، D2: D200 ، “آنلاین”)
این فرمول تمام مقادیر موجود در ستون L را جمع می کند که در آن ستون A برابر با “آسیا” ، ستون J از 150 بیشتر است و ستون D برابر با “آنلاین” است.
اگر در حال آزمایش مقادیر متن هستید ، اطمینان حاصل کنید که معیارهای شما در علائم نقل قول است.
Countifs به همان روش کار می کند ، به جز اینکه تعداد ردیف هایی را که شرایط شما را برآورده می کند ، برمی گرداند:
= Countifs (معیارها_RANGE1 ، معیارها 1 ، [معیار_range2 ، معیارهای 2] …) = Countifs (A2: A200 ، “آسیا” ، J2: J200 ، “> 150” ، D2: D200 ، “آنلاین”)
به جای یک مبلغ ، مانند SUMIFS ، تعداد سفارشات آسیا را که بصورت آنلاین سفارش داده شده و قیمت واحد بیش از 150 دلار دریافت می کنید ، دریافت خواهید کرد.
Sumifs و Countifs نیز دارای کارتهای وحشی هستند. به عنوان مثال ، شما می توانید همه کشورها را با “T” و همه موارد به جز میوه ها شروع کنید:
= Countifs (B2: B200 ، “= A*” ، C2: C200 ، “میوه”)
کارتهای وحشی مفید هستند ، اما به داده های پاک بستگی دارند. اگر مجموعه داده شما دارای سرمایه گذاری متناقض یا شخصیت های پنهان است ، ممکن است نتایج شما اشتباه به نظر برسد. پاکسازی سریع ورق Excel کثیف شما باعث سردردهای شما می شود.
زیبایی Sumifs و Countifs این است که آنها غیر مخرب هستند. شما به هیچ وجه مجموعه داده اصلی خود را خرد نمی کنید و خراب نمی کنید. و اگر به پیچیدگی بیشتری احتیاج دارید ، اکسل تا 127 جفت معیار پشتیبانی می کند.
بنابراین آیا شما در حال بررسی “چند مشتری در 30 روز گذشته بیش از 500 دلار هزینه کرده اید؟” یا “درآمد سفارشات آنلاین لوازم جانبی زیر 50 دلار در آمریکای شمالی چیست؟” ، این دو کارکرد فوراً جواب را به شما می دهند.
1
فیلتر کردن
عملکرد فیلتر فقط ممکن است رضایت بخش ترین عملکردی باشد که اکسل در سالها اضافه شده است. به یاد داشته باشید وقتی فیلتر کردن داده ها به معنای کلیک کردن از طریق منوها ، شرایط تنظیم و امیدوارید که به طور تصادفی ردیف های اشتباه را پنهان نکرده باشید؟ با فیلتر ، همه اینها به یک فرمول واحد کاهش می یابد ، و نحو اساسی ساده است:
= فیلتر (دامنه فیلتر ، معیارهای فیلتر)
بگویید می خواهید درآمد حاصل از فروش را ببینید که قیمت واحد بیش از 600 دلار بود:
= فیلتر (upenue_column ، unitssold_column> 600).
یا در یک مجموعه داده واقعی:
= فیلتر (L2: L200 ، J2: J200> = 600)
فوراً ، فقط شماره هایی را که با معیارهای شما مطابقت دارد مشاهده خواهید کرد. اگر هیچ چیز واجد شرایط نباشد ، می توانید به جای خطایی ، یک پیام برگشتی اضافه کنید:
= فیلتر (L2: L200 ، J2: J200> = 1000 ، “بدون مسابقه”)
دقیقاً مانند Sumifs ، شما به یک شرط محدود نمی شوید. شما می توانید از یا منطق استفاده کنید (حداقل یک شرط باید صحیح باشد) یا منطق (همه شرایط باید درست باشد). در اینجا چند نمونه ساده آورده شده است:
= فیلتر (L2: L200 ، (J2: J200> = 600)+(D200 = “آنلاین”) ، “بدون مطابقت”) = فیلتر (L2: L200 ، (J2: J200> = 600)*(D2: D200 = “آنلاین”) ، “بدون مسابقه”)
- تصویر توسط آدا
- تصویر توسط آدا
اولین فرمول مقادیر بازگرداندن بالاتر از 600 دلار یا به صورت آنلاین فروخته می شود. دوم فقط مقادیر بالاتر از 600 دلار را برمی گرداند و به صورت آنلاین فروخته می شود.
هنگام استفاده از چندین شرایط ، هر یک را در پرانتز بپیچید. در غیر این صورت ، اکسل نمی داند چگونه آنها را به درستی ارزیابی کند.
فیلتر در هنگام ترکیب با مرتب سازی بهتر می شود. فرض کنید سلول O2 حاوی “گوشت” است ، و شما می خواهید همه ردیف های تطبیق یافته بر اساس کشور:
= مرتب سازی (فیلتر (A2: N200 ، C2: C200 = O2 ، “”) ، 2،1)
این همه سوابق فروش برای خریدهای گوشتی را به شما می دهد ، که طبق یک ستون مشخص به ترتیب صعودی ترتیب داده شده است (در مورد من ، ستون 2 را انتخاب کردم ، که ستون کشور من است). 1 را در انتهای دم فرمول به -1 تغییر دهید و به جای آن نتایج شما به ترتیب نزولی ظاهر می شود.
فیلتر یک محدوده زنده را برمی گرداند. اگر داده های شما به روز می شود ، نتایج شما به طور خودکار به روز می شود. می توانید دامنه را به اشتراک بگذارید یا حتی از آن به عنوان منبع فرمول دیگری استفاده کنید. و اگر پرونده را به کسی ارسال کنید که نسخه آن بدون فیلتر باشد ، نگران نباشید. آنها هنوز هم نتایج را می بینند (نه فرمول زنده).
با هم ، xlookup ، sumifs/countifs ، و فیلتر بسیاری از کارهای تکراری و با کلیک سنگین را از بین می برد که ما به طور معمول کاربران را از آن خود می کنیم. اگر این هفته فقط یکی را به ابزار ابزار خود اضافه می کنید ، یکی را انتخاب کنید که شما را از سخت ترین راه حلی که امروز با آن زندگی می کنید ، نجات دهد. هنگامی که می بینید چقدر زمان شما را صرفه جویی می کند ، علاقه مند به تلاش دیگران خواهید بود.