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

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
BÀI GIẢNG MÔN TIN HỌC ỨNG DỤNG
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
Lập và định dạng bảng tính1
Quản lý dữ liệu2
Biểu đồ nâng cao3
4
1. Lập và định dạng bảng tính
Địnha. dạng có điều kiện
Điềnb. 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 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
Làm nổi các ô thỏa
mãn điểu kện
7
Đị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
Làm nổi các ô đỉnh/đáy
8
Thêm luật định dạng mới
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
Chèn thanh dữ liệu
9
Danh sách 
các thang 
màu có sẵn
Thêm luật định dạng mới
Tô màu theo khoảng
giá trị
10
Các bộ biểu 
tượng có sẵn
Thêm luật định dạng mới
Chèn biểu tượng
khoảng giá trị
11
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
Luật 
đang 
chọn 
để
chỉnh 
sửa
Sửa luật 
định dạng
Sửa luật 
định dạng
Tạo 
luật 
định 
dạng 
mới
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
Lậpa. bảng thống kê nhóm với lệnh Subtotal
Thốngb. 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 
nguồn dữ
liệu bên 
ngoài để vẽ
PivotTable
Chọn nơi đặt 
PivotTable là ở một 
trang tính khác 
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
02
4
6
8
10
12
14
16
18
20
12/2/2016 12/4/2016 12/7/2016 12/13/2016 12/16/2016 12/17/2016 12/23/2016
M
ill
io
n
s
Công tác phí
Hỗ trợ đi lại
Lương kỳ 1/12
Phụ cấp ngoài giờ
Thưởng hoa hồng
Tiếp khác
Thống kê dữ liệu bằng PivotTable và
PivotChart (tt)
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ố
Công• ty X kinh doanh một
loại sản phẩm mới. Cho 
doanh số sản phẩm trong
8 tháng đầu, vẽ đồ thị, tạo
đường xu hướng và dự báo
doanh số sản phẩm trong 2 
tháng kế tiếp.
Tháng Doanh số
1 10
2 11
3 12.5
4 15
5 20
6 30
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Ế