Bài tập bảng tính Excel

pdf

41 trang hapham 2720

Download

Bạn đang xem 20 trang mẫu của tài liệu "Bài tập bảng tính Excel", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

bai_tap_bang_tinh_excel.pdf

Nội dung text: Bài tập bảng tính Excel

BÀI TP BNG TÍNH - EXCEL Tp HCM, tháng 03/2008 (Tài liu lu hành ni b) - 1 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn MT S B I TP CN BN BÀI TP EXCEL S 1 (REVIEW) 1.1 HIN / GIU THANH TRNG THÁI, THANH CÔNG THC - Thanh trng thái (Status bar) : chn menu View > Status bar (nu có du tick phía trc là thanh trng thái ang hi n ra) click chu t trái giu hoc hin th . - Thanh công thc (Formular bar) : chn memu View > Formular bar ( nu có du tíck phía trc là thanh công thc ang hi n ra)click chu t trái giu hoc hin th . - Các thanh công c khác thì vào menu View > Toolbars và làm thao tác tng t 1.2 CÁC THAO TÁC DI CHUYÊN CHUT (POINTER) - Di chuyn sang ô lin sau dùng Tab , sang ô lin trc dùng Shift + Tab - Di chuyn n ô A22, X30,K58 trong Sheet hi n ti, ô B1, B2 trong Sheet 2, sau ó tr v ô A1 ca Sheet 1 o H ng d n: 8: Click chu t trái vào các thanh cu n ngang, thanh cu n dc cho n khi hi n ra ô mun di chuyn n. click chu t vào ô này. (Cách khác là click chu t vào h p a ch nm bên trái thanh công thc, gõ a ch ô mun di chuyn n vào ây ri Enter) 7: Nhn phím F5, nhp a ch ô cn n vào khung Reference, chn Ok. - Di chuyn v ô A1 : dùng Ctrl + Home, - Di chuyn v u m t dòng : dùng Home - Di chuyn v cui hàng : dùng End + Enter - Di chuyn v hàng cui cùng ca bng tính : dùng End + $ - Di chuyn v c t cui cùng ca bng tính : dùng End + " - Lên m t trang màn hình : dùng Page Up - Xung m t trang màn hình : dùng Page Down - Sang trái m t trang màn hình : dùng Alt + PageUp - Sang phi m t trang mành hình : dùng Alt + PageDown - Di chuyn sang Sheet sau : dùng Ctrl + PageUp - Di chuyn v Sheet trc : dùng Ctrl + PageDown 1.3 CH N VÙNG (RANGE) - Hng dn chn vùng liên tc – Ví d C4:F10 o 8 Click chu t trái vào ô u tiên ca vùng và kèo chu t n ô cui hay lick chu t trái vào ô u , gi phím Shift và click chu t trái ti ô cui o 7 Di chuyn h p nh v n ô âu tiên ca vùng, gi phím Shift và dùng các phím mi tên di chuyn n ô cui. - 1 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn - Hng dn chn vùng không liên tc - Ví d C4:F10 và D2:D8 o 8 Chn vùng th nht trc, gi phím Ctrl trong khi chn vùng tip theo o 7 Chn vùng th nht trc, gi phím Shift + F8 (m ch ADD) và chn vùng tip theo, sau ó nhn Esc - Hng dn chn toàn b m t c t o 8 Click chu t trái vào tiu c t o 7 a h p nh v n bt kì ô nào trong c t mun chn ri n Ctrl + Spacebar - Hng dn chn nhiu c t : o 8 Chn c t u tiên, gi phím Ctrl và chn c t tip theo o 7 Chn c t u tiên, gi phím Shift + F8 (m ch ADD) và chn c t tip theo, sau ó nhn Esc - Hng dn chn toàn b m t hàng o 8 Click chu t trái vào tiêu ca dòng o 7 a h p nh v n bt kì ô nào trong hàng mun chn ri n Shift + Spacebar - Hng dn chn nhiu hàng o 8 Chn hàng u tiên, gi phím Ctrl và chn hàng tip theo o 7 Chn hàng u tiên, gi phím Shift + F8 (m ch ADD) và chn hàng tip theo, sau ó nhn Esc - Hng dn chn toàn b bng tính : o 8 Click chu t trái ti ô trng nm v trí giao nhau gia tiêu c t và tiêu dòng o 7 Dùng Ctrl + A hay Ctrl + Shift + Spacebar 1.4 CÁC THAO TÁC V BNG TÍNH (SHEET) - Hng dn chn nhiu Sheet o Chn nhiu Sheet liên tc : click vào sheet u, gi phím Shift và click vào Sheet cui o Chn nhiu Sheet ri rc : Gi phím Ctrl và click vào các Sheet mun chn - Hng dn i tên Sheet o Double click vào tên Sheet mun i trên Sheet Tab ( hoc click chu t phi vào tên Sheet mun i và chn Rename). Xut hi n h p thai Rename Sheet, xóa tên c, nhp tên mi và Enter - Hng dn chèn thêm Sheet mi o Click chu t phi vào Sheet tab, chn Insert. Trong h p thoi New chn WorkSheet và Enter - Hng dn di chuyn Sheet n v trí mi trên Sheet Tab : o Click chu t vào tên Sheet mun di chuyn, gi chu t vào kéo Sheet n v trí mi - 2 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn - Hng dn xóa Sheet o Click chu t phi vào Sheet mun xóa, chn Delete 1.5 DÙNG CHUT DI CHUYN, SAO CHÉP VÀ XÓA D LIU - Nhp m t chu!i kí t vào ô B2, sau ó nhp m t chu!i kí t khác vào ô D2 - Di chuyn n i dung ô B2 sang ô B3 và ô D2 sang ô E2 o Hng dn : click và gi chu t trái ti cnh ca ô cn di chuyn ri kéo ô ó n v trí mi. - Copy n i dung ô B3 sang các ô C6, D10, H15 o Hng dn : Gi phím Ctrl trong khi click và gi chu t trái ti cnh ca ô cn di chuyn ri kéo ô ó n v trí mi. - Xóa n i dung ca tt c các ô : o Hng dn : chn các ô mun xóa và n nút Delete 1.6 DÙNG FILL HANDLE SAO CHÉP VÀ I N D LIU - Nhp s 1 vào ô A1. Sao chép n i dung ô A1 sang các ô t" A2 n A11 o Hng dn : Click chu t trái vào Fill Handle ca ô A1, kéo xung cho n ô A11 - in s th t t ô B1 n B11 o Hng dn : Nhp s 1 vào ô B1, sau ó gi phím Ctrl trong khi click chu t trái vào Fill Handle ca ô B1, kéo xung cho n ô B11 - Xóa d li u trong m t vùng: o Hng dn : Chn toàn b vùng d li u cn xóa, click chu t trái vào Fill Handle ca ô góc di bên phi trong vùng ó, kéo ng#c lên trên và qua trái cho n khi tt c d li u trong vùng b xóa ht. - 3 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 2 (Lp công thc và S dng hàm) 1. i tên Sheet1 thành FORMULARS 2. Nhp s li u cho các ô trong c t A và B, nhp tr s cho ô H s (G3) là 15. 3. Tính kt qu cho các ô còn li theo công thc cho dòng tiêu 4. nh dng tt c các ô cha giá tr dng s theo dng thc : #.##0,00 5. Trang trí bng tính. 6. Nu n i dung ca hai ô F3 và G3 nhp chung vào m t ô G3, thì công thc còn thc hi n #c không? Hng dn : Ch cn tính cho tt c các ô C5:K5 (dòng u tiên), sau ó chn toàn b các ô này và dùng chc n$ng Fill Handle copy xung cho nhng dòng di Yêu cu : Sinh viên t tìm hiu thêm và ghi nh các dng thc khác dành cho giá tr s cng nh cách thc s% dng chc n$ng Fill Handle. BÀI TP EXCEL S 3 (Lp công thc và S dng hàm) 1. Nhp và lu bng tính vi tên Dogia.xls 2. i tên sheet là Du tru vat tu 3. Tính Thành tin cho t"ng mt hàng 4. Tính Tng thành tin 5. Trang trí bng tính - 4 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 4 (S dng hàm) 1. i tên Sheet 2 thành FUNCTIONS và thc hi n bng tính trên. 2. S% dng chc n$ng Conditional Formatting ca Excel thc hi n yêu cu sau : s âm màu , s dng màu xanh và s 0 màu tím. Sinh viên nhn xét và rút ra kt lun v mc ích s% dng ca chc n$ng này. Yêu cu : Sinh viên t tìm hiu thêm và ghi nh các hàm ã #c cp trong bài tp này. BÀI TP EXCEL S 5 (Lp công thc) Dùng các phép tính s hc tính các giá tr ti các ô trng - 5 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 6 (Lp công thc và nh dng) 1. Tính c t Lng chính, bit Lng chính= Lng CB * H s 2. Tính c t Thc lãnh, bit Thc lãnh= Lng + Ph cp 3. Tính T ng: Lng chính, Ph cp và Thc lãnh 4. Trang trí bng tính BÀI TP EXCEL S 7 (S% dng a ch tuy t i) T& giá USD – VND trong tháng 1 n$m 2008 là 16,250 VND. Lp công thc tính toán và in s li u cho c t VND trong bng tính di ây. 1. Sau khi lp công thc tính cho ô C5, dùng chc n$ng Fill Handle copy công thc xung tt c nhng dòng còn li 2. Ch ra s khác bi t (và gii thích lí do) v kt qu ca hai cách làm sau : a. Ti ô C5 nhp công thc = B5*C1, Enter, dùng Fill Handle copy công thc cho các ô còn li ca c t VND - 6 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn b. Ti ô C5 nhp công thc = B5*$C$1, Enter, dùng Fill Handle copy công thc cho các ô còn li ca c t VND 3. Yêu cu nh dng c t VND theo dng thc Currency có n v là vn. Yêu cu : Sinh viên tìm hiu v cách s% dng a ch tuy t i trong Excel, và phi n'm rõ khi nào thì dùng loi a ch này. BÀI TP EXCEL S 8 (S% dng Công thc và Hàm) 1. Tính c t Thành tin: S l ng * n giá 2. Tính c t Lãi: Thành tin * Lãi sut 3. Tính T ng c ng Thành tin, Lãi 4. Tính B!ng Tng H p 5. Trang trí bng tính BÀI TP EXCEL S 9 ( nh dng bng tính) Nhp d li u và nh dng theo các dng thc nh bng tính bên di. Yêu cu: Sinh viên tìm hiu thêm và ghi nh cách s% dng chc n$ng Regional and Language Options trong Control Panel ca Windows nh dng cách biu di(n s, thi gian và tin t - 7 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 10 (Trang trí bng tính) Thc hi n bng tính di ây - Tin hành i màu nn và màu ch ca nhng ô có n i dung ging nhau - K) khung bng tính bng nhng nét khác nhau. Yêu cu : Sinh viên phi tìm hiu và n'm rõ các thao tác óng khung, i màu nn, màu ch ca bng tính. BÀI TP EXCEL S 11 (Hàm v chu!i: Left) Trong bng tính di ây, bit hai kí t u tiên ca Mã Du L ch là Mã a Phng, yêu cu tách ra hai kí t ó và in vào nhng ô có du ? (G#i ý : S% dng hàm Left) - 8 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 12 (Hàm v chu!i: Right) Trong bng tính di ây, bit hai kí t cui cùng ca Mã Du L ch là Mã a Phng. Yêu cu tách ra hai kí t ó và in vào nhng ô có du ? (G#i ý : hàm Right) BÀI TP EXCEL S 13 (Hàm v chu!i: Mid) Trong bng tính di ây, bit hai kí t th 2 và 3 ca Mã Du L ch là Mã a Phng. Yêu cu tách ra hai kí t ó và in vào nhng ô có du ? (G#i ý : hàm Mid) Yêu cu : Sinh viên tìm hiu và ghi nh cách s% dng các Left, Right, Mid và m t s hàm x% lí chu!i kí t khác. - 9 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 14 (S% dng hàm IF) Di ây là c t im Thi và Kt Qu trích t" bng im ca m t trng trung hc. Bit rng sinh viên #c coi là u nu im thi ln hn hoc bng 5, ng#c li là rt Nhp s li u vào file Excel nh bng trên, sau ó s% dng hàm If in giá tr u hoc Rt vào c t Kt qu cho t"ng sinh viên. BÀI TP EXCEL S 15 (Hàm If, Thng kê n gin) Lp bng tính sau và lu vi tên Tuyensinh.xls 1. Tính c t i"m KV: nu Khu vc là 3 thì im KV là 3, ng#c li là 1. 2. Tính c t Tng i"m: Anh v#n*HS2 + Toán và Tin h$c*HS1 + i"m KV. 3. Tính c t Kt qu! nh sau: - Nu Tng i"m t" 25 tr lê thì %t, ng#c li là Rt. - S'p xp t$ng dn theo c t Tng i"m. 4. Tính B!ng Thng Kê. - 10 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 16 (Hàm If, x% lý chu!i) Thc hi n bng tính sau: 1. in d li u vào c t Xut Nhp: Nu 2 ký t u ca c t Mã là “XK” thì vit “Xut kh*u”, ng#c li nu “NK” thì vit là “Nhp kh*u”. 2. in d li u vào c t Tên s!n ph&m: - Nu ký t th 3 ca c t Mã là G: tên sn ph*m là G%o - Nu ký t th 3 ca c t Mã là D: tên sn ph*m là 'ng - Nu ký t th 3 ca c t Mã là T: tên sn ph*m là Than 3. in d li u vào c t S hiu: là 3 ký t cui ca c t Mã BÀI TP EXCEL S 17 (S% dng hàm IF lng nhau) Di ây là bng im tuyn sinh ca m t trng i hc. M!i sinh viên có ba c t im Toán, Lý, Hóa và c t im TB. Thí sinh #c coi là u nu tt c ba môn thi u có im ln hn 4 và im TB >=15, ng#c li thì thí sinh ó Rt. 1. Nhp s li u vào file Excel nh bng trên, sau ó s% dng hàm If và hàm AND in giá tr u hoc Rt vào c t kt qu. 2. Nu không dùng hàm AND, thì phi s% dng bao nhiêu hàm If lng nhau trong bài tp này. - 11 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 18 (S% dng hàm IF lng nhau) Di ây là bng im tuyn sinh ca m t trng i hc. M!i thí sinh có ba c t im Toán, Lý, Hóa. T ng im ca t"ng thí sinh #c tính là t ng ca ba c t này và im u tiên theo t"ng khu vc ca m!i thí sinh. C th nh sau : Nu thí sinh ( khu vc 1, i"m u tiên là 0, thí sinh ( khu vc 2, i"m u tiên là 1, thí sinh ( khu vc 3 i"m u tiên là 1.5. Các tr'ng h p còn l%i i"m u tiên là 2. Yêu cu : Sau khi tính t ng im cho t"ng thí sinh, sinh viên nhn xét v mi liên h gia s iu ki n ca bài toán và s hàm If #c s% dng (Gi ý : so sánh v i bài trên) BÀI TP EXCEL S 19 (S% dng hàm IF lng nhau) Lp và gii phng trình bc nht vi các h s a,b nh và lu vi tên BacNhat.xls - 12 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 20 (Hàm dò tìm: VLOOKUP & HLOOKUP) M!i m,- a phng ng vi m t tên a phng . th cho bng 1. S% dng hàm dò t5m tham chiu tìm tên ga Či và Čn tng ng mã Č a phng (G i )*: D7ng 12m VLOOKUP). Vi mô 4-tng t-nh trên, thc hi n thay /ng t8nh di ây. (G i ): 67ng 12m HLOOKUP) Yêu cu : Sinh viên nhn 9:t v-s-;1 ?t ra kt lun v-vi c khi @2o s%-0ng 12m HLOOKUP, khi @2o s%-0ng 12m VLOOKUP; ng thi 45m hiu thêm v-. - -nht ;1<c nhau .a cht BCng bt ;5. Fng 2 mô 4-cht BCng A ti nhi t Č 120 và 0.5 . - 13 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn 1. H,y in GH<-tr Č nht thích h#p vào ô B16, B17. (G i ): Dùng h2m VLOOKUP). Nu I1<t sinh l!i, 1,y GHi 418ch 4i sao. 2. Sort Bi /ng 1 theo th-t gim dn ca c t A, và thc hi n li câu 1. Nu phát sinh l!i hãy gii thích ti sao. BÀI TP EXCEL S 22 (Hàm dò tìm có VLookup, HLookup, Index, Match, tham chiu Sheet) Lp bng tính sau và lu vi tên XemayCL.xls, to Sheet QLXE vi n i dung nh sau: To Sheet ThongtinXE vi n i dung nh sau: 1. in d li u cho c t Tên hãng da vào ký t u ca Mã hiu và B!ng Tên hãng xe trong Sheet ThongtinXE. 2. in d li u cho c t Phân khi da vào 2 ký t cui ca Mã hiu và B!ng Mã Phân khi trong Sheet ThongtinXE. 3. in d li u cho c t Tên xe da vào c t Mã hiu và B!ng Tên Lo%i xe trong Sheet ThongtinXE. - 14 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 23 (Hàm: SUMIF) Di ây là bng doanh thu phòng quý 1 n$m 2007 ca khách sn Sài Gòn. Lp bng thng kê t ng doanh thu ca t"ng phòng trong quý 1 – 2007 theo bng sau: BÀI TP EXCEL S 24 (Nhóm hàm Thng Kê: Count, Counta, CountIf, ) 1. m s thí sinh d thi (s% dng hàm Count) 2. m s thí sinh có im Toán ln hn 5, và m s thí sinh có im V$n bng 7 3. m s thí sinh n (s% dng Countif hoc Counta) - 15 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 25 (Hàm ngày gi) 1. Ti ô C4, nhp vào m t giá tr có nh dng ngày tháng 2. Ti ô D4 , hãy lp công thc in ra Th bng ting Vi t ca ngày v"a nhp vào ô C4 (G i ý : Dùng hàm VLOOKUP phi h#p vi hàm WEEKDAY ly Th t" bng DATE) 3. Ti ô E4 hãy lp công thc in ra s ngày có trong tháng ca tháng trích ra t" ô C4 G i ý: - S ngày có trong tháng bng serial number ca ngày u tháng sau-serial number ca ngày u tháng h in ti - Dùng hàm DATE i m t giá tr ngày-tháng-n$m ra serial number ca nó. 4. Nhp các giá tr ngày tháng khác nhau vào ô C4 kim tra kt qu. Yêu cu : Sinh viên tìm hiu v cách s% dng các hàm thu c nhóm hàm ngày tháng. BÀI TP EXCEL S 26 (In n bng tính) Ly bài tp bt kJ ã làm, sau ó: 1. To Header cho bng tính có dng sau Trng H Hoa Sen Khoa : Khoa Hc và Công Ngh 2. To Footer cho bng tính có dng sau : Khóa 07 Trang 1/1 Lp : QL07 3. Thay i các thông s ca trang in nh sau : a. Paper Size A4 b. Orientation: Landscape c. Top = 1, Bottom =1, Left =0.75, Right = 0.75 d. Thay i thông s Page Order thay i trình t in Yêu cu : Sinh viên tìm hiu thêm v các thông s khác, quan sát và rút ra nhn xét v hi u qu ca vi c thay i các thông s trang in. Nu không có máy in, có th m chc n$ng Print Preview xem. - 16 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 27 (Hàm c s d li u: DMax, DMin, ) Di ây là bng s li u thng kê v tu i, chiu cao, và di n tích trng ca các cây $n trái trong m t trang tri. 1. in s li u vào bng thng kê sau v chiu cao ln nht, nhC nht, chiu cao trung bình và t ng chiu cao ca các cây Táo : 2. Tính t ng s cây các loi: 3. Tính chiu cao trung bình ca Táo và Cherry : 4. in s li u vào bng thng kê sau v tu i ln nht, nhC nht và tu i trung bình ca các cây táo có chiu cao ln hn 9 5. in s li u vào bng thng kê sau v l#i nhun ln nht, nhC nht, l#i nhun trung bình và t ng l#i nhun ca nhng cây cáo có chiu cao t" 9 n 18 Yêu cu : Sinh viên rút ra nhn xét và ghi nh các cách lp bng iu ki n khi s% dng nhóm hàm Database, phân bi t mc ích s% dng ca t"ng hàm - 17 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 28 (T ng H#p, Trích Lc, SubTotal) a. in d li u cho c t Tên hàng, n giá và Nhóm hàng da vào Mã hàng và Bàng Tên hàng & n giá b. Vit công thc tính TL Thu da vào Nhóm hàng và B!ng TL thu, bit: i. Nu S l ng di 30 thì ly T+ l 1 ii. Ng#c li ly T+ l 2 c. Tính c t Giá tr,: S l ng * n giá * (1+T+ l Thu) d. S'p xp danh sách theo th t t$ng dn ca Mã hàng, cùng Mã hàng sE theo S L ng gim dn. e. Tính công thc i. (1): Nhp nhóm hàng cn tính t ng giá tr (ví d H2) ii. (2): Nhp công thc tính t ng giá tr (dùng hàm SubTotal) f. Trích lc d li u theo t"ng Nhóm hàng (da vào nhóm hàng ã nhp ti (1)), bng phng pháp Auto Filter g. Thng kê t ng s tin thu #c theo t"ng Mã hàng (dùng Data/ SubTotal) - 18 -
Trng H Hoa Sen BT Bng tính – Bài tp cn bn BÀI TP EXCEL S 29 (Pivot Table) Lp bng tính sau và lu vi tên BTExcel30_PivotTable. Yêu cu: T ng h#p s tin theo t"ng C%a hàng, t"ng Nhân viên và t"ng Ngày (dùng chc n$ng Pivot Table). G i ý: Page field: C%a hàng Row field: Nhân viên, Ngày Column field: Sn ph*m Data field: Tin Kt qu theo mu sau: - 19 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP T NG H P BÀI TP T-NG H.P 1 Thc hi n bng tính sau ( nhp kí hi u *, gõ du * , nhp kí hi u (, gõ du ( , sau ó i sang font Wingdings) 1. Tính im Trung Bình cho t"ng thí sinh bit : Trung Bình = (Toán + V$n + Ngoi Ng)/3 2. nh dng c t Trung Bình theo dng thc 0.0 3. Tính Kt Qu, bit rng : Trung Bình >= 5 và không có môn nào im 0 thì kt qu ca thí sinh là “u”, ng#c li là “Rt”. 4. Xp Lai cho t"ng thí sinh bit rng Trung Bình >=9 : GiCi 7<=Trung Bình<9 : Khá 5<=Trung Bình<7 : TB Ng#c li : Yu 5. Xp hng cho t"ng thí sinh theo im Trung Bình ca thí sinh ó (G i ý : Dùng hàm RANK) 6. Tính BKNG THLNG KÊ - 20 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP T-NG H.P 2 Thc hi n bng tính sau: 1. Da vào ký t u và 2 ký t cui ca Mã H tìm trong Bng Danh mc, in d li u cho c t M/t hàng, 0N V1 và c t n giá 2. Tính c t Thành tin và t ng c ng ca các c t S L ng, n Giá và Thành tin 3. Tính B!ng Tng tin bán 4. S'p xp bng BÁO CÁO BÁN HÀNG t$ng dn theo c t Ngày 5. Tính tng tin bán c theo M/t hàng. 6. VE th biu di(n t ng tin bán #c theo t"ng mt hàng. - 21 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP T-NG H.P 3 Thc hi n bng tính sau Chú ý : Giá tr 30,000,000 phi nhp vào ô khác v i ô cha chui “Tng qu lng” Chú thích : PCCV : Ph Cp Chc V NLV : S ngày làm vi c ca m!i ngi LCB : Lng c$n bn ca m!i ngi C-V : Chc v ca m!i ngi Thu : Thu thu nhp tính theo lng 1. Tính lng cho t"ng nhân viên, bit rng Lng = LCB * NLV * 210 2. Tính PCCV, bit rng : Nu Chc V là “G” thì PCCV là 400.000 Nu Chc V là “PG” hay “TP” thì PCCV là 250.000 Nu Chc V là “PP” thì PCCV là 200.000 Ng#c li PCCV là 0 Sinh viên thêm vào bng tính trên các c t Th(ng, Thu, Thc Lãnh sau c t PCCV 3. Tính s tin thng cho t"ng nhân viên, bit : Thng = NLV* (T ng quM lng-T ng Lng- T ng PCCV)/ T ng NLV. 4. Tính Thu vi các iu ki n nh sau : a. Nu Lng < 3.000.000 thì thu là 0% Lng b. Lng 3.000.000 n 4.000.000 thì thu là 10% Lng c. Ng#c li thì thu là 20% Lng 5. Tính Thc Lãnh = Lng + PCCV + Thng-Thu - 22 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP T-NG H.P 4 Thc hi n bng tính sau: Chú thích : G Ngày : n giá ngày G Tun : n giá Tun T-Tin : Thành Tin 1. Tính s tun gia Ngày n và Ngày i (ây là s tun khách thuê phòng khách sn) G i ý : Dùng hàm INT((Ngày i-Ngày n)/7) 2. Tính s ngày l) trong khong thi gian gia Ngày n và Ngày i. (Ví d : Ngày n là 12/06/07, Ngày i là 19/06/07 thì s tun 1, s ngày l) là 0. G i ý : Dùng hàm MOD(Ngày i-Ngày n,7) 3. Tùy theo loi phòng, in G tun và G ngày tng ng cho phòng ó, da vào s li u Bng Giá Phòng 4. Tính c t T-Tin = G Ngày x S Ngày + G Tun x S Tun (ây là s tin khách phi tr thuê phòng) - 23 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp 5. nh dng c t Ngày i và Ngày n theo dng dd/mm/yyyy và c t T-Tin theo Currency có n v là 2ng. 6. Tính bng doanh thu theo t"ng loi phòng và vE th biu di(n bng doanh thu. BÀI TP T-NG H.P 5 Thc hi n bng tính sau: Chú thích : Mã DL : Mã Du L ch Mã DP : Mã a Phng Hai kí t u ca Mã DL là Mã DP 1. Da vào bng 1, lp công thc in tên a phng, giá vé tàu xe tng ng vi t"ng mã a phng 2. Da vào mã a phng và bng 1 in s li u cho c t chi phí, bit rng kí t cui ca Mã DL cho bit chi phí loi A hay loi B 3. Tính c t THU = GIÁ VÉ TÀU XE + CHI PHÍ - 24 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP T-NG H.P 6 Thc hi n bng tính sau: 1. THÀNH TINN = MÉT KHLI TIÊU THO x 2500 2. Tính Ph thu theo t"ng khu vc dò tìm và ly ra trong bng ph thu 3. Tính Bng Thng Kê. 4. Da vào Bng Thng Kê, vE biu c t biu di(n s mét khi tiêu th ca t"ng khu vc 5. Dùng chc n$ng Filter tìm các khách hàng có s mét khi tiêu th >=25 6. Dùng chc n$ng Filter tìm các khách hàng khu vc A hoc B và có s mét khi tiêu th >=7 - 25 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP T-NG H.P 7 Thc hi n bng tính sau: Chú thích : LT : im Lí Thuyt TH : im Thc Hành KQ : Kt Qu X-Lai : Xp Loi 1. Lp công thc tính c t KQ là trung bình ca hai c t LT và TH 2. in thông tin vào c t X-Loi bit : Nu KQ Form và thc hi n : a. Duy t qua tt c các m*u tin b. Chèn thêm m*u tin mi vi thông tin tùy ý c. Xóa m*u tin bt kì 4. Xp th t danh sách hc viên theo các yêu cu sau : a. Theo Lp - 26 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp b. Theo Lp, nu cùng lp thì xp theo KQ c. Theo Lp, nu cùng lp thì xp theo im Lí Thuyt, im Lí Thuyt bng nhau thì xp theo im Thc Hành 5. Dùng chc n$ng Auto Filter hoc Advanced Filter tìm các hc viên thCa iu ki n sau a. Hc viên Nam b. Hc viên tên Thu c. Hc viên trong h có ch Th d. Hc viên tên Minh và xp loi GiCi e. Hc viên có tên b't u bng ch L và xp lai GiCi 6. Dùng chc n$ng Advanced Filter tìm các hc viên thCa mãn yêu cu sau : a. Hc viên lp B b. Hc viên n h T c. Hc viên tên Loan hoc Mai d. Hc viên lp C, xp lai GiCi e. Hc viên trên 30 tu i 7. Dùng hàm Database thc hi n các yêu cu sau : a. Tính s hc viên lp B b. Tính s hc viên xp lai GiCi ca c danh sách c. Tính s hc viên N lp C d. Tính im TH trung bình ca c lp C e. Tìm im LT cao nht ca các hc viên nam lp B f. Tìm im TH thp nht ca các hc viên n lp C - 27 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP T-NG H.P 8 Thc hi n bng tính sau: 1. in d li u cho c t Tên hàng, Giá da vào nhóm ký t ch cái phn u Mã hàng 2. Tính c t Thành tin: S l ng * n giá, nu S l ng trên 20 thì gim 10% Thành Tin 3. Tính c t Thu: Thành tin * T+ l thu vi T+ l thu da vào ký t cui ca Mã hàng và B!ng t+ l thu 4. Tính c t Tin vn: Thành tin + Thu 5. Tính c t Bán l3: Thành tin * (1+T+ l bán l3) 6. Tính c t Tin Lãi: Tin bán l3 - Tin vn 7. Trích lc các mt hàng là S4a, gm các c t: Mã hàng, Tên hàng, S l ng, n giá, Thành tin. (bng phng pháp Advance Filter) - 28 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP T-NG H.P 9 (Hàm CSDL, Trích l$c, Tham chiu Sheet) To Sheet TheodoiKH vi n i dung nh sau: To Sheet ThongtinKH vi n i dung nh sau: 1. Tính c t Tin Thuê: S ngày * Giá thuê 1 ngày. Da vào ký t u ca S phòng tìm giá thuê. Bit rng S ngày: Ngày i – Ngày n + 1 2. Tính c t Tin gi!m: Nu khách nào trong ngày 15/01/2007 sE #c gim 50% tin thuê ngày ó. 3. Tính c t Tin ph!i tr!: Tin thuê – Tin gi!m 4. Tính: - T ng s ngi trong ngày 15/01/2007 (dùng 3 cách: công thc mng, hàm CountIF, DCount) - T ng s tin thu #c t" ngày 01/01/2007 n ngày 15/01/2007 da vào c t ngày i tính (dùng công thc mng, Dsum). 5. Trích lc danh sách nhng ngi trong ngày 30/01/2007, gm các c t: Khách, S phòng, Ngày n, Ngày i, Tin ph!i tr!. - 29 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP T-NG H.P 10 (Hàm CSDL, Dsum, Mng) Thc hi n bng tính sau: 1. in d li u cho c t Tên da vào ký t u ca c t Mã, bit rng nu là “T” thì tên là “TEA”, “C” thì tên là “COFFEE”. 2. in d li u cho c t Lo%i da vào 2 ký t cui ca Mã và B!ng n giá xut hàng 3. Tình c t n Giá da vào B!ng n giá xut hàng. Nu mt hàng Nhp thì n giá sE thp hn 15% (Loi H: N là nhp, X: là xut) 4. Tình c t Thành tin: S l ng * n giá, nhng nu hóa n là xut và trong ngày 15/10/2007 thì gim 5%. 5. nh dng c t n giá và thành tin có n v tin t là “”, có du ng$n cách hàng ngàn và không có s l). 6. Tính Bng thng kê (dùng hàm Dsum và Công thc Mng) - 30 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP T-NG H.P 11 (Pivot Table) Lp bng tính sau: 1. Lp công thc ly ra d li u, s li u cho tt c các c t còn li, bit rng: a. C t VT và n giá #c dò tìm và ly ra t" BNG THAM CHI5U và da vào mt hàng tng ng b. T-Tin = S l ng * n giá c. Ph phí = T-Tin * Ph phí ca mt hàng tng ng trong BNG THAM CHI5U d. Thu TT #c tính nh sau: ° Nu là h#p ng n i b (2 ký t sau ca SH là NB) thì TT = 0 ° Nhng trng h#p khác thì: Thu TT = (T-Tin + Ph phí) * T+ l Thu TT trong BNG THAM CHI5U e. Tng c6ng = T-Tin + Ph phí + Thu TT 2. Lp công thc tính s li u cho tt c các ô có du ? trong bng T-NG H.P THEO DOANH S. 3. Trang trí bng tính và nh dng bng tính theo úng mu trên, các ô cha tr s #c nh dng sao cho nu bng 0 thì không in ra - 31 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp 4. Da vào bng T-NG H.P DOANH S, vE th 3-PIE phn ánh doanh s theo t"ng mt hàng. 5. Rút trích nhng khách hàng mua hàng t" 1/Apr n 30/Apr ra m t vùng trng trên bng tính. 6. Lp bng Pivot Table vi các cu trúc sau: Pivot Table 1 Pivot Table 2 Pivot Table 3 Page Field: C t Khách hàng Page Field: C t Ngày Page Field: C t Mt hàng Row Field: C t Ngày Row Field: C t Khách hàng Row Field: C t Khách hàng Column Field: C t Mt hàng Column Field: C t Mt hàng Column Field: C t Ngày Data Field: T ng ca c t Data Field: T ng ca c t S Data Field: T ng ca c t Thành tin, T ng c ng. l#ng, Thành tin, T ng S l#ng, Thành tin, T ng c ng. c ng. - 32 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp B I TP ÔN BÀI TP ÔN S 1 Thc hi n bng tính sau: 1. Tính lng = H s lng x 120.000 2. T" bng 1, tính PCCV (Ph cp chc v) bit PCCV=T& l PCCV x Lng 3. T" bng 1, tính Thng cho m!i ngi 4. T ng c ng= Lng + PCCV + Thng 5. T" bng 2, tính Thu (Thu thu nhp) = T& l thu x T ng c ng 6. S% dng chc n$ng Filter, lp danh sách nhng ngi phi óng thu - 33 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP ÔN S 2 Thc hi n bng tính di ây: 1. Da vào bng 1 và khu vc ca t"ng C Quan tính nh mc tiêu th ca t"ng c quan, bit kí t u tiên ca Mã c quan cho bit khu vc ca c quan ó 2. Tính s i n v#t nh mc, bit rng nu s KW tiêu th> nh mc thì : V#t nh mc = S KW tiêu th - nh mc. 3. Thành tin = S KW tiêu th x 500 + V#t nh mc x 600 4. Thu = Thành tin + Ph thu 5. in d li u cho c t ghi chú theo mô t sau : Nu n p tin tr( thì ghi chú là “C't i n”, ng#c li trng 6. Hoàn tt bng thng kê 7. Dùng chc n$ng Filter lp danh sách nhng c quan tiêu th i n v#t nh mc hoc b c't i n 8. VE th theo s li u ca bng thng kê. - 34 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP ÔN S 3 Thc hi n bng tính sau: 1. T" bng 2, lp công thc dò tìm và ly ra n i dung cho c t Tên hàng da vào Mã hàng 2. T" bng 1, lp công thc dò tìm và ly ra s li u cho c t n giá da vào Mã hàng và Ngày bán 3. Thành tin = S l#ng x n giá 4. Tính s l#ng và doanh thu ca t"ng mt hàng và in vào bng 3 5. VE biu th hi n s so sánh doanh thu ca các mt hàng ã bán 6. Lp danh sách nhng mt hàng có s l#ng bán ln nht. - 35 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP ÔN S 4 Thc hi n bng tính sau: 1. Da vào bng, in tên hàng vào c t tên hàng, bit rng : Ký t u tiên ca mã hàng i di n cho mt hàng. 2. Tính Thành tin, bit rng ký t th hai ca mã hàng cho bit mt hàng sE #c bán theo giá s (S) hoc giá l) (L). Thành tin = S l#ng x Giá S hoc Thành tin = S l#ng x Giá L). 3. Tính phí chuyên ch, bit rng ký t cui cùng ca mã hàng i di n cho m t khu vc. Nu kí t cui cùng là 0, thì phí chuyên ch bng 0, ng#c li phí chuyên ch ly t" bng 1 tùy theo khu vc. 4. Tính T ng = Thành Tin + Phí chuyên ch. 5. Tính s l#ng, doanh thu các mt hàng ã bán và in vào bng hai. 6. VE biu th hi n s so sánh doanh thu ca các mt hàng ã bán. 7. Lp danh sách các mt hàng bán khu v 0 bng chc n$ng Filter. - 36 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP ÔN S 5 Thc hi n bng tính sau: 1. Lp công thc ly ra d li u, s li u cho tt c các c t còn li, bit rng : a. C t VT và n giá #c dò tìm và ly ra t" BKNG THAM CHIPU và da vào mt hàng tng ng b. T-Tin = S l#ng x n giá c. Ph phí = T-Tin x Ph phí ca mt hàng tng ng trong BKNG THAM CHIPU d. Thu #c tính nh sau : i. Nu là h#p ng n i b (2 kí t sau ca SH là NB) thì Thu = 0 ii. Nhng trng h#p khác thì : Thu = (T-Tin + Ph phí)* T& l Thu trong BKNG THAM CHIPU e. T ng c ng= T-Tin + Ph phí + Thu 2. Lp công thc tính s li u cho bng TQNG HRP THEO DOANH SL 3. nh dng các ô cha giá tr s bng 0 sE không hin th 4. Da vào BKNG TQNG HRP THEO DOANH SL, vE th 3D-PIE phn ánh doanh s theo t"ng mt hàng 5. Lp danh sách nhng khách hàng mua hàng t" ngày 1/Apr n 30/Apr - 37 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP ÔN S 6 Thc hi n bng tính sau: 1. in s th t. Da vào hai kí t u tiên và kí t th ba ca Mã NV, lp công thc cho c t chc v, tham chiu n bng phòng ban, và bng chc v & lng (Ví d : Mã NV là KDT02 thì chc v ngi ó là Trng phòng kinh doanh) 2. Da vào kí t th 3 ca Mã NV, lp công thc cho c ng L-Ngày (Lng ngày) bit rng lng ngày #c dò tìm và ly ra t" bng chc v & lng 3. Lp công thc cho c t lng = L-Ngày x Ng-Công 4. Lp công thc cho c t thng = (T ng quM thng/T ng ngày công)*Ng-Công ca m!i nhân viên 5. Thc lãnh = Lng + Thng 6. Hoàn tt bng thng kê thng & thc lãnh 7. Da vào bng thng kê thng & thc lãnh vE th dng 3D-Pie phn ánh tin thng ca t"ng phòng ban 8. Lp danh sách các nhân viên ca phòng Kinh doanh và Tip Th có s ngày làm vi c hn 22 ngày. - 38 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP ÔN S 7 Thc hi n bng tính sau: 1. in d li u cho c t Tên CLB da vào 2 ký t gia ca Mã CLB và Bng Mã Câu L%c B6. 2. in d li u cho c t Tên nc da vào 1 ký t gia ca Mã CLB và B!ng Mã Nc. 3. Thêm c t Hng CLB sau c t SL CV nu là 1: hng nht, 2: hng hai 4. Tính Giá vé, da và ký t th 2 và 3 và Bng Mã Câu L%c B6, bit rng ký t cui ca Mã CLB cho bit th hng ca CLB ó và nu CLB ó là hng nht sE gim 5% tin vé, ng#c li t$ng thêm 5%. 5. Tính Doanh s: S l ng * Giá vé 6. Tính c t Lãi_L7 nh sau: - Nu CLB là hng nht và có doanh thu trên 900.000 và nu CLB là hng hai và có doanh thu trên 600.000 thì CLB ó “Lãi” - Ng#c li thì “L7” 7. nh dng c t Giá vé và Doanh thu theo dnh Currency, có n v tin là “USD”. 8. S'p xp bng tính theo c t H%ng CLB, thng kê Tng Doanh thu t"ng Hng CLB (dùng Data/ SubTotal). 9. Tính Bng thng kê - 39 -
Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp BÀI TP ÔN S 8 Thc hi n bng tính sau: 1. in d li u cho c t Tên hàng, n giá s8, n giá l3, da vào 3 ký t u ca Mã H và B!ng n giá. 2. Tính c t S thùng và S h6p l3, bit 1 thùng bng 24 h p. 3. Tính c t Thành tin: n giá * S thùng * 24 + n giá l3 * S h6p l3 4. in d li u cho c t Phí Vn chuy"n (%) da và ký t cui ca Mã H và B!ng Phí chuyên ch( 5. Tính c t Vn chuy"n: Phí Vn chuy"n (%) * Thành tin. 6. Tính c t Tng c6ng: Thành tin + Vn chuy"n. 7. Tính tng tin cho mt hàng S4a chua cho Khu vc A (dùng hàm Dsum và công thc m!ng) 8. S'p xp gim dn theo c t Tên hàng 9. Thng kê t ng s tin thu #c theo t"ng Tên hàng (dùng Data/ SubTotal) 10. Lp biu nh sau: - 40 -