ใช้สูตร VLOOKUP กับข้อมูลจำนวนมากแล้วมีปัญหา

[manadee]

ผมมีข้อมูลอยู่ 2 ชุด
-ชุดแรกเป็นข้อมูลประชากรวัยเรียน ผมนำไปกรอกเอาไว้ใน sheet ชื่อ "ประชากรวัยเรียน"
-ชุดที่สองเป็นข้อมูลนักเรียน ผมนำไปกรอกเอาไว้ใน sheet ชื่อ "ข้อมูลเรียน"
ผมต้องการหาว่าประชากรวัยเรียน ใน sheet "ประชากรวัยเรียน" รายใดเรียนอยู่ที่ไหน จึงใช้สูตร VLOOKUP ค้นหาจาก sheet "ข้อมูลนักเรียน" หากพบให้ดึงชื่อโรงเรียนมาแสดงในคอลัมภ์ C ตอนทดสอบข้อมูลจำนวนไม่มากก็ไม่มีปัญหา ใช้งานได้ดี แต่พอนำไปปฏิบัติจริงข้อมูลประชากรวัยเรียนมีประมาณ 40,000 ราย ข้อมูลนักเรียนมีประมาณ 30,000 ราย สูตรกลับทำงานได้ไม่ถูกต้อง มีการแสดงว่าพบข้อมูลเพียงไม่กี่รายซึ่งไม่ถูกต้อง จึงไม่ทราบว่าเป็นข้อจำกัดของสูตร VLOOKUP หรือไม่ ผมได้แนบไฟล์ตัวอย่างมาให้ดูแล้ว ถ้าใช้สูตร VLOOKUP ไม่ได้ มีสูตรอื่นใช้แทนหรือไม่ ขอคำแนะนำด้วยครับ ผมจะส่งไฟล์ที่มีปัญหามาให้ดูก็มีขนาดใหญ่เกินไปส่งมาไม่ได้ จึงส่งไฟล์ตัวอย่างมาให้ดูแทน

[snasui]

ฟังก์ชั่น Vlookup หากนำไปใช้กับข้อมูลปริมาณมากแล้วจะทำงานช้า ข้อมูลมากก็จะช้ามากครับ แต่ไม่ใช่ว่าเมื่อข้อมูลมากแล้วจะทำงานผิดพลาดครับ

จากตัวอย่างไฟล์ที่แนบมาลองปรับสูตรที่ C2 เป็น

=INDEX(ข้อมูลนักเรียน!$C$2:$C$13,MATCH(A2,ข้อมูลนักเรียน!$A$2:$A$13,0))

Enter > Copy ลงด้านล่าง อย่าลืมปรับช่วงเซลล์ในสูตรให้ตรงกับที่จะใช้จริง

[manadee]

ผมลองใช้สูตร INDEX ตามที่แนะนำก็ยังไม่ได้ผล ได้นำไฟล์มาตัดข้อมูลออกบางส่วนเพื่อให้เหลือข้อมูลไม่มากนักแล้วลองใส่สูตรเข้าไปก็ยังใช้ไม่ได้ เช่นในรายที่ 2 ในชีท "ประชากรวัยเรียน" (นางสาวกวินทิพย์ จินดารัศมี) ในคอลัมภ์ รร.ที่ค้นเจอ ไม่พบข้อมูล ทั้ง ๆ ที่ในชีท "ข้อมูลนักเรียน" มีข้อมูลรายนี้อยู่ในลำดับที่ 4 ผมจึงขอส่งไฟล์มาให้ดูว่าผมใช้สูตรผิดตรงไหนหรือเปล่า
สำหรับเครื่องคอมพิวเตอร์ที่ผมใช้เป็น Notebook CPU รุ่น T6400 2.0GHz Core(TM) 2 DUO RAM 2GB ระบบปฏิบัติการเป็น Windows7 Excel เป็นรุ่น 2007

[bank9597]

ผมลองใช้สูตร INDEX ตามที่แนะนำก็ยังไม่ได้ผล ได้นำไฟล์มาตัดข้อมูลออกบางส่วนเพื่อให้เหลือข้อมูลไม่มากนักแล้วลองใส่สูตรเข้าไปก็ยังใช้ไม่ได้ เช่นในรายที่ 2 ในชีท "ประชากรวัยเรียน" (นางสาวกวินทิพย์ จินดารัศมี) ในคอลัมภ์ รร.ที่ค้นเจอ ไม่พบข้อมูล ทั้ง ๆ ที่ในชีท "ข้อมูลนักเรียน" มีข้อมูลรายนี้อยู่ในลำดับที่ 4 ผมจึงขอส่งไฟล์มาให้ดูว่าผมใช้สูตรผิดตรงไหนหรือเปล่า
สำหรับเครื่องคอมพิวเตอร์ที่ผมใช้เป็น Notebook CPU รุ่น T6400 2.0GHz Core(TM) 2 DUO RAM 2GB ระบบปฏิบัติการเป็น Windows7 Excel เป็นรุ่น 2007

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

ที่ชีทประชากรวัยเรียน เซลล์ C2 คีย์ =INDEX(ข้อมูลนักเรียน!$C$2:$C$220,MATCH(A2,ข้อมูลนักเรียน!$A$2:$A$220+0,0)) กด Ctrl+Shift+Enter

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

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


วิธีแก้คือ ลองแปลงรหัสเลข 13 หลัก ให้อยู่ในรูปแบบเดียวกันก่อนครับ โดยคลิ๊กที่ ชีทข้อมูลนักเรียน คอลัมน์ A ให้ไปที่ Data >TextToColumns > Next > Next > Finish ก็จะได้รหัสที่อยู่ในรูปแบบที่เหมือนกันทั้ง 2 ชีท

จากนั้นก็ใช้สูตรตามของ อาจารย์ snasui ได้ตามปรกติครับ การดึงข้อมูลจะเร็วขึ้นกว่าเดิมครับ

การแก้ปัญหาความช้าวิธีสุดท้ายคือการสร้าง RangeName แบบไดนามิค ครับ แต่ก็อยู่ที่การสร้างด้วยเช่นกันว่าถูกวิธีหรือไม่ครับ

[manadee]

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