Snasui Excel Forum

เนื่องจากระบบ back office อยู่ในขั้นตอนการพัฒนาเพิ่มเติม ทำให้ต้องมาเปิดบิล manual แต่เมื่อเปิดบิลแล้ว เกิดปัญหาในเรื่องการ control bill จึงอยากจะทำการอ้างอิงเลขที่บิลที่มีอยู่ในระบบ จึงอยากรบกวนท่านผู้รู้ทุกท่าน ดังนี้

1. ต้องการแสดงเลขที่ inv. ในระบบทั้งหมด ซึ่งข้อมูลจะถูก export จากระบบออกมา เป็น excel และอาจจะมีข้อมูลที่ซ้ำกันมากกว่า 2 record โดยใช้รหัสลูกค้าเป็น reference key

2. ต้องการรันเลขที่บิลที่เปิดโดย excel โดยอัตโนมัติ เป็น "yymm" ตามด้วย "000" เช่น EW18070001 (โดยที่ EW เป็นหมวดของบิลที่เปิดค่ะ)

3. ถ้าต้องการทำปุ่มบันทึก หรือ สั่งพิมพ์ โดยใช้ macro สามารถทำได้ไหมคะ

อ่านแล้วไม่เข้าใจครับ

กรุณาอธิบายว่าต้องการให้ไฟล์ไหน ชีตไหน มีค่าเป็นเท่าใด ด้วยเงื่อนไขใด กรอกค่าที่ถูกต้องประกอบเข้ามาด้วยจะได้สะดวกในการทำความเข้าใจครับ

หากเขียนไว้แล้วให้แจ้งมาโดยละเอียดว่าเขียนไว้ตรงไฟล์ไหน ชีตไหน เซลล์ไหน ฯลฯ

ขอโทษที่อธิบายไม่ชัดเจนนะคะ ขออนุญาตอธิบายเพิ่มเติม ดังนี้ค่ะ

1. ต้องการให้แสดงเลขที่บิลอ้างอิงในระบบ จากไฟล์ "eWallet" (ที่ export มา) บนใบเสร็จรับเงิน/ใบกำกับภาษี (ไฟล์"ใบเสร็จ")
ตัวอย่าง เมื่อคีย์รหัสลูกค้าที่ช่อง AD9 ที่ไฟล์ใบเสร็จ ให้แสดงเลขที่บิลอ้างอิงในระบบในช่อง K15 เช่น คีย์รหัสลูกค้า 00001 เลขที่บิลอ้างอิงในระบบ(ทั้งหมด) คือ E18070001,02,05,08,10,11,12,13,14 และ E18070015 (รวม 10 บิล) ตามรูป 01.jpg , 02.jpg และ 03.jpg

2. ต้องการรันเลขที่ใบเสร็จ(ใหม่) จากไฟล์"ใบเสร็จ" ในช่อง AE5 โดยมี format ดังนี้ EW18070001 (EW+yymm+000x) ตามรูป 01.jpg

3. ต้องการสร้างปุ่ม "บันทึก" ในหน้า "ใบเสร็จรับเงิน/ใบกำกับภาษี" เพื่อเก็บเป็นข้อมูล โดยให้แสดง วันที่ซื้อ,เลขที่บิล,จำนวนเงิน,วิธีการชำระเงิน(เงินสด/เงินโอน/บัตรเครดิต/บัตรเดบิต) อ้างอิง format table เดิมจากไฟล์ "eWallet" ตามรูป 03.jpg และปุ่มคำสั่ง "พิมพ์" ค่ะ

ผมจะตอบเป็นแนวทางการใช้สูตรเป็นหลักนะครับ

การใช้สูตรให้ Save file eWallet.xls เป็น .xlsx เสียก่อนเพื่อจะใช้ฟังก์ชั่น Iferror ได้ และต้องเปิดไฟล์ eWallet.xlsx และ ใบเสร็จ.xlsx ไว้พร้อมกัน

ในสูตรด้านล่างผมจะอ้างถึงไฟล์ eWallet.xls ไปก่อนครับ

1. ที่ไฟล์ eWallet.xls เซลล์ N1 คีย์คำว่า Bill เพื่อเป็นหัวคอลัมน์
2. ที่เซลล์ N2 คีย์สูตร
   =IFERROR(LOOKUP(2,1/((C$1:C1=C2)*(C$1:C1=[ใบเสร็จ.xlsx]ใบเสร็จรับเงิน!$AD$9)),N$1:N1)&","&B2,B2)
   Enter > Copy ลงด้านล่าง
