Các hàm cơ bản trong Excel chính là những công cụ mạnh mẽ giúp bạn tổ chức và xử lý dữ liệu một cách hiệu quả. Trong bài viết này, chúng ta sẽ tập trung vào các hàm cơ bản nhất trong Microsoft Excel, cung cấp cái nhìn tổng quan về cách chúng hoạt động và cách chúng có thể hỗ trợ trong việc phân tích số liệu.
Bài viết được thực hiện trên phần mềm Excel 2013. Bạn có thể sử dụng tương tự cho các phiên bản Excel khác như: Excel 2007, 2010, 2017, 2020 và Microsoft Excel 365.
Hàm tính toán Logic
Hàm SUM
Hàm SUM được sử dụng để tính tổng các số trong một dãy ô. Nó có chức năng tính tổng giá trị của các số hoặc dãy số trong ô được chỉ định.
Cú pháp: =SUM(Number1, Number2, Number3)
Trong đó:
Number1, Number2, Number3: là các giá trị cần tính tổng.
Kết quả của hàm SUM sẽ là tổng của các giá trị đã được chọn.
Ví dụ: =SUM(20,40,60) có nghĩa là cộng các số lại với nhau và cho ra kết quả là 120.
Ví dụ khác: Để tính tổng các số từ ô A1 đến A3, ta sử dụng công thức =SUM(A1:A3) và kết quả sẽ là 120.
Hàm MIN/MAX
Hàm MIN/MAX được sử dụng để tìm giá trị nhỏ nhất/lớn nhất trong một phạm vi dữ liệu hoặc toàn bộ bảng tính.
Cú pháp: =MIN(Number1, Number2,...)
Trong đó:
Number1, Number2,…: Các giá trị cần so sánh.
Kết quả trả về là giá trị nhỏ nhất trong phạm vi dữ liệu đã chọn.
Ví dụ: Để tìm giá trị nhỏ nhất trong phạm vi dữ liệu của ví dụ này, ta có thể sử dụng công thức =MIN(A2:C5) và kết quả thu được là 25, đây là giá trị nhỏ nhất cần tìm.
Cú pháp: =MAX(Number1, Number2, ...)
Trong đó:
Number1, Number2, …: Các giá trị cần so sánh.
Kết quả trả về 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 phạm vi dữ liệu của ví dụ này, ta có thể sử dụng công thức =MAX(A2:C5) và kết quả thu được là 89, đây 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 ô có chứa giá trị số trong một vùng dữ liệu.
Cú pháp: =COUNT(Value1,...)
Với:
Value1,…: Tham chiếu đến các ô hoặc vùng dữ liệu cần đếm số lượng.
Ví dụ: Để tìm số lượng ô có chứa giá trị số trong vùng dữ liệu, ta có thể sử dụng công thức =COUNT(A2:C5). Kết quả thu được sẽ là tổng số lượng ô có chứa giá trị số.
– 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ể.
Cú pháp: =COUNTA(Value1,...)
Với:
Value1,…: Là các ô hoặc vùng dữ liệu 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 ký tự là số trong bảng dữ liệu dưới đây, ta có thể sử dụng công thức =COUNTA(A2:C5). Kết quả thu được sẽ là số lượng ô có chứa ký tự hoặc số.
Hàm ODD/EVEN
– Hàm ODD là một công cụ trong Excel giúp làm tròn số đến số nguyên lẻ gần nhất.
Cú pháp: =ODD(Number)
Trong đó:
Number: Là tham số bắt buộc, đại diện cho giá trị cần làm tròn.
Ví dụ: Sử dụng hàm ODD để làm tròn số 4.6 đến số nguyên lẻ gần nhất, ta sẽ nhận được kết quả là 5.
– Tương tự, hàm EVEN cũng là một công cụ trong Excel giúp làm tròn số đến số nguyên chẵn gần nhất.
Cú pháp: =EVEN(Number)
Trong đó:
Number: Là tham số bắt buộc, đại diện cho giá trị cần làm tròn.
Ví dụ: Sử dụng hàm EVEN để làm tròn số 53.4 đến số nguyên chẵn gần nhất, ta sẽ nhận được kết quả là 54.
Hàm AVERAGE
Hàm AVERAGE được sử dụng để tính toán giá trị trung bình của một tập hợp các số trong bảng tính.
Cú pháp: =AVERAGE(number1,number2,…)
Trong đó:
snumber1 (bắt buộc): Số đầu tiên, tham chiếu ô hoặc phạm vi cần tính trung bình.
number2,… (tùy chọn): Các số, tham chiếu ô hoặc phạm vi bổ sung cần tính trung bình, tối đa là 255.
Ví dụ: Để tính lương trung bình theo thông tin trong hình dưới, ta sử dụng công thức =AVERAGE(C3:C8) và thu được kết quả là giá trị trung bình của 6 tháng lương.
Các hàm điều kiện Logic
Hàm COUNTIF
Hàm COUNTIF được sử dụng để đếm số lượng ô thỏa mãn điều kiện trong một phạm vi dữ liệu đã chọn.
Cú pháp: =COUNTIF(phạm vi, điều kiện)
Trong đó:
phạm vi: Là vùng dữ liệu cần đếm.
điều kiện: Là tiêu chí để đếm.
Ví dụ: Nếu có bảng thống kê các mặt hàng và số lượng tồn như hình dưới, ta có thể sử dụng hàm COUNTIF để đếm số lượng mặt hàng còn tồn trên 150 sản phẩm.
Để làm điều này, ta nhập công thức sau: =COUNTIF(C2:C11,>150)
Kết quả trả về sẽ cho thấy có tổng cộng 8 mặt hàng còn tồn trên 150 sản phẩm.
Xem thêm: Đếm theo điều kiện với COUNTIF/COUNTIFS
Hàm IF
Hàm IF là một công cụ được sử dụng để kiểm tra xem liệu dữ liệu có đáp ứng được điều kiện mà người dùng đã đặt ra hay không, và trả về kết quả theo biểu thức logic đúng hoặc sai.
Cú pháp: =IF(Điều_kiện; Giá_trị_nếu_đúng; Giá_trị_nếu_sai)
Trong đó:
Điều_kiện: Điều kiện được đặt ra.
Giá_trị_nếu_đúng: Giá trị sẽ được trả về nếu điều kiện được thỏa mãn.
Giá_trị_nếu_sai: Giá trị sẽ được trả về nếu điều kiện không được thỏa mãn.
Lưu ý: Nếu bỏ trống Giá_trị_nếu_đúng và Giá_trị_nếu_sai, nếu điều kiện được thỏa mãn thì giá trị trả về sẽ là 0 và nếu điều kiện không được thỏa mãn thì giá trị trả về sẽ là FALSE.
Ví dụ: Xét trường hợp học sinh có qua môn với điều kiện:
– Điểm số từ 7 trở lên: Đạt
– Điểm số thấp hơn 7: Không Đạt
Tại ô D2, ta có công thức: =IF(C2>=7,Đạt,Không Đạt), và kết quả sẽ được hiển thị như trong hình bên dưới.
Xem thêm: Cách dùng hàm IF trong Excel | Có ví dụ đơn giản dễ hiểu
Hàm SUMIF
Hàm SUMIF được sử dụng để tính tổng các giá trị trong một phạm vi chỉ định dựa trên tiêu chí xác định.
Cú pháp: =SUMIF(phạm vi, tiêu chí, [phạm vi tổng])
Trong đó:
phạm vi: Là phạm vi các ô cần đánh giá theo tiêu chí.
tiêu chí: Là tiêu chí được đưa ra dưới dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm để xác định các ô nào sẽ được cộng.
phạm vi tổng: Là các ô thực tế sẽ được cộng nếu muốn tính tổng các ô không thuộc phạm vi đã xác định ở đối số phạm vi. Nếu bỏ qua đối số này, Excel sẽ tự động cộng các ô trong phạm vi đã xác định.
Ví dụ: Để tính tổng số lượng sản phẩm có màu cam trong cột A và số lượng xuất hiện của chúng trong cột B, ta có thể sử dụng công thức =SUMIF(A1:A8, "cam", B1:B8).
Các Hàm được áp 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 được chọn bởi người dùng.
Cú pháp: =LEFT(text,[num_chars])
Trong đó:
text: Chuỗi văn bản chứa các ký tự mà bạn muốn trích xuất.
num_chars: Số lượng ký tự mà hàm LEFT sẽ trích xuất.
Ví dụ: Trong bảng dưới đây, ta sử dụng hàm LEFT để tìm 3 ký tự đầu tiên trong ô B2. Ta nhập công thức =LEFT(B3,3), nhấn Enter và kết quả sẽ hiển thị như hình minh họa.
Hàm RIGHT được sử dụng để cắt chuỗi ký tự từ phía bên phải của một chuỗi văn bản được chọn bởi người dùng.
Cú pháp: =RIGHT(text,[num_chars])
Trong đó:
- text: Chuỗi văn bản chứa các ký tự mà bạn muốn trích xuất.
- num_chars: Số lượng ký tự mà hàm RIGHT sẽ trích xuất.
Ví dụ: Trong bảng dưới đây, ta sử dụng hàm RIGHT để tìm 7 ký tự cuối cùng trong ô B2. Ta nhập công thức =RIGHT(B3,7), nhấn Enter và kết quả sẽ hiển thị như hình minh họa.
Hàm MID được sử dụng để trích xuất một số lượng 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 vào số lượng ký tự được chỉ định.
Cú pháp: =MID(text, start_num, num_chars)
Trong đó:
- text: Chuỗi văn bản chứa các ký tự mà bạn muốn trích xuất.
- start_num: Vị trí của ký tự đầu tiên mà bạn muốn trích xuất trong chuỗi văn bản.
- num_chars: Số lượng ký tự mà hàm MID sẽ trích xuất từ văn bản.
Ví dụ: Trong bảng dưới đây, ta sử dụng hàm MID để tìm 7 ký tự trong ô B2 bắt đầu từ vị trí thứ 3. Ta nhập công thức =MID(B3,3,7), nhấn Enter và kết quả sẽ hiển thị là “Lâm Văn”.
Hàm LEN
Hàm LEN được sử dụng để đếm số lượng ký tự trong một chuỗi hoặc ô chứa chuỗi ký tự, bao gồm cả khoảng trắng.
Cú pháp: =LEN(chuỗi ký tự)
hoặc =LEN(ô chứa chuỗi ký tự)
Ví dụ: Trong ví dụ này, chúng ta sử dụng hàm LEN để đếm số ký tự trong ô A1, nơi chứa chuỗi ký tự Tin học văn phòng PRO
Xem thêm: Cách dùng hàm LEN trong Excel để đếm ký tự, có bài tập minh họa
Hàm CONCAT
Hàm CONCAT được sử dụng để ghép nối hai hoặc nhiều chuỗi văn bản thành một chuỗi duy nhất.
Cú pháp: =IF(Điều_kiện; Giá_trị_nếu_đúng; Giá_trị_nếu_sai)
Trong đó:
- Điều_kiện: Điều kiện để kiểm tra.
- Giá_trị_nếu_đúng: Giá trị sẽ được trả về nếu điều kiện là đúng.
- Giá_trị_nếu_sai: Giá trị sẽ được trả về nếu điều kiện là sai.
Lưu ý: Nếu không có giá trị cho Giá_trị_nếu_đúng và Giá_trị_nếu_sai, khi điều kiện đúng sẽ trả về giá trị 0 và khi điều kiện sai sẽ trả về giá trị FALSE.
Ví dụ: =CONCAT("Tin"," ","Học"," ","Văn"," ","Phòng"," ","Pro") sẽ trả về Tin Học Văn Phòng Pro.
Các hàm ngày tháng
Hàm NOW
Hàm NOW được sử dụng để hiển thị ngày và giờ hiện tại trên hệ thống của bạn, hoặc tính toán các giá trị dựa trên ngày và giờ hiện tại và tự động cập nhật khi trang tính được mở lại.
Cú pháp: =NOW()
Ví dụ:
=NOW() : Trả về ngày và giờ hiện tại trên hệ thống của bạn.
=NOW()+7: Trả về ngày và giờ sau 7 ngày tính từ thời điểm hiện tại.
Hàm DATE
Hàm DATE trong Excel được sử dụng để trả về kết quả số cho một ngày cụ thể.
Cú pháp của hàm này là: =DATE(Năm,Tháng,Ngày)
Trong đó:
- Năm: đại diện cho năm. Excel sẽ hiểu và tính toán theo hệ thống ngày được thiết lập trên máy tính của bạn.
- Tháng: đại diện cho tháng. Đây là một số nguyên từ 1 đến 12 tương ứng với các tháng trong năm.
- Ngày: đại diện cho ngày. Đây là một số nguyên dương từ 1 đến 31 tương ứng với các ngày trong tháng.
Ví dụ: =DATE(2015,5,20) sẽ trả về kết quả là ngày 20 tháng 5 năm 2015.
Các hàm tìm kiếm thông tin
Hàm VLOOKUP
Hàm VLOOKUP là một công cụ hữu ích trong Excel khi cần tìm kiếm dữ liệu trong một bảng, phạm vi theo hàng dọc và trả về kết quả tương ứng theo hàng ngang.
Cú pháp: =VLOOKUP(Giá_trị_tìm_kiếm, Bảng_dữ_liệu, Số_thứ_tự_cột, Tìm_kiếm)
Trong đó:
- Giá_trị_tìm_kiếm: Là giá trị cần tìm kiếm, có thể nhập trực tiếp hoặc tham chiếu đến một ô trong bảng tính.
- Bảng_dữ_liệu: Là bảng chứa dữ liệu cần tìm kiếm.
- Số_thứ_tự_cột: Là số thứ tự của cột chứa dữ liệu cần lấy từ bảng tìm kiếm, tính từ trái sang phải.
Tìm_kiếm: Chỉ định phương thức tìm kiếm, nếu không điền sẽ mặc định là 1.
- Nếu Tìm_kiếm = 1 (TRUE): Tìm kiếm tương đối.
- Nếu Tìm_kiếm = 0 (FALSE): Tìm kiếm chính xác.
Ví dụ: Xác định mức lương cơ bản cho nhân viên. Tại ô E4, nhập công thức: =VLOOKUP(D4,$H$3:$I$8,2,0)
Giải thích công thức (theo thứ tự các đối số trong công thức từ trái sang):
- Dấu $ được sử dụng để giữ nguyên các dòng và cột khi sao chép công thức sang các ô khác.
- 2 là số thứ tự của cột chứa dữ liệu cần lấy.
- Tìm_kiếm = 0 (FALSE) để tìm kiếm chính xác.
Xem thêm: Hàm VLOOKUP trong Excel: Cách dùng hàm VLOOKUP, có ví dụ cụ thể
Hàm INDEX
Hàm INDEX là một công cụ hữu ích trong Excel cho phép truy xuất và trả về giá trị hoặc tham chiếu đến một ô cụ thể trong bảng hoặc một phạm vi nhất định.
Cú pháp: =INDEX(mảng, số_hàng, số_cột)
Trong đó:
- mảng: Là phạm vi các ô hoặc một hàng số mảng. Nếu mảng chỉ có một hàng hoặc cột, thì số_hàng hoặc số_cột tương ứng có thể bỏ qua. Nếu mảng có nhiều hàng và nhiều cột, và chỉ có một trong hai đối số số_hàng hoặc số_cột được khai báo, hàm sẽ trả về một mảng chứa toàn bộ hàng hoặc cột trong mảng.
- số_hàng: Là thứ tự của hàng trong mảng để trả về giá trị. Đây là đối số bắt buộc, trừ khi số_cột đã được khai báo. Nếu không nhập số_hàng, số_cột phải được khai báo.
- số_cột: Là thứ tự của cột trong mảng để trả về giá trị. Nếu không nhập số_cột, số_hàng phải được khai báo.
Ví dụ: Giả sử bạn muốn tìm giá trị của ô ở hàng thứ 4, cột thứ 1 trong mảng. Mảng này có 10 hàng và 4 cột (được định vị tại 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ố từ trái sang phải):
- B4:E13 là mảng chứa giá trị cần truy xuất.
- 4 là số thứ tự của hàng trong mảng để trả về giá trị.
- 1 là số thứ tự của cột trong mảng để trả về giá trị.
Sau khi nhập xong, nhấn Enter và kết quả sẽ được hiển thị như hình dưới đây.
Xem thêm: Hàm INDEX trong Excel, cách sử dụng có bài tập cụ thể
Hàm MATCH
Hàm MATCH là một công cụ hữu ích trong việc tìm kiếm và xác định vị trí của một giá trị trong một mảng hoặc ô dữ liệu.
Cú pháp: =Match(Giá_trị_tìm_kiếm, Mảng_tìm_kiếm, Kiểu_khớp)
Trong đó:
- Giá_trị_tìm_kiếm: Là giá trị mà bạn muốn tìm kiếm.
- Mảng_tìm_kiếm: Là mảng hoặc dải ô dữ liệu mà bạn muốn xác định vị trí của giá trị tìm kiếm. Chú ý rằng chỉ có thể chọn một hàng hoặc một cột để thực hiện công thức này.
- Kiểu_khớp: Xác định cách khớp giữa giá trị tìm kiếm và mảng dữ liệu. Có ba loại khớp là -1, 0 và 1.
- Kiểu khớp là 0: Trả về vị trí tương đối của giá trị trong trường hợp mảng dữ liệu chưa được sắp xếp.
- Kiểu khớp là 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. Điều này áp dụng khi mảng dữ liệu đã được sắp xếp theo thứ tự tăng dần.
- Kiểu khớp là -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. Điều này áp dụng khi mảng dữ liệu đã được sắp xếp theo thứ tự giảm dần.
Ví dụ: Bạn có một danh sách hồ sơ thi tuyển của các học sinh và muốn tìm vị trí của học sinh Lâm Thiên Trang. Tại ô G3, bạn có thể sử dụng công thức =MATCH(G1,A2:A9,0) để tìm vị trí của học sinh này trong mảng dữ liệu.
Hàm HLOOKUP
Hàm HLOOKUP là một công cụ hữu ích trong việc tìm kiếm giá trị trên dòng đầu tiên của bảng tham chiếu và trả về kết quả tương ứng trong dòng chỉ định.
Cú pháp: =HLOOKUP(Giá_trị_tìm_kiếm, Bảng_tham_chiếu, Số_thứ_tự_dòng, Cách_tìm_kiếm)
Trong đó:
- Giá_trị_tìm_kiếm: là giá trị mà chúng ta muốn so sánh với dòng đầu tiên của bảng tham chiếu để tìm ra giá trị cần lấy.
- Bảng_tham_chiếu: là địa chỉ tuyệt đối của bảng tham chiếu, không bao gồm cột tiêu đề.
- Số_thứ_tự_dòng: là số thứ tự của dòng chứa giá trị cần lấy trong bảng tham chiếu (theo thứ tự từ trên xuống dưới và bắt đầu từ số 1).
- Cách_tìm_kiếm: là cách mà chúng ta muốn tìm kiếm trên bảng tham chiếu.
- + 0: Dòng đầu tiên của bảng tham chiếu chưa được sắp xếp.
- + 1: Dòng đầu tiên của bảng tham chiếu đã được sắp xếp theo thứ tự tăng dần (mặc định).
Ví dụ: Nếu chúng ta muốn xếp loại học sinh trong Bảng 1 (B3:D8) với dữ liệu từ Bảng 2 (B11:F12), tại ô D4 chúng ta có thể sử dụng công thức =HLOOKUP(C4,$B$11:$F$12,2,1). Hàm HLOOKUP sẽ tìm kiếm giá trị ở ô C4 trong Bảng 2 từ trái sang phải. Khi tìm thấy giá trị gần nhất, nó sẽ trả về kết quả xếp loại tương ứng nằm ở hàng 2.
Xem thêm
Tin học văn phòng Pro đã giới thiệu cho bạn một số hàm cơ bản trong Excel, mong rằng những thông tin trên có thể giúp ích cho bạn. Chúc bạn thành công và nếu có góp ý, xin bạn hãy comment bên dưới để chúng ta cùng trao đổi nhé!
