4 ฟังก์ชั่นการค้นหาของ Excel เพื่อค้นหาสเปรดชีตอย่างมีประสิทธิภาพ

4 ฟังก์ชั่นการค้นหาของ Excel เพื่อค้นหาสเปรดชีตอย่างมีประสิทธิภาพ

ส่วนใหญ่ ค้นหา a Microsoft Excel สเปรดชีตค่อนข้างง่าย หากคุณไม่สามารถสแกนแถวและคอลัมน์เพื่อหาข้อมูลได้ คุณสามารถใช้ Ctrl + F เพื่อค้นหาได้ หากคุณกำลังทำงานกับสเปรดชีตขนาดใหญ่ จะช่วยประหยัดเวลาได้มากในการใช้ฟังก์ชันการค้นหาหนึ่งในสี่ฟังก์ชันนี้





เมื่อคุณรู้วิธีค้นหาใน Excel โดยใช้การค้นหา ไม่สำคัญว่าสเปรดชีตของคุณจะใหญ่แค่ไหน คุณก็จะสามารถค้นหาบางสิ่งใน Excel ได้เสมอ!



1. ฟังก์ชัน VLOOKUP

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





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

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [lookup_value] เป็นข้อมูลที่คุณมีอยู่แล้ว ตัวอย่างเช่น หากคุณต้องการทราบว่าเมืองนั้นอยู่ในรัฐใด ก็จะเป็นชื่อเมืองนั้น
  • [table_array] ให้คุณระบุเซลล์ที่ฟังก์ชันจะค้นหาการค้นหาและส่งกลับค่า เมื่อเลือกช่วงของคุณ ตรวจสอบให้แน่ใจว่าคอลัมน์แรกที่รวมอยู่ในอาร์เรย์ของคุณเป็นคอลัมน์ที่จะรวมค่าการค้นหาของคุณ!
  • [col_index_num] คือจำนวนคอลัมน์ที่มีค่าส่งคืน
  • [range_lookup] เป็นอาร์กิวเมนต์ที่เป็นทางเลือก และรับ 1 หรือ 0 หากคุณป้อน 1 หรือละเว้นอาร์กิวเมนต์นี้ ฟังก์ชันจะค้นหาค่าที่คุณป้อนหรือตัวเลขที่ต่ำที่สุดถัดไป ในภาพด้านล่าง VLOOKUP ที่กำลังมองหาคะแนน SAT ที่ 652 จะส่งกลับ 646 เนื่องจากเป็นตัวเลขที่ใกล้เคียงที่สุดในรายการที่น้อยกว่า 652 และ [range_lookup] มีค่าดีฟอลต์เป็น 1

มาดูกันว่าคุณจะใช้สิ่งนี้อย่างไร สเปรดชีตนี้มีหมายเลขประจำตัวประชาชน ชื่อและนามสกุล เมือง รัฐ และคะแนน SAT สมมติว่าคุณต้องการหาคะแนน SAT ของบุคคลที่มีนามสกุล 'Winters' VLOOKUP ทำให้ง่าย นี่คือสูตรที่คุณจะใช้:



=VLOOKUP('Winters', C2:F101, 4, 0)

เนื่องจากคะแนน SAT เป็นคอลัมน์ที่สี่ต่อจากคอลัมน์นามสกุล 4 คืออาร์กิวเมนต์ดัชนีคอลัมน์ โปรดทราบว่าเมื่อคุณกำลังมองหาข้อความ การตั้งค่า [range_lookup] เป็น 0 เป็นความคิดที่ดี หากไม่มีคุณจะได้รับผลลัพธ์ที่ไม่ดี

นี่คือผลลัพธ์:



มันคืนค่า 651 คะแนน SAT ของนักเรียนชื่อ Kennedy Winters ซึ่งอยู่ในแถว 92 (แสดงในสิ่งที่ใส่เข้าไปด้านบน) จะใช้เวลานานกว่ามากในการเลื่อนดูชื่อเพื่อค้นหาชื่อมากกว่าที่จะพิมพ์ไวยากรณ์อย่างรวดเร็ว!

