วิธีจัดการกับความว่างในตาราง Excel

ความว่างที่แท้จริงคืออะไร

ทุกครั้งที่เปิดแฟ้มใหม่ที่ยังไม่ได้นำมาใช้งานขึ้นมา คุณจะเห็นพื้นที่ตารางว่างๆหรือเซลล์ว่างซึ่งยังไม่มีค่าใดๆบันทึกไว้ ตารางแบบนี้คือตารางที่ว่างอย่างแท้จริง (ภาษาอังกฤษใช้คำว่า Blank) หรือหากเดิมได้ผ่านการบันทึกค่าใดๆลงไปในเซลล์อยู่ก่อนแล้ว พอคลิกขวาลงไปในเซลล์แล้วสั่ง Clear Contents จะลบข้อมูลภายในเซลล์ทิ้งไปคืนสภาพกลับมาเป็นเซลล์ว่างอย่างแท้จริงอีกครั้งหนึ่ง

• หากเซลล์ B3 เป็นเซลล์ว่างอย่างแท้จริง สูตร =IsBlank(B3) ในเซลล์ C3 ย่อมคืนค่าเป็น TRUE ส่วนเซลล์ B10 มีเลข 0 บันทึกไว้ สูตร =IsBlank(B10) ในเซลล์ C10 จะคืนค่าเป็น False เพราะเลข 0 ที่บันทึกลงไปทำให้ไม่ใช่เซลล์ว่างอีกต่อไป
• เซลล์ว่าง B3 ไม่ได้ถือว่าเป็นตัวเลขหรือตัวอักษร ดังนั้นสูตร =IsNumber และ =IsText ในเซลล์ C4 และ C5 ตามลำดับ ต่างคืนค่าเป็น FALSE
• เซลล์ว่าง B3 ไม่มีค่าใดบันทึกไว้ ดังนั้นเมื่อใช้สูตร =Len ในเซลล์ C6 เพื่อนับความยาวของอักขระ จึงได้ค่าความยาวเท่ากับ 0
• เซลล์ว่าง B3 ถือว่ามีค่าเท่ากับ 0 เห็นได้ชัดจากการสร้างสูตร =B3 ในเซลล์ C7 จะได้ค่าเป็นเลข 0
• หากเซลล์ B10 มีเลข 0 บันทึกไว้ จะพบว่าสูตร =B3=B10 ในเซลล์ C16 มีค่าเป็น TRUE เพราะเซลล์ว่างถือว่า "มีค่า" เท่ากับ 0
• แม้เซลล์ว่าง B3 จะถือว่ามีค่าเท่ากับ 0 แต่อย่างไรก็ตามเซลล์ว่าง B3 มิได้มีลักษณะอื่นเหมือนกับเซลล์ B10 ที่มีเลข 0 บันทึกไว้ โดยเห็นได้ชัดจากสูตร =Exact(B3,B10) ในเซลล์ B17 ซึ่งคืนค่าเป็น FALSE

หลักการจัดการกับเซลล์ว่างในตารางฐานข้อมูล

ลองดูตารางข้อมูลในรูปต่อไปนี้ว่า คุณจะตัดสินใจทำอย่างไรกับเซลล์ที่ไม่มีตัวเลข จะยอมให้เซลล์ปล่อยว่างไว้ หรือบันทึกตัวเลข 0 หรือพิมพ์คำว่า na ที่ย่อมาจากคำว่า Not Available ลงไปดี

ในพื้นที่ตาราง Amount ตั้งแต่เซลล์ D3:D7 มีเซลล์ D4 ที่เป็นเซลล์ว่างอยู่เพียงเซลล์เดียว ส่วนเซลล์ D6 มีค่าเป็นเลข 0 และเซลล์ D7 มีค่าเป็นตัวอักษร na โดยใช้หลักบันทึกค่าตามความเป็นจริงต่อไปนี้

