Công thức Excel là một phép tính bắt đầu bằng dấu bằng, giúp Excel tự tính và tự cập nhật kết quả khi số liệu thay đổi. Bài này hướng dẫn bạn cách viết một công thức từ đầu, rồi dùng cùng một bảng đơn hàng để làm 8 công thức người mới cần biết.

Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn các phần trong ngoặc của hàm bằng dấu chấm phẩy.

Một công thức Excel gồm những phần nào?

Một công thức có thể gồm các phần sau:

Thành phần

Ví dụ

Thành phần này làm gì

Dấu bằng

=

Dấu này báo cho Excel biết ô chứa công thức.

Địa chỉ ô

D2

Địa chỉ này lấy giá trị đang nằm trong ô D2.

Vùng ô

F2:F6

Vùng này gồm các ô từ F2 đến F6.

Phép tính

+ - * / ^

Các ký hiệu này là cộng, trừ, nhân, chia và lũy thừa.

Hàm

SUM(F2:F6)

Hàm là phép tính được Excel viết sẵn. Các giá trị đặt trong ngoặc của hàm được gọi là tham số.

Bạn nên dùng địa chỉ ô thay vì gõ trực tiếp con số. Khi đó, bạn chỉ cần sửa số trong ô, và công thức tự tính lại. Một vài ví dụ trong bài gõ trực tiếp mốc và mã hàng để bạn dễ đọc. Khi làm thật, bạn nên đặt các giá trị này vào ô riêng.

Excel tính theo thứ tự nào?

Excel tính phần trong ngoặc trước, rồi đến lũy thừa, rồi đến nhân chia, cuối cùng là cộng trừ.

Công thức

Kết quả

Excel tính thế nào

=2+3*4

14

Excel nhân trước, rồi mới cộng.

=(2+3)*4

20

Excel tính trong ngoặc trước.

=2^3

8

Excel tính 2 mũ 3.

=100*10%

10

Excel coi 10% là 0,1.

Bạn chuẩn bị dữ liệu như thế nào?

Bạn gõ bảng sau vào một sheet mới, với ô A1 là tiêu đề Mã hàng:

Mã hàng

Tên hàng

Trạng thái

Số lượng

Đơn giá

SP01

Bàn phím

Đã giao

12

250000

SP02

Chuột

Đã giao

30

120000

SP03

Màn hình

Chờ giao

5

2600000

SP04

Tai nghe

Đã giao

18

310000

SP05

Loa

Chờ giao

7

450000

Từ ô F1 đến ô H1, bạn gõ các tiêu đề: Thành tiền, Tiền thuế và Đánh giá. Từ ô J1 đến ô J8, bạn gõ lần lượt: Chỉ số, Tổng tiền, Trung bình mỗi đơn, Đơn lớn nhất, Số đơn đã giao, Tiền đơn đã giao, Tên hàng của SP03 và Thuế suất. Ô K1 là tiêu đề Kết quả, và ô K8 là 10%.

Bảng trong ảnh được bật dấu phân cách nghìn, tô màu và nới rộng cột cho dễ đọc. Nếu máy của bạn hiện 28330000 thay vì 28,330,000, kết quả vẫn đúng. Khi ô hiện #### hoặc chữ bị cắt, bạn bấm đúp vào cạnh phải của tiêu đề cột để nới rộng cột.

Bảng đơn hàng 5 dòng và bảng chỉ số bên phải
Các ô tô vàng nhạt là nơi bài sẽ gõ công thức

Công thức 1: Bạn nhân hai ô để tính thành tiền

Bạn chọn ô F2, gõ dấu bằng, bấm vào ô D2, gõ dấu sao, bấm vào ô E2. Bạn cũng có thể gõ trực tiếp:

=D2*E2

Ô F2 đang gõ =D2*E2, ô D2 và E2 được tô màu
Excel tô màu các ô đang được dùng trong công thức để bạn dễ kiểm tra

