Giáo trình Microsoft Excel

Khai báo vùng tiêu chuẩn 
• Copy to : Địa chỉ chứa các mẫu tin sau khi lọc 
+ Chọn OK 
Chú ý: Sau khi chọn tr−ờng hợp thứ nhất (CSDL ẩn đi), khi muốn hiển thị toàn bộ 
CSDL ta vào Data chọn Filter chọn Show All 
Giáo trình Microsoft Excel Trung tâm tin học CIC 
 87A - Thợ Nhuộm Hà Nội 
Path: P.Q.Huy C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\Giao_trinh_Excel_nang_cao.doc 13
Ch−ơng 4 
 Sắp xếp dữ liệu vμ thống kê theo nhóm 
1. Sắp xếp thứ tự CSDL: 
Bấm chuột tại ô bất kỳ trong CSDL 
Vào Data/ Sort 
Trong đó 
• Sort by: Chọn tên cột (tên tr−ờng) thứ nhất cần sắp xếp 
- Chọn kiểu sắp xếp 
+ Ascending: Tăng dần hoặc A ->Z 
+Descending: Giảm dần hoặc Z->A 
• Then by: Chọn cột cần sắp xếp tiếp theo 
Nếu ta chọn Header row thì trong mục sort by sẽ xuất hiện tên tr−ờng cần sắp xếp 
Nếu ta chọn No header row thì trong mục sort by sẽ xuất hiện tên cột cần sắp xếp 
trong bảng tính. 
2. Tính năng Subtotals 
B−ớc 1: Sắp xếp dữ liệu của cột cần thống kê số lệu theo nhóm theo thứ tự (A->Z, Z->A) 
B−ớc 2: Bấm chọn ô bất kỳ trong CSDL 
- Vào Data/ Subtotal 
Giáo trình Microsoft Excel Trung tâm tin học CIC 
 87A - Thợ Nhuộm Hà Nội 
Path: P.Q.Huy C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\Giao_trinh_Excel_nang_cao.doc 14
Hộp thoại Subtotals xuất hiện: 
+ Bấm vào hộp At each change in để chọn vùng tin ( tr−ờng) cần nhóm. 
+ Chọn hàm cần thực hiện ở Use function 
+ Bấm chọn tr−ờng cần tính ở Add Subtotals to 
+ Nếu muốn ngắt các nhóm thành từng trang ta bấm chọn Page break between grop 
+ Chọn OK 
- Muốn huỷ bỏ việc thống kê theo nhóm vào Data/ Subtotals/ Remove All 
Giáo trình Microsoft Excel Trung tâm tin học CIC 
 87A - Thợ Nhuộm Hà Nội 
Path: P.Q.Huy C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\Giao_trinh_Excel_nang_cao.doc 15
Ch−ơng 5 
Hộp hội thoại Form vμ Phân tích dữ liệu 
1/ Hộp hội thoại Form 
- Đặt con trỏ ô nằm trong phạm vi vùng Database 
- Vào Data/Form. Hộp hội thoại xuất hiện 
Trong đó hiện nội dung CSDL theo từng mẫu tin, có thể sử dụng các phím sau: 
• ìỉ: Đến mẫu tin tr−ớc, sau mẫu tin hiện hành 
• Enter: Đến mẫu tin sau mẫu tin hiện hành 
• PgUp/PgDn: đến mẫu tin đầu/cuối 
• Tab/Shift-Tab: Di chuyển qua lại giữa các 
khung dữ liệu. 
Công dụng của những nút lệnh trong hộp 
hội thoại: 
New: Nhập mẫu tin mới 
Delete: Xoá mẫu tin hiện hành 
Restore: Khôi phục dữ liệu vừa nhập/xoá 
Find Prev: Đến mẫu tin tr−ớc mẫu tin 
hiện hành 
Find Next: Đến mẫu tin sau mẫu tin hiện 
hành 
Criteria: Nhập điều kiện truy tìm 
Close: Đóng cửa sổ Data Form 
2/ Sử dụng PiVol Table để phân tích bảng tính 
Ví dụ: Dựa vào bảng sau hãy phân tích số l−ợng hàng đã giao cho từng khách hàng 
Đơn vị Sản phẩm Số l−ợng Giá Trị giá Thuế Tổng 
Loan Bánh 170 5 850 0.15 977.5 
Loan Bánh 350 5 1750 0.15 2012.5 
Nam Bánh 600 5 3000 0.15 3450 
Sao Thuốc 590 20 11800 11800 
Nam Cà fê 550 10 5500 0.02 5610 
Loan Cà fê 400 10 4000 0.02 5610 
Loan Thuốc 280 20 5600 5600 
B−ớc 1: Bấm vào ô bất kỳ trong vùng dữ liệu sau đó vào Data/Pivol Table Report , 
thấy xuất hiện cửa số PiVol Table Wizard Step 1 of 4 
Giáo trình Microsoft Excel Trung tâm tin học CIC 
 87A - Thợ Nhuộm Hà Nội 
