Bài giảng thực hành - Tin ứng dụng | Trường Đại Học Duy Tân

Bài thực hành Tin Ứng Dụng
TRƯỜNG ĐẠI HỌC DUY TÂN KHOA CÔNG NGHỆ THÔNG TIN BÀI GIẢNG THỰC HÀNH TIN ỨNG DỤNG
GVHD: NGUYỄN THỊ BÍCH HẠNH 08/2019 Khoa Công Nghệ Thông Tin 1
Bài thực hành Tin Ứng Dụng MỤC LỤC
PHẦN 1: MS EXCEL .......................................................................................................................... 4
CÁC HÀM TÌM KIẾM VÀ THAM CHIẾU ................................................................................... 4
Hàm VLOOKUP ...................................................................................................................... 4
Hàm HLOOKUP ...................................................................................................................... 4
BÀI 1 ........................................................................................................................................ 4
CÁC HÀM TÌM KIẾM VÀ THAM CHIẾU (tt) ............................................................................. 5
Hàm MATCH ........................................................................................................................... 5
Hàm INDEX ............................................................................................................................. 5
BÀI 2 ........................................................................................................................................ 6
CÁC HÀM CƠ SỞ DỮ LIỆU (CSDL) ............................................................................................ 7
COUNTIF ................................................................................................................................. 7
SUMIF ...................................................................................................................................... 7
DSUM ....................................................................................................................................... 7
DMIN ....................................................................................................................................... 7
DCOUNT ................................................................................................................................. 8
DAVERAGE ............................................................................................................................ 8
BÀI 3 ........................................................................................................................................ 9
BÀI 4 ...................................................................................................................................... 10
BÀI 5 ...................................................................................................................................... 12
BÀI 6 ...................................................................................................................................... 13
BÀI 7 ...................................................................................................................................... 15
Sắp xếp dữ liệu ....................................................................................................................... 15
Trích lọc dữ liệu ..................................................................................................................... 15
BÀI 8 ...................................................................................................................................... 17
BÀI 9 ...................................................................................................................................... 20
BÀI 10 .................................................................................................................................... 21
BÀI 11 .................................................................................................................................... 23
TỔNG HỢP, PHÂN TÍCH VÀ THỐNG KÊ SỐ LIỆU ................................................................ 23
1. Tổng hợp số liệu với SubTotal ........................................................................................... 23
BÀI 12 .................................................................................................................................... 24
BÀI 13 .................................................................................................................................... 25
2. Tổng hợp số liệu với Consolidate ....................................................................................... 26
BÀI 14 .................................................................................................................................... 27
3. Tổng hợp, phân tích số liệu với Pivottable ......................................................................... 29
BÀI 15 .................................................................................................................................... 33
BÀI 16 .................................................................................................................................... 36
TÌM KIẾM MỤC TIÊU VỚI GOALSEEK VÀ SOLVER ............................................................ 38
Tìm kiếm mục tiêu với GoalSeek ........................................................................................... 38
BÀI THỰC HÀNH MS ACCESS ...................................................................................................... 42
I. TABLE ........................................................................................................................................ 42
BÀI TẬP THỰC HÀNH 01 ................................................................................................... 55
BÀI THỰC HÀNH SỐ 02 ..................................................................................................... 58
BÀI THỰC HÀNH SỐ 03 ..................................................................................................... 63
II. QUERY ...................................................................................................................................... 65
BÀI THỰC HÀNH SỐ 04 ..................................................................................................... 65
III. CÁC PHÉP TOÁN SỐ HỌC, SO SÁNH VÀ LOGIC ............................................................ 67
BÀI THỰC HÀNH SỐ 05 ..................................................................................................... 68
BÀI THỰC HÀNH SỐ 06 ..................................................................................................... 70 Khoa Công Nghệ Thông Tin 2
Bài thực hành Tin Ứng Dụng
BÀI THỰC HÀNH SỐ 07 ..................................................................................................... 72
BÀI TẬP THỰC HÀNH 08 ................................................................................................... 75
BÀI THỰC HÀNH SỐ 09 ..................................................................................................... 78
BÀI THỰC HÀNH SỐ 10 ..................................................................................................... 81
BÀI THỰC HÀNH SỐ 11 ..................................................................................................... 84
BÀI THỰC HÀNH SỐ 12 ..................................................................................................... 87
BÀI THỰC HÀNH SỐ 13 ..................................................................................................... 90
CẬP NHẬT 2018_2019 ................................................................................................................. 96 Khoa Công Nghệ Thông Tin 3
Bài thực hành Tin Ứng Dụng PHẦN 1: MS EXCEL
CÁC HÀM TÌM KIẾM VÀ THAM CHIẾU Hàm VLOOKUP
 Cú pháp: VLOOKUP(giá trị tìm, vùng tìm, cột trả về giá trị, kiểu tìm)
 Ý nghĩa: tiến hành tìm kiếm “giá trị tìm” trên cột đầu tiên của “vùng tìm” và trả lại giá
