Excel

นับเซลล์ที่มี x หรือ y

Count Cells That Contain Either X

สูตร Excel: นับเซลล์ที่มี x หรือ yสูตรทั่วไป |_+_| สรุป

ในการนับเซลล์ที่มี x หรือ y คุณสามารถใช้สูตรโดยยึดตาม ฟังก์ชัน SUMPRODUCT . ในตัวอย่างที่แสดง สูตรในเซลล์ F5 คือ:



= SUMPRODUCT (--(( ISNUMBER ( FIND ('abc',rng)) +  ISNUMBER ( FIND ('def',rng)))>0))

นี่คือโซลูชันสูตรเซลล์เดียวที่อธิบายไว้ด้านล่าง นอกจากนี้ยังสามารถใช้สูตรที่ง่ายกว่าโดยยึดตาม a คอลัมน์ผู้ช่วย อธิบายไว้ด้านล่างด้วย

คำอธิบาย

เมื่อคุณนับเซลล์ด้วย 'ตรรกะ OR' คุณต้องระมัดระวังไม่ให้นับซ้ำ ตัวอย่างเช่น หากคุณกำลังนับเซลล์ที่มี 'abc' หรือ 'def' คุณจะไม่สามารถรวมฟังก์ชัน COUNTIF สองฟังก์ชันเข้าด้วยกันได้ เนื่องจากคุณอาจนับเซลล์ที่มีทั้ง 'abc' และ 'def' ซ้ำได้





สารละลายเซลล์เดียว

สูตรเดียวก็ใช้ SUMPRODUCT กับ ISNUMBER + หา . สูตรใน F5 คือ:

 
= SUMPRODUCT (--(( ISNUMBER ( FIND ('abc',B5:B11))+ ISNUMBER ( FIND ('def',B5:B11)))>0))

สูตรนี้ขึ้นอยู่กับสูตร อธิบายไว้ที่นี่ ที่ค้นหาข้อความภายในเซลล์:



 
= SUMPRODUCT (--(( ISNUMBER ( FIND ('abc',B5:B11)) +  ISNUMBER ( FIND ('def',B5:B11)))>0))

เมื่อกำหนดช่วงของเซลล์ ตัวอย่างนี้จะส่งคืน an อาร์เรย์ ของค่า TRUE/FALSE หนึ่งค่าสำหรับแต่ละเซลล์ในช่วง เนื่องจากเราใช้สิ่งนี้สองครั้ง (หนึ่งครั้งสำหรับ 'abc' และอีกครั้งสำหรับ 'def') เราจะได้รับ สอง อาร์เรย์

ต่อไป เราเพิ่มอาร์เรย์เหล่านี้เข้าด้วยกัน (ด้วย +) ซึ่งจะสร้างอาร์เรย์ตัวเลขใหม่ขึ้นมาใหม่ ตัวเลขแต่ละตัวในอาร์เรย์นี้เป็นผลมาจากการเพิ่มค่า TRUE และ FALSE ในสองอาร์เรย์ดั้งเดิมเข้าด้วยกัน ในตัวอย่างที่แสดง อาร์เรย์ที่ได้จะมีลักษณะดังนี้:

 
 ISNUMBER ( FIND ('abc',B5:B11)

เราจำเป็นต้องบวกตัวเลขเหล่านี้เข้าด้วยกัน แต่เราไม่ต้องการนับซ้ำ ดังนั้นเราจึงต้องตรวจสอบให้แน่ใจว่าค่าใดๆ ที่มากกว่าศูนย์ถูกนับเพียงครั้งเดียว ในการทำเช่นนั้น เราบังคับให้ค่าทั้งหมดเป็น TRUE หรือ FALSE ด้วย '>0' จากนั้นบังคับเป็น 1/0 ด้วย ลบสองครั้ง (-).

สุดท้าย SUMPRODUCT จะคืนค่าผลรวมของค่าทั้งหมดในอาร์เรย์

โซลูชันคอลัมน์ผู้ช่วย

ด้วยคอลัมน์ตัวช่วยเพื่อตรวจสอบแต่ละเซลล์ทีละเซลล์ ปัญหาจึงซับซ้อนน้อยลง เราสามารถใช้ COUNTIF โดยมีค่าสองค่า (ระบุเป็น 'ค่าคงที่อาร์เรย์') สูตรใน C5 คือ:

 
{2020102}

ตรวจสอบว่าเซลล์มี x หรือ y ด้วย COUNTIF

วิธีเพิ่ม 1 เดือนในวันที่ใน excel

COUNTIF จะส่งคืนอาร์เรย์ที่มีสองรายการ: การนับสำหรับ 'abc' และการนับสำหรับ 'def' เพื่อป้องกันการนับซ้ำ เราเพิ่มรายการแล้วบังคับผลลัพธ์เป็น TRUE/FALSE ด้วย '>0' สุดท้าย เราแปลงค่า TRUE/FALSE เป็น 1 และ 0 ด้วยค่าลบคู่ (--)

ผลลัพธ์สุดท้ายคือ 1 หรือ 0 สำหรับแต่ละเซลล์ เพื่อให้ได้ยอดรวมสำหรับ ทั้งหมด เซลล์ในช่วง ให้รวมคอลัมน์ตัวช่วย

ผู้เขียน Dave Bruns


^