สร้างชีทคิดเงินค่าอาหารทุกงานปาร์ตี้ อย่างเสมอภาคด้วย 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) ศึกษาเพิ่มเติมได้จากลิ้งด้านล่าง

SQL language (Version 0.7)
- [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
- กรอกข้อมูลรายชื่อเมนู ในชีท รายชื่ออาหาร
- กรอกข้อมูลรายชื่อเพื่อน ในชีท รายชื่อเพื่อน
- จากนั้นทุกอย่างจะมาถูกคำนวน Auto ที่ชีท ชีทเก็บเงิน
- นำค่าใช้จ่ายแต่ละคนไปเรียกเก็บผ่านขุนทอง หรือให้โอนเข้ามาในบัญชีเราได้
หากเพื่อนๆ ชอบเนื้อหานี้ ฝากกด Share ให้คนอื่นด้วยนะคร้าบ
Member discussion