หมายเหตุเกี่ยวกับ VLOOKUP

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



นอกจากนี้ โปรดจำไว้ว่า VLOOKUP จะส่งกลับค่าเดียวเท่านั้น หากคุณใช้ 'จอร์เจีย' เป็นค่าการค้นหา ค่านั้นก็จะส่งคืนคะแนนของนักเรียนคนแรกจากจอร์เจีย และไม่มีข้อบ่งชี้ว่ามีนักเรียนจากจอร์เจียจริงๆ สองคน

2. ฟังก์ชัน HLOOKUP

โดยที่ VLOOKUP ค้นหาค่าที่สอดคล้องกันในคอลัมน์อื่น HLOOKUP จะค้นหาค่าที่สอดคล้องกันในแถวอื่น เนื่องจากโดยปกติแล้วจะง่ายที่สุดในการสแกนผ่านส่วนหัวของคอลัมน์จนกว่าคุณจะพบส่วนหัวที่ถูกต้อง และใช้ตัวกรองเพื่อค้นหาสิ่งที่คุณต้องการ HLOOKUP จึงเหมาะที่สุดเมื่อคุณมีสเปรดชีตขนาดใหญ่จริงๆ หรือคุณกำลังทำงานกับค่าที่จัดเรียงตามเวลา .

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

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [lookup_value] คือค่าที่คุณทราบและต้องการหาค่าที่สอดคล้องกัน
  • [table_array] คือเซลล์ที่คุณต้องการค้นหา
  • [row_index_num] ระบุแถวที่ค่าส่งคืนจะมาจาก
  • [range_lookup] เหมือนกับใน VLOOKUP เว้นว่างไว้เพื่อรับค่าที่ใกล้ที่สุดเมื่อเป็นไปได้ หรือป้อน 0 เพื่อค้นหาเฉพาะรายการที่ตรงกันเท่านั้น

สเปรดชีตนี้มีแถวสำหรับแต่ละรัฐ พร้อมด้วยคะแนน SAT ในปี 2543-2557 คุณสามารถใช้ HLOOKUP เพื่อค้นหาคะแนนเฉลี่ยในรัฐมินนิโซตาในปี 2013 โดยเราจะดำเนินการดังนี้:

=HLOOKUP(2013, A1:P51, 24)

ดังที่คุณเห็นในภาพด้านล่าง คะแนนจะถูกส่งคืน:

Minnesotans ให้คะแนนเฉลี่ย 1,014 ใน 2013 โปรดทราบว่า 2013 ไม่ได้อยู่ในเครื่องหมายคำพูดเพราะเป็นตัวเลขและไม่ใช่สตริง นอกจากนี้ 24 มาจากมินนิโซตาที่อยู่ในแถวที่ 24

นี่มัน วิธีการคำนวณถัวเฉลี่ยถ่วงน้ำหนักใน Excel .

หมายเหตุเกี่ยวกับHLOOKUP

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

3-4. ฟังก์ชัน INDEX และ MATCH

INDEX และ MATCH เป็นฟังก์ชันสองอย่างที่แตกต่างกัน แต่เมื่อใช้งานร่วมกัน ฟังก์ชันเหล่านี้จะทำให้การค้นหาสเปรดชีตขนาดใหญ่เร็วขึ้นมาก ทั้งสองฟังก์ชันมีข้อเสีย แต่ด้วยการรวมเข้าด้วยกัน เราจะสร้างจุดแข็งของทั้งสองอย่าง

อย่างแรก ไวยากรณ์ของฟังก์ชันทั้งสอง:

=INDEX([array], [row_number], [column_number])
  • [อาร์เรย์] คืออาร์เรย์ที่คุณจะค้นหา
  • [แถว_หมายเลข] และ [column_number] สามารถใช้เพื่อจำกัดการค้นหาของคุณให้แคบลง (เราจะมาดูกันในอีกสักครู่)
