Hướng dẫn cách viết công thức trong Excel đơn giản dễ hiểu

Hàm, công thức là một trong những nội dung đặc biệt quan trọng khi sử dụng Excel. Có cách viết công thức trong Excel nào nhanh, chính xác và hiệu quả? Trong bài viết này, Gitiho sẽ hướng dẫn các bạn chi tiết cách viết hàm, công thức và những kỹ thuật để viết nhanh hơn, chính xác hơn. Theo dõi ngay nhé!

Cách viết công thức trong Excel

Cách viết công thức trong ô tính trong ms excel vô cùng đơn giản, đây là thao tác căn bản giúp bạn sử dụng Excel trong công việc cũng như học tập.

Khi tìm hiểu về công thức, hàm trong Excel chúng ta sẽ thấy một đặc điểm chung là chúng không thay đổi trên hầu hết các phiên bản Excel. Ở một số phiên bản cao hơn chỉ có thêm các hàm, công thức mới mà thôi. Còn cách viết các hàm đã có sẵn thì không thay đổi.

• Để bắt đầu gõ công thức trong Excel, chúng ta sẽ bắt đầu với dấu bằng (=).
• Sau dấu bằng, bạn nhập hoặc là phép tính hoặc hàm.

Ví dụ: Để tính tiền lời của các mặt hàng tạp hóa, bạn có thể tính như sau:

• Gõ toàn bộ biểu thức: =E2+E3+E4+E5+E6+E7
• hoặc sử dụng hàm SUM như sau =SUM(E2: E7)
• Kết thúc thao tác này bằng cách sử dụng phím Enter trên bàn phím.

Ngoài ra, nếu bạn muốn học kiến thức Excel một cách đầy đủ và có hệ thống về các hàm, các công cụ trong Excel, ứng dụng Excel trong công việc…, hãy đăng ký ngay khóa học Excel online sau tại Gitiho:

Các thành phần của công thức Excel

Khi viết một công thức trong Excel, bạn sẽ gặp nhiều thành phần khác nhau. Sau đây chúng ta cùng tìm hiểu một số thành phần hay gặp:

Tên hàm: Khi sử dụng 1 công thức, bạn có thể sử dụng các phép tính trực tiếp như cộng, trừ, nhân, chia... hoặc thông qua các hàm. Các hàm là những phương pháp tính toán đã được lập trình từ trước. Bạn sẽ chỉ cần gọi tên hàm, khai báo các thành phần bên trong hàm là hàm sẽ tự động tính toán để cho ra kết quả.

Hằng số: là một giá trị số hoặc văn bản mà có nội dung cụ thể, giá trị cụ thể và không thay đổi, được viết trực tiếp trong công thức. Ví dụ: =3*5 thì số 3 và số 5 là hằng số.

Tham chiếu: là tọa độ của ô chứa các giá trị được đưa vào công thức tính. Có thể là 1 ô lẻ hoặc 1 vùng gồm nhiều ô nối tiếp nhau, hoặc hỗn hợp nhiều vùng ô.

Ví dụ: =SUM(A1, A2, B5)

Trong công thức này sẽ tính tổng các giá trị có trong 3 ô là ô A1, A2 và ô B5

Tên vùng ô: thay vì sử dụng tọa độ trong công thức, chúng ta có thể đặt tên cho các vùng ô trước, sau đó gọi tên các vùng này trong công thức. Như vậy cái bạn nhìn thấy sẽ là một cái tên, không phải hằng số cũng không phải tọa độ. Nhưng về bản chất đó chính là tọa độ. Tên vùng ô được sử dụng giống như các vùng ô:

Ví dụ: =SUM(cot_SoLuong)

Chúng ta tính tổng trên vùng ô đã được đặt tên là cot_Soluong

Tham khảo: Cách đặt tên vùng trong Excel

Toán tử: các ký hiệu đại diện cho các phép tính, hoặc phép so sánh trong công thức.

Các toán tử trong Excel rất đa dạng, hãy cùng tìm hiểu chi tiết hơn theo nội dung bên dưới:

Các ký hiệu toán tử trong công thức Excel

Có 4 loại toán tử trong Excel:

• Số học: để thực hiện các phép toán cơ bản: cộng, trừ, nhân, chia
• So sánh (hợp lý): để so sánh các giá trị: lớn hơn, nhỏ hơn...
• Kết nối: để nối các giá trị văn bản vào một chuỗi duy nhất, hoặc các ký tự đại diện cho 1 hoặc nhiều ký tự text
• Tham chiếu: để tạo ra các tham chiếu dạng cố định tọa độ

