Mọi công thức trong Excel bắt đầu bằng dấu bằng, sau đó là các địa chỉ ô nối bằng dấu cộng (+), trừ (-), nhân (*) và chia (/); ví dụ =B2*C2 lấy số lượng nhân đơn giá. Excel tính nhân chia trước, cộng trừ sau, giống quy tắc học ở trường; muốn khác thứ tự thì đặt ngoặc. Viết xong một ô, bạn kéo nút điền xuống là cả cột có công thức, mỗi dòng tự dùng số của dòng mình.
Bài này dành cho người đã biết gõ dữ liệu vào ô và giờ muốn bảng tự tính. Ví dụ xuyên suốt là một bảng nhập hàng nhỏ với các cột Tên hàng, Số lượng, Đơn giá, Thành tiền; mọi con số là ví dụ giả định. Hướng dẫn theo Excel cho Windows, kèm khác biệt trên macOS và Google Sheets.
Vì sao phải viết địa chỉ ô thay vì gõ thẳng con số
Bạn hoàn toàn có thể gõ =12*45000 vào một ô và Excel trả 540000. Nhưng làm vậy là dùng Excel như máy tính bỏ túi. Sức mạnh thật của bảng tính nằm ở công thức dạng =B2*C2: ô Thành tiền không chứa con số, mà chứa một mối quan hệ "lấy ô số lượng nhân ô đơn giá". Khi nhà cung cấp đổi giá, bạn sửa ô đơn giá, thành tiền và mọi tổng phía sau tự đổi theo.
Có hai cách đưa địa chỉ ô vào công thức. Cách thứ nhất là gõ tay: gõ dấu bằng, gõ B2, gõ dấu sao, gõ C2, bấm Enter. Cách thứ hai dễ hơn cho người mới: gõ dấu bằng, bấm chuột vào ô B2, gõ dấu sao, bấm chuột vào ô C2, bấm Enter. Excel tự điền địa chỉ và tô màu khung ô được dùng, nhìn vào là biết công thức đang lấy số từ đâu.
Khi chọn lại ô vừa tính, ô hiện kết quả nhưng thanh công thức hiện =B2*C2. Đó là cách kiểm số một: luôn nhìn thanh công thức để biết ô là con số gõ tay hay con số tính ra. Trong một bảng làm lâu, lẫn vài ô gõ tay giữa cột công thức là lỗi phổ biến, vì người nào đó lỡ gõ đè lên kết quả.
Thêm một lợi ích ít người để ý: công thức có địa chỉ ô tự giải thích chính nó. Ba tháng sau mở lại bảng, bạn hay đồng nghiệp chỉ cần bấm vào ô là biết số ấy từ đâu ra, còn một con số gõ tay thì không ai còn nhớ nó được tính thế nào.
Các ký hiệu phép tính trên bàn phím
Bàn phím máy tính không có dấu nhân chéo hay dấu chia hai chấm như vở toán. Excel dùng các ký hiệu sau, và chúng giống nhau trên Google Sheets và LibreOffice Calc.
| Phép tính | Ký hiệu | Ví dụ công thức | Ghi chú |
|---|---|---|---|
| Cộng | + | =B2+B3 | Cộng nhiều ô liền nhau thì dùng hàm SUM cho gọn |
| Trừ | - | =C2-D2 | Ví dụ thu trừ chi ra số dư |
| Nhân | * (dấu sao) | =B2*C2 | Gõ chữ x sẽ báo lỗi hoặc thành chữ |
| Chia | / (gạch chéo) | =E2/B2 | Dấu hai chấm trong Excel nghĩa là vùng ô, không phải chia |
| Luỹ thừa | ^ | =B2^2 | Ít dùng ở văn phòng, có khi tính diện tích |
| Phần trăm | % | =B2*10% | 10% được hiểu là 0,1 |
| Nối chữ | & | =A2&" - "&B2 | Ghép tên hàng với mã thành một chuỗi |
Trên bàn phím laptop, dấu sao nằm ở phím số 8 (giữ Shift), dấu cộng ở phím dấu bằng (giữ Shift). Nếu bàn phím có cụm phím số bên phải, các dấu cộng, trừ, nhân, chia có sẵn ở đó, tiện cho người nhập nhiều số.
Thứ tự tính và dấu ngoặc
Excel tuân theo thứ tự quen thuộc: trong ngoặc trước, rồi luỹ thừa, rồi nhân và chia từ trái sang phải, cuối cùng là cộng và trừ. Lỗi người mới hay mắc nhất là tính trung bình hai ô bằng =B2+C2/2. Excel chia C2 cho 2 trước rồi mới cộng B2, nên với B2 là 8 và C2 là 6, kết quả là 11 chứ không phải 7. Công thức đúng là =(B2+C2)/2.
Ví dụ giả định trong bảng nhập hàng: thành tiền sau chiết khấu bằng số lượng nhân đơn giá nhân (1 trừ tỉ lệ chiết khấu). Viết thành =B2*C2*(1-D2). Nếu bỏ ngoặc thành =B2*C2*1-D2, Excel nhân xong rồi trừ đi 0,1, kết quả gần như không đổi và rất khó phát hiện bằng mắt vì con số trông vẫn hợp lý.
Cách tự kiểm đơn giản: với một dòng bất kỳ, tính tay bằng máy tính bỏ túi hoặc tính nhẩm với số tròn. Nếu kết quả của Excel khớp với cách tính trong đầu, công thức đúng; nếu lệch, xem lại ngoặc trước tiên. Khi công thức dài, Excel tô các cặp ngoặc bằng màu khác nhau lúc bạn đang sửa, giúp nhìn ra ngoặc nào đóng với ngoặc nào.
Thói quen tốt: khi không chắc thứ tự, cứ thêm ngoặc. Ngoặc thừa không làm sai công thức, chỉ làm nó dài hơn một chút; còn thiếu ngoặc là sai lặng lẽ.
Kéo công thức cho cả cột
Viết công thức một lần ở dòng đầu, rồi để Excel chép xuống các dòng còn lại. Đây là thao tác tiết kiệm thời gian nhất trong bảng tính.
- Viết công thức ở ô đầu tiên, ví dụ ô D2 là =B2*C2, bấm Enter và kiểm kết quả dòng này cho đúng.
- Chọn lại ô D2. Đưa chuột vào chấm vuông nhỏ ở góc dưới bên phải viền ô (nút điền) cho đến khi con trỏ thành dấu cộng mảnh màu đen.
- Cách nhanh: đúp chuột vào nút điền. Excel tự chép công thức xuống tới dòng cuối có dữ liệu ở cột bên cạnh.
- Cách thủ công: giữ chuột trái vào nút điền và kéo xuống đến dòng cần.
- Cách bằng phím: chọn từ D2 xuống dòng cuối, bấm Ctrl+D (macOS: Cmd+D) để chép công thức của ô trên cùng xuống cả vùng.
- Kiểm: bấm vào một ô ở giữa, ví dụ D15, nhìn thanh công thức phải là =B15*C15. Excel đã tự đổi số dòng cho từng ô, gọi là địa chỉ tương đối.
Google Sheets cũng có nút điền ở góc ô và cũng đúp chuột được; Sheets đôi khi còn hiện gợi ý tự điền cả cột, bạn bấm dấu tích để nhận. Nếu đúp chuột mà công thức dừng giữa chừng, cột bên cạnh có một ô trống ở đó; điền ô trống hoặc kéo tay qua đoạn đó.
Khi nào cần dấu $: địa chỉ tương đối và tuyệt đối
Tính năng tự đổi số dòng khi kéo công thức rất tiện, cho tới khi bạn cần mọi dòng cùng dùng một ô cố định. Ví dụ giả định: cửa hàng nhập hàng tính theo đô la Mỹ, tỉ giá ghi ở ô F1. Ô D2 viết =C2*F1 thì đúng, nhưng kéo xuống, ô D3 thành =C3*F2, ô D4 thành =C4*F3, toàn trỏ vào ô trống và ra kết quả bằng 0.
Cách sửa là khoá ô tỉ giá bằng dấu đô la: viết =C2*$F$1. Dấu $ trước chữ F khoá cột, dấu $ trước số 1 khoá hàng. Kéo xuống, phần C2 vẫn đổi thành C3, C4, còn $F$1 đứng yên. Bạn không cần gõ dấu $ bằng tay: khi con trỏ đang ở chữ F1 trong công thức, bấm phím F4 trên Windows, Excel tự thêm cả hai dấu. Trên Excel cho Mac thường dùng Cmd+T hoặc F4 tuỳ phiên bản và bàn phím; Google Sheets cũng nhận F4.
Quy tắc nhớ nhanh: ô thay đổi theo từng dòng thì để trơn, ô dùng chung cho cả bảng thì khoá $. Các ô dùng chung hay gặp là tỉ giá, đơn giá chung, tỉ lệ thuế, mức phụ cấp mỗi ngày công. Đặt chúng ở một góc riêng có ghi tên, ví dụ ô E1 ghi "Tỉ giá" và ô F1 chứa con số, để người khác biết sửa ở đâu.
Còn hai dạng khoá một nửa ($F1 hoặc F$1) dùng khi kéo công thức theo cả chiều ngang lẫn chiều dọc, như bảng cửu chương hay bảng giá theo hai chiều. Ở mức nhập môn, bạn chỉ cần nhớ kiểu khoá cả hai $F$1.
Các lỗi công thức hay gặp và cách xử lý
Khi công thức không ra kết quả mong đợi, ô thường hiện một mã lỗi. Đọc được mã là biết nên tìm ở đâu.
| Ô hiện ra | Nghĩa là gì | Thường do | Cách xử lý |
|---|---|---|---|
| #DIV/0! | Chia cho số 0 | Ô mẫu số đang trống hoặc bằng 0 | Nhập đủ số, hoặc dùng IF để để trống khi mẫu số bằng 0 |
| #VALUE! | Tính với thứ không phải số | Một ô là chữ, kể cả số bị lưu dạng chữ | Tìm ô dồn trái trong vùng, gõ lại số trần |
| #NAME? | Excel không hiểu một tên | Gõ sai tên hàm, thiếu ngoặc kép quanh chữ | Kiểm chính tả hàm, đặt chữ trong ngoặc kép |
| #REF! | Tham chiếu tới ô không còn | Đã xoá hàng hoặc cột mà công thức đang dùng | Bấm Ctrl+Z nếu vừa xoá, hoặc viết lại công thức |
| Hiện nguyên chữ =B2*C2 | Excel coi công thức là chữ | Ô đặt kiểu Text, hoặc có dấu cách trước dấu bằng | Đổi ô về General, sửa ô (F2) rồi Enter lại |
| Kết quả không đổi khi sửa số | Chế độ tính tay đang bật | Ai đó chuyển Calculation sang Manual | Thẻ Formulas, Calculation Options, chọn Automatic |
Lỗi cuối cùng trong bảng hiếm nhưng nguy hiểm vì không hiện mã gì: bảng trông bình thường mà số đã cũ. Nếu nghi ngờ, sửa thử một con số và xem tổng có đổi không.
Nhờ AI giải thích và tự kiểm từng dòng
Trợ lý AI giúp nhiều nhất ở hai việc: giải thích một công thức bạn chưa hiểu và tìm lý do công thức báo lỗi. Một vài cách hỏi hiệu quả:
- "Excel trên Windows, vùng Việt Nam. Ô D2 có công thức =B2*C2*(1-D$1). Giải thích từng phần bằng lời thường, và cho biết khi kéo xuống dòng 10 thì công thức thành gì."
- "Ô E2 báo #VALUE!, công thức là =C2-D2. Ô C2 hiện 1.500.000 dồn trái, ô D2 hiện 200000 dồn phải. Nguyên nhân có thể là gì?"
- "Muốn cột F là số dư luỹ kế: dòng 2 bằng thu trừ chi, các dòng sau bằng số dư dòng trên cộng thu trừ chi. Viết công thức cho F2 và F3, nói rõ kéo từ ô nào."
- Chỉ dán tên cột và vài con số giả, không dán bảng thật có tên khách hàng hay doanh thu cửa hàng.
- Nhận công thức từ AI rồi thử trên một dòng mà bạn biết sẵn đáp án, trước khi kéo cho cả cột.
Excel cũng có công cụ tự giải thích: chọn ô, vào thẻ Formulas, chọn Evaluate Formula để xem Excel tính từng bước ra sao. Kết hợp công cụ này với lời giải thích của AI, bạn sẽ hiểu công thức chứ không chỉ chép lại.
Câu hỏi thường gặp
Có thể viết công thức không cần dấu bằng ở đầu không?
Excel cũng nhận dấu cộng ở đầu và tự đổi thành dấu bằng, do thói quen từ phần mềm cũ. Nhưng nên tập gõ dấu bằng vì đó là quy ước chung của mọi phần mềm bảng tính, kể cả Google Sheets và LibreOffice Calc, và hướng dẫn nào cũng viết theo cách đó.
Vì sao kéo công thức xuống mà các dòng dưới ra cùng một kết quả?
Có hai khả năng. Một là bạn đã khoá $ cả những ô lẽ ra phải đổi theo dòng. Hai là chế độ tính đang ở Manual nên Excel chưa tính lại. Bấm vào một ô phía dưới xem thanh công thức có đổi số dòng không để biết là trường hợp nào.
Có nên viết công thức tham chiếu sang trang tính khác không?
Được, công thức có dạng tên trang, dấu chấm than rồi địa chỉ ô, cách dễ nhất là gõ dấu bằng rồi bấm sang trang kia chọn ô. Nhưng người mới nên giữ phép tính trong cùng trang cho tới khi quen, vì đổi tên hay xoá trang có thể làm công thức báo lỗi #REF!.
Trừ hai ngày cho nhau có được không?
Được, vì ngày trong Excel là con số bên trong. Ngày trả hàng trừ ngày nhận hàng cho ra số ngày. Nếu ô kết quả hiện thành một ngày lạ, chỉ cần đổi định dạng ô đó sang General hoặc Number. Phép tính với ngày làm việc và ngày lễ phức tạp hơn, thuộc phần hàm ngày tháng nâng cao.
Sao chép một ô công thức sang chỗ khác thì địa chỉ có đổi không?
Có, địa chỉ tương đối đổi theo khoảng cách bạn dán. Dán sang phải một cột thì chữ cột tăng một, dán xuống hai dòng thì số dòng tăng hai. Ô khoá $ giữ nguyên. Muốn chép đúng con số kết quả, dùng Paste Special và chọn Values.
Nhờ AI viết công thức rồi dán vào báo lỗi ngay, phải làm sao?
Lý do hay gặp nhất là dấu ngăn cách: AI viết dấu phẩy trong khi máy vùng Việt Nam dùng chấm phẩy. Lý do thứ hai là dấu ngoặc kép kiểu cong khi chép từ khung chat. Thay hai thứ này rồi thử lại, và báo cho AI biết máy dùng dấu nào cho những lần sau.