تبدیل مورد مناسب با استثناء در اکسل

Question

فرانک باید روزانه 4000 تا 5000 نام را از حروف بزرگ به حروف مناسب تبدیل کند. تابع PROPER نتیجه قابل قبولی را به او می دهد که برای مقابله با کلمات اختصاری و غیره که نباید پنهان شوند، همچنان نیاز به بررسی و ویرایش دستی دارد. فکر فرانک این است که او احتمالاً به این تابع نیاز دارد تا یک جستجو در برابر نوعی لیست استثناء داشته باشد، بنابراین او نمی‌پرسد که آیا چنین قابلیتی برای تبدیل حروف در اکسل وجود دارد یا خیر.

بهترین راه برای رسیدگی به این امر به داده هایی که با آن شروع می کنید بستگی دارد. به عنوان مثال، اجازه دهید بگوییم که مقادیر زیر دو سلول در داده های منبع شما هستند:

Big Johns Mining, LLC
USA

اگر استثنای شما این است که نمی خواهید مورد LLC تغییر کند، پس به روشی نیاز دارید که به بخش هایی از هر سلول نگاه کند. اگر استثنای شما این است که نمی خواهید مورد USA تغییر کند، پس به روشی نیاز دارید که محتویات هر سلول را به طور کلی ارزیابی کند.

مراقبت از نوع دوم از داده ها آسان تر از نوع اول است، بنابراین اجازه دهید ابتدا به آن نگاه کنیم. فرمول زیر متکی به یک لیست استثنایی است که در جایی در کتاب کار خود دارید. این لیست استثنا باید به عنوان یک محدوده نامگذاری شده با استفاده از نام Exceptions تنظیم شود . اگر داده های اصلی شما در ستون A است، می توانید این فرمول را در سلول B1 قرار دهید و سپس تا آنجا که لازم است آن را کپی کنید:

=IFERROR(VLOOKUP(A1,Exceptions,1,0),PROPER(A1))

هر سلولی که به طور کامل با هر چیزی در جدول Exceptions شما مطابقت داشته باشد، در نهایت دقیقاً شبیه به استثنا خواهد بود، و هر سلولی که کاملاً مطابقت نداشته باشد، در نهایت PROPER روی آن اعمال می شود.

در مورد داده های نوع اول (جایی که باید داخل هر سلول را برای استثناها جستجو کنید)، بهتر است به یک ماکرو تکیه کنید. در زیر نمونه ای از مواردی است که می توانید به عنوان نقطه شروع استفاده کنید.

Function MyProper(ByVal r As Range) As String
    Dim vExceptions As Variant
    Dim vReplacements As Variant
    Dim vWords As Variant
    Dim iRaw As String
    Dim J As Integer
    Dim K As Integer
    Dim sTemp As String

     Exceptions array
    vExceptions = Array("USA", "PhD", "LLC", "and", _
      "Kentucky", "D.C.")

     Replacements array
    vReplacements = Array("USA", "PhD", "LLC", "and", _
      "KY", "DC")

     Convert the text to Proper case and store in a string
    iRaw = StrConv(r, 3)
     Split the words into an array
    vWords = Split(iRaw, " ")
    For J = LBound(vWords) To UBound(vWords)
        For K = LBound(vExceptions) To UBound(vExceptions)
            If UCase(vWords(J)) = UCase(vExceptions(K)) Then
                vWords(J) = vReplacements(K)
            End If
        Next K
    Next J

   Rebuild the cell contents
    sTemp = ""
    For J = LBound(vWords) To UBound(vWords)
        sTemp = sTemp & " " & vWords(J)
    Next J

    MyProper = Trim(sTemp)
End Function

این یک تابع تعریف شده توسط کاربر است، بنابراین می توانید از موارد زیر برای تبدیل داده های منبع خود استفاده کنید:

=MyProper(A1)

سرعت ماکرو به دو چیز بستگی دارد: تعداد دفعاتی که در کاربرگ شما استفاده می شود (تعداد کلماتی که باید تغییر دهید) و تعداد استثناهایی که در ماکرو بررسی می کنید. با بررسی 4000 تا 5000 کلمه و ده ها مورد استثنا، ماکرو همچنان باید آنقدر سریع کار کند که قابل قبول باشد. (مطمئناً سریعتر از انجام چک کردن با دست خواهد بود!)

این تابع به دو آرایه، vExceptions و vReplacements متکی است. با استفاده از تابع Split، محتویات سلول را در آرایه vWords منفجر می کند. (بعد از اجرای تابع Split، هر عنصر از آرایه vWords حاوی یک کلمه خواهد بود، همانطور که با وقوع یک فاصله تعریف شده است.) سپس هر عنصر از آرایه vWords با هر عنصر از آرایه vExceptions مقایسه می شود. اگر مطابقت داشته باشند (یا به عبارت بهتر، اگر نسخه بزرگ هر یک از آنها مطابقت داشته باشد)، عنصر مربوطه از آرایه vReplacements به جای کلمه اصلی استفاده می شود. این رویکرد دارای مزیت دیگری است که به شما امکان می دهد کلمات اختصاری را جایگزین کنید، همانطور که در جایگزینی KY برای کنتاکی و DC برای DC انجام می شود.

به یاد داشته باشید که من اشاره کردم که این ماکرو تنها یک نقطه شروع خوب است. بدیهی است که باید آن را تغییر دهید تا لیست استثناها و جایگزین های شما را منعکس کند. علاوه بر این، باید بدانید که اگر در داده‌های اصلی شما نشانه‌هایی وجود داشته باشد، آن علامت بخشی از "کلمات" منفجر شده توسط تابع Split در نظر گرفته می‌شود. به عنوان مثال، اگر داده های اصلی چیزی مانند "Davis, LLC, Stanton" داشته باشند، ویرگول ها بخشی از کلماتی هستند که دنبال می کنند. (به یاد داشته باشید که تقسیم در فاصله ها انجام می شود.) بنابراین، در نتیجه شما با "Davis, Llc, Stanton" در نتیجه خواهید رسید زیرا "LLC" در آرایه vExceptions با "LLC" که در vWords است مطابقت ندارد. آرایه.