Btexcel

Baøi taäp 1 1. Ñoùng taát caû caùc workbook ñaõ môû. Taïo workbook môùi, ñoåi teân cho Sheet1 laø LuongT1-2000, nhaäp döõ lieäu vaø trình baøy theo maãu sau: A

B

C

D

E

F

1

BAÛNG LÖÔNG NHAÂN VIEÂN

2

Thaùng 01/2000

3 4 5 6 7 8 9

Stt 1 2 3 4 5 6

10

Hoï vaø teân Hoaøng Minh Leâ Minh Hoàng Nguyeãn Quoác Taâm Leâ Mai Oanh Traàn Kim Chi Phaïm Minh Hieån Toång coäng

Chöùc vuï TP NV PP NV KT PP

LCB 15000 20000 19000 16000 24000 17000

Ngaøy Löông 25 24 26 20 23 27

G

H

Taïm öùng

Coøn laïi

Yeâu caàu tính: 1. Löông = Löông cô baûn (LCB) nhaân vôùi Ngaøy coâng (Ngaøy) 2. Taïm öùng = hai phaàn ba Löông 3. Coøn laïi = Löông tröø cho Taïm öùng 4. Tính toång coäng theo coät Löông, Taïm öùng, Coøn laïi 2. Choïn Sheet2, ñaët teân cho Sheet2 laø Hang nhap T9-2000, nhaäp döõ lieäu vaø trình baøy theo maãu sau: A

B

C

D

E

F

G

H

Thueá

CöôùcCC

Coäng

1

BAÛNG KEÂ HAØNG NHAÄP KHO

2

Thaùng 09/2000 - Kho Long Bình

3 4 5 6 7 8 9 10

C.Tu B50 B51 B52 B53 B54 B55

Dieãn giaûi Baøn goã Baøn saét Baøn Formica Gheá döïa Gheá xeáp Tuû goã Toång coäng

Soá löôïng Ñôn giaù Trò giaù 40 25 46 30 72 20 120 8 38 12 15 40

Yeâu caàu tính: 1. Trò giaù = Soá löôïng nhaân Ñôn giaù 2. Thueá = trò giaù nhaân vôùi 4.25% 3. Cöôùc chuyeân chôû (CuocCC) = Soá löôïng nhaân 1.12 4. Coäng = Trò giaù + Thueá + Cöôùc chuyeân chôû 5. Toång caùc coät (Trò giaù, Thueá, CöôùcCC, Coäng) 6. Löu laïi WorkBoook vôùi teân BT1 Trang 1

Baøi taäp 2 Taïo Workbool môùi, ñaët teân cho Sheet1 laø BIEN BAN THONG KE vaø nhaäp döõ lieäu vaøo nhöõng oâ thích hôïp ñeå taïo thaønh baûng tính coù daïng sau: A

B

C

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Tyû leä cöôùc chuyeân chôû Stt Loâ haøng ÑGiaù Ñieän töû 1 Tuû laïnh 240 2 Ñaàu Video 140 3 Cassette 50 4 Radio 30 5 PhotoCopy 280 Vi tính 6 Mainboard 350 7 Ñóa cöùng 220 8 Ram 120 9 Baøn phím 15 10 Mouse 5 Toång coäng

D

E

F

G

H

I

BIEÂN BAÛN THOÁNG KEÂ Quyù 01/2000 Thaùng 2/2000 2.50% Thaùng 3/2000 2.25% SLg Trò giaù CChôû SLg Trò giaù CChôû (5) (6) (7) (8) 12 (1) (2) 15 (3) (4) 10 24 15 10 20 24 10 15 (9) (10) (11) (12) 10 10 25 30 20 15 50 50 100 50 (13) (14) (15) (16)

J

Phí phaân boå (17)

(18) 80 000

Yeâu caàu: 1. Trò giaù taïi vò trí (1) vaø (3) = Ñgiaù * SLg (Ñgiaù ñöôïc duøng chung cho 2 thaùng). 2. Chuyeân chôû taïi (2) vaø (4) = Trò giaù * Tyû leä cöôùc chuyeân chôû tính theo töøng thaùng.

