สร้างชีทคิดเงินค่าอาหารทุกงานปาร์ตี้ อย่างเสมอภาคด้วย 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 ฟรี อยู่ตรงนี้แล้ว!