Những hàm cơ bản nhất trong Excel thường hay được sử dụng

1. Hàm Logic:

1.1. Hàm IF:

Hàm IF giúp bạn kiểm tra xem điều kiện có được trả lời hay không, nếu đúng thì trả về true, nếu sai trả về false. Công thức =IF(điều kiện,”giá trị thực”,”giá trị sai”). Để áp dụng cho nhiều điều kiện, bạn sẽ phải sử dụng các hàm IF lồng nhau.

Trong ví dụ dưới đây, chúng ta sẽ kiểm tra xem ô A có thỏa mãn điều kiện lớn hơn 10 hay không, nếu thỏa mãn thì trả về True ở ô C1, nếu không thì trả về False. Tại ô C1, bạn nhập hàm =IF(A1>10,”Correct”,”Incorrect”).

hàm IF trong Excel

Hàm IF trả về true vì giá trị trong ô A1 lớn hơn 10.

Lưu ý khi sử dụng

Hàm IF được sử dụng để chạy kiểm tra logic và phản ứng khác nhau tùy thuộc vào kết quả là TRUE hay FALSE. Đối số đầu tiên, điều kiện, là một biểu thức trả về TRUE hoặc FALSE. Cả đúng và sai đều là tùy chọn, nhưng ít nhất một số trong số chúng phải được cung cấp. Kết quả từ NẾU CÓ THỂ LÀ MỘT GIÁ TRỊ, MỘT THAM CHIẾU Ô HOẶC THẬM CHÍ MỘT Công thức KHÁC.

Hàm IF trả về một giá trị

Ví dụ sau muốn xác định “Đạt” hay “Không đạt” dựa trên điểm kiểm tra. Điểm đạt được là 70 hoặc cao hơn. Công thức là:

=IF(C6>=70,”Đạt”,”Không đạt”)

Giải thích: Nếu giá trị trong C6 lớn hơn hoặc bằng 70, kết quả là “Đạt”. Nếu không, kết quả là “Không thành công”.

Logic của công thức này có thể được đảo ngược. Công thức dưới đây trả về kết quả tương tự:

=IF(C6<70,”Không đạt”,”Đạt”)

Giải thích: Nếu giá trị trong C6 nhỏ hơn 70, kết quả là “Không thành công”. Nếu không, kết quả là “Đạt”.

Cả hai công thức trên sẽ trả về cùng một kết quả.

Hàm IF trả về một công thức khác

Hàm IF có thể trả về một công thức khác. Ví dụ: công thức bên dưới sẽ trả về A1*5% khi A1 nhỏ hơn 100 và A1*7% khi A1 lớn hơn hoặc bằng 100:

=IF(A1<100,A1*5%,A1*7%)

Câu lệnh IF lồng nhau

Một hàm IF có thể được “lồng nhau”, có nghĩa là một công thức trong đó ít nhất một hàm IF được lồng trong một hàm IF khác để kiểm tra nhiều điều kiện hơn và trả về nhiều kết quả khả dĩ hơn. Mỗi câu lệnh IF cần được “dài” cẩn thận để đưa vào bên trong câu lệnh khác cho đúng logic.

Ví dụ: có thể sử dụng công thức sau để chỉ định điểm số thay vì kết quả đạt hoặc không đạt:

=IF(C6<70,”F”,IF(C6<75,”D”,IF(C6<85,”C”,IF(C6<95,”B”,”A”))))

Lên đến 64 hàm IF lồng nhau. Tuy nhiên, nói chung, bạn nên xem xét các hàm khác, chẳng hạn như VLOOKUP hoặc HLOOKUP cho các vấn đề phức tạp hơn, vì chúng có thể xử lý nhiều điều kiện theo cách hợp lý hơn nhiều.

1.2. Hàm AND:

Hàm AND sẽ trả về true nếu tất cả các điều kiện được đáp ứng và trả về false nếu bất kỳ điều kiện nào sai. Công thức =IF(AND(điều kiện),”giá trị đúng”,”giá trị sai)

