BaiTap MS Excel

Document Sample
BaiTap MS Excel Powered By Docstoc
					Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 47

Bài Tập 1
Tạo bảng tính sau: STT 01 02 03 04 05 06
1. 2. 3. 4.

Họ tên Hoàng Văn Tám Nguyễn Thị Hồng Ngô Thị Nga Trần Thiên Thu Lâm Ðài Cát Lê Hoài Sơn Tổng cộng:

Chức vụ TP NV PP NV KT BV

LCB 1200 1600 1500 1300 1800 1300 ?

Ngày LV 25 24 26 20 23 24 ?

Lƣơng

Tạm ứng

Còn lại

?

?

?

Lƣơng = LCB * Ngày LV Tạm ứng = 2/3 * lƣơng. Còn lại = Lƣơng - tạm ứng. Tính tổng ở các cột LCB, Lƣơng, Tạm ứng, Còn lại.

Bài Tập 2
BẢNG KÊ HÀNG NHẬP
Tháng / Kho Long Bình

C/từ B50 B51 B52 B53 B54 B55 B56 B57 B58
1. 2. 3.

Diễn giải Bàn gỗ Bàn sắt Bàn formica Ghế dựa Ghế xếp Tủ kiếng Tủ gỗ Nệm Mouse Nệm bông Tổng cộng: Thuế = trị giá * 4.25%

Số lƣợng 40 46 72 120 38 41 15 30 105

Ðơn giá 25 30 20 8 12 42 40 14 18

Trị giá

Thuế

Cƣớc CC

Cộng

?

?

?

?

Trị giá = số lƣợng * đơn giá. Cƣớc chuyên chở đƣợc tính theo số lƣợng * tỉ lệ. Nếu Số lƣợng <= 50 thì Tỉ lệ = 0, số lƣợng > 50 và < 80 thì tỉ lệ là 1.12, nếu số lƣợng >= 80 và <100 thì tỉ lệ là 1.5, >= 100 là 1.7. Cộng = trị giá + thuế + cƣớc cc. Tính tổng cộng các cột (ở vị trí các dấu ?).

4. 5.

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 48

Bài Tập 3
1.

Tạo bảng tiền thƣởng nhƣ sau:
CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM Ðộc Lập - Tự Do - Hạnh Phúc BẢNG TIỀN THƢỞNG
STT HỌ TÊN LCB NGÀY CÔNG THƢỞNG XẾP LOẠI

1 2 3 4 5 6 7
2.

Trƣơng Thị Chi Nguyễn An Ðặng Phƣớc Cƣờng Nguyễn Ðức Duy Nguyễn Vũ Hoàng Phạm Cao Minh Nguyễn Chí Tài

500000 450000 250000 350000 220000 220000 300000

25 24 20 21 14 17 18

Tiền thƣởng đƣợc tính nhƣ sau: Nếu ngày công >= 20 thƣởng 10% của LCB Nếu ngày công trong khoảng từ 15 đến dƣới 20 thì thƣởng 8% của LCB. Nếu ngày công là 15 thì thƣởng 5% của LCB. Ngoài ra thƣởng = 0. Xếp loại đƣợc tính nhƣ sau: Nếu đƣợc thƣởng (thƣởng <> 0) và ngày công > 20 thì loại là “A”, ngày công từ 15 đến 20 thì loại “B”. Ngƣợc lại (thƣởng = 0) thì loại là “Không đạt".

3.

Bài Tập 4
1.

Tạo bảng tính có nội dung sau:
BẢNG ÐIỂM HỌC SINH
XẾP LOẠI

STT

HỌ TÊN

VĂN

TOÁN

ANH

LÝ

ÐTB

HỌC BỔNG

1 2 3 4 5 6 7
2. 3.

Nguyễn Văn Tuấn Trần Thị Liên Lê Cẩm Hòa Phan Quỳnh Nhƣ Lý Mỹ Hạnh Dƣơng Cẩm Tú Lê Thùy Mai

6 5 4 8 7 8 5

7.5 7 4 8 8 9 5

4 9 5 9 9 9 4

8 7 6 4 8 9 3

Ðiểm trung bình (ÐTB) = (văn * 2 + toán * 2 + anh + lý)/6, lấy 1 số lẻ. Xếp loại đƣợc tính theo yêu cầu sau: Nếu ÐTB >= 8 và môn toán >= 7, xếp loại giỏi. Nếu ÐTB >= 7, xếp loại khá. Nếu ÐTB >= 5, xếp TB. Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 49

4. 5.

Còn lại xếp loại yếu. Thêm cột Xếp hạng vào trƣớc cột học bổng và dựa vào điểm trung bình để xếp hạng cho từng học sinh. Ðiền dữ liệu vào cột học bổng: Xếp loại giỏi: 50000. Xếp loại khá: 30000. Còn lại 0. Ðếm xem có bao nhiêu ngƣời đƣợc lập trong danh sách trên.

6.

Bài Tập 5
Stt 1 2 3 4 5 Mã hàng TVC14SY COAT4DL VDH44JC TVS21PL COAT3CQ Tên Hàng Giá gốc 2800000 4000000 4200000 5200000 7600000 Thời gian trả góp 3 9 6 3 6 Lãi suất Giá đã Trả tính lãi trƣớc 2% Số tiền trả từng tháng

Yêu cầu: 1. Tên hàng: Dựa vào 2 ký tự đầu của Mã hàng: TV  “Ti vi”; CO  “Máy vi tính”; VD  “Đầu Video”. 2. Giá đã tính lãi= Giá gốc + Lãi. (Biết rằng Lãi = Giá gốc * Lãi suất * Thời gian trả góp). 3. Trả trƣớc = 30% * Giá đã tính lãi. 4. Số tiền trả từng tháng = (Giá gốc - trả trƣớc) / Thời gian trả góp. 5. Định dạng cột số tiền trả từng tháng có thêm chữ “Đồng”.

