Excel Tips บทความนี้ขอนำเสนอการสร้างตารางข้อมูลแบบไดนามิกส์ครับ โดยสมมุติว่าท่านผู้อ่านมีความรู้ความเข้าใจเกี่ยวกับสูตร excel : VLOOKUP มาอย่างดีแล้ว เรามาดูที่มาของ Tips ที่ผมจะนำเสนอกันก่อนครับ การใช้สูตร excel :VLOOKUP สิ่งที่สำคัญคือ table_array ซึ่งเป็นตารางข้อมูลสำหรับใช้ค้นหาและเก็บข้อมูลไว้สำหรับค้นหาครับ รูปแบบของตารางข้อมูลก็จะเรียงกันเป็นแถวแต่ละแถวจะเรียกว่า record ซึ่งในการใช้งานจริงข้อมูลในตารางสามารถเพิ่มหรือลดได้ ดังนั้นหากเรากำหนดให้ table_array ของเราเป็นข้อมูลแบบสถิต จะทำให้เราต้องเสียเวลากำหนดช่วงข้อมูลใหม่ทุกครั้งที่มีการเปลี่ยนแปลงตารางข้อมูล ดังนั้นเราจึงทำการสร้างตารางข้อมูลแบบไดนามิกส์สำหรับใช้ในสูตร excel :VLOOKUP โดยมีข้อกำหนดดังนี้
1.รายการข้อมูลในคีย์หลักจะต้องไม่ซ้ำกัน ซึ่งเราสามารถกำหนดได้ รายละเอียดดูได้ใน excel การป้องกันรายการข้อมูลซ้ำกัน
2. สร้างตารางข้อมูลแบบไดนามิกส์โดยใช้สูตร excel : OFFSET ผ่านวิธีการกำหนดข้อมูลแบบพลวัตร
มาดูตัวอย่างกันเลยครับ
สมมุติรูปแบบของ table_array เป็นดังภาพที่ 1
ภาพที่ 1 รูปแบบของ table_array ใน excel
จากรูปแบบของ table_array จะกล่าวได้ว่า table_array มี 5 ฟิลด์ และกำหนดให้จำนวน record สูงสุดเท่ากับ 1,000 แถว
ดังนั้นเราสามารถกำหนด Data validation แบบ Custom เพื่อป้องกันรายการข้อมูลซ้ำกันใน B4 ได้ดังนี้สูตร excel : COUNTIF($B$4:$B$1004,B4)=1
Copy สูตรดังกล่าวและวางลงใน B5 – B1003
สร้าง table_array แบบไดนามิกส์ โดยไปที่เมนู สูตร เลือก กำหนดชื่อ จะปรากฏหน้าต่าง ชื่อใหม่ ให้กำหนดชื่อข้อมูลเป็น tb_cutomer ขอบเขตเป็น สมุดงาน และกำหนดสูตร excel ใน หัวข้ออ้างอิงไปยัง โดยใช้ สูตร excel ดังนี้
OFFSET(Sheet1!$B$4,0,0,COUNTA(Sheet1!$B$4:Sheet1!$B$1003),5)
ภาพที่ 2 การกำหนดข้อมูลแบบไดนามิกส์ใน excel
ทดลองสร้างสูตร excel: VLOOKUP เพื่อแสดงผลการค้นหาข้อมูลตามรหัสพนักงาน โดยกำหนดให้ผลการค้นหาแสดงในแถวที่ 2 โดยกำหนดให้ผู้ใช้ พิมพ์รหัสลูกค้าใน B2 และแสดงรายชื่อ ที่อยู่ เบอร์โทรศัพท์ และ ผู้ติดต่อ ใน C2, D2, E2 และ F2 ตามลำดับ โดยใช้สูตร excel ดังนี้C2 : IFERROR(VLOOKUP(B2,tb_customer,2,False),”Data does not found”)
D2 : IFERROR(VLOOKUP(B2,tb_customer,3,False),”Data does not found”)
E2 : IFERROR(VLOOKUP(B2,tb_customer,4,False),”Data does not found”)
F2 : IFERROR(VLOOKUP(B2,tb_customer,5,False),”Data does not found”)
ทดลองพิมพ์รหัสลูกค้า A003 ใน B2 Excel จะแจ้งผลการค้นหาว่า Data does not found ดังแสดงในภาพที่ 3
ภาพที่ 3 ผลการค้นหาในกรณีที่ไม่พบข้อมูล
ทดลองเพิ่มข้อมูล A003 ในแถวที่ 6 และเมื่อทดสอบพิมพ์รหัสลูกค้า A003 ลงใน B2 จะได้ผลลัพธ์ดังแสดงในภาพที่ 4
ภาพที่ 4 ผลการค้นหาในกรณีที่ข้อมูล
สรุปในบทความนี้ เราสามารถสร้างตารางข้อมูลแบบไดนามิกส์สำหรับใช้งานในสูตร excel VLOOKUP ได้ โดยตารางข้อมูลดังกล่าวมีประสิทธิภาพสูง ในบทความต่อไปเราจะประยุกต์ วิธีการนำรายการข้อมูลจาก Data validation มาใช้ร่วมกับสูตร excel : VLOOKUP ซึ่งจะช่วยเพิ่มประสิทธิภาพการค้นหาข้อมูลด้วย excel ให้มากขึ้น สวัสดีครับ
ไม่มีความคิดเห็น:
แสดงความคิดเห็น