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

- 1 - BÀI T
P BNG TÍNH - EXCEL Tp HCM, tháng 03/2008 (Tài li
u lu hành ni b) Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 1 - M
T SI TP CN BN BÀI T
P EXCEL S 1 (REVIEW) 1.1 HIN / GIU THANH TRNG THÁI, THANH CÔNG THC - Thanh tr
ng thái (Status bar) : chn menu View > Status bar (nu có du tick  phía trc là thanh tr
ng thái ang hi n ra) click chu t trái  giu hoc 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 hoc 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 t
i, ô B1, B2 trong Sheet 2, sau ó tr v ô A1 ca Sheet 1 o H ng d n:
: 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) : 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
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 t
i ô cui o  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. Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 2 - - Hng dn chn vùng không liên tc - Ví d C4:F10 và D2:D8 .. o
Chn vùng th nht trc, gi phím Ctrl trong khi chn vùng tip theo o  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
Click chu t trái vào tiu  c t o  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
Chn c t u tiên, gi phím Ctrl và chn c t tip theo o  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
Click chu t trái vào tiêu  ca dòng o  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
Chn hàng u tiên, gi phím Ctrl và chn hàng tip theo o  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
Click chu t trái t
i ô trng nm  v trí giao nhau gia tiêu  c t và tiêu  dòng o  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 r
c : 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 ( hoc 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 tho
i 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 Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 3 - - Hng dn xóa Sheet o Click chu t phi vào Sheet mun xóa, chn Delete 1.5 DÙNG CHUT  DI CHUYN, 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 t
i c
nh 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 t
i c
nh 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. Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 4 - BÀI T
P 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 l
i theo công thc cho  dòng tiêu  4.  nh d
ng tt c các ô cha giá tr d
ng s theo d
ng 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 d
ng thc khác dành cho giá tr s cng nh cách thc s% dng chc n$ng Fill Handle. BÀI T
P 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 mt hàng 4. Tính Tng thành tin 5. Trang trí bng tính Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 5 - BÀI T
P 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 T
P EXCEL S 5 (Lp công thc) Dùng các phép tính s hc  tính các giá tr t
i các ô trng Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 6 - BÀI T
P EXCEL S 6 (Lp công thc và  nh d
ng) 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 T
P 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 l
i 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. T
i ô C5 nhp công thc = B5*C1, Enter, dùng Fill Handle copy công thc cho các ô còn l
i ca c t VND Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 7 - b. T
i ô C5 nhp công thc = B5*$C$1, Enter, dùng Fill Handle copy công thc cho các ô còn l
i ca c t VND 3. Yêu cu  nh d
ng c t VND theo d
ng 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 lo
i  a ch này. BÀI T
P 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 T
P EXCEL S 9 ( nh d
ng bng tính) Nhp d li u và  nh d
ng theo các d
ng 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 d
ng cách biu di(n s, thi gian và tin t … Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 8 - BÀI T
P 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 T
P 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) Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 9 - BÀI T
P 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 T
P 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. Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 10 - BÀI T
P 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 hoc bng 5, ng#c l
i là rt Nhp s li u vào file Excel nh bng trên, sau ó s% dng hàm If  in giá tr u hoc Rt vào c t Kt qu cho t"ng sinh viên. BÀI T
P 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 l
i 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 l
i là Rt. - S'p xp t$ng dn theo c t Tng i"m. 4. Tính B!ng Thng Kê. Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 11 - BÀI T
P 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 l
i 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 T
P 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 l
i 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 hoc 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. Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 12 - BÀI T
P 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 T
P 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 Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 13 - BÀI T
P EXCEL S 20 (Hàm dò tìm: VLOOKUP & HLOOKUP) M!i +,- a phng ng vi m t tên  a phng .-th-cho -/ng 1. S%-0ng 12m 03-45m tham chiu -tìm tên ga i và n tng ng mã  a phng (G i )*: 67ng 12m VLOOKUP). Vi mô 4-tng t-nh trên, thc hi n thay /ng 48nh 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-.<c 12m ;1<c thu c @1Am 12m 03-45m tham chiu. BÀI T
P EXCEL S 21 (Hàm dò tìm) M!i cht BCng khi -nhi t  -;1<c nhau DE-.A- -nht ;1<c nhau. Fng 1 di ây mô 4-.<c GH - -nht ;1<c nhau .a cht BCng bt ;5. Fng 2 mô 4-cht BCng A 4
i nhi t  -120-=2- 0.5 . Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 14 - BÀI T
P 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, t
o Sheet QLXE vi n i dung nh sau: T
o 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. 1. H,y in GH - -nht 418ch h#p =2o ô B16, B17. (G i ): 67ng 12m VLOOKUP). Nu I1<t sinh l!i, 1,y GHi 418ch 4
i sao. 2. Sort B
i /ng 1 theo th-t-GHm dn .a c t A, =2-thc hi n B
i câu 1. Nu I1<t sinh l!i 1,y GHi 418ch 4
i sao. Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 15 - BÀI T
P EXCEL S 23 (Hàm: SUMIF) Di ây là bng doanh thu phòng quý 1 n$m 2007 ca khách s
n 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 T
P 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 hoc Counta) Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 16 - BÀI T
P EXCEL S 25 (Hàm ngày gi) 1. T
i ô C4, nhp vào m t giá tr có  nh d
ng ngày tháng 2. T
i ô 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. T
i ô 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 t
i - 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 T
P EXCEL S 26 (In n bng tính) Ly bài tp bt kJ ã làm, sau ó: 1. T
o Header cho bng tính có d
ng sau 2. T
o Footer cho bng tính có d
ng sau : 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. Trng H Hoa Sen Khoa : Khoa Hc và Công Ngh Khóa 07 Trang 1/1 Lp : QL07 Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 17 - BÀI T
P 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 tr
i. 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 lo
i: 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 Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 18 - BÀI T
P 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 l
i 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 t
i (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) Trng H Hoa Sen BT Bng tính – Bài tp cn bn - 19 - BÀI T
P 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: Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 20 - BÀI TP T NG H P BÀI T
P 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 + Ngo
i Ng)/3 2.  nh d
ng c t Trung Bình theo d
ng 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 l
i 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 l
i : Yu 5. Xp h
ng 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Ê Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 21 - BÀI T
P 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 mt hàng. Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 22 - BÀI T
P 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 l
i 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 l
i thì thu là 20% Lng 5. Tính Thc Lãnh = Lng + PCCV + Thng-Thu Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 23 - BÀI T
P 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 s
n) 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 lo
i 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) Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 24 - 5.  nh d
ng c t Ngày i và Ngày n theo d
ng dd/mm/yyyy và c t T-Tin theo Currency có n v là 2ng. 6. Tính bng doanh thu theo t"ng lo
i phòng và vE  th biu di(n bng doanh thu. BÀI T
P 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í lo
i A hay lo
i B 3. Tính c t THU = GIÁ VÉ TÀU XE + CHI PHÍ Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 25 - BÀI T
P 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 hoc B và có s mét khi tiêu th >=7 Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 26 - BÀI T
P 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 Lo
i 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-Lo
i bit : Nu KQ < 5 thì hc viên “Thi HCng”, ng#c l
i thì dò tìm t" Bng Xp Lo
i. 3. M Menu Data>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 Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 27 - 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 hoc 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 lo
i 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 hoc 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 Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 28 - BÀI T
P 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 mt 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) Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 29 - BÀI T
P T-NG H.P 9 (Hàm CSDL, Trích l$c, Tham chiu Sheet) T
o Sheet TheodoiKH vi n i dung nh sau: T
o 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!. Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 30 - BÀI T
P 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 mt hàng Nhp thì n giá sE thp hn 15% (Lo
i 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 d
ng 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) Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 31 - BÀI T
P 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 l
i, bit rng: a. C t VT và n giá #c dò tìm và ly ra t" BNG THAM CHI5U và da vào mt hàng tng ng b. T-Tin = S l ng * n giá c. Ph phí = T-Tin * Ph phí ca mt 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 d
ng bng tính theo úng mu trên, các ô cha tr s #c  nh d
ng sao cho nu bng 0 thì không in ra Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 32 - 4. Da vào bng T-NG H.P DOANH S, vE  th 3-PIE phn ánh doanh s theo t"ng mt 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 Row Field: C t Ngày Column Field: C t Mt hàng Data Field: T ng ca c t Thành tin, T ng c ng. Page Field: C t Ngày Row Field: C t Khách hàng Column Field: C t Mt hàng Data Field: T ng ca c t S l#ng, Thành tin, T ng c ng. Page Field: C t Mt hàng Row Field: C t Khách hàng Column Field: C t Ngày Data Field: T ng ca c t S l#ng, Thành tin, T ng c ng. Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 33 - I TP ÔN BÀI T
P Ô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 Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 34 - BÀI T
P Ô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 l
i  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 hoc b c't i n 8. VE  th theo s li u ca bng thng kê. Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 35 - BÀI T
P Ô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 mt hàng và in vào bng 3 5. VE biu  th hi n s so sánh doanh thu ca các mt hàng ã bán 6. Lp danh sách nhng mt hàng có s l#ng bán ln nht. Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 36 - BÀI T
P Ô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 mt hàng. 2. Tính Thành tin, bit rng ký t th hai ca mã hàng cho bit mt hàng sE #c bán theo giá s (S) hoc giá l) (L). Thành tin = S l#ng x Giá S hoc 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 l
i 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 mt 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 mt hàng ã bán. 7. Lp danh sách các mt hàng bán  khu v 0 bng chc n$ng Filter. Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 37 - BÀI T
P Ô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 l
i, bit rng : a. C t VT và n giá #c dò tìm và ly ra t" BKNG THAM CHIPU và da vào mt hàng tng ng b. T-Tin = S l#ng x n giá c. Ph phí = T-Tin x Ph phí ca mt 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 d
ng 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 mt hàng 5. Lp danh sách nhng khách hàng mua hàng t" ngày 1/Apr n 30/Apr Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 38 - BÀI T
P Ô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 d
ng 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. Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 39 - BÀI T
P Ô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 H
ng CLB sau c t SL CV nu là 1: h
ng nht, 2: h
ng 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 h
ng ca CLB ó và nu CLB ó là h
ng nht sE gim 5% tin vé, ng#c l
i 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à h
ng nht và có doanh thu trên 900.000 và nu CLB là h
ng hai và có doanh thu trên 600.000 thì CLB ó “Lãi” - Ng#c l
i thì “L7” 7.  nh d
ng c t Giá vé và Doanh thu theo d
nh 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 H
ng CLB (dùng Data/ SubTotal). 9. Tính Bng thng kê Trng H Hoa Sen BT Bng tính – Excel – Bài tp tng hp - 40 - BÀI T
P Ô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 mt 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: