Bài tập thực hành MS Excel: Trường ĐH Văn Lang - Khoa CNTT.pdf (Bài tập thực hành MS Excel)

Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel BAØI 3: MICROSOFT EXCEL I. TOÅNG QUAN VEÀ MICROSOFT EXCEL - MicroSoft Excel laø phaàn meàm xöû lyù baûng tính ñieän töû (WorkSheet), chaïy trong moâi tröôøng Windows. Baûng tính ñieän töû laø 1 baûng coù kích thöôùc raát lôùn, goàm nhieàu coät (Column) vaø nhieàu haøng (Row). Nôi giao nhau giöõa coät vaø haøng ñöôïc goïi laø oâ (Cell). Caùc oâ naøy duøng ñeå chöùa vaø xöû lyù döõ lieäu. Ta coù theå nhaäp nhieàu loaïi döõ lieäu khaùc nhau vaøo 1 oâ ñeå xöû lyù vaø tính toaùn tuøy theo nhu caàu coâng vieäc cuûa mình. II. MOÄT SOÁ KHAÙI NIEÄM 1) Khôûi ñoäng chöông trình Microsoft Excel: - Caùch 1: Duøng bieåu töôïng cuûa noù, neáu coù (tieän lôïi nhaát). - Caùch 2: Nhaán Start\Programs\Microsoft Excel. - Caùch 3: Nhaán Start\Run ñaùnh Excel vaøo oâ Open Excel\Ok Maøn hình Excel nhö sau Title Bar: Thah tieâu ñeà Formular: Thanh Coâng thöùc OÂ ñang choïn, oâ vuoâng nhoû beân döôùi goïi Fill Handle Standar Toolbar: Thanh coâng cuï Formatting:Thanh ñònh daïng Tieâu ñeà coät 256 coät Tieâu ñeà doøng 65536 doøng Sheet: Baûng tính (1÷255) 2 Thoaùt khoûi Excel: - Click vaøo nuùt Close Duøng leänh File – Exit Double Click vaøo Control Box cuûa cöûa soå Excel Nhaán toå hôïp phím Alt-F4. 3) Workbook vaø Worksheet: - WorkBook: Laø moät taäp tin Excel coù phaàn môû roäng .XLS beân trong chöaù nhieàu WorkSheet töø 1 deán 255 sheet - Worksheet: Laø moät baûng tính goàm 256 coät (töø A, B, …AA, AB,.. IV) vaø 65536 doøng. Giao Cao Vaên An – Ñaëng Thanh Tuaán Trang 37 Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel giöõa coät vôùi haøng goïi laø OÂ (cell) (16,711,680 oâ) OÂ ñang choïn goïi oâ ñang hoaït ñoäng (Active) beân döôùi goùc phaûi coù moät oâ vuoâng nhoû goïi laø Fill Handle. - Fill Handle: Duøng ñeå sao cheùp döõ lieäu, coâng thöùc, haøm vaø daõy soá taêng giaûm. Baèng caùch ñöa con troû vaøo Fill Handle hieän daáu coäng (khoâng phaûi daáu thaäp) thì reâ chuoät ñi - Daõy soá taêng giaûm: Nhaäp trò khôûi ñaàu vaø trò thöù hai vöøa laø böôùc taêng. Choïn hai trò vöøa nhaäp duøng Fill Handle reâ ñi. 4) Caùc thao taùc treân WorkBook: - Taïo File môùi: + Choïn menu leänh File\New + Click vaøo bieåu töôïng New treân thanh coâng cuï + Nhaán toå hôïp phím Ctrl-N - Löu File: + Choïn menu leänh File\Save + Click vaøo bieåu töôïng Save treân thanh coâng cuï + Nhaán toå hôïp phím Ctrl-S (löu vôùi 1 teân khaùc baéng caùch choïn Save As) - Ñoùng 1 Taäp tin: + Choïn menu leänh File\Close - Môû 1 taäp tin: + Choïn menu leänh File\Open + Click vaøo bieåu töôïng Open treân thanh coâng cuï + Nhaán toå hôïp phím Ctrl-O 5) Caùc thao taùc treân WorkSheet: - Choïn Sheet hieän haønh + Click mouse vaøo teân cuûa Sheet muoán choïn. - Cheøn Sheet môùi beân caïnh Sheet hieän haønh + Baám phaûi chuoät teân Sheet ñeå kích hoaït Menu leänh vaø baïn choïn leänh Insert - Xoùa Sheet hieän haønh + Choïn menu Edit\Delete Sheet + Baám phaûi chuoät taïi teân Sheet ñeå kích hoaït menu leänh vaø baïn choïn Delete. - Sao cheùp hoaëc di chuyeån Sheet: + Choïn menu leänh Edit\Move or Copy Sheet + Click chuoät phaûi vaøo teân Sheet ñeå kích hoaït menu leänh vaø baïn choïn Move or Copy + Ñeå di chuyeån coù theå duøng phöông phaùp “Keùo-Thaû”. - Ñoåi teân cho Sheet hieän haønh: + Choïn menu leänh Format\Sheet-Rename. + Double click vaøo teân Sheet vaø ñaët laïi teân môùi. + Click chuoät phaûi vaøo teân Sheet ñeå kích hoaït menu leänh vaø baïn choïn Rename. - III. MOÄT SOÁ THAO TAÙC CÔ BAÛN VEÀ DÖÕ LIEÄU 1) Kieåu text (chuoãi): Laø chuoãi caùc kyù töï (A…Z,a…z,0…9,…) ngaàm ñònh laø canh traùi. 2) Kieåu numberic (soá): - Laø loaïi döõ lieäu theå hieän caùc giaù trò soá, goàm caùc soá aâm, döông, thaäp phaân, khoa hoïc, phaàn traêm. ngaàm ñònh laø canh phaûi. - Ñònh daïng: Choïn vuøng caàn ñònh daïng, choïn leänh Format\Cells\Number + General: Nhaäp sao hieån thò vaäy + Number: Decimal Places (soá chöõ soá thaäp phaân), Use 1000 separator (daáu ngaên caùch phaàn ngaøn) Cao Vaên An – Ñaëng Thanh Tuaán Trang 38 Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel 3) Kieåu date & time (ngaøy thaùng): - Laø loaïi döõ lieäu theå hieän caùc giaù trò ngaøy vaø giôø trong baûng tính, ngaàm ñònh canh beân phaûi. - Phaûi ñònh daïng daïng thöùc tröôùc khi nhaäp lieäu - Ñònh daïng: Choïn vuøng caàn ñònh daïng, choïn leänh Format\Cells\Number\Custom - Nhaäp daïng thöùc trong oâ Type ví duï dd/mm/yyyy - Daïng thöùc laø khuoân daïng maø tính toaùn ñöôïc DAÏNG THÖÙC HIEÅN THÒ 07/08/07 dd/mm/yy 07-08-07 dd-mm-yy 07/08/2007 dd/mm/yyyy - Neáu khoâng ñònh daïng ñöôïc: choïn Start\Settings\Control panel\Regional and Language choïn theû Date. Trong muïc Short date Style baïn goõ vaøo daïng thöùc DD/MM/YYYY 4) Kieåu Logic (luaän lyù): Chæ goàm 1 trong 2 giaù trò sau: TRUE vaø FALSE, True öùng vôùi bieåu thöùc Logic (meänh ñeà toaùn hoïc) ñuùng, False öùng vôùi bieåu thöùc Logic (meänh ñeà toaùn hoïc) sai. - Ví duï: “5 lôùn hôn 3” => trò True. 5) Coâng thöùc (Formular): Phaûi baét ñaàu baèng daáu = keát hôïp vôù toaùn töû vaø toaùn haïng. - Toaùn töû: + (coäng), - (tröø), * (nhaân), / (chia), ^ (muõ luõy thöùa), % (phaàn traêm), > (lôùn hôn), >= (lôùn hôn hay baèng), < (beù hôn), <= (beù hôn hay baèng), = (baèng), <> (khaùc), … - Toaùn haïng: Laø con soá, ñòa chæ, … IV. ÑÒA CHÆ 1) Ñòa chæ töông ñoái: Ñòa chæ thay ñoåi khi ta sao cheùp coâng thöùc (duøng cho döõ lieäu thay ñoåi) - Kyù hieäu: - Ví duï: A1 laø coät A vaø doøng 1 coù nghóa laø khi sao cheùp coâng thöùc hay haøm ñòa chæ thay ñoåi töông öùng 2) Ñòa chæ tuyeät ñoái: Ñòa chæ khoâng thay ñoåi khi ta sao cheùp coâng thöùc (duøng cho döõ lieäu coá ñònh) - Kyù hieäu: <$coät> <$doøng> - Ví duï: $A$1 laø tuyeät ñoái coät A vaø tuyeät ñoái doøng 1 coù nghóa khi sao cheùp coâng thöùc hay haøm ñòa chæ vaãn laø coät A vaø doøng 1 3) Ñòa chæ hoãn hôïp: Ñòa chæ khoâng thay ñoåi coät hay doøng laø keát hôïp ñòa chæ töông ñoái vaø tuyeät ñoái. - Kyù hieäu: <$coät> hay <$doøng> - Ví duï: &A1 laø tuyeät ñoái coät A vaø töông ñoái doøng 1 hay A$1 laø töông ñoái coät A vaø tuyeät ñoái doøng 1 V. ÑÒNH DAÏNG BAÛNG TÍNH - - - Tieâu ñeà baûng (cho bieát teân cuûa baûng ñoù): Trình baøy taïi A1, queùt töø A1 ñeán oâ baèng chieàu ngang baûng roài click nuùt Merge and Center Tieâu ñeà coät (cho bieát döõ lieäu cuûa coät ñoù): Neáu xuoáng doøng Alt Enter Trang trí baûng tính: + Choïn baûng tính caàn ñònh daïng. + Choïn leänh Format\Cells.. hieän hoäp thoaïi Font: Choïn kieåu phoâng, ñaäm, nghieâng, gaïch döôùi… Alignment: Canh vaên baûn + Horizontal: Canh theo chieàu ngang (Left: Canh leà traùi, Right: Canh leà phaûi, Center: Canh leà giöõa, Justify: Canh leà ñeàu 2 beân) + Vertical: Canh theo chieàu doïc (Top: Canh leà theo ñænh treân, Bottom: Canh leà theo ñaùy döôùi, Center: Canh leà giöõa, Justify: Canh leà ñeàu 2 beân) Border: Ñoùng khung Patterns: Phuû neàn Cao Vaên An – Ñaëng Thanh Tuaán Trang 39 Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel BAØI THÖÏC HAØNH EXCEL 01 Laäp coâng thöùc cho baûng tính sau: X-Y X*Y X/Y X2+Y3 X Y X+ Y 15 3 ? ? ? ? ? 7 16 ? ? ? ? ? 11 11 ? ? ? ? ? 14 7 ? ? ? ? ? 3 5 ? ? ? ? ? 8 4 ? ? ? ? ? 10 5 ? ? ? ? ? 12 4 ? ? ? ? ? Toång Coäng ? ? ? ? Laäp coâng thöùc cho baûng tính treân theo caùc pheùp toùan treân tieâu ñeà coät. (X+Y)% (X+Y)^2 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? VI. HAØM: 1) Khaùi nieäm: Haøm laø 1 ñoaïn chöông trình coù saün nhaèm thöïc hieän 1 yeâu caàu naøo ñoù veà tính toaùn maø thöôøng thì toaùn töû khoâng xöû lyù ñöôïc. Haøm luoân traû veà 1 giaù trò thuoäc 1 kieåu döõ lieäu naøo ñoù. 1) Toång quaùt: = TÊN HÀM(Đối số 1, Đối số 2, …., Đối số n) - - - Haøm phaûi laø daáu baèng (=) Teân haøm phaûi vieát ñuùng teân Excel quy ñònh, khoâng phaân bieät chöõ hoa hay thöôøng. Moãi moät teân haøm seõ coù caùc ñoái soá töông öùng ñöôïc ngaên caùch baèng daáu phaåy (, ) hay chaám phaåy (; ) tuøy theo khai baùo trong Regional and Language, caùc ñoái soá naøy phaûi ñöôïc raøo trong caëp daáu ngoaëc ñôn (). Moät soá haøm khoâng coù ñoái soá nhöng baét buoäc phaûi coù caëp daáu ngoaëc ñôn () keøm theo teân haøm. Ñoái soá coù theå: + Con soá: ví duï =SUM(2,3,5,4) → 14 + Ñòa chæ: ví duï cho A1=2, A2=3, A3=5 VAØ A4=4. Ta coù =SUM(A1,A2,A3,A4)→ 14 - Neáu: A1,A2,A3,A4 = A1:A4 (caùch vieát goïn) - Neáu: A1,B1,C1,D1 = A1:D1 (caùch vieát goïn) Chuoãi: phaûi ñaët trong daáu ngoaëc keùp (“ ), ví duï: =IF(3>7,”Sai”,”Ñuùng”) Ñoái soá laø Haøm: Haøm nhöng khoâng coù daáu baèng, ví duï: =IF(Diem<5,”Yeáu”,IF(Diem<7,…) Neáu khoâng tính ñöôïc, haøm seõ traû veà 1 trong caùc maõ loãi (Error Code) sau: BAÛNG MAÕ LOÃI #VALUE! #N/A #NAME? #NUM! #DIV/0 #REF! #NUL! Cao Vaên An – Ñaëng Thanh Tuaán Khoâng tính ñöôïc, trò sai. Giaù trò tham chieáu khoâng toàn taïi. Khoâng nhaän ñöôïc teân. Trò soá khoâng hôïp leä. Pheùp chia cho 0. Khoâng tham chieáu ñeán ñöôïc. Roãng. Trang 40 Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel BAØI THÖÏC HAØNH EXCEL 02 BAÛNG CHIEÁT TÍNH VAÊN PHOØNG PHAÅM Stt Teân Haøng Soá Löôïng Ngaøy: =Today() Ñôn Giaù Trò Giaù Hueâ Hoàng Chuyeân Chôû Thaønh Tieàn 1 2 Taäp 100 tr 3000 1950 Taäp 200 tr 2000 4500 Giaáy A4-80 100 42000 Giaáy A4-70 50 38000 Giaáy A4-60 50 35000 Soå Loaïi 1 120 17500 Soå Loaïi 2 100 15000 Buùt bi Nhaät 50 12000 Buùt daï quang 100 10000 Toång Coäng Giaù Trò Trung Bình Giaù trò Cao Nhaát Giaù trò Thaáp Nhaát Yeâu caàu: 1. Nhaäp döõ lieäu vaø ñieàn coät soá thöù töï 2. Laäp coâng thöùc cho coät Trò Giaù = Soá Löôïng * Ñôn Giaù 3. Coät Hueâ Hoàng = 10% * Trò Giaù 4. Coät Chuyeân Chôû = Soá Löôïng *100 5. Thaønh Tieàn = Trò Giaù – (Hueâ Hoàng +Chuyeân Chôû) 6. Laäp coâng thöùc doøng Toång Coäng, Trung Bình, Cao nhaát, Thaáp nhaát cho caùc coät Trò Giaù, Hueâ Hoàng, . . . 7. Trang trí & ñònh daïng Sheet 8. Ñoåi teân Sheet thaønh VanPhong VII. GIÔÙI THIEÄU CAÙC NHOÙM HAØM THOÂNG DUÏNG: 1. Nhoùm haøm logic (logical function) a) Haøm AND: =AND(Ñieàu kieän 1,Ñieàu kieän 2,…,Ñieàu kieän n) Cuù phaùp: Coâng duïng: Haøm naøy seõ traû veà True neáu taát caû caùc ñieàu kieän ñuùng, ngöôïc laïi False. Ví duï: =AND(5>3,3>0,“A”<>“B”) → TRUE =AND(10>0,12<20,“ABC”=“a”) → FALSE b) Haøm OR: - =OR(Ñieàu kieän 1,Ñieàu kieän 2,…,Ñieàu kieän n) Cuù phaùp: Coâng duïng: Haøm naøy seõ traû veà True neáu chæ caàn moät ñieàu kieän ñuùng, ngöôïc laïi False. Ví duï: =OR(5>3,3>0,“A”<>“B”) → TRUE =OR(10<0,12>20,“ABC”=“a”) → FALSE c) Haøm IF: - - Cuù phaùp: =IF(Meänh ñeà ñieàu kieän,Trò ñuùng, Trò sai) Cao Vaên An – Ñaëng Thanh Tuaán Trang 41 Tröôøng ÑHDL Vaên Lang – Khoa CNTT - - Baøi thöïc haønh MS Excel Coâng duïng: Haøm naøy seõ traû veà Trò ñuùng neáu Meänh ñeà ñieàu kieän ñuùng ngöôïc laïi laáy Trò sai. Ví duï: =IF(5>3,“ABC”,“EF”) => “ABC” =IF(5>10, “AB”,100) => 100 (B1) =IF(A1=“AN”,10,5) => 10 Neáu coù n giaù trò thì ta duøng (n-1) IF loàng nhau Ví duï: Tính coät Heä Soá Chöùc Vuï(Heä Soá CV) theo yeâu caàu: Neáu chöùc vuï laø GÑ thì HSCV laø 5 Neáu chöùc vuï laø TP thì HSCV laø 3 Neáu chöùc vuï laø PP thì HSCV laø 2 Ngöôïc laïi HSCV laø 1 Khi ñoù taïi oâ C2, ta duøng coâng thöùc: IF(B2=“GÑ”,5,IF(B2=“TP”,3,IF(B2=“PP”,2,1))) BAØI THÖÏC HAØNH EXCEL 03 BAÛNG CHIEÁT TÍNH GIAÛM GIAÙ Stt 1 2 Soá Tieàn Traû 2500 3750 1500 8540 5000 4500 Khaùch Haøng Nguyeãn Thanh Thuaän Traàn Ngoïc Quyønh Leâ Taâm Minh Nguyeãn Thu Cuùc Laïi Vaên Sau Huyønh Minh Trí Ngaøy Nhaän Haøng 10/03/05 23/03/05 12/03/05 26/03/05 21/03/05 05/03/05 Ngaøy Traû Tieàn 20/03/05 01/04/05 25/03/05 10/04/05 15/04/05 25/04/05 Yeâu caàu: 1. Cheøn theâm coät Soá Ngaøy Nôï beân phaûi coät Ngaøy Traû Tieàn. Tính Soá Ngaøy Nôï = Ngaøy Traû Tieàn – Ngaøy Nhaän Haøng 2. Cheøn vaø laäp coâng thöùc cho coät Giaûm Giaù. Neáu Soá Ngaøy Nôï <= 10, giaûm 3% Soá Tieàn Traû. Ngöôïc laïi khoâng giaûm 3. Theâm Coät Soá Tieàn Phaûi Traû = Soá Tieàn Traû – Giaûm Giaù BAØI THÖÏC HAØNH EXCEL 04 BAÛNG TÍNH TIEÀN LÖU KHO SAÛN PHAÛM LEÄ PHÍ LÖU KHO Stt Maët Haøng Ngaøy Nhaäp Ngaøy Xuaát Giaù Löu Kho Soá Ngaøy Löu Kho 2000 Leä Phí 1 2 Tivi 17’ Samsung 20/01/04 02/03/04 Maùy Photo LG 29/01/04 03/02/04 Maùy Vi Tính P4 28/01/04 28/04/04 Maùy In HP11200 30/01/04 30/03/04 Keùt Saét Korea 02/02/04 05/03/04 Gheá Xoay Japan 03/02/04 25/04/04 DVD Colifornia 04/03/04 15/04/04 Yeâu caàu: 1. Nhaäp döõ lieäu, ñieàn coät soá thöù töï vaø trang trí baûng tính. Cao Vaên An – Ñaëng Thanh Tuaán Trang 42 Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel 2. Tính soá ngaøy löu kho 3. Tính Leä Phí Löu Kho = Soá Ngaøy Löu Kho * Giaù Löu Kho 4. Cheøn theâm coät Ghi Chuù vaøo sau coät Leä Phí. Laäp coâng thöùc cho coät Ghi Chuù nhö sau: Neáu Soá Ngaøy Löu Kho >50 ngaøy, thì ghi laø “Giaûm phí”, ngöôïc laïi khoâng ghi. 5. Söû duïng chöùc naêng Conditional Formatting ñeå ñònh daïng Coät Soá Ngaøy Löu Kho theo yeâu caàu sau: Neáu Soá Ngaøy Löu Kho > 50 , thì ñònh daïng chöõ maøu ñoû, ñaäm. 2. Nhoùm haøm soá (numberic functions) a) Haøm ABS: Cuù phaùp: =ABS(Bieåu Thöùc Soá) Coâng duïng: Tính giaù trò tuyeät ñoái cuûa bieåu thöùc soá: Ví duï: =ABS(8) → 8 ; =ABS(-7) → 7 b) Haøm SQRT: - =SQRT(Bieåu Thöùc Soá) Cuù phaùp: Coâng duïng: Tính giaù trò caên baäc 2 döông cuûa bieåu thöùc soá: Ví duï: =SQRT(9) → 3 ; =SQRT(2) → 1.414214 c) Haøm INT: - =INT(Bieåu Thöùc Soá) Cuù phaùp: Coâng duïng: Tính giaù trò nguyeân cuûa bieåu thöùc soá: Ví duï: =INT(7.687) → 7 ; =INT(SQRT(5)) → 2 d) Haøm MOD: - =MOD(Soá bò chia,Soá chia) Cuù phaùp: Coâng duïng: Laáy phaàn dö cuûa pheùp chia: Ví duï: =MOD(10,3) → 1; =MOD(10,2) → 0 e) Haøm SUM: =SUM(daõy soá) - Cuù phaùp: - - Coâng duïng: Tính toång coäng caùc giaù trò cuûa caùc oâ coù daõy soá: Ví duï: =SUM(10,3) → 13; =SUM(10,2) → 12 f) Haøm AVERAGE: - =AVERAGE(daõy soá) Cuù phaùp: Coâng duïng: Tính Trung Bình coäng cuûa caùc soá hoaëc caùc oâ coù trong daõy soá: Ví duï: = AVERAGE(10,3,2) → 15; =AVERAGE(15,9,5) → 9,667 g) Haøm MAX: - =MAX(daõy soá) Cuù phaùp: Coâng duïng: Duøng ñeå tìm soá lôùn nhaát trong daõy soá: Ví duï: = MAX(10,3,2) → 10; =MAX(15,9,5) → 15 Cao Vaên An – Ñaëng Thanh Tuaán Trang 43 Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel h) Haøm MIN: - =MIN(daõy soá) Cuù phaùp: Coâng duïng: Duøng ñeå tìm soá nhoû nhaát trong daõy soá: Ví duï: = MAX(10,3,2) → 2; =MAX(15,9,5) → 5 BAØI THÖÏC HAØNH EXCEL 05 BAÛNG BAÙO CAÙO DOANH THU THAÙNG 04/05 BAÙO CAÙO DOANH THU THAÙNG 04/05 Stt Maõ Haøng Teân Haøng Loaïi Haøng Tyû Leä Thueá Ñôn Giaù Nokia Soá Löôïng Trò Giaù Toång Trò Giaù ? 1 N7270 7290000 12 2 N7710 9990000 10 N9500 13800000 5 Loaïi Haøng Samsung Toång Trò Giaù ? 1 P730 7950000 8 2 E310 3990000 5 P510 5700000 14 S755 4950000 6 Loaïi Haøng Motorola 10% Toång Trò Giaù ? 1 V878 4950000 12 2 V600 4800000 8 V3 9500000 6 E680 7900000 4 Toång Coäng Tính Ñôn Giaù Trung Bình Ñôn Giaù Cao Nhaát Ñôn Giaù Thaáp Nhaát Yeâu caàu: 1. Laäp coâng thöùc cho coät Trò Giaù = Soá Löôïng * Ñôn Giaù 2. Laäp coâng thöùc tính Toång Trò Giaù = Toång Trò Giaù cuûa töøng loïai haøng 3. Cheøn vaøo beân phaûi coät Trò Giaù, coät Thueá GTGT = Trò Giaù * Tyû Leä thueá 4. Laäp coâng thöùc cho Coät Teân Haøng. Bieát raèng: Teân Haøng = Loaïi Haøng & “ ” & Maõ Haøng 5. laäp coâng thöùc cho caùc oâ Toång Coäng, Ñôn Giaù Trung bình, . . . . 6. Trang trí vaø ñònh daïng döõ lieäu cho caùc coät Ñôn Giaù, Trò Giaù, Thueá GTGT theo daïng (#,##0.00). BAØI THÖÏC HAØNH EXCEL 06 BAÛNG KEÂ BAÙN HAØNG Thaùng: 02/2005 Stt Teân Haøng Maùy Laïnh TiVi 21” Cao Vaên An – Ñaëng Thanh Tuaán Soá Löôïng 15 25 Tyû Giaù : Ñôn Giaù 540 320 15,650 Chieát Khaáu Thueá Thaønh Tieàn Trang 44 Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel Xe Suzuki Viva R 24 2800 Maùy Giaët Toshiba 16 300 Ñaàu DVD JVC 27 120 Toång Coäng Yeâu Caàu: 1. Tính coät Chieát Khaáu = Soá Löôïng * Ñôn Giaù * Tyû Leä Chieát Khaáu. Bieát raèng: Tyû Leä Chieát Khaáu ñöôïc tính nhö sau: • Neáu Soá Löôïng mua < 10 thì baèng 0 • Neáu Soá Löôïng < 20 thì baèng 2% • Ngöôïc laïi, Soá Löôïng >= 20 thì baèng 5% 2. Tính Coät Thueá = (Soá Löôïng * Ñôn Giaù – Chieát Khaáu)* 10% 3. Thaønh Tieàn = (Soá Löôïng * Ñôn Giaù – Chieát Khaáu - Thueá)* Tyû Giaù 4. Laäp coâng thöùc tính Toång Coäng vaø trang trí baûng tính. BAØI THÖÏC HAØNH EXCEL 07 BAÛNG LÖÔNG CAÙN BOÄ, COÂNG NHAÂN VIEÂN THAÙNG 8/2000 STT 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 MAÕ SOÁ CHÖÙC VUÏ Nguyeãn Vaên Thaønh TP Leâ Thò Dung NV Traàn Vaên Ñang NV Phan Ñình Phuøng TP Hoà Thò Thu NV Löu Vaên Linh NV Cao Nguyeät Kieàu NV Döông Minh Quang NV Ñaøo Caåm Tuù NV Ngoâ Coâng Baèng PP Leâ Phöông Nga GD Traàn Haø Nam NV Phan Thi Taâm PP Toá Queá Chaâu NV Ngoâ Ba TP TOÅNG COÄNG: HOÏ VAØ TEÂN LÖÔNG CB 35,700 21,000 15,000 37,400 19,000 20,000 15,600 14,500 25,000 31,000 55,000 19,800 42,500 22,500 35,000 NGAØY COÂNG 25 24 27 27 24 25 26 24 24 27 27 24 24 27 28 PHUÏ CAÁP CHÖÙC VUÏ 100,000 70,000 70,000 100,000 70,000 70,000 70,000 70,000 70,000 90,000 150,000 70,000 90,000 65,000 75,000 LÖÔNG TAÏM ÖÙNG COØN LAÏI Yeâu Caàu: 1) Tính LÖÔNG, Bieát LÖÔNG = NGAØY COÂNG*LCB+PHUÏ CAÁP CHÖÙC VUÏ 2) Tính TAÏM ÖÙNG vaø laøm troøn tôùi phaàn ngaøn, bieát: TAÏM ÖÙNG=35%*LÖÔNG 3) Tính COØN LAÏI, bieát COØN LAÏI= LÖÔNG - TAÏM ÖÙNG 4) Theâm coät CHÖÙC VUÏ, theo yeâu caàu: • Neáu MAÕ SOÁ CHÖÙC VUÏ laø “GD” thì “Giaùm Ñoác” • Neáu MAÕ SOÁ CHÖÙC VUÏ laø “TP” thì “Tröôûng Phoøng” • Neáu MAÕ SOÁ CHÖÙC VUÏ laø “PP” thìø “Phoù Phoøng” • Neáu MAÕ SOÁ CHÖÙC VUÏ laø “NV” thì “Nhaân vieân” 5) Tính doøng toång coäng: 6) Trang trí baûng tính theo maãu ñaõ cho 7) Ñònh daïng taát caû caùc coät LÖÔNG, TAÏM ÖÙNG, COØN LAÏI thep daïng “#,##0.00VNÑ” Cao Vaên An – Ñaëng Thanh Tuaán Trang 45 Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel BAØI THÖÏC HAØNH EXCEL 09 ÑAÏI LYÙ EIGHTEEN - TICKET CHÖÙNG TÖØ THAÙNG 02/2005 Giaù goác Stt Maõ 1 2 N02X N01N X03N N02D X03D N01X Khu Vöïc A B B C D C Loaïi Teân Vaät Tö Ñôn Giaù Soá Löôïng 25 50 60 30 45 40 Phí Vaän Chuyeån Thueá 3200 Thaønh Tieàn Toång Coäng Trung Bình Cao Nhaát Thaáp Nhaát Yeâu caàu: 1. Laäp coâng thöùc cho coät Loaïi: Neáu kyù töï beân traùi cuûa Maõ Vaät Tö laø “N” thì Loaïi “Nhaäp”. Ngöôïc laïi Kyù Töï beân traùi cuûa Maõ Vaät Tö laø “X” thì Loaïi ”Xuaát” 2. Laäp coâng thöùc cho coät Teân Haøng : Neáu Kyù töï beân phaûi cuûa Maõ Vaät Tö laø “X”, thì “Xaêng”. Neáu Kyù töï beân phaûi cuûa Maõ Vaät Tö laø “D”, thì “Daàu”. Coøn laïi laø “Nhôùt” 3. Tính Ñôn Giaù = Heä Soá * Giaù Goác. Bieát raèng Heä soá laø giaù trò cuûa kyù töï thöù 2, 3 trong Maõ Vaät Tö. 4. Tính Phí Vaän Chuyeån: Döïa vaøo Maõ Khu Vöïc nhö sau: • A ...............................................................30000 • B ...............................................................32000 • C ...............................................................27000 • D................................................................25000 5. Tính coät Thueá. Bieát raèng: Neáu laø “Xaêng”, thì thueá 5% cuûa Trò Giaù, Neáu laø “Daàu” thì 3%, coøn laïi “Nhôùp” thì 2% cuûa Trò Giaù 6. Tính Coät Thaønh Tieàn = Trò Giaù – Thueá – Phí Vaän Chuyeån 7. Laäp coäng thöùc tính Toång Coäng, Trung Bình, cao Nhaát, Thaáp Nhaát 8. Laäp coâng thöùc cho coät Ghi Chuù: Neáu Thaønh Tieàn cuûa Ñaïi Lyù ñoù Cao Nhaát thì Ghi Chuù ghi “Cao Nhaát”, ngöôïc laïi thì ghi laø “Bình Thöôøng” i) Haøm ROUND: =ROUND(Bieåu thöùc soá, vò trí laøm troøn) - Cuù phaùp: Coâng duïng: Duøng ñeå laøm troøn soá ñeán vò trí laøm troøn (vò trí laøm troøn <0: laøm troøn phaàn nguyeân, vò trí laøm troøn =0: laøm troøn haøng ñôn vò, vò trí laøm troøn >0: laøm troøn phaàn thaäp phaân) Ví duï: Giaù trò n Vò trí caàn laøm troøn ñeán Soá caàn laøm troøn: 1647.2578 vò trí laøm troøn =3 3 Soá leû thaäp phaân. =ROUND(1647.2578,3) Æ 1647.258 vò trí laøm troøn =2 2 Soá leû thaäp phaân. =ROUND(1647.2578,2) Æ 1647.26 vò trí laøm troøn =1 1 Soá leû thaäp phaân. =ROUND(1647.2578,1) Æ 1647.3 vò trí laøm troøn =0 0 Soá leû (haøng ñôn vò) =ROUND(1647.2578,0) Æ 1647 vò trí laøm troøn =-1 Haøng chuïc. =ROUND(1647.2578,-1) Æ 1650 - Cao Vaên An – Ñaëng Thanh Tuaán Trang 46

Bài tập thực hành MS Excel: Trường ĐH Văn Lang - Khoa CNTT

Nội dung