Baitap Excel

Bi tp Microsoft Excel 2010

Trung tm Tin hc i Hc An Giang 87

BI TP MICROSOFT EXCEL I. CC HM TON HC

Bi 1: Dng cc php ton s hc tnh cc gi tr cho cc trng.

CC PHP TON S HC

x y (x+y)2 x2+y2+2xy x/y 3xy

9 5

125 48

32 18

29 12

Bi 2: Dng cc hm ton hc tnh gi tr cho cc trng.

CC HM TON HC - MATH FUNCTIONS

x y Mod(x,y) Int(x/y) Sqrt(x+y) Round(x/y,2) Power(x,4) Produc(x,y)

7 2

13 -4

15 66

8 8

2. HM LUN L (LOGIC)

Bi 1: Dng cc hm l lun (Logic) in vo cc trng.

a b c m=a>b n=b>c And(m,n) Or(m,n) And(m,Or(m,n))

4 5 14

2 12 32

24 14 16

24 24 16

3. HM NGY GI (DATE AND TIME)

Bi 1: Dng cc Hm Day, Month, Year v Weekday(SerialNumber) ly v ngy, thng, nm ca 1 s SerialNumber v dng hm Date(y,m,d) tr v dng ngy thng.

serial Ngy

(2)

Thng

(3)

Nm (4)

Stt ngy

trong tun To li ngy thng

t (2), (3), (4)

36500

38500

39000

40000



Bi 2: Dng cc hm Second, Minute, Hour (SerialNumber) ly v giy, pht, gi ca 1 s SerialNumber v Dng hm Time(h,m,s) tr v dng thi gian.

Serial

number

Giy

(2)

Pht

(3)

Gi (4)

To li thi gian t (2),(3),(4)

0,32

0,75

0,64

0,45

4. CC HM X L CHUI (TEXT)

Bi 1: S dng cc hm x l chui v cc hm ton hc.

Mt hng

M

H M

loi STT

H n gi

S lng

Gim gi Thnh tin

Kaki 2 K1252

80000 15

Jean 1 J2011

200000 24

Jean 2 J0982

150000 12

Kaki 1 K5801

120000 30

Yu cu:

1. Lp cng thc cho ct M Loi, bit rng: K t cui cng ca M H l M Loi. 2. Lp cng thc cho ct STT H, bit rng: 3 k t gia ca M H l STT ca

ho n. 3. Tnh ct gim gi sao cho:

a. Nu M loi l 1 th Gim gi = 30%*n gi * S lng b. Nu M loi l 2 th Gim gi = 50%*n gi * S lng

4. Tnh ct thnh tin = n gi * S lng - Gim gi

5. HM D TM

Bi 1: Trnh by bng tnh sau.

H v tn TB Xp loi Hng Bng Xp Loi

Trn Minh 5,9 TB Xp Loi

Nguyn Bo 8,6 0 Yu

L Hng 9,2 5 Trung Bnh

Phm Tun 9,5 7 Kh

Cao Bch 3,5 9 Gii

L Tun 6,4

Phan Cng 8,6

Yu cu:

1. Lp cng thc in d liu cho ct xp loi. 2. Xp hng cho cc hc sinh theo th t t cao n thp.




M Hiu Tn Hng Phn Khi Tn Xe Bng m phn khi

S11 M PK Phn Khi

Y11 10 100

H12 11 110

S12 12 125

Y10

H11

Bng tn xe

M Hng H S Y

Tn Hng Honda Suzuki Yamaha

Bng tn loi xe

100 110 125

Honda Dream Wave Spacy

Suzuki S100 Viva Fx

Yamaha Crypton Sirius Majesty

Yu cu:

1. Lp cng thc in d liu cho ct Tn Hng, bit rng: K t u tin ca M Hiu l M Hng Xe.

2. Lp cng thc in d liu cho ct phn phi, bit rng 2 k t cui ca M Hiu l M Phn Phi.

3. Lp cng thc ly d liu cho ct tn xe.

6. HM THNG K.


Kt qu thi cui kha

M s H v tn Windows Word Excel TB Kt qu

Xp loi

T001 Nguyn Hong Phng 7 7 6

T002 Nguyn Duy Dng 9 8 9

D003 Hong Quc Bo 8 6 6

T004 V Quc Vit 8 7 5

D005 Hong Bo Tr 10 8 6

K006 Bi ng Khoa 5 4 2

K007 V Th Thu H 10 7 7

T008 Nguyn Th Kim Loan 9 8 6



Bng im thm Bng thng k

M im cng thm

S th sinh u

T 0,5

S th sinh rt

D 1

S th sinh c im trung bnh >8

K 2

im trung bnh thp nht

Ngi c im trung bnh cao nht

Yu cu:

1. Tnh im Trung Bnh (TB) bit rng Mn Excel nhn h s 2. 2. Chn thm ct im thm bn tri ct Kt Qu. in vo ct im Thm da

vo k t vo k t u ca M D trong bng im Thm. 3. Kt qu = im Trung Bnh + im Thm 4. Xp Loi. Nu im Trung Bnh (TB) >=8 xp loi Gii. Nu TB < 8 v

TB>=7 xp loi Kh. Nu TB < 7 v TB>=5 xp loi Trung Bnh. Ngc li xp Yu.


BNG CHI TIT THNG TIN KHCH HNG CA 1 KHCH SN

STT H tn M s Ngy n Ngy i Tin n GT GN Tng cng

1 Trn Nam L1A-F3 03/05/2011 16/03/2011

2 Nguyn Thy L2A-F1 03/06/2011 20/03/2011

3 Nguyn An L1A-F2 03/10/2011 30/03/2011

4 Hunh Bo L2A-F1 23/03/2011 04/01/2011

5 Trn nh L1B-F3 17/03/2011 30/04/2011

6 Phan Phc L2B-F2 22/03/2011 27/03/2011

7 H Bo Ca L1A-F3 30/03/2011 21/04/2011

8 Phm Thnh L2B-F3 04/03/2011 21/04/2011

9 L Quc L1B-F1 04/05/2011 05/12/2011

10 Bi Th S L1B-F2 04/12/2011 27/04/2011

BIU GI PHNG BIU GI KHU PHN N

Loi phng GT GN

M phn n F1 F2 F3

L1A 260000 45000

n gi 20000 35000 50000

L1B 250000 40000

L2A 210000 36000

L2B 190000 30000

Yu cu:

1. Lp cng thc tnh s liu cho ct tin n, bit : Tin n = s ngy * n gi khu phn n. Bit 2 k t cui ca M s l M Phn n.



2. Thm ct S Tun vo bn tri ct. Lp cng thc cho ct s tun, bit s tun l s ngy c i ra tun l (khng tnh cc ngy l).

3. Lp cng thc cho ct GT (n Gi Tun). 4. Thm ct s ngy l vo bn tri ct GN. Lp

cng thc cho ct s ngy l bit. S ngy l l s ngy cn li sau khi i ra tun

5. Lp cng thc cho ct GN (n Gi Ngy). 6. Chn thm ct tin phng vo bn tri ct tng

cng. Tin phng = GT* S Tun + GN * S ngy l. 7. Thm ct gim gi vo bn tri ct tng cng. Tnh Ct gim gi, bit nu s

ngy t 15 ngy tr ln th gim gi 5% Tin phng. 8. Tnh ct tng cng bng = Tin n + Tin Phng - Gim Gi. 9. Lp cng thc tnh doanh thu theo tng phng Bng Thng k.

7. BI TP TNG HP.

Bi 1: Trnh by bng tnh. Thanh ton tin in.

BNG THANH TON TIN IN

STT H Tn Ch H M H S C

S Mi

S Trong

M

S Ngoi

M

Tin Trong

M

Tin Ngoi

M

Thnh

Tin

01 Nguyn Vn Thnh NN-A 44 285

02 L Th Dung NN-B 97 254

03 Trn Vn ang CB-C 28 202

04 Phan nh Phng CB-B 67 202

05 H Th Cn CB-A 50 231

06 Lu Vn Lang ND-D 59 300

07 Cao Nguyt Qu ND-C 10 283

08 Dng Minh Chu ND-A 51 291

09 o Cm T KD-D 25 291

10 Ng Cng Bng KD-C 98 249

11 L Phng Nam KD-A 12 279

12 Trn H Trung SX-A 60 212

BNG N GI IN

BNG NH MC (Kwh)

A B C D

NN CB ND KD SX

NN 250 200 175 150

A 150 100 80 80 120

CB 350 300 275 250

B 160 120 120 90 140

ND 450 400 375 350

C 180 150 150 100 180

KD 650 600 575 550

D 200 175 175 120 220

SX 550 500 475 450

BNG THNG K

Loi phng Doanh thu

L1A

L2A

L1B

L2B



M t: M H: Trong 2 k t u ch loi h, k t cui ch khu vc ng k.

Yu cu:

1. S trong nh mc = S mi - S c, nu (s mi - S c) < nh mc, ngc li th ly nh mc tra trong bng nh mc.

2. Nu khng vt nh mc S NGOI NH MC =0, ngc li S NGOI NH MC = (S MI -S C) - NH MC.

3. Tin Trong nh Mc = S Trong nh Mc * n Gi. Gi Tra trong bng n Gi (Hm INDEX & MATCH).

4. Tin Ngoi nh Mc = S Ngoi nh Mc*n Gi *1.5 . Gi Tra trong bng n Gi (Hm INDEX & MATCH).

5. Thnh Tin = Tin Trong nh Mc + Tin Ngoi nh Mc. 6. Trch rt sang khc nhng h tha iu kin : H Tiu Th Vt nh mc.

(Dng Chc Nng AutoFilter). 7. Trch rt sang khc nhng h tha iu kin : H Kinh Doanh Tiu Th Vt

nh mc.(Advanced Filter). 8. Trch rt d liu sang khc nhng h Hoc l NN hoc l CB. 9. Tnh ton bng thng k sau hm SumIf(), CountIf().

Bng Thng K Theo Loi H

M S Loi H S Lng Tng Thnh Tin

NN NH NC

CB CN B

ND NHN DN

KD KINH DOANH

SX SN XUT

10. V biu hnh ct (column) minh ha bng thng k trn. 11. Tnh ton bng thng k sau bng hm SUMIF(), COUNTIF().

Bng Thng K Theo Khu Vc

M S Loi H S Lng Tng Thnh Tin

A VNG SU

B NGOI THNH

C NI THNH

D U TIN

12. Dng chc nng Advanced Filter, hy trch rt danh sch mu tin tha tng trng hp sau.

a. Nhng h c S C=250 b. Nhng h l nh nc khu vc A, hoc Cn b khu vc B, Hoc ND

khu vc C. 13. nh dng ct thnh tin c du phn cch hng nghn, VN, k khung t mu cho

bng tnh.



Bi 2: Trnh by bng tnh Qun l khch sn sau.

QUN L KHCH SN

STT Tn Khch S

Phng Ngy n Ngy i

Tin Thu

Tin Gim

Tin Phi Tr (USD)

1 Trung A01 01/01/2011 10/01/2011

2 Tng B01 15/01/2011 16/01/2011

3 Giang A02 20/01/2010 29/01/2010

4 Hu C02 15/01/2010 20/01/2010

5 Phng A02 25/01/2010 30/01/2010

6 Dng B02 15/01/2010 30/01/2010

Bng thng k Bng Gi Phng (USD)

M t: K t u tin ca S Phng cho bit Loi phng.

Yu cu:

1. Da vo k t u ca S phng v bng gi tnh ct Tin thu, bit rng Tin thu = S ngy * gi.

2. Tin gim, nu khch vo ngy 15 c gim gi trong ngy hm 30%. 3. Tin phi tr = Tin thu - Tin gim. 4. Tnh tng s ngi trong ngy 15. 5. Tnh tng s tin thu c t u thng n ngy 15.

Bi 3: Trnh by bng tnh theo mu sau.

BO CO TNH HNH NP THU THNG 10/2011 S

KKD Tn

ch M

hng

Tn

hng

Vn kinh doanh

Tin thu

Ngy ht hn np

Ngy np thc t

Tin pht

Thu phi np

001/ML VPP 10.000.000 01/10/2011 01/10/2011

001/BK QA 12.000.000 15/10/2011 19/10/2011

002/B TP 5.000.000 13/10/2011 15/10/2011

004/MX MP 15.000.000 20/10/2011 25/10/2011

004/MB GK 18.000.000 20/10/2011 29/10/2011

002/ML QA 90.000.000 18/10/2011 17/10/2011

002/MB GK 15.000.000 02/10/2011 09/10/2011

003/B MP 19.000.000 10/10/2011 10/10/2011

002/BK VPP 48.000.000 12/10/2011 15/10/2011

003/MX TP 7.000.000 25/10/2011 26/10/2011

Tng s ngi trong ngy 15

Loi A B C

S tin thu c t ngy 01/01/2010 n 01/15/2010

Gi Thu 1 Ngy 300 100 70



Bng 1 Bng 2 Bng thng k

M

ch Tn ch

M

hng Tn hng

Thu sut

Ch Tng thu

ML M Long

TP Thc phm 5%

M Long

MX M Xuyn

GK Nc gii kht 3%

M Xuyn

MB M Bnh

MP M phm 10%

M Bnh

B Bnh c

VPP Vn phng phm 9%

Bnh c

BK Bnh Khnh

QA Qun o 8%

Bnh Khnh

Yu cu:

1. Xc nh ct Tn ch cn c vo 2 k t cui ca s KKD vi gi tr c th cho trong Bng 1.

2. Xc nh ct Tn hng cn c vo m hng v tra trong Bng 2. 3. Tnh ct Tin thu = Vn kinh doanh * Thu sut. Trong Thu sut c xc

nh cn c vo M hng v tra trong Bng 2. 4. Tnh ct Tin pht bit rng nu Ngy np thc t sau Ngy ht hn np th mi

ngy tr hn phi chu pht 0,5% trn s tin thu, ngc li Tin pht = 0. 5. Tnh ct Thu phi np = Tin thu + Tin pht. 6. Xc nh s thu thu c ca tng ch vo Bng thng k. 7. nh dng bng tnh cho theo mu. nh dng cc cha gi tr s c du phn

cch hng nghn & VN. 8. Thit lp iu kin rt trch ra cc h kinh doanh np thu tr hn. 9. Dng cng c AutoFilter lc ra cc h kinh doanh mt hng Qun o hoc Vn

phng phm ch M Long. 10. V biu hnh bnh (pie) cho bng thng k, hin th % tng thu ca cc ch v

t tn Biu thng k tin thu cc ch.

Baitap Excel

Documents

Transcript of Baitap Excel