(آموزشی) شمارش تعداد کلمات در شیت های مختلف

[ali.b]

سلام دوستان
ی سوالی برام پیش اومد که ایا راهی هست بشه مشخص کرد در چندین شیت و رنج خاص چند بار کلمه مورد نظر تکرار شده؟
خوب با کد زیر مینویند بدون دردسر مشکلتون رو حل کنید
اول ی ماژول تحت VB ایجاد کنید و فانکشن زیر رو قرار بدید

Function myCountIf(rng As Range, criteria) As Long    Dim ws As Worksheet


    For Each ws In ThisWorkbook.Worksheets
        myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
    Next ws
End Function

حالا در سلولی که میخوایم در اون شمارش انجام بشه ررو این فرمول توش قرار میدیم

=myCountIf(I1,A1)

یعنی ، کلمه ای که در سلول A1 اومده رو ببین چندبار از بین تمام رنج های I1 در شیت های مختلف تکرار شده

[ali.b]

اگه کسی دوست نداره از vb استفاده کنه میتونه از این فرمول استفاده کنه

[color=#000000][font=consolas]=[/font][/color][color=#000000][font=consolas]sumproduct[/font][/color][color=#000000][font=consolas]([/font][/color][color=#000000][font=consolas]countif[/font][/color][color=#000000][font=consolas]([/font][/color][color=#000000][font=consolas]indirect[/font][/color][color=#000000][font=consolas]([/font][/color][color=#800000][font=consolas]"'"[/font][/color][color=#000000][font=consolas]&[/font][/color][color=#000000][font=consolas]d3[/font][/color][color=#000000][font=consolas]:[/font][/color][color=#000000][font=consolas]d5[/font][/color][color=#000000][font=consolas]&[/font][/color][color=#800000][font=consolas]"'!a:a"[/font][/color][color=#000000][font=consolas]),[/font][/color][color=#000000][font=consolas] b2[/font][/color][color=#000000][font=consolas]))[/font][/color]

در این فرمول a:a رنج مورد نظر که کلمات در شیتها در اون رنج هستن

d35 مثلا من چندتا تا شیت دارم در این رنج نام شیت هایی که مدنظرم هست تا رد اونا جستجو انجام بشه رو مینویسم

[ali.b]

Function myCountIf(rng As Range, criteria) As Long    Dim ws As Worksheet


    For Each ws In ThisWorkbook.Worksheets
        If ws.name <>[COLOR=#0000cd] "Sheet1" And ws.name <> "Sheet2" [/COLOR]Then
            myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
        End If
    Next ws
End Function

این کد برای فقط دو شیت کار میکنه که مشخصه

[ali.b]

حالا اگه من ی حالت دیگه بخوام یعنی بگم 4 تا از شیت ها رو در نظر نگیر

[COLOR=#00008B][FONT=Consolas]Function[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] myCountIf[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]([/FONT][/COLOR][COLOR=#000000][FONT=Consolas]rng [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]As[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] Range[/FONT][/COLOR][COLOR=#000000][FONT=Consolas],[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] criteria[/FONT][/COLOR][COLOR=#000000][FONT=Consolas])[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]As[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]Long[/FONT][/COLOR]    [COLOR=#00008B]Dim[/COLOR] i [COLOR=#00008B]As[/COLOR] [COLOR=#00008B]Integer[/COLOR]

    [COLOR=#00008B]For[/COLOR] i = [COLOR=#800000]1[/COLOR] [COLOR=#00008B]To[/COLOR] ThisWorkbook.Worksheets.Count - [COLOR=#800000]4[/COLOR]
        myCountIf = myCountIf + WorksheetFunction.CountIf(ThisWorkbook.Worksheets(i).Range(rng.Address), criteria)
    [COLOR=#00008B]Next[/COLOR] i [COLOR=#00008B][FONT=Consolas]End[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]Function[/FONT][/COLOR]

[ali.b]

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&{1,2,3,4,5}&"!K5"),"Active"))

[ali.b]

این کد دیگه نیاز به این نداره که تعداد شیت از قبل باید مشخص باشه و هر بار شیت جدید ایجاد شد و متن مورد نظر توش بود نشون میده
اما ی مشکلی هست که حتما باید سلول رو دوبار کلیک کنم تا تغییرات رو نشون بده
راهی هست که این مشکل رفع شه و مثل sum درجا تغییرات رو نشون بده

[ali.b]

این کد دیگه نیاز به این نداره که تعداد شیت از قبل باید مشخص باشه و هر بار شیت جدید ایجاد شد و متن مورد نظر توش بود نشون میده
اما ی مشکلی هست که حتما باید سلول رو دوبار کلیک کنم تا تغییرات رو نشون بده
راهی هست که این مشکل رفع شه و مثل sum درجا تغییرات رو نشون بده

Function myCountIf(rng As Range, criteria) As Long    Dim ws As Worksheet


    For Each ws In ThisWorkbook.Worksheets
        myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
    Next ws
End Function

حالا در سلولی که میخوایم در اون شمارش انجام بشه ررو این فرمول توش قرار میدیم
کد:

 =myCountIf(I1,A1)

[ali.b]

بلاخره حلش کردم
و این تابع و فانکشن مثل توابع ها بدون اپدیت تغییرات رو ثبت میکنه
[CODE]

Function CountYes()  Application.Volatile  For sht = 1 To Sheets.Count   If Sheets(sht).Range("a1") = "a" Then     tmpYes = tmpYes + 1   End If  Next CountYes = tmpYes
End Function

بعدش از تابع زیر استفاده میکنم

 =CountYes()

[ali.b]

در ضمن ممکنه دوستان بخوان چندین مورد رو در یک شیت مشخص کنن
از این روش استفاده کنن
فرضا ما میخوایم در سلول a1 در هر شیت (چه شیت هایی که بعدا اضافه میشه چه شیت های موجود) ممکنه ی تعدادی شیت حرف a و تعدادی حرفb باشه
این نمونه کمک میکنه تا بتونین جندین مورد رو بدست بیارین

برای محاسب a

Function Count[COLOR=#0000cd]Yesa[/COLOR]()  Application.Volatile
  For sht = 1 To Sheets.Count
   If Sheets(sht).Range("a1") = "a" Then
     [COLOR=#0000cd]tmpYesa = tmpYesa[/COLOR] + 1
   End If
  Next
 Count[COLOR=#0000cd]Yesa[/COLOR] = tmp[COLOR=#0000cd]Yesa[/COLOR]
End Function

و تابع مربوط به اون میشه

[RIGHT][COLOR=#333333]=[/COLOR][/RIGHT][COLOR=#0000cd][RIGHT]CountYesa[/RIGHT][/COLOR][RIGHT][COLOR=#333333]()[/COLOR][/RIGHT]

و برای شمارش حرف B تو همه شیت ها

Function CountYesb()  Application.Volatile
  For sht = 1 To Sheets.Count
   If Sheets(sht).Range("a1") = "b" Then
     [COLOR=#800080]tmpYesb = tmpYesb[/COLOR] + 1
   End If
  Next
[COLOR=#800080] CountYesb = tmpYesb[/COLOR]
End Function

با تابع

[RIGHT][COLOR=#333333]=[/COLOR][/RIGHT][COLOR=#800080][RIGHT]CountYesb[/RIGHT][/COLOR][RIGHT][COLOR=#333333]()[/COLOR][/RIGHT]

[ali.b]

در ضمن اون کدی که گفتم مشکل داره و هر بار باید فرمول نوشته بشه تا تغییرات رو نشون بده هم
با قرار دادن این گزینه Application.Volatile هم حل میشه