Path: P.Q.Huy C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\Giao_trinh_Excel_nang_cao.doc 16
 B−ớc 2: Bấm Next thấy xuất hiện cửa sổ PiVol Table Wizard Step 2 of 4 
ở cửa sổ này ta chọn vùng dữ liệu cần phân tích (thông th−ờng máy tự chọn) -> bấm 
Next sẽ thấy xuất hiện cửa sổ PiVol Table Wizard Step 3 of 4 
Tại cửa sổ này kéo các tr−ờng vào vùng Column, Row, Data (nhìn ví dụ trên cửa sổ). 
Sau đó bấm kép chuột vào tr−ờng nằm ở vùng data sẽ xuất hiện cửa sổ 
Tại cửa sổ này chọn hàm sử dụng để 
phân tích dữ liệu sau đó bấm OK 
Bấm tiếp Next sẽ xuất hiện cửa sổ cho 
phép chọn vùng chứa dữ liệu sau khi phân 
tích: 
- New WorkSheet: nếu đ−a dữ liệu sang 
Sheet mới 
- Existing worksheet: Nếu để dữ liệu ở 
Sheet hiện hành. 
Bấm Finish để kết thúc 
Giáo trình Microsoft Excel Trung tâm tin học CIC 
 87A - Thợ Nhuộm Hà Nội 
Path: P.Q.Huy C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\Giao_trinh_Excel_nang_cao.doc 17
Ch−ơng 6 
Liên kết bảng tính: 
1. Dán liên kết 
- Gõ dữ liệu ở nhiều Sheet khác nhau 
- Gõ bảng tổng hợp vào một Sheet khác 
- Để chuột tại ô dữ liệu cần dán, Vào Edit/ Copy 
- Mở bảng tổng kết, bấm chuột tại vị trí cần dán 
- Vào Edit / Paste Special / Paste link 
Công dụng của dán liên kết: Khi ta thay đổi dữ liệu trong bảng dữ liệu thì bảng tổng 
hợp thay đổi theo 
2..Tổng hợp dữ liệu từ nhiều bảng khác nhau 
- Gõ các bảng tính cần tổng hợp ở nhiều Sheet khác nhau 
- Để chuột tại bảng tổng hợp 
- Vào Data/ Consolidate 
- Chọn hàm cần thực hiện ở Function 
- Bấm chuột vào các sheet cần tổng hợp, chọn vùng dữ liệu 
( Chọn bằng cách ta mở các Sheet dữ liệu , ta bấm chọn vùng dữ liệu cần tổng hợp rồi 
bấm chuột vào Add) 
- Bấm chọn mục Top Row nếu vùng dữ liệu ở các Sheet có cả tên tr−ờng 
- Bấm chọn Left Column nếu vùng dữ liệu ở các Sheet có cột đầu tiên bên trái là cột 
chứa các nội dung cần tổng hợp. 
- Chọn Create links to source data ( Khi dữ liệu của các bảng dữ liệu thay đổi thì dữ 
liệu trong bảng tổng hợp cũng thay đổi theo) 
- Bấm chọn OK 
Giáo trình Microsoft Excel Trung tâm tin học CIC 
 87A - Thợ Nhuộm Hà Nội 
Path: P.Q.Huy C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\Giao_trinh_Excel_nang_cao.doc 18
Ch−ơng 7 
Tạo biểu đồ 
Để tạo biều đồ chúng ta thực hiện các thao tác sau 
- Chọn dữ liệu muốn dùng để vẽ biểu đồ 
- Bấm chọn nút ChartWizard của thanh công cụ chuẩn 
- Hộp thoại Chart Wizard Step 1 of 4 hiện ra yêu cầu ta chọn họ biểu đồ và chọn loại 
trong họ biểu đồ vừa chọn 
Giáo trình Microsoft Excel Trung tâm tin học CIC 
 87A - Thợ Nhuộm Hà Nội 
