Khám phá 150 hàm Excel phổ biến và ví dụ thực tế

Microsoft Excel không chỉ là công cụ bảng tính thông thường, mà còn là một nguồn sức mạnh đáng kinh ngạc cho những người làm việc với dữ liệu. Trong bài viết này, chúng tôi sẽ cùng bạn khám phá 150 hàm Excel thường dùng và minh họa để giúp bạn hiểu và áp dụng dễ dàng hơn trong công việc hàng ngày.

Hàm tính toán logic

Hàm TỔNG

Hàm TỔNG được sử dụng để tính tổng của tất cả các số trong một dãy ô. Đơn giản, Hàm TỔNG thực hiện nhiệm vụ cộng các giá trị số hoặc dãy số trong ô mà bạn muốn tính tổng.

Trong Đó:

• Số1, Số2, Số3: đại diện cho các giá trị số hoặc dãy số cần tính tổng.

Kết quả của hàm TỔNG là tổng của các giá trị đã chọn.

Ví dụ: Nếu bạn sử dụng hàm như sau: =TỔNG(20, 40, 60), điều này có nghĩa là bạn đang cộng các số lại với nhau và kết quả sẽ là 120.

Hàm MIN/MAX

Hàm MIN/MAX được sử dụng để xác định giá trị nhỏ nhất/lớn nhất trong một phạm vi dữ liệu hoặc trong toàn bộ bảng tính.

Trong đó

• Số1, Số2: Đây là các giá trị cần so sánh để tìm giá trị nhỏ nhất hoặc lớn nhất.

Kết quả của hàm là giá trị nhỏ nhất hoặc lớn nhất trong phạm vi dữ liệu đã chọn.

Ví dụ: Để tìm giá trị nhỏ nhất trong một phạm vi dữ liệu, bạn có thể sử dụng công thức như sau: =NHỎ(A2:C5). Kết quả sẽ là giá trị nhỏ nhất trong vùng dữ liệu từ ô A2 đến C5, và ví dụ này có thể trả về kết quả là 25 là giá trị nhỏ nhất cần tìm.

Trong đó:

• Số1, Số2: Đây là các giá trị cần so sánh để tìm giá trị lớn nhất.

Kết quả của hàm là giá trị lớn nhất trong phạm vi dữ liệu đã chọn.

Ví dụ: Để tìm giá trị lớn nhất trong một phạm vi dữ liệu, bạn có thể sử dụng công thức như sau: =LỚN NHẤT(A2:C5). Kết quả sẽ là giá trị lớn nhất trong vùng dữ liệu từ ô A2 đến C5, và trong ví dụ này, nếu có giá trị là 89, nó sẽ là giá trị lớn nhất cần tìm.

Hàm ĐẾM/ĐẾM.A

Hàm ĐẾM được sử dụng để đếm số lượng ô chứa giá trị số trong một vùng dữ liệu. Hàm ĐẾM.A được sử dụng để đếm số lượng ô không trống trong một vùng dữ liệu cụ thể.

Trong đó:

• Giá trị1, …: Tham chiếu ô hoặc phạm vi mà bạn muốn đếm số.

Ví dụ: Để tìm số lượng ô trong vùng dữ liệu chứa giá trị số, bạn có thể sử dụng công thức như sau: =ĐẾM(A2:C5). Kết quả sẽ là tổng số lượng ô có giá trị số trong vùng dữ liệu đã chọn.

Trong phần này:

• Giá trị1, …: Là các ô cần đếm hoặc một vùng cần đếm. Số lượng ô tối đa có thể đếm là 255 (với Excel từ 2007 trở lên) và tối đa 30 (với Excel từ 2003 trở xuống).

Ví dụ: Để đếm số lượng ô chứa kí tự hoặc số trong bảng dữ liệu, bạn có thể sử dụng công thức như sau: =ĐẾM.A(A2:C5). Kết quả sẽ là số lượng ô không trống chứa kí tự hoặc số trong vùng dữ liệu đã chọn.

