راجر یک ستون کاربرگ دارد که حاوی لیستی از مجسمه ها است. او به یک روش ساده برای شمارش تعداد دفعات "موس" یا "سنجاقک" نیاز دارد، اما همیشه این تنها متن موجود در سلول نیست. او نمی تواند هیچ تابع "COUNTIF-CONTAINS" را پیدا کند، و می تواند آن را به صورت دستی با استفاده از Find and Replace انجام دهد، اما ارقام باید راه بهتری وجود داشته باشد.
در واقع چندین راه وجود دارد که می توانید به این موضوع نزدیک شوید. یک راه آسان این است که از فیلتر کردن استفاده کنید تا فقط ردیف هایی را که حاوی کلمه مورد نظر شما هستند نشان دهید. با دنبال کردن این مراحل می توانید این کار را انجام دهید:
شکل 1. کادر محاوره ای Custom AutoFilter.
- اطمینان حاصل کنید که داده های شما دارای عناوین ستونی تعریف شده است.
- یک سلول از داده های خود را انتخاب کنید.
- تب Data روبان را نمایش دهید.
- روی ابزار Filter در گروه Sort & Filter کلیک کنید. اکسل یک فلش کشویی را در کنار عناوین ستون قرار می دهد.
- روی فلش کشویی کنار عنوان ستون حاوی نام مجسمه ها کلیک کنید. تعدادی از گزینه های فیلتر نمایش داده می شوند.
- نشانگر ماوس را روی گزینه Text Filters ببرید و سپس از منوی Fly-out گزینه Contains را انتخاب کنید. اکسل کادر محاوره ای Custom AutoFilter را نمایش می دهد.
- فقط در سمت راست تنظیمات "حاوی"، آنچه را که می خواهید جستجو کنید، مانند "موس" (بدون علامت نقل قول) وارد کنید. (شکل 1 را ببینید.)
- روی OK کلیک کنید. داده‌های شما فیلتر می‌شوند تا فقط ردیف‌هایی را نشان دهند که حاوی متن مشخص‌شده در مرحله ۷ هستند.
در این مرحله می توانید، اگر تعداد ردیف های زیادی وجود ندارد، به سادگی ردیف های نمایش داده شده را بشمارید.
اگر ترجیح می دهید از یک فرمول کامل استفاده کنید، می توانید به تابع COUNTIF تکیه کنید. این روش کار می کند زیرا اکسل به شما امکان می دهد از حروف عام در آنچه در تابع جستجو می کنید استفاده کنید. فرض کنید آنچه را که در جستجوی آن هستید در سلول E1 قرار داده اید و می خواهید تمام نمونه هایی از ردیف های حاوی "موس" را در ستون A جستجو کنید. این فرمول برای استفاده است:
=COUNTIF(A:A,E1)
ترفند این است که این متن را در سلول E1 قرار دهید: *موس* . ستاره ها حروف عام هستند، به این معنی که COUNTIF هر ردیفی را که حداقل یک نمونه از حروف "موس" را با هر چیزی که آن کاراکتر را احاطه کرده باشد، می شمارد.
فرمول های دیگری نیز وجود دارد که می توانید از آنها استفاده کنید، مانند موارد زیر:
=COUNT(SEARCH(E1,A:A))
=SUMPRODUCT(IF(IFERROR(SEARCH(E1,A:A)>0*1,0),1,0))
=SUM(IF((ISNUMBER(SEARCH(E1,A:A))),1,0))
هنگام استفاده از هر یک از این فرمول ها، نیازی به ستاره های اطراف مقدار در E1 ندارید. آنها از حروف عام استفاده نمی کنند (یا نیازی ندارند). همچنین توجه داشته باشید که در برخی از نسخه های اکسل ممکن است لازم باشد این فرمول ها را با استفاده از Ctrl+Shift+Enter به عنوان فرمول های آرایه وارد کنید . (اگر از نسخه اکسل ارائه شده با Microsoft 365 استفاده می کنید، نیازی به انجام این کار ندارید.)
باید توجه داشته باشید که هر یک از فرمول های ارائه شده تاکنون (و همچنین روش فیلترینگ) دارای دو اشکال هستند. اول، آنها "حاوی کلمات" را شمارش می کنند. این به سادگی به این معنی است که اگر شما به دنبال "موس" هستید، کلمه حاوی "موس" (مانند "موسسه پد") به عنوان یک تطابق محاسبه می شود. دوم، اگر سلول حاوی دو نمونه از آنچه شما جستجو می‌کنید باشد (مانند "موس من موش شما است")، باز هم فقط به عنوان یک نمونه حساب می‌شود.
اگر نمی خواهید با این نوع نمونه ها مواجه شوید، بهترین روش این است که تابع تعریف شده توسط کاربر خود را ایجاد کنید تا شمارش را برای شما انجام دهد. این یکی فشرده است و می تواند شمارش را انجام دهد:
Function HowMany(rSource As Range, sWord As String) As Long
Dim X As Long
Dim V As Variant
Dim Arr As Variant
Dim sSearch As String
Const Pat As String = "[!A-Za-z0-9]"
Arr = rSource.Value
If VarType(Arr) < 8192 Then Arr = Split(rSource.Value & " ")
sSearch = Pat & LCase(sWord) & Pat
For Each V In Arr
V = " " & LCase(V) & " "
For X = 2 To Len(V) + 1
If Mid(V, X - 1, Len(sWord) + 2) Like sSearch Then
HowMany = HowMany + 1
End If
Next
Next
End Function
در کتاب کار خود باید موارد زیر را وارد کنید:
=HowMany(A:A,"mouse")
هنگام استفاده از تابع در کل ستون مانند این، ممکن است مدتی طول بکشد تا تابع برگردد. به عبارت دیگر، می تواند کند باشد. اگر به جای کل ستون، محدوده ای را در فراخوانی تابع مشخص کنید که فقط سلول هایی را که باید جستجو کنید، مشخص کنید.
