เคล็ดการสร้างสัญญาณเตือนว่ากรอกค่าผิดประเภท ... ง่ายจนนึกไม่ถึง

😎

ใน Excel มีเครื่องมือใหม่ๆมากมายที่จะช่วยจัดการกับการบันทึกข้อมูล มีเยอะมากจนหลายคนเลือกใช้ไม่ถูก ไม่แน่ใจว่าเอาไว้ใช้เมื่อไหร่ เช่น

Data Validation ใช้ควบคุมการกรอกค่า
Conditional Formatting ใช้เปลี่ยนสีเซลล์เพื่อเตือน
Remove Duplicates ใช้ลบรายการที่ซ้ำทิ้ง

พอมาถึงวันนี้ที่กำลังเห่อ Power Query ก็อาจเสียเวลาเดินทางข้ามโลก ไปใช้เครื่องมือเพื่อ clean data ทั้งๆที่จุดหมายปลายทางอยู่ใกล้นิดเดียว

👉 ในกรณีที่จัดเตรียมตารางให้กรอกตัวเลขเอาไว้ แล้วอยากให้เตือนเมื่อมีการกรอกข้อความที่เป็นตัวอักษรลงไปแทนที่จะเป็นตัวเลข ใช้แค่สูตรบวกก็พอครับ

จากภาพนี้ต้องการหายอดรวมตัวเลขในตาราง B3:B12

ถ้าใช้สูตร =Sum(B3:B12) ก็ง่ายนิดเดียว หายอดรวมมาให้ทันที แม้มีข้อมูลเป็นตัวอักษรปนอยู่ก็ยังบวกมาให้

แต่ถ้าเลือกสร้างสูตรบวกที่ยาวหน่อย จับเซลล์แต่ละเซลล์มาบวกกันด้วยสูตร =B3+B4+B5+B6+B7+B8+B9+B10+B11+B12 จะพบว่าหาคำตอบไม่ได้ กลายเป็น #VALUE! เตือนขึ้นมาให้เห็นทันที

☝️ น่าสงสัยไหมว่า ทำไมสูตร Sum จึงไม่ error เพราะสูตร Sum ถูกสร้างขึ้นมาให้บวกตัวเลขเท่านั้น แล้วละเลยไม่นำค่าที่เป็นตัวอักษรมาคำนวณด้วยให้เสียเวลา

✋ อย่าตอบว่าเพราะตัวอักษรมีค่าเท่ากับ 0 นะครับ ตัวอักษรมีค่ามากกว่าเลขทั้งปวง ถ้าเอาไปจัดเรียงจากน้อยไปมาก จะพบว่าตัวอักษรอยู่ล่างสุด

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

สูตรนี้จะนับจำนวนเซลล์ที่เป็นตัวอักษรครับ

=SUMPRODUCT(--ISTEXT(B3:B12))

ISTEXT(B3:B12) ทำหน้าที่ตรวจสอบว่าในตาราง B3:B12 มีเซลล์ไหนที่มีค่าเป็น Text จะหาค่าออกมาเป็นอาเรย์ของ True กับ False

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

พอใส่ ลบลบ -- ลงไปข้างหน้าก็จะเปลี่ยน True ให้เป็นเลข 1 และเปลี่ยน False ให้เป็นเลข 0 กลายเป็นอาเรย์ {0;0;0;0;0;0;0;1;0;0}

จากนั้น SumProduct จึงบวกเลขทุกตัว ได้คำตอบเป็น 1 เท่ากับจำนวนเซลล์ที่เป็นตัวอักษร

หรือถ้าต้องการให้แสดงเป็นคำเตือน ให้ใช้สูตร IF ผสมลงไป
=IF(SUMPRODUCT(--ISTEXT(B3:B12))=0,"กรอกตัวเลขถูกต้อง","กรอกค่าผิดพลาด")

นอกจากการเตือนด้วยสูตรนี้แล้ว ถ้าต้องการเตือนในแต่ละเซลล์ก็สามารถใช้ Data Validation หรือเปลี่ยนสีเซลล์ด้วย Conditional Formatting ก็ได้ แต่ต้องขยับจอไปหาเซลล์นั้น ต่างจากการใช้สูตรเตือนที่มองที่เดียว

Download ตัวอย่างนี้ได้จาก
https://drive.google.com/file/d/1lJRTn_SGKkKB_tSvHUmPxxIOtlLILm5h/view?usp=sharing