Cách Tính Tiền Điện Phòng Trọ Theo Bậc Thang Bằng Google Sheets

Trả lời nhanh: Lập bảng bậc thang riêng (ví dụ giả định 3 bậc: 0–50 kWh giá 2.000đ, 51–100 kWh giá 2.500đ, trên 100 kWh giá 3.000đ), tính số điện dùng bằng chỉ số cuối trừ chỉ số đầu, rồi dùng SUMPRODUCT hoặc MIN/MAX để cộng tiền từng bậc. Phòng dùng 120 kWh trả 285.000đ.

Thử hình dung anh Bảo có 12 phòng trọ. Cuối tháng anh ngồi với cuốn sổ ghi chỉ số, cái máy tính bấm và một cốc cà phê nguội. Phòng 101 dùng 120 số, anh phải nhân 50 số đầu với một giá, 50 số tiếp theo với giá khác, 20 số còn lại với giá thứ ba, rồi cộng ba kết quả. Làm xong phòng thứ tám thì anh bấm nhầm một con số, và đến khi khách hỏi "sao tháng này điện cao thế" anh không biết lần tính nào sai. Toàn bộ bài này là cách đưa việc đó vào một bảng Google Sheets để mỗi cuối kỳ chỉ còn việc gõ chỉ số cuối vào cột C.

Mọi con số trong bài đều là giả định để minh họa. Bậc thang, đơn giá và số bậc do chủ trọ tự đặt, bạn thay bằng mức của mình là công thức vẫn chạy.

Bảng chỉ số điện đầu kỳ, cuối kỳ cho từng phòng

Bảng nhập liệu càng đơn giản càng ít sai. Mỗi phòng một dòng, mỗi kỳ chốt điện một sheet riêng (ví dụ sheet tên T9, T10). Cách bố trí cột như sau:

Ô	A	B	C	D	E	F
Dòng 1	Phòng	Chỉ số đầu kỳ	Chỉ số cuối kỳ	Số điện dùng	Tiền điện	Kiểm tra
Dòng 2	P101	1250	1370	120	285.000
Dòng 3	P102	830	872	42	84.000
Dòng 4	P103	2410	2495	85	187.500
Dòng 5	P104	560	700	140	345.000

Cột D chỉ là phép trừ. Ở ô D2 gõ:

=C2-B2

rồi kéo xuống các dòng dưới. Cột F để bắt lỗi nhập liệu, phần sau sẽ nói kỹ. Hai cột E và F chưa cần điền ngay, ta làm sau khi có bảng bậc thang.

Bảng bậc thang do chủ trọ tự đặt

Đừng viết thẳng 2000, 2500, 3000 vào trong công thức. Hôm nào bạn đổi giá, bạn sẽ phải sửa từng ô và chắc chắn sót một chỗ. Hãy đặt bảng giá ở một góc của sheet, ví dụ vùng G1:I4, và để công thức trỏ vào đó.

Ô	G	H	I
Dòng 1	Từ kWh thứ	Đơn giá (đ/kWh)	Đơn giá chênh so với bậc trước
Dòng 2	0	2000	2000
Dòng 3	50	2500	500
Dòng 4	100	3000	500

Với bảng này, bậc 1 là 0–50 kWh giá 2.000đ, bậc 2 là 51–100 kWh giá 2.500đ, bậc 3 là trên 100 kWh giá 3.000đ. Cột G là mốc bắt đầu của mỗi bậc, cột H là đơn giá của bậc đó.

Cột I là mẹo nhỏ để dùng được SUMPRODUCT. Thay vì tách số điện ra từng khúc, ta coi mỗi bậc là "phần tăng thêm" so với bậc trước: ai dùng quá 50 số thì từ số thứ 51 trở đi phải trả thêm 500đ mỗi số so với giá cũ, ai dùng quá 100 số thì từ số thứ 101 trả thêm 500đ nữa. Cách nghĩ này cho ra đúng kết quả nhưng gọn hơn nhiều. Ở I2 gõ =H2, ở I3 gõ =H3-H2, rồi kéo xuống I4.

Muốn thêm bậc thứ tư thì sao

Thêm một dòng vào bảng (G5 là mốc, H5 là giá, I5 là chênh) rồi sửa vùng tham chiếu trong công thức từ $G$2:$G$4 thành $G$2:$G$5. Nếu bạn chèn dòng ở giữa vùng thay vì ở cuối, Google Sheets tự mở rộng vùng giúp bạn, nên nên chèn ở giữa thay vì gõ thêm bên dưới.

Công thức tính tiền điện theo bậc

Có hai cách viết, kết quả giống hệt nhau. Chọn cách nào tùy bạn thấy cách nào dễ đọc lại sau ba tháng.

Cách 1: SUMPRODUCT

Ở ô E2 gõ:

=IF(D2<0,"",SUMPRODUCT((D2>$G$2:$G$4)*(D2-$G$2:$G$4)*$I$2:$I$4))

Phần IF(D2<0,"",…) để trống ô khi số điện âm (lỗi nhập chỉ số, nói ở phần sau), tránh in ra một con số vô nghĩa. Còn lõi của công thức là: với mỗi mốc ở cột G, nếu số điện dùng vượt mốc thì lấy phần vượt nhân với đơn giá chênh ở cột I, rồi cộng tất cả lại.

Thử với P101, dùng 120 kWh:

• Mốc 0: phần vượt 120, nhân 2.000 = 240.000
• Mốc 50: phần vượt 70, nhân 500 = 35.000
• Mốc 100: phần vượt 20, nhân 500 = 10.000

Cộng lại được 285.000đ. Kiểm bằng cách tính tay theo bậc: 50 × 2.000 = 100.000, thêm 50 × 2.500 = 125.000, thêm 20 × 3.000 = 60.000, tổng cũng 285.000đ. Hai cách khớp nhau thì công thức đúng.

Cách 2: MIN và MAX

Nếu bạn thấy SUMPRODUCT khó nhìn, cách này đọc gần giống cách tính tay hơn. Ở E2:

=IF(D2<0,"",MIN(D2,$G$3)*$H$2+MAX(MIN(D2,$G$4)-$G$3,0)*$H$3+MAX(D2-$G$4,0)*$H$4)

Đọc từng khúc: MIN(D2,$G$3) là số điện nằm trong bậc 1 (tối đa 50). MAX(MIN(D2,$G$4)-$G$3,0) là số điện nằm trong bậc 2 (từ 51 đến 100). MAX(D2-$G$4,0) là phần vượt trên 100. Mỗi khúc nhân đơn giá của bậc đó ở cột H, rồi cộng.

Cách MIN/MAX dễ giải thích cho người khác trong nhà (vợ, chồng, người quản lý hộ) vì nó là bậc nào nhân giá bậc đó. Nhược điểm là mỗi bậc thêm vào phải viết thêm một khúc, từ 5 bậc trở lên công thức dài khó nhìn. SUMPRODUCT thì thêm bậc chỉ cần kéo dài vùng, nên hợp hơn nếu bảng giá của bạn có nhiều bậc.

Kết quả cho bốn phòng mẫu

Phòng	Số điện dùng	Cách tính	Tiền điện
P102	42 kWh	42 × 2.000	84.000đ
P103	85 kWh	50 × 2.000 + 35 × 2.500	187.500đ
P101	120 kWh	50 × 2.000 + 50 × 2.500 + 20 × 3.000	285.000đ
P104	140 kWh	50 × 2.000 + 50 × 2.500 + 40 × 3.000	345.000đ

Tổng bốn phòng là 901.500đ, và bạn có thể kiểm bằng =SUM(E2:E5). Cách tính bậc thang này là cùng một tư duy với việc tính hoa hồng theo mức doanh số. Nếu muốn xem thêm một ví dụ khác của kiểu công thức này, có bài mẫu Google Sheets tính hoa hồng bán hàng theo bậc thang.

Chia tiền điện khi người ở chuyển vào hoặc ra giữa kỳ

Đây là chỗ bảng tính đơn giản nhất cũng hay vỡ. Giả sử phòng P105 có chỉ số đầu kỳ 300 và cuối kỳ 400, tức cả kỳ dùng 100 kWh. Nhưng người thuê cũ trả phòng vào giữa kỳ, người mới dọn vào ngay hôm sau. Ai trả bao nhiêu?

Cách duy nhất không gây cãi nhau là ghi chỉ số công tơ vào đúng ngày bàn giao, ngay trước mặt cả hai bên nếu được. Giả sử chỉ số chốt hôm đó là 340. Người cũ dùng 340 − 300 = 40 kWh, người mới dùng 400 − 340 = 60 kWh. Bố trí trong sheet ở dòng 8 (dòng 7 để trống, dòng 8 làm dòng dữ liệu):

