[حل شده] به دست آوردن نزدیكترین تاریخ (كمتر) به یك تاریخ از بین مجموعه*ای از تواریخ

[ظهور 313]

سلام به همه دوستان
یك ستون پر از تاریخ دارم، می*خوام نزدیكترین تاریخ (كه كمتر از تاریخ مورد جستجو هست) را در یك ستون تاریخ پیدا كنم تا از اون در تابع vlookup استفاده كنم.
لطفا یه نفر راهنمایی كنه
فایل مثال هم قرار دادم

[generalsamad]

با سلام
ببینید این فرمول درست جواب میده؟

=index(b10:b15;match(b4;c10:c15;1)) 

[ظهور 313]

نه، متاسفانه در برخی حالت*ها جواب نمیده،
توی فایل قرارش دادم لطفا ببینید استاد

[generalsamad]

با سلام
فایل ضمیمه رو نگاه کنید
از دو ستون کمکی استفاده شده
یکی برای اختلاف بین تاریخ ها
یکی هم کمترین اختلافها رو رتبه بندی میکنه
در نهایت با استفاده از تابع جستجو خروجی مورد نظر رو پیدا میکنه
فایل ضمیمه گردید

[ظهور 313]

با سلام
فایل ضمیمه رو نگاه کنید
از دو ستون کمکی استفاده شده
یکی برای اختلاف بین تاریخ ها
یکی هم کمترین اختلافها رو رتبه بندی میکنه
در نهایت با استفاده از تابع جستجو خروجی مورد نظر رو پیدا میکنه
فایل ضمیمه گردید

متاسفانه باز هم اشتباه جواب میده
توی همین فایلی كه جواب دادید، مقدار درست، 965 است، در حالی كه عدد نزدیك بزرگتر وارد شده.
تازه، دو تا ستون كمكی، كار رو خیلی سخت میكنه، این فرمول رو میخوام توی ستون*های زیادی استفاده كنم و این كار رو خیلی سنگین می*كنه. فرمول ساده*تری نداره؟

[rasools13]

سلام به همه دوستان
یك ستون پر از تاریخ دارم، می*خوام نزدیكترین تاریخ (كه كمتر از تاریخ مورد جستجو هست) را در یك ستون تاریخ پیدا كنم تا از اون در تابع vlookup استفاده كنم.
لطفا یه نفر راهنمایی كنه
فایل مثال هم قرار دادم

سلام با تعریف تابع ذیل میتوانید نزدیک ترین تاریخ قبل از یک تاریخ مشخص رو در یک محدوده بدست بیارین یا در Vlookup استفاده کنید

Function NearDate(DateTarget As Date, Range As Range) As Variant
        Dim Result As Date
        For Each Rng In Range
            If Rng < DateTarget Then
                 Result = Application.WorksheetFunction.Max(Result, Rng)
            End If
        Next
        NearDate = Result
End Function 

[ظهور 313]

سلام با تعریف تابع ذیل میتوانید نزدیک ترین تاریخ قبل از یک تاریخ مشخص رو در یک محدوده بدست بیارین یا در Vlookup استفاده کنید

Function NearDate(DateTarget As Date, Range As Range) As Variant
        Dim Result As Date
        For Each Rng In Range
            If Rng < DateTarget Then
                 Result = Application.WorksheetFunction.Max(Result, Rng)
            End If
        Next
        NearDate = Result
End Function 

سلام
بله از لطف شما ممنونم. اما یه مشكل داره (البته در مورد كار بنده). من میخوام اگه توی محدوده، تاریخ مساوی با تاریخ مورد جستجو در آرگومان اول تابع شما وجود داشت، خود همون تاریخ برگرده و سراغ پایین تر از اون نره، تابع شما، به این صورتی كه عرض كردم، قابل اصلاح هست استاد؟

[rasools13]

سلام
بله از لطف شما ممنونم. اما یه مشكل داره (البته در مورد كار بنده). من میخوام اگه توی محدوده، تاریخ مساوی با تاریخ مورد جستجو در آرگومان اول تابع شما وجود داشت، خود همون تاریخ برگرده و سراغ پایین تر از اون نره، تابع شما، به این صورتی كه عرض كردم، قابل اصلاح هست استاد؟

سلام بله ه صورت ذیل

