Tài liệu Bai tap excell

BÀI T P B NG TÍNH - EXCEL Tp HCM, tháng 03/2008 (Tài li u l u hành n i b ) -1- Tr ng H Hoa Sen BT B ng tính – Bài t p c n b n M TS IT PC NB N BÀI T P EXCEL S (REVIEW) 1 1.1 HI N / GI U THANH TR NG THÁI, THANH CÔNG TH C - Thanh tr ng thái (Status bar) : ch n menu View > Status bar (n u có d u tick phía tr c là thanh tr ng thái ang hi n ra) click chu t trái gi u ho c hi n th . - Thanh công th c (Formular bar) : ch n memu View > Formular bar ( n u có d u tíck phía tr c là thanh công th c ang hi n ra)click chu t trái gi u ho c hi n th . - Các thanh công c khác thì vào menu View > Toolbars và làm thao tác t ng t 1.2 CÁC THAO TÁC DI CHUYÊN CHU T (POINTER) - Di chuy n sang ô li n sau dùng Tab , sang ô li n tr c dùng Shift + Tab - Di chuy n n ô A22, X30,K58 trong Sheet hi n t i, ô B1, B2 trong Sheet 2, sau ó tr v ô A1 c a Sheet 1 o H ng d n: : Click chu t trái vào các thanh cu n ngang, thanh cu n d c cho n khi hi n ra ô mu n di chuy n n. click chu t vào ô này. (Cách khác là click chu t vào h p a ch n m bên trái thanh công th c, gõ a ch ô mu n di chuy n n vào ây r i Enter) - : Nh n phím F5, nh p a ch ô c n Ok. Di chuy n v ô A1 : dùng Ctrl + Home, n vào khung Reference, ch n - Di chuy n v - Di chuy n v cu i hàng : dùng End + Enter - Di chuy n v hàng cu i cùng c a b ng tính : dùng End + - Di chuy n v c t cu i cùng c a b ng tính : dùng End + - Lên m t trang màn hình : dùng Page Up - Xu ng 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 ph i m t trang mành hình : dùng Alt + PageDown - Di chuy n sang Sheet sau : dùng Ctrl + PageUp - Di chuy n v Sheet tr u m t dòng : dùng Home c : dùng Ctrl + PageDown 1.3 CH N VÙNG (RANGE) - H ng d n ch n vùng liên t c – Ví d C4:F10 o Click chu t trái vào ô u tiên c a vùng và kèo chu t n ô cu i hay lick chu t trái vào ô u , gi phím Shift và click chu t trái t i ô cu i o Di chuy n h p nh v n ô âu tiên c a vùng, gi phím Shift và dùng các phím m i tên di chuy n n ô cu i. -1- Tr ng H Hoa Sen - H o - - - Ch n vùng th nh t tr c, gi phím Ctrl trong khi ch n vùng ti p theo Ch n vùng th nh t tr c, gi phím Shift + F8 (m ch vùng ti p theo, sau ó nh n Esc H ng d n ch n toàn b m t c t o Click chu t trái vào ti u o a h p Spacebar H ng d n ch n nhi u c t : Ch n c t nh v n b t kì ô nào trong c t mu n ch n r i n Ctrl + u tiên, gi phím Ctrl và ch n c t ti p theo Ch n c t u tiên, gi phím Shift + F8 (m ch theo, sau ó nh n Esc H ng d n ch n toàn b m t hàng o Click chu t trái vào tiêu o ah p Spacebar H ng d n ch n nhi u hàng Ch n hàng nh v ADD) và ch n c t ti p c a dòng n b t kì ô nào trong hàng mu n ch n r i n Shift + u tiên, gi phím Ctrl và ch n hàng ti p theo o Ch n hàng u tiên, gi phím Shift + F8 (m ch ti p theo, sau ó nh n Esc H ng d n ch n toàn b b ng tính : o Click chu t trái t i ô tr ng n m dòng o ADD) và ch n c t o o - ng d n ch n vùng không liên t c - Ví d C4:F10 và D2:D8 .. o o - BT B ng tính – Bài t p c n b n ADD) và ch n hàng v trí giao nhau gi a tiêu c t và tiêu Dùng Ctrl + A hay Ctrl + Shift + Spacebar 1.4 CÁC THAO TÁC V B NG TÍNH (SHEET) - H ng d n ch n nhi u Sheet o Ch n nhi u Sheet liên t c : click vào sheet Sheet cu i u, gi phím Shift và click vào o Ch n nhi u Sheet r i r c : Gi phím Ctrl và click vào các Sheet mu n ch n - H ng d n i tên Sheet o Double click vào tên Sheet mu n i trên Sheet Tab ( ho c click chu t ph i vào tên Sheet mu n i và ch n Rename). Xu t hi n h p th ai Rename Sheet, xóa tên c , nh p tên m i và Enter - H ng d n chèn thêm Sheet m i o Click chu t ph i vào Sheet tab, ch n Insert. Trong h p tho i New ch n WorkSheet và Enter - H ng d n di chuy n Sheet n v trí m i trên Sheet Tab : o Click chu t vào tên Sheet mu n di chuy n, gi chu t vào kéo Sheet m i -2- n v trí Tr ng H Hoa Sen - H BT B ng tính – Bài t p c n b n ng d n xóa Sheet o Click chu t ph i vào Sheet mu n xóa, ch n Delete 1.5 DÙNG CHU T DI CHUY N, SAO CHÉP VÀ XÓA D LI U - Nh p m t chu!i kí t vào ô B2, sau ó nh p m t chu!i kí t khác vào ô D2 - Di chuy n n i dung ô B2 sang ô B3 và ô D2 sang ô E2 o H - ng d n : click và gi chu t trái t i c nh c a ô c n di chuy n r i kéo ô ó n v trí m i. Copy n i dung ô B3 sang các ô C6, D10, H15 o H ng d n : Gi phím Ctrl trong khi click và gi chu t trái t i c nh c a ô c n di chuy n r i kéo ô ó n v trí m i. - Xóa n i dung c a t t c các ô : o H ng d n : ch n các ô mu n xóa và n nút Delete 1.6 DÙNG FILL HANDLE - SAO CHÉP VÀ I N D LI U Nh p s 1 vào ô A1. Sao chép n i dung ô A1 sang các ô t" A2 n A11 o H ng d n : Click chu t trái vào Fill Handle c a ô A1, kéo xu ng cho ô A11 - i n s th t t ô B1 n n B11 o H ng d n : Nh p s 1 vào ô B1, sau ó gi phím Ctrl trong khi click chu t trái vào Fill Handle c a ô B1, kéo xu ng cho n ô B11 - Xóa d li u trong m t vùng: o H ng d n : Ch n toàn b vùng d li u c n xóa, click chu t trái vào Fill Handle c a ô góc d i bên ph i trong vùng ó, kéo ng #c lên trên và qua trái cho n khi t t c d li u trong vùng b xóa h t. -3- Tr ng H Hoa Sen BT B ng tính – Bài t p c n b n BÀI T P EXCEL S 2 (L p công th c và S d ng hàm) 1. i tên Sheet1 thành FORMULARS 2. Nh p s li u cho các ô trong c t A và B, nh p tr s cho ô H s (G3) là 15. 3. Tính k t qu cho các ô còn l i theo công th c cho 4. dòng tiêu nh d ng t t c các ô ch a giá tr d ng s theo d ng th c : #.##0,00 5. Trang trí b ng tính. 6. N u n i dung c a hai ô F3 và G3 nh p chung vào m t ô G3, thì công th c còn th c hi n #c không? H ng d n : Ch c n tính cho t t c các ô C5:K5 (dòng u tiên), sau ó ch n toàn b các ô này và dùng ch c n$ng Fill Handle copy xu ng cho nh ng dòng d i Yêu c u : Sinh viên t tìm hi u thêm và ghi nh các d ng th c khác dành cho giá tr s c ng nh cách th c s% d ng ch c n$ng Fill Handle. BÀI T P EXCEL S 3 (L p công th c và S d ng hàm) 1. Nh p và l u b ng tính v i tên Dogia.xls 2. i tên sheet là Du tru vat tu 3. Tính Thành ti n cho t"ng m t hàng 4. Tính T ng thành ti n 5. Trang trí b ng tính -4- Tr ng H Hoa Sen BT B ng tính – Bài t p c n b n BÀI T P EXCEL S 4 (S d ng hàm) 1. i tên Sheet 2 thành FUNCTIONS và th c hi n b ng tính trên. 2. S% d ng ch c n$ng Conditional Formatting c a Excel th c hi n yêu c u sau : s âm màu , s d ng màu xanh và s 0 màu tím. Sinh viên nh n xét và rút ra k t lu n v m c ích s% d ng c a ch c n$ng này. Yêu c u : Sinh viên t tìm hi u thêm và ghi nh các hàm ã này. BÀI T P EXCEL S 5 (L p công th c) Dùng các phép tính s h c tính các giá tr t i các ô tr ng -5- #c c p trong bài t p Tr ng H Hoa Sen BT B ng tính – Bài t p c n b n BÀI T P EXCEL S 6 (L p công th c và nh d ng) 1. 2. 3. 4. Tính c t L ng chính, bi t L ng chính= L ng CB * H s Tính c t Th c lãnh, bi t Th c lãnh= L ng + Ph c p Tính T ng: L ng chính, Ph c p và Th c lãnh Trang trí b ng tính BÀI T P EXCEL S 7 (S% d ng a ch tuy t i) T& giá USD – VND trong tháng 1 n$m 2008 là 16,250 VND. L p công th c tính toán và i n s li u cho c t VND trong b ng tính d i ây. 1. Sau khi l p công th c tính cho ô C5, dùng ch c n$ng Fill Handle copy công th c xu ng t t c nh ng dòng còn l i 2. Ch ra s khác bi t (và gi i thích lí do) v k t qu c a hai cách làm sau : a. T i ô C5 nh p công th c = B5*C1, Enter, dùng Fill Handle copy công th c cho các ô còn l i c a c t VND -6- Tr ng H Hoa Sen BT B ng tính – Bài t p c n b n b. T i ô C5 nh p công th c = B5*$C$1, Enter, dùng Fill Handle copy công th c cho các ô còn l i c a c t VND 3. Yêu c u nh d ng c t VND theo d ng th c Currency có Yêu c u : Sinh viên tìm hi u v cách s% d ng khi nào thì dùng lo i a ch này. a ch tuy t n v là vn . i trong Excel, và ph i n'm rõ BÀI T P EXCEL S 8 (S% d ng Công th c và Hàm) 1. 2. 3. 4. 5. Tính c t Thành ti n: S l ng * n giá Tính c t Lãi: Thành ti n * Lãi su t Tính T ng c ng Thành ti n, Lãi Tính B!ng T ng H p Trang trí b ng tính BÀI T P EXCEL S 9 ( nh d ng b ng tính) Nh p d li u và nh d ng theo các d ng th c nh b ng tính bên d i. Yêu c u: Sinh viên tìm hi u thêm và ghi nh cách s% d ng ch c n$ng Regional and Language Options trong Control Panel c a Windows nh d ng cách bi u di(n s , th i gian và ti n t … -7- Tr ng H Hoa Sen BT B ng tính – Bài t p c n b n BÀI T P EXCEL S 10 (Trang trí b ng tính) Th c hi n b ng tính d i ây - Ti n hành i màu n n và màu ch c a nh ng ô có n i dung gi ng nhau - K) khung b ng tính b ng nh ng nét khác nhau. Yêu c u : Sinh viên ph i tìm hi u và n'm rõ các thao tác óng khung, i màu n n, màu ch c a b ng tính. BÀI T P EXCEL S 11 (Hàm v chu!i: Left) Trong b ng tính d i ây, bi t hai kí t u tiên c a Mã Du L ch là Mã a Ph tách ra hai kí t ó và i n vào nh ng ô có d u ? (G#i ý : S% d ng hàm Left) -8- ng, yêu c u Tr ng H Hoa Sen BT B ng tính – Bài t p c n b n BÀI T P EXCEL S 12 (Hàm v chu!i: Right) Trong b ng tính d c u tách ra hai kí t i ây, bi t hai kí t cu i cùng c a Mã Du L ch là Mã ó và i n vào nh ng ô có d u ? (G#i ý : hàm Right) a Ph ng. Yêu a Ph ng. Yêu BÀI T P EXCEL S 13 (Hàm v chu!i: Mid) Trong b ng tính d i ây, bi t hai kí t th 2 và 3 c a Mã Du L ch là Mã c u tách ra hai kí t ó và i n vào nh ng ô có d u ? (G#i ý : hàm Mid) Yêu c u : Sinh viên tìm hi u và ghi nh cách s% d ng các Left, Right, Mid và m t s hàm x% lí chu!i kí t khác. -9- Tr ng H Hoa Sen BT B ng tính – Bài t p c n b n BÀI T P EXCEL S 14 (S% d ng hàm IF) D i ây là c t i m Thi và K t Qu trích t" b ng i m c a m t tr ng trung h c. Bi t r ng sinh viên #c coi là u n u i m thi l n h n ho c b ng 5, ng #c l i là r t Nh p s li u vào file Excel nh b ng trên, sau ó s% d ng hàm If vào c t K t qu cho t"ng sinh viên. i n giá tr BÀI T P EXCEL S 15 (Hàm If, Th ng kê n gi n) L p b ng tính sau và l u v i tên Tuyensinh.xls 1. Tính c t i"m KV: n u Khu v c là 3 thì i m KV là 3, ng #c l i là 1. 2. Tính c t T ng i"m: Anh v#n*HS2 + Toán và Tin h$c*HS1 + i"m KV. 3. Tính c t K t qu! nh sau: - N u T ng i"m t" 25 tr lê thì %t, ng #c l i là R t. - S'p x p t$ng d n theo c t T ng i"m. 4. Tính B!ng Th ng Kê. - 10 - u ho c R t Tr ng H Hoa Sen BT B ng tính – Bài t p c n b n BÀI T P EXCEL S 16 (Hàm If, x% lý chu!i) Th c hi n b ng tính sau: 1. i n d li u vào c t Xu t Nh p: N u 2 ký t u c a c t Mã là “XK” thì vi t “Xu t kh*u”, ng #c l i n u “NK” thì vi t là “Nh p kh*u”. 2. i n d li u vào c t Tên s!n ph&m: - N u ký t th 3 c a c t Mã là G: tên s n ph*m là G%o - N u ký t th 3 c a c t Mã là D: tên s n ph*m là 'ng - N u ký t th 3 c a c t Mã là T: tên s n ph*m là Than 3. i n d li u vào c t S hi u: là 3 ký t cu i c a c t Mã BÀI T P EXCEL S 17 (S% d ng hàm IF l ng nhau) D i ây là b ng i m tuy n sinh c a m t tr ng i h c. M!i sinh viên có ba c t i m Toán, Lý, Hóa và c t i m TB. Thí sinh #c coi là u n u t t c ba môn thi u có i m l n h n 4 và i m TB >=15, ng #c l i thì thí sinh ó R t. 1. Nh p s li u vào file Excel nh b ng trên, sau ó s% d ng hàm If và hàm AND giá tr u ho c R t vào c t k t qu . i n 2. N u không dùng hàm AND, thì ph i s% d ng bao nhiêu hàm If l ng nhau trong bài t p này. - 11 - Tr ng H Hoa Sen BT B ng tính – Bài t p c n b n BÀI T P EXCEL S 18 (S% d ng hàm IF l ng nhau) D i ây là b ng i m tuy n sinh c a m t tr ng i h c. M!i thí sinh có ba c t i m Toán, Lý, Hóa. T ng i m c a t"ng thí sinh #c tính là t ng c a ba c t này và i m u tiên theo t"ng khu v c c a m!i thí sinh. C th nh sau : N u thí sinh ( khu v c 1, i"m u tiên là 0, thí sinh ( khu v c 2, i"m u tiên là 1, thí sinh ( khu v c 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 c u : Sau khi tính t ng i m cho t"ng thí sinh, sinh viên nh n xét v m i liên h gi a s i u ki n c a bài toán và s hàm If #c s% d ng (G i ý : so sánh v i bài trên) BÀI T P EXCEL S 19 (S% d ng hàm IF l ng nhau) L p và gi i ph ng trình b c nh t v i các h s a,b nh và l u v i tên BacNhat.xls - 12 - Tr ng H Hoa Sen BT B ng tính – Bài t p c n b n BÀI T P EXCEL S 20 (Hàm dò tìm: VLOOKUP & HLOOKUP) M!i +,- a ph ng ng v i m t tên a ph ng . th cho / ng 1. S%0 ng 12m 0345m tham chi u tìm tên ga i và n t ng ng mã a ph ng (G i )* : 67ng 12m VLOOKUP). V i mô 4-t HLOOKUP) ng t -nh trên, th c hi n thay / ng 48 nh d i ây. (G i ): 67ng 12m Yêu c u : Sinh viên nh n 9:t v s -;1?t ra k t lu n v -vi c khi @2o s%-0 ng 12m HLOOKUP, khi @2o s%-0 ng 12m VLOOKUP; ng th i 45 m hi u thêm v .- -nh t ;1- -nh t 418ch h#p =2o ô B16, B17. (G i ): 67ng 12m VLOOKUP). N u I1 - Xem thêm -