باقی گذاشتن مقدار سلول بدون تغییر اگر یک شرط نادرست باشد در اکسل

Question

در حین استفاده از تابع IF، Vineet می‌خواهد در صورت نادرست بودن شرط، مقدار قدیمی را در سلول حفظ کند. به عبارت دیگر، مقدار در سلولی که تابع IF در آن استفاده می‌شود باید تنها در صورتی تغییر کند که شرط مورد آزمایش توسط تابع IF درست باشد. با این حال، به طور پیش فرض، اگر شرط False باشد، تابع IF مقدار 0 را می سازد.

تابع IF می تواند تا سه پارامتر را به خود اختصاص دهد. پارامتر اول مقایسه ای است که قرار است انجام شود، پارامتر دوم این است که اگر مقایسه درست باشد چه چیزی باید برگردانده شود، و پارامتر سوم اینکه اگر مقایسه نادرست باشد چه چیزی باید برگردانده شود. ممکن است آخرین پارامتر را کنار بگذارید، اما اگر این کار را انجام دهید، اکسل در صورت نادرست بودن مقایسه، مقدار 0 را برمی گرداند. (این همان چیزی است که Vineet با استفاده از تابع IF خود می بیند.)

پس راه حل واضح این است که مطمئن شوید که تابع IF را با چیزی ارائه می کنید که باید در صورت نادرست بودن مقایسه برگردانده شود. به عنوان مثال، فرض کنید فرمول شما در سلول B1 است و شما در حال مقایسه چیزی در سلول A1 هستید. فرمولی که استفاده می کنید ممکن است به شکل زیر باشد:

=IF(A1

توجه داشته باشید که اگر مقدار A1 کمتر از 10 باشد، کلمات "زیر ده" برگردانده می شوند. اگر این شرط برقرار نباشد، مقدار B1 برگردانده می شود. از آنجایی که این فرمول در سلول B1 است، به این معنی است که اگر شرط نادرست باشد، مقدار قبلی در سلول برگردانده می شود.

همچنین به این معنی است که فرمول حاوی یک مرجع دایره ای است. برای اینکه ارجاعات دایره ای به خوبی کار کنند، باید به اکسل اطلاع دهید که وجود آنها در کاربرگ شما اشکالی ندارد. اگر از اکسل 2010 یا نسخه جدیدتر استفاده می کنید، این مراحل را دنبال کنید:

شکل 1. تب Formulas در کادر محاوره ای Excel Options.

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

اگر از اکسل 2007 استفاده می کنید، Tools | را انتخاب کنید گزینه ها | تب Calculation را بزنید و مطمئن شوید که چک باکس Iteration انتخاب شده است. اکنون اکسل به مرجع دایره ای بدون شکایت اجازه می دهد.

اگر نمی‌خواهید به یک مرجع دایره‌ای در کاربرگ خود اجازه دهید، تنها راه این است که یک ماکرو ایجاد کنید که مقدار سلول B1 را بر اساس هرگونه تغییر در سلول A1 به‌روزرسانی کند:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
  ByVal Target As Range)

     See if the change is related to our cell
    If Not (Application.Intersect(Target, Range("A1")) _
      Is Nothing) Then
        If Range("A1") < 10 Then
            Range("B1") = "under ten"
        End If
    End If
End Sub

این ماکرو ساده، زمانی که به ماژول ThisWorkbook اضافه می شود، هر بار که تغییری در کتاب کار ایجاد می شود، اجرا می شود. اگر مقدار سلول A1 تغییر می کند (و فقط آن سلول)، آنگاه مقدار بررسی می شود تا ببینیم که آیا کمتر از 10 است یا خیر. اگر اینطور است، مقدار سلول B1 تغییر می کند. اگر اینطور نیست، مقدار موجود در سلول B1 به حال خود باقی می ماند.

یک "گوچا" وجود دارد که باید با هر یک از رویکردهایی که تاکنون مورد بحث قرار گرفته است، به خاطر داشته باشید، فرمول یا کلان. اگر مقدار در سلول A1 (مثلاً) 15 باشد، سلول B1 شامل آنچه قبلاً وجود داشت، هر چه بود، خواهد بود. اگر مقدار سلول A1 را به (مثلاً) 7 تغییر دهید، B1 به "زیر ده" تغییر خواهد کرد. خوب است، اما از آن نقطه به بعد سلول B1 هرگز تغییر نخواهد کرد. چرا؟ زیرا اگر سلول A1 را به مقداری بیشتر از 10 تغییر دهید، سلول B1 حاوی آنچه قبلاً وجود داشت (همانطور که توضیح داده شد) خواهد بود. و همانطور که اکنون متوجه شدید، ارزشی که قبلا وجود داشت، نتیجه نتیجه واقعی قبلی است که «زیر ده» بود. بنابراین، درست یا نادرست، فرمول یا ماکرو از این نقطه به بعد متن "زیر ده" را نمایش می دهد.