Bài giảng Bảng tính điện tử microsoft excel 2000

ếp tăng dần. nếu là 1 thì dữ liệu trong vùng đối chiếu phải xếp tăng dần theo cột đầu tiên . 	Ví dụ: Tìm và ghi vào cột giá các mặt hàng theo mã hàng dựa vào bảng phụ:b- Hàm HLOOKUP (Horizontal lookup- tìm theo dòng) tương tự hàm Vlookup	- Chức năng : Thực hiện tìm, giá trị cần tìm ở dòng đầu tiên, trong vùng đối chiếu và lấy ra giá trị tương ứng ở dòng chỉ định tham gia vào việc ghi chép hoặc tính toán.	 - Cú pháp : = HLOOKUP (,,,) Chú ý:	- Trên bảng tính phải có 2 vùng : Vùng dữ liệu và vùng đối chiếu.	- Địa chỉ của vùng đối chiếu trong công thức phải viết là địa chỉ tuyệt đối.c- Một số tìm kiếm khác- Hàm Address: tìm địa chỉ=Address(Dòng, Cột, Biểu thức logic1, Biểu thức logic2, Bảng tính)=Address(3,7,True, True, “Sheet2”) -> Sheet2!$G$3- Hàm Choose:+ Cú pháp: = Choose (n, Gt1,Gt2,Gt3,Gt4,Gtm)+ Chức năng: Cho giá trị thứ n trong danh sách các giá trị từ Gt1-> Gtm=Choose(5,100,200,300,1000,5000,6000) -> 5000 - Hàm Match + Chức năng: cho biết vị trí của giá trị cần tìm trong vùng + Cú pháp =Match(Giá trị cần tìm, Vùng cần tìm, Dạng)Dạng có 3 loại : 	dạng =1 : Vùng cần tìm phải sắp xếp tăng dần	dạng =0 : Vùng cần tìm không phải sắp xếp trước	dạng = -1: Vùng cần tìm phải sắp xếp giảm dần ví dụ:=Match (30,A4:a12,1) -> Tìm giá trị 30 trong vùng từ A4 đến A12 đã được sắp xếp tăng dần trước-Hàm Index + Dạng tìm trên một vùng =Index(Vùng cần tìm,dòng,cột) Trả về giá trị chứa trong ô có dòng và cột trong vùng cần tìm =Index (b2:g8,4,3)Dạng tìm trên nhiều vùng=Index (Các vùng cần tìm, dòng, cột, Stt vùng)Trả về giá trị chứa trong ô có dòng và cột trong vùng có thứ tự đã chọn trong các vùng cần tìm=Index((B2:B8,G5:G12),3,5,1)8- Nhóm hàm phân tích tài chínha- Hàm FV.	- Chức năng : Tính giá trị tương lai của một đầu tư đều vào các kỳ, với lãi suất cố định. - Cú pháp : = FV (Lãi suất %, Tổng số kỳ, tiền đầu tư mỗi kỳ, vốn ban đầu, kiểu tt) Ví dụ 1: Đầu tư vào 1 dự án trong 2 năm, số tiền mỗi năm là 1000$. Lãi xuất: 10%/ năm, thì kết quả sau 2 năm đầu tư là? Ví dụ 2: Đầu tư 10000000VND vào 1 dự án trong 6 tháng, lãi xuất là 1.2%/ tháng, theo hình thức thanh toán cuối kỳ. 10,741,948.73 VNDVí dụ 3: Nếu mỗi năm đem gửi 2 triệu đồng, với lãi suất 5%/năm thì sau 4 năm số tiền thu được là ?Ví dụ 4: 1 dự án có vốn ban đầu là 100 triệu, mỗi tháng đầu tư thêm 20 triệu. trong 2 năm. Lãi xuất: 10%/ năm, thì kết quả sau 2 năm đầu tư là? b- Hàm tính PV.	 - Chức năng : Tính số tiền phải đầu tư với các định kỳ thanh toán 	 - Cú pháp : = PV(Lãi suất %, định kỳ TT, Tiền nhận theo kỳ, Tiền tương lai, kiểu TT) Ví dụ 1: 1 công gọi đầu tư vào công ty 270 triệu đồng, trong 3 năm. Cuối năm thứ 3 bạn nhận 300 triệu đồng, lãi suất là 10%/ 1 năm. Xét đầu tư này có lợi hay không? =PV(10%,3,0,300,0) -> 225.39 Ví dụ 2: 1 công gọi đầu tư vào công ty 230 triệu đồng, trong 3 năm. Cuối mỗi năm, bạn nhận được 100 triệu, lãi suất là 10%/ 1 năm. Xét đầu tư này có lợi hay không? =PV(10%,3,100,0,0) -> $248.69 c- Hàm NPV.	- Chức năng : Tính giá trị hiện tại thuần của việc đầu tư khi biết lãi suất chiết khấu và các khoản thanh toán trong tương lai (giá trị âm) và thu nhập (giá trị dương). - Cú pháp : = NPV (Lãi suất triết khấu, giá trị 1, giá trị 2, , giá trị 29) Ví dụ 1: Tính số tiền cần gửi tiết kiệm ở hiện tại, để hàng năm có thể rút ra số tiền tương ứng các kỳ như sau: - Năm thứ nhất: 20 triệu đồng, - Năm thứ hai: 25 triệu đồng. - Năm thứ ba: 30 triệu đồng - Năm thứ tư: 17 triệu đồng - Năm thứ năm: 40 triệu đồng	 Biết lãi suất là 5%/nămVí dụ 2: Xét việc đầu tư bắt đầu vào thời kỳ đầu tiên vào 1 dự án. 1 dự án đầu tư vào việc mua cửa hàng với giá 400 triệu, dự kiến các khảon thu nhập nhận được vào cuối mỗi năm, trong 5 năm đầu tiên là: 80 triệu, 92 triệu, 100 triệu, 120 triệu, 145 triệu. Dự án nhận thấy sang năm thứ 6 của hàng sẽ bị sập cần phải đầu tư thêm 90 triệu. Lãi suất là 8%.	=NPV(8%,80,92,100,120,145,-90) -> -37,495 ( đầu tư không có lợi)=NPV(5%,20,25,30,17,40)d- Hàm tính tiền trả cho một khoản vay trả góp PMT.	- Chức năng : Tính khoản trả cho một khoản vay, trên cơ sở các khoản trả hàng kỳ không đổi và lãi suất không thay đổi. Khoản trả do hàm này tìm ra bao gồm cả phần trả vốn và phần trả lãi.	 - Cú pháp : = PMT (Lãi suất %, Tổng số lần thanh toán, Vốn ban đầu, giá trị tương lai, kiểu thanh toán) Ví dụ 1: Mua 1 xe máy theo hình thức trả góp với giá 27 triệu đồng hiện tại, lãi suất là 6%/năm. Trả trong 18 tháng cả gốc và lãi, trả tiền vào cuối mỗi tháng. Tính số tiền mà người mua phải trả hàng tháng = PMT(6%/12,18,27,0,0) Ví dụ 2: tính số tiền phải gửi tiết kiệm mỗi tháng, để sau 18 năm có được số tiền là 500 triệu, với lãi suất 6%/năm.	= PMT(6%/12,18*12,0,500,1) e- Hàm NPER.	- Chức năng : Tính số kỳ hạn đủ để trả hết món nợ và với mức thanh toán định kỳ nhất định. - Cú pháp : = NPER (Lãi suất, khoản thanh toán mỗi kỳ, giá trị hiện tại, giá trị tương lai) Ví dụ : Một dự án có mức đầu tư ban đầu là 200 triệu đồng, sau đó cuối mỗi năm đầu tư thêm 50 triệu đồng. Với mức lãi suất 35%/năm. Hỏi bạn hải đầu tư trong bao lâu để số tiền cuối cùng được 1000 triệu. = NPER(35%,-50,-200,1000,0) f- Hàm RATE.	- Chức năng : xác định tỷ lệ lãi suất tính cho các khoản thanh toán định kỳ cố định hay thanh toán bằng tiền mặt trả gọn. 	 - Cú pháp : = RATE (Số thời kỳ, tiền thanh toán định kỳ, giá trị hiện tại, giá trị tương lai,Lãi suất ước tính) Ví dụ: Gửi tiền vào ngân hàng trong 5 năm, mỗi năm 1000$. Cuối năm thứ năm nhận được 6000$. Tính lãi suất? Chương III- Cơ sở dữ liệuI- Khái niệm cơ sở dữ liệu trong Excel - Mỗi Sheet hoặc WorkBook được coi là một cơ sở dữ liệu - Một loạt các dòng trên bảng tính, mỗi dòng chứa các dữ liệu liên quan đến nhau để mô tả một đối tượng nào đó. Các dòng đó gọi là danh sách - Dòng đầu tiên phải là tiêu đề cột hay tên cột + Cột trong danh sách gọi là trường (Field) : + Tên trường không được trùng nhau và dài tùy ý- Dòng trong danh sách gọi là bản ghi (Record)II- Form dữ liệu 1- Mục đích cho phép nhập, xem và sửa dữ liệu dạng cột. 2-Thao tác :	Mở Menu Data/chọn Form Các nút chức năng: New : Thêm bản ghi (dòng mới) Delete : Xoá bản ghi hiện thời Restore: Không thực hiện lệnh cuối Find Prev :Chuyển về bản ghi trước Find Next: Chuyển đến bản ghi kế tiếp Close : Đóng Form.	III- Kiểm tra dữ liệu (Validation)a. Chức năng: - Hướng dẫn người sử dụng cập nhật dữ liệu. - Thông báo lỗi sau khi người sử dụng cập nhật dữ liệu không hợp lệ.b. Thực hiện:Bước 1: Chọn vùng, ô cần kiểm tra. Mở menu Data/Validation...Bước 2: Thiết lập các thông số kiểm tra.- Chọn Tab Settings.Xác định kiểu SL cần kiểm tra.Tiêu chuẩn DL cần kiểm tra.Giới hạn đầu của tiêu chuẩn.Giới hạn cuối của tiêu chuẩn.Tiêu đề thông báo nhập DL.Nội dung thông báo nhập DL.- Chọn Tab Input Message.	Hướng dẫn người sử dụng cập nhật dữ liệu.Tiêu đề hộp thoại khi DL nhập sai.Nội dung thông báo hộp thoại khi DL nhập sai.- Chọn Tab Eror Alert.	Thông bấo lỗi khi người sử dụng cập nhật DL không hợp lệ.IV- Sắp xếp và lọc dữ liệu 1-Sắp xếp dữ liệu trên bảng tính. - Mục đích: Sắp xếp lại trật tự dữ liệu theo 1 cột nào đó - Thao tác: B1: Chọn vùng dữ liệu B2: Mở Menu Data/chọn Sort, đặt các tuỳ chọn:Sort by : chọn tên cột cần sắp xếp (ví dụ cột Tên) - Chọn Ascending: Sắp xếp dữ liệu tăng dần - Chọn Descending: Sắp xếp dữ liệu giảm dần.Then by: Cột ưu tiên thứ 2 khi sắp xếp.My list has: - Header row: có dòng tiêu đề. - No Header row: không có dòng tiêu đề. Chọn OK2- Lọc dữ liệu. -Mục đích: Đưa ra danh sách các dữ liệu thoả mãn điều kiện nào đó để kiểm tra, tính toán hoặc thống kê. -Thao tác: Cách 1: Lọc dữ liệu tự động B1: Chọn vùng dữ liệu B2: Mở Menu Data/Filter/Auto Filter. -> Chọn ô lấy tiêu chuẩn cần lọc ở tên cột lấy tiêu chuẩn -> Chọn giá trị cần lọc. Ví dụ: chọn ra tất cả sv có đạo đức loại A:Cách 2: Lọc có điều kiện B1: Viết điều kiện cần lọc ở một vùng nào đó, tên vùng điều kiện phải giống tên cột lấy điều kiện. B2: Chọn vùng dữ liệu: B3: Mở Menu Data/ Filter/Advance Filter. ->Đặt các tuỳ chọn :Action: - Filter the list, in-place: lọc tại vị trí dữ liệu - Copy to another location: copy dữ liệu lọc được, tới 1 vùng khác.List Range : Địa chỉ vùng dữ liệuCriteria Range: Địa chỉ vùng điều kiện -> Chọn OKV- Thống kê dữ liệu theo nhóm. 1- Chức năng: Tính toán, tổng hợp dữ liệu theo từng nhóm. 2- Cách thực hiện B1: Sắp xếp cột cần nhóm theo chiều tăng hoặc giảm dần. B2: Chọn vùng dữ liệu. B3: Mở Menu Data/ Subtotals -> Đặt các tuỳ chọn sau: . At each change in : Cột cần nhóm . Use Function : Chọn Hàm cần tính . Add subtotal to : cột cần tính -> Chọn OK	VI- Lập bảng báo cáo thống kê PivotTable 1-Chức năng: Lập bảng báo cáo thống kê dữ liệu 2-Cấu trúc của PivotTale	Cột lấy làm tiêu đề dòngCột lấy làm tiêu đề cộtCột cần tính toán thống kê3-Cách thực hiện B1: Sắp xếp cột cần nhóm theo chiều tăng hoặc giảm dần. B2: Chọn vùng dữ liệu. B3: Mở Menu Data/ Pivot Table Report B4: Thực hiện theo các chỉ dẫn của hộp thoại Wizard -> Chọn Finish	VII- Hợp nhất dữ liệu (Consolidate) 1-Chức năng: Tính toán tổng hợp trên nhiều miền dữ liệu 2-Cách thực hiện: B1: Chọn bảng tính đích. B2: Mở Data/ Consolidate. B3: Cài đặt các tuỳ chọnFunction: Chọn hàm tính toánReference: Vùng dữ liệu nguồnAdd: Đưa vùng dữ liệu vào miền tính toánChọn OK. Chương IV-chèn các đối tượng vào bảng tính1-Chèn chữ nghệ thuật B1: Mở Menu Insert, chọn Picture, chọn WordArt B2: Chọn kiểu dáng chữ trong hộp WordArt Gallery/ Chọn OK. B3: Chọn font và gõ văn bản vào dòng Your text here Chọn OK.2. Chèn hình ảnh lên bảng tính a-Chèn ảnh từ ClipArt( thư viện ảnh) B1: Chọn vị trí đặt ảnh B2: Mở Insert/Picture/ClipArt B3: Chọn nhóm và ảnh cần lấy, chọn Insert Clipb- Chèn ảnh từ các tệp tin ảnh B1: Chọn vị trí đặt ảnh trên bảng tính B2: Mở Insert chọn Picture chọn From file B3 Chọn Folder và File ảnh cần lấy/Chọn INSERT3. Chèn siêu liên kết VB - Mục đích : Là liên kết các tài liệu thông qua điểm liên kết trên bảng tính. - Cách tạo điểm liên kết B1: Chọn vị trí đối tượng lấy làm điểm liên kết B2: Mở Insert chọn HyperLink B3: Chọn Folder và File cần liên kết đến/Chọn OKChú ý: Các File cần liên kết đến phải có trên đĩa