=MATCH([lookup_value], [lookup_array], [match_type])
  • [lookup_value] เป็นคำค้นหาที่สามารถเป็นสตริงหรือตัวเลขได้
  • [lookup_array] คืออาร์เรย์ที่ Microsoft Excel จะค้นหาคำค้นหา
  • [match_type] เป็นอาร์กิวเมนต์ที่เป็นทางเลือกที่สามารถเป็น 1, 0 หรือ -1 1 จะคืนค่าที่มากที่สุดซึ่งน้อยกว่าหรือเท่ากับข้อความค้นหาของคุณ 0 จะส่งคืนเฉพาะคำที่ตรงทั้งหมดของคุณ และ -1 จะคืนค่าที่น้อยที่สุดที่มากกว่าหรือเท่ากับข้อความค้นหาของคุณ

อาจไม่ชัดเจนว่าเราจะใช้ฟังก์ชันทั้งสองนี้ร่วมกันได้อย่างไร ผมจะวางมันไว้ที่นี่ MATCH ใช้คำค้นหาและส่งกลับการอ้างอิงเซลล์ ในภาพด้านล่าง คุณจะเห็นว่าในการค้นหาค่า 646 ในคอลัมน์ F ฟังก์ชัน MATCH จะคืนค่า 4

ในทางกลับกัน INDEX ทำสิ่งที่ตรงกันข้าม: ใช้การอ้างอิงเซลล์และส่งกลับค่าในนั้น คุณจะเห็นได้ว่าเมื่อได้รับคำสั่งให้ส่งคืนเซลล์ที่หกของคอลัมน์ City แล้ว INDEX จะส่งกลับ 'Anchorage' ซึ่งเป็นค่าจากแถวที่ 6

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

คุณจะได้อะไรจาก linkedin premium
=INDEX(F:F, MATCH('Waters', C:C, 0))

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

ดังที่คุณเห็นจากสิ่งที่ใส่เข้าไป Owen Waters ได้คะแนน 1720 ซึ่งเป็นตัวเลขที่ปรากฏขึ้นเมื่อเราเรียกใช้ฟังก์ชัน สิ่งนี้อาจดูไม่มีประโยชน์ทั้งหมดเมื่อคุณสามารถดูคอลัมน์ได้ไม่กี่คอลัมน์ แต่ลองนึกดูว่าคุณจะประหยัดเวลาได้มากแค่ไหนถ้าคุณต้องทำ 50 ครั้งบน a สเปรดชีตฐานข้อมูลขนาดใหญ่ ที่มีหลายร้อยคอลัมน์!

ให้การค้นหา Excel เริ่มต้นขึ้น

Microsoft Excel มีมากมาย ฟังก์ชั่นอันทรงพลัง สำหรับการจัดการข้อมูล และสี่รายการข้างต้นเป็นเพียงการขีดข่วนพื้นผิว การเรียนรู้วิธีใช้งานจะทำให้ชีวิตของคุณง่ายขึ้นมาก

หากคุณต้องการเชี่ยวชาญ Microsoft Excel จริงๆ คุณสามารถได้รับประโยชน์จากการเก็บ Essential Excel Cheat Sheet ไว้ใกล้มือ!

เครดิตภาพ: Cico/ Shutterstock

แบ่งปัน แบ่งปัน ทวีต อีเมล Canon กับ Nikon: กล้องยี่ห้อไหนดีกว่ากัน?

Canon และ Nikon เป็นสองชื่อที่ใหญ่ที่สุดในอุตสาหกรรมกล้อง แต่แบรนด์ใดที่มีกล้องและเลนส์รุ่นต่างๆ ที่ดีกว่ากัน?

อ่านต่อไป หัวข้อที่เกี่ยวข้อง
  • ผลผลิต
  • สเปรดชีต
  • Microsoft Excel
  • เคล็ดลับการค้นหา
เกี่ยวกับผู้เขียน เอียน บัคลี่ย์(216 บทความที่ตีพิมพ์)

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

เพิ่มเติมจาก Ian Buckley

สมัครรับจดหมายข่าวของเรา

เข้าร่วมจดหมายข่าวของเราสำหรับเคล็ดลับทางเทคนิค บทวิจารณ์ eBook ฟรี และดีลพิเศษ!

คลิกที่นี่เพื่อสมัครสมาชิก