หากต้องการค้นหาในข้อมูล ให้ใช้ฟังก์ชัน LOOKUP เช่น
แม้ว่าส่วนใหญ่แล้วการเรียกใช้ฟังก์ชันเหล่านี้จะใช้เวลานาน แต่บทความนี้จะแสดงวิธีเพิ่มประสิทธิภาพ LOOKUP และลดความสิ้นเปลืองในการคํานวณ
ใช้การจัดเรียงช่วงด้วยฟังก์ชัน LOOKUPหากต้องการจัดเรียงข้อมูลอย่างมีประสิทธิภาพมากขึ้น ให้ใช้การจัดเรียงช่วงแทนฟังก์ชัน SORT โดยทําดังนี้
- เลือกเซลล์หรือคอลัมน์
- ที่แถบงานด้านบน ให้คลิกข้อมูล
จัดเรียงช่วง
- เลือกตัวเลือกใดตัวเลือกหนึ่งต่อไปนี้
- จัดเรียงช่วงตาม [คอลัมน์ด้านซ้ายสุด] (A ถึง Z)
- จัดเรียงช่วงตาม [คอลัมน์ด้านซ้ายสุด] (Z ถึง A)
- ตัวเลือกการจัดเรียงช่วงขั้นสูง: จัดเรียงตามหลายคอลัมน์พร้อมกันตามลําดับชั้น
จัดเรียงช่วงเคล็ดลับ: "ช่วงการจัดเรียง" จะจัดเรียงข้อมูลก่อน จากนั้นจึงป้อนข้อมูลที่จัดเรียงลงในฟังก์ชัน VLOOKUP
สิ่งที่ไม่ควรทํา
สําคัญ: ตัวอย่างต่อไปนี้แสดงวิธีการใช้งานฟังก์ชัน SORT ที่ไม่ถูกต้อง
=VLOOKUP(search_key, SORT(A1:B10, 1), 2)
ในตัวอย่างนี้ SORT จะฝังอยู่ในฟังก์ชัน VLOOKUP ทุกครั้งที่ข้อมูลในช่วงที่จัดเรียงมีการเปลี่ยนแปลง ฟังก์ชัน SORT จะตรวจสอบข้อมูลทั้งหมดซ้ำอีกครั้งโดยไม่จำเป็น และเรียกใช้ฟังก์ชัน SORT ใหม่
เคล็ดลับ: ตามหลักแล้ว ควรใช้ SORT กับชุดข้อมูลหนึ่งเพียงครั้งเดียว คุณสามารถใช้การอ้างอิงได้หากจําเป็น
หากต้องการเพิ่มความเร็วในการคํานวณ ให้นําข้อมูลที่ซ้ำออกทั้งชุด โดยทำดังนี้
- เลือกคอลัมน์และแถวทั้งหมดที่ต้องการ
- ในแถบงานด้านบน ให้คลิกข้อมูล
- เลือกคอลัมน์ที่จะวิเคราะห์
- คลิกนำรายการซ้ำออก
เคล็ดลับ: ข้อมูลที่มีค่าที่ซ้ำกันหลายรายการในคอลัมน์หรือแถวอาจทําให้การคํานวณช้าลง
ฟังก์ชัน LOOKUP มีลักษณะการทำงานดังนี้
- ตรวจหารายการที่ซ้ำกันอย่างชาญฉลาดไม่ได้
- ค้นหาชุดข้อมูลทั้งชุด ซึ่งรวมถึงค่าที่ซ้ำกันทั้งหมดที่ไม่ตรงกัน
หากต้องการเรียกใช้การคํานวณ LOOKUP ในคอมพิวเตอร์ของคุณเอง ให้นําเข้าข้อมูลในสเปรดชีตก่อน
- ใช้ IMPORTRANGE เพื่อดึงข้อมูลไว้ในช่วงว่างในสเปรดชีตเดียวกับที่มี LOOKUP อยู่ ดูข้อมูลเพิ่มเติมเกี่ยวกับ IMPORTRANGE
- อ้างอิงข้อมูลที่นําเข้าเป็นช่วงในฟังก์ชัน LOOKUP
สิ่งที่ไม่ควรทํา
สําคัญ: ตัวอย่างต่อไปนี้แสดงถึงวิธีการใช้งาน IMPORTRANGE ที่ไม่ถูกต้อง
=VLOOKUP(search_key, IMPORTRANGE(spreadsheet_url, range_string), index, [is_sorted])
ในตัวอย่างนี้ IMPORTRANGE จะฝังอยู่ในฟังก์ชัน LOOKUP ในแต่ละครั้งที่ LOOKUP ทำงาน ระบบจะเรียก IMPORTRANGE ก่อนเพื่อดึงข้อมูล จากนั้นจะใช้ฟังก์ชัน LOOKUP กับข้อมูลที่ดึงมา
เคล็ดลับ: พยายามอย่าฝังฟังก์ชันใดก็ตามในฟังก์ชัน LOOKUP ไม่เช่นนั้น ฟังก์ชันภายในจะดําเนินการคํานวณเพิ่มเติมภายใน LOOKUP เมื่อใดก็ตามที่ฟังก์ชัน LOOKUP ทํางาน
หากต้องการให้การคํานวณชีตทํางานได้เร็วขึ้น ให้ใช้คําสั่ง IF เพื่อข้ามค่าที่ซ้ำกัน เช่น N/A, #ERROR, REF# หรือเซลล์ว่าง
สิ่งที่ไม่ควรทํา
สําคัญ: ตัวอย่างต่อไปนี้แสดงถึงวิธีการใช้งาน VLOOKUP ที่ไม่ถูกต้อง
ในตัวอย่างข้างต้น คุณสามารถใช้ VLOOKUP เพื่อค้นหาราคาสําหรับรายการผลไม้ในคอลัมน์ A ได้ อย่างไรก็ตาม ยังมีเซลล์ว่างอยู่หลายเซลล์ในรายการผลไม้
Google ชีตจะเรียกใช้การคํานวณใน B2 ถึง B10 กับคีย์การค้นหาที่อ้างอิงทั้งหมดในคอลัมน์ A แม้ว่า VLOOKUP ใน B3, B7 และ B9 จะทำการค้นหาในช่องว่างก็ตาม ซึ่งการค้นหาเหล่านี้ไม่ได้แสดงผลการค้นหาที่มีความหมายแต่อย่างใด
สิ่งที่ควรทำ
ใช้ฟังก์ชัน IF เพื่อข้ามคีย์การค้นหาเหล่านั้นตามเกณฑ์ที่คุณต้องการ และเรียกใช้ VLOOKUP เมื่อข้อมูลในรายการผลไม้ไม่ว่างเปล่าเท่านั้น
การใช้ฟังก์ชัน IF จะเป็นการบอก Google ชีตว่า "หากคีย์การค้นหาไม่เท่ากับค่าว่าง ให้เรียกใช้ your_formula แต่หากคีย์การค้นหาเท่ากับค่าว่าง อย่าใช้ your_formula และแสดงผล N/A แทน"
โดยทั่วไปจะใช้สูตรใดก็ได้ และค่าที่จะข้ามก็ไม่จําเป็นต้องเป็นค่าว่าง เทคนิคนี้จะช่วยประหยัดเวลา เนื่องจากคุณจะหลีกเลี่ยงการคํานวณผลลัพธ์เพิ่มเติมได้ กรณีที่มีค่าที่ไม่มีความหมายอยู่
Google ชีตจะแยกแยะไม่ได้ว่าค่านั้นมีความหมายหรือไม่ เว้นแต่คุณจะเรียกค่านั้นด้วยฟังก์ชัน IF โดยเฉพาะ
=IF(A2 <> value_to_skip, your_formula, "N/A")
เคล็ดลับ: การใช้ฟังก์ชัน IF ด้านบนจะเป็นการบอก Google ชีตว่า "หาก A2 ไม่เท่ากับ value_to_skip ให้เรียกใช้ your_formula แต่หาก A2 เท่ากับ value_to_skip อย่าใช้ your_formula และให้แสดงเอาต์พุตเป็น N/A แทน"
แม้ว่า VLOOKUP จะทํางานได้เร็วกว่าเล็กน้อย แต่ฟังก์ชัน INDEX และ MATCH จะยืดหยุ่นมากกว่าในการแบ่งเวิร์กโฟลว์ LOOKUP ออกเป็นส่วนย่อยๆ วิธีนี้จะช่วยให้คุณนำผลการค้นหาก่อนหน้านี้มาใช้ซ้ำได้และยังช่วยประหยัดเวลาด้วย
สิ่งที่ไม่ควรทํา
ตัวอย่างเช่น หากต้องการให้ VLOOKUP ค้นหาทั้งราคาและปริมาณของ "Apple" ในตารางนี้ คุณต้องใช้สูตร VLOOKUP 2 สูตรแยกกัน ดังนี้
=VLOOKUP("Apple", $A$1:$C$4, 2, FALSE)
VLOOKUP จะทำงาน 2 ขั้นดังนี้
- ค้นหาตําแหน่งของ "Apple"
- ค้นหาราคาของ "Apple" โดยไปยังคอลัมน์ที่ 2
=VLOOKUP("Apple", $A$1:$C$4, 3, FALSE)
VLOOKUP จะทำงาน 2 ขั้นดังนี้
- ค้นหาตําแหน่งของ "Apple"
- ค้นหาจํานวน "Apple" โดยไปยังคอลัมน์ที่ 3
ทั้ง 2 สูตรข้างต้นจะมีขั้นตอนแรกเหมือนกัน คือหาตําแหน่งของ "Apple" ในรายการ แต่ขั้นตอนที่ 2 ของทั้ง 2 สูตรด้านบนจะทำงานร่วมกันไม่ได้เนื่องจาก VLOOKUP เป็นฟังก์ชันเดี่ยวที่มีเอาต์พุตเดียว
ดังนั้น หากคุณเรียกใช้ VLOOKUP เพื่อค้นหาข้อมูลหลายรายการที่แตกต่างกันสําหรับสินค้าเดียวกัน คุณจะต้องทําตามขั้นตอนแรก 2 ครั้ง ซึ่งจะใช้เวลาในการคํานวณมากขึ้น
สิ่งที่ควรทำ
คุณสามารถประหยัดเวลาได้เมื่อใช้ MATCH ร่วมกับ INDEX วิธีนี้จะแยกขั้นตอนที่ 1 และ 2 ออกเพื่อให้คุณนําขั้นตอนที่ 1 กลับมาใช้ซ้ำได้ทุกเมื่อที่ต้องการ
1. ค้นหาตำแหน่งของ "Apple" ด้วย MATCH ดังนี้
=MATCH("Apple", $A$2:$A$4, FALSE)
เอาต์พุตของสูตรนี้คือ "1" เนื่องจาก "Apple" อยู่ในตําแหน่งที่ 1 ของช่วง
2. ค้นหาราคาของ "Apple" ในคอลัมน์ที่ 2 ด้วย INDEX ดังนี้
=INDEX($A$2:$C$4, cell_with_MATCH_formula, 2)
เอาต์พุตของสูตรนี้คือ "$1"
หากต้องการค้นหาจํานวนของ "Apple" คุณสามารถใช้ขั้นตอนที่ 1 ซ้ำและอ้างอิงเซลล์ด้วยสูตร MATCH เพื่อที่จะได้ไม่ต้องคํานวณส่วนนั้นใหม่
3. ค้นหาจํานวนของ "Apple" ในคอลัมน์ที่ 3 ด้วย INDEX ดังนี้
=INDEX($A$2:$C$4, cell_with_MATCH_formula, 3)
ในตัวอย่างนี้ที่มี INDEX และ MATCH ให้คุณดำเนินการขั้นตอนที่ 1 เพียงครั้งเดียวและดำเนินขั้นตอนที่ 2 สองครั้ง ซึ่งขั้นตอนที่ใช้ในการคำนวณจะรวมเป็น 3 ครั้ง หากคุณใช้ VLOOKUP แทน ระบบจะใช้ฟังก์ชัน VLOOKUP 2 รายการและ 4 ขั้นตอน ซึ่งจะใช้เวลาและทรัพยากรในการคํานวณมากกว่าเดิม
ประสิทธิภาพจะยิ่งเพิ่มขึ้นตามจำนวนฟังก์ชัน VLOOKUP ที่ใช้ นอกจากนี้ยังมีอีกหลายกรณีที่คุณสามารถนำผลลัพธ์ของ MATCH มาใช้ซ้ำเพื่อช่วยประหยัดเวลา