برای استخراج اولین و آخرین تاریخ فروش ، یک فایل نمونه داریم دارای 3 ستون که ستون اول نام فروشنده و ستون دوم تاریخ و ستون سوم مقدار فروش می باشد ، حالا می خواهیم با انتخاب نام فروشنده تاریخ اولین فروش به همراه میزان فروش و همچنین تاریخ آخرین فروش به همراه میزان فروش نمایش داده شود.
فهرست محتوا
استخراج اولین و آخرین تاریخ فروش
اگر در نظر بگیریم که که داده های ما در آدرسهایی که در تصویر بالا مشخص شده است وجود دارد ، برای اینکه تاریخ اولین فروش بر اساس شرط نام فرشونده ای که در سلول F3 وارد شده است را به دست بیاوریم کافیست از تابع XLOOKUP به شکل زیر استفاده کنیم .
=XLOOKUP(F3,$B$3:$B$15,$C$3:$D$15)
فرمول بالا برای به دست آوردن تاریخ و تعداد اولین فروش زمانی خروجی صحیح خواهد داشت که در محدوده داده های ما ، تاریخ از کوچک به بزرگ مرتب سازی شده باشد ، در غیر این صورت تابع XLOOKUP به اولین داده متناظر با نام فروشنده وارد شده در F3 که برسد ، تاریخ و تعداد فروش را نمایش خواهد داد. و در واقع اولین و آخرین تاریخ فروش درست نمایش داده نمی شود.
برای به دست آوردن آخرین تاریخ فروش
با فرض اینکه ستون تاریخ در محدوده داده ما از کوچک به بزرگ مرتب سازی شده باشد ، کافیست فرمول زیر را استفاده کنیم.
=XLOOKUP(F3,$B$3:$B$15,$C$3:$D$15,,,-1)
در واقع با اینکار در آرگومان آخر ایکس لوکاپ به اکسل میگوئیم که در محدوده داده از پائین به بالا دنبالا نام فروشنده وارد شده در F3 بگرد و تاریخ و تعداد را نمایش بده.
اولین و آخرین تاریخ فروش در محدوده نا مرتب
اما توضیحات بالا برای زمانی بود که ستون تاریخ در محدوده داده ما از کوچک به بزرگ مرتب سازی شده باشد ، در غیر اینصورت ما باید XLOOKUP را رو داده هایی که از قبل با فرمول مرتب سازی شده اند اجرا کنیم .
یعنی باید داخل XLOOKUP محدوده ها را با تابع SORT و تابع SORTBY مرتب سازی کنیم و کافیست که فرمول را به شکل زیر بنویسیم.
=XLOOKUP(F3,SORTBY($B$3:$B$15,$C$3:$C$15),SORT($C$3:$D$15,1)) =XLOOKUP(F3,SORTBY($B$3:$B$15,$C$3:$C$15),SORT($C$3:$D$15,1),,,-1)
خط اول فرومل بالا برای استخراج تاریخ اولین فروش از محدوده داده نامرتب می باشد و خط دوم برای نمایش آخرین تاریخ فروش در همان محدوده نا مرتب.
خب بهتر است به مرتب بودن محدوده داده خود دل خوش نکنیم که به یکباره از همان روش دوم استفاده نمائیم که اگر به صورتی تصادفی هم داده های خود را بر اساس ستونی به غیر ستون تاریخ مرتب سازی کردیم ، نتیجه ما دچار خطا نشود.
چون با ترکیب توابع مرتب سازی و تابع XLOOKUP دیگر مررتب سازی دستی تاثیری در نتیجه نخواهد داشت.
