Excel Forum

ถามผู้รู้

วิธีการสร้างเงื่อนไขใน Drop Down List ทำยังไงบ้างครับ

โดยสิ่งที่ต้องการมีดังนี้ครับ

ต้องการข้อมูลตั่งต้นจาก Sheets list และ me Drop down list ใน Sheet order

ตอนนี้ได้ตั้งชื่อ กลุ่มคอลัมแล้ว

อยากให้หากเราเลือกDDLในคอลัม A แล้ว จะให้ DDL คอลัม B และ C โชว์แต่ที่เกี่ยวข้องเท่านั้น

แล้วใน DDL ไม่ต้องการให้โชว์ข้อมูลที่ซ้ำกัน และ ช่องว่าง

ตัวอย่างเช่น(ดูไฟล์แนบประกอบ) หากผมเลือกร้าน วัลลพ จะโชว์แต่รายการที่เกี่ยวข้องกับร้าน และโชว์ขนาดเฉพาะรายการที่ต้องการเท่านั้น

ผู้รู้บอกทีครับ

Thanks

ลองดูครับ

@menem

ขอบคุณ สำหรับคำตอบครับ

แต่อันนี้เป็นยกตัวอย่างเฉยๆ อยากได้คำตอบจาก Fileที่แนบไปครับ

เพราะเรื่องรายละเอียดมีเยอะกว่านี้ แต่เป็นข้อมูลลับของ บริษัท

ขอบคุณอีกครั้งครับ

ไฟล์ที่แนบมา โครงสร้างไม่ค่อยเอื้อให้ทำ Validate ในลักษณะนี้ (เท่าที่ผมทำเป็นครับ)

ดังนั้นจึงแสดงโครงสร้างข้อมูลใหม่ที่ทำให้การทำ Validate ง่ายขึ้น (ภายใต้เงื่อนไขว่า
ขนาดสินค้ามีแค่ เล็ก, กลาง, ใหญ่ : ถ้าไม่เป็นเช่นนี้ ให้ใช้เป็น สินค้า A - เล็ก, สินคา A - กลาง,
สินค้า A - ใหญ่, สินค้า A ขนาดพิเศษ แทน ) จะสังเกตุเห็นว่าผมเว้นคอลัมน์ B ไว้เสมอนะครับ

และให้ดูเงื่อนไขการทำ Validate ในแต่ละช่องด้วยนะครับเผื่อเอาไปประยุกต์ใช้ได้

ขอบคุณมากๆครับ เดี๋ยวจะลองดูครับ

ขอถามอีกนิดหน่อยครับ

=INDIRECT(("List2!B"&MATCH($B5,Customer,0)&":IV"&MATCH($B5,Customer,0)))

ถ้าต้องการข้อมูลจากแนวตั้ง List2! Bต้องเปลี่ยนเป็นอะไรครับ

=INDIRECT(("List2!B"&MATCH($B5,Customer,0)&":IV"&MATCH($B5,Customer,0)))
ให้ค่าเป็น Bxx:IVxx [ xx = เลขแถว ] โดยจะค้นจาก Customer ซึ่งเทียบเท่ากับ List2!A:A
ว่าข้อมูล $B5 อยู่ที่บรรทัดเท่าไหร่ แล้วเอามาแทน xx

ถ้าจะเอาเป็น Column อาจจะต้องเปลี่ยนโครงสร้างข้อมูลด้วยนะครับ และสูตรอาจจะเป็น
=INDIRECT(("List2!"&Address(2,MATCH($B5,List2!1:1,0))&":"&Address(65535,MATCH($B5,List2!1:1,0))))
หมายถึง ค้นค่าจากแถวที่ 1 เจอคอลัมน์เท่าไหร่ แปลงเป็น Address (โดยเลือกตั้งแต่บรรทัดที่ 2 ถึง 65535)
เพื่อเอาไปส่งต่อให้กับ Indirect อีกที

ไม่ได้ครับ

ลองช่วยทำให้หน่อยได้ไหมครับ

โดยอิงจาก Sheet "List"

Thanks

โครงสร้างไม่ค่อยเอื้อให้ทำ Validate ในแบบนี้
ลองดูนะครับว่าพอไหวหรือไม่

@Menem

งั้นดูตัวอย่างนี้น่าจะง่ายกว่าครับ

อันนี้เป็นสิ่งที่ไม่ได้ใช้งานแล้ว แต่จะประมาณนี้ครับ

แนะนำหน่อยครับ ช่องตารางจะเหมือนกับที่จะใช้จริง

สิ่งที่ต้องการคือ เมื่อ Drop down list คอลัม A แล้ว คอลัม B จะขึ้นที่เกี่ยวข้อง และ คอลัม C จะขึ้นที่เกี่ยวข้างกับทั้ง 2 และก็ D,E เหมือนกันครับ