Tiếp tục ví dụ trên, bây giờ ta kết hợp kiểm tra 2 điều kiện, A1 lớn hơn 10 và B1 lớn hơn 5, nếu cả 2 điều kiện sẽ trả về giá trị True, ngược lại trả về giá trị False. Tại ô D1 bạn nhập =IF(AND(A1>10,B1>5),”True”,”Incorrect”)

hàm AND trong Excel

Hàm AND trả về giá trị false vì ô B1 nhỏ hơn 5 nên hàm IF trả về giá trị false tương ứng ở đây là False.

1.3. Hàm OR:

Đảo ngược với AND, OR trả về true nếu bất kỳ điều kiện nào được thỏa mãn và false nếu tất cả các điều kiện không được đáp ứng.

Hãy thử điều chỉnh tình trạng trên bằng hàm OR. Tại ô E1 bạn nhập =IF(OR(A1>10,B1>5),”True”,”Incorrect”)

Hàm OR trong Excel

Hàm OR trả về true vì ô A1 lớn hơn 10, vì kết quả là hàm IF trả về true.

Lưu ý chung: Hàm AND và OR có thể kiểm tra tối đa 255 điều kiện.

Hàm IF lồng nhau

Như đã đề cập ở trên, khi bạn có nhiều hơn một điều kiện để kiểm tra, đã đến lúc sử dụng các hàm IF lồng nhau. Giá trị sai sẽ được thay thế bằng hàm IF khác để thực hiện thêm một lần kiểm tra. (Nếu sử dụng Excel 2016, bạn chỉ cần sử dụng IFS.)

Xin vui lòng xem các ví dụ dưới đây:

Bạn nhập công thức =IF(A1=1,”Bad”,IF(A1=2,”Good”,IF(A1=3,”Excellent”,”No Valid Score”))) để kiểm tra giá trị của ô A1, nếu bằng 1 trả về Kém, nếu bằng 2 trả về Tốt, nếu bằng 3 trả về Xuất sắc, nếu không trả về Không có Điểm hợp lệ.

Ví dụ 1 về hàm IF lồng nhau

Ví dụ 2 về hàm IF lồng nhau

Ví dụ 3 về hàm IF lồng nhau

Ví dụ 4 về hàm IF lồng nhau

Bạn nhập công thức =IF(A1<=10,350,IF(A1<=20,700,IF(A1<=30,1400,2000))) để kiểm tra giá trị của ô A1, nếu nhỏ hơn hoặc bằng 10 thì trả về giá trị giá trị 350, 10 < A1 <= 20 trả về 700, 20 < A1 <= 30 trả về 1400, nếu lớn hơn 30 trả về 2000.

2. Hàm ngày, tháng:

Hàm YEAR, MONTH, DAY:

Để nhập ngày trong Excel, bạn sử dụng dấu “/” hoặc dấu “-“. Để nhập thời gian, hãy sử dụng “:” (hai dấu chấm). Bạn cũng có thể điền ngày và giờ vào cùng một cột.

Nhập ngày trong Excel

Để lấy năm của ngày, bạn sử dụng hàm YEAR. Tương tự để lấy tháng sử dụng hàm MONTH và lấy ngày sử dụng hàm DATE. Công thức =YEAR(cột chứa năm cần lấy).

Lấy năm theo ngày tháng năm

Hàm DATE:

Để thêm số ngày vào tháng, chỉ cần sử dụng một công thức đơn giản lấy ô chứa ngày + với số ngày bạn muốn thêm, chẳng hạn như ví dụ dưới đây:

Thêm ngày vào ngày của năm đầu tiên

Để thêm số năm, số tháng và số ngày vào bất kỳ tháng nào, bạn sẽ phải sử dụng hàm DATE. Công thức =DATE(YEAR(ô chứa tháng gốc)+số năm cần thêm,MONTH(ô chứa tháng gốc)+số tháng cần thêm,DAY(ô chứa tháng gốc)+số ngày cần thêm).

Thêm năm, tháng, ngày vào tháng hiện có

