ผู้ร่วมงานท่านหนึ่งถามวิธีใส่เลข 0 ข้างหน้าตัวเลขให้ครบหลัก อย่างเช่น อยากได้ 2 หลัก ถ้าเป็น 1 2 3 อยากให้เป็น 01 02 03 หรืออาจจะอยากได้ 3 หลัก ถ้าเป็น 1 2 3 ก็จะให้เป็น 001 002 003 แบบนี้เป็นต้น ทั้งนี้เพราะว่าท่านผู้นั้นจะต้องทำ Lookup สองตารางเข้าด้วยกัน แต่มีฟิลด์หนึ่งซึ่งจำเป็นต้องใช้เป็นตัวเชื่อม ตารางหนึ่งบันทึกมาในรูปแบบข้อความ เวลาเป็นเลขหลักเดียว เขาจะเพิ่ม 0 เข้าไปข้างหน้า แต่อีกตารางหนึ่ง จะเป็นตัวเลข เมื่อนำมา lookup จะไม่เจอกัน
ตัวอย่างดังนี้
มีตาราง 2 ตาราง ที่จะใช้สูตร Xlookup เพื่อค้นค่าที่ตรงกัน โดยใช้ “Key” เป็นตัว lookup โดยตัว key นี้จะสร้างขึ้นมาจาก UTM1-3 และ LandNo
เราจะ Lookup เพื่อหาค่า Value จาก ตาราง 1 มาใส่ที่ตาราง 2 แต่ผลที่เกิดขึ้นคือ “ไม่พบ” แทนที่จะเป็น Match สาเหตุเพราะว่า ค่า UTM2 ในตาราง 1 เป็น 01 แต่ ในตาราง 2 เป็น 1
ถ้าหากมีข้อมูลแค่ 1 หรือ 2 ข้อมูลคงแก้กันง่าย ๆ แต่ในการทำงานจริง จะต้องเปลี่ยนทั้งหมดประมาณ 33 ล้านข้อมูล
แก้ทีละช่องคงไม่ไหว
เราต้องแก้โดยอาศัยความช่วยเหลือจากศักยภาพของซอฟต์แวร์ที่เราใช้งาน ซึ่งที่ใช้อยู่ก็มีทั้ง Excel Access และ ข้อมูลเป็น Text แล้วประมวลผลด้วย python แต่ในขั้นต้นจะพูดถึงวิธีแก้ไขสำหรับ Excel กับ Access ก่อน
ซึ่งโจทย์ก็จะเหมือนกันทั้ง 2 ซอฟต์แวร์ นั่นคือ ทำอย่างไรจะใส่เลข 0 เติมเข้าไปข้างหน้าตัวเลขหลักเดียวใน UTM2
เนื้อหาโดยรวม :)
ใส่เลข 0 ข้างหน้า ใน Excel
ตามปกติทั่วไป ใน Excel ถ้าเราพิมพ์ตัวเลข เช่น 01 Excel จะแสดงค่าเป็น 1 แต่ในบางกรณี เราต้องการให้แสดงเป็น 01 อาจจะด้วยว่ามันเป็นรหัสของอะไรสักอย่าง ซึ่งก็มีวิธีการหลายอย่างบังคับให้ Excel แสดงค่าเป็น 01 อย่างที่เราอยากให้เป็น แต่อาจจะไม่ให้ผลลัพธ์ที่เราต้องการก็ได้ ซึ่งก็จะแนะนำทั้งหมดก่อนแล้วจะนำมาทดสอบดู
ใช้ Number Format ให้เป็นประโยชน์
ตัวเลือกแรกคือเปลี่ยนรูปแบบ (Format) ของตัวเลขให้เป็นข้อความ (Text) ซึ่งจะทำให้เก็บเลขศูนย์นำหน้าเอาไว้ได้ เวลาที่เราพิมพ์ลงไป
Format เป็น Text
ใน format จะมีตัวเลือกหลากหลาย รวมทั้งการกำหนดรูปแบบตัวเลขให้อยู่ในรูปแบบข้อความ การทำเช่นนี้จะถือว่าข้อมูลที่พิมพ์เข้าไปจะเป็นข้อความ แม้สิ่งที่พิมพ์จะเป็นตัวเลขก็ตาม ซึ่งจะทำให้ Excel เก็บเลขศูนย์นำหน้าด้วย
วิธีการ
ไปที่แท็บ Home ดูกลุ่มคำสั่ง Number เลือกจากตัวเลือกให้เป็น Text หรือ ข้อความ
หรือ คลิกขวา เลือก Format Cells แท็บ Number ให้เลือก category เป็น Text
เมื่อเลือกวิธีนี้ ขอให้สังเกตว่า ตัวเลขจะมาชิดขวา แทนที่จะเป็นซ้ายเหมือนเดิม และที่ตัวเลขที่เป็นเลขเดี่ยว จะมีสามเหลี่ยมสีเขียวเล็ก ๆ ขึ้นที่มุมบนซ้าย อันนี้เป็นปกติเพราะเป็นการเตือนเฉย ๆ ว่ามีการเก็บตัวเลขในรูปแบบข้อความนะ
Custom Number Format
วิธีนี้คลิกขวา เลือก Format Cells แท็บ Number ให้เลือก category เป็น Custom
ตรง Type ให้พิมพ์ 00
เมื่อใช้รูปแบบตัวเลข เป็น Custom ท่านเพิ่มศูนย์นำหน้าเพื่อสร้างตัวเลขที่มีจำนวนหลักที่ต้องการได้ โดย
ถ้าเป็น 0 จะเป็นจำนวนหลักที่กำหนด เช่น 00 คือ ตัวเลข 2 หลัก ถ้าเป็นตัวเลขหลักเดียว จะเติม 0 เข้าไปข้างหน้า
ถ้าเป็น # จะเป็นเลขจำนวนเต็ม
อย่างเช่น ถ้าเรากำหนด type เป็น 000 แล้วเราพิมพ์ 1 จะออกมาเป็น 001 แต่ถ้าเรากำหนด type เป็น ### แล้วพิมพ์ 1 จะออกมาแค่ 1 อ่านเพิ่มเติมที่ custom number format
เติมอัญประกาศเดี่ยวไปข้างหน้า
การเติมอัญประกาศเดี่ยว หรือ apostrophe เป็นวิธีที่นิยมใช้วิธีหนึ่งในการกำกับว่าสิ่งที่จะพิมพ์เข้าไป จะเก็บไว้ในรูปแบบข้อความหรือ text ซึ่งเวลาแสดงผลหรือพิมพ์ออกมาจะไม่เห็นตัวอัญประกาศเดี่ยว แต่จะแสดงในช่อง Formular
ใช้ฟังก์ชันให้เป็นประโยชน์
แต่!!! เนื่องจาก สิ่งที่ต้องการในครั้งนี้ คือการเปลี่ยนสิ่งที่มีอยู่แล้ว สิ่งที่นำเสนอไปข้างต้นบางอย่างก็ไม่สามารถใช้งานได้
การเปลี่ยน format ให้เป็น Text เพราะอันนี้แค่เปลี่ยนเป็น text ซึ่งต้องบันทึกข้อมูลใหม่แบบมี 0 นำหน้า
Custom Format Number ตรงนี้แค่เปลี่ยนการแสดงผล แต่สิ่งที่เก็บไว้ก็ยังเป็น 1 ไม่ใช่ 01
การเติมอัญประกาศเดี่ยว ตรงนี้เป็นเหตุผลเดียวกับการเปลี่ยน format เป็น text ต้องบันทึกข้อมูลใหม่แบบมี 0 นำหน้า
เราต้องใช้ฟังก์ชันมาช่วย
ฟังก์ชัน Text
มาทำให้ยุ่งยากเพิ่มขึ้น ด้วยการใส่ฟังก์ชัน Text รูปแบบฟังก์ชันนี้ก็คือ
Text(ตัวเลข, รูปแบบ)
ซึ่งเราต้องการตัวเลขเป็น 2 หลัก เราใส่แบบนี้
=text(ตัวเลข, “00”)
ตัวเลขที่เป็นหลักเดียว ก็จะมีเลข 0 นำหน้า
ท่านสามารถศึกษาการใช้ฟังก์ชันนี้เพิ่มเติมได้ที่ https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c
ฟังก์ชัน Right
ฟังก์ชัน Right หรือการตัดข้อมูลจากทางด้านขวา ก็เป็นอีกฟังก์ชันหนึ่งที่เอามาประยุกต์ใช้งานได้ รูปแบบฟังก์ชันนี้ก็คือ
Right(“0”, ข้อความ, จำนวน)
โดยที่ “0” ก็คือจำนวนเลขศูนย์ที่ต้องการใส่ ในที่นี้ต้องการแค่ 0 เดียวเติมหน้าตัวเลขที่เป็นหลักเดียว
ข้อความ ก็คือตัวเลขที่เราต้องการจัดการ หรือ Cell ที่เราอ้างอิง
จำนวน ก็คือจำนวนของหลักที่เราต้องการ อย่างในตัวอย่างนี้ เราต้องการ 2 หลัก เราจะใส่เลข 2
ตัวอย่างการเขียนสูตรนี้ก็คือ
=RIGHT("0"&H3, 2)
ฟังก์ชัน REPT และ LEN
ฟังก์ชัน Rept ก็คือ repeat หรือทำซ้ำ คือ เราอยากให้เพิ่มจำนวนอักขระเท่าไหร่ เราก็จะใช้ฟังก์ชันนี้ ร่วมกับฟังก์ชัน LEN ที่ใช้สำหรับนับจำนวนอักขระ โดยมีแนวคิดง่าย ๆ คือ นับจำนวนหลัก ถ้าเป็นจำนวนน้อยกว่าหลักที่ต้องการ ให้ใส่เลข 0 เข้าไปข้างหน้า
สำหรับ REPT จะมีรูปแบบการเขียนว่า
=REPTสิ่งที่ต้องการเพิ่ม,จำนวน)
เช่น หากต้องการให้แสดงตัว x จำนวน 5 ตัว เราจะเขียนสูตรว่า
=REPT(“x”,5)
เราเอามาแทนที่ใน ค่า x เป็น 0 แต่จำนวนที่จะให้ ใส่เพิ่มเป็นจำนวนเท่าไหร่ล่ะ? ในที่นี้คือ ถ้ามี 2 หลัก ไม่ต้องเติม 0 ข้างหน้า แต่ถ้าหลักเดียวให้เติม 0 เข้าไป 1 ตัว เราก็จะใช้ 2 – LEN (ตัวเลข) คือ ถ้า เป็นหลักเดียว ก็จะเป็น 2-1 = 1 คือเพิ่ม 0 ไป 1 ตัว แต่ถ้าเป็นเลข 2 หลักอยู่แล้ว ก็จะเป็น 2-2 = 0 คือ ไม่ต้องเพิ่มเลข 0
=REPT(0, 2-LEN(ตัวเลข))
แต่ตรงนี้ก็จะได้แค่เลข 0 ว่ามี หรือไม่มี เราต้องเพิ่งค่าของตัวเลขเดิมนั้นไปด้วยเพื่อให้แสดงผล ก็จะเป็น
ในที่นี้ เราจะใช้ LEN นับจำนวนหลักของตัวเลข ซึ่งเราต้องการเลข 2 หลัก
=REPT(0, 2-LEN(ตัวเลข))&ตัวเลข
อย่างเช่น ในตัวอย่างนี้ เราต้องการเพิ่มตัวเลข 0 จำนวน 2 ตัว จากตัวเลขที่อยู่ใน Cell H3 สูตรก็จะเป็น
=REPT(0, 2-LEN(H3))&H3
ฟังก์ชัน Base
ฟังก์ชัน BASE ช่วยแปลงตัวเลขเป็นข้อความด้วยฐานที่กำหนด ตัวเลขที่คุณใช้เป็นประจำคือฐาน 10 แต่คุณสามารถใช้ฟังก์ชันนี้เพื่อแปลงตัวเลขฐาน 10 ให้เป็นตัวแทนฐาน 2 (ไบนารี) ได้
= BASE (ตัวเลข, ฐาน, [ความยาวขั้นต่ำ])
ตัวเลข – คือตัวเลขที่ต้องการแปลงเป็นค่าข้อความในฐานอื่น
ฐาน –คือฐานที่ต้องการแปลงค่า
ความยาวขั้นต่ำ (ไม่บังคับ) –ความยาวขั้นต่ำของอักขระของค่าข้อความที่แปลงแล้ว
= BASE(H3, 10,2)
สูตรข้างต้นจะแปลงตัวเลขในเซลล์ H3 เป็นฐาน 10 (เป็นตัวเลขฐาน 10 อยู่แล้ว แต่จะเก็บไว้เป็นฐาน 10) และแปลงเป็นค่าข้อความที่มีอักขระอย่างน้อย 2 ตัว หากค่าที่แปลงแล้วมีอักขระน้อยกว่า 2 ตัว ฟังก์ชันนี้จะเติมค่าศูนย์ไปข้างหน้าเพื่อให้ได้อักขระขั้นต่ำ 2 ตัว
ทั้งหมดนี้ สามารถเอาไปประยุกต์เข้ากับการแปลงตัวเลขเพื่อให้ lookup ได้
ใส่เลข 0 ข้างหน้า ใน ACCESS
สำหรับ ACCESS เรามีวิธีแปลงค่า 1 เป็น 01 ได้ง่ายมากโดยใช้ QUERY เพื่อจัดการ Format
ก่อนอื่นมาดูที่ตาราง หรือ Table ที่เก็บข้อมูล ตรงนี้เราจัดการ Type ของสิ่งที่เราต้องการได้ ถ้าหากเรา ใส่เลข 0 ข้างหน้า วิธีที่ง่ายสุด หากต้องการให้แสดงค่าเป็น 01 จากที่เป็น 1 ทำได้ง่าย ๆ โดย ไปที่ design view เลือก field ที่ต้องการ (ในที่นี้คือ UTM2) แล้วกำหนดรูปแบบเป็น double และ Format เป็น 00 (คือ 2 หลัก)
แต่! ก็เหมือนกับกำหนด format ใน Excel คือ ถึงแม้จะแสดงเป็น 01 แต่ค่าที่เก็บจริง ๆ จะเป็น 1 เวลาเราทำ QUERY เพื่อสร้าง KEY สำหรับนำไปเชื่อมข้อมูล จะยังเป็น 1 เหมือนเดิม สิ่งที่เราต้องทำคือใส่เลข 0 ข้างหน้าตัวเลขหลักเดียวและให้มันแสดงผลและเก็บค่าแบบนั้นด้วย
เราแก้ไขได้ด้วยฟังก์ชัน Format
สูตร Format ก็คือ
Format(ค่าที่ต้องการ, รูปแบบที่ต้องการ)
ซึ่งเราเพียงใส่ค่า
Format([UTM2],”00”)
เพียงเท่านี้ก็เปลี่ยนจาก 1 เป็น 01 แล้ว ง่าย ๆ เลย
ทั้งหมดนี้ก็เป็นเรื่องการ ใส่เลข 0 ข้างหน้าหลักที่ต้องการเติมเต็มให้เป็นจำนวนหลักที่ต้องการ