การเชื่อมข้อมูลแบบมีเงื่อนไข ด้วยการใช้ vlookup

[wasinee35]

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

1. ชีท Defect Record
[โดยในชีทนี้เราจะลงรายละเอียด defect ทุกอย่างที่เกิดขึ้นกับเครื่องจักรคะ โดย defect ที่เกิดขึ้นจะมี รายละเอียดในการแก้ไขบันทึกลงไปด้วยคะ โดยแบ่งออกเป็น แก้ไขได้โดยไม่ต้องเปลี่ยนชิ้นส่วนอะไหล่ กับ แก้ไขด้วยการเปลี่ยนชิ้นส่วนอะไหล่ ซึ่งรายลเอียดการเปลี่ยนชิ้นส่วนเราจะไม่บันทึกลงในชีทนี้โดยตรงคะ เราจะไปบันทึกในชีท Unscheded Removal และให้ข้อมูลในการเปลี่ยนชิ้นส่วนอะไหล่ลิ้งกลับมาที่ชีทนี้อัตโนมัติคะ สำหรับ defect ที่ไม่มี การเปลี่ยนชิ้นส่วน ช่องนี้ต้องการให้เป็นช่องว่างไปนะคะ หรือ ให้ขึ้น NA ]

2. ชีท Unscheded Removal
[ชีทนี้เราจะบันทึกข้อมูลชิ้นส่วนอะไหล่ที่ทำการเปลี่ยนเพื่อแก้ไข defect ที่พบในชีท defect record ทั้งหมดนะคะ โดยจะบันทึกเฉพาะข้อมูลของชิ้นส่วนอะไหล่ที่เปลี่ยนเท่านั้น โดยจะอ้างอิงว่าชิ้นส่วนที่เปลี่ยนเป็นของเครื่องจักรตัวไหน เปลี่ยนไปเมื่อวันที่เท่าไหร่ ใช้เอกสารเลขที่เท่าไหร่ คะ

ในส่วนการลิ้งข้อมูลได้ลองใช้สูตร =VLOOKUP([@[DOC NO.]]&[@[MANUAL REF. NO]],Table3[[#All],[DOC NO.]:[B/N]],4,TRUE)
แต่ในส่วนของ ข้อมูล defect ของเครื่องจักรที่ไม่ได้แก้ไขด้วยการติดตั้งชิ้นส่วนเข้าไปใหม่ แต่เมื่อให้ลิ้งกับ unsched removal ทั้งที่ข้อมูลที่เป็นคีย์หลักไม่ตรงกัน แต่ไม่ทราบว่าทำไมถึงมี ข้อมูลชิ้นส่วนขึ้นมาด้วยนะคะ

รบกวนช่วยแนะนำหน่อยนะคะ ขอบคุณคะ

[snasui]

ให้อธิบายเพิ่มเติมว่า ชีทใด เซลล์ใด ต้องการคำตอบเป็นเท่าใด ด้วยเงื่อนไขใดจึงได้ค่าเท่านั้น จะได้การเข้าถึงปัญหาได้โดยไวครับ

[wasinee35]

คำตอบที่ต้องการคะ

ในชีท defect record คอลัมน์ companent / PN off / SN off / PN on / SN on ทั้งหมดนี้คือคอลัมน์ที่เราต้องการลิ้งคำตอบมาจากชีท Unsched Removal คะ โดยคำตอบที่ได้ในแต่ละ แถวจะเป็นไปตามนี้นะคะ

Row 1 : เนื่องจากไม่มีการเปลี่ยนชิ้นส่วน ดังนั้นคำตอบในแต่ละคอลัมน์จะเป็นดังนี้คะ
คอลัมน์ component คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ PN off คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ SN off คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ PN on คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ SN on คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ

Row 2 : เนื่องจากมีการเปลี่ยนชิ้นส่วน ดังนั้นคำตอบในแต่ละคอลัมน์จะเป็นดังนี้คะ
คอลัมน์ component คำตอบที่ต้องการคือ "SCREW" ,ลิ้งมาจาก Row 2 , column component ในชีท unsched removal
คอลัมน์ PN off คำตอบที่ต้องการคือ "40176-7" ,ลิ้งมาจาก Row 2 , column PN off ในชีท unsched removal
คอลัมน์ SN off คำตอบที่ต้องการคือ "09052000FDF94" ,ลิ้งมาจาก Row 2 , column SN OFF ในชีท unsched removal
คอลัมน์ PN on คำตอบที่ต้องการคือ "40176-7" ,ลิ้งมาจาก Row 2 , column PN ON ในชีท unsched removal
คอลัมน์ SN on คำตอบที่ต้องการคือ "16549" ,ลิ้งมาจาก Row 2 , column SN ON ในชีท unsched removal

Row 3 : เนื่องจากมีการเปลี่ยนชิ้นส่วน ดังนั้นคำตอบในแต่ละคอลัมน์จะเป็นดังนี้คะ
คอลัมน์ component คำตอบที่ต้องการคือ "LAMP" ,ลิ้งมาจาก Row 3 , column component ในชีท unsched removal
คอลัมน์ PN off คำตอบที่ต้องการคือ "64300-200" ,ลิ้งมาจาก Row 3 , column PN off ในชีท unsched removal
คอลัมน์ SN off คำตอบที่ต้องการคือ "N/A" ,ลิ้งมาจาก Row 3 , column SN OFF ในชีท unsched removal
คอลัมน์ PN on คำตอบที่ต้องการคือ "64300-200" ,ลิ้งมาจาก Row 2 , column PN ON ในชีท unsched removal
คอลัมน์ SN on คำตอบที่ต้องการคือ "W12043" ,ลิ้งมาจาก Row 2 , column SN ON ในชีท unsched removal

Row 4 : เนื่องจากไม่มีการเปลี่ยนชิ้นส่วน ดังนั้นคำตอบในแต่ละคอลัมน์จะเป็นดังนี้คะ
คอลัมน์ component คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ PN off คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ SN off คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ PN on คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ SN on คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ

-โดยคำตอบทั้งหมดต้องอ้างอิงมากจากเงื่อนไขเดียวกันคะ คือ
คอลัมน์ Doc No และ Manual ref no. ของชีท defect record และ unsched removal ต้องตรงกัน

ขอบคุณคะ

[snasui]

ไฟล์ที่แนบมาไม่มีชีทชือ defect record ช่วยแนบไฟล์มาใหม่ครับ

นอกจากนี้ การอ้างอิงใน Excel ควรใช้ตำแหน่งเซลล์, คอลัมน์ หรือบรรทัด เช่น คอลัมน์ I, คอลัมน์ R, คอลัมน์ Z, เซลล์ B2, C9, XFD100, บรรทัดที่ 5, บรรทัดที่ 1000 ฯลฯ แทนการอ้างอิงชื่อเป็น PN off, Doc No แต่เพียงอย่างเดียว เพราะจะเสียเวลาในการเข้าถึงข้อมูล

[wasinee35]

แนบไฟล์มาให้ใหม่นะคะ

แก้ไขการอ้างอิงตำแหน่งใน excel คะ

ในชีท defect record คอลัมน์ H/ I/J /K / L ทั้งหมดนี้คือคอลัมน์ที่เราต้องการลิ้งคำตอบมาจากชีท Unsched Removal คะ โดยคำตอบที่ได้ในแต่ละ แถวจะเป็นไปตามนี้นะคะ

Row 2 : เนื่องจากไม่มีการเปลี่ยนชิ้นส่วน ดังนั้นคำตอบในแต่ละคอลัมน์จะเป็นดังนี้คะ
คอลัมน์ H2 คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ I2 คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ J2 คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ K2 คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ L2 คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ

Row 3 : เนื่องจากมีการเปลี่ยนชิ้นส่วน ดังนั้นคำตอบในแต่ละคอลัมน์จะเป็นดังนี้คะ
คอลัมน์ H3 คำตอบที่ต้องการคือ "SCREW" ,ลิ้งมาจาก Row 2 , column E2 ในชีท unsched removal
คอลัมน์ I3 คำตอบที่ต้องการคือ "40176-7" ,ลิ้งมาจาก Row 2 , column F2 ในชีท unsched removal
คอลัมน์ J3 คำตอบที่ต้องการคือ "09052000FDF94" ,ลิ้งมาจาก Row 2 , column G2 ในชีท unsched removal
คอลัมน์ K3 คำตอบที่ต้องการคือ "40176-7" ,ลิ้งมาจาก Row 2 , column J2 ในชีท unsched removal
คอลัมน์ L3 คำตอบที่ต้องการคือ "16549" ,ลิ้งมาจาก Row 2 , column K2 ในชีท unsched removal

Row 4 : เนื่องจากมีการเปลี่ยนชิ้นส่วน ดังนั้นคำตอบในแต่ละคอลัมน์จะเป็นดังนี้คะ
คอลัมน์ H4 คำตอบที่ต้องการคือ "LAMP" ,ลิ้งมาจาก Row 3 , column E3 ในชีท unsched removal
คอลัมน์ I4 คำตอบที่ต้องการคือ "64300-200" ,ลิ้งมาจาก Row 3 , column F3 ในชีท unsched removal
คอลัมน์ J4 คำตอบที่ต้องการคือ "N/A" ,ลิ้งมาจาก Row 3 , column G3 ในชีท unsched removal
คอลัมน์ K4 คำตอบที่ต้องการคือ "64300-200" ,ลิ้งมาจาก Row 2 , column J3 ในชีท unsched removal
คอลัมน์ L4 คำตอบที่ต้องการคือ "W12043" ,ลิ้งมาจาก Row 2 , column K3 ในชีท unsched removal

Row 5 : เนื่องจากไม่มีการเปลี่ยนชิ้นส่วน ดังนั้นคำตอบในแต่ละคอลัมน์จะเป็นดังนี้คะ
คอลัมน์ H5 คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ I5 คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ J5 คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ K5 คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ
คอลัมน์ L5 คำตอบที่ต้องการคือ N/A หรือ ให้เป็นช่องว่างไปเลยก็ได้คะ

ขอบคุณคะ

[snasui]

ทำตามด้านล่างครับ

1. ที่ชีท DEFECT RECORD เซลล์ H1:J1 เขียนหัวคอลัมน์ให้เหมือนกับ ชีท Unsched Removal เซลล์ E1:G1, J1:K1 ตามลำดับ ถ้าจะมี Slash คั่นก็คั่นให้เหมือนกัน ถ้าไม่มีก็ต้องไม่มีเหมือนกัน
2. ที่ชีท DEFECT RECORD เซลล์ M1 คีย์หัวคอลัมน์เช่น Change เพื่อระบุว่ามีการเปลี่ยน จากนั้นที่ M2 ลงไปให้คีย์ค่า Y หากมีการเปลี่ยน คีย์ค่า N หากไม่มีการเปลี่ยน หรือหากมีคอลัมน์ใดที่แสดงว่าเปลี่ยนหรือไม่เปลี่ยนให้แจ้งมาใหม่ การกำหนดการระบายสีเซลล์เพื่อแสดงว่าเปลี่ยนนั้นไม่สามารถใช้สูตรธรรมดาในการตรวจสอบได้ หากจะทำต้องใช้ Excel 4 Macro Function หรือ VBA ซึ่งเหมาะสำหรับนักพัฒนาหรือผู้ที่สามารถเขียนโปรแกรมได้บ้าง และหากจะใช้ VBA จำเป็นต้องเขียนมาเองก่อน ติดแล้วค่อยถามกัน
3. เมื่อทำตามข้อ 2 โดยระบุค่าที่คอลัมน์ M เรียบร้อยแล้วที่เซลล์ H2 คีย์สูตร
   =IF(Table4[[Change]:[Change]]="N",#N/A,VLOOKUP(Table4[[DOC NO.]:[DOC NO.]],Table3[[DOC NO.]:[SN ON]],MATCH(Table4[[#Headers],[COMPONENT]],Table3[[#Headers],[DOC NO.]:[SN ON]],0),0))
   Enter > Copy ไปทางขวา

การอ้างอิงที่ถูกต้อง หากเขียนเป็น H2 คือเซลล์ H2 ไม่ใช่คอลัมน์ H2 หรือบรรทัด H2 ครับ

[wasinee35]

จากการลองแก้ไขสูตรนะคะ

ชีท defect record หลังจากได้ทำการเพิ่มคอลัมน์ change ไปแล้ว
เซลล์ H2 : L2 = M2 "N" ค่าที่ได้เป็น N/A ถุกต้องคะ แต่
เซลล์ H3 : L3 และ H4 : L4 = M3 , M4 "Y" แต่สูตรไม่สามารถแสดงค่าที่อ่านได้นะคะ

[snasui]

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

จากไฟล์ที่แนบมาล่าสุดสูตรที่ H2 ควรเป็นตามด้านล่าง

=IF(Table4[[Change]:[Change]]="N",#N/A,VLOOKUP(Table4[[DOC NO.]:[DOC NO.]],Table3[[DOC NO.]:[S/N On]],MATCH(Table4[[#Headers],[COMPONENT]],Table3[[#Headers],[DOC NO.]:[S/N On]],0),0))

[wasinee35]

แก้ไขเรียบร้อยแล้วคะ ตอนนี้ทำได้แล้วคะ

ขอบคุณมากคะอาจารย์