COUNTIFS พร้อมคอลัมน์ตารางตัวแปร

ในการใช้ COUNTIFS กับคอลัมน์ตารางตัวแปร คุณสามารถใช้ INDEX และ MATCH เพื่อค้นหาและเรียกข้อมูลคอลัมน์สำหรับ COUNTIFS ในตัวอย่างที่แสดง สูตรใน H5 คือ:

= COUNTIFS ( INDEX (Table,0, MATCH (name,Table[#Headers],0)),criteria))

อันดับแรก สำหรับบริบท สิ่งสำคัญที่ควรทราบคือคุณสามารถใช้ COUNTIFS กับการอ้างอิงที่มีโครงสร้างปกติดังนี้:

 
= COUNTIFS ( INDEX (Table1,0, MATCH (G5,Table1[#Headers],0)),'x')

นี่เป็นสูตรที่ง่ายกว่ามาก แต่คุณไม่สามารถคัดลอกลงในคอลัมน์ H ได้ เนื่องจากการอ้างอิงคอลัมน์จะไม่เปลี่ยนแปลง

ตัวอย่างในหน้านี้จึงมีขึ้นเพื่อแสดงวิธีหนึ่งในการตั้งค่าสูตรที่อ้างอิงตารางที่มีการอ้างอิงคอลัมน์ตัวแปร

ฟังก์ชัน MATCH ทำงานจากภายในสู่ภายนอกเพื่อค้นหาตำแหน่งของชื่อคอลัมน์ที่แสดงอยู่ในคอลัมน์ G:

วิธีจับคู่ดัชนี

 
= COUNTIFS (Table1[Swim],'x')

MATCH ใช้ค่าใน G5 เป็นค่าการค้นหา ส่วนหัวใน Table1 สำหรับอาร์เรย์ และ 0 สำหรับประเภทการจับคู่เพื่อบังคับให้มีการจับคู่แบบตรงทั้งหมด ผลลัพธ์สำหรับ G5 คือ 2 ซึ่งเข้าสู่ INDEX เป็นหมายเลขคอลัมน์:

 
 MATCH (G5,Table1[#Headers],0)

มีการตั้งค่าหมายเลขแถวประกาศเป็นศูนย์ ซึ่งทำให้ INDEX ส่งคืนทั้งคอลัมน์ ซึ่งก็คือ C5:C13 ในตัวอย่างนี้

วิธีทำสูตรการลบใน excel

การอ้างอิงนี้จะเข้าสู่ COUNTIFS ตามปกติ:

 
 INDEX (Table1,0,2,0))

COUNTIFS นับเซลล์ที่มี 'x' และส่งคืนผลลัพธ์ 5 ในกรณีนี้

เมื่อคัดลอกสูตรลงในคอลัมน์ H แล้ว INDEX และ MATCH จะคืนค่าการอ้างอิงคอลัมน์ที่ถูกต้องไปที่ COUNTIFS ในแต่ละแถว

ทางเลือกด้วย INDIRECT

ฟังก์ชัน INDIRECT ยังสามารถใช้เพื่อตั้งค่าการอ้างอิงคอลัมน์ตัวแปรได้ดังนี้:

 
= COUNTIFS (C5:C13,'x')

ในที่นี้ การอ้างอิงแบบมีโครงสร้างจะประกอบเป็นข้อความ และ INDIRECT จะประเมินข้อความว่าเป็นการอ้างอิงเซลล์ที่เหมาะสม

หมายเหตุ: ทางอ้อมคือ a ฟังก์ชันระเหย และอาจทำให้เกิดปัญหาด้านประสิทธิภาพในเวิร์กบุ๊กขนาดใหญ่หรือซับซ้อนกว่าได้