Sau đây chúng ta cùng tìm hiểu chi tiết hơn về các toán tử này!

Cách sử dụng toán tử số học trong công thức Excel

Các toán tử số học đại diện cho các phép tính cơ bản:

• dấu + là phép cộng
• dấu - là phép trừ
• dấu * là dấu nhân
• dấu / là phép chia
• dấu % là tính theo giá trị dạng phần trăm, nghĩa là số đó đã chia cho 100 (thường đi kèm với 1 hằng số)
• dấu ^ là đại diện cho số mũ (lũy thừa)

Ví dụ: Tính tổng tiền nhập hàng sữa như sau:

• =D2*C2 nghĩa là giá trị trong ô A2 sẽ nhân với giá trị là ô C2 để ra tiền nhập 102 thùng sữa là bao nhiêu.

Cách sử dụng toán tử so sánh trong các công thức Excel

Khi bạn cần sử dụng tới các phép so sánh trong một công thức tính để xét 1 biểu thức logic, hoặc khi muốn tính toán theo 1 khoảng, bạn sẽ cần sử dụng tới các toán tử so sánh.

Các toán tử này gồm:

• Dấu = có nghĩa là bằng nhau
• Dấu > có nghĩa là lớn hơn
• Dấu >= có nghĩa là lớn hơn hoặc bằng
• Dấu < có nghĩa là nhỏ hơn
• Dấu <= có nghĩa là nhỏ hơn hoặc bằng
• Dấu <> có nghĩa là khác nhau, không bằng

Ví dụ:

• =IF(A1>B1, "Đúng", "Sai") có nghĩa là xét trường hợp nếu (dùng hàm IF để xét) giá trị trong ô A1 lớn hơn giá trị trong ô B1 thì công thức sẽ cho kết quả là chữ Đúng, còn nếu giá trị trong ô A1 không lớn hơn B1 thì cho kết quả là chữ Sai.
• =COUNTIF(B1:B10,">=5") có nghĩa là đếm trong vùng B1:B10 có bao nhiêu giá trị lớn hơn hoặc bằng 5 (đếm theo điều kiện được thực hiện thông qua hàm COUNTIF).

Cách sử dụng toán tử nối văn bản trong Excel

Để nối các đoạn text lại với nhau, bạn có thể sử dụng dấu & (giữ phím shift và nhấn số 7 trên bàn phím).

Ví dụ: nếu bạn có mã quốc gia trong cột A và số điện thoại trong cột B, bạn có thể sử dụng công thức sau để nhận số điện thoại kết hợp với mã quốc gia:

=A1&" "&B1

Trong công thức trên, chúng ta thêm một dấu cách vào giữa 2 thành phần sau khi nối để làm cho các con số dễ đọc hơn:

Bạn cũng có thể làm được kết quả tương tự như trên khi sử dụng hàm CONCATENATE.

Cách sử dụng các toán tử tham chiếu trong công thức Excel

Để giúp việc viết hàm trở nên đơn giản và thuận tiện hơn, chúng ta có thể sử dụng một số dấu toán tử cho mục đích tham chiếu, bao gồm:

• Dấu hai chấm (:) cho phép bạn tham chiếu tới 1 vùng ô liên tiếp nhau, thay vì việc phải chỉ định riêng từng ô
  • Ví dụ: bạn muốn tính tổng trong 1 vùng liên tiếp nhau là các ô B1, B2, B3, B4, B5. Thay vì viết =SUM(B1, B2, B3, B4, B5) thì bạn có thể viết là =SUM(B1:B5). Cách viết này khiến công thức ngắn gọn và dễ viết hơn nhiều phải không nào.

Bạn cũng có thể dùng phương pháp này để tham chiếu trên cả 1 dòng hoặc cả 1 cột. Khi viết theo cách này bạn chỉ cần xác định tọa độ của 1 đối tượng (là dòng hoặc cột) mà thôi.

• Ví dụ: Tính tổng toàn bộ các số trong cột A: =SUM(A:A)
• Dấu phẩy (,) dùng để phân biệt các thành phần (tham số) trong một hàm.

Ví dụ: hàm IF trong Excel gồm 3 thành phần, khi viết ta có:

= IF(A1>0, “Đúng”, “Sai”) nghĩa là xem xét giá trị trong ô A1 có lớn hơn số 0 hay không, nếu có lớn hơn 0 thì trả về kết quả là chữ Đúng, còn không phải thì trả về kết quả là chữ Sai.

