Giáo trình về Excel

hay gõ Enter để kết thúc 
Chú ý: Nếu là công thức mảng, sau khi kết thúc nhập nhấn F2 để quay lại chế độ Edit, sau đó để kết thúc bằng Ctrl + Shift + Enter.
3. Các hàm cơ bản của Excel.
a. Nhóm hàm toán học - Lượng giác (Math & Trig).
* Hàm ABS(Number). 
Chức năng: Trả lại trị tuyệt đối của tham số number
Ví dụ: ABS(-5) = 5; ABS(5-10) =5
* Hàm INT(Number).
Chức năng :Làm tròn một số tới số nguyên gần nhất.
Ví dụ: INT(3.2) = 3. ;	INT(-3.2) = - 3
* Hàm MOD(Number, n) .
Chức năng : Trả lại số dư của phép chia Number cho n
Ví dụ : MOD(10,3) =1 ;	 MOD(11,3)=2
* Hàm ROUND(Number, n).
Chức năng: Làm tròn tham số Number đến vị trí thứ n của phần thập phân.
Ví dụ:
ROUN(123.456, 2) = 123.46
ROUN(123.456, 1) = 123.5
ROUN(123.456, 0) = 123
ROUN(123.456, -1) = 120
* Hàm SQRT(Number) 
Chức năng: Tính căn bậc hai của Number
Ví dụ: SQRT(25) =5
* Hàm FACT(N) 
Chức năng: Tính N! 
Ví dụ: FAC T(4) =24
* Hàm LN(N) 
Chức năng: Tính Ln(N)
Ví dụ: Ln(2) =0.693147
* Hàm PI() 
Chức năng: Cho giá trị số Pi = 3.14593
* Hàm SUMIF( Range, Criteria, Sum_range) 
Chức năng: Cộng các ô thoả mãn một tiêu chuẩn nào đó.
+ Range : là vùng các ô cần ước lượng.
+ Criteria :là tiêu chuẩn dưới dạng số, biểu thức, hay văn bản xác định các ô sẽ được cộng. Ví dụ về tiêu chuẩn là 32, "32", ">32", "apples"
+ Sum_range: là các ô được cộng. Các ô trong vùng sum_range chỉ được cộng nếu các ô tương ứng trong vùng range phù hợp với tiêu chuẩn criteria. Nếu bỏ qua sum_range thì chính các ô trong vùng range được cộng.
Ví dụ: giả sử các ô từ A1:A4 lần lượt chứa các giá bất động sản sau: 100.000; 200.000; 300.000; 400.000. Còn các ô B1:B4 chứa tiền hoa hồng lần lượt ứng với mỗi giá trị bất động sản: 7.000; 14.000; 21.000; 28.000.
Khi đó SUMIF(A1:A4,">160.000",B1:B4) = 63.000.
b. Nhóm hàm thống kê (Statistical).
* Hàm AVERAGE(Number1, Number2,...)
Chức năng: Tính trung bình cộng của các tham số Number, Number2,....
Ví dụ : 
Trong các ô B1, B2, B3 chứa lần lượt 4, 8, 3; thì khi đó AVERAGE(B1:B3)=15
* Hàm RANK(Value, Range)
Chức năng: Xác định thứ hạng của giá trị Value so với các giá trị có trong Range (vùng chứa danh dách). Value và Range phải cùng kiểu dữ liệu là số, nếu không sẽ gây ra lỗi #VALUE, đồng thời gía trị X phải là một trong các giá trị nằm trong Range nếu không sẽ gây ra lỗi #N/A.
Ví dụ Công thức nhập ở ô C2 là: = RANK(B2, B$2:E$10);
* COUNT(Value1, Value2,...)
 Chức năng: Đếm các ô kiểu số trong danh sách đối số Value1, Value2,...
Ví dụ: trong các ô A1:A4 có các giá trị 2, 4, 6, 9 khi đó COUNT(A1:A4) = 4.
* COUNTA(Value1, Value2,...)
 Chức năng: Đếm các dữ liệu Value1, Value2,... (bao gồm cả số và chữ)
