Validation List แบบเลือกรายการย่อยต่อๆกันไป

เห็นถามกันบ่อยๆว่า จะทำอย่างไรให้คลิกเลือกชื่อจังหวัด แล้วให้อีกช่องหนึ่งคลิกเลือกอำเภอของจังหวัดนั้นๆ แล้วอีกช่องหนึ่งคลิกเลือกตำบลของอำเภอนั้นๆ จึงทำมาให้ดูครับ

ผมออกแบบตารางข้อมูลให้เก็บรายละเอียดตามแนวตั้งแทนเพื่อให้ใช้สูตรได้ง่ายและเก็บข้อมูลได้ง่ายขึ้น จะได้ไม่ต้องใส่ชื่อรายการซ้ำให้เสียเวลา

วิธีแรกใช้สูตร
=INDEX(SubGroup1,MATCH(List1,MainGroupList,0),0)

วิธีที่สองใช้สูตรยากขึ้นหน่อยแต่ให้ปรับขนาด List ได้ตามข้อมูลที่มีได้ด้วย
=OFFSET(INDEX(MainGroupList,MATCH(List1,MainGroupList,0)),0,1,1,Width1)

ผมเพิ่มสูตรนำข้อมูลในตารางมาจัดเรียงใหม่ตามแนวตั้ง จะได้ไม่ต้องเสียเวลามากรอกข้อมูลซ้ำครับ โดยใช้สูตรหาตำแหน่ง Row.0000Col
=SMALL(IF(NOT(ISBLANK(SubGroup1)),ROW(SubGroup1)+COLUMN(SubGroup1)/100000),ROW(INDIRECT("1:"&COUNTA(SubGroup1))))

จากนั้นใช้สูตรต่อไปนี้ดึงข้อมูลจากตำแหน่งเซลล์ตามเลข Row.0000Col
=INDIRECT(ADDRESS(INT(A14),ROUND((A14-INT(A14))*100000,0)))

เพิ่มสีเตือนเมื่อคลิกเลือกข้อมูลที่อยู่ต่างกลุ่มกัน โดยใช้ Conditional Formatting ด้วยสูตรต่อไปนี้ครับ
=SUMPRODUCT((MainGroupList=List1)*ROW(MainGroupList))<>SUMPRODUCT((SubGroup1=List2)*ROW(SubGroup1))

จะได้ระวังว่า กำลังเลือกข้อมูลต่างกลุ่มกันอยู่

ผมจัดตารางฐานข้อมูลแต่ละเรื่องให้กลับข้างกันกับตัวอย่างที่แล้วมา จะได้กรอกข้อมูลได้ง่ายขึ้นครับ

Quote:

---

Originally Posted by สมเกียรติ

ผมเพิ่มสูตรนำข้อมูลในตารางมาจัดเรียงใหม่ตามแนวตั้ง จะได้ไม่ต้องเสียเวลามากรอกข้อมูลซ้ำครับ โดยใช้สูตรหาตำแหน่ง Row.0000Col
=SMALL(IF(NOT(ISBLANK(SubGroup1)),ROW(SubGroup1)+COLUMN(SubGroup1)/100000),ROW(INDIRECT("1:"&COUNTA(SubGroup1))))

จากนั้นใช้สูตรต่อไปนี้ดึงข้อมูลจากตำแหน่งเซลล์ตามเลข Row.0000Col
=INDIRECT(ADDRESS(INT(A14),ROUND((A14-INT(A14))*100000,0)))

---

บรรทัดนี้หมายความว่าอย่างไร ทำงานอย่างไรครับ ไม่เคยเห็นเลย รบกวนด้วยครับ
"ตำแหน่งเซลล์ตามเลข Row.0000Col"

Quote:

---

Originally Posted by สมเกียรติ

ผมเพิ่มสูตรนำข้อมูลในตารางมาจัดเรียงใหม่ตามแนวตั้ง จะได้ไม่ต้องเสียเวลามากรอกข้อมูลซ้ำครับ โดยใช้สูตรหาตำแหน่ง Row.0000Col
=SMALL(IF(NOT(ISBLANK(SubGroup1)),ROW(SubGroup1)+COLUMN(SubGroup1)/100000),ROW(INDIRECT("1:"&COUNTA(SubGroup1))))

จากนั้นใช้สูตรต่อไปนี้ดึงข้อมูลจากตำแหน่งเซลล์ตามเลข Row.0000Col
=INDIRECT(ADDRESS(INT(A14),ROUND((A14-INT(A14))*100000,0)))

---

2 สูตรนี้ช่วยทำหน้าที่จัดเรียงข้อมูลที่กรอกไว้ในพื้นที่ตารางสี่เหลี่ยมหลาย row หลาย column มาเรียงติดต่อกันตามแนวตั้งครับ เราจะได้ไม่ต้องเสียเวลามากรอกค่าใหม่ อยู่ในแฟ้ม ListinList2.xls

=SMALL(IF(NOT(ISBLANK(SubGroup1)),ROW(SubGroup1)+COLUMN(SubGroup1)/100000),ROW(INDIRECT("1:"&COUNTA(SubGroup1))))

เป็นสูตรหาตำแหน่งของข้อมูลที่กรอกไว้ในพื้นที่ตาราง ให้ออกมาในโครงสร้างของเลข row ในหลักหน่วยขึ้นไป ต่อด้วยเลข col ในหลักทศนิยมครับ เช่น 7.00003 หมายถึงค่าที่มีตำแหน่งเซลล์จาก row 7 และ column 3

จากนั้นจึงใช้สูตร =INDIRECT(ADDRESS(INT(A14),ROUND((A14-INT(A14))*100000,0))) หาค่าจากเซลล์ row 7 และ column 3 มาเรียงไว้ตามแนวตั้งต่อ

Quote:

---

Originally Posted by สมเกียรติ

2 สูตรนี้ช่วยทำหน้าที่จัดเรียงข้อมูลที่กรอกไว้ในพื้นที่ตารางสี่เหลี่ยมหลาย row หลาย column มาเรียงติดต่อกันตามแนวตั้งครับ เราจะได้ไม่ต้องเสียเวลามากรอกค่าใหม่ อยู่ในแฟ้ม ListinList2.xls

=SMALL(IF(NOT(ISBLANK(SubGroup1)),ROW(SubGroup1)+COLUMN(SubGroup1)/100000),ROW(INDIRECT("1:"&COUNTA(SubGroup1))))

เป็นสูตรหาตำแหน่งของข้อมูลที่กรอกไว้ในพื้นที่ตาราง ให้ออกมาในโครงสร้างของเลข row ในหลักหน่วยขึ้นไป ต่อด้วยเลข col ในหลักทศนิยมครับ เช่น 7.00003 หมายถึงค่าที่มีตำแหน่งเซลล์จาก row 7 และ column 3

จากนั้นจึงใช้สูตร =INDIRECT(ADDRESS(INT(A14),ROUND((A14-INT(A14))*100000,0))) หาค่าจากเซลล์ row 7 และ column 3 มาเรียงไว้ตามแนวตั้งต่อ

---

ไม่ได้เข้ามาดู Excel Expert Volunteer's Article & News สักเท่าไร เลยไม่เห็นที่อาจารย์ Post ตอบ เพิ่มได้เข้ามาดูวันนี้ครับ
ขอบคุณครับ อาจารย์ เข้าใจขึ้นบ้างแล้วครับ ขอบคุณครับ