TOP 10 Hàm Excel Quan Trọng Nhất Trong Công Việc

Tải file thực hành tại đây.

Hàm SUMIFS

Khái niệm: là hàm tính tổng các giá trị dựa trên một hoặc nhiều điều kiện.

Cú pháp: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Trong đó: 

• Sum_range(bắt buộc): Phạm vi ô cần tính tổng.
• Criteria_range1(bắt buộc): Phạm vi được kiểm tra bằng cách sử dụng Criteria1.
• Criteria_range1 và Criteria1 thiết lập một cặp tìm kiếm, theo đó một phạm vi được tìm kiếm theo các tiêu chí cụ thể. Sau khi tìm thấy các mục trong phạm vi, giá trị tương ứng của chúng trong Sum_range sẽ được cộng vào.
• Criteria1(bắt buộc): Tiêu chí xác định ô nào trong Criteria_range1 sẽ được cộng vào. Ví dụ: tiêu chí có thể được nhập là 32, ">32", B4, "táo" hoặc "32".
• Criteria_range2, criteria2, …(tùy chọn): Các phạm vi bổ sung và các tiêu chí liên kết với chúng. Bạn có thể nhập tối đa 127 cặp phạm vi/tiêu chí.

Hàm COUNTIFS

Khái niệm: Là hàm đếm ô với một hoặc nhiều điều kiện. Các điều kiện có thể là số, ngày tháng, text hay ô chứa dữ liệu.

Cú pháp: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

Trong đó:

• criteria_range1 (Bắt buộc). Phạm vi thứ nhất trong đó cần đánh giá các tiêu chí liên kết.
• criteria1 (Bắt buộc). Tiêu chí dưới dạng một số, biểu thức, tham chiếu ô hoặc văn bản để xác định những ô nào cần đếm. Ví dụ: tiêu chí có thể được biểu thị là 32, ">32", B4, "táo" hoặc "32".
• criteria_range2, criteria2, ...Tùy chọn. Những phạm vi bổ sung và tiêu chí liên kết của chúng. Cho phép tối đa 127 cặp phạm vi/tiêu chí.

Ví dụ: Mình có bảng dữ liệu như bên dưới:

Giả sử, mình muốn tính tổng số lượng hóa đơn và tính tổng số tiền trong mỗi khu vực. Các bạn sẽ gõ công thức như sau:

Để tính tổng số lượng hóa đơn: =COUNTIFS($C$2:$C$4996,H3)

Trong đó: $C$2:$C$4996: Phạm vi vùng muốn đếm.
          H3: Điều kiện để đếm trong vùng đó.

Để tính tổng số tiền: =SUMIFS($E$2:$E$4996,$C$2:$C$4996,H3)

Trong đó: $E$2:$E$4996: Vùng muốn tính tổng.
          $C$2:$C$4996: Vùng chứa điều kiện để tính tổng.
          H3: Điều kiện để tính tổng.

Hàm TEXT

Khái niệm: Hàm TEXT giúp bạn thay đổi cách số hiển thị bằng cách áp dụng định dạng cho số bằng mã định dạng.

Cú pháp: =TEXT (Giá trị bạn muốn định dạng, "Mã định dạng bạn muốn áp dụng")

Ví dụ: Mình có bảng dữ liệu dưới đây:

Giờ mình muốn thay đổi không muốn hiển thị ngày tháng ở dạng các con số mà muốn hiển thị dạng ngày tháng năm bằng cách gõ công thức như sau trong bảng dữ liệu:

="Từ Ngày "&TEXT(C2,"dd/mm/yyyy")&" đến ngày "&TEXT(C3,"dd/mm/yyyy")

Hàm AGGREGATE

Khái niệm: là hàm trả về tổng gộp trong một danh sách hoặc một cơ sở dữ liệu với tùy chọn bỏ qua hoặc ẩn các hàng hoặc giá trị lỗi.

Cú pháp: AGGREGATE(function_num, options, ref1, [ref2], …)

