การค้นหาข้อมูลที่สัมพันธ์กันแต่บรรทัดไม่ตรงกัน

[janiemin]

เนื่องจากดูตัวอย่างจากลิงค์นี้คะ http://snasui.blogspot.com/2012/08/blog-post.html
พอลองเอามาทำในไฟล์ของตัวเองแล้วงงคะ ทำไม่ได้เลยมันขึ้นเป็น #N/A ตลอด
รบกวนช่วยดูสูตรให้หน่อยนะคะ
ขอบคุณมากคะ

[bank9597]

เนื่องจากดูตัวอย่างจากลิงค์นี้คะ http://snasui.blogspot.com/2012/08/blog-post.html
พอลองเอามาทำในไฟล์ของตัวเองแล้วงงคะ ทำไม่ได้เลยมันขึ้นเป็น #N/A ตลอด
รบกวนช่วยดูสูตรให้หน่อยนะคะ
ขอบคุณมากคะ

โจทย์ของคุณ กับตัวอย่างที่คุณไปทำตาม ไม่ได้มีความเหมือนกันครับ ถึงจะมีความคล้ายแต่ก็ต้องอาศัยความรู้ในการปรับแก้สูตรอยู่มากพอสมควร

ลองอธิบายโจทย์มาให้ดูหน่อยครับ พร้อมตัวออย่างคำตอบที่ถูกต้องมาด้วย เช่นที่ S16 ชีท HD-01 (A4) ต้องการให้แสดงคำตอบออะไร อ้างอิงจากไหนบ้างเป็นต้น

[janiemin]

ได้คะ จะค้นหาข้อมูลจาก Database ในหน้าที่ 2 ทางขวามือของชีท HD-01 (A4)
โดยนำข้อมูลมาใส่ไว้ในเชลล์ S16 โดยให้อ้างอิงจาก เลขแถวของสูตรคือ O16 และ ข้อความของเครื่องวัดน้ำ Z16
ในหน้าที่สองที่เป็นdatabase เลขแถวของสูตร คือ คอลลัม AQ และข้อความของเครื่องวัดน้ำ คือคอลลัม AP ข้อมูลที่ต้องการหาคือ คอลลัม AV เพื่อจะได้นำมาใส่ใน เชลล์ S16 คะ พอจะเข้าใจไหมคะ =]

[snasui]

ที่ S16 สามารถปรับสูตรเป็น

=VLOOKUP(O16+0,OFFSET($AQ$4,MATCH(Z16,$AP$4:$AP$225,0)-1,0,5,6),6,0)

หรือปรับเป็น

=VLOOKUP(O16+0,OFFSET($AQ$4,MATCH(Z16,$AP$4:$AP$225,0)-1,0,LOOKUP(9.99999999999999E+307,CHOOSE({1,2},5,MATCH(TRUE,INDEX(OFFSET(AP$4,MATCH(E2,$AP$4:$AP$225,0),0,7)<>"",0),0))),6),6,0)

เพื่อให้หาเฉพาะช่วงที่กำหนดโดยที่ตัวเลขในคอลัมน์ O อาจจะไม่มีอยู่ในช่วงที่ต้องการ Lookup ซึ่งหากเป็นเช่นนั้นก็จะแสดง #N/A แทนแสดงตัวเลขของช่วงอื่น

[janiemin]

รบกวนอีกทีคะ ลองใส่ สูตรนี้ดูคะ เพื่อไม่ให้มันขึ้น #N/A
=if(ISERROR(VLOOKUP(O18+0,OFFSET($AQ$4,MATCH(Z18,$AP$4:$AP$225,0)-1,0,5,6),6,0),"",VLOOKUP(O18+0,OFFSET($AQ$4,MATCH(Z18,$AP$4:$AP$225,0)-1,0,5,6),6,0)))

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

[bank9597]

รบกวนอีกทีคะ ลองใส่ สูตรนี้ดูคะ เพื่อไม่ให้มันขึ้น #N/A
=if(ISERROR(VLOOKUP(O18+0,OFFSET($AQ$4,MATCH(Z18,$AP$4:$AP$225,0)-1,0,5,6),6,0),"",VLOOKUP(O18+0,OFFSET($AQ$4,MATCH(Z18,$AP$4:$AP$225,0)-1,0,5,6),6,0)))

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

ที่ S16 คีย์ =IF(ISNA(VLOOKUP(O16+0,OFFSET($AQ$4,MATCH(Z16,$AP$4:$AP$225,0)-1,0,5,6),6,0)),"",VLOOKUP(O16+0,OFFSET($AQ$4,MATCH(Z16,$AP$4:$AP$225,0)-1,0,5,6),6,0)) คัดลอกลงมาครับ

[snasui]

ไม่ใช้สูตรของอาจารย์ที่อยู่ด้านล่างคะ เพราะว่างานนี้ไม่ต้องการค่าประมาณคะ ถ้าค่านั้นไม่มีก็ไม่ต้องใส่เลยคะ

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

=VLOOKUP(O16+0,OFFSET($AQ$4,MATCH(Z16,$AP$4:$AP$225,0)-1,0,LOOKUP(9.99999999999999E+307,CHOOSE({1,2},5,MATCH(TRUE,INDEX(OFFSET(AP$4,MATCH(E2,$AP$4:$AP$225,0),0,7)<>"",0),0))),6),6,0)

ซึ่งจะถูกต้องกว่าสูตรด้านล่างนี้

=VLOOKUP(O16+0,OFFSET($AQ$4,MATCH(Z16,$AP$4:$AP$225,0)-1,0,5,6),6,0)

เนื่องจากมีโอกาสที่จะ Lookup ค่าในช่วงข้อมูลอื่นได้

หากไม่มีค่าที่ Lookup ก็จะได้ผลลัพธ์เป็น #N/A ทั้งสองสูตร

รบกวนอีกทีคะ ลองใส่ สูตรนี้ดูคะ เพื่อไม่ให้มันขึ้น #N/A
=if(ISERROR(VLOOKUP(O18+0,OFFSET($AQ$4,MATCH(Z18,$AP$4:$AP$225,0)-1,0,5,6),6,0),"",VLOOKUP(O18+0,OFFSET($AQ$4,MATCH(Z18,$AP$4:$AP$225,0)-1,0,5,6),6,0)))

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

ที่มันไม่ยอมรับเพราะใส่วงเล็บผิดครับ ดูตัวอย่างการใส่วงเล็บตามสูตรทีคุณ bank9597 ตอบครับ

[bank9597]

อาจารย์ครับ ผมรบกวนนิดนึงครับ
ที่ BC4 ผมใส่สูตร =LOOKUP(CHAR(255),$AP$4:$AP4) คัดลอกลงมา ทำไมค่าที่ได้ ถึงไม่ครบทุกเซลล์ครับ ทั้งที่จริงควรจะได้ครบทุกเซลล์

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

รบกวนด้วยอธิบายให้ฟังหน่อยน่ะครับ

[snasui]

เนื่องจากที่เห็นว่าเป็นเซลล์ว่างนั้นไม่ได้ว่างจริง

ลองตามนี้ครับ

1. คลิกเซลล์ AL5 > กดแป้น F2
2. สังเกตดูผล

[bank9597]

เนื่องจากที่เห็นว่าเป็นเซลล์ว่างนั้นไม่ได้ว่างจริง

ลองตามนี้ครับ

1. คลิกเซลล์ AL5 > กดแป้น F2
2. สังเกตดูผล

ชักจะอารมณ์เสียกับ Excel แล้วครับอาจารย์ เบื้องลึกเบื้องหลังเยอะจริงๆ