[101] "บิล123" โปรแกรมขายหน้าร้าน โดยใช้ Excel

โปรแกรมหน้าร้าน "บิล123" เป็นโปรแกรมที่เขียนด้วย VBA บน Excel ครับ ใช้ Excel sheet เป็นหน้าจอในการรับข้อมูลการขายสินค้าเลย ไม่ได้สร้าง Form พิเศษแต่อย่างใด สิ่งที่หวังไว้คือ เป็นโปรแกรมที่ใช้งานง่าย ถึง ง่ายที่สุด โดยเหมาะสำหรับคนที่คุ้นเคยโปรแกรม Excel อยู่แล้วครับ ถ้าใครใช้ Excel ไม่เป็นไม่แนะนำให้เอาไปใช้จริง ทำไมเดี๋ยวตอนท้ายจะบอกเหตุให้ฟังครับ

ตอนนี้โปรแกรมออกรุ่นใหม่ Version 2 สามารถตามไปดูและใช้งานแทนรุ่นแรกได้ที่
https://charin-nawaritloha.github.io/excel-vba-notes/p/bill123.html




สำหรับโปรแกรมนี้ตั้งใจจะสร้างให้เสร็จภายในเวลาไม่เกิน 1 วัน ซึ่งจากการทำงานจริงพบว่าเกินไปประมาณ 3 ชั่วโมง ปัญหาเกิดจากการจัดวางหน้าจอไม่ถูกใจ เลยจัดใหม่ๆๆๆๆ ทำให้เสียเวลา แต่ถ้ามีการออกแบบไว้ชัดเจนแล้วคิดว่า 1 วันก็ไม่น่าจะเกิน

แผ่นงานสำหรับใส่ข้อมูลการขายหน้าร้าน บิล123

อธิบายโครงสร้างของ บิล123 กันหน่อย เมื่อเปิดไฟล์ขึ้นมาจะพบว่ามันมี 3 แผ่นงาน ได้แก่

1. form ← กรอกรายการขายที่แผนงานนี้ จะอธิบายภายหลังครับ
2. master
3. config

แผนงาน form master และ config

เริ่มต้นใช้งาน

ใส่ข้อมูลสินค้า

ในการเริ่มใช้งานโปรแกรม สิ่งแรกคือ เราต้องมีข้อมูลสินค้าที่จะขายกัน ให้ดูที่แผ่นงาน master ครับ (ย่อมาจาก master data) โดยข้อมูลที่จำเป็นต้องการได้แก่

• Item ID รหัสสินค้าที่ไม่ซ้ำกัน อาจเอามาจากบาร์โค้ดของสินค้า หรือตั้งเอง
• Title ชื่อสินค้า
• Price ราคาขาย
• Cost ต้นทุน (ไม่ใส่ก็ได้)

ใส่ข้อมูลสินค้าในแผนงาน master

ในช่อง  Item ID จะมีการใส่รูปแบบแบบมีเงื่อนไขไว้ว่า หากมีรหัสซ้ำจะขึ้นเป็นสีแดง (ในภาพรหัส 1111 ซ้ำ) ถ้าใส่ซ้ำจะเกิดอะไรขึ้น ผมไม่ได้เขียนให้มันไม่ยอมทำงานเวลาซ้ำ ดังนั้นหากใส่รหัสที่ซ้ำกัน การดึงข้อมูลจะผิดพลาดครับ

สำหรับส่วนลด ให้ใส่ราคาเป็นค่าลบไว้ (ตัวอย่างคือ รหัส 0000 มีค่าราคาเป็น -1.00)

คำแนะนำสุดท้ายสำหรับการตั้งรหัสคือ ไม่แนะนำให้ใช้รหัสที่ขึ้นต้นด้วยเลข 0 เพราะเมื่อนำข้อมูลที่เก็บมาเปิดด้วยโปรแกรม Excel มันจะตีความหมายเป็นตัวเลขได้ ซึ่งในกรณีที่รหัสสินค้าเป็นตัวเลขทั้งหมด เช่น 01234 เมื่อดึงข้อมูลการขายมาแก้ไขและบันทึกซ้ำ อาจทำให้รูปแบบข้อมูลเก็บเป็น 1234 แทน ถ้าเลี่ยงก็จะดีมาก


ตั้งค่าการทำงานโปรแกรม

ในดูในแผ่นงาน config จะพบว่าสามารถกำหนดค่าของโปรแกรมได้ดังนี้

• อายุ
• ภาษีมูลค่าเพิ่ม
• วิธีคิดภาษี
• รวม Cost ในบิล