Path: P.Q.Huy C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\Giao_trinh_Excel_nang_cao.doc 19
- Bấm nút Next, hộp hội thoại Chart Wizard Step 2 of 4 hiện ra, yêu cầu ta chọn số 
liệu đã vẽ trong nhóm biểu đồ là dòng hay cột của vùng dữ liệu đã chọn, dữ liệu để vẽ biểu 
đồ bắt đầu từ dòng hay cột thứ mấy trong vùng dữ liệu đã chọn. 
- Bấm nút Next, hộp thoại Chart Wizard Step 3 of 4 hiện ra 
Cho phép ta đặt thuộc tính cho biểu đồ trong các mục sau: 
* Title: - Chart title: Đặt tiêu đề cho biểu đồ 
Giáo trình Microsoft Excel Trung tâm tin học CIC 
 87A - Thợ Nhuộm Hà Nội 
Path: P.Q.Huy C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\Giao_trinh_Excel_nang_cao.doc 20
 - Catology (X) axis: đặt tiêu đề cho trục X 
 - Catology (Y) axis: đặt tiêu đề cho trục Y 
* Gridlines: ẩn, hiện đ−ờng l−ới 
* Data Labels: Hiện nhãn 
 - Show Value: hiện giá trị 
 - Show Percent: hiện phần trăm 
 - Show Label: hiện nhãn 
 - Show Label and percent: hiện cả nhãn và phần trăm 
- Bấm nút Next, hộp thoại Chart Wizard Step 4 of 4 hiện ra cho phép ta lựa chọn vị 
trí của đồ thị: 
+ As new Sheet :Vẽ đồ thị trên một Sheet mới 
+ As objec in :Vẽ đồ thị ngay trên Sheet hiện hành 
- Bấm Finish để kết thúc 
Giáo trình Microsoft Excel Trung tâm tin học CIC 
 87A - Thợ Nhuộm Hà Nội 
Path: P.Q.Huy C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\Giao_trinh_Excel_nang_cao.doc 21
Mục lục 
Ch−ơng 1: Các tính năng cao cấp của excel ................................................... 3 
1/ Đặt mật khẩu cho tập bảng tính ............................................................. 3 
2. Làm chủ những tính năng lập dàn của Excel (Outlining)...................... 3 
3. Thiết lập giá trị giới hạn cho ô ............................................................... 3 
Ch−ơng 2: Hàm.................................................................................................. 5 
I. Hàm thống kê một điều kiện ...................................................................... 5 
1. Hàm Sumif: ............................................................................................ 5 
2. Hàm Countif: ......................................................................................... 5 
II/ Các dạng vùng tiêu chuẩn ......................................................................... 6 
III/ Hàm thống kê nhiều điều kiện:.............................................................. 7 
1. Hàm Dsum: ......................................................................................... 7 
2. Hàm Dcount, Dmax, Dmin, Daverage .....cũng đ−ợc tính t−ơng tự....... 7 
IV/ Hàm tham chiếu....................................................................................... 8 
1. Hàm Match ............................................................................................ 8 
2. Hàm INDEX .......................................................................................... 8 
3. Ví dụ áp dụng các hàm MACTH() và INDEX ...................................... 9 
Ch−ơng 3: Lọc dữ liệu..................................................................................... 11 
1. Sử dụng Auto Filter:............................................................................. 11 
2. Sử dụng Advanced Filter:..................................................................... 12 
Ch−ơng 4: Sắp xếp dữ liệu và thống kê theo nhóm...................................... 13 
1. Sắp xếp thứ tự CSDL:........................................................................... 13 
2. Tính năng Subtotals.............................................................................. 13 
Ch−ơng 5: Hộp hội thoại Form và Phân tích dữ liệu................................... 15 
1/ Hộp hội thoại Form.............................................................................. 15 
2/ Sử dụng PiVol Table để phân tích bảng tính ....................................... 15 
Ch−ơng 6: Liên kết bảng tính: ....................................................................... 17 
1. Dán liên kết.......................................................................................... 17 
2..Tổng hợp dữ liệu từ nhiều bảng khác nhau.......................................... 17 
Ch−ơng 7: Tạo biểu đồ.................................................................................... 18