ตัวอย่างบทช่วยสอนนี้ใช้สูตรอาร์เรย์ MEDIAN IF เพื่อค้นหาการประมูลระดับกลางสำหรับสองโครงการที่แตกต่างกัน ลักษณะของสูตรช่วยให้เราค้นหาผลลัพธ์ได้หลายรายการโดยการเปลี่ยนเกณฑ์การค้นหา (ในตัวอย่างบทช่วยสอนนี้ ชื่อโครงการ)
ข้อมูลในบทความนี้ใช้กับ Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 และ Excel สำหรับ Mac
เกี่ยวกับฟังก์ชัน MEDIAN และ IF
งานของแต่ละส่วนของสูตรคือ:
- ฟังก์ชัน MEDIAN ค้นหาค่ากลางสำหรับโครงการ
- ฟังก์ชัน IF ช่วยให้เราเลือกโครงการที่ต้องการประมูลได้โดยกำหนดเงื่อนไขโดยใช้ชื่อโครงการ
- สูตรอาร์เรย์ให้ฟังก์ชัน IF ทดสอบเงื่อนไขหลายเงื่อนไขในเซลล์เดียว เมื่อตรงตามเงื่อนไข สูตรอาร์เรย์จะกำหนดข้อมูล (การประกวดราคาโครงการ) ที่ฟังก์ชัน MEDIAN จะตรวจสอบเพื่อค้นหาการประมูลระดับกลาง
สูตร Excel CSE
สูตรอาร์เรย์ถูกสร้างขึ้นโดยกดปุ่ม Ctrl+ Shift+ Enter บนปุ่ม แป้นพิมพ์พร้อมกันเมื่อพิมพ์สูตรแล้ว เนื่องจากมีการกดแป้นเพื่อสร้างสูตรอาร์เรย์ บางครั้งจึงเรียกว่าสูตร CSE
MEDIAN IF ไวยากรณ์และอาร์กิวเมนต์ของสูตรที่ซ้อนกัน
ไวยากรณ์และอาร์กิวเมนต์สำหรับสูตร MEDIAN IF มีดังนี้:
=MEDIAN(IF(logical_test, value_if_true, value_if_false))
เนื่องจากฟังก์ชัน IF ซ้อนอยู่ภายในฟังก์ชัน MEDIAN ฟังก์ชัน IF ทั้งหมดจะกลายเป็นอาร์กิวเมนต์เดียวสำหรับฟังก์ชัน MEDIAN
อาร์กิวเมนต์สำหรับฟังก์ชัน IF คือ:
- logical_test (จำเป็น): ค่าหรือนิพจน์ที่ทดสอบค่าบูลีนเป็น TRUE หรือ FALSE
- value_if_true (จำเป็น): ค่าที่แสดงถ้า logical_test เป็นจริง
- value_if_false (ทางเลือก): ค่าที่แสดงถ้า logical_test เป็นเท็จ
ตัวอย่างสูตรอาร์เรย์ MEDIAN IF ของ Excel
ตัวอย่างต่อไปนี้ค้นหาการประมูลสำหรับสองโครงการที่แตกต่างกันเพื่อค้นหาการประกวดราคาระดับกลางหรือค่ามัธยฐาน อาร์กิวเมนต์สำหรับฟังก์ชัน IF ทำได้โดยการตั้งค่าเงื่อนไขและผลลัพธ์ต่อไปนี้:
- การทดสอบเชิงตรรกะพบชื่อโครงการที่ตรงกันในเซลล์ D10 ของเวิร์กชีต
- ค่าอาร์กิวเมนต์ value_if_true คือ ด้วยความช่วยเหลือของฟังก์ชัน MEDIAN การประมูลระดับกลางสำหรับโครงการที่เลือก
- อาร์กิวเมนต์ value_if_false ถูกละเว้นเนื่องจากไม่จำเป็น และการไม่มีอาร์กิวเมนต์จะทำให้สูตรสั้นลง ถ้าชื่อโครงการที่ไม่ได้อยู่ในตารางข้อมูล (เช่น โครงการ C) ถูกพิมพ์ลงในเซลล์ D10 สูตรจะส่งคืนค่าศูนย์
ป้อนข้อมูลการสอนใน Excel
-
ป้อนข้อมูลตัวอย่าง ดังที่แสดงด้านบน ลงในเวิร์กชีต Excel เปล่า
- ในเซลล์ D10 พิมพ์ Project A สูตรจะค้นหาในเซลล์นี้เพื่อค้นหาโครงการที่จะจับคู่
ป้อนค่ามัธยฐาน IF สูตรที่ซ้อนกัน
เมื่อคุณสร้างทั้งสูตรที่ซ้อนกันและสูตรอาร์เรย์ ต้องพิมพ์สูตรทั้งหมดลงในเซลล์แผ่นงานเดียว เมื่อสูตรเสร็จสมบูรณ์ อย่ากดปุ่ม Enter หรือเลือกเซลล์อื่นเพราะสูตรจะเปลี่ยนเป็นสูตรอาร์เรย์
A VALUE! ข้อผิดพลาดหมายความว่าป้อนสูตรไม่ถูกต้องเป็นอาร์เรย์
- เลือกเซลล์ E10 นี่คือที่ที่ผลลัพธ์ของสูตรจะแสดง
-
พิมพ์สูตรต่อไปนี้ในเซลล์:
=MEDIAN(IF(D3:D8=D10, E3:E8))
- กดปุ่ม Ctrl และ Shift ค้างไว้
- กดปุ่ม Enter เพื่อสร้างสูตรอาร์เรย์
- คำตอบ 15875 ($ 15, 875 พร้อมการจัดรูปแบบ) ปรากฏในเซลล์ E10 เนื่องจากเป็นการประมูลระดับกลางสำหรับโครงการ A
ทดสอบสูตร
ทดสอบสูตรโดยค้นหาการประกวดราคากลางสำหรับโครงการ B พิมพ์ โครงการ B ลงในเซลล์ D10 แล้วกด Enter ที่สำคัญ
สูตรส่งคืนค่า 24365 ($24, 365) ในเซลล์ E10