3. ที่ไฟล์ ใบเสร็จ.xlsx ที่เซลล์ K15 คีย์
   =LEFT(LOOKUP(2,1/([eWallet.xls]รายงานe!$C$2:$C$18=$AD$9),[eWallet.xls]รายงานe!$N$2:$N$18),10)&SUBSTITUTE(MID(LOOKUP(2,1/([eWallet.xls]รายงานe!$C$2:$C$18=$AD$9),[eWallet.xls]รายงานe!$N$2:$N$18),11,255),LEFT(LOOKUP(2,1/([eWallet.xls]รายงานe!$C$2:$C$18=$AD$9),[eWallet.xls]รายงานe!$N$2:$N$18),7),"")
   Enter

ในส่วนของการ Run ใบเสร็จ จะต้องมีใบเสร็จก่อนหน้าออกมาก่อนเพื่อจะได้ Run ต่อจากนั้น โดยจะต้องระบุมาว่าใบเสร็จก่อนหน้านั้นอยู่ใน ไฟล์ไหน ชีตไหน คอลัมน์ไหน หากยังไม่มีต้องสร้างขึ้นมาให้เรียบร้อย

ในกรณีของการสร้างปุ่มเป็นการใช้ VBA จำเป็นต้องเขียนมาเองก่อนเสมอตามกฎการใช้บอร์ดข้อ 5 ด้านบน ติดตรงไหนค่อยถามกันต่อครับ

ืทำตามที่อาจารย์แนะนำได้แล้วค่ะ ขอบคุณค่ะอาจารย์ ในส่วนของการรันเลขที่บิล หนูลองเขียนสูตรเองเบื้องต้น แต่ output ที่ได้ยังไม่ตรงตามความต้องการ จึงอยากขอความช่วยเหลือค่ะ

1. จากรูปใบเสร็จรับเงิน output เลขที่ใบเสร็จรับเงิน ที่ได้คือ 1, 2, 3... และได้ทำการปรับ format cell custom นิดหน่อย จึงได้ output ตามภาพ แต่ Output ที่ต้องการจริงๆ ก็คือ ED0001807000001 ซึ่ง format ของเลขที่ใบเสร็จจะเป็นดังนี้ คือ ED(หมวดบิล) ตามด้วย(รหัส)สาขา เช่น สนง.ใหญ่ คือ "000" ตามด้วย yymm (ปีเดือน) และเลขที่บิลที่จะรันขึ้นเรื่อยๆ (6 digit) => ED+BranchID+yymm+billNo (ตามรูป)

2. ในส่วน "เลขที่อ้างอิงในระบบ" ในช่อง K15 ถ้าหากต้องการให้ output ที่แสดงขึ้นบรรทัดใหม่ ต้องทำอย่างไรคะ เพราะในกรณีที่อาจจะมีเลขที่อ้างอิงในระบบมาก จนทำให้ข้อความยาวจนเกินไป หรือหายไปค่ะ (หนูลองทำดูแล้ว แต่ทำได้แค่ปรับเพิ่ม/ลด digit เท่านั้นเองค่ะ)

ขอบพระคุณค่ะ

ตัวอย่างสูตรใน AD5 ครับ

="ED0001807"&TEXT(LOOKUP(9.99999999999999E+307,บันทึก!C:C)+1,"000000")

Enter เป็นการแสดงเลขที่บิลถัดไปล่วงหน้าไว้ให้

ในส่วนของการขึ้นบรรทัดใหม่อาจใช้การกำหนดเป็น Wrap Text ร่วมกับการ Merge Cell ครับ

ขอบพระคุณค่ะอาจารย์ ลองตามสูตรตัวอย่างได้แล้วค่ะ

แต่ขอรบกวนสอบถามเป็นความรู้เพิ่มเติมค่ะ
จากสูตร ="ED0001807"&TEXT(LOOKUP(9.99999999999999E+307,บันทึก!C:C)+1,"000000")

1. ถ้าขึ้นเดือนหรือปีใหม่ เช่น ปี2018 เดือน8 เลขที่ใบเสร็จจะเปลี่ยนเป็น ED000180800000x หรือเปล่าคะ
(ตามความเข้าใจ หลังเครื่องหมายดับเบิ้ลโขดตัวนี้ข้อมูลจะเป็น text นั่นหมายความว่า ปีและเดือนจะไม่เปลี่ยนอัติโนมัติ ถ้าจะทำให้เปลี่ยนอัตโนมัติทำได้หรือเปล่าคะ)
2. ถ้าเปลี่ยนสาขาอื่นที่ไม่ใช่ 000 สนง.ใหญ่ หลัง ED เลขที่สาขาจะเปลี่ยนไปด้วยมั๊ยคะ เช่น สาขาอื่นๆ รหัสสาขา "001" (ช่อง AD7 ไฟล์ใบเสร็จ)