Lưu ý: Hàm DATE chỉ có 3 đối số: năm, tháng và ngày. Excel biết rằng 6+2=8= tháng 8, tháng 8 chỉ có 31 ngày nên nó sẽ tự động tính sang tháng tiếp theo (23 tháng 8 + 9 ngày đến 1 tháng 9).

Chức năng NOW hiển thị giờ hệ thống:

Nếu bạn muốn nhập thời gian hiện tại vào bất kỳ ô nào trong bảng tính Excel, hãy sử dụng hàm =NOW. Đầu tiên, di chuyển con trỏ chuột đến bất kỳ ô nào rồi chỉ cần gõ cú pháp =NOW(). Ưu điểm của chức năng này là nó không yêu cầu bất kỳ tham số nào bên trong dấu ngoặc. Kết quả trả về là thời gian hiện tại trên hệ thống máy tính.

Hiển thị thời gian hiện tại

Hàm HOUR, MINUTE, SECOND:

Để trả về thời gian giờ, phút, giây, bạn sẽ sử dụng HOUR, MINUTE, SECOND tương ứng. Công thức =HOUR(ô chứa số giờ cần thực hiện).

Quay trở lại thời gian trong thời gian

Hàm TIME:

Hàm TIME được sử dụng để thêm số giờ, phút và giây vào thời gian bắt đầu, giống như hàm DATE ở trên. Công thức =TIME(HOUR(ô thời gian cấm đầu tiên)+số giờ để thêm,MINUTE(ô thời gian cấm đầu tiên)+số phút để thêm,SECOND(ô thời gian cấm đầu tiên)+số giây để thêm).

Thêm thời gian vào thời gian bắt đầu

Hàm ngày giữa hai thời điểm DAYS

Đây là một công thức hữu ích để tính số ngày giữa hai ngày, vì vậy bạn không cần phải ghi lại ô phạm vi cần tính bao nhiêu ngày trong mỗi tháng. Ví dụ, ô E4 chứa ngày bắt đầu là 30/04/2015 và ô F4 chứa ngày kết thúc là 09/02/2015. Bạn nhập =DAYS(F4,E4)

Chức năng tính ngày giữa 2 đèn báo giờ

3. Hàm đếm và tính tổng:

3.1. Hàm đếm COUNT:

Bạn có một bảng tính với cơ sở dữ liệu cần biết và cần biết có bao nhiêu ô trong một phạm vi nhất định hoặc trong toàn bộ trang tính chỉ chứa các số chứ không phải các chữ cái. Thay vì phải đếm thủ công, bạn có thể sử dụng hàm =COUNT.

Hàm COUNT trong Excel trả về số lượng giá trị số. Số ở đây bao gồm số âm, Tỷ lệ phần trăm, ngày, giờ, phân số và công thức trả về số. Các ô trống và giá trị văn bản bị bỏ qua.

Cú pháp:

ĐẾM(giá trị1, [giá trị2], …)

Cú pháp hàm COUNT có các đối số sau:

giá trị1: Bắt buộc. Mục đầu tiên, tham chiếu ô hoặc phạm vi mà bạn muốn đếm.

value2,…: Tùy chọn. Lên đến 255 mục bổ sung, tham chiếu ô hoặc phạm vi mà bạn muốn đếm.

Lưu ý: Các đối số có thể chứa hoặc tham chiếu đến nhiều loại dữ liệu khác nhau, nhưng chỉ số liệu mới được tính.

Các giá trị được tính bao gồm:

Các đối số là số, ngày tháng hoặc biểu diễn văn bản của các số (ví dụ: một số nằm trong dấu ngoặc kép, thực sự có một thuật ngữ như “1”) được tính.

Các giá trị logic và biểu diễn văn bản của các số mà bạn nhập trực tiếp vào danh sách các đối số sẽ được tính toán.

Các đối số là giá trị lỗi hoặc văn bản không thể giao dịch thành số sẽ không được tính toán.

