โดยใช้สูตรอาร์เรย์ใน Excel เราสามารถสร้างสูตรการค้นหาที่ใช้เกณฑ์หลายเกณฑ์เพื่อหาข้อมูลในฐานข้อมูลหรือตารางข้อมูล สูตรอาร์เรย์เกี่ยวข้องกับการทำซ้อนฟังก์ชัน MATCH ภายในฟังก์ชัน INDEX
บทแนะนำนี้ประกอบด้วยตัวอย่างทีละขั้นตอนในการสร้างสูตรการค้นหาที่ใช้เกณฑ์หลายเกณฑ์เพื่อหาซัพพลายเออร์ของวิดเจ็ตไทเทเนียม ในฐานข้อมูลตัวอย่าง ปฏิบัติตามเพื่อเรียนรู้เพียงวิธีที่ง่ายก็คือการสร้างสูตรการค้นหาของคุณเอง
เตรียมสมุดงาน Excel ของคุณด้วยข้อมูล
ในการทำตามขั้นตอนในบทแนะนำนี้คุณจะต้องเริ่มต้นด้วยการป้อนข้อมูลตัวอย่างลงในเซลล์ต่อไปนี้ดังที่แสดงในภาพด้านบน แถว 3 และ 4 เว้นว่างไว้เพื่อรองรับสูตรอาร์เรย์ที่สร้างขึ้นในระหว่างบทแนะนำนี้
- ป้อนช่วงข้อมูลด้านบนลงในเซลล์ D1 ไปยัง F2
- ป้อนช่วงที่สองลงในเซลล์ D5 ไปยัง F11
กวดวิชานี้ไม่รวมถึงการจัดรูปแบบที่เห็นในภาพ แต่จะไม่มีผลต่อวิธีที่สูตรการค้นหาทำงานได้ ข้อมูลเกี่ยวกับรูปแบบตัวเลือกที่คล้ายกับที่เห็นข้างต้นมีอยู่ในการกวดวิชาฟอร์แมต Excel ขั้นพื้นฐานนี้
02 จาก 08สร้างฟังก์ชัน INDEX ใน Excel
ดัชนี เป็นหนึ่งในไม่กี่แห่งใน Excel ที่มีหลายรูปแบบ ฟังก์ชั่นมี a แบบอาร์เรย์ และ a แบบฟอร์มอ้างอิง. แบบอาร์เรย์ส่งกลับข้อมูลจริงจากฐานข้อมูลหรือตารางข้อมูลในขณะที่แบบฟอร์มอ้างอิงจะให้ข้อมูลอ้างอิงเซลล์หรือตำแหน่งของข้อมูลในตาราง
ในบทแนะนำนี้เราจะใช้แบบฟอร์มอาร์เรย์เนื่องจากเราต้องการทราบชื่อผู้จัดจำหน่ายเครื่องมือไทเทเนียมแทนการอ้างอิงเซลล์ไปยังผู้จัดจำหน่ายรายนี้ในฐานข้อมูลของเรา ทำตามขั้นตอนต่อไปนี้เพื่อสร้างฟังก์ชัน INDEX ของคุณ:
- คลิกที่เซลล์ F3 เพื่อให้เซลล์ที่ใช้งานอยู่ - นี่คือที่ที่เราจะเข้าสู่ฟังก์ชันที่ซ้อนกัน
- คลิกที่ สูตร แท็บของเมนูริบบัว
- เลือก การค้นหาและการอ้างอิง จากแถบริบบิ้นเพื่อเปิดฟังก์ชั่นแบบเลื่อนลง
- คลิกที่ ดัชนี ในรายการเพื่อนำมาขึ้น เลือกอาร์กิวเมนต์ กล่องโต้ตอบ
- เลือก อาร์เรย์ row_num, col_num ในไดอะลอกบ็อกซ์
- คลิก ตกลง เพื่อเปิดกล่องโต้ตอบฟังก์ชัน INDEX; ซึ่งจะเป็นการเปิดตัวสร้างสูตรใน Excel
- ใน สูตรสร้าง, คลิกที่ แถว เส้น
- ไฮไลต์เซลล์ D6 ไปยัง F11โดยการคลิกและลากในแผ่นงานเพื่อป้อนช่วงลงในตัวสร้าง
ใน Excel รุ่นเก่า สูตรสร้าง จะถูกแทนที่ด้วย อาร์กิวเมนต์ของฟังก์ชัน หน้าต่าง. ใช้ขั้นตอนเดียวกับที่กล่าวถึงในบทแนะนำนี้ในหน้าต่าง
03 จาก 08เริ่มฟังก์ชัน MATCH ที่ซ้อนกัน
เมื่อทำรังเลี้ยงหนึ่งฟังก์ชั่นภายในอีกอันหนึ่งจะไม่สามารถเปิดตัวสร้างสูตรที่สองหรือที่ซ้อนกันได้ o ป้อนอาร์กิวเมนต์ที่จำเป็น ฟังก์ชั่นที่ซ้อนกันจะต้องถูกพิมพ์ลงในลำดับแรกของฟังก์ชันแรก
เมื่อป้อนฟังก์ชันด้วยตนเองอาร์กิวเมนต์ของฟังก์ชันจะถูกแยกออกจากกันโดยใช้เครื่องหมายจุลภาค
ขั้นตอนแรกในการป้อนฟังก์ชัน MATCH ที่ซ้อนกันคือป้อน lookup_value ข้อโต้แย้ง. Lookup_value จะเป็นตำแหน่งหรือเซลล์อ้างอิงสำหรับคำค้นหาที่เราต้องการจับคู่ในฐานข้อมูล
โดยปกติ Lookup_value จะยอมรับเกณฑ์การค้นหาหรือคำค้นหาเพียงอย่างเดียว ในการค้นหาหลายเกณฑ์เราต้องขยาย Lookup_value; เสื้อเขาจะทำโดย concatenating หรือเข้าร่วมสองคนหรือมากกว่าการอ้างอิงเซลล์โดยใช้สัญลักษณ์เครื่องหมาย - &.
- ใน สูตรสร้าง, คลิกที่ ROW_NUM เส้น
- พิมพ์ชื่อฟังก์ชัน การจับคู่ ตามด้วยวงเล็บกลมเปิด
- คลิกที่เซลล์ D3 เพื่อป้อนการอ้างอิงเซลล์นั้นลงในกล่องโต้ตอบ
- พิมพ์เครื่องหมายอัศเจรีย์หลังจากการอ้างอิงเซลล์ D3 เพื่อเพิ่มการอ้างอิงเซลล์ที่สอง
- คลิกที่เซลล์ E3 เพื่อป้อนการอ้างอิงเซลล์ที่สอง
- พิมพ์เครื่องหมายจุลภาคหลังจากการอ้างอิงเซลล์ E3 เพื่อทำรายการของฟังก์ชัน MATCH lookup_value ข้อโต้แย้ง.
ในขั้นตอนสุดท้ายของการสอนเรา Lookup_values จะถูกป้อนลงในเซลล์ D3 และ E3 ของแผ่นงาน
04 จาก 08เสร็จสิ้นฟังก์ชัน MATCH ที่ซ้อนกัน
ขั้นตอนนี้ครอบคลุมการเพิ่ม lookup_array อาร์กิวเมนต์สำหรับซ้อนกัน การจับคู่ ฟังก์ชัน Lookup_array คือช่วงของเซลล์ที่ฟังก์ชัน MATCH จะค้นหาเพื่อค้นหาอาร์กิวเมนต์ Lookup_value ที่เพิ่มในขั้นตอนก่อนหน้าของการกวดวิชา
เนื่องจากเราได้ระบุช่องค้นหาสองช่องไว้ในอาร์กิวเมนต์ Lookup_array แล้วเราจะต้องทำแบบเดียวกันกับ Lookup_array ฟังก์ชัน MATCH จะค้นหาเฉพาะหนึ่งอาร์เรย์สำหรับแต่ละคำที่ระบุเท่านั้นดังนั้นเพื่อป้อนอาร์เรย์หลายอันที่เราใช้เครื่องหมายบวกและต่ออาร์เรย์เข้าด้วยกันอีกครั้ง
ขั้นตอนเหล่านี้จะถูกป้อนหลังจากเครื่องหมายจุลภาคที่ป้อนในขั้นตอนก่อนหน้าใน ROW_NUM บรรทัดใน ดัชนี ฟังก์ชัน
- คลิกที่ ROW_NUM บรรทัดหลังจุลภาคเพื่อวางจุดแทรกที่ท้ายรายการปัจจุบัน
- ไฮไลต์เซลล์ D6 ไปยัง D11 ในแผ่นงานเพื่อป้อนช่วง - เป็นแถวแรกฟังก์ชันคือการค้นหา
- พิมพ์เครื่องหมายอัศเจรีย์หลังจากการอ้างอิงเซลล์ D6: D11 เพราะเราต้องการให้ฟังก์ชันค้นหาทั้งสองอาร์เรย์
- ไฮไลต์เซลล์ E6 ไปยัง E11 ในแผ่นงานเพื่อป้อนช่วง - เป็นแถวที่สองฟังก์ชั่นคือการค้นหา
- พิมพ์เครื่องหมายจุลภาคหลังจากการอ้างอิงเซลล์ E3 เพื่อให้รายการของ การจับคู่ ฟังก์ชั่น lookup_array ข้อโต้แย้ง.
- ออกจาก ดัชนี เปิดกล่องโต้ตอบฟังก์ชันสำหรับขั้นตอนถัดไปในบทแนะนำ
เพิ่มอาร์กิวเมนต์ประเภท 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 บรรทัดใน ดัชนี ฟังก์ชัน
- ตามจุลภาคบน ROW_NUM line พิมพ์เครื่องหมาย zero - 0 - เนื่องจากเราต้องการให้ฟังก์ชันที่ซ้อนกันสามารถส่งคำที่ตรงกับคำที่เราป้อนลงในเซลล์ได้ D3 และ E3.
- พิมพ์วงเล็บกลมปิด - ) - เพื่อดำเนินการ การจับคู่ ฟังก์ชัน
- ออกจาก สูตรสร้าง เปิดสำหรับขั้นตอนต่อไปในบทแนะนำ
การสิ้นสุดฟังก์ชัน INDEX
ตอนนี้ที่ การจับคู่ เราจะย้ายไปที่บรรทัดที่สามของตัวสร้างสูตรและป้อนอาร์กิวเมนต์สุดท้ายสำหรับ ดัชนี ฟังก์ชัน อาร์กิวเมนต์ที่สามและสุดท้ายนี้คือ คอลัมน์ อาร์กิวเมนต์ที่บอก Excel หมายเลขคอลัมน์ในช่วง D6 ไปยัง F11; นี่คือที่ที่มันจะหาข้อมูลที่เราต้องการจะถูกส่งกลับโดยฟังก์ชัน ในกรณีนี้คือซัพพลายเออร์สำหรับ เครื่องมือไทเทเนียม.
- คลิกที่ คอลัมน์ เส้น
- ใส่หมายเลขสาม - 3 - ในบรรทัดนี้เนื่องจากเรากำลังมองหาข้อมูลในคอลัมน์ที่สามของช่วง D6 ไปยัง F11.
- อีกครั้งปล่อยให้ สูตรสร้าง เปิดสำหรับขั้นตอนต่อไปในบทแนะนำ
การสร้างสูตรอาร์เรย์
ก่อนที่จะปิด สูตรสร้างเราต้องเปลี่ยนฟังก์ชันที่ซ้อนกันของเราเป็นสูตรอาร์เรย์ นี่คือสิ่งที่ช่วยให้ฟังก์ชันของเราสามารถค้นหาคำต่างๆในตารางข้อมูลได้ ในบทแนะนำนี้เรากำลังค้นหาคู่คำสองคำ: วิดเจ็ต จากคอลัมน์ 1 และ ไทเทเนียม จากคอลัมน์ 2
การสร้างสูตรอาร์เรย์ใน Excel ทำได้โดยการกดปุ่ม CTRL, SHIFT, และ ENTER บนแป้นพิมพ์ในเวลาเดียวกัน เมื่อกดแล้วฟังก์ชันจะถูกล้อมรอบด้วยวงเล็บปีกกาแสดงว่าฟังก์ชันนี้เป็นอาร์เรย์แล้ว
- ปิด สูตรสร้าง โดยการคลิกที่ เสร็จสิ้น ปุ่ม.
- ถัดไปเลือกเซลล์ F4 และกดปุ่ม เข้าสู่ คีย์เพื่อดูสูตร
- ในการแปลงสูตรเป็นอาร์เรย์ให้กดพร้อมกัน CTRL + SHIFT + ENTER บนแป้นพิมพ์ของคุณ
- ถ้าทำอย่างถูกต้อง a # N / A ข้อผิดพลาดจะปรากฏในเซลล์ F3 - เซลล์ที่เราป้อนฟังก์ชัน
- # N / A ข้อผิดพลาดปรากฏขึ้นในเซลล์ F3 เพราะเซลล์ D3 และ E3 ว่างเปล่า D3 และ E3 เป็นเซลล์ที่เราบอกว่าฟังก์ชั่นเพื่อค้นหา Lookup_values. เมื่อมีการเพิ่มข้อมูลลงในเซลล์ทั้งสองเซลล์ข้อผิดพลาดจะถูกแทนที่ด้วยข้อมูลจากฐานข้อมูล
การเพิ่มเกณฑ์การค้นหา
สุดท้ายเราจะเพิ่มคำค้นหาลงในแผ่นงานของเรา ดังที่ได้กล่าวไว้ในขั้นตอนก่อนหน้านี้เรากำลังมองหาเพื่อให้ตรงกับข้อกำหนด วิดเจ็ต จากคอลัมน์ 1 และ ไทเทเนียม จากคอลัมน์ 2. ถ้าและถ้าสูตรของเราพบการจับคู่ทั้งสองคำในคอลัมน์ที่เหมาะสมในฐานข้อมูลจะเป็นการส่งคืนค่าจากคอลัมน์ที่สาม
- คลิกที่เซลล์ D3.
- ชนิด วิดเจ็ต และกดปุ่ม เข้าสู่ บนแป้นพิมพ์
- คลิกที่เซลล์ E3.
- ชนิด ไทเทเนียม และกดปุ่ม เข้าสู่ บนแป้นพิมพ์
- ชื่อผู้จัดจำหน่าย Widgets Inc. ควรปรากฏในเซลล์ F3 - ตำแหน่งของฟังก์ชันเนื่องจากเป็นผู้จัดหารายเดียวที่ขายไทเทเนี่ยมวิดเจ็ต
- เมื่อคุณคลิกที่เซลล์ F3 ฟังก์ชันที่สมบูรณ์จะปรากฏในแถบสูตรเหนือแผ่นงาน
{= INDEX (D6: F11 ตรง (D3 & E3, D6: D11 & E6: E11, 0), 3)}
ในตัวอย่างของเรามีซัพพลายเออร์เพียงตัวเดียวสำหรับเครื่องมือไทเทเนียม หากมีซัพพลายเออร์มากกว่าหนึ่งรายผู้จัดจำหน่ายรายแรกในฐานข้อมูลจะถูกส่งกลับโดยฟังก์ชัน