Bài Tập 6
BẢNG ĐIỂM VÀ HỌC BỔNG CỦA SINH VIÊN Stt 1 2 3 4 5 6 Họ Lê Văn Hồ Thanh Lý Ngọc Nguyễn Âm Văn Trần Tên M K S T B K Mã số C4A C2B Q1A D4C C1D Q5B Cơ 8 10 6.5 8 6 9 Điểm Quang Điện 8.5 9 6 9 9 10 9 7 10 7.5 9 8 Tổng Xếp loại Học bổng

Yêu cầu tính: 1. Tổng: là tổng số 3 môn: Cơ, Điện, Quang, trong đó có một môn nhân hệ số 2 đƣợc tính nhƣ sau:  Ký tự đầu của mã số là C, môn nhân hệ số 2 là môn Cơ.  Ký tự đầu của mã số là D, môn nhân hệ số 2 là môn Điện.  Ký tự đầu của mã số là Q, môn nhân hệ số 2 là môn Quang. 2. Xếp loại đƣợc tính nhƣ sau: Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 50

 Tổng >= 34 và không có môn nào dƣới 8 thì xếp loại “Giỏi”.  Tổng >= 30 và không có môn nào dƣới 7 thì xếp loại “khá”.  Các trƣờng hợp còn lại để trống. 3. Học bổng: Đối với học sinh xếp loại Giỏi thì học bổng là 70000, xếp loại khá thì học bổng là 40000, các trƣờng hợp còn lại học bổng là 0. Định dạng cột học bổng có thêm chữ “Đồng”.

Bài Tập 7
BẢNG LƢƠNG NHÂN VIÊN Stt Chức vụ Thống TP Hồng NV Trân PP Thanh NV Nam KT Sơn BV Tâm PP Bình GD Thắng BV Nga NV Tùng TX Phƣợng NV Thành PGD Minh TP Thƣơng BV Tổng cộng Trung bình Cao nhất Thấp nhất Tên Lƣơng Căn bản 1200 1600 1500 1300 1800 1500 1400 1600 1500 1300 1800 1500 1800 1600 1300 Ngày công 25 24 26 20 23 24 27 14 17 23 22 14 23 27 26 Pccv Lƣơng Tạm ứng Còn lại

Yêu cầu: 1. Nhập số thứ tự. 2. Tính Pccv (Phụ cấp chức vụ) theo yêu cầu sau:  Nếu chức vụ là GD thì Pccv là 50000  Nếu chức vụ là PGD hoặc TP thì Pccv là 40000  Nếu chức vụ là PP hoặc KT thì Pccv là 30000  Nếu chức vụ là BV và có ngày công >= 22 thì Pccv là 40000  Các trƣờng hợp còn lại Pccv = 0. 3. Lƣơng = Lƣơng căn bản * Ngày công. Nếu ngày công > 24 thì mỗi ngày dƣ ra đƣợc tính gấp đôi. 4. Tạm ứng = 2/3* (lƣơng + Pccv), tính tròn đến hàng ngàn. Tạm ứng không vƣợt quá 25000. 5. Tính tổng, trung bình, cao nhất, thấp nhất của các cột Ngày công, Pccv, Tạm ứng. 6. Còn lại = (Pccv + Lƣơng) - Tạm ứng.

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 51

Bài Tập 8
DANH SÁCH SINH VIÊN ÐƢỢC CẤP HỌC BỔNG Ngày a tháng b năm c
STT HỌ TÊN SV NGÀY SINH NƠI SINH MÃ ÐIỂM ÐTB DIỆN MỨC HB

1 2 3 4 5 6 7 8 9 10

Nguyễn Văn Lâm Trần Văn Nam Lê Tú Lệ Vũ Nam Phạm Thị Hoa Tạ Ánh My Ðỗ Hoàng Lý Thu Minh Dƣơng Ngọc Hoàng Lê Ngọc Hoa

14/07/80 10/10/80 22/02/80 03/04/81 01/01/80 02/02/81 20/12/81 02/01/81 10/10/80 12/10/81
ÐTB 10 3 7.5 9 8.5 8 7 7.5 6

TPHCM TPHCM HUẾ LONG AN LONG AN TPHCM HUẾ TPHCM TPHCM TPHCM
ÐTVP 8 4 8 8.5 9 10 7.5 6 2

B01 C03 C01 A01 B01 C02 A02 B02 B03 A03
NNLT 10 7 8.5 7.5 9.5 9 8 9 5

A A B C B B A A B C

BÀNG ÐIỂM: MÃ ÐIỂM A01 A02 B03 A03 B01 B02 C03 C02 C01 1. 2. 3. 4.

Ðiền số thứ tự. Lập công thức ngày, tháng, năm lần lƣợt tại a, b, c. Tìm điểm trung bình căn cứ vào mã điểm và Bảng điểm, làm tròn đến phần trăm. Mức HB (học bổng) đƣợc tính nhƣ sau: 9<= ĐTB <10 và Diện là “A” thì học bổng 1000000 9<= ĐTB <10 và Diện là “B” thì học bổng 500000. 8<= ĐTB <9 và Diện loại là “A” thì học bổng 300000. Các trƣờng hợp khác học bổng = 0.

Bài Tập 9
1.

Tạo bảng tính sau: Bảng tính phụ cấp Bảng tính thuế
Giới hạn < < Ðịnh mức 60000 100000 Tỷ lệ thuế 0.01% 1.00% 1 8000 6500 2 7000 5500 LCB 520 480

CV A B

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông C F E 5000 3500 7000 4500 3000 6000 420 380 380 < > 150000 150000

Trang 52 1.50% 2.00%

BẢNG KÊ THUẾ THU NHẬP
STT 1 2 3 4 5 6 7 MNV A01 B02 C01 B01 C02 E02 D02 CỘNG NGÀYCÔNG 20 26 20 25 20 25 24 7000000 PCCV LƢƠNG CHÍNH THƢỞNG LƢƠNG THUẾ

2. 3. 4.

Chèn cột LCB (lƣơng căn bản) vào giữa MNV (mã nhân viên) và NGÀY CÔNG. Dựa vào ký tự bên trái của MNV lập công thức tính LCB từ Bảng tính phụ cấp vào Bảng tính thuế. Dựa vào ký tự bên trái và ký tự bên phải (cấp bậc) của MNV để tính mức phụ cấp chức vụ PCCV theo Bảng tính phụ cấp (yêu cầu dùng Hlookup hoặc Vlookup phối hợp với hàm Match). Tính Lƣơng chính = LCB * ngày công, nhƣng nếu ngày công > 25 thì mỗi ngày dƣ ra đƣợc tính thành 2 ngày công. Tính tổng ở các cột ngày công, PCCV, lƣơng chính. Tổng thƣởng = tổng lƣơng - tổng PCCV - tổng Lƣơng chính. Tính thƣởng cho từng ngƣời = (tổng thƣởng / tổng ngày công) * Ngày công. Tính lƣơng cho từng ngƣời = PCCV + lƣơng chính + thƣởng (Thuế = lƣơng * tỉ lệ thuế)

5. 6. 7. 8. 9.

10. Căn cứ vào tiền lƣơng và Bảng tính thuế, hãy tính thuế cho từng đối tƣợng. 11. Tính tổng thuế.

Bài Tập 10
1.

Tạo bảng tính:

BẢNG TÍNH TÌNH HÌNH TIÊU THỤ ÐIỆN
STT 1 2 3 4 5 6 7 8 SỐ ÐIỆN KẾ A001 D002 B001 C002 E001 C002 E002 B002 ÐỊNH MỨC 180 160 200 160 180 220 210 180 SỐ CŨ 2500 2300 5600 2500 3200 4400 2800 4500 SỐ MỚI 2900 2450 5700 2720 3450 4590 3000 4980 TIÊU THỤ TIỀN TRONG ÐM TIỀN VƢỢT ÐM TỔNG

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông 9 10 D001 A002 200 180 2400 1880 2600 2200

Trang 53

Bảng đơn giá
KHU VỰC A B C D E ÐGL1 500 460 450 420 410 ÐGL2 450 450 430 410 400

Bảng hệ số vượt định mức
VƢỢT ÐM < < >= SỐ LẦN 1 2 2 HỆ SỐ 1.5 2.0 3.0

2. 3.

Tiêu thụ số mới - số cũ. Căn cứ vào ký tự bên trái, phải của số điện kế, bảng đơn giá, tính tiền trong định mức (= số tiêu thụ * đơn giá). Biết rằng nếu tiêu thụ > định mức thì lấy định mức, ngƣợc lại thì lấy tiêu thụ. Ví dụ: D002 -> đơn giá là 410. Căn cứ vào ký tự bên trái, phải của số điện kế, bảng đơn giá, bảng hệ số vƣợt định mức để tính tiền vƣợt định mức (=số KW vƣợt định mức * đơn giá* hệ số vƣợt định mức). Tính tổng của cột trong định mức và cột vƣợt định mức. Trích lọc trong bảng tính các dòng có ký tự bên phải của số điện kế là “1”.

4.

5. 6.

Bài Tập 11
1. 2. 3. 4. 5. 6.

Lập bảng tính nhƣ mẫu dƣới đây. Chèn thêm cột đơn giá vào bên trái cột Cƣớc phí. Tính đơn giá dựa vào cột lộ trình, bảng đơn giá và thời gian qui định. Tính cƣớc phí = đơn giá * SL. Nếu số lƣợng chở ít hơn trọng tải xe trong bảng qui định trọng tải xe, thì tính nguyên giá, ngƣợc lại tính 105% của cƣớc phí. Chèn cột thời gian thực hiện vào trƣớc cột thƣởng và tính bằng công thức = ngày đến - ngày đi. Nếu ngày đi = ngày đến thì thời gian thực hiện là 1. Tính thƣởng: Nếu thời gian thực hiện ít hơn thời gian qui định trong bảng đơn giá và thời gian qui định thì thƣởng 5% của cƣớc phí, ngƣợc lại thƣởng = 0. Tạo biểu đồ Line thể hiện tiền thƣởng của các đầu xe.

BẢNG TÍNH CƢỚC PHÍ VẬN TẢI
SỐ XE SỐ LƢỢNG LỘ TRÌNH CƢỚC PHÍ NGÀY ÐI NGÀY ÐẾN THƢỞNG

50-2923 52-1234 50-9553 51-1111 52-2222 52-1234 50-4455 51-1111

5 10 2 6 5 10 4 1

Pleiku Qui Nhơn Đà Lạt Đà Lạt Hà Nội Lào Lào Đà Nẵng

01/05/04 03/05/04 03/05/04 06/05/04 10/05/04 20/05/04 21/05/04 25/10/04

03/05/04 04/05/04 05/05/04 06/05/04 16/05/04 26/05/04 27/05/04 12/05/04

Bảng qui định trọng tải

Bảng đơn giá và thời gian qui định

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông SỐ XE TRỌNG TẢI TUYẾN ÐƢỜNG ÐƠN GIÁ

Trang 54 THỜI GIAN

50 51 52

4 8 12

Hà Nội Đà Nẵng Qui Nhơn Pleiku Lào Campuchia Đà Lạt

10000 5000 4000 6000 25000 20000 3000