(Tyû leä cöôùc chuyeân chôû töøng thaùng naèm trong caùc oâ ôû phía treân caùc oâ CChôû). Tính vaø laøm troøn ñeán haøng ñôn vò, coù söû duïng ñòa chæ tuyeät ñoái, hoãn hôïp. 3. Thöïc hieän thao taùc coäng cho töøng nhoùm loâ haøng theo caùc coät Trò giaù, Chuyeân chôû. Coäng trò giaù vaø CChôû cho caùc nhoùm: loâ haøng Ñieän töû taïi caùc vò trí 5, 6, 7, 8; loâ haøng Vi tính taïi vò trí 9,10,11,12. 4. Toång coäng cho 2 nhoùm loâ haøng theo caùc coät Trò giaù, CChôû taïi (13), (14), (15) vôùi (13) = (5) + (9) 5. Phí phaân boå taïi (17) cho caùc maët haøng 80 000 (laø toång Phí phaân boå) chia cho toång Toång Trò giaù vaø Cöôùc CChôû trong caû 2 thaùng cuûa taát caû caùc maët haøng Ñieän töû vaø Vi tính (ñeå ñöôïc 1 ñôn vò phí chung) vaø nhaân cho toång Trò giaù vaø CChôû trong caû 2 thaùng cuûa töøng maët haøng. Höôùng daãn: Phí phaân boå taïi (17) = 80 000 / Toång (13 + 14 + 15 + 16) * Toång (1 + 2 + 3 + 4). Löu yù: coá ñònh ñòa chæ oâ cuûa nhöõng oâ coù yù nghóa toång coäng. Rieâng Phí phaân boå cuûa maët haøng Mouse seõ tính xeâ xích theo kieåu "bao choùt" sao cho troøn soá tieàn 80000. Nghóa laø Phí phaân boå cuûa Mouse taïi (18) = 80 000 - toång caùc Phí phaân boå cuûa caùc maët haøng ñaõ ñöôïc tính ôû phía treân. 6. Löu WorkBook vôùi teân BT2.

Trang 2

Baøi taäp 3 Môû file MauBT3.xls theo chæ daãn cuûa giaùo vieân höôùng daãn vaø löu laïi vôùi teân laø a:\BT3.xls (duøng [menu] File/ Save As). Ñaët teân cho sheet 1 laø Ket qua thi. Döõ lieäu cuûa Sheet coù daïng: KEÁT QUAÛ THI CUOÁI KHOÙA Stt

TenHV Phaùi Hieäp Nu Chi Nu Lan Nu Ñöùc Nam Laân Nam Ngoïc Nu Thaûo Nu Dieãm Nu Nhi Nu Toaøn Nam Thö Nu Taân Nam Duõng Nam Trung bình Cao nhaát Thaáp nhaát

NS 70 74 68 72 68 74 71 74 69 73 74 66 80

Toaùn 9 4.5 8 4 10 4 3 7.5 4 3 6 10 9

Vaên 8 6 8.5 6 8 4.5 9 5 5 5 6 4 5

SNgöõ 7 4 9 7 9 6 7 6 5.5 5 8 7 6

ÑTheâm

ÑTN

ÑTB

ÑKquaû

Yeâu caàu: 1. Nhaäp soá thöù töï cho coät Stt. 2. Theâm vaøo beân phaûi coät Ñieåm Keát quaû (Ñkquaû) 2 coät Xeáp loaïi vaø Xeáp haïng. 3. Ñieåm ñöôïc theâm (ÑTheâm): - Hoïc vieân töø naêm 72 veà sau coù ñieåm theâm laø 0.75. - Hoïc vieân tröôùc naêm 72 nhöng laø phaùo nöõ coù ñieåm theâm laø 0.5. - Vaø nhöõng tröôøng hôïp coøn laïi coù ñieåm theâm laø 0. 4. Ñieåm thaáp nhaát (ÑTN) laø ñieåm thaáp nhaát trong 3 ñieåm cuûa töøng hoïc vieân. 5. Ñieåm trung bình (ÑTB):ñöôïc tính vôùi ñieåm Toaùn heä soá 3, Vaên heä soá 2, Sinh ngöõ heä soá 2 vaø laøm troøn 2 soá leû. 6. Ñieåm keát quaû (ÑKquaû) : nhöõng hoïc vieân naøo coù ÑTB>=5 ÑKquaû chính laø ÑTB; ngöôïc laïi ÑKquaû chính laø ÑTB coäng vôùi Ñtheâm, nhöng neáu sau khi coäng theâm maø lôùn hôn 5 thì chæ ñöôïc laáy 5 maø thoâi. 7. Xeáp loaïi döïa vaøo caùc tieâu chuaån sau: Gioûi neáu ÑKquaû >=8.0 vaø khoâng coù moân naøo döôùi 6.5 Khaù neáu ÑKquaû >=6.5 vaø khoâng coù moân naøo döôùi 5.5 TB neáu ÑKquaû >=5.0 vaø khoâng coù moân naøo döôùi 3.5 Yeáu neáu ÑKquaû >=3.5 vaø khoâng coù moân naøo döôùi 2.0 Keùm coøn laïi 8. Trung bình, Cao nhaát, Thaáp nhaát theo coät cho töøng moân, ÑTB, ÑKquaû. 9. Xeáp haïng theo ñieåm keát quaû. 10. Trình baøy baûng tính laïi cho deã nhìn vaø hôïp lyù. 11. Löu workbook laïi tröôùc khi thoaùt khoûi Excel.

Trang 3

Baøi taäp 4 Môû taäp tin MauBT4 theo chæ daãn cuûa giaùo vieân höôùng daãn thöïc haønh. Löu laïi workbook môùi môû vôùi teân BT4 trong oå ñóa A: (duøng [menu] File/ Save As) ñaët cho Sheet chöùa döõ lieäu laø Luong T2-2000

BAÛNG LÖÔNG NHAÂN VIEÂN

Stt

Hoï vaø teân Chöùc vuï Nguyeãn An Ninh TP Leâ Chöùc Vuï NV Traàn Thaønh Phoá PP Leâ Tieáu Laâm NV Nguyeãn Vaên Ñua KT Traàn Anh Taøi BV Löu Anh Huøng PP Traàn Trai GD Traàn Anh Huøng BV Löu Vaên Theå NV Traàn Phöôùc Thö TX Leâ Minh Nhaät NV Ñoaøn Anh Duõng PG Leâ Hoaøng Anh TP Trònh Minh Ngoïc BV Toång coäng Bình quaân Cao nhaát Thaáp nhaát

Thaùng 02/2000 LCB Ngaøy Pccv 1200 25 1600 24 1500 26 1300 20 1800 23 1500 24 1400 27 1600 14 1500 17 1300 23 1800 22 1500 14 1800 23 1600 27 1300 26

Löông

Taïm öùng

Coøn laïi

Yeâu caàu: 1. Nhaäp soá thöù töï cho coät Stt 2. Tính Pccv (phuï caáp chöùc vuï) theo yeâu caàu sau: - Neáu chöùc vuï laø "GD" (giaùm ñoác) thì phuï caáp chöùc vuï laø 5000. - Neáu chöùc vuï laø "PG" (phoù giaùm ñoác) hoaëc "TP" (tröôûng phoøng) thì phuï caáp chöùc vuï laø 4000. - Neáu chöùc vuï laø "PP" (phoù phoøng) hoaëc "KT" (keá toaùn) thì phuï caáp chöùc vuï laø 3000. - Neáu chöùc vuï laø "BV" (baûo veä) vaø coù ngaøy coâng >=22 ngaøy thì phuï caáp chöùc vuï laø 1500. - Coøn laïi khoâng coù phuï caáp chöùc vuï. 3. Löông ñöôïc tính baèng Löông caên baûn nhaân vôùi ngaøy coâng, nhöng neáu soá ngaøy coâng laøm vieäc treân 24 ngaøy thì moãi ngaøy laøm vöôït giôø treân 24 ngaøy ñoù ñöôïc tính gaáp ñoâi. 4. Taïm öùng tính baèng 2 phaàn 3 cuûa toång soá (löông coäng vôùi phuï caáp chöùc vuï); tính troøn ñeán haøng ngaøn nhöng taïm öùng khoâng ñöôïc quaù 25000. 5. Coøn laïi = (Phuï caáp chöùc vuï + Löông) - Taïm öùng 6. Tính Toång coäng, Bình quaân, Cao nhaát, Thaáp nhaát theo caùc coät Ngaøy, PCCV, Löông. 7. Trình baøy baûng tính thaät ñeïp theo yù thích cuûa baïn. 8. Löu workbook laïi.

Trang 4

Baøi taäp 5 Taïo workbook môùi, ñaët teân cho Sheet1 vôùi teân CHI PHI THUE KS vaø laäp baûng tính theo maãu sau: Löu yù: Tröôùc tieân neân quy ñònh thöù töï nhaäp ngaøy, thaùng, naêm: Choïn Start/ Settings/ Control Panel/ Regional Settings, choïn tab Date, treân doøng Short date style: goõ töø baøn phím: dd/mm/yy. BAÛNG KEÂ CHI PHÍ THUEÂ KHAÙCH SAÏN

Hoï vaø teân Lyù Ñaøo Vuõ Nguyeãn Traàn Phaïm Traàn Hoàng Döông Voõ

Sôn Huøng Thanh Lan Leä Vuõ Quyù Höông Anh Trung

Maõ phoøng

Ngaøy ñeán

Ngaøy ñi

T2C T3B T4A T3A T4A T2B T4C T2C T4B T1D

10/05/98 23/05/98 12/05/98 26/05/98 23/05/98 13/05/98 24/05/98 24/05/98 26/05/98 28/05/98

23/05/98 11/06/98 19/06/98 07/06/98 06/06/98 27/05/98 06/06/98 16/06/98 18/06/98 18/06/98

Thaønh tieàn

Loaïi phoøng A B C D

55 50 45 42

Ngaøy

Stt

Tuaàn

Bieåu giaù

9 8 8 7