• ถ้าเซลล์ใดไม่เคยมีค่ามาก่อน ให้เว้นว่างไว้ เช่น เซลล์ D4
• ถ้าเซลล์ใดมีค่าเท่ากับ 0 ก็ให้บันทึกเลข 0 ลงไป เช่น เซลล์ D6
• ถ้าเซลล์ใดเคยมีค่ามาก่อน แต่ตอนนี้ยังหาค่าไม่ได้ เช่น เซลล์ D7 ให้บันทึกคำว่า na ซึ่งหมายความว่า Not Available เอาไว้ก่อน

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

1. จำนวนรายการทั้งหมด 5 รายการ หาได้จากสูตร =Rows(Amount) โดยสูตรนี้จะนับจำนวน row ให้ไม่ว่าจะมีข้อมูลบันทึกไว้หรือไม่
2. จำนวนรายการที่มีข้อมูลบันทึกไว้มีทั้งหมด 4 รายการ หาได้จากสูตร =CountA(Amount) ซึ่งจะนับจำนวนเซลล์ที่มีเลข 0 และตัวอักษร เช่น na แต่ไม่นับเซลล์ที่เว้นว่าง
3. จำนวนรายการที่ทราบตัวเลขมีทั้งหมด 3 รายการ หาได้จากสูตร =Count(Amount) ซึ่งสูตรนี้จะนับเซลล์ที่มีเลข 0 รวมไว้ด้วย

ถ้านำตัวเลขจำนวนรายการทั้งหมด 5 รายการ(ข้อ 1) มาลบด้วยจำนวนรายการที่มีข้อมูลบันทึกไว้ 4 รายการ(ข้อ 2) เพื่อหาจำนวนที่แตกต่าง จะพบว่าต่างกันอยู่ 1 รายการ ซึ่งก็คือจำนวนเซลล์ที่เว้นว่างไว้ 1 เซลล์นั่นเอง ตรงกับตัวเลขที่ได้จากสูตร =CountBlank(Amount)

หากอยากทราบจำนวนรายการที่เคยมีมาก่อนเพียงแต่ตอนนี้ยังหาข้อมูลมาบันทึกไว้ไม่ได้(na) ให้นำจำนวนรายการที่มีข้อมูลบันทึกไว้ 4 รายการ(ข้อ 2) มาหาความแตกต่างกับจำนวนรายการที่ทราบตัวเลข 3 รายการ(ข้อ 3) จะพบว่ายังมีรายการที่ยังหาค่าไม่พบอยู่ 1 รายการ

เมื่อต้องการหายอดรวม ก็สามารถใช้สูตร =Sum(Amount) เพราะสูตร Sum จะละเลยไม่นำค่าที่เป็นตัวอักษรมาคำนวณ

ข้อสำคัญคือเมื่อถามว่าสินค้ามียอดต่ำสุดเป็นเท่าใด ก็ตอบได้โดยใช้สูตร =Min(Amount) เพราะจากตัวอย่างนี้ในเซลล์ D6 มีตัวเลข 0 บันทึกไว้ จึงทำให้สูตร Min คืนค่า 0 ซึ่งเป็นตัวเลขที่ต่ำสุดมาให้ แต่ถ้าไม่มีเซลล์ใดที่บันทึกเลข 0 เอาไว้เลย จะทำให้สูตร Min คืนค่าเป็นเลข 10  ดังนั้นจึงเป็นข้อควรระวังในการบันทึกข้อมูลว่า หากไม่จำเป็นแล้ว อย่าพิมพ์เลข 0 ลงไปเล่นๆในตารางอย่างเด็ดขาด เพราะจะทำให้สูตร Min คืนค่า 0 ออกมาเสมอ ถ้ายอมเว้นว่างไว้ จะทำให้สูตร Min หายอดต่ำสุดที่ไม่เท่ากับ 0 แต่ถ้ายึดหลักบันทึกตามความเป็นจริงว่า สินค้านั้นจำหน่ายหมดแล้ว ก็ต้องบันทึกเลข 0 ลงไป เพื่อทำให้สูตร Min คืนค่าตามความเป็นจริง