Function NearDate(DateTarget As Date, Range As Range) As Variant
        Dim Result As Date
        For Each Rng In Range
          If Rng = DateTarget Then
      Result = DateTarget
                 Exit For
            Else If Rng < DateTarget Then
                 Result = Application.WorksheetFunction.Max(Result, Rng)
            End If
        Next
        NearDate = Result
End Function 

[ظهور 313]

سلام بله ه صورت ذیل

Function NearDate(DateTarget As Date, Range As Range) As Variant
        Dim Result As Date
        For Each Rng In Range
          If Rng = DateTarget Then
      Result = DateTarget
                 Exit For
            Else If Rng < DateTarget Then
                 Result = Application.WorksheetFunction.Max(Result, Rng)
            End If
        Next
        NearDate = Result
End Function 

خطا داد

[rahi_feri]

سلام
چک کنید....!

[rasools13]

خطا داد

ببخشید عبارت ElseIF میبایست بدون فاصله نوشته شود که اصلاح کردم .. کد ذیل... در ضمن برای نمایش صحیح تاریخ فرمت سلولی که فرمول را مینویسید Date باشد

Function NearDate(DateTarget As Date, Range As Range) As Date
        Dim Result As Date
        For Each Rng In Range
            If Rng = DateTarget Then
                Result = DateTarget
                Exit For
            ElseIf Rng < DateTarget Then
                 Result = Application.WorksheetFunction.Max(Result, Rng)
            End If
        Next
        NearDate = Result
End Function 

[ظهور 313]

ببخشید عبارت ElseIF میبایست بدون فاصله نوشته شود که اصلاح کردم .. کد ذیل... در ضمن برای نمایش صحیح تاریخ فرمت سلولی که فرمول را مینویسید Date باشد

Function NearDate(DateTarget As Date, Range As Range) As Date
        Dim Result As Date
        For Each Rng In Range
            If Rng = DateTarget Then
                Result = DateTarget
                Exit For
            ElseIf Rng < DateTarget Then
                 Result = Application.WorksheetFunction.Max(Result, Rng)
            End If
        Next
        NearDate = Result
End Function 

از لطف شما و دوست دیگرمون خیلی خیلی ممنونم

[ظهور 313]

ببخشید عبارت ElseIF میبایست بدون فاصله نوشته شود که اصلاح کردم .. کد ذیل... در ضمن برای نمایش صحیح تاریخ فرمت سلولی که فرمول را مینویسید Date باشد

Function NearDate(DateTarget As Date, Range As Range) As Date
        Dim Result As Date
        For Each Rng In Range
            If Rng = DateTarget Then
                Result = DateTarget
                Exit For
            ElseIf Rng < DateTarget Then
                 Result = Application.WorksheetFunction.Max(Result, Rng)
            End If
        Next
        NearDate = Result
End Function 

استاد ببخشید یه سوال دیگه هم داشتم.
گفتید كه میشه این تابع رو در تابع vlookup استفاده كنم. یعنی اگر بخوام آخرین تاریخ (در ستون b) مربوط به یك مقدار خاص (در ستون a) را به دست بیارم، باید چه جوری بنویسمش؟

[rasools13]

استاد ببخشید یه سوال دیگه هم داشتم.
گفتید كه میشه این تابع رو در تابع vlookup استفاده كنم. یعنی اگر بخوام آخرین تاریخ (در ستون b) مربوط به یك مقدار خاص (در ستون a) را به دست بیارم، باید چه جوری بنویسمش؟

فرمول vlookup محدودیت زیادی داره و زیاد هم منعطف نیست.. از ترکیب توابع Index و Match میتوانید همان کار vlookup را با انعطاف بیشتری انجام دهید .. بصورت ذیل
فایل پیوست رو هم ببینید

=INDEX(D8:D13;MATCH(NearDate(C5;C8:C13);C8:C13;0)) 

[ظهور 313]

فرمول vlookup محدودیت زیادی داره و زیاد هم منعطف نیست.. از ترکیب توابع Index و Match میتوانید همان کار vlookup را با انعطاف بیشتری انجام دهید .. بصورت ذیل
فایل پیوست رو هم ببینید

=INDEX(D8:D13;MATCH(NearDate(C5;C8:C13);C8:C13;0)) 

فرمول فایلتون به یه فایل دیگه ارجاع میده ؟!
و ظاهرا منظور بنده كاملا روشن نشده، روی همون فایل، منظورم رو بهتر توضیح دادم لطفا یه نگاه بندازید
ممنونم