ใส่เลข 0 ข้างหน้า (Excel / Access)

ผู้ร่วมงานท่านหนึ่งถามวิธีใส่เลข 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 หรือ ข้อความ

ใส่เลข 0 ข้างหน้า

หรือ คลิกขวา เลือก Format Cells แท็บ Number ให้เลือก category เป็น Text

ใส่เลข 0 ข้างหน้า โดย Custom Format Number
เลือกรูปแบบเป็น 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 ได้

ผลลัพธ์ 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(ค่าที่ต้องการ, รูปแบบที่ต้องการ)

ซึ่งเราเพียงใส่ค่า

Format([UTM2],”00”)

เพียงเท่านี้ก็เปลี่ยนจาก 1 เป็น 01 แล้ว ง่าย ๆ เลย

ทั้งหมดนี้ก็เป็นเรื่องการ ใส่เลข 0 ข้างหน้าหลักที่ต้องการเติมเต็มให้เป็นจำนวนหลักที่ต้องการ

ความคิดเห็นของคุณ :)

%d bloggers like this: