Đây là những hàm cơ bản nhất trong Excel mà bạn cần nắm rõ

Sử dụng ứng dụng Excel thì làm việc với các hàm cơ bản trong Excel là việc rất thường xuyên. Chúng giúp thực hiện tính toán dữ liệu trực tiếp trên bảng tính nhanh và tiện lợi hơn. Các hàm Excel là những công thức được định nghĩa trước và đã có sẵn trong Excel, bạn chỉ việc lôi ra sử dụng mà thôi, chúng tiện hơn so với các biểu thức rất nhiều.

Những hàm cơ bản trong Excel mà chúng tôi tổng hợp dưới đây như hàm Excel tính toán, thống kê,... sẽ rất có ích với các bạn thường xuyên phải làm việc trên bảng tính Excel, đặc biệt là trong lĩnh vực kế toán, hành chính nhân sự. Vì thế, hãy chắc chắn rằng bạn đã thuộc nằm lòng những hàm Excel cơ bản này. Mời bạn tham khảo nhé.

1. HÀM ĐẾM VÀ TÍNH TỔNG

Hàm đếm COUNT

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

Hàm COUNT trong Excel trả về số lượng các giá trị là 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:

COUNT (value1, [value2], ...)

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

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

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 số.

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ố mới được tính.

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

• Các đối số là số, ngày tháng hoặc đại diện văn bản của số (ví dụ: một số được đặt trong dấu ngoặc kép, chẳng hạn như "1") được tính.
• Những 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.
• Các đối số là giá trị lỗi hoặc văn bản không thể dịch thành số sẽ không được tính.
• 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 đó mới đượ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.

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

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

Hàm đếm COUNTIF với điều kiện cụ thể

Hàm COUNTIF của Microsoft Excel đếm số ô trong một phạm vi đáp ứng tiêu chí nhất định.

Hàm COUNTIF là một hàm tích hợp trong Excel được phân loại là 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 cho hàm COUNTIF trong Microsoft Excel là:

COUNTIF( range, criteria )

Tham số hoặc đối số

• range: Phạm vi ô mà bạn muốn đếm dựa trên tiêu chí.
• criteria: Tiêu chí được sử dụng để xác định ô nào cần đếm.

Hàm COUNTIF trả về một giá trị số. Hàm 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 một Named Range trong hàm COUNTIF. Named Range là tên mô tả cho một tập hợp các ô hoặc dải ô 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 với một chuỗi dài hơn 255 ký tự, nó sẽ trả về lỗi. Để khắc phục điều 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 nhập toàn bộ hàm bằng cách chỉ cần sử dụng dấu và (&), như được minh họa bên dưới.

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

Một hành vi 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.

Hàm 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ụ, green và lớn hơn 9), hãy sử dụng hàm COUNTIFS sau.

Hàm tính tổng SUM

Đây có thể nói là hàm đầu tiên mà bất kỳ người dùng nào mới học Excel cũng cần phải biết đến. Hàm SUM trong Excel cộng các giá trị số trong một phạm vi ô. Được phân loại theo hàm toán học và lượng giác, hàm được nhập bằng cách gõ “=SUM” theo sau là các giá trị được tính 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 những thay đổi được thực hiện đối với một phạm vi ô hiện có. Hơn nữa, hàm cũng tự 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 sau:

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

• Number1: Đây là giá trị số đầu tiên được thêm vào.
• Number2: Đây là giá trị số thứ hai được thêm vào.
• Đối số “number1” là bắt buộc trong khi các số tiếp theo (“number2”, “number3”, v.v...) là tùy chọn.

Các quy tắc điều chỉnh việc sử dụng hàm đượ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 những hàm hoặc công thức khác.
• Trong khi cung cấp một dải ô, chỉ dải ô đầu tiên (cell1:cell2) là bắt buộc.
• Đầu ra là số và đại diện cho tổng các 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 SUM excel 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.
Ô chứa đầu ra phải luôn được định dạng dưới dạng số.

Cùng tìm hiểu ví dụ sau:

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

Tiếp theo, hãy nhấn phím Ctrl cùng với thao tác nhấn chuột chọn vào ô A2 và B2 rồi cuối cùng là nhấn phím Enter. Kết quả tổng của hai con số trong hai ô A2 và B2 mà bạn vừa chọn sẽ xuất hiện tức thì trong ô B3. Bạn có thể sử dụng hàm SUM để tính tổng của hai hay nhiều ô, chỉ với thao tác chọn thêm những ô cần thiết vào trong nội dung của hàm.

Hàm tính tổng SUMIF có điều kiện

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

