Tin học đại cương và ứng dụng
(Một số hàm thông dụng trong Excel)
Phan Trọng Tiến
Department of Software Engineering
Hanoi University of Agricaltural
Office location: 3
rd
floor, Administrative
building
Office phone: 8276346, Ext: 132
Website: www.fita.hua.edu.vn/pttien
Email:[email protected]
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
2
Ch5c. Công thức và một số hàm
thông dụng
1. Nhắc lại khái niệm công thức
2. Khái niệm hàm
3. Nhập công thức và hàm
4. Một số hàm thông dụng
5. Bài tập thực hành số 2
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
3
1. Công thức (nhắc lại)

Công thức



Đố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,
…
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
5
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

NOW(): thời điểm hiện tại

DATE(y,m,d)
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
7
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 f
x
để mở hộp
thoại chọn hàm.

Select a category:
loại hàm.

Select a function:
chọn hàm.
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E

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) =


TRUNC(16.276, -1) = 10
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
11
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

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, trả về phần
nguyên phép chia.

Phải lựa chọn
Analysis Toolpak
trong Tools

Add-ins
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel


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)
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
15
4.1. Một số hàm toán học và
lượng giác {7}

PRODUCT(X1,[X2],…]

X1*X2*…

SUMSQ(X1,[X2],…) hoặc SUMQ(miền)

Tính ∑x
i
2

SUMPRODUCT(mảng_x,mảng_y,…)

i
– y
i
)

2
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
16
4.2. Một số hàm thống kê {1}

COUNT(X1,X2,…)
hay COUNT (miền)

Đếm số ô có dữ liệu là số

COUNT(A1:A5)

COUNTA (X1,X2,…) hay COUNTA(miền)

Đếm số ô có chứa dữ liệu trong dãy

COUNTA(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



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.
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
18
Ví dụ thống kê: tổng kết điểm
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
19
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.


xcel
20
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/0
1/1999

DATE(2004,1,1)=0
1/01/2004

DAY(xâu ngày tháng)

Lấy giá trị ngày

DAY(“4-Jan”) = 4

MONTH(xâu ngày

nếu có trị_tra_cứu bằng hoặc giá trị lớn nhất nhỏ
hơn trị_tra_cứu thì trả về giá trị ứng với hàng tìm
thấy và cột_lấy_dữ_liệu trong bảng_tra_cứu.

Kiểu tra cứu quy định cách thức tra cứu:

0 (false):

Tìm chính xác

Vùng tra cứu không cần sắp xếp

1 (true) hoặc khuyết:

Tìm gần chính xác

Vùng tra cứu phải được sắp xếp tăng dầ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.
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
22
Ví dụ sử dụng VlookUp
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
23
Ví dụ sử dụng VLOOKUP

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)

Trả về giá trị tham chiếu ứng
với 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
02/07/14 Ch5c. Công thức và một s
ố hàm thông dụng trong E
xcel
25
4.6. Một số hàm Logic

NOT(X)

AND(X1,X2,…)