Ban hành kèm theo Quyết định số: 01 /QĐ-CĐN ngày 04 tháng 01 năm 201 6 của Hiệu trưởng trường Cao đẳng nghề tỉnh BR - VT
Bà Rịa – Vũng Tàu, năm 2016
TUYÊN BỐ BẢN QUYỀN
Tài liệu này thuộc loại sách giáo trình nên các nguồn thông tin có thể được phép dùng nguyên bản hoặc trích dùng cho các mục đích về đào tạo và tham khảo.
Mọi mục đích khác mang tính lệch lạc hoặc sử dụng với mục đích kinh doanh thiếu lành mạnh sẽ bị nghiêm cấm.
MỤC LỤC
- LỜI GIỚI THIỆU
- BÀI 1: SỬ DỤNG MỘT SỐ HÀM TOÁN HỌC
- 1. Hàm Roundup
- 1. Hàm Rounddown.........................................................................................
- 1. Hàm Sumifs
- 1. Hàm Sumproduct.........................................................................................
- 1. Hàm SumQ
- 1. Hàm Trunc...................................................................................................
- BÀI 2: SỬ DỤNG MỘT SỐ HÀM THỐNG KÊ
- 1. Hàm Countblank
- 1. Hàm Countifs
- 1. Hàm AverageA............................................................................................
- 1. Hàm AverageIF
- 1. Hàm AverageIFS
- 1. Hàm MaxA
- 1. Hàm MinA...................................................................................................
- 1. Hàm Small
- 1. Hàm Large
- BÀI 3: SỬ DỤNG MỘT SỐ HÀM TÌM KIẾM
- 1. Hàm Choose
- 1. Hàm Index
- Dạng mảng
- Biểu mẫu tham chiếu
- 1. Hàm Match
- BÀI 4: SỬ DỤNG HÀM TÍNH KHẤU HAO TÀI SẢN
- 1. Hàm DB.......................................................................................................
- 1. Hàm DDB
- 1. Hàm SLN.....................................................................................................
- BÀI 5: SỬ DỤNG HÀM TÍNH LÃI SUẤT
- 1. Hàm PMT[]:
- 1. Hàm NPER[]:
- 1. Hàm FV[]:
- 4àm PV[]:
- 1. Hàm RATE[]:
- BÀI 6: SỬ DỤNG SUBTOTAL TRONG CƠ SỞ DỮ LIỆU
- 1. Đặt vấn đề....................................................................................................
- 1. Tạo SubTotal
- 1. Xóa SubTotal...............................................................................................
- 1. Ví dụ
- BÀI 7: SỬ DỤNG PIVOT TABLE TRONG CƠ SỞ DỮ LIỆU
- 1. Đặt vấn đề....................................................................................................
- 1. Tạo Pivot Table
- 1. Tạo biểu đồ từ Pivot Table
- 1. Ví dụ
- BÀI 8: SỬ DỤNG CONSOLIDATE TRONG CƠ SỞ DỮ LIỆU
- 1. Đặt vấn đề....................................................................................................
- 1. Tạo Consolidate...........................................................................................
- 1. Chỉnh sửa Consolidate
- 1. Ví dụ
- TẾ BÀI 09: ỨNG DỤNG TÍNH NĂNG GOAL SEEK ĐỂ GIẢI BÀI TOÁN KINH
- 1. Khái niệm Goal seek
- 1. Cách dùng Goal seek
- 1. Thiết lập bài toán Tìm giá trị thanh toán của khoản tiền trong tương lai
- 1. Các bước tạo Goal seek để giải bài toán
- 1. Các dạng thay đổi bài toán trên
- BÀI 15: TẠO HÀM MỚI TRONG EXCEL BẰNG VBA
- 1. Khái niệm Hàm trong Excel
- 1. Các bước để tạo hàm mới trong Excel
- 1. Ví dụ
CHƢƠNG TRÌNH MÔ ĐUN
EXCEL NÂNG CAO
Mã số của mô đun: MĐ 12; Thời gian của mô đun: 120 giờ; [Lý thuyết: 45 giờ, Thực hành: 75 giờ]
I. VỊ TRÍ, TÍNH CHẤT CỦA MÔ ĐUN:
Là mô đun bổ trợ các kiến thức chuyên sâu về phần mềm excel của chương trình đào tạo Cao đẳng nghề Công nghệ thông tin [ứng dụng phần mềm], mô đun được học sau Tin học văn phòng. II. MỤC TIÊU CỦA MÔ ĐUN: Sau khi học xong mô đun này, học sinh – sinh viên có năng lực:
- Trình bày được công dụng, cú pháp và vận dụng được các hàm Toán học: Roundup, Roundown, Sumifs, Sumproduct, Sumq, Trunc , hàm thống kê: Countblank, Countifs, AverageA, AverageIF, AverageIFS, MaxA, MinA, Small, Large , hàm tìm kiếm: Choose, Index, Match.
- Trình bày công dụng, phương pháp của Conditional Formatting, Data Validation
- Trình bày được công dụng, phương pháp sử dụng công thức mảng.
- Trình bày được phương pháp Tính tổng theo nhóm subtotals; Lập bảng PivotTable; Kỹ thuật liên kết các bảng tính Consolidate;
- Trình bày được cách chia sẻ Excel với Word và Access;
- Trình bày được các khái niệm, cách tạo và sử dụng Macro
- Trình bày được phương pháp Kỹ thuật lập trình VBA trên Excel để tạo ra các hàm người dùng.
- Giải các bài toán vận dụng các hàm Toán học, thống kê, Tìm kiếm trên.
- Tạo được các bảng thống kê sử dụng Subtotal, Pivot Table.
- Liên kết các bảng tính đã có thành 1 bảng kết quả theo yêu cầu;
- Chia sẻ được Excel với Word và Access;
- Tạo được và sử dụng thành thạo macro.
BÀI 1: ĐỊNH DẠNG DỮ LIỆU THEO ĐIỀU KIỆN Mã bài: 01 Giới thiệu Khi xây dựng bảng tính trong Excel, một số trường hợp yêu cầu nhập liệu là giá trị cụ thể hoặc trong danh sách sẵn có. Làm thế nào bạn có thể kiểm soát được dữ liệu đầu vào đó theo đúng yêu cầu? Làm thế nào Excel tự thông báo lỗi nhập không đúng? Chức năng Data validation [xác nhận tính hợp lệ của dữ liệu] sẽ giúp bạn thực hiện công việc đó một cách khá trọn vẹn những đòi hỏi trên. Ngoài ra định dạng theo điều kiện [ Conditional Formating] là công cụ cho phép bạn áp dụng định dạng cho một ô [cell] hay nhiều ô [rangeof cells] trong bảng tính và sẽ thay đổi định dạng tùy theo giá trị của ô hay giá trị của công thức.
Mục tiêu:
- Trình bày được chức năng của Conditional Formating và Data validation.
- Vận dụng được các tiện ích trên vào bài tập cụ thể.
- Nghiêm túc, tự giác trong học tập
Nội dung
- Conditional Formating
- Giới thiệu Việc định dạng theo điều kiện có thể được thực hiện nhanh chóng thông qua các tập định dạng thông dụng được thiết lập trước. Danh mục định dạng theo điều kiện thiết lập sẵn:
Hình 1. Các loại định dạng theo điều kiện Hoặc người dùng có thể tạo qui luật định dạng mới tại New rules và quản lý các qui luật định dạng bằng công cụ Manage Rules...
Hình 1. Hộp hội thoại New Formatting Rule
trống, ô không trống và các lỗi trong ô. Qui luật này giống với định dạng theo điều kiện căn cứ vào nội dung trong ô của các phiên bản Excel trước kia. Format only top or bottom ranked values: Qui luật này chỉ áp dụng định dạng nhóm các ô chứa giá trị số lớn nhất và nhỏ nhất [tính theo phần trăm].
Format only values that are above or below average: Qui luật này áp dụng cho các ô chứa giá trị số so sánh với giá trị trung bình, độ lệch chuẩn của tập giá trị đang áp dụng định dạng.
Format only unique or duplicate values: Qui luật này áp dụng cho các ô chứa giá trị số là duy nhất hoặc trùng lặp.
Use a formula to determine which cells to format: Qui luật này áp dụng định dạng dựa trên kết quả luận lý của các công thức.
- Định dạng theo điều kiện sử dụng hình ảnh 1.2. Sử dụng Data Bars Định dạng theo điều kiện này hiển thị đồ thị hình thanh trực tiếp trong ô bảng tính. Chiều dài của thanh đồ thị dựa trên giá trị số đang chứa trong ô với sự so sánh tương đối với các giá trị khác trong vùng định dạng. Ví dụ: Sử dụng Data Bars để vẽ đồ thị hình thanh trực tiếp trong các ô để so sánh GPD dự tính của các nước Châu Á năm 2009. Cách làm:
- Chọn vùng B5:B
- Vào Home | Styles | Conditional Formatting | Data Bars | chọn Orange Data Bars
Hình 1. Bảng dữ liệu Data Bars chỉ cung cấp 6 màu cơ bản, để có nhiều lựa chọn hơn ta nhấn vào More Rules... Hộp thoại New Formatting Rule cung cấp cho bạn thêm một số tùy chọn như: Show bar only: ẩn các giá trị trong ô
Sử dụng các hộp Type và Value để điều chỉnh cách hiển thị của các thanh đồ thị theo giá trị trong các
Bar Color: Danh mục các màu cho thanh đồ thị
Hình 1. Các tùy chọn bổ sung cho Data Bars 1.2. Sử dụng Color Scales Áp dụng color scale trong định dạng theo điều kiện để tô màu nền các ô dựa trên
Hình 1 Bảng dữ liệu sau khi chọn màu
Hình 1. Bảng thông số mặc định cho kiểu Conditional Formatting vừa chọn
- Có thể tùy chỉnh thêm cho CF trên bằng cách vào Home | Styles | Conditional Formatting | Manage Rules ...
Hình 1. Hộp hội thoại Conditional Formatting Rules Manager 4. Chọn Rule cần hiệu chỉnh và nhấn nút Edit Rule.... Ví dụ điều chỉnh lại như sau:
- Format Style: chọn kiểu 3 màu là 3-Color Scale
- Minimun: Lowest Value – giá trị thấp nhất trong vùng số liệu đang chọn
- Maximun: Highest Value – giá trị cao nhất trong vùng số liệu đang chọn
- Midpoint: chọn Percent là 5%.
Hình 1. Hộp hội thoại Edit Formatting Rule để hiệu chỉnh các giá trị định dạng 5. Nhấn OK hoàn tất.