TRƢỜNG ĐẠI HỌC NGÂN HÀNG TP.HCM
TRUNG TÂM NGOẠI NGỮ- TIN HỌC

BÀI TẬP
PHẦN MỀM
XỬ LÝ BẢNG TÍNH
(Cơ bản)

Lưu hành nội bộ
TP.HCM


BÀI TẬP 1: Định dạng dữ liệu, trình bày bảng tính
XÍ NGHIỆP SỮA CHỮA & BẢO HÀNH
TRẠM BẢO HÀNH SỐ 5

DANH SÁCH HỌC SINH
Họ lót

Tên

Ngơ Duy
Nguyễn Văn
Diệp Thanh
Hồng Văn
Trần Thanh

Ngày sinh
(mm/dd/yyyy)

Hồng

Minh
Mai
Tuyến
Sơn

Nơi sinh

Chức vụ

Huế
Đà Nẵng
Sài Gịn
Bạc Liêu
Sơng Bé

LCB

CNV
CNV
TP
CNV
PGĐ

TĐVH

ĐH
CĐ

u cầu:
- Nhập liệu cho bảng tính

- Định dạng cho bảng tính
- Vẽ khung và tơ nền
BÀI TẬP 2: Trình bày & định dạng bảng tính

LỊCH CƠNG TÁC TUẦN

THỨ
HỌ TÊN

HAI

Đỗ Thành Nghĩa
Nguyễn Minh Qn
Lê Thị Vy

BA

TƢ

NĂM

Họp ở Tổng Công ty

BẢY

Đi học

Họp

Đi công tác Hà Nội


Nghỉ
Đi học

SÁU

Nghỉ

Võ Anh Sơn

Đi học

Nghỉ

Đi học

Nghỉ

Đi Cảng
SG

Họp

Lập kế hoạch sản xuất năm

Hồng Thị Thu

Đi Ngân
hàng


Trần Bích Ngọc

Nghỉ

Nghỉ

Đi học

Đi TSN

Lập dự tốn XDCB 2015

u cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- Định dạng các ơ có nội dung “Nghỉ” – in nghiêng, chữ màu đỏ.

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


BÀI TẬP 3: Định dạng công thức – Fill handle – Hàm: SUM

BẢNG THANH TOÁN LƢƠNG THÁNG 12
STT

HỌ

TÊN

Nguyễn Văn
Trần Thị

Bùi Hồng
Võ Xn
Đinh Bách
Lê Thanh
Trần Duy

NGÀY
CƠNG

LCB

LƢƠNG

TẠM
ỨNG

CỊN
LẠI

An
Bạch
Hiền
Duy
Phƣơng
Bình
Hồng
TỔNG CỘNG

u cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu, Điền giá trị cột STT.

- LƢƠNG = LCB * NGÀY CÔNG
- TẠM ỨNG = LƢƠNG *
- CÒN LẠI = LƢƠNG – TẠM ỨNG
- Tính TỔNG CỘNG cho các cột LƢƠNG, TẠM ỨNG, CỊN LẠI
BÀI TẬP 4: Định dạng dữ liệu cơng thức – Fill handle

BẢNG CHIẾT TÍNH GIÁ THÀNH VẬT TƢ
STT

Tên vật tƣ

ĐVT

Xi măng
Cát
Gạch thẻ
Sắt f10
Sắt f5
Thép góc 5x6
Đá x
Que hàn
Gỗ
Gạch men
Sơn trắng

Bao
M
1000 viên
Kg
Kg

Kg
M
Hộp
M
Thùng
Kg

Đơn giá
nhập

Số
lƣợng

Thuế & Phí
vận chuyển

Thành
tiền

Đơn giá
xuất

Yêu cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu, Điền giá trị cột STT.
- THUẾ & PHÍ VẬN CHUYỂN = (ĐƠN GIÁ * SỐ LƢỢNG) * 15%
- THÀNH TIỀN = (ĐƠN GIÁ * SỐ LƢỢNG) + (THUẾ & PHÍ VẬN CHUYỂN)
- Tính ĐƠN GIÁ XUẤT sao cho có mức lãi thu đƣợc là 15%
BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu



BÀI TẬP 5: Các loại địa chỉ tham chiếu – Sao chép, di chuyển dữ liệu
CÔNG TY XUẤT NHẬP KHẨU

BẢNG GIÁ NHẬP HÀNG HĨA
STT

HÀNG HĨA

ĐƠN
SỐ
GIÁ USD LƢỢNG

TRỊ
GIÁ
USD

THUẾ

CHI
PHÍ

TRỊ
GIÁ
VNĐ

ĐƠN
GIÁ

CABLE IN
CARD MOTOR

MONITOR
UPS
HARD DISK
TỔNG THUẾ
TỔNG CHI PHÍ
TỶ GIÁ USD

Yêu cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu, Điền giá trị cột STT.
- TRỊ GIÁ USD = ĐƠN GIÁ USD * SỐ LƢỢNG
- THUẾ = (TỔNG THUẾ / TỔNG cột TRỊ GIÁ USD) * TRỊ GIÁ USD
- CHI PHÍ = (TỔNG CHI PHÍ / TỔNG cột SỐ LƢỢNG) * SỐ LƢỢNG
- TRỊ GIÁ VNĐ = (TRỊ GIÁ USD * TỶ GIÁ USD) + THUẾ + CHI PHÍ
- ĐƠN GIÁ = TRỊ GIÁ VNĐ SỐ LƢỢNG
BÀI TẬP 6: Mở nhiều bảng tính cùng lúc – Paste Special
CÔNG TY XUẤT NHẬP KHẨU XYZ

BẢNG GIÁ XUẤT HÀNG HĨA
STT

HÀNG
HĨA

SỐ
LƢỢNG

TRỊ GIÁ
VNĐ

THUẾ


CHI
PHÍ

TRỊ GIÁ
XUẤT

LỢI
NHUẬN

u cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- Cột HÀNG HÓA, SỐ LƢỢNG, TRỊ GIÁ VNĐ sao chép dữ liệu từ Bài tập 5
- THUẾ = 2.3% * TRỊ GIÁ VNĐ
- CHI PHÍ = 1.2% * TRỊ GIÁ VNĐ
- TRỊ GIÁ XUẤT = TRỊ GIÁ VNĐ + THUẾ + CHI PHÍ
- LỢI NHUẬN = 20% * TRỊ GIÁ XUẤT
- ĐƠN GIÁ = (TRỊ GIÁ XUẤT + LỢI NHUẬN) / SỐ LƢỢNG
BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu

ĐƠN
GIÁ


BÀI TẬP 7: Hàm IF
CÔNG TY KHO BÃI ABC

BÁO CÁO TIỀN THUÊ KHO BÃI
THÀNH
TIỀN


NGÀY KẾT
THÚC THUÊ
(mm/dd/yy)

ĐƠN GIÁ

NGÀY BẮT
ĐẦU THUÊ
(mm/dd/yy)

SỐ NGÀY

ĐƠN VỊ TH

TRỌNG
LƢỢNG

TT

Cty Hƣng Long
XN Bao bì
Cty Hóa chất
XN Vải sợi
XN Bơng
u cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- SỐ NGÀY = NGÀY KẾT THÚC THUÊ – NGÀY BẮT ĐẦU THUÊ
- ĐƠN GIÁ đƣợc tính nhƣ sau:
o Nếu TRỌNG LƢỢNG <= 100 tấn: thì tính 50.000

o Nếu TRỌNG LƢỢNG > 100 tấn và <= 150 tấn: thì tính 45.000
o Nếu TRỌNG LƢỢNG > 150 tấn và <= 200 tấn: thì tính
o Từ 200 tấn trở lên: thì tính 35.000
- THÀNH TIỀN = SỐ NGÀY * ĐƠN GIÁ

BÀI TẬP 8: Quy định nhập liệu (Validation) – Hàm : MAX, MIN, AVERAGE

BẢNG THANH TỐN LƯƠNG
THÁNG:

STT

Họ lót

Tên

Trần Thị Anh
Lê Thị
Nga
Đỗ Hữu San

Lƣơng
ngày
,
,
,

Số
ngày
công


Lƣơng
tháng

Chức vụ
GĐ
PGĐ
PGĐ
CỘNG

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu

Thƣởng

Phụ cấp
chức vụ

Lƣơng


P. Kinh doanh :
Nguyễn Văn
Bùi Kim
Trƣơng
Lạc Vỉ

Thiện
Chi
Xuân
Cƣờng


,
,
,
,

P. Tài vụ :
Quách Vủ
Vũ Đình
Lâm Thành

Nghi
Phƣớc
Cơng

,
,
,

TP
PP
NV
NV
CỘNG

TP
PP
NV
CỘNG
TỔNG CỘNG


LƢƠNG CAO NHẤT
LƢƠNG THẤP NHẤT
LƢƠNG TRUNG BÌNH
u cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- THÁNG: dữ liệu ngày tháng hiện hành, nhƣng định dạng chỉ hiển thị Tháng
- SỐ NGÀY CÔNG: Thiết lập quy định chỉ cho phép nhập số nguyên từ đến 31, nếu
ngƣời dùng nhập sau, cho xuất hiện hộp thông báo yêu cầu nhập lại (Data 
Validation)
- LƢƠNG THÁNG = LƢƠNG NGÀY * SỐ NGÀY CƠNG
- THƢỞNG đƣợc tính nhƣ sau:
o NGÀY CÔNG >= : thƣởng % LƢƠNG
o NGÀY CÔNG >= : thƣởng % LƢƠNG
o NGÀY CÔNG < : không thƣởng
- PHỤ CẤP CHỨC VỤ: GĐ:
, PGĐ:
, PP:
, NV:
- LƢƠNG = LƢƠNG THÁNG + THƢỞNG + PHỤ CẤP CHỨC VỤ
- Tính CỘNG & TỔNG CỘNG các cột tƣơng ứng
- Tính LƢƠNG CAO NHẤT, THẤP NHẤT, TRUNG BÌNH.

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


BÀI TẬP 9: Hàm : LEFT, RIGHT, MID, LEN

BẢNG THANH TỐN LƯƠNG THÁNG 06/2014
MSNV

C01B
B02A
A03C
B04B
B05C
C06A
A07B

Họ lót
Lê
Vƣơng
Vũ
Đào
Bích
La
Triệu

Tên

LCB

Ngày
cơng

Hệ số
CB

Hệ số
KV


Điểm

Lƣơng

Tạm
ứng

Thanh
Cỗ
Thành
Minh
Hà
Lƣơng
An
TỔNG CỘNG
Hệ số CB

Hệ số KV

A
B
C

TỔNG Q.LƢƠNG
N.CƠNG Q.ĐỊNH

u cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- HỆ SỐ CB dựa vào ký tự đầu của MSNV và tra trong bảng kèm theo.
- HỆ SỐ KV dựa vào ký tự cuối của MSNV và tra trong bảng kèm theo.