ในกรณีที่ใช้สูตร link ค่าจากเซลล์ที่เว้นว่างในตารางฐานข้อมูลไปใช้คำนวณต่อ ย่อมพบว่าเซลล์ที่เว้นว่างไว้นั้นส่งค่า 0 ไปยังเซลล์ปลายทางเสมอ ทำให้ไม่สามารถใช้สูตร Min เพื่อหาตัวเลขที่น้อยที่สุดแต่ไม่เท่ากับ 0 ได้ง่ายเช่นเดิม ทำให้ต้องสร้างสูตร Min-IF Array ขึ้นมาใช้แทน

{=Min(IF(Amount>0,Amount))}

ในการสูตรนี้ต้องกดปุ่ม Ctrl+Shift+Enter แทนการกด Enter เพื่อทำให้เกิดวงเล็บปีกกาปิดหัวท้ายสูตร Array

วิธีค้นหาเซลล์ว่างและทำให้ไม่ว่าง

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

ผมเชื่อว่าผู้ที่รู้จัก Excel อย่างผิวเผินหรือผู้ที่เป็นหัวหน้ามักชอบตารางด้านซ้ายมากกว่า เพราะแค่มองดูก็รู้ว่ามีสินค้าอยู่ 2 ชนิด คือ รหัส a001 และ a002 และมีอยู่ชนิดละ 3 รายการและ 2 รายการตามลำดับ เพราะเซลล์ที่เว้นว่างไว้นั้นช่วยให้คุณไม่ต้องเสียเวลาอ่านชื่อรหัสให้ครบทุกเซลล์ แต่หากถามว่า Excel จะรับรู้ว่ามีสินค้าอยู่กี่ชนิดและแต่ละชนิดมีกี่รายการ จะได้คำตอบไม่ตรงกับความเข้าใจเพราะ Excel จะไม่รับรู้ด้วยเลยว่าเซลล์ที่เว้นว่างไว้นั้นเป็นรายการของสินค้าใด

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

1. เลือกเซลล์ B3:C7 ซึ่งเป็นพื้นที่ตารางส่วนที่อยากจะดึงข้อมูลไปเติมเต็มเซลล์ที่เว้นว่าง
2. กดปุ่ม F5 > Special แล้วกาช่อง Blanks เพื่อเลือกเฉพาะเซลล์ที่เว้นว่างไว้เท่านั้น
3. พิมพ์เครื่องหมาย = แล้วคลิกเซลล์ B3 ซึ่งอยู่เหนือขึ้นไปติดกับเซลล์ที่เว้นว่างเซลล์แรก เพื่อทำให้เกิดสูตร =B3 เพื่อส่งค่าเซลล์ด้านบนลงไปเซลล์ด้านล่างที่ติดกันอยู่
4. กดปุ่ม Ctrl+Enter เพื่อบันทึกซ้ำสูตรลงไปในทุกเซลล์ที่เว้นว่างพร้อมกันทีเดียว
5. เลือกเซลล์ B3:C7 แล้วสั่ง Copy แล้ว Paste ซ้ำแบบ Value ทับลงไปในตารางเดิมเพื่อทำลายสูตรที่ได้จากขั้นตอนที่ผ่านมาให้กลายเป็นข้อมูลที่เป็นค่าคงที่แทนทั้งหมด

เมื่อปรับตารางด้านซ้ายให้มีข้อมูลเติมเต็มทุกเซลล์ว่างตามตารางด้านขวาแล้ว จะช่วยให้ใช้สูตร SumIF และ CountIF หรือสูตร Array อื่นเพื่อหาค่าที่ต้องการได้ทันที