trị của ô cùng hàng ở “cột trả về giá trị”  Kiểu tìm:
- False (hoặc 0): các giá trị không cần sắp xếp tăng dần
- True (hoặc 1): các giá trị được sắp xếp tăng dần Hàm HLOOKUP
 Cú pháp: HLOOKUP(giá trị tìm, vùng tìm, hàng trả về giá trị, kiểu tìm)
 Ý nghĩa: tiến hành tìm kiếm “giá trị tìm” trên hàng đầu tiên của “vùng tìm” và trả lại
giá trị của ô cùng cột ở “hàng trả về giá trị”  Kiểu tìm:
- False (hoặc 0): các giá trị không cần sắp xếp tăng dần
- True (hoặc 1): các giá trị được sắp xếp tăng dần BÀI 1
BẢNG 1 – BẢNG KÊ NHẬP HÀNG STT MÃ TÊN NGÀY SỐ
THÀNH THUẾ CHIẾT TỔNG HÀNG HÀNG NHẬP LƯỢNG TIỀN
VAT KHẤU TIỀN IP308GB 20/10/2017 70 IP416GB 15/11/2017 80 IP308GB 10/12/2017 100 IP516GB 18/11/2017 85 IP432GB 11/10/2017 60 IP416GB 19/11/2017 50 IP308GB 25/12/2017 90 IP316GB 17/11/2017 80
BẢNG 2: BẢNG GIÁ NHẬP HÀNG MÃ HÀNG TÊN ĐƠN GIÁ (USD) THUẾ HÀNG 08GB 16GB 32GB VAT IP3 Iphone 3 300 350 400 10% IP4 Iphone 4 400 450 500 12% IP5 Iphone 5 500 550 600 15% Khoa Công Nghệ Thông Tin 4
Bài thực hành Tin Ứng Dụng Yêu cầu:
1. Xác định tên hàng dựa vào 3 ký tự đầu tiên của mã hàng và tra trong Bảng 2.
2. Thành tiền = số lượng * đơn giá. Đơn giá được tra trong Bảng 2, dựa vào mã hàng và
dung lượng bộ nhớ của sản phẩm (dung lượng bộ nhớ là 4 ký tự cuối của mã hàng).
3. Thuế VAT= thành tiền * thuế VAT (%) (thuế VAT được tra trong Bảng 2).
4. Chiết khấu = 10% * thành tiền (chỉ áp dụng cho những mặt hàng có số lượng>50).
5. Tổng tiền = Thành tiền + Thuế VAT – Chiết khấu.
CÁC HÀM TÌM KIẾM VÀ THAM CHIẾU (tt) Hàm MATCH
 Cú pháp: MATCH(Trị dò tìm, vùng tìm, 0)
 Ý nghĩa: xác định vị trí tìm thấy “Trị dò tìm” trong vùng dò tìm. Vùng dò tìm phải là bảng 1 dòng hoặc 1 cột
Ví dụ: Để biết được ĐN đứng ở vị trí thứ mấy trong vùng từ A2:D2, ta dùng hàm MATCH =MATCH(“ĐN”,A2:D2)2
 Hàm MATCH thường được kết hợp với hàm INDEX để xác định tọa độ hàng cột cần
lấy dữ liệu trong 1 bảng Hàm INDEX
 Cú pháp: INDEX(Vùng tìm, hàng X, cột Y)
 Ý nghĩa: trả về dữ liệu của ô tại hàng X cột Y Ví dụ:
Để lấy dữ liệu ttrong ô C3 tương ứng với nơi đi ĐN, nơi đến SG, sử dụng hàm INDEX($B$3:$D$5,1,2)600 Khoa Công Nghệ Thông Tin 5
Bài thực hành Tin Ứng Dụng BÀI 2
DANH SÁCH ĐĂNG KÝ THUÊ PHÒNG Số Số
Tiền Tiền Giảm Thanh STT Tên khách Ngày đến Ngày đi Mã DV Tên dịch vụ phòng ngày dịch vụ phòng giá toán 1 Thanh Thanh 103 03/01/2017 05/01/2017 LA 2 Bình Bình 106 08/03/2017 11/03/2017 FB 3 An An 307 16/04/2017 16/04/2017 FB 4 Tú Tú 207 10/04/2017 21/04/2017 BU 5 Hà Hà 401 09/05/2017 18/05/2017 FB 6 Minh Minh 204 07/05/2017 12/05/2017 LA 7 Nhi Nhi 110 09/05/2017 12/05/2017 BU 8 Loan Loan 308 07/05/2017 09/05/2017 CR 9 Hùng Hùng 205 06/05/2017 07/05/2017 BU 10 Trinh Trinh 210 09/05/2017 10/05/2017 CR
Biểu giá phòng (Bảng 1) Tầng 1 2 3 4 Giá 600,000 500,000 400,000 300,000
Biểu giá dịch vụ (Bảng 2) Mã DV 1 2 3 4 BU 50,000 120,000 80,000 20,000 CR 100,000 50,000 50,000 70,000 FB 70,000 90,000 70,000 90,000 LA 30,000 60,000 15,000 40,000
Bảng tham khảo dịch vụ (Bảng 3)
Mã DV Tên dịch vụ BU Business center CR Car Rental FB Food & Beverage LA Laundry Yêu cầu:
1. Số ngày ở = Ngày đi – Ngày đến +1
2. Tên dịch vụ: Dựa vào mã dịch vụ và dò tìm trong Bảng 3
3. Tiền dịch vụ = Số ngày ở * Giá dịch vụ (dựa vào mã dịch vụ và ký tự đầu tiên của số
phòng, dò tìm trong Bảng 2; yêu cầu: Dùng hàm INDEX, MATCH).
4. Tiền phòng = Số ngày ở * Giá phòng (dựa vào ký tự đầu tiên của số phòng và dò tìm trong Bảng 1)
5. Giảm giá: giảm 10% tiền phòng cho khách có số ngày ở >=3
6. Thanh toán = Tiền phòng + Tiền dịch vụ - Giảm giá. Khoa Công Nghệ Thông Tin 6
Bài thực hành Tin Ứng Dụng
CÁC HÀM CƠ SỞ DỮ LIỆU (CSDL)  Mục đích:
 Ôn tập lại cách định dạng dữ liệu trong bảng tính.
 Sử dụng các hàm cơ bản.
 Các hàm cơ sở dữ liệu COUNTIF
 Cú pháp: COUNTIF(Vùng điều kiện, điều kiện)
 Ý nghĩa: Đếm số ô trong “vùng điều kiện” thỏa mãn “điều kiện” nào đó
 Chú ý: Điều kiện nằm trong dấu ngoặc kép (“ ”) Ví dụ: SUMIF
 Cú pháp: SUMIF(Vùng chứa trị dò tìm, ĐK, [Vùng lấy số liệu tính tổng])
 Ý nghĩa: tính tổng có điều kiện. Chỉ những ô nào thảo mãn điều kiện đặt ra thì mới được tính tổng DSUM
 Cú pháp: DSUM(Database, field, Criteria)
 Ý nghĩa: tính tổng tại field có điều kiện thỏa mãn điều kiện Criteria trong danh sách Database DMIN
 Cú pháp: DMIN(Database, field, Criteria)
 Ý nghĩa: tìm giá trị nhỏ nhất tại field có điều kiện thỏa mãn điều kiện Criteria trong danh sách Database
 Cú pháp: DMAX(Database, field, Criteria)
 Ý nghĩa: tìm giá trị lớn nhất tại field có điều kiện thỏa mãn điều kiện Criteria trong danh sách Database Khoa Công Nghệ Thông Tin 7
Bài thực hành Tin Ứng Dụng DCOUNT
 Cú pháp: DCOUNT(Database, field, Criteria)
 Ý nghĩa: Đếm số ô có giá trị tại field có điều kiện thỏa mãn điều kiện Criteria trong danh sách Database DAVERAGE
 Cú pháp: DAVERAGE(Database, field, Criteria)
 Ý nghĩa: tìm giá trị trung bình tại field có điều kiện thỏa mãn điều kiện Criteria trong danh sách Database
 Các bước thực hiện:
- Bước 1: Thiết lập vùng điều kiện. Vùng điều kiện có tối thiểu 2 dòng. Dòng đầu chứa
tiêu đề (field), dòng thứ hai chứa điều kiện.
Ví dụ 1: Lọc ra danh sách các mặt hàng là Gạch men Tên hàng Gạch men
Ví dụ 2: Lọc ra danh sách các mặt hàng là gạch men và có đơn giá trên 15000 Tên hàng Đơn giá Gạch men >15000
Ví dụ 3: Lọc ra danh sách các mặt hàng có đơn vị tính là viên hoặc có số lượng trên 80. ĐVT SL Viên >80
- Bước 2: Thiết lập công thức Khoa Công Nghệ Thông Tin 8
Bài thực hành Tin Ứng Dụng BÀI 3
BẢNG CHI TIẾT HÀNG XUẤT TỪ NGÀY 01/02/2018 ĐẾN 28/02/2018 S Tên T Ngày Đơn Số Mã tỉnh Mã hàng hàng Doanh thu T hóa hóa ĐVT giá lượng 1 02/02/2018 SG S12 425 2 02/02/2018 ĐN X50 340 3 06/02/2018 HN S10 440 4 07/02/2018 ĐN GHN 650 5 08/02/2018 HN GTQ 725 6 15/02/2018 HN S12 430 7 06/02/2018 SG X30 225 TỔNG CỘNG DANH MỤC HÀNG HOÁ
Mã hàng hóa Tên hàng hóa ĐVT Đơn giá S12 Sắt 12 Tấn 120,000 X50 Xi Măng P500 Tấn 850,000 S10 Sắt 10 Tấn 100,000 GHN Gạch hoa m2 45,000 GTQ Gạch men TQ m2 40,000 X30 Xi Măng P300 Tấn 700,000 VET Ván ép Tấn 35,000 Yêu cầu:
1. Dựa vào Mã hàng hóa và bảng Danh mục hàng hoá để xác định Tên hàng hoá, ĐVT, và Đơn giá
2. Tính doanh thu = đơn giá * số lượng
3. Tính tổng cho các cột số lượng, đơn giá, và doanh thu 4. Lập bảng thống kê. THỐNG KÊ
MÃ TỈNH TỔNG D.THU SG ĐN HN Khoa Công Nghệ Thông Tin 9
Bài thực hành Tin Ứng Dụng BÀI 4
 Mục đích: Sử dụng các hàm thống kê đơn giản, các hàm CSDL DSUM, DMAX, DMIN, DAVERAGE, DCOUNT, DCOUNTA..
Nhập CSDL sau vào bảng tính Khách Sạn Bình Minh. Bảng theo dõi doanh thu Đơn vị tính: 1000 đ . S Ngày Ngày Lưu Loại Đơn Tiền T Tên khách Đến Đi Trú Phòng Giá Phòng T 1 Hồng Hồng 14/05/2018 22/05/2018 TR-A 2 Bảo Bảo 16/05/2018 20/06/2018 L1-B 3 Cách Cách 30/05/2018 11/06/2018 L2-C 4 Thanh Thanh 02/06/2018 07/06/2018 L1-A 5 Bội Bội 05/06/2018 28/06/2018 TR-C 6 Minh Minh 09/06/2018 15/06/2018 L2-A 7 Văn Văn 12/06/2018 07/07/2018 TR-B 8 Ngọc Ngọc 21/06/2018 06/07/2018 L1-B 9 Liên Liên 25/06/2018 04/07/2018 L1-A 10 Muội Muội 28/06/2018 05/07/2018 L1-C
Bảng Giá Tiền Phòng Hạng L1 L2 TR A 150 125 105 B 120 95 85 C 100 75 65 Yêu cầu:
1. Số ngày lưu trú = ngày đi - ngày đến+1.
2. Tính đơn giá cho mỗi loại phòng theo bảng giá tiền phòng
Gợi ý: Dùng hàm INDEX kết hợp với hàm MATCH để đối chiếu với bảng Giá tiền phòng
mà lấy đơn giá cho từng loại phòng.
3. Tính tiền phòng biết rằng: tiền phòng bằng lưu trú nhân với đơn giá, trong đó:
- Nếu số ngày lưu trú lớn 20 ngày thì giảm 10%.
- Nếu số ngày lưu trú từ 10 đến 20 ngày thì giảm 5%.
- Nếu số ngày lưu trú nhỏ hơn hoặc bằng 10 thì không giảm. (theo dõi trang kế tiếp) Khoa Công Nghệ Thông Tin 10
Bài thực hành Tin Ứng Dụng
4. Sử dụng các hàm CSDL, Thực hiện các thống kê sau STT YÊU CẦU KẾT QUẢ 1
Tổng số ngày lưu trú của các khách hàng có ngày đến trong tháng 5 ? 2
Tổng số ngày lưu trú của các khách hàng có ngày đi trong tháng 6 ? 3
Tổng số tiền mà phòng Hạng A đã cho thuê ? 4
Tổng số ngày mà khách hàng có ngày đến trong tháng 6 và ở hạng B đã thuê ? 5
Tính tiền phòng lớn nhất mà phòng L1 thu được ? 6
Tính số ngày ở trung bình của loại phòng L2 ? 7
Đếm xem có bao nhiêu lần khách hàng đã thuê phòng TR hoặc L1 ? 8
Tính số ngày ở ít nhất của phòng Hạng A ? 9
Số ngày lưu trú trung bình của phòng hạng A hoặc B ?
5. Thực hiện trang trí và lưu bảng tính. Khoa Công Nghệ Thông Tin 11
Bài thực hành Tin Ứng Dụng BÀI 5 Tên Mã số Thành Ngày đến Ngày đi
T.phòng Giảm giá Loại phòng tiền tiền Sao 001A 12/12/2018 15/12/2018 USD Anh 104B 18/02/2018 25/02/2018 VND Không 101C 25/03/2018 28/03/2018 FR Về 201A 30/04/2018 15/05/2018 VND Chơi 204C 15/05/2018 20/05/2018 VND Thôn 202A 11/06/2018 20/06/2018 VND Vỹ 101B 21/07/2018 21/07/2018 VND Nhìn 004A 30/08/2018 30/08/2018 YEN Nắng 002C 03/09/2018 03/09/2018 USD Hàng 002B 12/09/2018 15/09/2018 VND Cau 103A 21/09/2018 30/09/2018 VND Nắng 202C 01/10/2018 15/10/2018 VND Mới 003C 11/10/2018 15/10/2018 YEN Lên 003B 17/10/2018 24/10/2018 USD BẢNG GIÁ PHÒNG VND BẢNG TỶ GIÁ A B C USD FR YEN 0 720,000 590,000 670,000 23,000 4,018 20,000 1 670,000 520,000 590,000 2 590,000 490,000 520,000 Yêu cầu:
1. Tiền phòng = (Ngày đi – Ngày đến +1)*Giá phòng
2. Giảm giá=Tiền phòng * Giảm giá (nếu thời gian thuê <5 ngày thì không giảm, nếu
thời gian thuê <10 ngày thì giảm 10%, các trường hợp còn lại giảm 15%).
3. Nếu loại tiền VND thì Thành tiền=Tiền phòng – Giảm giá. Ngược lại, thành tiền=(tiền
phòng-giảm giá)/loại tiền, làm tròn đến hàng nghìn.
4. Trang trí định dạng bảng tính Khoa Công Nghệ Thông Tin 12
Bài thực hành Tin Ứng Dụng BÀI 6
 Mục đích: Sử dụng hàm CSDL.
 Mở một Workbook mới và lập bảng tính sau:
DANH SÁCH LƯƠNG DOANH NGHIỆP THÁNG 11/2018 Maso Họ tên C.vụ
Phái TĐVH LGCB N.công P.cấp
Thưởng T.lương AFD8 Thu Giang 4,600,000 23 CFC1 Lê Hương 3,100,000 24 CMT5 Như Thông 3,300,000 23 BMC7 Minh Hoàng 4,300,000 25 AMD7 Thanh Quang 3,200,000 24 CFT3 Anh Đào 3,200,000 22 CFC6 Hao Lài 3,600,000 26 CFT4 Thanh Thuý 3,500,000 23 BMD5 Quang Tùng 3,900,000 20 CMC9 Chí Công 3,800,000 23 MS1 C.VỤ MS2 PHÁI MS3 TĐVH A TP F Nữ D Đại Học B PP M Nam C Cao Đẳng C NV T Trung Cấp C.VỤ TP PP NV PCCV 600,000 450,000 300,000
Giải thích: MASO cho trên gồm 4 kí tự:
- Kí tự đầu là chức vụ.
- Kí tự thứ hai là PHÁI.
- Kí tự thứ ba là Trình độ VH.
- Kí tự thứ tư là Năm Công Tác. Yêu cầu:
- Căn cứ vào MASO và dò tìm trong các bảng điền thông tin vào các cột PHÁI, C.VỤ, TĐVH.
- Tính phụ cấp (P.CẤP) = PCCV+Năm công tác nhân 100,000. Với PCCV được cho ở bảng phụ trên. - Tính thưởng, biết:
- N.CÔNG>=25 thì thưởng 1,500,000
- 23<=N.CÔNG<25 thì thưởng 1,000,000 Khoa Công Nghệ Thông Tin 13
Bài thực hành Tin Ứng Dụng
- Các trường hợp khác không được thưởng
- Tính T.LƯƠNG = (LGCB * 1.1 * N.CÔNG)/26 + P.CẤP + THƯỞNG.
- Tính tổng cho các cột p.cẤp, thưỞng, t.lương.
- Tính tổng p.cẤp, thưởng, t.lương theo phải và lưu theo mẫu sau. PHÁI P. cấp Thưởng T. lương Nữ Nam Tổng Cộng Gợi ý:
 Tính tổng P.cấp theo phái là nữ : =SUMIF(Các Giá Trị Cột PHÁI,"=Nữ", Các Giá
Trị Cột P.CẤp)
 Tính tổng P.cấp theo phái là nam : =SUMIF(Các Giá Trị Cột PHÁI,"=Nam", Các
Giá Trị Cột P.CẤp)  Tính tổng th ở
ư ng, T.lương tương tự.
 Tính tổng cộng các cột p.cấp, thưởng, T.lương ở bảng tính trên. So sánh các giá trị
này với các giá trị tổng cộng của bảng tính chính.
- Đếm có bao nhiêu người có T.LƯƠNG >=800000.
- Đếm có bao nhiêu người có C.VỤ là NV.
Và lưu vào bảng tính phụ sau.
Có bao nhiêu người có T.Lương lớn hơn 800000 ?
Có bao nhiêu người C.VỤ là NV ?
- Trang trí và lưu bảng tính. Khoa Công Nghệ Thông Tin 14
Bài thực hành Tin Ứng Dụng BÀI 7
 Mục đích: Các thao tác trên danh sách dữ liệu: sort, filter. Sắp xếp dữ liệu
Chức năng sắp xếp dữ liệu nhằm mục đích tổ chức lại bảng dữ liệu theo một thứ tự nào đó.
Khi thực hiện chức năng này, bảng dữ liệu sẽ bị thay đổi nhưng các công thức bên trong sẽ
được tự động thay đổi cho phù hợp. Có hai loại thứ tự:
- Theo chiều tăng: đối với trường dữ liệu text (A to Z), đối với trường dữ liệu số là smallest to Largest.
- Theo chiều giảm: đối với trường dữ liệu text (Z to A), đối với trường dữ liệu số là Largest to smallest.(Descending).
Việc sắp xếp thường tiến hành trong Excel như sau:
 Chọn vùng dữ liệu cần sắp xếp hoặc đặt con trỏ trong vùng dữ liệu.
 Gọi Menu Data  Sort, xuất