Ô	A	B	C	D	E	F
Dòng 7	Phòng	Đầu kỳ	Chỉ số chốt	Cuối kỳ	Điện người cũ	Điện người mới
Dòng 8	P105	300	340	400	40	60

E8 là =C8-B8, F8 là =D8-C8. Tới đây bạn có hai cách áp bậc thang, và đây là quyết định của chủ trọ, không phải của công thức.

Cách A: mỗi người tính bậc thang riêng

Người cũ 40 kWh nằm hết trong bậc 1: 40 × 2.000 = 80.000đ. Người mới 60 kWh: 50 × 2.000 + 10 × 2.500 = 125.000đ. Tổng thu cả phòng là 205.000đ. Dùng đúng công thức SUMPRODUCT ở trên, chỉ đổi D2 thành E8 (rồi F8 cho người mới).

Cách B: tính cả kỳ rồi chia theo tỷ lệ

Cả phòng dùng 100 kWh, tiền điện cả kỳ là 50 × 2.000 + 50 × 2.500 = 225.000đ. Chia theo tỷ lệ 40/60 thì người cũ 90.000đ, người mới 135.000đ. Công thức cho người cũ, đặt ở G8:

=SUMPRODUCT(((D8-B8)>$G$2:$G$4)*((D8-B8)-$G$2:$G$4)*$I$2:$I$4)*E8/(D8-B8)

Người mới ở H8 giống hệt, chỉ thay E8 ở cuối bằng F8.

Cách B thu nhiều hơn cách A 20.000đ trong ví dụ này, vì mỗi người không được "hưởng" lại bậc giá thấp từ đầu. Cách B nhất quán hơn: cả phòng dùng bao nhiêu thì tính đúng như một kỳ trọn vẹn rồi chia. Nhưng cách A nghe công bằng hơn với người ở ít ngày. Chọn cách nào cũng được, miễn là bạn báo cho người thuê trước khi họ dọn vào, và áp dụng một cách cho mọi phòng.

Nếu ngày bàn giao không ai nhớ ghi chỉ số công tơ, đành chia theo số ngày ở, nhưng cách này lệch hơn vì người ở nhiều ngày chưa chắc dùng điện nhiều. Phần tiền phòng khi vào hoặc trả phòng giữa tháng cũng có cách tính riêng, bạn có thể xem cách tính tiền phòng trọ khi thuê giữa tháng.

Những lỗi hay gặp khi tính điện bằng Google Sheets

Chỉ số cuối nhỏ hơn chỉ số đầu

Hai nguyên nhân thường gặp là gõ thiếu một chữ số (1370 gõ thành 137) hoặc gõ nhầm sang dòng của phòng khác. Cột D lúc đó ra số âm. Ở ô F2 gõ công thức:

=IF(C2<B2,"Chỉ số cuối nhỏ hơn đầu","")

Sau đó vào Định dạng, chọn Định dạng có điều kiện, tô đỏ ô F nào có chữ. Công thức tính tiền ở trên đã trả về ô trống khi số điện âm nên bạn sẽ không in nhầm hóa đơn.

Có một trường hợp hợp lệ: công tơ chạy hết vòng và quay lại số 0. Nếu công tơ phòng bạn có 4 chữ số, số điện thực dùng là chỉ số cuối cộng 10.000 rồi trừ chỉ số đầu. Trường hợp này hiếm, nên cứ để cột F báo lỗi rồi bạn tự kiểm tra bằng mắt thì an toàn hơn là viết công thức tự đoán.

Quên chốt kỳ, hoặc chốt kỳ không liền mạch

Lỗi này đắt hơn: tháng này quên gõ chỉ số cuối, sang tháng sau vẫn dùng chỉ số đầu cũ, thế là số điện tháng sau gấp đôi. Cách chặn là đừng gõ tay cột B. Ở sheet T10, ô B2 gõ ='T9'!C2 để chỉ số đầu kỳ luôn lấy từ chỉ số cuối kỳ trước. Nếu ô C của kỳ trước còn trống, B hiện ra 0, và số điện của kỳ này sẽ lớn bất thường, tự thấy ngay.

Đổi đơn giá làm hóa đơn cũ nhảy theo

Vì công thức trỏ vào bảng giá G1:I4, khi bạn sửa giá thì mọi sheet cũ dùng chung bảng đó sẽ đổi tiền theo. Cách tránh: mỗi sheet tháng có bảng giá riêng ở vùng G1:I4 của chính sheet đó. Khi tạo sheet tháng mới, sao chép sheet cũ rồi mới sửa giá. Tháng cũ vẫn giữ nguyên giá của nó.

