SUM cộng tất cả số trong một vùng, AVERAGE tính trung bình, COUNT đếm có bao nhiêu ô chứa số; cả ba viết theo cùng một khuôn, ví dụ =SUM(C2:C31) là tổng các ô từ C2 đến C31. Ba hàm này cộng với MIN, MAX và COUNTA đủ cho phần lớn bảng thu chi, bảng doanh thu và danh sách ở văn phòng nhỏ. Điều người mới cần để ý không phải cú pháp mà là cách chúng đối xử với ô trống, ô chữ và số 0.
Bài dùng một ví dụ giả định xuyên suốt: bảng doanh thu bảy ngày của một quầy nước, có một ngày nghỉ. Bạn sẽ thấy cùng một bảng mà để trống hay ghi số 0 cho ngày nghỉ thì doanh thu trung bình khác hẳn nhau. Hướng dẫn theo Excel trên Windows, kèm cách làm trên macOS và Google Sheets; tên hàm giống nhau ở cả ba nơi và ở LibreOffice Calc.
Hàm là gì và đọc một hàm thế nào
Hàm là công thức có sẵn mà Excel đã đặt tên. Thay vì viết =C2+C3+C4+…+C31 dài ba mươi số hạng, bạn viết =SUM(C2:C31). Mọi hàm có chung một khuôn: dấu bằng, tên hàm, mở ngoặc, các thứ hàm cần (gọi là đối số), đóng ngoặc. Với SUM, đối số là vùng ô cần cộng.
Khi có nhiều đối số, chúng được ngăn bằng dấu phẩy hoặc dấu chấm phẩy tuỳ cài đặt vùng của máy. Máy theo vùng Việt Nam, dùng dấu phẩy thập phân, thường ngăn bằng chấm phẩy: =SUM(C2:C10;E2:E10) cộng hai vùng tách rời. Máy theo vùng Mỹ thì viết =SUM(C2:C10,E2:E10). Khi bạn gõ tên hàm và mở ngoặc, Excel hiện một dòng gợi ý nhỏ bên dưới ô, cho biết hàm cần những gì và dùng dấu ngăn nào; nhìn dòng đó là biết máy mình theo kiểu nào.
Bạn không cần thuộc cách viết. Gõ dấu bằng và vài chữ đầu như =AV, Excel hiện danh sách hàm bắt đầu bằng AV, bấm Tab để chọn AVERAGE. Sau đó kéo chuột chọn vùng ô thay vì gõ địa chỉ. Cách này vừa nhanh vừa tránh gõ nhầm chữ cột.
Một mẹo nhỏ cho người hay quên: bấm vào nút fx nằm ngay bên trái thanh công thức, Excel mở hộp chèn hàm, tìm theo từ khoá và hiện từng ô đối số kèm lời giải thích. Google Sheets có thực đơn Insert, chọn Function với chức năng tương tự.
Tính tổng bằng SUM và nút AutoSum
Có ba cách ra cùng một kết quả. Người mới nên tập cả ba, rồi chọn cách mình thấy thuận tay.
- Gõ tay: bấm vào ô ngay dưới cột số, gõ =SUM( rồi kéo chuột chọn từ ô số đầu đến ô số cuối, gõ dấu đóng ngoặc, bấm Enter.
- Nút AutoSum: bấm vào ô ngay dưới cột số, trên thẻ Home (hoặc thẻ Formulas) bấm nút có chữ Σ. Excel tự đoán vùng cần cộng và tô viền nhấp nháy. Nhìn kỹ vùng đó, nếu đúng thì bấm Enter.
- Phím tắt: trên Windows là Alt và dấu bằng (Alt+=); trên Excel cho Mac thường là Cmd+Shift+T. Phím tắt làm y hệt nút Σ.
- Google Sheets: gõ tay như trên, hoặc chọn vùng số rồi bấm nút Σ (Functions) trên thanh công cụ và chọn SUM.
- Kiểm vùng: bấm vào ô tổng, nhìn thanh công thức. Vùng phải bắt đầu ở ô số đầu tiên, không lấy dòng tiêu đề, và kết thúc ở ô số cuối cùng.
Cái bẫy hay gặp nhất của SUM là thêm dòng mới ngay phía dưới vùng. Ví dụ tổng đang là =SUM(C2:C31), bạn chèn ngày 32 ở dòng 32 ngay trên ô tổng, nhiều phiên bản Excel tự mở rộng vùng nhưng không phải lúc nào cũng vậy. Thói quen an toàn là chèn dòng mới vào giữa vùng, hoặc sau khi thêm dòng thì bấm vào ô tổng kiểm lại địa chỉ cuối. AutoSum cũng dừng đoán khi gặp một ô trống giữa cột, nên luôn nhìn viền nhấp nháy trước khi bấm Enter.
AVERAGE: ô trống và số 0 không giống nhau
AVERAGE lấy tổng chia cho số ô có số. Điểm then chốt: ô trống và ô chữ bị bỏ qua, nhưng ô có số 0 được tính. Trong ví dụ giả định, quầy nước có doanh thu sáu ngày lần lượt là 1.200.000, 950.000, 1.100.000, 1.400.000, 1.800.000 và 1.650.000; ngày thứ tư nghỉ bán. Tổng sáu ngày là 8.100.000.
Nếu ô ngày nghỉ để trống, =AVERAGE(B2:B8) chia tổng cho 6 và ra 1.350.000, đây là doanh thu trung bình mỗi ngày có mở cửa. Nếu ô ngày nghỉ ghi 0, AVERAGE chia cho 7 và ra khoảng 1.157.143, đây là doanh thu trung bình mỗi ngày trong tuần, kể cả ngày đóng cửa. Hai con số đều đúng, nhưng trả lời hai câu hỏi khác nhau.
Vì vậy trước khi tính trung bình, bạn phải tự hỏi mình muốn biết điều gì, rồi chọn cách ghi ngày nghỉ cho phù hợp và ghi chú lại trong bảng. Bảng chung cho nhiều người nhập càng cần quy ước này: một người để trống, người khác ghi 0, trung bình sẽ sai mà không ai thấy.
Ô chứa chữ như "nghỉ" cũng bị AVERAGE bỏ qua, nên kết quả giống ô trống. Cách này tiện để đánh dấu ngày nghỉ, nhưng nếu cột đó còn dùng cho phép cộng trừ trực tiếp như =B5-B4 thì ô chữ sẽ gây lỗi #VALUE!. Muốn ghi lý do nghỉ, tốt hơn để một cột Ghi chú riêng.
COUNT, COUNTA, COUNTBLANK: ba hàm đếm khác nhau ở đâu
Người mới hay dùng COUNT để đếm số dòng trong danh sách rồi ngạc nhiên vì kết quả bằng 0. Lý do là mỗi hàm đếm một loại ô khác nhau.
| Hàm | Đếm những ô nào | Bỏ qua | Ví dụ dùng |
|---|---|---|---|
| COUNT | Ô chứa số, kể cả ngày tháng | Ô chữ, ô trống | Đếm số ngày có ghi doanh thu |
| COUNTA | Mọi ô không trống: số, chữ, công thức | Chỉ ô trống thật | Đếm số khách trong cột họ tên |
| COUNTBLANK | Ô trống | Ô có nội dung | Đếm số dòng còn thiếu số điện thoại |
| COUNTIF | Ô thoả một điều kiện | Ô không thoả | Đếm số ngày có doanh thu trên một mức |
Trong ví dụ quầy nước, =COUNT(B2:B8) ra 6 nếu ngày nghỉ để trống, ra 7 nếu ghi 0. Muốn đếm số khách trong danh sách tên, dùng =COUNTA(A2:A200), vì tên là chữ và COUNT không đếm chữ. COUNTIF ở hàng cuối là bước tiếp theo khi bạn muốn đếm có điều kiện, ví dụ đếm số ô ghi chữ x trong bảng chấm công; với nhiều điều kiện cùng lúc thì chuyển sang COUNTIFS, phần đó được viết kỹ ở trang AI Dữ Liệu.
MIN, MAX và thanh trạng thái
MIN và MAX tìm số nhỏ nhất, lớn nhất. Viết =MAX(B2:B8) ra 1.800.000, ngày bán chạy nhất trong ví dụ. =MIN(B2:B8) ra 950.000 nếu ngày nghỉ để trống, nhưng ra 0 nếu ngày nghỉ ghi 0. Lại một lần nữa, cách ghi ô ngày nghỉ quyết định kết quả.
Thanh trạng thái tính sẵn khi bạn chọn vùng. Chọn các ô B2 đến B8, nhìn góc dưới bên phải cửa sổ Excel: Average, Count và Sum hiện ngay. Bấm chuột phải vào thanh trạng thái để bật thêm Minimum, Maximum và Numerical Count.
Count ở thanh trạng thái giống COUNTA, không giống COUNT. Thanh trạng thái đếm mọi ô có nội dung. Numerical Count mới là đếm ô số như hàm COUNT. Hiểu khác biệt này giúp bạn phát hiện ô số bị lưu dạng chữ: nếu Count là 7 mà Numerical Count là 6, có một ô đang là chữ.
Dùng thanh trạng thái để kiểm công thức. Sau khi viết =SUM, chọn lại vùng đó và so Sum ở thanh trạng thái với ô tổng. Hai số phải bằng nhau. Nếu khác, công thức đang lấy thiếu hoặc thừa ô.
Google Sheets có ô tổng nhanh tương tự. Chọn vùng số, góc dưới bên phải hiện một ô nhỏ ghi Sum; bấm vào đó để đổi sang Average, Min, Max, Count. LibreOffice Calc cũng hiện tổng ở thanh trạng thái và cho chọn hàm bằng chuột phải.
Ráp lại: dựng khối tổng kết cho bảng doanh thu một tuần
Để các hàm trên thành thói quen, dựng một khối tổng kết nhỏ dưới bảng doanh thu. Làm một lần, tuần sau chỉ việc chép trang tính và nhập số mới.
- Ở trang tính mới, ô A1 gõ Ngày, ô B1 gõ Doanh thu (đồng), ô C1 gõ Ghi chú. Nhập bảy ngày vào A2 đến A8, doanh thu vào B2 đến B8, ngày nghỉ để trống ô doanh thu và ghi lý do ở cột C.
- Chừa dòng 9 trống cho dễ nhìn. Từ ô A10 trở xuống, gõ lần lượt các nhãn: Tổng tuần, Trung bình ngày có bán, Số ngày có bán, Ngày cao nhất, Ngày thấp nhất.
- Ô B10 viết =SUM(B2:B8), ô B11 viết =AVERAGE(B2:B8), ô B12 viết =COUNT(B2:B8), ô B13 viết =MAX(B2:B8), ô B14 viết =MIN(B2:B8).
- Chọn B2 đến B14, áp định dạng số có dấu ngăn nghìn, không số lẻ. Riêng ô B12 là số ngày nên đổi lại về định dạng số thường.
- Tô đậm các nhãn từ A10 đến A14 để tách khối tổng kết khỏi phần dữ liệu.
- Kiểm: chọn B2 đến B8, so Sum và Average trên thanh trạng thái với B10, B11. Với số liệu ví dụ giả định ở trên, B10 phải là 8.100.000, B11 là 1.350.000, B12 là 6.
- Đổi tên trang tính thành tên tuần, ví dụ Tuần 1. Tuần sau, bấm chuột phải vào thẻ trang, chọn Move or Copy, đánh dấu Create a copy để có bản sao, xoá số cũ ở B2 đến B8 và nhập số mới; khối tổng kết tự tính lại.
Khối tổng kết đặt dưới bảng như vậy dễ hiểu với người mới, nhưng có nhược điểm là khi bảng dài ra phải chèn dòng phía trên nó. Khi đã quen, nhiều người chuyển khối tổng kết sang bên phải bảng hoặc sang một trang riêng để bảng dữ liệu tự do kéo dài xuống dưới.
Hỏi AI và kiểm lại kết quả
Ba hàm này đơn giản nên AI hiếm khi viết sai cú pháp. Cái AI không biết là ý định của bạn và cách bảng của bạn ghi ô trống. Hãy nói rõ hai điều đó:
- "Excel trên Windows, vùng Việt Nam. Cột B từ B2 đến B32 là doanh thu từng ngày trong tháng, ngày nghỉ để trống. Muốn biết doanh thu trung bình mỗi ngày có bán và số ngày đã bán. Viết công thức và giải thích vì sao chọn hàm đó."
- "Google Sheets. Cột A là họ tên khách, cột C là số tiền đã trả, có dòng chưa trả để trống. Muốn đếm có bao nhiêu khách và bao nhiêu khách đã trả. Dùng hàm nào cho mỗi việc?"
- Khi muốn AI xem bảng, chỉ cần mô tả cột và vài giá trị giả. Tên khách, số điện thoại, doanh thu thật của cửa hàng không cần và không nên dán vào khung chat.
- Kiểm bằng tay một lần: lấy ba bốn ô đầu, cộng nhẩm hoặc dùng máy tính bỏ túi, rồi so với kết quả của hàm áp cho đúng ba bốn ô đó.
- Kiểm số đếm bằng mắt: nếu COUNT báo 25 ngày mà tháng có 31 ngày, đếm xem có đúng 6 ô trống không.
Khi AI đưa công thức có dấu phẩy mà máy bạn dùng chấm phẩy, Excel sẽ báo có vấn đề với công thức. Chỉ cần đổi dấu, không phải AI sai hàm.
Câu hỏi thường gặp
SUM có cộng được các ô nằm rải rác không liền nhau không?
Được. Giữ phím Ctrl (macOS: Cmd) và bấm từng ô hoặc từng vùng khi đang viết công thức, Excel tự thêm dấu ngăn giữa chúng. Kết quả dạng =SUM(B2;B5;D2:D10) trên máy vùng Việt Nam. Nhưng nếu phải cộng rải rác thường xuyên, có lẽ bảng nên được sắp lại cho gọn.
Vì sao SUM ra 0 dù cột có toàn số?
Gần như chắc chắn các ô đó là số lưu dạng chữ, thường do dán từ phần mềm khác hoặc gõ kèm dấu chấm, chữ đ. Chọn cột, nhìn thanh trạng thái: có Count mà không có Sum là dấu hiệu. Chuyển về số rồi SUM sẽ cộng đúng.
AVERAGE có tính các ô bị ẩn không?
Có, AVERAGE và SUM tính cả hàng bị ẩn bằng lệnh Hide. Nếu bạn lọc bảng và chỉ muốn tính những dòng đang hiện, cần dùng hàm SUBTOTAL hoặc AGGREGATE, đây là bước tiếp theo sau khi đã quen lọc dữ liệu. Thanh trạng thái thì chỉ tính các ô đang hiện khi lọc.
Có hàm nào cộng theo điều kiện, ví dụ chỉ cộng tiền chi ăn uống không?
Có, đó là SUMIF với một điều kiện và SUMIFS với nhiều điều kiện. Chúng là bước tiếp theo sau SUM, dùng nhiều trong bảng chi tiêu và báo cáo bán hàng. Trang AI Dữ Liệu có bài riêng hướng dẫn SUMIFS, COUNTIFS với ví dụ văn phòng.
Làm tròn trung bình thế nào cho dễ đọc?
Có hai cách. Bớt số lẻ bằng nút Decrease Decimal chỉ đổi cách hiện. Bọc hàm trong ROUND, ví dụ làm tròn kết quả AVERAGE về số nguyên, thì đổi cả giá trị. Với doanh thu tiền đồng, làm tròn tới nghìn đồng thường đủ để đọc và so sánh.
Đếm số dòng của danh sách dài thì dùng COUNT hay COUNTA?
Dùng COUNTA trên một cột mà dòng nào cũng phải có, ví dụ cột họ tên hoặc mã đơn. COUNT chỉ đếm ô số nên sẽ ra 0 với cột chữ. Cách khác là chọn cả cột rồi đọc Count ở thanh trạng thái, nhớ trừ đi một cho dòng tiêu đề.