- ĐIỂM = LCB * (NGÀY CÔNG N CÔNG Q ĐỊNH) * HỆ SỐ CB * HỆ SỐ KV
- Tính TỔNG CỘNG của cột ĐIỂM
- LƢƠNG = (TỔNG Q LƢƠNG TỔNG ĐIỂM) * ĐIỂM
- TẠM ỨNG =
* LƢƠNG
- CÒN LẠI = LƢƠNG – TẠM ỨNG
- Tính TỔNG CỘNG các cột tƣơng ứng.

BÀI TẬP 10: Hàm : AND, OR, ROUND
Yêu cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- PCCV đƣợc tính nhƣ sau:
o GĐ: đƣợc 5.000
o PGĐ hoặc TP: đƣợc 4.000
o PP hoặc KT: đƣợc 3.000
o BV có ngày cơng trên ngày: đƣợc 2.000
o Các trƣờng hợp còn lại PCCV là 0
BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu

Còn
lại


-

LƢƠNG = LCB * N CƠNG, nhƣng nếu ngày cơng trên 25 ngày thì những ngày dƣ
đƣợc tính gấp đơi
TẠM ỨNG =
* (LƢƠNG + PCCV), làm tròn số đến hàng trăm, và khơng tạm ứng
vƣợt q 25.000

CỊN LẠI = (LƢƠNG + PCCV) – TẠM ỨNG
Tính TỔNG CỘNG cho các cột tƣơng ứng.

BẢNG LƯƠNG THÁNG 05/2014
CƠNG TY XYZ

STT

HỌ TÊN
QUANG ĐẠI
HỒNG HỒ
TRẦN XN
MAI TRÚC
BÁ ĐẠT
THÀNH TRÍ
HỒNG HIỆP
BÌNH AN
XN HẬU
LONG MINH
THU TRANG
BÍCH THỦY
BÍCH THU
LA LÂM
NGỌC TÍN

CH.VỤ LCB N.CƠNG PCCV LƢƠNG

TẠM
ỨNG


CỊN
LẠI

TP
NV
PP
NV
KT
BV
PP
GĐ
BV
NV
TX
KT
PGĐ
TP
BV
TỔNG CỘNG

BÀI TẬP 11: Hàm : COUNTIF
ĐIỀM CHUẨN

STT

HỌ LĨT
Nguyễn Xn
Hồ Thị
Ngơ Ngọc
Lê Thị Ngọc

Lê Quang
Trần Đình
Võ Vinh
Đinh Xn
Lý Ngọc
Văn Viết
Huỳnh Văn

TÊN
Ái
Cúc
Dinh
Hân
Lợi
Phú
Quang
Thành
Thịnh
Tạo
Xn

SỐ
BÁO
DANH
A012
B067
A065
C123
A015
B106

C165
C209
C231
A123
A009

ĐIỂM
TỐN

ĐIỂM
LÝ

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu

ĐIỂM
HÓA

15

TỔNG
ĐIỂM

KẾT
QUẢ


BẢNG BÁO CÁO
Số thí sinh đậu
Số thí sinh trƣợt
Yêu cầu:

- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- Lập tiêu chuẩn cho việc nhập số liệu vào 3 cột ĐIỂM TỐN, LÝ, HĨA theo u cầu
nhƣ sau: Điểm phải là số lẻ (decimal), điểm phải trong khoảng >=0 và <=10. Khi nhập
điểm không hợp lệ, cho xuất hiện hộp thoại thông báo lỗi và yêu cầu nhập lại.
- Chèn cột THÊM vào bên trái cột TỔNG ĐIỂM, lập công thức nhƣ sau: Khu vực A
đƣợc thêm 2, Khu vực B đƣợc thêm 1, các khu vực khác là 0. Biết rằng ký tự đầu của
SỐ BÁO DANH cho biết khu vực.
- TỔNG ĐIỂM = ĐIỂM TOÁN + ĐIỂM LÝ + ĐIỂM HÓA + THÊM
- KẾT QUẢ : Đƣợc tính là ĐẬU nếu khơng có mơn nào dƣới 3 và tổng điểm >= điểm
chuẩn; ngƣợc lại là TRƢỢT
- Lập cơng thức tính số liệu cho BẢNG BÁO CÁO.

BÀI TẬP 12: Làm việc với nhiều SHEET
Trong Sheet 1 tạo BẢNG LƢƠNG cho Phòng Kế hoạch nhƣ sau:
BẢNG LƢƠNG THÁNG 02/2001
STT

HỌ & TÊN
Lê Thị Hoàng
Nguyễn Văn Bảo
Lâm Thị Quỳnh
Hà Anh Thƣ

LCN
,
,
,
,

Phịng Kế hoạch

Ngày cơng

Lƣơng

Thƣởng

Thực lãnh

Trong Sheet 2 tạo BẢNG LƢƠNG cho Phòng Kinh doanh nhƣ sau:
BẢNG LƢƠNG THÁNG 02/2001
STT

HỌ & TÊN
Cao Đức Anh
Vũ Văn Huy
Lã Thị Thu Hiền
Trần Kim Bình

LCN
,
,
,
,

Phịng Kinh doanh
Ngày công

Lƣơng

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


Thƣởng

Thực lãnh


Trong Sheet 3 tạo BẢNG LƢƠNG cho Phòng Tiếp thị nhƣ sau:
BẢNG LƢƠNG THÁNG 02/2001
STT

HỌ & TÊN
Đinh Thị Hồng
Đoàn Mạnh Tâm
Ngơ Dỗn Thu
Vĩnh Giang

LCN
,
,
,
,

Phịng Tiếp thị
Ngày cơng

Lƣơng

Thƣởng

Thực lãnh


Trong Sheet 4 tạo BẢNG TỔNG HỢP:

BẢNG TỔNG HỢP
Phịng

Số ngày
làm thêm

Thƣởng

Ngày cơng quy định
Tổng ngày
công làm thêm

Kế hoạch
Kinh doanh
Tiếp thị

22
Tổng thƣởng
,

,

Yêu cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- Với mỗi Sheet (từ sheet đến sheet 3), thực hiện các công việc nhƣ sau:
o Chèn thêm cột SỐ NGÀY LÀM THÊM vào bên trái cột LƢƠNG
o Lập công thức lấy ra số liệu cho cột SỐ NGÀY LÀM THÊM

- Lập cơng thức tính LƢƠNG của các nhân viên thuộc các phịng, biết rằng:
LƢƠNG = LCN * NGÀY CƠNG, nếu NGÀY CƠNG > NGÀY CƠNG QUY ĐỊNH
(trong sheet 4) thì mỗi ngày làm hơn Ngày công quy định (vƣợt) đƣợc tính gấp 1.5
lần.
- Lập cơng thức tính số liệu cho BẢNG TỔNG HỢP, biết rằng:
o TỔNG NGÀY CÔNG LÀM THÊM là số ngày công là vƣợt Ngày công quy
định của cả 3 phòng cộng lại.
o THƢỞNG = (TỔNG THƢỞNG / TỔNG NGÀY CƠNG LÀM THÊM)*SỐ
NGÀY VƢỢT (của phịng đó)
- Lập cơng thức tính tiền THƢỞNG cho từng nhân viên, biết rằng:
o THƢỞNG (từng nhân viên) = MỨC THƢỞNG CHO NGÀY VƢỢT * SỐ
NGÀY CƠNG LÀM VƢỢT (của nhân viên đó)
- THỰC LÃNH = LƢƠNG + THƢỞNG

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


BÀI TẬP 13: Hàm TODAY, DATEVALUE, DATE, DAY, MONTH, YEAR

CÔNG TY KHO BÃI VẬT TƢ XÂY DỰNG
TIỀN
GIẢM

(mm/dd/yy)

TỶ LỆ
GIẢM

(mm/dd/yy)


THÀNH
TIỀN

NGÀY
KẾT
THÚC

ĐƠN GIÁ

SỐ LƢỢNG

ĐƠN VỊ

NGÀY
BẮT
ĐẦU

TỔNG
CỘNG

GHI
CHÚ

Cty Hƣng Long
XN Bao Bì
Cty Hóa Chât
Cty Vải Sợi
Cty Thái Sơn
XN Bơng
XN Xây Dựng

u cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- ĐƠN GIÁ:
o Nếu NGÀY KẾT THÚC là năm
tính đơn giá
o Nếu NGÀY KẾT THÚC là năm
tính đơn giá
o Nếu NGÀY KẾT THÚC là năm
tính đơn giá
o Cịn lại tính đơn giá
- THÀNH TIỀN = SỐ LƢỢNG * ĐƠN GIÁ
- TỶ LỆ GIẢM: nếu thời gian thuê dƣới 30 ngày, giảm 4%; nếu từ
đến dƣới 60 ngày,
giảm 8%; nếu trên 60 ngày giảm 12%
- TIỀN GIẢM = THÀNH TIỀN * TỶ LỆ GIẢM
- TỔNG CỘNG = THÀNH TIỀN – TIỀN GIẢM
- GHI CHÚ: Ghi chữ “Có quà Tết” cho đơn vị nào kết thúc trong thời gian từ
đến 27
tháng Giêng năm
, các trƣờng hợp khác khơng ghi gì cả.

BÀI TẬP 14: Biểu đồ

DOANH SỐ KINH DOANH TỔNG CÔNG TY
TH1 TH2 TH3 TH4 TH5 TH6
CTY A
CTY B
CTY C

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu



SỐ CNV TỔNG CÔNG TY
95
96
97
98
CTY A
CTY B
CTY C

KIM NGẠCH XUẤT NHẬP KHẨU
90 91 92 93 94 95
XUẤT
NHẬP

BÀI TẬP 15: Hàm MOD, INT
SỐ BỊ CHIA
132 182 273 285
SỐ CHIA

12
13
14
15

Yêu cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- Điền chữ “Chia Chẵn” – Nếu phép chia các ô tƣơng ứng cho nhau là chia chẵn.
- Ngƣợc lại, điểu vào số dƣ của phép chia

- Ví dụ:
132
182
273
285
Chia
12
Dƣ 2
Dƣ 9
Dƣ 9
Chẵn

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


BÀI TẬP 16: Hàm RANK, COUNT, COUNTA – Toán tử ghép &
SBN
A01
A02
A03
A04
A05
B08
B09
B10
B11
B12
C18
C19
C20

D01
D02
D03
D04
D05
D06

TÊN
QUANG
KIẾN
HÀ
THỦY
THIÊN
HẢI
HỒI
CỔ
SINH
THANH
XN
ĐIỀN
NGUYỆT
THU
MINH
TÂM
QN
TRÂN
CHÂU

T.TÂM
ĐNG

SGN
HUẾ
SGN
ĐNG
SGN
HUẾ
ĐNG
ĐNG
SGN
HUẾ
HUẾ
ĐNG
SGN
SGN
HUẾ
ĐNG
ĐNG
HUẾ

Đ.LT

Đ.TH

TR.B

HẠNG

GHI CHÚ

u cầu:

- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- TR B = (Đ LT * + Đ TH)
(làm tròn số lẻ thập phân)
- HẠNG căn cứ theo cột TR B để xếp hạng: hiển thị “thứ hạng / tổng số dự thi”
- GHI CHÚ: Ghi chữ “ĐỒNG HẠNG” cho những ai đồng điểm TR.B

BÀI TẬP 17: Hàm UPPER, PROPER, LOWER, VALUE – Toán tử ghép &
CƠNG TY XNK VẬT TƢ HÀNG HĨA

TỶ GIÁ (VND)

21.110

BẢNG GIÁ
MÃ
NGUỒN
TrTvBiPuPeAiPuAtAc-

NGUỒN
Mỹ
Anh
Trung Quốc
H.Kong
Nhật
Tr.Tiên
Malaysia
Singapore
Thái

MÃ

LOẠI
Truc
Tele
Bicy
Furn
Elec
Airc
Food
Comp
Acce

LOẠI
VẬT TƢ
Xe tải
Tivi
Xe đạp
Gia dụng
Điện tử
Máy lạnh
Thực phẩm
Máy tính
Linh kiện

QUY
CÁCH
TrTvBiPuPeAiPuAtAc-

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu

MAVT


ĐƠN
GIÁ
(USD)

TIỀN
(VND)


u cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫu.
- MAVT: Hình thành bằng cách ghép MÃ NGUỒN, MÃ LOẠI, QUY CÁCH, và cho
hiển thị dƣới dạng ký tự chữ in hoa. Ví dụ: AM-TRUC-TR- ĐƠN GIÁ (USD): Trích ra ký tự số kể từ vị trí thứ 4 của chuỗi QUY CÁCH, định
dạng tiền tệ là $.
- TIỀN (VND) = ĐƠN GIÁ (USD) * TỶ GIÁ (VND) định dạng có chữ “Đồng”

BÀI TẬP 18: Hàm VLOOKUP

Yêu cầu:
- Nhập liệu cho bảng tính, trình bày bảng tính nhƣ mẫ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
- THỰC LĨNH = LƢƠNG CƠ BẢN + (PHỤ CẤP * LƢƠNG CƠ BẢN)

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


BÀI TẬP 19: Hàm VLOOKUP

Yêu cầu:


BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


BÀI TẬP 20: Hàm HLOOKUP – Hàm thống kê – Rút trích dữ liệu

Yêu cầu:

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


BÀI TẬP 21: Bài tập tổng hợp

NHẬP XUẤT MẶT HÀNG ĐIỆN
STT

MASP

Tên
Sản Phẩm

Ngày
Nhập / Xuất

Số Lượng

Đơn Giá

Thành Tiền


Giảm Giá

CT-N
CD-N
CT-N
CT-X
BD-N
OP-N
DĐ-N
BD-X
DĐ-N
OP-N
Bảng Mặt Hàng & Đơn Giá
MASP

Tên
Sản Phẩm

DĐ
BD
CD
OP
CT

Dây điện
Bóng đèn
Cầu dao
Ổn áp
Công tắc


Đơn Giá
Nhập

Xuất

Bảng Tỷ Giá USD
Ngày
Tỷ giá
USD

u cầu:
-

Tên Sản Phẩm: Dựa vào ký tự 3 và 4 của MASP để lấy Tên Sản Phẩm từ Bảng Mặt
Hàng & Đơn Giá.
Đơn Giá: Dựa vào Tên Sản Phẩm để lấy Đơn giá từ Bảng Mặt Hàng & Đơn Giá,
nếu ký tự cuối của MASP là N thì lấy giá nhập ngược lại lấy giá xuất
Thành Tiền = Số Lượng*Đơn giá*Tỷ giá (Tỷ giá lấy trong Bảng Tỷ Giá USD dựa
vào Ngày nhập/xuất)
Giảm Giá: Nếu mặt hàng có Ngày Nhập / Xuất từ 10/02 đến ngày 20/02 thì Giảm
Giá 5% trên Thành tiền.
Sắp xếp bảng tính tăng dần theo Số Lượng và Thành Tiền
Trích lọc những sản phẩm công tắc và được giảm tiền
Lập Bảng thống kê theo mẫu:
Mặt Hàng

Dây điện

Bóng đèn


Cầu dao

Tổng Số Lượng
Tổng Thành Tiền

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu

Ổn áp

Công tắc


BÀI TẬP 22: Bài tập tổng hợp
Mã
HĐ
AT12
BT31
CT42
DT21
ET23
FT12
GT42
HT11
KT33

Tên Chủ
Hàng
Mai
Hoa
Nam

Trúc
Minh
Thanh
Phương
An
Châu

Loại Hàng
T1
T2
T3
T4

Loại
Hàng

Tên
Hàng

BẢNG GIÁ
Tên hàng
Giá lô
Điện tử
Gia dụng
Vi tính
Cơ khí

Giá
Lô


Giá
Rời

Giá rời

Số
Lượng

Số Lô
Hàng

Số Hàng
Rời

Thành
Tiền

BẢNG TỈ LỆ TIỀN CỌC
Đợt nhập
%
%
Tỉ Lệ Tiền Cọc

Tiền
Cọc

Còn
Lại

7 %


