Excel ứng dụng trong kinh tế 2 - Trần Thanh Phong

119 trang phuongnguyen 3830

Download

Bạn đang xem 20 trang mẫu của tài liệu "Excel ứng dụng trong kinh tế 2 - Trần Thanh Phong", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

excel_ung_dung_trong_kinh_te_2_tran_thanh_phong.pdf

Nội dung text: Excel ứng dụng trong kinh tế 2 - Trần Thanh Phong

Chương Trình Giảng Dạy Kinh Tế Fulbright Chương Trình Đào Tạo Một Năm Về Kinh Tế Học Ứng Dụng Cho Chính Sách Công EEXXCCEELL ƯỨNÙNGG DDUỤNÏNGG TTRROONNGG KKIINNHH TTEẾÁ PHẦNÀ 2 BIÊN SOẠN: TRẦN THANH PHONG Phòng máy tính, năm 2004
MỤC LỤC BÀI 1. QUI TRÌNH LẬP BÀI TOÁN TRÊN BẢNG TÍNH 1 1.1. Giới thiệu 1 1.2. Qui trình 2 BÀI 2. TỔ CHỨC DỮ LIỆU TRONG BẢNG TÍNH 6 2.1. Tạo danh sách (List) 6 2.2. Sử dụng mẫu nhập liệu (Data Form) 7 2.3. Sắp xếp dữ liệu (Sort) 10 2.4. Lọc dữ liệu từ danh sách bằng Auto Filter 13 2.5. Lọc dữ liệu nâng cao bằng Advance Filter 17 2.6. Dùng Data Validation để kiểm soát nhập liệu 20 2.7. Bài tập thực hành 21 BÀI 3. TỔNG HỢP DỮ LIỆU VÀ PIVOTTABLE 23 3.1. Tạo Pivort Table 23 3.2. Hiệu chỉnh PivotTable 27 3.3. Điều khiển việc hiển thị thông tin 28 3.4. Tạo PivotChart 29 3.5. Sử dụng subtotals 31 3.6. Dùng các hàm dữ liệu 32 3.7. Bài tập thực hành 34 BÀI 4. BÀI TOÁN ĐIỂM HOÀ VỐN 36 4.1. Giới thiệu 36 4.2. Bài toán minh họa 37 BÀI 5. GIẢI PHƯƠNG TRÌNH VÀ HỆ PHƯƠNG TRÌNH 41 5.1. Giải phương trình 41 5.2. Giải hệ phương trình 43 5.3. Sử dụng Solver 47 5.4. Ma trận 49 BÀI 6. BÀI TOÁN TỐI ƯU VÀ QUI HOẠCH TUYẾN TÍNH 52 6.1. Tối ưu một mục tiêu (Linear Programming) 52 6.2. Bài toán đầu tư (Linear Programming) 58 6.3. Qui hoạch nguyên (Integer Linear Programming) 60 BÀI 7. PHÂN TÍCH RỦI RO 63 7.1. Phân tích độ nhạy 63 Phân tích độ nhạy một chiều 64 Phân tích độ nhạy hai chiều 65 7.2. Phân tích tình huống (Scenarios) 67 Phân tích tình huống 67 Hàm Index 72 7.3. Mô phỏng bằng Crystal Ball 74 a. Mô hình giá không đổi không chắc chắn 75 b. Mô hình giá độc lập không chắc chắn 79 c. Mô hình bước ngẫu nhiên 82
d. Mô hình tự hồi qui bậc nhất – AR(1) 85 BÀI 8. XÁC SUẤT & THỐNG KÊ 89 Bổ sung công cụ phân tích dữ liệu vào Excel: 89 8.1. Thống kê 89 Các thông số thống kê mô tả (Descriptive statistics) 92 Bảng tần suất (Histogram) 93 Xếp hạng và phần trăm theo nhóm (Rank and Percentile) 95 8.2. Biến ngẫu nhiên và Phân phối xác suất 96 Phát số ngẫu nhiên theo các phân phối xác suất 97 Một số hàm về phân phối trong Excel 99 8.3. Tương quan và hồi qui tuyến tính 105 Phụ Lục 111 Đặt tên vùng 111 Danh sách AutoFill tự tạo 112 Phím tắt thông dụng 113
Lời giới thiệu Tài liệu được biên soạn dưới dạng hướng dẫn từng bước phần mềm Microsoft Excel để giải các bài toán trong kinh tế. Tài liệu không chú trọng vào việc đánh giá, giải thích các ý nghĩa kinh tế của bài toán cũng như các lý thuyết kinh tế có liên quan. Các bạn đọc cần tham khảo thêm các tài liệu về lý thuyết để nắm rõ hơn cách diễn giải và phân tích ý nghĩa các kết quả tìm được bằng công cụ máy tính. Các tài liệu cần tham khảo thêm: - Phương pháp định lượng trong quản lý, Cao Hào Thi, Nguyễn Thống, Nhà xuất bản Thống kê, 1998. - Toán ứng dụng trong kinh doanh, Cao Hào Thi, Chương trình Giảng dạy Kinh tế Fulbright, 1999. - Thống kê ứng dụng trong kinh doanh, Cao Hào Thi, Đại Học Bách Khoa Tp. HCM, 1998. - Ra quyết định trong quản lý, Cao Hào Thi, Đại Học Bách Khoa Tp. HCM, 2001. Tài liệu được kèm theo các tập số liệu cho phần lý thuyết và bài tập. Ngoài ra mỗi bài còn có phần minh họa sinh động bằng phim hy vọng sẽ giúp bạn đọc dễ tiếp cận vấn đề hơn. Còn nhiều vấn đề chưa được đề cập đến trong tài liệu, sẽ dần được bổ sung trong thời gian sắp tới. Mọi góp ý xin vui lòng gửi đến tác giả theo địa chỉ sau: Trần Thanh Phong Chương trình Giảng dạy Kinh tế Fulbright 232/6 Võ Thị Sáu, Quận 3, Tp. HCM. Điện thoại: 848-9325103 Fax: 848-9325104 E-mail: ttphong@fetp.vnn.vn Website: www.fetp.edu.vn Tài liệu cho phép mọi người sử dụng, sao chép theo qui định của Chương trình Giảng dạy Kinh tế Fulbright. Tp. HCM, ngày 18 tháng 08 năm 2004.
Chương trình Giảng dạy Kinh tế Fulbright Bài 1. Qui trình lập bài toán trên bảng tính BÀI 1. QUI TRÌNH LẬP BÀI TOÁN TRÊN BẢNG TÍNH 1.1. Giới thiệu Nhiều thập kỷ qua, hàng triệu nhà quản lý phát hiện ra phương cách hiệu quả nhất để phân tích và đánh giá các phương án bằng cách xây dựng các mô hình bài toán trên bảng tính. Mô hình trên bảng tính là một tập các quan hệ toán học và luận lý được thiết lập trên máy tính nhằm giải quyết các vấn đề trong thực tế và hỗ trợ nhà quản lý ra các quyết định kinh doanh. Sử dụng mô hình bảng tính đã giúp cho nhà quản lý có thể phân tích các phương án kinh doanh trước khi lựa chọn một phương án để thực thi. Phân loại và đặc trưng của các kỹ thuật mô hình hóa các bài toán quản lý: Mô hình Hình thức của Giá trị của biến Kỹ thuật hàm f(*) độc lập Mô hình giới Biết trước Biết trước hoặc Qui hoạch tuyến tính (Linear hạn Xác định rõ trong sự kiểm programming), mạng (Prescriptive ràng soát của nhà ra (networks), CPM, bài toán models) quyết định tối ưu (Goal programming), EOQ, qui hoạch phi tuyến (non-linear programming) Mô hình dự Không biết Biết trước hoặc Phân tích hồi qui báo trước trong sự kiểm (Regression analysis, phân (Predictive Không rõ ràng soát của nhà ra tích chuỗi thời gian (Time models) quyết định series analysis), phân tích sai biệt (discriminant analysis) Mô hình mô tả Biết trước Không biết trước Mô phỏng (Simulation), (Descriptive Xác định rõ hoặc bất định Dòng chờ (Queuing), PERT, models) ràng Bài toán tồn kho (Inventory Models) - Mô hình giới hạn: giải quyết các bài toán mà chúng ta biết trước các giá trị của các biến độc lập x1, x2, , xn hoặc giá trị của các biến này nằm trong sự kiểm soát và biết được mối quan hệ giữa các biến độc lập và biến phụ thuộc với nhau. Khi đó, kết quả của biến phụ thuộc Y xác định theo hàm f(x1, x2, , xn) sẽ thu được một cách chính xác. - Mô hình dự báo: giải quyết các bài toán chúng ta biết trước các giá trị của các biến độc lập x1, x2, , xn hoặc giá trị của các biến này nằm trong sự kiểm soát và hàm f(x1, x2, , xn) chưa biết trước. Khi đó, ta cần phải ước lượng Trần Thanh Phong 1 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 1. Qui trình lập bài toán trên bảng tính hàm f để từ đó xác định giá trị dự báo của biến phụ thuộc Y. - Mô hình mô tả: trong bài toán này ta biết trước mối quan hệ giữa các biến độc lập x1, x2, , xn và biến phụ thuộc Y. Tuy nhiên, giá trị cụ thể của một hoặc nhiều biến độc lập ta lại không biết. Do vậy để tính toán kết quả của biến phụ thuộc Y, ta cần phải mô phỏng các giá trị của các biến độc lập. 1.2. Qui trình Quá trình giải quyết bài toán thường theo các bước sau: 1/ Xác định vấn đề cần giải quyết 2/ Lập mô hình 3/ Thu thập dũ liệu 4/ Tìm lơì giải 5/ Thử nghiệm lời giải 6/ Phân tích kết quả 7/ Thực hiện lời giải Xác định các vấn đề cần giải quyết Lập mô hình Thu thập dữ liệu Tìm lời giải Thử nghiệm lời giải Phân tích kết quả Thực hiện lời giải Hình 1.1. Các bước trong quá trình giải bài toán Trần Thanh Phong 2 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 1. Qui trình lập bài toán trên bảng tính Bước 1: Xác định vấn đề cần giải quyết Hình thành một câu hay một mệnh đề ngắn gọn, rõ ràng về cái gì cần phải giải quyết. Các khó khăn khi đặt vấn đề : • Vấn đề đặt ra tạo ra những mâu thuẫn trong nội bộ cơ quan và quyền lợi các thành phần trái ngược nhau. • Vấn đề giải quyết đụng chạm đến mọi mặt của cơ quan nên phải chọn những vấn đề nào cần giải quyết ưu tiên để nó đem lại kết quả tổng hợp cho cơ quan. • Nhiều khi đặt vấn đề theo định hướng của lời giải cục bộ • Khi đặt vấn đề và tìm ra lời giải thì lời giải đã lạc hậu so với thực tế. Bước 2: Lập mô hình Mô hình là một sự đơn giản hóa thực tế, được thiết kế bao gồm các đặc điểm chủ yếu đặc trưng cho sự hoạt động của hệ thống thực. Mô hình cần phải diễn tả được các bản chất, các tình huống và các trạng thái của hệ thống. Có thể có 3 loại mô hình : • Mô hình vật lý : mô hình thu gọn của một thực thể • Mô hình khái niệm (mô hình sơ đồ) : mô hình diễn tả các mối liên hệ giữa các bộ phận trong hệ thống. • Mô hình toán học : thường là một tập họp các biểu thức toán học dùng để diễn tả bản chất của hệ thống. Trong phương pháp định lượng, người ta thường dùng các mô hình toán học. Trong loại mô hình này có chứa các biến số và các tham số. Biến số có thể chia làm hai loại gồm biến số điều khiển được và những biến số không thể điều khiển được. Các đặc điểm cần có của mô hình toán học : • Mô hình phải giải được • Mô hình phải phù hợp với thực tế • Mô hình phải dễ hiểu đối với nhà quản lý • Mô hình phải dễ thay đổi • Mô hình phải dễ thu thập dữ liệu Những khó khăn khi lập mô hình : • Cần phải dung hoà giữa mức độ phức tạp của mô hình toán và khả năng sử dụng mô hình của nhà quản lý. • Làm thế nào để mô hình tương thích với những mô hình có sẵn trong lý thuyết phân tích định lượng. Trần Thanh Phong 3 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 1. Qui trình lập bài toán trên bảng tính Bước 3: Thu thập dữ liệu dùng cho mô hình Đặc điểm của dữ liệu : • Phải chính xác • Phải đầy đủ Dù mô hình tốt nhưng dữ liệu tồi cũng cho ra kết quả sai (“GIGO” Garbage In Garbage Out) Nguồn dữ liệu được thu thập từ : • Các bản báo cáo của cơ quan mình, cơ quan liên hệ • Các cuộc phỏng vấn trực tiếp • Các phiếu thăm dò ý kiến • Đo đạc hay đo đếm để lấy mẫu trực tiếp • Dùng các phương pháp thống kê để suy ra các thông số cần thiết Các khó khăn khi thu thập dữ liệu • Không biết lấy dữ liệu từ đâu • Dữ liệu không chính xác không đầy đủ Bước 4: Tìm lời giải Tìm lời giải nghĩa là vận dụng mô hình với dữ liệu đã thu thập được để tìm ra lời giải tối ưu nhất. Tìm lời giải bằng các phương pháp sau : • Giải phương trình, hệ phương trình hay bất phương trình • Phương pháp thử dần hay phương pháp dò dẫm (Trial and error method) rồi so sánh kết quả • Liệt kê một số phương án (hữu hạn) rồi so sánh các phương án để chọn ra phương án tốt nhất. • Dùng thuật toán (giải thuật - algorithm) Thuật toán là 1 dãy theo những thứ tự nhất định các hành động hay các bước đi nếu thực hiện theo đó thì sẽ đạt được kết quả trong một thời gian hữu hạn. Những khó khăn về lời giải : • Lời giải khó hiểu đối với nhà quản lý, nhất là những lời giải đặc biệt • Thường mô hình toán chỉ có một lời giải duy nhất trong khi nhà quản lý lại thích có nhiều lời giải để lựa chọn. Trần Thanh Phong 4 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 1. Qui trình lập bài toán trên bảng tính Bước 5: Thử nghiệm lời giải Lời giải có được là do áp dụng mô hình với các dữ liệu đã thu thập được. Thử nghiệm lời giải là xem xét mức độ ổn định của lời giải đối với dữ liệu và mô hình. • Đối với dữ liệu : thu thập từ nguồn khác rồi đưa và lời giải để thử • Đối với mô hình : phân tích độ nhạy của mô hình toán bằng cách thay đổi một ít về số liệu rồi đưa vào mô hình, phân tích sự thay đổi của kết quả. Nếu kết quả quá nhạy đối với sự thay đổi của số liệu thì phải điều chỉnh mô hình. Các khó khăn khi thử lời giải : Thường lời giải là các dự kiến xảy ra trong tương lai chưa biết tốt xấu ở mức độ nào, thường phải hỏi ý kiến đánh giá của các nhà quản lý. Bước 6: Phân tích kết quả Phải cân nhắc, xem xét những ảnh hưởng, những hậu quả gây nên cho cơ quan hay cho hệ thống khi thực hiện lời giải Các khó khăn thường gặp : • Kết quả gây tác động ảnh hưởng đến toàn thể cơ quan • Khi thay đổi nề nếp hoạt động sinh hoạt của cơ quan là một điều khó • Phải biết rõ khi áp dụng lời giải thì ai bị ảnh hưởng, ảnh hưởng như thế nào, những người bị ảnh hưởng sẽ sa sút hay thịnh vượng hơn. Bước 7: Thực hiện kết quả Thực hiện kết quả nghĩa là đưa giải pháp mới vào hoạt động của cơ quan Khó khăn • Thiếu sự ủng hộ của các nhà quản lý (do làm mất quyền lợi của họ) • Thiếu sự cam kết điều chỉnh của nhóm nghiên cứu. Trần Thanh Phong 5 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính BÀI 2. TỔ CHỨC DỮ LIỆU TRONG BẢNG TÍNH Trong bài này chúng ta sẽ nghiên cứu về cách tạo danh sách, thêm, hiệu chỉnh, xóa và tìm kiếm thông tin trong danh sách. Ngoài ra trong bài cũng đề cập đến các lệnh lọc tìm dữ liệu từ danh sách theo một hay nhiều điều kiện. Bài học sử dụng các tập tin: bai2-1.xls cho phần lý thuyết và bai2-2.xls cho phần thực hành. 2.1. Tạo danh sách (List) Danh sách được cấu thành từ các bản ghi (record) thường là dòng trong bảng tính Excel. Mỗi bản ghi chứa thông tin về một điều gì đó (ví dụ: một dòng trong sổ địa chỉ). Mỗi bản ghi có nhiều trường (field), mỗi trường chứa các thông tin cụ thể: tên, ngày sinh, địa chỉ, điện thoại, . Trong Excel, các trường thường được bố trí vào các cột và các bản ghi thường bố trí theo dòng (xem hình 2.1 và hình 2.2). Hình 2.1. Tên các trường (field) được nhập vào dòng đầu tiên của danh sách Hình 2.2. Danh sách dữ liệu Hình 2.3. Thanh định dạng Các bước tạo danh sách như hình 2.2: B1. Khởi động Excel B2. Nhập “Tên chỉ tiêu” vào ô có địa chỉ A1, nhấp phím để di chuyển qua ô kế tiếp B3. Nhập tên các trường còn lại như: Tên nước, 1990, 1991, B4. Nhập vào thông tin cho các dòng B5. Chọn vùng A1:J1 chọn nền xanh và chữ đậm từ thanh định dạng. B6. Để thuận tiện cho việc nhập liệu ta chia màn hình làm hai phần. Di chuyển Trần Thanh Phong 6 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính chuột vào hộp chia màn hình theo chiều dọc và hình chuột biến thành mũi tên 2 chiều, giữ chuột và kéo xuống dưới dòng 1 và thả chuột. Màn hình Excel đã được chia làm hai phần theo chiều dọc. B7. Vào thực đơn Window Ỉ Freeze để làm cho dòng tiêu đề luôn luôn hiển thị trên màn hình. Các điểm lưu ý khi tạo danh sách: Lưu ý Giải thích Chỉ tạo một danh sách trên một bảng Chức năng quản lý dữ liệu như: lọc dữ tính (worksheet) liệu (filter) chỉ có thể áp dụng mỗi lần cho một danh sách. Nên chừa ra ít nhất 1 cột hoặc dòng Điều này giúp Excel dễ dàng xác định trống giữa danh sách và các dữ liệu khác danh sách khi áp dụng các chức năng trên worksheet sắp xếp (sort), lọc dữ liệu (filter) hoặc chèn (insert) một biểu thức tính toán dạng tổng. Tránh để các dòng hoặc cột trống trong Để giúp Excel dễ dàng chọn đúng danh danh sách sách. Tạo nhãn các trường (field) ở dòng đầu Excel dùng nhãn để đưa vào các báo cáo tiên của danh sách. và dùng để tìm kiếm/ tổ chức dữ liệu. Cố gắng chia nhỏ các thông tin Điều này giúp dễ dàng sắp xếp, lọc và tạo báo cáo tổng hợp theo yêu cầu. Mỗi cột nên chứa cùng loại thông tin Giúp danh sách dễ theo dõi và dễ hiễu Không dùng trùng tên trường Tên trường bị trùng sẽ gây sai sót trong nhập liệu và sắp xếp. 2.2. Sử dụng mẫu nhập liệu (Data Form) Có hai cách để nhập dữ liệu (bản ghi) vào danh sách: nhập trực tiếp vào các dòng bên dưới tiêu đề và nhập thông qua mẫu nhập liệu. Tạo mẫu nhập liệu (sử dụng worksheet ASEAN trong tập tin bai2-1.xls) Trần Thanh Phong 7 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính Hình 2.4. Hộp thoại Data Form B1. Đặt ô hiện hành vào nơi nào đó trong danh sách. B2. Chọn Data Ỉ Form (xem hình 2.4). B3. Nhấp nút Find Next để di chuyển đến bản ghi tiếp theo. B4. Nhấp nút Find Prev để lùi về bản ghi phía trước. B5. Nhấp nút New để thêm bản ghi mới và nhập thông tin. B6. Dùng phím hay để di chuyển tới/ lui trong bản ghi và nhập các thông tin vào các trường tương ứng. B7. Nhấp nút Close khi hoàn thành việc nhập liệu. Tìm dữ liệu Hình 2.5. Mẫu đặt điều kiện tìm kiếm Trần Thanh Phong 8 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính B1. Đặt ô hiện hành vào nơi nào đó trong danh sách. B2. Chọn Data Ỉ Form từ thanh thực đơn. B3. Nhấp nút Criteria và một mẫu trống sẽ xuất hiện. Nhập vào điều kiện tìm ở các trường cần tìm và nhấp nút Find Next để hiển thị kết quả tìm. Ví dụ: hãy nhật vào trường “Tên nước” là “Vietnam” và nhấp nút Find Next để xem kết quả (xem hình 2.5). B4. Nhấp nút Find Next hay Find Prev để di chuyển tới/ lui trong các bản ghi thõa điều kiện tìm. B5. Nhấp nút Close khi hoàn tất công việc. Tìm và thay thế (sử dụng worksheet ASEAN trong bai2-1.xls) Hình 2.6. Hộp thoại tìm và thay thế B1. Chọn Edit Ỉ Replace từ thanh thực đơn B2. Nhập “Lao PDR” vào hộp “Find what” và hhập “Lào” vào hộp “Replace with” để tìm và thay thế “Lao PDR” bằng “Lào” (xem hình 2.6). B3. Nhấp nút Replace All để thay thế tất cả không cần kiểm tra hoặc nhấp nút Find Next để đến bản ghi thõa điều kiện tìm và nếu muốn thay thế thì nhấp tiếp nút Replace. Bạn làm tương tự như vậy cho đến hết danh sách. B4. Nhấp nút Close để đóng hộp thoại. Xóa bản ghi (sử dụng worksheet ASEAN trong tập tin bai2-1.xls) Trần Thanh Phong 9 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính Hình 2.7. Hộp thoại Data Form Hình 2.8. Xác nhận xóa bản ghi B1. Đặt ô hiện hành vào nơi nào đó trong danh sách và chọn Data Ỉ Form. B2. Dùng Find Next hoặc Find Prev hoặc đặt điều kiện tìm bằng Criteria để tìm đến bản ghi cần xóa (xem hình 2.7). B3. Nhấp nút Delete, hộp thoại xuất hiện yêu cầu bạn xác nhận lệnh xóa bằng cách nhấp nút OK, nhấp nút Cancel để hủy lệnh xóa (Xem hình 2.8). B4. Nhấp nút Close để đóng hộp thoại và trở về worksheet. 2.3. Sắp xếp dữ liệu (Sort) Chúng ta thường nhập liệu vào cuối danh sách, do vậy dữ liệu thường không theo một trình tự nào. Chúng ta cần sắp xếp lại dữ liệu nhằm thuận lợi trong việc quản lý dữ liệu. Excel hỗ trợ sắp xếp dữ liệu theo thứ tự tăng dần (A Ỉ Z) hoặc theo thứ tự giảm dần (Z Ỉ A) theo một hoặc nhiều trường cần sắp xếp. (sử dụng worksheet ASEAN trong tập tin bai2-1.xls) Trần Thanh Phong 10 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính Hình 2.9. Bảng dữ liệu cần sắp xếp theo “Tên nước” và “Tên chỉ tiêu” theo thứ tự tăng dần. Hình 2.10. Bảng dữ liệu đã được sắp xếp theo “Tên nước” và “Tên chỉ tiêu” theo thứ tự tăng dần. Trần Thanh Phong 11 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính Hình 2.11. Hộp thoại Sort, sắp xép ưu tiên 1 là “Tên nước” sau đó mới sắp xếp “Tên chỉ tiêu” Các bước sắp xếp B1. Đặt ô hiện hành vào nơi nào đó trong danh sách và chọn thực đơn Data Ỉ Sort B2. Chọn “Tên nước” tại Sort by và chọn Ascending để sắp xếp tăng dần. B3. Chọn “Tên chỉ tiêu” tại Then by và chọn Ascending để sắp xếp tăng dần. B4. Chọn Header row do danh sách có dòng tiêu đề. B5. Nhấp nút OK để sắp xếp. Sắp xếp theo dòng (sử dụng worksheet Row_sort trong tập tin bai2-1.xls) Hình 2.12. Tốc độ tăng trưởng GDP hàng năm của Việt Nam Hình 2.13. Tốc độ tăng trưởng GDP hàng năm của Việt Nam sau khi đã sắp xếp theo thứ tự giảm dần Trần Thanh Phong 12 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính Hình 2.14. Hộp thoại chọn lựa chế độ sắp xếp theo cột hoặc dòng Hình 2.15. Hộp thoại Sort, chọn sắp xếp dòng thứ 3 theo thứ tự giảm dần B1. Đặt ô hiện hành vào nơi nào đó trong danh sách và chọn thực đơn Data Ỉ Sort B2. Nhấp nút Option và chọn Sort left to right từ hộp hội thoại mới xuất hiện. Nhấp nút OK để trở về hộp thoại Sort. B3. Chọn “Row 3” tại Sort by và chọn Descending để sắp xếp giảm dần. B4. Nhấp nút OK để hoàn tất việc sắp xếp. 2.4. Lọc dữ liệu từ danh sách bằng Auto Filter Đôi khi chúng ta chỉ cần lấy ra một số bản ghi trong một danh sách, Excel hỗ trợ chức năng lọc dữ liệu từ danh sách theo một hoặc nhiều điều kiện lọc và chỉ các bản ghi thõa các điều kiện thì mới được hiển thị. (sử dụng worksheet ASEAN trong tập tin bai2-1.xls) Trần Thanh Phong 13 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính Hình 2.16. Danh sách trước khi lọc dữ liệu về nước Việt Nam Hình 2.17. Danh sách sau khi lọc dữ liệu về nước Việt Nam B1. Đặt ô hiện hành vào nơi nào đó trong danh sách và chọn Data Ỉ Filter Ỉ AutoFilter từ thực đơn B2. Tại cột “tên nước”, chọn Vietnam từ mũi tên hướng xuống tại . Danh sách lúc này sẽ chỉ hiển thị các thông tin về nước Việt Nam. B3. Để hiễn thị lại tất cả dữ liệu thì chọn Data Ỉ Filter Ỉ Show All hoặc chọn (All) từ . B4. Thoát khỏi chức năng AutoFilter vào chọn Data Ỉ Filter Ỉ AutoFilter từ thực đơn. Các lựa chọn của AutoFilter Lựa chọn Giải thích (All) Hiển thị tất cả bản ghi (dòng) của danh sách (Top 10 ) Aùp dụng cho các trường khác kiểu Text. Hiển thị các bản ghi ở cận trên hay cận dưới theo lựa chọn dưới hai hình thức bản ghi hay phần Trần Thanh Phong 14 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính trăm. Ví dụ: hiển thị 10 dòng có giá trị lớn nhất trong danh sách hoặc hiển thị 10% số dòng có giá trị lớn nhất. (Custom ) Aùp dụng trong trường hợp cần hai điều kiện trong một cột hoặc dùng các phép toán so sánh. (Blanks) Hiển thị các dòng mà ô tại cột ra điều kiện lọc là rỗng (NotBlanks) Hiển thị các dòng mà ô tại cột ra điều kiện lọc khác rỗng Sử dụng (Top 10 ) Hình 2.18. Chọn 10 dòng tại cột 1990 có giá trị lớn nhất bằng (Top 10 ) Hình 2.19. Kết quả lọc dùng (Top 10 ) dạng Items Ỉ trả vế 10 dòng có giá trị lớn nhất tại cột năm 1990. Trần Thanh Phong 15 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính Hình 2.19. Đổi Items sang Percent thì kết quả chỉ còn 4 dòng (vì tổng số dòng trong danh sách là 47 dòng) B1. Đặt ô hiện hành vào nơi nào đó trong danh sách và chọn Data Ỉ Filter Ỉ AutoFilter từ thực đơn (nếu chưa chọn). B2. Chọn (Top 10 ) từ mũi tên hướng xuống tại . B3. Chọn Top (cận trên), chọn 10, và chọn Items (dòng, nếu chọn Percent thì hiển thị 10% số dòng trong danh sách) như hình trên. B4. Nhấp OK để hiển thị kết quả (xem các hình 2.17, 2.18 và 2.19) Sử dụng (Custom ) Hình 2.20. Dùng (Custom ) để lọc thông tin về hai nước Vietnam và Singapore B1. Đặt ô hiện hành vào nơi nào đó trong danh sách và chọn Data Ỉ Filter Ỉ AutoFilter từ thực đơn (nếu chưa chọn). B2. Chọn (Custom ) từ cột . B3. Khai báo các thông tin như hình 2.20 B4. Nhấp OK để hiển thị kết quả. Trần Thanh Phong 16 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính 2.5. Lọc dữ liệu nâng cao bằng Advance Filter Advanced Filter là chức năng mạnh mẽ và linh hoạt trong việc lọc dữ liệu từ danh sách trong bảng tính. Advanced Filter giúp tạo các điều kiện lọc phức tạp và xuất kết quả lọc đến một nơi chỉ định trong bảng tính. Để sử dụng Advanced Filter trước tiên ta phải tạo vùng điều kiện tại một vùng trống nào đó trong bảng tính. Ta sao chép dòng tiêu đề của danh sách và dán vào nơi làm vùng điều kiện, sau đó đặt các điều kiện bên dưới các tiêu đề này (Xem hình 2.21). Hình 2.21. Lập vùng điều kiện và đặt các điều kiện lọc cho danh sách Hình 2.22. Khai báo trong Advanced Filter Trần Thanh Phong 17 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính Hình 2.23. Kết quả lọc dữ liệu cho hiển thị ngay trong danh sách B1. Chèn danh sách xuống dưới vài dòng, sau đó sao chép dòng tiêu đề của danh sách lên trên và đặt các điều kiện như hình 2.21. B2. Đặt ô hiện hành vào nơi nào đó trong danh sách và chọn Data Ỉ Filter Ỉ Advanced Filter từ thực đơn. B3. Khai báo danh sách, vùng điều kiện và các tùy chọn như hình 2.22. B4. Nhấp OK để hiển thị kết quả lọc. Dùng kết quả của một công thức vào làm điều kiện lọc Hình 2.24. Advanced Filter có dùng công thức trong điều kiện. Trần Thanh Phong 18 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính Hình 2.25. Khai báo trong Advanced Filter Hình 2.26. Kết quả lọc B1. Đặt điều kiện lọc tại C2 là “=C8>Average($C$8:$C$54)” để lọc các dòng mà các ô tại cột năm 1990 có giá trị lớn hơn giá trị trung bình của tất cả các giá trị của cột. Trần Thanh Phong 19 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính B2. Đặt ô hiện hành vào nơi nào đó trong danh sách và chọn Data Ỉ Filter Ỉ Advanced Filter từ thực đơn. B3. Khai báo danh sách, vùng điều kiện và các tùy chọn như hình 2.25. B4. Nhấp OK để hiển thị kết quả lọc. Các toán tử so sánh và ký tự thay thế Ký hiệu Giải thích và ví dụ = Bằng Lớn hơn = Lớn hơn hoặc bằng <= Nhỏ hơn hoặc bằng * Đại diện cho nhiều ký tự trước và sau ký tự kèm theo Ví dụ: *east thì các từ “Northeast”, “Southeast”, thõa điều kiện ? Đại diện cho ký tự tại vị trí đặt nó Ví dụ: sm?th thì các từ “smith”, “smyth”, thõa điều kiện ~ Nếu sau nó là các ký tự ? * hoặc ~ mà ta cần lọc. Ví dụ: Fulbright~? thì từ Fulbright? thõa điều kiện 2.6. Dùng Data Validation để kiểm soát nhập liệu Chức năng Data Validation giúp người sử dụng nhập chính xác dữ liệu vào bảng tính. Chức năng nằy sẽ kiểm tra kiểu dữ liệu, giá trị nhập vào một ô nào đó trong bảng tính và cảnh báo người dùng khi nhập sai. Tạo danh sách xổ xuống (drop-down list) để chỉ cho phép người dùng chọn từ danh sách này. Trong phần này hướng dẫn tạo drop-down list cho cột “Tên nước”. Hình 2.27. Tạo danh sách Tên nước Trần Thanh Phong 20 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính Hình 2.28. Chọn các điều kiện trong hộp thoại Data Validation B1. Lập danh sách tên các nước tại một vùng trống nào đó trong bảng tính. B2. Chọn cả cột B, chọn Data Ỉ Validation từ thực đơn. B3. Tại Allow chọn List, tại Source chọn vùng địa chỉ chứa danh sách Tên nước B4. Nhấp OK để hoàn tất quá trình tạo kiểm tra điều kiện cho cột “Tên nước” Tương tự như trên, ta có thể tạo kiểm soát nhập liệu các tất cả các ô trong bảng tính. Mỗi cột trong danh sách có thể chứa các giá trị khác nhau: dạng số, ngày tháng, do vậy cần lựa chọn tại Allow trong hộp thoại Data Validation cho phù hợp với kiểu giá trị cần nhập. 2.7. Bài tập thực hành Các bài tập sử dụng tập tin bai2-2.xls Câu 1. Sắp xếp (Sort) 1. Sắp xếp cột Tên nước theo thứ tự tăng dần (AỈZ) 2. Sắp xếp nhiều cột Ưu tiên 1: Tên chỉ tiêu theo thứ tự tăng dần (AỈZ) Ưu tiên 2: 2000 theo thứ tự tăng dần (ZỈA) 3. Sắp xếp nhiều cột Ưu tiên 1: Tên nước theo thứ tự tăng dần (AỈZ) Ưu tiên 2: 1995 theo thứ tự tăng dần (ZỈA) Trần Thanh Phong 21 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 2. Tổ chức dữ liệu trong bảng tính Câu 2. Lọc dữ liệu bằng AutoFilter 1. Lọc tất cả dữ liệu về nước Việt nam 2. Lọc tất cả dữ liệu của 2 nước Việt Nam và Lào 3. Lọc tất cả các dòng trong danh sách có giá trị ở cột 1990 nằm trong khoảng từ 20 triệu đến 120 triệu và giá trị ở cột 2000 nằm trong khoảng từ 50 triệu đến 150 triệu. 4. Lọc tất cả các dòng trong danh sách với các điều kiện sau: Cột Tên nước lấy các dòng có ký tự bắt đầu là “v” hoặc ký tự sau cùng là “a” Cột 1995 lấy các dòng có giá trị lớn hơn hoặc bằng 1 triệu Cột 2000 lấy các dòng có giá trị lớn hơn 10 triệu và nhỏ hơn 100 triệu Câu 3. Lọc dữ liệu bằng AdvancedFilter 1. Lọc tất cả dữ liệu về nước Malaysia, Indonesia và Brunei 2. Lọc tất cả các dòng trong danh sách chứa dữ liệu về nước Thailand và có giá trị ở cột 1990 nằm trong khoảng từ 10 triệu đến 50 triệu. 3. Lọc tất cả các dòng mà tên ở cột Tên chỉ tiêu có đoạn văn bản là female và giá trị tại cột 2000 trừ cho giá trị ở cột 1990 là lớn hơn 500.000. Trần Thanh Phong 22 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable BÀI 3. TỔNG HỢP DỮ LIỆU VÀ PIVOTTABLE Công cụ PivotTable rất tiện lợi trong việc tổng hợp, tóm tắt và phân tích dữ liệu từ các danh sách. Bài này sẽ minh họa cách tạo, hiệu chỉnh, định dạng một PivotTable. (Sử dụng tập tin bai3-1.xls) 3.1. Tạo Pivort Table Hình 3.1. Danh sách dữ liệu Hình 3.2. PivotTable tổng hợp số liệu lấy Tổng giá trị xuất khẩu theo quí và năm Trần Thanh Phong 23 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable Các bước tạo PivotTable B1. Chọn từ thực đơn Data Ỉ PivotTable and PivotChart Wizard để mở trình hướng dẫn từng bước tạo bảng tổng hợp. B2. Chọn nguồn dữ liệu: có 4 loại (xem hình 3.3) Hình 3.3. Chọn nguồn dữ liệu cho PivotTable và chọn loại báo cáo • Microsoft Excel list or database: Nguồn dữ liệu là một danh sách trong Excel • External data source: Nguồn dữ liệu ở bên ngoài Excel, thông thường là các cơ sở dữ liệu chứa trong ODBC của máy cục bộ. • Multiple consolidation ranges: Nguồn dữ liệu là nhiều danh sách tại một hoặc nhiều worksheet trong Excel. • Chọn nguồn dữ liệu từ một PivotTable hay một PivotChart khác B3. Trong bài này minh họa chọn nguồn dữ liệu là Microsoft Excel list or database. Nhấp nút Next B4. Chọn vùng địa chỉ chứa danh sách cần tổng hợp và nhấp nút Next Hình 3.4. Chọn vùng địa chỉ chứa danh sách Trần Thanh Phong 24 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable B5. Chọn nơi chứa PivotTable là New worksheet (tạo worksheet mới chứa kết quả), sau đó nhấp nút Finish. Hình 3.5. Nơi lưu trữ PivotTable B6. Kéo thả các trường từ danh sách PivotTable Field List vào vị trí phù hợp Ỉ Kéo thả “Tháng” vào vùng “Row Fields” Ỉ Kéo thả United States, Canada, Australia, Japan và New Zealand vào vùng Data Items. Hình 3.6. Giao diện của một PivotTable chưa có thông tin B7. Nhóm “Month” thành các quí và năm: chọn ô “Month” (A3), vào thực đơn Data Ỉ Group and Outline Ỉ Group Chọn cả ba loại là Months, Quarters và Years. Nhấp OK để chấp nhận. (xem hình 3.7) Trần Thanh Phong 25 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable Hình 3.7. Nhóm các tháng thành quí và năm B8. Chọn định dạng cho PivotTable: chọn PivotTable và chọn Format Ỉ AutoFormat từ thanh thực đơn. Chọn kiểu Reprot 4 và nhấp nút OK. (Gọi định dạng từ thanh công cụ PivotTable: View Ỉ Toolbars Ỉ PivotTable Ỉ Format Report) Hình 3.8. Chọn kiểu định dạng PivotTable Trần Thanh Phong 26 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable 3.2. Hiệu chỉnh PivotTable Phần trước đã minh họa các bước tạo một PivotTable. Phần này sẽ trình bày cách hiệu chỉnh bảng dữ liệu tổng hợp để có được các thông tin theo yêu cầu. Hình 3.9. PivotTable: Trung bình Tổng giá trị xuất khẩu theo quí và năm B1. Nhấp phải chuột lên trường “United States” và chọn Field Setting Hình 3.10. Hiệu chỉnh cách tính cho các trường dữ liệu Trần Thanh Phong 27 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable B2. Chọn Average từ danh sách Summarize by. Nhấp OK để chấp nhận Hình 3.11. Chọn cách tính toán cho một trường B3. Thực hiện lại hai bước trên cho các trường cần thay đổi cách tính. Thanh công cụ PivotTable Hình 3.12. Thanh công cụ PivotTable 3.3. Điều khiển việc hiển thị thông tin Đôi khi chúng ta không cần hiển thị tất cả thông tin trong bảng báo cáo. PivotTable cũng có hỗ trợ chức năng che giấu các thông tin không cần hiển thị. B1. Chọn mũi tên hướng xuống tại trường “Years” B2. Bỏ chọn các năm không cần hiển thị (Ví dụ: bỏ chọn năm 2003). Nhấp nút OK để chấp nhận. (xem hình 3.13). B3. Làm tương tự cho các trường khác trong PivotTable Trần Thanh Phong 28 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable Hình 3.13. Ẩn thông tin của năm 2003 3.4. Tạo PivotChart Các bước tạo PivotChart từ một PivotTable B1. Chọn một ô trong PivotTable, sau đó chọn Chart Wizard từ thanh công cụ PivotTable (xem hình 3.14). Hình 3.14. Chọn Chart Wizard từ thanh công cụ B2. Chọn Chart Ỉ Chart Type. Chọn kiểu đồ thị như hình 3.15 bên dưới. Sau đó nhấp nút OK để chấp nhận. Trần Thanh Phong 29 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable Hình 3.15. Chọn kiểu đồ thị B3. Định dạng đồ thị theo yêu cầu như hình 3.16. Hình 3.16. PivotChart B4. Ẩn các nút lệnh của PivotChart: nhấp phải chuột lên bất kỳ nút lệnh nào (Ví dụ: Years) và chọn Hide PivotChart Field Button. Trần Thanh Phong 30 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable 3.5. Sử dụng subtotals Chúng ta đã học cách tổng hợp dữ liệu để rút ra các thông tin hữu ích bằng PivotTable, trong phần này trình bày một cách nhanh hơn và dễ hơn trong việc tổng hợp và tóm tắt dữ liệu đó là chức năng subtotals của Excel. Hình 3.17. Danh sách cần tổng hợp thành tiền theo người mua B1. Sắp xếp danh sách trước khi dùng Subtotals. Sắp xếp cột “Người mua” B2. Chọn danh sách, sau đó chọn Data Ỉ SubTotals Hình 3.18. Hộp thoại Subtotal Trần Thanh Phong 31 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable B3. Chọn “Người mua” tại At each change in, chọn Sum tại Use function và chọn “Thành tiền” tại Add subtotal to (xem hình 3.18). B4. Nhấp nút OK để chấp nhận. Hình 3.19. Kết quả sau khi dùng Subtotals Để bỏ Subtotals: Data Ỉ Subtotals Ỉ Remove All Ỉ OK 3.6. Dùng các hàm dữ liệu Các hàm dữ liệu thực hiện việc tính toán cho các dòng thõa điều kiện. Tất cả hàm dữ liệu sử dụng cùng cú pháp: = Function(database, field, criteria) Trong đó: Database: là vùng địa chỉ của danh sách hoặc cơ sở dữ liệu Field: Xác định cột dùng trong hàm. Ta tham chiếm các cột bằng nhãn và đặt trong dấu ngoặc kép “”. Cũng có thể tham chiếu đến cột thông qua số thứ tự cột của nó trong danh sách. Criteria: Tham khảo đến địa chỉ các ô xác định điều kiện của hàm. Các hàm dữ liệu: Hàm Giải thích DAVERAGE Trả về trung bình của các giá trị trong cột của danh sách thoã điều kiện. DCOUNT Trả về số ô trong cột của danh sách chứa giá trị thõa điều kiện. Nếu bỏ trống tham số tại “field” thì đếm tất cả ô của Trần Thanh Phong 32 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable danh sách. DCOUNTA Trả về số ô khác rỗng trong cột của danh sách chứa giá trị thõa điều kiện. Nếu bỏ trống tham số tại “field” thì đếm tất cả ô của danh sách. DGET Trả về giá trị của ô trong cột thuột danh sách thõa điều kiện. DMAX Trả về giá trị lớn nhất trong cột của danh sách thõa điều kiện DMIN Trả về giá trị nhỏ nhất trong cột của danh sách thõa điều kiện DPRODUCT Nhân các giá trị trong cột của danh sách thõa điều kiện DSTDEV Ước lượng độ lệch chuẩn tổng thể dựa vào mẫu các giá trị trong cột của danh sách thõa điều kiện. DSTDEVP Ước lượng độ lệch chuẩn tổng thể dựa vào tập hợp chính các giá trị trong cột của danh sách thõa điều kiện. DSUM Tổng các giá trị trong cột của danh sách thõa điều kiện DVAR Ước lượng phương sai tổng thể dựa vào mẫu các giá trị trong cột của danh sách thõa điều kiện. DVARP Ước lượng phương sai tổng thể dựa vào tập hợp chính các giá trị trong cột của danh sách thõa điều kiện. GETPIVOTDATA Trả về dữ liệu chứa trong PivotTable Hình 3.20. Các ví dụ minh họa Trần Thanh Phong 33 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable 3.7. Bài tập thực hành Các bài tập sử dụng tập tin bai3-2.xls kèm theo. Trong tập tin có sử dụng hàm WEEKNUM(serial_num,return_type) để trả về số thứ tự tuần trong năm của ngày khai báo trong serial_num. Return_type= 1 : tuần bắt đầu là ngày Chủ nhật, Return_type=2 : tuần bắt đầu là ngày Thứ hai. Câu 1. Tạo một PivotTable như hình sau: Câu 2. Tạo một PivotChart như hình sau: Câu 3. Dùng các hàm dữ liệu: a. Tính tổng số giờ làm việc của công nhân A từ ngày 16/05/1996 đến 20/05/1996. b. Đếm các ô của cột Giờ thoã các điều kiện: giờ làm việc của công nhân B, trong tuần thứ 20, kể cả các ô trống. c. Tìm số giờ làm việc cao nhất của công nhân C từ ngày 17/05/1996 đến ngày 21/05/1996 thuộc tuần thứ 21. Trần Thanh Phong 34 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 3.Tổng hợp dữ liệu và PivotTable Câu 4. Tạo SubTotal như hình sau: Trần Thanh Phong 35 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình BÀI 4. BÀI TOÁN ĐIỂM HOÀ VỐN 4.1. Giới thiệu Trong hoạt động sản xuất kinh doanh, câu hỏi thường đặt ra là sản xuất hay bán bao nhiêu sản phẩm để cân bằng giữa thu nhập và chi phí, nghĩa là khi đó doanh nghiệp được hòa vốn. Dưới đây là tóm tắt lý thuyết: Số liệu cần có F: định phí v: biến phí đơn vị r: giá bán đơn vị Biến Q: sản lượng Biến trung gian TC: Tổng chi phí DT: Tổng thu nhập Hàm mục tiêu LN: Lợi nhuận Điểm hoà vốn là điểm mà tại đó lợi nhuận bằng 0 Các phương trình quan hệ LN = DT – TC DT = r*Q TC = F + v*Q Công thức tính điểm hoà vốn LN = DT – TC LN = r*Q – (F + v*Q) Điểm hoà vốn thì LN = 0 Ỵ r*Q – (F + v*Q) = 0 F Ỵ Điểm hoà vốn Q = BE r − v Để giải bài toán điểm hòa vốn ta cần: xác định các dữ liệu, các biến, hàm mục tiêu và các mối quan hệ giữa các biến. Trần Thanh Phong 36 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình 4.2. Bài toán minh họa Bài toán có các số liệu tóm tắt như sau: Định phí là 5triệu đồng, giá bán sản phẩm là 14.000 đồng và chi phí để sản xuất một đơn vị sản phẩm là 6.000 đồng. Xác định điểm hòa vốn và vẽ đồ thị. Bài này sẽ giới thiệu hai cách giải (dùng tập tin bai4-1.xls). Tính điểm hòa vốn Cách 1. Dùng công thức tính điểm hòa vốn F Công thức tính điểm hoà vốn là Q = BE r − v Ta có: F = 5.000.000 VND r = 14.000 VND v = 6.000 VND 5.000.000 Thay vào công thức Q = = 625 đơn vị sản phẩm. BE 14.000 − 6.000 Cách 2. Dùng Goal Seek B1. Lập bài toán trên Excel: nhập các biến, thiết lập hàm mục tiêu và các quan hệ như hình 4.1. Hình 4.1. Lập bài toán trên bảng tính B2. Chọn ô có địa chỉ B12, sau đó chọn Tools Ỉ Goal Seek Khai báo các thông số như hình 4.2 bên dưới. Trần Thanh Phong 37 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình Hình 4.2. Khai báo các thông số cho Goal Seek B3. Nhấp nút OK để chạy Goal Seek. Kết quả cần tìm sẽ hiển thị tại ô B7 (sản lượng) và giá trị của hàm mục tiêu lợi nhuận tại B12 lúc này bằng 0. Vẽ đồ thị điểm hòa vốn B1. Lập bảng số liệu cần thiết cho vẽ đồ thị. Ỉ Tự nhập vào các giá trị trong vùng B15:H15 Ỉ Lập công thức cho các ô B16:H20 theo các quan hệ trình bày ở phần tóm tắt lý thuyết và xem phần ghi chú trong hình 4.3. Hình 4.3. Lập bảng số liệu cho đồ thị B2. Chọn vùng địa chỉ A15:H20, sau đó chọn Insert Ỉ Chart . Chọn kiểu đồ thị Scatter như hình 4.4. Nhấp nút Next qua bước tiếp theo. Trần Thanh Phong 38 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình Hình 4.4. Chọn kiểu đồ thị B3. Chọn vùng dữ liệu cho các đồ thị, sau đó nhấp nút Next. Hình 4.5. Chọn vùng dữ liệu cho các đồ thị B4. Thiết lập các tùy chọn cho đồ thị: tên đồ thị, tên các trục, đường kẽ ngang/ dọc, chú thích, Nhấp nút Next qua bước tiếp theo. Trần Thanh Phong 39 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình Hình 4.6. Thiết lập các tùy chọn cho đồ thị B5. Chọn nơi đặt đồ thị. Hình 4.7. Chọn nơi đặt đồ thị B6. Nhấp nút Finish để hoàn tất. Sau đó hiệu chỉnh đồ thị theo yêu cầu. Hình 4.8. Đồ thị điểm hòa vốn Trần Thanh Phong 40 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình BÀI 5. GIẢI PHƯƠNG TRÌNH VÀ HỆ PHƯƠNG TRÌNH Nguyên tắc chung để giải phương trình, hệ phương trình trên bảng tính là phải xác định các biến, các hàm, rồi lập mô hình và sau đó dùng Goal Seek hoặc Solver để dò tìm nghiệm. (sử dụng tập tin bai5-1.xls) 5.1. Giải phương trình Giải phương trình bậc hai x2 + 5x – 6 = 0 B1. Xác định biến, hàm mục tiêu và lập mô hình trên bảng tính Ỉ Tại ô A6 và A7 nhập các giá trị khởi động bất kỳ cho biến x Ỉ Tại ô B6 và B7 nhập các công thức theo phương trình đề cho để tính f(x) Hình 5.1. Lập mô hình trên bảng tính B2. Chọn ô B6, sau đó chọn Tools ỈGoal Seek và khai báo như hình 5.2. Nhấp nút OK để chạy Goal Seek. Hình 5.2. Khai báo cho Goal Seek tìm nghiệm thứ nhất x1 B3. Sau quá trình chạy Goal Seek thì hộp thoại thông báo xuất hiện. Nhấp OK để chấp nhận kết quả hoặc nhấp Cancel để hủy kết quả chạy Goal Seek. Trần Thanh Phong 41 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình Hình 5.3. Kết quả chạy Goal Seek lần thứ nhất và tìm được nghiệm x1=1 B4. Phương trình bậc hai có tối đa hai nghiệm, do vậy ta cần chạy Goal Seek lần nữa để tìm nghiệm thứ hai x2. Ghi chú: Để tránh lần chạy Goal Seek thứ hai trả về cùng kết quả với lần chạy thứ nhất, ta hãy cho giá trị khởi động x2 một con số âm rất nhỏ (Ví dụ: -10000) rồi chạy Goal Seek. Nếu kết quả trùng với lần chạy đầu tiên thì hãy cho lại giá trị khởi động x2 một con số dương lớn (Ví dụ: 10000) rồi chạy lại Goal Seek. B5. Cho lại giá trị khởi động tại ô A7 là -10000, chọn ô B7 và chọn Tools Ỉ Goal Seek. Khai báo như hình 5.4. Hình 5.4. Khai báo cho Goal Seek tìm nghiệm thứ nhất x2 B6. Sau quá trình chạy Goal Seek thì hộp thoại thông báo xuất hiện. Nhấp OK để chấp nhận kết quả hoặc nhấp Cancel để hủy kết quả chạy Goal Seek. Hình 5.5. Kết quả phương trình bậc II Trần Thanh Phong 42 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình 5.2. Giải hệ phương trình Giải hệ phương trình sau: x + 2y + 3z = 25 (1) 2x + y + z = 14 (2) x + 4y + 2z = 10 (3) Cách 1. Giải hệ phương trình dùng Solver B1. Xác định các biến, các hàm mục tiêu và lập mô hình trên bảng tính Ỉ Các ô trong mảng A26:C28 nhập các hệ số của các phương trình (mỗi phương trình nhập một dòng). Ỉ Các ô D26, D27, D28 lần lượt chứa giá trị khởi động của các biến x, y, z Ỉ Các ô F26, F27, F28 lần lượt chứa các giá trị ở vế phải của các phương trình (1), (2) và (3). Ỉ Các ô E26, E27, E28 được tính bằng cách nhân các hệ số của phương trình với các giá trị khởi động của x, y, z (xem công thức minh họa trong hình 5.6). Hình 5.6. Lập mô hình bài toán trên bảng tính Ghi chú: Cách khác để tính nhanh Vế trái của các phương trình là dùng kết hợp hàm Sumproduct (array1, array2) và hàm Transpose (array). Cách làm như sau: 1. Chọn 3 ô E26 và nhập vào công thức sau: =SUMPRODUCT(A26:C26,TRANSPOSE($D$26:$D$28)) 2. Sao chép công thức cho 2 ô còn lại E27 và E28. B2. Vào thực đơn Tools Ỉ Solver. Nếu chưa thấy chức năng Solver trên thực đơn Tools thì ta cần bổsung chức năng này vào Excel. Các bước để bổ sung chức năng Solver cho Excel: 1. Vào thực đơn Tools Ỉ Add-Ins Trần Thanh Phong 43 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình Hình 5.7. Hộp thoại Add-Ins chứa các chức năng mở rộng của Excel 2. Chọn Solver Add-in và chọn OK để chấp nhận. Sau khi thực hiện lệnh Tools Ỉ Solver, hộp thoại Solver xuất hiện. Ta cần khai báo các thông số cho Solver như sau: Ỉ Đưa địa chỉ D26:D28 vào By Changing Cells Ỉ Đưa các ràng buộc vào Subject to the Constraints: 1. Nhấp nút Add và khai báo như hình sau Hình 5.9. Thêm ràng buộc 2. Nhấp nút OK để hoàn tất. Nếu bài toán cần nhiều ràng buộc hơn thì thực hiện lại hai bước trên để nhập thêm các ràng buộc khác. Trần Thanh Phong 44 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình Hình 5.10. Khai báo thông số cho Solver B3. Nhấp nút Solve chạy tìm lời giải. Hộp thông báo sau sẽ xuất hiện: Hình 5.11. Chọn kiểu báo cáo B4. Chọn Keep Solver Solution để lưu kết quả trên bảng tính. Chọn Restore Original Values để hủy kết quả Solver vừa tìm được và trả các biến về tình trạng ban đầu. Chọn Save Scenario để lưu kết quả vừa tìm được thành một tình huống để có xem lại sau này. Ngoài ra còn có 3 loại báo cáo là Answer, Sensitivity và Limits. B5. Chọn OK để hoàn tất quá trình chạy Solver. Hình 5.12. Các nghiệm hệ phương trình Cách 2. Giải hệ phương trình bằng phương pháp ma trận Hệ phương trình trên là tương đương với phương trình ma trận sau: Trần Thanh Phong 45 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình A * X = B 1 2 3 x 25 Ỵ Nghiệm của hệ là X = A-1*B 2 1 1 * y = 14 1 4 2 z 10 B1. Xác định các biến, các hàm mục tiêu và lập mô hình trên bảng tính Ỉ Các ô B75:D77 nhập vào hệ số của các phương trình (1), (2) và (3) Ỉ Các ô F75, F76, F77 là nhãn các nghiệm x, y, z Ỉ Các ô H75, H76, H77 là các con số ở vế phải của các phương trình. Hình 5.13. Lập mô hình bài toán trên bảng tính -1 B2. Tìm ma trận nghịch đảo của ma trận hệ số A Ỵ Tìm A Ỉ Chọn vùng địa chỉ B80:D82 Ỉ Nhập vào công thức =Minverse(B75:D77) để nghịch đảo ma trận Ỉ Nhấn tổ hợp phím Ctrl + Shift + Enter để thực hiện phép tính Hình 5.14. Tính ma trận nghịch đảo B3. Tìm nghiệm hệ phương trình Ỉ Chọn vùng địa chỉ B85:B87 Ỉ Nhập vào công thức =MMULT(B80:D82,H75:H77) Ỉ Nhấn tổ hợp phím Ctrl + Shift + Enter để thực hiện phép tính Hình 5.15. Nghiệm hệ phương trình Trần Thanh Phong 46 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình Lưu ý: Việc tính toán trên dãy số liệu (array) có một số tính chất sau: 1. Khi nhập, xóa, chỉnh sửa công thức phải thực hiện trên toàn bộ dãy, do vậy cần chọn cả dãy trước khi thực hiện nhập, xóa hay chỉnh sửa. 2. Nhấn phím F2 để vào chế độ chỉnh sửa 3. Nhấn tổ hợp phím Ctrl + Shift + Enter khi hoàn tất. 5.3. Sử dụng Solver Để sử dụng tốt Solver ta cần nắm vững các yêu cầu thông số cần phải khai báo cho Solver: Hình 5.16. Hộp thoại Solver • Set Target Cell: Nơi đây ta cần nhập vào địa chỉ của hàm mục tiêu. • Equal To: Hàm mục tiêu muốn đạt tới Max, Min hay Value of (bằng một giá trị mong muốn nào đó thì nhập giá trị vào.) • By Changing Cell: Nhập vào địa chỉ chứa các biến của bài toán cần giải. • Subject to the constraints: Nhập vào các ràng buộc của bài toán. Cách làm của Solver là thay đổi giá trị các biến tại By Changing Cell đến lúc nào đó làm cho giá trị hàm mục tiêu tại Set Target Cell đạt một giá trị qui định tại Equal To (Max, Min hoặc Value of) và đồng thời phải thõa mãm tập các ràng buộc tại Subject to the constraints. Thiết lập các thuộc tính cho Solver ta nhấp chuột vào nút Options, hộp thoại Solver Options xuất hiện: Trần Thanh Phong 47 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình Hình 5.17. Thiết lập thông số cho Solver: Chế độ mặc định Tham số Giải thích Max Time Thời gian tối đa để giải bài toán, giá trị mặc định là 100 giây dùng cho các bài toán đơn giản. Thời gian tối đa có thể nhập vào là 32.767 giây. Iterations Số lần lặp tối đa để giải bài toán, giá trị mặc định là 100 giây dùng cho các bài toán đơn giản. Số lần lặp tối đa có thể nhập vào là 32.767 lần. Precision Độ chính xác của bài toán. Tại đây có thể nhập vào các số trong khoảng 0 và 1. Số càng gần 0 thì độ chính xác càng cao. Giá trị này điều chỉnh độ sai số cho tập ràng buộc. Giá trị mặc định là 1 phần triệu. Tolerance Chỉ áp dụng đối với bài toán có ràng buộc nguyên. Nhập vào sai số có thể chấp nhận được, sai số càng lớn thì tốc độ giải càng nhanh. Giá trịmặc định là 5% Convergence Chỉ áp dụng cho các bài toán không tuyến tính (nonlinear). Tại đây nhập vào các số trong khoảng 0 và 1. Giá trị càng gần 0 thì độ chính xác cao hơn và cần thời gian nhiều hơn. Assume Chọn để tăng tốc độ giải bài toán khi tất cả quan hệ trong mô Linear Model hình là tuyến tính. Assume Chọn tùy chọn này nếu muốn Solver giả định là tất cả các biến là Non-Negative không âm. Use Automatic Chọn khi bài toán mà các dữ liệu nhập và xuất có sự khác biệt Scaling lớn. Ví dụ bài toán tối đa % lợi nhuận trên hàm triệu USD vốn đầu tư. Show Iteration Chọn nếu muốn Solver tạm dừng lại và hiển thị kết quả sau mỗi Results lần lặp. Trần Thanh Phong 48 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình Estimates Chọn phương pháp cho Solver dùng để ước lượng các biến: Tangent: Sử dụng cách xấp xỉ tuyến tính bậc nhất. Quadratic: Sử dụng cách xấp xỉ bậc bốn Derivatives Chọn cách để ước lượng hàm mục tiêu và các ràng buộc Forward: được dùng rất phổ biến hơn, khi đó các giá trị của ràng buộc biến đổi chậm. Central: Dùng khi các giá trị của ràng buộc biến đổi nhanh và được dùng khi Solver báo không thể cải tiến kết quả thu được. Search Qui định giải thuật tìm kiếm kết quả cho bài toán: Newton: là phương pháp mặc định, nó sử dụng nhiều bộ nhớ hơn và có số lần lặp ít hơn phương pháp Conjugate. Conjugate: Cần ít bộ nhớ hơn phương pháp Newton nhưng số lần lặp thì nhiều hơn. Dùng phương pháp này cho các bài toán phức tạp và bộ nhớ thì có giới hạn. Save Model Chọn nơi lưu mô hình bài toán. Được dùng khi cần lưu nhiều hơn một mô hình trên một worksheet. Mô hình đầu tiên đã được lưu tự động. Load Model Xác định vùng địa chỉ của mô hình bài toán cần nạp vào 5.4. Ma trận Ma trận được đặt trong cặp móc vuông: Kích thước ma trận được xác định theo số dòng vào số cột của ma trận, ma trận n x m đọc là n dòng và m cột. Hai ma trận chỉ nhân được với nhau khi số dòng cột của ma trận đứng trước bằng với số dòng của ma trận đứng sau. Ví dụ ma trận có kích thước n x p thì có thể nhân với ma trận có kích thước p x m. Dưới đây là công thức nhân hai ma trận đặc biệt có kích thước 1 x n và n x 1: ⎡b1 ⎤ ⎢b ⎥ ⎢ 2 ⎥ []a1 a2 L aa . = a1b1 + a2b2 + + anbn ⎢ M ⎥ ⎢ ⎥ ⎣bn ⎦ Trần Thanh Phong 49 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình Công thức tổng quát xác định giá trị của phần tử cij trong ma trận kết quả: n cij = ∑ aik bkj (i là số dòng; j là số cột) k=1 Ví dụ: Nhân hai ma trận sau: ⎡ 1 3⎤ ⎡ 2 3 −1⎤ ⎢ ⎥ A = ⎢ ⎥ ; B = ⎢ 2 0⎥ ⎣− 2 1 2 ⎦ ⎢ ⎥ ⎣−1 2⎦ ⎡ ⎡ 1 ⎤ ⎡3⎤⎤ ⎢ ⎥ 2 3 −1.⎢ 2 ⎥ 2 3 −1.⎢0⎥ ⎡ 1 3⎤ ⎢[][]⎢ ⎥ ⎢ ⎥⎥ ⎡ 2 3 −1⎤ ⎢ ⎥ ⎢ ⎣⎢−1⎦⎥ ⎣⎢2⎦⎥⎥ ⎡ 9 4 ⎤ 2 0 = ⎢ ⎥ = ⎢ ⎥ ⎢ ⎥ 1 3 ⎢ ⎥ ⎣− 2 1 2 ⎦ ⎢ ⎥ ⎢ ⎡ ⎤ ⎡ ⎤⎥ ⎣− 2 − 2⎦ ⎣−1 2⎦ ⎢[][]− 2 1 2 .⎢ 2 ⎥ − 2 1 2 .⎢0⎥⎥ ⎢ ⎢ ⎥ ⎢ ⎥⎥ ⎣⎢ ⎣⎢−1⎦⎥ ⎣⎢2⎦⎥⎦⎥ 2 x 3 3 x 2 2 x 2 Ví dụ về cách cách tìm các phần tử trong ma trận nghịch đảo từ ma trận ⎡a c⎤ A = ⎢ ⎥ ⎣b d⎦ Một số yêu cầu về tính định thức và tìm nghịch đảo ma trận trong Excel: o Phải là ma trận vuông, nếu không vuông sẽ báo lỗi #VALUE!. o Nếu có phần tử nào trong ma trận là rỗng hoặc là chữ thì báo lỗi #VALUE!. Hàm tính định thức Cú pháp: MDETERM(array) Array: là địa chỉ ma trận cần tính định thức Hàm tìm ma trận nghịch đảo Cú pháp: Minverse(array) Array: là địa chỉ ma trận cần nghịch đảo Trần Thanh Phong 50 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 5.Giải phương trình và hệ phương trình Ỵ Nhấn tổ hợp phím Ctrl+Alt+Enter sau khi nhập xong công thức. Hàm nhân hai ma trận Cú pháp: MMULT(array1,array2) Array1, array2 là địa chỉ các ma trận cần nhân. Ỵ Nhấn tổ hợp phím Ctrl+Alt+Enter sau khi nhập xong công thức. Trần Thanh Phong 51 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 6.Bài toán tối ưu và qui hoạch tuyến tính BÀI 6. BÀI TOÁN TỐI ƯU VÀ QUI HOẠCH TUYẾN TÍNH Dạng tổng quát của một bài toán qui hoạch tuyến tính Hàm mục tiệu: F = c1X1 + c2X2 + + cnXn Ỉ Max (hoặc Min) Các ràng buộc: a11X1 + a12X2 + + a1nXn ≤ b1 : ak1X1 + ak2X2 + + aknXn ≥ bk : am1X1 + am2X2 + + amnXn = bm Với i, j, k, m, n ∈ Z • Các ký hiệu c1, c2, cn là các hệ số của hàm mục tiêu. Chúng có thể biểu thị cho lợi nhuận (hoặc chi phí). • Ký hiệu aij là các hệ số của các phương trình trong tập ràng buộc. Các phương trình có dạng bất đẳng thức hoặc đẳng thức. • Một tập hợp X = (X1, X2, Xn) gọi là lời giải chấp nhận được khi nó thõa tất cả ràng buộc. • Một tập hợp X* = (X*1, X*2, X*n) gọi là lời giải tối ưu nếu giá trị hàm mục tiêu tại đó tốt hơn giá trị hàm mục tiêu tại các phương án khác. 6.1. Tối ưu một mục tiêu (Linear Programming) Tìm X1 và X2 sau cho hàm lợi nhuận F = 350X1 + 300X2 đạt giá trị cực đại với các ràng buộc sau đây: X1 + X2 ≤ 200 (R1) 9X1 + 6X2 ≤ 1566 (R2) 12X1 + 16X2 ≤ 2880 (R3) X1 ≥ 0 (R4) X2 ≥ 0 (R5) B1. Tổ chức dữ liệu trên bảng tính Ỉ Biến quyết định: là số lượng sản phẩm mỗi loại cần sản xuất nhập tại các ô B3 và C3. Cho các giá trị khởi động là 0. Ỉ Hàm mục tiêu: là hàm lợi nhuận được tính căn cứ trên các giá trị khởi động của X1, X2 và lợi nhuận đơn vị. Công thức tại ô D4 xem hình 6.1. Ỉ Các ràng buộc: nhập các hệ số của các quan hệ ràng buộc tại các ô B7:C9. Tính lượng tài nguyên đã sử dụng tại các ô D7, D8 và D9 theo công thức ở hình 6.1. Nhập các giá trị ở vế phải các các quan hệ ràng buộc tại các ô E7, E8 và E9. Trần Thanh Phong 52 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 6.Bài toán tối ưu và qui hoạch tuyến tính Hình 6.1. Lập mô hình trên bảng tính B2. Chọn ô D4 và chọn Tools Ỉ Solver, sau đó khai báo các thông số cho Solver Ỉ Địa chỉ hàm mục tiêu D4 được đưa vào Set Target Cell Ỉ Chọn Max tại Equal To để cho Solver tìm lời giải cực đại chohàm mục tiêu, nghĩa là tối đa hóa lợi nhuận. Hình 6.2. Khai báo hàm mục tiêu B3. Nhập B3:C3 tại By Changing Cells: là vùng địa chỉ các biến quyết định (tượng trưng lượng sản phẩm X1 và X2 cần phải sản xuất). Trần Thanh Phong 53 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 6.Bài toán tối ưu và qui hoạch tuyến tính Hình 6.3. Khai báo địa chỉ các biến cần tìm B4. Thêm các ràng buộc vào Subject to the Constraints Ỉ Nhấp nút Add, chọn vùng địa chỉ D7:D9 tại Cell Reference, chọn dấu <= và chọn E7:E9 tại Constraint. (Các ràng buộc R1, R2, R3 đều là bất phương trình dạng <= nên ta chọn cả vùng địa chỉ). Hình 6.4. Nhập các ràng buộc Ỉ Nhấp nút Add và khai báo tiếp các ràng buộc về cận dưới cho X1 và X2 như hình 6.5. Nhấp OK sau khi hoàn tất. Hình 6.5. Ràng buộc cận dưới cho các biến X1 và X2 Ỉ Nhấp OK sau khi hoàn tất. Ỉ Để hiệu chỉnh ràng buộc ta chọn ràng buộc và nhấp nút Change Ỉ Để xóa ràng buộc, ta chọn ràng buộc từ danh sách Subject to the Contraints và nhấp nút Delete. Trần Thanh Phong 54 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 6.Bài toán tối ưu và qui hoạch tuyến tính Hình 6.6. Danh sách các ràng buộc B5. Nhấp nút Solve để chạy Solver, sau đó hộp thoại kết quả xuất hiện Hình 6.7. Kết quả chạy Solver và tạo báo cáo. B6. Nhấp chọn Keep Solver Solution và chọn OK. Hình 6.8. Kết quả bài toán tối ưu một mục tiêu. Lợi nhuận đạt $66.100 khi đó cần sản xuất 122 sản phẩm X2 và 78 sản phẩm X2. Trần Thanh Phong 55 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 6.Bài toán tối ưu và qui hoạch tuyến tính Phân tích bài toán tối ưu khi các yếu tố đầu vào thay đổi Lưu ý: Chỉ áp dụng cho các bài toán được giải bằng Solver. Bổ sung thư viện hàm cho Excel 1. Chép tập tin “Sensitivity.xla” và thư mục Library tại nơi cài đặt bộ Microsoft Office, thông thường tại: “c:\Program files\ Microsoft Office\ Office\ Library\”. Lưu ý tên Office sẽ thay đổi tùy theo phiên bản của bộ Office. 2. Vào thực đơn Tools 3. Chọn Add-Ins 4. Chọn Sensitivity Assistant 5. Nhấp nút OK. Từ kết quả của ở trên ta thực hiện phân tích tiếp theo: B1. Lập bảng phân tích: Ỉ Ô B17 tham chiếu đến ô D4 chứa giá trị hàm mục tiêu vừa tìm được. Ỉ Các ô C17, D17 và E17 lần lượt tham chiếu đến địa chỉ các ô E7, E8 và E9 (chứa giá trị của các nguồn lực). Ỉ Nhập các giá trị từ 90% đến 110% cho các ô B18:B28 với bước nhảy 2%. Nghĩa là mỗi lần một yếu tố trong nguồn lực sẽ thay đổi 2% so với giá trị hiện tại của nó (xem giá trị hiện tại là 100%) và chương trình sẽ tính lại giá trị tối ưu mới của hàm mục tiêu. Hình 6.9. Lập bảng phân tích Trần Thanh Phong 56 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 6.Bài toán tối ưu và qui hoạch tuyến tính B2. Chọn cả vùng địa chỉ B17:E28 B3. Chọn thực đơn Tools Ỉ Sensitivity Assistant B4. Khai báo vùng địa chỉ của bảng phân tích B17:E28 và chọn Spider Table và Plot để vẽ biểu đồ mạng nhện. Hình 6.10. Khai báo thông số B5. Nhấp OK để chạy chương trình Hình 6.11. Phân tích hàm mục tiêu trong trường hợp các yếu tố đầu vào thay đổi Spider Plot 70,000 69,000 68,000 R1 67,000 66,000 R2 65,000 Cell D4 Cell 64,000 R3 63,000 62,000 61,000 88% 92% 96% 100% 104% 108% 112% % of Original Hình 6.12. Biểu đồ mạng nhện Trần Thanh Phong 57 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 6.Bài toán tối ưu và qui hoạch tuyến tính 6.2. Bài toán đầu tư (Linear Programming) Nhà đầu tư chứng khoán Chí Phèo đang phân tích kế hoạch đầu tư toàn bộ số tiền $750.000 vào các loại trái phiếu của các Công ty được đánh giá theo bảng sau: Trái phiếu Suất thu lợi Số năm Đánh giá của công ty hàng năm đáo hạn Trái phiếu ACME Chemical 8.65% 11 1-Cực kỳ tốt DynaStar 9.50% 10 3-Tốt Eagle Vision 10.00% 6 4-Khá tốt MicroModeling 8.75% 10 1- Cực kỳ tốt OptiPro 9.25% 7 3-Tốt Sabre Systems 9.00% 13 2-Rất tốt Nhằm bảo vệ khoản đầu tư, nhà đầu tư quyết định đầu tư không quá 25% tiền vào bất kỳ trái phiếu nào và phải đầu tư ít nhất là 50% của tổng số tiền vào trái phiếu dài hạn (có năm đáo hạn lớn hơn hay bằng 10 năm). Các trái phiếu DynaStar, Eagle Vision và OptiPro có suất thu lợi cao nhất tuy nhiên không được đầu tư vào 3 loại trái phiếu này quá 35% của tổng số tiền vì chúng có rủi ro cao (rủi ro cao khi được đánh giá từ 2-Tốt trở xuống). Chí Phèo cần xác định phải đầu tư như thế nào để cực đại hóa lợi tức trong khi đảm bảo thõa mãn các qui định nêu ra như phần trên. Xác định các biến: số tiền đầu tư vào mỗi loại trái phiếu Đặt X1: là tổng số tiền đầu tư vào Acme Chemical X2: là tổng số tiền đầu tư vào DynaStar X3: là tổng số tiền đầu tư vào Eagle Vision X4: là tổng số tiền đầu tư vào MicroModeling X5: là tổng số tiền đầu tư vào OptiPro X6: là tổng số tiền đầu tư vào Sabre Systems Xác định hàm mục tiêu: cực đại hóa lợi tức đầu tư 0.0865X1 + 0.095X2 + 0.10X3 + 0.0875X4 + 0.0925X5 + 0.09X6 Ỵ Max Xác định các ràng buộc: - Tổng đầu tư phải bằng $750.000 X1 + X2 + X3 + X4 + X5 + X6 = 750.000 - Đảm bảo không đầu tư quá 25% của tổng số tiền vào một loại trái phiếu nào đó. (25%*750.000 = 187.500). Ta có 6 ràng buộc sau: Trần Thanh Phong 58 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 6.Bài toán tối ưu và qui hoạch tuyến tính X1 , X2 , X3 , X4 , X5 , X6 ≤ 187.500 - Phải đầu tư ít nhất 50% tiền vào các trái phiếu dài hạn (50%*750.000=375.000). Các trái phiếu có số năm đáo hạn lớn hơn hay bằng 10 năm là X1, X2, X4 và X6. X1 + X2 + X4 + X6 ≥ 375.000 - Đầu tư không quá 35% tiền (35%*750.000=262.500) vào các trái phiếu DynaStar (X2), Eagle Vision (X3) và OptiPro (X5). X2 + X3 + X5 ≤ 262.500 - Vì các biến là tiền đầu tư nên phải lớn hơn hay bằng 0. X1 , X2 , X3 , X4 , X5 , X6 ≥ 0 B1. Lập mô hình bài toán trên bảng tính Ỉ Nhập các số tiền đầu tư khởi động cho các ô B4:B9 là 0. Ỉ Tính tổng tiền đầu tư và đặt tại ô B10 theo công thức =Sum(B4:B9). Ỉ Nhập số tiền cần đầu tư 750.000 vào ô B11. Ỉ Tính số tiền đầu tư tối đa cho mỗi trái phiếu và đặt tại các ô C4:C9. Tất cả tính bằng công thức =$C$3*$B$11 Ỉ Tính tổng lợi tức hàng năm tại ô D10 theo công thức sau: =SUMPRODUCT(D4:D9,$B$4:$B$9). Ỉ Nhập số 1 vào các ô F4:F9 nếu nó là trái phiếu dài hạn, nếu không là trái phiếu dài hạn thì nhập số 0. Sau đó tính tổng số tiền đầu tư vào các trái phiếu dài hạn như công thức sau: =SUMPRODUCT(F4:F9,$B$4:$B$9). Ỉ Nhập số 1 vào các ô H4:H9 nếu đánh giá trái phiếu là rủi ro cao (lời nhiều), ngược lại thì nhập số 0. Tính tổng số tiền đầu tư các trái phiếu có suất thu lợi cao theo công thức: =SUMPRODUCT(H4:H9,$B$4:$B$9) Ỉ Tính ô F11 theo công thức =50%*B11 và tính ô H11 theo công thức =35%*B11. Hình 6.13. Lập mô hình bài toán trên bảng tính Trần Thanh Phong 59 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 6.Bài toán tối ưu và qui hoạch tuyến tính B2. Chọn ô hàm mục tiêu D10, sau đó chọn Tools Ỉ Solver. Khai báo các tham số như hộp thoại bên dưới: Hình 6.14. Khai báo tham số cho Solver B3. Nhấp nút Solve để chạy Solver. Chọn loại báo cáo và nhấp OK để hoàn thành giải bài toán. Hình 6.15. Kết quả bài toán đầu tư Ỵ Phương án trên hình 6.11 trình bày lời giải tối tư cho bài toán đầu tư của Chí Phèo. Các số tiền đầu tư vào các loại trái phiếu như minh họa trong hình bên trên. 6.3. Qui hoạch nguyên (Integer Linear Programming) Trong Excel cách giải bài toán qui hoạch nguyên tuyến tính cũng giống như các giải bài toán qui hoạch tuyến tính. Bạn chỉ cần thêm điều kiện nguyên cho các biến bắt buộc là số nguyên và hiệu chỉnh một số tuỳ chọn trong Options Tìm X1 và X2 sau cho hàm lợi nhuận F = 350X1 + 300X2 đạt giá trị cực đại Trần Thanh Phong 60 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 6.Bài toán tối ưu và qui hoạch tuyến tính với các ràng buộc sau đây: X1 + X2 ≤ 200 (R1) 9X1 + 6X2 ≤ 1520 (R2) 12X1 + 16X2 ≤ 2650 (R3) X1 ≥ 0 (R4) X2 ≥ 0 (R5) X1 và X2 phải là số nguyên. Hình 6.16. Thiết lập mô hình bày toán Cách giải bài toán giống như phần 6.1, tuy nhiên thêm ràng buộc sau vào bước 4 để qui định X1 và X2 là số nguyên: Hình 6.17. Các ràng buộc của bài toán Trần Thanh Phong 61 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 6.Bài toán tối ưu và qui hoạch tuyến tính Hiệu chỉnh Tolerance trong tùy chọn Options của Solver và nhập Tolerance là 0 (không sai số). Hình 6.18. Thiết lập tham số cho Tolerance Sau khi nhấn nút Solve, chọn loại báo cáo và nhấp nút OK Ỉ Kết quả bài toán qui hoạch nguyên như sau: Hình 6.19. Kết quả bài toán qui hoạch nguyên Trần Thanh Phong 62 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro BÀI 7. PHÂN TÍCH RỦI RO Sau khi lời giải bài toán tìm được qua các phương pháp trình bài ở trên thì chúng ta cũng cần xét đến “yếu tố rủi ro” của các phương án tối ưu tìm được. Trong thực tế chúng ta rất khó xác định được các yếu tố đầu vào của bài toán một cách chính xác và đầy đủ, do vậy các lời giải tìm được trong các bài toán là đã ngầm giả định các yếu tố đã được biết một cách rõ ràng. Các phương pháp phân tích rủi ro sẽ làm sáng tỏ hơn vấn đề và giúp nhà quản lý tự tin hơn trong việc đưa ra các quyết định: o Phân tích độ nhạy o Phân tích tình huống o Phân tích mô phỏng Xem cách thiết lập các thông số, công thức và các hàm bài toán trong tập tin Bai7-1.xls kèm theo. 7.1. Phân tích độ nhạy Phân tích độ nhạy: Là dạng phân tích nhằm trả lời câu hỏi “điều gì sẽ xảy ra nếu như ” (what – if). Phân tích này xem xét yếu tố đầu vào nào là quan trọng nhất (yếu tố mang tính chất rủi ro) ảnh hưởng đến kết quả bài toán (lợi nhuận hoặc chi phí). Trong Excel hỗ trợ phân tích độ nhạy 1 chiều và hai chiều, nghĩa là chỉ đánh giá được tối đa 2 yếu tố rủi ro. Tuy nhiên bổ sung thư viện “Sensitivity.xla” thêm vào Excel sẽ giúp chúng ta phân tích được độ nhạy nhiều chiều cho các bài toán có dùng Solver. Phân tích độ nhạy không xét đến mối quan hệ tương quan giữa các biến. B1. Nhập các thông số bài toán và các ô C2:C8 với các nhãn tương ứng. B2. Lập bảng báo cáo ngân lưu cho dự án trong 5 năm. Với: - Thu nhập = giá đơn vị * số lượng Ỵ D13=$C$4*$C$5sau đó chép công thức cho các ô E13:H13. - Giá trị thanh lý Đất tại ô I15 chính là tham chiếu ô C2 - Giá trị thanh lý Nhà xưởng tại ô I16 chính là tham chiếu ô C7 - Ngân lưu vào từ năm 1 đến năm 6 - thanh lý chính là tổng của Thu nhập, giá trị thanh lý Đất, giá trị thanh lý Nhà xưởng hàng năm tương ứng. C17=SUM(C11:C16) sau đó chép công thức cho các ô D17:I17 - Chi phí đầu tư Đất tại ô C21 chính là tham chiếu ô C2 - Chi phí đầu tư Nhà xưởng tại ô C22 chính là tham chiếu ô C6. - Chi phí vận hành = Chi phí đơn vị * Số lượng Ỵ D23=$C$3*$C$5 sau đó chép công thức cho các ô E23:H23. - Ngân lưu ra từ năm 1 đến năm 6 –thanh lý chính là tổng của Chi phí đầu tư Đất, Nhà xưởng và Chi phí vận hàng hàng năm tương ứng. C24=SUM(C20:C23) sau Trần Thanh Phong 63 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro đó chép công thức cho các ô D24:I24 - Ngân lưu ròng = Ngân lưu vào – Ngân lưu ra Ỵ C25=C17-C24 sau đó chép công thức cho các ô D25:I25 - Giá trị NPV tại ô C26=C25+NPV(C8,D25:I25) Hình 7.1. Lập mô hình bài toán trên bảng tính Giá trị NPV ở trên là $3275 ởmức chiết khấu 10%. Giá trị này được phân tích dựa trên giả thuyết giá trị của các yếu tố đầu vào không đổi suốt thời kỳ hoạt động của dử án. Do vậy, giá trị đơn lẻ của NPV thu được từ phân tích xác định là giá trị không thực bởi vì giá trị riêng biệt này sẽ không bao giờ có được. Cải tiến phân tích xác định trên bằng việc kiểm tra độ nhạy của NPV đối với sự thay đổi của một biến đầu vào “Giá đơn vị” và phân tích NPV đối với sự thay đổi của hai biến đầu vào “Giá đơn vị” và “Chi phí đơn vị” bằng công cụ phân tích độ nhạy một chiều và hai chiều của Excel. Phân tích độ nhạy một chiều Tại đây xét sự thay đổi của một yếu tố “Giá đơn vị” đầu vào tác động đến kết quả NPV. Giá đơn vị dao động từ $48 đến $53 và mỗi lần dao động 1 đơn vị. Trần Thanh Phong 64 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro B1. Tạo vùng chứa các giá trị có thể có của “Giá đơn vị” tại các ô D34:I34, lần lượt nhập các con số từ 48 đến 53. B2. Tại ô C35 tham chiếu đến địa chỉ ô cần phân tích Ỵ ô NPV: C26 B3. Đặt thêm các nhãn cho yếu tố đầu vào và nhãn cho giá trị cần phân tích giúp bài toán được rõ ràng hơn. B4. Đánh dấu chọn cả vùng C34:I35 B5. Chọn thực đơn Data Ỉ Table B6. Khai báo tại Row input cell địa chỉ của ô chứa “Giá đơn vị” Ỵ ô C4 (nhập vào Row input cell do các giá trị của yếu tố đầu vào “Giá đơn vị” được bố trí theo dòng). Hình 7.2. Chọn địa chỉ của ô là yếu tố rủi ro B7. Nhấp nút OK. Hình 7.3. Kết quả phân tích độ nhạy một chiều – một yếu tố rủi ro. Phân tích độ nhạy hai chiều Tại đây xét sự thay đổi của hai yếu tố “Giá đơn vị”, “Chi phí đơn vị” đầu vào tác động đến kết quả NPV. Giá đơn vị dao động từ $48 đến $53 và mỗi lần dao động 1 đơn vị. Chi phí đơn vị dao động từ $45 đến $55 và mỗi lần dao động 1 đơn vị. B1. Tạo vùng chứa các giá trị có thể có của “Giá đơn vị” tại các ô D43:I43, lần lượt nhập các con số từ 48 đến 53. B2. Tạo vùng chứa các giá trị có thể có của “Chi phíù đơn vị” tại các ô C44:C54, lần lượt nhập các con số từ 45 đến 55. Trần Thanh Phong 65 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro B3. Tại ô C43 tham chiếu đến địa chỉ ô cần phân tích Ỵ ô NPV: C26 B4. Đặt thêm các nhãn cho các yếu tố đầu vào và nhãn cho giá trị cần phân tích giúp bài toán được rõ ràng hơn. B5. Đánh dấu chọn cả vùng C43:I54 B6. Chọn thực đơn Data Ỉ Table B7. Khai báo tại Row input cell địa chỉ của ô chứa “Giá đơn vị” Ỵ ô C4 (nhập vào Row input cell do các giá trị của yếu tố đầu vào “Giá đơn vị” được bố trí theo dòng). Khai báo tại Column input cell địa chỉ của ô chứa “Chi phí đơn vị” Ỵ ô C3 (nhập vào Column input cell do các giá trị của yếu tố đầu vào “chi phí đơn vị” được bố trí theo cột) Hình 7.4. Khai báo địa chỉ chứa các yếu tố rủi ro B8. Nhấp nút OK. Hình 7.5. Kết quả phân tích độ nhạy hai chiều – hai yếu tố rủi ro. Qua phân tích độ nhạy, ta thấy rằng biên dạng của NPV là có biến đổi theo “Giá đơn vị” và “Chi phí đơn vị”. Trần Thanh Phong 66 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro 7.2. Phân tích tình huống (Scenarios) Phân tích tình huống: Cũng là dạng phân tích “what-if”, phân tích tình huống thừa nhận rằng các biến nhất định có quan hệ tương hỗ với nhau. Do vậy, một số ít biến số có thể thay đổi theo một kiểu nhất định tại cùng một thời điểm. Tập hợp các hoàn cảnh có khả năng kết hợp lại để tạo ra “các trường hợp” hay “các tình huống” khác nhau là: A. Trường hợp xấu nhất / Trường hợp bi quan B. Trường hợp kỳ vọng/ Trường hợp ước tính tốt nhất C. Trường hợp tốt nhất/ Trường hợp lạc quan Ghi chúù: Phân tích tình huống không tính tới xác suất của các trường hợp xảy ra Giải thích là dễ dàng khi các kết quả vững chắc : A. Chấp thuận dự án nếu NPV > 0 ngay cả trong trường hợp xấu nhất B. Bác bỏ dự án nếu NPV < 0 ngay cả trong trường hợp tốt nhất C. Nếu NPV đôi lúc dương, đôi lúc âm, thì các kết quả là không dứt khoát. Không may, đây sẽ là trường hợp hay gặp nhất. Phân tích tình huống Các kết quả khảo sát về tình hình chi phí nguyên vật liệu và giá sản phẩm của dự án trên như sau: Trường hợp Trường hợp Trường hợp tốt nhất kỳ vọng xấu nhất Chi phí đơn vị ($) 45 47 55 Giá đơn vị ($) 53 50 48 Chúng ta sẽ lần lượt tạo các Tình huống theo các bước sau: B1. Lập bài toán trên bảng tính như phần 7.1. B2. Chọn thực đơn Tools Ỉ Scenarios (xem hình 7.6) Trần Thanh Phong 67 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Hình 7.6. Bảng quản lý các tình huống. B3. Nhấp nút Add Ỉ Đặt tên cho Tình huống là “Tốt nhất” tại khung Scenario name Ỉ Tại khung Changing cells chọn địa chỉ hai ô chứa “Chi phí đơn vị” và “Giá đơn vị” là C3:C4. Hình 7.7. Khai báo các thông số cho tình huống “Tốt nhất” B4. Nhấp nút OK Ỉ Tại ô C3 (Chi phí đơn vị) nhập vào giá trị 45. Ỉ Tại ô C4 (Giá đơn vị) nhập vào giá trị 53. Trần Thanh Phong 68 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Hình 7.8. Nhập các giá trị cho tình huống “Tốt nhất” B5. Nhấp nút Add để thêm Tình huống khác, (nhấp nút OK để trở về bảng quản lý các tình huống). Trong bài này hãy nhấp nút Add Ỉ Đặt tên cho Tình huống là “Trung bình” tại khung Scenario name Ỉ Tại khung Changing cells chọn địa chỉ hai ô chứa “Chi phí đơn vị” và “Giá đơn vị” là C3:C4. Hình 7.9. Tạo tình huống “Trung bình” B6. Nhấp nút OK. Ỉ Tại ô C3 (Chi phí đơn vị) nhập vào giá trị 47. Ỉ Tại ô C4 (Giá đơn vị) nhập vào giá trị 50. Trần Thanh Phong 69 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Hình 7.10. Nhập giá trị cho tình huống “Trung bình” B7. Tiếp tục nhấp nút Add để tạo Trường hợp xấu nhất. Đặt nhãn và chọn địa chỉ các ô cần thay đổi. Hình 7.11. Tạo tình huống “Xấu nhất” B8. Nhấp OK và nhập giá trị cho các ô Ỉ Tại ô C3 (Chi phí đơn vị) nhập vào giá trị 55. Ỉ Tại ô C4 (Giá đơn vị) nhập vào giá trị 48. Hình 7.12. Nhập giá trị cho tình huống “Xấu nhất” Trần Thanh Phong 70 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro B9. Nhấp nút OK để trở về bảng quản lý các tình huống. Hình 7.13. Bảng quản lý các tình huống B10. Để xem kết quả của tình huống nào thì chọn tên tình huống trong danh sách và nhấp nút Show. Tương tự cho việc tạo thêm, hiệu chỉnh và xóa tình huống thì nhấp tương ứng các nút Add , Edit và Delete. B11. Tạo báo cáo tổng hợp về các tình huống Ỉ nhấp nút Summary Ỉ Nhập địa chỉ ô kết quả (NPV của dự án) C26 tại khung Result cells Ỉ Chọn kiểu báo cáo là Scenario summary hoặc Scenario PivotTable Report. Hình 7.14. Tạo bảng báo cáo tổng hợp về các tình huống B12. Nhấp nút OK sau khi khai báo các thông số Trần Thanh Phong 71 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Hình 7.15. Kết quả tổng hợp Hàm Index Chúng ta có thể vận dụng các hàm tham chiếu trong việc phân tích tình huống. Các hàm trên tham chiếu đến một ô hay một dãy các ô, hay là giá trị của một ô trong một mảng, hay là một mảng các giá trị từ một mảng lớn hơn. Cú pháp có hai dạng: Dạng 1: Hàm đầu tiên trả về tham chiếu đến một ô hay một dãy các ô. INDEX(reference, row_num, column_num, area_num) Trong đó: Reference: tham chiếu đến một hay nhiều mảng số liệu. Nếu các mảng nằm không liền kề nhau thì đặt các mảng trong cặp ngoặc tròn. Nếu mảng chỉ có một dòng hoặc một cột thì các đối số row_num hoặc column_num cho hàm là tùy chọn (nhập hay bỏ trống đều được). Row_num: là số hàng trong vùng Reference ở trên cần xác định địa chỉ trả về. Column_num: là số cột trong vùng Reference ở trên cần xác định địa chỉ trả về. Area_num: xác định số mảng trong vùng Reference ở trên sử dụng cho tham chiếu. Nếu bỏ trống xem như là áp dụng tham chiếu cho mảng thứ 1 trong vùng Reference. Ví dụ: Các mảng số liệu và các công thức minh hoạ hàm Index Trần Thanh Phong 72 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Hình 7.16. Ví dụ hàm Index dạng 1 Dạng 2: Hàm thứ hai trả về giá trị của một ô hay là dãy các ô trong một mảng. INDEX(array, row_num, column_num) Trong đó: Array: mảng địa chỉ các ô. Nếu mảng chỉ có một dòng hoặc một cột thì các đối số row_num hoặc column_num cho hàm là tùy chọn (nhập hay bỏ trống đều được). Nếu mảng có nhiều cột hoặc nhiều dòng mà chỉ khai báo một trong hai đối số row_num hoặc column_num thì hàm Index sẽ trả về cả dãy của dòng hoặc cột. Row_num: chọn số hàng cần lấy giá trị trả về. Column_num: chọn số cột cần lấy giá trị trả về. Một trong hai đối số Row_num, Column_num phải có trọng hàm Ví dụ: Mảng số liệu và các minh họa hàm Index Hình 7.17. Ví dụ hàm Index dạng 2 Trần Thanh Phong 73 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro 7.3. Mô phỏng bằng Crystal Ball Một sự mở rộng tự nhiên của phân tích độ nhạy và phân tích tình huống Đồng thời có tính tới các phân phối xác suất khác nhau và các miền giá trị tiềm năng khác nhau đối với các biến chính của dự án. Cho phép có tương quan (cùng biến thiên) giữa các biến Tạo ra một phân phối xác suất cho các kết quả của dự án (các ngân lưu, NPV) thay vì chỉ ước tính một giá trị đơn lẻ. Phân phối xác suất của các kết quả dự án có thể hỗ trợ các nhà ra quyết định trong việc lập ra các lựa chọn, nhưng có thể có các vấn đề về giải thích và sử dụng. Qui trình lập bài toán mô phỏng: 1. Mô hình toán học : bảng tính thẩm định dự án 2. Xác định các biến nhạy cảm và không chắc chắn 3. Xác định tính không chắc chắn - Xác định miền các lựa chọn (tối thiểu và tối đa) - Định phân phối xác suất, các phân phối xác suất thông thường nhất là: Phân phối xác xuất chuẩn, phân phối xác suất tam giác, phân phối xác suất đều, phân phối xác suất bậc thang 4. Xác định và định nghĩa các biến có tương quan - Tương quan đồng biến hoặc nghịch biến - Độ mạnh của tương quan 5. Mô hình mô phỏng: làm một chuỗi phân tích cho nhiều tổ hợp giá trị tham số khác nhau 6. Phân tích các kết quả - Các trị thống kê - Các phân phối xác suất Trong suốt phần này trình bày phương pháp phân tích rủi ro bằng mô phỏng trên một yếu tố rủi ro là “giá sản phẩm” theo 4 mô hình: Bổ sung thư viện hàm cho Excel 6. Cài đặt phần mềm Crystal Ball vào máy 7. Vào thực đơn Tools 8. Chọn Add-Ins 9. Chọn Crystal Ball 10. Nhấp nút OK. Trần Thanh Phong 74 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro a. Mô hình giá không đổi không chắc chắn Giá trong suốt thời kỳ hoạt động của dự án không thay đổi với phân phối chuẩn: P(t) = P(t −1) = P(t − 2) = = P(t − n) = M + e Ỵ Pt = Ptb + e Ví dụ: Giả thiết ta biết trong quá khứ giá sản phẩm tuân theo phân phối chuẩn có trung bình là 50 và giá thấp nhất là 44 và cao nhất là 56. B1. Lập mô hình trên bảng tính Hình 7.18. Lập bài toán trên bảng theo mô hình 1 Ỉ đã tính ra Thu nhập B2. Biến giá sản phẩm là biến rủi ro cần phân tích, với các thông tin sau: Giá trị lớn nhất là: 56 Giá trị thấp nhất là : 44 Giá trị trung bình là: 50 Trần Thanh Phong 75 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Độ lệch chuẩn của giá sản phẩm là (Gmax - Gmin)/3 = 2 Giá sản phẩm tuân theo phân phối chuẩn (50, 2) Ỉ e(0, 2) B3. Khai báo các biến giả thuyết (giá sản phẩm – thực chất là giá trị của e thay đổi) và biến kết quả (kết quả cần phân tích rủi ro - NPV) cho phần mềm mô phỏng Crystal Ball. Ỉ Chọn ô D10, sau đó chọn thực đơn Cell Ỉ Define Assumption Hình 7.19. Chọn phân phối cho e Ỉ Chọn phân phối chuẩn “Normal” và nhấp nút OK Hình 7.20. Khai báo phân phối cho e(0, 2) Ỉ Tại Mean (giá trị trung bình) nhập vào 0 và tại Std Dev (độ lệch chuẩn) nhập vào 2. Nhấp nút OK sau khi hoàn tất. Ỉ Chọn ô C27 và chọn thực đơn Cell Ỉ Define Forecast Trần Thanh Phong 76 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Hình 7.21. Đặt tên và đơn vị tính cho biến kết quả Ỉ Nhập vào NPV tại Forecast Name và nhập ký hiệu đơn vị tính cho NPV là $ tại Units. Nhấp nút OK sau khi hoàn tất. B4. Khai báo thông số mô phỏng Ỉ Vào thực đơn Run Ỉ Run Reference Hình 7.22. Thiết lập thông số mô phỏng Ỉ Chọn mô phỏng là 1000 lần thử tại Maximun Number of Trials. Và các tùy chọn khác như hình trên. Nhấp OK sau khi hoàn tất. B5. Chạy mô phỏng, vào thực đơn Run Ỉ Run và chờ kết quả chạy. Nhấp OK sau khi hoàn tất chạy mô phỏng. Hình 7.23. Đã chạy xong mô phỏng Trần Thanh Phong 77 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro B6. Kết quả mô phỏng Hình 7.24a. Các thông số thống kê Forecast: NPV 1,000 Trials Frequency Chart 996 Displayed .026 26 .020 19.5 .013 13 .007 6.5 Mean = 3,337 .000 0 -16,938 -7,193 2,552 12,297 22,042 Certai nty i s 68.40% from 0 to +Infi nity $ Hình 7.24b. Biểu đồ tần suất của NPV có thể hiện % NPV dương – âm Forecast: NPV 1,000 Trials Cumulativ e Char t 996 Displayed 1.000 1000 .750 750 .500 500 .250 250 Mean = 3,337 .000 0 -16,938 -7,193 2,552 12,297 22,042 Certai nty i s 31.60% from -Infi ni ty to 0 $ Hình 7.24c. Biểu đồ tần suất tích lũy của NPV Trần Thanh Phong 78 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro b. Mô hình giá độc lập không chắc chắn Giá độc lập đối với thời kỳ hoạt động của dự án với giá hàng năm có phân phối chuẩn. Pt = Ptb + et Ví dụ: Giả thiết giá sản phẩm của mỗi năm của dự án sẽ có cùng phân phối chuẩn (50, 2), nhưng giá của năm này sẽ độc lập với giá của năm khác. B1. Lập mô hình bài toán trên bảng tính Hình 7.25. Lập bài toán trên bảng theo mô hình 2 Ỉ đã tính ra Thu nhập B2. Giá sản phẩm của các năm thay đổi độc lập theo phân phối chuẩn (50, 2) B3. Khai báo các biến giả thuyết (giá sản phẩm – thực chất là giá trị của e thay đổi) và biến kết quả (kết quả cần phân tích rủi ro - NPV). Trần Thanh Phong 79 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Ỉ Chọn ô D10, sau đó chọn thực đơn Cell Ỉ Define Assumption Hình 7.26. Chọn phân phối cho e1 Ỉ Chọn phân phối chuẩn “Normal” và nhấp nút OK Hình 7.27. Khai báo phân phối cho năm 1 Ỉ e1(0, 2) Ỉ Tại Mean (giá trị trung bình) nhập vào 0 và tại Std Dev (độ lệch chuẩn) nhập vào 2. Nhấp nút OK sau khi hoàn tất. Ỉ Làm tương tự cho các ô E10, F10, G10, H10 với các phân phối và tên tương ứng là e2(0, 2), e3(0,2), e4(0,2) và e5(0,2). Cách định nghĩa nhanh các biến giả thuyết giống nhau là định nghĩa biến đầu tiên D10, sau đó chọn D10 rồi vào thực đơn Cell Ỉ Copy Data, sau đó chọn vùng địa chỉ các ô E10:H10 và vào thực đơn Cell Ỉ Paste Date. Trần Thanh Phong 80 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Ỉ Chọn ô C27 và chọn thực đơn Cell Ỉ Define Forecast Hình 7.28. Đặt tên và đơn vị tính cho biến kết quả Ỉ Nhập vào NPV tại Forecast Name và nhập ký hiệu đơn vị tính cho NPV là $ tại Units. Nhấp nút OK sau khi hoàn tất. B4. Khai báo số lần thử là 1000 lần trong Run Ỉ Run Reference B5. Chạy mô phỏng Run Ỉ Run. Nhấp OK sau khi chạy xong. B6. Kết quả mô phỏng Hình 7.29a. Các thông số thống kê Forecast: NPV 1,000 Trials Frequency Chart 987 Displayed .027 27 .020 20.25 .014 13.5 .007 6.75 Mean = 3,129 .000 0 -5,310 -1,086 3,138 7,362 11,586 Certai nty i s 82.70% from 0 to +Infi nity $ Hình 7.29b. Biểu đồ tần suất của NPV có thể hiện % NPV dương – âm Trần Thanh Phong 81 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Forecast: NPV 1,000 Trials Cumulativ e Char t 987 Displayed 1.000 1000 .750 750 .500 500 .250 250 Mean = 3,129 .000 0 -5,310 -1,086 3,138 7,362 11,586 Certai nty i s 17.30% from -Infi ni ty to 0 $ Hình 7.29c. Biểu đồ tần suất tích lũy của NPV c. Mô hình bước ngẫu nhiên Giá thay đổi hàng năm tuân theo phân phối chuẩn và giá của năm sau sẽ bằng giá của năm trước đó cộng thêm dao động ngẫu nhiên. Pt = P(t-1) + et Ví dụ: Giả thiết giá sản phẩm thay đổi hàng năm, giá năm sau bằng giá năm trước cộng dao động ngẫu nhiên. Dao động ngẫu nhiên tuân theo phân phối chuẩn có trung bình là 0 và độ lệch chuẩn là 2 Ỵ e(0, 2). B1. Lập mô hình bài toán trên bảng tính (xem hình 7.30 bên dưới) B2. Giá năm t bằng giá năm (t-1) cộng dao động ngẫu nhiên của năm t Ỉ Giá năm 1: P1 = 50 + e1(0,2) Ỉ Giá năm 2: P2 = P1 + e2(0,2) Ỉ Giá năm 3: P3 = P2 + e3(0,2) Ỉ Giá năm 2: P4 = P3 + e4(0,2) Ỉ Giá năm 2: P5 = P4 + e5(0,2) Lưu ý: Mô hình bài toán ở hình 7.30, sự thay đổi của giá đã được tính trực tiếp ra thu nhập không qua bước trung gian. Trần Thanh Phong 82 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Hình 7.30. Lập bài toán trên bảng theo mô hình 3 Ỉ đã tính ra Thu nhập B3. Khai báo các biến giả thuyết (giá sản phẩm – thực chất là giá trị của e thay đổi) và biến kết quả (kết quả cần phân tích rủi ro - NPV). Ỉ Chọn ô D10, sau đó chọn thực đơn Cell Ỉ Define Assumption Ỉ phân phối chuẩn “Normal” và nhấp nút OK Ỉ Tại Mean (giá trị trung bình) nhập vào 0 và tại Std Dev (độ lệch chuẩn) nhập vào 2. Nhấp nút OK sau khi hoàn tất. Ỉ Làm tương tự cho các ô E10, F10, G10, H10 với các phân phối và tên tương ứng là e2(0, 2), e3(0,2), e4(0,2) và e5(0,2). Cách định nghĩa nhanh các biến giả thuyết giống nhau là định nghĩa biến đầu tiên D10, sau đó chọn D10 rồi vào thực đơn Cell Ỉ Copy Data, sau đó chọn vùng địa chỉ các ô E10:H10 Trần Thanh Phong 83 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro và vào thực đơn Cell Ỉ Paste Date. Ỉ Chọn ô C27 và chọn thực đơn Cell Ỉ Define Forecast Ỉ Nhập vào NPV tại Forecast Name và nhập ký hiệu đơn vị tính cho NPV là $ tại Units. Nhấp nút OK sau khi hoàn tất. B4. Khai báo số lần thử là 1000 lần trong Run Ỉ Run Reference B5. Chạy mô phỏng Run Ỉ Run. Nhấp OK sau khi chạy xong. B6. Kết quả mô phỏng Hình 7.31a. Các thông số thống kê Forecast: NPV 1,000 Trials Frequency Chart 995 Displayed .031 31 .023 23.25 .016 15.5 .008 7.75 Mean = 3,196 .000 0 -22,317 -8,496 5,326 19,148 32,969 Certai nty i s 62.60% from 0 to +Infi nity $ Hình 7.31b. Biểu đồ tần suất của NPV có thể hiện % NPV dương – âm Trần Thanh Phong 84 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Forecast: NPV 1,000 Trials Cumulativ e Char t 995 Displayed 1.000 1000 .750 750 .500 500 .250 250 Mean = 3,196 .000 0 -22,317 -8,496 5,326 19,148 32,969 Certai nty i s 37.40% from -Infi ni ty to 0 $ Hình 7.31c. Biểu đồ tần suất tích lũy của NPV d. Mô hình tự hồi qui bậc nhất – AR(1) Giá của năm này cao thì giá năm tiếp theo sẽ cao, và ngược lại. Nghĩa là giá của năm này được quan hệ với giá ở năm kế tiếp. Trong mô hình này giả thiết là giá của sản lượng chỉ tùy thuộc vào giá của năm trước. Pt = Φ1Pt-1 + (1 - Φ1)Ptb + et Phi (Φ) là hệ số tự tương quan, là đại lượng để chỉ độ mạnh của quan hệ giữa giá ở năm t và giá ở năm trước (t-1) Phi càng cao thì chứng tỏ có sự tương quan mạnh của giá giữa các năm (Ví dụ: 0.9) Tương quan yếu của giá giữa các năm thì có thể cho Phi là 0.4 Nếu Φ = 1 Ỉ Trở lại mô hình 3 Nếu Φ = 0 Ỉ Trở lại mô hình 2 Ví dụ: Giá năm 0 có giá trị trung bình là 50, giá giữa các năm dao động và tương quan với nhau theo hệ số Φ = 0.8. Dao động ngẫu nhiên của giá tuân theo phân phối chuẩn có trung bình là 0 và độ lệch chuẩn là 2 Ỵ e(0, 2). B1. Lập mô hình bài toán trên bảng tính (xem hình 7.32 bên dưới) B2. Tính giá của các năm tại các ô D11 : H11 (xem hình 7.32 bên dưới) Trần Thanh Phong 85 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Hình 7.32. Lập bài toán trên bảng theo mô hình 4 B3. Khai báo các biến giả thuyết (giá sản phẩm – thực chất là giá trị của e thay đổi) và biến kết quả (kết quả cần phân tích rủi ro - NPV). Ỉ Chọn ô D10, sau đó chọn thực đơn Cell Ỉ Define Assumption Ỉ phân phối chuẩn “Normal” và nhấp nút OK Ỉ Tại Mean (giá trị trung bình) nhập vào 0 và tại Std Dev (độ lệch chuẩn) nhập vào 2. Nhấp nút OK sau khi hoàn tất. Ỉ Làm tương tự cho các ô E10, F10, G10, H10 với các phân phối và tên tương ứng là e2(0, 2), e3(0,2), e4(0,2) và e5(0,2). Cách định nghĩa nhanh các biến giả thuyết giống nhau là định nghĩa biến đầu tiên D10, sau đó chọn D10 rồi Trần Thanh Phong 86 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro vào thực đơn Cell Ỉ Copy Data, sau đó chọn vùng địa chỉ các ô E10:H10 và vào thực đơn Cell Ỉ Paste Date. Ỉ Chọn ô C28 và chọn thực đơn Cell Ỉ Define Forecast Ỉ Nhập vào NPV tại Forecast Name và nhập ký hiệu đơn vị tính cho NPV là $ tại Units. Nhấp nút OK sau khi hoàn tất. B4. Khai báo số lần thử là 1000 lần trong Run Ỉ Run Reference B5. Chạy mô phỏng Run Ỉ Run. Nhấp OK sau khi chạy xong. B6. Kết quả mô phỏng Hình 7.33a. Các thông số thống kê Forecast: NPV 1,000 Trials Frequency Chart 989 Displayed .028 28 .021 21 .014 14 .007 7 Mean = 3,200 .000 0 -17,341 -7,147 3,047 13,240 23,434 Certai nty i s 65.00% from 0 to +Infi nity $ Hình 7.33b. Biểu đồ tần suất của NPV có thể hiện % NPV dương – âm Trần Thanh Phong 87 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 7.Phân tích rủi ro Forecast: NPV 1,000 Trials Cumulativ e Char t 989 Displayed 1.000 1000 .750 750 .500 500 .250 250 Mean = 3,200 .000 0 -17,341 -7,147 3,047 13,240 23,434 Certai nty i s 65.00% from 0 to +Infi nity $ Hình 7.33c. Biểu đồ tần suất tích lũy của NPV Trần Thanh Phong 88 Ứng dụng Microsoft Excel trong kinh tế
Chương trình Giảng dạy Kinh tế Fulbright Bài 8. Xác suất & Thống kê BÀI 8. XÁC SUẤT & THỐNG KÊ Bổ sung công cụ phân tích dữ liệu vào Excel: Bổ sung thư viện hàm cho Excel 1. Khởi động Microsoft Excel 2. Vào thực đơn Tools 3. Chọn Add-Ins 4. Chọn Analysis ToolPak 5. Nhấp nút OK. 8.1. Thống kê Tập hợp chính (Populations): Tập hợp chính là tập hợp tất cả các đối tượng mà ta quan tâm nghiên cứu trong một vấn đề nào đó. Số phần tử của tập hợp chính được ký hiệu là N. Mẫu (Sample): Mẫu là tập hợp con của tập hợp chính. Mẫu gồm một số hữu hạn n phần tử. Số n được gọi là cỡ mẫu. Tần số (Frequency): Gọi xi là các giá trị quan sát được của biến ngẫu nhiên X (i = 1, 2, l). Số lần xuất hiện của giá trị xi trong khối dữ liệu được gọi là tần số l của xi và được ký hiệu là fi. Ta có ∑ fi= n với n là cỡ mẫu i=1 Tần số tích lũy (Cumulative Frequency): Tần số tích lũy của một giá trị xi là tổng số tần số của giá trị này với tần số của các giá trị nhỏ hơn xi. Các số định tâm (Measure of Central Tendency): Số định tâm của nhóm dữ liệu là số đại diện cho tất cả các dữ liệu đó, nó thể hiện vai trò trung tâm của nhóm dữ liệu. Có các loại số định tâm sau: số trung bình (Mean), trung bình trọng số (Weighted mean) số trung vị (Median) và số yếu vị (Mode). Các số phân tán (Measure of Dispersion): Số phân tán dùng để thể hiện sự khác biệt giữa các số trong khối Dữ liệu đối với số định tâm: Khoảng (Range), độ lệch chuẩn (Standard deviation) và phương sai (variance). Trần Thanh Phong 89 Ứng dụng Microsoft Excel trong kinh tế