Yeâu caàu: Giaûi thích tröôùc: - Bieåu giaù phoøng cho tröôùc coù yù nghóa nhö sau: neáu phoøng cho thueâ coù loaïi laø A thì Ñôn giaùo thueâ cho 1 tuaàn laø $55 vaø cho moät ngaøy laø $9, … - Maõ phoøng coù 3 kyù töï: 2 kyù töï ñaàu beân traùi laø soá taàng, kyù töï beân phaûi laø soá loaïi phoøng. 1. Cheøn vaøo giöõa 2 coät Ngaøy ñi vaø Thaønh tieàn 4 coät: Soá tuaàn, Soá ngaøy leû, ÑgTuaàn, ÑgNgaøy. 2. Tính Soá Tuaàn, Soá ngaøy leû (duøng haøm INT, MOD) 3. Tính ÑgTuaàn, ÑgNgaøy döïa vaøo loaïi phoøng vaø Baûng bieåu giaù. 4. Haõy tính Thaønh tieàn = Tieàn tuaàn + Tieàn ngaøy leû. Bieát * Tieàn tuaàn = Soá tuaàn * Ñôn giaù tuaàn. * Tieàn ngaøy leû = soá nhoû hôn giöõa (Ñôn giaù ngaøy * Soá ngaøy leû) vôùi (Ñôn giaù tuaàn) Giaûi thích tính Tieàn ngaøy leû: Ñôn giaù tuaàn ñöôïc tính khi ñaõ ôû ñöôïc 7 ngaøy. Trong moät soá tröôøng hôïp ñôn giaù ngaøy leû khaù cao neân coù theå chöa ôû ñuû 7 ngaøy maø soá tieàn Ñôn giaù ngaøy * Soá ngaøy leû ñaõ lôùn hôn tieàn ôû moät tuaàn. Vì theá luoân tính giaù thaáp hôn ñeå laøm haøi loøng khaùch haøng. 5. Trang trí baûng tính nhö maãu. 6. Löu workbook vôùi teân BT5.xls

Trang 5

Baøi taäp 6 Taïo workbook môùi. Taïo baûng tính KET TOAN treân Sheet 1 coù daïng sau: BAÛNG ÑÔN GIAÙ VAØ LOAÏI HAØNG MAÕ HAØNG TEÂN HAØNG ÑÔN GIAÙ ÑOÄC HAÏI DUMEX Söõa boät 25000 555 Thuoác laù 10000 X SGD Ñöôøng 4500 WALKER Röôïu 15000 X AJINO Boät ngoït 20000

STT 1 2 3 4 5 6 7 8

MAÕ HAØNG DUMEX 555 DUMEX SGD SGD WALKER AJINO DUMEX

NGAØY NHAÄP 02/10/99 04/10/99 04/10/99 19/10/99 21/10/99 22/10/99 22/10/99 24/10/99

KEÁT TOAÙN HAØNG NHAÄP THAÙNG 10/99 TEÂN SL ÑÔN GIAÙ THAØNH HAØNG TIEÀN 50 100 125 47 80 90 120 48

THUEÁ ÑOÄC HAÏI

TOÅNG COÄNG

Yeâu caàu: 1. Laäp coâng thöùc ñieàn döõ lieäuvaøo coät Teân Haøng, Ñôn Giaù döïa vaøo Maõ haøng vaø Baûng ñôn giaù vaø loaïi haøng. 2. Tính Thaønh Tieàn = Soá löôïng * Ñôn giaù. 3. Tính Thueá ñoäc haïi = 5% cuûa Thaønh tieàn. Löu yù: chæ tính thueá ñoäc haïi ñoái vôùi nhöõng maët haøng coù ñaùnh daáu laø ñoäc haïi ôû coät ñoäc haïi trong Baûng ñôn giaù vaø loaïi haøng. 4. Tính Toång coäng = Thaønh tieàn + Thueá ñoäc haïi. 5. Saép xeáp baûng tính theo thöù töï taêng daàn cuûa coät Toång coäng. 6. Löu workbook vôùi teân BT6.xls.

Trang 6

Baøi taäp 6b Taïo baûng tính THUE KHO treân Sheet 2 trong file BT6.xls theo maãu sau: Maõ haøng B D G K

Maõ haøng B01 G02 D01 K02 G01 K01

BAÛNG GIAÙ Teân haøng Giaù loaïi 1 Baép 200 Ñaäu 100 Gaïo 150 Khoai 120

Teân haøng

Giaù loaïi 2 180 80 140 100

BAÛNG TÍNH TIEÀN THUEÁ THUEÂ KHO Soá Ngaøy Loaïi Giaù Ngaøy Traû Löôïn Thueâ g 28 30/08/99 05/10/99 20 30/08/99 02/10/99 28 02/09/99 02/10/99 15 01/09/99 10/10/99 30 02/09/99 13/10/99 20 04/09/99 14/10/99 Toång coäng

Soá ngaøy Thueâ

Tieàn Thueâ

Yeâu caàu: 1. Tính Soá ngaøy thueâ = Ngaøy traû – Ngaøy thueâ. 2. Tính Loïai laø kyù töï cuoái cuûa maõ haøng vaø chuyeån qua giaù trò soá. 3. Tính coät Teân haøng döïa theo Maõ haøng vaø Baûng giaù. 4. Tính coät Giaù döïa theo Maõ haøng, Baûng giaù vaø Loaïi. 5. Tieàn Thueâ = Soá löôïng * Giaù * Soá ngaøy thueâ; nhöng soá löôïng lôùn hôn 20 thì seõ giaûm 10% Tieàn thueâ. 6. Tính Toång coäng coät Soá ngaøy thueâ vaø Tieàn thueâ.

Trang 7

Baøi taäp 7 Môû file MauBT7. Löu laïi file vôùi teân BT7.xls treân A:\. Ñoåi teân Sheet 1 laø CUOC PHI VAN TAI. Sheet döõ lieäu coù daïng: BAÛNG ÑÔN GIAÙ VAØ THÔØI GIAN QUY ÑÒNH Nôi ñeán Ñôn giaù Thôøi gian Ha Noi 10000 5 Da Nang 5000 7 Quy Nhon 4000 2 Playku 6000 4 Lao 25000 8 Campuchia 20000 6 Nha Trang 3000 1

Soá xe 50-2993 52-1234 50-9553 50-2923 51-1111 52-2222 52-1234 51-1111 50-4455 52-2929 51-1111

SL 5 10 2 3 6 5 10 5 3 2 4

BAÛNG QUY ÑÒNH TROÏNG TAÛI Soá xe Troïng taûi (2 kyù töï ñaàu) 50 4 51 8 52 12

BAÛNG TÍNH CÖÔÙC PHÍ VAÄN TAÛI Nôi ñeán Ñôn giaù Cöôùc phí Ngaøy ñi Playku 01/05/99 Quy Nhon 03/05/99 Nha Trang 03/05/98 Ha Noi 01/05/98 Nha Trang 06/05/98 Ha Noi 10/05/98 Lao 20/05/98 Lao 21/05/98 Ha Noi 25/05/98 Campuchia 22/05/98 Da Nang 10/05/98

Ngaøy ñeán 03/05/99 04/05/99 05/05/98 07/05/98 06/05/98 16/05/98 26/05/98 27/05/98 31/05/98 26/05/98 12/05/98

T.gianth

Yeâu caàu: 1. Quy ñònh thöù töï ngaøy, thaùng, naêm cuûa heä thoáng theo daïng: dd/mm/yy. 2. Theâm coät Thöôûng vaøo beân phaûi coät Thôøi gian thöïc hieän (T.gianth). 3. Tính Ñôn giaù: laäp coâng thöùc laáy Ñôn giaù töø BAÛNG ÑÔN GIAÙ VAØ THÔØI GIAN QUY ÑÒNH. (DUØNG HAØM Vlookup doø tìm theo coät vaø giaù trò doø tìm laø Nôi ñeán). 4. Tính Cöôùc phí : Neáu Soá löôïng (SL) chôû ít hôn troïn taûi xe (cho trong BAÛNG QUY ÑÒNH TROÏNG TAÛI) Cöôùc phí = Ñôn giaù * SL (coù nghóa laø tính nguyeân giaù), ngöôïc laïi Cöôùc phí = Ñôn giaù * SL * 105% (nghóa laø tính theâm 5%). Höôùng daãn: Muoán bieát ñöôïc troïng taûi cuûa xe ñang tínhbaïn phaûi döïa vaøo 2 kyù töï ñaàu cuûa soá xe vaø baûng quy ñònh troïng taûi. Do ñoù caàn söû duïng haøm doø tìm theo coät, vôùi giaù trò doø tìm laø 2 kyù töï beân traùi cuûa Soá xe. 5. Tính Thôøi gian thöïc hieän (T.gianth) = Ngaøy ñeán – Ngaøy ñi, nhöng tröôøng hôïp ñeán vaø ñi trong cuøng mt ngaøy (Ngaøy ñi = Ngaøy ñeán) thì thôøi gian thöïc hieän ñöôïc tính laø 1 ngaøy. 6. Tính Thöôûng: neáu thôøi gian thöïc hieän ít hôn thôøi gian quy ñònh (cho trong Baûng ñôn giaù vaø thôøi gian quy ñònh) thì thöôûng laø 5% cöôùc phí, ngöôïc laïi seõ tính thöôûng laø 0. 7. Trang trí baûng tính vaø löu vaøo ñóa vôùi teân BT7.xls

Trang 8

Baøi taäp 7b Môû laïi file BT7b.xls, trong ñoù Sheet 1 coù teân CUOC PHI VAN TAI, choïn Sheet 2 vaø ñaët teân laø TINH TIEN DIEN. Sheet naøy chöùa caùc döõ lieäu sau: BAÛNG ÑÔN GIAÙ BAÛNG HEÄ SOÁ Khu vöïc Soá laàn Heä soá Loaïi 0 1.5 A B C D E Loïai 1 500 460 450 420 410 1 2 Loïai 2 450 440 430 410 400 2 3

Stt

SoáÑK A001 D002 B001 C002 E001 C002 E002 B002 D001 A001

Ñònh möùc 180 160 200 160 180 220 210 180 200 180

BAÛNG TÍNH TIEÀN ÑIEÄN Soá cuõ Soá môùi Ñôn giaù Tieâu thuï 2500 2300 5600 2500 3200 4400 2800 4500 2400 1880

TrÑM

VöôïtÑ M

Toång

2900 2450 5700 2720 3450 4590 3000 4980 2600 2200

Yeâu caàu: 1. Nhaäp soá thöù töï cho coät Stt 2. Tính soá Kw tieâu thuï (Tieâu thuï) = Soá môùi – Soá cuõ 3. Tính Ñôn giaù döïa vaøo Soá ñieän keá (Soá ÑK) vaø Baûng ñôn giaù (Trong SoáÑK: kyù töï ñaàu beân traùi bieåu thò cho Khi vöïc, kyù töï beân phaûi cho bieát Loïai ñaõ ñaêng kyù). HD: Duøng haøm HLOOKUP ñeå doø tìm theo haøng. 4. Tính Tieàn Trong ñònh möùc (Tr.ÑM) = Soá Kw cuûa phaàn tieâu thuï trong ñònh möùc nhaân vôùi Ñôn giaù. 5. Tính Tieàn Vöôït ñònh möùc (VöôïtÑM) = Soá Kw teâu thuï vöôït ñònh möùc * Ñôn giaù * Heä soá vöôït ñònh möùc. + Bieát raèng soá laàn vöôït ñònh möùc = (Tieâu thuï – Ñònh möùc) / Ñònh möùc. + Heä soá vöôït ñònh möùc ñöôïc tính döïa vaøo soá laàn vöôït ñònh möùc: neáu soá laàn <1 thì Heä soá laø 1.5, soá laàn<2 thì heä soá laø 2, ngöôïc laïi >= 2 heä soá laø 3. Tuy nhieân Heä soá coù theå ñöôïc tính döïa vaøo baûng heä soá. 6. Tính Toång = Trong ñònh möùc + Vöôït ñònh möùc. 7. Haõy trình baøy baûng tính thaät ñeïp maét. 8. Löu workbook laïi 9. Saép xeáp baûng tính treân sao cho döõ lieäu treân coät SoáÑK coù thöù töï taêng daàn. 10. Taïo vuøng döõ lieäu chæ goàm nhöõng maåu tin coù kyù töï beân phaûi cuûa SoáÑK laø “1” (Advanced Filter)

Trang 9

Baøi taäp 8 Môû file MauBT8. Löu laïi file vôùi teân BT8.xls treân A:\. Ñaët teân Sheet 1 laø ThueNK. Sheet döõ lieäu coù daïng: BAÛNG TYÛ LEÄ BAÛNG TYÛ GIAÙ SO VÔÙI USD Loaïi Tyû leä MNT Tyû giaù A 5% FF 5.6 B 10% JPY 132 C 20% USD 1 D 50% RUP 200 HKD 20

Maõ soá A001 B001 B002 A002 C001 D001 C002 D002 A003 D003

Teân haøng Thuoác nhuoäm Vaät tö aûnh Maùy Minilab Sôïi Döôïc lieäu Cassette Lk ñieän töû Nissan car Maùy deät Moâ toâ 125

BAÙO CAÙO THUEÁ NHAÄP KHAÅU Soá löôïng Ñôn giaù MNT Ngaøy 10 30 USD 12/10/98 15 100 JPY 12/10/98 4 10000 JPY 16/10/92 120 20000 RUP 13/10/92 10 10000 FF 10/10/92 10 3000 JPY 07/10/92 15 4000 HKD 08/10/92 4 30000 JPY 18/10/92 2 30000 USD 11/10/92 5 10000 JPY 10/10/92

TTVN

Thueá

Yeâu caàu: 1. Quy ñònh thöù töï ngaøy, thaùng, naêm cuûa heä thoáng theo daïng: dd/mm/yy. 2. Cheøn theâm 2 coät Thaønh tieàn, Quy ra USD tröôùc coät TTVN. 3. Tính Thaønh tieàn = Soá löôïng * Ñôn giaù 4. Tính Quy ra USD = Thaønh tieàn / Tyû giaù. HD: Tyû giaù ñöôïc tính döïa vaøo MNT (maõ nguyeân teä) vaø Baûng tyù giaù so vôùi USD ôû treân. 5. Tính TTVN (thaønh tieàn VN) = Quy ra USD * Tyû giaù USD so vôùi VND Tyû giaù USD so vôùi VND ñöôïc tính döïa vaøo Ngaøy (ngaøy nhaäp khaåu) - Neáu ngaøy nhaäp tröôùc 15/10/98 thì tyû giaù laø 14370 - Neáu ngaøy nhaäp sau 15/10/98 thì tyû giaù laø 13960 6. Tính Thueá = Tyû leä theo loaïi haøng * Thaønh tieàn VN. HD: Tyû leä theo loaïi haøng ñöôïc tính döïa vaøo kyù töï beân traùi cuûa Maõ soá vaø Baûng tyû leä. 7. Söû duïng chöùc naêng Advanced Filter, laàn löôït ttaïo caùc vuøng döõ lieäu chæ goàm nhöõng maåu tin coù: a. Kyù töï beân traùi cuûa Maõ soá laø A vaø Maõ nguyeân teä laø USD. b. Maõ nguyeân teä laø JPY vaø kyù töï beân phaûi cuûa Maõ soá laø 2. c. Kyù töï beân traùi cuûa Maõ soá laø B hoaëc Maõ nguyeân teä laø FF.

Trang 10

Baøi taäp 9 Nhaäp vaø trình baøy baûng tính sau: BAÛNG LÖÔNG THAÙNG 10/99 Teân Nam Thu Hoa Ñoâng Thanh Quang Ñoan Trung Thaûo Khanh

Maõ loaïi A B C D

Maõ soá A4 C2 D1 B4 B7 C5 B3 B5 A6 D2

Lcb 600 480 390 520 520 480 520 520 600 390

Nc

Maõ loaïi

SNCT

Heä soá

Löông

Phuï caáp

Thöïc laõnh

23 25 28 24 23 25 25 25 22 28

BAÛNG TÍNH HEÄ SOÁ Soá naêm coâng taùc 1->3 4->6 7->8 10 12 15 9 11 13 9 10 12 8 9 11

Löu yù: + Maõ loaïi laø kyù töï beân traùi cuûa Maõ soá. + Töông öùng vôùi moãi Maõ loaïi vaø soá naêm coâng taùc treân baûng Heä soá vaø heä soá löông töông öùng.

Yeâu caàu: 1. Laäp coâng thöùc ñieàn döõ lieäu vaøo coät Maõ loaïi: laø kyù töï beân traùi cuûa maõ soá 2. Laäp coâng thöùc tính Soá naêm coâng taùc (SNCT): laø kyù töï cuoái cuûa Maõ soá vaø noù phaûi laø trò soá. 3. Tính Heä soá döïa vaøo Maõ soá (hoaëc Maõ loaïi), SNCT vaø Baûng tính heä soá (duøng haøm Vlookup). 4. Tính Löông = Lcb * Heä soá * Nc, nhöng neáu Nc>24 thì phaàn ngaøy coâng vöôït treân 24 ñöôïc tính gaáp ñoâi. 5. Tính Phuï caáp: moãi naêm coâng taùc ñöôïc tính phuï caáp laø 10000 vaø neáu Maõ loaïi laø A thì phuï caáp ñöôïc coäng theâm 6000. 6. Tính Thöïc laõnh = Löông + Phuï caáp nhöng Thöïc laõnh naøy khoâng ñöôïc thaáp hôn 140000. 7. Taïo vuøng döõ lieäu goàm caùc maåu tin coù Maõ loaïi laø B vaø Ngaøy coâng töø 24 trôû leân. 8. Löu baûng tính vôùi teân BT9.xls

Trang 11

Baøi taäp 10 Taïo baûng tính sau vaø löu vaøo ñóa vôùi teân BT10.xls Baûng 1 -BAÛNG ÑIEÅM CHUAÅN Baûng 2 - BAÛNG ÑIEÅM HOÏC BOÅNG Maõ Ñieåm Ñieåm Ngaønh thi Maõ ngaønh A B C ngaønh chuaån 1 chuaån 2 A Maùy tính 20 22 Ñieåm hoïc boång 26 24 21 B Ñieän töû 18 20 C Xaây döïng 15 16 D Hoùa 13 14

Maõ soá A101 B102 C203 D204 A205 C106 D107 A208

Hoï Leâ Kieàu Lyù Phaïm Nguyeãn Traàn Leä Laâm

Teân Trung Leä Thanh Uyeân Tuøng Huøng Hoa Sôn

KEÁT QUAÛ TUYEÅN SINH Ngaønh thi Khu vöïc Toaùn

Lyù 4 5 4 2 6 8 9 9

Hoùa 7 6 3 4 7 6 7 9

Keát quaû 5 6 1 2 9 7 8 9

Yeâu caàu: 1. Cheøn vaøo tröôùc coät keát quaû 2 coät: Toång coäng, Ñieåm chuaån. 2. Saép xeáp baûng tính Keát quaû tuyeån sinh theo Teân vôùi thöù töï taêng daàn. 3. Laäp coâng thöùc ñieàn döõ lieäu cho caùc coät Khu vöïc vaø Ngaønh thi töông öùng cho töøng thí sinh. Trong ñoù: Khu vöïc laø kyù töï thöù hai cuûa maõ soá. Ngaønh thi döïa vaøo kyù töï ñaà cuûa maõ soá vaø baûng 1. 4. Töø kyù töï ñaàu cuûa maõ soá (maõ ngaønh), khu vöïc vaø baûng 1 haõy ñieàn döõ lieäu cho coät Ñieåm chuaån. Trong ñoù neáu thí sinh thuoäc khu vöïc 1 thì laáy ñieåm chuaån 1, ngöôïc laïi laáy ñieåm chuaån 2. 5. Tính ñieåm Toång coäng laø toång ñieåm cuûa 3 moân. 6. Haõy laäp coâng thöùc ñieàn Keát quaû nhö sau: neáu thí sinh coù ñieåm toång coäng lôùn hôn hay baèng ñieåm chuaån cuûa ngaønh mình döï thi deõ coù keát quaû laø "Ñaäu", ngöôïc laïi laø "Rôùt". 7. Theâm coät Hoïc boång vaø laäp coâng thöùc ñieàn vaøo ñoù laø "Coù" neáu ñieåm toång coäng cuûa thí sinh lôùn hôn hay baèng ñieåm hoïc boång ngaønh mình döï thi (tröôøng hôïp ngöôïc laïi ñeå troáng). 8. Duøng chöùc naêng Advanced Filter loïc ra moät danh saùch goàm toaøn boä caùc thí sinh coù keát quaû laø "Ñaäu". 9. Taïo baûng thoáng keâ cho bieát: Soá HS Ñaäu, soá HS Rôùt, bao nhieâu hoïc sinh coù hoïc boång.

Trang 12

D 19

Baøi taäp 11 Taïo baûng tính sau vaø löu laïi vôùi teân BT11.xls BAÛNG ÑÔN GIAÙ Maõ Teân haøng Ñg_1 Ñg_2 SP H Hoät ñieàu 25000 23000 G Gaïo 6000 5000 C Caø pheâ 50000 40000 D Ñöôøng 20000 18000

Stt 1 2 3 4 5 6 7

Maõ haøng H11 G32 C41 D22 G12 H31 C42

Teân haøng

BAÛNG TYÛ LEÄ THUEÁ Mieãn thueá

Maõ 1 2 3 4

X X

KEÁT TOAÙN HAØNG NHAÄP KHO Loaïi Soá Thaønh tieàn haøng löôïng 90 70 80 60 40 30 50

Thueá

Tyû leä thueá 10% 8% 5% 2%

Coøn laïi

Yeâu caàu 1. Saép xeáp baûng tính sao cho coät maõ haøng coù thöù töï taêng daàn. 2. Laäp coâng thöùc ñieàn döõ lieäu cho coät Loaïi haøng, vôùi Loaïi haøng laø kyù töï beân phaûi cuûa Maõ haøng vaø phaûi chuyeån sang trò soá. 3. Haõy ñieàn Teân haøng töông öùng döïa vaøo kyù töï ñaàu cuûa maõ haøng (laø MaSP) vaø Baûng ñôn giaù. 4. Tính Thaønh tieàn: laø Soá löôïng nhaân Ñôn giaù, vôùi Ñôn giaù cuûa töøng maët haøng ñaõ cho trong Baûng giaù. Löu yù: neáu haøng loaïi 1 thì laáy ñôn giaù loaïi 1 (Ñg_1), ngöôïc laïi laáy ñôn giaù loaïi 2 (Ñg_2). 5. Tính Thueá: Thueá ñöôïc tính baèng Thaønh tieàn nhaân Tyû leä thueá vaø chæ tính cho caùc maët haøng khoâng thuoäc loaïi mieãn thueá, trong ñoù tyû leä thueá ñöôïc bieát döïa vaøo kyù töï ôû giöõa cuûa Maõ haøng vaø Baûng tyû leä thueá. 6. Tính Coøn laïi: laø thaønh tieàn sau khi ñaõ tröø ñi phaàn thueá. 7. Töø baûng keát toaùn haøng xuaát kho, taïo moät danh saùch goàm toaøn caùc doøng ñöôïc mieãn thueá. 8. Trang trí baûng tính. 9. Thoáng keâ Teân haøng Toång giaù trò xuaát (sau thueá) Hoät ñieàu ? Gaïo ? Caø pheâ ? Ñöôøng ?

Trang 13