5 7 2 4 8 6 1

Bài Tập 12
BẢNG TÍNH HỆ SỐ SỐ NĂM CÔNG TÁC MÃ LOẠI 13 46 78 A 10 12 15 B 9 11 13 C 9 10 12 D 8 9 11

BẢNG LƢƠNG THÁNG 02/04
STT TÊN MÃ SỐ LCB NGÀY CÔNG MÃ LOẠI SNCT HỆ SỐ LƢƠN G PHỤ CẤP THỰC LÃNH

01 02 03 04 05 06 07 08
1.

NAM THU HOA ĐỒNG THANH QUANG KHANH ÐỨC

A4 C2 D1 B4 B7 C5 D2 B3

600 480 390 520 520 480 390 520

23 25 28 23 24 22 28 22

Lập bảng tính nhƣ trên, biết: Mã loại là ký tự bên trái của mã số. Tƣơng ứng với mỗi mã loại và số năm công tác trên bảng hệ số là hệ số tƣơng ứng. Lập công thức tìm mã loại. Số năm công tác (SNCT) là ký tự bên phải của mã số và chuyển thành giá trị số. Hệ số dựa vào mã số (hay mã loại), SNCT dò trong Bảng tính hệ số. Lƣơng = Hệ số * LCB * Ngày công (lƣu ý nếu ngày công > 24 đƣợc tính gấp đôi). Tính phụ cấp, biết rằng mỗi năm công tác đƣợc tính phụ cấp là 10000 và nếu mã loại là A thì phụ cấp đƣợc cộng thêm 6000. THỰC LÃNH = LƢƠNG + PHỤ CẤP. Với THỰC LÃNH không vƣợt quá 220000 và không thấp hơn 140000. Tạo biểu đồ biểu diễn THỰC LÃNH của những đối tƣợng theo dạng Pie.

2. 3. 4. 5. 6. 7. 8.

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 55

9.

Tạo vùng dữ liệu có mã số là “B” và có ngày công từ 24 trở lên.

Bài Tập 13
1.

Tạo bảng tính sau:

KẾT QUẢ THI HỌC KỲ
STT HỌ TÊN ÐẠO ÐỨC TOÁN LÝ TIN HỌC ÐTB ÐKQ LOẠI HẠNG HỌC BỔNG

01 02 03 04 05 06 07 08
2. 3.

Trần Anh Hoàng Liên Văn Thụ Uyên Nhi Hoàng Hoa Hồng Hà Lệ Mỹ Vân Nam

A B C D A C B C

7 9 9 4 6 9 8 5

8 8 9 3 4 9 7 4

5 7 10 2 5 10 8 7

Tính ÐTB (Điểm trung bình) biết rằng toán và lý có hệ số 2, tin học hệ số 3. ÐKQ (Điểm kết quả) đƣợc tính nhƣ sau: Nếu đạo đức là A, ĐKQ = ÐTB + 0.5 Nếu đạo đức là B, ĐKQ = ÐTB + 0.25 Những trƣờng hợp khác ĐKQ = ÐTB. Xếp loại dựa vào ÐKQ, với qui định nhƣ sau: Nếu ÐTB < 5, xếp loại yếu. Từ 5  <7 xếp loại trung bình. Từ 7  < 8 xếp loại khá. >= 8 xếp loại giỏi. Xếp hạng dựa vào ÐTB. Tính học bổng dựa vào xếp hạng nhƣ sau: Từ hạng 1 – 2: học bổng 50000. Từ hạng 3 – 4: học bổng 30000. Từ hạng 5 – 6: học bổng 10000. Ngoài ra học bổng = 0. Sắp xếp danh sách trên theo tên với thứ tự tăng dần. Tạo vùng dữ liệu chứa danh sách các học viên nhận học bổng. Tạo biểu đồ dạng 3D column biểu diễn đạo đức của lớp.

4.

5. 6.

7. 8. 9.

Bài Tập 14
1. 2. 3.

Tạo bảng tính theo mẫu ở dƣới. Sắp xếp bảng tính theo thứ tự tăng dần của vùng TÊNHV, nếu trùng thì sắp xếp giảm dần theo NĂM SINH. Lần lƣợt trích lọc những mẫu tin theo các yêu cầu sau: Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 56

STT 01 02 03 04 05 06 07 08

Có ÐTB = 11. Có ÐTB = 10 và năm sinh < 1978. Có điểm văn > 9 và là phái nữ.
TÊNHV QUANG SƠN THU VĂN YẾN BẢO ANH LÝ PHÁI NAM NAM NỮ NAM NỮ NAM NAM NỮ NĂM SINH 1980 1980 1981 1982 1980 1982 1982 1981 TOÁN 15 12 10 8 9 12 7 13 VĂN 16 3 2 15 5 10 10 9 NGOẠI NGỮ 18 14 10 10 4 11 5 12 ÐTB

Bài Tập 15
Bảng theo dõi công tác
MÃNV A B C PHÒNGBAN GĐ TÀI VỤ VẬT TƢ SỐ NĂM CÔNG TÁC 13 46 78 10 12 15 9 11 13 8 9 11 CHỨCVỤ HỆ SỐ

Bảng hệ số
GÐ 3 PGÐ 2.5 KHÁC 2.2 NV 2

BẢNG LƢƠNG THÁNG 08/04
STT 01 02 03 04 05 06 07 08 TÊN NAM THU HOA ÐÔNG THANH QUANG KHANH ÐỨC MANV A4 C2 D1 B4 B7 C5 D2 B3 LCB 600 480 390 520 520 480 390 520 NC 23 25 28 23 24 22 28 22 PB SNCT CVỤ GÐ PGÐ NV NV BV NV KT NV LƢƠNG PHỤ CẤP T.LÃNH

1. 2. 3.

Dựa vào ký tự bên trái của MANV (mã nhân viên) và bảng theo dõi công tác để điền vào cột PB (phòng ban). Dựa vào ký tự đầu và ký tự bên phải của MANV và bảng theo dõi công tác để tính số năm công tác. Tính lƣơng = hệ số * LCB * NC (ngày công). Biết rằng: Ngày công đƣợc tính gấp đôi công nếu > 25. Hệ số dựa vào chức vụ và bảng hệ số đã cho. Mỗi năm công tác sẽ đƣợc tính phụ cấp là 0.75% * LCB và: Nếu phòng ban là GÐ, đƣợc tăng thêm 30000. Nếu phòng ban là vật tƣ, tăng thêm 20000. Tính thực lãnh = lƣơng + phụ cấp.

4.

5.

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 57

Bài tập 16
1.

Tạo bảng tính sau:

KẾT TOÁN HÀNG NHẬP THÁNG 11/2004
STT Mã CT Ngày nhập Tên hàng
1 2 3 4 5 6 7 8 BL01 BL02 BL01 BL03 BL03 BL04 BL05 BL01 02/11/2001 04/11/2001 04/11/2001 19/01/2001 21/11/2001 22/11/2001 22/11/2001 24/11/2001

SLƣợng
50 100 125 47 80 90 120 48

Ðơn giá

Thành Thuế độc tiền hại

Tổng Cộng

Bảng mã loại hàng hoá và đơn giá
MLOẠI TÊN HÀNG ÐƠN GIÁ ÐỘC HẠI

BL01 BL02 BL03 BL04 BL05
2. 3. 4.

Sữa bột Thuốc lá Ðƣờng Rƣợu Bột ngọt

25000 10000 4500 150000 20000

X X

Dựa vào mã chứng từ (Mã CT) và bảng mã loại hàng hoá và đơn giá, hãy điền dữ liệu vào các cột Tên hàng và Ðơn giá. Tính Thành Tiền = Số lƣợng * Ðơn giá. Tính thuế độc hại = 5% của thành tiền. Lƣu ý: Chỉ tính đối với các mặt hàng có ghi chú độc hại (đánh dấu X) trong bảng Mã loại.

5. 6. 7.

Tính Tổng cộng = Thành tiền + Thuế độc hại. Sắp xếp bảng tính theo thứ tự tăng dần của Tổng cộng. Lập bảng tổng kết doanh thu các mặt hàng nhập trong tháng: Tên mặt hàng Sữa bột Thuốc lá Ðƣờng Rƣợu Bột ngọt Doanh thu

8.

Lập biểu đồ doanh thu với các mặt hàng trên. (Có chú thich đầy đủ)

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 58

Bài tập 17
1. Lập bảng tính sau:

BÁO CÁO THUẾ NHẬP KHẨU
Mã Số Tên hàng số lƣợng A001 Thuốc nhuộm 10 B001 Vật tƣ ảnh 15 B002 Máy Minilab 4 A002 Sợi 120 C001 Dƣợc liệu 10 D001 Casselle 100 C002 LK điện tử 15 D002 Nissan car 4 A003 Máy dệt 2 D003 Mô tô 125 5 Bảng tỉ lệ Loại Tỷ lệ A 5% B 10% C 20% D 50% Ðơn giá MNT Ngày 12/10/2004 12/10/2004 16/10/2004 13/10/2004 10/10/2004 07/10/2004 08/10/2004 18/10/2004 11/10/2004 10/10/2004 Thành tiền VN Thuế

30 USD 100 JPY 10000 JPY 20000 RUP 10000 FF 3000 JPY 4000 HKD 30000 JPY 30000 USD 10000 JPY Bảng tỉ giá với USD MNT Tỷ giá FF 56 JPY 132 USD 1 RUP 200 HKD 20

2. Chèn thêm 2 cột: Thành tiền và Quy ra USD trƣớc cột Thành tiền VN. 3. Tính Thành tiền = Số lƣợng * Ðơn giá. 4. Tính Quy ra USD = Thành tiền / Tỷ giá (Tỷ giá đƣợc tính dựa vào MNT (Mã

nguyên tệ) và Bảng tỷ giá với USD).
5. Tính Thành tiền VN = Quy ra USD * Tỷ giá

Tỷ giá đƣợc tính dựa vào Ngày và Bảng quy định tỷ giá sau: Ngày Trƣớc ngày 15/10/2004 Sau ngày 15/10/2004 Tỷ giá 11.000 11.500

6. Tính thuế = Tỷ lệ theo loại hàng * Thành tiền VN. (Tỷ lệ theo loại hàng đƣợc

tính dựa vào ký tự bên trái của Mã số và Bảng tỷ lệ)
7. Sử dụng Advanced Filler:

-

Tạo vùng dữ liệu có ký tự bên trái của Mã số là A và có Mã Nguyên Tệ là USD. Tạo vùng dữ liệu có Mã Nguyên Tệ là JPY và có ký tự bên phải của Mã số là “2”. Tạo vùng dữ liệu có ký tự bên trái của Mã số là “B” và có Mã Nguyên Tệ là “FF”. Trích lọc các dòng có Ngày từ 12 đến 16. Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 59

Bài tập 18
BÁO CÁO DOANH THU ĐẠI LÝ VÉ SỐ
Mã BT TP VL TN
Stt

Bảng phụ 1 Tên tỉnh Bến tre Thành phố Vĩnh Long Tây Ninh

Loại Đơn giá

Bảng phụ 2 1 2 1000 2000

3 3000

Tên & Loại Số lƣợng Mã vé vé vé

Thành tiền

Tiền thƣởng

Tiền lời

Ghi chú

1 ? KBT1 150 ? ? ? ? 2 KTP1 200 3 CTN2 100 4 NBT2 200 5 FTP3 210 6 TTN1 150 7 SBT1 210 8 NTP2 140 9 MBT3 120 10 TVL2 130 Mô tả:  Tên & Loại vé: Gồm tên Tỉnh và Đơn giá vé Dựa vào ký tự thứ 2 và 3 của Mã vé, tra trong bảng phụ 1 để lấy tên tỉnh. Dựa vào ký tự thứ 4 của Mã vé, tra trong bảng phụ 2 để lấy Đơn giá.  Thành tiền = Số lƣợng * đơn giá. (Đơn giá đƣợc lấy từ bảng phụ 2, dựa vào ký tự cuối của Mã vé).  Tiền thƣởng: Nếu tỉnh là Bến Tre và số lƣợng vé từ 200 trở lên thì đƣợc thƣởng 2% của thành tiền, ngƣợc lại thì tiền thƣởng = 0  Tiền lời = Tiền thƣởng + 10% * Thành Tiền Nếu tỉnh là Bến Tre và số lƣợng vé từ 200 trở lên thì ghi "Có  Ghi chú: tặng phẩm", ngƣợc lại thì để trống Yêu cầu: 1 – Lập công thức tính toán tại các Cell có dấu ? 2 – Định dạng bảng tính 3 – Rút trích các vé của tỉnh Bến Tre và có Loại là 1000

Bài tập 19
BẢNG THỐNG KÊ NHẬP XUẤT CPU TRONG THÁNG
Stt 1 2 Số HĐ NI3100 NI3133 Ngày N/X Tên hàng Số lƣợng Nhập/xuất 03/03/04 28/03/04 ? 20 34 ? Thành tiền ?

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 60

3 4 5 6 7 8

XI2100 XI3133 NA7200 NA6100 NI2100 XA7200

22/03/04 28/03/04 11/03/04 11/03/04 06/03/04 12/03/04 BẢNG PHỤ

15 14 10 8 15 9

Mã I3 I2 A7 A6 Mô tả:

Tên hàng Intel Pentium III Intel Pentium II AMD Athlon K7 AMD K6

Đơn giá (USD) BUS 100 BUS 133 BUS 200 104 107 20 115 39

 Ký tự đầu của số hóa đơn cho biết nhập hay xuất.  2 ký tự kế tiếp là Mã loại của CPU  3 kí tự cuối cho biết Loại BUS Yêu cầu: 1 - Tên hàng: Dựa vào ký tự thứ 2 và 3 của số hóa đơn tra trong BẢNG PHỤ. Dựa vào 3 ký tự cuối để ghi loại BUS. Ví dụ: NI3100 -> thì tên hàng là Intel pentium III BUS 100. 2 - Nhập/Xuất: Dựa vào ký tự đầu của số hóa đơn (X -> Xuất, N -> Nhập) 3 - Thành tiền = Đơn giá * Số lƣợng. (Đơn giá tra trong BẢNG PHỤ. Nếu hóa đơn là X thì tăng đơn giá lên 0.5%) 4 - Sắp xếp dữ liệu tăng dần theo ngày N/X, nếu trùng sắp tăng dần theo số hóa đơn. 5 - Tạo bảng thống kê sau: Tổng số lƣợng nhập CPU Intel: ? Tổng số lƣợng nhập CPU InTel ? trong khoảng 10/03 đến 20/03. 6 - Trích ra đầy đủ thông tin về những mặt hàng CPU AMD

Bài tập 20
KẾT QUẢ KỲ THI CHỨNG CHỈ QUỐC GIA A/B
Mã Ngày Nơi Kết Họ Tên Windows Word Excel Access ĐTB số sinh sinh quả A001 Lê Minh Hoàng 05/02/1980 TPHCM 5 9 9 ? ? A002 Trần Hải 08/04/1978 Biên Hoà 10 6 2 A003 Đỗ Minh 01/01/1984 Hà Nội 0 9 8 B001 Trần Thị Loan 05/09/1974 Long An 7 A004 Hoàng Thị Hạnh 12/11/1976 Vũng Tàu 2 8 9 B002 Lê Hồng Loan 18/08/1977 TPHCM 9 Xếp loại ?

Mô tả:  

Ký tự đầu của Mã số cho biết thi chứng chỉ A hay B Chứng chỉ A thì thi 3 phần: Windows, Word, Excel; Chứng chỉ B thi Access Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 61

Yêu cầu: 1 - Chèn thêm một cột "Loại chứng chỉ" vào trƣớc cột Windows. Dựa vào ký tự đầu của Mã số để tính Loại chứng chỉ. (Vd: A001 -> Chứng chỉ A) 2 - ĐTB: Nếu đăng ký thi Chứng chỉ A thì đƣợc tính nhƣ sau: Windows hệ số 2; Word và Excel hệ số 4; sau đó cộng cả 3 lại và chia cho 10. Nếu Chứng chỉ B thì lấy điểm Access. 3 - Kết quả: Nếu ĐTB>=5 thì ghi "Đậu", Ngƣợc lại thì "Rớt", nhƣng nếu thi Chứng chỉ A và có điểm của một trong 3 môn là 0 thì kết quả là "Rớt".

4 - Xếp loại: Trong số những thí sinh Đậu, nếu ĐTB >= 9 thì ghi "Giỏi", ĐTB >= 6.5 và ĐTB < 9 thì ghi "Khá", còn lại ghi "TB". Trƣờng hợp "Rớt" thì bỏ trống. 5 - Sắp xếp tăng dần theo Tên, nếu trùng Tên thì sắp giảm dần theo Họ. 6 - Tạo bảng thống kê sau: Tổng số thí sinh thi chứng chỉ A: Tổng số thí sinh thi chứng chỉ A có kết quả "Đậu": 7 - Trích ra danh sách thí sinh "Rớt" ? ?

Bài tập 21
BẢNG CHI TIẾT QUẢN LÝ VẬT TƢ - THIẾT BỊ
Mã VT - TB A5TQ TSBT A4TQ A0VT CABT A5VT A4TQ A4VT Loại giấy A4 A5 A0 Tên VT - TB ? Khổ Giấy ? Công việc ? Số Thành Lƣợng Tiền 4 ? 1 10 1 1 6 2 4 THIẾT BỊ Mã TB TS CA Tên TB TOSHIBA CANON Bảo Trì 250000 185000 Đơn Giá ?

GIÁ GIẤY Giá cả VT 37500 18000 110000

TQ 38000 30000 120000

Yêu cầu: Nhập dữ liệu và trang trí bảng tính nhƣ trên

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 62

1. Tên VT - TB: Căn cứ vào Mã VT - TB: nếu ký tự đầu là “A” thì ghi là "Giấy", ngƣợc lại căn cứ vào 2 ký tự đầu tra bảng THIẾT BỊ. Ví dụ: A5TQ -> Giấy; CATM -> CANON 2. Khổ giấy: 3. Công việc: 4. Đơn giá: Căn cứ vào Mã VT - TB: nếu ký tự đầu là “A” thì lấy 2 ký tự đầu làm khổ giấy, ngƣợc lại để trống. Nếu là giấy thì ghi là "Mua", ngƣợc lại ghi "Bảo trì" Đơn giá giấy phụ thuộc vào khổ giấy (2 ký tự đầu Mã VT - TB) và Loại giấy (2 ký tự cuối Mã VT - TB). Đơn giá thiết bị phụ thuộc vào loại máy (2 ký tự đầu mã VT - TB), tra trong bảng THIẾT BỊ. = Đơn giá * Số lƣợng và thêm chữ "đồng" vào sau.

5. Thành tiền

6. Sắp xếp bảng tính theo tên VT - TB tăng dần, nếu trùng tên thì sắp theo Thành tiền giảm dần. 7. Trích lọc những loại giấy khổ A4 có số lƣợng > 3 (yêu cầu tạo vùng điều kiện)

Bài tập 22
CỬA HÀNG KINH DOANH THỰC PHẨM
Quầy Mã hàng Tên hàng Nhà Sx 1 1 1 2 2 2 2 1 ADA+ ELM+ ASA NVN EGM NSN+ AWA EPM+ ? ? Ngày bán 15/04/2002 26/04/2002 30/04/2002 01/05/2002 01/05/2002 05/05/2002 07/05/2002 10/05/2002 Số lƣợng 15 5 10 15 20 20 5 5 Đơn giá ? Thành tiền ? Ghi chú ?

DANH MỤC NHÀ SẢN XUẤT Mã hiệu Nhà Sx M A N Mã loại

DANH MỤC HÀNG Tên Đơn giá hàng

Mead Jonhson Abbou Lab Nuti Foot

BẢNG THỐNG KÊ Nhà Sx Tổng doanh thu Abbou Lab ? Mead Jonhson ? Nuti Foot ? Yêu cầu:

1AD 1AS 2AW 1EL 1EP 2EG 2NS 2NV

Gain Advanced Gain Similac Gain Grow Up Enfalac A+ Enfa Pro Enfa Grow Nuti Smart Nuti Vita Plus

145000 140000 135000 170000 130000 145000 75000 85000

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 63

1. Tên hàng: Kết hợp Quầy và 2 ký tự đầu của Mã hàng tạo thành Mã loại. Dò tìm Mã loại trong Bảng Danh Mục Hàng 2. Nhà Sx: Dựa vào ký tự thứ 3 của Mã hàng dò tìm trong bảng danh mục nhà

sản xuất

3. Đơn giá: Dò tìm Mã loại nhƣ câu 1 trong Bảng Danh mục Hàng 4. Thành tiền =Số lƣợng * Đơn giá. Nếu Số lƣợng >= 15 sẽ giảm 5% của Thành tiền 5. Ghi chú: Những mặt hàng có ký tự cuối là "+" thì ghi "Tăng cƣờng DHA", ngƣợc lại để trống. 6. Sắp xếp bảng tính tăng dần theo Nhà Sx và giảm dần theo Ngày bán. 7. Trích lọc đầy đủ thông tin của những mặt hàng có "Tăng cƣờng DHA" 8. Lập bảng thống kê theo mẫu ở BẢNG THỐNG KÊ.

Bài tập 23
CUỘC ĐUA XE ĐẠP MỪNG XUÂN TÂN TỴ CHẶNG SÀI GÒN - VŨNG TÀU Giờ xuất phát: 6:00 Số Km: 120

STT Mã Vận động viên Tên vận động viên Đội 1 2 3 4 5 6 7 8 9 10 KSTVM KSVVH KSPDT CAHVH CAHMQ CANTK TGHDD TGLDC TGTAT AGVGS ? ?

Giờ kết thúc 8:30 8:20 8:45 8:32 8:35 8:22 8:27 8:26 9:00 8:21

Thành tích ?

Vận tốc Xếp hạng (Km/h) ? ?

DANH SÁCH ĐỘI

DANH SÁCH VẬN ĐỘNG VIÊN Mã Vđv Tên Vđv

Đội
KS CA TG AG

Tên Đội

Khách sạn Thanh Bình Công An Thành Phố Tiền Giang Bảo vệ TV An Giang

HDD HMQ HVH LDC NTK PDT TAT TVM VGS VNH

Huỳnh Đại Đồng Hoàng Mạnh Quân Hồ Văn Hùng Lê Đức Công Nguyễn Trần Khải Phạm Đình Tuấn Trần Anh Thƣ Trần Vũ Minh Vũ Giáo Sửu Vũ Ngọc Hoàng Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 64

Mô tả:



2 ký tự đầu của Mã vận động viên cho biết tên đội; các ký tự còn lại là tên vận động viên.

Yêu cầu: 1 - Dựa vào 2 bảng để lấy tên đội và tên VĐV 2 - Thành tích là số giờ và phút đạt đƣợc từ lúc xuất phát cho đến đích. 3 - Vận tốc là số Km / tổng số giờ đi đƣợc và làm tròn đến hàng đơn vị. Định dạng theo kiểu Km/h (Ví dụ: 50 Km/h). 4 - Xếp hạng thành tích cá nhân ở cột xếp hạng 5 - Thống kê theo mẫu sau:
Vận tốc trung bình của các đội

Đội KS CA TG AG

Vận tốc TB ? ? ? ?

Xếp hạng ? ? ? ?

5.1 - Định dạng cột Vận tốc TB theo kiểu Km/h. (Ví dụ: 40Km/h) 5.2 - Xếp hạng: Nếu đội nào có Vận tốc TB cao nhất thì xếp hạng nhất. 6 - Rút trích đầy đủ thông tin về các vận động viên đoạt giải 1, 2, 3.

Bài tập 24
Mã hàng Tên hàng Đ/vị tính Quí 1 Đơn giá Quí 2 theo quí Quí 3 Quí 4 Bảng danh mục G D Gạo Đƣờng Kg Kg 3500 3800 4000 4000 4200 4500 4500 3000 M Muối Kg 1000 1500 1500 1000 S Sữa Hộp 4200 4000 4100 4500

Hoá đơn D04 G05 S06 M03 D08 G06 S09 G06 M04

BẢNG THEO DÕI BÁN HÀNG KHO NÔNG SẢN Đ/v Đơn Thành Ngày lập Quí Tên hàng tính giá Slg bán tiền 15/02/2000 ? ? ? ? ? ? 20/01/2000 20/04/2000 15/05/2000 12/06/2000 18/07/2000 26/09/2000 10/11/2000 25/12/2000

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 65

Mô tả: Yêu cầu: 1234-

 

Ký tự đầu trong hoá đơn cho biết mã hàng hóa 2 ký tự cuối trong hoá đơn cho biết số lƣợng

Tên hàng, Đ/v tính: dựa theo mã hàng, dò tìm trong bảng danh mục Quí: Nếu từ tháng 1 -> tháng 3 thì ghi "Quí 1"; từ tháng 4 -> tháng 6 thì ghi "Quí 2"; từ tháng 7 -> 9 thì "Quí 3"; từ tháng 10 -> 12 ghi "Quí 4" Đơn giá: Dựa theo mã hàng và Quí dò tìm trong bảng Danh mục. Số lƣợng bán: Căn cứ theo 2 ký tự cuối của hoá đơn. Nếu mặt hàng là Gạo, Đƣờng, Muối thì ghi là "Tấn"; mặt hàng là Sữa thì ghi "Thùng". Ví dụ: D04 --> 4 Tấn. Slg bán * Đơn giá; với Slg bán qui đổi thành đơn vị tính (Tấn --> 1000Kg, Thùng --> 24 Hộp).
Thống kê doanh số Quí 1 Gạo Sữa Quí 2

56-

Thành tiền

=

Lập bảng thống kê theo mẫu sau:

? ?

? ?

7-

Rút trích các mặt hàng Gạo bán trong Quí 3 và 4

Bài tập 25
BẢNG GIÁ

Mã Tên hàng hàng CC Coca Cola PS Pepsi SP Sprite FT Fanta DN Sữa đậu nành

Giá 1 (Lon) 65000 70000 68000 72000 48000

Giá 2 (Chai) 35000 37000 38000 40000 21000

Phí vận chuyển Mã kho 1 2 3 4 Cƣớc phí 45000 60000 50000 70000

THỐNG KÊ BÁN VÀ GIAO HÀNG CỦA CÔNG TY ABC
Mã hđ Tên Đơn giá hàng Ngày bán Slg Thành Phí tiền vận chuyển Phụ thu Tổng tiền

FT-L1 ? ? PS-C1 DN-L2 SP-L3 FT-C3 PS-L1 CC-L4 DN-C3 Mô tả mã hoá đơn:

15/01/2004 ? 24/01/2004 27/01/2004 28/01/2004 05/02/2004 07/02/2004 10/02/2004 18/02/2004

?

?

?

?

Giáo trình và bài tập Excel

Trường THCN Dân Lập Công Nghệ Tin học – Viễn thông

Trang 66

 2 ký tự đầu là mã hàng.  Ký tự cuối là loại giá: L là Giá 1 (lon), C là Giá 2 (Chai).  Ký tự cuối là mã kho. Yêu cầu: 1. Tên hàng và đơn giá: Dựa vào mã hàng tra trong bảng giá. (đơn giá phụ thộc vào giá Lon hoặc giá Chai) 2. Thành tiền = Slg * Đơn giá 3. Phí vận chuyển: Dựa vào Mã kho tra trong bảng Phí vận chuyển. 4. Phụ thu = 10% * Thành tiền đối với các mặt hàng bán trƣớc ngày 01/02/1998, ngƣợc lại 0.

5. Tổng tiền = Thành tiền + Phí vận chuyển + Phụ thu. 6. Lập bảng thống kê sau:
Tháng 1 Tháng 2 Pepsi ? ? Sắp xếp bảng tính tăng dần theo Tên hàng, nếu trùng thì sắp giảm dần theo Tổng tiền.

7.

Giáo trình và bài tập Excel


				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:17573
posted:11/12/2009
language:Vietnamese
pages:20