การค้นหา Excel แบบสองทางโดยใช้ VLOOKUP ส่วนที่ 2

01 จาก 06

เริ่มต้นฟังก์ชัน MATCH ที่ซ้อนกัน

กลับไปที่ส่วนที่ 1

การป้อนฟังก์ชัน MATCH เป็นอาร์กิวเมนต์จำนวนคอลัมน์ดัชนี

โดยปกติ VLOOKUP จะแสดงเฉพาะข้อมูลจาก คอลัมน์ หนึ่งของตารางข้อมูลและคอลัมน์นี้จะถูกกำหนดโดยอาร์กิวเมนต์ หมายเลขดัชนีของคอลัมน์

อย่างไรก็ตามในตัวอย่างนี้เรามีคอลัมน์สามคอลัมน์ที่เราต้องการค้นหาข้อมูลดังนั้นเราจำเป็นต้องมีวิธีการเปลี่ยน หมายเลขดัชนีของคอลัมน์ โดยไม่แก้ไขสูตรการค้นหาของเรา

นี่คือที่ที่ฟังก์ชัน MATCH เข้ามาเล่น จะช่วยให้เราสามารถจับคู่หมายเลขคอลัมน์กับ ชื่อฟิลด์ - มกราคม, กุมภาพันธ์หรือมีนาคม - ซึ่งเราพิมพ์ลงในเซลล์ E2 ของแผ่นงาน

ฟังก์ชันการทำรัง

ฟังก์ชัน MATCH จึงทำหน้าที่เป็น อาร์กิวเมนต์ หมายเลขดัชนี ของ VLOOKUP

ทำได้โดยการซ้อนฟังก์ชัน MATCH ภายใน VLOOKUP ในบรรทัด Col_index_num ของกล่องโต้ตอบ

การป้อนฟังก์ชัน MATCH ด้วยตนเอง

เมื่อทำหน้าที่ทำรัง Excel ไม่อนุญาตให้เราเปิด กล่องโต้ตอบ ของฟังก์ชันที่สองเพื่อป้อนอาร์กิวเมนต์

ฟังก์ชัน MATCH ต้องถูกป้อนด้วยตนเองในบรรทัด Col_index_num

เมื่อป้อนฟังก์ชันด้วยตนเองอาร์กิวเมนต์แต่ละฟังก์ชันจะต้องคั่นด้วยเครื่องหมายจุลภาค ","

ขั้นตอนการสอน

การป้อนอาร์กิวเมนต์ Lookup_value ของฟังก์ชัน MATCH

ขั้นตอนแรกในการป้อนฟังก์ชัน MATCH ที่ซ้อนกันอยู่เพื่อป้อนอาร์กิวเมนต์ Lookup_value

Lookup_value จะเป็นตำแหน่งหรือ เซลล์อ้างอิง สำหรับคำค้นหาที่เราต้องการจับคู่ในฐานข้อมูล

1. ในกล่องโต้ตอบฟังก์ชัน VLOOKUP คลิกที่บรรทัด Col_index_num
2. พิมพ์ชื่อฟังก์ชันตามด้วยวงเล็บกลมเปิด " ( "
3. คลิกที่เซลล์ E2 เพื่อป้อนการอ้างอิงเซลล์นั้นลงในไดอะลอกบ็อกซ์
4. พิมพ์เครื่องหมายจุลภาค "," หลังจากการอ้างอิงเซลล์ E3 เพื่อกรอกข้อมูลอาร์กิวเมนต์ Lookup_value ของฟังก์ชัน MATCH
5. ปล่อยให้ VLOOKUP เปิดกล่องโต้ตอบสำหรับขั้นตอนถัดไปในบทแนะนำ

ในขั้นตอนสุดท้ายของการสอน Lookup_values ​​จะถูกป้อนลงในเซลล์ D2 และ E2 ของ แผ่นงาน

02 จาก 06

การเพิ่ม Lookup_array สำหรับฟังก์ชัน MATCH

การเพิ่ม Lookup_array สำหรับฟังก์ชัน MATCH

ขั้นตอนนี้ครอบคลุมการเพิ่มอาร์กิวเมนต์ Lookup_array สำหรับฟังก์ชัน MATCH ที่ซ้อนกัน

Lookup_array คือช่วงของเซลล์ที่ฟังก์ชัน MATCH จะค้นหาเพื่อค้นหาอาร์กิวเมนต์ Lookup_value ที่ เพิ่มในขั้นตอนก่อนหน้าของการกวดวิชา

ในตัวอย่างนี้เราต้องการฟังก์ชัน MATCH เพื่อค้นหาเซลล์ D5 ถึง G5 เพื่อให้ตรงกับชื่อของเดือนที่จะถูกป้อนลงในเซลล์ E2

ขั้นตอนการสอน

ขั้นตอนเหล่านี้จะต้องป้อนหลังเครื่องหมายจุลภาคที่ป้อนในขั้นตอนก่อนหน้าในบรรทัด Col_index_num ในกล่องโต้ตอบฟังก์ชัน VLOOKUP

1. หากจำเป็นให้คลิกที่บรรทัด Col_index_num หลังจากเครื่องหมายจุลภาคเพื่อวางจุดแทรกที่ส่วนท้ายของรายการปัจจุบัน
2. ไฮไลต์เซลล์ D5 ถึง G5 ในแผ่นงานเพื่อป้อนข้อมูลการอ้างอิงเซลล์เหล่านี้เป็นช่วงของฟังก์ชันคือการค้นหา
3. กดแป้น F4 บนแป้นพิมพ์เพื่อเปลี่ยนช่วงนี้เป็น ข้อมูลอ้างอิงของเซลล์ที่แน่นอน การทำเช่นนี้จะทำให้สามารถคัดลอกสูตรการค้นหาที่เสร็จสมบูรณ์ไปยังตำแหน่งอื่น ๆ ในแผ่นงานได้ในขั้นตอนสุดท้ายของการกวดวิชา
4. พิมพ์เครื่องหมายจุลภาค "," หลังจากการอ้างอิงเซลล์ E3 เพื่อกรอกข้อมูลของอาร์กิวเมนต์ Lookup_array ของฟังก์ชัน MATCH

03 จาก 06

การเพิ่มประเภทการจับคู่และการเสร็จสิ้นฟังก์ชัน MATCH

การเพิ่มประเภทการจับคู่และการเสร็จสิ้นฟังก์ชัน MATCH

อาร์กิวเมนต์ ที่สามและสุดท้ายของฟังก์ชัน MATCH คือ อาร์กิวเมนต์ Match_type

อาร์กิวเมนต์นี้บอก Excel ว่าจะจับคู่ Lookup_value กับค่าใน Lookup_array อย่างไร ตัวเลือกคือ: -1, 0 หรือ 1

อาร์กิวเมนต์นี้ไม่จำเป็น ถ้าถูกละเว้นฟังก์ชันใช้ค่าเริ่มต้นเป็น 1

• ถ้า Match_type = 1 หรือถูกละไว้: MATCH พบค่าที่ใหญ่ที่สุดที่น้อยกว่าหรือเท่ากับ Lookup_value ถ้าเลือกค่านี้ข้อมูล Lookup_array ต้อง เรียงตามลำดับ จากน้อยไปมาก
• ถ้า Match_type = 0: MATCH พบค่าแรกที่เท่ากับ Lookup_value ข้อมูล Lookup_array สามารถจัดเรียงตามลำดับใดก็ได้
• ถ้า Match_type = 1: MATCH พบค่าที่น้อยที่สุดซึ่งมากกว่าหรือเท่ากับ Lookup_value หากเลือกค่านี้ข้อมูล Lookup_array จะต้องเรียงลำดับจากมากไปน้อย

ขั้นตอนการสอน

ขั้นตอนเหล่านี้จะถูกป้อนหลังจากเครื่องหมายจุลภาคที่ป้อนในขั้นตอนก่อนหน้าในบรรทัด Row_num ในกล่องโต้ตอบฟังก์ชัน VLOOKUP

1. หลังจากเครื่องหมายจุลภาคที่สองในบรรทัด Col_index_num ให้พิมพ์ 0 เป็น 0 เนื่องจากเราต้องการให้ฟังก์ชันที่ซ้อนกันส่งคืนการจับคู่แบบตรงทั้งหมดกับเดือนที่ป้อนในเซลล์ E2
2. พิมพ์วงเล็บกลมปิด " ) " เพื่อทำฟังก์ชัน MATCH
3. ปล่อยให้ VLOOKUP เปิดกล่องโต้ตอบสำหรับขั้นตอนถัดไปในบทแนะนำ

04 จาก 06

การป้อนอาร์กิวเมนต์ Lookup Range VLOOKUP

อาร์กิวเมนต์ Lookup Range

อาร์กิวเมนต์ Range_lookup ของ VLOOKUP เป็น ค่าลอจิคัล (TRUE หรือ FALSE เท่านั้น) ที่ระบุว่าคุณต้องการให้ VLOOKUP หาคำค้นหาที่ตรงหรือตรงกับ Lookup_value หรือไม่

• ถ้า TRUE หรือถ้าอาร์กิวเมนต์นี้ถูกละเว้น VLOOKUP จะส่งกลับค่าที่ตรงกันทั้งหมดไปยัง Lookup_value หรือถ้าไม่พบการจับคู่ที่ตรงกัน VLOOKUP จะส่งค่าที่ใหญ่ที่สุดถัดไป สำหรับสูตรการทำเช่นนี้ข้อมูลในคอลัมน์แรกของ Table_array ต้อง เรียงตามลำดับจากน้อยไปมาก
• ถ้า FALSE VLOOKUP จะใช้การจับคู่แบบตรงทั้งหมดกับ Lookup_value เท่านั้น หากมีค่าตั้งแต่สองค่าขึ้นไปในคอลัมน์แรกของ Table_array ที่ตรงกับค่าการค้นหาค่าแรกที่พบจะถูกใช้ หากไม่พบการทำงานแบบตรงทั้งหมดระบบจะส่งคืนข้อผิดพลาด # N / A

ในบทแนะนำนี้เนื่องจากเรากำลังมองหาตัวเลขการขายสำหรับเดือนใดเดือนหนึ่งเราจะตั้งค่า Range_lookup เท่ากับ False

ขั้นตอนการสอน

1. คลิกที่บรรทัด Range_lookup ในไดอะลอกบ็อกซ์
2. พิมพ์คำว่า False ในบรรทัดนี้เพื่อระบุว่าเราต้องการให้ VLOOKUP คืนค่าการจับคู่แบบตรงทั้งหมดสำหรับข้อมูลที่เรากำลังหาอยู่
3. คลิกตกลงเพื่อทำสูตรการค้นหาสองมิติและปิดกล่องโต้ตอบ
4. เนื่องจากเรายังไม่ได้ป้อนเกณฑ์การค้นหาลงในเซลล์ D2 และ E2 ข้อผิดพลาด # N / A จะปรากฏในเซลล์ F2
5. ข้อผิดพลาดนี้จะได้รับการแก้ไขในขั้นตอนต่อไปในบทแนะนำเมื่อเราจะเพิ่มเกณฑ์การค้นหาในขั้นตอนต่อไปของบทแนะนำ

05 จาก 06

การทดสอบสูตรค้นหาแบบสองทาง

การทดสอบสูตรค้นหาแบบสองทาง

ในการใช้สูตรค้นหาแบบสองวิธีเพื่อค้นหาข้อมูลการขายรายเดือนสำหรับคุกกี้อื่นที่ระบุในอาร์เรย์ของตารางให้พิมพ์ชื่อคุกกี้ลงในเซลล์ D2 ซึ่งเป็นเดือนในเซลล์ E2 และกดปุ่ม ENTER บนแป้นพิมพ์

ข้อมูลการขายจะปรากฏในเซลล์ F2

ขั้นตอนการสอน

1. คลิกที่เซลล์ D2 ในแผ่นงานของคุณ
2. พิมพ์ ข้าวโอ๊ต ลงในเซลล์ D2 แล้วกดปุ่ม ENTER บนแป้นพิมพ์
3. คลิกที่เซลล์ E2
4. พิมพ์ February ลงในเซลล์ E2 และกดปุ่ม ENTER บนแป้นพิมพ์
5. มูลค่าควรเป็น จำนวน 1,345 ดอลลาร์ ซึ่งเป็นยอดขายของคุกกี้ข้าวโอ๊ตในเดือนกุมภาพันธ์ - ควรแสดงในเซลล์ F2
6. ณ จุดนี้แผ่นงานของคุณควรตรงกับตัวอย่างใน หน้า 1 ของบทแนะนำนี้
7. ทดสอบสูตรการค้นหาต่อไปโดยการพิมพ์คุกกี้และเดือนที่มีอยู่ใน Table_array รวมทั้งตัวเลขยอดขายควรปรากฏในเซลล์ F2
8. ขั้นตอนสุดท้ายในการกวดวิชานี้ครอบคลุมการคัดลอกสูตรการค้นหาด้วยการใช้ Fill Handle

หากข้อความแสดงข้อผิดพลาดเช่น #REF! ปรากฏในเซลล์ F2 รายการของข้อความแสดงข้อผิดพลาด VLOOKUP นี้อาจช่วยให้คุณทราบว่าปัญหาอยู่ที่ใด

06 จาก 06

การคัดลอกสูตรการค้นหาแบบสองมิติด้วยการเติม

การคัดลอกสูตรการค้นหาแบบสองมิติด้วยการเติม

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

เนื่องจากข้อมูลถูกวางไว้ในรูปแบบปกติในแผ่นงานเราสามารถคัดลอกสูตรการค้นหาในเซลล์ F2 ไปยังเซลล์ F3 ได้

เมื่อมีการคัดลอกสูตร Excel จะอัปเดตการอ้างอิงเซลล์สัมพัทธ์เพื่อให้สอดคล้องกับตำแหน่งใหม่ของสูตร ในกรณีนี้ D2 กลายเป็น D3 และ E2 กลายเป็น E3,

Excel ช่วยให้การอ้างอิงของเซลล์สัมบูรณ์เหมือนกันดังนั้นช่วงที่แน่นอน $ D $ 5: $ G $ 5 ยังคงเหมือนเดิมเมื่อทำสำเนาสูตร

มีวิธีการคัดลอกข้อมูลใน Excel มากกว่าหนึ่งวิธี แต่อาจเป็นวิธีที่ง่ายที่สุดคือการใช้ Fill Handle

ขั้นตอนการสอน

1. คลิกที่เซลล์ D3 ในแผ่นงานของคุณ
2. พิมพ์ ข้าวโอ๊ต ลงในเซลล์ D3 และกดปุ่ม ENTER บนแป้นพิมพ์
3. คลิกที่เซลล์ E3
4. พิมพ์ March ลงในเซลล์ E3 และกดปุ่ม ENTER บนแป้นพิมพ์
5. คลิกเซลล์ F2 เพื่อทำให้เซลล์เป็นเซลล์ที่ใช้งานอยู่
6. วางตัวชี้เมาส์ไว้เหนือสี่เหลี่ยมสีดำที่มุมล่างขวา ตัวชี้จะเปลี่ยนเป็นเครื่องหมายบวก "+" - นี่คือ Fill Handle
7. คลิกปุ่มซ้ายของเมาส์และลากที่จับเติมลงไปที่เซลล์ F3
8. ปล่อยปุ่มเมาส์และเซลล์ F3 ควรมีสูตรการค้นหาแบบสองมิติ
9. มูลค่าที่ 1,287 ดอลลาร์ - ยอดขายของคุกกี้ข้าวโอ๊ตในเดือนมีนาคม - ควรแสดงในเซลล์ F3