Trong một số trường hợp bạn có thể bắt gặp dấu chấm phẩy (;) được sử dụng để làm việc này (chứ không phải là dấu phẩy). Đây là do thiết lập trong máy tính của bạn tại Control pannel > Region > Number setting. Bạn hoàn toàn có thể thay đổi dấu này nếu muốn.

Tham khảo: Cách đổi dấu ngăn cách các thành phần trong hàm Excel từ dấu chấm phẩy sang dấu phẩy và ngược lại.

• Khoảng trắng (dấu cách) Dùng để xác định giá trị là giao điểm giữa 2 vùng ô.
  • Ví dụ: Bạn có 2 vùng ô là B3:D3 (dòng 3) và C2:C4 (cột C). Để xác định giá trị là giao điểm giữa 2 vùng này bạn có thể viết như sau:

=B3:D3 C2:D4

Kết quả thu được là số 180 tương ứng với vị trí ô C3 (vừa nằm ở dòng 3, vừa nằm ở cột C)

Các loại công thức Excel

Trong Excel có rất nhiều loại công thức khác nhau. bạn có thể viết:

• Công thức đơn: chỉ có duy nhất 1 công thức hoặc 1 hàm.
• Công thức phức tạp: Gồm nhiều hàm, nhiều biểu thức tính toán trên cùng 1 công thức. Các hàm có thể lồng vào nhau trong cùng 1 công thức... Điều này khiến bạn gặp khó khi viết công thức.

Chúng ta hãy cùng tìm hiểu cụ thể các cách viết này nhé:

Cách viết các phép tính trong Excel

Việc sử dụng các phép tính trong Excel cần tuân thủ các nguyên tắc về toán học:

• Các phép tính được thực hiện theo chiều từ trái qua phải, tính từ vị trí dấu bằng (để bắt đầu công thức tính).
• Phép tính nhân chia sẽ được thực hiện trước, các phép tính cộng trừ sẽ được thực hiện sau.
• Các tham số đặt trong dấu ngoặc đơn sẽ được tính toán trước, sau đó mới tính toán ở bên ngoài dấu ngoặc đơn.
• Nếu bắt đầu với dấu trừ (ví dụ =-A5+B6-...) thì tham số đầu tiên kèm theo dấu trừ được hiểu là số âm.

Như vậy khi viết công thức, các bạn phải hết sức chú ý đến thứ tự viết để đảm bảo nguyên tắc này.

Ví dụ: Chúng ta có bảng chứa các thông tin về xác định tiền bán hàng các sản phẩm và tính tiền hoa hồng, tiền thuế như sau:

Để xác định số thuế, ta có quy tắc tính:

Thuế = 10% của số tiền còn lại sau khi trừ đi tiền hoa hồng.

Khi viết biểu thức tính ta có:

• Với sản phẩm Cam, thành tiền ở ô D2, Hoa hồng ở ô E2, như vậy tiền thuế = (D2 - E2) * 10%

• Nhưng nếu viết dạng không có dấu ngoặc đơn thì D2-E2*10% thì kết quả sẽ khác. Biểu thức này sẽ tính E2*10% trước, sau đó mới lấy giá trị trong ô D2 trừ đi kết quả này. Điều này không đúng.

Khi viết dạng sử dụng dấu ngoặc, ta có phép tính D2-E2 được thực hiện trước, sau đó ra kết quả rồi mới nhân với 10% thuế suất.

Cách viết hàm Excel lồng nhau

Nguyên nhân của việc cần phải viết hàm lồng ghép là vì một hàm đôi khi không đảm bảo được kết quả chính xác ngay, mà phải qua nhiều bước tính toán mới ra. Do đó lồng hàm là điều không tránh khỏi, bạn cần phải làm quen với việc này nếu muốn sử dụng Excel tốt hơn.

Ví dụ như trong tình huống tính tiền thuế ở trên, chúng ta thấy để tính ra tiền thuế phải qua 3 bước tương ứng với 3 cột:

• Cột D để tính thành tiền = Số lượng * Đơn giá
• Cột E để tính Hoa hồng
• Cột F để tính tiền thuế.

Vậy chúng ta có thể chỉ cần 1 cột duy nhất để tính ra kết quả này không?

Câu trả lời là có. Hãy xem kết quả dưới đây:

tại ô D2 sử dụng công thức =(B2*C2*(1-5%))*10%

