[137] การแปลงตัวเลขที่ติดลบ แต่เครื่องหมายลบอยู่ด้านหลัง ครั้งละมาก ๆ ใน Excel (Converting mass Numbers with Minus Sign on Right to Minus on Left)

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

เมื่อเลือกข้อมูลผลที่ได้คือ Sum:0 ที่ควรจะเป็นคือ Sum:-86391.98

จากที่ลองหาวิธีแก้ไขในกรณีนี้ (ด้วย google) พบว่าส่วนใหญ่จะใช้อยู่ 2 วิธีคือ

1. ใช้สูตรช่วยในการแก้ไขข้อมูล
2. เขียน VBA macro

(อ้างอิงวิธีการแก้ไขจาก Microsoft: //support.microsoft.com/kb/42964)

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

วิธีที่ใช้มีสองวิธี วิธีแรกคือบันทึกเป็น Text แล้วเปิดไฟล์ Text โปรแกรม Excel จะช่วยแปลง 123- เป็นค่า -123 ในตอนที่นำเข้าข้อมูลให้อัตโนมัติ ลองแล้วเยี่ยมมาก ๆ 

ในกรณีที่ข้อมูลไม่มาก ไม่อยากใช้สูตร หรือ VBA ก็ใช้คำสั่ง Text to columns คำสั่งเดียวจบครับ

ลำดับวิธีการแปลงข้อมูลทำดังนี้

1. ทำการ Save As เป็น Text file เป็นชื่อใหม่
2. ทำการเปิด Text file ที่ Save ด้วย Excel อีกครั้ง
3. ในขั้นตอนการนำเข้าข้อมูล ให้แนใจว่าเลือก Tab เป็นตัวแบ่งข้อมูลในขั้นตอนที่ 2 จาก 3 (ในการใช้งานจริงกดปุ่ม Finish เลยก็ได้ ถ้าข้อมูลแสดงไม่ถูกต้องค่อยไล่ที่ละขั้นตอน
4. ตรวจสอบว่าตัวเลขติดลบค่าถูกต้องหรือไม่
5. ถ้าถูกต้องแล้วทำการ copy ข้อมูลทั้งแผ่นงาน แล้วไป Paste by Values ในไฟล์ต้นฉบับ

ทำการ Save as เป็น Text (Tab delmited) โดยเปลี่ยนชื่อไฟล์ใหม่ด้วย กันพลาด

คำเตือน ให้กด Yes เลย

ใช้คำสั่ง Open เพื่อเปิด Text file ที่บันทึกไว้ในตอนแรก

ในขั้นตอนการ Import ข้อมูล สามารถกด Finish ได้เลย

ในกรณีที่กด Finish แล้วข้อมูลไม่ถูกต้อง ให้ปิดแล้วลองเปิดไฟล์ใหม่
กด Next > ตรวจสอบว่า Step 2 of 3 ใน Delimiters เป็น Tab หรือไม่ (ตัวแบ่งคอลัมน์)

ตรวจสอบความถูกต้องจะเห็นว่า 86391.98- ในตอนแรก ตอนนี้เป็นค่าตัวเลขติดลบไปคำนวณได้แล้ว

การแปลงโดยใช้คำสั่ง Text to columns
หากลองใช้คำสั่งนี้บ่อย ๆ จะพบว่ามันมีประโยชน์นอกจากช่วยแปลงวันที่แล้ว ยังช่วยแปลงเลขติดลบที่ตัวเครื่องลบอยู่ด้านหลังได้ด้วย วิธีนี้เหมาะกับข้อมูลไม่เยอะครับ เพราะทำได้ทีละคอลัมน์เท่านั้น (วิธีนี้ยังพออยู่บ้างใน google แต่ปัญหาคือ มันทำได้ทีละคอลัมน์นี่แหละ เลยใช้งานลำบาก)

เลือกข้อมูลที่ต้องการจะแปลง เลือกคำสั่ง Data → Text to Columns

กดปุ่ม Finish ได้เลย

จะเห็นว่าเลขติดลบสามารถนำมาคำนวณได้แล้ว