فقط نمایش حداکثر تکرارهای چندگانه در اکسل

Question

مایک دارای سه سلول (A1:A3) است که نتایج محاسبات را نشان می دهد. او به راهی نیاز دارد تا حداکثر مقداری را که تا به حال در هر یک از این سلول ها ظاهر شده است تعیین کند و آن مقدار را در سلول E5 ذخیره کند. او می داند که چگونه حداکثر از سه مورد را به دست آورد، اما وقتی کاربرگ را دوباره محاسبه می کند، اگر مقادیر A1:A3 کمتر از حداکثر مقدار در E5 باشد (بر اساس تعیین های قبلی حداکثر در A1:A3)، آنگاه E5 نباید تغییر کند. به عبارت دیگر، E5 فقط در صورتی باید تغییر کند که هر چیزی در A1:A3 بزرگتر از آنچه در E5 است باشد. مایک مطمئن نیست که چگونه چنین محاسبه ای را انجام دهد.

دو راه برای حل این مشکل وجود دارد. اولین مورد ایجاد یک فرمول ساده است که در سلول E5 قرار می گیرد:

=MAX(A1:A3,E5)

تابع MAX مقادیر مختلفی را که به آن ارجاع می دهد بررسی می کند و سپس حداکثر آن را برمی گرداند - دقیقاً همان چیزی که می خواهید. با این حال، از آنجایی که این فرمول در سلول E5 قرار می گیرد و به E5 نیز ارجاع می دهد، یک خطا برمی گرداند. این به این دلیل است که فرمول یک مرجع دایره ای ایجاد می کند. اکسل می تواند این موارد را مدیریت کند، اما برای انجام آن باید یک تغییر کوچک در پیکربندی ایجاد کنید:

شکل 1. گزینه های Formulas در کادر محاوره ای Excel Options.

1. کادر محاوره ای Excel Options را نمایش دهید. (در اکسل 2007 روی دکمه آفیس کلیک کنید و سپس روی گزینه‌های Excel کلیک کنید. در اکسل 2010 و نسخه‌های بعدی، تب File را در نوار نمایش داده و سپس روی گزینه‌ها کلیک کنید.)
2. روی Formulas در سمت چپ کادر محاوره ای کلیک کنید. (شکل 1 را ببینید.)
3. مطمئن شوید که کادر بررسی Enable Iterative Calculation انتخاب شده است.
4. روی OK کلیک کنید.

اکنون اکسل مراجع دایره ای مانند فرمول ساده ای را که در سلول E5 قرار داده اید مدیریت می کند.

روش دوم استفاده از ماکرو برای انجام محاسبات است. این رویکرد ممکن است ترجیح داده شود زیرا ممکن است نخواهید (به دلایلی) مراجع دایره ای را در کتاب کار خود فعال کنید. زیر در واقع یک کنترل کننده رویداد است که به کد کاربرگ اضافه شده است. (ساده ترین روش: روی برگه برگه کلیک راست کنید، پنجره کد را از منوی Context نشان داده و ماکرو را به آن پنجره کد اضافه کنید.)

Private Sub Worksheet_Calculate()
    Dim dMax As Double
    dMax = Application.WorksheetFunction.Max(Range("A1:A3"))
    If dMax > Range("E5") Then
        Application.EnableEvents = False
        Range("E5") = dMax
        Application.EnableEvents = True
    End If
End Sub

هر بار که کاربرگ دوباره محاسبه می شود، ماکرو فعال می شود. حداکثر A1:A3 را می گیرد و آن را با آنچه در E5 است مقایسه می کند. فقط اگر بزرگتر باشد آن مقدار در E5 قرار می گیرد.