Kết quả vẫn đúng với cách tính ở trên. Trong công thức này chúng ta thấy thay vì tính riêng từng giá trị Thành tiền, Hoa hồng thì chúng ta sẽ tính gộp luôn trong cùng 1 biểu thức.

Nhận xét:

• Ưu điểm: giúp bảng tính đơn giản hơn, ít cột hơn
• Nhược điểm: công thức phức tạp hơn

Xét tiếp một ví dụ khác: Tính tổng số tiền thuế nhưng kết quả cần làm tròn tới phần nghìn (làm tròn hết các số hàng trăm, hàng chục, hàng đơn vị lên phần nghìn)

Chúng ta có công thức như sau:

=ROUND(SUM(D2:D6),-3)

Kết quả nhận được là giá trị tổng các số từ D2 tới D6 mà đã được làm tròn lên tới phần nghìn.

Trong đó chúng ta có 2 hàm:

• Hàm SUM(D2:D6) để tính tổng các số trong vùng D2:D6
• Hàm ROUND(..., -3) để làm tròn số đó (là kết quả của hàm SUM) lên tới phần nghìn (với tham số là -3)

Bạn hoàn toàn có thể tách riêng các hàm này ra thành nhiều ô, nhiều cột. Mỗi ô, mỗi cột sẽ dùng 1 hàm đơn để tính toán theo 1 hàm. Sau đó bạn dùng chính kết quả của hàm trước làm tham số cho hàm sau. Đây chính là bản chất của việc lồng ghép hàm. Khi đã quen dần với việc này, bạn có thể rút gọn quá trình viết công thức bằng cách lồng ghép những hàm đơn giản vào chung với nhau. Khi đó khả năng đọc hiểu và viết các hàm lồng ghép phức tạp của bạn sẽ được cải thiện nhiều.

Tham khảo:

Cách dùng hàm ROUND để làm tròn số trong Excel

Hướng dẫn cách lồng ghép nhiều hàm IF trong một công thức duy nhất

Cách viết công thức mảng trong Excel

Công thức mảng trong Excel là một dạng đặc biệt khi viết công thức. Nó làm cho Excel thông minh hơn khi tính toán theo những điều kiện phức tạp hơn bình thường, nhưng đồng thời nó lại khiến bạn khó khăn hơn trong việc đọc hiểu công thức này.

Để giúp bạn có thể có một khái niệm cơ bản về công thức mảng và cách hoạt động của công thức này, chúng ta sẽ xem xét ví dụ sau đây:

Tính tổng số tiền các sản phẩm có số lượng bán được lớn hơn 6

{=SUM((B2:B6>6)*(D2:D6))}

Khi kết thúc công thức này, bạn không sử dụng phím Enter như thông thường, mà phải sử dụng tổ hợp phím Ctrl + Shift + Enter. Đây là tổ hợp phím đặc trưng cho dạng công thức mảng.

Dấu { } được tạo ra ở bên ngoài công thức chính là do sử dụng tổ hợp phím Ctrl + Shift + Enter, bạn không phải gõ trực tiếp ký hiệu này vào công thức.

Giải thích kết quả:

Trong công thức trên ta thấy:

• Đầu tiên hàm sẽ xét các giá trị số lượng trong vùng B2:B6 giá trị nào lớn hơn 6. Cụ thể là tại dòng 3, dòng 4 và dòng 6 thỏa mãn điều kiện này.
• Tiếp theo ta tính tổng thành tiền tại cột D trên các dòng 3, 4, 6. Khi đó ta có 218000 + 216000 + 105000 = 449000.

Chú ý: Ở phiên bản Office 365 thì Excel tự động hiểu những công thức nào được tính toán theo mảng, nên bạn có thể viết công thức mảng như công thức bình thường mà Excel vẫn hiểu đúng được và tính toán đúng.

Kết luận

Như vậy với bài viết này, Gitiho đã chia sẻ cho các bạn cách viết công thức trong Excel. Hy vọng bài viết này sẽ hữu ích trong việc nâng cao khả năng viết công thức tính trong Excel của bạn. Nếu có bất kỳ thắc mắc gì bạn có thể góp ý, phàn hồi ngay dưới bài viết này để chúng tôi có thể giải quyết mọi thắc mắc của bạn một cách sớm nhất.

Tải về file mẫu trong bài viết

Bạn có thể tải về file mẫu sử dụng trong bài viết tại địa chỉ bên dưới: