[حل شده] ایجاد لیست با استفاده از CheckBox

[sabertb]

با سلام و تبریک سال جدید به همه اساتید بزرگوار
جدیدا یک بانک اطلاعاتی از فیلم هام ایجاد کردم با اطلاعات کامل مانند Rate & Directors & Stars & year & ... حالا میخواستم در شیت دیگری هم ابزاری ایجاد کنم که بتونم با استفاده از آن لیست فیلم هام بر اساس فیلتر های مورد نظر ایجاد بشه . مثلا 22 عدد ژانر در سینما داریم که من برای هر کدوم یک CheckBox از قسمت Form ایجاد کردم که هر کدومش هم با Cell link به سلول متناظر اش در لیست ژانر ها ارتباط دارد .
حالا میخواهم هر کردوم از این چک باگس ها رو که تیک میزنم اسامی فیلم ها ایی که این ژانر هارا با هم دارند بیاره . مثلا اگر Action و Crime را تیک میزنم فیلم هایی که تو ژانرشون هردو را دارد لیست کند .
خودم تک تک میتونم فرمول بنویسم که اگر Action برابر با True شد اکشن رو جستجو کند در ژانر ها و نام فیلم رو بنویسد ولی برای 22 تا ژانر خیلی فرمول غولپیکری میشه و تازه من فیلتر هایی مانند سال و کشور یا ستاره ها یا امتیاز ها رو هم دارم که هرکدوم از آنها خودش داستانه.
اگه امکانش هست راهنمایی کنید .نمونه فایل هم پیوست میکنم

[amir_ts]

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

دوست عزیز همان طور که میدونید check Box طوری طراحی شده که این امکان رو به کاربر میده که چندین گزینه رو همزمان در حالت انتخاب داشته باشه برای انتخاب یک گزینه از بین گزینه ها به نظرم بهتر از Option Button استفاده کنید.
با یک فرمول آرایه ای به راحتی تمام موارد جستجو شده رو لیست میشه کرد.

=IFERROR(INDEX(Table1[#All];SMALL(IF(ISNUMBER(SEARCH(LOOKUP($D$2;$A$2:$A$23;$B$2:$B$23);Table1[[#All];[Genre]]));ROW(Table1[[#All];[Genre]])-1);ROW(A1));1);"") 

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

این فرمول برای تفکیک true ها برای امکان جستجو هست:

=C2&COUNTIF($C$2:$C2; TRUE) 

یک ستون کمکی در شیت Data base ایجاد کردم و با این فرمول شرایط check box های true شده (سه تا) رو با هر ردیف سنجیده و در صورت تطبیق با یک counter یک شماره برای ردیف در نظر گرفتم.

=IF(AND(ISNUMBER(SEARCH(INDEX(List!$B$2:$B$23, MATCH("True1",  List!$D$2:$D$23,0)),[@Genre])),  IF(COUNTIF(List!$C$2:$C$23,TRUE)>1,ISNUMBER(SEARCH(INDEX(List!$B$2:$B$23,  MATCH("True2", List!$D$2:$D$23,0)),[@Genre])),TRUE),  IF(COUNTIF(List!$C$2:$C$23,TRUE)  >2,ISNUMBER(SEARCH(INDEX(List!$B$2:$B$23, MATCH("True3",  List!$D$2:$D$23,0)), [@Genre])),TRUE)), MAX($J$2:$J2)+1, "") 

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

و در آخر در شیت list با یک فرمول ساده index و match ردیف های شماره گذاری شده رو فراخوانی کردم.

=IFERROR(INDEX(Table1[Name]; MATCH(ROWS($I$3:$I3); Table1[Key];0));"") 

[sabertb]

ممنون از پاسختون خیلی جالب بود ، اگر بخوایم بقیه موارد مانند نام کشور و امتیاز های بالاتر از عدد مورد نظر یا نام بازیگر مورد نظر و نام کارگردان توش لحاظ بشه کجای این فرمول جا پیدا میکنه ؟
من یه نمونه که خودم همرو انجام دادم قبل از پاسخ شما اینجا قرار میدم . ولی مشکل اینه که فرمول نویسیش زیاد شده و حدودا چند ثانیه صبر میکنه تا فیلتر هارو اعمال کنه مخصوصا که فایل اصلی من خیلی بیشتر از نمونه دیتا بیس داره

[amir_ts]

با سلام
فایل شما رو به سختی باز کردم و دیدم.خوب فرمول ها بدلیل شرایط زیاد طولانی شده،حالا شما ببینید سرعت کدوم فرمول بیشتره از آن استفاده کنید.
ببینید درفرمول ارسالی در صورتی Counter شماره میندازه که نتیجه تابع and ) true ) باشه.مشکلی که هست نوع داده های شماست شما هم باید نتیجه Check box ها رو بسنجید هم داده های نام کشور و امتیاز و نام بازیگر که البته نوع داده ها هم یکسان نیست.
چون لیست جستجو و مقایسه این فرمول بر اساس true و false های check box ها هست و نام کشور و امتیاز های بالاتر و نام بازیگر از نوع داده های عادی، فکر کنم این بخش ها رو نشه تو این فرمول لحاظ کرد.

[Amir Ghasemiyan]

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

Sub Autofiltering()
j = 0
Dim arr() As Variant
For i = 2 To 23
    If Range("C" & i) Then
        ReDim Preserve arr(j)
        arr(j) = "*" & Range("B" & i) & "*"
        j = j + 1
    End If
Next i
    Sheet2.ListObjects("Table14").Range.AutoFilter Field:=4, Criteria1:=arr, Operator:=xlFilterValues
End Sub

[Amir Ghasemiyan]

اون جوابی که دادم به این صورت عمل میکرد که هر فیلمی که یکی از ژانر ها رو داشته باشه نشون بده
حالا اگه بخواین فیلم هایی که همه ژانر های انتخابی رو همزمان داشته باشه نشون بده از این کد میتونین استفاده کنید

Sub Autofiltering1()
Dim arr As String
For i = 2 To 23
    If Range("C" & i) Then
        arr = arr & "*" & Range("B" & i) & "*"
    End If
Next i
    Sheet2.ListObjects("Table14").Range.AutoFilter Field:=4, Criteria1:=arr, Operator:=xlFilterValues
End Sub

[sabertb]

با سلام
فایل شما رو به سختی باز کردم و دیدم.خوب فرمول ها بدلیل شرایط زیاد طولانی شده،حالا شما ببینید سرعت کدوم فرمول بیشتره از آن استفاده کنید.
ببینید درفرمول ارسالی در صورتی Counter شماره میندازه که نتیجه تابع and ) true ) باشه.مشکلی که هست نوع داده های شماست شما هم باید نتیجه Check box ها رو بسنجید هم داده های نام کشور و امتیاز و نام بازیگر که البته نوع داده ها هم یکسان نیست.
چون لیست جستجو و مقایسه این فرمول بر اساس true و false های check box ها هست و نام کشور و امتیاز های بالاتر و نام بازیگر از نوع داده های عادی، فکر کنم این بخش ها رو نشه تو این فرمول لحاظ کرد.