ช่วยดูอีกซักครั้งครับ ผมลองพยายามแล้วงงมากครับ

Thanks

เท่าที่ผมทำได้นะครับ

ช่องแรก (TYPE) ผมทำแบบให้เลือกจากรายการที่เป็น Uniq ได้
แต่หลังจากนั้นจะทำไม่ได้ (ดังนั้นรายการมันจะซ้ำ ๆ กันได้)

และใช้พื้นที่ใน Sheet:Uniq เพื่อจับเอาแต่ละ Column มาต่อกัน
เพื่อเป็น Key ในการค้นหาบรรทัดแรก และจำนวนรายการที่สามารถเลือกได้

สิ่งสำคัญคือ ข้อมูลจะต้องถูก Sort มาแล้วนะครับ

หมายเหตุ : ถ้าจะให้ Advance มากกว่านี้ทำไม่เป็นครับ >_<"

อีกแบบหนึ่งครับ
ชีท List
อย่างที่คุณ menem บอก ตารางเดิมไม่สะดวกในการสร้าง range name เลยมาสร้างใหม่ด้วยสูตรครับ

1. E2 คีย์
=LOOKUP(CHAR(255),A$2:A2)
enter -> คัดลอกไป G2 และลงล่างจนสุดตาราง

2. แยกรายการไม่ซ้ำของแต่ละคน
2.1 I2 คีย์
=INDEX($E$2:$E$61,SMALL(IF(MATCH($E$2:$E$61,$E$2:$E$61,0)=ROW($E$2:$E$61)-ROW($E$2)+1,ROW($E$2:$E$61)-ROW($E$2)+1),COLUMNS($I2:I2)))
Ctrl+Shift+Enter -> คัดลอกไปทางขวาจนเห็นค่า error

2.2 I3 คีย์
=INDEX($F$2:$F$61,SMALL(IF(($E$2:$E$62=I$2)*FREQUENCY(MATCH(IF($E$2:$E$61=I$2,$F$2:$F$61),IF($E$2:$E$61=I$2,$F$2:$F$61),0),ROW($F$2:$F$61)-ROW($F$2)+1),ROW($F$2:$F$61)-ROW($F$2)+1),ROWS(I$3:I3)))
Ctrl+Shift+Enter -> คัดลอกไปทางขวาจนสุดสูตรในแถว 2 และลงล่างจนเห็นค่า error

2.3 H2 คีย์
=SUMPRODUCT(--ISTEXT(I2:N2))
enter

2.4 I1 คีย์
=SUMPRODUCT(--ISTEXT(I3:I19))
enter -> คัดลอกไปทางขวาจนสุดสูตรที่แถว 2

ที่ทำมาทั้งหมดนี้เพื่อหาว่ามีกี่คน และแต่ละคนมีรายการให้เลือกแบบไม่ซ้ำอะไรบ้างครับ

3. สร้าง dynamic range name ที่เป็นชื่อคน ผมสมมติว่าชื่อ _Name
Ctrl+F3 --> New... --> ตั้งชื่อ --> refer to: คีย์
=List!$I$2:INDEX(List!$I$2:$N$2,List!$H$2)

(range name ของขนาดก็สร้างแบบธรรมดาเลยครับ เพราะเข้าใจว่ามีให้เลือก 3 ขนาดเหมือนกันหมด หรือเปล่า )

4. ไปที่ชีท order
4.1 ที่ A5 ไปตั้งค่า data validation แบบเลือกจากรายการโดยให้ source คือ _Name
4.2 ที่ B5 เลือก data validation แบบรายการ แต่ตรง source ให้คีย์
=OFFSET(List!$I$3,0,MATCH($A5,List!$I$2:$N$2,0)-1,INDEX(List!$I$1:$N$1,MATCH($A5,List!$I$2:$N$2,0)))
แล้ว OK
4.3 ที่ C5 ตั้งค่า data validation โดยใช้ source เป็นขนาดที่ทำไว้
4.4 คัดลอก validation ในเซลล์ A5:C5 ไปใช้กับตำแหน่งอื่นๆ ครับ

DhitiBank wrote:อีกแบบหนึ่งครับ
ชีท List
อย่างที่คุณ menem บอก ตารางเดิมไม่สะดวกในการสร้าง range name เลยมาสร้างใหม่ด้วยสูตรครับ

1. E2 คีย์
=LOOKUP(CHAR(255),A$2:A2)
enter -> คัดลอกไป G2 และลงล่างจนสุดตาราง

2. แยกรายการไม่ซ้ำของแต่ละคน
2.1 I2 คีย์
=INDEX($E$2:$E$61,SMALL(IF(MATCH($E$2:$E$61,$E$2:$E$61,0)=ROW($E$2:$E$61)-ROW($E$2)+1,ROW($E$2:$E$61)-ROW($E$2)+1),COLUMNS($I2:I2)))
Ctrl+Shift+Enter -> คัดลอกไปทางขวาจนเห็นค่า error

2.2 I3 คีย์
=INDEX($F$2:$F$61,SMALL(IF(($E$2:$E$62=I$2)*FREQUENCY(MATCH(IF($E$2:$E$61=I$2,$F$2:$F$61),IF($E$2:$E$61=I$2,$F$2:$F$61),0),ROW($F$2:$F$61)-ROW($F$2)+1),ROW($F$2:$F$61)-ROW($F$2)+1),ROWS(I$3:I3)))
Ctrl+Shift+Enter -> คัดลอกไปทางขวาจนสุดสูตรในแถว 2 และลงล่างจนเห็นค่า error

2.3 H2 คีย์
=SUMPRODUCT(--ISTEXT(I2:N2))
enter

2.4 I1 คีย์
=SUMPRODUCT(--ISTEXT(I3:I19))
enter -> คัดลอกไปทางขวาจนสุดสูตรที่แถว 2

ที่ทำมาทั้งหมดนี้เพื่อหาว่ามีกี่คน และแต่ละคนมีรายการให้เลือกแบบไม่ซ้ำอะไรบ้างครับ

3. สร้าง dynamic range name ที่เป็นชื่อคน ผมสมมติว่าชื่อ _Name
Ctrl+F3 --> New... --> ตั้งชื่อ --> refer to: คีย์
=List!$I$2:INDEX(List!$I$2:$N$2,List!$H$2)

(range name ของขนาดก็สร้างแบบธรรมดาเลยครับ เพราะเข้าใจว่ามีให้เลือก 3 ขนาดเหมือนกันหมด หรือเปล่า )

4. ไปที่ชีท order
4.1 ที่ A5 ไปตั้งค่า data validation แบบเลือกจากรายการโดยให้ source คือ _Name
4.2 ที่ B5 เลือก data validation แบบรายการ แต่ตรง source ให้คีย์
=OFFSET(List!$I$3,0,MATCH($A5,List!$I$2:$N$2,0)-1,INDEX(List!$I$1:$N$1,MATCH($A5,List!$I$2:$N$2,0)))
แล้ว OK
4.3 ที่ C5 ตั้งค่า data validation โดยใช้ source เป็นขนาดที่ทำไว้
4.4 คัดลอก validation ในเซลล์ A5:C5 ไปใช้กับตำแหน่งอื่นๆ ครับ

อ่านแล้วท้อเลยค่ะ

ผมลองเขียนใหม่ขึ้นเป็นตัวอย่างนะครับ ค่อย ๆ ศึกษา และนำไปพัฒนาต่อครับ
มี 4 Field ที่ขึ้นต่อกัน ( ใช้ข้อมูล ภาค จังหวัด อำเภอ ตำบล )

ต้อง Zip เพราะใหญ่เกินไปครับ

janesit wrote:อ่านแล้วท้อเลยค่ะ

โปรดลืมไปเถอะครับ ทำแบบนั้นข้อจำกัดเยอะ แถมได้แค่ drop down ที่เกี่ยวข้องกันแค่ 2 fields ลองดูของคุณ menem ครับใช้วิธีเขียน user defined function และนอกจากนี้ผมเจอวิธีทำที่น่าสนใจอีกอย่าง ลองดูที่ลิ้งค์ด้านล่างนี้ครับ แต่เสียเวลาตอนแรกที่ต้องนั่งสร้าง unique list ของแต่ละตัวเลือก

dynamic cascading drop down - chandoo

มีตัวอย่างไฟล์ให้ดาวน์โหลด

sanyadraco wrote:ถามผู้รู้

วิธีการสร้างเงื่อนไขใน Drop Down List ทำยังไงบ้างครับ

โดยสิ่งที่ต้องการมีดังนี้ครับ

ต้องการข้อมูลตั่งต้นจาก Sheets list และ me Drop down list ใน Sheet order

ตอนนี้ได้ตั้งชื่อ กลุ่มคอลัมแล้ว

อยากให้หากเราเลือกDDLในคอลัม A แล้ว จะให้ DDL คอลัม B และ C โชว์แต่ที่เกี่ยวข้องเท่านั้น

แล้วใน DDL ไม่ต้องการให้โชว์ข้อมูลที่ซ้ำกัน และ ช่องว่าง

ตัวอย่างเช่น(ดูไฟล์แนบประกอบ) หากผมเลือกร้าน วัลลพ จะโชว์แต่รายการที่เกี่ยวข้องกับร้าน และโชว์ขนาดเฉพาะรายการที่ต้องการเท่านั้น

ผู้รู้บอกทีครับ

Thanks

