Bài tập Excel tổng hợp

Baøi Taäp Excel Trang 1 Caâu 1: Nhaäp lieäu, tính toaùn vaø löu file vôùi TeânAnhChi_Ex1.xls BAÛNG DÖÏ TRUØ VAÄT TÖ STT Teân Vaät Tö ÑVT Ñôn giaù (ñoàng VN) Soá Löôïng Thaønh Tieàn (ñoàng VN) 1 Xi maêng P400 Bao 45000 1845 2 Caùt M3 30000 16 3 Gaïch theû 1000 Vieân 400000 12 4 Saét 10 Kg 5200 345 5 Gaïch men M2 52000 35 6 Saét 20 Kg 6200 20 7 Gaïch granit M2 132000 12 8 Gaïch vieàn Vieân 2000 2520 9 Boät treùt töôøng Kg 1000 150 10 Sôn nöôùc Kg 12000 150 11 Sôn daàu Kg 25000 35 12 Gaïch trang trí vieân 2500 100 Toång coäng Yeâu caàu tính toaùn: Thaønh Tieàn = Ñôn Giaù * Soá Löôïng. Toång coäng = Toång cuûa coät Thaønh Tieàn Caâu 2: Tính toaùn baøi taäp naøy treân Sheet2 cuûa file TeânAnhChi_Ex1.xls . LAÄP COÂNG THÖÙC STT A B A+B A*B A/B A+B*2 (A+B)*2 A+B +A*B 1 12 4 2 16 49 3 3.2 26.29 4 12.56 29.74 5 27 156.5 6 421 12 7 45 97.3 8 234.1 13.3 9 16.4 12 10 15 561 11 24.5 19 12 38.1 28.3 Baøi Taäp Excel Trang 2 Caâu 1: Nhaäp lieäu vaø tính toaùn, löu file vôùi teân TeânAnhChi_Ex2.xls TÌNH HÌNH THÖÏC HIEÄN KEÁ HOAÏCH Naêm 2004 Ñôn vò tính: 1,000,000 ñoàng VN STT Teân ñôn vò Keá hoaïch Quyù I Quyù II Quyù III Quyù IV Caû naêm Tyû leä Ñaùnh giaù 1 XN Cô khí 900 145 411 267 280 1,103 122.56% Ñaït loaïi A 2 XN Deät 1,200 425 341 318 320 1,404 117.00% Ñaït loaïi B 3 XN May 1,400 390 438 346 311 1,485 106.07% Ñaït loaïi B 4 XN Giaøy Da 870 167 179 214 235 795 91.38% Khoâng ñaït 5 XN Ñoâng Laïnh 2,100 456 541 534 532 2,063 98.24% Khoâng ñaït 6 XN Thuoác laù 756 232 224 245 257 7 XN Thuyû saûn 340 120 156 137 198 8 XN Chaên nuoâi 120 35 32 27 30 Yeâu caàu tính toaùn: 1. CAÛ NAÊM = Coäng cuûa 4 quyù 2. TYÛ LEÄ = CAÛ NAÊM / KEÁ HOAÏCH (Ñònh daïng % vaø laáy hai soá leû) 3. ÑAÙNH GIAÙ : Neáu TYÛ LEÄ < 100% Khoâng ñaït Neáu TYÛ LEÄ >=100% Ñaït 4. Xeáp laïi baûng tính theo thöù töï giaûm daàn cuûa coät CAÛ NAÊM. Caâu 2: Tính toaùn baøi taäp naøy treân Sheet2 cuûa file TeânAnhChi_Ex2.xls BAÛNG KEÂ HAØNG BAÙN THAÙNG 01/2003 STT TEÂN HAØNG SOÁ LÖÔÏNG ÑÔN GIAÙ THUEÁ PHÍ VC THAØNH TIEÀN 1 Maùy laïnh 12 540 2 Tivi 45 320 3 Xe Dream II 15 2800 4 Maùy giaët 20 300 5 Ñaàu Video 5 120 6 Ñaàu DVD 60 230 7 Computer 13 460 8 Printer 15 120 9 Scanner 10 250 10 Digital Camera 8 1230 11 Maxtor HDD 80 79 TOÅNG COÄNG Giaûi thích: PHÍ VC: phí vaän chuyeån. Tính toaùn: 1. THUEÁ = SOÁ LÖÔÏNG * ÑÔN GIAÙ * 10% 2. PHÍ VC = ÑÔN GIAÙ * SOÁ LÖÔÏNG * TÆ LEÄ VC  TÆ LEÄ VC = 6% NEÁU SOÁ LÖÔÏNG < 30.  TÆ LEÄ VC = 4% NEÁU SOÁ LÖÔÏNG >=30. 3. THAØNH TIEÀN = ÑÔN GIAÙ * SOÁ LÖÔÏNG + THUEÁ + PHÍ VC . 4. Saép xeáp theo thöù töï taêng daàn cuûa coät THAØNH TIEÀN. Baøi Taäp Excel Trang 3 BAØI TAÄP 2 Nhaäp döõ lieäu, tính toaùn vaø löu vôùi _BT2.xls vôùi laø teân cuûa Anh (Chò) BAÛNG TÍNH TIEÀN ÑIEÄN thaùng 4/2004 STT HOÏ VAØ TEÂN CHUÛ HOÄ CSC CSM KW TÑIEÄN VAT TOÅNG COÄNG Traàn Thò Thanh Bình 115 240 Phaïm Tröôøng Laâm 120 350 Phan Caåm Loan 161 421 Quaùch Theá Long 145 165 Traàn Thò Minh Haèng 225 320 Nguyeãn Thaønh Taâm 142 178 Traàn Vaên Ngoïc 125 156 Huyønh Thanh Hoa 456 499 Leâ Hoaøng Tuøng 431 488 Nguyeãn Ngoïc Thanh 589 691 Traàn Thanh Hoàng 1754 1978 Leâ Vaên Huøng 346 456 Nguyeãn Höõu Chieán 1245 1564 Traàn Vaên Thoâng 132 342 Leâ Höõu Hoaøng 357 451 Hoà Vaên Lôïi 751 851 Nguyeãn Trang Thanh 478 567 Traàn Hoàng Ngoïc 1456 1789 Möùc tieâu thuï lôùn nhaát trong thaùng: Möùc tieâu thuï trung bình trong thaùng: Möùc tieâu thuï nhoû nhaát trong thaùng: Coäng: Giaûi thích: CSC: Chæ soá cuõ, CSM : Chæ soá môùi , Tñieän : Tieàn ñieän Yeàu caàu: 1. Ñieàn chuoãi soá thöù töï vaøo coät STT . 2. KW : Möùc tieâu thuï trong thaùng tính baèng KW = CHÆ SOÁ MÔÙI -CHÆ SOÁ CUÕ. 3. TIEÀN ÑIEÄN = Soá KW * GIAÙ TIEÀN ÑIEÄN /KW, bieát giaù tieàn ñieän laø 1200ñ /KW 4. VAT: 10% cuûa TieànÑieän. 5. TOÅNG COÄNG : Laø toång cuûa TIEÀN ÑIEÄN vaø VAT . 6. COÄNG = Toång cuûa coät TOÅNG COÄNG 7. Tìm möùc tieâu thuï lôùn nhaát trong thaùng. 8. Tìm möùc tieâu thuï trung bình trong thaùng. 9. Tìm möùc tieâu thuï nhoû nhaát trong thaùng. 10. Xeáp theo thöù töï taêng daàn cuûa coät TOÅNG COÄNG. ? ? ? ? Baøi Taäp Excel Trang 4 Nhaäp döõ lieäu, tính toaùn vaø löu file TeânAnhChi_Ex4.xls TRUNG TAÂM KHAÛO THÍ KHAÙNH HOAØ KEÁT QUAÛ TUYEÅN SINH KHOAÙ NGAØY20/05/2003 STT HOÏ VAØ TEÂN SBD Khuvuc Anh vaên Toaùn Tin hoïc ÑieåmKV Toång ñieåm Keát quaû Traàn Ñình Anh 346B 1 9 7 5 Baûo Ngoïc 890C 1 3 6 6 Buøi thi Thu Cuùc 342B 3 5 8 4 Nguyeãn thò Haèng 674B 2 7 8 4 Buøi vaên Tuaán 781B 3 4 3 5 Traàn Bình Minh 201C 2 8 8 9 Traàn vaên Huøng 459A 2 5 4 3 Buøi Thanh 576C 2 7 6 4 Nguyeãn thò Thu 324A 1 7 8 4 Nguyeãn vaên Anh 231A 2 6 3 4 Leâ Vaên Huøng 142C 2 5 4 6 Nguyeãn Höõu Chieán 231A 1 8 2 1 Traàn Vaên Thoâng 463C 2 2 5 4 Leâ Höõu Hoaøng 346C 3 6 4 8 Toång ñieåm thaáp nhaát:. Toång ñieåm cao nhaát:. Toång ñieåm trung bình: Yeàu caàu:  Ñieàn chuoãi soá thöù töï vaøo coät STT . Tính caùc coät coøn laïi cuûa baûng tính theo caùc qui taéc sau:  ÑieåmKV : Neáu laø Khuvuc 3 thì ÑieåmKV = 2. Ngöôïc laïi, ÑieåmKV = 1.  Toångñieåm: laø toång caùc moân thi ( Anh vaên heä soá 1, Toaùn vaø Tin hoïc coù heä soá 2) vaø ÑieåmKV  Keátquaû : Neáu TOÅNG ÑIEÅM beù hôn 25 thì ghi laø “Thi laïi”, ngöôïc laïi ghi “Ñaäu”.  Tìm TOÅNG ÑIEÅM thaáp nhaát .  Tìm TOÅNG ÑIEÅM cao nhaát .  Tìm TOÅNG ÑIEÅM trung bình.  Saép xeáp coät TOÅNG ÑIEÅM theo thöù töï giaûm daàn. Nhaäp döõ lieäu, tính toaùn vaø löu vôùi TenAnhChi_BTEx5.xls Baøi Taäp Excel Trang 5 BAÛNG TÍNH TIEÀN ÑIEÄN thaùng 2/2004 STT HOÏ VAØ TEÂN CHUÛ HOÄ CSC CSM KhuVöï c TOÅNG COÄNG Traàn Thò Thanh Bình 115 240 2 Phaïm Tröôøng Laâm 120 350 1 Phan Caåm Loan 161 421 3 Quaùch Theá Long 145 165 2 Traàn Thò Minh Haèng 225 320 2 Nguyeãn Thaønh Taâm 142 178 1 Traàn Vaên Ngoïc 125 156 1 Huyønh Thanh Hoa 456 499 2 Leâ Hoaøng Tuøng 431 488 3 Nguyeãn Ngoïc Thanh 589 691 3 Traàn Thanh Hoàng 1754 1978 1 Leâ Vaên Huøng 346 456 1 Nguyeãn Höõu Chieán 1245 1564 2 Traàn Vaên Thoâng 132 342 2 Leâ Höõu Hoaøng 357 451 1 Hoà Vaên Lôïi 751 851 1 Coäng: Möùc tieâu thuï lôùn nhaát trong thaùng: Möùc tieâu thuï trung bình trong thaùng: Giaûi thích: CSC: Chæ soá cuõ, CSM : Chæ soá môùi. Yeàu caàu: Theâm caùc coät KW, Vöôït, Tñieän, Phaït, VAT sau coät tröôùc coät Toång Coäng 1. Ñieàn chuoãi soá thöù töï vaøo coät STT . 2. KW : Möùc tieâu thuï trong thaùng tính baèng KW = CHÆ SOÁ MÔÙI -CHÆ SOÁ CUÕ. 3. Vöôït: Soá KW vöôït ñònh möùc. Ñònh möùc soá KW cho moãi hoä laø 120 KW 4. TIEÀN ÑIEÄN = Soá KW ñònh möùc * GIAÙ TIEÀN ÑIEÄN /KW, bieát giaù tieàn ñieän ñònh möùc cuûa khu vöïc 1 laø 800ñ /KW. Caùc khu vöïc coøn laïi giaù 1000ñ/kw 5. PHAÏT : Chæ phaït nhöõng hoä coù möùc duøng vöôït quaù ñònh möùc. Moãi KW vöôït seõ ñöôïc tính 1500ñ/kw cho taát caû caùc khu vöïc. 6. VAT = 10% cuûa TIEÀN ÑIEÄN vaø TIEÀN PHAÏT 7. TOÅNG COÄNG : Laø toång cuûa TIEÀN ÑIEÄN, VAT vaø PHAÏT. 8. COÄNG = Toång cuûa coät TOÅNG COÄNG 9. Tìm möùc tieâu thuï lôùn nhaát trong thaùng. 10. Tìm möùc tieâu thuï trung bình trong thaùng. 11. Xeáp theo thöù töï taêng daàn cuûa coät TOÅNG COÄNG Baøi Taäp Excel Trang 6 Nhaäp döõ lieäu, thöïc hieän tính toaùn vaø löu baøi vôùi teân TenAnhChi_Ex5.xls THOÁNG KEÂ HAØNG NHAÄP - Thaùng 10 /2003 Tyû giaù USD 15,500 STT LOÂ HAØNG KHAÙCH HAØNG MAÕ HAØNG ÑGIAÙ (USD) SLÖÔÏNG (Taán) TRÒ GIAÙ (USD) HUEÂ HOÀNG (USD) PHÍ VC (USD) THUEÁ (USD) THAØNH TIEÀN (VNÑ) 1 H01A N01 233 2 L02A C02 32 3 H03S N01 853 4 L04R C03 26 5 L03R C01 568 6 H04A N03 253 7 L12S N01 78 TOÅNG COÄNG Chuù yù: Giaù trò 15500 cuûa Tyû giaù USD phaûi nhaäp vaøo moät oâ rieâng. Theâm coät MAÕKH vaøo sau coät LOÂ HAØNG. Theâm coät MH vaøo sau coät MAÕ HAØNG. Theâm coät VC vaøo sau coät MAÕKH. Thöïc hieän tính toaùn cho caùc coät coøn troáng: 1. MAÕKH = Kyù töï ñaàu cuûa LOÂ HAØNG. 2. MH = Kyù töï ñaàu cuûa MAÕ HAØNG. 3. VC = Kyù töï sau cuûa LOÂ HAØNG. 4. Khaùch haøng ñöôïc tính:  KHAÙCH HAØNG = Haø Noäi neáu MAÕKH = H  KHAÙCH HAØNG = Laâm Ñoàng neáu MAÕKH = L 5. Ñôn giaù ñöôïc tính nhö sau:  ÑÔN GIAÙ = 2000 neáu MH = N (nho)  ÑÔN GIAÙ = 2500 neáu MH = C (caøpheâ) 6. TRÒ GIAÙ = ÑÔN GIAÙ * SOÁ LÖÔÏNG 7. HUEÂ HOÀNG ñöôïc tính nhö sau:  HUEÂ HOÀNG = 0.6% * TRÒ GIAÙ ñoái vôùi caùc loâ haøng coù MH = N  HUEÂ HOÀNG = 0 ñoái vôùi caùc loâ haøng coøn laïi 8. PHÍ VAÄN CHUYEÅN = SOÁ LÖÔÏNG * GIAÙ CÖÔÙC. Trong ñoù:  GIAÙ CÖÔÙC = 3 neáu VC = A (maùy bay)  GIAÙ CÖÔÙC = 1.2 neáu VC = R (xe taûi)  GIAÙ CÖÔÙC = 0.8 neáu VC = S (taøu thuyû). 9. Thueá ñöôïc tính:  THUEÁ = 5% * TRÒ GIAÙ neáu TRÒ GIAÙ < 20,000  THUEÁ = 4% * TRÒ GIAÙ trong tröôøng hôïp ngöôïc laïi. 10. THAØNH TIEÀN = (TRÒ GIAÙ + HUEÂ HOÀNG + PHÍ VC + THUEÁ) * TYÛ GIAÙ USD vaø ñöôïc laøm troøn ñeán haøng ngaøn. 11. Tính TOÅNG COÄNG cuûa coät THAØNH TIEÀN. TOÅNG COÄNG ñöôïc laøm troøn ñeán haøng ngaøn 12. Saép xeáp theo thöù töï taêng daàn cuûa coät THAØNH TIEÀN 13. Ñònh daïng caùc coät soá coù daáu taùch haøng ngaøn, trang trí vaø keû khung baûng tính. Baøi Taäp Excel Trang 7 Baøi taäp 6 BAÛNG TÍNH TIEÀN ÑIEÄN Thaùng 10/2003 Ñôn giaù 800 STT LOAÏI SD CHÆ SOÁ ÑAÀU CHÆ SOÁ CUOÁI HEÄ SOÁ THAØNH TIEÀN PHUÏ TROÄI COÄNG 1 KD 34 62 2 NN 58 350 3 SX 90 150 4 CN 150 700 5 KD 400 650 6 SX 50 90 7 KD 345 546 8 NN 454 511 9 NN 786 904 Toång coäng Trung bình Cao nhaát Thaáp nhaát DOØ HEÄ SOÁ LOAÏI SD HEÄ SOÁ LOAÏI Löu yù: Trò 500 cuûa ÑÔN GIAÙ phaûi nhaäp vaøo moät oâ bieät laäp. 1. Theâm vaøo coät LOAÏI sau coät LOAÏI SD. LOAÏI ñöôïc doø tìm trong baûng DOØ HEÄ SOÁ döïa vaøo LOAÏI SD. 2. Heä soá ñöôïc doø tìm trong BAÛNG DOØ HEÄ SOÁ, döïa vaøo LOAÏI SD. NN 1 Noâng Nghieäp KD 3 Kinh Doanh SX 2.5 Saûn Xuaát CN 2 Coâng Nghieäp 3. Theâm vaøo coät KW sau coät HEÄ SOÁ. KW laø ñònh möùc tieâu thuï ñieän trong thaùng vaø = CHÆ SOÁ CUOÁI - CHÆ SOÁ ÑAÀU. 4. THAØNH TIEÀN = KW * HEÄ SOÁ * ÑÔN GIAÙ. 5. PHUÏ TROÄI ñöôïc tính:  PHUÏ TROÄI = 0 Neáu (CHÆ SOÁ CUOÁI - CHÆ SOÁ ÑAÀU ) <50  PHUÏ TROÄI = THAØNH TIEÀN * 50% Neáu ngöôïc laïi. 6. COÄNG = THAØNH TIEÀN + PHUÏ TROÄI vaø ñöôïc laøm troøn ñeán haøng traêm. 7. TÍNH TOÅNG COÄNG, TRUNG BÌNH, CAO NHAÁT, THAÁP NHAÁT cho caùc coät . 8. Xeáp theo thöù töï giaûm daàn cuûa coät COÄNG. 9. Ñònh daïng caùc coät soá coù daáu taùch haøng ngaøn. Keû khung vaø trang trí baûng tính. Baøi Taäp Excel Trang 8 Baøi 7: Nhaäp döõ lieäu, thöïc hieän tính toaùn vaø löu baøi vôùi teân TenAnhChi_Ex7.xls BAÛNG THANH TOAÙN LÖÔNG THAÙNG 10/2003 Heä soá löông: 120 STT MAÕ NV HOÏ & TEÂN LCB NAÊM VAØO NGAØY COÂNG LÖÔNG PHUÏ CAÁP ÑOÄC HAÏI THÖÏC LAÕNH H01 25 D01 24 D02 28 S02 22 D03 29 Toång coäng DANH SAÙCH NHAÂN VIEÂN MAÕ NV HOÏ & TEÂN LCB NAÊM VAØO LAØM H01 Traàn Thaùi 410 1992 D01 Trònh Saâm 350 1989 D02 Traàn Bình 300 1991 D03 Chaâu Haø 280 1997 S02 Phan Tieán 300 1995 1. Nhaäp döõ lieäu cho baûng tính vaø baûng DANH SAÙCH NHAÂN VIEÂN nhö treân. 2. Caùc coät HOÏ & TEÂN, LCB, NAÊM VAØO LAØM doø tìm trong baûng danh saùch nhaân vieân, döïa vaøo MAÕNV. 3. LÖÔNG = LCB * NGAØYCOÂNG * HEÄ SOÁLÖÔNG. 4. PHUÏ CAÁP ÑOÄC HAÏI ñöôïc tính döïa vaøo kyù töï ñaàu cuûa MAÕ NV nhö sau:  Neáu kyù töï ñaàu cuûa MAÕNV laø D (Ñoäc haïi ) thì phuï caáp ÑH = 30*LÖÔNG.  Coøn ngoaøi ra thì khoâng phuï caáp ñoäc haïi. 5. THÖÏC LAÕNH = LÖÔNG + PHUÏ CAÁP ÑOÄC HAÏI 6. TOÅNG COÄNG = Coäng cuûa coät THÖÏC LAÕNH. 7. Trang trí vaø keû khung baûng tính – Ñònh daïng caùc coät döõ lieäu soá coù daáu taùch haøng ngaøn. 8. Caùc coät STT, MAÕ NV, LCB, NAÊM VAØO, NGAØY COÂNG ñöôïc canh giöõa. Chuù yù: Giaù trò 120 cuûa Heä soá löông phaûi nhaäp vaøo moät oâ rieâng. Baøi Taäp Excel Trang 9 Baøi 1: Nhaäp döõ lieäu, thöïc hieän tính toaùn vaø löu baøi vôùi teân TenAnhChi_2.xls BAÛNG THANH TOAÙN LÖÔNG THAÙNG 10/2003 Heä soá löông: 120 STT MAÕ NV HOÏ & TEÂN LCB NAÊM VAØO NGAØY COÂNG LÖÔNG PHUÏ CAÁP ÑOÄC HAÏI THÖÏC LAÕNH H01 25 D01 24 D02 28 S02 22 D03 29 Toång coäng Tính trung bình DANH SAÙCH NHAÂN VIEÂN MAÕ NV HOÏ & TEÂN LCB NAÊM VAØO LAØM H01 Traàn Thaùi 410 1992 D01 Trònh Saâm 350 1989 D02 Traàn Bình 300 1991 D03 Chaâu Haø 280 1997 S02 Phan Tieán 300 1995 1. Nhaäp döõ lieäu cho baûng tính vaø baûng DANH SAÙCH NHAÂN VIEÂN nhö treân. 2. Caùc coät HOÏ & TEÂN, LCB, NAÊM VAØO LAØM doø tìm trong baûng danh saùch nhaân vieân, döïa vaøo MAÕNV. 3. LÖÔNG = LCB*NGAØY COÂNG*HEÄ SOÁ LÖÔNG. 4. PHUÏ CAÁP ÑOÄC HAÏI ñöôïc tính döïa vaøo kyù töï ñaàu cuûa MAÕ NV nhö sau:  Neáu kyù töï ñaàu cuûa MAÕNV laø D (Ñoäc haïi ) thì phuï caáp ÑH = 30*LÖÔNG.  Coøn ngoaøi ra thì khoâng phuï caáp ñoäc haïi. 5. THÖÏC LAÕNH = LÖÔNG + PHUÏ CAÁP ÑOÄC HAÏI 6. TOÅNG COÄNG = Coäng cuûa coät THÖÏC LAÕNH. 7. Trang trí vaø keû khung baûng tính – Ñònh daïng caùc coät döõ lieäu soá coù daáu taùch haøng ngaøn. 8. Caùc coät STT, MAÕ NV, LCB, NAÊM VAØO, NGAØY COÂNG ñöôïc canh giöõa. 9. Tính giaù trò trung bình cuûa caùc coät LÖÔNG, PHUÏ CAÁP ÑOÄC HAÏI, THÖÏC LAÕNH 10. Döïa vaøo keát quaû cuûa caâu 9, veõ bieåu ñoà bieåu dieãn keát quaû trung bình cuûa caùc LÖÔNG, PHUÏ CAÁP ÑOÄC HAÏI, THÖÏC LAÕNH Chuù yù: Giaù trò 120 cuûa Heä soá löông phaûi nhaäp vaøo moät oâ rieâng. Baøi Taäp Excel Trang 10 Caâu 8: THOÁNG KEÂ TAØI CHÍNH THAÙNG 10/2003 STT MAÕ BOÄ PHAÄN BOÄ PHAÄN NHAÂN SÖÏ DÖÏ KIEÁN CHI PHÍ THÖÏC THÖÔÛNG LÖÔNG BÌNH QUAÂN 1 CN 14000000 2 QT 6500000 3 BV 3000000 4 BT 1700000 5 TP 6000000 6 CN 5800000 7 CN 6200000 Toång coäng BAÛNG DOØ MAÕ BOÄ PHAÄN NHAÂN SÖÏ CHI PHÍ DÖÏ KIEÁN QT Quaûn Trò 3 6000000 CN Coâng Nhaân 25 15000000 BV Baûo Veä 5 3000000 BT Baûo Trì 2 2000000 TP Tröôûng Phoøng 2 5000000  Neáu CHI PHÍ THÖÏC < CHI PHÍ DÖÏ KIEÁN THÖÔÛNG = 10% * CHI PHÍ DÖÏ KIEÁN.  Ngoaøi ra Khoâng thöôûng. 5. LÖÔNG BÌNH QUAÂN = (CHI PHÍ THÖÏC + THÖÔÛNG ) / NHAÂN SÖÏ. LÖÔNG BÌNH QUAÂN ñöôïc laøm troøn ñeán haøng ngaøn. 6. Tính toång coäng cuûa caùc coät : NHAÂN SÖÏ, CHI PHÍ DÖÏ KIEÁN, CHI PHÍ THÖÏC, THÖÔÛNG. 7. Xeáp laïi baûng tính theo thöù töï giaûm daàn cuûa coät LÖÔNG BÌNH QUAÂN. 8. Ñònh daïng caùc coät soá coù daáu taùch haøng ngaøn. Trang trí vaø keû khung baûng tính. 9. Tính giaù trò trung bình cuûa coät CHI PHÍ THÖÏC, THÖÔÛNG, LÖÔNG BÌNH QUAÂN 10. Döïavaøo keát quaû cuûa caâu 9, veõ bieåu ñoà. 11. Löu file vôùi teân BT8 1. Laäp BAÛNG THOÁNG KEÂ vaø BAÛNG DOØ nhö treân. 2. Theâm coät BOÄ PHAÄN vaøo sau coät MAÕ BOÄ PHAÄN. 3.BOÄ PHAÄN, NHAÂN SÖÏ vaø CHI PHÍ DÖÏ KIEÁN ñöôïc doø tìm trong BAÛNG DOØ, döïa vaøo MAÕ BOÄ PHAÄN. 4. THÖÔÛNG ñöôïc tính nhö sau: Baøi Taäp Excel Trang 11 BAÛNG LÖÔNG NHAÂN VIEÂN THAÙNG 02/2003 STT HOÏ VAØ TEÂN CHÖÙC VUÏ NGAØY COÂNG MAÕ KT SOÁ CON PCKV 1 Nguyeãn thò Cuùc TP 26 B 0 21500 2 Traàn vaên Taâm PP 25 C 2 25000 3 Nguyeãn vaên Khang NV 24 D 1 35000 4 Traàn Anh Huøng KT 25 A 3 13000 5 Nguyeãn thò Phöôïng NV 26 A 2 21000 6 Hoaøng thò Loan NV 23 C 2 21500 7 Nguyeãn Toá Nhö BV 20 C 3 13000 8 Nguyeãn thò Thu Cuùc TQ 18 D 1 30000 CHÖÙC VUÏ MÖÙC LÖÔNG HSCV MAÕKT A B C D TP 500 5 Tieàn KT 350000 250000 150000 0 PP 450 4 1. Haõy cheøn theâm vaøo baûng tính caùc coät:  Coät LÖÔNG vaøo tröôùc coät PCKV. Coät TAÏM ÖÙNG vaøo sau coät PCKV  Caùc coät PCGÑ, AÊN TRÖA, TOÅNG COÄNG vaøo tröôùc coät TAÏM ÖÙNG. Coät TIEÀN KT vaøo tröôùc coät TOÅNG COÄNG. KT 450 3 TQ 400 2 NV 350 1 BV 300 1 Coät THÖÏC LAÕNH vaøo sau coät TAÏM ÖÙNG. Yeâu caàu tính toaùn: 2. LÖÔNG = MÖÙC LÖÔNG * NGAØY COÂNG * HEÄ SOÁ CHÖÙC VUÏ. 3. TIEÀN AÊN TRÖA = 15000 * NGAØY COÂNG. 4. PHUÏ CAÁP GIA ÑÌNH = SOÁ CON * 100000 neáu SOÁ CON <=2 PHUÏ CAÁP GIA ÑÌNH = 150000 neáu SOÁ CON >2 5. TAÏM ÖÙNG= 300000 neáu MAÕ KHEN THÖÔÛNG laø A TAÏM ÖÙNG = 200000 neáu MAÕ KHEN THÖÔÛNG laø B TAÏM ÖÙNG = 100000 neáu MAÕ KHEN THÖÔÛNG laø C 6. TOÅNG COÄNG = LÖÔNG + PHUÏ CAÁP KHU VÖÏC + PHUÏ CAÁP GIA ÑÌNH + TIEÀN AÊN TRÖA + TIEÀN KHEN THÖÔÛNG. 7. THÖÏC LÓNH = TOÅNG COÄNG – TAÏM ÖÙNG. 8. Saép xeáp laïi baûng tính theo thöù töï taêng daàn cuûa coät CHÖÙC VUÏ. Neáu truøng coät CHÖÙC VUÏ thì saép xeáp theo thöù töï giaûm daàn cuûa coät THÖÏC LÓNH. 9. Laøm troøn coät THÖÏC LÓNH ñeán haøng traêm. 10. Tính toång cuûa coät THÖÏC LÓNH. 11. Keû khung vaø ñònh daïng baûng tính. 12. Tính toång thöïc lónh cuûa caùc nhaân vieân (CHÖÙC VUÏ = NV) 13. Löu file vôùi teân BT9.XLS Baøi Taäp Excel Trang 12 COÂNG TY ÑIEÄN LÖÏC KHU VÖÏC3 TOÅ PHUÏC VUÏ 18 BAÙO CAÙO DOANH THU THAÙNG 10/2003 STT HO VA TEN MDK MA_KH THANG TRUOC THANG NAY M_TT TIEN TT PHAT VUOT PHU THU VAT THANH TIEN 1 Nguyeãn thò Cuùc A120 2 230 650 2 Traàn vaên Taâm B345 1 652 1021 3 Nguyeãn vaên Khang A324 2 580 1089 4 Traàn Anh Huøng C450 1 569 780 5 Nguyeãn thò Phöôïng C908 1 234 654 6 Hoaøng thò Loan B128 2 348 762 7 Nguyeãn Toá Nhö B102 2 290 295 8 Nguyeãn thò Thu Cuùc C405 2 234 568 9 Traàn Thanh Huøng B334 1 345 546 10 Nguyeãn Theá Huy C540 2 142 346 Toång coäng: Möùc duøng ñieän trung bình: Toång Tieàn TT Möùc duøng ñieän cao nhaát: Toång Phaït vöôït Möùc duøng ñieän thaáp nhaát: Toång phuï thu Toång thu phaït vöôït möùc: Toång VAT Tính caùc coät coøn troáng trong baûng :  MDK : maõ soá ñieän keá phaân loaïi theo kí töï ñaàu.  MA_KH : phaân loaïi khaùch haøng ñeå aùp giaù ñieän  THANGTRUOC : chæ soá cuûa thaùng tröôùc.  THANGNAY : chæ soá cuûa thaùng naøy.  M_TT : möùc tieâu thuï ñieän trong thaùng cuûa khaùch haøng.  TIEN_TT : tieàn tieâu thuï ñieän naêng tính theo MA_KH , khaùch loaïi A ñôn giaù laø 400 ñ/kw, caùc loaïi khaùc ñôn giaù laø 600ñ/kw.  PHATVUOT : tieàn phaït tieâu thuï ñieän vöôït ñònh möùc, ñònh möùc ñieän chung laø 150kw, moãi kw vöôït möùc giaù 1000.  PHUTHU : phuï thu tieàn tieâu thuï ñieän naêng vaø thu baèng 2% toång cuûa TIEN_TT vaø PHATVUOT.  VAT : thueá giaù trò gia taêng,baèng 10% cuûa tieàn tieâu thuï vaø tieàn phaït vöôït.  THANHTIEN : laø toång tieàn khaùch haøng phaûi traû.  TOÅNG COÄNG = coäng cuûa coät THAØNH TIEÀN.  Saép xeáp theo thöù töï giaûm daàn cuûa coät THAØNHTIEÀN.  Tính toång cuûa TIEÀN TT, PHAÏT VÖÔÏT, PHUÏ THU, VAT.  Döïa vaøo keát quaû cuûa caâu treân, veõ bieåu ñoà bieåu dieãn toång caùc khoaûn thu cuûa coâng ty ñieän löïc  Löu taäp tin vôùi teân BaiExcel4.xls Baøi Taäp Excel Trang 13 BT10: Haõy duøng EXCEL nhaäp lieäu vaø thöïc hieän caùc yeâu caàu sau: TRUNG TAÂM KHAÛO THÍ KHAÙNH HOAØ KEÁT QUAÛ TUYEÅN SINH KHOAÙ NGAØY20/05/2003 STT Hoï vaø teân SBD Khuvuc Anh vaên Toaùn Tin hoïc ÑieåmKV ÑieåmCS Toång ñieåm Keát quaû Xeáp haïng Traàn Ñình Anh 346B 1 9 7 5 Baûo Ngoïc 890C 1 3 6 6 Buøi thi Thu Cuùc 342B 3 5 8 4 Nguyeãn thò Haèng 674B 2 7 8 4 Buøi vaên Maïnh 125B 3 6 7 7 Traàn thi Lan 345C 1 3 4 4 Buøi vaên Tuaán 781B 3 4 3 5 Traàn Bình Minh 201C 2 8 8 9 Traàn vaên Huøng 459A 2 5 4 3 Buøi Thanh 576C 2 7 6 4 Nguyeãn thò Thu 324A 1 7 8 4 Nguyeãn vaên Anh 231A 2 6 3 4 Baûng ñieåm öu tieân theo khu vöïc Baûng_2: Baûng ñieåm coäng theo gia ñình chính saùch Khuvuc Ñieåm Khuvuc Ñieåm trung bình Loaïi Ñieåm coäng 1 1.5 1 A 0 2 1 2 B 1 3 0 3 C 2 Baûng_1 1. Ñònh daïng baûng tính theo maãu cuûa caùc baûng con. 2. Tính caùc coät coøn laïi cuûa baûng tính theo caùc qui taéc sau:  SBD : soá baùo danh cuûa thí sinh, kí töï cuoái chæ loaïi gia ñình chính saùch .  ÑieåmKV : Ñieåm thöôûng theo khu vöïc cö truù cuûa thí sinh ( tính theo baûng 1).  ÑieåmCS: Ñieåm thöôûng theo loaïi gia ñình chính saùch.(tính theo baûng 2)  Toångñieåm: laø toång caùc moân thi ( Anh vaên heä soá 1, Toaùn vaø Tin hoïc coù heä soá 2) vaø caùc loaïi ñieåm thöôûng.  Keátquaû : Neáu Toång ñieåm beù hôn 25 thì ghi laø “Thi laïi”, ngöôïc laïi ghi “Ñaäu”. 3. Treân cô sôû keát quaû cuûa baûng 3 , haõy veõ bieåu ñoà bieåu dieãn tæ leä phaàn traêm caùc soá lieäu. 4. Xeáp thöù töï baûng tính theo teân thí sinh , neáu truøng teân thì saép theo toång ñieåm. 5. Ñònh daïng coät toång ñieåm: neáu ñieåm beù hôn 25 thì in maøu ñoû. Chuù yù : Nhaäp xong , cho löu thaønh taäp tin daïng .XLS ñaët taïi thö muïc goác ñóa C: Baøi Taäp Excel Trang 14 Baøi Taäp 11: Taïo moät Worksheet teân laø BT10.XLS ñaët taïi thö muïc goác ñóa C vaø nhaäp baûng tính sau( chuù yù canh tieâu ñeà vaø ñònh daïng theo maãu ) CHI CUÏC THUEÁ HUYEÄN HOAØNG HAØ TOÅ THUEÁ SOÁ 2 BAÙO CAÙO THUEÁ THAÙNG 10/2003 STT HOÏ VAØ TEÂN MKD DT HANG DTHU THUE VAT THUCTHU Nguyeãn thò Cuùc A120 2 24000 Traàn vaên Taâm B345 1 12000 Nguyeãn vaên Khang A324 3 550 Traàn Anh Huøng C450 1 28000 Nguyeãn thò Phöôïng C908 3 160000 Hoaøng thò Loan B128 2 19000 Nguyeãn Toá Nhö B102 2 21500 Nguyeãn thò Thu Cuùc C405 2 18900 Buøi vaên Ngoïc C210 3 31000 Traàn thò Ngoïc A908 1 290000 Nguyeãn vuõ Minh B127 1 45100 Ngoâ vaên Huøng C891 3 18300 1 2 3 Baûng loaïi haøng Baûng giaù thu THUE Baûng toång möùc thu theo loaïi haøng MKD Teân LOAÏI DT MÖÙC THU MKD Möùc thöïc thu A Noâng saûn 1 5% A B Dieän maùy 2 12% B C Haûi saûn 3 10% C Giaûi thích caùc coät: STT : Soá thöù töï , ñieàn töï ñoäng. MKD : maõ kinh doanh , kí töï ñaàu chæ loaïi maët haøng. DT : chæ ñoái töôïng tham gia kinh doanh. HANG : teân maët haøng( Baûng 1) DTHU : doanh thu trong thaùng , tính theo ñôn vò ngaøn ñoàng . THUE : thueá thu theo möùc doanh thu vaø thu theo loaïi ñoái töôïng ( baûng 2). VAT: Thueát trò giaù gia taêng , tính thaønh 10% cuûa doanh thu, ñôn vò tính laø ngaøn ñoàng. THUCTHU : tieàn thueá thöïc thu , bao goàm Thueá vaø VAT , laøm troøn ñeán haøng traêm. Yeâu caàu: 1.Ñieàn caùc döõ lieäu coøn troáng trong baûng. 2.Ñònh daïng baûng tính theo maãu. 3. Tính baûng soá 3. 4. Theo döõ lieäu baûng 3, haõy veõ moät bieåu ñoà bieåu dieãn möùc thu thueá theo loaïi haøng kinh doanh. Baøi Taäp Excel Trang 15 Baøi Taäp 11: Taïo moät Worksheet teân laø BT11.XLS ñaët taïi thö muïc goác ñóa C vaø nhaäp baûng tính sau( chuù yù canh tieâu ñeà vaø ñònh daïng theo maãu ) COÂNGTY BAÛO HIEÅM VYC NHOÙM ÑAÏI LYÙ 21 BAÙO CAÙO CHI THÖÔÛNG QUÍ 2/2004 STT HOÏ VAØ TEÂN CARD K_VUC S_HÑ D_SO H_H THUONG T_NHAN Nguyeãn thò Cuùc A120 2 50 23000 Traàn vaên Taâm B345 1 25 56000 Nguyeãn vaên Khang A324 3 18 6012 Traàn Anh Huøng C450 1 28 56000 Nguyeãn thò Phöôïng C908 3 40 23400 Hoaøng thò Loan B128 2 42 85000 Nguyeãn Toá Nhö B102 2 15 2900 Nguyeãn thò Thu Cuùc C405 2 30 23400 Buøi vaên Ngoïc C210 3 56 209000 Traàn thò Ngoïc A908 1 62 126000 Nguyeãn vuõ Minh B127 1 25 53000 Ngoâ vaên Huøng C891 3 23 12000 1 2 3 Möùc tính hoa hoàng Heä soá thöôûng Baûng thu nhaäp theo loaïi CARD K_VUC tæ leä LOAI CARD Heä soá LOAI Möùc thu 1 10% A 5% A 2 8% B 6% B 3 6% C 7% C Giaûi thích caùc coät: CARD : maõ soá cuûa nhaân vieân ñaïi lyù, kí töï ñaàu chæ loaïi ñaïi lyù. K_VUC : vuøng hoaït ñoäng cuûa nhaân vieân ñaïi lyùû. S_HÑ : soá löôïng hôïp ñoàng cuûa ñaïi lyù trong quí. D_SO: doanh soá chæ soá tieàn ñaïi lyù thu ñöôïc trong quí, tính theo ñôn vò ngaøn ñoàng. H_H : möùc hoa hoàng ñaïi lyù ñöôïc höôûng theo doanh soá .Möùc phaàn traêm tínhtheo khuvöïc hoaït ñoäng( baûng 1) THUONG : tieàn thöôûng cuûanhaân vieân ñaïi lyù theo doanh soá. Möùc thöôûng tính theo loaïi CARD vaø chæ thöôûng cho nhöõng nhaân vieân coù soá hôïp ñoàng khoâng beù hôn 30, Tieàn thöôûng tính ñeán haøng traêm.( Baûng 2 ) T_NHAN : soá tieàn thöïc nhaän cuûa nhaân vieân, goàm hoa hoàng vaø tieàn thöôûng. 1.Ñieàn caùc döõ lieäu coøn troáng trong baûng. 2.Ñònh daïng baûng tính theo maãu. 3. Tính baûng soá 3. 4. Theo döõ lieäu baûng 3, haõy veõ moät bieåu ñoà bieåu dieãn möùc thu nhaäp cuûa nhaân vieân ñaïi lyù theo loaïi CARD. Baøi Taäp Excel Trang 16 Phaàn 2 : Môû moät Worksheet teân laø AESBD.XLS ( Chuù yù : laø soá baùo danh cuûa anh ( chò ))ñaët taïi thö muïc goác ñóa C. Nhaäp baûng tính sau( chuù yù canh tieâu ñeà vaø ñònh daïng theo maãu ) TRUNG TAÂM XUÙC TIEÁN VIEÄC LAØM SAOMAI TOÅ ÑAØO TAÏO KEÁT QUAÛ TUYEÅN SINH TU NGHIEÄP STT HOÏ VAØ TEÂN SBD K_VUC N_V N_N U_T THUONG KETQUA Nguyeãn thò Phöôïng C908 3 7 8 Hoaøng thò Loan B128 2 6 4 Nguyeãn Toá Nhö B102 2 4 6 Buøi vaên Ngoïc C210 3 8 5 Traàn thò Ngoïc A908 1 7 9 Nguyeãn thò Cuùc A120 2 6 3 Traàn vaên Taâm B345 1 9 5 Nguyeãn vaên Khang A324 3 7 4 Traàn Anh Huøng C450 1 5 5 Nguyeãn thò Thu Cuùc C405 2 3 6 Nguyeãn vuõ Minh B127 1 4 7 Ngoâ vaên Huøng C891 3 7 6 1 2 3 Möùc ñieåm öu tieân Ñieåm thöôûng Ñieåm trung bình moân ngoaïi ngöõ K_VUC Ñieåm LOAI TS Ñieåm K_VUC Ñieåm 1 2 A 3 1 2 1 B 2 2 3 0 C 0 3 Giaûi thích caùc coät: SBD : soá baùo danh , kí töï ñaàu chæ loaïi thí sinh theo dieän chính saùch. K_VUC : vuøng cö truù cuûa thí sinhû. N_V : ñieåm moân nghieäp vuï cuûa thí sinh. N_N: ñieåm moân ngoaïi ngöõ cuûa thí sinh. U_T : ñieåm öu tieân coäng theâm , tính theo khu vöïc cö truù ( baûng 1) THUONG : ñieåm thöôûng cho thí sinh , tính theo dieän chính saùch xaõ hoäi.( Baûng 2 ) KETQUA : laø toång caùc loaïi ñieåm ,neáu keát quaû beù hôn 25 ñieåm thì in maøu ñoû. 1.Ñieàn caùc döõ lieäu coøn troáng trong baûng. 2.Ñònh daïng baûng tính theo maãu. 3. Tính baûng soá 3. 4. Theo döõ lieäu baûng 3, haõy veõ moät bieåu ñoà bieåu dieãn möùc ñieåm trung bình veà ngoaïi ngöõ cuûa caùc khu vöïc. 5. Trích ra moät baûng con chæ goàm caùc thí sinh ôû khu vöïc 3, xeáp thöù töï baûng naøy theo möùc toång keát quaû giaûm daàn.