Nếu đối số là một mảng hoặc tham chiếu, thì chỉ các số trong mảng hoặc tham chiếu đó được tính. Các ô trống, giá trị logic, văn bản hoặc giá trị lỗi trong mảng hoặc tham chiếu không được tính toán.

Nếu bạn muốn đếm các giá trị logic, văn bản hoặc lỗi, hãy sử dụng hàm COUNTA (COUNT tính toán số ô có giá trị số và COUNTA tìm các ô có dữ liệu trống). Nếu bạn chỉ muốn đếm các câu trả lời cho các tiêu chí được xác định trước, hãy sử dụng hàm COUNTIF hoặc hàm COUNTIFS.

Ví dụ: nếu bạn cần đếm từ ô B1 đến B10, hãy nhập =COUNT(B10:B10).

ĐẾM . chức năng đếm chữ số

3.2. Hàm COUNTIF với các điều kiện cụ thể:

Hàm Microsoft Excel COUNTIF đếm các ô trong phạm vi phản hồi tiêu chí được xác định trước.

Hàm COUNTIF là hàm giải tích trong excel được xếp vào dạng hàm thống kê. Nó có thể được sử dụng như một hàm trang tính (WS) trong Excel. Là một hàm trang tính, hàm COUNTIF có thể được nhập như một phần của công thức trong một ô của trang tính.

Nếu bạn muốn áp dụng nhiều tiêu chí, hãy thử sử dụng hàm COUNTIFS.

Cú pháp của hàm COUNTIF trong Microsoft Excel là:

COUNTIF(phạm vi, tiêu chí)

Tham số hoặc đối số

phạm vi: Phạm vi ô mà bạn muốn đếm dựa trên tiêu chí.

criteria: Tiêu chí dùng để xác định các ô cần đếm.

Hàm COUNTIF trả về một giá trị giá trị. Chức năng này có thể áp dụng cho Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.

Bạn cũng có thể sử dụng Phạm vi được đặt tên trong hàm COUNTIF. Phạm vi được đặt tên là tên mô tả cho tập hợp các ô hoặc phạm vi trong một trang tính.

Nếu công thức COUNTIF của bạn sử dụng tiêu chí phù hợp cho một chuỗi dài hơn 255 ký tự, thì công thức đó sẽ trả về lỗi. Để giải quyết vấn đề này, hãy sử dụng hàm CONCATENATE để khớp các chuỗi dài hơn 255 ký tự. Bạn có thể tránh phải nhập toàn bộ chức năng bằng cách sử dụng dấu và (&), như minh họa bên dưới.

=COUNTIF(A2:A5,”long string”&”another long string”)

Một hành động của hàm COUNTIF cần lưu ý là nó bỏ qua các chuỗi chữ hoa và chữ thường. Các tiêu chí bao gồm một chuỗi chữ thường và một chuỗi chữ hoa sẽ khớp với các ô giống nhau và trả về cùng một giá trị.

Để đếm các ô dựa trên một điều kiện cụ thể (ví dụ: lớn hơn 9), hãy sử dụng hàm COUNTIF sau đây.

Ví dụ hàm COUNTIF

Hàm đếm COUNTIFS với nhiều điều kiện

Để đếm các ô dựa trên nhiều điều kiện (ví dụ: màu xanh lục và lớn hơn 9), hãy sử dụng hàm COUNTIFS sau.

Ví dụ hàm COUNTIFS

3.3. Hàm tính tổng SUM:

Có thể nói đây là hàm đầu tiên mà bất kỳ người mới sử dụng Excel nào cũng cần phải biết. Hàm SUM trong Excel cộng các giá trị trong một dải ô. Được phân loại theo các hàm toán học và lượng giác, các hàm được nhập bằng cách nhập “=SUM” theo sau là các giá trị tổng. Các giá trị được cung cấp cho hàm có thể là số, tham chiếu ô hoặc phạm vi.

Công thức SUM tự động cập nhật khi người dùng chèn hoặc xóa một giá trị. Nó cũng bao gồm các thay đổi được thực hiện đối với một dải ô hiện có. Hơn nữa, chức năng này cũng tự động bỏ qua các ô trống và giá trị văn bản.