เพิ่มเติมค่ะ : อาจารย์คะ หนูลองปรับสูตรของอาจารย์ (ดูจากกระทู้เก่าๆ https://www.snasui.com/viewtopic.php?t=7560) เป็น

="EW000"&YEAR(TODAY())&MONTH(TODAY())&TEXT(LOOKUP(9.99999999999999E+307,บันทึก!C:C)+1,"000000")

ผลลัพธ์ที่ได้ จาก EW0001807000006 เป็น EW00020187000006 แต่ถ้าหนูต้องการให้ปีแสดงแค่ 2 ตัวหลัง คือ
ปี2018 เป็น 18 และเดือน จาก 7 เป็น 07 ต้องแก้อย่างไรคะ

ขอความกรุณาด้วยค่ะ
อาจจะไม่เก่ง excel มาก แต่จะพยายามค่ะ
ขอบพระคุณค่ะ

premNaka wrote: Mon Jul 23, 2018 11:09 am 1. ถ้าขึ้นเดือนหรือปีใหม่ เช่น ปี2018 เดือน8 เลขที่ใบเสร็จจะเปลี่ยนเป็น ED000180800000x หรือเปล่าคะ

ไม่เปลี่ยนให้ครับ

premNaka wrote: Mon Jul 23, 2018 11:09 am ตามความเข้าใจ หลังเครื่องหมายดับเบิ้ลโขดตัวนี้ข้อมูลจะเป็น text นั่นหมายความว่า ปีและเดือนจะไม่เปลี่ยนอัติโนมัติ

เข้าใจถูกต้องแล้วครับ

premNaka wrote: Mon Jul 23, 2018 11:09 am ถ้าจะทำให้เปลี่ยนอัตโนมัติทำได้หรือเปล่าคะ

ทำได้ครับ การจะทำเช่นนั้นต้องใช้วันเดือนปีที่เป็นปัจจุบันเข้าไปช่วย เช่นฟังก์ชั่น Today() เป็นต้นครับ

premNaka wrote: Mon Jul 23, 2018 11:09 am ถ้าเปลี่ยนสาขาอื่นที่ไม่ใช่ 000 สนง.ใหญ่ หลัง ED เลขที่สาขาจะเปลี่ยนไปด้วยมั๊ยคะ

ไม่เปลี่ยนครับ การจะเปลี่ยนให้ได้จะต้องมีค่าใดค่าหนึ่งเป็นอย่างน้อยให้โปรแกรมใข้ตรวจสอบว่าจะเป็นสานั้นสาขานี้ โปรแกรมไม่สามารถทราบได้เองครับ

premNaka wrote: Mon Jul 23, 2018 11:09 am เพิ่มเติมค่ะ : อาจารย์คะ หนูลองปรับสูตรของอาจารย์ (ดูจากกระทู้เก่าๆ https://www.snasui.com/viewtopic.php?t=7560) เป็น

="EW000"&YEAR(TODAY())&MONTH(TODAY())&TEXT(LOOKUP(9.99999999999999E+307,บันทึก!C:C)+1,"000000")

ผลลัพธ์ที่ได้ จาก EW0001807000006 เป็น EW00020187000006 แต่ถ้าหนูต้องการให้ปีแสดงแค่ 2 ตัวหลัง คือ
ปี2018 เป็น 18 และเดือน จาก 7 เป็น 07 ต้องแก้อย่างไรคะ

ปรับเป็นด้านล่างครับ

=Text(Today(),"""EW000""YYMM")&TEXT(LOOKUP(9.99999999999999E+307,บันทึก!C:C)+1,"000000")

ปรับเป็นสูตรตามอาจารย์ได้แล้วค่ะ ขอบคุณค่ะ

ขอรบกวนเพิ่มเติมค่ะ ช่วยแนะนำสูตร .. การเปลี่ยนสาขาในเลขที่บิลในกรณีเป็นสาขาอื่นที่ไม่ใช่สนง.ใหญ่ ให้ดูหน่อยค่ะ

ขอบพระคุณค่ะ

ตามภาพที่จับมานั้น ข้อความที่ผมเขียนแจ้งไปชัดเจนแล้ว อ่านแล้วเข้าใจว่าอย่างไร ไม่เข้าใจข้อความใดครับ