Đóng vai trò 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 đưa cho một bảng liệt kê các lô hàng rau từ những nhà cung cấp khác nhau. Tên loại rau, tên nhà cung cấp và số lượng lần lượt ở cột A, cột B và cột C. Trong trường hợp như vậ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ừ một nhà cung cấp mong muốn.

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

=SUMIF(range, criteria, [sum_range])

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

• range (đối số bắt buộc) - Đây là phạm vi ô mà bạn muốn áp dụng tiêu chí.
  criteria (đối số bắt buộc) - Đây là tiêu chí được sử dụng để xác định ô nào cần được thêm vào. Đối số criteria 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 giá trị số) sẽ được cộng lại với nhau nếu mục nhập phạm vi tương ứng đáp ứng các tiêu chí được cung cấp. Nếu đối số [sum_range] bị bỏ qua, thay vào đó, các giá trị từ đố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ụ, green), 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).

Hàm tính tổng SUMIFS với nhiều điều kiện

Để tính tổng các ô dựa trên nhiều điều kiện (ví dụ, blue và green), hãy sử dụng hàm SUMIFS sau (đối số đầu tiên là phạm vi bảng tính cần tính tổng).

Hàm tính giá trị trung bình AVERAGE

Hàm =AVERAGE làm đúng chức năng như tên gọi của nó là đưa ra con số có giá trị trung bình trong những số được chọn. Hàm AVERAGE có thể xử lý tới 255 đối số riêng lẻ, có thể bao gồm số, tham chiếu ô, phạm vi, mảng và hằng số.

Cú pháp của hàm AVERAGE là:

= AVERAGE (number1, [number2],...)

Trong đó:

• number1 - Một số hoặc tham chiếu ô đề cập đến các giá trị số.
• number2 - [tùy chọn] Một số hoặc tham chiếu ô đề cập đến các giá trị số.

Hàm AVERAGE tính giá trị trung bình của các số được cung cấp dưới dạng đối số. Để tính giá trị trung bình, Excel tính tổng tất cả các giá trị số và chia cho số giá trị số.

AVERAGE nhận nhiều đối số ở dạng number1, number2, number3, v.v. lên đến tổng số 255. Các đối số có thể bao gồm số, tham chiếu ô, phạm vi, mảng và hằng số. Các ô trống và ô có chứa văn bản hoặc giá trị logic bị bỏ qua. Tuy nhiên, các giá trị không (0) được bao gồm. Bạn có thể bỏ qua các giá trị không (0) bằng hàm AVERAGEIFS, như được giải thích bên dưới.

Hàm AVERAGE sẽ bỏ qua các giá trị logic và số được nhập dưới dạng văn bản. Nếu bạn cần đưa các giá trị này vào giá trị trung bình, hãy xem hàm AVERAGEA.

Nếu các giá trị được cung cấp cho AVERAGE có lỗi, thì AVERAGE trả về lỗi. Bạn có thể sử dụng hàm AGGREGATE để bỏ qua lỗi.

Ví dụ bạn muốn tính giá trị trung bình từ ô A10 đến ô J10, chỉ cần gõ =AVERAGE(A10:J10) rồi nhấn Enter.

Kết quả đưa ra trong ô K10 là con số có giá trị trung bình giữa các ô từ A10 đến J10. Ngoài ra, bạn cũng có thể sử dụng con trỏ chuột kéo thả và đánh dấu chọn vùng dữ liệu hoặc có thể đồng thời nhấn phím Ctrl rồi nhấn chuột vào từng ô riêng rẽ trong trường hợp nếu các ô không nằm kế cận nhau.

Tương tự như SUMIF, COUNTIF bạn có thể sử dụng AVERAGEIF và AVERAGEIFS để tính giá trị trung bình của các ô dựa trên một hoặc nhiều tiêu chí.

Hàm AVERAGE tự động bỏ qua các ô trống. Trong màn hình bên dưới, thông báo ô C4 trống và AVERAGE chỉ cần bỏ qua nó và tính giá trị trung bình của B4 và D4:

Tuy nhiên, lưu ý rằng giá trị không (0) trong C5 được bao gồm trong giá trị trung bình, vì nó là một giá trị số hợp lệ. Để loại trừ các giá trị 0, hãy sử dụng AVERAGEIF hoặc AVERAGEIFS thay thế. Trong ví dụ dưới đây, AVERAGEIF được sử dụng để loại trừ các giá trị bằng không. Giống như hàm AVERAGE, AVERAGEIF tự động loại trừ các ô trống.

=AVERAGEIF(B3:D3,">0") // exclude zero

Hàm đếm ô trống COUNTBLANK

