Hàm thống kê Excel: Hướng dẫn có ví dụ, Phần thứ ba

Excel cung cấp một loạt các hàm thống kê thực hiện các phép tính từ hàm trung bình đến hàm phân phối thống kê và đường xu hướng phức tạp hơn.

Trong bài viết này chúng ta sẽ tìm hiểu sâu hơn về các hàm thống kê tính toán hàm đường xu hướng của Excel.

Xin lưu ý rằng một số chức năng thống kê đã được giới thiệu trong các phiên bản Excel gần đây và do đó không có sẵn trong các phiên bản cũ hơn.

Thời gian đọc ước tính: 12 minuti

Chức năng đường xu hướng

Forecast

Hàm Dự báo của Excel dự đoán một điểm trong tương lai trên đường xu hướng tuyến tính phù hợp với một tập hợp giá trị x và y nhất định.

cú pháp

= FORECAST( x, known_y's, known_x's )

đối tượng

• x: Giá trị x bằng số mà bạn muốn dự đoán giá trị y mới.
• known_y's: Một mảng các giá trị y đã biết
• known_x's: Một mảng các giá trị x đã biết

Lưu ý rằng độ dài của mảng known_x phải giống như của known_y và phương sai của known_x nó không nhất thiết phải bằng 0.

thí dụ

Trong bảng tính sau đây, hàm FORECAST Excel được sử dụng để dự đoán một điểm bổ sung dọc theo đường thẳng phù hợp nhất thông qua một loạt các giá trị x và y đã biết (được lưu trong các ô F2:F7 và G2:G7).

Như được hiển thị trong ô F7 của bảng tính, hàm tính giá trị y kỳ vọng tại x=7 là :=FORECAST( 7, G2:G7, F2:F7 )

Điều này mang lại kết quả 32.666667 .

Intercept

Trong số các chức năng dự báo của Excel, chúng tôi tìm thấy Intercept. Hàm Chặn của Excel tính toán điểm chặn (giá trị tại giao điểm của trục y) của đường hồi quy tuyến tính trên một tập hợp các giá trị x và y nhất định.

cú pháp

= INTERCEPT( known_y's, known_x's )

đối tượng

• known_y's: Một mảng các giá trị y đã biết
• known_x's: Một mảng các giá trị x đã biết

Lưu ý rằng độ dài của mảng known_x phải giống như của known_y và phương sai của known_x nó không nhất thiết phải bằng 0.

thí dụ

Bảng tính sau đây hiển thị một ví dụ về hàm Intercept của Excel được sử dụng để tính điểm mà đường hồi quy tuyến tính đi qua known_x và known_y (được liệt kê trong các ô F2:F7 và G2:G7) cắt trục y.

Le known_x và known_y được vẽ trên biểu đồ trong bảng tính.

Như được hiển thị trong ô F9 của bảng tính, công thức của hàm Chặn là :=INTERCEPT( G2:G7, F2:F7 )

mang lại kết quả 2.4 .

Slope

Một chức năng dự đoán rất thú vị khác là Độ dốc (Slope) Excel tính độ dốc của đường hồi quy tuyến tính thông qua một tập hợp giá trị x và y cho trước.

Cú pháp của hàm là:

cú pháp

= SLOPE( known_y's, known_x's )

đối tượng

• known_y's: Một mảng các giá trị y đã biết
• known_x's: Một mảng các giá trị x đã biết

Lưu ý rằng độ dài của mảng known_x phải giống như của known_y và phương sai của known_x nó không nhất thiết phải bằng 0.

thí dụ

Bảng tính sau đây hiển thị một ví dụ về hàm Slope (độ dốc) của Excel dùng để tính độ dốc của đường hồi quy tuyến tính thông qua known_x và known_y, trong các ô F2:F7 và G2:G7.

Le known_x và known_y được vẽ trên biểu đồ trong bảng tính.

Như được hiển thị trong ô F9 của bảng tính, công thức của hàm Chặn là :=SLOPE( G2:G7, F2:F7 )

mang lại kết quả 4.628571429.

Trend

Một chức năng dự báo Excel rất thú vị là KHUYNH HƯỚNG Excel (Trend) tính toán đường xu hướng tuyến tính thông qua một tập hợp giá trị y nhất định và (tùy chọn) một tập hợp giá trị x nhất định.

Sau đó, hàm mở rộng đường xu hướng tuyến tính để tính toán các giá trị y bổ sung cho một tập hợp giá trị x mới bổ sung.

Cú pháp của hàm là:

cú pháp

= TREND( known_y's, [known_x's], [new_x's], [const] )

đối tượng

• known_y's: Một mảng các giá trị y đã biết
• [known_x's]: Một hoặc nhiều mảng giá trị x đã biết. Đây là một đối số tùy chọn, nếu được cung cấp, phải có cùng độ dài với tập hợp các known_y's. Nếu bị bỏ qua, tập hợp [known_x's] nhận giá trị {1, 2, 3, …}.
• [new_x]: Đối số tùy chọn, cung cấp một hoặc nhiều mảng giá trị số đại diện cho một tập hợp các giá trị x mới mà bạn muốn tính các giá trị y mới tương ứng. Mỗi mảng [của new_x] phải khớp với một mảng [known_x's]. Nếu đối số [của new_x] bị bỏ qua, nó được đặt bằng [known_x's] .
• [trị giá]: Một đối số logic tùy chọn xác định xem hằng số 'b', trong phương trình tuyến tính y = m x + b , phải bị buộc phải bằng 0. Bản thân [chi phí] là TRUE (hoặc nếu đối số này bị bỏ qua) thì hằng số b được xử lý bình thường;
• Bản thân [chi phí] là FALSE hằng số b được đặt thành 0 và phương trình đường thẳng trở thành y = mx .

thí dụ

Trong bảng tính sau, hàm Excel Trend được sử dụng để mở rộng một chuỗi giá trị x và y nằm trên đường thẳng y = 2x + 10. Các giá trị x và y đã biết được lưu trữ trong các ô A2-B5 của bảng tính và cũng được hiển thị trong biểu đồ bảng tính.

Lưu ý rằng không nhất thiết các điểm đã cho phải khớp chính xác dọc theo đường thẳng y = 2x + 10 (mặc dù trong ví dụ này chúng đúng như vậy). Hàm Trend của Excel sẽ tìm dòng phù hợp nhất cho bất kỳ tập hợp giá trị nào bạn cung cấp.

Hàm Trend sử dụng phương pháp bình phương tối thiểu để tìm dòng phù hợp nhất và sau đó sử dụng nó để tính các giá trị y mới cho các giá trị x mới được cung cấp.

Trong ví dụ này, các giá trị của [của new_x] được lưu trữ trong các ô A8-A10 và hàm Xu hướng của Excel đã được sử dụng trong các ô B8-B10 để tìm các giá trị y tương ứng mới. Như được hiển thị trên thanh công thức, công thức là := XU HƯỚNG( B2:B5, A2:A5, A8:A10 )

Bạn thấy hàm Trend trong thanh công thức được đặt trong dấu ngoặc nhọn { }. Điều này chỉ ra rằng hàm đã được nhập dưới dạng công thức mảng .

Growth

Trong số các chức năng dự báo của Excel, chúng tôi tìm thấy Growth. Chức năng Growth Excel tính toán đường cong tăng trưởng theo cấp số nhân thông qua một bộ giá trị y nhất định và (tùy chọn), một hoặc nhiều bộ giá trị x. Sau đó, hàm mở rộng đường cong để tính các giá trị y bổ sung cho một tập hợp giá trị x mới bổ sung.

Cú pháp của hàm là:

cú pháp

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

đối tượng

• known_y's: Một mảng các giá trị y đã biết
• [known_x's]: Một hoặc nhiều mảng giá trị x đã biết. Đây là một đối số tùy chọn, nếu được cung cấp, phải có cùng độ dài với tập hợp các known_y's. Nếu bị bỏ qua, tập hợp [known_x's] nhận giá trị {1, 2, 3, …}.
• [new_x]: Một tập hợp các giá trị x mới mà hàm này tính toán các giá trị y mới tương ứng. Nếu bỏ qua, giả sử rằng tập hợp [của new_x] bằng với [known_x's] và hàm trả về các giá trị y nằm trên đường cong tăng trưởng theo cấp số nhân được tính toán.
• [trị giá]: Một đối số logic tùy chọn xác định xem hằng số 'b', trong phương trình tuyến tính y = b * m^x , phải buộc phải bằng 1. Nếu [chi phí] là TRUE (hoặc nếu đối số này bị bỏ qua) thì hằng số b được xử lý bình thường; Bản thân [chi phí] là FALSE hằng số b được đặt thành 1 và phương trình đường thẳng trở thành y = mx .

thí dụ

Trong bảng tính sau, hàm tăng trưởng Excel được sử dụng để mở rộng một chuỗi giá trị x và y nằm trên đường cong tăng trưởng theo cấp số nhân y = 5 * 2^x. Chúng được lưu trữ trong các ô A2-B5 của bảng tính và cũng xuất hiện trong biểu đồ bảng tính.

Hàm Tăng trưởng tính toán đường cong tăng trưởng theo cấp số nhân phù hợp nhất với các giá trị x và y đã biết được cung cấp. Trong ví dụ đơn giản này, đường cong phù hợp nhất là đường cong hàm mũ y = 5 * 2^x.

Khi Excel tính toán phương trình đường cong tăng trưởng theo cấp số nhân, nó có thể sử dụng nó để tính các giá trị y mới cho các giá trị x mới được cung cấp trong các ô A8-A10.

Trong ví dụ này, các giá trị của [new_x's] được lưu trữ trong các ô A8-A10 và hàm Growth của Excel đã được chèn vào ô B8-B10. Như được hiển thị trên thanh công thức, công thức cho điều này là:=Growth( B2:B5, A2:A5, A8:A10 )

Bạn có thể thấy hàm Tăng trưởng trong thanh công thức được đặt trong dấu ngoặc nhọn { }. Điều này chỉ ra rằng hàm đã được nhập dưới dạng công thức mảng .

Lưu ý rằng mặc dù các điểm trong ví dụ trên khớp chính xác dọc theo đường cong y = 5 * 2^x, nhưng điều này không cần thiết. Chức năng Growth Excel sẽ tìm đường cong phù hợp nhất cho bất kỳ tập hợp giá trị nào bạn cung cấp.

Chức năng tài chính

Hiệu ứng

Chức năng Effect Excel trả về lãi suất thực tế hàng năm cho một mức lãi suất danh nghĩa nhất định và một số kỳ hạn gộp nhất định mỗi năm.

Lãi suất thực tế hàng năm

Lãi suất thực tế hàng năm là thước đo lãi suất kết hợp vốn hóa lãi suất và thường được sử dụng để so sánh các khoản vay tài chính với các điều khoản vốn hóa khác nhau.

Lãi suất thực tế hàng năm được tính theo công thức sau:

chim bồ câu nominal_rate là lãi suất danh nghĩa e npery là số kỳ tính lãi trong năm.

Cú pháp của hàm là:

cú pháp

= EFFECT( nominal_rate, npery )

đối tượng

• nominal_rate: Lãi suất danh nghĩa (phải là giá trị số từ 0 đến 1)
• npery: Số kỳ tính lãi trong năm (phải là số nguyên dương).

thí dụ

Bảng tính sau đây hiển thị ba ví dụ về hàm Hiệu ứng Excel:

Nếu kết quả của hàm Effect hiển thị dưới dạng thập phân hoặc hiển thị 0%, cả hai vấn đề này đều có thể do định dạng của ô chứa hàm Effect.

Do đó, vấn đề có thể được giải quyết bằng cách định dạng ô theo phần trăm, với số thập phân.

Để làm điều này:

1. Chọn các ô để định dạng theo tỷ lệ phần trăm.
2. Mở hộp thoại “Định dạng ô” bằng một trong các phương pháp sau:
   • Bấm chuột phải vào ô hoặc phạm vi đã chọn và chọn tùy chọn Định dạng ô… từ menu ngữ cảnh;
   • Bấm vào Công cụ khởi động hộp thoại trong nhóm Số trên tab Trang Chủ Dải băng Excel;
   • Sử dụng phím tắt CTRL-1 (tức là chọn phím CTRL và giữ phím đó, chọn phím “1” (một).
3. Trong hộp thoại “Định dạng ô”:
   • Hãy chắc chắn rằng thẻ Số ở đầu hộp thoại được chọn.
   • lựa chọn Phần trăm từ danh sách Thể loại ở phía bên trái của hộp thoại .Thao tác này sẽ đưa ra các tùy chọn bổ sung ở bên phải hộp kiểm, cho phép bạn chọn số vị trí thập phân bạn muốn xuất hiện.
   • Sau khi đã chọn được số chữ số thập phân muốn hiển thị, hãy nhấp vào OK .

Nominal

Chức năng Nominal Excel trả về lãi suất danh nghĩa cho một mức lãi suất thực tế nhất định và một số kỳ hạn gộp nhất định mỗi năm.

Cú pháp của hàm là:

cú pháp

= NOMINAL( effect_rate, npery )

đối tượng

• effect_rate: Lãi suất thực tế (giá trị số từ 0 đến 1).
• npery: Số kỳ tính lãi trong năm (phải là số nguyên dương).

thí dụ

Trong bảng tính sau đây, hàm Nominal của Excel được dùng để tính lãi suất danh nghĩa của ba khoản vay có kỳ hạn khác nhau.

Ercole Palmeri