หากยังต้องการปรับตารางด้านขวาให้ดูเหมือนกับตารางด้านซ้ายอีก ให้ใช้คำสั่ง Conditional Formatting แบบใช้สูตร =F3=F2 เพื่อปรับค่าซ้ำให้มีรูปแบบที่ตัวข้อมูลใช้สีกลืนกับสีพื้นของเซลล์แล้วจะดูเหมือนว่าค่าในเซลล์หายไป

ความว่างปลอมที่สุดสวยแต่แฝงอันตรายอันร้ายกาจ

นอกเหนือจากการใช้ Conditional Formatting เพื่อทำให้ดูเหมือนเป็นเซลล์ว่างแล้ว ยังมีวิธีอื่นทำให้เซลล์ที่เดิมมีข้อมูลอยู่กลายเป็นเซลล์ที่ดูเหมือนเป็นเซลล์ที่ไม่มีข้อมูลหรือเซลล์ว่างได้ด้วย ซึ่งโดยทั่วไปเราจะทำให้เซลล์ที่คืนค่าเป็น error กลายเป็นเลข 0 หรือทำให้เซลล์ที่มีค่าเป็นเลข 0 แสดงเป็นเซลล์ว่างแทน ทั้งนี้เพื่อทำให้ตารางข้อมูลดูสวยขึ้น แทนที่จะมองตารางแล้วเห็นแต่เลข 0 รกรุงรังไปหมดแทบทุกเซลล์หรือเห็น error ที่ทำให้สงสัยว่าคำนวณผิด จะได้เห็นเฉพาะแต่เซลล์ที่คุณเลือกให้เห็นตัวเลขหรือค่าผลลัพธ์เท่านั้น

• หากเซลล์มีค่าเป็น error แล้วต้องการทำให้เป็นเซลล์ว่าง ขอแนะนำให้ใช้สูตรปรับค่า error ให้เป็นเลข 0 ก่อน โดยใช้สูตร =IF(IsError(Cell), 0, Cell) จากนั้นจึงใช้ Format ปรับรูปแบบให้เลข 0 หายไป
• เว้นแต่ว่าเซลล์สูตรใดๆที่ต้องการให้คืนค่าเป็นตัวอักษร แล้วอาจคืนเป็น error จากผลของการหาค่าไม่พบ แนะนำให้ใช้สูตร
  =IF(IsError(Cell), 0, Cell) เพื่อเปลี่ยนค่าเป็น 0 แทน หรือ
  =IF(IsError(Cell), "", Cell) เพื่อทำให้ดูเหมือนมีค่าเป็นช่องว่างแทน หรือ
  =IF(IsError(Cell), "หาค่าไม่พบ", Cell) เพื่อแสดงคำว่าหาค่าไม่พบแทน
• หากเซลล์มีค่าเท่ากับ 0 แล้วต้องการทำให้ดูเหมือนเซลล์ว่าง ให้เลือกใช้ Format แบบ 0;-0; หรือ #,##0.00;(#,##0.00); ซึ่งรูปแบบตัวเลขดังกล่าวนี้จะแสดงเฉพาะตัวเลขบวกหรือลบเท่านั้น ขอให้สังเกตว่าข้างหลังเครื่องหมาย ; ตัวที่สองไม่ได้กำหนดรูปแบบของค่า 0 เอาไว้

ข้อผิดพลาดที่ผู้ใช้ Excel แบบมักง่ายชอบใช้กันก็คือ เลือกใช้สูตรที่ทำให้เซลล์ที่ตามปกติจะคืนค่าเป็นตัวเลข กลายเป็นเซลล์ที่ดูเหมือนว่างโดยใช้เครื่องหมาย "" แทนลงไปเสมอ โดยใช้สูตร(ที่ไม่ควรใช้)ต่อไปนี้

=IF(IsError(Cell), "", Cell)
=IF(Cell=0, "", Cell)

