شمارش سلول ها بر اساس مورد

Question

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

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

=IF(A1>"",IF(EXACT(UPPER(A1),A1),"Upper",
IF(EXACT(LOWER(A1),A1),"Lower","Mixed")),"")

فرمول بررسی می کند که آیا چیزی در A1 وجود دارد یا خیر. اگر وجود داشته باشد، از تابع EXACT برای مقایسه محتویات با تبدیل های مختلف محتویات سلول ها استفاده می کند. اگر سلول A1 خالی یا کلمات Upper، Lower یا Mixed باشد، فرمول یک رشته خالی برمی گرداند.

فرمول را تا جایی که نیاز دارید در ستون B کپی کنید و سپس می توانید از نوع فرمول زیر برای تعیین تعداد استفاده کنید:

=COUNTIF(B:B,"Upper")

برای یافتن تعداد سلول های حروف کوچک یا مختلط، «بالا» را با «پایین» یا «مخلوط» جایگزین کنید.

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

=SUMPRODUCT(--(EXACT(A1:A100,UPPER(A1:A100))),--(A1:A100<>""))

از یک تغییر در این فرمول می توان برای برگرداندن تعداد سلول های کوچک استفاده کرد. تنها چیزی که در موارد زیر تغییر می کند استفاده از تابع LOWER به جای تابع UPPER است:

=SUMPRODUCT(--(EXACT(A1:A100,LOWER(A1:A100))),--(A1:A100<>""))

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

=SUMPRODUCT(--(NOT(EXACT(A1:A100,UPPER(A1:A100)))),--
(NOT(EXACT(A1:A100,LOWER(A1:A100)))),--(A1:A100<>""))

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

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

• از هر سلول از یک محدوده عبور کنید
• تعیین کنید که سلول موردی بالایی، پایینی یا مختلط است
• مقداری شمارنده را افزایش دهید
• مقداری را برگردانید

ماکرو زیر نمونه ای از نحوه پیاده سازی موارد فوق است:

Function CountCase(rng As Range, sCase As String) As Long
    Dim vValue
    Dim lUpper As Long
    Dim lMixed As Long
    Dim lLower As Long
    Dim rCell As Range
    lUpper = 0
    lLower = 0
    lMixed = 0

    For Each rCell In rng
        If Not IsError(rCell.Value) Then
            vValue = rCell.Value
            If VarType(vValue) = vbString _
                And Trim(vValue) <> "" Then
                If vValue = UCase(vValue) Then
                    lUpper = lUpper + 1
                ElseIf vValue = LCase(vValue) Then
                    lLower = lLower + 1
                Else
                    lMixed = lMixed + 1
                End If
            End If
        End If
    Next
    Select Case UCase(sCase)
        Case "U"
            CountCase = lUpper
        Case "L"
            CountCase = lLower
        Case "M"
            CountCase = lMixed
        Case Else
            CountCase = CVErr(xlErrValue)
    End Select
End Function

مشخص است که تعیین اینکه یک سلول دارای حروف بالا، پایین یا مختلط است یا خیر، به وضوح موضوع اصلی یک کلان مانند این است. انجام چنین تعیینی از همان فرآیندی استفاده می کند که در فرمول های کاربرگ انجام شد: محتویات سلول را با تبدیل حروف بزرگ یا کوچک آن محتویات مقایسه کنید. در این ماکرو مقدار سلول (vValue) با vValue که با تابع UCase یا LCase تبدیل شده است مقایسه می شود.

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

=COUNTCASE(A1:A100, "L")

برای آرگومان اول از محدوده ای که می خواهید ارزیابی شود استفاده می کنید. آرگومان دوم یک کاراکتر واحد است - L، M یا U - که نشان می دهد کدام تعداد را می خواهید برگردانید. اگر از مقدار دیگری برای آرگومان دوم استفاده کنید، تابع یک خطا برمی گرداند.