Cú pháp của hàm SUM trong Excel được hiển thị trong hình dưới đây:

Hàm chấp nhận các đối số sau:

Number1: Đây là giá trị đầu tiên được thêm vào.

Number2: Đây là giá trị thứ hai đã được thêm vào.

Đối số “số1” là bắt buộc trong khi các số tiếp theo (“số2”, “số3”, v.v.) là tùy chọn.

Các quy tắc chi phối việc sử dụng chức năng được liệt kê như sau:

Các đối số được cung cấp có thể là số, mảng, tham chiếu ô, hằng số, phạm vi và kết quả của các hàm hoặc công thức khác.

Trong khi cung cấp một ô phạm vi, bắt buộc phải có ô đầu tiên biểu thị phạm vi (ô1: ô2).

Đầu ra là số và đại diện cho tổng giá trị được cung cấp.

Các đối số được cung cấp có thể lên tới tổng số 255.

Lưu ý: Hàm excel SUM trả về giá trị “#VALUE!” lỗi nếu tiêu chí được cung cấp là một chuỗi văn bản dài hơn 255 ký tự. Phạm vi ô được cung cấp phải khớp với kích thước của nguồn.

Ô đầu ra phải luôn được định dạng dưới dạng số.

Hãy xem các ví dụ sau:

Giả sử bạn muốn cộng các số trong ô A2 và B2 lại với nhau, sau đó hiển thị kết quả trong ô B3. Để thực hiện việc này, bạn chỉ cần chuyển đến ô B3 và nhập cụm từ “=SUM” rồi chọn hàm =SUM vừa xuất hiện trong danh sách bật lên.

Tiếp theo, nhấn phím Ctrl cùng với nhấp chuột để chọn các ô A2 và B2 rồi cuối cùng nhấn phím Enter. Tổng của hai số trong ô A2 và B2 mà bạn vừa chọn sẽ xuất hiện ngay tại ô B3. Bạn có thể sử dụng hàm SUM để tính tổng của hai hoặc nhiều ô, chỉ bằng cách chọn các ô bổ sung cần thiết trong nội dung của hàm.

3.4. Hàm SUMIF có điều kiện:

Hàm SUMIF sẽ tổng hợp các ô trả lời các tiêu chí đã chọn. Các tiêu chí được dựa trên ngày, số và văn bản. Nó hỗ trợ các logic toán học như (>, <, <>, =) và ký tự đại diện (*,?).

Là một công cụ phân tích tài chính, SUMIF là một trong những hàm được sử dụng thường xuyên nhất. Giả sử bạn được cung cấp một danh sách các lô hàng rau từ các nhà cung cấp khác nhau. Tên rau, tên nhà cung cấp và số lần như trong cột A, cột B và cột C. Trong trường hợp này, bạn có thể sử dụng hàm SUMIF để tìm ra tổng số tiền liên quan đến một loại rau cụ thể từ nhà cung cấp mong muốn.

Công thức chung của hàm SUMIF trong Excel là:

Công thức sử dụng các đối số sau:

phạm vi (đối số bắt buộc) – Đây là phạm vi ô mà bạn muốn áp dụng tiêu chí.

tiêu chí (đối số bắt buộc) – Đây là tiêu chí được sử dụng để xác định bất kỳ ô nào sẽ được thêm vào. Số tiêu chí có thể là:

Giá trị số (có thể là số nguyên, số thập phân, ngày, giờ hoặc giá trị logic) (ví dụ: 10, 01/01/2018, TRUE)

Chuỗi văn bản (ví dụ: “Văn bản”, “Thứ Năm”)

Một biểu thức (ví dụ: “>12”, “<>0”).

sum_range (đối số tùy chọn) – Đây là một mảng các giá trị số (hoặc các ô chứa các giá trị số) sẽ được cộng lại với nhau nếu mục nhập phạm vi tương ứng trả lời các tiêu chí được cung cấp. Nếu các đối số [sum_range] bị bỏ qua, thay vào đó, các giá trị từ các đối số phạm vi sẽ được tính tổng.

