Transcript giai-bai-toan-quan-ly-bang-excel

Giải bài toán quản lý bằng
Excel
Đặc điểm bài toán quản lý
• Thông tin hầu hết có dạng bảng 2 chiều:
– Bảng lương
– Bảng kê nhập xuất vật tư
• Số cột là cố định.
• Số dòng thay đổi tùy theo dữ liệu nhiều
hay ít.
Đặc điểm của 1 cột
• Mỗi cột đều chứa thông tin thống nhất về
1 tiêu chí nào đó:
– Họ tên.
– Mức lương
• Mỗi cột có một tên riêng
• Thông tin trong cùng 1 cột có cùng kiểu
dữ liệu
Đặc điểm của cột
• Các loại cột:
– Cột thông tin đầu vào
– Cột thông tin đầu ra
– Cột thông tin trung gian
Thông tin đầu vào
• Là các cột có thông tin đã cho trước:
– Họ tên.
– Đơn vị….
• Các loại cột đầu vào:
– Thông tin tự do: Nhập liệu tự do
– Thông tin cần được chuẩn hóa: Nếu cần
thống nhất dữ liệu để tìm kiếm, thống kê.
Thông tin đầu ra
• Là các cột được tính toán từ các công
thức, dựa trên thông tin đầu vào và/hoặc
thông tin trung gian.
Thông tin trung gian
• Trong trường hợp công thức tính toán cột
đầu ra phức tạp, phải bổ sung các cột
trung gian để việc tính toán được dễ dàng
hơn.
Phương pháp giải bài toán quản lý
• Xác định các cột thông tin đầu vào và đầu
ra.
• Tất cả các cột ban đầu để chung trong 1
bảng.
• Lập công thức cho các cột đầu ra.
• Nếu công thức phức tạp, cần bổ sung các
cột trung gian.
Các công thức thường dùng cho
thông tin đầu vào
• Nếu cần chuẩn hóa, thống nhất thông tin thì tạo
1 bảng phụ đã mã hóa thông tin.
• Hàm thường dùng: VLookUp
• Ví dụ: Trong bảng lương thì cột Đơn vị cần
được mã hóa.
• Dùng bảng phụ ĐƠN VỊ, có 2 cột Mã Đơn vị và
Tên Đơn vị.
• Trong bảng lương chính, có cột Mã đơn vị, tự gõ
vào, dùng hàm VlookUp để lấy ra thông tin cho
cột Tên Đơn vị
Công thức cho thông tin đầu ra
• Tìm công thức liên hệ giữa thông tin đầu
vào và thông tin đầu ra.
• Xác định hàm tương ứng cần sử dụng.
• Chú ý cách dùng địa chỉ tương đối, tuyệt
đối để sao cho chỉ xác định công thức cho
dòng đầu tiên, sao đó có thể COPY cho
toàn bộ các dòng bên dưới.
Bài toán quản lý học phí –
Mức đơn giản
Yêu cầu
• Hệ đào tạo: chính quy tập trung
• Mức học phí thay đổi theo năm học. Nếu
đóng học phí trễ hạn thì phần chưa đóng
theo tỷ lệ của năm học trước sẽ tính theo
mức học phí mới. Có thể đóng nhiều lần
cho 1 năm học.
Bài toán quản lý học phí –
Mức đơn giản
Thông tin đầu vào
• Danh sách lớp
• Biên lai học phí
• Mức học phí cho từng năm học
Bài toán quản lý học phí –
Mức đơn giản
Thông tin đầu ra
– Danh sách sinh viên nợ học phí theo từng lớp
– Tình hình đóng học phí theo lớp
– Chi tiết đóng học phí của từng sinh viên theo
yêu cầu
– Tổng hợp tình hình đóng học phí theo khóa –
lớp
– Tổng hợp tình hình nợ học phí theo khóa –
lớp.
BÀI TOÁN TÍNH LƯƠNG –
Mức đơn giản
• Yêu cầu tính lương cho 1 đơn vị có các thông tin
sau:
– Mỗi người có 1 hệ số lương.
– Mức lương cơ bản cho hệ số 1 là 2.000.000đ
– Hàng tháng có mức phụ cấp riêng tùy theo từng
người.
– Phải nộp BHXH (16%), BHYT (1.5%), BHTN (1%)
trên lương cơ bản
– Nộp thuế TNCN theo mức lũy tiến (<4tr: 0%, <9tr:
5%, <14tr: 10%, <19tr: 15%, trên 19tr: 20%.
TÍNH LƯƠNG – MỨC PHỨC TẠP
• Yêu cầu tính thêm mức giảm trừ gia cảnh:
– Cha mẹ quá tuổi lao động.
– Con dưới 18 tuổi hoặc đang đi học.
• Mỗi người phụ thuộc được giảm trừ
1.6triệu vào thu nhập chịu thuế
CÁCH GIẢI QUYẾT
• Phương án 1: Bổ sung thêm cột tương
ứng số người được giảm trừ.
• Phương án 2: Tạo thêm bảng phụ chứa
thông tin người phụ thuộc
Nhận xét
• Nhận xét:
– Không phải ai cũng có người phụ thuộc.
– Số người phụ thuộc cũng không như nhau.
• Trong trường hợp như vậy, người ta
thường tổ chức thành bảng phụ để chứa
số người phụ thuộc.
• Trong bảng chính, sẽ đếm số người phụ
thuộc từ bảng phụ để tính toán.
Tổ chức thông tin nhiều bảng
• Thường các bài toán cần nhiều bảng để
lưu trữ thông tin.
• Bảng chính chứa những thông tin cơ bản
mà mọi mẫu tin đều có.
• Những thông tin mà có mẫu tin có, mẫu tin
không hoặc mẫu tin có nhiều, mẫu tin ít thì
chứa vào bảng riêng.
Tổ chức thông tin nhiều bảng
• Các bảng này phải có cột gọi là KHÓA (key) để
liên kết với nhau.
• Bảng chính: Mỗi mẫu tin phải có 1 khóa nhận
dạng, không trùng nhau, gọi là Primary Key (PK)
• Bảng phụ: Có 1 vùng chứa cùng thông tin như
Primary Key của bảng chính, nhưng có thể lập
lại gọi là Foreign key (FK).
• Giữa hai bảng sẽ kết nối với nhau qua PK-FK
Cách kết nối các bảng trong Excel
• VLOOKUP – HLOOKUP
• SUMIF
• COUNTIF
PHƯƠNG PHÁP TỔNG HỢP
THÔNG TIN
• - Trường hợp 1: Đã có thông tin các
nhóm cần tổng hợp, chỉ cần dùng công
thức để tính số liệu cho các ô tương ứng > SUMIF, COUNTIF
• Ví dụ:
– Dựa trên bảng phụ danh mục đơn vị để tính
bản tổng hợp lương gồm các cột Đơn vị - Số
người – Mức Lương – Thực lãnh…
Trường hợp 2
• Tạo dòng tổng theo nhóm trong bảng chi
tiết -> Dùng Subtotal
• Ví dụ: Trong bảng lương, mỗi đơn vị in 1
trang, cuối mỗi đơn vị in dòng tổng của
đơn vị.
Trường hợp 3
• Tổng hợp theo nhiều chiều -> Dùng
PIVOT TABLE.
• Dòng: 0-n chiều
• Cột: 0-n chiều
• Công thức: Mỗi ô công thức (Sum/ Count/
Max/ Min..) sẽ được tính theo tham số cột
và dòng tương ứng
PIVOT TABLE
• Tính số người và tổng lương phải trả theo
từng tháng.
• Dòng: Đơn vị
• Cột : Tháng
• Công thức: Đếm số người và tổng số tiền
theo đơn vị và tháng.
Các hàm kiểu chuỗi
• Chú ý các khoảng trắng (space) trước và sau
chuỗi khi dùng hàm LEFT, RIGHT -> Dùng hàm
TRIM.
• Phối hợp các hàm khi cắt chuỗi một phần bên
trong từ bên phải qua -> Dùng hàm LEN hoặc
RIGHT + LEFT.
• Khi sắp xếp thứ tự, so sánh chuỗi, nên dùng
hàm UPPER, LOWER, PROPER để quy về
cùng kiểu chữ.
• Dùng hàm VALUE khi cần chuyển chuỗi kiểu số
thành số để so sánh, làm các phép toán số học
Các hàm tính toán
• Không có điều kiện: SUM, COUNT, AVERAGE,
MAX, MIN
• Có điều kiện đơn giản (=, >, < ): SUMIF,
COUNTIF
• Có điều kiện phức tạp (AND, OR): DSUM,
DCOUNT, DAVERAGE, DMAX, DMIN
Các hàm kiểu ngày
• Bản chất là kiểu số 1= 1/1/1900
• Kiểu xuất hiện: Format cell – Date
• Các phép toán:
– Ngày – Ngày = Số ngày
– Ngày + số ngày = Ngày
• Tách ngày – tháng – năm: DAY – MONTH –
YEAR
• Để so sánh với ngày cụ thể: dùng hàm
DATE hoặc nhập liệu vào 1 ô rồi dùng địa
chỉ để so sánh
Các hàm kiểu giờ
• Chú ý 1 giờ = 60 phút; 1 phút = 60 giây
• Tách giờ phút giây bằng: HOUR –
MINUTE – SECOND.
• Dùng hàm TIME để so sánh với thời gian
cụ thể
Các hàm dò tìm
• VLOOKUP/ HLOOKUP
• CHOOSE: Lấy một phần tử thứ <i> trong
n phần tử cho trước (i = 1-n).
• Lấy phần tử hàng m, cột n trong ma trận 2
chiều: INDEX.
• Hàm MATCH thường kết hợp với INDEX
để biết hàng – cột cần lấy.