ค่า "" (เรียกว่า Null Text หรือ Text ที่ไม่มีตัวตน) แม้จะลัดขั้นตอนช่วยให้ไม่ต้องใช้ Format ปรับรูปแบบเพื่อซ่อนเลข 0 ก็ตาม แต่การใช้ Null Text จะเป็นตัวปัญหาที่ทำให้ทุกเซลล์ที่รับค่าต่อเนื่องกันไปจำเป็นต้องใช้สูตรตรวจสอบค่า Null Text ต่อกันไปอีก เช่นสมมติว่าใช้สูตร =IF(Cell= "", "", Cell+500) เพื่อตรวจสอบค่าจากเซลล์ต้นทางก่อนว่ามีค่าเป็น Null Text หรือไม่ หากเป็น Null Text ก็ให้เป็น Null Text ต่อไป แต่ถ้าไม่ใช่ Null Text จึงจะยอมให้นำค่าจากเซลล์ต้นทางไปบวกเพิ่มอีก 500

หากตารางคำนวณมีขนาดใหญ่มีเซลล์สูตรนับพันนับหมื่นเซลล์แล้วทุกเซลล์ต้องใช้สูตร =IF(Cell= "", "", ... เช่นนี้ซ้ำกันไปอีก นอกเหนือจากจะทำให้แฟ้มมีขนาดใหญ่ขึ้นเพราะมีสูตรยาวขึ้นโดยไม่จำเป็นแล้วยังทำให้แฟ้มคำนวณช้าลงเพราะต้องเสียเวลาตรวจสอบค่าผ่านสูตร IF อีกทุกเซลล์

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

ทางเลือกของการใช้สูตรหรือรูปแบบเพื่อทำให้เป็นเซลล์ว่าง

เซลล์ว่างที่แท้จริง (Blank Cell) มีโอกาสเป็นไปได้เฉพาะในตารางฐานข้อมูลที่เกิดจากการบันทึกค่าลงไปโดยตรงในตาราง หรือเป็นเซลล์ที่ผ่านการสั่ง Clear All หรือเกิดจากข้อมูลที่ส่งจากโปรแกรมฐานข้อมูลอื่นมาเป็นแฟ้มนามสกุล Excel เท่านั้น แต่เมื่อใดที่เป็นเซลล์ว่างที่เกิดจากสูตรหรือใช้รูปแบบทำให้ดูเหมือนว่าง ย่อมไม่ใช่เซลล์ที่ว่างอย่างแท้จริง ดังนั้นก่อนที่จะนำค่าไปใช้ต่อจึงต้องคอยตรวจสอบให้ทราบอย่างแน่ชัดก่อนเสมอว่า เซลล์ว่างที่เห็นว่าว่างนั้น แท้จริงแล้วมีค่าเป็นเท่าใด

การเลือกใช้คำสั่ง Format หรือ Conditional Formatting เพื่อทำให้ดูเป็นเซลล์ว่าง จะสามารถใช้กับเซลล์ที่มีค่าเป็นค่าใดๆก็ได้ โดยโปรดจำไว้ว่าผลจากการใช้คำสั่ง Format หรือ Conditional Formatting นั้น มิได้ทำให้ค่าในเซลล์เปลี่ยนไปจากเดิมแต่อย่างใด เช่น

• Format ;;; จะซ่อนค่าทั้งตัวเลขบวก ลบ 0 และตัวอักษร
• Format [>20]0;;; จะแสดงตัวเลขเฉพาะที่มีค่ามากกว่า 20 เท่านั้น และไม่แสดงตัวอักษร
• Conditional Formatting ที่ใช้สูตร =IsError(Cell) จะเปลี่ยนสีและรูปแบบเซลล์ที่มีค่าเป็น error

ดังนั้นหากยังต้องการนำค่าไปใช้ในการคำนวณต่อ ก็ขอแนะนำให้ใช้สูตรในการปรับค่า แต่ถ้าเป็นผลลัพธ์สุดท้ายแล้วจะเลือกใช้คำสั่ง Format หรือ Conditional Formatting ก็ไม่น่าห่วงแต่อย่างใด