Hàm COUNTBLANK được phân loại theo các hàm thống kê của Excel. Bạn sử dụng hàm này khi cần đếm các ô trống. Trong phân tích tài chính, hàm này có thể hữu ích trong việc đánh dấu hoặc đếm các ô trống trong một phạm vi nhất định. Cú pháp của hàm là:

=COUNTBLANK(phạm vi bảng tính cần đếm)

Trong đó:

Phạm vi bảng tính cần đếm chỉ định phạm vi ô mà bạn muốn đếm các ô trống.

Đối với hàm COUNTBLANK, hãy nhớ rằng:

• Bất kỳ ô nào chứa văn bản, số, lỗi, v.v... đều không được tính bởi hàm này.
• Các công thức trả về văn bản trống (“”) được coi là trống và sẽ được tính. Vì vậy, nếu một ô chứa một chuỗi văn bản trống hoặc một công thức trả về một chuỗi văn bản trống, thì ô đó sẽ được tính là trống bởi hàm COUNTBLANK.
• Các ô chứa số 0 được coi là không trống và sẽ không được tính.

Là một hàm trang tính, COUNTBLANK có thể được nhập như một phần của công thức trong một ô của trang tính. Để hiểu cách sử dụng hàm, hãy xem xét một ví dụ:

Nếu muốn biết số lượng ô trống, bạn có thể sử dụng định dạng có điều kiện với hàm này.

Giả sử bạn được cung cấp dữ liệu dưới đây:

Ở đây, bạn có thể sử dụng công thức =COUNTBLANK(A2:D5) để đếm các hàng trống:

Ta được kết quả dưới đây:

Nếu muốn, bạn có thể sử dụng định dạng có điều kiện để đánh dấu các hàng có ô trống bằng cách sử dụng hàm COUNTBLANK. Chọn phạm vi mong muốn và trên định dạng có điều kiện đã chọn và áp dụng hàm COUNTBLANK(). Điều này sau đó sẽ highlight tất cả các ô trống trên phạm vi mong muốn.

Hàm đếm ô không trống COUNTA

Hàm COUNTA có chức năng dùng để đếm số ô có chứa nội dung bất kỳ bao gồm chữ số, chữ cái hay biểu tượng, hay nói cách khác nó dùng để đếm các ô không trống. Cú pháp =COUNTA(phạm vi bảng tính cần đếm).

Lưu ý khi sử dụng:

Hàm COUNTA đếm các ô có chứa giá trị, bao gồm số, văn bản, logic, lỗi và văn bản trống (""). COUNTA không đếm các ô trống.

Hàm COUNTA trả về số lượng giá trị trong danh sách các đối số được cung cấp. COUNTA nhận nhiều đối số ở dạng value1, value2, value3, v.v... Các đối số có thể là các giá trị được hardcode riêng lẻ, tham chiếu ô hoặc phạm vi lên đến tổng số 255 đối số. Tất cả các giá trị đều được tính, bao gồm văn bản, số, tỷ lệ phần trăm, lỗi, ngày, giờ, phân số và công thức trả về chuỗi trống (""). Các ô trống được bỏ qua.

Ví dụ về cách sử dụng hàm COUNTA

Trong ví dụ được hiển thị, COUNTA được thiết lập thành các giá trị trong phạm vi B5:B15:

=COUNTA(B5:B15) // returns 9

COUNTA trả về 9, vì có 9 ô không trống trong phạm vi B5:B15.

Hàm COUNTA đếm số và văn bản:

=COUNTA(1,2,3) // returns 3
=COUNTA(1,"a","b") // returns 3
=COUNTA(1,2,3,"a",5%) // returns 5

Để đếm các ô không trống trong phạm vi A1:A10:

=COUNTA(A1:A10) // count non-empty cells in A1:A10

Để đếm các ô không trống trong phạm vi A1:A10 và phạm vi C1:H2:

=COUNTA(A1:A10,C1:H2) // two ranges

Lưu ý rằng COUNTA bao gồm các chuỗi rỗng ("") trong số đếm, có thể được trả về bằng công thức. Ví dụ, công thức bên dưới sẽ trả về "OK" khi giá trị trong A1 ít nhất là 10 và một chuỗi rỗng ("") khi giá trị nhỏ hơn 10:

=IF(A1>=10,"OK","")

Hàm COUNTA sẽ tính cả hai kết quả là không trống.

Hàm tính tổng giá tiền sản phẩm SUMPRODUCT

Để tính tổng sản phẩm của các số tương ứng (kiểu tính tổng tiền của các sản phẩm dựa trên số lượng và giá tương ứng của từng sản phẩm) trong một hoặc nhiều dãy, bạn hãy sử dụng hàm SUMPRODUCT mạnh mẽ của Excel.

Hàm SUMPRODUCT trong Excel nhân các phạm vi hoặc mảng với nhau và trả về tổng các tích. Điều này nghe có vẻ nhàm chán nhưng SUMPRODUCT là một hàm cực kỳ hữu ích có thể được sử dụng để đếm và tính tổng như COUNTIFS hoặc SUMIFS, nhưng linh hoạt hơn. Có thể dễ dàng sử dụng các hàm khác bên trong SUMPRODUCT để mở rộng chức năng hơn nữa.

Hàm SUMPRODUCT nhân các mảng với nhau và trả về tổng các tích. Nếu chỉ có một mảng được cung cấp, SUMPRODUCT sẽ chỉ tính tổng các mục trong mảng. Có thể cung cấp tối đa 30 phạm vi hoặc mảng.

Khi bạn gặp SUMPRODUCT lần đầu tiên, nó có vẻ nhàm chán, phức tạp và thậm chí vô nghĩa. Nhưng SUMPRODUCT là một hàm linh hoạt đáng kinh ngạc với nhiều công dụng. Bởi vì nó sẽ xử lý các mảng một cách duyên dáng, bạn có thể sử dụng nó để xử lý các phạm vi ô theo những cách vô cùng thông minh.

Ví dụ dưới đây dùng hàm SUMPRODUCT để tính tổng số tiền đã tiêu.

Cụ thể ở trên, hàm SUMPRODUCT đã thực hiện phép tính: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500.

Phạm vi tính tổng phải có cùng kích thước, nếu không Excel sẽ hiển thị lỗi #VALUE!

Nếu giá trị trong ô không phải dạng số thì SUMPRODUCT sẽ mặc định giá trị của chúng là 0.

Lưu ý

• SUMPRODUCT coi các mục không phải là số trong mảng là số không.
• Các đối số mảng phải có cùng kích thước. Nếu không, SUMPRODUCT sẽ tạo ra giá trị lỗi #VALUE!.
• Các kiểm tra logic bên trong mảng sẽ tạo ra những giá trị TRUE và FALSE. Trong hầu hết các trường hợp, bạn sẽ muốn ép buộc các giá trị này thành 1 và 0.
• SUMPRODUCT thường có thể sử dụng trực tiếp kết quả của các hàm khác

Hàm MIN, MAX

Để tìm giá trị thấp nhất (số nhỏ nhất) trong một tập hợp các giá trị, hãy sử dụng hàm MIN.

Hàm MIN có các đối số sau trong cú pháp của nó:

• number1 - bắt buộc
• number2 đến number255 - tùy chọn

number1, [number2], … là chuỗi giá trị từ đó bạn muốn nhận giá trị nhỏ nhất. Number1 là bắt buộc trong khi [number2] và các mục sau đó là tùy chọn.

Đối với mỗi đối số, bạn có thể nhập số, tên, mảng hoặc tham chiếu trang tính nơi lưu trữ số.

Nếu đối số là một tham chiếu hoặc mảng, thì mọi ô trống, giá trị logic (true hoặc false) hay giá trị văn bản đều bị bỏ qua khi tính toán giá trị tối thiểu.

Lưu ý: Nếu phạm vi không chứa bất kỳ giá trị số nào, công thức MIN sẽ trả về số không

Nếu bạn muốn tìm một con số có giá trị nhỏ nhất trong một vùng dữ liệu, hàm =MIN có thể làm việc đó. Chỉ cần gõ vào cụm từ =MIN(D3:J13), Excel sẽ trả về cho bạn số nhỏ nhất nằm trong phạm vi đó.

Có tối đa 255 đối số được phép trong một hàm. Các đối số có thể là số, ô, mảng tham chiếu và phạm vi. Tuy nhiên, các đối số như giá trị logic, văn bản, ô trống sẽ bị bỏ qua.

Ví dụ về cách sử dụng hàm MIN:

Giả sử bạn có một số loại trái cây trong kho. Nhiệm vụ của bạn là kiểm tra xem có loại nào bị hết không.

Trường hợp 1: Nhập từng chữ số từ cột Qty in stock:

=MIN(366, 476, 398, 982, 354, 534, 408)

Trường hợp 2: Tham chiếu lần lượt các ô từ cột Qty:

=MIN(B2,B3,B4,B5,B6,B7,B8)

Trường hợp 3: Hoặc đơn giản là tham chiếu toàn bộ phạm vi:

=MIN(B2:B8)

Trường hợp 4: Ngoài ra, bạn có thể tạo một dải ô đã đặt tên và sử dụng dải ô đó để tránh mọi tham chiếu trực tiếp:

=MIN(Qty-in-stock)

Ngược với hàm =MIN vừa đề cập, hàm =MAX sẽ trả về con số có giá trị lớn nhất trong phạm vi cần tìm. Cú pháp của hàm này cũng tương tự như hàm =MIN, gồm địa chỉ ô đầu tiên cho đến ô cuối cùng. Hàm Excel MAX có các đối số sau trong cú pháp của nó:

• number1 - bắt buộc
• number2 đến number255 - tùy chọn

Đối với mỗi đối số, bạn có thể nhập số, tên, mảng hoặc tham chiếu trang tính nơi lưu trữ số.

Nếu đối số là một tham chiếu hoặc mảng, thì bất kỳ ô trống, giá trị logic hoặc văn bản nào đều bị bỏ qua khi tính toán giá trị tối đa.

Lưu ý: Nếu phạm vi không chứa bất kỳ giá trị số nào, thì kết quả của công thức MAX bằng 0.

Trong Excel 2019 hoặc Excel for Office 365, bạn có thể sử dụng các hàm MINIFS và MAXIFS để tìm giá trị tối thiểu hoặc giá trị tối đa, dựa trên một hoặc nhiều tiêu chí. Đối với các phiên bản Excel trước, các hàm MINIFS và MAXIFS không khả dụng

2. HÀM LOGIC

Hàm IF

Hàm IF giúp bạn kiểm tra xem điều kiện có được đáp ứng không, nếu đúng nó sẽ trả về giá trị đúng, nếu sai sẽ trả về giá trị sai. Công thức =IF(điều kiện,"giá trị đúng","giá trị sai"). Để áp dụng cho nhiều điều kiện bạn sẽ phải dùng đến 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 không, nếu thảo mãn thì trả về giá trị Correct trong ô C1, nếu không thỏa mãn sẽ trả về giá trị Incorrect. Tại ô C1, bạn nhập vào hàm =IF(A1>10,"Correct","Incorrect").

Hàm IF trả về Correct 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 một bài kiểm tra logic và phản ứng khác nhau tùy thuộc vào việc 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ả giá trị đúng và giá trị sai đều là tùy chọn, nhưng ít nhất một trong số chúng phải được cung cấp. Kết quả từ IF có thể là một giá trị, một tham chiếu ô hoặc thậm chí là một công thức khác.

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

Ví dụ sau muốn chỉ định "Pass" hoặc "Fail" dựa trên điểm kiểm tra. Điểm đạt là 70 hoặc cao hơn. Công thức là:

=IF(C6>=70,"Pass","Fail")

Giải thích: Nếu giá trị trong C6 lớn hơn hoặc bằng 70, kết quả trả về là "Pass". Nếu không, kết quả trả về "Fail".

Luồ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ề cùng một kết quả:

=IF(C6<70,"Fail","Pass")

Giải thích: Nếu giá trị trong C6 nhỏ hơn 70, kết quả trả về "Fail". Nếu không, kết quả trả về "Pass".

Cả hai công thức trên sẽ trả về kết quả giống nhau.

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

Hàm IF có thể được "lồng vào nhau", nghĩa là một công thức trong đó ít nhất một hàm IF được lồng bên 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ả có thể hơn. Mỗi câu lệnh IF cần được "lồng" một cách cẩn thận vào bên trong một câu lệnh khác để logic là chính xác.

Ví dụ, công thức sau có thể được sử dụng để chỉ định điểm thay vì kết quả đạt hay 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 có thể được lồng vào nhau. Tuy nhiên, nói chung, bạn nên xem xét các hàm khác, như VLOOKUP hoặc HLOOKUP cho các tình huống 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.

Hàm AND

Hàm AND sẽ trả về giá trị đúng nếu tất cả các điều kiện được thỏa mãn và trả về giá trị sai nếu có 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)

Vẫn ví dụ trên, giờ ta kết hợp kiểm tra 2 điều kiện là A1 lớn hơn 10 và B1 lớn hơn 5, nếu cả 2 thỏa mãn sẽ trả về Correct, ngược lại trả về Incorrect. Tại ô D1 bạn nhập =IF(AND(A1>10,B1>5),"Correct","Incorrect")

Hàm AND trả về giá trị sai vì ô B1 nhỏ hơn 5, do đó hàm IF trả về giá trị sai tương ứng ở đây là Incorrect.

Hàm OR

Hàm Excel OR trả về TRUE nếu bất kỳ đối số đã cho nào được đánh giá là TRUE và trả về FALSE nếu tất cả các đối số được cung cấp bị đánh giá là FALSE. Ví dụ, để kiểm tra A1 cho "x" hoặc "y", hãy sử dụng =OR(A1="x",A1="y"). Hàm OR có thể được sử dụng làm phép thử logic bên trong hàm IF để tránh các IF lồng nhau và có thể được kết hợp với hàm AND.

Ngược lại với AND, OR trả về giá trị đúng nếu có bất kỳ điều kiện nào được thỏa mãn và trả về giá trị sai nếu tất cả các điều kiện đều không được đáp ứng.

Cú pháp:

=OR(logical1, [logical2], ...)

Trong đó:

• logic1 - Điều kiện đầu tiên hoặc giá trị logic để đánh giá.
• logic2 - [tùy chọn] Điều kiện thứ hai hoặc giá trị logic để đánh giá.

Ví du, thử lại điều kiện trên với hàm OR. Tại ô E1 bạn nhập =IF(OR(A1>10,B1>5),"Correct","Incorrect")

Hàm OR trả về giá trị đúng vì ô A1 lớn hơn 10, vì thế kết quả là hàm IF trả về giá trị Correct.

Ghi chú chung: Hàm AND và OR có thể kiểm tra đến 255 điều kiện, được cung cấp dưới dạng đối số. Mỗi đối số (logic1, logic2, v.v...) phải là một biểu thức trả về TRUE hoặc FALSE hoặc một giá trị có thể được đánh giá là TRUE hoặc FALSE. Các đối số được cung cấp cho hàm OR có thể là hằng số, tham chiếu ô, mảng hoặc biểu thức logic.

Mục đích của hàm OR là đánh giá nhiều bài kiểm tra logic cùng lúc và trả về TRUE nếu bất kỳ kết quả nào là TRUE. Ví dụ, nếu A1 chứa số 50 thì:

• =OR(A1>0,A1>75,A1>100) // trả về TRUE
• =OR(A1<0,A1=25,A1>100) // trả về FALSE

Hàm OR sẽ đánh giá tất cả các giá trị được cung cấp và trả về TRUE nếu bất kỳ giá trị nào được đánh giá là TRUE. Nếu tất cả logic đánh giá là FALSE, hàm OR sẽ trả về FALSE. Lưu ý, Excel sẽ đánh giá bất kỳ số nào ngoại trừ số không (0) là TRUE.

Cả hàm AND và hàm OR sẽ tổng hợp kết quả thành một giá trị duy nhất. Điều này có nghĩa là chúng không thể được sử dụng trong các hoạt động cần cung cấp một mảng kết quả. Để khắc phục hạn chế này, bạn có thể sử dụng logic Boolean.

Hàm IF lồng nhau

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

Hãy xem các ví dụ dưới đây:

Bạn nhập vào 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ề Bad, nếu bằng 2 trả về Good, nếu bằng 3 trả về Excellent, nếu là một giá trị khác sẽ trả về No Valid Score.

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

3. HÀM NGÀY THÁNG

Hàm YEAR, MONTH, DAY

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

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

Hàm DATE

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

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

Chú ý: Hàm DATE chỉ có 3 đối số là 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 thành 1 tháng 9).

Hàm NOW hiển thị thời gian hệ thống

Nếu muốn điền thời gian hiện tại vào một ô nào đó trong bảng tính Excel, hãy sử dụng hàm =NOW. Trước hết, hãy di chuyển con trỏ chuột đến một ô bất kỳ rồi chỉ cần gõ cú pháp =NOW(). Điểm tiện lợi của hàm này là 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ề chính là thời gian hiện tại trên hệ thống máy tính.

Hàm HOUR, MINUTE, SECOND

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

Hàm TIME

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

Hàm tính số ngày giữa 2 mốc thời gian DAYS

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

Hàm DATEIF

Để lấy số ngày, tháng, năm của 2 mốc thời gian trong Exel bạn sẽ sử dụng hàm DATEIF. Công thức =DATEIF(mốc thời gian thứ nhất,mốc thời gian thứ hai,"d"). Đối số thứ 3 là cái bạn muốn lấy, "d" là số ngày, muốn lấy số năm bạn thay bằng "y", lấy số tháng thay bằng "m".

Thay đối số thứ 3 bằng "yd" để bỏ qua năm, chỉ tính số ngày giữa các tháng của khoảng thời gian đó, "md" sẽ bỏ qua tháng, chỉ tính số ngày, "ym" sẽ bỏ qua năm, chỉ đếm số tháng chênh lệch.

Lưu ý quan trọng: Hàm này có thể trả về kết quả sai nếu ngày/tháng của ngày tháng thứ 2 (nằm ở đối số 2) thấp hơn ngày/tháng ở ngày tháng thứ nhất (đối số 1). Xem ví dụ dưới đây:

Khi đó bạn nên dùng công thức như trong hình để trả về kết quả đúng:

Hàm WEEKDAY

Hàm WEEKDAY trả về giá trị từ 1 (Chủ nhật) đến 7 (thứ 7), đại diện cho ngày trong tuần của ngày tháng năm. Ví dụ, 12/18/2017 rơi vào thứ Hai. Công thức =WEEKDAY(ô chứ ngày tháng năm).

Hàm TEXT

Sử dụng hàm TEXT để hiển thị ngày trong tuần dưới dạng chữ. Công thức =TEXT(ô chứa ngày tháng,"dddd").

Hàm NETWORKDAYS tính số ngày làm việc

Hàm =NETWORKDAYS() dùng để tính số ngày làm việc (nghĩa là một tuần làm việc gồm 5 ngày) trong một khung thời gian cụ thể. Tương tự hàm =DAYS, hãy sử dụng công thức: =NETWORKDAYS(E3,F3).

Nếu bạn cung cấp danh sách ngày nghỉ lễ (như trong vùng E1:E2 của ví dụ dưới) NETWORKDAYS sẽ trả về số ngày làm việc (đã trừ đi ngày nghỉ cuối tuần và ngày nghỉ lễ).

Xem lịch dưới đây để hiểu rõ hơn nhé, màu đỏ là nghỉ lễ, màu xanh là ngày làm việc:

Hàm EOMONTH

Để lấy ngày cuối cùng của ngày tháng hiện tại bạn sẽ sử dụng hàm EOMONTH. Công thức =EOMONTH(ô chứa ngày tháng hiện tại,0). Sử dụng một số khác thay cho không để lấy ngày cuối cùng của các tháng sau đó, ví dụ thay bằng 2 thì sẽ lấy ngày cuối cùng của 2 tháng sau đó, thay bằng -8 là lấy ngày cuối cùng của 8 tháng trước đó.

4. HÀM LÀM VIỆC VỚI CHUỖI VĂN BẢN

Nối chuỗi văn bản

Để nối chuỗi văn bản bạn sử dụng ký tự &, muốn chèn thêm dấu cách thì sử dụng " " (mở ngoặc kép, cách, đóng ngoặc kép).

Hàm LEFT

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

Hàm RIGHT

Dùng hàm RIGHT khi cần lấy những 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ự muốn lấy)

Hàm MID

Dùng để lấy các ký tự bắt đầu từ đâu (tính từ trái sang). Công thức =MID(ô chứ chuỗi cần lấy, vị trí lấy, số ký tự cần lấy). Trong ví dụ này ta 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ự hay còn gọi là chiều dài chuỗi ký tự trong một ô bao gồm cả chữ cái, chữ số và khoảng trắng, hãy nghĩ đến hàm =LEN. Công thức =LEN(ô chứa chuỗi cần đếm).

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

Hàm FIND

Dùng để tìm vị trí của một chuỗi con trong chuỗi. Công thức =FIND("chuỗi con", ô chứa chuỗi). Trong ví dụ này ta tìm vị trí của chuỗi con "am" trong chuỗi "example text", và nó ở vị trí thứ 3.

Hàm SUBSTITUTE thay thế văn bản

Để thay thế văn bản trong chuỗi bằng một văn bản khác bạn sẽ sử dụng hàm SUBSTITUTE, với công thức =SUBSTITUTE(ô chứa chuỗi ban đầu,"văn bản ban đầu","văn bản thay thế")

Hàm cắt các khoảng trống TRIM

Nếu phải nhập hay dán văn bản vào Excel chẳng hạn như dữ liệu từ một cơ sở dữ liệu, trang web, phần mềm xử lý văn bản hay các chương trình văn bản khác, bạn sẽ nhận thấy văn bản được nhập có nhiều khoảng trắng nằm rải rác trong danh sách.

TRIM là một công thức khắc phục hiện tượng này bằng cách nhanh chóng loại bỏ các khoảng trống. Hàm =TRIM chỉ có thể xử lý văn bản trong một ô duy nhất. Ví dụ, để thực hiện cắt các khoảng trắng trong cột E8, bạn chỉ cần gõ hàm =TRIM(E8) một lần vào ô F8 bên cạnh, rồi sau đó chép lại công thức này xuống cho đến cuối danh sách.

Hàm LOWER, UPPER, PROPER để chuyển đổi chữ hoa, chữ thường

Bạn sử dụng hàm LOWER để chuyển đổi tất cả các chữ in hoa trong chuỗi thành chữ thường, công thức =LOWER(ô chứa chữ cần chuyển đổi).

