Bài giảng Microsoft Excel nâng cao

ng điều kiện để so sánh giá trị thoả mãn trong quá trình lọc. Copy to	Địa chỉ ô đầu tiên của một vùng bảng tính sẽ chứa dữ liệu sau khi lọc Lưu ý để hiển thị danh sách dữ liệu đầy đủ, ta gọi lệnh Data  Filter  Show All (cho cả lọc tự động hay nâng cao). 32Bài 3 – BIỂU ĐỒ VÀ IN ẤN I. Biểu đồ trong bảng tính Excel Mục đích hiển thị trực quan và dể hiểu bảng tính với những số liệu phức tạp. Biểu đồ biến đổi dữ liệu từ các hàng và cột trong bảng tính thành khối hình.1.1 Các thành phần của biểu đồ Vùng dữ liệu. Trục tạo độ. Hộp ghi chú (Legend). Tiêu đề (Title). *331.2 Các bước vẽ biểu đồ Để thực hiện vẽ biểu đồ ta gọi lệnh Insert->Chart Hoặc kích chuột vào biểu tượng Chart Wizard trên thanh công cụ chuẩn.Thực hiện qua 4 bước sau Lưu ý chọn vùng dữ liệu trước khi vẽ biểu đồ.*34 Bước 1 Chọn kiểu biểu đồ *35 Bước 2 Chọn vùng dữ liệu để vẽ biểu đồ Nếu vùng dữ liệu đã chọn thì chọn Next Nếu chưa chọn thì kích chuột vào khung Data range và kích chuột chọn vùng dữ liệu trong bảng tính để chọn, chọn Next *36 Bước 3 Lựa chọn thông tin bổ sung Chọn Thẻ Title: Nhập các tiêu đề cho biểu đồ và các trục: Chart Title	: Nhập tiêu đề cho biểu đồ. Category (X) axis	: Nhập tên trụ chủ đề. Value (Y) axis	: Nhập tên trục giá trị. Chọn thẻ Axis: Lựa chọn hiển thị hoặc không các tiêu đề của các trục. Chọn thẻ Grid lines: Chọn đường thẳng để vẽ các kiểu đường kẻ ô. Chọn thẻ Legend: Chọn vị trí hiển thị hộp chú thích.Chọn thẻ Data Labels: Chọn hiện hoặc không nhãn dữ liệu. Chọn thẻ Data Table: Chọn hiển thị bảng dữ liệu hoặc không. *37 Bước 4 Nơi lưu biểu đồ As new sheet 	: Tạo một sheet mới và lưu biểu đồ ở sheet đó. As object in	: Chọn một sheet đang hiển thị để lưu bảng đồ. *38II. In ấn bảng tính Vào File -> Print*39Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU I. Tổng hợp số liệu với SubTotal 1.1. Giới thiệuSố liệu dùng cho tổng hợp nằm trên 1 SheetThống kê dự vào một tiêu thức duy nhất, điều kiện thống kê đơn giảnKết quả của tổng hợp là Tổng lớn(Grand Sum), Tổng chi tiết(Subtotal), Giá trị trung bình (Average), Lớn nhất (Max), Nhỏ nhất( Min)Date40Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU I. Tổng hợp số liệu với SubTotal 1.2. Cách thực hiện- Sắp xếp các giá trị tại cột dùng làm tiêu thức thống kêChọn số liệu thống kêVào Data\SubTotalDate41At each change in: Chọn cột có giá trị làm điều kiện thống kê(Cột đã được sắp xếp)Use Function: Chọn hàm để thực hiện thống kêAdd Subtotal to: Chọn cột hoặc các cột số liệu chứa giá trị thống kêReplace curent subtotalPage break between groupsSummary below dataBài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU I. Tổng hợp số liệu với SubTotal Date42Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU II. Tổng hợp số liệu với Consolidate 1.1. Giới thiệuChức năng: Tổng hợp số liệu trên nhiều nguồn khác nhauSố liệu dùng cho tổng hợp có thể tập trung hoặc phân tán trên một Sheet hoặc các wordbook-Tổ chức cấu trúc dữ liệu cho các Source Data phảI giống nhauKết quả tổng hợp có thể lưu cùng vớI Data Source hoặc wordSheet khácDate43Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU II. Tổng hợp số liệu với Consolidate 1.2. Thực hiệnChuẩn bị số liệu nguồn, tổ chức các số liệu có cùng cấu trúcChọn Sheet chứa số liệu tổng hợpChọn Data\ ConsolidateDate44Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU II. Tổng hợp số liệu với Consolidate 1.2. Thực hiệnFunction: Chọn hàmReference: Địa chỉ số liệu nguồnTop row: Chọn hàng đầu tiênLeft column: Cột tráiCreate links to source data: Tạo liên kết giữa số liệu nguồn và tổng hợpDate45Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU III. Tổng hợp và phân tích số liệu với Pivottable 1.1. Giới thiệuChức năng: Dùng để nhóm và thống kê số liệu theo dạng hàng, cột (2D) và có thể báo cáo theo dạng (3D)Tạo ra bảng tổng kết-Tổ chức dữ liệu theo dạng kéo, thảLọc và nhóm dữ liệuVẽ biểu đồDate46Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU III. Tổng hợp và phân tích số liệu với Pivottable 1.2. Các thành phần cơ bản- Row Field: Một Field hàng trong Pivottable là biến có giá trị khác nhauVí dụ: Field là Hanghoa mà biến có thể là: Đường, Sữa, MuốI, TiêuColumn Field: Field cột giống như Field hàng. Một Field có thể là một biếnVí dụ: Field là Nghiepvu mà giá trị của nó là Nhap, XuatDate47Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU III. Tổng hợp và phân tích số liệu với Pivottable 1.2. Các thành phần cơ bản-Data Field : Là một biến mà Pivottable tổng kếtVí dụ: Tổng số lượng, Tổng thanh toán.Page Field: Dùng để chi tiết hơn so vớI Field cột, Field hàngVí dụ: Khi chọn Tenkhach ở Page Field thì Field hàng, Field cột chỉ hiển thị những số liệu liên quan đến khách hàng trênDate48Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU III. Tổng hợp và phân tích số liệu với Pivottable 1.3.Cách tạoQuét chọn khốI số liệu mà ta muốn xây dựng Pivottable Vào Data\Pivottable and PivotChart ReportDate49Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU III. Tổng hợp và phân tích số liệu với Pivottable 1.3.Cách tạoMicrosoft Excel list or Database: Danh sách hay CSDL trong wordSheetExternal data source: Dữ liệu lấy ở nguồn khác Multiple Consolidation ranges: Nhiều vùng khác nhau trong wordSheetAnother Pivottable Report: Một Pivottable khácPivottable: Tạo báo biểu dạng PivottablePivottable Report: Biểu đồ dạng Pivot kèm theo PivottableDate50Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU III. Tổng hợp và phân tích số liệu với Pivottable Chọn NextXác định lạI nguồn dữ liệuChọn Next qua bước 3Date51Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU III. Tổng hợp và phân tích số liệu với Pivottable 1.3.Cách tạoTrong bước 3, ta xác định các biến sẽ hoạt động ở Field hàng, Field cột, Field Page và Field DataKích chọn Layout Kéo thả các trường. Chọn OKDate52Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU III. Tổng hợp và phân tích số liệu với Pivottable 1.3.Cách tạoChọn Option để khai báo một số thành phần khác như Tên của PivottableChọn Finish để hoàn thànhDate53Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU III. Tổng hợp và phân tích số liệu với Pivottable Date54Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU III. Tổng hợp và phân tích số liệu với Pivottable 1.4. Hiệu chỉnh PivottableThay đổi thiết kế:Bổ sung thêm Column, row hoặc hoán đổI các trường khác+ Sử dụng PivotTable wizard: Đặt con trỏ trong vùng Pivottable, kích chọn Pivottable trên thanh công cụ và chọn wizard rồi bổ sung, xoá bỏ hay thay đổi ở bước 3b. Thay đổi hàm tổng kếtDouble click vào trường ở vùng Data hoặc Right Click tạI trường đó và chọn hàm tương ứngDate55Bài 4 – TỔNG HỢP, PHÂN TÍCH  VÀ THỐNG KÊ SỐ LIỆU III. Tổng hợp và phân tích số liệu với Pivottable 1.4. Hiệu chỉnh PivottableDate56Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL I. Tìm kiếm mục tiêu với Goal Seek1.1. Chức năng Goal Seek-Tìm kiếm giá trị xác định trong một công thức, dùng để điều chỉnh một số ô nhập liệu thích hợp vớI ô đóXác định giá trị cho ô công thức để ô đích đạt đến kết quả mong muốnVí dụ: Để tính được tỉ lệ lãi suất cần thiết nhằm để huy động vốn cần thiết hoặc tính được mức lãi suất tiền gởI ở tương lai ta có thể dùng chức năng Goal SeekDate57Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL I. Tìm kiếm mục tiêu với Goal Seek1.2. Sử dụng Goal SeekVí dụ : Sử dụng công thức Goal Seek để giảI bài toán: “ Vừa gà, vừa chó bó lại cho tròn, ba mươi sáu con, một trăm chân chẵn. HỏI mấy con gà, mấy con chó?”Ta lập bài toán:Tại ô A2: nhập số Gà( nhập giá trị bất kỳ nằm trong khoảng 1 đến 36)Tại ô B2 nhập số chó( 36-A1)TạI ô C2 tính tổng số chân : A2*2 + B2*4Date58Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL I. Tìm kiếm mục tiêu với Goal Seek1.2. Sử dụng Goal SeekYêu cầu: Thay đổI giá trị số gà (A2) sao cho số chân đạt đến 100(C2)Vào Tool\Goal Seek + Set Cell: Nhắp chọn C2+ To Value: 100+ By changing cell: A2- Chọn OKDate59Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL I. Tìm kiếm mục tiêu với Goal Seek1.2. Sử dụng Goal SeekChọn OK: Thay thế giá trị tìm được tạI ô By changing cellCancel: Huỷ bỏ kết quả tìm kiếm giữ nguyên giá trị ban đầuDate60Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL II. Kỹ thuật dự báo trên Excel2.1. Khái niệm-Dự báo định lượng chú trọng đến việc sử dụng các số liệu trong quá khứ để từ đó rút ra qui luậtDự báo định tính sử dụng phương pháp chuyên gia để dự báoKết quả của dự báo phụ thuộc vào việc sử dụng các số liệu thích hợp và phương pháp xử lý số liệuChuỗI số liệu: + sắp xếp theo thờI gian quan sát+ MọI kỳ trong chuỗI số liệu đều bằng nhau+ Date61Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL II. Kỹ thuật dự báo trên Excel2.1. Khái niệmChuỗI số liệu: + sắp xếp theo thờI gian quan sát+ Mọi kỳ trong chuỗI số liệu đều bằng nhau+Số liệu quan sát phảI xuất phát từ một điểm trong mỗI kỳ+ Không cho phép thiếu sô liệuDate62Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL II. Kỹ thuật dự báo trên Excel2.2. Mô hình dự báoMô hình dự báo nhân quảMô hình dự báo thời gianDate63Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL II. Kỹ thuật dự báo trên Excel2.3. Kỹ thuật dự báoBài toán: Ngân hàng A thống kê số tiền luân chuyển (tiền gởi và tiền vay) trong năm 2006 như sauYêu cầu: Sử dụng kỹ thuật dự báo hồI qui Tuyến tính để dự đoán số liệu kinh doanh cho năm 2007Date64Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL II. Kỹ thuật dự báo trên Excel2.3. Kỹ thuật dự báoHàm hồI qui tuyến tính có dạng: y=Ax+BA: Độ dốc(slope) của đường dự báo	Hàm Slope(y,x), trong đó y và x là dãy số đã biếtB: Là mức chặn (Intercept) của đường dự báo vào thờI điểm x	Hàm Intercept(y,x)Date65Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL II. Kỹ thuật dự báo trên Excel2.3. Kỹ thuật dự báoLập mô hình bài toán như sau:Date66Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL II. Kỹ thuật dự báo trên Excel2.3. Kỹ thuật dự báoSử dụng biểu đồ: Vẽ biểu đồ có dạng như sauDate67Bài 5 – TÌM KIẾM MỤC TIÊU VÀ DỰ BÁO TRONG EXCEL II. Kỹ thuật dự báo trên Excel2.3. Kỹ thuật dự báoSử dụng biểu đồ: Nhắp phảI chuột vào chuỗI dữ liệu trong biểu đồ, Chọn Add trendlinetrong type -> linaerTrong Options, TạI customãôg vào “dự báo” và chọn Display Equation on ChartChọn OKDate68Date69