Bài t p thực hành Microsoft Excel 2007
BÀI T P MICROSOFT EXCEL
Bài 1: Thực hành kỹ năng định d ng dữ liệu trong Excel. Yêu cầu:
a) T o m t file đặt tên là Baitap_Excel_1
- So n th o 3 sheet: TT_Chung, TT_Nhansu, TT_Hocsinh. Các ô nhập li u (Tên tr ng, Mã tr
ng ...) đặt màu nên xanh nh t.Sau khi so n th o xong hãy b ch đ hiển th l i
(Gridlines). N i dung các sheet đ c mô t nh sau 1. Sheet “TT_chung” Trang 1/21
Bài t p thực hành Microsoft Excel 2007 2. Sheet “TT_Nhansu” Tiêu đ nên màu xám Hàng này màu n n xanh Hàng này màu n n vàng Các ô nhập li u màu n n xanh nh t
3. Sheet “TT_Hocsinh”: Đ i màu n n các khu vực để d nhìn Trang 2/21
Bài t p thực hành Microsoft Excel 2007
b) Chèn thêm m t sheet vào tr
c sheet “TT_Chung” trong phần a, đặt tên là “Ho_so_truong”. N i dung nh sau: Chữ màu đ T o các đ ng link t ng ng v i các sheet để ng
i dùng click chu t vào link này s chuyển đ n sheet t ng ng.
Cách t o link đ n các sheet:
- Click chu t ph i vào chữ c n t o link -> ch n Hyperlink -> ch n Bookmark và ch n tên Sheet t
ng ng -> OK (để xóa link: Click chu t ph i vào chữ c n xóa link-> Remove Hyperlink)
Hình minh họa cách tạo Hyperlink Trang 3/21
Bài t p thực hành Microsoft Excel 2007
Bài 2: Thực hành kỹ năng định d ng dữ liệu trong Excel với các yêu cầu tương
tự Bài 1. Nội dung các sheet như sau: 1. Sheet “Huongdan” 2. Sheet “Lop_1” Trang 4/21
Bài t p thực hành Microsoft Excel 2007 3. Sheet “Lop_2” 4. Sheet “Lop_3” Trang 5/21
Bài t p thực hành Microsoft Excel 2007
Bài 3: Sử dụng Format cells (định d ng kiểu ngày, số, đơn vị ti n tệ, b ng tính…)
thực hiện chức năng thay đổi độ rộng cột,chi u cao hàng, chức năng freeze
panes, sắp x p b ng tính)
Nhập và trình bày b ng tính nh sau:
a) Tính THANHTIEN = DONGIA * SOLUONG (đ nh d ng đ n v ti n t là USD)
b) Tính THANHTIENVND = THANHTIEN * 21000 (đ nh d ng đ n v ti n t là VND, có d u phân cách hàng nghìn).
c) Sắp x p b ng tính trên theo mã hàng tĕng d n.
Bài 4: Sử dụng Format cells (định d ng kiểu ngày, số, đơn vị ti n tệ, b ng tính…)
thực hiện chức năng thay đổi độ rộng cột,chi u cao hàng, chức năng freeze
panes, sắp x p b ng tính)
Nhập và trình bày b ng tính nh sau:
a) Tính c t THÀNH TI N = SLG * Đ N GIÁ (đ nh d ng đ n v ti n t là USD)
b) Tính THU VAT = 10%* THÀNH TI N.
c) Tính T NG TI N = THÀNH TI N + THU VAT
d) Sắp x p b ng tính trên theo MÃ C.T (mã ch ng t ) tĕng d n, n u trùng mã ch ng t thì sắp
x p theo ngày nhập gi m d n.
e) Thực hi n ch c nĕng Freeze Panes cho c t dữ li u MÃ C.T . Trang 6/21
Bài t p thực hành Microsoft Excel 2007
Bài 5: Format cells, thực hành cách sử dụng ô địa chỉ tương đối, tuyệt đối Cho b ng dữ li u sau:
a) Tr giá t i v trí (1) và (3) = Đ N GIÁ * S L
NG (2 tháng có cùng đ n giá)
b) PHÍ C.CH (phí chuyên ch ) t i v trí (2) và (4) = TR GIÁ * T L C C CHUYÊN CH
(theo t ng tháng). Tính và làm tròn đ n hàng đ n v . Nên lập công th c cho 1 tháng, còn tháng
kia thì sao chép sang, dùng đ a ch tuy t đ i, h n h p.
c) C ng cho t ng nhóm lô hàng theo các c ng TR GIÁ, PHÍ C.CH . C ng TR GIÁ và PHÍ C.CH
cho các nhóm hàng ĐI N T t i các v trí (5), (6),(7), (8); cho nhóm VI TÍNH t i các v trí (9), (10), (11), (12).
d) Tính T NG C NG cho 2 nhóm theo TR GIÁ và PHÍ C.CH t i (13), (14), (15), (16). T i (13) = (5) + (9)
e) PHÍ PHÂN B t i (17) cho các mặt hàng = 50 000 (là t ng phí phân b ), chia cho T NG
TR GIÁ và PHÍ C.CH trong c 2 tháng c a t t c các mặt hàng ĐI N T và VI TÍNH và
nhân cho T NG TR GIÁ và PHÍ PHÂN B trong 2 tháng c a t ng mặt hàng.
Hướng d n: Phí phân b t i (17) = 50000/t ng (13+14+15+16) x t ng(1+2+3+4). Nh c đ nh
đ a ch ô c a những ô có ý nghĩa t ng c ng. Phí phân b c a Mouse t i 18 = 50000 - t ng các
phí phân b c a các mặt hàng đã đ c tính trên. Trang 7/21
Bài t p thực hành Microsoft Excel 2007
Bài 6: Một số hàm cơ b n: ROUND, MIN, MAX, AVERAGE, SUM, RANK, AND, OR, IF Cho b ng dữ li u sau:
a) Tính ĐTB (điểm trung bình) = (TOAN *2 + VAN*2 + NGOAINGU)/5. Làm tròn đ n 2 chữ s thập phân.
b) Đi n vào c t KQ n u ĐTB >=5 đi n là “Đ t”, ng c l i là “R t”.
c) Tính điểm trung bình, cao nh t, th p nh t, x p h ng.
d) Thêm vào c t KHEN TH
NG sau c t X P H NG, đi n dữ li u cho c t KHEN TH NG nh sau: h ng 1 th ng 200.000, h ng 2 th ng 100.000, còn l i không đ c th ng. Trang 8/21
Bài t p thực hành Microsoft Excel 2007
Bài 7: Một số hàm cơ b n: ROUND, MIN, MAX, AVERAGE, SUM, RANK, AND, OR, IF
Cho b ng dữ li u sau: a) Th ng 8-3: th
ng 200.000 cho những nhân viên Nữ, còn l i không đ c th ng. b) Th ng A: th
ng 300.000 cho những nhân viên có ngày công >=24, còn l i không đ c th ng. c) Thêm vào c t Th ng B: th
ng 100.000 cho những nhân viên Nam có ngày công >26 hoặc
nhân viên Nữ có ngày công >25.
Bài 8: Một số hàm cơ b n: ROUND, MIN, MAX, AVERAGE, SUM, RANK, AND, OR, IF
Cho b ng dữ li u sau:
a) Thêm vào c t Tu i k c t ngày, sau đó tính tu i c a nhân viên. b) Tính l
ng c a nhân viên = LCB*NGÀY. c) Tính t m ng = 80%*L NG.
d) Thêm vào m t c t TH NG k c t L NG, tính th
ng theo yêu c u sau: n u ch c vụ là GĐ th ng 500000, PGD th ng 400000, TP th ng 300000, PP th ng 200000, còn l i th ng 100000.
e) Thêm vào c t CÒN L I cu i b ng tính, tính CÒN L I =L NG + TH NG - T M
NG. Tính t ng c ng, bình quân, cao nh t, th p nh t. Trang 9/21
Bài t p thực hành Microsoft Excel 2007
Bài 9: Xử dụng hàm INT, MOD và một số hàm xử lý chuỗi (LEFT, RIGHT, MID....) Cho b ng dữ li u sau:
a) Cĕn c vào ký tự đ u tiên c a CH NG T để phân b s l ng vào các c t S L NG c a XĔNG, GASOIL và D U L A.
• N u ký tự đ u c a ch ng t là X thì s l ng đ c phân b vào c t XĔNG.
• N u ký tự đ u c a ch ng t là G thì s l ng đ c phân b vào c t GASOIL.
• N u ký tự đ u c a ch ng t là L thì s l ng đ c phân b vào c t D U L A.
b) Tính thành ti n cho m i c t = S L
NG *Đ N GIÁ, trong đó Đ N GIÁ dựa vào b ng
giá, có 2 lo i giá: giá cung c p (CC) và giá kinh doanh (KD); n u ký tự ph i c a ch ng t là C thì l y giá cung c p, ng c l i l y giá kinh doanh.
c) Tính t ng và bình quân ngày (T ng c ng/30) cho m i c t.
Bài 10: Xử dụng hàm INT, MOD và một số hàm xử lý chuỗi (LEFT, RIGHT, MID....) Cho b ng dữ li u sau:
Câu 1: Tính s ngày thuê = NGÀY TR - NGÀY THUÊ
Câu 2: Tính s tu n, s ngày lẻ (dùng hàm INT, MOD)
Câu 3: Tính t ng s ngày thuê, t ng s tu n, t ng s ngày lẻ (dùng ch c nĕng AutoSum) Trang 10/21
Bài t p thực hành Microsoft Excel 2007
Bài 11: Hàm dò tìm (VLOOKUP), chức năng rút trích dữ liệu Cho b ng dữ li u sau: STT Họ tên
Mã ngạch Tên ngạch Tên đơn vị
Lương cơ bản Thực lĩnh 001 Đào Hoa Mai 1003 Phòng Hành chính 1,200,000 002 Ngô Vĕn Nhu 1002 Phòng Hành chính 1,850,000 003 Nguy n H ng 1001 Phòng QLCL 1,600,000 004 Qu c Khánh 1003 Phòng Khoa h c 950,000 005 Ph m Thành 1002 Phòng Qu n tr 1,000,000 006 Tr n Thuỷ 6033 Phòng Tài chính 2,000,000 007 Nguy n H ng 1003 Phòng Thi t b 2,200,000 008 Lê Dung 1003 Phòng Kinh doanh 1,800,000
Bảng mã ngạch và phụ cấp Mã ngạch Tên
ngạch công chức Phụ cấp (%) 1001 Chuyên viên cao c p 0.25 1002 Chuyên viên chính 0.20 1003 Chuyên viên 0.10 6033 Kỹ s 0.05
Yêu c u: Dựa vào B ng danh mục “B ng mã ng ch và phụ c p” hãy dùng hàm VLOOKUP để
đi n dữ li u vào 2 c t Tên ng ch và Thực lĩnh. Trong đó: Thực lĩnh = L ng c b n + (Phụ c p * L ng c b n) Trang 11/21
Bài t p thực hành Microsoft Excel 2007
Bài 12: Hàm dò tìm (VLOOKUP), chức năng rút trích dữ liệu Cho b ng dữ li u sau:
a) Dựa vào mã hàng và b ng tra dữ li u, đi n s li u cho các c t: Tên hàng, giá nhập, SL nhập (s l ng nhập), SL xu t (s l ng xu t).
b) Tính thành ti n nhập dựa vào giá nhập và s l ng nhập.
c) Tính giá xu t dựa vào mã hàng: n u mã hàng có ký tự th 4(tính t bên trái) là A thì giá xu t =
giá nhập + 15, n u là B thì giá xu t = giá nhập + 12, còn l i giá xu t = giá nhập + 10
d) Tính ti n xu t dựa vào giá xu t và s l
ng xu t, đ nh d ng đ n v ti n t là USD.
e) Tính t ng c ng cho m i c t
f) Trích ra danh sách các mặt hàng có ti n xu t >= 20000
Câu 7: Chèn thêm c t Ghi chú cu i. Đi n thông tin cho c t ghi chú nh sau: n u SL nhập –
SL xu t >=60 thì ghi “Bán chậm”, n u SL nhập – SL xu t >=30 thì ghi “Bán đ c”, còn l i ghi “Bán ch y”. Trang 12/21
Bài t p thực hành Microsoft Excel 2007
Bài 13: Hàm dò tìm (HLOOKUP), chức năng rút trích dữ liệu, các hàm thống kê Cho b ng dữ li u sau:
a) S ngày = ngày đi – ngày đ n, n u ngày đi trùng v i ngày đ n thì tính 1 ngày.
b) Giá phòng dựa vào lo i phòng và b ng đ n giá phòng, n u phòng có 1 ng i thuê thì l y giá 1, n u có t 2 ng i tr lên thì giá 2.
c) Giá phụ thu dựa vào lo i phòng và b ng giá phụ thu
d) Ti n phòng = s ngày *(giá phòng + giá phụ thu), nh ng n u khách thuê phòng trên 10 ngày thì đ c gi m 10% giá phụ thu.
e) Thực hi n b ng th ng kê t ng ti n theo t ng lo i phòng
f) Trích ra danh sách khách thuê phòng lo i A.
g) Trích ra danh sách khách thuê phòng lo i B và có s ng i > 2. Trang 13/21
Bài t p thực hành Microsoft Excel 2007
Bài 14: Các hàm thống kê, biểu đồ Cho b ng dữ li u sau: a) Đi n tên tr
ng dựa vào ký tự bên trái c a Mã HS và B ng tra tên tr ng.
b) Đi n môn thi dựa vào ký tự cu i c a Mã s và b ng tra môn thi.
c) X p lo i dựa vào b ng x p lo i
d) X p h ng dựa vào điểm thi
e) Trích ra danh sách các thí sinh thu c tr ng Võ Tr
ng To n (l u ý: đ nh d ng l i tiêu đ H VÀ TÊN nằm 2 ô t
ng ng v i c t H , c t Tên r i m i rút trích).
f) Trích ra danh sách h c sinh x p h ng t 5 tr lên.
g) Thực hi n b ng th ng kê sau:
Môn Ngoại ngữ Toán Văn Trường Đinh Tiên Hoàng Hòa Bình Nguy n Du Võ Tr ng To n
h) V đ th biểu di n dữ li u cho b ng th ng kê trên. Trang 14/21
Bài t p thực hành Microsoft Excel 2007
Bài 15: Bài t p tổng hợp Cho b ng dữ li u sau:
a) Tính c t thành tích dựa vào gi xu t phát và đ n đích
b) X p h ng dựa vào c t thành tích
c) Tính thành tích cá nhân cao nh t, trung bình và th p nh t
d) Tính t ng s gi c a toàn đ i A, B, C.
e) Tính s vận đ ng viên c a m i đ i.
f) Tính thành tích trung bình c a m t vận đ ng viên trong m i đ i. Trang 15/21
Bài t p thực hành Microsoft Excel 2007
Bài 16: Bài t p tổng hợp Cho b ng dữ li u sau: 1. Đ i t
ng dựa vào ký tự th 2 c a s báo danh, n u là A thì u tiên 1, là B thì u tiên 2, còn l i để tr ng.
2. Ngành h c dựa vào ký tự th nh t c a s báo danh và B ng điểm chuẩn
3. Đi n dữ li u cho c t k t qu : n u điểm t ng > điểm chuẩn thì k t qu là đậu, ng c l i là r t.
4. Thực hi n b ng th ng kê theo mẫu. Trang 16/21
Bài t p thực hành Microsoft Excel 2007
Bài 17: Bài t p tổng hợp Cho b ng dữ li u sau:
a) Dựa vào ngày đ n và ngày đi để xác đ nh s tu n, s ngày lẻ
b) Dựa vào lo i phòng và b ng đ n giá phòng để đi n dữ li u cho đ n giá tu n và đ n giá ngày.
c) C t phụ thu: n u 1 ng
i tính theo phụ thu c a phòng đ n, 2 ng i tr lên tính phụ thu c a phòng đôi.
d) Tính t ng ti n = đ n giá tu n * s tu n + đ n giá ngày * s ngày + phụ thu, nh ng n u đ n
giá ngày * s ngày > đ n giá tu n thì l y đ n giá tu n.
e) Trích ra danh sách các phòng có t ng ti n >300.
f) Thực hi n b ng th ng kê sau: Trang 17/21
Bài t p thực hành Microsoft Excel 2007
Bài 18: Bài t p tổng hợp Cho b ng dữ li u sau:
a) Dựa vào s báo danh để đi n dữ li u vào c t kh i thi
b) Dựa vào b ng u tiên khu vực dự thi để đi n vào c t Di n u tiên. c) Điểm u tiên đ c tính nh sau:
- Dựa vào b ng khu vực dự thi -
Điểm u tiên ch đ c tính khi không có môn dự thi nào là điểm 0 d) Tính t ng điểm.
e) Đi n dữ li u cho c t k t qu : n u t ng điểm l n h n điểm chuẩn thì ghi “Đậu”, ng c l i ghi
“R t”. Bi t rằng điểm chuẩn kh i A là 20, kh i B là 21. f) Thực hi n b ng th ng kê
g) Tính t l Đậu/R t: s h c sinh thi đậu/r t chia cho t ng s h c sinh dự thi * 100.
h) Xác đ nh điểm th khoa theo kh i dự thi.
i) V đ th biểu di n s h c sinh đậu, r t theo t ng kh i. Trang 18/21
Bài t p thực hành Microsoft Excel 2007
Bài 19: Bài t p tổng hợp. Cho các b ng dữ liệu sau:
a) T o “B NG THANH TOÁN TI N SÁCH” nh trên.
T o công th c tính c t Loại hàng là giá tr c a ký tự cu i cùng c a Mã hàng. Chèn giữa 2
c t Đơn giá và Thành tiền m t c t m i có tên là Tên hàng. Lập công th c cho c t
Tên hàng bi t giá tr c a ký tự đ u c a Mã hàng là Tên hàng.
b) Tính c t Đơn giá: Đ n giá là ký tự đ u c a Mã hàng và dò trong Bảng giá , n u Lo i hàng
= 1 thì l y Giá_1, n u Lo i hàng = 2 thì l y Giá_2 c) Thành tiền = S l
ng * Đ n giá. Sắp x p b ng tính theo th tự tĕng d n c a Thành Tiền
d) Phụ thu = Thành ti n * % Phụ thu % phụ thu là ký tự th 4 (tr c ký tự cu i cùng) tra
trong Bảng % phụ thu
e) Tổng tiền = Thành ti n + Phụ thu
Tổng c ng 3 c t : Thành ti n, Phụ thu và T ng ti n
f) Lập công th c tính: Tổng số lượng và Tổng tiền t ng lo i hàng trong Bảng tổng hợp
g) Rút trích (dùng ch c nĕng Advanced Filter) đ y đ thông tin những mẫu tin có đi u ki n
sau: (Số lượng >=200) và (kí tự đầu của Mã hàng là V hoặc G)
h) Th ng kê t ng s cu n sách theo t ng tên sách nh b ng sau:
i) Th ng kê t ng ti n thanh toán cho những sách có s l ng > 100
(dùng công th c m ng ={SUM(IF… )} ) theo t ng lo i sách và tên sách nh b ng sau: Trang 19/21
Bài t p thực hành Microsoft Excel 2007
Bài 20: Cho b ng tính sau, vẽ biểu đồ như trong hình (chỉ hiển thị dữ liệu cho tháng 1 và tháng 2)
a) Lập b ng dữ li u và v biểu đ nh sau: Tháng 1 Tháng 2 Tháng 3 Quầy A 40 20 40 Quầy B 40 30 20 Quầy C 20 50 40
DOANH THU 3 QUAÀY TRONG 2 THAÙNG 60 40 eàn Thaùng 1 Ti Thaùng 2 20 0 Quaày A Quaày B Quaày C TEÂN QUAÀY Yêu c u:
- Thay đ i kiểu tô n n cho Series dữ li u
- Di chuyển khung Legend đ n các v trí khác nhau.
- Chèn thêm m t Series dữ li u tháng 3
- S a l i tiêu đ chính c a đ th thành “Doanh thu 3 qu y trong 3 tháng)
2. T đ th trên, s a l i đ th sau:
Doanh thu 3 quaày trong 3 thaùng 60 50 40 40 40 40 ) $ 40 30 ( n 20 20 20 eà 20 ti 0 Thaùng 1 Thaùng 2 Thaùng 3 Thôøi gian Quaày A Quaày B Quaày C Trang 20/21