شمارش رخدادهای رشته در ردیف های فرد

Question

جف یک کاربرگ با مقدار کمی متن در ستون C دارد. او می خواهد وقوع یک رشته خاص را در ستون C، اما فقط برای ردیف های فرد (1، 3، 5، و غیره) در داده ها شمارش کند. رشته ای که به دنبال آن است ممکن است کل محتوای سلول نباشد و ممکن است چندین بار در یک سلول خاص رخ دهد. (اگر 2 یا 3 بار در یک سلول اتفاق بیفتد، باید به عنوان 2 یا 3 رخداد حساب شود.) او نمی پرسد که آیا یک روش فرمولی برای انجام این کار، بدون توسل به ماکرو وجود دارد.

یکی از راه های نزدیک شدن به این موضوع، استفاده از ستون کمکی است. به عنوان مثال، اجازه دهید بگوییم که می توانید از ستون D به عنوان ستون کمکی استفاده کنید، و اولین سلول حاوی داده، سلول C2 است. (شاید سلول C1 دارای عنوان ستونی در آن باشد.) می توانید از فرمول زیر در سلول D2 استفاده کنید:

=IF(MOD(ROW(),2)=1,(LEN(C2)-LEN(SUBSTITUTE(C2,"my text","")))/LEN("my text"),"")

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

می توانید این فرمول را به تعداد ردیف های لازم کپی کنید و سپس ستون را جمع کنید. نتیجه تعداد دفعاتی است که رشته در ردیف های فرد ظاهر می شود.

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

=SUMPRODUCT((LEN(C:C)-LEN(SUBSTITUTE(C:C,"my text","")))/LEN("my text")*ISODD(ROW(C:C)))

این فرمول اساساً مانند فرمول قبلی عمل می کند، با این تفاوت که تابع SUMPRODUCT محاسبات را برای هر سلول در ستون C به صورت داخلی انجام می دهد. باید بدانید که از آنجایی که فرمول تمام ستون C را بررسی می کند، به این معنی است که اگر متن جستجوی شما ("متن من" ") در هر سلول غیر داده ای در ستون (مانند سرصفحه ستون) رخ می دهد، سپس در کل بازگردانده شده نیز لحاظ می شود.

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

Function CountInst(rSource As Range, sSearch As String, bCaseInsensitive As Boolean) As Integer
    Dim c As Range
    Dim iCount As Integer
    Dim sTemp1 As String
    Dim sTemp2 As String

    sTemp2 = sSearch
    If bCaseInsensitive Then sTemp2 = LCase(sTemp2)
    iCount = 0

    For Each c In rSource
        If c.Row Mod 2 = 1 Then
            sTemp1 = c.Text
            If bCaseInsensitive Then sTemp1 = LCase(sTemp1)

            iCount = iCount + (Len(sTemp1) - _
              Len(Replace(sTemp1, sTemp2, ""))) / Len(sTemp2)
        End If
    Next c

    CountInst = iCount
End Function

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

=CountInst(C2:C99,"my text",TRUE)