อีกตัวอย่าง เป็นการใช้สูตรหาค่า Unique List เพื่อใช้ใน Validation และค่อนข้างซับซ้อนครับ

โดยมีลำดับการทำงานตามด้านล่าง

1. ชีต List
   1. เซลล์ F1 คีย์สูตรเพื่อ List รายชื่อไป
      =IFERROR(INDEX($A$2:$A$61,SMALL(IF($A$2:$A$61<>"",ROW($A$2:$A$61)-ROW($A$2)+1),COLUMNS($E1:E1))),"")
      Ctrl+Shift+Enter > Copy ไปทางขวาจนถึง N1
   2. เซลล์ F2 คีย์สูตรเพื่อ List รายการที่เกี่ยวข้องกับข้อ 1
      =IFERROR(INDEX(INDEX($B$2:$B$1000,MATCH(F$1,$A$2:$A$61,0)):INDEX($B$2:$B$1000,IF(G$1="",MATCH(F$1,$A$2:$A$61,0)+100,MATCH(G$1,$A$2:$A$61,0)-1)),SMALL(IF(INDEX($B$2:$B$61,MATCH(F$1,$A$2:$A$61,0)):INDEX($B$2:$B$1000,IF(G$1="",MATCH(F$1,$A$2:$A$61,0)+100,MATCH(G$1,$A$2:$A$61,0)-1))<>"",ROW(INDEX($B$2:$B$1000,MATCH(F$1,$A$2:$A$61,0)):INDEX($B$2:$B$1000,IF(G$1="",MATCH(F$1,$A$2:$A$61,0)+100,MATCH(G$1,$A$2:$A$61,0)-1)))-MIN(ROW(INDEX($B$2:$B$61,MATCH(F$1,$A$2:$A$61,0)):INDEX($B$2:$B$1000,IF(G$1="",MATCH(F$1,$A$2:$A$61,0)+100,MATCH(G$1,$A$2:$A$61,0)-1))))+1),ROWS(F$2:F2))),"")
      Ctrl+Shift+Enter > Copy ลงด้านล่างตามต้องการและ Copy ไปด้านขวาถึงคอลัมน์ N
2. ที่ชีต Order
   1. คลิกเซลล์ A5 > เข้าเมนู Data > Data Validation > ตรง Allow: เลือก List > ตรง Soruce: คีย์สูตร
      =OFFSET(List!$F$1,0,0,1,COUNTIF(List!$F$2:$N$2,"?*"))
      คลิก OK
   2. คลิกเซลล์ B5 > เข้าเมนู Data > Data Validation > ตรง Allow: เลือก List > ตรง Soruce: คีย์สูตร
      =OFFSET(List!$F$2,0,MATCH($A$5,List!$F$1:$N$1,0)-1,COUNTIF(OFFSET(List!$F$2,0,MATCH($A$5,List!$F$1:$N$1,0)-1,1000),"?*"))
      คลิก OK
   3. เซลล์ C5 ให้สร้าง Validation โดยทำแบบ Custom คีย์เพียง 3 ค่า คือ เล็ก, กลาง, ใหญ่ หรือหากใช้ List ก็ควรจะเลือกมาเพียง 3 เซลล์ที่เป็น เล็ก, กลาง, ใหญ่ ไม่ใช่เลือกมาทั้งหมด

รบกวนอาจารย์ ครับ ผมทำตาม dynamic cascading drop down - chandoo แล้วช่องสุดท้ายไม่แสดงผลครับ
ผมก็เลยทำตามคุณ menem ก็ได้แค่เริ่มครับ ไปต่อไม่เป็น

การทำ Validation แบบสัมพันธ์กันตามตัวอย่างที่แนบมาจะต้องสร้าง Range Name จำนวนมาก ปัญหาที่เกิดขึ้นคือยังสร้าง Range Name ไว้ไม่ครบครับ

ตัวอย่างการสร้าง Validation แบบสัมพันธ์กันอีกวิธีดูที่นี่ครับ wordpress/multi-level-relational-validation/

snasui wrote: Sat May 19, 2018 6:59 am การทำ Validation แบบสัมพันธ์กันตามตัวอย่างที่แนบมาจะต้องสร้าง Range Name จำนวนมาก ปัญหาที่เกิดขึ้นคือยังสร้าง Range Name ไว้ไม่ครบครับ

ตัวอย่างการสร้าง Validation แบบสัมพันธ์กันอีกวิธีดูที่นี่ครับ wordpress/multi-level-relational-validation/

ขอบคุณครับอาจารย์

รบกวน อาจารย์อีกครั้งครับ ผมอยากให้ช่อง E2 แสดงทุกตำบลครับ
ป.ล. แกะสูตรอาจารย์จากอีกหัวข้อที่อาจารย์ทำให้แล้ว ยังแกะไม่ได้ครับ