วิธีการนับเซลล์ที่ถูกกรองด้วยข้อความใน 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

---

เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด

Kutools สำหรับ Excel แก้ปัญหาส่วนใหญ่ของคุณและเพิ่มผลผลิตของคุณได้ถึง 80%

• ซุปเปอร์ฟอร์มูล่าบาร์ (แก้ไขข้อความและสูตรหลายบรรทัดได้อย่างง่ายดาย); การอ่านเค้าโครง (อ่านและแก้ไขเซลล์จำนวนมากได้อย่างง่ายดาย); วางลงในช่วงที่กรองแล้ว...
• ผสานเซลล์ / แถว / คอลัมน์ และการเก็บรักษาข้อมูล แยกเนื้อหาของเซลล์ รวมแถวที่ซ้ำกันและผลรวม / ค่าเฉลี่ย... ป้องกันเซลล์ซ้ำ; เปรียบเทียบช่วง...
• เลือกซ้ำหรือไม่ซ้ำ แถว; เลือกแถวว่าง (เซลล์ทั้งหมดว่างเปล่า); Super Find และ Fuzzy Find ในสมุดงานจำนวนมาก สุ่มเลือก ...
• สำเนาถูกต้อง หลายเซลล์โดยไม่เปลี่ยนการอ้างอิงสูตร สร้างการอ้างอิงอัตโนมัติ ถึงหลายแผ่น ใส่สัญลักษณ์แสดงหัวข้อย่อย, กล่องกาเครื่องหมายและอื่น ๆ ...
• แทรกสูตรที่ชื่นชอบและรวดเร็ว, ช่วงแผนภูมิและรูปภาพ; เข้ารหัสเซลล์ ด้วยรหัสผ่าน; สร้างรายชื่อผู้รับจดหมาย และส่งอีเมล ...
• แยกข้อความ, เพิ่มข้อความ, ลบตามตำแหน่ง, ลบ Space; สร้างและพิมพ์ผลรวมย่อยของเพจ แปลงระหว่างเนื้อหาของเซลล์และความคิดเห็น...
• ซุปเปอร์ฟิลเตอร์ (บันทึกและใช้โครงร่างตัวกรองกับแผ่นงานอื่น ๆ ); การเรียงลำดับขั้นสูง ตามเดือน / สัปดาห์ / วันความถี่และอื่น ๆ ตัวกรองพิเศษ โดยตัวหนาตัวเอียง ...
• รวมสมุดงานและแผ่นงาน; ผสานตารางตามคอลัมน์สำคัญ แยกข้อมูลออกเป็นหลายแผ่น; Batch แปลง xls, xlsx และ PDF...
• การจัดกลุ่มตาราง Pivot ตาม จำนวนสัปดาห์วันในสัปดาห์และอื่น ๆ ... แสดงปลดล็อกเซลล์ที่ถูกล็อก ด้วยสีที่ต่างกัน เน้นเซลล์ที่มีสูตร / ชื่อ...

อ่านเพิ่มเติม ... คุณสมบัติเต็มรูปแบบ ทดลองใช้งานฟรี 30 วัน... ซื้อ... 

---

แท็บ Office - นำอินเทอร์เฟซแบบแท็บมาสู่ Office และทำให้งานของคุณง่ายขึ้นมาก

• เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
• เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
• เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!

อ่านเพิ่มเติม ... คุณสมบัติเต็มรูปแบบ ทดลองใช้งานฟรี 30 วัน... ซื้อ...