Bài giảng Excel 1

1
EXCEL TRONG QUẢN LÝ
DỮ LiỆU
2
Yêu cầu kiến thức
Hiểu biết cơ bản về phần mềm Microsoft Excel.
Nắm được các hàm cơ bản trong Excel.
Giải quyết được các bài liên quan.
3
Nội dung bài
Ôn lại các kiểu dữ liệu, các phép toán trong Excel.
Hiểu và sử dụng các hàm thông dụng trong Excel.
Bảo mật và ràng buộc nhập liệu trong Excel.
Chức năng phân tích dữ liệu Pivot Tables.
4
Kiểu dữ liệu trong Excel
Number
Kiểu dữ liệu dạng số
Text
Kiểu dữ liệu dạng chữ
Date
Kiểu dữ liệu dạng ngày
Time
Kiểu dữ liệu dạng giờ
….
5
MS Excel đưa ra 4 loại phép toán chính:
Phép toán số học: thực hiện các phép toán cơ bản như cộng (), trừ (), nhân (*), chia (), phần trăm (), lũy thừa (^).
Phép toán so sánh: dùng để so sánh 2 giá trị và trả về kiểu luận lý (đúng/ sai). Phép toán so sánh gồm: <, <=, >, >=, =, < >
Các phép toán trong Excel (1)
6
Phép toán chuỗi (): dùng để nối các chuỗi và tạo ra chuỗi mới.
Phép toán tham chiếu: dùng chỉ phạm vi các Ô hay khối (block) cần truy xuất.
Các phép toán trong Excel (2)
7
8
Các hàm thông dụng trong Excel

9
HÀM CHUỖI
10
HÀM CHUỖI (1)
11
HÀM LUẬN LÝ (1)
AND(BtLogic1, BtLogic2, .): trả về True nếu tất cả các BtLogic có giá trị True, False nếu có ít nhất một BtLogic có giá trị False.
OR(BtLogic1, BtLogic2, .): trả về False nếu tất cả các BtLogic có giá trị False, True nếu có ít nhất một BtLogic có giá trị True.
NOT(btLogic): trả về giá trị phủ định của BtLogic
12
HÀM LUẬN LÝ (2)
IF(BtLogic, Giá trị_1, Giá trị_2)
Nếu BtLogic có giá trị đúng thì trả về Giá trị_1, ngược lại trả về Giá trị_2.
13
HÀM NGÀY GIỜ (1)
14
HÀM NGÀY GIỜ (2)
15
HÀM SỐ HỌC
16
HÀM THỐNG KÊ (1)
17
HÀM THỐNG KÊ
18
HÀM DÒ TÌM (1)
Hàm dò tìm và tham chiếu bảng 1 chiều
VLOOKUP hàm dò tìm theo CỘT;
HLOOKUP hàm dò tìm theo DÒNG;
19
HÀM DÒ TÌM (2)
VLOOKUP (giá trị dò, bảng dò, cột giá trị, kiểu dò): dò tìm giá trị dò trên cột đầu tiên của bảng dò.
Nếu tìm thấy thì trả về giá trị ở Ô tương ứng trên cột chỉ ra trong hàm.
Nếu không tìm thấy thì trả về # N/A (Not Available)
Kiểu dò là: <>0 (hoặc không chỉ rõ): dò tìm xấp xỉ, = 0: dò tìm chính xác
20
HÀM DÒ TÌM (3)
21
HÀM DÒ TÌM (4)
HLOOKUP (giá trị dò, bảng dò, hàng giá trị, kiểu dò): dò tìm giá trị dò trên hàng đầu tiên của bảng dò. Tương tự như hàm VLOOKUP nhưng dò theo hàng đầu của bảng dò.
Nếu tìm thấy thì trả về giá trị ở Ô tương ứng trên hàng chỉ ra trong hàm.
Nếu không tìm thấy thì trả về # N/A.
22
Bảo mật và ràng buộc nhập liệu
23
Bảo mật trong Excel (1)
Các mức bảo mật trong Excel
Bảo vệ bằng mật khẩu
Bảo vệ bảng tính
Bảo vệ các sheet
Bảo vệ ô dữ liệu
24
Bảo vệ bằng mật khẩu (1)
Giúp bảo vệ bảng tính Excel tránh truy cập trái phép
Có 2 mức bảo vệ bằng mật khẩu:
Mật khẩu đọc nội dung file
Mật khẩu sửa nội dung file
25
Bảo vệ bằng mật khẩu (2)
Các bước thiết lập mật khẩu cho file Excel:
26
Bảo vệ bằng mật khẩu (3)
Chọn Tools ở góc trên bên phải. Trong menu thả xuống, chọn General Options:
27
Bảo vệ bảng tính (1)
Chức năng bảo vệ bảng tính cho phép thiết lập các bảo vệ về cấu trúc (Structure) và kích thước cửa sổ (Window)
Structure: không cho thay đổi cấu trúc bảng tính như thêm, xóa, sửa các sheet.
Windows: ngăn cấm việc thay đổi kích thước, phóng to, thu nhỏ v.v.
28
Bảo vệ bảng tính (2)
Tools > Protection > Protect Workbook
29
Bảo vệ sheet
Giúp bảo vệ không cho thao tác trên sheet
Chọn menu Tools > Protection > Protect Sheet
30
Bảo vệ ô dữ liệu
Kết hợp với bảo vệ sheet, bảo vệ ô dữ liệu không cho thay đổi.
Click chuột phải vào ô dữ liệu tương ứng và chọn Format cells  Protection
31
Ràng buộc nhập liệu (1)
Tên tiếng anh: Data validation
Giúp kiểm tra khi người dùng nhập liệu vào bảng tính
Bảo đảm được tính chính xác dữ liệu
Hỗ trợ tốt cho người dùng khi làm việc với dữ liệu lớn và phức tạp như:
Bán hàng
Bảng lương
Bảng chấm công
…….
32
Ràng buộc nhập liệu (2)
Thiết lập ràng buộc nhập liệu:
Chọn Menu Data  Validation
33
Ràng buộc nhập liệu (3)
Any value: Là giá trị mặc định của ô, cho phép đưa vào giá trị bất kỳ.
Whole number và Decimal: Chỉ cho phép nhập các giá trị số.
List: Dữ liệu nhập phải nằm trong một danh sách các dữ liệu cho trước nào đó.
34
Ràng buộc nhập liệu (4)
Date: Dữ liệu nhập phải là kiểu ngày.
Time: Dữ liệu nhập phải là kiểu thời gian.
Text length: Kiểm tra độ dài của xâu ký tự nhập
 Khi người dùng nhập sai dữ liệu với những qui tắc đã thiết lập, Excel sẽ báo lỗi và hiện ra thông báo nhắc nhở.
35
Phân tích dữ liệu Pivot Tables
36
Khái niệm Pivot
Bảng Pivot - Pivot table - là một bảng làm việc sử dụng để:
Tổng kết, thống kê
Phân tích số liệu dựa trên một nguồn dữ liệu có sẵn nào đó.
Khi sử dụng bảng Pivot, dữ liệu ban đầu vẫn được giữ nguyên không bị thay đổi gì.
37
Sử dụng bảng Pivot (1)
Bảng Pivot có thể được tạo từ các nguồn dữ liệu khác nhau như:
Dữ liệu từ chính bảng tính Excel.
Dữ liệu từ Cơ sở dữ liệu bên ngoài.
Các đoạn dữ liệu được hợp nhất trong Excel.
Một bảng Pivot khác trong cùng một trang làm việc.
38
Sử dụng bảng Pivot (1)
Cần cho biết:
Xác định thông tin cần báo cáo
Xác định trường nào theo cột, trường nào theo dòng
39
Tạo PivotTables
Thực hành:
Tổng kết doanh thu bán hàng của Siêu thị điện máy Toàn Thắng
40
Câu hỏi và bài tập
Quản lý điểm
Quản lý hàng hoá
Quản lý nhân sự