Tổng quát công thức và 10 hàm trong Excel thông dụng nhất

Top 10 hàm trong Excel thông dụng nhất và kiến thức tổng quát về công thức Excel sẽ được bật mí trong bài viết này. Đọc ngay để biết ứng dụng các hàm đơn giản cho công việc của nhiều ngành nghề khác nhau.

Công thức là một biểu thức tính giá trị của một ô. Còn hàm là các công thức đã được xác định trước và có sẵn trên Excel.

Ví dụ như chúng ta có ô A3 ở dưới đây đã chưa một công thức. Nó là công thức tính tổng của giá trị ô A1 và giá trị ô A2.

Hay ví dụ như trong ô A3 dưới đây lại chứa Hàm tính tổng. Hàm này cho phép tính tổng phạm vi A1:A2.

Nhập công thức

Để nhập một công thức chúng ta thực hiện theo các bước sau.

Bước 1. Chọn một ô trống.

Bước 2. Chúng ta sẽ bắt đầu công thức bằng cách nhập dấu bằng (=). Đây chính là dấu hiệu nhận biết để Excel sẽ biết ta muốn nhập công thức để tính toán.

Bước 3. Ví dụ ta nhập công thức A1+A2.

Mẹo: Thay vì chúng ta gõ chữ A1 và A2 thì ta có thể nhấn chuột chọn ô A1 và A2. Thao tác này đơn giản và giúp ta tiết kiệm thời gian hơn rất nhiều.

Bước 4. Thay giá trị trong ô A1 thành 3.

Lúc này Excel sẽ tự động tính lại giá trị của ô A3. Có thể nói đây là một trong những tính năng ưu việt nhất của Excel.

Chỉnh sửa công thức

Khi ta chọn một ô trên bảng tính Excel, Excel sẽ trình bày giá trị hoặc công thức có trong ô trên thanh công thức (formula bar). Giống như hình minh họa dưới đây.

Bước 1. Để chỉnh sửa công thức, chúng ta nhấn chọn thanh công thức và thay đổi công thức tại đây.

Bước 2. Nhấn phím Enter.

Thứ tự tính toán

Excel sử dụng thứ tự mặc định để tính toán. Excel ưu tiên phần công thức nằm trong ngoặc đơn để tính toán trước. Sau đó thực hiện các phép tính nhân và chia. Khi hoàn tất thì Excel sẽ cộng và trừ phần còn lại trong công thức. Giống như ví dụ minh họa bên dưới.

Đầu tiên Excel sẽ thực hiện phép nhân (A1 * A2). Tiếp theo Excel sẽ cộng giá trị ô A3 vào kết quả vừa tính được để ra đáp án.

Chúng ta có một ví dụ khác sau đây.

Đầu tiên Excel sẽ tính phần công thức nằm trong dấu ngoặc đơn (A2+A3). Tiếp theo sẽ đem kết quả tính được nhân với giá trị ô A1 để ra đáp án cần tính.

Sao chép và Dán một công thức

Khi chúng ta sao chép một công thức, Excel sẽ tự động điều chỉnh lại các ô tham chiếu cho mỗi ô mới được dán công thức vào đó. Chúng ta có ví dụ minh họa dễ hiểu cho tình huống này qua các bước thực hiện như dưới đây.

Bước 1. Nhập công thức sau vào ô A4.

=A1*(A2+A3)

Bước 2. Chọn ô A4, nhấn chuột phải và sau đó nhấn chọn Copy (hoặc nhấn tổ hợp phím Ctrl + C trên bàn phím).

Tiếp theo nhấn chọn ô B4, tiếp tục nhấn chuột phải và chọn Paste dưới mục Paste Option như hình dưới đây (hoặc nhấn tổ hợp phím Ctrl + V trên bàn phím).

Chúng ta cũng có thể kéo công thức tới ô B4. Bằng cách nhấn chuột chọn ô A4, nhấp chuột vào góc phải bên dưới của ô A4 và kéo sang ô B4. Lúc này công thức tương đồng sẽ được đem áp dụng cho ô B4, đồng thời các ô tham chiếu trong công thức cũng được điều chỉnh theo.

Chúng ta thu được kết quả như dưới đây, công thứ trong ô B4 đã sử dụng các ô tham chiếu mới là các giá trị trong cột B.

Chèn Hàm vào trang tính

Mỗi hàm đều có cấu trúc tương tự nhau. Ví dụ như hàm tính tổng SUM(A1:A4). Tên của hàm này là SUM. Phần giữa dấu ngoặc (đối số) là phạm vi dữ liệu đầu vào A1:A4. Hàm này cho phép tính tổng các giá trị trong các ô A1, A2, A3 và A4.

Việc nhớ mỗi hàm và đối số cho mỗi tác vụ rất khó khăn, cho nên tính năng chèn hàm này trong Excel sẽ giúp chúng ta thao tác dễ dàng hơn.

Để chèn một hàm vào trang tính Excel ta sẽ thực hiện theo các bước sau đây.

Bước 1. Nhấn chọn một ô.

Bước 2: Nhấn vào nút Insert Function.

Lúc này hộp thoại Insert Function sẽ xuất hiện.

Bước 3. Tìm kiếm một hàm hoặc chọn một hàm từ danh sách gợi ý. Ví dụ như trong tình huống này ta chọn hàm COUNTIF từ trong danh sách gợi ý.

Bước 4. Nhấn nút OK.

Lúc này một hộp thoại mới có tên là Function Arguments sẽ xuất hiện.

Bước 5. Nhấn vào hộp Range và chọn phạm vi dữ liệu là A1:C2.

Bước 6. Trong hộp Criteria ta gõ >5.

Bước 7. Nhấn nút OK.

