Tin học đại cương và ứng dụng

(Một số hàm thông dụng trong Excel)
Ch5c. Công thức và một số hàm thông dụng trong Excel
2
11/12/2005
Ch5c. Công thức và một số hàm thông dụng
Nhắc lại khái niệm công thức
Khái niệm hàm
Nhập công thức và hàm
Một số hàm thông dụng
Bài tập thực hành số 2
Ch5c. Công thức và một số hàm thông dụng trong Excel
3
11/12/2005
1. Công thức (nhắc lại)
Công thức
Bắt đầu bởi dấu “=“
Gồm:
Địa chỉ, hằng, miền,…
Toán tử
Hàm
VD:
= A1+A2-B2
= SIN(A1) + COS(B2)
= LN(A5)
Ch5c. Công thức và một số hàm thông dụng trong Excel
4
11/12/2005
2. Khái niệm hàm
Các công thức tính toán được xây dựng trước.
Phục vụ các tính toán thông dụng.
Cú pháp:
Tên hàm (danh sách đối số)
Đối số được phân cách bởi dấu phảy
Ví dụ: =rank(x,range,order)
Đối số có thể là giá trị, địa chỉ, hằng,…
Ch5c. Công thức và một số hàm thông dụng trong Excel
5
11/12/2005
Các loại hàm
Toán học và lượng giác (Math and Trigonometry)
Thống kê (Statistical)
Tài chính (Financial)
Tra cứu và tham chiếu (Lookup and Reference)
Văn bản (Text)
Thời gian (Date and Time)
Lo-gic (Logical)
Cơ sở dữ liệu (Database)
Thông tin (Information)
Kỹ thuật (Engineering)
Ch5c. Công thức và một số hàm thông dụng trong Excel
6
11/12/2005
Ví dụ
Toán học và lượng giác
ABS(X): trị tuyệt đối
SIN(X), COS(X)
LN(X)
Thống kê
AVERAGE(miền): tính trung bình
Thời gian
NOW(): thời điểm hiện tại
DATE(y,m,d)
Ch5c. Công thức và một số hàm thông dụng trong Excel
7
11/12/2005
3. Nhập công thức và hàm
Nhập trực tiếp vào ô
Sử dụng thanh công thức
Kích chuột vào biểu tượng fx để mở hộp thoại chọn hàm.
Select a category: loại hàm.
Select a function: chọn hàm.
Ch5c. Công thức và một số hàm thông dụng trong Excel
8
11/12/2005
4. Một số hàm thông dụng
Một số hàm toán học và lượng giác
Một số hàm thống kê
Một số hàm xử lý văn bản
Một số hàm xử lý thời gian
Một số hàm tra cứu và tham chiếu
Một số hàm Logic
Ch5c. Công thức và một số hàm thông dụng trong Excel
9
11/12/2005
4.1. Một số hàm toán học và lượng giác {1}
ABS(X)
Giá trị tuyệt đối của X
ABS(4) = ABS(-4) = 4
ABS(-4.5) = 4.5
INT(X)
Làm tròn “dưới” tới một số nguyên gần nhất
INT(-4.45) = -5
INT( 4.55) = 4
CEILING (X,N)
Trả về số nhỏ nhất ≥ X và chia hết cho N
N ở đây có thể coi là sai số
CEILING (4.27, 0.1) = 4.3
FLOOR (X,N)
Trả về số lớn nhất ≤ X và chia hết cho N
FLOOR (4.27, 0.1) = 4.2
Ch5c. Công thức và một số hàm thông dụng trong Excel
10
11/12/2005
4.1. Một số hàm toán học và lượng giác {2}
ROUND(X,N)
Làm tròn X
N - số chữ số sau dấu phảy “.”
ROUND(4.27, 1) = 4.3
ROUND(-4.27, 0) = - 4
ROUND(16.27, -1) = 20
TRUNC(X, [N])
Phần nguyên của X
N - số chữ số sau dấu phảy “.”
TRUNC(-4.45)
= TRUNC(-4.45, 0) = - 4
TRUNC(11.276, 2) = 11.27
TRUNC(16.276, -1) = 10
Ch5c. Công thức và một số hàm thông dụng trong Excel
11
11/12/2005
4.1. Một số hàm toán học và lượng giác {3}
COS(X)
COSIN của X (radian)
ACOS(X)
ARCCOS của X
SIN(X)
SIN của X
TAN(X)
TANG của X
LOG10(X)
Logarit cơ số 10 của X
LN(X)
Logarit Neper của X
PI()
3.14159…
RANDIANS (độ)
Chuyển từ đơn vị độ sang đơn vị Radian
DEGREES(radian)
Chuyển từ Radian sang độ
Ch5c. Công thức và một số hàm thông dụng trong Excel
12
11/12/2005
4.1. Một số hàm toán học và lượng giác {4}
EXP(X)
eX
SQRT(X)
Căn bậc 2 của X
MOD(X,Y)
X mod Y
RAND()
Trả về số ngẫu nhiên trong khoảng (0,1)
QUOTIENT(X,Y)
X/Y
Phải lựa chọn
Analysis Toolpak
trong Tools 
Add-ins
Ch5c. Công thức và một số hàm thông dụng trong Excel
13
11/12/2005
4.1. Một số hàm toán học và lượng giác {5}
SUM(X1,X2,…)
Tổng dãy số X1,X2,…
SUM(miền)
Tổng các số trong miền
Ví dụ:
SUM(A1:A9)
SUM(B2..B15)
Ch5c. Công thức và một số hàm thông dụng trong Excel
14
11/12/2005
4.1. Một số hàm toán học và lượng giác {6}
SUMIF(miền kiểm tra, điều kiện, miền tổng)
Tính tổng các phần tử trong miền tổng với điều kiện phần tử tương ứng trong miền kiểm tra thoả mãn điều kiện
Miền kiểm tra điều kiện
Miền tính tổng
Ví dụ:
B6: Số lượng cam
Dùng công thức:
SUMIF(A1:A5, “Cam”, B1:B5)
Ch5c. Công thức và một số hàm thông dụng trong Excel
15
11/12/2005
4.2. Một số hàm thống kê {1}
COUNT(X1,X2,…) hay COUNT (miền)
Đếm số lượng giá trị số trong dãy, miền
COUNT(A1:A5): đếm số ô có dữ liệu là số
COUNTA (X1,X2,…) hay COUNTA(miền)
Đếm số ô có chứa dữ liệu trong miền
COUNTA(A1:A5): số ô chứa dữ liệu trong A1:A5
COUNTIF (X1,X2,…, điều_kiện) hay COUNTIF(miền, điều_kiện)
Đếm số lượng giá trị thoả mãn điều kiện
COUNTIF(C3:C11,">=5"): Số ô có giá trị ≥5 trong C3:C11
COUNTIF(C3..C11,”5”): Số ô có giá trị = 5 trong C3..C11
Ch5c. Công thức và một số hàm thông dụng trong Excel
16
11/12/2005
4.2. Một số hàm thống kê {2}
AVERAGE(X1,X2,…)
Trung bình cộng của X1,X2,…
Có thể thay X1,X2,… bởi địa chỉ hay tên miền
AVERAGE(A1:A5)
MAX(X1,X2,…)
Giá trị lớn nhất
MIN(X1,X2,…)
Giá trị nhỏ nhất
RANK(X,miền,thứ_tự)
Cho thứ hạng của X trong miền
thứ_tự = 0 hoặc khuyết thì sắp xếp theo chiều giảm dần, khác 0 thì sắp xếp tăng dần.
Ch5c. Công thức và một số hàm thông dụng trong Excel
17
11/12/2005
Ví dụ thống kê: tổng kết điểm
Ch5c. Công thức và một số hàm thông dụng trong Excel
18
11/12/2005
4.3. Một số hàm xử lý văn bản
LEFT(s,n)
n ký tự trái của s.
RIGHT(s,n)
n ký tự phải của s.
MID(s,m,n)
n ký tự, từ vị trí m.
TRIM(s)
Bỏ dấu cách thừa.
LEN(s)
Độ dài xâu s.
VALUE(s)
Chuyển xâu s thành số.
TEXT(value, định_dạng):
Chuyển thành xâu theo định dạng.
Ví dụ:
TEXT(“01/01/2004”, ”mmm”) = “Jan”
TEXT(1/3,"0.00") = 0.33.
LOWER(s): Đổi xâu s thành chữ thường.
UPPER(s): Đổi xâu s thành chữ hoa.
Ch5c. Công thức và một số hàm thông dụng trong Excel
19
11/12/2005
4.4. Một số hàm thời gian
NOW()
Thời điểm hiện tại
TODAY()
Ngày hôm nay
DATE(năm, tháng, ngày)
Năm = 1900-9999
DATE(99,1,1)=01/01/1999
DATE(2004,1,1)=01/01/2004
DAY(xâu ngày tháng)
Lấy giá trị ngày
DAY(“4-Jan”) = 4
MONTH(xâu ngày tháng)
Lấy giá trị tháng
MONTH("5/10/2004") = 5
YEAR(xâu ngày tháng)
Lấy giá trị năm
DATEVALUE(xâu ngày)
Chuyển xâu sang dữ liệu số biểu diễn ngày tháng
DATEVALUE("01/01/1900") = 1
Ch5c. Công thức và một số hàm thông dụng trong Excel
20
11/12/2005
4.5. Một số hàm tra cứu và tham chiếu {1}
VLOOKUP(trị_tra_cứu, bảng_tra_cứu, cột_lấy_dữ_liệu, kiểu_tra_cứu)
Tra cứu giá trị của ô thuộc cột thứ cột_lấy_dữ_liệu mà giá trị của ô thuộc cột đầu tiên có giá trị bằng trị tra cứu.
Kiểu tra cứu quy định cách thức tra cứu:
0 (false):
So khớp
Vùng tra cứu không cần sắp xếp
1 (true):
So gần khớp (tìm giá trị “gần nhất”)
Vùng tra cứu phải được sắp xếp sẵn
Nói chung, vùng tra cứu nên được sắp xếp trước khi sử dụng vlookup để tra cứu.
Ch5c. Công thức và một số hàm thông dụng trong Excel
21
11/12/2005
Ví dụ sử dụng VLOOKUP
VT xác định bởi mã VT.
Vật tư nhập
Miền A16:B24
Cột thứ 2 (cột B)
Báo cáo vật tư
Mỗi dòng ứng với một vật tư.
Cột “NHẬP”: số lượng vật tư nhập
Cần phải tra cứu từ A16:B24, sử dụng công thức:
=VLOOKUP(B9,$D$17:$E$25,2,1)
Ch5c. Công thức và một số hàm thông dụng trong Excel
22
11/12/2005
4.5. Một số hàm tra cứu và tham chiếu {2}
HLOOKUP(trị_tra_cứu, bảng_tra_cứu, hàng_lấy_dữ_liệu, kiểu_tra_cứu)
Giống hàm VLOOKUP nhưng dữ liệu được xử lý theo hàng

INDEX (miền,hàng,cột)
Tham chiếu tới ô có số thứ tự hàng và cột trong miền được truyền vào
Ví dụ:
INDEX(A2:D9,5,2) sẽ tham chiếu đến hàng thứ 5, cột thứ 2 trong miền A2:D9
Ch5c. Công thức và một số hàm thông dụng trong Excel
23
11/12/2005
4.6. Một số hàm Logic
NOT(X)
AND(X1,X2,…)
OR(X1,X2,…)
IF(điều_kiện,giá_trị_1,giá_trị_2)
Nếu điều kiện đúng, trả về giá trị 1
Nếu điều kiện sai, trả về giá trị 2
Giá trị 2 có thể là một hàm IF khác
Ví dụ:
IF(B2>5, “Đạt yêu cầu”, “Không đạt”)
IF(B2>=8, “Giỏi”, IF(B2<5,”Trượt”, “Đạt yêu cầu”))
Ch5c. Công thức và một số hàm thông dụng trong Excel
24
11/12/2005
5. Bài tập thực hành 2
Bài tập thực hành 2 (tr.11, GT Tin học Excel ứng dụng)
Lập bảng điểm
AVERAGE: tính TB.
SUM: Tính tổng.
COUNTIF: Đếm.
IF: Xếp loại.
VLOOKUP: Tra cứu số trình.
RANK: Xếp thứ.