รวมฟังก์ชัน MEDIAN และ IF ในสูตรอาร์เรย์
ตัวอย่างการกวดวิชานี้ใช้สูตรอาร์เรย์ MEDIAN IF เพื่อหาซื้อกลางสำหรับโครงการที่แตกต่างกันสองโครงการ
ลักษณะของสูตรช่วยให้เราสามารถค้นหาผลลัพธ์หลายอย่างได้ง่ายๆโดยการเปลี่ยนเกณฑ์การค้นหา - ในกรณีนี้ชื่อโครงการ
งานของแต่ละส่วนของสูตรคือ:
- ฟังก์ชัน MEDIAN พบค่ากลางสำหรับโครงการ
- ฟังก์ชัน IF ช่วยให้เราสามารถเลือกโครงการที่เราต้องการซื้อได้โดยการตั้งเงื่อนไขโดยใช้ชื่อโครงการ
- สูตรอาร์เรย์จะ ช่วยให้การทดสอบฟังก์ชัน IF สำหรับเงื่อนไขหลายข้อในเซลล์เดียวและเมื่อเงื่อนไขได้รับการตอบสนองสูตรอาร์เรย์จะกำหนดข้อมูลใด (โครงการประกวดราคา) ฟังก์ชัน MEDIAN จะตรวจสอบเพื่อหาซื้อกลาง
สูตร CSE
สูตรอาร์เรย์ถูกสร้างขึ้นโดยกดปุ่ม Ctrl , Shift และ Enter บนแป้นพิมพ์พร้อมกันเมื่อป้อนสูตรแล้ว
เนื่องจากคีย์ที่กดเพื่อสร้างสูตรอาร์เรย์จึงมีบางครั้งเรียกว่าสูตร CSE
MEDIAN IF ไวยากรณ์สูตรที่ซ้อนกันและอาร์กิวเมนต์
ไวยากรณ์ สำหรับสูตร MEDIAN IF คือ:
& # 61; MEDIAN (IF (logical_test, value_if_true, value_if_false))
- เนื่องจากฟังก์ชัน IF ถูกซ้อนกันภายในฟังก์ชัน MEDIAN ฟังก์ชัน IF ทั้งหมดจะกลายเป็น อาร์กิวเมนต์ เพียงอย่างเดียวสำหรับฟังก์ชัน MEDIAN
อาร์กิวเมนต์สำหรับฟังก์ชัน IF คือ:
- logical_test - (จำเป็น) ค่าหรือนิพจน์ที่ทดสอบเพื่อดูว่าเป็นจริงหรือเท็จ
- value_if_true - (required) ค่าที่จะแสดงขึ้นถ้า logical_test เป็น true
- value_if_false - (optional) ค่าที่จะแสดงขึ้นถ้า logical_test เป็น false
ตัวอย่างสูตร MEDIAN IF Array ของ Excel
ตามที่กล่าวไว้การค้นหาตัวอย่างจะเป็นการประกวดราคาสำหรับโครงการที่แตกต่างกันสองโครงการเพื่อค้นหาการประมูลกลางหรือมัธยฐาน อาร์กิวเมนต์สำหรับฟังก์ชัน IF ทำได้โดยการกำหนดเงื่อนไขและผลลัพธ์ต่อไปนี้:
- การทดสอบลอจิคัลพยายามค้นหาการจับคู่ชื่อโครงการที่พิมพ์ลงในเซลล์ D10 ของ แผ่นงาน
- อาร์กิวเมนต์ value_if_true จะด้วยความช่วยเหลือของฟังก์ชัน MEDIAN ซึ่งตรงกลางสำหรับโครงการที่เลือก
- อาร์กิวเมนต์ value_if_false จะถูกละเว้นเนื่องจากไม่จำเป็นและการขาดจะย่อสูตร ถ้าชื่อโครงการที่ไม่อยู่ในตารางข้อมูลเช่นโครงการ C ถูกพิมพ์ลงในเซลล์ D10 สูตรจะคืนค่าศูนย์ (0)
การป้อนข้อมูลบทแนะนำ
- ป้อนข้อมูลต่อไปนี้ลงในเซลล์ D1 ถึง E9 ตามที่เห็นในภาพด้านบน: โครงการ Tenders Project Tender Project A $ 15,785 โครงการ A $ 15,365 โครงการ A $ 16,472 โครงการ B $ 24,365 โครงการ B $ 24,612 โครงการ B $ 23,999 โครงการซื้อกลาง
- ในเซลล์ D10 พิมพ์ "Project A" (ไม่มีเครื่องหมายคำพูด) สูตรจะดูในเซลล์นี้เพื่อหาว่าจะให้ตรงกับโครงการใด
ป้อน MEDIAN IF สูตรที่ซ้อนกัน
เนื่องจากเรากำลังสร้างทั้งสูตรที่ซ้อนกันและสูตรอาร์เรย์เราจำเป็นต้องพิมพ์สูตรทั้งหมดลงใน เซลล์ แผ่นเดียว
เมื่อคุณป้อนสูตรแล้ว อย่า กดปุ่ม Enter บนแป้นพิมพ์หรือคลิกที่เซลล์อื่นด้วยเมาส์เมื่อต้องการเปลี่ยนสูตรเป็นสูตรอาร์เรย์
- คลิกที่เซลล์ E10 - ตำแหน่งที่จะแสดงผลลัพธ์ของสูตร
- พิมพ์ข้อมูลต่อไปนี้:
= MEDIAN (IF (D3: D8 = D10, E3: E8))
การสร้างสูตรอาร์เรย์
- กด แป้น Ctrl และ Shift ค้าง ไว้บนแป้นพิมพ์
- กดปุ่ม Enter บนแป้นพิมพ์เพื่อสร้างสูตรอาร์เรย์
- คำตอบ 15875 ($ 15,875 ที่มีการจัดรูปแบบ) ควรปรากฏในเซลล์ E10 เนื่องจากนี่เป็นการประมูลกลางสำหรับโครงการ A
- สูตรอาร์เรย์แบบสมบูรณ์
{= MEDIAN (IF (D3: D8 = D10, E3: E8))}
สามารถดูได้จาก แถบสูตร ด้านบนแผ่นงาน
ทดสอบสูตร
ทดสอบสูตรโดยการหาซื้อกลางสำหรับโครงการ B
พิมพ์ โครงการ B ลงในเซลล์ D10 แล้วกดปุ่ม Enter บนแป้นพิมพ์
สูตรควรคืนค่า 24365 (24,365 ดอลลาร์) ในเซลล์ E10