Bạn nhấn Enter. Tiếp theo, bạn chọn lại ô F2 và đưa chuột tới chấm vuông ở góc dưới bên phải ô. Khi con trỏ thành dấu cộng đen mảnh, bạn giữ chuột trái, kéo xuống dòng 6 rồi thả chuột. Bạn có thể bỏ qua biểu tượng nhỏ hiện ra ở cuối vùng vừa kéo.

Con trỏ chuột là dấu cộng đen mảnh ở góc dưới bên phải ô F2
Dấu cộng đen mảnh báo hiệu bạn có thể kéo công thức xuống
Cột F đã có kết quả cả 5 dòng, ô F4 đang chọn có công thức =D4*E4
Khi bạn kéo xuống, công thức ở ô F4 tự đổi thành =D4*E4

Công thức 2: Bạn tính tổng bằng hàm SUM

Bạn gõ công thức sau vào ô K2 rồi nhấn Enter:

=SUM(F2:F6)

Ô K2 hiện 28,330,000
Hàm SUM cộng tất cả các ô từ F2 đến F6

Công thức 3: Bạn tính trung bình bằng hàm AVERAGE

Bạn gõ công thức sau vào ô K3 rồi nhấn Enter:

=AVERAGE(F2:F6)

Ô K3 hiện 5,666,000
Giá trị trung bình bằng tổng chia cho 5 đơn

Công thức 4: Bạn tìm số lớn nhất bằng hàm MAX

Bạn gõ công thức sau vào ô K4 rồi nhấn Enter:

=MAX(F2:F6)

Ô K4 hiện 13,000,000
Đơn lớn nhất là đơn màn hình trị giá 13 triệu đồng

Khi cần tìm số nhỏ nhất, bạn đổi thành hàm MIN với cùng cách viết.

Công thức 5: Bạn phân loại bằng hàm IF

Hàm IF kiểm tra một điều kiện và trả về một trong hai kết quả. Nếu điều kiện đúng, hàm trả về kết quả thứ nhất. Nếu điều kiện sai, hàm trả về kết quả thứ hai. Bạn gõ công thức sau vào ô H2 rồi kéo xuống dòng 6:

=IF(F2>=5000000,"Đơn lớn","Đơn nhỏ")

Cột H hiện Đơn nhỏ, Đơn nhỏ, Đơn lớn, Đơn lớn, Đơn nhỏ
Đơn từ 5 triệu đồng trở lên được ghi là Đơn lớn

Ở ví dụ này, nếu ô F2 lớn hơn hoặc bằng 5000000, Excel ghi Đơn lớn, còn nếu không thì Excel ghi Đơn nhỏ. Dấu >= nghĩa là lớn hơn hoặc bằng. Phần chữ trong công thức phải nằm trong dấu ngoặc kép. Bạn có thể đọc thêm bài 5 cách dùng hàm IF.

Công thức 6: Bạn đếm theo điều kiện bằng hàm COUNTIF

Bạn gõ công thức sau vào ô K5 rồi nhấn Enter:

=COUNTIF(C2:C6,"Đã giao")

Ô K5 hiện 3
Có 3 đơn mang trạng thái Đã giao

Tham số thứ nhất là vùng cần đếm, còn tham số thứ hai là điều kiện. Bạn có thể đọc thêm bài hàm đếm số lượng trong Excel.

Công thức 7: Bạn cộng theo điều kiện bằng hàm SUMIF

Bạn gõ công thức sau vào ô K6 rồi nhấn Enter:

=SUMIF(C2:C6,"Đã giao",F2:F6)

Ô K6 hiện 12,180,000
Hàm SUMIF chỉ cộng thành tiền của các đơn Đã giao

Excel tìm chữ Đã giao trong cột C, rồi cộng các ô tương ứng ở cột F. Bạn có thể đọc thêm bài 7 ví dụ dùng hàm SUMIF.

Công thức 8: Bạn tra cứu bằng hàm VLOOKUP

Bạn gõ công thức sau vào ô K7 rồi nhấn Enter:

=VLOOKUP("SP03",A2:B6,2,FALSE)

Ô K7 hiện Màn hình
Hàm VLOOKUP trả về tên hàng của mã SP03