u cầu:
-

Điền LOẠI HÀNG là 2 ký tự giữa của Mã HĐ (Mã Hợp Đồng).
Điền TÊN HÀNG, GIÁ LÔ, GIÁ RỜI dựa vào LOẠI HÀNG và BẢNG GIÁ.
Tính SỐ LÔ HÀNG. Biết mỗi lô là 10 đơn vị mặt hàng.
Tính SỐ HÀNG RỜI: là phần số lượng không chẵn 1 lô.
Ví dụ: Số Lượng là 25 thì Số Lô Hàng là 2 và Số Hàng Rời là 5
Tính THÀNH TIỀN = SỐ LÔ HÀNG * GIÁ LÔ + SỐ HÀNG RỜI * GIÁ RỜI.
Tính TIỀN CỌC = THÀNH TIỀN * TỈ LỆ TIỀN CỌC, biết Tỉ Lệ Tiền Cọc được tính
dựa vào Đợt Nhập (Đợt Nhập là ký tự cuối của Mã hợp đồng) và Bảng tỉ lệ tiền cọc.
CÒN LẠI = THÀNH TIỀN – TIỀN CỌC
Lập công thức tính toán cho bảng tổng hợp sau:
Loại hàng
Tổng số lượng
Tổng thành tiền

-

T1

T2

T3

T4

Sắp xếp bảng tăng dần theo LOẠI HÀNG, cùng LOẠI HÀNG sắp giảm dần theo
THÀNH TIỀN.

Rút trích các mặt hàng có SỐ LƯNG từ 40 trở lên.

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


BÀI TẬP 23: Hàm MATCH
BẢNG BÁN HÀNG THÁNG 05/2014
HÀNG
SỐ
ĐƠN THÀNH
LOẠI
HÓA
LƢỢNG GIÁ
TIỀN
BIA
VN
MŨ
NHẬT
DÙ
THÁI
BIA
TQ
RƢỢU
VN
ÁO
VN
SƠN
VN
MŨ
THÁI

SƠN
THÁI
DÙ
VN
BIA
TQ
RƢỢU THÁI
BIA
NHẬT

BẢNG GIÁ HÀNG HĨA
HÀNG/LOẠI

VN

THÁI

NHẬT

DÉP
DÙ
MŨ
BUA
RƢỢU
ÁO
SƠN
THUỐC

u cầu:
-


Tìm giá từng loại hàng hóa theo BẢNG GIÁ, phân bổ vào cột ĐƠN GIÁ
Tính THÀNH TIỀN = ĐƠN GIÁ * SỐ LƢỢNG

BÀI TẬP 24: Hàm INDEX
Yêu cầu:
-

HỆ SỐ: Căn cứ vào bảng HỆ SỐ, tiến hành dị tìm và phân bổ hệ số.
PHỤ CẤP = HỆ SỐ * LCB
(mức phụ cấp không dƣới mức Tối thiểu 43.000, khơng q mức Tối đa
BẢNG TÍNH TIỀN PHỤ CẤP
STT TÊN
LCB CẤP BẬC KHU VỰC HỆ SỐ PHỤ CẤP
NHÂN
C2
HNI
DIỆN
C3
SGN
HÀ
D1
HNI
MINH
D2
DNG
ĐÀO
D3
HNI
KHÁNH

A2
SGN
HOA
A3
DNG
NHƢ
A1
CTO
ĐỒNG
B2
CDO
PHONG
B3
DNG
QUANG
C1
CDO
TÂM
D1
SGN

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu

)

TQ


BẢNG HỆ SỐ
HNI SGN DNG CTO CDO

A1
A2
A3
B1
B2
B3
C1
C2
C3
D1
D2
D3
BÀI TẬP 25: Bài tập tổng hợp

Yêu cầu:
-

MÃ XE: 2 ký tự đầu trong MÃ LÔ HÀNG XUẤT.
TÊN XE: Căn cứ vào MÃ XE, dị tìm trong BẢNG GIÁ XE.
DỊNG: Ký tự 3-5 trong MÃ LÔ HÀNG XUẤT
ĐẠI LÝ: chuỗi DLxx với xx là 2 ký tự cuối trong MÃ LÔ HÀNG XUẤT.
NGÀY: giá trị ngày tháng DD/MM/YY với dd: ký tự 6 – 7, mm: ký tự 8 – 9, yy: mặc
định = 14.
ĐƠN GIÁ: Dựa vào MÃ XE và DỊNG, dị tìm trong BẢNG GIÁ XE.

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


-


CHIẾT KHẤU = 10% cho các lô hàng dƣới 20 xe hoặc 12% cho các lô từ 20 xe trở lên.
THÀNH TIỀN = ĐƠN GIÁ * SỐ LƢỢNG * (1 – CHIẾT KHẤU)
Thống kê số xe đã xuất & doanh số bán cho từng đại lý (bảng Thống kê tổng hợp)

BÀI TẬP 26: Đồ thị

Cho bảng thống kê doanh số bán 6 tháng cuối năm của công ty X :
Mặt hàng

Doanh số tháng (tỷ đồng)
1

2

3

4

5

6

TỔNG CỘNG

Áo thun nam
Áo thun nữ
Áo somi
CỘNG
Yêu cầu:
-


Nhập, định dạng và hoàn tất bảng trên (viết cơng thức tính tổng cho các ơ cịn trống).
Vẽ biểu đồ cột biểu diễn doanh số bán của 3 mặt hàng trên trong các tháng 7 –
Vẽ biểu đồ phần trăm cho biết tỷ trọng tổng doanh số các mặt hàng trong 6 tháng.