Ví dụ: Mình có bảng dữ liệu bên dưới:

Trong bảng ví dụ 1, đề yêu cầu tính tổng doanh thu với 2 hàm: SUM và AGGREGATE. Bạn gõ công thức như sau:

=SUM(D6:D17)

=AGGREGATE(9,3,D6:D17)

Giải thích: 

• Hàm AGGREGATE: 9: Tương ứng công thức sử dụng hàm SUM.
•        3: Tùy chọn loại bỏ các ô bị lỗi, các ô bị ẩn đi.
•         D6:D17: Phạm vi vùng muốn tính.

Tuy nhiên khi sử dụng hàm SUM thì hiển thị kết quả lỗi vì trong cột doanh thu có ô chưa giá trị lỗi. Còn hàm AGGREGATE thì khác, hàm trả về kết quả đúng vì trong quá trình thực hiện thì nó bỏ qa các ô có giá trị lỗi hoặc ô bị ẩn đi.

Hàm ROUND

Khái niệm:  hàm làm tròn số thập phân, những số nằm sau dấu chấm hoặc dấu phẩy (tùy cấu hình Excel của bạn). Dùng để làm tròn kết quả tính trung bình.

Cú pháp: ROUND(number, num_digits)

Trong đó:

• number (Bắt buộc): Số mà bạn muốn làm tròn.
• num_digits (Bắt buộc): Số chữ số mà bạn muốn làm tròn số tới đó.

Ví dụ: 

Làm tròn 13,619,659.737300 ở ô C5 tới 2 vị trí thập phân: =ROUND(C5,2)
Làm tròn 13,619,659.737300 ở ô C5 bỏ số thập phân: =ROUND(C5,0)
Làm tròn trăm số 13,619,659.737300 ở ô C5: =ROUND(C5,-2)

Hàm EOMONTH

Khái niệm: là hàm để tính toán ngày đến hạn hoặc ngày đáo hạn rơi vào ngày cuối cùng của tháng.

Cú pháp: EOMONTH(start_date, months)

Trong đó: 
Start_date (Bắt buộc): Ngày biểu thị ngày bắt đầu. Nên nhập ngày bằng cách sử dụng hàm DATE hoặc sử dụng kết quả của những công thức hay hàm khác. Ví dụ: sử dụng DATE(2008;5;23) cho 23/05/2008. Có thể xảy ra sự cố khi nhập ngày tháng dưới dạng văn bản.
Months (Bắt buộc): Số tháng trước hoặc sau start_date. Giá trị dương cho đối số months tạo ra ngày trong tương lai; giá trị âm tạo ra ngày trong quá khứ.

Ví dụ:

Để tính ngày cuối tháng dựa vào ngày 29/10/2021: =EOMONTH(DATE(2021,10,29),0)

Để tính ngày cuối tháng sau dựa vào ngày 29/10/2021: =EOMONTH(DATE(2021,10,29),1)

Hàm EDATE

Khái niệm: Là hàm trả về giá trị ngày trước hoặc sau ngày đã biết đã xác định trước.

Cú pháp: =EDATE(start_date, months)

Trong đó: 

• Start_date: Ngày ngày bắt đầu.
• Months: Số tháng trước hoặc sau ngày bắt đầu (start_date). Giá trị dương cho đối số months tạo ra ngày trong tương lai, giá trị âm tạo ra ngày trong quá khứ.

Ví dụ: Sử dụng hàm Edate để tính bảng dữ liệu sau:

Dùng công thức: =EDATE(B3,6), thu được đáp án.

Hàm WORKDAY.INTL

Khái niệm: là hàm tính toán trong Excel để cho ra kết quả là số sê-ri của ngày trước hoặc sau một số ngày làm việc đã xác định.

Cú pháp: WORKDAY(start_date, days, [weekend],[holidays])

Trong đó:

