เทคนิคการทำ Validation แบบสัมพันธ์กัน มากกว่า 2 คอลัมภ์

[ตาโต]

จากความรู้ตาม Link http://snasui.blogspot.com/2010/07/validation.html

มีคำถามว่าสามารถทำได้มากกว่า 2 คอลัมภ์มั้ยคะ มีตัวอย่าง ตามไฟล์แนบค่ะ

[snasui]

ลองดูตัวอย่างตามไฟล์แนบครับ

สิ่งที่ควรทำคือ ฐานข้อมูลควรเติมข้อมูลให้เต็มครับ

สามารถดูตัวอย่างสูตรได้ที่

1. Insert > Name > Define
2. Data > Validaion
3. E22, F22

[ตาโต]

ช่วยอธิบายความหมายเพิ่มเติมในแต่ละสูตรได้มั้ยคะ
แล้วกรณีที่อาจจะมีคอลัมภ์ที่ 4 ที่ 5 ที่สัมพันธ์กันอีกมันจะใช้งานยังไงคะ

[snasui]

ถ้ามีหลายคอลัมน์ที่สัมพันธ์กันก็ต้องยุ่งยากซับซ้อนขึ้นไปอีกครับ

ไม่เข้าใจสูตรไหนยกมาถามกันเป็นสูตร ๆ ไป แล้วค่อยเชื่อมความสัมพันธ์กันไปเรื่อย ๆ ดีกว่าครับ ช่วงนี้ผมมีเวลาไม่มากพอที่จะอธิบายรวดเดียวครับ

จากด้านล่างให้ศึกษาตามลำดับคือ
๑. เติมฐานข้อมูลให้เต็มก่อน
๒. สรุปข้อมูลใหม่ตามตัวอย่างในคอลัมน์ F, G, H
๓. ศึกษาข้อ ๑, ๓, ๒ ด้านล่างตามลำดับ

ลองดูตัวอย่างตามไฟล์แนบครับ

สิ่งที่ควรทำคือ ฐานข้อมูลควรเติมข้อมูลให้เต็มครับ

สามารถดูตัวอย่างสูตรได้ที่

1. Insert > Name > Define
2. Data > Validaion
3. E22, F22

[ตาโต]

สอบถามเพิ่มเติมค่ะ

1. จากไฟล์ตัวอย่างที่ให้มา ถ้าข้อมูลอยู่คนละ Sheet สามารถทำได้มั้ยคะ รบกวนส่งตัวอย่าง มาให้ด้วยนะคะ
2. ถ้าเขียนสูตร E22 , F22 ผิด จะทำให้ C22 ไม่มีข้อมูลให้เลือกรึป่าวคะ

[snasui]

ข้อ 1 ทำได้ครับ เพื่อให้ง่ายผมแนะนำให้นำข้อมูลมาไว้ในชีทเดียวกันก่อน แล้วค่อย Cut ข้อมูลไปไว้อีกชีทตามต้องการ
ข้อ 2 เข้าใจถูกต้องแล้วครับ

Note: ปกติต้องส่งตัวอย่างที่เป็นปัจจุบันมาให้ก่อนผมถึงจะส่งตัวอย่างกลับไป ยกเว้นว่าจำนวนสูตรไม่เยอะมาก และไม่อยู่หลายตำแหน่งก็จะเขียนสูตรให้เลยโดยไม่แนบไฟล์

[ตาโต]

ส่ง ตัวอย่าง ไฟล์มาให้แล้วค่ะ

สูตรexcel3.xls

[snasui]

ลองตามไฟล์แนบครับ เพื่อให้เข้าใจได้ง่ายขึ้นผมปรับให้ลดการเขียนสูตรใน Validation เป็นมาเขียนตอน Define name แทน

สำหรับสูตรในเซลล์ D2 นั้นการกดแป้นให้รับสูตรจะต้องกด 3 แป้นเสมอคือ Ctrl+Shift+Enter เนื่องจากเป็นสูตร Array หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะคีย์เข้าไปเองไม่ได้ การปรับปรุงแก้ไขสูตรจะต้องกดให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้งครับ

[ตาโต]

ขอบคุณมากๆนะคะ ถึงแม้จะไม่ค่อยเข้าใจสูตรเท่าไหร่ แต่ก็นำไปใช้งานได้แล้วค่ะ

[ตาโต]

จากไฟล์ที่ให้มาครั้งล่าสุด ติดปัญหากับ Row ที่ 2 ค่ะ มันแสดงความสัมพันธ์ไม่ถูกต้องค่ะ คือ มันไปจำค่าของ row ที่1 มาแสดงค่ะ
ต้องแสดงผลแค่ 2 Size ค่ะ

aa.jpg

[snasui]

ต้องเข้าไปแก้สูตรของการให้ชื่อของช่วงเซลล์ที่ชื่อว่า Size ครับ โดยเข้าเมนู Insert > Name > Define > เลือกชื่อ Size

เดิมสูตรเป็น

=OFFSET(Mgr!$H$1,Template!$D$2,0,Template!$E$2)

แก้เป็น

=OFFSET(Mgr!$H$1,Template!$D2,0,Template!$E2)

เปลี่ยนชื่อ SubCat

เดิม

=OFFSET(Mgr!$C$2,MATCH(Template!$A$2,Mgr!$B$2:$B$65536,0)-1,0,COUNTIF(Mgr!$B$2:$B$65536,Template!$A$2))

เป็น

=OFFSET(Mgr!$C$2,MATCH(Template!$A2,Mgr!$B$2:$B$65536,0)-1,0,COUNTIF(Mgr!$B$2:$B$65536,Template!$A2))

จากนั้นกลับมาที่หน้า Template > Copy เซลล์ A2:E2 ลงด้านล่าง > ทดสอบเืลือกค่าและสังเกตการเปลี่ยนแปลง

เดิมผมไม่ได้เขียนให้ยืดหยุ่นเพราะถือว่าใช้ที่บรรทัดเดียว

[ตาโต]

ตอนนี้ได้แล้วจริง ๆ ค่ะ ขอบคุณอีกครั้งนะคะ (ขนาด copy เปลี่ยนชื่อ column ยังงมอยู่นาน )

[ตาโต]

เพิ่มเงื่อนไขอีกอย่างนึงค่ะ จะมีคอลัมภ์ที่ 4 ที่สัมพันธ์กันแต่คราวนี้ไม่ต้องการให้แสดงเป็น List Box แล้ว และต้องการให้แสดงผลลัพธ์ตามไฟล์แนบ ทำอย่างไรได้บ้างคะ เคยได้ยินคนบอกว่าถ้าใช้ IF มันได้ไม่เกิน 7 เงื่อนไข

สูตรexcel4.xls

[snasui]

ลองตามไฟล์แนบครับ

ที่ D2 คีย์

=INDEX(Mgr!$M$2:$M$57,MATCH(1,IF(A2=Mgr!$J$2:$J$57,IF(B2=Mgr!$K$2:$K$57,IF(C2=Mgr!$L$2:$L$57,1))),0))

Ctrl+Shift+Enter > Copy ลงด้านล่าง การแก้ไขปรับปรุงสูตรจะต้องคีย์ให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้งครับ

[ตาโต]

เราสามารถใช้คำสั่ง IF ร่วมกับการทำ List Box ได้มั้ยคะ เช่น ถ้าข้อมูลในคอลัมภ์ A = Bed จึงจะแสดง listbox ให้เลือกขนาด
แนบตัวอย่างมาด้วยค่ะ

สูตรexcel5.xls

[ตาโต]

ลองตามไฟล์แนบครับ

ที่ D2 คีย์

=INDEX(Mgr!$M$2:$M$57,MATCH(1,IF(A2=Mgr!$J$2:$J$57,IF(B2=Mgr!$K$2:$K$57,IF(C2=Mgr!$L$2:$L$57,1))),0))

Ctrl+Shift+Enter > Copy ลงด้านล่าง การแก้ไขปรับปรุงสูตรจะต้องคีย์ให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้งครับ

สำหรับสูตรนี้มีจำกัดจำนวน Row ด้วยเหรอคะ เพราะลองแล้ว ได้สูงสุด 19565 Row ข้อมูล 60,000 Row

[snasui]

ลองตามไฟล์แนบครับ

วิธีการ
1. ต้องให้ชื่อของข้อมูลที่ต้องการนำมาแสดงหากเลือก Bed ชื่อที่ให้ไว้คือ SubDepart
2. เขียนสูตรใน Validation เข้าไปดูได้โดยเลือก B2 > Data > Validation > สังเกตการเขียนสูตร

[ตาโต]

ลองตามไฟล์แนบครับ

ที่ D2 คีย์

=INDEX(Mgr!$M$2:$M$57,MATCH(1,IF(A2=Mgr!$J$2:$J$57,IF(B2=Mgr!$K$2:$K$57,IF(C2=Mgr!$L$2:$L$57,1))),0))

Ctrl+Shift+Enter > Copy ลงด้านล่าง การแก้ไขปรับปรุงสูตรจะต้องคีย์ให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้งครับ

สำหรับสูตรนี้มีจำกัดจำนวน Row ด้วยเหรอคะ เพราะลองแล้ว ได้สูงสุด 19565 Row ข้อมูล 60,000 Row

รบกวนตอบให้ด้วยค่ะ อยากรู้ว่าเป็นเพราะเขียนสูตรผิดหรือเป็นข้อจำกัดของ Excel หนะค่ะ

[snasui]

อืม...ขอบคุณที่ช่วยถามมาซ้ำไม่งั้นก็จะหลุดไป ผมลืมตอบครับ

คำตอบคือ สูตรข้างต้นไม่จำกัดบรรทัด ตราบใดที่ไม่ได้ใช้ทั้งคอลัมน์ เช่นด้านล่าง สังเกตว่าไม่มีการระบุบรรทัด เช่นนี้สูตรจะให้ค่าผิดพลาดครับ

=INDEX(Mgr!$M:$M,MATCH(1,IF(A2=Mgr!$J:$J,IF(B2=Mgr!$K:$K,IF(C2=Mgr!$L:$L,1))),0))

[ตาโต]

อืม...ขอบคุณที่ช่วยถามมาซ้ำไม่งั้นก็จะหลุดไป ผมลืมตอบครับ

คำตอบคือ สูตรข้างต้นไม่จำกัดบรรทัด ตราบใดที่ไม่ได้ใช้ทั้งคอลัมน์ เช่นด้านล่าง สังเกตว่าไม่มีการระบุบรรทัด เช่นนี้สูตรจะให้ค่าผิดพลาดครับ

=INDEX(Mgr!$M:$M,MATCH(1,IF(A2=Mgr!$J:$J,IF(B2=Mgr!$K:$K,IF(C2=Mgr!$L:$L,1))),0))

รบกวนช่วยตรวจสอบไฟล์ให้หน่อยค่ะ สังเกตุตั้งแต่ row ว่าช่วงหลัง ๆ จะไม่แสดงสูตร ส่งทาง Email นะคะ เพราะแนบไม่ได้ขนาดเกิน 1 mb