در ستون A Ron دارای یک سری مقادیر عددی است که به ترتیب صعودی مرتب شده اند. (مقادیر نتایج فرمول ها هستند.) او می خواهد از فرمول دیگری برای برگرداندن آدرس اولین سلول در ستون A استفاده کند که در آن مقدار از آستانه ای که در سلول E3 یافت می شود بیشتر است. مشکل این است که ران هیچ ایده ای در مورد چگونگی جمع آوری چنین فرمولی ندارد.
چندین رویکرد فرمولی وجود دارد که می توانید برای این موضوع اتخاذ کنید، و یکی که انتخاب می کنید می تواند به ماهیت داده هایی که تجزیه و تحلیل می کنید بستگی داشته باشد. اگر آدرس سلولی را می خواهید که آستانه برای اولین بار در آن برآورده شده یا برای اولین بار از آن فراتر رفته است، این فرمول ها به خوبی انجام می شوند:
=ADDRESS(ROW(XLOOKUP(E3,A:A,A:A,,1)),1)
=ADDRESS(XMATCH(E3,A:A,1),1)
با این حال، اگر می خواهید آدرس اولین سلولی که در آن آستانه واقعاً از آن فراتر رفته است (به عبارت دیگر، مطابقت با آستانه شمارش نمی شود)، فرمول های زیر کار خواهند کرد:
=ADDRESS(MATCH(E3,A:A,1)+1,1)
=ADDRESS(ROW(XLOOKUP(E3+0.001,A:A,A:A,,1)),1)
=ADDRESS(XMATCH(E3+0.001,A:A,1),1)
=ADDRESS(MATCH(E3,A:A,1)+1,COLUMN(A:A))
="A" & MATCH(E3,A:A,1)+1
=CELL("address",INDEX($A:$A,MATCH(E3,$A:$A,1)+1))
اگر هیچ چیزی در ستون A از آستانه در E3 فراتر نرود، هیچ یک از فرمول های بالا کار نمی کند - آنها به سادگی آدرس سلولی در ستون A را که یک ردیف فراتر از آخرین مقدار است، برمی گردانند. این مشابه چیزی است که با فرمول های زیر اتفاق می افتد:
=CELL("address",INDEX(A:A,MATCH(TRUE,INDEX(A:A>E3,0),)))
=ADDRESS(MATCH(MIN(IF(A:A>E3,A:A)),A:A,0),1)
=ADDRESS(MATCH(1,--(A:A>E3),0),COLUMN(A:A),4)
=SUBSTITUTE(CELL("address",INDEX(A:A,MATCH(1,--(A:A>E3),0))),"$","")
تفاوت این فرمول ها در این است که وقتی مقداری از آستانه فراتر نمی رود، آدرس اشتباهی را برمی گردانند، اما در عوض یک خطای #N/A را برمی گردانند. این بدان معنی است که هر یک از آنها می تواند در یک تابع IFNA قرار گیرد که شرایط خطا را "گیر" می کند و به شما امکان می دهد نشان دهید که چه کاری باید انجام شود. در اینجا نمونه ای از یکی از فرمول هایی که به این ترتیب پیچیده شده است:
=IFNA(ADDRESS(MATCH(MIN(IF(A:A>E3,A:A)),A:A,0),1),"Threshold not Exceeded")
این بسته بندی واضح است که فرمول ها را کمی طولانی تر می کند، اما نتایج فرمول را قابل درک تر می کند.
در نهایت، لازم به ذکر است که همه این فرمول ها باید در اکسل 2019 یا نسخه های جدیدتر به خوبی کار کنند. در نسخه های قبلی، بیشتر فرمول ها باید با استفاده از Ctrl+Shift+Enter به صورت فرمول آرایه وارد شوند . (شما باید بتوانید به نتایج بازگردانده شده توسط فرمول نگاه کنید و بگویید که آیا باید آن را به عنوان فرمول آرایه وارد کنید یا خیر.)
