قالب بندی خودکار برای مکان های اعشاری در اکسل

Question

جان یک کاربرگ ورود داده دارد که به کاربران اجازه می دهد اطلاعات را وارد کنند. او مایل است یک سلول به طور خودکار فرمت شود تا دقیقاً تعداد ارقام اعشاری را که کاربر تایپ می کند نمایش دهد. به عنوان مثال، اگر کاربر "12.345" را تایپ کند، می‌خواهد سلول به طور خودکار برای نمایش 3 رقم اعشار فرمت شود. جان می‌داند که می‌تواند از قالب عمومی برای یک سلول استفاده کند (که این کار را به خوبی انجام می‌دهد)، اما اگر کاربر مقداری را وارد کند که به 0 ختم می‌شود، مانند "12.34500"، که می‌خواهد برای نمایش (به طور خودکار) فرمت شود، این روش کار نمی‌کند. 5 رقم اعشار

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

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

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

=VALUE(A1) * 1.375

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

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Dim sEntry As String
    Dim dEntryNumber As Double
    Dim arr

    If Not Intersect(Target, Range("DataEntry")) Is Nothing Then
        Application.EnableEvents = False
        For Each c In Target.Cells
            If IsEmpty(c) Then
                c.NumberFormat = "@"     Reset to Text format
            Else
                If IsNumeric(c) Then
                    If Len(c.Value) = 0 Then
                        c.NumberFormat = "@"     Reset to Text format
                    Else
                        sEntry = c.Value
                        dEntryNumber = CDbl(sEntry)

                        arr = Split(sEntry, ".")
                        If UBound(arr) = 1 Then
                             Change NumberFormat in accordance with
                             the number of digits after the decimal point
                            c.NumberFormat = "0." & String(Len(arr(1)), "0")
                            c.Value = dEntryNumber
                        End If
                    End If
                End If
            End If
        Next c
        Application.EnableEvents = True
    End If
End Sub

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

تنها شرطی که این روش کار نمی کند این است که یک مقدار را در سلولی در محدوده DataEntry قرار دهید (که سلول را به فرمت عددی تبدیل می کند) و سپس مقدار عددی متفاوتی را در همان سلول وارد کنید. در آن مثال، ماکرو راهی برای دانستن اینکه آیا صفرهای انتهایی وارد شده است، ندارد. (به یاد داشته باشید که صفرهای انتهایی تنها در صورتی حفظ می شوند که سلول به صورت Text قالب بندی شده باشد. از آنجایی که سلول اینطور نیست، اکسل صفرهای انتهایی را حذف می کند و ماکرو با آن مقدار کار می کند که گویی وارد شده است.)