intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE
 » 
 » 

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

Chia sẻ: Thanh Tuấn | Ngày: | Loại File: PDF | Số trang:33

Thêm vào BST
Báo xấu
882
lượt xem 213
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

"Bài tập thực hành MS Excel" giới thiệu đến bạn đọc kiến thức kèm theo bài tập thực hành với các nội dung như: Tổng quan về Microsoft Excel; một số khái niệm; một số thao tác cơ bản về dữ liệu; định dạng bảng tính; hàm; giới thiệu các hàm thông dụng; sắp xếp (sort) và filter (lọc).

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

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

  1. 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à Standar Toolbar: Thanh coâng cuï Formatting:Thanh ñònh daïng Formular: Thanh Coâng thöùc OÂ ñang choïn, oâ vuoâng nhoû Tieâu ñeà coät 256 coät beân döôùi goïi Fill Handle 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
  2. 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
  3. 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Ò dd/mm/yy 07/08/07 dd-mm-yy 07-08-07 dd/mm/yyyy 07/08/2007 - 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),
  4. 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: X2+Y3 X Y X+ Y X-Y X*Y X/Y (X+Y)% (X+Y)^2 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. 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. 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 Ngaøy: =Today() Hueâ Chuyeân Thaønh Stt Teân Haøng Soá Löôïng Ñôn Giaù Trò Giaù Hoàng Chôû Tieàn 1 Taäp 100 tr 3000 1950 2 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,123,3>0,“A”“B”) → TRUE =OR(1020,“ABC”=“a”) → FALSE c) Haøm IF: =IF(Meänh ñeà ñieàu kieän,Trò ñuùng, Trò sai) Cuù phaùp: - Cao Vaên An – Ñaëng Thanh Tuaán Trang 41
  6. 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Ù Soá Ngaøy Ngaøy Stt Khaùch Haøng Tieàn Traû Nhaän Haøng Traû Tieàn 1 Nguyeãn Thanh Thuaän 2500 10/03/05 20/03/05 2 Traàn Ngoïc Quyønh 3750 23/03/05 01/04/05 Leâ Taâm Minh 1500 12/03/05 25/03/05 Nguyeãn Thu Cuùc 8540 26/03/05 10/04/05 Laïi Vaên Sau 5000 21/03/05 15/04/05 Huyønh Minh Trí 4500 05/03/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ôï
  7. 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: =ABS(Bieåu Thöùc Soá) Cuù phaùp: - 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
  8. 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 Tyû Leä Thueá 10% Stt Maõ Haøng Teân Haøng Ñôn Giaù Soá Löôïng Trò Giaù Loaïi Haøng Nokia 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 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 Tyû Giaù : 15,650 Stt Teân Haøng Soá Löôïng Ñôn Giaù Chieát Khaáu Thueá Thaønh Tieàn Maùy Laïnh 15 540 TiVi 21” 25 320 Cao Vaên An – Ñaëng Thanh Tuaán Trang 44
  9. 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 MAÕ SOÁ LÖÔNG NGAØY PHUÏ CAÁP TAÏM COØN STT HOÏ VAØ TEÂN LÖÔNG CHÖÙC VUÏ CB COÂNG CHÖÙC VUÏ ÖÙNG LAÏI 1 Nguyeãn Vaên Thaønh TP 35,700 25 100,000 2 Leâ Thò Dung NV 21,000 24 70,000 3 Traàn Vaên Ñang NV 15,000 27 70,000 4 Phan Ñình Phuøng TP 37,400 27 100,000 5 Hoà Thò Thu NV 19,000 24 70,000 6 Löu Vaên Linh NV 20,000 25 70,000 7 Cao Nguyeät Kieàu NV 15,600 26 70,000 8 Döông Minh Quang NV 14,500 24 70,000 9 Ñaøo Caåm Tuù NV 25,000 24 70,000 10 Ngoâ Coâng Baèng PP 31,000 27 90,000 11 Leâ Phöông Nga GD 55,000 27 150,000 12 Traàn Haø Nam NV 19,800 24 70,000 13 Phan Thi Taâm PP 42,500 24 90,000 14 Toá Queá Chaâu NV 22,500 27 65,000 15 Ngoâ Ba TP 35,000 28 75,000 TOÅNG COÄNG: 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: “Giaùm Ñoác” • Neáu MAÕ SOÁ CHÖÙC VUÏ laø “GD” thì “Tröôûng Phoøng” • Neáu MAÕ SOÁ CHÖÙC VUÏ laø “TP” thì “Phoù Phoøng” • Neáu MAÕ SOÁ CHÖÙC VUÏ laø “PP” thìø “Nhaân vieân” • Neáu MAÕ SOÁ CHÖÙC VUÏ laø “NV” thì 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
  10. 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 3200 Khu Teân Ñôn Soá Phí Vaän Thaønh Stt Maõ Loaïi Thueá Vöïc Vaät Tö Giaù Löôïng Chuyeån Tieàn 1 N02X A 25 2 N01N B 50 X03N B 60 N02D C 30 X03D D 45 N01X C 40 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 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
  11. Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel vò trí laøm troøn =-2 Haøng traêm. =ROUND(1647.2578,-2) 1600 vò trí laøm troøn =-3 Haøng ngaøn. =ROUND(1647.2578,-3) 2000 j) Haøm RANK: =RANK(Trò doø,Danh saùch,caùch doø) - Cuù phaùp: - Coâng duïng: Tính thöù haïng cuûa trò doø trong phaïm vi danh saùch theo quy ñònh bôûi caùch doø. + Neáu caùch doø = 0: thöù haïng tính theo giaù trò soá giaûm daàn. + Neáu caùch doø = 1: thöù haïng tính theo giaù trò soá taêng daàn. + Neáu ta khoâng ñeå caùch doø, maëc nhieân seõ laáy caùch doø 0. - Ví duï: =RANK(A2,$A$2:$A$7,0)→ 2 k) Haøm COUNT: =COUNT(danh saùch) Cuù phaùp: - Coâng duïng: Ñeám soá oâ coù giaù trò soá, ngaøy, giôø trong danh saùch: - Ví duï: = COUNT(10,3,2) → 3; =COUNT(15,9,5,6,9,”anh”) → 5 l) Haøm COUNTA: =COUNTA(danh saùch) Cuù phaùp: - Coâng duïng: Ñeám soá oâ coù giaù trò soá, ngaøy, giôø vaø chuoãi trong danh saùch: - Ví duï: = COUNTA(10,3,2) → 3; =COUNT(15,9,5,6,9,”anh”) → 6 m) Haøm PRODUCT: =PRODUCT(Soá thöù 1, Soá thöù 2,… ,Soá thöù n) Cuù phaùp: - Coâng duïng: Tính tích caùc soá ñaõ choïn hoaëc tính tích caùc oâ trong danh saùch ñaõ choïn: - Ví duï: =PRODUCT(2,7,5) → 70; =PRODUCT(7,5) → 35 n) Haøm SUMPRODUCT =SUMPRODUCT(Array 1, Array 2,…, Array n) - Cuù phaùp: - Coâng duïng: Haøm naøy duøng ñeå tính Tích voâ höôùng cuûa caùc thaønh phaàn trong Array (maûng). - Ví duï: = SUMPRODUCT(A1:A3,B1:B3)=A1*B1+A2*B2+A3*B3= 122 o) Haøm COUNTIF: =COUNTIF(coät doø, ñieàu kieän) - Cuù phaùp: - Coâng duïng: Ñeám soá oâ trong danh saùch thoûa ñieàu kieän p) Haøm SUMIF: =SUMIF(coät doø, ñieàu kieän, coät coäng) - Cuù phaùp: Coâng duïng: Ñeám soá oâ trong danh saùch thoûa ñieàu kieän - Chuù yù: Ñieàu kieän cuûa Haøm COUNTIF, SUMIF - + Ñieàu kieän phaûi ñaët trong daáu nhaùy ñoâi (“ ) + Duøng toaùn töû so saùnh: =, >=,
  12. Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel Ví duï: Xeùt Baûng tính beân caïnh. - Ñeå tính soá ngöôøi coù thaâm nieân treân 5 naêm: C8=COUNTIF(C2:C7,”>5”) - Soá ngöôøi coù chöùc vuï laø “PP” seõ laø: B8 =COUNTIF(B2:B7,“PP”) → 2 - Toång tieàng löông cuûa ngöôøi coù chöùc vuï PP =SUMIF(B2:B7,“PP”,D2:D7) → 6000000 Hoaëc: =SUMIF(B2:B7,B3,D2:D7) → 6000000 - Tính Toång THAÂM NIEÂN cuûa nhöõng ngöôøi coù LÖÔNG töø 3000000 trôû leân = SUMIF(D2:D7,“>=3000000”,C2:C7) → 15 BAØI THÖÏC HAØNH EXCEL 10 KEÁT QUAÛ THI HOÏC KYØ II NAÊM NGOAÏI ÑIEÅM TOÅNG MaHV HOÏ VAØ TEÂN PHAÙI TOAÙN VAÊN SINH NGÖÕ THEÂM ÑIEÅM T0301 Traàn Vaên Ñang Nöõ 1970 9.00 6.50 7.00 N0302 Phan Ñình Phuøng Nöõ 1974 4.50 5.00 4.00 V0303 Hoà Thò Thu Nöõ 1968 6.50 9.00 6.50 T0304 Löu Vaên Linh Nam 1974 4.00 4.00 4.50 T0305 Cao Nguyeät Kieàu Nöõ 1974 10.00 8.00 9.25 V0306 Döông Minh Quang Nam 1974 4.00 5.25 6.00 V0307 Ñaøo Caåm Tuù Nöõ 1969 8.50 9.00 7.00 N0308 Ngoâ Coâng Baèng Nam 1973 7.00 5.00 8.00 N0309 Leâ Phöông Nga Nöõ 1971 4.00 5.00 6.25 V0310 Traàn Haø Nam Nam 1966 3.00 3.50 4.00 T0311 Phan Thi Taâm Nöõ 1960 6.00 6.00 8.00 V0312 Toá Queá Chaâu Nöõ 1968 5.00 4.00 7.50 T0313 Ngoâ Ba Nam 1973 4.00 3.00 3.50 Trung Bình ? ? ? ? ? Cao Nhaát ? ? ? ? ? Thaáp Nhaát ? ? ? ? ? Yeâu caàu: Giaûi thích: Maõ HV goàm 5 kyù töï: • Kyù töï ñaàu laø Ngaønh hoïc (T: Ngaønh Toaùn; N: Ngaønh Ngoaïi Ngöõ; V: Ngaønh Vaên) • 2 kyù töï tieáp theo laø 2 soá cuoái cuûa naêm nhaäp hoïc • 2 kyù töï cuoái laø soá thöù töï Thí duï: T0311: Hoïc vieân Ngaønh Toaùn; naêm nhaäp hoïc laø 2003 vaø soá thöù töï laù 11 1) Coät ÑIEÅM THEÂM ñöôïc tính theo tieâu chuaån: • Neáu hoïc vieân coù naêm sinh >1972, thì theâm 0.75 ñieåm • Ngöôïc laïi, neáu laø phaùi Nöõ thì theâm 0.5 ñieåm. • Ngoaøi ra khoâng ñöôïc theâm (ÑIEÅM THEÂM = 0) 2) Cheøn coät Ñieåm Trung Bình vaøo tröôùc coät Ñieåm Theâm vaø laäp coâng thöùc nhö sau: • Neáu hoïc vieân thuoäc ngaønh hoïc naøo thì ñieåm moân hoïc thuoäc ngaønh ñoù laø heä soá 2, caùc moân coøn laïi laø heä soá 1. Thí duï: Neáu hoïc vieân coù MaHV laø T0311 thì Ñieåm Trung Bình= (Ñieåm Toaùn * 2 + Ñieåm Vaên + Ñieåm Ngoaïi Ngöõ)/4 Cao Vaên An – Ñaëng Thanh Tuaán Trang 48
  13. Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel 3) Coät TOÅNG ÑIEÅM ñöôïc tính theo yeâu caàu: • Neáu hoïc vieân coù Ñieåm Trung Bình >=5 : Toång Ñieåm = Ñieåm Trung Bình • Ngöôïc laïi. Toång Ñieåm = Ñieåm Trung Bình + Ñieåm Theâm, nhöng khoâng vöôït quaù 5. 4) Cheøn coät KEÁT QUAÛ vaø tính: • Neáu ñieåm toång keát >=5 thì “Ñaït” • Ngöôïc laïi thì “Rôùt” 5) Laäp Baûng Thoáng Keâ sau: Ngaønh Hoïc Ñaït Rôùt Toaùn Vaên Ngoaïi Ngöõ BAØI THÖÏC HAØNH EXCEL 11 KEÁT QUAÛ THI CUOÁI KHOÙA – NAÊM 2004 3 Heä soá moân Toùan 1 Heä soá moân Vaên 2 Heä soá moân Ngoaïi Ngöõ Ngaøy Ñieåm Ñieåm Ñieåm Xeáp Stt Hoï vaø teân Phaùi Sinh Theâm Tr.Bình Loaïi Toùan Vaên Ng.Ngöõ Tröông Veä Nam 15/02/81 7 6 4 Traàn Minh Nam 14/05/82 8 7 7 Lyù Baïch Nöõ 17/03/84 5 7 6 Leâ Ngoïc Uyeân Nöõ 21/03/85 7 6 8 Vuõ Hoaøng Nam 19/08/80 9 7 6 Nguyeån Tyù Nam 15/06/82 3 6 5 Löu Ly Nöõ 26/05/83 6 4 7 Yeâu caàu: 1. Laäp coâng thöùc cho coät Ñieåm Theâm. Bieát raèng: Neáu Hoïc Vieân laø phaùi Nöõ vaø naêm sinh tröôùc naêm 1984, thì ñöôïc 0.75 ñieåm, Neáu sinh vaøo naêm 1982 thì ñöôïc 0.5 ñieåm. Coøn laïi thì baèng 0. 2. Tính Coät Ñieåm Tr.Bình = (Toaùn * HsToaùn+Vaên *HsVaên + Ng.Ngöõ *HsNg.Ngöõ)/Toång Heä Soá 3. Coät Xeáp Loaïi : • Neáu Ñieåm KQ >=9.5 Thì Xeáp Loaïi : “Xuaát Saéc” • Neáu Ñieåm KQ >=8.0 Thì Xeáp Loaïi : “Gioûi” • Neáu Ñieåm KQ >=7.0 Thì Xeáp Loaïi : “Khaù” • Neáu Ñieåm KQ >=5.0 Thì Xeáp Loaïi : “Tr.Bình” • Coøn laïi thì “Yeáu”. Bieát raèng Ñieåm KQ= Ñieåm Tr.Bình + Ñieåm Theâm 4. Theâm vaø laäp coâng thöùc cho coät Xeáp Haïng döïa vaøo Ñieåm Tr.Bình 5. Chuyeån sang Sheet2, Laäp coâng thöùc tính cho baûng sau: Toång Soá Hoïc Vieân Toång Soá Hoïc Vieân Nam Toång Soá Hoïc Vieân Nöõ Toång Soá Hoïc Vieân Ñaït Toång Soá Hoïc Vieân Khoâng Ñaït Cao Vaên An – Ñaëng Thanh Tuaán Trang 49
  14. Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel BAØI THÖÏC HAØNH EXCEL 12 BAÛNG THANH TOAÙN LÖÔNG THAÙNG 10/2004 Ngaøy Coâng Qui Ñònh 25 Heä Soá Löông 4525 Heä Soá Pheùp 0.75 Ngaøy Stt Hoï Vaø teân Löông CB Pheùp Löông Taïm ÖÙng Coøn Laõnh Coâng Nguyeãn Tyù Chuoät 520 25 Traàn Chuoät Ñoàng 425 26 Löu Meo Meo 390 20 3 Tröông Tyù Teøo 333 24 Hoà Bao Boá 275 23 1 Toång Coäng Cao Nhaát Thaáp Nhaát Yeâu caàu: 1. Löông = Löông Ngaøy + Löông Pheùp vaø laøm troøn ñeán haøng ngaøn. Trong ñoù: • Löông Ngaøy = Ngaøy Coâng * LöôngCB * Heä Soá Löông /Ngaøy Coâng Qui Ñònh • Löông Pheùp = Ngaøy Pheùp * Heä Soá Pheùp *LöôngCB * Heä Soá Löông / Ngaøy Coâng Qui Ñònh 2. Taïm ÖÙng = 1/3 Löông. Nhöng khoâng vöôït quaù 500.000 vaø ñöôïc laøm troøn ñeán haøng ngaøn 3. Cheøn theâm coät Phuï Caáp vaøo tröôùc coät Coøn Laõnh vaø ñöôïc tính nhö sau: Neáu Ngaøy Coâng >=25 thì Phuï Caáp = 50000, coøn laïi thì baèng khoâng. 4. Coøn Laõnh = Löông – Taïm ÖÙng + Phuï Caáp. 5. Laäp Baûng Thoáng Keâ Sau: Coù Ngaøy Coâng < 25 Coù Ngaøy Coâng >=25 Tính Toång Löông 2. Nhoùm haøm Text (Text functions) a) Haøm LEFT: =LEFT(Bieåu Thöùc chỗi, n) Cuù phaùp: - Coâng duïng: Caét ra n kyù töï tính töø beân traùi cuûa bieåu thöùc chuoãi: - Ví duï: =LEFT(“ABCDEF”,2) → AB ; = LEFT(“ABCDEF”,4) → ABCD b) Haøm RIGHT: =RIGHT(Bieåu Thöùc chỗi, n) Cuù phaùp: - Coâng duïng: Caét ra n kyù töï tính töø beân phaûi cuûa bieåu thöùc chuoãi. - Ví duï: =RIGHT(“ABCDEF”,2) → EF ; = RIGHT(“ABCDEF”,4) → DEF c) Haøm MID: =MID(Bieåu thöùc chuoãi, n1, n2) Cuù phaùp: - Coâng duïng: Caét chuoãi taïi vò trí n1 vaø coù soá löôïng n2 nhö ñaõ chæ ra. - Ví duï: =MID(“ABCDEF”,2,1) → B ; = MID(“ABCDEF”,4,2) → DE Cao Vaên An – Ñaëng Thanh Tuaán Trang 50
  15. Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel d) Haøm VALUE: =VALUE(Bieåu thöùc chuoãi coù daïng soá) Cuù phaùp: - Coâng duïng: Ñoåi bieåu thöùc chuoåi coù daïng soá thaønh soá töông öùng - Ví duï: =VALUE(LEFT(“123CD”,2) → 12 ; =VALUE(MID(“AB123CD”,4,2) → 23 e) Haøm UPPER: =UPPER(Bieåu thöùc chuoãi ) Cuù phaùp: - Coâng duïng: Ñoåi bieåu thöùc chuoãi sang chöõ IN HOA - Ví duï: =UPPER(“abcd”)= “ABCD” f) Haøm LOWER: =LOWER(Bieåu thöùc chuoãi ) Cuù phaùp: - Coâng duïng: Ñoåi bieåu thöùc chuoãi sang chöõ thöôøng - Ví duï: =LOWER(“ABCD”)= “abcd” e) Haøm PROPER: =PROPER(Bieåu thöùc chuoãi ) Cuù phaùp: - Coâng duïng: Vieát Hoa ñaàu töø trong bieåu thöùc chuoãi. - Ví duï: =PROPER(“ABCD mnpq” = “Abcd Mnpq” 3. Nhoùm haøm Thôøi gian (Time functions) a) Haøm NOW: =NOW() Cuù phaùp: - Coâng duïng: Theå hieän giaù trò Ngaøy, Giôø hieän haønh cuûa heä thoáng: - Ví duï: ngaøy hoâm nay 13/08/2007 vaøo luùc 13:45 =NOW()) → 13/08/2007 13:45 b) Haøm TODAY: Cuù phaùp: - =TODAY() Coâng duïng: Theå hieän giaù trò Ngaøy hieän haønh cuûa heä thoáng. - Ví duï: ngaøy hoâm nay 13/08/2007 vaøo luùc 13:45 =NOW()) → 13/08/2007 c) Haøm DAY: =DAY(döõ lieäu daïng ngaøy) Cuù phaùp: - Coâng duïng: Haøm naøy seõ traû veà Ngaøy cuûa döõ lieäu daïng ngaøy. - Ví duï: =DAY(A1) → 24 d) Haøm MONTH: =MONTH(döõ lieäu daïng ngaøy) Cuù phaùp: - Coâng duïng: Haøm naøy seõ traû veà Thaùng cuûa döõ lieäu daïng ngaøy. - Ví duï: =MONTH(A1) → 12 e) Haøm YEAR: =YEAR (döõ lieäu daïng ngaøy) Cuù phaùp: - Cao Vaên An – Ñaëng Thanh Tuaán Trang 51
  16. 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à Naêm cuûa döõ lieäu daïng ngaøy. - Ví duï: =YEAR(A1) → 2000 3. Nhoùm haøm tìm kieám vaø tham chieáu (Lookup and Reference Function) a) Haøm VLOOKUP: =VLOOKUP(trò doø, baûng doø, coät tham chieáu, caùch doø) - Cuù phaùp: Coâng duïng: Cho keát quûa laáy töø moät baûng cho tröôùc, trong ñoù: - Trò doø: ñöôïc laáy treân baûng chính laø caùi maø baïn ñem ra ñeå doø trong coät ñaàu tieân cuûa baûng - doø. - Baûng doø: coät ñaàu tieân chöùa trò töông öùng vôùi trò doø goïi coät 1, caùc coät coøn laïi coät 2, 3, ... coät laáy trò. - Coät tham chieáu: laø coät 2, 3, 4 … - Caùch doø: 0 laø doø chính xaùc trò doø baèng trò coù trong coät ñaàu tieân trong baûng doø (thöôøng chuoãi), doø 1(hay khoâng ghi caùch doø) laø do töông ñoái (thöôøng duøng cho soá) coät ñaàu tieân cuûa baûng doø phaûi xeáp theo thöù töï taêng daàn (haøm döøng taïi trò nhoû hôn hay baèng trò doø vaø gaàn trò doø nhaát) b) HaømHVLOOKUP: =HLOOKUP(trò doø, baûng doø, haøng tham chieáu, caùch doø) - Cuù phaùp vaø Coâng duïng: Gioáng nhö haøm VLOOKUP nhöng doø theo haøng Ví duï: - c) Haøm MATCH: =MATCH(Giaù trò doø, Baûng doø, caùch doø) - Cuù phaùp: - Coâng duïng: Haøm naøy coù coâng duïng töông töï nhö haøm VLOOKUP hoaëc HLOOKUP, tuy nhieân trò traû veà cuûa haøm naøy seõ laø chæ soá thöù töï cuûa phaàn töû ñöôïc tìm thaáy trong baûng doø chöù khoâng phaûi laø giaù trò nöõa. Thöôøng baûng doø chæ laø 1 coät hoaëc 1 haøng maø thoâi - Ví duï: Xeùt BAÛNG ÑIEÅM CHUAÅN cho beân döôùi, ta coù: = MATCH("2",D2:G2,0) → 3 (Vò trí “2” trong BAÛNG ÑIEÅM CHUAÅN laø vò trí thöù 3) d) Haøm INDEX: =INDEX(Baûng, Haøng tham chieáu, Coät tham chieáu) - Cuù phaùp: Coâng duïng: Haøm naøy seõ traû veà giaù trò cuûa cell coù trong baûng doø ñöôïc chæ ra bôûi Haøng - tham chieáu vaø Coät tham chieáu. Cao Vaên An – Ñaëng Thanh Tuaán Trang 52
  17. Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel Ví duï 1: Xeùt BAÛNG ÑIEÅM CHUAÅN cho beân döôùi, ta coù: - =INDEX($D$2:$G$7,3,2) → 20 Ví duï 2: Tìm Ñieåm chuaån döïa vaøo KHU VÖÏC vaø NGAØNH trong BAÛNG ÑIEÅM CHUAÅN - =INDEX($D$2:$G$7,MATCH(LEFT(A3,2),$D$2:$D$7,0),MATCH(RIGHT(A3),$D$2:$G $2,0)) 4. Nhoùm haøm cô sôû döõ lieäu (Database functions) - Coâng duïng: Ñaây laø nhöõng Haøm ñöôïc duøng ñeå tính cho moät cô sôû döõ lieäu, ñaëc bieät vì chuùng tính toaùn coù ñieàu kieän phöùc (töø 2 ñieàu kieän trôû leân). a) Haøm DSUM: Tính toång soá caùc giaù trò trong Field thoûa Criteria =DSUM(Database, Field, Criteria) b) Haøm DSUM: Tính Trung Bình Coäng caùc giaù trò trong Field thoûa Criteria =DAVERAGE(Database, Field, Criteria) c) Haøm DCOUNT: Ñeám soá oâ chöùa caùc giaù trò soá trong Field thoûa Criteria =DCOUNT(Database, Field, Criteria) d) Haøm DMAXT: Tính giaù trò lôùn nhaát trong Field thoûa Criteria =DMAX(Database, Field, Criteria) e) Haøm DMAXT: Tính giaù trò nhoû nhaát trong Field thoûa Criteria =DMIN(Database, Field, Criteria) Trong ñoù: - + Database : Toïa ñoä cuûa cô sôû döõ lieäu. + Field : Tröôøng (Vuøng) chòu taùc ñoäng cuûa Haøm (neân duøng soá thöù töï cuûa Field ñoù) + Criteria : Toïa ñoä cuûa Baûng ñieàu kieän. Ví duï: Xeùt baûng tính treân vaø yeâu caàu laøm nhö sau: - Cao Vaên An – Ñaëng Thanh Tuaán Trang 53
  18. Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel Tính Toång Löông cuûa Tröôûng Phoøng (TP), ta coù: - = DSUM($A$1:$G$8,7,$B$10:$B$11) = 5,342,000 Tính Trung bình coäng ngaøy coâng cuûa nhöõng nhaân vieân (NV) ôû phoøng Keá Hoaïch. - = DAVERAGE($A$1:$G$8,6,$C$10:$D$11) = 21,5 Tính Löông lôùn nhaát cuûa Tröôûng phoøng (TP) coù möùc löông töø 1800000 trôû leân. - = DMAX($A$1:$G$8,7,$E$10:$F$11) = 1,872,000 Tính Löông nhoû nhaát cuûa Tröôûng phoøng (TP) coù möùc löông töø 1800000 trôû leân. - = DMIN($A$1:$G$8,7,$E$10:$F$11) = 1,820,000 Ñeám soá ngöôøi coù chöùc vuï laø Tröôûng phoøng (TP) vaø möùc löông töø 1800000 trôû leân. - = DCOUNT($A$1:$G$8,7,$E$10:$F$11) = 2 VIII. SAÉP XEÁP (SORT) VAØ FILTER (LOÏC): a) Chöùc naêng sort: Saép xeáp cô sôû döõ lieäu theo 1 hay nhieàu Field (coät, tröôøng) naøo ñoù do ta chæ ñònh. Ñònh vò con troû trong phaïm vi cô sôû döõ lieäu hoaëc toâ khoái toaøn boä cô sôû döõ lieäu vaø duøng leänh - DATA\SORT. Luùc naøy treân maøn hình xuaát hieän hoäp thoaïi Sort. - Trong hoäp thoaïi Sort. + Sort by (khoùa chính): Duøng laøm khoùa saép xeáp. + Ascending: Saép xeáp taêng daàn. + Descending: Saép xeáp giaûm daàn. + Then by (khoùa phuï): Duøng laøm khoùa saép xeáp + My List has: choïn 1 trong 2 vò trí sau: - Header Row: Neáu trong phaïm vi saép xeáp coù doøng Header Row. - No Header Row: ngöôïc laïi. b) Chöùc Filter (Loïc): Loïc hoaëc ruùt trích caùc maãu tin thoûa ñieàu kieän do ta quy ñònh. Caùc maãu tin naøy ñöôïc loïc hoaëc laáy töø 1 cô sôû döõ lieäu. - Laáy baûng tính döôùi, loïc ra nhöõng ngöôøi ôû phoøng keá hoaïch. - Ñònh vò con troû trong phaïm vi cô sôû döõ lieäu vaø duøng leänh: DATA\Filter\Auto Filter. Luùc naøy treân maøn hình seõ xuaát hieän caùc kyù hieäu taïi goùc döôùi beân phaûi cuûa moãi Fieldname Click mouse taïi kyù hieäu loïc cuûa Fieldname naøo coù lieân quan ñeán ñieàu kieän loïc. Theo yeâu caàu - ôû ví duï naøy laø ta seõ choïn ñieàu kieän loïc laø “Keá Hoaïch”. Caùc meänh ñeà so saùnh trong hoäp thoaïi Custom Auto Filter: - + Equals: so saùnh baèng (=). + Does not equal: so saùnh khaùc (). + Is greater than: so saùnh lôùn hôn (>). + Is greater than or equal to: so saùnh lôùn hôn hoaëc baèng (>=). Cao Vaên An – Ñaëng Thanh Tuaán Trang 54
  19. Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel + Is less than: so saùnh beù thua (
  20. Tröôøng ÑHDL Vaên Lang – Khoa CNTT Baøi thöïc haønh MS Excel • Neáu Kyù töï beân phaûi cuûa Loâ Haøng laø “R” (xe taûi) thì Giaù Cöôùc = 1.2 • Neáu Kyù töï beân phaûi cuûa Loâ Haøng laø “S” (Taøu thuûy)) thì Giaù Cöôùc = 0.8 6. Thueá ñöôïc tính nhö sau: • Neáu Trò Giaù < 20,000 thì Thueá = 5% * Tri Giaù • Neáu Trò Giaù < 40,000 thì Thueá = 4% * Tri Giaù • Neáu Trò Giaù >= 40,000 thì Thueá = 2% * Tri Giaù 7. Thaønh Tieàn = (Trò Giaù +Hueâ Hoàng + Phí VC+ Thueá )* Tyû Giaù 8. Ñònh daïng caùc coät soá (tröø coät Thaønh Tieàn) theo daïng $ #,##0.00 9. Laäp BAÛNG THOÂNG KEÂ THEO KHAÙCH HAØNG Haø Noäi Laâm Ñoàng Caø Mau Toång Thaønh Tieàn 10. Laäp BAÛNG THOÂNG KEÂ THEO LOAÏI HAØNG Nho Caø pheâ Gaïo Toång Thaønh Tieàn BAØI THÖÏC HAØNH EXCEL 14 BAÛNG KEÂ CHI PHÍ THUEÂ KHAÙCH SAÏN Tyû Giaù 15,540 Maõ Ngaøy Ngaøy Soá Soá Stt Hoï Loùt Teân Thaønh Tieàn Phoøng Ñeán Ñi Tuaàn Ngaøy 1 Lyù Haûi Sôn T2C 10/04/05 14/04/05 2 Ñaøo Duy Huøng T3B 12/04/05 15/04/05 Vuõ Thanh Tuøng T4A 12/04/05 25/04/05 Nguyeãn Lan Vi T2B 15/04/05 25/04/05 Trònh Thöông Uyeân T4C 15/04/05 20/04/05 Haø Caåm Tuù T1D 20/04/05 30/04/05 Traàn Vaân Duy T3D 22/04/05 15/05/05 Nguyeãn Haûi Nam T4C 23/04/05 02/05/05 Leâ Duy Tuøng T2C 25/04/05 07/05/05 Toång Coäng Yeâu caàu: BIEÅU GIAÙ 1. Laäp coâng thöùc cho coät Soá Tuaàn = Phaàn nguyeân cuûa Soá Ñôn Giaù Ñôn Giaù Ngaøy ÔÛ chia cho 7. Loaïi Tuaàn Ngaøy 2. Laäp coâng thöùc cho coät Soá Ngaøy = phaàn dö cuûa Soá Ngaøy ÔÛ (USD) (USD) chia cho 7. A 55 10 Trong ñoù: Soá Ngaøy ÔÛ = Ngaøy Ñi – Ngaøy Ñeán+1 B 50 9 3. Cheøn theâm coät Ñôn Giaù Tuaàn vaø coät Ñôn Giaù Ngaøy vaøo C 45 8 beân traùi coät Thaønh Tieàn. Döïa vaøo kyù töï beân phaûi cuûa Maõ D 40 7 Phoøng doø tìm trong Baûng Bieåu Giaù ñeå laáy Ñôn Giaù Tuaàn hoaëc Ñôn Giaù Ngaøy. 4. Laäp coâng thöùc tính coät Thaønh Tieàn = (Soá Tieàn Tuaàn + Soá Tieàn Ngaøy) * Tyû Giaù Bieát raèng : • Soá Tieàn Tuaàn = Ñôn Giaù Tuaàn * Soá Tuaàn • Soá Tieàn Ngaøy = Ñôn Giaù Ngaøy * Soá Ngaøy. Nhöng Soá Tieàn Ngaøy khoâng theå vöôït quaù Ñôn Giaù Tuaàn. 5. Laäp baûng Thoáng Keâ Doanh Thu Theo Töøng Loaïi Phoøng Cao Vaên An – Ñaëng Thanh Tuaán Trang 56
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD

THÔNG TIN
TRỢ GIÚP
HỖ TRỢ KHÁCH HÀNG
Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn

Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2022-2032 TaiLieu.VN. All rights reserved.

 

Đồng bộ tài khoản
4=>1