วันนี้ Excel Tips แบบง่ายๆที่จะนำเสนอคือ การกำจัดอักขระที่ไม่เป็นตัวเลขในข้อความ ซึ่งท่านผู้อ่านจะพบได้มากในกรณีการนำเข้าข้อมูลรูปแบบอื่นๆมายัง Excel ดังนั้นก่อนจะดำเนินการวิเคราะห์หรือนำข้อมูลไปใช้เราต้องจัดการกำจัดอักขระเหล่านี้ออกไปก่อนครับ เป็นที่ทราบอยู่แล้วว่าอักขระทั้งหมดมี 255 ตัว เราสามารถแสดงอักขระเหล่านั้นใน CELL ได้โดยใช้สูตร excel : Char(number) โดย number จะเป็นตัวเลขระหว่าง 1 - 255 ในตัวอย่างนี้จะแสดงตัวอักขระแต่ละตัวในคอมลัมภ์ D โดยจะเรียงตั้งแต่อักขระตัวที่ 1 ถึง ตัวที่ 255 ในแถวถัดไป ซึ่ง number จะได้จากสูตร excel : ROW() ซึ่งจะคืนค่าหมายเลขแถวของ CELL ดังกล่าว ดังนั้นเราจะเขียนสูตร excel เพื่อแสดงอักขระแต่ละตัวได้ดังนี้
สมมุติให้ D1 เป็นอักขระตัวที่ 1 จะเขียนสูตร excel ใน D1 ได้เป็น
= CHAR(ROW())
และเมื่อ copy สูตร excel ลงไปตามแถวจนถึงแถวที่ 255 ในหลัก D เราจะได้อักขระแสดงได้ดังภาพที่ 1
ภาพที่ 1 อักขระที่ได้จากสูตร excel : CHAR
ผลการใช้สูตร Excel : CHAR เราจะพบว่าอักขระที่เป็นตัวเลขอารบิก 0-9 จะเป็นอักขระตัวที่ 48-57 ขณะที่ตัวอักขระภาษาอังกฤษตัวพิมพ์ใหญ่จะเป็นอักขระตัวที่ 65-90 และ ตัวพิมพ์เล็กจะเป็นอักขระตัวที่ 97-122
สมมุติเรามีข้อความที่ได้จากโปรแกรมอื่นเข้ามาซึ่งมีอักขระที่ไม่มีความหมายหรือเรียกง่ายๆว่าข้อความขยะปะปนมาด้วย โดยเราต้องการเฉพาะข้อความที่เป็นตัวเลขเท่านั้นเพื่อนำไปวิเคราะห์ปัญหาในลำดับถัดไป ดังนั้นจึงต้องกำจัดอักขระเหล่านี้ก่อนครับ โดยจะใช้สูตร Excel : Substitute ซึ่งมีรูปแบบดังนี้
Substitute(text,old text,new text) โดย
text คือข้อความต้นฉบับ
old text คือข้อความหรืออักขระภายในข้อความต้นฉบับที่ต้องการกำจัด
new text คือข้อความที่ต้องการแทนที่ old text ซึ่งในที่นี้คือค่าว่าง ""
เราจะแทนที่อักขระที่ไม่ต้องการด้วยค่าว่างและจะทำการตรวจสอบข้อความเพื่อกำจัดอักขระที่ไม่ต้องการและแทนที่ด้วยค่าว่างตั้งแต่อักขระตัวที่ 1 -255 โดยยกเว้นอักขระตัวที่ 48-57 ดังนั้นเราจะต้องลบสูตร Excel : CHAR ในแถวที่ 48 - 57 ออกก่อน สมมุติให้ข้อความที่ต้องการเฉพาะตัวเลขอยู่ใน CELL B3 ดังแสดงในภาพที่ 2 เราจะดำเนินการกำจัดอักขระดังกล่าวตามขั้นตอนดังนี้
1. เขียนสูตร Excel : Substitute ใน CELL E1 เป็น =SUBSTITUTE(B3,D1,"") เพื่อกำจัดอักขระตัวที่ 1
2. เขียนสูตร Excel : Substitute ใน CELL E2 เป็น =SUBSTITUTE(E1,D2,"") เพื่อกำจัดอักขระตัวที่ 2
3. Copy สูตร Excel ใน CELL E2 ลงไป CELL E3 จนถึง CELL E255 เพื่อกำจัดอักขระตัวที่ 3 - 255 (ยกเว้นตัวที่ 48 -57 ซึ่งเราได้ลบสูตร Excel : CHAR ไปแล้วจะไม่ถูกกำจัด)
แสดงผลลัพธ์การกำจัดอักขระที่ไม่ต้องออกจากข้อความหมดแล้วใน CELL C3 โดยกำหนดให้ค่าใน CELL C3 = E255 ดังแสดงในภาพที่ 2
ภาพที่ 2 แสดงผลการกำจัดอักขระที่ไม่ใช่ตัวเลขหมดแล้ว
จากตัวอย่างท่านผู้อ่านสามารถประยุกต์ได้อีกมากครับ เช่นหากต้องการทั้งตัวเลข ตัวอักษร ภาษาไทยภาษาอังกฤษ ก็สามารถทำได้ โดยสิ่งสำคัญต้องทราบก่อนคืออักขระที่เราต้องการเป็นอักขระลำดับที่เท่าไหร่จากนั้นให้ไปลบสูตร excel : CHAR(ROW()) ในแถวนั้นก่อนดังตัวอย่างที่นำเสนอไป เท่านี้เราก็ได้ข้อความที่สามารถนำไปใช้งานได้แล้ว
ไม่มีความคิดเห็น:
แสดงความคิดเห็น