Giáo trình Excel 2021 toàn tập [phần cơ bản-2]

Bài 17: Các hàm cơ bản trong Excel

Những người làm trong lĩnh vực kế toán, hành chính nhân sự thường xuyên phải làm việc với bảng tính Excel, thì việc nắm vững những hàm thông dụng trong Excel là rất cần thiết.

Đây là những hàm cơ bản, được định nghĩa sẵn, bạn có thể kết hợp các hàm lại với nhau để tính toán. Mời các bạn cùng theo dõi bài viết:

Nhóm hàm tính tổng trong Excel 2016

Hàm SUM: Tính tổng

Hàm SUM là một hàm tính tổng được sử dụng phổ biến nhất, có thể thực hiện tính tổng theo cột, theo hàng một cách nhanh chóng.

Cú pháp: SUM(Number1, Number2..).

Các tham số: Number1, Number2… là các số cần tính tổng. Ví dụ: =SUM(A2:A10) tính tổng các giá trị từ ô A2 đến ô A10. Hàm SUMIF: Tính tổng có điều kiện

Hàm SUMIF là hàm tính tổng có điều kiện, có khả năng kết hợp với

nhiều hàm khác. Được dùng khi cần tính tổng một vùng nào đó trong bảng tính Excel theo điều kiện cho trước.

Cú pháp: SUMIF(Range, Criteria, Sum_range).

Các tham số:

• Range: Là dãy số mà muốn xác định.
  • Criteria: Điều kiện, tiêu chuẩn muốn tính tổng (có thể là số, biểu thức hoặc chuỗi).
  • Sum_range: Là các ô thực sự cần tính tổng.

Ví dụ: =SUMIF(B1:B5,”Nữ”,C1:C5). Tính tổng các ô từ C1 đến C5 với điều kiện giá trị trong cột từ B1 đến B5 là Nữ.

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

Hàm SUMIF là hàm tính tổng có điều kiện, có khả năng kết hợp nhiều điều kiện khác nhau. Được dùng khi cần tính tổng một vùng nào đó trong bảng tính Excel theo nhiều điều kiện cho trước.

Cú pháp: SUMIFS(Sum_range; Range1; Criteria1; [Range2; …]; [Criteria2; …])

Các tham số:

• Sum_range: Là các ô thực sự cần tính tổng.
  • Range: Là dãy số mà muốn xác định.
  • Criteria: Điều kiện, tiêu chuẩn muốn tính tổng (có thể là số, biểu thức hoặc chuỗi).

Ví dụ: =SUMIFS(A1:A10; B1:B10; “>20”; C1:C10; “<30”). Tính

tổng các ô từ A1 đến A10 với điều kiện: giá trị trong ô từ B1 đến B10 lớn hơn 20 VÀ giá trị các ô từ C1 đến C10 nhỏ hơn 30.

Nhóm hàm Logic trong Excel

Hàm AND: Hàm điều kiện VÀ

Hàm AND được sử dụng rất nhiều trong Excel, với kết quả trả về giá trị TRUE, FALSE. Hàm trả về giá trị TRUE(1) nếu tất cả các đối số là đúng, trả về giá trị FALSE(0) nếu một hay nhiều đối số là sai.

Cú pháp: AND (Logical1, Logical2…)

Trong đó: Logical1, Logical2… là các biểu thức điều kiện.

Lưu ý:

• Các đối số phải là giá trị logic hoặc mảng hay tham chiếu có chứa giá trị logic.
  • Nếu đối số tham chiếu là giá trị text hoặc Null (rỗng) thì những giá trị đó bị bỏ qua.
  • Nếu vùng tham chiếu không chứa giá trị logic thì hàm trả về lỗi #VALUE!

Ví dụ: =AND(A4=”Nữ”,B4=7) vì cả 2 biểu thức đều đúng lên giá trị trả về là TRUE.

Hàm OR: Hàm điều kiện HOẶC

Hàm OR là một hàm logic trong Excel. Trả về giá trị TRUE(1) nếu bất cứ một đối số nào là đúng, trả về giá trị FALSE(0) nếu tất cả các đối số là sai.

Cú pháp: OR(Logical1,Logical2…).

Các đối số: Logical1, Logical2… là các biểu thức điều kiện.

Ví dụ: =OR(B1<20,B3>100)

• Nếu B1 nhỏ hơn 20 hoặc B3 nhỏ hơn 20 thì hàm trả về giá trị TRUE.
  • Nếu B1 lớn hơn 20 và B3 nhỏ hơn 100 thì hàm trả về giá trị FALSE.

Hàm NOT: Hàm điều kiện phủ định

Hàm NOT là hàm lấy giá trị nghịch đảo của một giá trị mà bạn đã biết kết quả, sử dụng hàm NOT khi muốn phủ định giá trị của đối số.

Cú pháp: NOT(Logical).

Đối số: Logical là một giá trị hay một biểu thức logic.

Hàm IF

Hàm IF là một trong số hàm phổ biến nhất khi tính toán bằng Excel. Nó cho phép bạn tạo các so sánh logic giữa một giá trị với giá trị bạn chọn.

Do đó, một lệnh IF có thể có hai kết quả. Kết quả đầu tiên là nếu so sánh của bạn đúng (True) và thứ hai là nếu sao sánh sai (False).

Ví dụ: =IF(C2=”Yes”,1,2) cho biết IF(C2 = Có, trả về kết quả 1, sai trả về kết 2).

Cú pháp

Dùng hàm IF – một trong số hàm logic, để trả về một giá trị nếu điều kiện đúng và giá trị khác nếu điều kiện sai.

IF(logical_test, value_if_true, [value_if_false]) Ví dụ:

=IF(A2>B2,”Over Budget”,”OK”)

=IF(A2=B2,B4-A4,””)

Các vấn thường gặp

Hàm IFS

Dùng hàm IFS để kiểm tra một hoặc nhiều điều kiện được đáp ứng và trả về một giá trị tương ứng với điều kiện TRUE đầu tiên.

Lưu ý: Tính năng này chỉ có sẵn nếu bạn đăng ký thuê bao Microsoft 365.

