Bài giảng Tin học ứng dụng - Chương 2: Kỹ thuật bảng tính nâng cao

BÀI GIẢNG MÔN TIN HỌC ỨNG DỤNG
 PHẦN 1
 TIN HỌC VĂN PHÒNG 
 NÂNG CAO
 Khoa Hệ thống thông tin quản lý
 Trường ĐH Ngân hàng TP. HCM
 CHƯƠNG 2
KỸ THUẬT BẢNG TÍNH
 NÂNG CAO
 2
Mục tiêu
• Sử dụng phần mềm MS Excel để thực hiện các 
 thao tác xử lý bảng tính nâng cao.
 3
 NỘI DUNG
1 Lập và định dạng bảng tính
2 Quản lý dữ liệu
3 Biểu đồ nâng cao
 4
1. Lập và định dạng bảng tính
a. Định dạng có điều kiện
b. Điền dữ liệu tự động
c. Sử dụng công thức mảng
 5
 Định dạng có điều kiện
• Làm nổi bật các ô thỏa mãn
 điệu kiện (luật);
• Làm nổi bật các ô giá trị lớn 
 nhất (Top)/nhỏ nhất (Bottom);
• Chèn thanh dữ liệu;
• Tô màu/chèn biểu tượng theo
 khoảng giá trị.
 6
 Làm nổi các ô thỏa
 mãn điểu kện
Làm nổi bật các ô có giá trị lớn hơn
Làm nổi bật các ô có giá trị nhỏ hơn
Làm nổi bật các ô có giá trị giữa hai giá trị
Làm nổi bật các ô có giá trị bằng một số cố
định
 Làm nổi bật các ô mà văn bản có chứa 
 Làm nổi bật các ô mà ngày xảy ra là 
 Làm nổi bật các ô có giá trị trùng lặp
 Tạo luật định dạng mới 7
 Làm nổi các ô đỉnh/đáy
Định đạng 10 mục hàng đầu
Định dạng 10% mục hàng đầu trong danh sách
 Định dạng 10 mục dưới cùng
 Định dạng 10% mục dưới cùng trong danh sách
Định dạng các ô có giá trị trung bình trên 
Định dạng các ô có giá trị trung bình dưới
Thêm luật định dạng mới
 8
 Chèn thanh dữ liệu
 Tô thanh dữ liệu 
 với màu sắc kiểu 
 Gradient có sẵn
 Tô thanh dữ liệu 
 với màu sắc kiểu 
 Solid có sẵn
Thêm luật định dạng mới
 9
 Tô màu theo khoảng
 giá trị
Danh sách 
các thang 
màu có sẵn
 Thêm luật định dạng mới
 10
Chèn biểu tượng
 khoảng giá trị
 Các bộ biểu 
 tượng có sẵn
 11
 Thêm luật định dạng mới
Tạo luật định dạng mới
 Chọn loại luật định dạng
 Hiệu chỉnh mô tả luật 
 định dạng
 Kiểu định dạng
 12
 Quản lý các luật định dạng
 Sửa luật Sửa luật 
 định dạng định dạng
Tạo 
luật 
định 
dạng Luật 
mới đang 
 chọn 
 để
 chỉnh 
 sửa
 13
 Điền dữ liệu tự động
• Điền giá trị/công thức vào dãy ô được chọn.
 14
 Fill Series
• Điền dãy cấp số
 cộng/nhân/ngày
 tháng vào vùng
 được chọn:
 15
Flash Fill (Excel 2013)
• Công cụ tổng hợp/phân tích nhanh các chuỗi
 (không cần sử dụng công thức).
• Ví dụ:
 – Phân tích Tên lớp thành Hệ ĐT, Khóa, 
 Chuyên ngành;
 – Phân tích Họ tên => Họ, Họ lót và Tên;
 – Tổng hợp Họ, Họ lót và Tên => Họ tên.
 16
Flash Fill (tt)
 Ctrl + E Dòng mẫu
 17
Công thức mảng
• Thực hiện trên một vùng ô (mảng);
• Đặt trong cặp ngoặc móc (“{“, “}”);
• Không xóa/sửa được một phần mảng;
• Viết công thức mảng:
 – Chọn vùng ô;
 – Gõ công thức;
 – Nhấn Ctrl + Shift + Enter.
 18
Ví dụ
 19
Tham khảo : Phép toán ma trận
• Cộng hai ma trận;
• Nhân ma trận với một số;
• Tính định thức ma trận;
• Tạo ma trận nghịch đảo (Hàm MINVERSE);
• Tạo ma trận chuyển vị (Hàm TRANSPOSE);
• Nhân hai ma trận (Hàm MMULT).
 20
2. Quản lý dữ liệu
a. Lập bảng thống kê nhóm với lệnh Subtotal
b. Thống kê dữ liệu bằng Pivot Table và Pivot 
 Chart
 21
Lập bảng thống kê nhóm với lệnh 
Subtotal
• Lập bảng thống kê (Sum, 
 Count, Max, Min, Average, 
 ) cho các nhóm dữ liệu.
• Bảng dữ liệu được sắp xếp
 theo nhóm trước khi thực
 hiện thống kê.
 22
Sử dụng lệnh Subtotal
Sử dụng lệnh Subtotal
 24
Thống kê dữ liệu bằng PivotTable và
PivotChart
• Tạo báo cáo tổng hợp từ bảng dữ liệu.
• Tạo bảng Pvot Table:
 – Lệnh Insert | Pivot Table;
 – Chỉ định nguồn dữ liệu;
 – Chỉ định vị trí báo cáo;
 – Chọn trường tiêu đề hàng – cột; 
 – Chọn trường cần thống kê (ô).
 25
Thống kê dữ liệu bằng PivotTable và
PivotChart (tt)
Thống kê dữ liệu bằng PivotTable và
PivotChart (tt)
 Bảng hoặc 
 dải dữ liệu 
 dùng để vẽ
 PivotTable
 Dùng 
 Chọn nơi đặt nguồn dữ
 PivotTable là ở một liệu bên 
 trang tính khác ngoài để vẽ
 PivotTable
 Chọn nơi đặt 
 PivotTable nằm cùng 
 bảng/dãi dữ liệu
 27
Thống kê dữ liệu bằng PivotTable và
PivotChart (tt)
 28
Thống kê dữ liệu bằng PivotTable và
 PivotChart (tt)
 20
 18
 Millions 16
 14 Công tác phí
 12 Hỗ trợ đi lại
 10 Lương kỳ 1/12
 8 Phụ cấp ngoài giờ
 6 Thưởng hoa hồng
 4 Tiếp khác
 2
 0
 12/2/2016 12/4/2016 12/7/2016 12/13/2016 12/16/2016 12/17/2016 12/23/2016
 29
3. Biểu đồ
• Tạo Sparkline
• Tạo đường xu hướng (Trendline)
• Biểu đồ tích hợp
 30
Sparkline
• Sparkline là biểu đồ đường rất nhỏ, thường 
 được vẽ không có trục hoặc tọa độ. Nó trình 
 bày hình dạng chung của sự thay đổi trong 
 một số phép đo, chẳng hạn như nhiệt độ hoặc 
 giá thị trường chứng khoán, theo một cách 
 đơn giản và đặc biệt.
• Các thao tác với Sparkline:
 – Tạo Sparkline
 – Hiệu chỉnh Sparkline.
 31
 Tạo Sparkline
Vào thẻ Insert => trong nhóm Sparklines, chọn kiểu biểu đồ
Sparkline muốn vẽ. Các kiểu 
 biểu đồ
 Sparkline
 Dãi dữ liệu sử
 dụng vẽ
 Sparkline
 Chọn nơi đặt Sparkline
 32
Tạo Sparkline
 33
Hiệu chỉnh Sparkline
 34
Tạo đường xu hướng (Trendline)
• Được bổ sung thêm vào các đồ thị chuỗi thời
 gian, thể hiện xu hướng biến động của chuỗi
 dữ liệu.
• Công cụ dự dự báo, hỗ trợ phân tích kinh
 doanh.
 35
Các loại đường xu hướng
• Đường trung bình di chuyển (Moving 
 Average);
• Đường hồi qui tuyến tính;
• Đường hồi qui ex;
• Đường hồi qui b.xa;
• Đường hồi qui logarithm;
• Đường hồi qui đa thức (bậc n);
 36
37
Ví dụ 1: Số trẻ em duy dinh dưỡng
 38
Ví dụ 2: Dự báo doanh số
 Tháng Doanh số
• Công ty X kinh doanh một 1 10
 loại sản phẩm mới. Cho 2 11
 doanh số sản phẩm trong 3 12.5
 8 tháng đầu, vẽ đồ thị, tạo 4 15
 đường xu hướng và dự báo 5 20
 doanh số sản phẩm trong 2 6 30
 tháng kế tiếp. 7 45
 8 70
 39
Kết quả của Trendline
 40
Biểu đồ tích hợp
• Cho phép thể hiện nhiều dãy dữ liệu khác biệt
 (khoảng giá trị, đơn vị đo trên một biểu đồ).
• Mỗi dãy giá trị được biểu diễn với một kiểu
 biểu đồ.
• Sử dụng 2 cột giá trị (Primary, Secondary) cho
 các dãy dữ liệu khác nhau.
 41
 Chọn loại
biểu đồ và 
 trục cho
serie dữ liệu
 42
Q&A
 43
 Chương kế tiếp 
ỨNG DỤNG EXCEL GIẢI 
CÁC BÀI TOÁN KINH TẾ