“Excel มีวิธีรันตัวเลข 001 ถึง 999 ไหมครับ?”
“พิมพ์เลข 1, 2 แล้วลากเซลล์ถึง 999 ไม่ได้เหรอโจ้?” ผมถามกลับ
“แบบนั้นมันได้แค่เลขชุดเดียวครับพี่”
“โจ้ต้องการหลายชุด?”
“ใช่ครับ ถ้ารันถึง 999 แล้วให้เริ่ม 001 ใหม่ วนไปเรื่อย ๆ “
“อ้อ! ทำได้สิ ไม่ยากด้วยนะ” ผมยิ้ม
“จริงเหรอ ทำยังไงครับพี่!”
สมมติต้องการรันตัวเลขตั้งแต่เซลล์ A2
สูตรที่ใช้คือ
=TEXT(
MOD(ROWS($A$2:A2)-1,999)+1,
"000"
)
ได้คำตอบตามต้องการ

“ง่ายงี้เลยหรือพี่!” โจ้ร้องเสียงดัง
“ใช่ ง่ายงี้แหละ” ผมยิ้ม ^_^
สูตรนี้ทำงานยังไง?
เพื่อให้เข้าใจง่าย ลองเริ่มจากตรรกะ
ถ้าเปลี่ยนคำถามเป็น
มีตัวเลข 1-10,000 ทำยังไงให้เป็น 1 ถึง 999 แล้ววนไปเรื่อย ๆ?

ตรรกะนึงที่ใช้ได้คือ
นำตัวเลขไปลบ 1 หารด้วย 999 แล้วนำเศษจากการหารไปบวกด้วย 1
เช่น ถ้าตัวเลขคือ 1,001
1,001-1 = 1,000
1,000/999 เหลือเศษ 1
1+1 = 2
แปลว่า 1,001 ถูกเปลี่ยนเป็น 2
1,002 ก็จะถูกเปลี่ยนเป็น 3
1,003 ก็จะถูกเปลี่ยนเป็น 4
ได้ตรรกะแล้ว คราวนี้มาดูสูตร Excel กัน
ถ้าคิดง่าย ๆ ลองเขียนเป็น
(number-1)/999 + 1
แบบนี้จะมีปัญหา เพราะต้องใช้เศษจากการหาร
ถ้าต้องการเศษจากการหาร ฟังก์ชันที่ใช้คือ MOD
โครงสร้างของฟังก์ชัน MOD คือ
MOD(number,divisor)
เคสนี้เขียนสูตรได้เป็น
MOD(number-1,999)+1
“แล้วจะหา number ได้ยังไง?”
คำตอบคือฟังก์ชัน ROWS
ROWS คือฟังก์ชันที่ใช้ส่งค่ากลับมาว่า ข้อมูลในช่วงนั้น (array) มีกี่บรรทัด
โครงสร้างของฟังก์ชัน ROWS คือ
ROWS(array)
เคสนี้เริ่มต้นที่เซลล์ A2 ให้เขียนสูตรเป็น
ROWS($A$2:A2)
(ล็อกเซลล์ตัวแรก ไม่ต้องล็อกตัวปลาย)
ผลลัพธ์คือตัวเลขที่ต้องการ

“พี่ครับ”
“ว่าไงโจ้?”
“เราเขียนสูตรที่เซลล์ A2 แต่อ้างอิงเซลล์ A2 ได้ด้วยหรือครับ?”
เป็นคำถามที่ดีมาก
โดยทั่วไปแล้ว การเขียนสูตรที่เซลล์ใด จะไม่สามารถอ้างอิงเซลล์นั้น เพราะจะเกิดสิ่งที่เรียกว่า Circular Reference
แต่สำหรับฟังก์ชัน ROWS (และ COLUMNS) เป็นข้อยกเว้น
เพราะแค่อ้างอิงตำแหน่ง แต่ไม่ได้นำข้อมูลในเซลล์นั้นมาคำนวณ
เช่น อ้างอิงเป็น ROWS($A$2:A2) แต่ไม่ได้นำข้อมูลในเซลล์ A2 มาคำนวณ
แบบนี้ไม่เกิด Circular Reference
มาคุยเรื่องสูตรกันต่อ
จากตรรกะเดิม
=MOD(number-1,999)+1
เปลี่ยน number เป็น ROWS($A$2:A2)
=MOD(ROWS($A$2:A2)-1,999)+1
ได้ตัวเลข 1-999 วนไปเรื่อย ๆ ตามต้องการ

“แล้วถ้าอยากทำให้ 1 กลายเป็น 001 ล่ะครับ?” โจ้ถาม
คำตอบคือฟังก์ชัน TEXT
TEXT คือฟังก์ชันที่ใช้เปลี่ยนข้อมูลให้เป็น text ความเจ๋งคือสามารถกำหนดฟอร์แมตของ text ได้
โครงสร้างของฟังก์ชัน TEXT คือ
TEXT(value,format_text)
ถ้าต้องการเปลี่ยนฟอร์แมตจาก 1 ให้เป็น 001
format_text ที่ใช้คือ “000”
เคสนี้จึงกลายเป็น
=TEXT(
MOD(ROWS($A$2:A2)-1,999)+1,
"000"
)
“ว้าว! แบบนี้เอง หลงเขียนเป็น 0&number ตั้งนาน” โจ้ยิ้มเขิน ๆ
“พี่ก็เคยทำแบบนั้นเหมือนกัน” ผมยิ้ม
“แล้วถ้าต้องการคำว่า TH- นำหน้าล่ะครับ เช่น TH-001, TH-002?” โจ้เกิดไอเดีย
ทำได้ 2 แบบ
แบบแรกคือนำ “TH-” ไป & กับสูตรเดิม
="TH-"&
TEXT(
MOD(ROWS($A$2:A2)-1,999)+1,
"000"
)
หรือให้ง่ายกว่านั้น ใส่ TH- ลงไปใน format_text
แบบนี้
=TEXT(
MOD(ROWS($A$2:A2)-1,999)+1,
"T\H-000"
)
ได้คำตอบตามต้องการ

“เอ๋! แบบนี้ได้ด้วยเหรอ” โจ้ร้องลั่น
“ว่าแต่.. ทำไมถึงเป็น “T\H-000” ล่ะครับ?“
เพราะ H เป็น reserved word
หลักการของ format_text คือ ถ้าต้องการระบุอักขระใด แล้วอักขระนั้นเป็น reserved word ต้องใส่เครื่องหมาย \ (backslash) นำหน้า
h, H เป็น reserved word จึงต้องใส่เครื่องหมาย \ นำหน้าเป็น \H
d, m, y, b, h, s พวกนี้ก็เป็น reserved word ทั้งหมด
ส่วน T, – ไม่ใช่ reserved word จึงไม่ต้องเขียนเป็น \T, \- (แต่จะใส่ก็ไม่ผิด)
เทคนิคนี้นอกจากใช้ใน Excel ได้แล้ว ยังใช้ในสูตร DAX (Power BI) ได้ด้วย ^_^
“แล้วถ้าต้องการระบุคำยาว ๆ เช่น Thailand แบบนี้ต้องเขียนเป็น \T\h\a\i\l\a\n\d ไหมครับ?” โจ้เกิดไอเดีย
เขียนแบบนั้นก็ไม่ผิด แต่คงไม่สะดวกในทางปฏิบัติ
ถ้ามีหลายอักขระ และไม่อยากมานั่งจำว่าอักขระใดเป็น reserved word ให้ใส่เครื่องหมาย double quotation mark ( ” ” ) ครอบคำนั้น ๆ สองชั้น
เช่น “ “”Thailand-“”000 “
สังเกตว่ามีเครื่องหมาย ” ” 2 ระลอก
ระลอกแรกอยู่นอกสุด (สีแดง)
ระลอกสองครอบเฉพาะคำว่า “”Thailand“” และใส่สองชั้น (สีฟ้า)
หรือจากสูตรเดิม ก็เปลี่ยนเป็นแบบนี้
=TEXT(
MOD(ROWS($A$2:A2)-1,999)+1,
"""Thailand-""000"
)
ได้คำตอบตามต้องการ

“ว้าว! เพิ่งรู้ว่าทำแบบนี้ได้ด้วย ขอบคุณมากนะครับ” โจ้โน้มตัวลง
“ยินดีครับ”
บทความนี้แม้สูตรสั้น แต่มีเทคนิคไม่น้อย
ไม่ว่าจะเป็น
- MOD
- ROWS
- Circular Reference
- TEXT
- “000”
- \H
- “””Thailand-“”000”
หวังว่าจะเป็นประโยชน์กับทุกคนนะครับ ^_^
สำหรับผู้ที่สนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ
แล้วถ้า 3 ตัว สลุบตำแหน่งกันได้ล่ะคะ