Các hàm Excel thông dụng

Để giúp cho các bạn tiện lợi trong việc tra cứu các hàm trong Excel khi giải bài tập tin nghề, tôi xin trình bày một số hàm thông dụng sau: 
1. Hàm AND 
Hàm AND Trả về TRUE nếu tất cả các đối số là TRUE, trả về FALSE nếu một hay nhiều đối số là FALSE
Cú pháp: AND(logical1 [, logical2] [, logical3]...)
-logical: Những biểu thức sẽ được xét xem đúng (TRUE) hay sai (FALSE)
Nếu tất cả các biểu thức đều đúng, hàm AND() sẽ trả về giá trị TRUE, và chỉ cần 1 trong các biểu thức sai, hàm AND() sẽ trả về giá trị FALSE.
Bạn có thể dùng hàm AND() bất cứ chỗ nào bạn muốn, nhưng thường thì hàm AND() hay được dùng chung với hàm IF().
Ví dụ: Tại Ô D2 gõ:
=IF(AND(B2 > 0, C2 > 0), 1000, 0)
Nếu giá trị ở B2 và ở C2 lớn hơn 0, thì giá trị trả về tại ô D2 là 1.000, còn nếu chỉ cần ít nhất một trong 2 ô B2 hoặc C2 nhỏ hơn 0 thì kết quả tại ô D2 là 0
Vấn đề xét một giá trị nằm trong một khoảng
Có một bạn, khi nói đến một giá trị nằm trong khoảng từ 3 đến 10, đã dùng công thức 3 < x < 10 trong một công thức của hàm IF()
Không sai. Nhưng Excel thì "hổng hỉu". Vậy phải viết sao để Excel "hỉu" ?
Bạn ấy phải viết như thế này:
AND(x>3, x<10)
2. Hàm IF: Dùng để kiểm tra điều kiện theo giá trị và công thức
Cú pháp: IF(logical_test, value_is_true, value_is_false)
-logical_test: Biểu thức sẽ được xét xem đúng (TRUE) hay sai (FALSE)
-value_is_true: giá trị trả về khi biểu thức logical_test được kiểm tra là đúng (TRUE)
-value_is_false: giá trị trả về khi biểu thức logical_test được kiểm tra là không đúng (FALSE)
Ví dụ: Tại B1 gõ:
=IF(A1 >= 1000, "Số lớn", "Số nhỏ!")
Nghĩa là, nếu giá trị ở A1 lớn hơn hoặc bằng 1000, thì kết quả nhận được tại B1 sẽ là " Số lớn ", còn không, nếu A1 nhỏ hơn 1000, kết quả tại B1 sẽ là " Số nhỏ!"
3. Hàm OR: Trả về TRUE nếu một hay nhiều đối số là TRUE, trả về FALSE nếu tất cả các đối số là FALSE
Cú pháp: OR(logical1 [, logical2] [, logical3]...)
-logical: Những biểu thức sẽ được xét xem đúng (TRUE) hay sai (FALSE)
Nếu tất cả các biểu thức đều sai, hàm OR() sẽ trả về giá trị FALSE, và chỉ cần 1 trong các biểu thức đúng, hàm OR() sẽ trả về giá trị TRUE.
Giống như hàm AND(), bạn có thể dùng hàm OR() bất cứ chỗ nào bạn muốn, nhưng thường thì hàm OR() hay được dùng chung với hàm IF().
Ví dụ: Tại D2 gõ:
=IF(OR(B2 > 0, C2 > 0), 1000, 0)
Nếu giá trị ở B2 hoặc ở C2 lớn hơn 0 (tức là chỉ cần 1 trong 2 ô lớn hơn 0), thì kết quả trả về tại D2 là 1000, còn nếu cả 2 ô B2 và C2 đều nhỏ hơn 0, thì kết quả trả về tại D2 là 0.
4. Hàm LEFT
Hàm LEFT cho kết quả là chuỗi con bên trái của một chuỗi cho trước với số lượng ký tự được chỉ định trước.
Cú pháp: LEFT(text,num_chars)
Các tham số: 
- Text: Là chuỗi cho trước (ký tự trắng vẫn kể là một ký tự)
- Num_chars: Số lượng ký tự cần lấy
VD: tại ô A1 chứa giá trị : A01; tại B1 gõ:
= LEFT(A1,2) , kết quả tại ô B1 nhận được là: A0
2. Hàm RIGHT
Hàm RIGHT cho kết quả là chuỗi con bên phải của một chuỗi cho trước với số lượng ký tự được chỉ định trước.
Cú pháp: RIGHT(text,num_chars)
Các tham số: Tương tư hàm LEFT
VD: tại ô A1 chứa giá trị : A01; tại B1 gõ:
= RIGHT(A1,2) , kết quả tại ô B1 nhận được là: 01
3. Hàm MID
Hàm MID cho kết quả là chuỗi con của một chuỗi cho trước trên cơ sở vị trí và số ký tự được xác định trước.
Cú pháp: MID(text,start_num,num_chars)
Các tham số:
- Text và num_chars: Tương tự như ở hàm LEFT, RIGHT
- Start_num: Vị trí của ký tự bắt đầu (ký tự đầu tiên là 1, ký tự thứ hai là 2, )
VD: tại ô A1 chứa giá trị : A0123; tại B1 gõ:
= MID(A1,3,2) , kết quả tại ô B1 nhận được là: 12
4. Hàm LEN
Hàm LEN đo chiều dài của chuỗi (text). Mỗi ký tự được tính là 1 đơn vị, kể cả ký tự trắng (khoảng cách giữa hai ký tự hoặc hai từ). Text phải được đặt trong dấu ngoặc kép (“”).
Cú pháp: LEN(text)
Ví dụ:
= LEN(“informatics”) = 11. 
= LEN(“Long Xuyen city”) = 15
5. Hàm DAY: Trả về phần ngày của một giá trị ngày tháng, được đại diện bởi số tuần tự. Kết quả trả về là một số nguyên từ 1 đến 31.
Cấu trúc :     DAY(serial_number)
- Serial_number : là một biểu thức ngày tháng, có thể là một giá trị ngày tháng hay một chuỗi ngày tháng (date_text).
VD: 
+ Tại ô A1 (được định dạng : dd/mm/yyyy) chứa giá trị: 21/12/2010, tại B1 gõ:
 =Day(A1), kết quả tại ô B1 là: 21
6. Hàm DATEDIF
Chức năng :   Hàm DATEDIF trả về một giá trị, là số ngày, số tháng hay số năm giữa hai khoảng thời gian theo tùy chọn.
Cấu trúc :     DATEDIF(firstdate,enddate,option)
- firstdate  : là Ngày bắt đầu của khoảng thời gian cần tính toán
- Enddate  : là Ngày kết thúc của khoảng thời gian cần tính toán
- Option    : là tùy chọn, xác định kết quả tính toán sẽ trả về trong công thức. Các tùy chọn theo sau :
+ "d"     : Hàm sẽ trả về số ngày giữa hai khoảng thời gian.
+ "m"    : Hàm sẽ trả về số tháng (chỉ lần phần nguyên) giữa hai khoảng thời gian.
+ "y"     : Hàm sẽ trả về số năm (chỉ lần phần nguyên) giữa hai khoảng thời gian.
+ "yd"   : Hàm sẽ trả về số ngày lẻ của năm (số ngày chưa tròn năm) giữa hai khoảng thời gian.
+ "ym"  : Hàm sẽ trả về số tháng lẻ của năm (số tháng chưa tròn năm) giữa hai khoảng thời gian.
+ "md"  : Hàm sẽ trả về số ngày lẻ của tháng (số ngày chưa tròn tháng) giữa hai khoảng thời gian.
VD:
+ Một người thêu phòng từ ngày 11/01/2010 (tại A1) đến ngày 20/02/2010 (tại B1) tính số ngày người đó đã thêu phòng (tại C1).
Tại C1 gõ:
 = Datedif(A1,B1,”d”), kết quả tại ô C1 là: 40
7. Hàm MONTH: 
Hàm MONTH Trả về phần tháng của một giá trị ngày tháng, được đại diện bởi số tuần tự. Kết quả trả về là một số nguyên từ 1 đến 12.
Cấu trúc :     MONTH(serial_number)
- Serial_number : là một biểu thức ngày tháng, có thể là một giá trị ngày tháng hay một chuỗi ngày tháng (date_text).
+ date_text trong công thức phải được đặt trong dấu ngoặc kép "". Nếu là tham chiếu đến một ô khác thì ô này phải có định dạng là text.
VD: =MONTH("01/02/08")  giá trị trả về : 2
+ Tại ô A1 (được định dạng : dd/mm/yyyy) chứa giá trị: 21/12/2010, tại B1 gõ:
 =Day(A1), kết quả tại ô B1 là: 12
8. Hàm TODAY
Chức năng : Hàm TODAY trả về Ngày, Tháng, Năm hiện hành.
Cấu trúc :   TODAY()
+ Ngày tháng trả về trong công thức chính là Ngày tháng hiện hành của hệ thống máy tính mà bạn đang làm việc.
+ Tùy theo kiểu định dạng mà nội dung thể hiện sẽ khác nhau.
Ví dụ :
=TODAY()     giá trị trả về : 25/03/10    với định dạng dd/mm/yy
=TODAY()     giá trị trả về : 25-Mar-2010  với định dạng dd-mmm-yyyy
9. Hàm YEAR
Chức năng : Hàm YEAR trả về một giá trị, là số chỉ Năm trong một biểu thức ngày tháng.
Cấu trúc :     YEAR(serial_number)
- Serial_number : là một biểu thức ngày tháng, có thể là một giá trị ngày tháng hay một chuỗi ngày tháng (date_text).
+ date_text trong công thức phải được đặt trong dấu ngoặc kép "". Nếu là tham chiếu đến một ô khác thì ô này phải có định dạng là text.
+ Năm trong date_text phải trong khoảng từ 1900 đến 9999, nếu vượt quá số này, hàm sẽ báo lỗi #Value.
+ Thông thường date_text có 3 đối số (ngày, tháng, năm). Nếu date_text chỉ có 2 đối số thì excel sẽ tính toán như sau :
++ Nếu đối số thứ nhất < 32 và đối số thứ 2 < 13 thì excel coi đối số thứ nhất là Ngày, thứ 2 là tháng. Năm là năm hiện hành.
++ Nếu đối số thứ nhất <13, đối số thứ 2 12 thì excel coi đối số thứ nhất là tháng, thứ 2 là năm và cho ngày là 1.
++ Các trường hợp khác hàm sẽ báo lỗi #Value.
Ví dụ :
=YEAR("01/02/08")  giá trị trả về : 2008
=YEAR("12/29")      giá trị trả về : 2029
=YEAR("12/30")      giá trị trả về : 1930
=YEAR("13/13")      giá trị trả về : #Value!
=YEAR(today())      giá trị trả về : 2010
10. Hàm MIN
Hàm MIN cho kết quả là giá trị bé nhất trong các đối số được chỉ định
Cú pháp: MIN(number1,number2, )
Các tham số: number1, number2,  là những giá trị số.
Ví dụ:
= MIN(4,8,9,10,3,5) = 3
11. Hàm MAX
Hàm MAX cho kết quả là giá trị lớn nhất trong các đối số được chỉ định
Cú pháp: MAX(number1,number2, )
Các tham số: number1, number2,  là những giá trị số.
Ví dụ:
= MAX(4,8,9,10,3,5) = 10
12. Hàm AVERAGE
Hàm AVERAGE cho kết quả là giá trị trung bình số học của các đối số.
Cú pháp: AVERAGE(number1,number2, )
Các tham số: tương tự hàm MIN và MAX.
Ví dụ:
= AVERAGE(5,7,6) = 6. 
= AVERAGE(10,15,9) = 11,33
13. Hàm SUM
Hàm SUM cho kết quả là tổng các đối số trong một khối hoặc một tham chiếu hoặc một danh sách.
Cú pháp: SUM((number1,number2, )
Các tham số: tương tự hàm MIN và MAX.
Ví dụ: 
= SUM(B2:B4) = 13. 
= SUM(7,10,9) = 26
14. Hàm SUMIF()
Tính tổng các ô trong một vùng thỏa một điều kiện cho trước.
Cú pháp: = SUMIF(range, criteria, sum_range)
Range : Dãy các để kiểm tra điều kiện: có thể là ô chứa số, tên, mảng, hay tham chiếu đến các ô chứa số. Ô rỗng và ô chứa giá trị text sẽ được bỏ qua. 
Criteria : Điều kiện để kiểm tra tính tổng: Có thể địa chỉ, dạng số, biểu thức, hoặc text. Ví dụ, criteria có thể là: A1, 32, "32", "> 32", hoặc "apple", v.v...
Sum_range : Là vùng thực sự để tính tổng. Nếu bỏ qua, Excel sẽ coi như sum_range = range.
Lưu ý:
Sum_range không nhất thiết phải cùng kích thước với range. Vùng thực sự để tính tổng được xác định bằng ô đầu tiên phía trên bên trái của sum_range, và bao gồm thêm những ô tương ứng với kích thước của range. Ví dụ: 
- Nếu Range là A1:A5, Sum_range là B1:B5, thì vùng thực sự để tính tổng là B1:B5
- Nếu Range là A1:A5, Sum_range là B1:B3, thì vùng thực sự để tính tổng là B1:B5
- Nếu Range là A1:B4, Sum_range là C1:D4, thì vùng thực sự để tính tổng là C1:D4
- Nếu Range là A1:B4, Sum_range là C1:D2, thì vùng thực sự để tính tổng là C1:D4
Có thể dùng các ký tự đại diện trong điều kiện: dấu ? đại diện cho một ký tự, dấu * đại diện cho nhiều ký tự (nếu như điều kiện là tìm những dấu ? hoặc *, thì gõ thêm dấu ~ ở trước dấu ? hay *). 
Khi điều kiện để tính tổng là những ký tự, SUMIF() không phân biệt chữ thường hay chữ hoa. 
Ví dụ: Có bảng tính như sau
Tính tổng của những huê hồng mà có doanh thu > 160,000 ?
= SUMIF(A2:A5, ">160000", B2:B5) = 63,000 
Tính tổng của những doanh thu > 160,000 ?
= SUMIF(A2:A5, ">160000") = 900,000 
Tính tổng của những huê hồng mà có doanh thu = 300,000 ?
= SUMIF(A2:A5, "=300000", B2:B3) = 21,000
15. Hàm HLOOKUP
Chức năng: Hàm HLOOKUP là hàm dò tìm theo dòng, sẽ trả về giá trị của một ô nằm trên một dòng nào đó nếu thỏa mãn điều kiện dò tìm.
Cú pháp hàm:  HLOOKUP(lookup_value,table_array,row_index_num,option_lookup)
- Lookup_value: là giá trị dùng để dò tìm, giá trị này sẽ được dò tìm trong dòng đầu tiên của bảng dữ liệu dò tìm. Giá trị dò tìm có thể là một số, một chuỗi, một công thức trả về giá trị hay một tham chiếu đến một ô nào đó dùng làm giá trị dò tìm.
- Table_array: là bảng dùng để dò tìm, bảng dò tìm có thể là tham chiếu đến một vùng nào đó hay Name trả về vùng dò tìm. Bảng dò tìm gồm có Rj hàng và Ci cột (I,j >=1), trong đó dòng thứ nhất của bảng dò tìm sẽ được dùng để dò tìm.
- Row_index_num: là số thứ tự của dòng (tính từ trên xuống dưới) trong bảng dò tìm chứa giá trị mà ta muốn trả về. Row_index_num phải >=1 và <= số dòng lớn nhất có trong bảng dò tìm, ngược lại hàm sẽ trả về #VALUE! hoặc #REF.
- Option_lookup: là tùy chọn xác định kiểu dò tìm, có 2 kiểu dò tìm:
True hoặc 1 hoặc để trống: là kiểu dò tìm tương đối, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên dòng đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, nó sẽ lấy giá trị lớn nhất mà có giá trị nhỏ hơn giá trị dò tìm.
False hoặc 0: là kiểu dò tìm chính xác, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên dòng đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, hàm sẽ trả về #N/A.
Ví dụ:
16. Hàm VLOOKUP
Chức năng: Hàm VLOOKUP là hàm dò tìm theo cột, sẽ trả về giá trị của một ô nằm trên một cột nào đó nếu thỏa mãn điều kiện dò tìm.
Cú pháp hàm:  VLOOKUP(lookup_value,table_array,col_index_num,option_lookup)
- Lookup_value: là giá trị dùng để dò tìm, giá trị này sẽ được dò tìm trong cột đầu tiên của bảng dữ liệu dò tìm. Giá trị dò tìm có thể là một số, một chuỗi, một công thức trả về giá trị hay một tham chiếu đến một ô nào đó dùng làm giá trị dò tìm.
- Table_array: là bảng dùng để dò tìm, bảng dò tìm có thể là tham chiếu đến một vùng nào đó hay Name trả về vùng dò tìm. Bảng dò tìm gồm có Rj hàng và Ci cột (I,j >=1), trong đó cột thứ nhất của bảng dò tìm sẽ được dùng để dò tìm.
- Col_index_num: là số thứ tự của cột (tính từ trái qua phải) trong bảng dò tìm chứa giá trị mà ta muốn trả về. Col_index_num phải >=1 và <= số cột lớn nhất có trong bảng dò tìm, ngược lại hàm sẽ trả về #VALUE! hoặc #REF.
- Option_lookup: là tùy chọn xác định kiểu dò tìm, có 2 kiểu dò tìm:
True hoặc 1 hoặc để trống: là kiểu dò tìm tương đối, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên cột đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, nó sẽ lấy giá trị lớn nhất mà có giá trị nhỏ hơn giá trị dò tìm.
False hoặc 0: là kiểu dò tìm chính xác, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên cột đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, hàm sẽ trả về #N/A.
VD: