Tin học đại cương và ứng dụng
(Một số hàm thông dụng trong Excel)
Trần Quang Hải Bằng
Faculty of Information Technology
University of Communication and Transport (Unit 2)
Office location: Administration building, Block D3, Room 6
Office phone: 38962018
Cell phone: N/A
Email:

Ch5c. Công thức và một số hàm thông dụng
1.
2.
3.
4.
5.

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

2


1. Công thức (nhắc lại)
n

Công thức
¤
¤

Bắt đầu bởi dấu “=“
Gồm:
n
n
n

n

Đị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

3

2. Khái niệm hàm
n

n
n

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

n

Đối số được phân cách bởi dấu phảy
¤

n

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

4


Các loại hàm
n

n
n
n


n

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)

n

n
n

n

n

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

5

Ví dụ
n

Toán học và lượng giác
¤
¤
¤

n

Thống kê
¤

n

ABS(X): trị tuyệt đối
SIN(X), COS(X)
LN(X)
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

6


3. Nhập công thức và hàm
n
n

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


7

4. Một số hàm thông dụng
n
n
n
n
n
n

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

8


4.1. Một số hàm toán học và lượng giác {1}
n

ABS(X)
¤ Giá trị tuyệt đối của
X
n
n


n

n

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
n
n

CEILING (X,N)
¤ Trả về số nhỏ nhất ≥ X
và chia hết cho N
¤ N ở đây có thể coi là sai
số
n

n

INT(-4.45) = -5
INT( 4.55) = 4

CEILING (4.27, 0.1) = 4.3


FLOOR (X,N)
¤ Trả về số lớn nhất ≤ X
và chia hết cho N
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

9

4.1. Một số hàm toán học và lượng giác {2}
n

ROUND(X,N)
¤ Làm tròn X
¤ N - số chữ số sau dấu phảy “.”
n
n
n

n

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
n TRUNC(11.276, 2) = 11.27
n TRUNC(16.276, -1) = 10
n

Ch5c. Công thức và một số hàm thông dụng trong Excel

10


4.1. Một số hàm toán học và lượng giác {3}
n

COS(X)
¤

n

ARCCOS của X
n

n

n

3.14159…

RANDIANS (độ)

¤

TANG của X

Logarit Neper của X

PI()
¤

SIN của X

Logarit cơ số 10 của X

LN(X)
¤

TAN(X)
¤

LOG10(X)
¤

n

SIN(X)
¤

n

COSIN của X (radian)


ACOS(X)
¤

n

n

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

11

4.1. Một số hàm toán học và lượng giác {4}
n

EXP(X)
¤

n

QUOTIENT(X,Y)
¤

¤

Căn bậc 2 của X

MOD(X,Y)
¤

n

eX

SQRT(X)
¤

n

n

X mod Y

X/Y
Phải lựa chọn
Analysis Toolpak
trong Tools
Add-ins

RAND()
¤

Trả về số ngẫu

nhiên trong
khoảng (0,1)
Ch5c. Công thức và một số hàm thông dụng trong Excel

12


4.1. Một số hàm toán học và lượng giác {5}
n

SUM(X1,X2,…)
¤

n

Tổng dãy số X1,X2,…

SUM(miền)
¤
¤

Tổng các số trong miền
Ví dụ:
n
n

SUM(A1:A9)
SUM(B2..B15)

Ch5c. Công thức và một số hàm thông dụng trong Excel


13

4.1. Một số hàm toán học và lượng giác {6}
n

SUMIF(miền kiểm tra, điều kiện, miền tổng)
¤

¤
¤

n

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:
n

SUMIF(A1:A5, “Cam”, B1:B5)
Ch5c. Công thức và một số hàm thông dụng trong Excel


14


4.2. Một số hàm thống kê {1}
n

COUNT(X1,X2,…) hay COUNT (miền)
¤

Đếm số lượng giá trị số trong dãy, miền
n

n

COUNTA (X1,X2,…) hay COUNTA(miền)
¤

Đếm số ô có chứa dữ liệu trong miền
n

n

COUNT(A1:A5): đếm số ô có dữ liệu là số

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
n
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

15

4.2. Một số hàm thống kê {2}
n

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
n

n

MAX(X1,X2,…)
¤

n


Giá trị lớn nhất

MIN(X1,X2,…)
¤

n

AVERAGE(A1:A5)

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

16


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

17

4.3. Một số hàm xử lý văn bản

n

LEFT(s,n)
¤

n

n ký tự phải của s.

n

Bỏ dấu cách thừa.

LEN(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ụ:
n

n ký tự, từ vị trí m.

TRIM(s)
¤

n


n

MID(s,m,n)
¤

n

n ký tự trái của s.

RIGHT(s,n)
¤

n

n

n
n

Độ dài xâu s.

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

18


4.4. Một số hàm thời gian
n

NOW()
¤

n

Ngày hôm nay

DATE(năm, tháng,
ngày)
¤
¤
¤

n

Thời điểm hiện tại

TODAY()
¤

n


n

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

n

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

n

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

Lấy giá trị ngày
DAY(“4-Jan”) = 4
Ch5c. Công thức và một số hàm thông dụng trong Excel

19

4.5. Một số hàm tra cứu và tham chiếu {1}
n

VLOOKUP(trị_tra_cứu, bảng_tra_cứu,
cột_lấy_dữ_liệu, kiểu_tra_cứu)
¤

n

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):
n

n

¤

1 (true):
n
n

n

So khớp
Vùng tra cứu không cần sắp xếp
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

20


Ví dụ sử dụng VLOOKUP
n
n

VT xác ñịnh bởi mã
VT.
Vật tư nhập
¤

¤

n

Báo cáo vật tư
¤
¤

n

Miền A16:B24
Cột thứ 2 (cột B)
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

21

4.5. Một số hàm tra cứu và tham chiếu {2}
n

HLOOKUP(trị_tra_cứu,

bảng_tra_cứu, hàng_lấy_dữ_liệu,
kiểu_tra_cứu)
¤

n

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ụ:
n

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

22


4.6. Một số hàm Logic
n
n
n

n

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ụ:
n
n

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

23

5. Bài tập thực hành 2
n

n

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

Ch5c. Công thức và một số hàm thông dụng trong Excel

24