อายุ จะเก็บรายการที่จะให้เลือกช่วงอายุของลูกค้าที่มาซื้อของ สำหรับเอาไว้วิเคราะห์ข้อมูลสถิติได้ ผู้ใช้สามารถใส่รายการได้จนถึงแถวที่ 11 (พื้นที่สีชมพูทั้งหมด)

ส่วนต่อมาคือเรื่องของภาษีมูลค่าเพิ่ม สามารถกำหนดเป็น % ได้ตามการใช้งาน ในตัวอย่างกำหนดไว้ 7% จากนั้นให้กำหนดวิธีคิดภาษี หากกำหนดเป็น 1 คือ ราคาขายรวมกับ VAT แล้ว ถ้าจะให้คิดเพิ่มจากราคาสินค้าให้กำหนดเป็น 2 (ดูรายละเอียดจาก comment)

ส่วนสุดท้ายคือ รวม Cost ในบิล กำหนดว่าให้บันทึกต้นทุนสินค้าไปกับข้อมูลการขายด้วยหรือไม่ ถ้าไม่รวมให้กำหนดเป็น 0 ถ้ารวมให้กำหนดเป็น 1 สาเหตุที่ต้องมีเนื่องจากต้นทุนสินค้าอาจแปรผันไปตามช่วงเวลา ดังนั้นหากต้องการวิเคราะห์ margin จำเป็นต้องเก็บเอาไว้ แต่ถ้าไม่ใช้ก็ไม่ต้องสนใจ

การตั้งค่าของ บิล123 ในแผ่นงาน config

สำหรับข้อมูลผู้ซื้อ ใช้สำหรับอ้างอิงในโปรแกรม ไม่ต้องไปยุ่งกับมัน (ยกเว้นจะแก้ไขโปรแกรม)


สุดท้าย เมื่อกำหนดค่าต่าง ๆ รวมถึงใส่ข้อมูลสินค้าแล้ว อย่าลืม SAVE นะครับ เพราะเวลาทำการขาย โปรแกรมจะบันทึกรายการที่ขายไว้ไฟล์อื่น ๆ ที่ SAVE อัตโนมัติ ส่วนไฟล์ bill123.xlsm ผู้ใช้ต้อง SAVE เอง



เริ่มขายสินค้าได้แล้ว

เมื่อกำหนดค่าต่าง ๆ เรียบร้อยแล้ว ก็เริ่มขายของกันได้ วิธีใช้คือ

1. เลือกแผ่นงาน form
2. เลือกที่ช่องผู้ซื่อ ทำการใส่ชื่อผู้ซื้อ (ถ้าต้องการ)
3. ป้อนรหัสสินค้า สามารถป้อนด้วยแป้นพิมพ์โดยตรง หรือ ยิงจากเครื่องอ่านบาร์โค้ด แล้วแต่สะดวก
4. กด Enter หรือ Tab เพื่อรับข้อมูล
5. ทำซ้ำไปเรื่อย ๆ จนครบทุกรายการ
6. คลิกที่ปุ่ม บันทึก (หรือกด Ctrl+B) เพื่อบันทึกรายการขาย

หน้าจอขณะทำการขายสินค้า

หากต้องการบันทึกรายละเอียดของลูกค้า ก่อนบันทึก อย่าลืมเลือกเพศ และ อายุ ให้เรียบร้อย แล้วค่อยปุ่มบันทึก

เลือก เพศ และ อายุ ของลูกค้า

กดปุ่ม บันทึก หรือ Ctrl+B เพื่อบันทึกรายการขาย

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

แจ้งผลการบันทึก

สิ่งที่ควรทราบเกี่ยวกับการกรอกรายการขายเพิ่มเติม

• หากต้องการทำรายการรหัสเดียว แต่จำนวนมากกว่า 1 ชิ้น ให้แก้ไขค่าในช่องจำนวน (ค่าปกติเป็น 1.00) เป็นจำนวนที่ต้องการ แล้วจึงใส่รหัสสินค้าตามปกติ
• ถ้าใส่รหัสสินค้าซ้ำ โปรแกรมจะไปเพิ่มจำนวนแทน
• ถ้าต้องการลบรายการสินค้าที่ใส่ผิด ให้ไปที่รหัสที่ใส่ผิด แล้วลบรหัสทิ้ง
• ถ้าต้องการยกเลิกรายการทั้งหมด ให้คลิกที่ปุ่ม ยกเลิก
• หากบันทึกข้อมูลไปเรียบร้อยแล้ว (ได้หมายเลขบิลมาแล้ว) ต้องการจะยกเลิก ให้ไปลบรายการในไฟล์ .csv ในโฟลเดอร์ data ได้โดยตรง (อย่าลืม Save ด้วย)

ข้อมูลการขายเก็บไว้ที่ไหน

เมื่อเราทำการกดปุ่มบันทึกแล้ว โปรแกรมจะทำการสร้างโฟลเดอร์ data ในโฟลเดอร์ที่อยู่ที่เดียวกับไฟล์ bill123.xlsm ที่ทำงานอยู่

โฟลเดอร์ที่เก็บไฟล์ bill123.xlsm จะโฟลเดอร์ชื่อ data สำหรับเก็บรายการขาย

เมื่อคลิกเข้าไปดูโฟลเดอร์ data จะพบไฟล์ .csv และ .txt ถ้าจะ backup ข้อมูล ก็ให้เก็บข้อมูลในโฟลเดอร์ data เอาไว้ให้ดี เพราะข้อมูลสินค้าที่ขายได้จะอยู่ในนี้หมด

ไฟล์เก็บข้อมูลการขาย

อธิบายการเก็บข้อมูล

• ข้อมูลรายการขาย จะเก็บไว้ในไฟล์ Excel CSV ซึ่งเป็น Text file ที่เรียบง่ายที่สุด สามารถเปิดได้จาก Excel หรือ Notepad ก็ได้ โดยจะมีชื่อไฟล์เป็น bill123_ปี-เดือน.csv ดังนั้น 1 ปี จะมี 12 ไฟล์
• ข้อมูลการขายที่เก็บจะเก็บในรูปแบบที่รวมส่วนหัวบิลและรายการขายเข้าด้วยกัน ถ้าในภาษาการเก็บข้อมูลจะเรียกว่าเก็บแบบไม่ Normalize ครับ ซึ่งข้อดีคือ เวลาใช้งานไม่ต้องมา join ข้อมูล รูปแบบการไม่ซับซ้อน แต่จะเปลืองพื้นที่เก็บข้อมูลมากกว่าแบบ Normalize
• ไฟล์สำหรับเก็บหมายเลขบิล bill123_id.txt จะเก็บหมายเลขลำดับถัดไปของบิลที่จะบันทึก หากต้องการเปลี่ยนลำดับหมายเลขให้แก้ไขไฟล์นี้ครับ

ข้อมูลดิบที่ได้จากการขายสินค้าในแต่ละครั้ง จะถูกเก็บไว้ในไฟล์ .csv

การพิมพ์ใบเสร็จ
เนื่องจากการพิมพ์ใบเสร็จออกกระดาษมีข้อจำกัดเรื่องรูปแบบการพิมพ์ เลยตัดส่วนนี้ออกไป เพราะเป็นส่วนที่เสียเวลามาก (+_+) และจำเป็นต้องออกแบบให้ตรงกับขนาดกระดาษ และ ชนิดของเครื่องพิมพ์ที่ใช้ด้วย (-ω-｀)

ดังนั้นในส่วนนี้หากใครอยากได้คงต้องไปทำเพิ่มเติมเอง ผมได้สร้าง Sub PrintReceipt() ซึ่งจะถูกเรียกทุกครั้งที่มีการบันทึกการขาย ตัวแปรจะเป็นข้อมูลของหัวบิล และ รายการขาย

พิมพ์ใบเสร็จ โดยแก้ไข Sub PrintReceipt()

การนำข้อมูลการขายมาประมวลผลและทำรายงาน

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

ที่กล่าวมาทั้งหมด โปรแกรม Excel สามารถทำได้อยู่แล้ว แค่รู้วิธีในการเข้าข้อมูลดิบที่ได้จากการขายมาประมวลผลเท่านั้น ซึ่งใครที่ใช้โปรแกรม Excel เป็นอยู่แล้วน่าจะเข้าใจวิธีทำรายงานเหล่านี้ได้ไม่ยาก ดังนั้นจะเห็นว่า "บิล123" ไม่มีตัวเลือกให้สร้างรายงานเลย เพราะ Excel ทำได้อยู่แล้ว ดังนั้นถ้าใช้ Excel ไม่เป็นแนะนำว่าไปใช้โปรแกรมสำเร็จรูปตามท้องตลาดดีกว่าครับ


ทำ PivotTable

PivotTable เป็นเครื่องมือช่วยสรุปข้อมูลได้หลากหลายมาก ตามแล้วแต่เราจะประยุกต์ใช้ เรามาเริ่มลองทำกันแบบง่าย ๆ ดู เริ่มจากเลือกคอลัมน์ A-M คลิกที่ Insert → PrivotTable

เลือกข้อมูลเพื่อสร้าง PivotTable

เมื่อแทรก PivotTable ทำการแทรก fields ที่เราต้องการ ตัวอย่างแรกจะลองสรุปผลรวมยอดขายโดยแบ่งตามบิลครับ

