Khám Phá 150 Hàm Excel Thường Dùng và Ví Dụ Thực Tế

Microsoft Excel không chỉ là một công cụ bảng tính thông thường, nó còn là một nguồn lực vô cùng mạnh mẽ 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à ví dụ cụ thể để giúp bạn hiểu và áp dụng chúng dễ dàng trong công việc hàng ngày của mình.

Hàm Tính Toán Logic

Hàm SUM

Hàm SUM được dùng để tính tổng của tất cả các số trong một dãy ô. Đơn giản, SUM 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: là các giá trị số hoặc dãy số cần tính tổng.

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

Ví dụ: Nếu bạn sử dụng hàm như sau: =SUM(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 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: =MIN(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: 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: =MAX(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 COUNT/COUNTA

Hàm COUNT được sử dụng để đếm số lượng ô chứa giá trị số trong một vùng dữ liệu. Hàm COUNTA đượ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: =COUNT(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 này:

• Giá trị1, …: Đại diện cho các ô cần đếm hoặc một phạm vi 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ở về trước).

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: =COUNTA(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 AVERAGE

Hàm AVERAGE được sử dụng để tính giá trị trung bình của một dãy số trong một bảng tính.

Trong đó:

• số1 (Cần thiết): 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: =AVERAGE(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 đó:

• Số (Cần thiết): Giá trị cần làm tròn lên số nguyên lẻ.

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

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

Trong này:

• Số (Cần thiết): Giá trị cần làm tròn lên số nguyên chẵn.

Ví dụ: Để làm tròn số 53.4 đến số nguyên chẵn gần nhất, bạn có thể sử dụng công thức: =EVEN(53.4). Kết quả sẽ là 54.

Hàm điều kiện logic

Hàm COUNTIF

Hàm COUNTIF được dùng để đếm số ô thỏa mãn một điều kiện cụ thể trong một vùng dữ liệu.

Trong này:

• phạm vi: Vùng dữ liệu cần đếm.
• điều kiện: Điều kiện để đếm.

Ví dụ: Giả sử bạn có một bảng thống kê về các mặt hàng và số lượng tồn như hình dưới.

Để thống kê số lượng mặt hàng có tồn kho trên 150 sản phẩm, bạn có thể sử dụng công thức như sau: =COUNTIF(C2:C11, “>150”).

Kết quả sẽ cho biết có tổng cộng 8 mặt hàng còn tồn kho trên 150 sản phẩm, dựa trên điều kiện đã đặt.

Hàm IF

Hàm IF được dùng để kiểm tra liệu một điều kiện nào đó có đúng hay không, và trả về một giá trị dựa trên kết quả của điều kiện đó.

Trong này:

• Logical_test: Điều kiện cần kiểm tra.
• Value_if_true: Giá trị trả về nếu điều kiện là đúng.
• Value_if_false: Giá trị trả về nếu điều kiện là sai.

Lưu ý: Nếu bỏ trống Value_if_true và Value_if_false, giá trị trả về sẽ là 0 nếu điều kiện đúng và FALSE nếu điều kiện sai.

Ví dụ: Giả sử bạn muốn kiểm tra xem một học sinh có qua môn hay không 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.

Tại ô D2, bạn có thể sử dụng công thức như sau: =IF(C2>=7, “Đạt”, “Không Đạt”), và kết quả sẽ được hiển thị như trong ví dụ trên.

Hàm SUMIF

Một trong 150 hàm excel thường dùng và ví dụ mà HR Insider muốn chia sẻ đến bạn đó là hàm SUMIF, được sử dụng để tính tổng các giá trị trong một phạm vi thoả mãn một tiêu chí cụ thể.

Trong này:

• phạm vi: Khu vực ô bạn muốn đánh giá theo tiêu chí.
• tiêu chí: Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng ở ô nào.
• sum_range: Các ô thực tế để cộng nếu bạn muốn cộng các ô không phải là ô đã xác định trong đối số range. Nếu bỏ qua, Excel sẽ cộng các ô được xác định trong đối số range.

Ví dụ: Để tính tổng số màu cam trong cột A với số lượng xuất hiện trong cột B, bạn có thể sử dụng công thức: =SUMIF(A1:A8, “cam”, B1:B8).

Hàm sử dụng trong văn bản

Hàm LEFT/RIGHT/MID

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

Ở đây:

• văn bản: Chuỗi văn bản chứa ký tự mà bạn muốn trích xuất.
• số_ký_tự: Số lượng ký tự mà hàm LEFT sẽ trích xuất.

Ví dụ: Trong bảng dưới đây, để trích xuất 3 ký tự đầu tiên từ ô B2, bạn có thể sử dụng công thức: =LEFT(B3, 3). Sau khi nhập công thức này, nhấn Enter và bạn sẽ nhận được kết quả như được hiển thị trong hình.

– 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 mà người dùng chọn.

Ở đây:

• văn bản: Chuỗi văn bản chứa ký tự mà bạn muốn trích xuất.
• số_ký_tự: Số lượng ký tự mà hàm RIGHT sẽ trích xuất.

Ví dụ: Trong bảng sau đây, để lấy 7 ký tự cuối cùng từ ô B2, bạn có thể sử dụng công thức: =RIGHT(B3, 7). Sau khi nhập công thức này, nhấn Enter và bạn sẽ nhận được kết quả như hiển thị trên hình.

– Hàm MID được dùng để trả về một số ký tự cụ thể từ một chuỗi văn bản, bắt đầu từ vị trí được chỉ định và dựa trên số lượng ký tự được chỉ định. Đây là một trong 150 hàm excel phổ biến và ví dụ, giúp bạn thấy rõ hiệu quả khi sử dụng trong bảng tính Excel.

Ở đây:

• văn_bản: Chuỗi ký tự chứa các ký tự cần trích xuất.
• start_num: Vị trí của ký tự đầu tiên muốn trích xuất trong văn bản.
• số_ký_tự: Số lượng ký tự mà hàm MID sẽ trả về từ văn bản.

Ví dụ: Trong bảng sau đây, để lấy 7 ký tự từ ô B2, bắt đầu từ vị trí thứ 3, bạn có thể sử dụng công thức: =MID(B3, 3, 7). Sau khi nhập công thức này, nhấn Enter và bạn sẽ nhận được kết quả là “ạm Văn”.

Hàm CONCAT

Hàm CONCAT được dùng để gộp hai hoặc nhiều đoạn văn thành một đoạn văn duy nhất.

Ở đây:

• văn_bản1, văn_bản2, …: Các chuỗi văn mà bạn muốn gộp lại.

Ví dụ:

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

Hàm CONCAT cho phép bạn hợp nhất các đoạn văn thành một chuỗi hoàn chỉnh, giúp tạo ra các đoạn văn phức tạp từ các thành phần đơn giản.

Hàm LEN

Hàm LEN sử dụng để đếm số ký tự trong một chuỗi, bao gồm cả khoảng trắng. Hàm này giúp xác định kích thước của chuỗi một cách nhanh chóng, hữu ích trong quản lý và xử lý dữ liệu văn bản.

Ví dụ:

Trong ví dụ, ta sử dụng hàm LEN để đếm số ký tự trong ô A1 chứa chuỗi “Điện máy XANH”. Công thức sẽ là =LEN(A1), và kết quả sẽ là độ dài của chuỗi, bao gồm cả khoảng trắng.

Hàm ngày tháng

Hàm NOW

Hàm NOW là một trong 150 hàm excel thường dùng và ví dụ mà bạn cần biết. Hàm này được dùng để hiển thị ngày và thời gian hiện tại trên hệ thống hoặc tính toán giá trị dựa trên ngày và thời gian hiện tại, tự động cập nhật khi mở lại trang tính.

Ví dụ:

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

• =NOW()+7: Trả về ngày và thời gian 7 ngày trong tương lai.

Hàm này hữu ích khi muốn theo dõi và ghi lại ngày giờ khi thực hiện các thay đổi trong tệp tính hoặc tính toán các giá trị dựa trên thời gian hiện tại và tương lai.

Hàm DATE

Hàm DATE trả về kết quả số cho một ngày cụ thể.

Trong đó:

• Year: chỉ năm. Excel hiểu đối số năm theo hệ thống ngày được thiết lập trên máy tính của bạn.
• Month: chỉ tháng. Một số nguyên đại diện cho tháng trong năm, từ tháng 1 đến tháng 12.
• Day: chỉ ngày. Một số nguyên dương ứng với ngày trong tháng.

Ví dụ:

=DATE(2018, 6, 11) trả về kết quả là ngày 11 tháng 6 năm 2018.

Hàm này hữu ích khi muốn tạo ra giá trị ngày cụ thể để sử dụng trong các tính toán hoặc so sánh ngày trong Excel.

Hàm tra cứu dữ liệu

Hàm VLOOKUP

Hàm VLOOKUP được sử dụng để dò tìm dữ liệu trong một bảng, một phạm vi theo hàng dọc và trả về dữ liệu tương ứng theo hàng ngang.

Trong đó:

• Lookup_value: Giá trị cần dò tìm, có thể điền giá trị trực tiếp hoặc tham chiếu tới một ô trên bảng tính.
• Table_array: Bảng giới hạn để dò tìm.
• Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải.
• Range_lookup: Tìm kiếm chính xác (0) hoặc tìm kiếm tương đối (1). Nếu bỏ qua, mặc định là 1.

Ví dụ:

Xác định mức phụ cấp cho nhân viên. Tại ô E4, bạn điền công thức: =VLOOKUP(D4, $H$3:$I$8, 2, 0)

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

• Dấu $ được sử dụng để cố định các dòng, cột của bảng 2 khi bạn copy công thức sang các ô khác.

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

• Range_lookup = 0 (FALSE) để dò tì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 số 150 hàm excel thường dùng và ví dụ trong nội dung này, không thể bỏ qua hàm MATCH 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ụ:

Để xác định vị trí của “Lâm Thiên Trang” trong danh sách học sinh (A2:A9), sử 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 kiế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ị đối chiếu 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ố ở Bảng 2 (B11:F12), sử dụng công thức: =HLOOKUP(C4, $B$11:$F$12, 2, 1) tại ô D4. Hàm sẽ tìm giá trị điểm ở ô C4 trong Bảng 2 và trả về phân loại tương ứng từ hàng 2.

Như vậy, bạn đã chứng kiến 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 công việc của bạn. Hãy thử nghiệm và áp dụng chúng trong bảng tính của bạn để đạt được hiệu suất làm việc tốt nhất. Chúc bạn sớm trở thành một chuyên gia Excel!