ممنون از پاسختون خیلی خوب جواب میده در مورد ژانر ها فقط همون که شما گفتید هر جور فکر میکنم دیگر فیلتر ها رو توش نتونستم لحاظ کنم . باز اگر راه حلی از روش های دیگر پیدا کردید که سرعت فایلم رو بالا ببره ممنون میشم . من این فایل رو از لحاظ دیتابیس تکمیل کردم و ماکرو های نمایش پستر و اجرای فیلم های مورد نظر فیلترم رو هم اضافه کردم سرعت فایل برای اعمال فیلتر حدودا 10 ثانیه هست زیاده !

[sabertb]

اون جوابی که دادم به این صورت عمل میکرد که هر فیلمی که یکی از ژانر ها رو داشته باشه نشون بده
حالا اگه بخواین فیلم هایی که همه ژانر های انتخابی رو همزمان داشته باشه نشون بده از این کد میتونین استفاده کنید

Sub Autofiltering1()
Dim arr As String
For i = 2 To 23
    If Range("C" & i) Then
        arr = arr & "*" & Range("B" & i) & "*"
    End If
Next i
    Sheet2.ListObjects("Table14").Range.AutoFilter Field:=4, Criteria1:=arr, Operator:=xlFilterValues
End Sub

ممنون از پاسخ شما ، بیشتر دنبال نتیجه ای هستم که بالاهم گفتم . نتیجه فیلتر تو صفحه لیست مشاهده بشه با توجه به اینکه فیلتر های بیشتری هم هست مانند نام کشور و بازیگر و امتیاز و گارگردان و کشور . برای همین هم سعی کردم با فرمول نویسی برم چون همینطور که استاد عزیز Amir_TS فرمودند حالت های فیلتر های مورد نظرم با هم متفاوت هست و دیتا هم تو شرایط های مختلف یک جور طراحی نشده مثلا امتیاز باید بالای رقم مورد نظر رو بگیره و نام ستاره ها باید از فیلد مورد نظر سرچ بشه در حالی که تو نام کشور باید دقیق ذکر بشه مثل UK و Ukraine که نباید اینارو باهم اشتباه بگیره . خلاصه شیرتو شیره . من فایل رو آماده کردم این جواب ها رو هم میده فقط مشکل سرعت هست . شکی ندارم که راه های بهینه تری هم هست هم از لحاظ فرمول نویسی هم از لحاظ ماکرو.

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

Private Sub Image2_Click()
Private Sub Worksheet_Change(ByVal Target As Range)
Image2.Picture = LoadPicture(Application.ThisWorkbook.Path & Sheet2.Range("B1").Value & "POSTER (2).jpg")
Image2.Left = 1240
End Sub