Tới đây ta sẽ thu được kết quả từ hàm Countif, đếm số ô lớn hơn 5.

Chú ý: Thay vì sử dụng tính năng chèn hàm (Insert Function) chúng ta có thể thực hiện đơn giản hơn bằng cách gõ =COUNTIF(A1:C2,">5"). Khi ta nhập đến =COUNTIF( thì thay vì gõ A1:C2 ta sẽ nhấn và di chuột bôi đen hết phạm vi ô A1:C2.

Top 10 hàm thông dụng nhất

Dưới đây là tổng quan về 10 hàm Excel được sử dụng nhiều nhất cùng các thủ thuật có liên quan được bổ sung để tạo công thức trong Excel.

1. COUNT

Để đếm số các ô có chứa các số ta sử dụng hàm COUNT.

Chú ý: sử dụng COUNTBLANK và COUNTA để đếm các ô trống hoặc không trống trong Excel. COUNTA là viết tắt của count all (đếm tất cả).

Học thêm về các hàm COUNT tại đây.

2. SUM

Để tính tổng một phạm vi các ô, chúng ta sử dụng hàm SUM (giống như hình thứ hai trong trang này). Ta cũng có thể sử dụng hàm SUM để tính tổng toàn bộ một cột trên Excel.

Chú ý: =SUM(5:5) sẽ tính tổng tất cả các giá trị trong hàng thứ 5 trên bảng tính. Để tạo ra các công thức hữu ích hơn ta có thể kết hợp hàm SUM với các hàm Excel khác.

Học thêm về các hàm SUM tại đây.

3. IF

Hàm IF cho phép kiểm tra một phạm vi dữ liệu có đáp ứng một điều kiện hay không và trả về các giá trị nếu đúng và giá trị khác nếu sai.

Chú thích: Nếu như điểm lớn hơn hoặc bằng 60 thì hàm IF sẽ trả kết quả là Pass (qua môn), nếu không sẽ trả Fail vào ô kết quả (không đạt). Chúng ta có thể nhanh chóng sao chép công thức này vào các ô khác bằng cách nhấp chuột vào góc phải bên dưới cùng của ô C2 và kéo nó xuống hết ô C6. Như vậy công thức sẽ được áp dụng tương tự với ô tham chiếu mới tương ứng.

Học thêm về cách dùng hàm IF tại đây.
 

4. AVERAGE

Để tính giá trị trung bình của một tập hợp số, chúng ta sẽ sử dụng hàm AVERAGE (không có phương pháp nào nhanh hơn cách này). Công thức dưới đây sẽ tính giá trị trung bình của 3 số lớn nhất trong phạm vi dữ liệu A1:A6.

Chú thích: Hàm LARGE trả về kết quả là 3 số lớn nhất trong phạm vi A1:A6.

Học thêm về cách dùng hàm AVERAGE tại đây.
 

5. COUNTIF

Hàm COUNTIF dưới đây giúp ta đếm số các ô cho giá trị chính xác là STAR không có số seri kèm theo hoặc kèm theo nhiều kí tự hơn.

Chú thích: Dấu hoa thị (*) tương ứng với một chuỗi số 0 hoặc nhiều ký tự hơn. 

Học thêm về cách dùng hàm COUNTIF tại đây.
 

6. SUMIF

Hàm SUMIF dưới đây cho phép ta tính tổng các giá trị trong phạm vi B1:B5 nếu các ô tương ứng trong phạm vi A1:A5 chứa chính xác kí tự circle kèm một ký tự.

 Chú thích: Dấu hỏi chấm (?) tương ứng với đúng một ký tự.

Học thêm về cách dùng hàm SUMIF tại đây.
 

7. VLOOKUP

Hàm VLOOKUP bên dưới cho phép ta tra cứu giá trị 53 (đối số đầu tiên) trong cột ngoài cùng bên trái của bảng màu đỏ (đối số thứ hai). Giá trị 4 (đối số thứ ba) cho biết hàm VLOOKUP trả về giá trị tương ứng trong cùng một hàng từ cột thứ tư của bảng màu đỏ.

Học thêm về cách dùng hàm VLOOKUP tại đây.

8. INDEX và MATCH

Sử dụng hàm INDEX và MATCH trong Excel để thực hiện các thao tác tra cứu nâng cao (dành cho người giàu kinh nghiệm sử dụng Excel). Chúng ta có ví dụ dưới đây minh họa việc sử dụng INDEX và MATCH để tra cứu 2 cột.

Chú ý: công thức trên tra cứu mức lương của James Clark mà không phải James Smith hay James Anderson. Chúng ta có thể sử dụng INDEX và MATCH trong Excel để gây ấn tượng tốt trong mắt sếp của bạn.

Học thêm về cách dùng hàm INDEX và MATCH tại đây.

9. MIN và MAX

Để tìm giá trị lớn nhất ta sủe dụng hàm MIN. Để tìm giá trị lớn nhất ta sử dụng hàm MAX. Chúng ta có ví dụ đơn giản như hình dưới đây.

Học thêm về cách dùng hàm MIN và MAX tại đây.
 

10. SUMPRODUCT

Để tính tổng các tích của các số tương ứng trong phạm vi dữ liệu xác định, chúng ta có thể sử dụng hàm SUMPRODUCT. Đây là một hàm rất mạnh của Excel.

Giải thích: Hàm SUMPRODUCT thực hiện phép tính sau: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500. Hàm SUMPRODUCT là một hàm linh hoạt tạo ra kết quả đáng ngạc nhiên, chúng ta có thể kết hợp hàm này với nhiều hàm khác để giải các vấn đề phức tạp.

Học thêm về cách dùng hàm SUMPRODUCT tại đây.