เรื่องการ Run เลขเอกสารอัตโนมัติเป็นเรื่องที่หลายๆ ท่านถามมา ผมเลยขอนำเสนอเทคนิคที่ผมคิดขึ้นเองสดๆ ซึ่งเทคนิคนี้เจ๋งตรงที่ว่าไม่ต้องใช้ VBA เลยด้วยนะ!!
สารบัญ
เริ่มต้นกันเลย
ก่อนอื่นเราก็ทำเอกสารหน้าแรกให้เสร็จก่อน จะใส่ข้อความอะไร ตารางอะไรยังไง แต่สวยแค่ไหนก็ใส่ไปให้ครบ
ซึ่งให้ตั้งชื่อชีทให้เรียบร้อยด้วย เช่น ผมตั้งว่า BillDoc (จริงๆ จะตั้งอะไรก็ได้นะ)
ประเด็นหลักคือ เดี๋ยวเราจะทำให้ไอ้ตรงช่องเลข Running เอกสารสีเขียวๆ มันเพิ่มเองได้เวลาเพิ่ม Sheet
โดยที่ผมแถมความพิเศษอีกอย่างให้ด้วย คือ เราสามารถกำหนด Parameter ด้านขวาได้ (นอกขอบเขต Print Area จะได้ Print ไม่ออก) เช่น กำหนดได้ว่าเลขจะให้ adjust บวกเพิ่มไปเท่าไหร่ กำหนดคำนำหน้าได้ และกำหนดจำนวน Digit ได้ด้วย
สรุปวีธีรันเลขเอกสารแบบรวดรัด
ใส่สูตรนี้ลงไปในช่องสีเขียว แค่นี้จบเลย (แต่ตำแหน่งตัว parameter ด้านขวาต้องอยู่ในช่องตรงกับผมนะ ถ้าไม่ตรงต้องแก้ในสูตรเองนะครับ)
=M3&TEXT(IFERROR(MID(CELL("filename",A1),FIND("(",CELL("filename",A1))+1,FIND(")",CELL("filename",A1))-1-FIND("(",CELL("filename",A1))),1)+M2,REPT("0",M4))Copy Sheet เพื่อเปลี่ยนเลขอัตโนมัติ
เมื่อเรา Copy Sheet ออกมา เลขจะเปลี่ยนเองอัตโนมัติด้วย ซึ่งวิธี Copy Sheet ที่ง่ายที่สุดคือ กด Ctrl ค้างไว้ แล้วคลิ๊กที่แถบ Sheet แล้วลากออกมาด้านขวา
จะเห็นว่าเลข Running เปลี่ยนโดยอัตโนมัติเลย!! โดยไม่ต้องใช้ VBA ด้วยซ้ำ!!
แก้ Parameter ได้
นอกจากนี้เรายังแก้ Parameter ของแต่ละ sheet ได้ด้วย (ถ้าอยากให้แก้ทีเดียวแล้วแก้หมดทุก sheet ควรสร้าง sheet parameter แยกไว้ แล้วเอาสูตร Link ไปที่นั่นที่เดียว)
เช่น
- ผมแก้ให้เลข adj เพิ่มไป 4 ดังนั้นเลขจะกลายเป็น 2+4=6
- แก้คำนำหน้าเป็นคำว่า PO-
- แก้จำนวน Digit เป็น 3 หลัก
จะเห็นว่าเลขเอกสารกลายเป็น PO-006 แล้วววว
อธิบายหลักการของสูตรรันเลขเอกสาร
ผมคิดสูตรนี้ขึ้นมาจากแนวคิดที่ว่า ปกติเวลา Copy Sheet เลขบนชื่อ Sheet จะเพิ่มขึ้นเองอยู่แล้ว โดยไม่ต้องใช้ VBA ถ้าอย่างนั้น เราดึงตัวเลขที่เพิ่มขึ้นเองนี้มาใช้ประโชน์ในการรันเลขเอกสารก็ได้น่ะสิ
ดังนั้นจากสูตรยาวๆ อันนี้ ผมจะขออธิบายทีละส่วน ดังนี้
=M3&TEXT(IFERROR(MID(CELL("filename",A1),FIND("(",CELL("filename",A1))+1,FIND(")",CELL("filename",A1))-1-FIND("(",CELL("filename",A1))),1)+M2,REPT("0",M4))
M3 & … แค่เป็นการเอาข้อความ 2 ก้อนมาต่อกัน ไม่น่างงเนอะ
ฟังก์ชัน TEXT เปลี่ยนตัวเลขไปเป็น Text ที่มี Format ตามต้องการ
หลักๆ ในก้อน 2 จะมีการใช้ =TEXT(value,format_text) ซึ่งมีความสามารถในการเปลี่ยนตัวเลขไปเป็น Text ที่มี Number Format ตาม Custom Format ที่กำหนด
เช่น ผมสามารถเปลี่ยนเลข 6 ให้กลายเป็น 0006 ได้ด้วยการเขียนว่า
=TEXT(6,"0000")
ซึ่งในที่นี้ format_text ผมมีการใช้ REPT (Repeat) เพื่อให้สร้างข้อความเลข 0 ต่อกันโดยให้มีจำนวนเท่ากับค่าใน M4 เช่นถ้า M4 เขียนว่า 3 จะสร้างข้อความว่า 000 ลงมาใน format_text
ทำให้ TEXT สามารถเปลี่ยนเลข 6 ให้กลายเป็น 006 ได้ เป็นต้น
ทีนี้เลข 6 ใน value มันมาจากไหน?
ก็จะพบว่าเกิดจากส่วนสูตรยาวๆ สีฟ้า + ค่าใน M2 ซึ่ง M2 คือตัว Adjust เลขที่ผมใส้ไว้นั่นเอง
IFERROR(MID(CELL("filename",A1),FIND("(",CELL("filename",A1))+1,FIND(")",CELL("filename",A1))-1-FIND("(",CELL("filename",A1))),1)+M2
ดังนั้นเหลือไอ้ตัวสีฟ้าแล้วล่ะที่ซับซ้อน…
CELL เอาไว้หาข้อมูลชื่อชีทได้
หลักการคือ มันจะหาตัวเลขที่อยู่ในวงเล็บของชื่อ Sheet ซึ่งประยุกต์มาจากการหาชื่อชีทซึ่งสามารถหาได้จากฟังก์ชัน CELL(“filename”) ตามที่ได้อธิบายแล้วในบทความแฉความลับภาค 3 ข้อ3
ซึ่งถ้าเขียนแค่ CELL(“filename”,A1) เฉยๆ เครื่องผมจะได้ตามนี้
D:\ThepExcel\web\running number document\[runningdoc.xlsx]BillDoc (2)
ซึ่งมันคือชื่อ path ตามด้วยชื่อไฟล์ และชื่อชีท ซึ่งผมอยากได้แค่ตัวเลขในวงเล็บสุดท้ายแค่นั้นเอง
ซึ่งฟังก์ชันที่ใช้หาเลขในวงเล็บของชื่อชีท ผมใช้ฟังก์ชัน FIND โดยหาตำแหน่งวงเล็บเปิด และวงเล็บปิด ผสมกับการใช้ MID ช่วยในการดึงตัวเลขนั้นออกมาด้วยสูตรนี้
MID(CELL("filename",A1),FIND("(",CELL("filename",A1))+1,FIND(")",CELL("filename",A1))-1-FIND("(",CELL("filename",A1)))
ทวนเรื่อง MID ให้นิดนึง
=MID(text,start_num,num_chars) มีความสามารถในการดึงบางส่วนของข้อความ text ออกมาโดยเริ่มต้นที่ start_num เป็นจำนวนตัวอักษรเท่ากับ num_chars
- text คือ CELL(“filename”,A1)
- start_num คือ FIND(“(“,CELL(“filename”,A1))+1 ซึ่งเราหาตำแหน่งของเครื่องหมาย ( นั่นเอง
- num_chars หลักการเกิดจากการหาตำแหน่ง ) ลบด้วยตำแหน่ง ( แต่มี adj นิดหน่อย สูตรคือ FIND(“)”,CELL(“filename”,A1))-1-FIND(“(“,CELL(“filename”,A1))
ซึ่งผมมีการเขียนดักด้วย IFERROR ไว้ว่า ถ้าไม่เจอวงเล็บให้ถือว่าได้เลข 1 ไปซะ มันก็เลยซับซ้อนขึ้นอีกนิดนึง
วิธีที่ผมทำนี้ถ้าสร้างเอกสารคนละกลุ่มกัน เช่น ข้างหน้าเป็น INV กับ PO ให้รันแยกกัน โดยเลขจะ Run แยกของใครของมันในไฟล์เดียวกันได้เลยครับ (เพราะมันดูจากเลขในวงเล็บนั่นเอง)
อีกวิธีนึงที่ง่ายมากๆ
คุณ Bo แห่ง Excel Wizard แนะนำวิธีนี้มาครับ ใส่สูตรนี้ลงไปในช่องเขียวได้เลย
=M3&TEXT(M2+SHEET(I6),REPT("0",M4))แบบนี้ก็ง่ายดีใช้ SHEET เพื่อหาลำดับ Sheet ที่สร้างได้เลย ตอนแรกที่ทำบทความผมคิดไม่ถึงว่ามีฟังก์ชัน SHEET ให้ใช้ด้วย (แต่วิธีนี้ถ้าสร้างจากเอกสารคนละกลุ่มกันต้องปรับเลข Adjust ให้เหมาะสมอีกที)
และนี่ก็คือการสร้างเลข Running เอกสาร โดยไม่ต้องใช้ VBA ครับ ใช้ได้ผลยังไงมาบอกกันด้วยนะ ถ้าชอบช่วยกด Like กด Share ให้หน่อยนะครับผม ^^