Hàm TRUNG BÌNH

Hàm TRUNG BÌNH được sử dụng để tính trung bình cộng của một dãy số trong một bảng tính.

Trong phần này:

• số1 (Bắt buộc): Số thứ nhất, tham chiếu ô, hoặc phạm vi mà bạn muốn tính trung bình.
• số2, … (Tùy chọn): Các số, tham chiếu ô hoặc phạm vi bổ sung mà bạn muốn tính trung bình, tối đa là 255.

Đây là một trong 150 hàm excel thường dùng và ví dụ: Để tính lương trung bình dựa trên thông tin bảng dưới đây, bạn có thể sử dụng công thức như sau: =TRUNG.BÌNH(C3:C8). Kết quả sẽ là giá trị trung bình của lương trong 6 tháng.

Hàm ODD/EVEN

Hàm ODD trả về số được làm tròn lên số nguyên lẻ gần nhất.

Trong phần này:

• Số (Bắt buộc): Giá trị cần làm tròn lên số nguyên lẻ.

Khi bạn cần làm tròn số 4.6 lên số nguyên lẻ gần nhất, hãy sử dụng công thức: =ODD(4.6). Kết quả sẽ là 5.

Hàm EVEN sẽ giúp bạn làm tròn số nhập vào lên số nguyên chẵn gần nhất.

Đối với hàm này:

• Số (Bắt buộc): Giá trị cần làm tròn lên số nguyên chẵn.

Khi muốn làm tròn số 53.4 lên số nguyên chẵn gần nhất, hãy sử dụng công thức: =EVEN(53.4). Kết quả sẽ là 54.

Hàm điều kiện logic sẽ giúp bạn xử lý các tình huống logic trong bảng tính của bạn.

Để đếm số lượng ô thỏa mãn điều kiện nhất định trong một phạm vi dữ liệu.

Đây là cách sử dụng hàm COUNTIF trong Excel.

Trong hàm COUNTIF:

range là phạm vi dữ liệu cần đếm.

criteria là điều kiện để đếm.

Ví dụ: Đếm số lượng sản phẩm có số lượng tồn kho lớn hơn 150.

Kết quả sẽ hiển thị 8 sản phẩm có số lượng tồn kho lớn hơn 150, dựa trên điều kiện đã cho.

Hàm IF được sử dụng để kiểm tra một điều kiện và trả về giá trị tương ứng.

Điều kiện được kiểm tra trong hàm IF là Logical_test.

Value_if_true là giá trị trả về nếu điều kiện đúng.

Value_if_false là giá trị trả về nếu điều kiện sai.

Nếu bỏ trống Value_if_true và Value_if_false, hàm IF sẽ trả về 0 nếu điều kiện đúng và FALSE nếu điều kiện sai.

Ví dụ: Xác định việc một học sinh có qua môn dựa trên điểm số.

Điểm số từ 7 trở lên: Đạt.

Điểm số thấp hơn 7: Không Đạt.

Ví dụ: Sử dụng hàm IF để xác định việc một học sinh có qua môn hay không.

Hàm SUMIF được sử dụng để tính tổng các giá trị thoả mãn một tiêu chí cụ thể.

Hàm SUMIF là một trong 150 hàm excel thường dùng, giúp tính tổng các giá trị theo tiêu chí đã cho.

Các đối số của hàm SUMIF là:

range: Phạm vi ô để đánh giá theo tiêu chí.

criteria: Tiêu chí để so sánh với dữ liệu.

Ví dụ: Tính tổng số màu cam trong cột A dựa trên số lần xuất hiện trong cột B.

Hàm LEFT/RIGHT/MID được sử dụng để cắt chuỗi ký tự từ phía trái/phải/giữa của một chuỗi văn bản.

Hàm LEFT cắt chuỗi từ phía bên trái của một chuỗi văn bản.

Các tham số của hàm LEFT là:

text: Chuỗi văn bản chứa ký tự cần trích xuất.

num_chars: Số ký tự mà hàm LEFT sẽ trích xuất.

Hàm RIGHT được sử dụng để tách chuỗi ký tự từ phía bên phải của một chuỗi văn bản.

Các tham số của hàm RIGHT là:

text: Chuỗi văn bản chứa ký tự cần trích xuất.

Ví dụ: Để trích xuất 7 ký tự cuối cùng từ chuỗi văn bản.

Hàm MID được sử dụng để trả về một số lượng ký tự từ một chuỗi văn bản.

Các tham số của hàm MID là:

text: Chuỗi văn bản chứa các ký tự cần trích xuất.

Ví dụ: Trích xuất 7 ký tự từ chuỗi văn bản, bắt đầu từ vị trí thứ 3.

Hàm CONCAT được sử dụng để nối các chuỗi văn bản thành một chuỗi duy nhất.

Hàm CONCAT được dùng để gắn kết nhiều đoạn văn bản thành một chuỗi duy nhất.

Các tham số của hàm CONCAT là:

text1, text2, …: Các chuỗi văn bản cần được nối lại.

Ví dụ:

=CONCAT(“Điện”, ” “, “máy”, ” “, “XANH”, ” “, “Xin”, ” “, “Cám”, ” “, “ơn.”) trả về “Điện máy XANH Xin Cám ơn.”

Hàm CONCAT giúp kết hợp các đoạn văn bản thành chuỗi hoàn chỉnh.

Hàm LEN dùng để đếm số ký tự trong một chuỗi, bao gồm cả khoảng trắng.

Hàm LEN giúp xác định nhanh chóng kích thước của chuỗi văn bản.

Ví dụ:

Trong ví dụ, sử dụng hàm LEN để đếm số ký tự trong chuỗi “Điện máy XANH”.

Hàm ngày tháng dùng để xử lý dữ liệu liên quan đến thời gian.

Hàm NOW trả về ngày và giờ hiện tại.

Hàm NOW là một trong những hàm excel phổ biến, giúp hiển thị ngày và thời gian hiện tại.

Ví dụ:

=NOW(): Trả về ngày và thời gian hiện tại.

=NOW()+7: Trả về ngày và thời gian 7 ngày sau.

Hàm NOW giúp ghi lại ngày giờ thực hiện thay đổi và tính toán các giá trị dựa trên thời gian.

Hàm DATE được sử dụng để xử lý dữ liệu liên quan đến ngày tháng.

Hàm DATE trả về kết quả là một ngày cụ thể dưới dạng số.

Các tham số của hàm DATE là:

Year: chỉ năm.

Month: chỉ tháng.

Day: chỉ ngày.

Ví dụ:

Hàm tra cứu dữ liệu được dùng để tìm kiếm thông tin trong một bảng dữ liệu.

Hàm VLOOKUP dùng để tìm kiếm dữ liệu trong một bảng và trả về dữ liệu tương ứng.

Các tham số của hàm VLOOKUP là:

Lookup_value: Giá trị cần tìm kiếm.

Table_array: Phạm vi dữ liệu để tìm kiếm.

Ví dụ:

Để xác định mức phụ cấp cho nhân viên, hãy sử dụng công thức sau: =VLOOKUP(D4, $H$3:$I$8, 2, 0)

Giải thích công thức:

• Dấu $ giúp cố định các dòng và cột trong bảng khi sao chép công thức.

• 2 là số thứ tự của cột chứa dữ liệu.

• Range_lookup = 0 (FALSE) để tìm kiếm chính xác.

Hàm INDEX

Hàm INDEX trả về giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc một phạm vi nhất định.

Trong đó:

• array: Phạm vi ô hoặc một mảng số. Nếu mảng chỉ có một hàng hoặc cột, row_num hoặc column_num có thể là tùy chọn. Nếu mảng có nhiều hàng và nhiều hơn một cột và chỉ có một trong 2 đối số row_num hoặc column_num được khai báo, hàm sẽ trả về một mảng của toàn bộ hàng hoặc cột trong mảng.
• row_num: Thứ tự của hàng trong mảng chứa giá trị trả về. Đối số này là bắt buộc, trừ khi bạn khai báo column_num. Nếu bỏ qua row_num, bạn cần phải khai báo column_num.
• column_num: Thứ tự của cột trong mảng chứa giá trị trả về. Nếu bỏ qua column_num, bạn cần khai báo row_num.

Ví dụ:

Để tìm giá trị ở dòng 4, cột 1 trong mảng B4:E13, bạn nhập công thức: =INDEX(B4:E13,4,1). Giải thích công thức (theo thứ tự các đối số trong công thức từ trái sang phải): B4:E13 là mảng chứa giá trị cần trả về. 4 là số thứ tự của hàng trong mảng. 1 là số thứ tự của cột trong mảng. Sau khi nhập, nhấn Enter và nhận kết quả.

Hàm MATCH

Trong 150 hàm excel phổ biến và ví dụ, hàm MATCH là một trong những hàm không thể thiếu khi bạn cần tìm kiếm và xác định vị trí tương đối của một giá trị trong một mảng hoặc một ô.

Trong đó:

• Lookup_value: Giá trị bạn muốn tìm kiếm.
• Lookup_array: Mảng hoặc dải ô để xác định vị trí của Lookup_value. Vùng dữ liệu chỉ có thể có duy nhất 1 hàng hoặc 1 cột.
• Match_type: Xác định kiểu khớp là -1, 0, hoặc 1.

Kiểu khớp:

• 0: Trả về vị trí tương đối của giá trị trong trường hợp vùng dữ liệu chưa được sắp xếp.
• 1: Trả về vị trí của giá trị lớn nhất nhưng nhỏ hơn hoặc bằng giá trị tìm kiếm (vùng dữ liệu sắp xếp tăng dần).
• -1: Trả về vị trí của giá trị nhỏ nhất nhưng lớn hơn hoặc bằng giá trị tìm kiếm (vùng dữ liệu sắp xếp giảm dần).

Ví dụ:

Để tìm vị trí của “Lâm Thiên Trang” trong danh sách học sinh (A2:A9), dùng công thức: =MATCH(“Lâm Thiên Trang”, A2:A9, 0) tại ô G3.

Hàm HLOOKUP

Hàm HLOOKUP tìm giá trị trên dòng đầu tiên của bảng tham chiếu và trả về giá trị tương ứng trong dòng chỉ định.

Trong đó:

• Lookup_value: Giá trị so sánh với dòng đầu tiên của bảng tham chiếu để lấy giá trị cần tìm.
• Table_array: Địa chỉ tuyệt đối của bảng tham chiếu, không bao gồm cột tiêu đề.
• Row_index_Num: Số thứ tự của dòng chứa giá trị cần lấy trong bảng tham chiếu (bắt đầu từ 1).
• Range_lookup: Cách tìm kiếm trên bảng tham chiếu. “+0” cho tìm kiếm chính xác, “+1” cho tìm kiếm theo thứ tự tăng dần (mặc định).

Ví dụ:

Để phân loại học sinh trong Bảng 1 (B3:D8) dựa trên điểm số từ Bảng 2 (B11:F12), dùng công thức: =HLOOKUP(C4, $B$11:$F$12, 2, 1) tại ô D4. Hàm sẽ tìm điểm ở ô C4 trong Bảng 2 và trả về loại tương ứng từ hàng 2.

Bạn đã thấy sức mạnh của 150 hàm Excel thường dùng và ví dụ mà HR Insider cung cấp để nâng cao hiệu suất làm việc của bạn. Hãy thử và áp dụng chúng trong bảng tính của mình để làm việc hiệu quả hơn. Chúc bạn trở thành một chuyên gia Excel trong thời gian ngắn!