• Start_date: Là ngày bắt đầu, là tham số bắt buộc.
• Days: Là ngày không nằm trong ngày cuối tuần và ngày lễ trước hay sau Start_date, là tham số bắt buộc.
• Lưu ý: Days > 0: số ngày tạo ra trong tương lai; Days < 0:="" số="" ngày="" tạo="" ra="" trong="" quá="">
• Weekend: Cho biết những ngày nào là ngày cuối tuần không được tính vào ngày làm việc.

Ví dụ: Sử dụng hàm WORKDAY.INTL để tính bảng dữ liệu dưới đây:

Tại ô C3 bạn nhập công thức như sau: =WORKDAY.INTL(B3,8,1,$F$3:$F$7)

Hàm VLOOKUP

Khái niệm: là hàm 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 tương ứng.

Cú pháp: =VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)

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 hay tìm kiếm tương đối với bảng giới hạn, nếu bỏ qua thì mặc định là 1.

Ví dụ: Mình có bảng dữ liệu dưới đây:

Để hiển thị đơn giá của điện thoại iPhone X trong bảng dữ liệu, các bạn nhập công thức như sau:

=VLOOKUP(F3,$C$2:$D$8,2,0)

Giải thích: 

• F3: đối tượng cần dò tìm.
• $C$2:$D$8: Phạm vi dò tìm và phạm vi đó cần cố định để kết quả trả về chính xác.
• 2:vị trí cột chứa giá trị hiển thị.
• 0: Hiển thị kết quả chính xác.

Hàm SUMPRODUCT

Khái niệm: là hàm trả về tổng của tổng của các dải ô hoặc mảng tương ứng. Thao tác mặc định là nhân nhưng cũng có thể thực hiện phép nhân, phép trừ và phép chia.

Cú pháp: =SUMPRODUCT(array1, [array2], [array3], ...)

Trong đó:

• 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.

Ví dụ: Mình có bảng dữ liệu như bên dưới:

Để tính tổng số tiền trong bảng trên, bạn gõ công thức sau: 

=SUMPRODUCT($F$3:$F$4996,$G$3:$G$4996)

Giải thích: Tổng tiền sẽ bằng tổng giá trị của cột số lượng nhân cho cột đơn giá.

Để tính tổng số lượng của Miền bắc trong bảng dữ liệu trên ta gõ công thức sau:

=SUMPRODUCT(($C$3:$C$4996=$J$6)*$F$3:$F$4996)

Giải thích: 

• $C$3:$C$4996: Vùng chứa điều kiện cần tính.
• $J$6: Điều kiện cần tính.
• $F$3:$F$4996: Vùng tính tổng.

Để tính tổng số lượng với điều kiện là vùng khu vực là miền Trung và năm 2020, bạn gõ công thức như sau:

=SUMPRODUCT(($C$3:$C$4996=$J$11)*(YEAR($B$3:$B$4996)=$J$12)*$F$3:$F$4996)

Giải thích:

• $C$3:$C$4996: Vùng chứa điều kiện thứ nhất với điều kiện nằm ở ô J11.
• $B$3:$B$4996: Vùng chứa điều kiện thứ hai và dùng hàm year để hiển thị ra năm với điểu kiện nằm ở ô J12.
• $F$3:$F$4996: Vùng tính tổng.

Hàm IF

Khái niệm: là hàm cho phép bạn tạo so sánh lô-gic giữa một giá trị và một giá trị dự kiến bằng cách kiểm tra điều kiện, rồi trả về kết quả nếu True hay False.

Cú pháp: IF(logical_test, value_if_true, [value_if_false])

Trong đó: 

• logical_test (bắt buộc): Điều kiện bạn muốn kiểm tra.
• value_if_true (Bắt buộc): Giá trị bạn muốn trả về nếu kết quả của logical_test là ĐÚNG.
• value_if_false (Tùy chọn): Giá trị bạn muốn trả về nếu kết quả của logical_test là SAI.

Ví dụ: Sử dụng hàm IF cho bảng dữ liệu dưới đây:

Để tính cột Thưởng, tại ô D4, mình gõ công thức như sau:

=IF(C4>80,10%,0)

Video hướng dẫn