• ใส่ฟิลด์ บิลหมายเลข ใส่ใน Row Labels
• ใส่ฟิลด์ตัวเลขที่ต้องการหาผลรวมใน Values โดยให้เลือกเป็นแบบ Sum

แล้วก็ลองปรับแต่งรูปแบบในเมนู PivotTable Tools → Design เพื่อให้ได้ตารางในแบบเราต้องการ ในตัวอย่างจะกรองเอา (blank) ออกจาก บิลหมายเลข จะได้รายงานดังนี้

PivotTable ที่แสดงผลรวมของรายการขาย โดยแบ่งตามหมายเลขบิล

หากเราต้องการจะสรุปตามกลุ่มข้อมูลที่สนใจ เราก็ทำการใส่ฟิลด์เหล่านั้นในช่อง Row Labels จากตัวอย่าง จะสรุปโดยแบ่งตามเพศ → รหัสสินค้า → รายละเอียดสินค้า

สรุปตามกลุ่มที่สนใจได้

การเพิ่มรายละเอียดในการทำรายงาน

ในกรณีที่เราต้องการสรุปสถิติที่ข้อมูลดิบที่โปรแกรม บิล123 ไม่มีให้ เราเพียงใส่คอลัมน์เพิ่มเติมเข้าไปได้ เช่น หากต้องการสรุปผลตามเวลาที่ซื้อของ เพื่ออยากทราบว่าช่วงเวลากี่โมงมีการซื้อของมากน้อยแค่ไหน

ในภาพเป็นการใส่ฟังก์ชั่น HOUR() เพื่อดึงเอาเฉพาะชั่วโมงของเวลาที่ทำการขาย เพื่อนำมาจัดกลุ่มสรุปผล

เพิ่มคอลัมน์ ช่วงเวลาที่มาซื้อของ เพื่อสรุปตามเวลา

การหา Subtotal

ใน Excel ยังมีเครื่องมืออีกหลายตัวที่จะช่วยให้เรามองภาพรวมของการขายได้อีกมาก แต่ไม่ขอลงรายละเอียดเนื่องจากจะกลายเป็นการสอนวิธีใช้ Excel ไปแทน (>ω<＊)ﾉﾉ ซึ่งจะยาวไม่จบแค่นี้ครับ

การใส่ Subtotal โดยรวมตามหมายเลขบิล

รายงานที่ได้จากคำสั่ง Subtotal

สุดท้ายที่จะฝากเตือนคือ เมื่อเปิดไฟล์ .csv เพื่อนำมาสรุปทำรายงานแล้ว และอยากจะเก็บรายงานไว้ โปรด Save As เป็นไฟล์ Excel อื่น เพราะถ้าหากไป Save ทับ นอกจากรายงานที่ทำจะไม่ถูกเก็บแล้วยังทำให้ข้อมูลการขายเสียหายไปด้วย

ส่งท้าย กว่าจะเขียนอธิบายวิธีใช้ บิล123 เสร็จ ใช้เวลาทำไปเกือบ 3 วัน นานกว่าเขียนโปรแกรมอีก เฮ้อ (-ω-｀)


ตอนนี้โปรแกรมออกรุ่นใหม่ Version 2 สามารถตามไปดูและใช้งานแทนรุ่นแรกได้ที่
https://charin-nawaritloha.github.io/excel-vba-notes/p/bill123.html


ดาวน์โหลด บิล123

บิล123 โดย Charin Nawaritloha อนุญาตให้ใช้ได้ตาม
  สัญญาอนุญาตของครีเอทีฟคอมมอนส์แบบ แสดงที่มา-อนุญาตแบบเดียวกัน 3.0 ประเทศไทย.
อยู่บนพื้นฐานของงาน บิล123.

ข้อตกลงในการดาวน์โหลด บิล123 ไปใช้งาน

1. ใช้ฟรี  โหลดฟรี ไม่เสียเงิน
2. แจกจ่ายได้ แต่ต้องไม่ลบ ลิขสิทธิ์ และชื่อผู้พัฒนาโปรแกรม
3. สามารถแก้ไข และ ขายโปรแกรมที่แก้ไขได้ โดยต้องแสดงที่มาของบิล123

ดาวน์โหลดโปรแกรม โดยยอมรับเงื่อนไขการใช้งานทั้ง ข้อ

ตอนนี้โปรแกรมออกรุ่นใหม่ Version 2 สามารถตามไปดูและใช้งานแทนรุ่นแรกได้ที่
https://charin-nawaritloha.github.io/excel-vba-notes/p/bill123.html