สาเหตุที่สูตร VLookup หรือสูตรใดๆใช้เวลาคำนวณนานขึ้น เพราะจำนวนรายการที่มีมากขึ้นเรื่อยๆ และนำพื้นที่ข้อมูลทั้งหมดไปใช้
.
ถ้าอยากทำให้ Excel ใช้เวลาคำนวณเร็วขึ้น แทนที่จะนำพื้นที่ตารางทั้งหมดตั้งแต่รายการแรกจนถึงรายการสุดท้ายไปใช้ ควรเปลี่ยนไปเลือกใช้เฉพาะพื้นที่ของรายการช่วงที่ต้องการ โดยอาศัยสูตร Offset ช่วยกำหนดขนาดและตำแหน่งของพื้นที่
.
ตามภาพนี้ หากต้องการตารางเฉพาะพื้นที่สีเขียว
.
เซลล์สีแดง คือ เซลล์หัวตารางตรงมุมซ้ายบนสุด
.
A คือเลขจำนวนรายการตั้งแต่รายการแรกนับไปจนถึงหัวมุมตารางสีเขียว หรือเป็นเลขที่รายการของรายการเริ่มต้นของพื้นที่สีเขียว
.
0 เลขที่ column ที่ยังคงอยู่ในแนวเดิม ไม่ได้ย้ายตารางสีเขียวไปที่อื่น
.
B คือ จำนวนรายการที่ต้องการนำไปใช้งาน
.
9 คือ จำนวน column ของตาราง
.
ถ้าเซลล์สีแดงคือเซลล์ B2 หากต้องการพื้นที่ตั้งแต่รายการที่ 50,000 โดยกำหนดให้ใช้พื้นที่สีเขียวจำนวน 100,000 รายการ ให้สร้างสูตรตามนี้
.
=OFFSET($B$2, 50000, 0, 100000, 9)
.
โดยวิธีการใช้สูตรนี้ จะนำไปใช้ในสูตร VLookup ในส่วนของพื้นที่ตารางก็ได้ แบบนี้
.
=VLookup( รหัสสินค้า, OFFSET($B$2, 50000, 0, 100000, 9), 2, 0)
.
แต่แนะนำให้นำไปตั้งเป็น Formula Name ก่อน เช่นนำสูตรไปตั้งเป็นชื่อ MyData แล้วนำไปใช้ต่อจะสะดวกกว่า
.
=VLookup( รหัสสินค้า, MyData, 2, 0)
.
ดูคลิปวิธีการใช้สูตร Offset ได้จาก
https://www.excelexperttraining.com/online/courses/02-expert-guide/lessons/expert-guide-offset-range/
.
วิธีการตั้งชื่อให้กับสูตร
https://www.excelexperttraining.com/book/index.php/a-to-z/k-l-m-n-o/n-n-n-n-n/formula-name
.
หมายเหตุ
.
1. วิธีนี้ยืดหยุ่นมากกว่าการใช้คำสั่ง Insert > Table ซึ่งจะนำรายการทั้งหมดตั้งแต่รายการแรกจนถึงรายการสุดท้ายไปใช้เสมอ
.
2. เลขที่รายการ 50,000 และจำนวนรายการ 100,000 ควรลิงก์ค่าจากเซลล์มาใช้ เพื่อกำหนดตำแหน่งเริ่มต้นและขนาดได้ตามต้องการ ส่วนเลข 9 สามารถเปลี่ยนเป็นจำนวน column ที่ใช้งานอยู่ ซึ่งมักมีจำนวนคงที่
.
3. สูตร Offset นี้ถ้าสร้างใน Excel 365 จะขยายตัวดึงข้อมูลตามขนาดที่กำหนดไว้ให้ทันที แต่ถ้าใช้กับรุ่นก่อนๆจะ Error เพราะไม่สามารถแสดงค่าทั้งตารางในเซลล์เดียวได้ แนะนำให้เลือกพื้นที่ขนาดใหญ่ๆไว้แล้วสร้างสูตร Offset ลงไปพร้อมกันทั้งตารางแล้วกด Ctrl+Shift+Enter
.
4. ชื่อ MyData สามารถนำไปใช้กับทุกสูตรหรือนำใช้ในการสร้าง Dynamic Chart ทำให้กราฟยืดได้หดได้ แต่ถ้านำไปใช้กับ Pivot Table ต้องกำหนดขนาดเริ่มต้นจากหัวตารางด้วยเพราะ Pivot ใช้ชื่อหัวตารางในการกำหนด Field
.
เคล็ดนี้คุณจะได้เรียนในหลักสูตร "คิดจะใช้ Power BI หันมาใช้ Excel จัดการข้อมูลก่อนดีกว่า : Data Management with Excel Expert" ดูเนื้อหาได้จาก
https://www.excelexperttraining.com/private/managing-data
