ค้นหาเซลล์ว่างด้วย COUNTBLANK

บางครั้งคำถามที่มีประโยชน์อาจไม่ใช่ "มีเซลล์ที่กรอกข้อมูลแล้วกี่เซลล์" แต่เป็น "มีเซลล์ที่ ว่างอยู่กี่เซลล์"

คุณอาจต้องการทราบว่ามีคำถามในแบบสำรวจที่ถูกเว้นไว้กี่ข้อ หรือมีกี่วันที่ยังไม่มีการบันทึกยอดขาย

ฟังก์ชัน COUNTBLANK ตอบคำถามนี้โดยตรง โดยนับเซลล์ว่างในช่วง มาดูกันว่าฟังก์ชันนี้ทำงานอย่างไร

COUNTBLANK จะคืนค่าจำนวนเซลล์ว่างในช่วงเซลล์เดียว

รูปแบบการใช้งานคือ:

• =COUNTBLANK(range)

ต่างจาก COUNT และ COUNTA ตรงที่ COUNTBLANK รับช่วงเซลล์เพียง หนึ่งช่วง เท่านั้น โดยจะตรวจดูภายในช่วงนั้นและนับเซลล์ที่ไม่มีข้อมูล

สมมติว่า A1:A5 มีค่า 120, (ว่าง), 200, (ว่าง), 80

สูตร =COUNTBLANK(A1:A5) จะคืนค่า 2 เพราะมีเซลล์ว่างสองเซลล์

สังเกตรูปแบบ: COUNT ในที่นี้คืนค่า 3 (ตัวเลข) และ COUNTBLANK คืนค่า 2 (ช่องว่าง) รวมกันเป็นเซลล์ทั้ง 5 เซลล์ในช่วง

นี่คือข้อแตกต่างสำคัญเมื่อเทียบกับ COUNTA เซลล์ที่มีสตริงข้อความว่าง เช่น ผลลัพธ์จาก =IF(B1>0, B1, "") จะดูเหมือนว่าง

COUNTA จะถือว่าเซลล์นั้นมีข้อมูล แต่ COUNTBLANK จะถือว่าเป็น เซลล์ว่าง และนับเซลล์นั้น

ดังนั้น COUNTBLANK จึงตรงกับสิ่งที่คุณมองเห็น: หากเซลล์ดูเหมือนว่าง เซลล์นั้นจะถูกนับ

โปรดระวังเซลล์ที่มีเพียง อักขระเว้นวรรค ที่พิมพ์ด้วยตนเอง อักขระเว้นวรรคเป็นอักขระจริง ดังนั้นเซลล์จึงไม่ว่าง

COUNTBLANK จะ ไม่นับเซลล์ที่มีอักขระเว้นวรรค แม้เซลล์นั้นจะดูเหมือนว่าง

หากจำนวนเซลล์ว่างดูน้อยกว่าที่ควร อาจมีอักขระเว้นวรรคที่ซ่อนอยู่ การลบอักขระเหล่านั้นด้วย TRIM อาจช่วยได้ ซึ่งคุณจะได้เรียนรู้ในหลักสูตรภายหลัง

คุณส่งแบบสำรวจ และคอลัมน์ C เก็บคำตอบของคำถามหนึ่งข้อ ตั้งแต่ C2 ถึง C101 (ผู้ตอบ 100 คน)

หากต้องการทราบว่ามีกี่คนที่ไม่ได้ตอบคำถามนั้น ให้ใช้:

• =COUNTBLANK(C2:C101)

ผลลัพธ์คือจำนวนคำตอบที่ขาดหายไป ซึ่งเป็นตัวชี้วัดทันทีว่าข้อมูลของคุณครบถ้วนเพียงใด

COUNTBLANK และ COUNTA ทำงานร่วมกันได้เป็นอย่างดี ฟังก์ชันหนึ่งนับช่องว่าง ส่วนอีกฟังก์ชันนับเซลล์ที่มีข้อมูล

สำหรับช่วงแบบสำรวจ C2:C101 คุณสามารถอธิบายความครบถ้วนของข้อมูลด้วยสองสูตร:

• =COUNTA(C2:C101) สำหรับคำตอบที่ได้รับ
• =COUNTBLANK(C2:C101) สำหรับคำตอบที่เว้นไว้

เมื่อนำมารวมกัน ทั้งสองค่าจะเท่ากับจำนวนเซลล์ทั้งหมดในช่วงเสมอ

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

สำหรับ C2:C101 (100 เซลล์):

• =(100 - COUNTBLANK(C2:C101)) / 100

จัดรูปแบบผลลัพธ์เป็นเปอร์เซ็นต์ แล้วคุณจะได้อัตราการกรอกข้อมูลที่อัปเดตตามจริงเมื่อมีคำตอบเข้ามา

โปรดจำข้อจำกัดสำคัญ: COUNTBLANK รับช่วงเซลล์ ช่วงเดียว ไม่ใช่รายการของหลายช่วง

การเขียน =COUNTBLANK(A1:A10, C1:C10) จะไม่ทำงานแบบเดียวกับที่ COUNT หรือ COUNTA ทำได้

หากต้องการนับเซลล์ว่างในสองพื้นที่ที่แยกจากกัน ให้เรียกใช้ COUNTBLANK กับแต่ละพื้นที่แล้วนำผลลัพธ์มาบวกกัน: =COUNTBLANK(A1:A10) + COUNTBLANK(C1:C10)

COUNTBLANK ทำงานเหมือนกันทั้งใน Microsoft Excel และ Google Sheets สูตรเดียวกันจะให้คำตอบเดียวกันในทั้งสองเครื่องมือ

ดังนั้นไม่ว่าคุณจะติดตามคำตอบแบบสำรวจที่ขาดหายใน Excel หรือใน Sheets =COUNTBLANK(range) ก็จะบอกจำนวนเซลล์ที่ยังว่างได้อย่างน่าเชื่อถือ

COUNTBLANK สามารถตรวจสอบบล็อกเซลล์รูปสี่เหลี่ยมได้ ไม่ใช่แค่คอลัมน์เดียว จึงเหมาะสำหรับค้นหาข้อมูลที่หายไปทั่วทั้งตารางในครั้งเดียว

สำหรับตารางข้อมูลใน A2:E50 สูตร =COUNTBLANK(A2:E50) จะนับเซลล์ว่างทุกเซลล์ในห้าคอลัมน์และหลายแถวรวมกัน

จำนวนที่สูงเป็นสัญญาณเตือนอย่างรวดเร็วว่าตารางของคุณมีช่องว่างที่ควรเติมก่อนวิเคราะห์ข้อมูล

ทดสอบความเข้าใจเกี่ยวกับ COUNTBLANK

ตอนนี้คุณทราบวิธีวัดจำนวนเซลล์ว่างในข้อมูลแล้ว ประเด็นสำคัญมีดังนี้:

• =COUNTBLANK(range) นับเซลล์ที่ว่างในช่วงเดียว
• ถือว่าสตริงว่าง ("") จากสูตรเป็นเซลล์ว่าง
• เซลล์ที่มีอักขระเว้นวรรคจะไม่ใช่เซลล์ว่าง จึงไม่ถูกนับ
• รับช่วงเซลล์เดียว หากมีหลายพื้นที่ให้เรียกใช้ฟังก์ชันแยกกัน
• ใช้ร่วมกับ COUNTA เพื่อติดตามความครบถ้วนของข้อมูลหรืออัตราการกรอกข้อมูลครบถ้วน

ถัดไป เราจะค้นหาค่าสูงสุดและต่ำสุดด้วย MAX และ MIN