Microsoft Excel 2010
Tài liệu dành cho chương trình Chứng chỉ B
Microsoft Word 2010, Excel 2010 nâng cao
TRUNG TÂM TIN HỌC KINH TẾ
2013
Thông tin chương trình và tuyển sinh www.TinHocB.com
Tải tài liệu học tập www.TinHocB.com/TaiLieu

Giáo trình Tin học B – Phần Microsoft Excel 2010 nâng cao
TRUNG TÂM TIN HỌC KINH TẾ

1
MỤC LỤC
BÀI 1: CÁC HÀM CĂN BẢN 2
TỔNG QUAN 2
HÀM XỬ LÝ CHUỖI 2
HÀM XỬ LÝ NGÀY THÁNG 3
HÀM SỐ HỌC 4
HÀM THỐNG KÊ 5
HÀM LUẬN LÝ 6
HÀM DÒ TÌM 7
BÀI 2: CÔNG THỨC MẢNG TRONG EXCEL 9
KHÁI NIỆM 9
CÁC VÍ DỤ 9
BÀI 3: CƠ SỞ DỮ LIỆU 11
KHÁI NIỆM CƠ SỞ DỮ LIỆU 11
SẮP XẾP DỮ LIỆU 11
TRÍCH LỌC DỮ LIỆU 12
HÀM LIÊN QUAN CƠ SỞ DỮ LIỆU 14
KIỂM TRA DỮ LIỆU NHẬP 15

Các tham s cách nhau bi du phy (có th i da các tham s bi ký hiu
khác bng cách dùng lnh Start \ Control Panel \ Region and Language. Chn Additional
i chn th Numbers, chn ký hiu trong hp List Separator)
Tham s có th là công tha ch a ch vùng, hoc giá tr hng; tuy vy bao gi i
tha mãn kiu ca tng tham s. Có tham s bt buc và có tham s tùy chn. Hàm luôn tr v
mt giá tr.
HÀM XỬ LÝ CHUỖI
Tên hàm
Cú pháp và công dụng
LEFT
LEFT(chui, [n]): tr v n ký t bên trái ca tham s chui.
u ta b qua n thì mnh n=1.
Ví dc kinh t Kt qu: Tin hc
RIGHT
RIGHT(chui, [n]): tr v n ký t bên phi ca tham s chui.
u ta b qua n thì mnh n=1.
Ví dc kinh t Kt qu: kinh t
MID
MID(chui, m, n): tr v n ký t k t ký t th m ca chui
Ví dc kinh t Kt qu: hc
TRIM
TRIM(chui): tr v chut b nhng khong trng ca chui,
i t ch cách nhau mt khong trng.
Ví d: =TRIMc kinh t Kt qu: Tin hc kinh t
LOWER
LOWER(chui): tr v chui ch ng vi chu
Ví dC KINH T Kt qu: tin hc kinh t
UPPER
UPPER(chui): tr v chui ch ng vi chu
Ví dc kinh t Kt qu: TIN HC KINH T

SUBSTITUTE(chui, chui_mi, [i]): thay chu i
trong chui bng chui_mi, nu b  tt c
chung chui_mi.
Ví d: = SUBSTITUTEc kinh t )
 Kt qu: Tin hc B
TEXT
TEXT(snh_d t kiu s v kiu chui vnh_dng
(xem thêm phnh dng s)
Ví d Kt qu: $2,000.00
VALUE
VALUE(chui-s): tr v s ng vi chui-s 
Ví d Kt qu: 45
HÀM XỬ LÝ NGÀY THÁNG
Tên hàm
Cú pháp và công dụng
NOW
NOW(): tr v ngày-tháng- hin hành mà 
gi
Ví d: =NOW()  Kt qu: 13/04/2013 19:20
TODAY
TODAY(): tr v ngày-tháng-
Ví d: =TODAY()  Kt qu: 13/04/2013
DATE
 v ngày-tháng-ng.
Ví d: =DATE(2013,04,30)  Kt qu: 30/04/2013
DAY
DAY(ngày-tháng- v mt con s (1  31) ch 
ng vi tham s ngày-tháng-
Ví d Kt qu: 30
MONTH

SECOND(btgi): tr v mt s ch giây (0  ng vi btgi
Ví d : =SECOND()  Kt qu : 15
HÀM SỐ HỌC
Tên hàm
Cú pháp và công dụng
ABS
ABS(x): tr v giá tr tuyi (ABSolute) ca biu-thc-s x
Ví d: =ABS(3-5)  Kt qu: 2
INT
INT(x): tr v s nguyên (INTeger) ln nht còn nh c bng x
Ví d: =INT(9/2)  Kt qu: 4
MOD
MOD(x, y): tr v s a phép chia nguyên x cho y theo qui tc sau:
MOD(x, y) = x - y* INT(x/y)
Ví d: =MOD(9,2)  Kt qu: 1
ROUND
ROUND(x, n): làm tròn s x tùy theo n; vi n > 0: làm tròn vi n v trí
th, n = -n hàng
chc, n = -
Ví d: =ROUND(1.23456,2)  Kt qu : 1.23
PRODUCT
 v tích các s 
SUM
 v tng các s 
SUMIF
u-kin, vùng2): tr v t
ng theo th t vi các ô trong vùng1 thu-kin. Nu b qua tham
s .
Ví d :


AVERAGE
AVERAGE(danh-sách) : tr v giá tr trung bình ca các s trong danh-
sách
Ví d: = AVERAGE (6,4,2,8)  Kt qu: 5.75
Giáo trình Tin học B – Phần Microsoft Excel 2010 nâng cao
TRUNG TÂM TIN HỌC KINH TẾ

6
AVERAGEIF
u_kin[, vùng_2]): tr v giá tr trung bình
ca các ng theo th t vi các ô trong vùng1 tha
u-kin. Nu b qua tham s 
Ví d:

COUNT
COUNT(danh-sách) : tr v s ng các ô có kiu s trong danh-sách
Ví d Kt qu: 3
COUNTA
COUNTA(danh-sách) : tr v s ng các ô có ni dung khác rng
trong danh-sách
Ví d Kt qu: 4
COUNTIF
u-kin) : tr v s ng các ô trong vùng tha
u-kin
Ví d:

RANK
RANK(ô, vùng, kiu) : tr v th t xp hng ca ô trong vùng;
kiu = 1: xp hn, kiu = 0 hoc l p hng gim dn
Ví d:

trên cu tiên ca bng-dò (Không tìm thy tr v #N/A)
ng hp:
 Kiu dò là False 
 Kiu dò là True, hoc 1, hoc l p x. Excel
n mt giá tr ln nht còn nh ng giá-tr-dò. Ct
u tiên ca bng-c sp xn.
Ví d :

HLOOKUP
HLOOKUP(giá-tr-dò, bng-dò, dòng, kiu-dò): dùng giá-tr- dò
u tiên ca bng-dò (Không tìm thy tr v #N/A)
ng hp:
 Kiu-dò là False hay 0: dò tìm chính xác.
 Kiu-dò là True, hoc 1, hoc l p x. Excel
n mt giá tr ln nht còn nh ng giá-tr-dò.
u tiên ca bng-c sn.
MATCH
MATCH(giá-tr-dò, bng-dò, kiu-dò): Tr v th t v trí ca giá-tr-dò
trong bng-dò (ch là 1 dòng hoc 1 ct).
 Kiu-dò là 0 (dò chính xác và bng-dò không cn sp xp)
 Kiu-dò là 1 hoc l n giá tr ln nht còn nh
ng giá-tr-dò và bng-dò phc sp xn)
 Kiu-dò là -n giá tr nh nht còn l
bng giá-tr-dò và bng-dò phc sp xp gim dn).
Ví d :
Giáo trình Tin học B – Phần Microsoft Excel 2010 nâng cao
TRUNG TÂM TIN HỌC KINH TẾ

8


mng s ly kt qu tính toán tr v trên bng tính.
 Mt công thc mng có th tr v kt qu là nhiu ô (range) hay ch mt ô.
 Công thc mng có th tr v kt qu cho mt ô hoc nhiu ô, khi mun mt công thc
mng tr v kt qu cho nhiu ô thì phi chi mi nhp công th
nhn CTRL+SHIFT+ENTER
 Ký hiu sau khi nhn CTRL + SHIFT + ENTER {=SUM(B2:B5*C2:C5)}
 Mng hai chiu là mt hình ch nht bao gm nhiu hàng và nhiu c 
mng mt chiu, ta s dng các du ph n t trong cùng mt hàng
và du chm ph n t trong cùng mt ct.
 
o m bo s chính xác v kt qu ng hp vô tình sao
chép sai công thc do cha ch tham chiu)
o Dùng công thc mc vic v i công thc
trong m    a vùng công thc mng. Vì công thc mng không cho
phép xoá, sa cha mt ô trong vùng công thc mng.
o 

CÁC VÍ DỤ
 Ví d 1:

Giáo trình Tin học B – Phần Microsoft Excel 2010 nâng cao
TRUNG TÂM TIN HỌC KINH TẾ

10
Ch vi 1 công thc duy nht, ta vin CTRL + SHIFT +
 c kt qu là s s l trong dãy s. Gii thích: =SUM(IF(MOD
(B2:B9,2)=1,1,0))
o Hàm SUM  ngoài yêu cu vào là mt m tính tng
o y kt qu ca hàm IF lúc này là mt mng
o Hàm MOD(x,y) tr v s a x khi chia cho y

Cách 1: Thc hin
 t con tr vào mt ô trong ct mun xp th t,
 Chn th Data, nhóm Sort & Filter, nhp nút AZ hay nút ZA tùy theo bn mun xp
theo th t m.

Cách 2: Thc hin
 t con tr vào mt ô trong ct mun xp th t,
 Chn lnh Home \ Editing \ Sort & Filter \ Sort A to Z hoc Sort Z to A tùy theo bn
mun xp theo th t  t gim
Giáo trình Tin học B – Phần Microsoft Excel 2010 nâng cao
TRUNG TÂM TIN HỌC KINH TẾ

12

Cách 3: Thc hin
 Ch d liu cha các ct mun xp th t,
 Chn lnh Data \ Sort & Filter \ Sort hoc lnh Home \ Editing \ Sort & Filter \
Custom Sort, xut hin hp thoi Sort

 Hp Sort by :  chn ct sp xp trong danh sách các ct ca CSDL
 Hp Sort On: xp th t theo Values (giá tr), Cell Color (màu ô), Font Color (màu ký
t), Cell Icon (bing trong ô),
 Hp Order: xp th t A to Z (ch n), Z to A (ch gim dn), Smallest to Largest
(s n), Largest to Smallest (s gim dn), Custom List (theo th, theo tháng, theo
danh sách mi t to)
 Nút Add Level thêm mt cp sp xp na (Then by).
TRÍCH LỌC DỮ LIỆU

TRUNG TÂM TIN HỌC KINH TẾ

14
 Nu tiêu chun thuc loi tính toán  u kin s là mt m  logic (computed
criteria), t qu sau khi ban hành công thc là TRUE hoc FALSE, thì s dng
a ch ô ca mu tiên trong vùng cha các mu tin cn lc, ng thi xóa hay
ng ca vùng tiêu chun bng mt t 
 Nu tiêu chun thuc long trong
vùng tiêu chun.
Ví dục trình bày theo hai loi hay hn hp c hai loi tiêu chun
Lc ra các nhân viên thuc phòng A, B hay P

Lc ra các nhân viên thuc phòng A, B hay P và có LCB > 500

L
HÀM LIÊN QUAN CƠ SỞ DỮ LIỆU
 DAVERAGE(vùng danh sách, ct, vùng tiêu chun): Tr v giá tr trung bình ca các ô
trong tham s ct trong tham s vùng danh sách, các ô này thu kin ca tham s

xut hin hai hp Minimum và Maximum.

o Th Input Message: u ch
p xut hin khi bn chn ô nhp lip có 2 ph (hp
Title) và ni dung (hp Input message).

o Th Error Alert: u chn Show error alert after invalid data is entered
 thông báo xut hin nu nhp sai.

Giáo trình Tin học B – Phần Microsoft Excel 2010 nâng cao
TRUNG TÂM TIN HỌC KINH TẾ

16
BÀI 4: PHÂN TÍCH DỮ LIỆU
Ni dung:
 Công c Subtotal
 Công c Consolidate
 Công c Pivot Table
 Phân tích  nhy (What-If)
CÔNG CỤ SUBTOTAL
Chng hp tng nhóm d liu ca các ct kiu s  d
liu, chng hng, s ln nht, s nh nht, s t qu tng hc
i mi nhóm.
Gi s có bng d li

Ta cn tính tng s tin mà mi nhân viên thc hic:
(1) Xp th t ct Nhân viên thực hiện
(2) Ch d liu hay tr chut vào mt ô bt k
(3) Chn lnh Data \ Outline \ Subtotal, xut hin hp thoi Subtotal:
 Hp At each change in: chn tên c 

th kt qu trong vùng d li
Gi s công ty u ABC có 3 ca hàng, mi ca hàng có mt bng báo cáo doanh thu 6
u tng hp các báo cáo ca 3 ca hàng thành mt báo
a công ty. Giáo trình Tin học B – Phần Microsoft Excel 2010 nâng cao
TRUNG TÂM TIN HỌC KINH TẾ

18

Thc hic sau:
(1) Chn vùng d ling cách tr chut vào mt ô bt k  
(2) Chn lnh Data \ Data Tools \ Consolidate, xut hin hp thoi Consolidate

 Hp Function: chn hàm s dng khi tng hp d
liu bn ch tính tng.
 Hp Reference tham chiu lt các vùng d liu ngun.
 Hp All references: cha tt c các vùng d liu ngun cn thit cho vic hp nh
xóa mt vùng d liu trong hp All references, bn chi nhp nút Delete.
 Top row: chn nu mun dùng tên ct ca vùng ngun,
 Left column: chn nu mun dùng các giá tr ca cu tiên ca vùng ngun,  
giá tr ca ct Mt hàng,
 Create links to source data: chn nu mun d liu hp nhc cp nht mi khi có
i  vùng d liu ngun.

Giáo trình Tin học B – Phần Microsoft Excel 2010 nâng cao
TRUNG TÂM TIN HỌC KINH TẾ

19

What-u gì s xy ra) là n nh truy vn d liu
trong bng tính. Gi s, bn thit lp công thc tính Li nhun = Doanh thu  Chi phí. Bn t
hi: u gì s xy vi kt qu Li nhun nu i hoi hoc c
i?
Phân tích 1 chiều: Biến thay đổi theo hàng
Ví di t i = 6, li nhun i?
Giáo trình Tin học B – Phần Microsoft Excel 2010 nâng cao
TRUNG TÂM TIN HỌC KINH TẾ

21

 To bng vi bii theo hàng (A6:F8)
 Tr ô tham chiu kt qu vào ô li nhun (B8 = B3)
 Chn vùng B7:F8
 Ra lnh Data \ Data Tools \ What-If Analysis \ Data Table
 Cho Row input cell tham chin ô B1 vì bii theo hàng là Doanh thu.
 Nh xem kt qu
Phân tích 1 chiều: Biến thay đổi theo cột
Ví di t n 8, li nhui?

 To bng vi bii theo ct (A6:C11)
 Tr ô tham chiu kt qu vào ô li nhun (C7 = B3)
 Chn vùng B7:C11
 Ra lnh Data \ Data Tools \ What-If Analysis \ Data Table
 Cho Column input cell tham chin ô B2 vì bii theo ct là Chi phí
 Nhn OK  xem kt qu
Giáo trình Tin học B – Phần Microsoft Excel 2010 nâng cao
TRUNG TÂM TIN HỌC KINH TẾ

22


24
Ví d: Bn cn thit k mt giao din tìm kim thông tin Khi i dùng nhp vào mt
hoc nhim và nhn nút Tìm kiếm. Kt qu hin th  khung Kt qu
tìm kim i.
 thit k công c tìm kim, thc cht là s dng chc nâng cao (Advanced fillter),
tuy nhiên ta không lc th n, ghi li bng Macro và khi cn tìm
kim thì cho chy Macro (nhn nút Tìm ki nó thc hin l.