สร้างชีทคิดเงินค่าอาหารทุกงานปาร์ตี้ อย่างเสมอภาคด้วย GoogleSheets

บทความนี้เรายังอยู่กับ GoogleSheets เครื่องมือ Spreadsheet ฟรี! จาก Google รอบนี้แอดจะสอนวิธีการ Apply สกิล Spreadsheet กับชีวิตประจำวันที่ทุกคนหลีกเลี่ยงไม่ได้ นั่นก็คือ การไปกินข้าวหรือสังสรรค์กับเพื่อนๆ นั่นเอง

แอดคิดว่าหลายคนน่าจะคุ้นเคยกับ @Khunthong นกขุนทองที่ถูกพัฒนามาโดย KBTG เพื่อตอบโจทย์การทวงหนี้เป็นอย่างดี ลดการทะเลาะกันระหว่างเพื่อนพ้อง

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

เช่น เมนูราคา 2,000 แต่เราไม่ได้สั่ง ไม่ได้กินสักคำทำไมเราต้องมาหารด้วย นี่เป็นอีกสาเหตุใหญ่ที่ทำให้หลายคนไม่อยากไปกินข้าวกับเพื่อน บทความนี้แอดจะช่วยแก้ปัญหานี้ให้เพื่อนๆ ด้วยการ สร้างชีทเก็บเงินค่าอาหารทุกงานปาร์ตี้ ด้วยสูตรง่ายๆ บน GoogleSheets

สร้างไฟล์ GoogleSheets

สร้างไฟล์ GoogleSheets อย่างรวดเร็วตามสูตรของชาว มาลองเรียน กันเลย ก่อนอื่นให้เพื่อนๆ เปิด Web Browser และพิมพ์

sheet.new

ตั้งชื่อว่า เก็บเงินเพื่อนแบบเสมอภาค

สร้างชีท "รายชื่ออาหาร"

กำหนดชื่อ Columns ที่จำเป็น (หรือจะมากกว่านี้ก็ได้)

• ลำดับ
• รายการอาหาร
• ราคา

เพิ่มสูตรง่ายๆ ที่ Cell A3 ให้ Column นี้เรียงลำดับแบบ Automate มากขึ้น

=IF(B3="","",A2+1)

จากสูตร เรากำหนดเงื่อนไขว่า ถ้า Column รายการอาหารเป็นค่าว่าง ให้ Column ลำดับ แสดงเป็นค่าว่างด้วย แต่ถ้า Column รายการอาหาร มีข้อความอยู่ให้รันลำดับ + ไปอีก 1 จากด้านบน Result คือ มันจะรันลำดับให้เราแบบออโต้

ปล. อย่าลืมลากสูตรลงไปถึง Row สุดท้ายด้วยน้า

ทีนี้เรามาลองเพิ่มเมนูกันดีกว่า แอดลองใช้รูปนี้จาก Google เป็นตัวอย่างในบทความนี้เลยแล้วกัน

ในระหว่างที่เราเพิ่มเมนูเข้าไป จะเห็นได้ว่าเลขลำดับจะถูกรันเองโดยอัตโนมัติตามภาพด้านล่าง

เท่านี้การสร้างชีท รายชื่ออาหาร เป็นอันเสร็จสิ้น

สร้างชีท "รายชื่อเพื่อน"

กำหนดชื่อ Columns ที่จำเป็น (หรือจะมากกว่านี้ก็ได้)

• ลำดับ
• ชื่อเพื่อน

ใส่สูตรเดียวกันกับชีท รายชื่ออาหาร ใน Column A และใส่รายชื่อเพื่อนที่ไปกินข้าวในมื้อนั้นๆ ด้วยกัน แบบตัวอย่างในภาพด้านล่าง

เท่านี้การสร้างชีท รายชื่อเพื่อน เป็นอันเสร็จสิ้น

สร้างชีท "ชีทเก็บเงิน"

ชีทนี้จะเป็นส่วนสำคัญที่เราใช้คำนวนว่าจะเก็บเงินเพื่อนแต่ละคนเท่าไหร่

1. ดึงข้อมูลรายการอาหารและราคาจากชีท "รายการอาหาร"

ใช้ Function QUERY ในการดึงข้อมูล โดย syntax เป็นไปตามนี้

QUERY(data, query, [headers])

• data = ช่วงขอมูลที่เป็น Source หลัก
• query = SQL language (Version 0.7) ศึกษาเพิ่มเติมได้จากลิ้งด้านล่าง

Query Language Reference (Version 0.7) | Charts | Google Developers

Google Developers

• [headers] = หลักๆ คือ จำนวนแถวที่เป็นหัว Column

เราจะเขียนแบบนี้ใส่ไปใน Cell A1

=QUERY('รายการอาหาร'!A:C,"SELECT*",1)

อธิบายเพิ่มเติม เราต้องการดึงข้อมูลจากชีท รายการอาหาร Column A:C ด้วยการ SELECT * (คือการเลือกค่าทั้งหมด) โดยมีจำนวนแถวที่เป็นหัว Column คือ 1

2. ดึงข้อมูลรายชื่อเพื่อนร่วมปาร์ตี้จากชีท "รายชื่อเพื่อน"

ใช้ Function QUERY เหมือนเดิม แต่รอบนี้เพิ่ม Syntax นิดหน่อย

=QUERY(TRANSPOSE('รายชื่อเพื่อน'!B2:B),"SELECT *",0)

• TRANSPOSE = การหมุนตารางจาก Long Format เป็น Wide Format คือจากบนลงล่าง เป็นซ้ายไปขวา

ส่วน syntax อื่นยังคงเดิม รอบนี้เราเปลี่ยน [headers] จาก 1 เป็น 0 เพราะไม่ต้องการหัว Columns ให้เราใส่ syntax ด้านบนไปที่ Cell E1 จะได้ผลดังภาพด้านล่าง

3. เข้าสู่ขั้นตอนการคำนวน

เริ่มต้นด้วยการเพิ่มแถวจำนวน 5 แถวด้านบนของข้อมูลที่เรามีอยู่ โดยการทำแถบตั้งแต่ แถวที่ 1 ถึงแถวที่ 5 จากนั้นคลิกขวาและเลือก Insert 5 rows above ตามภาพด้านล่าง

ทำการคำนวนยอดรวมทั้งหมดของบิล

โดยใส่สูตร SUM ปกติ ไว้ที่ Cell C5 ตามภาพด้านล่าง

สร้าง Checklist เพื่อแยกแยะว่าใครกินอะไรไปบ้างด้วย Check Box

• คลิกขวาที่ Cell E7 แล้วเลือก Data Validation จากนั้นกด Add Rules

• ตั้งค่า Apply to range และ Criteria ตามภาพด้านล่าง

จะเห็นได้ว่า Checkbox จะขึ้นมาเรียบร้อยแล้ว จากนั้นกด Done

Checkbox ที่เราสร้างขึ้นมามีค่าในตัวมันเองดังนี้

• ถ้ามีการเลือกในช่อง Checkbox ค่าจะเท่ากับ TRUE หรือ 1
• ถ้าไม่มีการเลือกในช่อง Checkbox ค่าจะเท่ากับ FALSE หรือ 0

คำนวนราคาแต่ละเมนูตามจำนวนคนที่กินเมนูนั้น

ลองเลือกว่าใครกินอะไรบ้างในเมนูวันนี้

ตัวอย่างแถวที่ 1 มีคนกิน coke 5 คน

ถ้า SUM จากจำนวน Checkbox = 1 จะได้ Total ของ coke = 5 หรือ

ถ้า COUNT จากจำนวน Checkbox = "TRUE" จะได้ Total ของ coke = 5 เช่นกัน

ดังนั้นเราจะใช้ ราคาของแต่ละเมนู หารด้วย COUNTIF เพื่อคำนวนว่า เมื่อแต่ละเมนูถูกหารตามจำนวนคนด้วย Checkbox จะเหลือเมนูละกี่บาท

=C7/COUNTIF(E7:Z7,TRUE)

อธิบายเพิ่มเติม สูตรนี้จะเป็นการนำราคา 15 บาท ของ coke ไปหารกับ 5 เหลือเมนูนี้จ่ายคนละ 3 บาท สำหรับคนที่กิน อย่าลืมลากสูตรลงไปจนแถวสุดท้าย ตามภาพด้านล่าง

สร้างตารางสรุปยอดด้วย QUERY เดียวกับการดึงรายชื่อเพื่อน

=QUERY(TRANSPOSE('รายชื่อเพื่อน'!B2:B),"SELECT *",0)

คำนวนยอดรวมด้วย SUMIF ปกติ โดยเงื่อนไขจะเป็น SUM เฉพาะแถวที่ Checkbox = TRUE

=IF(E6="","", SUMIF(E7:E,TRUE,$D$7:$D))

อธิบายเพิ่มเติม สูตรนี้จะเป็นการเขียน IF ครอบเอาไว้ว่า ถ้า Cell E6 หรือ รายชื่อเพื่อนเป็นค่าว่างให้ตรงนี้เป็นค่าว่างด้วย

แต่ถ้าไม่ ให้คำนวนยอดรวมด้วย SUMIF ปกติ โดยเงื่อนไขจะเป็น SUM เฉพาะแถวที่ Checkbox = TRUE

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

เพื่อความชัวร์ว่าเราคำนวนถูกต้อง สามารถเพิ่ม SUM ยอดที่ทุกคนต้องจ่ายทั้งหมด เพื่อ Recheck กับยอดรวมบิลอีกทีได้ จะได้ไม่ผิดพลาดสบายใจกันทุกฝ่าย ตามภาพด้านล่าง

เท่านี้เราก็รู้แล้วว่าควรไปใส่ยอดเท่าไหร่ใน @Khunthong เย่ๆๆ

Recap

1. กรอกข้อมูลรายชื่อเมนู ในชีท รายชื่ออาหาร
2. กรอกข้อมูลรายชื่อเพื่อน ในชีท รายชื่อเพื่อน
3. จากนั้นทุกอย่างจะมาถูกคำนวน Auto ที่ชีท ชีทเก็บเงิน
4. นำค่าใช้จ่ายแต่ละคนไปเรียกเก็บผ่านขุนทอง หรือให้โอนเข้ามาในบัญชีเราได้

หากเพื่อนๆ ชอบเนื้อหานี้ ฝากกด Share ให้คนอื่นด้วยนะคร้าบ

Template ฟรี อยู่ตรงนี้แล้ว!

คลิกเพื่อเข้าสู่ Template

เมื่อเข้ามาแล้วก็กด File > Make a copy กันตามสะดวกเลยจ้า

Special E-Book and Online Course

สำหรับใครที่สนใจ E-Book และคอร์ส Google Sheets ลงชื่อไว้ได้เลย ถ้าทำเสร็จแล้วจะรีบแจ้งข่าวให้รู้ก่อนใคร!!