Feb 3, 2023 3 min read Google Sheets

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

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

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

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

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

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

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

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

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

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

สร้างชีทรายรับ และข้อมูลที่จะเป็นต้องเก็บ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

สร้างชีทรายจ่าย และข้อมูลที่จะเป็นต้องเก็บ

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

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

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

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

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

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

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

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

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

3. ตั้งค่า Pivot table

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

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

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

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

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

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

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

ตั้งค่า Pivot Table ตามภาพด้านล่าง

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

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

แทรก Rows เข้าไปด้านบนประมาณ 3 แถว เพื่อให้มีพื้นที่ในการพิเคราะห์ข้อมูล แล้วพิมพ์

รายรับ
รายจ่าย
คงเหลือ

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 เท่านั้นเอง