Ví dụ: để tính tổng các ô dựa trên một điều kiện (ví dụ: lớn hơn 9), hãy sử dụng hàm SUMIF sau (hai đối số).

Để tính tổng các ô dựa trên một tiêu chí (ví dụ: màu xanh lá cây), hãy sử dụng hàm SUMIF với 3 đối số (đối số cuối cùng là phạm vi cần tính tổng).

4. Các chức năng làm việc với chuỗi văn bản:

Nối chuỗi văn bản: Để nối một chuỗi văn bản, bạn dùng ký tự &, nếu muốn chèn khoảng trắng thì dùng dấu ” ” (khai báo chiết khấu, dấu cách, dấu ngoặc kép).

Hàm LEFT: Để lấy các ký tự bên trái của một chuỗi, hãy sử dụng hàm LEFT. Công thức =LEFT(ô chứa chuỗi cần lấy, số ký tự cần lấy)

Hàm RIGHT: Sử dụng hàm RIGHT khi bạn cần lấy các ký tự từ phía bên phải của chuỗi. Công thức =RIGHT(ô chứa chuỗi cần lấy, số ký tự cần lấy)

Hàm MID: Dùng để truy xuất nơi bắt đầu ký tự (từ trái sang phải). Công thức =MID(ô chứa chuỗi cần lấy, vị trí lấy, số ký tự cần lấy). Trong ví dụ này, chúng tôi lấy 3 ký tự, bắt đầu từ ký tự thứ 5.

Hàm LEN:

Nếu bạn muốn đếm tổng số ký tự hoặc chuỗi ký tự dài trong một ô bao gồm chữ cái, số và dấu cách, hãy nghĩ đến hàm =LEN. Công thức =LEN(ô chứa chuỗi cần đếm).

Ví dụ, để biết có bao nhiêu ký tự trong ô B4, chỉ cần đi đến bất kỳ ô nào khác và nhập cú pháp =LEN(B4). Ngay lập tức, Excel sẽ trả về một số giá trị tính tổng các ký tự trong ô B4.

5. Hàm tra cứu và tham chiếu:

5.1. Hàm VLOOKUP :

Hàm VLOOKUP tìm kiếm các giá trị trong cột ngoài cùng bên trái của bảng và trả về giá trị trong cùng một hàng từ cột khác mà bạn chỉ định. Cú pháp =VLOOKUP(ô chứa giá trị tìm kiếm, bảng chứa giá trị tìm kiếm và trả về, bảng chứa giá trị trả về), có thể thêm đối số thứ 4 như trong ví dụ bên dưới.

Hàm VLOOKUP tìm kiếm ID (104) trong cột ngoài cùng bên trái của phạm vi $E$4:$G$7 và trả về giá trị trong cùng một hàng từ cột thứ 3 (đối số thứ 3 chỉ được chỉ định là 3). Đối số thứ 4 được đặt thành FALSE để trả về kết quả đúng hoặc lỗi #N/A nếu không tìm thấy.

Bạn chỉ cần thực hiện trên 1 ô, sau đó kéo hàm VLOOKUP bên dưới để áp dụng cho các hàng bên dưới. Do vùng tham chiếu được giữ nguyên, $ phải được sử dụng trước các ký hiệu để tạo tham chiếu tuyệt đối.

Để hiểu rõ hơn về cách sử dụng hàm Vlookup, bạn có thể tham khảo bài viết: Cách sử dụng hàm Vlookup trong Excel mà Quantrimang.com đã giới thiệu trước đây

5.2. Hàm MATCH:

Hàm MATCH trả về vị trí của giá trị trong một phạm vi nhất định.

Màu vàng được tìm thấy ở vị trí 3 trong dải E4:E7. Đối số thứ 3 là tùy chọn, bạn có thể đặt đối số này thành 0 để trả về vị trí của giá trị trong ô hoặc trả về #N/A nếu không tìm thấy.