Bài tập thực hành Microsoft Excel

Bài t p th c hành ậ ự MS Excel BÀI T P TH C HÀNH MS EXCELẬ Ự BÀI M Đ UỞ Ầ 1. Kh i đ ng Excel, quan sát màn hình, ghi nh n các thành ph n c a ch ngở ộ ậ ầ ủ ươ trình, vùng làm vi c...ệ 2. Th c hi n các thao tác c b n nh :ự ệ ơ ả ư - Nh p và ch nh s a d li u các ô tùy ý, di chuy n gi a các ô, ch n ô, c t,ậ ỉ ử ữ ệ ở ể ữ ọ ộ hàng (m t và nhi u), c t dán d li u, ch n toàn b và xóa,ộ ề ắ ữ ệ ọ ộ 3. K thu t đi n dãy s t đ ng:ỹ ậ ề ố ự ộ - T i ô A4 nh p s 1, ch n ô A4 làm xu t hi n nút vuông đi u khi n, nh nạ ậ ố ọ ấ ệ ề ể ấ Ctrl và kéo nút đi u khi n sang ph i đ n ô G4 th mouse, th Ctrl đ t o ra dãy sề ể ả ế ả ả ể ạ ố t 1 đ n 7. Sau đó l n l t kéo các ô t A4 đ n C4 (kéo 3 l n) xu ng đ n cácừ ế ầ ượ ừ ế ầ ố ế hàng A13, B13, C13 đ t o ra m t b ng các s . Nh n D-click vào nút đi u khi nể ạ ộ ả ố ấ ề ể c a ô D4, E4 và quan sát k t qu .ủ ế ả 4. Đi n công th c t đ ng:ề ứ ự ộ - T i ô F5 nh p công th c tính t ng các ô B5:E5 nh sau: đ u tiên nh p d u =,ạ ậ ứ ổ ư ầ ậ ấ dùng mouse ch vào ô B5, nh p d u +, ch vào ô C5... cho đ n khi đ c: =ỉ ậ ấ ỉ ế ượ B5+C5+D5+E5, nh n Enter đ hoàn t t.ấ ể ấ - Ch n ô F5, kéo nút đi u khi n xu ng đ n F13; sau đó ch n l i F5 và kéo sangọ ề ể ố ế ọ ạ G5, nh n D-click nút đi u khi n c a G5.ấ ở ề ể ủ - So sánh 2 thao tác trên. L n l t ch n các ô F5, F6, F7... và quan sát thanhở ầ ượ ọ ở công th c đ nh n th y s thay đ i t đ ng c a công th c tính. T ng t đ i v iứ ể ậ ấ ự ổ ự ộ ủ ứ ươ ự ố ớ các ô c t G.ở ộ 5. K khung vi n, tô bóngẻ ề - Ch n các ô t A4 đ n G13 t o khung vi n hai nét bên ngoài và 1 nét bên trong.ọ ừ ế ạ ề - Ch n A4:G4 (hàng đ u c a b ng) và tô màu tùy ý.ọ ầ ủ ả Cu i cùng ta có k t qu nh sau:ố ế ả ư Ngoài ra, ta có th th c hành thêm các l nh nh : ch nh s p các c t (theo các vể ự ệ ư ỉ ắ ộ ị trí trái, gi a, ph i) và ch nh n i dung c a m t ô ra gi a c a kho ng các ô (ch nữ ả ỉ ộ ủ ộ ữ ủ ả ọ kho ng s ch nh gi a, click vào bi u t ng )ả ẽ ỉ ữ ể ượ Đóng b ng tính (nh n Ctrl-F4, ho c ch n [File]Close) và m m t b ng tínhả ấ ặ ọ ở ộ ả m i (New) đ th c hành bài s 1.ớ ể ự ố ======================== Bài t p th c hành MS Excelậ ự Trang 1 Bài t p th c hành ậ ự MS Excel BÀI S 1Ố Kh i đ ng Excel, t m t Workbook m i hãy t o b ng d li u sau và th cở ộ ừ ộ ớ ạ ả ữ ệ ự hi n tu n t các yêu c u c a bài th c hành.ệ ầ ự ầ ủ ự STT H TÊNỌ PHÁI NGÀY SINH LCB L NGƯƠ CÒN L IẠ 1 An Nam 05/02/65 3.26 2 Th yủ Nữ 23/12/64 2.14 3 H ngươ Nữ 17/03/66 3.12 4 Hùng Nam 09/04/64 2.46 Câu 1. Nh p d li u (c t STT dùng k thu t đi n dãy s t đ ng)ậ ữ ệ ộ ỹ ậ ề ố ự ộ Câu 2. Chèn m t dòng tr c ng i tên H ng và nh p thêm d li u nh sau (đi uộ ướ ườ ươ ậ ữ ệ ư ề ch nh c t STT cho đúng):ỉ ộ 3 S nơ Nam 02/11/65 2.56 Câu 3. Chèn m t c t tr c c t Còn l i và nh p vào tên ô là T m ngộ ộ ướ ộ ạ ậ ạ ứ Th c hi n tính toán các c t nh sau:ự ệ ở ộ ư Câu 4. L ng b ng LCB nhân v i 144000ươ ằ ớ Câu 5. T m ng b ng 25% L ngạ ứ ằ ươ Câu 6. Còn l i b ng L ng tr T m ngạ ằ ươ ừ ạ ứ Câu 7. Trang hoàng và l u b ng tính v i tên là BTAP1.XLSư ả ớ Câu 8. Sau khi l u, đóng và m l i b ng tính đ ki m tra, đ ng th i th sư ở ạ ả ể ể ồ ờ ử ử d ng các l nh t o d ng nh : đ i font, t o khung vi n, tô màu, ch nh d ng...; cácụ ệ ạ ạ ư ổ ạ ề ỉ ạ l nh sao chép, c t dán d li u...ệ ắ ữ ệ Ki m tra l i các khái ni m "tham chi u t ng đ i", "tham chi u tuy t đ i" vàể ạ ệ ế ươ ố ế ệ ố dùng ph ng pháp đ t tên cho kho ng các ô đ đ t tên cho vùng b ng tính trên.ươ ặ ả ể ặ ả ở H ng d n th c hànhướ ẫ ự : 1. Th ng b t đ u t o b ng d li u t ô A3 tr đi. Trong khi nh p ngày c nườ ắ ầ ạ ả ữ ệ ừ ở ậ ầ l u ý, n u nh p ngày đúng d ng thì s t đ ng ch nh ph i, ng c l i c n ki m traư ế ậ ạ ẽ ự ộ ỉ ả ượ ạ ầ ể xem ngày có d ng mm/dd/yy hay d ng dd/mm/yy đ nh p cho đúng.ạ ạ ể ậ 2. Đ t con tr ô có STT là 3, nh n Mouse ph i và ch n Insert trong menu r iặ ỏ ở ấ ả ọ ơ xu ng sau đó ch n ti p Entire Row đ chèn 1 hàng (ho c ch n hàng b ng cáchố ọ ế ể ặ ọ ằ click vào s hi u hàng sau đó dùng l nh Insert đ chèn).ố ệ ệ ể 3. T ng t câu 2, đ t con tr ô T m ng, nh n mouse ph i và ch n Insert,ươ ự ặ ỏ ở ạ ứ ấ ả ọ sau đó ch n Entire Column đ chèn 1 c t.ọ ể ộ 4. Nh p công th c: = [LCB] * 144000, trong đó [LCB] là tham chi u đ n ô ch aậ ứ ế ế ứ LCB (dùng Mouse ch vào ô ch a giá tr LCB t ng ng v i hàng đang l p côngỉ ứ ị ươ ứ ớ ậ th c, không ph i ch vào ô có tên là LCB)ứ ả ỉ 5. Nh p công th c: = [L ng] * 25%ậ ứ ươ 6 Nh p công th c: = [L ng] - [T m ng]ậ ứ ươ ạ ứ STT HỌ TÊN PHÁI NGÀY SINH LCB L NGƯƠ T MẠ NGỨ CÒN L IẠ 1 An Nam 05/02/65 3.26 469440 117360 352080 2 Th yủ Nữ 23/12/64 2.14 308160 77040 231120 3 S nơ Nam 02/11/65 2.56 368640 92160 276480 4 H ngươ Nữ 17/03/66 3.12 449280 112320 336960 5 Hùng Nam 09/04/64 2.46 354240 88560 265680 Bài t p th c hành MS Excelậ ự Trang 2 Bài t p th c hành ậ ự MS Excel BÀI S 2aỐ S d ng các hàm IF, SUM, MAX, MIN, AVERAGE, ROUND…ử ụ B ng tính l ng Tháng 04 năm 2006ả ươ STT H tênọ Cvu L ngươ CB Ngày công L ngươ T m ngạ ứ Th c nh nự ậ 1 Linh TP 3.16 23 436080 145000 291080 (đ) 2 Vuong NV 2.44 25 366000 122000 244000 (đ) 3 Thuy PP 3.12 26 505440 150000 355440 (đ) 4 Yen GD 5.46 29 1081080 150000 931080 (đ) 5 Tien DV 2.56 30 537600 150000 387600 (đ) 6 Loi PGD 3.92 15 352800 118000 234800 (đ) 7 Hung NV 1.92 18 207360 69000 138360 (đ) 8 Tri NV 3.24 19 369360 123000 246360 (đ) 9 Loan TK 2.92 22 385440 128000 257440 (đ) 10 Nhung DV 3.14 26 508680 150000 358680 (đ) T ng c ngổ ộ 4749840 1305000 3444840 (đ) Max = 931080 Min = 138360 Average = 344484 Câu 1. Nh p d li u (c t STT dùng k thu t đi n dãy s t đ ng)ậ ữ ệ ộ ỹ ậ ề ố ự ộ Câu 2. Tính L ng = 6000 * L ng CB * Ngày côngươ ươ (Trong đó, n u ngày công > 25 thì t ngày 26 tr đi m i ngày đ c tính thành 2ế ừ ở ỗ ượ ngày, ví d : n u ngày công là 27 thì s d ra là 2 ngày đ c tính thành 4 ngày, doụ ế ố ư ượ đó t ng ngày s là 25+4)ổ ẽ Câu 3. T m ng = 1/3 L ng (nh ng t i đa ch cho t m ng 150000đ)ạ ứ ươ ư ố ỉ ạ ứ Câu 4. Th c nh n = L ng – T m ngự ậ ươ ạ ứ Câu 5. Tính t ng c ng các c t L ng, T m ng, Th c nh nổ ộ ộ ươ ạ ứ ự ậ Câu 6. Cho bi t giá tr cao nh t, th p nh t, trung bình c a Th c nh nế ị ấ ấ ấ ủ ự ậ Câu 7. Làm tròn c t T m ng đ n ngàn đ ngộ ạ ứ ế ồ Câu 8. Đ nh d ng các c t ti n theo d ng ti n (đ) đ ng Vi t Namị ạ ộ ề ạ ề ồ ệ Câu 9. Trang trí, l u b ng tính v i tên là BTAP2.XLSư ả ớ H ng d n th c hành:ướ ẫ ự 1. L u ý các vùng tô xám là k t qu sau khi tính toán, dùng đ ki m tra.ư ế ả ể ể 2. [L ng] = 6000 * [L ng CB] * IF([Ngày công]<=25, [Ngày công], 25 +ươ ươ ([Ngày công]-25)*2) Công th c trên đ c hi u là: N u ngày công không l n h n 25 thì tr l i Ngàyứ ượ ể ế ớ ơ ả ạ công, ng c l i thì l y 25 c ng v i s Ngày công đ c tính g p đôi.ượ ạ ấ ộ ớ ố ượ ấ 3. [T m ng] = IF([L ng]/3 >150000, 150000, [L ng]/3)ạ ứ ươ ươ N u m t ph n ba s l ng không v t quá 150000 thì cho t m ng đúng b nế ộ ầ ố ươ ượ ạ ứ ằ m t ph n ba l ng, ng c l i ch cho t m ng 150000.ộ ầ ươ ượ ạ ỉ ạ ứ Bài t p th c hành MS Excelậ ự Trang 3 Bài t p th c hành ậ ự MS Excel 5. S d ng hàm SUM, nh p công th c "= SUM(" và dùng mouse đ ch n vùngử ụ ậ ứ ể ọ c n tính t ng. Sau đó kéo sang các ô bên c nh đ t o công th c t đ ng cho cácầ ổ ạ ể ạ ứ ự ộ t ng k ti p.ổ ế ế 6. Dùng các hàm MAX, MIN và AVERAGE 7. S a công th c trong c t T m ng thành = ROUND([IF(….)], -3). L u ý giá trử ứ ộ ạ ứ ư ị -3 dùng đ làm tròn đ n hàng ngàn (3 s 0). Sau khi s a xong, kéo xu ng phía d iể ế ố ử ố ướ đ thay th .ể ế 8. Ch n ô, dùng menu [Format]\Cells và s d ng đ nh d ng: 0 "(đ)", ho c dùngọ ử ụ ị ạ ặ đ nh d ng #.##0 "(đ)" đ t o thêm d u phân cách nhóm 3 s .ị ạ ể ạ ấ ố 9. T o khung vi n, tô bóng. Dòng tiêu đ nh p c t A, sau đó dùng mouse ch nạ ề ề ậ ở ộ ọ m t vùng ngang qua các ô đ ch nh tiêu đ ra gi a các ô.ộ ể ỉ ề ữ - Ch n Sheet2 và hoàn t t bài t p t ng t s 2b (không có ph n h ng d n).ọ ấ ậ ươ ự ố ầ ướ ẫ ==================== BÀI S 2bỐ ST T TÊN LO IẠ SỐ L NGƯỢ Đ NƠ GIÁ1 Đ NƠ GIÁ2 THÀNH TI NỀ THUẾ T NGỔ C NGỘ 1 Toán 1 20 3000 3200 60000 6000 66000 2 Lý 1 15 2500 2800 37500 3750 41250 3 Hóa 2 10 4000 4500 45000 4500 49500 4 Sinh 1 5 2000 2300 10000 1000 11000 5 S ử 1 10 3500 3800 35000 3500 38500 6 Đ aị 2 25 2500 2600 65000 6500 71500 7 Văn 1 15 4500 4700 67500 6750 74250 8 Ti ng Vi tế ệ 1 10 3000 3300 30000 3000 33000 9 Anh văn 2 20 5000 5200 104000 10400 114400 10 Giáo d cụ 1 15 4000 4300 60000 6000 66000 SUM 514000 51400 565400 MIN 10000 1000 11000 MAX 104000 10400 114400 AVER AGE 51400 5140 56540 Câu 1 Tính thành ti n b ng s l ng nhân đ n giá tùy thu c vào lo i (n u lo i 1ề ằ ố ượ ơ ộ ạ ế ạ thì đ n giá 1, ng c l i n u là lo i 2 thì tính theo đ n giá 2)ơ ượ ạ ế ạ ơ Câu 2 Tính Thu b ng 10% c a Thành ti nế ằ ủ ề Câu 3 Tính t ng c ng b ng t ng c a thành ti n v i thuổ ộ ằ ổ ủ ề ớ ế Câu 4 Tính t ng, giá tr l n nh t, nh nh t, trung bình c a các c t THTI N,ổ ị ớ ấ ỏ ấ ủ ộ Ề THU và TC NG.Ế Ộ BÀI S 3Ố S d ng hàm IF, rèn luy n vi c l p các m nh đ logic làm đi u ki n; khử ụ ệ ệ ậ ệ ề ề ệ ả năng l ng nhau c a các hàm IF. Dùng hàm RANK đ s p th t (cách dùng thamồ ủ ể ắ ứ ự chi u tuy t đ i).ế ệ ố Bài t p th c hành MS Excelậ ự Trang 4 Bài t p th c hành ậ ự MS Excel B NG ĐI MẢ Ể SAP HOTEN KHOI TOAN VAN NNGU TONG KQUA XLOAI 1 THONG A 9 10 8 36 DAU GIOI 10 SI D 3 2 5 15 ROT X 5 SANG C 7 6 7 26 DAU KHA 7 DAN D 2 9 6 23 ROT X 3 TRI C 6 8 8 30 DAU KHA 4 KHA A 7 7 8 29 DAU KHA 8 TAM A 5 6 4 20 DAU TBINH 2 GIOI D 9 8 8 33 DAU GIOI 6 DAT C 5 6 8 25 DAU TBINH 8 YEU C 1 8 3 20 ROT X Câu 1. Nh p d li uậ ữ ệ Câu 2. C t t ng đ c tính căn c vào KHOI: n u KHOI A thì đi m TOAN nhânộ ổ ượ ứ ế ể 2; KHOI D đi m NNGU nhân 2; KHOI C đi m VAN nhân 2.ể ể Câu 3. C t KQUA đ c tính nh sau: là Đ U đ i v i TONG l n h n ho c b ngộ ượ ư Ậ ố ớ ớ ơ ặ ằ 20, nh ng không có đi m môn nào d i 3; ng c l i KQUA s là R Tư ể ướ ượ ạ ẽ Ớ Câu 4. C t XLOAI đ c tính n u KQUA là Đ U và căn c vào TONG:ộ ượ ế Ậ ứ - Lo i GIOI n u TONG >= 32ạ ế - Lo i KHA n u TONG t 26 đ n d i 32ạ ế ừ ế ướ - Lo i TRBINH đ i v i tr ng h p còn l iạ ố ớ ườ ợ ạ + N u KQUA là R T thì đánh d u X vào v trí XLOAIế Ớ ấ ị Câu 5. C t SAP x p th t theo c t TONG v i đi m cao nh t là 1.ộ ế ứ ự ộ ớ ể ấ Câu 6. Trang trí và l u b ng tính v i tên BTAP3.XLSư ả ớ H ng d n th c hành:ướ ẫ ự 2. S d ng hàm IF, ta có công th c sau:ử ụ ứ IF([KHOI]="A",[TOAN]*2+[VAN]+[NNGU],IF([KHOI]=D,[TOAN]+[VAN]+ [NNGU]*2, [TOAN]+[VAN]*2+[NNGU])) L u ý r ng, tùy thu c vào KHOI đ quy t đ nh s nhân h s 2 đ i v i mônư ằ ộ ể ế ị ẽ ệ ố ố ớ thích h p. Có 3 đi u ki n (A, B, C) nên có 2 hàm IF l ng nhau; n u có n đi u ki nợ ề ệ ồ ế ề ệ thì s có n-1 hàm IF l ng nhau. Các bài t p sau s s d ng các hàm tìm ki mẽ ồ ậ ở ẽ ử ụ ế HLOOKUP và VLOOKUP thay cho vi c dùng nhi u hàm IF l ng nhau.ệ ề ồ 3. Đi u ki n đ Đ u là: T ng đi m >= 20, và đi m c a t ng môn > 2. Ta sề ệ ể ậ ổ ể ể ủ ừ ử d ng IF v i hàm AND đ t o bi u th c đi u ki n:ụ ớ ể ạ ể ứ ề ệ IF(AND([TONG]>=20, [TOAN]>2, [VAN]>2, [NNGU]>2), "Đ U", "R T")Ậ Ớ 4. Vì KQUA ph i Đ U, nên đ u tiên c n ki m tra KQUA, sau đó m i xét đi mả Ậ ầ ầ ể ớ ể đ x p lo i:ể ế ạ IF([KQUA]="Đ U", IF([TONG]>=32, "GIOI", IF([TONG]>=26,Ậ "KHA","TRBINH")),"X") Trong công th c trên l u ý cách tính c n c a các giá tr .ứ ư ậ ủ ị 5. Dùng hàm RANK đ s p th t . Theo yêu c u s p theo th t gi m (đi mể ắ ứ ự ầ ắ ứ ự ả ể cao nh t có th h ng 1) nên ph ng th c s p b ng 0, ta có:ấ ứ ạ ươ ứ ắ ằ [SAP] = RANK([TONG], danh_sách_đi m, 0)ể Trong đó danh_sách_đi m là c t (g m 10 ô) ch a t ng đi m (trong bài là t ôể ộ ồ ứ ổ ể ừ có đi m 36 đ n ô cu i có đi m 20; sau khi dùng mouse ch n các ô xong, nh n F4ể ế ố ể ọ ấ Bài t p th c hành MS Excelậ ự Trang 5 Bài t p th c hành ậ ự MS Excel đ t o tham chi u tuy t đ i, n u không thì khi sao chép công th c xu ng d i sể ạ ế ệ ố ế ứ ố ướ ẽ gây ra l i).ỗ Trong câu này, ngoài vi c s d ng hàm RANK ta còn l u ý đ n cách dùng c aệ ử ụ ư ế ủ tham chi u tuy t đ i mà hàm RANK là m t trong s nh ng hàm th ng dùng ki uế ệ ố ộ ố ữ ườ ể tham chi u này. ế BÀI S 4aỐ Các hàm INT, MOD x lý s nguyên; các phép toán trên d li u ki u ngàyử ố ữ ệ ể (hi u c a 2 ngày), đ nh d ng ki u ngày. V n d ng hàm tìm ki m HLOOKUP đệ ủ ị ạ ể ậ ụ ế ể tìm m t giá tr tham gia vào quá trình tính toán.ộ ị KHÁCH S N TAM KỲẠ MS LPH NDEN NDI STU SNG TTUAN TNGAY THTIEN a1 A 06/12/95 06/15/95 0 3 0 đ 330,000 đ 330,000 đ a2 C 06/12/95 06/15/95 0 3 0 đ 225,000 đ 225,000 đ a3 C 06/12/95 06/21/95 1 2 500,000 đ 150,000 đ 650,000 đ a4 B 06/12/95 06/25/95 1 6 600,000 đ 540,000 đ 1,140,000 đ a5 B 06/12/95 06/28/95 2 2 1,200,000 đ 180,000 đ 1,380,000 đ a6 C 06/17/95 06/29/95 1 5 500,000 đ 375,000 đ 875,000 đ a7 A 07/01/95 07/03/95 0 2 0 đ 220,000 đ 220,000 đ a8 A 07/02/95 07/09/95 1 0 700,000 đ 0 đ 700,000 đ a9 C 07/25/95 08/10/95 2 2 1,000,000 đ 150,000 đ 1,150,000 đ a10 B 07/26/95 08/12/95 2 3 1,200,000 đ 270,000 đ 1,470,000 đ T ng c ng:ổ ộ 5,700,000 đ 2,440,000 đ 8,140,000 đ Lo i phòngạ A B C Đ n giá trên tu nơ ầ 700000 600000 500000 Đ n giá trên ngàyơ 110000 90000 75000 L u ý: Trong b ng d li u trên, STU, SNG là s tu n và s ngày l u trú c aư ả ữ ệ ố ầ ố ư ủ khách. TTUAN, TNGAY là ti n tr theo tu n và theo ngày (vì khách s n gi m giáề ọ ầ ạ ả đ i v i khách thuê phòng đăng ký theo tu n).ố ớ ầ Câu 1. T giá tr ngày đ n và ngày đi hãy tính s tu n (STU) và s ngày (SNG)ừ ị ế ố ầ ố l u trú (ví d : 12 ngày thì tính là 1 tu n và 5 ngày).ư ụ ầ Câu 2. D a vào b ng giá ti n theo t ng lo i A, B, C cho trên, hãy tính s ti nự ả ề ừ ạ ố ề theo tu n và theo ngày c a các khách trầ ủ ọ Câu 3. Tính TTIEN b ng t ng c a ti n tu n và ti n ngày; tính t ng c ng cho cácằ ổ ủ ề ầ ề ổ ộ c t TTUAN, TNGAY và TTIENộ Câu 4. Đ nh d ng cho các c t ngày đi và ngày đ n theo d ng năm có 4 ch s (víị ạ ộ ế ạ ữ ố d 1995) và đ nh d ng cho các c t ti n có d ng #,## đ.ụ ị ạ ộ ề ạ Câu 5. Trang trí và l u v i tên BTAP4.XLSư ớ H ng d n th c hành:ướ ẫ ự 1. M t tu n g m 7 ngày; do đó s tu n b ng ph n nguyên c a s ngày l u trúộ ầ ồ ố ầ ằ ầ ủ ố ư chia cho 7. S ngày sau khi tính tu n s là ph n d c a phép chia 7. Ta có côngố ầ ẽ ầ ư ủ th c tính nh sau:ứ ư [STU] = INT(([NDI]-[NDEN])/7) [SNG] = MOD([NDI]-[NDEN], 7) Bài t p th c hành MS Excelậ ự Trang 6 Bài t p th c hành ậ ự MS Excel 2. Đ bi t đ n giá (theo lo i phòng) ta dò tìm trong b ng giá, và vì b ng giá bể ế ơ ạ ả ả ố trí s li u theo chi u ngang nên ta dùng hàm HLOOKUP. Khi đó đ n giá ti n tu nố ệ ề ơ ề ầ hàng th 2 và đ n giá ti n ngày hàng th 3 c a b ng tìm. Ta có:ở ứ ơ ề ở ứ ủ ả [TTUAN] = [STU] * HLOOKUP([LPH], b ng_tìm, 2, 0)ả [TNGAY] = [SNG] * HLOOKUP([LPH], b ng_tìm, 3, 0)ả - Trong đó, b ng_tìm là kho ng g m 9 ô (có khung tô đ m) t ô có giá tr A đ nả ả ồ ậ ừ ị ế ô có ch a 75000. Dùng mouse đ ch n 9 ô này, sau đó nh n F4 đ t o tham chi uứ ể ọ ấ ể ạ ế tuy t đ i.ệ ố - Có th đ t tên cho vùng 9 ô c a b ng đ n giá (ví d Table4) khi đó trong côngể ặ ủ ả ơ ụ th c ta ch c n nh p tên Table4 v trí c a b ng_tìm.ứ ỉ ầ ậ ở ị ủ ả Sau khi ghi xong, copy n i dung Sheet1 sang Sheet2 và s a l i d li u b ngộ ử ạ ữ ệ ở ả tìm thành b ng d c và dùng VLOOKUP đ làm l i câu 2. (đ copy sheet, có thả ọ ể ạ ể ể ch n tên Sheet dòng ch a tên các Sheet và nh n Ctrl+Drag kéo sang bên c nh đọ ở ứ ấ ạ ể copy). BÀI S 4bỐ S d ng hàm tìm ki m VLOOKUPử ụ ế MSO TEN SLUONG TTIEN GCHU A DOS 40 4800000 X B WORD 20 2800000 C EXCEL 35 4550000 X A DOS 25 3000000 C EXCEL 35 4550000 X B WORD 15 2100000 C EXCEL 40 5200000 X B WORD 25 3500000 A DOS 45 5400000 X Mã số Tên Đ n giáơ A DOS 120000 B WORD 140000 C EXCEL 130000 Câu 1. Ch n Sheet3 c a BTAP4 đ nh p d li u.ọ ủ ể ậ ữ ệ Câu 2. Căn c vào b ng ch a tên và đ n giá c a mã s đ đi n thông tin vào c tứ ả ứ ơ ủ ố ể ề ộ TEN Câu 3. Tính TTIEN b ng s l ng nhân đ n giá tùy thu c vào lo i, và t o d ngằ ố ượ ơ ộ ạ ạ ạ v i đ n v ti n là $ (d ng #,##0 "$")ớ ơ ị ề ạ Câu 4. C t GCHU đánh d u X n u TTIEN l n h n 4000000, ng c l i đ tr ngộ ấ ế ớ ơ ượ ạ ể ố (L u ý chu i tr ng là chu i có d ng "" )ư ỗ ố ỗ ạ Câu 5. Trang trí và ghi l i nh ng thay đ i v a t o ra Sheet3.ạ ữ ổ ừ ạ ở Bài t p th c hành MS Excelậ ự Trang 7 Bài t p th c hành ậ ự MS Excel BÀI S 5aỐ S d ng các hàm chu i (LEFT, RIGHT, MID...) đ trích ra các ký t dùngử ụ ỗ ể ự trong các hàm tìm ki m.ế BÁO CÁO DOANH THU STT MAH TEN SLG TTIEN VCHUYEN TONG 1 XL0 Xăng 50 225,000 đ 0 đ 225,000 đ 2 DS1 D uầ 35 105,000 đ 630 đ 105,630 đ 3 NS3 Nh tớ 60 600,000 đ 12,600 đ 612,600 đ 4 DL0 D uầ 35 122,500 đ 0 đ 122,500 đ 5 XS2 Xăng 70 280,000 đ 2,800 đ 282,800 đ 6 XL1 Xăng 50 225,000 đ 1,125 đ 226,125 đ 7 DL3 D uầ 40 140,000 đ 2,520 đ 142,520 đ 8 NL2 Nh tớ 30 330,000 đ 4,620 đ 334,620 đ 9 NS0 Nh tớ 70 700,000 đ 0 đ 700,000 đ 10 XS3 Xăng 65 260,000 đ 3,900 đ 263,900 đ T ng c ngổ ộ 2,987,500 đ 28,195 đ 3,015,695 đ ==>Trong đó, ký t đ u c a mã hàng (MAH) đ i di n cho m t hàng (TEN); ký tự ầ ủ ạ ệ ặ ự th hai đ i di n cho giá s (S) hay l (L); ký t cu i là khu v c. (0, 1, 2 và 3). M iứ ạ ệ ỉ ẻ ự ố ự ố quan h đ c cho trong b ng sau:ệ ượ ả Mã Tên Giá sỉ Giá lẻ Khu v c 1ự Khu v c 2ự Khu v c 3ự X Xăng 4000 4500 0.50% 1.00% 1.50% D D uầ 3000 3500 0.60% 1.20% 1.80% N Nh tớ 10000 11000 0.70% 1.40% 2.10% Câu 1. Căn c vào ký t đ u c a MAH và b ng d li u trên đ đi n tên thíchứ ự ầ ủ ả ữ ệ ở ể ề h p vào c t TEN.ợ ộ Câu 2. Tính thành ti n (TTIEN) b ng s l ng (SLG) nhân đ n giá; trong đó đ nề ằ ố ượ ơ ơ giá tùy thu c vào giá s hay l .ộ ỉ ẻ Câu 3. Tính ti n v n chuy n (VCHUYEN) v i đi u ki n: n u khu v c 0 thìề ậ ể ớ ề ệ ế ự mi n ti n v n chuy n, các khu v c khác tính theo giá tr % c a c t thành ti nễ ề ậ ể ự ị ủ ộ ề t ng ng v i t ng khu v c cho trong b ng.ươ ứ ớ ừ ự ả Câu 4. Tính t ng (TONG) b ng thành ti n c ng chuyên ch và tính t ng c ng cácổ ằ ề ộ ở ổ ộ c t TTIEN, VCHUYEN, TONG. Sau đó đ nh d ng cho các c t bi u di n giá ti nộ ị ạ ộ ể ễ ề theo d ng #,##0 đ.ạ Câu 5. Trang trí và l u v i tên BTAP5.XLSư ớ H ng d n th c hành:ướ ẫ ự Đ i v i các b ng d li u dùng đ tìm thông tin ta nên gán tên đ d thao tácố ớ ả ữ ệ ể ể ễ và x lý. Gi s ta đ t tên cho b ng tìm là Table5.ử ả ử ặ ả 1. Đ l y ký t đ u c a MAH, ta dùng hàm LEFT. Vì b ng tìm b trí theo c tể ấ ự ầ ủ ả ố ộ nên ta dùng hàm VLOOKUP, c t 2 ch a giá tr tên lo i hàng. Ta có:ộ ứ ị ạ VLOOKUP(LEFT([MAH]), TABLE5, 2, 0) 2. Vì có hai lo i giá tùy thu c vào ký t th hai c a MAH, nên ta ph i dùng IF đạ ộ ự ứ ủ ả ể xác đ nh v trí c t ch a giá thích h p, n u giá s thì c t 3 ch a đ n giá, ng c l iị ị ộ ứ ợ ế ỉ ộ ứ ơ ượ ạ s là c t 4. Dùng hàm MID đ l y các ký t gi a chu i. Ta có công th c tínhẽ ộ ể ấ ự ở ữ ỗ ứ nh sau:ư Bài t p th c hành MS Excelậ ự Trang 8 Bài t p th c hành ậ ự MS Excel [TTIEN] = [SLG] * VLOOKUP(LEFT([MAH]), TABLE5, IF(MID([MAH],2,1) = "S", 3, 4), 0) 3. Dùng hàm IF ki m tra đi u ki n khu v c là 0 hay 0; sau đó dùngể ề ệ ự VLOOKUP đ tính % t ng ng v i t ng khu v c và nhân v i TTIEN. L u ý ,ể ươ ứ ớ ừ ự ớ ư n u KV=1 thì c t tr l i là 5, KV=2 thì c t là 6, KV=3 thì c t là 7 ==> KV+4 = sế ộ ả ạ ộ ộ ố hi u c t s tr l i. Do đó ta có công th c:ệ ộ ẽ ả ạ ứ IF(RIGHT[MAH]="0", 0, VLOOKUP(LEFT([MAH]), TABLE5, RIGHT([MAH])+4, 0) * [TTIEN]) Bi u th c: RIGHT([MAH])+4 xác đ nh giá tr c a c t tr l i t ng ng v iể ứ ị ị ủ ộ ả ạ ươ ứ ớ t ng khu v c. N u không nh n xét nh trên, ta ph i dùng 2 hàm IF l ng nhau đừ ự ế ậ ư ả ồ ể xác đ nh v trí c a c t c n l y: If(kv=1, 5, if(kv=2, 6, 7))ị ị ủ ộ ầ ấ Sau khi hoàn t t và ghi file, ch n Sheet2 và th c hành bài t ng t 5b:ấ ọ ự ươ ự BÀI S 5bỐ BÁO CÁO DOANH THU SAP MSO VATTU NGNHAP SL NHAP TG NHAP SL XUAT TG XUAT GHI CHU A1 02/06/99 15 15 C2 04/06/99 20 15 B1 05/06/99 30 25 C1 08/06/99 10 10 A2 15/06/99 25 20 A1 17/06/99 30 25 C2 25/06/99 35 30 B1 27/06/99 20 20 B2 30/06/99 25 20 Trong đó, MSO g m 2 ký t , ký t đ u ch tên v t t (A, B, C) ký t cu i chồ ự ự ầ ỉ ậ ư ự ố ỉ lo i (1, 2). B ng d i đây cho bi t đ n giá nh p, xu t c a t ng v t t ng v iạ ả ướ ế ơ ậ ấ ủ ừ ậ ư ứ ớ các lo i 1 và 2:ạ MSO VATTU GNHAP1 GNHAP2 GXUAT1 GXUAT2 A XANG 3000 3500 4000 4500 B DAU 2000 2500 3000 3500 C NHOT 10000 10500 11000 15000 Câu 1. Căn c vào MSO đi n tên v t t vào c t VATTUứ ề ậ ư ộ Câu 2. Tính t ng ti n nh p (TGNHAP) c a các lo i b ng s l ng nhân đ n giáổ ề ậ ủ ạ ằ ố ượ ơ nh p tùy thu c vào lo i.ậ ộ ạ T ng t hãy tính t ng ti n xu t (TGXUAT)ươ ự ổ ề ấ Câu 3. c t ghi chú đánh d u X n u đã xu t h t s l ng nh p.Ở ộ ấ ế ấ ế ố ượ ậ Câu 4. C t SAP ghi th t c a các hàng t ng ng theo TGXUAT v i giá tr caoộ ứ ự ủ ươ ứ ớ ị là 1 (s p theo chi u gi m).ắ ề ả BÀI S 6Ố Bài t p th c hành MS Excelậ ự Trang 9 Bài t p th c hành ậ ự MS Excel T o bi u đ b ng Chart Wizard. Tham chi u tuy t đ i.ạ ể ồ ằ ế ệ ố PHI U THEO DÕI TI N G I TI T KIÊMẾ Ề Ở Ế Lãi su tấ 1.10% So tien Them bot Cong tien Thang Dau thang Lai Cuoi thang cuoi thang cuoi thang 1 20000000 220000 20220000 500000 20720000 2 20720000 2000000 3 -300000 4 -500000 5 200000 6 1000000 7 600000 8 -200000 9 -500000 10 1000000 T ng s ti n có đ cổ ố ề ượ sau 12 tháng T ng s ti n có đ c sau 12 thángổ ố ề ượ Câu 1. Tính ti n Lãi = S ti n đ u tháng * Lãi su tề ố ề ầ ấ Câu 2. S ti n cu i tháng = S ti n đ u tháng + Lãiố ề ố ố ề ầ Câu 3. C ng ti n cu i tháng = Cu i tháng + Thêm b t cu i thángộ ề ố ố ớ ố Câu 4. S ti n đ u tháng sau = C ng ti n cu i tháng c a tháng tr c đóố ề ầ ộ ề ố ủ ướ Câu 5. Tính s ti n s có đ c sau 12 thángố ề ẽ ượ Câu 6. L p bi u đ minh h a s ti n có đ c c a đ u các thángậ ể ồ ọ ố ề ượ ủ ầ Câu 7. Trang trí và l u v i tên BTAP6.XLSư ớ H ng d n th c hành:ướ ẫ ự 1. L u ý r ng, đ sao chép công th c đúng thì tham chi u đ n ô Lai suat c nư ằ ể ứ ế ế ầ ph i đ t là tham chi u tuy t đ i.ả ặ ế ệ ố Sau khi tính xong các ô đ u tiên, sao chép công th c xu ng phía d i và l uở ầ ứ ố ướ ư ý ch khi sao chép ô cu i c a b ng thì s li u m i đ c đi n vào đ y đ .ỉ ở ố ủ ả ố ệ ớ ượ ề ầ ủ 5. Trong bài ch có d li u c a 10 tháng. Đ tính đ n 12 tháng, ta xem ph n thêmỉ ữ ệ ủ ể ế ầ b t cu i tháng b ng 0, và tính theo công th c:ớ ố ằ ứ Gi s t ng sau 10 tháng là x. Khi đó ta cóả ử ổ T ng sau 12 tháng = (x + x*Lai suat) + (x + x*Lai suat)*Lai suatổ V i Lai suat đây là ô ch a giá tr 1,1%.ớ ở ứ ị 6. V bi u đẽ ể ồ B c 1:ướ Ch n vùng d li u: bao g m c t Tháng và c t Dau thangọ ữ ệ ồ ộ ộ B c 2:ướ Ch n bi u t ng ChartWizard, con tr có d ng d u +ọ ể ượ ỏ ạ ấ (V m t vùng trên b ng tính đ đ t bi u đ )ẽ ộ ả ể ặ ể ồ B c 3: ướ Cung c p thông tin c n thi t sau đó ch n [Finish] đ k t thúc.ấ ầ ế ọ ể ế Ti n hành theo các b c h ng d n trong ph n lý thuy tế ướ ướ ẫ ầ ế Bi u đ có d ng:ể ồ ạ Bài t p th c hành MS Excelậ ự Trang 10 Bài t p th c hành ậ ự MS Excel 0 5000000 10000000 15000000 20000000 25000000 30000000 1 2 3 4 5 6 7 8 9 10 Sau khi t o đ c bi u đ , ch n t ng ph n t trong bi u đ đ ch nh s a vàạ ượ ể ồ ọ ừ ầ ử ể ồ ể ỉ ử xem k t qu đ hi u thêm v các thành ph n trong m t bi u đ .ế ả ể ể ề ầ ộ ể ồ BÀI S 7aỐ T o b ng d li u, s d ng các hàm c s d li u (DSUM, DAVERAGE...),ạ ả ữ ệ ử ụ ơ ở ữ ệ t ch c các vùng đi u ki n.ổ ứ ề ệ NHÀ MÁY N C TAM KỲƯỚ STT KHHANG KVUC METK TTIEN PTHU TTHU 1 VAN A 45 58500 0 58500 2 HOANG B 65 91000 6500 97500 3 VO C 23 34500 3450 37950 4 TRAN B 14 19600 1400 21000 5 LE C 78 117000 11700 128700 6 BUI A 93 120900 0 120900 7 VU A 90 117000 0 117000 8 NGUYEN C 24 36000 3600 39600 9 BUI B 56 78400 5600 84000 10 LE B 78 109200 7800 117000 Khu v cự Phụ thu Đ n giáơ A 0 1300 B 100 1400 [B ng tìm]ả C 150 1500 Câu 1. Tính Thành ti n = Mét kh i * Đ n giá (tùy thu c khu v c)ề ố ơ ộ ự Câu 2. Tính T ng thu = Thành ti n + Ph thu (theo khu v c)ổ ề ụ ự Câu 3. Tính t ng tiêu th l n nh t, nh nh t và trung bình c a s mét kh i đãổ ụ ớ ấ ỏ ấ ủ ố ố tiêu th c a t ng khu v c và ghi k t qu vào b ng sau:ụ ủ ừ ự ế ả ả Khu V cự A B C T ngổ 296400 319500 206250 L n nh tớ ấ 120900 117000 128700 Bé nh tấ 58500 21000 37950 Trung bình 98800 79875 68750 Câu 4. V bi u đ minh h a cho b ng câu 3ẽ ể ồ ọ ả ở Câu 5. Trang trí và l u file v i tên BTAP7.XLSư ớ Bài t p th c hành MS Excelậ ự Trang 11 Bài t p th c hành ậ ự MS Excel H ng d n th c hành:ướ ẫ ự 1. Dùng Vlookup đ tính đ n giá c a t ng khu v c sau đó nhân v i s mét kh i,ể ơ ủ ừ ự ớ ố ố ta có: (c t 3 c a b ng tìm ch a đ n giá)ộ ủ ả ứ ơ [TTIEN]=[METK] * VLOOKUP([KVUC], [Bang_Tìm], 3, 0) 2. T ng t trên, v i c t 2 c a b ng tìm ch a ph thu ta có:ươ ự ớ ộ ủ ả ứ ụ [TTHU]=[TTIEN] + VLOOKUP([KVUC], [Bang_Tìm], 2, 0) 3. Dùng các hàm c s d li u DSUM, DMIN, DMAX, DAVERAGE v i cácơ ở ữ ệ ớ đi u ki n v khu v c đ c t ch c nh sau:ề ệ ề ự ượ ổ ứ ư KVUC KVUC KVUC A B C L u ý r ng, các nhãn tham gia trong đi u ki n ph i chính xác nh nhãn c tư ằ ề ệ ả ư ộ trong b ng d li u, th ng ta dùng ch c năng copy đ sao chép các nhãn c t đả ữ ệ ườ ứ ể ộ ể tránh sai sót. Gi s b ng d li u đ c gán tên là DATA7a, ta có công th c đ tính t ngả ử ả ữ ệ ượ ứ ể ổ tiêu th c a khu v c A là: DSUM(DATA7a, "TTHU", [đkA]), v i [đkA] là hai ôụ ủ ự ớ KVUC và A vùng đi u ki n trên. T ng t đ i v i công th c các ô còn l i.ở ề ệ ươ ự ố ớ ứ ở ạ - Trong th c hành, ta sao chép các công th c sang các ô bên c nh, sau đó s a l iự ứ ạ ử ạ cho chính xác. 4. Bi u đ :ể ồ 0 100000 200000 300000 400000 Tong Lon nhat Be nhat Trung binh A B C Bài t p th c hành MS Excelậ ự Trang 12 Bài t p th c hành ậ ự MS Excel BÀI T P 7bẬ V n d ng các hàm c s d li u có k t h p các hàm chu i. Ôn t p các hàmậ ụ ơ ở ữ ệ ế ợ ỗ ậ tìm ki m (Th c hành ti p trong Sheet2 c a bài 7a)ế ự ế ủ DANH SÁCH L NG THÁNG 04 NĂM 2009ƯƠ SO MASO HOTEN PHAI CHVU TĐO VH LGCB PH CAP NG CONG THUONG CG LUONG AFD8 H ngồ 460 23 CFC1 Thanh 310 24 CMT5 S nơ 330 23 BMC7 Hoàng 430 25 CMT3 Tâm 320 24 CFT3 Lan 320 22 CFC6 Mai 360 26 CFT4 Thúy 350 23 CMD2 Hùng 310 20 CMC9 Tình 380 23 MaxL= ? MinL= ? AveL= ? MASO cho trên g m 4 ký t MS1, MS2, MS3 và MS4, ký t đ u là ch c v ,ồ ự ự ầ ứ ụ th hai là phái, th ba là trình đ văn hóa và ký t cu i là s năm công tác, v i cácứ ứ ộ ự ố ố ớ giá tr nh sau:ị ư MS1 Ch c vứ ụ MS2 Phái MS3 Trình đ văn hóaộ MS4 Năm công tác A TP F Nữ D Đ i h cạ ọ B PP M Nam C Cao đ ngẳ C NV T Trung c pấ Câu 1. Căn c vào MASO chèn thông tin vào các c t PHAI, CHVU, TĐOVHứ ộ Câu 2. Tính PHCAP = PCCV + THNIEM, v i THNIEM = NAMCT * 6000 vàớ PCCV đ c tính nh sau:ượ ư CHVU PCCV TP 40000 PP 25000 NV 10000 Câu 3. Tính THUONG. Bi t: N u NGCONG>=25, THUONG = 120.000; n uế ế ế 23<=NGCONG<25, THUONG=70.000; còn l i THUONG=20.000ạ Câu 4. Tính CGLUONG = LGCB*1200 + PHCAP + THUONG Câu 5. Tính L ng cao nh t ươ ấ MaxL L ng th p nh tươ ấ ấ MinL L ng trung bìnhươ AveL Câu 6. C t SO đ c đánh s theo CGLUONG v i m c cao nh t là 1 ộ ượ ố ớ ứ ấ Câu 7. Tính t ng PHCAP, THUONG và CGLUONG theo PHAI và l u vào Sheet3,ổ ư theo m u sau:ẫ Bài t p th c hành MS Excelậ ự Trang 13 Bài t p th c hành ậ ự MS Excel Phái Ph c pụ ấ Th ngưở C ng l ngộ ươ Nam ? ? ? Nữ ? ? ? Câu 8 . Trang trí và ghi l i các thay đ iạ ổ H ng d n th c hành:ướ ẫ ự 1. Dùng Vlookup và các hàm chu i đ l y thông tin.ỗ ể ấ 2. Dùng Vlookup đ l y ph c p ch c v t ng ng v i ch c v .ể ấ ụ ấ ứ ụ ươ ứ ớ ứ ụ 3. Dùng 2 hàm IF l ng nhau.ồ 5. Dùng các hàm Max, Min và Average 7. T o vùng đi u ki n theo phái, t o b ng báo cáo trong Sheet3 và dùng DSUMạ ề ệ ạ ả đ tính.ể BÀI S 8Ố Các thao tác c b n trên danh sách d li u: SORT, FILTERơ ả ữ ệ Danh sách h c viên đ c c p h c b ng - Năm h c 2009ọ ượ ấ ọ ổ ọ STT HOTEN NGSINH NOISINH TRBINH HBONG MADIEM Tuoi 1 TUAN 05/15/80 HUE 4.2 0 A01 19 2 VIET 02/13/75 Q.BINH 7.5 50000 B03 24 3 ANH 11/11/78 HUE 6.7 50000 C04 21 4 HUNG 10/12/76 DA NANG 8.3 50000 A03 23 5 HOA 04/01/82 HA TINH 8.7 150000 C02 17 6 THUAN 12/18/78 NGHE AN 4.0 0 B01 21 7 VAN 01/01/81 DA NANG 4.2 0 B05 18 8 SON 09/09/77 HUE 5.5 50000 A02 22 9 BINH 10/10/79 HA TINH 8.3 100000 C01 20 Câu 1. Nh p b ng d li u trong Sheet1 và nh p danh sách đi m sau trong Sheet2:ậ ả ữ ệ ậ ể Mã đi mể Đi m c sể ơ ở Chuyên môn Ngo i ngạ ữ C01 9.0 8.0 8.0 B05 7.0 2.5 3.0 B01 2.0 4.5 5.5 A02 5.5 6.5 4.5 C02 9.0 8.5 8.5 A03 8.5 9.0 7.5 C04 9.0 2.5 8.5 B03 6.0 7.5 9.0 A01 4.5 6.0 2.0 Câu 2. Căn c vào MADIEM và b ng mã đi m trên đ tính đi m trung bìnhứ ả ể ể ể (TRBINH) Câu 3. Tính h c b ng (HBONG) theo các đi u ki n sau:ọ ổ ề ệ - n u TRBINH >=8.5 và tu i <=18 thì h c b ng là: 150000 đế ổ ọ ổ Bài t p th c hành MS Excelậ ự Trang 14 Bài t p th c hành ậ ự MS Excel - n u TRBINH >=8.0 và tu i <=20 thì h c b ng là: 100000 đế ổ ọ ổ - n u 5.0 20 thì h c b ng là: 50000 đế ổ ọ ổ - ngoài ra không có h c b ng.ọ ổ Câu 4. Trích ra nh ng b ng tính khác (l u vào Sheet3):ữ ả ư - nh ng h c viên có m c h c b ng 150000 đữ ọ ứ ọ ổ - nh ng h c viên có m c h c b ng 100000 đữ ọ ứ ọ ổ - nh ng h c viên có m c h c b ng 50000 đữ ọ ứ ọ ổ - nh ng h c viên không có h c b ng.ữ ọ ọ ổ Câu 5. Trích ra nh ng b ng tính khác (l u vào Sheet3) nh ng h c viên trong cácữ ả ư ữ ọ đ tu i sau:ộ ổ - t 16 đ n 18ừ ế - 19 ho c 20ặ - trên 20 tu i.ổ Câu 6. Trang trí và l u v i tên BTAP8.XLSư ớ Câu 7. Th c hi n các thao tác s p th t theo HOTEN, NGSINH, TRBINH... sauự ệ ắ ứ ự m i l n s p hãy quan sát s thay đ i (có th nh n Undo và Redo)ỗ ầ ắ ự ổ ể ấ H ng d n th c hành:ướ ẫ ự 2. Đ tính đi m trung bình c n ph i bi t 3 c t đi m, do đó dùng hàmể ể ầ ả ế ộ ể AVERAGE v i 3 l n dùng VLOOKUP, m i l n ch thay đ i c t tr l i giá tr :ớ ầ ỗ ầ ỉ ổ ộ ả ạ ị Average(Vlookup(...,...,2,0), Vlookup(...,...,3,0), Vlookup(...,...,4,0)) Ngoài ra, Excel còn có 2 hàm cho phép l y m t kho ng các ô mà không ph iấ ộ ả ả dùng nhi u l n các hàm VLOOKUP; đó là hàm OFFSET và MATCH. Hàmề ầ OFFSET có 5 đ i s nh sau:ố ố ư - OFFSET(v trí g c, đ d i đ ng, đ d i ngang, s hàng, s c t k t qu )ị ố ộ ờ ứ ộ ờ ố ố ộ ế ả V i ý nghĩa: tính t v trí g c, d i lên trên (âm)/d i xu ng d i (d ng), d iớ ừ ị ố ờ ờ ố ướ ươ ờ sang trái (âm)/d i sang ph i (d ng) bao nhiêu ô; và kho ng c n tr l i s ch aờ ả ươ ả ầ ả ạ ẽ ứ bao nhiêu hàng, bao nhiêu c t.ộ - Hàm MATCH g n gi ng các hàm tìm ki m nh ng không tr l i giá tr tìm màầ ố ế ư ả ạ ị ch tr l i v trí (th t ) c a giá tr tìm th y trong kho ng tìm.ỉ ả ạ ị ứ ự ủ ị ấ ả MATCH(giá tr tìm, kho ng tìm, ph ng th c)ị ả ươ ứ T hai hàm trên ta có cách gi i khác đ tính đi m trung bình nh sau:ừ ả ể ể ư =AVERAGE(OFFSET(v trí g c, MATCH(mã đi m, b ng mã, 0), 0, 1, 3))ị ố ể ả trong đó: v trí g c đ c ch n là ô ch a giá tr [Đi m c s ]; mã đi m là sị ố ượ ọ ứ ị ể ơ ở ể ố hi u mã đi m c a t ng ng i; b ng mã là danh sách toàn b mã đi m c n tìm.ệ ể ủ ừ ườ ả ộ ể ầ Hàm Match có nhi m v tính đ d i xu ng khi tìm th y mã đi m trong danh sách;ệ ụ ộ ờ ố ấ ể đ d i ngang là 0 (vì các ô s l y có ô đ u tiên cùng c t v i v trí g c); s ô c nộ ờ ẽ ấ ầ ộ ớ ị ố ố ầ l y là 1 hàng và 3 c t.ấ ộ 3. Trong đi u ki n tính h c b ng có s d ng giá tr tu i, nh ng trong b ng dề ệ ọ ổ ử ụ ị ổ ư ả ữ li u không có giá tr này, do đó ta s t o thêm m t c t trung gian đ tính tu iệ ị ẽ ạ ộ ộ ể ổ (nh m đ n gi n hóa đi u ki n) và tu i đ c tính b i hi u c a năm hi n th i v iằ ơ ả ề ệ ổ ượ ở ệ ủ ệ ờ ớ năm sinh, ta có: [TUOI] = YEAR(NOW())-YEAR([NGSINH]) khi đó, gi s E6 là ô ch a đi m trung bình, ta có công th c sau:ả ử ứ ể ứ IF(AND(E6>=8.5, [TUOI]<=18), 150000, IF(AND(E6>=8, [TUOI]<=20, 10000, IF(AND(E6>=5, [TUOI]>20), 50000, 0))) Bài t p th c hành MS Excelậ ự Trang 15 Bài t p th c hành ậ ự MS Excel 4. Đ l c và ghi k t qu ta dùng ch c năng l c nâng cao (Advanced Filter) v iể ọ ế ả ứ ọ ớ các vùng đi u ki n v h c b ng nh sau:ề ệ ề ọ ổ ư HBONG HBONG HBONG HBONG 150000 100000 50000 0 L u ý, sau khi t o vùng đi u ki n Sheet3, ta đ t con tr m t ô tr ng tr cư ạ ề ệ ở ặ ỏ ở ộ ố ướ khi s d ng l nh l c.ử ụ ệ ọ 5. T ng t , s d ng l c nâng cao v i các đi u ki n l c nh sau:ươ ự ử ụ ọ ớ ề ệ ọ ư Tuoi Tuoi Tuoi Tuoi >=16 20 20 7. Tr c h t ch n tên tr ng c n s p (ví d , ch n tr ng HOTEN), sau đó m iướ ế ọ ườ ầ ắ ụ ọ ườ ớ s d ng l nh [DATA]SORT. Sau m i l n s p, nên thay đ i m t s y u t đử ụ ệ ỗ ầ ắ ổ ộ ố ế ố ể hi u rõ v ch c năng này.ể ề ứ BÀI S 9Ố Các thao tác c b n trên danh sách d li u: SORT, FILTER, SUBTOTAL...ơ ả ữ ệ B NG BÁN HÀNGẢ MAH NBAN TEN SLUONG TGIA THUE TONG 1 12/12/97 BAP 324 2 12/12/97 BIA 454 3 14/12/97 BOT 656 4 11/01/98 GAO 431 5 20/01/98 KEO 455 6 25/01/98 BAP 564 7 01/02/98 BIA 657 8 11/02/98 BOT 432 9 05/02/98 KEO 544 10 12/03/98 GAO 767 T ng tr giá các m t hàng bán trong tháng 2/1998ổ ị ặ ??? Câu 1. Tính tr giá (TGIA) b ng s l ng (SLUONG) nhân đ n giá (DGIA), v iị ằ ố ượ ơ ớ đ n giá và thu đ c cho b ng sau:ơ ế ượ ở ả Tên Đ n giáơ Thuế BAP 3500 1% BIA 15000 1% GAO 3000 2% KEO 10000 2% BOT 5000 1% Câu 2. Tính thu (THUE) theo s li u trên và chú ý r ng n u tr giá d i 100000ế ố ệ ằ ế ị ướ đ ng thì không thu thu . Sau đó tính t ng c ng (TONG) b ng tr giá c ng v i thuồ ế ổ ộ ằ ị ộ ớ ế Câu 3. Trích ra danh sách các m t hàng BAP, GAO, BOT và l u vào Sheet2.ặ ư Câu 4. Tính t ng các c t TGIA, THUE và t ng s l n bán theo t ng lo i m tổ ộ ổ ố ầ ừ ạ ặ hàng và l u vào b ng sau Sheet3: ư ả ở Tên S l n bánố ầ T ng tr giáổ ị T ng thuổ ế Bài t p th c hành MS Excelậ ự Trang 16 Bài t p th c hành ậ ự MS Excel BAP BIA GAO KEO BOT Câu 5. Dùng k t qu câu 4 đ v đ th so sánh t ng tr giá c a t ng lo i m tế ả ở ể ẽ ồ ị ổ ị ủ ừ ạ ặ hàng Câu 6. Trích ra hai b ng tính bán hàng ng v i 2 năm: 1997 và 1998ả ứ ớ Câu 7. Tính t ng tr giá các m t hàng bán trong tháng 2/1998ổ ị ặ Câu 8. S p th t (Sort) b ng theo c t TEN v i chi u gi m d n (Descending)ắ ứ ự ả ộ ớ ề ả ầ Câu 9. Dùng SubTotal đ tính t ng các c t SLUONG, TGIA, TONG. Sau đó thayể ổ ộ t ng b ng các hàm khác nh Min, Max, Average...ổ ằ ư Trang trí và l u v i tên BTAP9.XLSư ớ H ng d n th c hành:ướ ẫ ự 2. Vì có đi u ki n nên khi tính thu ta c n ph i xét xem tr giá l n h n hay nhề ệ ế ầ ả ị ớ ơ ỏ h n 100000, do đó có công th c sau:ơ ứ IF([TGIA]<100000, 0, [TGIA]*VLOOKUP(...)) 3. L p vùng đi u ki n d ng ho c (OR) đ l c.ậ ề ệ ạ ặ ể ọ 4. Đ tính t ng s l n bán ta dùng DCOUNTA, các giá tr khác thì dùng DSUM.ể ổ ố ầ ị Đ i v i phép tính t ng theo đi u ki n, ngoài hàm DSUM Excel còn cung c pố ớ ổ ề ệ ấ m t hàm t ng đ ng, đó là SUMIFộ ươ ươ Cú pháp: SUMIF(kho ng_s _tính, đi u_ki n, kho ng th t s s tính)ả ẽ ề ệ ả ậ ự ẽ Trong đó, kho ng_s _tính tham chi u đ n kho ng các ô s tham gia tính t ng;ả ẽ ế ế ả ẽ ổ đi u_ki n th ng có d ng "bi u th c so sánh"; riêng kho ng th t s s tính là tùyề ệ ườ ạ ể ứ ả ậ ự ẽ ch n, nh ng n u đ a vào thì t ng k t qu s tính trong vùng này.ọ ư ế ư ổ ế ả ẽ * Ví d : xét b ng s li u sau:ụ ả ố ệ A B C D E F 1 BAP 5 BAP BIA BAP BAP 2 BIA 8 7 9 5 4 3 BAP 7 4 GAO 9 5 BIA 6 - Khi đó công th c: SUMIF(A1:A5,"BAP",B1:B5) s có giá tr là 12; t ngứ ẽ ị ươ đ ng v i vi c dùng hàm DSUM v i đi u ki n tên hàng là BAP.ươ ớ ệ ớ ề ệ - T ng t ta có:ươ ự SUMIF(C1:F1,"BAP",C2:F2) = 16 Th dùng SUMIF đ gi i l i câu 4 trên.ử ể ả ạ ở Cùng d ng v i SUMIF là hàm COUNTIF(kho ng ô, đi u ki n) dùng đ đ mạ ớ ả ề ệ ể ế s các ô trong kho ng ô h p v i đi u ki n.ố ả ợ ớ ề ệ 6. L p vùng đi u ki n t ngày 01/01/1997 đ n 31/12/1997 (năm 1997) và t ngậ ề ệ ừ ế ươ t đ tính năm 1998.ự ể 7. L p vùng đi u ki n có d ng ngày bán l n h n ho c b ng ngày 01/02/1998 vàậ ề ệ ạ ớ ơ ặ ằ nh h n ngày 01/03/1998 (trong kho ng tháng 2)ỏ ơ ả Ngoài ph ng pháp dùng m t kho ng ngày nh trên, ta còn có th s d ngươ ộ ả ư ể ử ụ d ng công th c trong vùng đi u ki n đ tính. Ví d , đ i v i câu 6 có th l p đi uạ ứ ề ệ ể ụ ố ớ ể ậ ề Bài t p th c hành MS Excelậ ự Trang 17 Bài t p th c hành ậ ự MS Excel ki n d ng =YEAR(ô đ u tiên ch a d li u ngày)=1997 (l u ý trong công th c trênệ ạ ầ ứ ữ ệ ư ứ có hai d u =) và nhãn tên tr ng c n ph i b tr ng.ấ ườ ầ ả ỏ ố ==>T ng t , đi u ki n trong câu 7 s là =MONTH(ô ch a ngày)=2.ươ ự ề ệ ẽ ứ BÀI S 10Ố B NG GHI TÊN, GHI ĐI MẢ Ể Đi m chu nể ẩ = 17 T T SB D HOTEN TEN TRUONG BAN DVAN DTOAN TONG KETQUA A00 AI C A11 BINH A A20 CHAU B B31 ANH A B42 BAO C C50 DUNG B C61 HANH B C71 HUONG A D82 DUONG A D90 PHUOC C Mã tr ngườ Tên tr ngườ Ký t đ u c a SBD là mã tr ngự ầ ủ ườ A QUOC HOC Ký t cu i c a SBD là đi m u tiênự ố ủ ể ư . B HAI BA TRUNG C NGUYEN HUE D GIA HOI Câu 1. Căn c vào ký t đ u c a SBD đi n thông tin vào TEN TRUONGứ ự ầ ủ ề Câu 2. Căn c vào SBD đi n DVAN và DTOAN (theo b ng đi m Sheet2)ứ ề ả ể ở Câu 3. Tính TONG theo các yêu c u sau:ầ * N u ban A ho c B thì đi m Toán h s 2ế ặ ể ệ ố * N u ban C thì đi m Văn h s 2ế ể ệ ố * C ng thêm đi m u tiênộ ể ư Câu 4. Tính KETQUA: Đ u n u TONG >= Đi m chu n ng c l i là R tậ ế ể ẩ ượ ạ ớ Câu 5. L c ra các danh sách và ghi vào Sheet3 nh ng ng i:ọ ữ ườ a) Có k t qu R tế ả ớ b) Có k t qu Đ u và t ng đi m l n h n 23ế ả ậ ổ ể ớ ơ Câu 6. L p báo cáo theo m u sau:ậ ẫ H c sinh tr ngọ ườ Đi m trung bìnhể S h c sinh Đ uố ọ ậ QUOC HOC ? ? HAI BA TRUNG ? ? NGUYEN HUE ? ? GIA HOI ? ? Câu 7 C t TT đánh s theo c t TONG v i giá tr cao nh t là 1ộ ố ộ ớ ị ấ B ng đi m (L u trong Sheet2)ả ể ư Bài t p th c hành MS Excelậ ự Trang 18 Bài t p th c hành ậ ự MS Excel SBD DTOAN DVAN D90 4 7 D82 5 4 C71 8 7 C61 9 8 C50 5 5 B42 3 7 B31 5 4 A20 4 5 A11 7 9 A00 6 8 Ghi bài th c hành v i tên BTAP10.XLSự ớ BÀI S 11Ố Các bài t p b sung. S d ng hàm SumProduct đ gi i bài toán tính đi mậ ổ ử ụ ể ả ể trung bình các môn theo các h s tùy ý.ệ ố SUMPRODUCT(m ng_1, m ng_2, ...)ả ả - Hàm SumProduct tính t ng các tích m t cách t ng ng t các ph n t c aổ ộ ươ ứ ừ ầ ử ủ m ng 1 v i m ng 2... cho đ n t i đa 32 m ng đ c phép s d ng - các m ng nàyả ớ ả ế ố ả ượ ử ụ ả ph i có cùng s chi u.ả ố ề L u ý: hàm PRODUCT(so1, so2,...) th c hi n phép nhân liên ti p các so1,ư ự ệ ế so2, ... v i nhau. Ví d : Product(2,4,5) = 2*4*5 = 40.ớ ụ B NG GHI ĐI MẢ Ể TT HTEN M1 M2 M3 M4 M5 TRBINH 1 AN 5 7 6 8 7 6.8 2 BINH 8 7 9 6 8 7.5 3 SON 9 9 8 7 8.1 4 VAN 8 7 6 9 5 7.3 5 TUAN 8 4 5 8 5.5 6 LAN 3 5 4 5 8 4.9 7 HOA 4 7 6 7 5 5.9 8 QUANG 5 4 3 5 4 4.3 9 VINH 9 9 9 8 8.8 10 THANH 9 7 9 8 9 8.5 * V i h s các môn M1 đ n M5 đ c cho trong b ng sau:ớ ệ ố ế ượ ả Môn M1 M2 M3 M4 M5 H sệ ố 2 1 3 4 2 Câu 1. Căn c vào h s , tính đi m trung bình (TRBINH).ứ ệ ố ể G i ýợ Đi m trung bình đ c tính b ng cách l y t ng đi m các môn có nhân hể ượ ằ ấ ổ ể ệ s sau đó chia cho t ng s các h s ( trên là b ng 2+1+3+4+2=12). Công th c cóố ổ ố ệ ố ở ằ ứ d ng:ạ SUMPRODUCT(đi m, he_so)/SUM(he_so)ể Trong đó đi m tham chi u đ n các ô ch a đi m c a t ng h c sinh; he_so làể ế ế ứ ể ủ ừ ọ vùng ch a thông tin v h s .ứ ề ệ ố Bài t p th c hành MS Excelậ ự Trang 19 Bài t p th c hành ậ ự MS Excel Hãy đ ý tình hu ng có m t s h c sinh đ c mi n m t s môn h c (ô đi mể ố ộ ố ọ ượ ễ ộ ố ọ ể đ tr ng) thì công th c có còn đúng không? Rõ ràng trong tình hu ng này, phép chiaể ố ứ ố cho toàn b t ng c a các h s s làm cho đi m trung bình b th p xu ng. Thayộ ổ ủ ệ ố ẽ ể ị ấ ố vào đó ta ph i xét xem môn nào đ c mi n đ gi m đi h s c a môn đó. Sả ượ ễ ể ả ệ ố ủ ử d ng hàm SumIf ta có th kh c ph c tình hu ng này:ụ ể ắ ụ ố SUMPRODUCT(đi m, he_so)/SUMIF(đi m, ">=0", he_so)ể ể Hàm SumIf lúc này có nhi m v tính t ng các h s t ng ng v i các mônệ ụ ổ ệ ố ươ ứ ớ h c có đi m l n h n ho c b ng 0, do đó s không tính nh ng môn mi n h c (cóọ ể ớ ơ ặ ằ ẽ ữ ễ ọ giá tr r ng)ị ỗ Câu 2 S d ng hàm SumProduct v i các giá tr b trí theo c tử ụ ớ ị ố ộ Tên hàng S l ngố ượ Giá_1 Giá_2 A12 20 3000 4000 C21 30 1000 3000 E23 25 2000 4000 D32 50 4000 5000 T ng c ng (s _l ng giá) =ổ ộ ố ượ ? ? Gi s c n tính t ng c ng toàn b các m t hàng trên theo đ n giá lo i 1 màả ử ầ ổ ộ ộ ặ ơ ạ không ph i tính t ng t ng m t hàng, ta s d ng hàm SumProduct nh sau:ả ổ ừ ặ ử ụ ư SumProduct(so_luong, don_gia_1) s cho k t qu : 340000. T ng t tính t ngẽ ế ả ươ ự ổ theo đ n giá lo i 2.ơ ạ BÀI S 12Ố S d ng công c ử ụ ụ Solver đ gi i các bài toán đ c bi t.ể ả ặ ệ Trong Excel có b sung m t s công c m nh đ gi i các bài toán nh : tìmổ ộ ố ụ ạ ể ả ư nghi m c a h ph ng trình, gi i bài toán t i u... đó là Solver (trong menuệ ủ ệ ươ ả ố ư Tools). Trong ph n này ch gi i thi u m t s ví d minh h a ch c năng này.ầ ỉ ớ ệ ộ ố ụ ọ ứ Bài 1. Gi i h ph ng trình sau:ả ệ ươ 3x + 4y - 3z = 5 4x - 2y + 6z = 40 x + 4y + 8z = 78 B c 1ướ . L p mô hình bài toán (theo m u d i đây)ậ ẫ ướ A B C D 1 Bi nế x y z 2 Nghi m (t m)ệ ạ 1 1 1 3 4 H sệ ố a b c 5 ph ng trình 1ươ 3 4 -3 6 ph ng trình 2ươ 4 -2 6 7 ph ng trình 3ươ 1 4 8 8 9 Giá tr t mị ạ 4 8 13 10 M c tiêuụ 5 40 78 Bài t p th c hành MS Excelậ ự Trang 20 Bài t p th c hành ậ ự MS Excel - Các ô B2:D2 s ch a nghi m c a h , đ u tiên ta cho t t c b ng 1 (và g i làẽ ứ ệ ủ ệ ầ ấ ả ằ ọ nghi m t m) và s dùng Solver đ yêu c u tính ra nghi m th t s .ệ ạ ẽ ể ầ ệ ậ ự - Các ô B5:D7 ch a h s c a các ph ng trìnhứ ệ ố ủ ươ - Các ô B10:D10 ch a giá tr v ph i c a các ph ng trìnhứ ị ế ả ủ ươ - Các ô B9:D9 là các giá tr trung gian t ng ng v i các nghi m t m c a h ;ị ươ ứ ớ ệ ạ ủ ệ đ c tính b ng cách thay b nghi m t m vào v trái c a các ph ng trình (t ngượ ằ ộ ệ ạ ế ủ ươ ổ các tích), do đó đây ta dùng hàm SumProduct đ tính. Công th c ô B9 s là:ở ể ứ ẽ [B9] = SumProduct(B5:D5, $B$2:$D$2) = 4 (t ng t v i các ô [C9] và [D9])ươ ự ớ B c 2ướ . Cung c p thông tin cho Solverấ Sau khi l p xong mô hình, ch n ô [B9] làm m c tiêu, và g i l nh [Tools]Solver, taậ ọ ụ ọ ệ có: - Trong h p [Set Target Cell] s t đ ng ch n ô B9, n u không ta ph i t nh pộ ẽ ự ộ ọ ế ả ự ậ B9 vào đó. m c Equal to ta ch n Value of và nh p giá tr 5 (giá tr c a ô m cỞ ụ ọ ậ ị ị ủ ụ tiêu B10) là giá tr đích th c c a ph ng trình 1 (v ph i)ị ự ủ ươ ế ả - Trong h p [By Changing Cells] ta dùng mouse đ ch n vùng l u k t quộ ể ọ ư ế ả (nghi m) là B2:D2 (Excel s t đ ng thêm ki u tham chi u tuy t đ i)ệ ẽ ự ộ ể ế ệ ố - Trong vùng [Subjects to the Constraints] ta nh p các ràng bu c dùng làm m cậ ộ ụ tiêu c a l i gi i. Đ i v i vi c gi i h ph ng trình, m c tiêu c n đ t đ c là cácủ ờ ả ố ớ ệ ả ệ ươ ụ ầ ạ ượ giá tr t m ph i b ng v i các giá tr th c có (v ph i c a các ph ng trình). ị ạ ả ằ ớ ị ự ế ả ủ ươ Ở tr c ta đã dùng ô B9 làm đích, v y còn l i 2 giá tr ph i đ t đ c. Ta ch n Addướ ậ ạ ị ả ạ ượ ọ và khai báo đ ng th c còn l i làm m c tiêu trong h p:ẳ ứ ạ ụ ộ S d ng nút Add đ thêm các ràng bu c, sau khi hoàn t t ch n [OK] đ quayử ụ ể ộ ấ ọ ể v h p Solver Parametersề ộ B c 3. Th c hi n l nh và k t thúcướ ự ệ ệ ế Sau khi hoàn t t các khai báo, ta ch n nút [Solve] đ Excel t đ ng tính và thôngấ ọ ể ự ộ báo k t qu ;ế ả Bài t p th c hành MS Excelậ ự Trang 21 Bài t p th c hành ậ ự MS Excel N u đ ng ý v i l i gi i thì ch n nút Keep Solver Solution, n u không thì ph cế ồ ớ ờ ả ọ ế ụ h i các giá tr g c Restore Orginal Values và ch n OK đ hoàn t t.ồ ị ố ọ ể ấ Cu i cùng, ta có k t qu nh sau:ố ế ả ư A B C D 1 Bi nế x y z 2 Nghi mệ 2 5 7 3 ... ... ... ... 8 9 Giá trị 5 40 78 10 M c tiêuụ 5 40 78 L u ý r ng giá tr các ô B2:D2 và B9:D9 đã thay đ i.ư ằ ị ở ổ Tóm l i, đ s d ng Solver đòi h i các yêu c u sau:ạ ể ử ụ ỏ ầ - Ph i cung c p đ y đ các tham s trong h p tho i. Đ c bi t c n l u ý r ng ôả ấ ầ ủ ố ộ ạ ặ ệ ầ ư ằ đích (Set Target Cell) c n ph i ch a công th c có liên quan đ n vùng nghi m, n uầ ả ứ ứ ế ệ ế không Excel s báo l i.ẽ ỗ - Ch ra vùng mà Excel s tác đ ng và l u k t qu , đây th ng b t đ u v iỉ ẽ ộ ư ế ả ở ườ ắ ầ ớ các giá tr 1 là giá tr t m th i đ Excel có kh i đi m tính toán.ị ị ạ ờ ể ở ể - Cung c p đ y đ các ràng bu c dùng làm m c tiêu đ tính toán.ấ ầ ủ ộ ụ ể Bài t p t ng tậ ươ ự. Gi i h ph ng trình sauả ệ ươ 3x + 4y - 3z - t = 26 4x - 2y + 6z - 7t = 10 {nghi m là:ệ x + 4y + 8z - 6t = 12 (x=8; y=3; z=2; t=4) } 2x - 9y + 5z + 3t = 11 Bài 2. Xét bài toán tìm c c đ i l i nhu n nh sau: "M t nhà máy s n xu t 3 s nự ạ ợ ậ ư ộ ả ấ ả ph m (sph_a, sph_b và sph_c) v i ti n lãi t ng ng trên 1 đ n v s n ph m là 75,ẩ ớ ề ươ ứ ơ ị ả ẩ 35 và 50. Các s n ph m này đòi h i m t s linh ki n (6 lo i) v i t ng quan gi aả ẩ ỏ ộ ố ệ ạ ớ ươ ữ chúng đ c cho b i b ng và trong b ng còn cho bi t s l ng các linh ki n t nượ ở ả ả ế ố ượ ệ ồ kho. Hãy l p k ho ch s n xu t sao cho l i nhu n thu đ c là l n nh t.ậ ế ạ ả ấ ợ ậ ượ ớ ấ Bài t p th c hành MS Excelậ ự Trang 22 Bài t p th c hành ậ ự MS Excel Dùng Solver, ta l p mô hình bài toán nh sau:ậ ư A B C D E F 1 SPh_a SPh_b SPh_c 2 M c tiêu s n xu t -ụ ả ấ > 100 100 100 3 Linh ki nệ T n khoồ Yêu c uầ 4 Lk_1 700 400 2 0 2 5 Lk_2 850 500 3 0 2 6 Lk_3 380 300 0 3 0 7 Lk_4 500 400 2 1 1 8 Lk_5 650 400 1 0 3 9 Lk_6 450 200 0 1 1 10 Ti n lãi/ề sp 75 35 50 11 L i nhu nợ ậ 7500 3500 5000 12 T ng l i nhu nổ ợ ậ 16000 Trong mô hình trên, có các ô ch a công th c nh sau:ứ ứ ư - Các ô t C4 đ n C9 ch a hàm SumProduct($D$2:$F$2,D4:F4)...ừ ế ứ - Tính l i nhu n b ng ti n lãi m t s n ph m nhân v i s s n ph mợ ậ ằ ề ộ ả ẩ ớ ố ả ẩ - T ng l i nhu n b ng t ng c ng các l i nhu n c a các s n ph m.ổ ợ ậ ằ ổ ộ ợ ậ ủ ả ẩ  Trong h p tho i Solver Parameters ta khai báo nh sau:ộ ạ ư - Ch n ô D12 làm ô đích và giá tr m c tiêu là Max; các ô l u k t qu là D2:F2ọ ị ụ ư ế ả (v i các giá tr kh i đ u là 100) và các ràng bu c nh sau:ớ ị ở ầ ộ ư Sau khi ch n Solve, ta đ c nghi m là 107, 105 và 181 v i T ng l i nh n làọ ượ ệ ớ ổ ợ ậ 20750. Bài t p t ng tậ ươ ự. Hãy thay đ i các tham s và gi i l i bài toán trên. Hãy tìmổ ố ả ạ m t bài toán t ng t , (ví d l p l ch s n xu t sao cho chi phí th p nh t) sau đóộ ươ ự ụ ậ ị ả ấ ấ ấ l p mô hình và gi i.ậ ả Bài t p th c hành MS Excelậ ự Trang 23