hiện hộp thoại Sort như sau:
 Tại ComboBox Sort by: Từ
danh sách đổ xuống, kích chọn
một trường để làm khoá sắp xếp.
 Tại 2 ComboBox Then by: Bạn
có thể chọn trường để làm
khoá thứ cấp (có nghĩa là nếu
trường ở Sort by có giá trị
trùng nhau thì sẽ tiếp tục sắp
xếp theo các trường khoá thứ cấp).
 Trong các trường khoá: Tại combobox Order chọn A to Z hoặc Z to A để sắp xếp tăng
hoặc giảm dần cho trường dữ liệu text. Smallest to Largest hoặc Largest to smallest
để sắp xếp tăng hoặc giảm dần cho trường dữ liệu số.
 Kích chọn ô My data Headers để lấy dòng đầu tiên làm trường sắp xếp.
Trích lọc dữ liệu
Trích lọc dữ liệu nhằm mục đích cho phép xem xét hoặc lấy thông tin từ bảng dữ liệu mà
thoả mãn các điều kiện nào đó. Trong Excel có hai phương pháp, đó là lọc tự động và lọc nâng cao. Có 2 phương pháp:
 Lọc tự động (AutoFilter)
 Lọc nâng cao (Advanced Filter) Khoa Công Nghệ Thông Tin 15
Bài thực hành Tin Ứng Dụng  Lọc tự động
- B1: nháy chuột chọn 1 ô trong vùng có dữ liệu cần lọc
- B2: vào menu Data/ Filter, ô tên trường có đầu mũi tên thả xuống của hộp danh sách
All: hiện lại mọi bản ghi  Lọc nâng cao
- Bước 1: Thiết lập vùng điều kiện. Vùng điều kiện có tối thiểu 2 dòng. Dòng đầu chứa
tiêu đề (field), dòng thứ hai chứa điều kiện.
Ví dụ 1: Lọc ra danh sách các mặt hàng là gạch men Tên hàng Gạch men
Ví dụ 2: Lọc ra danh sách các mặt hàng là gạch men và có đơn giá trên 15000 Tên hàng Đơn giá Gạch men >15000 Khoa Công Nghệ Thông Tin 16
Bài thực hành Tin Ứng Dụng
Ví dụ 3: Lọc ra danh sách các mặt hàng có đơn vị tính là viên hoặc có số lượng trên 80. ĐVT SL Viên >80 - Bước 2:
 Chọn vùng dữ liệu cần lọc thông tin
 Kích chọn menu Data/ Filter/ Advanced
 Mục Action có 2 tùy chọn:
 Filter the list, in place: lọc danh sách và đặt tại chỗ
 Copy to another location: copy dữ liệu sau
khi trích lọc sang 1 vùng khác  List range:
 Chọn vùng dữ liệu sẽ lọc
 Hoặc nhập tên vùng của danh sách dữ liệu đã đặt trước đó  Criteria range
 Vùng điều kiện để so sánh giá trị thỏa mãn trong quá trình lọc  Copy to:
 Địa chỉ ô đầu tiên của 1 vùng bảng tính sẽ chứa dữ liệu sau khi lọc BÀI 8
 Mở một Workbook mới và nhập bảng tính sau:
DANH SÁCH HỌC VIÊN ĐƯỢC CẤP HỌC BỔNG - NĂM HỌC 2017-2018 Stt Họ và tên Ngày sinh Nơi sinh
MAĐ ĐTB Học bổng Tuổi 1 Thu Giang 15/05/1997 Đà Nẵng A01 2 Như Thông 13/02/1998 Đà Nẵng B2018 3 Lê Hương 11/11/1998 Quảng Nam C04
4 Minh Hoàng 12/10/1997 TT-Huế A2018 5 Thu Hiền 01/04/1999 Quảng Nam C02 6 Anh Đào 18/02/1998 Đà Nẵng B01 7 Hoa Lài 01/01/1999 Quảng Nam B05
8 Thanh Thuý 09/09/1997 Đà Nẵng A02
9 Quang Tùng 10/10/1999 Quảng Nam C01 10 Chí Công 24/09/1998 TT-Huế B02 Khoa Công Nghệ Thông Tin 17
Bài thực hành Tin Ứng Dụng BẢNG ĐIỂM Mã điểm Cơ sở C.môn N.ngữ C01 10.0 9.0 9.0 C04 7.0 8.0 10.0 A2018 2.0 5.5 6.5 B05 5.5 7.5 5.5 B01 9.0 9.5 9.5 B2018 8.5 10.0 8.0 A02 9.0 3.5 9.5 B02 6.0 8.5 10.0 A01 4.5 7.0 3.0 C02 7.0 6.0 8.0  Yêu cầu:
Căn cứ vào MAĐ và Bảng Điểm để tính điểm trung bình (ĐTB).
 Để tính điểm trung bình cần phải biết 3 cột điểm, do đó sử dụng hàm AVERAGE với 3
lần dùng VLOOKUP, mỗi lần chỉ thay đổi cột trả giá trị:
= AVERAGE (VLOOKUP (MAĐ, Bảng điểm, 2, 0), VLOOKUP (..., ..., 3, 0), VLOOKUP (..., ..., 4, 0))
Tính học bổng (H.BỔNG) theo điều kiện sau:
Nếu ĐTB >=9 thì h.bỔng là 5,000,000.
Nếu ĐTB >=8 thì h.bỔng là 3,000,000.
Nếu ĐTB >=7 thì h.bỔng là 2,000,000.
Ngoài ra không có học bổng.
Trích ra bảng tính khác và lưu vào Sheet2 với những điều kiện sau:
Những học viên có mức học bổng 240000.
Những học viên có mức học bổng 180000.
Những học viên có mức học bổng 120000.
Những học viên không có học bổng.
 Lập bảng điều kiện lọc:
Các vùng điều Học bổng Học bổng Học bổng Học bổng
kiện lọc về học 5,000,000 3,000,000 2,000,000 0 bổng
 Để trích lọc ra bảng tínhnằm ở Sheet khác: Kích chuột vào ô trống của Sheet sẽ trích lọc
đến, kích chọn Menu Data  Filter  Advancel Filter, xuẤt hiện hộp thoại sau: Địa chỉ vùng dữ liệu cần trích lọc Địa chỉ bảng điều kiện trích lọc Địa chỉ vùng chứa dữ liệu khi lọc Khoa Công Nghệ Thông Tin 18
Bài thực hành Tin Ứng Dụng
Trích ra bảng tính khác và lưu vào Sheet3 với những điều kiện sau: Từ 20 đến 23. 24 hoặc 25 tuổi. Trên 25 tuổi.
Các vùng điều kiện TUỔI TUỔI TUỔI TUỔI lọc về tuổi >=20 <=23 24 >25 25
Thực hiện thao tác sắp xếp theo thứ tự Họ Tên, NG.Sinh, ĐTB. Sau mỗi lần sắp xếp hãy quan sát sự thay đổi. Khoa Công Nghệ Thông Tin 19
Bài thực hành Tin Ứng Dụng BÀI 9
 Mục đích: Vẽ biểu đồ.
 Mở một Workbook mới và lập bảng tính sau: A B C D 1 SỐ LIỆU VỀ DÂN SỐ 2 ĐVT: triệu người
3 T.PHỐ NĂM 2017 NĂM 2018 TĐỘ TĂNG GIẢM/ NĂM 4 CKT 3.75 4.16 5 VKT 2.15 2.45 6 VTV 1.55 1.75 Yêu cầu:
Câu 1: TĐỘ TĂNG GIẢM/ NĂM = (NĂM 2018 - NĂM 2017) / NĂM 2017
Câu 2: Mô tả tổng quát dân số của các thành phố qua 2 năm 2017 và 2018
Câu 3: Mô tả tốc độ tăng giảm dân số qua 2 năm
Câu 4. Trang trí và lưu bảng tính Khoa Công Nghệ Thông Tin 20