สร้างบัญชีรายรับ-รายจ่าย และวิเคราะห์ข้อมูลง่ายๆ ด้วย Google Sheets

สร้างบัญชีรายรับ-รายจ่าย และวิเคราะห์ข้อมูลง่ายๆ ด้วย Google Sheets
สร้างบัญชีรายรับ-รายจ่าย และวิเคราะห์ข้อมูลง่ายๆ ด้วย Google Sheets

การทำรายรับ-รายจ่าย มีมานานแล้ว ตั้งแต่การจดลงสมุดจนกระทั่งมี Application สำหรับการออม แต่บทความนี้เราจะสอนทุกคนออกแบบการทำรายรับ-รายจ่าย ด้วยตัวเอง ผ่าน Google Sheets ที่เป็น Web Application ฟรี ไม่ต้องเสียเงินใดๆ

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

สร้างชีทใหม่อย่างรวดเร็ว ตามสูตร!!

สร้าง Google Sheets ใหม่ด้วยคำสั่ง sheet.new

ตั้งชื่อไฟล์ให้ชัดเจน

แอดจะตั้งชื่อว่า "บันทึกรายรับรายจ่ายด้วย Google Sheets"

ตั้งชื่อไฟล์ Google Sheets

สร้างชีทรายรับ

เริ่มด้วยการสร้างชีทรายรับ ที่มี Column ง่ายๆ 3 อัน

  • วันที่: ใช้บันทึกวันที่รายรับเข้ามา
  • ประเภทรายรับ: เช่น เงินเดือน, งานพิเศษ, ปันผลกองทุน และอื่นๆ
  • จำนวนเงิน
สร้างชีทรายรับ และข้อมูลที่จะเป็นต้องเก็บ

กำหนด Column วันที่ เพื่อให้ขึ้นรูปปฏิทินขึ้นมา

  1. คลิกขวาที่ Column วันที่ แล้วเลือก Data Validation จากนั้นเลือก Add a Rule
ใช้งาน Data Validation

2. กำหนดค่าใน Function ตามรูปภาพด้านล่าง

  • Apply to range = Column ที่ต้องการใช้งาน กำหนดตั้งแต่ Row A2 เป็นต้นไป เพราะเราไม่รวมหัว Column
  • Criteria = เลือกเป็น is valid date
กำหนดค่าใน Function Data Validation

3. คลิก Cell ในคอลัมน์ วันที่ เพื่อเลือก Date จากปฏิทินที่ Pop up ขึ้นมาได้เลย

Result จากการทำ Function Data Validation

สร้าง Drop-down ประเภทรายรับเพื่อง่ายต่อการใช้งาน

  1. คลิกขวาที่ Column ประเภทรายรับ แล้วเลือก Dropdown
ใช้งาน Dropdown

2. กำหนดค่าใน Function ตามรูปภาพด้านล่าง

  • Apply to range = Column ที่ต้องการใช้งาน กำหนดตั้งแต่ Row B2 เป็นต้นไป เพราะเราไม่รวมหัว Column
  • Criteria = เลือกเป็น Dropdown และใส่ค่าและกำหนดสีที่ต้องการลงไป แต่เรื่องสีแอดว่าแล้วแต่ความชอบ มีมากไปก็ลายตาใช้ได้เลย

เมื่อเสร็จทุกอย่างแล้ว ให้กด Done

กำหนดค่าใน Function Dropdown

3. เลือกใช้งานประเภทรายรับผ่านตัว Dropdown ได้เลย

Result จากการทำ Function Dropdown

เท่านี้เราก็สร้างชีท รายรับ เสร็จเรียบร้อยแล้ว ลองกรอกข้อมูลไว้ก่อนได้เลย

ลองกรอกข้อมูลลงในชีท รายรับ

สร้างชีทรายจ่าย

สร้างชีทรายจ่าย ที่มี Column ตามนี้ (เพิ่มเติมเองได้)

  • วันที่: ใช้บันทึกวันที่รายรับเข้ามา
  • หมวดหมู่รายจ่าย: เช่น อาหาร, น้ำ, ไฟ, เติมน้ำมัน, อินเตอร์เนต, บันเทิง, หาหมอ, เที่ยว เป็นต้น
  • ประเภทรายจ่าย: เช่น ค่าใช้จ่ายทั่วไป, ค่าใช้จ่ายประจำ (Internet, Netflix, Spotify), ผ่อนจ่าย
  • ร้านค้า (Optional): เอาไว้ใส่คู่กับค่าอาหารเฉยๆ ก็ได้
  • ประเภทการใช้ง่าย: เช่น เงินสด, บัตรเครดิต (จะแยกชื่อเป็นแต่ละบัตรก็ได้เหมือนกัน)
  • จำนวนเงิน
สร้างชีทรายจ่าย และข้อมูลที่จะเป็นต้องเก็บ

แอดจะไม่ลงรายละเอียดการสร้าง Dropdown กับ Data Validation เพิ่ม เพราะอธิบายแบบละเอียดยิบไปแล้วในชีทรายรับ เมื่อสร้างทุกอย่างเสร็จแล้ว ชีทรายจ่าย จะมีหน้าตาออกมาประมาณนี้

ตัวอย่างการกรอก ชีทรายจ่าย

เราลองมากรอกข้อมูลมากขึ้น เพื่อต่อยอดการทำ Descriptive Data Analytics หรือ การวิเคราะห์ข้อมูลง่ายๆ ด้วยตัวเราเอง

กรอกข้อมูลในชีทรายรับ และชีทรายจ่าย ให้เรียบร้อย

กรอกข้อมูลรายรับและรายจ่ายให้เรียบร้อย

วิเคราะห์ข้อมูลกันเถอะ

ทำ Pivot ในชีทรายรับ

  1. ลากครอบหัว Column A ถึง Column C จากนั้นไปที่ Insert > Pivot table
สร้าง Pivot table ให้ชีทรายรับ

2. ใส่ข้อมูลใน Function Pivot Table

  • Data Range: ข้อมูลเพื่อนำมา Summarize ใน Pivot Table
  • Insert to: พื้นที่ใช้แสดงตาราง Pivot

เมื่อ Set ค่าตามในภาพแล้ว ให้กดปุ่ม Create ได้เลย

การใส่ค่าใน Function Pivot Table

3. ตั้งค่า Pivot table

หลังจากนั้นเราจะได้ตาราง Pivot ออกมา ให้เรากดปุ่ม Edit เพื่อเข้าไปแก้ไขค่าต่างๆ ที่อยู่ใน Pivot โดยจะมี 4 ส่วนที่ต้องเข้าไปตั้งค่า

  • Rows: เลือกสิ่งที่จะอยู่ใน Column แรก เพื่อเป็นมิติหลักในการมองข้อมูล โดยข้อมูลจะเรียงลงไปเป็นแบบ Long format
  • Columns: เลือกสิ่งที่จะอยู่ในแนว Column ถัดๆ ไป โดยข้อมูลจะเรียงไปด้านขวาแบบ Wide format
  • Values: เลือกค่าที่จะใช้เป็นตัวชี้วัด โดยส่วนใหญ่มักเป็นตัวเลข
  • Filters: ใช้กรองสิ่งที่อยากให้แสดงหรือไม่แสดงใน Pivot Table
Set up Pivot Table

โดยให้เพื่อนๆ ตั้งค่าตามภาพด้านล่าง โดยเราจะให้

  • Rows = "วันที่"
  • Columns = "ประเภทรายรับ"
  • Values = "จำนวนเงิน"
  • Filters = "ประเภทรายรับ" โดยจะเอา Checkbox ตรงค่าที่เป็น Blank ออก
ตั้งค่า Pivot Table ของชีทรายรับ

จากนั้นให้คลิกขวาที่คอลัมน์ วันที่ และเลือก Create pivot date group > Year-Month ตามภาพด้านล่าง

Create pivot date group

จากนั้นเราก็ได้ตาราง Pivot ง่ายๆ ของชีทรายรับเรียบร้อย

Final Pivot table สำหรับชีทรายรับ

ทำ Pivot ในชีทรายจ่าย

  1. ตั้งค่า Pivot Table ตามภาพด้านล่าง
ตั้งค่า Pivot Table ของชีทรายจ่าย

2. คลิกขวาที่ Cell C2 แล้ว Create pivot date group > Year-Month เพื่อให้เราสามารถดูเป็นรายเดือนได้ ตามภาพด้านล่าง

Create pivot date group

เชื่อมข้อมูลรายรับรายจ่ายเพื่อหายอดคงเหลือ

  1. แทรก Rows เข้าไปด้านบนประมาณ 3 แถว เพื่อให้มีพื้นที่ในการพิเคราะห์ข้อมูล แล้วพิมพ์
  • รายรับ
  • รายจ่าย
  • คงเหลือ
แทรก Rows ด้านบน Pivot Table

2. ใช้ VLOOKUP เพื่อดึงข้อมูลรายรับจากชีทรายรับมาเป็นตัวตั้ง

=VLOOKUP(search_key, range, index, [is_sorted])
  • search_key = ค่าที่เราใช้เป็นตัวเชื่อมจากชีทรายจ่ายไปยังชีทรายรับ
  • range = ช่วงข้อมูลที่ต้องการเข้าไปหาค่า โดย Column ที่เชื่อมกันได้กับ search_key ต้องอยู่ Column แรกเสมอ
  • index = ลำดับ Column ใน range ที่มีข้อมูลที่ต้องการ
  • [is_sorted] ให้ใส่เป็น FALSE ไป

สามารถ Copy สูตรนี้ไปใช้งานในแถว รายรับ (Cell C1 และลากสูตรไปทางขวาจนถึง Cell E1 หรือลากต่อไปได้ตามต้องการ)

=VLOOKUP(C5,'รายรับ'!$F:$I,4,FALSE)
ใช้ Function VLOOKUP เพื่อดึงข้อมูลจากชีทรายรับ

3.  ใช้ ArrayFormula + SUM เพื่อคำนวนผลรวมรายจ่าย

=ArrayFormula(SUM(C6:C))

สามารถ Copy สูตรนี้ไปใช้งานในแถว รายจ่าย (Cell C2 และลากสูตรไปทางขวาจนถึง Cell E2 หรือลากต่อไปได้ตามต้องการ)

ใช้ ArrayFormula + SUM เพื่อคำนวนผลรวมรายจ่าย

4. ใช้ IF เพื่อสร้างเงื่อนไขในการคำนวนยอดคงเหลือ

=IF(ISNUMBER(B3)=FALSE,C1-C2,B3+C1-C2)

เงื่อนไขนี้คือ

ถ้า B3 หรือช่องด้านซ้ายไม่ใช่ตัวเลข =  รายรับของเดือนปัจจุบัน - รายจ่ายของเดือนปัจจุบัน

ถ้า B3 หรือช่องด้านซ้ายเป็นตัวเลข = ยอดคงเหลือจากเดือนก่อน + รายรับของเดือนปัจจุบัน - รายจ่ายของเดือนปัจจุบัน

สามารถ Copy สูตรนี้ไปใช้งานในแถว คงเหลือ (Cell C3 และลากสูตรไปทางขวาจนถึง Cell E3 หรือลากต่อไปได้ตามต้องการ)

โดยเราสามารถต่อยอดด้วยการใส่ Conditional Formatting ให้กับยอดคงเหลือที่มีค่าติดลบให้แสดงผลเป็นสีแดงก็ได้ หรือสร้าง Chart เพิ่มจาก Pivot Table เพื่อเห็นภาพรวมค่าใช้จ่ายมากขึ้น

เท่านี้เราก็ได้ชีท บันทึกรายรับรายจ่ายด้วย Google Sheets พร้อมการวิเคราะห์ข้อมูลได้ว่า ค่าใช้จ่ายไหนที่มีมากเกินความจำเป็นทำให้ยอดคงเหลือเราติดลบ เป็นต้น


Recap

แค่ Function ไม่กี่อย่าง เราก็สามารถสร้างไฟล์ Google Sheets ที่มีประโยชน์กับตัวเราเอง โดยเริ่มจากเรื่องใกล้ๆ ตัวได้แล้ว ในบทความนี้แอดใช้แค่ 7 Functions เท่านั้นเอง

  • Dropdown
  • Data Validation
  • Pivot Table
  • VLOOKUP
  • IF
  • SUM
  • ArrayFormula

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

สำหรับใครที่อยากได้ Template ไว้ใช้งานก็เข้าไป Make a Copy ไว้ลองใช้งานส่วนตัวได้เลย โดยการคลิก ที่นี่

Make a copy เพื่อเก็บไฟล์ไว้ใช้งานส่วนตัว

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

ถ้าไม่อยากพลาดบทความแบบนี้สามารถ Subscribe หรือ สมัครสมาชิก มา Comment แลกเปลี่ยนความคิดเห็นได้เลย!!

Subscribe to มาลองเรียน - Malonglearn

Don’t miss out on the latest issues. Sign up now to get access to the library of members-only issues.
jamie@example.com
Subscribe