Hàm VLOOKUP trong ví dụ này có 4 tham số:

Tham số

Giá trị

Tham số này là gì

Giá trị cần tìm

"SP03"

Đây là mã hàng cần tìm.

Bảng tra

A2:B6

Cột đầu tiên của bảng phải chứa mã hàng.

Cột trả về

2

Excel lấy kết quả ở cột thứ 2 của bảng tra.

Kiểu dò tìm

FALSE

Excel tìm chính xác.

Bạn có thể đọc thêm bài hàm VLOOKUP trong Excel.

Dấu $ trong công thức Excel có tác dụng gì?

Khi bạn kéo công thức xuống, Excel tự đổi địa chỉ ô theo dòng. Điều này đúng với phép nhân ở công thức 1, nhưng sai khi mọi dòng cần dùng chung một ô.

Ví dụ, cột Tiền thuế dùng chung thuế suất ở ô K8. Bạn gõ công thức sau vào ô G2 rồi kéo xuống dòng 6:

=F2*$K$8

Cột G đã có tiền thuế, ô G6 có công thức =F6*$K$8
Xuống tới dòng 6, F2 đã đổi thành F6, còn $K$8 vẫn giữ nguyên

Nếu bạn gõ =F2*K8 rồi kéo xuống, ô G3 sẽ dùng K9 là ô trống, nên kết quả bằng 0. Bạn có thể nhấn F4 ngay sau khi gõ địa chỉ ô để Excel tự thêm dấu $. Trên laptop, bạn có thể cần nhấn Fn + F4. Khi nhấn tiếp, Excel lần lượt đổi sang các kiểu còn lại.

Khi kéo xuống, chỉ phần dòng có thể đổi. Khi kéo ngang, chỉ phần cột có thể đổi. Dấu $ khóa phần đứng ngay sau nó.

Cách viết

Kéo xuống 1 ô

Kéo sang phải 1 ô

K8

K9

L8

$K$8

$K$8

$K$8

$K8

$K9

$K8

K$8

K$8

L$8

Những lỗi công thức nào hay gặp?

Bạn thử các ví dụ dưới đây ở một ô trống bên dưới bảng, ví dụ ô A10, để không ghi đè lên dữ liệu.

Lỗi bạn thấy

Ví dụ

Nguyên nhân

Cách xử lý

#DIV/0!

=F2/0

Công thức chia cho 0 hoặc cho ô trống.

Bạn kiểm tra ô ở mẫu số.

#VALUE!

=D2*B2

Công thức đem số nhân với chữ.

Bạn kiểm tra ô có đang chứa chữ không.

#NAME?

=SUMM(D2:D6)

Excel không nhận ra tên hàm.

Bạn sửa lại tên hàm cho đúng.

#N/A

=VLOOKUP("SP99",A2:B6,2,FALSE)

Excel không tìm thấy giá trị cần tra.

Bạn kiểm tra lại giá trị cần tìm.

Ô hiện #### hoặc 1E+07

Không có

Cột quá hẹp nên không đủ chỗ hiện số.

Bạn nới rộng cột. Nếu ô vẫn hiện 1E+07, bạn đổi định dạng ô sang Number.

Người mới thường hỏi gì về công thức Excel?

Công thức và hàm khác nhau như thế nào?

Công thức là toàn bộ nội dung bắt đầu bằng dấu bằng, còn hàm là phép tính có sẵn nằm trong công thức. Ví dụ, =SUM(F2:F6)*10% là một công thức có dùng hàm SUM.

Vì sao ô hiện chữ công thức thay vì kết quả?

Ô đó có thể đang ở định dạng Text, hoặc Excel đang bật chế độ hiện công thức. Bạn có thể đọc bài hiển thị công thức trong Excel để biết cách xử lý.

Tôi nên học tiếp những hàm nào?

Bạn có thể đọc bài 16 hàm Excel cơ bản dân văn phòng nên biết, rồi luyện thêm các hàm tra cứu và xử lý ngày tháng.

Bạn muốn học Excel bài bản hơn?

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.