ایجاد سلول های وابسته

Question

Lance دو سلول در یک ورک بوک دارد: Sheet1!A1 و Sheet2!C3. او دوست دارد این سلول ها به یکدیگر وابسته باشند تا تغییر در هر یک از آنها منجر به تغییر دیگری شود.

پاسخ البته بستگی به نحوه ایجاد تغییر دارد. اگر تغییر به دلیل یک فرمول در هر سلول باشد که دوباره محاسبه می شود، پاسخ این است که به سادگی فرمول ها را طوری تغییر دهید که در هر سلول یکسان باشند.

اگر تغییر به صورت دستی انجام می شود، به این معنی که شخصی مقداری را در هر کدام از سلول ها وارد می کند، راه حل این است که از یک جفت ماکرو برای به روز رسانی سلول مقابل استفاده کنید. به عنوان مثال، با کلیک راست بر روی برگه Sheet1 و انتخاب View Code، مدیریت رویداد زیر را وارد کنید:

Private Sub Worksheet_Activate()
    Worksheets("Sheet1").Range("A1") = Worksheets("Sheet2").Range("C3")
End Sub

به طور مشابه، روی برگه Sheet2 کلیک راست کرده، View Code را انتخاب کنید و سپس موارد زیر را وارد کنید:

Private Sub Worksheet_Activate()
    Worksheets("Sheet2").Range("C3") = Worksheets("Sheet1").Range("A1")
End Sub

با قرار دادن این کنترل کننده های رویداد، هر زمان که Sheet1 فعال شود، سلول A1 روی هر چیزی که در Sheet2!C3 باشد تنظیم می شود. به طور مشابه، هنگامی که Sheet2 فعال می شود، سلول C3 روی هر چیزی که در Sheet1!A1 است تنظیم می شود.

این رویکرد یک نقطه ضعف دارد - مقادیر فقط زمانی هماهنگ می شوند که یک کاربرگ فعال شود. به علاوه، همگام سازی می تواند از کار بیفتد. برای مثال، فرض کنید Sheet2 را نمایش می دهید. در آن نقطه، سلول C3 با Sheet1!A1 همگام خواهد شد. (تا اینجا، خیلی خوب است.) اکنون، آنچه در سلول C3 وجود دارد را تغییر می دهید. با این حال، Sheet1!A1 با آن تغییر همگام نیست زیرا شما هنوز Sheet1 را فعال نکرده اید. اگر در این مرحله Sheet3 را فعال کنید و سپس به Sheet2 برگردید، تغییر شما در سلول C3 با مقدار همگام نشده Sheet1!A1 بازنویسی می شود.

یک روش بهتر این است که مطمئن شوید به روزرسانی ها بلافاصله انجام می شوند. این کار را می توان با استفاده از کنترل کننده رویداد زیر در Sheet1 انجام داد:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Application.EnableEvents = False
        Worksheets("Sheet2").Range("C3") = Range("A1")
        Application.EnableEvents = True
    End If
End Sub

این کنترل کننده رویداد هر بار که تغییری در Sheet1 رخ می دهد فعال می شود. اگر تغییر در سلول A1 انجام شود، آن تغییر نیز در Sheet2!C3 به روز می شود. برای تکمیل ترتیب متقابل، باید از یک کنترل کننده رویداد مشابه در Sheet2 استفاده کنید:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$3" Then
        Application.EnableEvents = False
        Worksheets("Sheet1").Range("A1") = Range("C3")
        Application.EnableEvents = True
    End If
End Sub

اگرچه این رویکرد بهتر از روش قبلی است، اما همچنان یک مشکل بالقوه را ارائه می دهد. بیایید بگوییم که شما کنترل کننده های رویداد را در جای خود دارید و در Sheet2 کار می کنید. حالا یک فرمول را در سلول C3 وارد می کنید. (بگذارید فرمول =B6+B7 باشد .) با وارد کردن فرمول، کنترل کننده رویداد فعال می شود و نتیجه فرمول در Sheet1!A1 کپی می شود. به این نکته مهم توجه کنید - این فرمول نیست که کپی می شود، بلکه نتیجه فرمول است. اگر در این مرحله، مقدار Sheet2!B6 را تغییر دهید، فرمول وابسته در Sheet2!C3 به روزرسانی می شود، اما این مقدار در Sheet1!A1 کپی نمی شود، زیرا کنترل کننده رویداد تغییر فرمولی را در Sheet2!C3 تشخیص نمی دهد. در این مرحله، دو سلول شما دوباره هماهنگ نیستند.

البته، می توانید کنترل کننده های رویداد را پیچیده تر کنید تا بررسی کنید آیا فرمولی در یکی از سلول ها وارد شده است یا خیر. مشکل این است که شما نمی خواهید که کنترل کننده فرمول را از یک سلول به سلول دیگر کپی کند زیرا به راحتی می توانید با نتایج اشتباهی روبرو شوید زیرا فرمول کپی شده ممکن است زمانی که دیگر در زمینه اصلی خود نباشد نتیجه مناسب را ارائه ندهد.

صادقانه بگویم، یک رویکرد بهتر این است که در مورد نحوه استفاده از داده های خود، با نگاه به یکپارچگی داده ها فکر کنید. یک قانون سرانگشتی خوب این است که داده ها باید فقط در یک مکان وجود داشته باشند. اگر به آن داده ها در جای دیگری نیاز دارید، به داده های اصلی مراجعه کنید. به عنوان مثال، شما ممکن است Sheet1!A1 را به سلول "مستر"، مخزن داده ها تبدیل کنید. سپس، در Sheet2!C3، فرمولی را اضافه می کنید که به Sheet1!A1 اشاره دارد. این بدان معنی است که شما نباید اجازه ورود داده به Sheet2!C3 را بدهید.

اگر این امکان پذیر نیست، باید مکانی را در ورک بوک خود تنظیم کنید که در آن کاربر داده ها را در یک نقطه وارد کند، و سپس Sheet1!A1 و Sheet2!C3 می توانند با استفاده از فرمول ها به آن مکان ورودی داده مراجعه کنند. بنابراین، هر دو سلول همیشه با داده های وارد شده توسط کاربر هماهنگ هستند، بدون نیاز به ماکروهایی که به راحتی می توانند داده های شما را از بین ببرند.