การวิเคราะห์ข้อมูล Excel - ฟังก์ชันการค้นหา

คุณสามารถใช้ฟังก์ชัน Excel เพื่อ -

• ค้นหาค่าในช่วงข้อมูล - VLOOKUP และ HLOOKUP
• รับค่าหรือการอ้างอิงถึงค่าจากภายในตารางหรือช่วง - INDEX
• รับตำแหน่งสัมพัทธ์ของรายการที่ระบุในช่วงของเซลล์ - MATCH

คุณยังสามารถรวมฟังก์ชันเหล่านี้เพื่อให้ได้ผลลัพธ์ที่ต้องการตามอินพุตที่คุณมี

การใช้ฟังก์ชัน VLOOKUP

ไวยากรณ์ของฟังก์ชัน VLOOKUP คือ

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

ที่ไหน

• lookup_value- คือค่าที่คุณต้องการค้นหา Lookup_value อาจเป็นค่าหรือการอ้างอิงไปยังเซลล์ Lookup_value ต้องอยู่ในคอลัมน์แรกของช่วงของเซลล์ที่คุณระบุใน table_array

• table_array- คือช่วงของเซลล์ที่ VLOOKUP จะค้นหา lookup_value และค่าที่ส่งคืน table_array ต้องมี

  • lookup_value ในคอลัมน์แรกและ

  • ค่าส่งคืนที่คุณต้องการค้นหา

    Note- คอลัมน์แรกที่มี lookup_value สามารถเรียงลำดับจากน้อยไปมากหรือไม่ก็ได้ อย่างไรก็ตามผลลัพธ์จะเป็นไปตามลำดับของคอลัมน์นี้

• col_index_num- คือหมายเลขคอลัมน์ใน table_array ที่มีค่าส่งคืน ตัวเลขเริ่มต้นด้วย 1 สำหรับคอลัมน์ทางซ้ายสุดของตารางอาร์เรย์

• range_lookup- เป็นค่าตรรกะทางเลือกที่ระบุว่าคุณต้องการให้ VLOOKUP ค้นหาการจับคู่แบบตรงทั้งหมดหรือการจับคู่โดยประมาณ range_lookup ได้

  • ละเว้นซึ่งในกรณีนี้จะถือว่าเป็น TRUE และ VLOOKUP จะพยายามหาค่าที่ตรงกันโดยประมาณ

  • TRUE ซึ่งในกรณีนี้ VLOOKUP จะพยายามหาค่าที่ตรงกันโดยประมาณ กล่าวอีกนัยหนึ่งคือหากไม่พบการจับคู่แบบตรงทั้งหมดจะส่งคืนค่าที่ใหญ่ที่สุดถัดไปที่น้อยกว่า lookup_value

  • FALSE ซึ่งในกรณีนี้ VLOOKUP จะพยายามค้นหาการจับคู่แบบตรงทั้งหมด

  • 1 ซึ่งในกรณีนี้ถือว่าเป็น TRUE และ VLOOKUP จะพยายามหาค่าที่ตรงกันโดยประมาณ

  • 0 ซึ่งในกรณีนี้ถือว่าเป็น FALSE และ VLOOKUP พยายามค้นหาการจับคู่แบบตรงทั้งหมด

Note- หากเว้นช่วง range_lookup หรือ TRUE หรือ 1 VLOOKUP จะทำงานได้อย่างถูกต้องก็ต่อเมื่อคอลัมน์แรกใน table_array เรียงลำดับจากน้อยไปมาก มิฉะนั้นอาจส่งผลให้ค่าไม่ถูกต้อง ในกรณีนี้ให้ใช้ FALSE สำหรับ range_lookup

การใช้ฟังก์ชัน VLOOKUP กับ range_lookup TRUE

พิจารณารายชื่อคะแนนนักเรียน คุณสามารถรับเกรดที่สอดคล้องกันด้วย VLOOKUP จากอาร์เรย์ที่มีช่วงเวลาของเครื่องหมายและประเภทการส่งผ่าน

table_array -

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

ตั้งชื่ออาร์เรย์นี้ว่า Grades.

เป็นแนวทางปฏิบัติที่ดีในการตั้งชื่ออาร์เรย์ด้วยวิธีนี้เพื่อที่คุณจะได้ไม่ต้องจำช่วงของเซลล์ ตอนนี้คุณพร้อมที่จะค้นหาเกรดสำหรับรายการเครื่องหมายที่คุณมีดังต่อไปนี้ -

อย่างที่คุณสังเกตได้

• col_index_num - ระบุคอลัมน์ของค่าส่งคืนใน table_array คือ 2

• ที่ range_lookup เป็นความจริง

  • คอลัมน์แรกที่มีค่าการค้นหาในเกรด table_array อยู่ในลำดับจากน้อยไปมาก ดังนั้นผลลัพธ์จะถูกต้อง

  • คุณสามารถรับค่าส่งคืนสำหรับการจับคู่โดยประมาณได้เช่นกัน เช่น VLOOKUP คำนวณดังนี้ -

เครื่องหมาย	ผ่านหมวดหมู่
<35	ล้มเหลว
> = 35 และ <50	ชั้นสาม
> = 50 และ <60	ชั้นสอง
> = 60 และ <75	ชั้นหนึ่ง
> = 75	ชั้นหนึ่งที่มีความแตกต่าง

คุณจะได้รับผลลัพธ์ดังต่อไปนี้ -

การใช้ฟังก์ชัน VLOOKUP กับ range_lookup FALSE

พิจารณารายการผลิตภัณฑ์ที่มีรหัสผลิตภัณฑ์และราคาสำหรับผลิตภัณฑ์แต่ละรายการ รหัสผลิตภัณฑ์และราคาจะถูกเพิ่มไว้ท้ายรายการเมื่อใดก็ตามที่มีการเปิดตัวผลิตภัณฑ์ใหม่ ซึ่งหมายความว่ารหัสผลิตภัณฑ์ไม่จำเป็นต้องเรียงลำดับจากน้อยไปมาก รายการสินค้าอาจเป็นดังที่แสดงด้านล่าง -

table_array -

ตั้งชื่ออาร์เรย์นี้ว่า ProductInfo

คุณสามารถรับราคาของผลิตภัณฑ์ที่ระบุรหัสผลิตภัณฑ์ด้วยฟังก์ชัน VLOOKUP เนื่องจากรหัสผลิตภัณฑ์อยู่ในคอลัมน์แรก ราคาอยู่ในคอลัมน์ 3 ดังนั้น col_index_ num ควรเป็น 3

• ใช้ฟังก์ชัน VLOOKUP กับ range_lookup เป็น TRUE
• ใช้ฟังก์ชัน VLOOKUP กับ range_lookup เป็น FALSE

คำตอบที่ถูกต้องมาจากอาร์เรย์ ProductInfo คือ 171.65 คุณสามารถตรวจสอบผลลัพธ์

คุณสังเกตว่าคุณมี -

• ผลลัพธ์ที่ถูกต้องเมื่อ range_lookup เป็น FALSE และ
• ผลลัพธ์ที่ไม่ถูกต้องเมื่อ range_lookup เป็น TRUE

เนื่องจากคอลัมน์แรกในอาร์เรย์ ProductInfo ไม่ได้เรียงลำดับจากน้อยไปมาก ดังนั้นอย่าลืมใช้ FALSE เมื่อใดก็ตามที่ข้อมูลไม่ได้รับการจัดเรียง

การใช้ฟังก์ชัน HLOOKUP

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

ตัวอย่าง

ให้เรานำตัวอย่างข้อมูลผลิตภัณฑ์ สมมติว่าอาร์เรย์มีลักษณะดังนี้ -

• ตั้งชื่อ Array ProductRange นี้ คุณสามารถค้นหาราคาของผลิตภัณฑ์ที่ระบุรหัสผลิตภัณฑ์ด้วยฟังก์ชัน HLOOKUP

ไวยากรณ์ของฟังก์ชัน HLOOKUP คือ

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

ที่ไหน

• lookup_value - คือค่าที่พบในแถวแรกของตาราง

• table_array - เป็นตารางข้อมูลที่ใช้ค้นหาข้อมูล

• row_index_num - คือหมายเลขแถวใน table_array ซึ่งจะส่งคืนค่าที่ตรงกัน

• range_lookup - เป็นค่าตรรกะที่ระบุว่าคุณต้องการให้ HLOOKUP ค้นหาการจับคู่แบบตรงทั้งหมดหรือการจับคู่โดยประมาณ

• range_lookup เป็นไปได้

  • ละเว้นซึ่งในกรณีนี้จะถือว่าเป็น TRUE และ HLOOKUP จะพยายามหาค่าที่ตรงกันโดยประมาณ

  • TRUE ซึ่งในกรณีนี้ HLOOKUP จะพยายามหาค่าที่ตรงกันโดยประมาณ กล่าวอีกนัยหนึ่งคือหากไม่พบการจับคู่แบบตรงทั้งหมดจะส่งคืนค่าที่ใหญ่ที่สุดถัดไปที่น้อยกว่า lookup_value

  • FALSE ซึ่งในกรณีนี้ HLOOKUP จะพยายามค้นหาการจับคู่แบบตรงทั้งหมด

  • 1 ซึ่งในกรณีนี้ถือว่าเป็น TRUE และ HLOOKUP จะพยายามหาค่าที่ตรงกันโดยประมาณ

  • 0 ซึ่งในกรณีนี้จะถือว่าเป็น FALSE และ HLOOKUP พยายามค้นหาการจับคู่แบบตรงทั้งหมด

Note- ถ้า range_lookup เป็นละเว้นหรือ TRUE หรือ 1 HLOOKUP จะทำงานได้อย่างถูกต้องก็ต่อเมื่อคอลัมน์แรกใน table_array เรียงลำดับจากน้อยไปมาก มิฉะนั้นอาจส่งผลให้ค่าไม่ถูกต้อง ในกรณีนี้ให้ใช้ FALSE สำหรับ range_lookup

การใช้ฟังก์ชัน HLOOKUP กับ range_lookup FALSE

คุณสามารถรับราคาของผลิตภัณฑ์ที่ระบุรหัสผลิตภัณฑ์ด้วยฟังก์ชัน HLOOKUP เนื่องจากรหัสผลิตภัณฑ์อยู่ในแถวแรก ราคาอยู่ในแถวที่ 3 ดังนั้น row_index_ num ควรเป็น 3

• ใช้ฟังก์ชัน HLOOKUP กับ range_lookup เป็น TRUE
• ใช้ฟังก์ชัน HLOOKUP กับ range_lookup เป็น FALSE

คำตอบที่ถูกต้องจากอาร์เรย์ ProductRange คือ 171.65 คุณสามารถตรวจสอบผลลัพธ์

คุณสังเกตว่าในกรณีของ VLOOKUP คุณได้รับ

• ผลลัพธ์ที่ถูกต้องเมื่อ range_lookup เป็น FALSE และ

• ผลลัพธ์ที่ไม่ถูกต้องเมื่อ range_lookup เป็น TRUE

เนื่องจากแถวแรกในอาร์เรย์ ProductRange ไม่ได้เรียงลำดับจากน้อยไปมาก ดังนั้นอย่าลืมใช้ FALSE เมื่อใดก็ตามที่ข้อมูลไม่ได้รับการจัดเรียง

การใช้ฟังก์ชัน HLOOKUP กับ range_lookup TRUE

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

table_array -

ตั้งชื่ออาร์เรย์นี้ว่า GradesRange

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

อย่างที่คุณสังเกตได้

• row_index_num - ระบุคอลัมน์ของค่าส่งคืนใน table_array คือ 2

• ที่ range_lookup เป็นความจริง

  • คอลัมน์แรกที่มีค่าการค้นหาใน table_array Grades อยู่ในลำดับจากน้อยไปมาก ดังนั้นผลลัพธ์จะถูกต้อง

  • คุณสามารถรับค่าส่งคืนสำหรับการจับคู่โดยประมาณได้เช่นกัน เช่น HLOOKUP คำนวณดังนี้ -

เครื่องหมาย	<35	> = 35 และ <50	> = 50 และ <60	> = 60 และ <75	> = 75
ผ่านหมวดหมู่	ล้มเหลว	ชั้นสาม	ชั้นสอง	ชั้นหนึ่ง	ชั้นหนึ่งที่มีความแตกต่าง

คุณจะได้รับผลลัพธ์ดังต่อไปนี้ -

การใช้ฟังก์ชัน INDEX

เมื่อคุณมีอาร์เรย์ของข้อมูลคุณสามารถดึงค่าในอาร์เรย์ได้โดยระบุหมายเลขแถวและหมายเลขคอลัมน์ของค่านั้นในอาร์เรย์

พิจารณาข้อมูลการขายต่อไปนี้ซึ่งคุณจะพบยอดขายในแต่ละภูมิภาคเหนือใต้ตะวันออกและตะวันตกโดยพนักงานขายที่มีรายชื่ออยู่

• ตั้งชื่ออาร์เรย์เป็น SalesData

เมื่อใช้ฟังก์ชัน INDEX คุณจะพบ -

• การขายของพนักงานขายใด ๆ ในบางภูมิภาค
• ยอดขายรวมในภูมิภาคโดยพนักงานขายทั้งหมด
• ยอดขายทั้งหมดโดยพนักงานขายในทุกภูมิภาค

คุณจะได้รับผลลัพธ์ดังต่อไปนี้ -

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

คุณสามารถทำได้ด้วยฟังก์ชัน MATCH ตามที่อธิบายไว้ในหัวข้อถัดไป

การใช้ฟังก์ชัน MATCH

หากคุณต้องการตำแหน่งของรายการในช่วงคุณสามารถใช้ฟังก์ชัน MATCH คุณสามารถรวมฟังก์ชัน MATCH และ INDEX ได้ดังนี้ -

คุณจะได้รับผลลัพธ์ดังต่อไปนี้ -