BÀI TẬP 27: Bài tập tổng hợp
BAÛNG THEO DÕI BÁN HÀNG
Ngày Mua
Tên Sách Số lượng Đơn giá Giảm giá Thành tiền
(dd/mm/yyyy)
LSP31
7
NVP11
7
7
NVP21
7
7
LSP15
7
LSP25
7
LSDR1
7
LSFL2
7
TTP11
7
TTP21
7

Mã Số

DANH MỤC SÁCH
Mã Sách
P1
P2
P3
DR
FL
Tên sách TH đại cương tập 1 TH đại cương tập 2 TH đại cương tập 3 DreamWeaver Flash MX
,
,
,
,
,
Đơn giá

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


BẢNG TỔNG KẾT
Tên Sách
Tổng Số Lượng Tổng Thành Tiền
TH đại cương tập 1
TH đại cương tập 2
TH đại cương tập 3
DreamWeaver
Flash MX
u cầu:
-


SỐ LƯNG là ký tự cuối của MÃ SỐ và chuyển sang giá trị số.
Cột TÊN SÁCH và ĐƠN GIÁ: dựa vào ký tự thứ ba và thứ tư của MÃ SỐ và tra trong BẢNG DANH
MỤC SÁCH để điền vào
Thiết lập công thức điền cột GIẢM GIÁ. Biết rằng nếu ngày mua từ 25/8/2007 đến 31/08/2007 được
giảm giá là 10% ngược lại giảm giá bằng 0.
Tính THÀNH TIỀN = SỐ LƯNG * ĐƠN GIÁ * (100% - GIẢM GIÁ).
Sắp xếp bảng tính TĂNG DẦN theo NGÀY, cùng ngày tăng dần theo TÊN SÁCH.
Rút trích các sách là Tin học đại cương được mua sau ngày 25/08/2007.
Tính TỔNG SỐ LƯNG và TỔNG THÀNH TIỀN trong bảng tổng kết.
Vẽ biểu đồ tỷ trọng cho tổng thành tiền trong bảng tổng kê.

BÀI TẬP 28: Bài tập tổng hợp
BÁO CÁO DOANH THU KHÁCH SẠN HỒ CỐC – VŨNG TÀU
Ngày
STT Mã phòng Ngày nhận Ngày trả Sức chứa Loại phòng Thành tiền
AV- - BT- - AT- - BV- - AV- - AT- - BV- - AT- - BT- - AV- - Bảng Đơn giá (ĐVT:

đ ngày)

Sức chứa
A
B

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


Yêu cầu:
- Chèn thêm vào bên trái cột THÀNH TIỀN 4 cột: KHÁCH VIP, ĐƠN GIÁ, SỐ NGÀY,
GIẢM GIÁ.

- Lập công thức điền cột SỨC CHỨA là giá trị của ký tự cuối mã phịng.
- Lập cơng thức điền cột LOẠI PHỊNG là ký tự đầu của mã phịng.
- Lập công thức điền cột KHÁCH VIP. Nếu ký tự thứ 2 của mã phịng là V thì điền vào “X”,
ngƣợc lại để trống.
- Lập công thức điền cột ĐƠN GIÁ dựa vào SỨC CHỨA và tra Bảng Đơn Giá
- Lập công thức điền cột SỐ NGÀY = NGÀY TRẢ – NGÀY NHẬN.
- Lập công thức điền cột GIẢM GIÁ. Nếu là khách VIP hoặc Số ngày >7 thì giảm giá =
%, ngƣợc lại là 0.
- Lập công thức điền cột THÀNH TIỀN = SỐ NGÀY * ĐƠN GIÁ*( - GIẢM GIÁ).
- Trích lọc những khách hàng ở khách sạn trên 10 ngày
- Trích lọc những khách hàng VIP và ở phòng đơn (sức chứa =1)

BÀI TẬP 29: Bài tập tổng hợp
CHI PHÍ HÀNG NHẬP
THÁNG 01/2011
Tỉ giá USD:
Số
TT

Mã Hàng

21000
Ngày Nhập

Số Lƣợng

Thành
Tiền

Thuế


Cịn Lại

SAM01KOR
PAN02SIN
KIN02MAL
SON01JAP
KIN01MAL
SON02SIN
PAN01JAP
SAM02KOR
SON01JAN
Tổng:
Bảng 1: Tên hãng sản xuất và đơn giá sản phẩm
Mã
Giá Loại Giá Loại
Tên Hãng
Hãng
1
2
KIN
Kingmax
PAN
Panasonic
SAM
Samsung
SON
Sony
Bảng 2: Tên quốc gia và thuế
Jan

Kor
Mal
Sin
Nhật
Hàn quốc
Malaysia Singapore
%
%
%
%

Bảng 3: Tổng tiền từng
loại đĩa
?
Kingmax
?
Panasonic
?
Samsung
?
Sony

Mô tả ý nghĩa của cột MãHàng: 3 ký tự đầu là Mã hãng sản xuất, ký tự thứ 5 là loại hàng, 3
ký tự cuối là mã nước sản xuất.
BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


Yêu cầu:
- Nhập nội dung bảng tính và trang trí giống mẫu.
- Chèn trƣớc cột SỐ LƢỢNG 2 cột: TÊN HÃNG và ĐƠN GIÁ Sau đó dùng hàm dị tìm để

điền dữ liệu vào cột TÊN HÃNG và ĐƠN GIÁ. Cột ĐƠN GIÁ lấy từ cột GIÁ LOẠI 1
hoặc GIÁ LOẠI 2 phụ thuộc vào LOẠI HÀNG.
- Chèn cột TÊN NƢỚC SX sau cột Số lƣợng Sau đó, dựa vào 3 ký tự cuối của Mã hàng
và tra Bảng 2 để điền dữ liệu vào cột TÊN NƢỚC SX
- Tính THÀNH TIỀN = SỐ LƢỢNG * ĐƠN GIÁ * TỶ GIÁ USD. Giảm 5% cho các mặt
hàng có số lƣợng >=50 và là hàng của Malaysia.
- Tính THUẾ: Nếu hàng của Nhật thì thuế là 7% của Thành Tiền, hàng của Hàn quốc thì
thuế là 5% của Thành Tiền, hàng của Malaysia và Singapore thì thuế là 3% của Thành tiền
(Có thể dựa vào Bảng để lấy các giá trị phần trăm)
- Tính cột CỊN LẠI = THÀNH TIỀN - THUẾ.
- Tính Tổng cột THÀNH TIỀN, THUẾ và cột CỊN LẠI.
- Sắp xếp danh sách tăng dần theo TÊN HÃNG. Cùng tên hãng thì sắp xếp SỐ LƢỢNG
giảm dần.
- Thống kê Tổng Tiền Từng Loại Đĩa tại Bảng 3.
- Lọc ra các mặt hàng là đĩa của Sony hoặc Kingmax.
- Lọc ra các mặt hàng có Ngày Nhập từ ngày 10/01 đến 20/01.

BÀI TẬP 30: Bài tập tổng hợp

BẢNG TÍNH TIỀN KHÁCH SẠN
Tên KH Quốc tịch Mã PH
David
Kim
Dũng
Nam
Hùng
Minh
John
Yoo
Lee

Peter

Pháp
Korea
Việt Nam
Korea
Việt Nam
Việt Nam
Mỹ
Korea
Korea
Anh

Ngày đến

Ngày đi

Số
ngày
ở

Tiền
Tổng
Tiền ăn Tiền PV
phòng
cộng

L1A-F1
L1A-F1
L1A-F3

L1B-F2
L1C-F1
L2A-F2
L2A-F2
L2A-F3
L2B-F1
L2B-F2

BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu


BẢNG ĐƠN GIÁ PHÕNG
Loại phòng
A
Lầu
L1
L2
L3

BẢNG GIÁ ĂN
B

C

Loại phòng

F1

F2


F3

Giá

15

10

5

BẢNG THỐNG KÊ TIỀN PHÕNG
Loại phịng
A
B
C
L1
L2

Mơ tả:
- 2 ký tự đầu của Mã phịng cho biết phịng đó thuộc Lầu nào.
- Ký tự thứ của Mã phòng cho biết Loại phòng.
Yêu cầu:
- SỐ NGÀY Ở = (NGÀY ĐI - NGÀY ĐẾN) +1
- TIỀN PHÒNG = SỐ NGÀY Ở * ĐƠN GIÁ PHÒNG.
- ĐƠN GIÁ PHỊNG: Dựa vào Loại phịng, tra trong BẢNG ĐƠN GIÁ PHÒNG kết hợp với
Lầu để lấy giá trị.
- TIỀN ĂN = SỐ NGÀY Ở * GIÁ ĂN (Định dạng ,
)
- GIÁ ĂN: Dựa vào 2 ký tự cuối của Mã phịng, tra trong Bảng Giá Ăn để có giá trị hợp lý
- TIỀN PV: Nếu là khách trong nƣớc (Việt Nam) thì tiền PV = 0, ngƣợc lại thì TIỀN PV =

SỐ NGÀY Ở * 2 (USD/NGÀY).
- TỔNG CỘNG = TIỀN PHÒNG + TIỀN ĂN + TIỀN PV
- Sắp xếp bảng tính tăng dần theo MÃ PHỊNG, nếu trùng thì sắp giảm dần theo QUỐC
TỊCH.
- Rút trích ra danh sách khách hàng có Quốc tịch là Korea và Anh ở tại khách sạn trong
15 ngày đầu của tháng 09/2012.
- Thống kê tiền phòng theo mẫu trên.
- Vẽ đồ thị dạng tròn, biểu diễn tỷ lên tiền phòng thu đƣợc của từng loại Phòng so với tổng
số tiền phòng thu đƣợc.

BÀI TẬP 31: Bài tập tổng hợp
Yêu cầu:
- Sử dụng công thức hãy điền giá trị cho cột SỐ THỨ TỰ.
- Đối với Cột CHỨC VỤ, yêu cầu sinh viên sử dụng Data Validation để chọn từ danh sách
với giá trị tƣơng ứng trong bảng trên.
- Căn cứ vào ký tự đầu tiên bên trái của Mã Nhân Viên và BẢNG TRA PHÒNG BAN, hãy
điền TÊN PHÒNG BAN cho các nhân viên ở cột Phòng.
- Căn cứ vào số liệu ở cột CHỨC VỤ và BẢNG TRA PHỤ CẤP, hãy tính tiền PHỤ CẤP
CHỨC VỤ cho mỗi nhân viên.
- Tính THỰC LÃNH = PHỤ CẤP CHỨC VỤ + LƢƠNG.
BÀI TẬP THỰC HÀNH – XỬ LÝ BẢNG TÍNH – ThS. Huỳnh Đỗ Bảo Châu