Ngược lại, để chuyển tất cả các chữ thường trong chuỗi ký tự thành chữ in hoa, bạn sử dụng hàm UPPER. Công thức: =UPPER(ô chứa chuỗi cần chuyển đổi).

Nếu muốn viết hoa các chữ đầu từ bạn sử dụng hàm PROPER. Công thức =PROPER(ô chứa chuỗi cần viết hoa chữ cái đầu từ).

Hàm EXACT để so sánh hai cột

Khi cần so sánh các cột xem giá trị của chúng có trùng nhau không bạn sử dụng EXACT. Công thức =EXACT(ô cần so sánh).

Hàm CONCATENATE kết hợp nội dung

Hàm này sẽ kết hợp nội dung của hai hay nhiều ô vào trong một ô duy nhất. Chẳng hạn, khi bạn muốn nhập chung Ngày, tháng, năm, bài, ngôi sao vào cùng 1 ô thì hãy gõ =CONCATENATE(A1,B1,C1,D1) như bên dưới

5. HÀM TRA CỨU VÀ THAM CHIẾU

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, vùng bảng chứa giá trị tìm kiếm và trả về,cột chứa giá trị trả về), có thể thêm đối số thứ 4 như ví dụ dưới.

Hàm VLOOKUP tìm ID (104) trong cột ngoài cùng bên trái của dải $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 được chỉ định là 3). Đối số thứ 4 đặt là FALSE để trả lại kết quả chính xác 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 xuống dưới để áp dụng cho các hàng bên dưới. Vì vùng tham chiếu giữ nguyên nên phải 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 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.

Hàm MATCH

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

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

Hàm INDEX

Hàm INDEX dưới đây trả về một giá trị cụ thể trong một phạm vi hai chiều.

92 được tìm thấy tại giao điểm của hàng 3 và cột 2 trong dải E4:F7.

Hàm INDEX dưới đây trả về giá trị đặc biệt trong phạm vi một chiều.

Giá trị 97 được tìm thấy ở vị trí thứ 3 của dải E4:E7.

Hàm CHOOSE

CHOOSE là một trong những hàm Excel trông có vẻ không hữu ích, nhưng khi kết hợp với các hàm khác sẽ mang lại một số lợi ích tuyệt vời. Ở cấp độ cơ bản nhất, bạn sử dụng hàm CHOOSE để lấy một giá trị từ danh sách bằng cách chỉ định vị trí của giá trị đó.

Hàm CHOOSE trả về giá trị từ danh sách bạn chỉ định, dựa trên vị trí được yêu cầu.

Ví dụ trên, hàm CHOOSE trả về giá trị Boat ở vị trí thứ 3.

Hàm CHOOSE là một hàm tích hợp sẵn trong Excel được phân loại là nhóm Hàm tra cứu/Tham chiếu. Nó có thể được sử dụng như một hàm trang tính (WS) và một hàm VBA (VBA) trong Excel. Là một hàm trang tính, hàm CHOOSE có thể được nhập như một phần của công thức trong một ô của trang tính. Là một hàm VBA, bạn có thể sử dụng hàm này trong code macro được nhập thông qua Microsoft Visual Basic Editor.

Cú pháp cho hàm CHOOSE trong Microsoft Excel là:

CHOOSE( position, value1, [value2, ... value_n] )

Tham số hoặc đối số

• position: Số vị trí trong danh sách các giá trị cần trả về. Nó phải là một số từ 1 đến 29.
• value1, value2, ... value_n: Danh sách lên đến 29 giá trị. Giá trị có thể là bất kỳ giá trị nào sau đây: số, tham chiếu ô, tên đã xác định, công thức/hàm hoặc giá trị văn bản.

Kết quả trả về

Hàm CHOOSE trả về bất kỳ kiểu dữ liệu nào như chuỗi, số, ngày, v.v...

• Nếu vị trí nhỏ hơn 1, hàm CHOOSE sẽ trả về lỗi #VALUE!.
• Nếu vị trí lớn hơn số lượng giá trị trong danh sách, hàm CHOOSE sẽ trả về lỗi #VALUE!.

Ghi chú

Nếu vị trí là một phân số (không phải là một giá trị nguyên), nó sẽ được chuyển đổi thành một số nguyên bằng cách loại bỏ thành phần phân số của số đó.

Hàm CHOOSE được áp dụng cho Excel trong bộ Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.

• MS Excel - Bài 5: Các công thức và hàm Excel
• Làm sao để giữ cố định các ô trong Excel?
• Làm thế nào để chuyển số thành chữ trong Excel?

Các bạn nhớ bookmark bài này lại nhé, mình sẽ cập nhật thường xuyên đến khi hết "vốn" thì thôi ^^