Đăng ký Đăng nhập
Trang chủ Tài chính - Ngân hàng Ngân hàng - Tín dụng Giáo trình tin học ứng dụng ngành tài chính...

Tài liệu Giáo trình tin học ứng dụng ngành tài chính

.PDF
163
92
62

Mô tả:

BỘ GIÁO DỤC VÀ ĐÀO TẠO TRƯỜNG ĐẠI HỌC LẠC HỒNG GIÁO TRÌNH TIN HỌC ỨNG DỤNG NGÀNH TÀI CHÍNH NGUYỄN QUỐC HUY ĐOÀN VIỆT HÙNG Tháng 04/2014 LỜI MỞ ĐẦU Trong những năm gần đây, việc giàng dạy môn Tin học ứng dụng Tài chính cho sinh viên ngành Tài chính-Ngân hàng từng bước được chú trọng. Xuất phát từ thực tiễn giảng dạy và nhu cầu tài liệu tin học ứng dụng tài chính để sinh viên tham khảo và nâng cao chất lượng đào tạo, nhóm tác giả biên soạn giáo trình này để góp phần phục vụ việc học tập của sinh viên khoa Tài chính-Ngân hàng nói riêng và Trường Đại học Lạc Hồng nói chung. Tài liệu cung cấp cho sinh viên những công cụ do Microsoft Excel hỗ trợ trong lĩnh vực Tài chính một cách hiệu quả. Ngoài ra giúp sinh viên có khả năng thiết lập được các bài toán tài chính trong kinh doanh của doanh nghiệp, hoạch định được ngân sách, thiết lập được mô hình thẩm định dự án đầu tư trên máy tính. Dùng các hàm dự báo trong Excel để dự báo tình hình sản xuất kinh doanh của doanh nghiệp. Để thuận tiện cho việc theo dõi môn học, nhóm tác giả khái quát về môn học như sau:  Sử dụng một số hàm Tài chính trong Microsoft Excel  Sử dụng PivotTable để lập các báo cáo  Sử dụng Goal Seek để lập kế hoạch tài chính  Sử dụng Solver để tìm danh mục đầu tư tài chính  Phân tích độ nhạy và phân tích tình huống trong thẩm định hiệu quả dự án đầu tư  Sử dụng Crystal Ball để đánh giá rủi ro dự án đầu tư  Sử dụng SPSS để phân tích số liệu thống kê. Để hoàn thiện cuốn giáo trình này, nhóm tác giả xin chân thành cảm ơn Ban giám hiệu Nhà trường, Ban lãnh đạo Khoa Tài chính-Ngân hàng, tập thể cán bộ, nhân viên, các thầy cô đồng nghiệp giảng dạy đã tạo điều kiện giúp đỡ nhóm tác giả hoàn thành giáo trình này. Nhóm tác giả xin cảm ơn quý thầy cô và các bạn sinh viên đã tham gia góp ý tài liệu. Do đây là lần đầu biên soạn giáo trình theo chuẩn mới của Nhà trường nên chắc chắn còn những sai sót khó tránh khỏi. Nhóm tác giả rất mong nhận được sự đóng góp ý kiến của đọc giả gần xa để tài liệu ngày càng hoàn thiện hơn. Xin chân thành cảm ơn. NHÓM TÁC GIẢ DANH MỤC CÁC TỪ VIẾT TẮT DN NĐT NG : Doanh nghiệp : Nhà đầu tư : Nguyên giá STT : Số thứ tự TSCĐ : Tài sản cố định TS : Tài sản MỤC LỤC Lời nói đầu CHƯƠNG 1: ÔN TẬP MỘT SỐ HÀM EXCEL TRONG TÀI CHÍNH ................. 1 1.1 Giới thiệu công thức và hàm .....................................................................................1 1.1.1 Giới thiệu công thức (Formula) .........................................................................1 1.1.2 Giới thiệu hàm (Function) .................................................................................1 1.2 Một số hàm thường dùng ..........................................................................................2 1.2.1 Hàm thời gian .....................................................................................................2 1.2.2 Hàm xử lý chuỗi .................................................................................................4 1.2.3 Hàm thống kê .....................................................................................................6 1.2.4 Hàm tham chiếu .................................................................................................8 1.2.5 Các hàm tài chính - Financian functions ..........................................................12 1.2.6 Hàm tính toán khoa học ...................................................................................15 CHƯƠNG 2: ĐỒ THỊ TRONG EXCEL................................................................... 25 2.1 Giới thiệu đồ thị .......................................................................................................25 2.2 Vẽ đồ thị trong Excel ...............................................................................................27 2.3 Hiệu chỉnh đồ thị trong Excel ..................................................................................30 CHƯƠNG 3: PIVOTTABLE ..................................................................................... 38 3.1 Tổng quan về PivotTable.........................................................................................38 3.1.1 PivotTable là gì? ..............................................................................................38 3.1.2 Các thành phần của PivotTable ........................................................................38 3.2 Tạo lập PivotTable ..................................................................................................38 3.2.1 Báo cáo bằng PivotTable .................................................................................38 3.2.2 Tạo một PivotChart ..........................................................................................44 CHƯƠNG 4: SỬ DỤNG GOAL SEEK ĐỂ LẬP KẾ HOẠCH TÀI CHÍNH ....... 50 4.1 Khái niệm điểm hòa vốn .........................................................................................50 4.2 Giải bài toán điểm hòa vốn .....................................................................................50 4.3 Bài toán xác định điểm hòa vốn của doanh nghiệp sản xuất ..................................50 CHƯƠNG 5: SOLVER ............................................................................................... 65 5.1 Tổng quan về Solver ................................................................................................ 65 5.1.1 Solver là gì? ......................................................................................................65 5.1.2 Lập và giải bài toán bằng Solver ......................................................................65 5.2 Sử dụng Solver ........................................................................................................67 5.2.1 Add-In Solver cho Excel 2010 .........................................................................67 5.2.2 Sử dụng Solver .................................................................................................69 CHƯƠNG 6: PHÂN TÍCH ĐỘ NHẠY VÀ PHÂN TÍCH TÌNH HUỐNG TRONG THẨM ĐỊNH HIỆU QUẢ DỰ ÁN ĐẦU TƯ............................................................ 80 6.1 Bảng dữ liệu(Data Tables) ......................................................................................80 6.2 Phân tích độ nhạy một chiều ..................................................................................80 6.3 Phân tích độ nhạy hai chiều ....................................................................................80 6.4 Phân tích tình huống ...............................................................................................82 6.4.1 Kịch bản (Scenario) là gì? ................................................................................83 6.4.2 Bài toán phân tích tình huống ..........................................................................83 6.4.3 Lập báo cáo tổng kết ........................................................................................84 CHƯƠNG 7: SỬ DỤNG CRYSTAL BALL ĐỂ ĐÁNH GIÁ RỦI RO DỰ ÁN ĐẦU TƯ ........................................................................................................................89 7.1 Ưu điểm của Crystall Ball so với Excel .................................................................89 7.2 Các phương pháp lấy mẫu phổ biến trong Crystal Ball .........................................91 7.3 Thiết lập bài toán ....................................................................................................91 7.3.1 Bài toán tĩnh .....................................................................................................91 7.3.2 Bài toán động ...................................................................................................91 7.4 Chuẩn bị trước khi chạy mô phỏng .........................................................................91 7.5 Thao tác ...................................................................................................................92 7.5.1 Khai báo biến giả thiết, biến kết quả ................................................................ 92 7.5.2 Chạy mô phỏng ................................................................................................ 94 7.5.3 Xem kết quả chạy được ....................................................................................95 7.5.4 Lập báo cáo và phân tích ..................................................................................98 7.5.4.1 Lập báo cáo đơn giản ................................................................................98 7.5.4.2 Lập báo cáo theo khuôn mẫu ....................................................................98 7.6 Chạy lại bài toán với các biến giả thiết và biến kết quả mới ...................................99 7.7 Đưa Crystal Ball hiển thị trên màn hình Excel........................................................99 CHƯƠNG 8: HƯỚNG DẪN SỬ DỤNG SPSS ....................................................... 103 8.1 Tổng quan về SPSS ...............................................................................................103 8.2 Mở các tập tin dữ liệu ............................................................................................109 8.3 Cửa sổ Data Editor ................................................................................................112 8.4 Frequencies (Tần số) .............................................................................................121 8.5 Phân tích hồi quy tuyến tính đơn với SPSS...........................................................124 CHƯƠNG 9: SỬ DỤNG MỘT SỐ HÀM TÀI CHÍNH TRONG EXCEL .......... 128 9.1 Các phương pháp tính khấu hao ...........................................................................128 9.1.1 Khái niệm .......................................................................................................128 9.1.2 Các hàm tính khấu hao TSCĐ.......................................................................128 9.1.2.1 Hàm SLN (Straight Line) .......................................................................128 9.1.2.2 Hàm SYD (Sum of Year’s Digits) ..........................................................129 9.1.2.3 Hàm DB (Declining Balance) .................................................................130 9.1.2.4 Hàm DDB (Double Declining Balance) .................................................130 9.2 Bài toán mua hàng trả góp ....................................................................................131 9.3 Lập dòng tiền có chiết khấu ..................................................................................131 9.3.1 Hàm PV (Present Value) ................................................................................132 9.3.2 Hàm FV (Furture Value) ................................................................................132 9.4 Sử dụng các hàm xác suất thống kê để dự báo .....................................................133 9.4.1 Ý nghĩa của dự báo kinh tế ............................................................................133 9.4.2 Giới thiệu các phương pháp dự báo kinh tế ...................................................133 9.4.2.1 Dự báo bằng phương pháp trung bình dài hạn trong Excel ....................133 9.4.2.2 Dự báo bằng phương pháp trung bình động trong Excel .......................135 9.4.2.3 Dự báo bằng hồi quy tuyến tính trong Excel ..........................................135 9.4.2.4 Sử dụng Regression để hồi quy và dự báo .............................................138 9.4.2.5 Sử dụng các hàm GROWTH và LOGEST .............................................142 9.4.2.6 Sử dụng đồ thị để dự báo ........................................................................146 CHƯƠNG 1: ÔN TẬP MỘT SỐ HÀM EXCEL TRONG TÀI CHÍNH Mục tiêu Sau khi học xong bài này sinh viên nắm được:  Ôn tập các hàm Excel thường sử dụng.  Thiết kế các bảng tính theo yêu cầu. 1.1 Giới thiệu công thức và hàm 1.1.1 Giới thiệu công thức (Formula) [1] Công thức giúp bảng tính hữu ích hơn rất nhiều, nếu không có các công thức thì bảng tính cũng giống như trình soạn thảo văn bản. Dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới giúp đỡ tốn công sức tính lại nhiều lần. Vậy công thức có các thành phần gì? Công thức trong Excel được nhận dạng là do nó bắt đầu là dấu “=” và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm. Ví dụ: 1.1.2 Giới thiệu hàm (Function) [1] Hàm mẫu (Standrad Function) là những chương trình con được viết sẵn và việc sử dụng chỉ là gọi chương trình con đó ra thực hiện mà không cần viết lại nữa. Nói cách khác, người dùng chỉ cần sử dụng đúng tên hàm cùng các đối tượng liên quan (tham số) trong hàm đó, việc tính toán giá trị cho hàm và trả lại cho người dùng sẽ do excel thực hiện. Nhờ đó mà việc tính toán và phân tích số liệu trong excel trở nên đơn giản hơn. Ngoài ra cũng có những hàm do người sử dụng tự định nghĩa (User Defined Function) Hàm trong Excel được lập trình sẵn dùng tính toán hoặc thực hiện một chức năng nào đó. Việc sử dụng thành thạo các hàm sẽ giúp tiết kiệm được rất nhiều thời gian so với tính toán thủ công không dùng hàm. Các hàm trong Excel rất đa dạng bao trùm nhiều lĩnh vực, có những hàm không yêu cầu đối số, có những hàm yêu cầu một hoặc nhiều đối số, và các đối số có thể là bắt buộc hoặc tự chọn. 1 -Trong Excel có các hàm chính như sau: + Hàm ngoại: Call, Registed.ID,… + Hàm lấy dữ liệu từ SSAS: Cubeset, Cubevalue,… + Hàm dữ liệu: Dmin, Dmax, Dcount,… + Hàm ngày và thời gian: Time, Now, Date,…. Hàm kỹ thuật: Dec2Bin, Dec2Hex, Dec2Oct,… Hàm tài chính: Npv, Pv, Fv, Rate,… + + + + Hàm thông tin: Cell, Thông tin, IsNa,… Hàm luận lý: If, And, Or,… + Hàm tham chiếu và tìm kiếm: Choose, Vlookup, OffSet,… Hàm toán và lượng giác: Log, Mmult, Round,… + Hàm thống kê: Stdev, Var, CountIf,… + Hàm văn bản: Asc, Find, Text,… + 1.2 Một số hàm thường dùng [1] 1.2.1 Hàm thời gian  Hàm TIME()  Cú pháp: = TIME(hour, minute, second) - Hour: Số chỉ giờ, là một con số từ 0 đến 23. Nếu lớn hơn 23, Excel sẽ tự trừ đi một bội số của 24. - Minute: Số chỉ phút, là một con số từ 0 đến 59. Nếu lớn hơn 59, Excel sẽ tính lại và tăng số giờ lên tương ứng. - Second: Số chỉ giây, là một con số từ 0 đến 59. Nếu lớn hơn 59, Excel sẽ tính lại và tăng số phút, số giờ lên tương ứng.  Ý nghĩa: Trả về một giá trị thời gian nào đó Ví dụ: TIME(14, 45, 30) = 2:45:30 PM TIME(14, 65, 30) = 3:05:30 PM TIME(25, 85, 75) = 2:26:15 AM * Cũng như DATE(), hàm TIME() rất hữu dụng khi hour, minute, second là những công thức mà không phải là một con số, nó sẽ giúp tính toán chính xác hơn  Hàm TIMEVALUE()  Cú pháp: = TIMEVALUE(time_text) - Time_text: Chuỗi văn bản cần chuyển đổi  Ý nghĩa: chuyển đổi một chuỗi văn bản có dạng thời gian thành một giá trị thời gian để có thể tính toán được. Ví dụ: 2 TIMEVALUE("26:15") = 0.09375 (= 2:15:00 AM)  Hàm NOW( )  Cú pháp: NOW()  Ý nghĩa: Trả về ngày, giờ hiện hành của hệ thống.  Hàm TODAY( )  Cú pháp: TODAY()  Ý nghĩa: Trả về ngày hiện hành của hệ thống.  Hàm DATE( )  Cú pháp: DATE(year,month,day)  Ý nghĩa: Trả về dữ liệu kiểu ngày ứng với year, month, day  Hàm DAY( )  Cú pháp: DAY(serial_number)  Ý nghĩa: - Trả về một số nguyên có giá trị từ 1 đến 31 là ngày của serial_number. - Tính số ngày giữa 2 mốc ngày tháng dựa trên cơ sở một năm có 360 ngày =DAYS360(BTNT1, BTNT2). - Trả về mốc thời gian xảy ra trước hoặc sau mốc chỉ định =EDATE - Trả về ngày cuối cùng của tháng xảy ra trước hoặc sau mốc chỉ định =EOMONTH  Hàm NETWORKDAYS()  Cú pháp: = NETWORKDAYS(start_date, end_date [, holidays]) - Start_date, end_date: ngày tháng đại diện cho ngày bắt đầu và ngày kết thúc công việc. Nên nhập bằng hàm DATE(), hoặc dùng một kết quả trả về của một công thức khác, vì có thể sẽ xảy ra lỗi nếu bạn nhập trực tiếp ngày tháng dưới dạng text. - Holidays: danh sách những ngày nghỉ ngoài những ngày thứ Bảy và Chủ Nhật. Danh sách này có thể là một vùng đã được đặt tên. Nếu nhập trực tiếp thì phải bỏ trong cặp dấu móc {}.  Ý nghĩa: trả về số ngày làm việc trong mốc thời gian đưa ra sau khi trừ đi ngày nghĩ và ngày lễ. Ví dụ: Công thức tính số ngày làm việc giữa ngày 1/12/2010 và ngày 10/1/2011, trong đó có nghỉ ngày Noel (25/12) và ngày Tết Tây (1/1): = NETWORKDAYS("01/12/2010", "10/01/2011", {"12/25/2007", "1/1/2011"})  Hàm MONTH( )  Cú pháp: MONTH(serial_number)  Ý nghĩa: Trả về một số nguyên có giá trị từ 1 đến 12 là tháng của serial_number. 3 Trích tháng: Month; =month(now()); =month(today())  Hàm YEAR( )  Cú pháp: YEAR(serial_number)  Ý nghĩa: Trả về một số nguyên có giá trị từ 1900 đến 9999 là năm của serial_number. Hình 1.1: Hàm Year  Hàm HOUR( )  Cú pháp: HOUR(serial_number)  Ý nghĩa: Trả về một số nguyên có giá trị từ 0 đến 23 là giờ của serial_number. Ví dụ: HOUR(TIMEVALUE("9:30:20")) = 9  Hàm MINUTE( )  Cú pháp: MINUTE (serial_number)  Ý nghĩa: Trả về một số nguyên có giá trị từ 0 đến 59 là phút của serial_number. Ví dụ: HOUR (TIMEVALUE("9:30:20")) = 30  Hàm SECOND( )  Cú pháp: SECOND (serial_number)  Ý nghĩa: Trả về một số nguyên có giá trị từ 0 đến 59 là giây của serial_number. Ví dụ: SECOND(TIMEVALUE("9:30:20")) = 20 1.2.2 Hàm xử lý chuỗi  Hàm LEFT()  Cú pháp: LEFT (text, num_chars ) 4  Ý nghĩa: Trả về num_chars ký tự được lấy ra từ bên trái chuỗi text.  Hàm RIGHT()  Cú pháp: RIGHT (text, num_chars )  Ý nghĩa: Trả về num_chars ký tự được lấy ra từ bên phải chuỗi text.  Hàm LEN()  Cú pháp: LEN (text)  Ý nghĩa: Trả về số ký tự có trong chuỗi text, kể cả ký tự trắng.  Hàm MID( )  Cú pháp: MID(text,start_num,num_chars)  Ý nghĩa: Trả về num_chars ký tự được lấy ra từ chuỗi text bắt đầu từ vị trí start_num Ví dụ: MID(“Fluid Flow” ,1,5)=“Fluid” MID(“Fluid Flow” ,7,20)=“Flow” MID(“Fluid Flow“,20,5)=“”  Hàm FIND( )  Cú pháp: FIND(text)  Ý nghĩa: trích xuất họ và tên (ví dụ dùng để trích cho tên tiếng Anh, bỏ qua tên đệm) *Hướng dẫn: dùng hàm FIND() để tìm những khoảng trắng phân cách giữa họ và tên, sau đó dùng hàm LEFT() để tách phần tên, và hàm RIGHT() để tách phần họ. Để lấy phần tên (First Name), dùng công thức sau (giả sử họ tên nằm ở cell A2): =LEFT(A2, FIND(" ", A2) - 1) Nghĩa là dùng hàm FIND() để tìm vị trí của ký tự trắng đầu tiên kể từ bên trái, ví dụ nó là vị trí thứ 5, khi đó hàm LEFT() sẽ xác định được cái tên này gồm có 4 chữ (= 5-1). Để lấy phần họ (Last Name), dùng công thức: =RIGHT(A2, LEN(A2) - FIND(" ", A2)) *Trích xuất họ, tên đệm và tên (ví dụ với tên tiếng Anh, phần tên đệm được viết tắt) : cách làm giống như bài Trích xuất họ và tên ở trên, tuy nhiên có khác một chút, để trích thêm phần tên đệm. Giả sử Họ và Tên (full name) nằm ở cell A2, và đang có giá trị là Karen E. Hammond Đầu tiên, như bài trên, dùng công thức sau để tách phần Tên (first name): =LEFT(A2, FIND(" ", A2) - 1) → Karen Công thức FIND(" ", A2) sẽ cho kết quả là 6, là vị trí của khoảng trắng đầu tiên (sau chữ Karen). 5 Để tìm vị trí của khoảng trắng thứ hai, phải gán vị trí bắt đầu tìm (start_num) là 7, hoặc là bằng kết quả của FIND(" ", A2) cộng thêm 1: =FIND(" ", A2, FIND(" ",A2) + 1) Rồi dùng kết quả của công thức này làm tham số cho hàm RIGHT() để trích ra phần Họ (last name): =RIGHT(A2, LEN(A2) - FIND(" ", A2, FIND(" ", A2) +1)) → Hammond Để trích phần tên đệm, dùng hàm FIND() để tìm vị trí của dấu chấm (.) rồi đưa vào trong công thức của hàm MID() để tìm ký tự đứng trước dấu chấm: = MID(A2, FIND(".", A2) - 1, 1) → E  Hàm TRIM()  Cú pháp: TRIM (text).  Ý nghĩa: loại bỏ tất cả các ký tự trắng thừa trong chuỗi text, trừ một ký tự trắng giữa hai từ.  Hàm TEXT()  Cú pháp: TEXT (value,format_text )  Ý nghĩa: Chuyển value sang dạng chuỗi tương ứng. format_text là định dạng trong menu Format/Cell/ Number tab /Category. Ví dụ: TEXT(1200,"0.0")=“1200.0” TEXT(1200,"$0.00") =“$1200.00” TEXT(1234567,"#,##0.00")=“1,234,567.00”  Hàm VALUE()  Cú pháp: VALUE (text)  Ý nghĩa: Chuyển chuỗi text (dạng số) sang số tương ứng. Ví dụ: VALUE("$1,000")=1000 123+ VALUE(“456”)=579 VALUE("16:48:00")-VALUE("12:00:00")= 04:48:00 (=0.2) 1.2.3 Hàm thống kê  AVEDEV (number1, number2, ...) : Tính trung bình độ lệch tuyệt đối các điểm dữ liệu theo trung bình của chúng. Thường dùng làm thước đo về sự biến đổi của tập số liệu  AVERAGE (number1, number2, ...) : Tính trung bình cộng  AVERAGEA (number1, number2, ...) : Tính trung bình cộng của các giá trị, bao gồm cả những giá trị logic 6  AVERAGEIF (range, criteria1) : Tính trung bình cộng của các giá trị trong một mảng theo một điều kiện  AVERAGEIFS (range, criteria1, criteria2, ...) : Tính trung bình cộng của các giá trị trong một mảng theo nhiều điều kiện  COUNT (value1, value2, ...) : Đếm số ô trong danh sách  COUNTA (value1, value2, ...) : Đếm số ô có chứa giá trị (không rỗng) trong danh sách  COUNTBLANK (range) : Đếm các ô rỗng trong một vùng  COUNTIF (range, criteria) : Đếm số ô thỏa một điều kiện cho trước bên trong một dãy  COUNTIFS (range1, criteria1, range2, criteria2, ...) : Đếm số ô thỏa nhiều điều kiện cho trước  DEVSQ (number1, number2, ...) : Tính bình phương độ lệch các điểm dữ liệu từ trung bình mẫu của chúng, rồi cộng các bình phương đó lại.  FREQUENCY (data_array, bins_array) : Tính xem có bao nhiêu giá trị thường xuyên xuất hiện bên trong một dãy giá trị, rồi trả về một mảng đứng các số. Luôn sử dụng hàm này ở dạng công thức mảng  GEOMEAN (number1, number2, ...) : Trả về trung bình nhân của một dãy các số dương. Thường dùng để tính mức tăng trưởng trung bình, trong đó lãi kép có các lãi biến đổi được cho trước...  HARMEAN (number1, number2, ...) : Trả về trung bình điều hòa (nghịch đảo của trung bình cộng) của các số  KURT (number1, number2, ...) : Tính độ nhọn của tập số liệu, biểu thị mức nhọn hay mức phẳng tương đối của một phân bố so với phân bố chuẩn  LARGE (array, k) : Trả về giá trị lớn nhất thứ k trong một tập số liệu  MAX (number1, number2, ...) : Trả về giá trị lớn nhất của một tập giá trị  MAXA (number1, number2, ...) : Trả về giá trị lớn nhất của một tập giá trị, bao gồm cả các giá trị logic và text  MEDIAN (number1, number2, ...) : Tính trung bình vị của các số.  MIN (number1, number2, ...) : Trả về giá trị nhỏ nhất của một tập giá trị  MINA (number1, number2, ...) : Trả về giá trị nhỏ nhất của một tập giá trị, bao gồm cả các giá trị logic và text  MODE (number1, number2, ...) : Trả về giá trị xuất hiện nhiều nhất trong một mảng giá trị  PERCENTILE (array, k) : Tìm phân vị thứ k của các giá trị trong một mảng dữ liệu 7  PERCENTRANK (array, x, significance) : Trả về thứ hạng (vị trí tương đối) của một trị trong một mảng dữ liệu, là số phần trăm của mảng dữ liệu đó  PERMUT (number, number_chosen) : Trả về hoán vị của các đối tượng.  QUARTILE (array, quart) : Tính điểm tứ phân vị của tập dữ liệu. Thường được dùng trong khảo sát dữ liệu để chia các tập hợp thành nhiều nhóm...  RANK (number, ref, order) : Tính thứ hạng của một số trong danh sách các số  SKEW (number1, number2, ...) : Trả về độ lệch của phân phối, mô tả độ không đối xứng của phân phối quanh trị trung bình của nó  SMALL (array, k) : Trả về giá trị nhỏ nhất thứ k trong một tập số  STDEV (number1, number2, ...) : Ước lượng độ lệch chuẩn trên cơ sở mẫu  STDEVA (value1, value2, ...) : Ước lượng độ lệch chuẩn trên cơ sở mẫu, bao gồm cả những giá trị logic  STDEVP (number1, number2, ...) : Tính độ lệch chuẩn theo toàn thể tập hợp  STDEVPA (value1, value2, ...) : Tính độ lệch chuẩn theo toàn thể tập hợp, kể cả chữ và các giá trị logic  VAR (number1, number2, ...) : Trả về phương sai dựa trên mẫu  VARA (value1, value2, ...) : Trả về phương sai dựa trên mẫu, bao gồm cả các trị logic và text  VARP (number1, number2, ...) : Trả về phương sai dựa trên toàn thể tập hợp  VARPA (value1, value2, ...) : Trả về phương sai dựa trên toàn thể tập hợp, bao gồm cả các trị logic và text.  TRIMMEAN (array, percent) : Tính trung bình phần trong của một tập dữ liệu, bằng cách loại tỷ lệ phần trăm của các điểm dữ liệu ở đầu và ở cuối tập dữ liệu. 1.2.4 Hàm tham chiếu  Hàm VLOOKUP( )  Cú pháp: VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) Trong đó: - Lookup_value: giá trị cần tìm. - Table_array: vùng tìm kiếm. - Col_index_num: số thứ tự của cột (trong vùng table_array) chứa giá trị cần trả về. - Range_lookup: kiểu tìm kiếm. Nếu là: • • 0 (False): tìm chính xác. 1 (True): tìm tương đối. 8  Ý nghĩa: Tìm lookup_value trong cột đầu tiên của table_array, nếu thấy thì hàm trả về giá trị của ô là giao của dòng chứa lookup_value tìm thấy và cột thứ col_index_num trong table_array. Hình 1.2: Hàm Vlookup Bảng 1.1: Dữ liệu hàm Vlookup STT Số hóa đơn Ngày bán tiền bán Mã số thuế 1 30 14/4/2002 123000 t01 2 30 14/4/2002 750000 t03 3 31 15/4/2002 120000 t04 4 31 16/4/2002 45000 t01 5 31 16/4/2002 24000 t01 6 32 16/4/2002 360000 t03 7 32 17/4/2002 1400000 t04 8 32 17/4/2002 600000 t02 9 32 17/4/2002 3100000 t03 Thuế GTGT Tổng tiền Thuế suất Mã số thuế Thuế suất t01 10% Yêu cầu: Dựa vào bảng phụ hãy tính thuế giá trị phải đóng trong bảng chính 9 Hướng dẫn: tại ô thuế GTGT ta nhập công thức F2 =Vlookup(E2,$B$14:$B$18,2)*D2 Các ô dưới copy công thức từ ô F2 Cách chọn hàm Cách 1: Gọi trực tiếp tên hàm =Tên hàm(tham số của hàm) Cách 2: Dùng thực đơn hàm Chọn menu insert/Function Bảng function xuất hiện với các khung  Hàm HLOOKUP( )  Cú pháp: HLOOKUP(lookup_value, table_array, row_index_num, range_lookup) Trong đó: - Lookup_value: giá trị cần tìm. - Table_array: vùng tìm kiếm. - Row_index_num: số thứ tự của dòng (trong vùng table_array) chứa giá trị cần trả về. - Range_lookup: kiểu tìm kiếm. Nếu là: • 0 (False): tìm chính xác. • 1 (True): tìm tương đối.  Ý nghĩa: Tìm lookup_value trong dòng đầu tiên của table_array, nếu thấy thì hàm trả về giá trị của ô là giao của cột chứa lookup_value tìm thấy và dòng thứ row_index_num trong table_array.  Hàm MATCH( )  Cú pháp: MATCH(lookup_value,lookup_array,match_type) Trong đó: - Lookup_value: giá trị cần tìm. - Lookup_array: mảng 1 chiều (1 dòng/cột) chứa giá trị cần tìm. - Match_type: kiểu tìm kiếm. Nếu là: • 0: tìm chính xác. • 1: tìm tương đối. Lookup_array phải được sắp xếp tăng dần • -1: tìm tương đối.Lookup_array phải được sắp xếp giảm dần  Ý nghĩa: Trả về vị trí của lookup_value trong lookup_array. 10 Nếu match_type=0 và lookup_value là một chuỗi thì lookup_value có thể chứa ký tự thay thế ‘*’ hoặc ‘?’. Ví dụ: MATCH("b",{"a","b","c"},0) = 2. Ví dụ: MATCH(41,B2:B5,0)=4 (vị trí của 41 trong vùng B2:B5) MATCH(39,B2:B5,1)=2 (vị trí của giá trị gần nhất :38). MATCH(40,B2:B5,-1)= #N/A (vì vùng B2:B5 không được sắp xếp giảm dần) A B 1 Tên Hàng Số lượng 2 Bananas 25 3 Oranges 38 4 Apples 40 5 Pears 41 Hình 1.3: Dữ liệu hàm Match  Hàm INDEX( )  Cú pháp: INDEX(array, row_num, column_num) Trong đó: - Array: vùng chứa giá trị cần trả về. - Row_num,column_num: chỉ số dòng, cột:  Ý nghĩa: Trả về giá trị của ô (nằm trong vùng array) là giao điểm của dòng row_num và cột column_num. Nếu array chỉ có 1 dòng/cột thì chỉ cần đối số row_num/column_num Ví dụ: INDEX(B2:C5,3,2 ) = 40 INDEX(B2:C5,3,1 ) = Apples INDEX(B2:B5,2) = Oranges 11 B C Tên Hàng Số lượng 2 Bananas 25 3 Oranges 38 4 Apples 40 5 Pears 41 1 Hình 1.4: Dữ liệu hàm Index  Hàm IF()  Cú pháp: IF ( logical_test, value_if_true, value_if_false) Trong đó: - Value_if_true: nếu logical_test có giá trị True - Value_if_false: nếu logical_test có giá trị False  Ý nghĩa: Trả về một trong hai giá trị sau Ví dụ : IF( 1< 2 , “hello”, “goodbye”) → hello 1.2.5 Các hàm tài chính - Financian functions  ACCRINT (issue, first_interest, settlement, rate, par, frequency, basis, calc_method) : Tính lãi tích lũy cho một chứng khoán trả lãi theo định kỳ  ACCRINTM (issue, settlement, rate, par, basis) : Tính lãi tích lũy đối với chứng khoán trả lãi theo kỳ hạn  AMORDEGRC (cost, date_purchased, first_period, salvage, period, rate, basis) : Tính khấu hao trong mỗi tài khóa kế toán tùy theo thời hạn sử dụng của tài sản (sử dụng trong các hệ thống kế toán theo kiểu Pháp)  AMORLINC (cost, date_purchased, first_period, salvage, period, rate, basis) : Tính khấu hao trong mỗi tài khóa kế toán (sử dụng trong các hệ thống kế toán theo kiểu Pháp)  COUPDAYBS (settlement, maturity, frequency, basis) : Tính số ngày kể từ đầu kỳ lãi tới ngày kết toán  COUPDAYS (settlement, maturity, frequency, basis) : Tính số ngày trong kỳ lãi bao gồm cả ngày kết toán  COUPDAYSCN (settlement, maturity, frequency, basis) : Tính số ngày từ ngày kết toán tới ngày tính lãi kế tiếp 12  COUPNCD (settlement, maturity, frequency, basis) : Trả về một con số thể hiện ngày tính lãi kế tiếp kể từ sau ngày kết toán  COUPNUM (settlement, maturity, frequency, basis) : Tính số lần lãi suất phải trả trong khoảng từ ngày kết toán đến ngày đáo hạn  COUPPCD (settlement, maturity, frequency, basis) : Trả về một con số thể hiện ngày thanh toán lãi lần trước, trước ngày kết toán  CUMIPMT (rate, nper, pv, start_period, end_period, type) : Tính lợi tức tích lũy phải trả đối với khoản vay trong khoảng thời gian giữa start_period và end_period  CUMPRINC (rate, nper, pv, start_period, end_period, type) : Trả về tiền vốn tích lũy phải trả đối với khoản vay trong khoảng thời gian giữa start_period và end_period  DB (cost, salvage, life, period, month) : Tính khấu hao cho một tài sản sử dụng phương pháp số dư giảm dần theo một mức cố định (fixed-declining balance method) trong một khoảng thời gian xác định.  DDB (cost, salvage, life, period, factor) : Tính khấu hao cho một tài sản sử dụng phương pháp số dư giảm dần kép (double-declining balance method), hay giảm dần theo một tỷ lệ nào đó, trong một khoảng thời gian xác định.  DISC (settlement, maturity, pr, redemption, basis) : Tính tỷ lệ chiết khấu của một chứng khoán  DOLLARDE (fractional_dollar, fraction) : Chuyển đổi giá dollar ở dạng phân số sang giá dollar ở dạng thập phân  DOLLARFR (decimal_dollar, fraction) : Chuyển đổi giá dollar ở dạng thập phân số sang giá dollar ở dạng phân số  DURATION (settlement, maturity, coupon, yld, frequency, basis) : Tính thời hạn hiệu lực Macauley dựa trên đồng mệnh giá $100 (thời hạn hiệu lực là trung bình trọng giá trị hiện tại của dòng luân chuyển tiền mặt và được dùng làm thước đo về sự phản hồi làm thay đổi lợi nhuận của giá trị trái phiếu)  EFFECT (nominal_rate, npery) : Tính lãi suất thực tế hằng năm, biết trước lãi suất danh nghĩa hằng năm và tổng số kỳ thanh toán lãi kép mỗi năm  FV (rate, nper, pmt, pv, type) : Tính giá trị kỳ hạn của sự đầu tư dựa trên việc chi trả cố định theo kỳ và lãi suất cố định  FVSCHEDULE (principal, schedule) : Tính giá trị kỳ hạn của một vốn ban đầu sau khi áp dụng một chuỗi các lãi suất kép (tính giá trị kỳ hạn cho một đầu tư có lãi suất thay đổi) 13  INTRATE (settlement, maturity, investment, redemption, basis) : Tính lãi suất cho một chứng khoán đầu tư toàn bộ  IPMT (rate, per, nper, pv, fv, type) : Trả về khoản thanh toán lãi cho một đầu tư dựa trên việc chi trả cố định theo kỳ và dựa trên lãi suất không đổi  IRR (values, guess) : Tính lợi suất nội hàm cho một chuỗi các lưu động tiền mặt được thể hiện bởi các trị số  ISPMT (rate, per, nper, pv) : Tính số tiền lãi đã trả tại một kỳ nào đó đối với một khoản vay có lãi suất không đổi, sau khi đã trừ số tiền gốc phải trả cho kỳ đó. MDURATION (settlement, maturity, coupon, yld, frequency, basis) : Tính thời hạn Macauley sửa đổi cho chứng khoán dựa trên đồng mệnh giá $100  MIRR (values, finance_rate, reinvest_rate) : Tính tỷ suất doanh lợi nội tại trong một chuỗi luân chuyển tiền mặt theo chu kỳ  NOMINAL (effect_rate, npery) : Tính lãi suất danh nghĩa hằng năm, biết trước lãi suất thực tế và các kỳ tính lãi kép mỗi năm  NPER (rate, pmt, pv, fv, type) : Tính số kỳ hạn để trả khoản vay trong đầu tư dựa trên từng chu kỳ, số tiền trả và tỷ suất lợi tức cố định  NPV (rate, value1, value2, ...) : Tính hiện giá ròng của một khoản đầu tư bằng cách sử dụng tỷ lệ chiếu khấu với các chi khoản trả kỳ hạn (trị âm) và thu nhập (trị dương)  ODDFPRICE (settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, basis) : Tính giá trị trên mỗi đồng mệnh giá $100 của chứng khoán có kỳ đầu tiên lẻ (ngắn hạn hay dài hạn)  ODDFYIELD (settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, basis) : Trả về lợi nhuận của một chứng khoán có kỳ tính lãi đầu tiên là lẻ (ngắn hạn hay dài hạn)  ODDLPRICE (settlement, maturity, last_interest, rate, yld, redemption, frequency, basis) : Tính giá trị trên mỗi đồng mệnh giá $100 của chứng khoán có kỳ tính lãi phiếu cuối cùng là lẻ (ngắn hạn hay dài hạn)  ODDLYIELD (settlement, maturity, last_interest, rate, pr, redemption, frequency, basis) : Tính lợi nhuận của chứng khoán có kỳ cuối cùng là lẻ (ngắn hạn hay dài hạn) PMT (rate, nper, pv, fv, type) : Tính tiền phải trả đối với khoản vay có lãi suất không đổi và chi trả đều đặn  PPMT (rate, per, nper, pv, fv, type) : Tính khoản vốn thanh toán trong một kỳ hạn đã cho đối với một khoản đầu tư, trong đó việc chi trả được thực hiện đều đặn theo định kỳ với một lãi suất không đổi 14
- Xem thêm -

Tài liệu liên quan