HC VIN CÔNG NGH BU CHÍNH VIN THÔNG
- - - - - - - ( - - - - - - -

BÀI GING
TIN HC QUN LÝ
Biên son : Ths. DNG TRN C
Ks. PHM HÙNG TH
Lu hành ni b HÀ NI - 2006 LI NÓI U
nhóm tác gi rt mong nhn đc ý kin đóng góp ca bn đc đ tài liu đc hoàn thin
hn. Cui cùng xin gi li cm n ti các đng nghip đã giúp đ nhóm biên son hoàn
thành cun tài liu này.

Nhóm tác gi
Phn 1: S dng Excel trong qun lý

PHN 1
S DNG EXCEL TRONG QUN LÝ GII THIU
Nh đã bit, Excel là ng dng bng tính rt thông dng chy trên môi trng Windows.
Rt nhiu ngi đang s dng Excel, nhng hu ht ch s dng các tính nng c bn nh to các
bng tính đn gin có trình bày theo hàng, ct, vi các phép tính c s nh cng tng, tính tr,
nhân, chia, trung bình v.v. Tuy nhiên, Excel là mt ng dng rt đa nng, không nhng d dàng
cho phép ngi dùng th hin các bài toán đn gin 1 cách nhanh chóng mà còn cho phép ngi
dùng s dng các chc nng tính toán và phân tích rt mnh nhm qun lý các h thng tính toán
tng đi phc tp.
Phn này gii thiu cho bn đc nhng chc nng nâng cao ca Excel nh to tp mu,
macro, bo mt v.v., các hàm nâng cao dùng trong các bài toán phc tp nh hàm if, hàm countif,
lookup v.v., các hàm lng nhau, và cách s dng các công c phân tích d liu trong Excel nh
Goal seek, Solver v.v. Vic s dng các hàm nâng cao cho phép thc hin các phép tính phc tp,
to các lnh điu kin, r nhánh, tham chiu d liu v.v., to ra các kt qu di các đnh dng
mong mun. c bit, vic cho phép s dng các hàm lng nhau (kt qu ca hàm này là tham s
cho hàm kia) to cho Excel mt kh nng lp trình khá mnh và tin li. Tuy nhiên, dù đc h
tr kh nng lp trình và các hàm khá mnh, Excel vn ch là 1 chng trình ng dng, do vy
kh nng qun lý d liu rt hn ch, không qun lý đc các h thng có lng d liu ln và
cn đn kh nng lp trình mnh. Phn 2 s gii thiu vi bn đc 1 công c qun tr d liu mnh
hn nhiu, chuyên s dng cho các ng dng qun lý d liu, đó là Microsoft Access.

ra cách gii tt nht. c hng dn gii, so sánh vi cách làm ca mình (nu t ngh ra).
- c phn tóm tt đ tng hp kin thc. Làm các bài tp ôn tp, đc li, nghiên cu li
nhng phn kin thc cha nm vng.
Yêu cu kin thc trc khi hc phn này
 hiu đc các kin thc trong phn này, bn đc cn có các kin thc c s v tin hc,
nm đc cách s dng Excel và các hàm c bn trong Excel. Ngoài ra, cn có các kin thc c
s v lp trình và qun lý d liu, có k nng phân tích bài toán.

6
Phn 1: S dng Excel trong qun lý

CHNG 1: S DNG CÁC CHC NNG NÂNG CAO 1.1 TO VÀ S DNG CÁC TP MU (TEMPLATE)
Trong công vic hàng ngày, nhiu khi ngi dùng có nhu cu s dng mt đnh dng file
Excel theo mt “mu” nào đó có đnh dng và các cách trình bày d liu bit trc, ví d nh các
hóa đn gi khách hàng, các báo cáo tài chính v.v. Nu mi ln ngi dùng to mt bng tính mi,
h li phi làm li mi thao tác đnh dng và trình bày li d liu thì s mt rt nhiu thi gian và
cha chc đnh dng đó đã ging nh đnh dng mà h mong mun hoc đnh dng ca file đã
làm ln trc. Chính vì vy, đ tit kim thi gian và công sc cho ngi s dng, Microsoft đã

-  ví d này, chúng ta mong mun thit lp đnh dng cho Font ch và kiu s. Vì vy 
hp chn “Category”, la chn ln lt các mc là General và Number đ thit lp các
đnh dng mong mun cho tng mc này. Sau khi đã thc hin xong mi thao tác, bm
nút lnh OK đ ghi li các thit lp và thoát khi hp thoi này.
-  ca s style, bm OK đ kt thúc vic thit lp đnh dng cho tp mu.
Nh vy, đn đây chúng ta đã to đc các đnh dng mong mun. Vic cui cùng và rt
quan trng là phi “nh” Excel ghi li các đnh dng đó thành 1 tp mu đ ln sau có th s dng
li chúng.  làm đc vic này, t menu File, chn Save As. Khi đó, Excel s m mt ca s
“Save as” đ chúng ta đt tên và ghi li tp mu va to.
- Trong mc “File name”, đt tên tùy ý cho tp mu. Trong mc “Save as type”, click chut
vào hp chn đ chn kiu file mun ghi li là Template. Sau đó, click chut vào nút lnh
“Save” đ ghi li tp mu.

8
Phn 1: S dng Excel trong qun lý

Lu ý: Mt cách ngm đnh, Excel s ghi tp mu vào th mc “Templates” trong th mc
mà Excel đc khi đng. Ta có th tùy ý la chn th mc mong mun lu bng cách click
chut vào mc “Save in”  phía trên cùng ca ca s “Save as”. Sau đó có th chn th mc
mong mun thay vì th mc ngm đnh ca Excel.
Nh vy ta đã có mt tp mu cha tt c các đnh dng và cách trình bày, b trí d liu
v.v., và có th s dng tp này cho ln sau.
1.1.2 S dng tp mu
Bây gi chúng ta s tìm hiu cách to mt file Excel mi áp dng mu đã đnh ngha trc.
 làm đc điu này, sau khi đã khi đng Excel, t menu chính ca chng trình, chn File >
New hoc bm vào biu tng “New” phía di menu “File”. Khi đó, Excel s m mt ca s có
tên “New workbook” nh sau:

Ca s “New workbook” có hai vùng cho ta la chn. Vùng th nht là “New”. Vùng này
cho phép ta có th to mt trang tính mi hoàn toàn vi la chn “Blank workbook” hoc có th

Macro có nhiu mc, t đn gin đn phc tp. Các Macro phc tp cho phép thc hin các tác
v khó, yêu cu ngi dùng phi có kin thc lp trình nht đnh (Visual Basic Macro). Các
Macro đn gin có th đc thit lp khá d dàng nh công c đc Excel cung cp, và không
cn đn kin thc v lp trình. Vi các Macro loi này, có mt cách đn gin nht đ to mt
Macro mi là yêu cu Excel ghi li nhng hành đng mà ngi dùng thc hin và lu tr nó di
dng mt Macro. Nhng hành đng mà ngi dùng cn ghi li có th là 1 hoc nhiu lnh hoc là
s kt hp ca các lnh mà Excel có th thc thi đc.
Ngoài tính nng có th tái s dng, Macro còn t ra rt mm do trong s dng vì chúng ta
hoàn toàn có th sa đi mi khi cn s dng mt vài thao tác mi mà ln s dng trc cha có.
1.2.2 To macro
Có nhiu cách đ to mt Macro trong MS Excel, tuy nhiên mc tiêu ca môn hc ch gii
thiu cách to và s dng Macro mt cách cn bn. Do vy, phn này ch gii thiu mt cách đn
gin nht đ ngi dùng  mi trình đ đu có th làm vic đc vi Macro.
Nh đã nói  trên, cách đn gin nht đ to mt Macro trong MS Excel đó là yêu cu
Excel ghi li các thao tác mà ngi dùng có nhu cu s dng nhiu ln di dng mt Macro. Các
bc đ thc hin công vic này nh sau:
- Chn menu Tools > Macro > Record New Macro đ hin th hp thoi to Macro nh sau:

- Trên vùng “Macro name”, đt tên cho Macro mong mun to ra.
- Trong vùng “Description”, Excel cung cp mt mô t ngm đnh cho mi Macro. Nu
mun thay đi, ta có th thay đi mô t cho Macro ca mình.
-  bt đu ghi, click chut vào nút lnh OK và bt đu thc hin các lnh mong mun
(Ví d thc hin thay đi đnh dng d liu, ghi file, m file mi v.v.). Excel s t đng
ghi li các lnh đó cho đn khi kt thúc ghi bng cách chn menu Tools > Macro > Stop
Recording hoc click chut vào “Stop Recording” trên thanh công c “Stop Recording” 11
Phn 1: S dng Excel trong qun lý
1.2.3 S dng macro

- Khi nh chut ra, hp thoi “Assign Macro” s xut hin cho phép gn nút lnh va to
vi mt Macro đc to ra t trc.

- Click chut đ chn Macro mun gi (ví d Macro1) khi nút lnh đc bm ri chn
“OK” đ kt thúc.
Lu ý:
Ta cng có th to mi 1 Macro và gn cho nút lnh thay vì chn mt Macro có sn
bng cách chn nút “Record” đ Excel ghi to mt Macro mi. Vic thc hin to mi mt Macro
đã trình bày  trên.
Nh vy, mi khi cn gi Macro, ta ch vic click chut vào nút lnh va to ra và Macro s
đc gi thc thi.
 giúp ngi dùng to ra mt nút lnh thân thin, Excel cho phép ngi dùng thay đi tên
ca nút lnh. iu này đc bit hu ích vì mi khi nhìn vào tên ca nút lnh chúng ta có th bit
ni dung mà nó s thc hin. Các bc tin hành thay đi tên ca nút lnh nh sau:
- Click chut phi lên nút lnh mun đi tên, chn “Text Edit” t menu đy xung.

13
Phn 1: S dng Excel trong qun lý
- Thay đi tên nút lnh thành tên mong mun.
- Click chut ra khi phm vi ca nút lnh đ thoát khi ch đ thay đi tên cho nút lnh.
Ngoài ra, Excel còn cho phép chúng ta thit lp đnh dng cho nút lnh.  thit lp đnh
dng cho nút lnh, tin hành các bc sau:
- Click chut phi lên nút lnh mun thit lp đnh dng, chn menu “Format Control” t
menu đy xung đ m hp thoi “Format Control”:
- La chn tab tng ng vi mong mun thit lp đnh dng và thit lp các giá tr mong
mun ri bm OK đ ghi li các thay đi (ví d có th thit lp phông ch, kích thc,
màu, v.v. cho nút lnh).

1.3 CÁC CHC NNG BO MT VÀ KIM SOÁT
1.3.1 S dng chú thích cho d liu trong Excel

1.3.2 Thm đnh d liu nhp
Chc nng thm đnh d liu nhp (data validation) ca Excel cho phép ngi dùng đa
vào các phép kim tra d liu nhp xem có tha mãn ràng buc mong mun không, ví d kim tra
xem d liu có thuc loi d liu đnh trc không, hoc kim tra xem d liu s có nm trong
khong quy đnh không, hoc đ dài ca d liu xâu ký t có vt quá đ dài cho phép không v.v.
Chc nng thm đnh d liu nhp rt hu ích trong trng hp ngi dùng phi làm vic
vi s lng nhiu d liu, khó có th kim tra d liu mt cách th công bng mt thng, hoc
vi d liu quan trng, cn có đ chính xác cao, mà s nhm ln có th gây ra nhng hu qu
đáng tic, ví d nh bng d liu bán hàng, bng lng, bng chm công v.v.
1.3.2.1 Thit lp các phép thm đnh d liu cho 1 ô
 thit lp quy tc thm đnh d liu cho 1 ô, click chn ô đó, sau đó chn menu Data >
Validation. Ca s Data Validation hin ra cho phép ngi dùng đa vào các quy tc thm đnh
d liu.

15
Phn 1: S dng Excel trong qun lý Trong ca s này, trên tab Settings, hp chn th xung Allow cho phép ta thit lp loi
quy tc thm đnh. Có các loi quy tc sau:
- Any value: Là giá tr mc đnh ca ô, cho phép đa vào giá tr bt k
- Whole number và Decimal: Ch cho phép nhp các giá tr s. Khi chn la chn này,
Excel hin thêm các ràng buc cho giá tr s nh sau:

Hp la chn th xung Data cho phép ta la chn ràng buc cho d liu. Ví d ta có th la
chn các toán t between (nm gia 2 giá tr max và min), not between (không nm gia), equal to
(bng), not equal to (không bng), greater than (ln hn), less than (nh hn), greater than or equal
to (ln hn hoc bng), less than or equal to (nh hn hoc bng).  đa vào các ràng buc mong
mun, chn toán t tng ng và đa tham s tng ng vào các ô trên ca s này.
- List: D liu nhp phi nm trong mt danh sách các d liu cho trc nào đó. Khi chn la

Thông thng, khi ngi dùng nhp d liu sai so vi các quy tc đã đc thit lp, Excel
s hin th thông báo li mc đnh. Tuy nhiên, ngi dùng có th t đnh ngha thông báo li
nhm hin th rõ hn thông tin v li cho ngi nhp d liu, hoc thay các thông báo li bng
ting Vit thay vì ting Anh nh mc đnh.

 làm vic này, trên ca s Data Validation, chn tab Error Alert. Trong ô Title đa vào tiêu
đ ca thông báo và trong ô Error Message đa vào ni dung thông báo li. Ngoài ra, Excel còn cho
phép thit lp phng án x lý tình hung khi xy ra li trong hp chn Style. Có 3 cách x lý, đó là
Stop (dng), Warning (cnh báo), và Information (thông tin). Chn cách x lý thích hp.
Sau khi thit lp xong, click OK. Nh vy, khi ngi dùng nhp sai d liu, thông báo li
có ni dung nh sau s đc hin th:

1.3.3 Bo mt trong Excel
Chc nng bo mt trong Excel cho phép bo v các d liu trong bng tính, chng các truy
nhp trái phép, đng thi cho phép ngi dùng truy cp d liu theo các mc bo mt khác nhau.

18
Phn 1: S dng Excel trong qun lý
Ngoài ra các chc nng bo mt đa dng ca Excel còn cho phép bo v các phn khác nhau ca
bng tính vi các mc đ khác nhau. Ví d, các vùng d liu quan trng có th đc bo v cn
mt, trong khi các vùng d liu khác có th không cn phi bo v.
Các lu ý v bo mt d liu trong Excel
- Bo v các d liu công thc và các d liu quan trng, không đc thay đi
- Bo v hoc n đi các d liu nhy cm trong bng tính
- Thêm vào các chú thích cho các d liu tng hp
Các mc bo mt trong Excel
- Bo v bng mt khu
- Bo v bng tính
- Bo v các sheet
- Bo v ô d liu hoc đ th

 bo v cu trúc hoc ca s, click chn la chn Structure hoc Windows tng ng.
a vào mt khu  ô Password, sau đó bm OK. Excel s yêu cu gõ xác nhn mt khu.
 b chc nng bo v bng tính, chn menu Tools > Protection > Unprotect Workbook.
Excel s yêu cu cung cp mt khu đã thit lp trc đó. Nu mt khu đc cung cp là đúng,
Excel s m khóa bo mt bng tính.
1.3.3.3 Bo v các sheet
Chc nng bo v sheet cho phép thit lp các bo v cho tng sheet trong bng tính cùng
vi ni dung ca tt c các ô trong sheet này.  thit lp chc nng này, chn menu Tools >
Protection > Protect Sheet. Ca s bo v sheet hin ra.

20
Phn 1: S dng Excel trong qun lý Trên ca s này, Excel cho phép ngi dùng đa vào mt khu đ m khóa bo v sheet.
Tip theo, ngi dùng có th chn các ni dung cn bo v  danh sách la chn bên di. Mc
đnh Excel ch cho phép chn (bôi đen) ô d liu, ngoài ra các chc nng khác nh đnh dng ô d
liu, đnh dng hàng, ct, thêm ct, hàng v.v. đu b cm.  cho phép các chc nng này, click
chn các la chn tng ng.
1.3.3.4 Bo v các ô d liu
Chc nng bo v ô d liu cho phép khóa các ô d liu hoc n đi, không cho phép hin
th trên bng tính.  thc hin bo v ô d liu, click chut phi vào ô d liu tng ng và chn
Format cells … Tip theo, trên ca s hin ra chn Protection. 21
Phn 1: S dng Excel trong qun lý
La chn Locked cho phép khóa ni dung ca ô d liu, trong khi la chn Hidden s cho
n ni dung ca ô. Chú ý rng các la chn này ch có tác dng khi ta thc hin vic bo v Sheet
(nh đã trình bày  trên). Do vy, ta thy các ô d liu ca Excel đu có la chn mc đnh là

Ví d: Ta có mt bng d liu cuc gi (đin thoi) ca khách hàng. Công vic cn thc
hin là tính cc cho các cuc gi da vào đ dài (tính theo giây) và đn giá cc ca cuc gi
(tính theo hng gi). Yêu cu nghip v ca bài toán là: nhng cuc gi đc coi là bình thng
nu nó dài hn 6 giây, khi đó cc s là đ dài cuc gi nhân vi giá. Ngc li, nu cuc gi nh
hn 6 giây thì đc coi là cuc gi “li” và khách hàng ch b tính cc bng ½ cc bình thng.
Khi đó ta có mt bng tính nh sau:

Vi bài toán nh trên, có th s dng hàm IF ca Excel đ tính toán giá tr ca ct “Thành
tin”. Cú pháp ca hàm IF nh sau:
IF(<Biu thc logic>, <Giá tr tr v nu đúng>, <Giá tr tr v nu sai>).
Lu ý: Khi áp dng vào Excel, không s dng các du >, <.  đây chúng tôi đa vào đ
bn đc có th d dàng nhn ra s cách bit gia các tham s ca hàm.
Trong đó, ý ngha ca các biu thc (tham s) trong hàm IF nh sau:
- Biu thc logic: Thng là các phép toán so sánh và kt qu ca nó cho ta 1 trong 2 kh
nng: đúng hoc sai. Biu thc này thng s dng các toán t so sánh nh =, > , <, >=
(ln hn hoc bng), <= (nh hn hoc bng).
- Giá tr tr v nu đúng: Là giá tr mà ngi lp biu thc mun hàm IF tính toán và tr v
nu biu thc logic  trên là đúng.
- Giá tr tr v nu sai: Là giá tr mà hàm IF s tr v nu biu thc logic  trên cho kt qu sai.

23
Phn 1: S dng Excel trong qun lý
Quay tr li vi bài toán trên. Tin hành áp dng hàm IF theo cú pháp trên cho tng ô trong ct
“Thành tin”, ta s có cách tính nh sau:
IF Thi gian liên lc >= 6, Thành tin = Thi gian liên lc * Giá cc , Thành tin = Thi
gian liên lc * Giá cc/2
iu này đc hiu nh sau: Nu thi gian liên lc ln hn hay bng 6s thì “Thành tin”
đc tính bng thi gian liên lc nhân vi giá cc. Ngc li, ngha là thi gian liên lc nh hn
6s, thì “Thành tin” đc tính bng thi gian liên lc nhân vi cc và chia đôi.
T đó, ta có công thc cho các ô  ct “Thành tin” s dng hàm IF nh sau:

hàm IF ban đu.
 đn gin nhng không mt tính tng quát, chúng ta tr li vi ví d v tính cc cho
khách hàng s dng đin thoi  trên. Vi nghip v yêu cu tng t nh c, nhng ta thêm vào
mt chính sách khuyn mãi. Theo đó, nhng khách hàng gi nhiu (cuc gi có đ dài ln hn 30
phút = 1800s) s đc gim 5% giá cc trên tng s cc h phi tr tính theo thi gian gi và
đn giá cc theo s b gi. Khi đó có th áp dng vic s dng hàm IF lng nhau nh sau:
“Nu thi gian liên lc nh hn 6s, cc s là Thi gian liên lc*Giá cc/2. Ngc li, nu
thi gian liên lc nh hn 1800s (và tt nhiên ln hn hoc bng 6s) thì cc s là Thi gian liên
lc * giá cc. Cui cùng, nu thi gian liên lc ln hn 1800s thì cc s là Thi gian liên lc *
giá cc * 95% (Gim giá 5%).”
Nh vy công thc đ tính cc s nh sau:
IF(Thi gian liên lc <6, Thành tin = Thi gian liên lc * Cc/2, IF(Thi gian liên lc
<1800,Thành tin = Thi gian liên lc* Giá cc,Thành tin = Thi gian liên lc* Giá cc *
95%) )
Áp dng vào bng d liu nh sau:

Công thc cho ô đu tiên ca ct thành tin s là:

 đây, chúng ta đ ý đn cú pháp ca câu lnh IF. Thay vì đa vào mt công thc tính c
th khi giá tr kim tra là sai (đi s th 3 ca hàm IF), chúng ta đa thêm vào mt hàm IF na.
Hàm này s kim tra giá tr ca ô thi gian liên lc đ quyt đnh s áp dung cách tính nào (tính
bình thng cho cuc ln hn 6s và nh hn 1800s hay tính theo cách khuyn mi cho cuc dài
hn 1800s).

25
Phn 1: S dng Excel trong qun lý
Excel cho phép chúng ta có th đa vào s lng tùy ý các lnh IF lng nhau. Nh là mt
bài tp, các bn hãy lp mt bng xp hng hc sinh theo qui c v xp hng da trên đim trung
bình nh đã trình bày  trên.
2.1.3 Hàm COUNTIF

kim vào (T D2 đn D9 và t E2 đn E9). Giá tr điu kin vn gi nh c là 10 (đim).
- Vi phép tính tng s đim 9, vùng tìm kim s là c 3 ct Toán, Vt lý và Hóa hc. Do
đó vùng tìm kim s là các ô t C2 đn E9.
Sau khi áp dng các công thc, Excel s tính cho chúng ta kt qu nh sau:

2.1.4 Hàm MAX
Trong Excel, hàm MAX là mt hàm khá đn gin. Hàm MAX đc s dng khi ngi
dùng cn tính mt giá tr có kiu s ln nht trong mt vùng d liu cn tìm kim. Kt qu tr v
ca hàm MAX là chính giá tr ln nht trong vùng d liu đó. Cú pháp ca hàm MAX nh sau:
MAX(Number_1, Number_2, … ,Number_n)

27

Trích đoạn Report design view BIN VÀ KHAI BÁO BIN To các báo cá ov tình hình nhâ ns Mr ng bài toán

---