“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”

หวังว่าจะเป็นประโยชน์กับทุกคนนะครับ ^_^

สำหรับผู้ที่สนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