Giải bài toán quản lý bằng 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ươngBả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ộtMỗ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ươngMỗi cột có một tên riêngThông tin trong cùng 1 cột có cùng kiểu dữ liệuĐặc điểm của cộtCác loại cột:Cột thông tin đầu vàoCột thông tin đầu raCột thông tin trung gianThông tin đầu vàoLà 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ự doThô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 raLà 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 gianTrong 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àoNế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: VLookUpVí 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 raTì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ảnYêu cầuHệ đào tạo: chính quy tập trungMứ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ảnThông tin đầu vàoDanh sách lớpBiên lai học phíMức học phí cho từng năm họcBài toán quản lý học phí – Mức đơn giảnThông tin đầu raDanh sách sinh viên nợ học phí theo từng lớpTình hình đóng học phí theo lớpChi tiết đóng học phí của từng sinh viên theo yêu cầuTổng hợp tình hình đóng học phí theo khóa – lớpTổ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ảnYê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ảnNộp thuế TNCN theo mức lũy tiến ( SUMIF, COUNTIFVí 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ãnhTrường hợp 2Tạo dòng tổng theo nhóm trong bảng chi tiết -> Dùng SubtotalVí 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 3Tổng hợp theo nhiều chiều -> Dùng PIVOT TABLE.Dòng: 0-n chiềuCột: 0-n chiềuCô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 ứngPIVOT TABLETính số người và tổng lương phải trả theo từng tháng.Dòng: Đơn vịCột : ThángCô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ỗiChú ý 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ọcCác hàm tính toánKhông có điều kiện: SUM, COUNT, AVERAGE, MAX, MINCó điều kiện đơn giản (=, >, 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.