Ví dụ: trong các ô A1:A4 có các giá trị 2,"A", "B", 9 khi đó COUNT(A1:A4) = 4
* Hàm COUNTIF(Range,Criteria).
Chức năng: Đếm các ô trên vùng Range thoả mãn tiêu chuẩn Criteria.
+ Range : là vùng các ô cần đếm. 
+ Criteria: là tiêu chuẩn dưới dạng số, biểu thức, hay chuỗi văn bản chỉ định ô được đếm. Ví dụ tiêu chuẩn có thể viết dưới dạng sau: 32, "32", ">32", "apples"
Ví dụ : giả sử A3:A6 chứa tương ứng "apples","oranges", "peaches", "apples". Khi đó: COUNTIF(A3:A6,"apples") = 2.
* Hàm MAX(Number1, Number2,...)
Chức năng: Trả lại gía trị lớn nhất trong các tham biến.
Ví dụ : Dữ liệu trong các ô D1, D2, D3 lần lượt là 5, 7, 9 ; trong ô D4 nhập công thức = MAX(D1:D4) khi đó D4 bằng 9.
* Hàm MIN(Number1, Number2,...)
Chức năng: Trả lại gía trị nhỏ nhất trong các tham biến.
Ví dụ : Dữ liệu trong các ô D1, D2, D3 lần lượt là 5, 7, 9 ; trong ô D4 nhập công thức = MIN(D1:D4) khi đó D4 bằng 5.
* Hàm SUM(Number1, Number2,...)
Chức năng: Tính tổng các số trong danh sách tham số.
Ví dụ : Dữ liệu trong các ô D1, D2, D3 lần lượt là 5, 7, 9 ; trong ô D4 nhập công thức = SUM(D1:D4) khi đó D4 bằng 9.
c. Nhóm các hàm chuỗi (Text)
* Hàm LEFT(Text, n).
Chức năng: Lấy n kí tự từ vị trí trái nhất trong chuỗi text
Ví dụ: LEFT("Lê Thị Minh Nguyệt",6) = "Lê Thị"
* Hàm RIGHT(Text, n). 
Chức năng: Lấy n kí tự từ vị trí phải nhất trong chuỗi text
Ví dụ: RIGHT("Lê Thị Minh Nguyệt",11) = "Minh Nguyệt".
* Hàm MID(Text,m,n). 
Chức năng: Lấy n kí tự bắt đầu từ vị trí m tính từ trái sang trong chuỗi text
Ví dụ: MID("Lê Thị Minh Nguyệt",8,4) = "Minh".
* Hàm LEN(Text) 
Chức năng: Trả về chiều dài của chuỗi Text.
Ví dụ: LEN("Lê Thị Minh Nguyệt") = 18
* Hàm UPPER(Text) 
Chức năng: Đổi chuỗi Text thành chữ hoa.
Ví dụ: UPPER("Hà Nội") = "Hà nội"
* Hàm LOWER(Text) 
Chức năng: Đổi chuỗi Text thành chữ thường
Ví dụ: LOWER("Hà Nội) = "hà nội"
* Hàm PROPER(Text) 
Chức năng: Đổi chữ cái đầu tiên thành các chữ hoa còn lại là chữ thường
Ví dụ: LEFT("lê thị minh nguyệt") = "Lê Thị Minh Nguyệt"
* Hàm TRIM(Text) 
Chức năng: Cắt bỏ các ký tự trắng ở đầu và cuối chuỗi text
Ví dụ: TRIM(" Lê Quốc Minh ") = "Lê Quốc Minh"
* Hàm TEXT(value, format_text) 
Chức năng: Đổi value thành chuỗi theo khuôn dạng chỉ định trong format _text
Ví dụ: TEXT(1234.56,"##,###,###") = 1,234.56
* Hàm Value(text) .
Chức năng: Đổi chuỗi có kiểu số thành số
Ví dụ: VALUE("123")=123
d. Nhóm hàm ngày tháng, thời gian.
* Hàm DATE(year,month,day) 
Chức năng: Trả về ngày ứng với ngày, tháng năm cung cấp trong tham số.
Ví dụ: DATE(2001,08,10) = 10/08/2001.
* Hàm DAY(Date) 
Chức năng: Trả về ngày của biểu thức ngày tháng
Ví dụ: DAY("24/12/2001) = 24
Chú ý: Tham số kiểu ngày có trật tự ngày /tháng/năm tuân theo ngày giờ thiết lập cho hệ thống máy tính của bạn trong Control Panel. Trước khi sử dụng các hàm có liên quan đến kiểu ngày bạn hãy kiểm tra lại xem máy đang đặt trật tự ngày/tháng/năm hay tháng/ngày/năm, hay một trật tự khác.
* Hàm NOW() 
Chức năng: Trả về giờ hiện hành của hệ thống
* Hàm TODAY()
Chức năng: Trả về ngày hiện hành
* Hàm MONTH(Date) 
Chức năng: Trả về tháng tương ứng của biểu thức ngày tháng.
Ví dụ: MONTH("08/03/2001") = 3
* Hàm YEAR(Date) 
Chức năng: Trả về năm tương ứng của biểu thức ngày tháng.
Ví dụ: YEAR(30/04/2001) = 2001
* Hàm HOUR(Time) 
Chức năng: Trả về giờ của biểu thức thời gian
Ví dụ: HOUR("6:30:40") =6
* Hàm MINUTE(Time) 
Chức năng: Trả về phút của biểu thức thời gian
Ví dụ: MINUTE("6:30:40") =30
* Hàm SECOND(Time) 
Chức năng: Trả về giây của biểu thức thời gian
Ví dụ: SECOND("6:30:40") =40
e. Nhóm hàm Logic
* AND(Logic1, logic2,...)
Chức năng : Cho kết quả là phép và của biểu thức logic. Hàm AND cho kết quả TRUE khi và chỉ khi các biểu thức logic là TRUE, ngược lại hàm AND cho kết quả FALSE.
Ví dụ :
AND(1000>900,LEFT("Excel")="ex") =TRUE
AND(1000<900,LEFT("Excel")="ex")=FALES
* OR(Logic1, logic2,...)
Chức năng : Cho kết quả là phép OR của biểu thức logic. Hàm OR cho kết quả TRUE khi một trong các biểu thức logic là TRUE, hàm OR cho kết quả FALSE khi và chỉ khi tất cả các biểu thức Logic là sai
Ví dụ :
OR(1000>900,LEFT("Excel")="ex") =TRUE
OR(500>900,700<650)=FALSE
Chú ý : Logic là một biểu thức logic nó chỉ trả về giá trị đúng hoặc sai. Ví dụ a=5, b=3 ị a > b =TRUE
* NOT(Logic)
Chức năng : Cho kết quả là phép phủ định của biểu thức logic. Hàm NOT cho kết quả TRUE nếu biểu thức logic là FALSE và ngược lại.
Ví dụ : NOT(3>2) = FALSE
* IF(Logic_test, Value_if_true, Value_if_false)
Chức năng : Hàm IF cho kết quả 
+ Value_if_true nếu logic_test là TRUE
+ Value_if_false nếu logic_test là FALSE
Ví dụ: Nhập công thức = IF(F6>5;"Trên trung bình";"Dưới trung bình").
Chú ý :
+ Value_if_true có thể là hằng, hàm tính toán, biểu thức logic
+ Value_if_false có thể là hằng, hoặc chứa một hàm IF khác (gọi là IF lồng IF)
+ Logic_test có thể là một hàm trả về một giá trị nào đó : LEFT, RIGHT...
f. Nhóm hàm tìm kiếm và tra cứu:
Nhóm hàm tìm kiếm cho phép tìm kiếm theo khóa và trả về một thông tin liên quan đến khoá đó.
* Hàm VLOOKUP
Cấu trúc hàm:
 Hàm VLOOKUP (lookup_value,table_array,col_index_num, range_lookup)
Chứcc năng : Hàm VLOOKUP tìm trên cột bên trái của vùng table_array cho đến khi thấy lookup_value thì dò sang ngang lấy ô nằm ở cột được chỉ ra bởi col_index_num.
- Lookup_value là giá trị tìm kiếm trong hàng thứ nhất của bảng table_array
- Table_array là một bảng cần tìm thông tin trong đó. Dùng một tham chiếu đến một vùng hoặc tên vùng.
- Col_index_num là số thứ tự của cột trong bảng table_array tính từ trái qua phải để trả về giá trị tìm được.
- Range_lookup giá trị logic xác định có dò tìm một cách chính xác hay không. Nếu nó là TRUE (1) hoặc bỏ qua, tìm gần đúng (nếu không tìm được chính xác, nó trả về giá trị lớn nhất tiếp theo mà nhỏ hơn lookup_value). Nếu bằng FALES (0), VLOOKUP sẽ tìm chính xác. Nếu không tìm thấy nó cho lỗi #N/A
Ví dụ bạn hãy xem hình dưới đây:
Table_array
Lookup_ value
Col_index =2
* Hàm HLOOKUP.
Cấu trúc lệnh:
 Hàm HLOOKUP (lookup_value,table_array,row_index_num, range_lookup)
Chức năng :Hàm HOOKUP tìm trên hàng đầu của vùng table_array cho đến khi thấy lookup_value thì dò xuống đến hàng chỉ ra bởi row_index_num.
Tham số: 
- Lookup_value là giá trị tìm kiếm trong hàng thứ nhất của bảng table_array
- Table_array là một bảng cần tìm thông tin trong đó. Dùng một tham chiếu đến một vùng hoặc tên vùng.
- Row_index_num là số thứ tự của hàng trong bảng kể từ trên xuống dưới của table_array để trả về giá trị tìm được. Nếu row_index_num bằng 1 thì là hàng đầu tiên table_array, row_index_num bằng 2 thì là hàng thứ hai trong table_array và...
- Range_lookup giá trị logic xác định có dò tìm một cách chính xác hay không. Nếu nó là TRUE (1) hoặc bỏ qua, tìm gần đúng (nếu không tìm được chính xác, nó trả về giá trị lớn nhất tiếp theo mà nhỏ hơn lookup_value). Nếu bằng FALSE (0), HLOOKUP sẽ tìm chính xác. Nếu không tìm thấy nó cho lỗi #N/A
Ví dụ bạn hãy xem hình dưới đây:
Table_array
Lookup_ value
row_index =2
Chú ý: Đối với Table_array của cả hai hàm VLOOKP và HLOOKUP đều phải lấy địa chỉ tuyệt đối (bạn hãy xem ví dụ trên hình).
* Hàm Index .
Chức năng: Cho tham chiếu tới hay giá trị của một ô hay một mảng.
Cú pháp: có hai dạng
= INDEX(array,row_num,column_num)
= INDEX(reference, row_num, column_