สูตรการค้นหา Excel มีหลายเกณฑ์

โดยใช้ สูตรอาร์เรย์ ใน Excel เราสามารถสร้างสูตรการค้นหาที่ใช้เกณฑ์หลายเกณฑ์เพื่อหาข้อมูลใน ฐานข้อมูล หรือตารางข้อมูล

สูตรอาร์เรย์เกี่ยวข้องกับการทำซ้อนฟังก์ชัน MATCH ภายในฟังก์ชัน INDEX

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

ทำตามขั้นตอนในหัวข้อการสอนด้านล่างนี้เพื่อแนะนำให้คุณสร้างและใช้สูตรที่เห็นในภาพด้านบน

01 จาก 09

การป้อนข้อมูลบทแนะนำ

ขั้นตอนแรกในการกวดวิชาคือการใส่ ข้อมูล ลงใน แผ่นงาน Excel

ในการทำตามขั้นตอนในบทแนะนำให้ป้อนข้อมูลที่แสดงในรูปภาพด้านบนลงใน เซลล์ ต่อไปนี้

• ป้อน ช่วง ข้อมูลด้านบนลงในเซลล์ D1 ถึง F2
• ป้อนช่วงที่สองลงในเซลล์ D5 ถึง F11

แถว 3 และ 4 เว้นว่างไว้เพื่อรองรับ สูตรอาร์เรย์ที่ สร้างขึ้นในระหว่างบทแนะนำนี้

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

ข้อมูลเกี่ยวกับรูปแบบตัวเลือกที่คล้ายกับที่เห็นข้างต้นมีอยู่ในการกวดวิชาฟอร์แมต Excel ขั้นพื้นฐานนี้

02 จาก 09

เริ่มต้นฟังก์ชัน INDEX

ฟังก์ชัน INDEX เป็นหนึ่งในไม่กี่แห่งใน Excel ที่มีหลายรูปแบบ ฟังก์ชัน มี แบบอาร์เรย์ และ แบบฟอร์มอ้างอิง

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

ในบทแนะนำนี้เราจะใช้แบบฟอร์มอาร์เรย์เนื่องจากเราต้องการทราบชื่อผู้จัดจำหน่ายเครื่องมือไทเทเนียมมากกว่าการอ้างอิงเซลล์ไปยังผู้จัดจำหน่ายรายนี้ในฐานข้อมูลของเรา

แต่ละแบบมี อาร์กิวเมนต์ ที่แตกต่างกันซึ่งต้องเลือกก่อนที่จะเริ่มทำงาน

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

1. คลิกที่เซลล์ F3 เพื่อทำให้ เซลล์ นั้นเป็น เซลล์ที่ใช้งานอยู่ นี่คือที่ที่เราจะเข้าสู่ฟังก์ชันที่ซ้อนกัน
2. คลิกแท็บ สูตร ในเมนู ริบบัว
3. เลือกการ ค้นหาและการอ้างอิง จากริบบิ้นเพื่อเปิดรายการแบบเลื่อนลงฟังก์ชั่น
4. คลิกที่ INDEX ในรายการเพื่อเปิด หน้าต่างโต้ตอบ เลือกอาร์กิวเมนต์
5. เลือก อาร์เรย์ row_num, col_num ตัวเลือกในกล่องโต้ตอบ
6. คลิกตกลงเพื่อเปิดกล่องโต้ตอบฟังก์ชัน INDEX

03 จาก 09

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

ต้องใช้อาร์กิวเมนต์อาร์เรย์อาร์กิวเมนต์แรก อาร์กิวเมนต์นี้ระบุ ช่วง ของ เซลล์ ที่จะค้นหาข้อมูลที่ต้องการ

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

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

1. ใน กล่องโต้ตอบ ฟังก์ชัน INDEX ให้คลิกที่เส้น Array
2. ไฮไลต์เซลล์ D6 ถึง F11 ใน แผ่นงาน เพื่อป้อนช่วงลงในกล่องโต้ตอบ

04 จาก 09

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

เมื่อทำรังเลี้ยงหนึ่ง ฟังก์ชัน ภายในอื่น ๆ ไม่สามารถเปิด กล่องโต้ตอบ ของฟังก์ชันที่สองหรือที่ซ้อนกันเพื่อป้อน อาร์กิวเมนต์ที่ จำเป็น

ฟังก์ชันที่ซ้อนกันจะต้องถูกพิมพ์ลงในอาร์กิวเมนต์หนึ่งของฟังก์ชันแรก

ในบทแนะนำนี้ฟังก์ชัน MATCH ที่ซ้อนกันและอาร์กิวเมนต์จะถูกป้อนลงในบรรทัดที่สองของกล่องโต้ตอบฟังก์ชัน INDEX - แถว Row_num

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

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

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

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

โดยปกติ Lookup_value จะยอมรับเกณฑ์การค้นหาหรือคำค้นหาเพียงอย่างเดียว ในการค้นหาหลายเกณฑ์เราต้องขยาย Lookup_value

โดยการ รวม หรือ เชื่อมโยง เซลล์สองตัวหรือมากกว่าเข้าด้วยกันโดยใช้เครื่องหมายสัญลักษณ์ " & "

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

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

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

05 จาก 09

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

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

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

เนื่องจากเราได้ระบุช่องค้นหาสองช่องไว้ในอาร์กิวเมนต์ Lookup_array แล้ว เราจะต้องทำแบบเดียวกันกับ Lookup_array ฟังก์ชัน MATCH จะค้นหาเฉพาะหนึ่งอาร์เรย์สำหรับแต่ละคำที่ระบุเท่านั้น

ในการใส่อาร์เรย์หลาย ๆ อันเราจะใช้เครื่องหมาย " & " เพื่อ รวม อาร์เรย์เข้าด้วยกัน

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

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

1. คลิกที่บรรทัด Row_num หลังจากเครื่องหมายจุลภาคเพื่อวางจุดแทรกที่ตอนท้ายของรายการปัจจุบัน
2. ไฮไลต์เซลล์ D6 ถึง D11 ใน แผ่นงาน เพื่อป้อนช่วง นี่คืออาร์เรย์แรกของฟังก์ชันคือการค้นหา
3. พิมพ์เครื่องหมาย " และ " หลังจากเซลล์อ้างอิง D6: D11 เนื่องจากเราต้องการให้ฟังก์ชันค้นหาทั้งสองอาร์เรย์
4. ไฮไลต์เซลล์ E6 ถึง E11 ในแผ่นงานเพื่อป้อนช่วง นี่คืออาร์เรย์ที่สองฟังก์ชันคือการค้นหา
5. พิมพ์เครื่องหมายจุลภาค "," หลังจากการอ้างอิงเซลล์ E3 เพื่อกรอกข้อมูลของอาร์กิวเมนต์ Lookup_array ของฟังก์ชัน MATCH
6. ปล่อยให้กล่องโต้ตอบฟังก์ชัน INDEX เปิดขึ้นสำหรับขั้นตอนต่อไปในบทแนะนำ

06 จาก 09

การเพิ่มประเภทการจับคู่และการเสร็จสิ้นฟังก์ชัน 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 ใน กล่องโต้ตอบ ฟังก์ชัน INDEX

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

07 จาก 09

กลับไปที่ฟังก์ชัน INDEX

เราจะย้ายไปที่บรรทัดที่สามของ กล่องโต้ตอบ เปิดและป้อน อาร์กิวเมนต์ สุดท้ายสำหรับฟังก์ชัน INDEX

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

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

1. คลิกที่ Column_num line ในไดอะลอกบ็อกซ์
2. ป้อนหมายเลขสาม " 3 " (ไม่มีเครื่องหมายคำพูด) ในบรรทัดนี้เนื่องจากเรากำลังมองหาข้อมูลในคอลัมน์ที่สามของช่วง D6 ถึง F11
3. อย่าคลิกตกลงหรือปิดกล่องโต้ตอบฟังก์ชัน INDEX ต้องเปิดกว้างสำหรับขั้นตอนต่อไปในบทแนะนำ - การสร้าง สูตรอาร์เรย์