Gõ số bằng dấu chấm hoặc có chữ

Chỉ số công tơ gõ "1.370" có thể bị hiểu là chữ hoặc là 1,37 tùy cài đặt vùng. Hãy gõ liền 1370, không dấu, và đặt định dạng cột B, C là số. Muốn chắc hơn, chọn vùng B2:C13 rồi vào Dữ liệu, Xác thực dữ liệu, cho phép số lớn hơn hoặc bằng 0.

Khi nào bảng tính tay không còn đủ

Với 10–15 phòng, một sheet như trên làm tròn việc tính điện. Nhưng điện chỉ là một dòng trong hóa đơn: còn tiền phòng, tiền nước, tiền dịch vụ, khoản nợ tháng trước, rồi còn phải gửi hóa đơn và đối chiếu ai đã chuyển khoản. Khi số phòng tăng và các tờ sheet chồng lên nhau, việc nhập liệu và lập hóa đơn bắt đầu chiếm cả buổi.

Nếu đã đến mức đó, Phần Mềm Quản Lý Phòng Trọ trên Google Sheets làm sẵn những phần trong bài này. Bạn nhập chỉ số điện nước của cả kỳ trong một bảng, chỉ số đầu kỳ tự lấy từ kỳ trước, giá điện nước bậc thang nhiều bậc, rồi lập hóa đơn hàng loạt cho cả kỳ. Tiền phòng cũng được tính theo số ngày ở thực tế khi khách vào hoặc trả phòng giữa tháng. Phần mềm chạy trong tài khoản Google của chính bạn nên cần có tài khoản Google, dữ liệu nằm trong Google Drive của bạn.

Giá là 459.000đ, trả một lần dùng trọn đời, không phí hằng tháng, và có 7 ngày dùng thử. Nếu bạn chỉ có vài phòng và bảng ở trên đã đủ dùng thì cứ dùng bảng đó. Muốn xem thêm bố cục một file quản lý nhà trọ hoàn chỉnh trước khi quyết định, bạn có thể đọc bài template quản lý nhà trọ Google Sheets.

Câu hỏi thường gặp

Nếu muốn thêm bậc thứ tư vào bảng giá điện thì làm thế nào?

Thêm một dòng vào bảng bậc thang: G5 là mốc, H5 là đơn giá, I5 là phần chênh so với bậc trước. Sau đó sửa vùng tham chiếu trong công thức SUMPRODUCT từ $G$2:$G$4 thành $G$2:$G$5. Nếu chèn dòng ở giữa vùng thay vì gõ thêm bên dưới, Google Sheets sẽ tự mở rộng vùng giúp bạn.

Nên dùng SUMPRODUCT hay MIN/MAX để tính tiền điện theo bậc?

Hai cách cho kết quả giống hệt nhau. MIN/MAX đọc gần giống cách tính tay (bậc nào nhân giá bậc đó), dễ giải thích cho người nhà, nhưng mỗi bậc thêm vào phải viết thêm một khúc, từ 5 bậc trở lên công thức dài khó nhìn. SUMPRODUCT chỉ cần kéo dài vùng khi thêm bậc, nên hợp hơn nếu bảng giá có nhiều bậc.

Khi đổi đơn giá điện, hóa đơn các tháng cũ có bị nhảy theo không?

Có, nếu các sheet dùng chung một bảng giá, vì công thức trỏ vào bảng đó. Cách tránh là cho mỗi sheet tháng một bảng giá riêng ở vùng G1:I4 của chính sheet đó. Khi tạo sheet tháng mới, hãy sao chép sheet cũ rồi mới sửa giá, nhờ vậy tháng cũ vẫn giữ nguyên giá của nó.

Người thuê chuyển vào hoặc ra giữa kỳ thì chia tiền điện thế nào?

Ghi chỉ số công tơ vào đúng ngày bàn giao, ví dụ chốt 340 trong kỳ từ 300 đến 400. Có hai cách: mỗi người tính bậc thang riêng (tổng 205.000đ) hoặc tính cả kỳ rồi chia theo tỷ lệ số điện (tổng 225.000đ). Cách nào cũng được, miễn báo người thuê trước khi dọn vào và áp dụng thống nhất cho mọi phòng.