افزودن خودکار 20% به ورودی

Question

جولی می خواهد چند سلول ورودی در یک کاربرگ داشته باشد که وقتی شخصی مقداری را وارد می کند، به طور خودکار 20٪ به آنچه وارد شده اضافه می کند. به عنوان مثال، اگر شخصی 200 را در یکی از این سلول ها وارد کند، آنچه در واقع وارد می شود 240 است.

راه های زیادی وجود دارد که می توانید با استفاده از ماکروها به این کار نزدیک شوید. بهترین روش این است که یک ماکرو ایجاد کنید که به صورت خودکار، زمانی که یک سلول در کاربرگ تغییر می کند، اجرا شود. سپس می توانید بررسی کنید که آیا تغییر در یکی از سلول های ورودی انجام شده است و مقادیر را بر اساس آن تنظیم کنید. مثال زیر مقدار وارد شده را اگر در یکی از سه سلول ساخته شده باشد تغییر می دهد: A1، C3، یا B8.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rInput As Range
    Dim rInt As Range
    Dim rCell As Range

    change the input cell range as desired
    Set rInput = Range("A1, C3, B8")

    Set rInt = Intersect(Target, rInput)
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            If IsNumeric(rCell) Then
                With Application
                    .EnableEvents = False
                    rCell = rCell * 1.2
                    .EnableEvents = True
                End With
            End If
        Next
    End If
End Sub

به یاد داشته باشید که این یک کنترل کننده رویداد است، به این معنی که هر زمان که چیزی در کاربرگ تغییر کند فعال می شود (در این مورد). برای استفاده از این ماکرو، روی برگه کاربرگ راست کلیک کرده و از منوی Context، View Code را انتخاب کنید. اکسل ویرایشگر VB را نمایش می دهد و سپس می توانید کد Worksheet_Change را اضافه کنید.

توجه داشته باشید که کلید تشخیص اینکه آیا تغییر در یکی از سه سلول ورودی تعریف شده انجام شده است، تابع Intersect است. بررسی می کند که آیا یک تقاطع بین محدوده Target (سلول های تغییر یافته که کنترل کننده Worksheet_Change را فعال کرده اند) و محدوده rInput (سلول های ورودی شما) وجود دارد یا خیر. اگر وجود داشته باشد، rInt حاوی سلول هایی خواهد بود که قطع شده اند.

سپس ماکرو از آن سلول ها عبور می کند و اگر سلول ها دارای مقادیر عددی باشند، آن سلول ها را در 120 درصد ضرب می کند. (ضرب در 120% برابر است با افزایش مقدار 20%). توجه داشته باشید که وقتی ضرب انجام شد، ویژگی .EnableEvents روی False تنظیم می شود. اگر این حفاظت رعایت نمی شد، آنگاه هر ضرب دوباره این کنترل کننده رویداد را فعال می کند و شما به طور مکرر (و برای همیشه) مقدار سلول را در 120٪ ضرب می کنید.

اگر می خواهید در واقع پردازش های دیگری را روی مقادیر انجام دهید - مانند گرد کردن به تعداد مشخصی از اعشار یا یک مقدار صحیح - پس فقط باید تغییری در خط واحدی ایجاد کنید که عمل ضرب را انجام می دهد.

اگر سلول های ورودی شما در یک ناحیه به هم پیوسته هستند، یک رویکرد بهتر ممکن است این باشد که آن سلول های ورودی را به عنوان یک محدوده نام گذاری شده تعریف کنید، و سپس از آن محدوده نامگذاری شده در ماکرو برای تعیین محل تقاطع سلول های تغییر یافته استفاده کنید. به این ترتیب، وقتی یا اگر گروه سلول های ورودی شما تغییر می کند، نیازی به تغییر ماکرو ندارید.

برای استفاده از این رویکرد، اجازه دهید بگوییم که محدوده سلول های ورودی شما B7:B19 است. آن سلول ها را انتخاب کنید و سپس با استفاده از کادر نام در گوشه سمت چپ بالای ناحیه سلول ها، نام "plus20pct" را وارد کنید. این عمل نام را به محدوده اختصاص می دهد. سپس می توانید از آن نام در ماکرو استفاده کنید.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range

    change the input cell range as desired

    Set rInt = Intersect(Target, Range("plus20pct"))
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            If IsNumeric(rCell) Then
                With Application
                    .EnableEvents = False
                    rCell = rCell * 1.2
                    .EnableEvents = True
                End With
            End If
        Next
    End If
End Sub

توجه داشته باشید که تنها تغییر در نحوه تعیین تقاطع سلول ها است - تابع Intersect از محدوده "plus20pct" به عنوان پارامتر استفاده می کند. همه چیز مثل قبل کار می کند.

اکنون که نحوه انجام این کار را با استفاده از ماکروها دیدید، این سوال باقی می ماند که آیا واقعاً باید این کار را با استفاده از ماکرو انجام دهید یا خیر. اول، محدودیت هایی برای کارهایی که این ماکروها می توانند انجام دهند وجود دارد. به عنوان مثال، اگر کاربر شما تاریخ یا ساعتی را در یکی از سلول های ورودی وارد کند، چه؟ در داخل، اکسل تاریخ ها و زمان ها را به عنوان اعداد کنترل می کند، به این معنی که آنها نیز 20٪ افزایش می یابند.

دوم، باید در نظر بگیرید که اگر شخصی ساختار کاربرگ را با افزودن یا حذف سطرها یا ستون ها اصلاح کند، چه اتفاقی برای کاربرگ می افتد. ماکروها از مراجع سلول مطلق (A1، C3، و B8) یا یک محدوده نام گذاری شده (plus20pct) استفاده می کنند. در حالی که محدوده نام گذاری شده ممکن است با افزودن یا حذف سطر یا ستون تنظیم شود، مراجع سلول های مطلق تغییر نمی کنند. بنابراین، می توانید سلول هایی را که دیگر سلول های ورودی داده مورد انتظار نیستند، بررسی (و تنظیم) کنید.

سوم، اجازه دهید بگوییم که شخصی مقدار (200) را در یکی از سلول های ورودی شما وارد می کند. به طور خودکار 20% افزایش می یابد و 240 می شود. شخص این تغییر را می بیند و تعجب می کند که چه اتفاقی افتاده است، بنابراین سلول را انتخاب می کند و F2 را فشار می دهد تا شروع به ویرایش سلول کند. قبل از ایجاد تغییر، آنها به یاد می آورند که "اوه، بله، قرار است به طور خودکار 20٪ افزایش یابد." بنابراین، آنها به سادگی Enter را فشار می دهند تا مقدار 240 را بپذیرند. با این حال، اکسل این را به عنوان یک تغییر می بیند و 240 را 20 درصد افزایش می دهد و در نتیجه 288 به دست می آید – نه آنچه شما یا کاربر در نظر داشتید.

این دومین ملاحظه - سردرگمی کاربر - بزرگترین مشکل بالقوه با تغییر خودکار آنچه کاربر در کاربرگ وارد می کند است. یک رویکرد کمتر گیج کننده این است که یک منطقه ورودی به خوبی تعریف شده برای ورک بوک خود داشته باشید. کاربر ارقام را در ناحیه ورودی قرار می دهد و آن ارقام همانطور که آنها را وارد می کنند باقی می مانند. سپس، در سلول های دیگر یا در فرمول های خود، تنظیم را 20٪ انجام می دهید.

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