08 จาก 09

การสร้างสูตรอาร์เรย์

ก่อนปิด กล่องโต้ตอบ เราต้องเปลี่ยนฟังก์ชันที่ซ้อนกันของเราเป็น สูตรอาร์เรย์

สูตรอาร์เรย์คือสิ่งที่ช่วยให้สามารถค้นหาคำต่างๆได้ในตารางข้อมูล ในบทแนะนำนี้เราต้องการจับคู่คำสองคำ: วิดเจ็ตจากคอลัมน์ 1 และไทเทเนียมจากคอลัมน์ 2

การสร้างสูตรอาร์เรย์ใน Excel ทำได้โดยการกดปุ่ม CTRL , SHIFT และ ENTER บนแป้นพิมพ์ในเวลาเดียวกัน

ผลของการกดคีย์เหล่านี้ร่วมกันคือการล้อมรอบฟังก์ชันด้วยวงเล็บปีกกา: {} แสดงให้เห็นว่าขณะนี้เป็นสูตรอาร์เรย์

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

1. ด้วยกล่องโต้ตอบเสร็จสมบูรณ์ที่ยังคงเปิดจากขั้นตอนก่อนหน้าของบทแนะนำนี้ให้กด CTRL และ SHIFT ค้างไว้บนแป้นพิมพ์จากนั้นกดและปล่อยคีย์ ENTER
2. หากทำอย่างถูกต้องกล่องโต้ตอบจะปิดและข้อผิดพลาด # N / A จะปรากฏในเซลล์ F3 ซึ่งเป็นเซลล์ที่เราป้อนฟังก์ชัน
3. ข้อผิดพลาด # N / A ปรากฏใน เซลล์ F3 เนื่องจากเซลล์ D3 และ E3 ว่างเปล่า D3 และ E3 คือเซลล์ที่เราบอกว่าฟังก์ชันค้นหาเฟิร์มแวร์ในขั้นตอนที่ 5 ของบทแนะนำ เมื่อมีการเพิ่มข้อมูลลงในเซลล์ทั้งสองเซลล์ข้อผิดพลาดจะถูกแทนที่ด้วยข้อมูลจาก ฐานข้อมูล

09 จาก 09

การเพิ่มเกณฑ์การค้นหา

ขั้นตอนสุดท้ายในบทแนะนำคือการเพิ่มคำค้นหาในแผ่นงานของเรา

ดังที่ได้กล่าวไว้ในขั้นตอนก่อนหน้านี้เรากำลังมองหาเพื่อให้ตรงกับข้อกำหนดของ วิดเจ็ต จากคอลัมน์ 1 และ ไทเทเนียม จากคอลัมน์ 2

ถ้าและเฉพาะในกรณีที่สูตรของเราพบการจับคู่ทั้งสองคำในคอลัมน์ที่เหมาะสมในฐานข้อมูลจะส่งคืนค่าจากคอลัมน์ที่สาม

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

1. คลิกที่เซลล์ D3
2. พิมพ์ วิดเจ็ต แล้วกดปุ่ม Enter บนแป้นพิมพ์
3. คลิกที่เซลล์ E3
4. พิมพ์ Titanium และกดปุ่ม Enter บนแป้นพิมพ์
5. ชื่อ วิดเจ็ต Widgets Inc. ควรปรากฏในเซลล์ F3 - ตำแหน่งของฟังก์ชันเนื่องจากเป็นผู้จัดหารายเดียวที่ขายไทเทเนี่ยมวิดเจ็ต
6. เมื่อคุณคลิกที่เซลล์ F3 ฟังก์ชั่นที่สมบูรณ์
   {= INDEX (D6: F11 ตรง (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   ปรากฏใน แถบสูตร เหนือ แผ่นงาน

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