TIN HỌC VĂN PHÒNG
Bài 9 & 10: HÀM TRONG EXCEL

1


Nội dung chính

1.
2.
3.
4.
5.
6.
7.
8.

Các loại địa chỉ trong cơng thức
Khái niệm hàm và quy tắc sử dụng
Các hàm thời gian
Các hàm văn bản
Các hàm toán học
Các hàm thống kê
Các hàm logic
Các hàm tìm kiếm

2


Các loại địa chỉ trong công thức

1. Các loại địa chỉ trong cơng thức

Mỗi ơ trong excel có địa chỉ giúp các cơng th
Khi
tronghiện
cơng tính
thức sẽtốn. V
cósao
thểchép
lấycơng
sốthức,
liệuđịarachỉđểbên
thực
thay đổi theo. Ví dụ :
ơ• B10
tức là ơ ở cột B dịng 10
trong ô B1, gõ công thức = A1
• sao chép công thức từ B1 xuống B2, công thức trong ô B2
Khi
thực hiện sao chép công thức, địa chỉ bê
đổi thành = A2
trong
thay
đổi
Ví đổi
dụ:
Có
nhữngcơng
trường thức
hợp ta sẽ

khơng
muốn
địatheo.
chỉ bị thay

cơng
thức
=A1
ơ B1
→ phải Gõ
chỉ ra
những
thành
phầnở cố
định trong địa chỉ bằng
cách Sao
thêmchép
dấu $từ
vàoB1
đằng
trước
xuống B2, công thức đổi thành

Trương Xuân Nam - Khoa CNTT

3

=



Cỏc loi a ch trong cụng thc

- Để tính Lương cho những người còn lại, đưa con trỏ về ô
E7.
iu gì xảy ra nếu ơ F 2 được viết là = E 2/E 8 ?

ã Tính tỷ lệ phần trăm Lương của từng người so với Tổng Lương :
1.

Đưa con trá vỊ « F2

4


Các loại địa chỉ trong cơng thức
Các loại địa chỉ
•
•
•
•

Tương đối :
E8
Cố định cột : $E 8
Cố định hàng : E $8
Tuyệt đối :
$E $8

Tham chiếu đến địa chỉ Sheet khác :
<tên sheet> !<địa chỉ ơ>

Ví dụ : ’Baitap1’ !E$8
Tham chiếu đến địa chỉ Workbook khác :
[<tên workbook>]<tên sheet> !<địa chỉ ơ>
Ví dụ : [Bai9.xlsx]Sheet2 !E8

5


2. Khái niệm hàm và quy tắc sử dụng
Hàm là các cơng thức định sẵn nhằm thực hiện những
tính tốn chun biệt mà các tốn tử đơn giản có thể
khơng thực hiện được
Cú pháp : Tên hàm(Các đối số)
Tên hàm viết liền, không phân biệt chữ hoa, chữ thường
Đối số có thể là giá trị, địa chỉ một ơ hoặc nhiều ô
Các đối số đặt trong cặp ngoặc đơn, ngăn cách nhau bởi
dấu phẩy hay chấm phẩy hay dấu theo quy định
Khơng có đối số vẫn phải viết cặp ngoặc, ví dụ TODAY()
Hàm có thể lồng nhau, ví dụ SQRT(SUM(B1 : B9))

6


Nhập hàm vào bảng tính
Đưa con trỏ về ơ cần tính rồi chọn một trong các cách sau
Cách 1 : gõ dấu = rồi gõ trực tiếp tên hàm vào ô
Cách 2 : vào ribbon Formulas → Function Library, chọn
hàm phù hợp trên menu
Cách 3 : vào ribbon Formulas → Function Library, ấn
Insert Function, chọn hàm và nhập đối số


7


3. Các hàm thời gian
Chú ý : định dạng ngày giờ trong Excel phụ thuộc vào
thiết lập của máy tính, thường là theo kiểu Mỹ
tháng/ngày/năm
DATE(year,month,day) : trả về ngày tháng ứng với số
ngày tháng năm
• DATE(2017,3,14) trả về 3/14/2017

DAY/MONTH/YEAR(serial_number) : trả về
ngày/tháng/năm trong chuỗi serial_number
• DAY("4/1/2017") trả về 1
• MONTH("4/1/2017") trả về 4
• YEAR("4/1/2017") trả về 2017

8


Các hàm thời gian

TIME(hour,minute,second) : trả về thời gian dạng số
• TIME(18,7,30) trả về 18 :07 :30 hoặc 6 :07 PM

TODAY() : trả về ngày hiện tại
DAYS(end_date,start_date) : trả về số ngày giữa hai thời
điểm
• DAYS(TODAY(),"1/1/2017") trả về số ngày từ đầu năm tới


hiện tại

9


Các hàm thời gian

WEEKDAY(serial_number,return_type) : trả về thứ trong
tuần
• serial_number : giá trị biến biểu diễn ngày tháng
• return_type : quy định kiểu tính ngày đầu tuần
1 : chủ nhật là 1 đến thứ 7 là 7
2 : thứ 2 là 1 đến chủ nhật là 7
3 : thứ 2 là 0 đến chủ nhật là 6
• WEEKDAY("3/14/2017",1) trả về 3, ngày 3/14/2017 là thứ 3

10


4. Các hàm văn bản

EXACT(text1,text2) : trả về True nếu text1 và text2
giống hệt nhau, ngược lại trả về False
• EXACT(Excel,EXCEL) trả về False

LOWER/UPPER(text) : chuyển text thành chữ
thường/hoa
• LOWER("EXCEL") trả về "excel"


PROPER(text) : viết hoa chỉ các chữ cái đầu của mỗi từ
trong text
• PROPER("HƠM nay") trả về "Hơm Nay"

11


Các hàm văn bản
FIND(text1,text) : trả về vị trí xuất hiện đầu tiên của
text1 trong text, nếu khơng tìm thấy thì trả về #VALUE !
• FIND("a","Hoa cỏ may") trả về 3
• có thể thêm tham số thứ 3 quy định vị trí bắt đầu tìm

FIND("a","Hoa cỏ may",4) trả về 9
• phân biệt chữ hoa chữ thường

FIND("N","Bình MINH") trả về 8

SEARCH(text1,text) : tương tự hàm FIND nhưng không
biệt chữ hoa chữ thường
LEN(text) : trả về độ dài (số ký tự) của text
• LEN("Hoa cỏ may") trả về 10

12


Các hàm văn bản
LEFT/RIGHT(text,n) : trả về n ký tự ngồi cùng bên
trái/phải của text
• RIGHT("Hoa cỏ may",3) trả về "may"


MID(text,n,k) : trả về đoạn giữa của text, tính từ vị trí n,
lấy k ký tự
• MID("Hoa cỏ may",5,3) trả về "cỏ "

REPLACE(text,n,k,text1) : cắt đoạn giữa của text gồm k
ký tự tính từ vị trí n, thay bằng text1
• REPLACE("Hoa cỏ may",5,6,"gạo") trả về "Hoa gạo"

TRIM(text) : cắt bỏ các ký tự trống vơ nghĩa của text
• TRIM(" Hoa cỏ may ") trả về "Hoa cỏ may"

13


5. Các hàm tốn học

ABS(x) : tính giá trị tuyệt đối
SIGN(x) : xác định dấu của x , trả về 1 nếu x > 0, 0 nếu
x = 0, -1 nếu x < 0
SQRT(x) : tính căn bậc 2 của x với x > 0
COS(x), SIN(x), TAN(x) : các hàm lượng giác, x tính
bằng radian
PI() : trả về số π 3.141592654
DEGREES(x) : đổi radian sang độ
RAND() : trả về số ngẫu nhiên giữa 0 và 1

14



Các hàm tốn học

SUM(n1,n2,. . .) : tính tổng n1 + n2 + . . .
PRODUCT(n1,n2,. . .) : tính tích n1 ∗ n2 ∗ . . .
FACT(n) : tính n! = 1 ∗ 2 ∗ · · · ∗ n
POWER(a,b) : tính ab
EXP(x) : tính e x
LOG(a,b) : trả về logb a, nếu khơng có b thì mặc định
b = 10
MOD(a,b) : trả về số dư trong phép chia hai số nguyên
a/b

15


Các hàm toán học

TRUNC(x) : cắt bỏ phần thập phân, chỉ lấy phần nguyên
• TRUNC(3.24) trả về 3, TRUNC(-3.24) trả về -3

INT(x) : trả về số nguyên lớn nhất không vượt quá x
• INT(3.24) trả về 3, INT(-3.24) trả về -4

ROUND(x,n) : làm tròn x đến n chữ số thập phân nếu
n>0
• Nếu n < 0 thì x được làm trịn đến chữ số bên trái của dấu

chấm thập phân
• ROUND(1234.567,2) trả về 1234.57, ROUND(1234.567,1) trả


về 1234.6, ROUND(1234.567,-2) trả về 1200

16


SUMIF(range,criteria,[sum_range])
tính tổng các giá trị trong phạm vi đáp ứng tiêu chí
• range : phạm vi cần đánh giá theo tiêu chí
• criteria : tiêu chí ở dạng số, biểu thức, tham chiếu ơ, văn bản

HÀM TỐN HỌC

hoặc hàm xác định

• sum_range : các ơ thực tế để cộng
Giá trị Tài sản

Tiền hoa hồng

Dữ liệu

$ 100.000,00

$ 7.000,00

$ 250.000,00

$ 200.000,00

$ 14.000,00


$ 300.000,00

$ 21.000,00

$ 400.000,00

$ 28.000,00

Công thức

Mô tả

Kết quả

=SUMIF(A2:A5,">160000",B2:B5)

Tổng tiền hoa hồng cho các giá
trị tài sản lớn hơn 160.000.

$ 63.000,00

=SUMIF(A2:A5,">160000")

Tổng các giá trị tài sản lớn hơn
160.000.

$ 900.000,00

=SUMIF(A2:A5,300000,B2:B5)


Tổng tiền hoa hồng cho các giá
trị tài sản bằng 300.000.

? 21.000,00
$

=SUMIF(A2:A5,">" & C2,B2:B5)

Tổng tiền hoa hồng cho các giá
trị tài sản lớn hơn giá trị tại C2.

? 49.000,00
$
17


SUMIFS(sum_range,criteria_range1,criteria1,
[criteria_range2,criteria2],. . .)

HÀM TỐN HỌC

tính tổng các giáHÀM
trị trongTỐN
phạm vi HỌC
đáp ứng nhiều tiêu chí

• •  Ví
Ví dụ
dụ Số

Sốlượng
lượngĐã
Đãbán
bán

Sản
Sản phẩm
phẩm

Người bán hàng

55

Táo
Táo

1

44

Táo
Táo

2

15
15

Atisô
Atisô


1

33

Atisô
Atisô

2

22
22

Chuối
Chuối

1

12
12

Chuối
Chuối

2

10
10

Cà rốt

rốt
Cà

1

33
33

Cà rốt
rốt
Cà

2

Côngthức
thức
Công
=SUMIFS(A2:A9,B2:B9,
B2:B9,
=SUMIFS(A2:A9,
"=A*",C2:C9,
C2:C9,1)
1)
"=A*",
=SUMIFS(A2:A9,B2:B9,
B2:B9,
=SUMIFS(A2:A9,
"<>Chuối",C2:C9,
C2:C9,1)
1)

"<>Chuối",

Mô tả
tả
Mô
Cộng tổng
tổng số
số sản
sản phẩm
phẩm bán
bán được
được bắt đầu
Cộng
bằng chữ
chữ "A"
"A" và
và do
do Người
Người bán
bán hàng
hàng 1 bán.
bằng
Cộng tổng
tổng số
số sản
sản phẩm
phẩm (không
(không bao
bao gồm
Cộng

Chuối) do
do Người
Người bán
bán hàng
hàng 11 bán.
bán.
Chuối)

Kết quả
15

30

18


6. Các hàm thống kê

MAX/MIN(n1,n2,. . .) : trả về giá trị lớn/nhỏ nhất trong
tập dữ liệu
• MAX/MIN(range) trả về giá trị lớn/nhỏ nhất trong một vùng

LARGE/SMALL(array,k) : trả về phần tử lớn/nhỏ thứ k
trong vùng array
RANK(x,range) : trả về thứ hạng của x trong danh sách
các số tham chiếu bởi range, xếp theo thứ tự giảm dần
• có thể thêm tham số thứ 3 với giá trị là 1 để lấy thứ hạng theo

thứ tự tăng dần


19


Các hàm thống kê

AVERAGE(n1,n2,. . .) : trả về trung bình cộng của một
dãy các số
• AVERAGE(range) trả về trung bình cộng trong một vùng

MODE(n1,n2,. . .) : trả về giá trị hay gặp nhất trong vùng
COUNT(range) : đếm số ô chứa dữ liệu số trong một vùng
COUNTA(range) : đếm số ô không rỗng trong một vùng

20


HÀM TỐN HỌC

COUNTIF(range,criteria)
•  COUNTIF HÀM TỐN

HỌC

–  Hàm đếm số ơ trong phạm vi xác định đáp ứng một tiêu chí nào đó.
–  Cúđếm
pháp:sốCOUNTIF(
ơ trong range,
phạm criteria
vi đáp) ứng một tiêu chí nào đó
• 

range:
mảng
hay
tham
chiếu
chứa số
•  COUNTIF
• 
Criteria:
tiêu
chí
–  Hàm đếm số ơ trong phạm vi xác định đáp ứng một tiêu chí nào đó.

Kết
–  Cú pháp: COUNTIF(
range, criteria Mơ
) tả
Cơng thức
quả
•  range: mảng hay tham chiếu chứa số
Số ơ có chứa táo trong các ơ từ A2 tới
•  Criteria: tiêu=COUNTIF(A2:A5,"t
chí
2
?
áo")

Dữ liệu

Dữ liệu


táo

32

camDữ liệu

54Dữ liệu

đàotáo

7532

táo cam

8654

đào

75

táo

86

Cơng thức

A5.

Mơ tả


Kết

=COUNTIF(A2:A5,A Số ơ có chứa đào trong các ơ từ A2 quả
tới
1
?
4)=COUNTIF(A2:A5,"t Số
A5.ơ có chứa táo trong các ơ từ A2 tới
2
?
áo")
A5.
=COUNTIF(A2:A5,A
Số ơ có chứa cam và táo trong các ơ
=COUNTIF(A2:A5,A Số ơ có chứa đào trong các ơ từ A2 tới
3)+COUNTIF(A2:A5,
3
?
1
?
từ A2 tới A5.
4)
A5.
A2)
=COUNTIF(A2:A5,A
=COUNTIF(B2:B5,">
Sốơơcócóchứa
giá trị
lớn

55 trong
cam
vàhơn
táo trong
các ơcác ơ
Số
3)+COUNTIF(A2:A5,
3
?
55")
từA2
B5.
B2tớitới
từ
A5.

2
?

=COUNTIF(B2:B5,"<
=COUNTIF(B2:B5,">
>"&B4)
55")

3
?

A2)

Số ơ có giá trị khác 75 trong các ơ từ

Số ơ có giá trị lớn hơn 55 trong các ơ
2
?
B2B2tới
từ
tớiB5.
B5.

=COUNTIF(B2:B5,"< Số ơ có giá trị khác 75 trong các ô từ
>"&B4)
B2 tới B5.

3
?

21


7. Các hàm logic
AND(logical1,logical2,. . .) : trả về True nếu tất cả các đối
số là True, ngược lại trả về False
OR(logical1,logical2,. . .) : trả về False nếu tất cả các đối
số là False, ngược lại trả về True
NOT(logical) : phép phủ định
IF(test,value1,value2) : trả về value1 nếu test có giá trị
True, ngược lại trả về value2
• hàm IF có thể lồng nhau đến 7 cấp

IFERROR(expression,value) : trả về giá trị của expression
nếu tính được, ngược lại trả về value

• IFERROR(3/0,"lỗi tính tốn") trả về "lỗi tính tốn"

22


8. Các hàm tìm kiếm
VLOOKUP(A,B,C,D) : tìm giá trị A trong cột đầu tiên
của vùng B, nếu tìm được sẽ trả về giá trị tương ứng
trong cột thứ C của vùng B
• B là vùng tìm kiếm hay bảng tra cứu, địa chỉ phải là tuyệt đối,

nên đặt tên cho vùng này
• Tham số D có giá trị logic, quy định cách thức tìm kiếm
D = True hoặc bỏ qua D : tìm "gần chính xác"
D = False : tìm chính xác giá trị A, nếu khơng thấy sẽ trả về
#N/A
• Trong chế độ tìm "gần chính xác"
cột đầu tiên của B phải xếp thứ tự tăng dần
A được xếp tương ứng với giá trị lớn nhất trong các giá trị
nhỏ hơn hoặc bằng A

23


Các hàm tìm kiếm

24


Các hàm tìm kiếm

HLOOKUP(A,B,C,D) : tương tự VLOOKUP nhưng thay
cột bằng hàng
COLUMN(A) : trả về thứ tự cột mà ô A đứng
• COLUMN(D5) trả về 4 (cột D)

ROW(A) : trả về thứ tự dịng mà ơ A đứng
MATCH(A,B,C) : trả về thứ tự của giá trị A trong dãy B,
giá trị C quy định cách thức tìm
• C = 1 hoặc bỏ qua C : tìm giá trị lớn nhất trong các giá trị

nhỏ hơn hoặc bằng A, dãy B phải xếp tăng dần
• C = -1 : tìm giá trị nhỏ nhất trong các giá trị lớn hơn hoặc

bằng A, dãy B phải xếp giảm dần
• C = 0 : tìm chính xác, khơng cần sắp xếp dãy B
• MATCH("c",{"a","b","c"}, 0) trả về 3

25