วิธีการนับเซลล์ที่ถูกกรองด้วยข้อความใน Excel?

ใน Excel การนับเซลล์ที่มีข้อความทำได้ง่าย แต่เมื่อมันมาถึง นับเซลล์ที่กรองด้วยข้อความ, สิ่งที่ได้รับยุ่งยาก. บทแนะนำนี้จะแนะนำ สามทาง เพื่อนับเซลล์ที่มีข้อความจากรายการที่กรอง

นับเซลล์ที่กรองด้วยข้อความโดยใช้คอลัมน์ตัวช่วย

นับเซลล์ที่กรองด้วยข้อความโดยเชื่อมฟังก์ชัน SUMPRODUCT, SUBTOTAL, INDIRECT, ROW & ISTEXT

นับเซลล์ที่กรองด้วยข้อความโดยการรวมฟังก์ชัน SUMPRODUCT, SUBTOTAL, OFFSET, MIN, ROW & ISTEXT

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

1 โปรดคัดลอกสูตรด้านล่างลงในเซลล์ D2 จากนั้นกดปุ่ม Enter เพื่อให้ได้ผลลัพธ์แรก

=ยอดรวม (103, A2)

หมายเหตุ: คอลัมน์ตัวช่วยที่มีสูตร SUBTOTAL ใช้สำหรับทดสอบว่าแถวนั้นถูกกรองหรือไม่ และ 103 ย่อมาจาก นับ ฟังก์ชั่นใน function_num ข้อโต้แย้ง.

2 แล้วก็ ลากที่จับเติมลง กับเซลล์ที่คุณต้องการใช้สูตรนี้ด้วย

3. โปรดคัดลอกสูตรด้านล่างลงในเซลล์ F2จากนั้นกดปุ่ม เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์สุดท้าย

=COUNTIFS(A2:A18,"*", D2:D18, 1)

เราจะเห็นว่ามี 4 เซลล์ที่มีข้อความในข้อมูลที่กรอง

อีกวิธีหนึ่งในการนับเซลล์ที่กรองด้วยข้อความคือการใช้ การผสมผสาน ของ SUMPRODUCT, SUBTOTAL, ทางอ้อม, แถว & ISEXT ฟังก์ชั่น. กรุณาทำดังนี้

โปรดคัดลอกสูตรด้านล่างลงในเซลล์ E2จากนั้นกดปุ่ม เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18)))

คำอธิบายสูตร:

1. แถว(A2:A18) ส่งคืนหมายเลขแถวตามลำดับของช่วง A2:A18
2. ทางอ้อม("A"&ROW(A2:A18)) ส่งคืนการอ้างอิงเซลล์ที่ถูกต้องจากช่วงที่กำหนด
3. ผลรวมย่อย(103, ทางอ้อม("A"&ROW(A2:A18))) ทดสอบว่าแถวถูกกรองหรือไม่ และคืนค่า 1 สำหรับเซลล์ที่มองเห็นได้ 0 สำหรับเซลล์ที่ซ่อนอยู่และเซลล์ว่าง
4. ISTEXT(A2:A18) ตรวจสอบว่าแต่ละเซลล์ในช่วง A2:A18 มีข้อความหรือไม่ และคืนค่า True สำหรับเซลล์ที่มีข้อความ เท็จสำหรับเซลล์อื่นๆ ตัวดำเนินการเอกนารีคู่ (--) บังคับค่า TRUE และ FALSE ให้เป็นค่า 1 และ 0
5. SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18))) สามารถดูได้เป็น SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). จากนั้น SUMPRODUCT จะคูณสองอาร์เรย์เข้าด้วยกันและส่งกลับผลรวมของค่า ซึ่งก็คือ 4

วิธีที่สามในการนับเซลล์ที่มีข้อความจากข้อมูลที่กรองคือ ต่อกัน SUMPRODUCT, SUBTOTAL, OFFSET, นาที, แถว & ISEXT ฟังก์ชั่น. กรุณาทำดังนี้

โปรดคัดลอกสูตรด้านล่างลงในเซลล์ E2จากนั้นกดปุ่ม เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ISTEXT(A2:A18)) )

คำอธิบายสูตร:

1. OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1) ส่งกลับการอ้างอิงแต่ละเซลล์จากช่วง A2:A18
2. SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)) ตรวจสอบว่าแถวถูกกรองหรือไม่ และคืนค่า 1 สำหรับเซลล์ที่มองเห็นได้ 0 สำหรับเซลล์ที่ซ่อนอยู่และเซลล์ว่าง
3. ISTEXT(A2:A18) ตรวจสอบว่าแต่ละเซลล์ในช่วง A2:A18 มีข้อความหรือไม่ และคืนค่า True สำหรับเซลล์ที่มีข้อความ เท็จสำหรับเซลล์อื่นๆ ตัวดำเนินการเอกนารีคู่ (--) บังคับค่า TRUE และ FALSE ให้เป็นค่า 1 และ 0
4. SUMPRODUCT(ผลรวมย่อย(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ISTEXT(A2:A18))) สามารถดูได้เป็น SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). จากนั้น SUMPRODUCT จะคูณสองอาร์เรย์เข้าด้วยกันและส่งกลับผลรวมของค่า ซึ่งก็คือ 4