1. Chọn ô bạn muốn tạo hàm IFS.
2. Gõ =IFS(.
3. Tạo điều kiện đầu tiên.
4. Thêm điều kiện khác.
5. Nhấn Enter.

Áp dụng công thức IFS vào cột

1. Gõ $ để thêm tham chiếu tuyệt đối tới một công thức IFS hiện tại.
2. Kéo công thức xuống tới cột mong muốn.

Nhóm hàm tính giá trị trung bình trong Excel

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

Hàm AVERAGE là hàm tính trung bình cộng của một dãy số, hỗ trợ tính toán nhanh hơn nếu số lượng phần tử trong dãy lớn và dài.

Cú pháp: AVERAGE(Number1,Number2…).

Các tham số: Number1,Number2… là các số cần tính giá trị trung bình.

Ví dụ: =AVERAGE(A10:J10). Tính giá trị trung bình các ô từ A10 đến ô J10.

Hàm SUMPRODUCT: Tính tổng của các tích

Hàm SUMPRODUCT lấy tích của các dãy đưa vào, sau đó tính tổng các tích đó.

Cú pháp: SUMPRODUCT(Array1,Array2,Array3…).

Các tham số:

• Array1: Bắt buộc, đối số mảng đầu tiên mà bạn muốn nhân các thành phần của nó rồi cộng tổng.
• Array2, Array3…: Tùy chọn, các đối số mảng từ 2 đến 255 mà bạn muốn nhân các thành phần của nó rồi cộng tổng.

Lưu ý: Các đối số trong các dãy phải cùng chiều, nếu không hàm sẽ trả về giá trị lỗi #VALUE.

Nhóm hàm tìm giá trị lớn nhất và nhỏ nhất trong Excel

Hàm MAX: Tìm giá trị lớn nhất

Hàm MAX là hàm trả về số lớn nhất trong dãy được nhập.

Cú pháp: MAX(Number1, Number2…).

Các tham số: Number1, Number2… là dãy muốn tìm giá trị lớn nhất ở trong đó.

Ví dụ: =MAX(B1:B5) đưa ra giá trị lớn nhất trong các ô từ B1 đến B5.

Hàm MIN: Tìm giá trị nhỏ nhất

Hàm MIN là hàm trả về số nhỏ nhất trong dãy được nhập vào.

Cú pháp: MIN(Number1, Number2…).

Các tham số: Number1, Number2… là dãy muốn tìm giá trị nhỏ nhất. Ví dụ: =MIN(B1:B7). Trả về số nhỏ nhất trong số các ô từ B1 đến B7. Hàm LAGRE: Tìm giá trị lớn nhất thứ k

Hàm LAGRE là hàm trả về giá trị lớn nhất thứ k.

Cú pháp: LARGE(Array,k).

Các tham số:

• Array: Là một mảng hoặc một vùng dữ liệu.
• k: Là thứ hạng của số bạn muốn tìm kể từ số lớn nhất trong dãy.

Hàm SMALL: Tìm giá trị nhỏ nhất thứ k

Hàm SMALL là hàm tìm số nhỏ thứ k trong một dãy được nhập vào.

Cú pháp: SMALL(Array,k).

Các tham số:

• Array: Là một mảng hoặc một vùng của dữ liệu.
• k: Là thứ hạng của số mà bạn muốn tìm kể từ số nhỏ nhất trong dãy.

Nhóm hàm đếm dữ liệu trong Excel

Hàm COUNT: Đếm dữ liệu kiểu số

Hàm COUNT được sử dụng để đếm dữ liệu, các đối tượng trong chuỗi hay trong một bảng tính nhất định.

Cú pháp: COUNT(Value1, Value2…).

Các tham số: Value1, Value2… là mảng hay dãy dữ liệu.

Ví dụ: =COUNT(B1:B10) đếm các ô chứa dữ liệu kiểu số trong dãy từ ô B1 đến ô B10.

Hàm COUNTA: Đếm dữ liệu

Hàm COUNTA là hàm đếm tất cả các ô chứa dữ liệu.

Cú pháp: COUNTA(Value1, Value2…).

Các tham số: Value1, Value2… là mảng hay dãy dữ liệu.

Ví dụ: =COUNTA(B1:B8) đếm các ô chứa dữ liệu trong dãy từ ô B1 đến ô B8.

Hàm COUNTIF: Đếm có điều kiện

Hàm COUNTIF giúp đếm, tính số lượng trong một bảng tính như tính số lượng nhân viên trong công ty, đếm có bao nhiêu nhân viên nam, bao nhiêu nhân viên nữ…

Cú pháp: COUNTA(Range, Criteria).

Các tham số:

• Range: Dãy dữ liệu muốn đếm.
• Criteria: Điều kiện, tiêu chuẩn cho các ô đếm.

Ví dụ: =COUNTIF(B1:B9,”<20″). Đếm tất cả các ô từ B1 đến B9 có chứa số nhỏ hơn 20.

Hàm COUNTIFS: Đếm nhiều điều kiện

Hàm COUNTIFS giúp đếm, tính số lượng trong một bảng tính như tính số lượng nhân viên trong công ty, đếm có bao nhiêu nhân viên nam, bao nhiêu nhân viên nữ… kèm nhiều điều kiện khác.

Cú pháp: COUNTIFS(Range1; Criteria1; [Range2; …]; [Criteria2;

…])

Các tham số:

• Range: Dãy dữ liệu muốn đếm.
• Criteria: Điều kiện, tiêu chuẩn cho các ô đếm.

Ví dụ: =COUNTIFS(A1:A10; “>20”; B1:B10; “<30”). Đếm tất cả các

ô trong khoảng A1 đến A10 có chứa số lớn hơn 20 VÀ các ô trong khoảng B1 đến B10 nhỏ hơn 30.

Nhóm hàm Toán học trong Excel

Hàm ABS: Tính giá trị tuyệt đối

Hàm ABS là hàm tính giá trị tuyệt đối của một số hay một biểu thức số học nào đó.

Cú pháp: ABS(Number).

Đối số: Number là một giá trị số, một tham chiếu hay một biểu thức.

Ví dụ: =ABS(A10) trả về giá trị tuyệt đối của ô A10.

Hàm POWER: Tính lũy thừa của một số

Hàm POWER là hàm tính lũy thừa của một số, với cơ số cho trước.

Cú pháp: POWER(Number, Power). Các tham số:

• Number: Là một số thực mà bạn muốn lấy lũy thừa.

• Power: Là số mũ.

Ví dụ: =POWER(10,2) kết quả trả về là 100.

Hàm PRODUCT: Tính tích

Hàm PRODUCT giúp nhân tất cả các số đã cho với nhau, rồi trả về tích của chúng nhanh chóng.

Cú pháp: PRODUCT(Number1, Number2…).

Các tham số: Number1, Number2… là dãy số mà bạn muốn nhân. Ví dụ: =PRODUCT(B1,B6) nhân các số trong dãy số từ B1 đến B6. Hàm MOD: Lấy số dư của phép chia

Hàm MOD là hàm lấy số dư của phép chia để kết hợp và sử dụng với

các hàm khác thực hiện tính toán trên bảng tính Excel.

Cú pháp: MOD(Number, divisor).

Các đối số:

• Number: Là số bị chia.
• Divisor: Là số chia.

Ví dụ: =MOD(25,2) giá trị trả về là 1.

Hàm ROUNDUP: Làm tròn một số theo số thập phân cho trước

Hàm ROUNDUP giúp làm tròn những số thập phân trong bảng tính Excel nhanh chóng.

Cú pháp: ROUNDUP(Number, Num_digits)

Các tham số:

• Number: Là một số thực mà muốn làm tròn lên.
• Num_digits: Là bậc số thập phân mà các bạn muốn làm tròn.

Lưu ý:

• Nếu Num_digits > 0 sẽ làm tròn phần thập phân.
• Nếu Num_digits = 0 sẽ làm tròn lên số tự nhiên gần nhất.
• Nếu Num_digits < 0 sẽ làm tròn phần nguyên sau dấu thập phân.

Hàm EVEN: Làm tròn thành số nguyên chẵn gần nhất

Hàm EVEN giúp làm tròn lên thành số nguyên chẵn gần nhất.

Cú pháp: EVEN(Number).

Tham số: Number là số mà các bạn muốn làm tròn.

Lưu ý: Nếu number không phải là kiểu số thì hàm trả về lỗi #VALUE!

Hàm ODD: Làm tròn thành số nguyên lẻ gần nhất

Hàm ODD giúp làm tròn lên thành số nguyên lẻ gần nhất.

Cú pháp: ODD(Number).

Tham số: Number là số muốn làm tròn.

Hàm ROUNDDOWN: Làm tròn xuống 1 số

Hàm ROUNDDOWN làm tròn xuống một số.

Cú pháp: ROUNDDOWN(Number, Num_digits).

Các tham số:

• Number: Là một số thực mà bạn muốn làm tròn lên.
• Num_digits: Là bậc số thập phân muốn làm tròn.

Nắm vững những hàm Excel thông dụng trên đây, bạn dễ dàng áp dụng vào việc tính toán, thống kê của mình. Trong quá trình sử dụng Excel bạn còn có thể sử dụng hàm Index, hàm Vlookup…

Hàm tra cứu và kết hợp dữ liệu

Hàm VLOOKUP

Dùng VLOOKUP khi bạn cần tìm dữ liệu nào đó trong bảng hoặc phạm vi theo hàng.

1. Chọn một ô.
2. Gõ =VLOOKUP( rồi chọn giá trị muốn tra cứu.
3. Gõ dấu phẩy (,) và chọn phạm vi hoặc bảng muốn tìm giá trị đó.
4. Gõ dấu phẩy (,) và số cột của giá trị đang tìm kiếm.
5. Gõ ,FALSE) để tìm kết hợp chính xác.
6. Nhấn Enter.

Ví dụ công thức ở video trên như sau: =VLOOKUP(A7, A2:B5, 2, FALSE).

Ở hình thức đơn giản nhất này, hàm VLOOKUP cho biết:

=VLOOKUP(giá trị tra cứu, phạm vi chứa giá trị tìm kiếm, số cột trong phạm vi chứa giá trị trả về, tùy chọn TRUE cho kết quả tương đối hoặc FALSE cho kết quả chính xác).

Hàm CONCAT

Bạn có thể kết hợp dữ liệu ở nhiều ô thành một ô bằng biểu tượng & hoặc hàm CONCAT

Kết hợp dữ liệu bằng biểu tượng &

1. Chọn ô bạn muốn đặt dữ liệu kết hợp.
2. Gõ = và chọn ô đầu tiên bạn muốn kết hợp.
3. Gõ & và dùng dấu trích dẫn với các dấu ngoặc kép và khoảng trắng kèm theo.
4. Chọn ô tiếp theo bạn muốn kết hợp và nhấn Enter. Ví dụ: =A2&” “&B2.

Kết hợp dữ liệu bằng hàm CONCAT

1. Chọn ô tại vị trí bạn muốn đặt dữ liệu kết hợp.
2. Gõ =CONCAT(.
3. Chọn ô bạn muốn kết hợp đầu tiên.

• Dùng dấu phẩy để tách riêng các ô bạn đang kết hợp và dùng dấu trích dẫn để thêm khoảng cách, dấu phẩy hoặc text khác.
• Đóng công thức bằng dấu ngoặc đơn và nhấn Enter. Ví dụ:

=CONCAT(A2, ” Family”).

Qua bài viết: Giáo trình Excel 2021 toàn tập [phần cơ bản-2] nếu vẫn còn thắc mắc hoặc cần tư vấn, hỗ trợ. Quý khách vui lòng liên hệ theo thông tin dưới đây: