สร้างใบ Invoice อัตโนมัติด้วย Google Sheets โดยไม่ต้องเขียน script

อย่าดูถูกพลังของ Google Sheets ถ้าคุณยังไม่ได้ลองใช้ฟังก์ชัน QUERY ด้วยฟังก์ชันนี้ คุณอาจจะเมิน VLOOKUP เพื่อนเก่าของคุณไปเลยก็ได้ เดี๋ยววันนี้เรามาลองใช้ QUERY ช่วยทำให้เราดึงข้อมูลจากหลายๆ sheet มาแสดงในหน้าเดียวเพื่อสร้างใบ Invoice แบบง่ายๆโดยไม่ต้องพึ่ง script ใดๆเพิ่มครับ

สร้าง Sheet ใหม่จาก template #

เริ่มต้นเรามาสร้าง Sheet ใหม่กันก่อนครับ สำหรับใครที่มี Google Account อยู่แล้วก็เข้าไปที่ Google Drive หรือ Google Sheets ได้เลยครับ ตอนที่เราจะสร้าง Sheets ใหม่ให้เลือก “From a template”

ในหน้า template จะมีให้เราเลือกใช้อยู่หลายอันครับ แต่วันนี้เราจะมาทำ Invoice ดังนั้นเราก็ต้องเลือก Invoice

วางโครสร้างข้อมูล #

โครงสร้างที่ผมจะใช้ผมจะอิงมาจาก Template ที่ Google Sheets มีให้เรานะครับ ซึ่งหลักๆใน Invoice หนึ่งใบจะประกอบไปด้วย

1. ข้อมูลของเราที่เป็นผู้ออกเอกสาร (อันนี้ไม่มีการเปลี่ยนแปลง)
2. ข้อมูลลูกค้า
3. รายการสินค้าและราคา

เมื่อข้อ (1) ไม่มีการเปลี่ยนแปลงเราจึงไม่มีความจำเป็นจะต้องสร้าง sheet เพื่อเก็บข้อมูลของเราครับ แปะเอาไว้ใน Template เลยก็ได้ แต่ข้อ (2), (3) เราจำเป็นจะต้องสร้าง sheet ขึ้นมาเก็บข้อมูลพวกนี้ด้วย

สร้าง Database เล็กๆบน Sheets #

จาก template เราจะเห็นว่าลูกค้ากับรายการสินค้าเนี่ยเราต้องทำ sheet ของมันแยกไว้แน่ๆ แต่มีอีก sheet นึงที่ผมเพิ่มเข้าไปก็คือ Header ครับ เพื่อที่จะเอาไว้เก็บวันที่ต่างๆของใบ invoice หรือเรียกง่ายๆว่าเก็บข้อมูลของใบ invoice นั่นแหละ แปลว่าตอนนี้เราจะมี sheet ที่ต้องเพิ่มมาเป็น 3 ตารางที่มี field ประกอบดังนี้

- Header (กรอบสีเขียว)
  - invoice id
  - submited on
  - due date
  - customer id
  - project name
  - payable to
  - adjustments	note
- Customer (กรอบสีเหลือง)
  - customer id
  - name
  - company name
  - street address
  - district, province, zip
- Item (กรอบสีแดง)
  - item id
  - invoice id
  - description
  - quantity
  - unit price

ใครเห็นแล้วงงๆ ลองดู field ของผมเทียบกับ template ที่ google ให้มาครับ เพื่อให้ข้อมูลถูกกรอกได้สมบูรณ์ผมก็ลอกมาแทบทั้งหมดเลย สร้าง sheet กันเลยนะครับ

[DB] Header หัวใจของ Invoice ทุกๆใบ #

ข้อมูลหัวกระดาษใน Invoice จะถูกเก็บไว้ในนี้ แต่ข้อมูลของลูกค้าจะถูกเก็บไว้ในรูปแบบ id เพื่อให้เราโยงไปหา sheet Customer ได้นะครับ ตัวนี้ผมเพื่มความยากเข้าไปหน่อยนึงตรงที่ Invoice Id แทนที่จะเก็บเป็นตัวเลขตรงๆเช่น 0001, 0002, 0003 ผมจะเก็บแบบนี้ครับ

INVYYYYMM0000 ใช้จริงๆก็จะประมาณ INV2019080001

• INV หมายถึงเอกสารนี้คือ invoice นะ
• YYYY หมายถึงปี ค.ศ. ที่ออกเอกสาร
• MM หมายถึงเดือนที่ออกเอกสาร
• 0000 เลข 4 ตัวคือลำดับเอกสารที่ออกในเดือนนั้นๆ

เพื่อไม่ให้เกิดการผิดพลาดออกหมายเลขซ้ำกัน ผมเลยทำตัวช่วยสร้างหมายเลขในแถวที่ 1 (B2) ของ sheet [DB] Header ซะเลยโดยความยากคือการนับเอกสารที่ออกในเดือนนั้นๆครับ เช่น ณ วันที่จะออกเป็นเดือนสิงหาคม cell นี้ก็จะแสดงจำนวนเอกสารที่ออกในเดือนสิงหาคมเท่านั้น

อย่าพึ่งกลัวสูตรหาจำนวน invoice ในเดือนปัจจุบันนะ เดี๋ยวอธิบายก่อน หน้าตามันพอเว้นบรรทัดให้สวยงามก็จะประมาณนี้

=QUERY(A4:H, "
    select count(A)
    where
        B > date '"&TEXT(EOMONTH(TODAY(),-1)+1,"yyyy-mm-dd")&"' and
        B < date '"&TEXT(EOMONTH(TODAY(),0),"yyyy-mm-dd")&"'
        label count(A) ''
    ")

อธิบายกันก่อนเดี๋ยวงง

• A4:H คือ range ของข้อมูล Invoice ทั้งหมดของเราใน sheet นี้แหละครับ
• =TEXT(val, "format") อันนี้คือคำสั่งจัด format ตัวอักษรใน cell ครับว่าอยากให้แสดงออกมาแบบไหน
• =EOMONTH(DATE,amount) ตัวนี้ชื่อเต็มคือ End Of Month ครับ ผมใช้หาขอบเขตของเดือน ในตัวอย่างจะเห็นว่าผมใช้ TODAY() ก็เพื่อที่จะหาวันสิ้นสุดของเดือนนี้ครับ แต่ถ้าจะหาวันแรกของเดือนเขาไม่มีคำสั่งนะ ผมเลยใช้ท่าคือการ -1 เพื่อให้หาวันสุดท้ายของเดือนที่แล้ว หลังจากนั้นผมก็ +1 เพื่อที่จะได้วันแรกของเดือนปัจจุบันนั่นเอง
• =QUERY(Range, "Query String") อันนี้คือฟังก์ชันสารพัดประโยชน์ ผมสั่งให้มัน count(A) ก็คือนับ column แรกของ range มานะ โดยมีเงื่อนไขใน where คือ column ที่ 2 ซึ่งเก็บวันที่ออกเอกสารเนี่ย ต้องไม่มากกว่าหรือน้อยกว่าเดือนปัจจุบันเพราะเราต้องการนับรายการของเดือนนี้ label คือการสั่งสร้าง header column ใหม่บน cell ที่ทำ query ได้ ถ้าตามปกติไม่ใช้คำสั่ง label มันจะได้มา 2 cell แบบภาพด้านล่าง ซึ่งผมก็ให้เปลี่ยนเป็น blank text แทนตามสูตรด้านบนนั่นแหละ

หลังจากได้จำนวนแล้วผมก็เอามาประกอบร่างให้ได้ Invoice Id ตามที่ต้องการ ส่วนตัวเลข “0000” นำหน้าแบบนี้ใช้คำสั่ง =TEXT(1, "0000") จะได้ผลเป็น 0001 นะครับ และแล้วสูตรของหมายเลข Invoice ก็หน้าตาแบบนี้

="INV"&TEXT(TODAY(), "YYYYMM")&TEXT(B1+1,"0000")

สูตรนี้ทำให้เราไม่ต้องมานั่งนับเองครับว่าเดือนนี้ออกไปแล้วกี่ใบ ทุกครั้งที่จะออกใบใหม่เราก็แค่ copy จาก cell D1 แล้วไปวางที่แถวใหม่ได้ล่างได้เลยครับ

เราก็เริ่มกรอกข้อมูลใบ Invoice ของเราได้เลยครับ ถ้าระหว่างกรอกคุณสงสัยว่าแล้วไอ้ customer id มันมาจากไหน แปลว่าคุณสงสัยได้ถูกต้องแล้วครับ เพราะต่อไปเราจะมาสร้างข้อมูลของลูกค้าเพื่อให้มันมาโยงเข้ากับใบ Invoice ใบนี้นี่เอง

[DB] Customer ลูกค้าของเราเอามาไว้ตรงนี้ #

เพื่อให้ตาราง [DB] Header ไม่แน่นจนเกินไปผมก็แยกข้อมูลฝั่งลูกค้าที่ [DB] Customer ครับ ซึ่งมันจะมีประโยชน์ตรงที่ ถ้าเกิดอนาคต ลูกค้ารายเดิมกลับมาใช้บริการเรา เราไม่จำเป็นต้องสร้างใหม่ทุกครั้ง แค่ค้นหาจากชื่อเราก็เอา customer id ไปแปะลงใน [DB] Header ได้เลยไม่ต้องเสียเวลาพิมใหม่ โดยผมจะให้ความสำคัญกับ customer id นะครับ แน่นอนมันต้องไม่ซ้ำกันและผมจะเขียนสูตรเพื่อให้มันสร้างตัวเอง โดยที่เราไม่ต้องไปแตะต้อง column customer id อีกเลยหลังจากเขียนสูตรนี้

=ARRAYFORMULA(
    IF(
        B2:B<>"", "C"&TEXT(ROW(A2:A)-1,"0000"), ""
    )
)

อย่าพึ่งปิดนะ!! เดี๋ยวอธิบายให้เข้าใจก่อน

• =ARRAYFORMULA() ฟังก์ชันนี้ช่วยให้เราเขียนแค่แถวแรกแถวเดียว แต่ได้ผลลัพธ์ลงไปกี่แถวก็ได้ตามต้องการ สะดวกมากเราไม่ต้องมาคอยลากไปทีละ cell เพื่อให้สูตรมันตามไป เช่น ถ้าเราเขียนคำสั่งนี้ =ARRAYFORMULA(A1:A3+B1:B3) ไว้ที่ C1 เราจะได้ค่าของการบวกกันของ A1 + B1 ที่ C1 แล้วก็ไล่ไปตามลำดับจน C3 เป็นต้น

  

• =IF() อันนี้ใครใช้ MS Excel มาคงเคยผ่านหูผ่านตามาบ้าง มันคือการกำหนดเงื่อนไขในการใส่ค่าต่างๆของ cell นั้นๆ เช่น ถ้า =IF(A1>0, "ผ่าน", "ไม่ผ่าน") แปลว่า A1 มากกว่า 0 ให้ใส่แสดงคำว่า “ผ่าน” ถ้าไม่ใช่ก็แสดงคำว่า “ไม่ผ่าน” เมื่อรวมร่างกับ ARRAYFORMULA มันก็จะทำให้เราเขียน IF แค่บรรทัดเดียว แล้วคลุมไปทั้ง Sheet ก็ยังได้ เช่น =ARRAYFORMULA(IF(A1:A > 0,"ผ่าน","ไม่ผ่าน")) ในคำสั่งของผมจะเห็นว่าเงื่อนไขผมคือ B2:B<>"" อันนี้แปลว่า column B ต้องไม่ว่างเปล่านะ ต้องมีค่านะ ถึงจะใส่เลขที่ลงไป
• =ROW() ดึงค่าแถวของ cell นั้นๆขึ้นมา ผมเอาไว้เป็นลำดับของลูกค้านั่นเอง แต่ต้อง -1 เพราะตัด header ออกไป 1 แถวครับ

เพียงเท่านี้เมื่อเรากรอกข้อมูลชื่อลูกค้าใหม่ลงไป customer id ก็จะผุดขึ้นมาเองโดยที่เราไม่ต้องไปทำอะไรเลย

ได้ customer id แล้วก็อย่าลืมกลับไปเติมใน [DB] Header ด้วยนะครับ ต่อไปเราจะมาเพิ่มรายการกันบ้าง

[DB] Item รายการไหนๆก็รวมที่นี่ #

สำหรับ sheet นี้ก็เป็นอีกตัวที่สำคัญครับ เพราะถ้าขาดข้อมูลชุดนี้ไป เราก็ไม่รู้จะคิดเงินลูกค้ายังไง แต่ยินดีด้วยครับ sheet นี้ไม่มีสูตรใหม่ ใช้คล้ายๆกับ [DB] Item เลยแต่เปลี่ยนจาก C มาเป็น IT นำหน้า item id เท่านั้นเอง

สิ่งที่ควรระวังใน sheet นี้นะครับ คือ column invoice id ตรงนี้เราต้องใส่ให้ถูกนะครับ ไม่เช่นนั้นรายการของเราจะไปโผล่ใน Invoice ผิดใบนะครับ ถ้าไม่แน่ใจก็กลับไปที่ [DB] Header ก่อนแล้ว copy invoice id ของใบที่เราต้องการมาวางใส่เลยจะได้เป๊ะๆ มีแต่รายการก็ต้องวางให้ครบนะครับอย่างในภาพ ใบที่ 1 มี 3 รายการผมก็ใส่ตามให้ครบเลย ส่วนรายละเอียด จำนวน และราคาอันนี้ตามต้องการเลยครับ เสร็จแล้วนะ sheet นี้ เดี๋ยวต่อไปเราจะกลับไปสร้างใบ Invoice กันครับ

ปรับแต่ง Invoice ที่ template ทำมาให้ #

และแล้วก็ถึง sheet สุดท้ายที่เราจะต้องปรับแต่ง ซึ่งมันก็คือ sheet แรกที่เราได้มาจาก template นั่นเอง คราวนี้เราจะมาสร้างกลไกให้มันดึงข้อมูลจาก database เล็กๆของเรากันครับ

เริ่มจาก Invoice # ส่วนนี้เราจะทำให้ cell F12 มีตัวเลือกเป็น invoice id ที่เราสร้างไว้ใน [DB] Header ครับ เพื่อไม่ให้เราต้องสับสนหรือเสียเวลากรอก ผมคลิกขวาที่ cell F12 แล้วเลือก Data validation ครับ

ที่ Criteria ผมจะเลือก List from a range คือเราจะดึงข้อมูลมาจาก range ใน sheet ครับ สำหรับการกำหนด range ถ้าไม่อยากพิมพ์ก็กดที่รูปตารางเล็กๆขวามือสุดได้นะครับ หลังจากนั้นก็เปิด [DB] Header ขึ้นมาแล้วลากคลุมแถว A ที่เราเก็บ invoice id นั่นแหละครับ แต่สุดท้ายคือผมตัดเลขที่อยู่หลังสุดออก '[DB] Header'!A4:A ครับ หมายความว่าผมเอา A มาตั้งแต่ A4 จนถึงแถวสุดท้ายเลย

เรียบร้อยแล้วก็กด Save ครับ แล้วที่ cell ของเราก็จะมี dropdown เวลาที่คลิก ซึ่งมันก็จะแสดง invoice id ที่เราสร้างไปแล้วนั่นเอง

ได้ Invoice Id แล้ว ต่อไปเราจะดึงค่ามาแสดงทีละตัวครับ แต่เพื่อความสะดวก ผมจะเพิ่มแถวมา 1 แถวครับด้านบน ซึ่งเดี๋ยวจะซ่อนมันอีกที

หลังจากนั้นก็ใช้คำสั่ง =QUERY('[DB] Header'!A4:H,"select A,B,C,D,E,F,G where A = '"&F12&"'") ดึงเอาค่าของแถวที่มี invoice id ตรงกับ cell F12 ของเราครับ

ได้ข้อมูล header มาแล้วก็เหลือของ customer เพื่อความสะดวกผมจะเพิ่มอีกแถวด้านล่างแล้วใช่คำสั่ง

=QUERY('[DB] Customer'!A2:E,"select A,B,C,D,E where A = '"&D2&"'")

ซึ่ง D2 มันก็คือ customer id ใน [DB] Header นั่นแหละครับ เราจะหาลูกค้าที่มี id ตรงกับในใบ Invoice ของเรานั่นเอง

ได้ตรงนี้เราก็เอามาจีบใส่แต่ละช่องของเราแบบง่ายๆเลยครับ เช่น

• cell B14 ผมก็ใช้แค่ =B3 ก็จะได้ชื่อลูกค้ามาแปะ
• cell ไหนที่ต้องจัด format หน่อยเช่น Due date F17 ผมจะใช้ =TEXT(C2, "DD/MM/YYYY")
• cell B11 มีประโยคติดมาด้วยก็แปะไปในสูตรแบบนี้ได้ครับ ="Submitted on "&TEXT(B2, "DD/MM/YYYY") เครื่องหมาย & คือตัวเชื่อม text ครับ

ใส่ครบแล้วก็จะได้หน้าตาประมาณนี้ครับ

เพื่อความสวยงามผมก็จะซ่อน row ที่ 2-3 ไปครับ เวลาสั่งพิมพ์จะได้ไม่เห็นอะไรแปลกๆ

สุดท้ายเราจะมาใส่รายการกันครับ แต่เผื่ออนาคตผมจะเพิ่ม row ของ item ไว้เยอะๆหน่อย แล้ว hide เอาไว้ครับ หลังจากนั้นครับที่ B21 ผมก็จะดึงรายการมาแสดง

=QUERY('[DB] Item'!A2:E,"select C where B = '"&A2&"'")

เลือก column C จาก [DB] Item ครับเพราะมันคือ Description ที่ผมไม่เลือกมาหมดเลยทีเดียว C, D, E เพราะว่า template เขา merge cell ไว้ครับ ทำให้เวลาเราใช้คำสั่งแล้วมันจะแสดงแค่ description column เดียว ดังนั้นเราต้องทำแบบนี้อีก 2 column ครับ

สำหรับ Qty =QUERY('[DB] Item'!A2:E,"select D where B = '"&A2&"'")

สำหรับ Unit price =QUERY('[DB] Item'!A2:E,"select E where B = '"&A2&"'")

เสร็จแล้วจะได้ผลแบบนี้

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

เรียบร้อยครับ เพียงเท่านี้เราก็จะได้ Sheet ที่เอาไว้เก็บรวบรวมข้อมูลของ Invoice ของเรา รวมถึงลูกค้าและรายการซึ่งสามารถแก้ไขได้ โดยที่ไม่ต้องมาจัดหน้ากระดาษใหม่ หรือ save แยกไฟล์ไว้แล้วครับ เพียงแค่เราจัดการข้อมูลใน database เล็กๆของเราแบบนี้ เราจะสามารถเก็บ invoice ได้เป็นล้านรายการโดยไม่ต้องไปสร้างไฟล์ใหม่อีกครับ

เนื่องจากบทความนี้พยายามไม่ใช่การเขียน script เพื่อให้มือใหม่หรือคนที่เคยใช้ excel มาบ้างยังพอมองเห็นภาพและเข้าใจได้ทัน แต่ถ้าจะให้การ input ข้อมูลลง database ของเราสะดวกมากขึ้นเราสามารถใช้ Google Form หรือเขียน Script มาช่วยได้ครับ

สรุปสูตรที่เราเรียนรู้วันนี้กันครับ #

• =QUERY(range, query) คำสั่งดึงข้อมูลที่เราต้องการ เพียงแค่กำหนด range และเขียน query select xxx where xxx เราก็จะได้สิ่งที่ต้องการ อ่านง่ายและเข้าใจกว่า VLOOKUP แน่นอน (แต่บางกรณีก็ต้องใช้ VLOOKUP อยู่นะหัดไว้)
• =TEXT(val, "format") คำสั่งจัด format ของ text ใน cell ครับว่าอยากให้แสดงออกมาแบบไหนก็ได้ไม่ว่าจะเป็นรูปแบบตัวเลขหรือวันที่ก็ได้
• =EOMONTH(DATE,amount) คำสั่งหาวันสุดท้ายของเดือน
• =ARRAYFORMULA() คำสั่งที่จะช่วยทำซ้ำคำสั่งที่อยู่ด้านในอีกทีตาม range ที่กำหนด
• =IF() คำสั่งกำหนดเงื่อนไขในการใส่ค่าต่างๆของ cell นั้นๆ
• =ROW() ดึงค่าเลขที่แถวของ cell นั้นๆขึ้นมา

การเขียนโปรแกรมคือทักษะสำคัญในยุคนี้ครับ เราควรเรียนรู้เพื่อเพิ่มประสิทธิภาพการทำงานของตัวเรา แต่ทุกคนไม่จำเป็นต้องไปเขียนโปรแกรมภาษาต่างๆอย่างที่ programmer เขาทำก็ได้ครับ แค่เราออกคำสั่งใช้งานโปรแกรมที่เราเจอกันบ่อยๆอย่าง Sheets หรือ Excel ได้ เท่านี้เราก็มีทักษะการเขียนโปรแกรมแล้วเหมือนกันครับ หวังว่าบทความนี้จะทำให้คุณรัก Google Sheets หรือแม้แต่ Excel มากขึ้นนะครับ (แต่ Excel ไม่มีคำสั่ง query แบบนี้นะ)