Đề Xuất 6/2023 # Cách Vẽ Tóm Tắt Trong Excel Kinh Tế Lượng. Hồi Quy Trong Excel # Top 15 Like

Cập nhật nội dung chi tiết về Cách Vẽ Tóm Tắt Trong Excel Kinh Tế Lượng. Hồi Quy Trong Excel mới nhất trên website Techcombanktower.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

Phân tích hồi quy trong Microsoft Excel – hướng dẫn toàn diện nhất để sử dụng MS Excel để giải quyết các vấn đề phân tích hồi quy trong lĩnh vực kinh doanh thông minh. Konrad Karlberg giải thích các vấn đề lý thuyết một cách dễ tiếp cận, kiến u200bu200bthức về nó sẽ giúp bạn tránh nhiều sai lầm cả khi tự mình thực hiện phân tích hồi quy và khi đánh giá kết quả phân tích do người khác thực hiện. Tất cả các tài liệu, từ các phép tương quan đơn giản và phép thử t đến phép phân tích nhiều hiệp phương sai, đều dựa trên các ví dụ trong thế giới thực và đi kèm với các quy trình chi tiết từng bước.

Konrad Karlberg. Phân tích hồi quy trong Microsoft Excel. – M .: Phép biện chứng, 2017 .– 400 tr.

Tải xuống ghi chú ở định dạng hoặc, ví dụ ở định dạng

Chương 1. Đánh giá độ biến dữ liệu

Các nhà thống kê có nhiều chỉ số về sự thay đổi (biến đổi) theo ý của họ. Một trong số đó là tổng bình phương độ lệch của các giá trị riêng lẻ so với giá trị trung bình. Excel sử dụng hàm SQUARE () cho nó. Nhưng phương sai được sử dụng phổ biến hơn. Phương sai là bình phương trung bình của độ lệch. Phương sai không nhạy cảm với số lượng giá trị trong tập dữ liệu quan tâm (trong khi tổng bình phương độ lệch tăng theo số thứ nguyên).

Excel cung cấp hai hàm trả về phương sai: VAR.G () và VAR.V ():

Sử dụng hàm DISP.G () nếu các giá trị được xử lý tạo thành một tập hợp. Nghĩa là, các giá trị có trong phạm vi là giá trị duy nhất mà bạn quan tâm.

Sử dụng hàm chúng tôi () nếu các giá trị được xử lý tạo thành một mẫu từ một tập hợp lớn hơn. Giả định rằng có các giá trị bổ sung có phương sai mà bạn cũng có thể ước tính.

Nếu một đại lượng như giá trị trung bình hoặc hệ số tương quan được tính toán dựa trên tổng thể, thì nó được gọi là tham số. Một giá trị tương tự được tính toán trên cơ sở một mẫu được gọi là thống kê. Đếm ra độ lệch từ mức trung bình trong tập hợp này, bạn nhận được tổng bình phương của các độ lệch có độ lớn nhỏ hơn nếu bạn đếm chúng từ bất kỳ giá trị nào khác. Một tuyên bố tương tự cũng đúng cho phương sai.

Cỡ mẫu càng lớn thì giá trị thống kê được tính toán càng chính xác. Nhưng không có mẫu nào có kích thước nhỏ hơn kích thước của tổng thể chung, do đó bạn có thể chắc chắn rằng giá trị của thống kê trùng với giá trị của tham số.

Tổng bình phương trung bình của mẫu đưa ra ước tính thấp hơn về phương sai của tổng thể. Phương sai được tính theo cách này được gọi là dời chỗ thẩm định, lượng định, đánh giá. Hóa ra là để loại trừ độ chệch và có được ước tính không chệch, chỉ cần chia tổng bình phương của độ lệch không cho nỞ đâu n là kích thước mẫu và n – 1.

Bản chất của sự khác biệt giữa các hàm DISP.G () và DISP.B () như sau:

Trong VARP.G (), tổng bình phương được chia cho số lần quan sát và do đó đại diện cho ước tính chệch của phương sai, giá trị trung bình thực.

Trong hàm VAR. Trong (), tổng bình phương được chia cho số lần quan sát trừ đi 1, tức là bằng số bậc tự do, ước tính chính xác hơn, không thiên vị về phương sai của tổng thể mà từ đó mẫu được trích xuất.

Độ lệch chuẩn (tương tác độ lệch chuẩn, SD) là căn bậc hai của phương sai:

Bình phương độ lệch chuyển đổi thang đo lường sang một số liệu khác, là bình phương của số liệu ban đầu: mét – thành mét vuông, đô la – thành đô la vuông, v.v. Độ lệch chuẩn là căn bậc hai của phương sai và do đó trả chúng ta về các đơn vị ban đầu. Cái nào thuận tiện hơn.

Thông thường cần phải tính toán độ lệch chuẩn sau khi dữ liệu đã được thực hiện một số thao tác. Và mặc dù trong những trường hợp này, kết quả chắc chắn là độ lệch chuẩn, chúng thường được gọi là lỗi tiêu chuẩn… Có một số loại sai số chuẩn, bao gồm sai số chuẩn về phép đo, sai số chuẩn về tỷ lệ, sai số chuẩn của giá trị trung bình.

Giả sử bạn thu thập dữ liệu chiều cao từ 25 người đàn ông trưởng thành được chọn ngẫu nhiên ở mỗi tiểu bang trong số 50 tiểu bang. Tiếp theo, bạn tính toán chiều cao trung bình của nam giới trưởng thành ở mỗi tiểu bang. Lần lượt, 50 giá trị trung bình thu được có thể được coi là các quan sát. Từ đó, bạn có thể tính toán độ lệch chuẩn của chúng, đó là sai số chuẩn của giá trị trung bình… Nhân vật: 1. so sánh phân phối của 1.250 giá trị cá nhân cơ bản (dữ liệu về chiều cao của 25 nam giới ở mỗi tiểu bang trong số 50 tiểu bang) với phân phối của trung bình 50 tiểu bang. Công thức ước tính sai số chuẩn của giá trị trung bình (tức là độ lệch chuẩn của phương tiện, không phải các quan sát riêng lẻ):

Trong thống kê, có một thỏa thuận về việc sử dụng các chữ cái Hy Lạp và Latinh để biểu thị các đại lượng thống kê. Thông thường, biểu thị các thông số của dân số chung bằng chữ cái Hy Lạp và số liệu thống kê mẫu bằng tiếng Latinh. Do đó, khi chúng ta nói về độ lệch chuẩn của dân số, chúng ta viết nó là σ; nếu độ lệch chuẩn của mẫu được xem xét, thì chúng ta sử dụng ký hiệu s. Đối với các ký hiệu để chỉ định giá trị trung bình, chúng không thống nhất với nhau. Trung bình dân số được ký hiệu bằng chữ cái Hy Lạp μ. Tuy nhiên, ký hiệu X̅ thường được sử dụng để biểu thị giá trị trung bình của mẫu.

điểm z thể hiện vị trí quan sát trong phân bố theo đơn vị độ lệch chuẩn. Ví dụ, z u003d 1,5 có nghĩa là quan sát là 1,5 độ lệch chuẩn so với giá trị trung bình theo hướng giá trị cao hơn. Kỳ hạn điểm z được sử dụng cho các đánh giá cá nhân, tức là cho các phép đo được chỉ định cho các mục riêng lẻ trong mẫu. Đối với các nhà thống kê như vậy (ví dụ: trung bình của tiểu bang), hãy sử dụng thuật ngữ giá trị z:

Giả sử bạn là người hướng dẫn tại một câu lạc bộ chơi gôn. Bạn đã có khả năng đo phạm vi trong một thời gian dài và bạn biết rằng mức trung bình là 205 thước Anh và độ lệch chuẩn là 36 thước Anh. Bạn đã được cung cấp một câu lạc bộ mới và tuyên bố rằng nó sẽ tăng phạm vi của bạn thêm 10 thước. Bạn yêu cầu mỗi người trong số 81 người chơi câu lạc bộ tiếp theo thử một lần đánh thử với một cây gậy mới và ghi lại phạm vi của họ. Nó chỉ ra rằng phạm vi đánh trung bình của câu lạc bộ mới là 215 mét. Xác suất để chênh lệch 10 yard (215 – 205) chỉ do lỗi lấy mẫu là bao nhiêu? Hay nói một cách khác: khả năng xảy ra trong thử nghiệm lớn hơn, câu lạc bộ mới sẽ không thể hiện sự gia tăng phạm vi so với mức trung bình dài hạn hiện có là 205 yard là gì?

Chúng ta có thể kiểm tra điều này bằng cách tạo giá trị z. Sai số chuẩn của giá trị trung bình:

Đối số thứ hai của hàm chúng tôi () có thể nhận hai giá trị: TRUE – hàm trả về diện tích của vùng dưới đường cong bên trái của điểm được chỉ định bởi đối số đầu tiên; FALSE – hàm trả về chiều cao của đường cong tại điểm được chỉ định bởi đối số đầu tiên.

Excel có hai hàm cho phân phối t, còn được gọi là phân phối t của Student: chúng tôi () trả về diện tích bên dưới đường cong ở bên trái của giá trị t đã cho và chúng tôi () trả về bên phải.

Chương 2. Tương quan

Tương quan là thước đo mối quan hệ giữa các phần tử của một tập hợp các cặp có thứ tự. Mối tương quan được đặc trưng bởi hệ số tương quan Pearson– r. Hệ số có thể nhận các giá trị trong phạm vi từ -1.0 đến +1.0.

Tùy thuộc vào giá trị của hệ số tương quan chung (thường được ký hiệu bằng chữ cái Hy Lạp ρ ), Hệ số tương quan r đưa ra một ước tính chệch, với tác động của độ chệch tăng khi kích thước mẫu giảm. Tuy nhiên, chúng tôi không cố gắng sửa sai lệch này theo cách, ví dụ, chúng tôi đã làm khi tính độ lệch chuẩn, khi chúng tôi thay thế không phải số quan sát mà là số bậc tự do vào công thức tương ứng. Trên thực tế, số lượng quan sát được sử dụng để tính hiệp phương sai không ảnh hưởng đến độ lớn.

Các quan sát được sử dụng để vẽ biểu đồ đường đều cách đều nhau dọc theo trục hoành. Các dấu tích dọc theo trục này chỉ là nhãn, không phải giá trị số.

Trong khi mối tương quan thường có nghĩa là quan hệ nhân quả, nó không thể được sử dụng làm bằng chứng cho thấy nó đúng. Thống kê không được sử dụng để chứng minh một lý thuyết là đúng hay sai. Để loại trừ các giải thích cạnh tranh về kết quả quan sát, hãy đặt các thí nghiệm có kế hoạch… Số liệu thống kê được sử dụng để tóm tắt thông tin thu thập được trong quá trình các thí nghiệm như vậy và để định lượng khả năng một quyết định được đưa ra có thể sai với cơ sở bằng chứng sẵn có.

Chương 3. Hồi quy đơn giản

Tìm phương tiện, độ lệch chuẩn và hệ số tương quan (phạm vi A14: C18). Tính điểm z khu vực (E2: E12). Ví dụ: ô EZ chứa công thức: u003d (B3- $ B $ 14) / $ B $ 15. Tính điểm số z của giá dự báo (F2: F12). Ví dụ, ô F3 chứa công thức: u003d EZ * $ B $ 18. Chuyển đổi điểm số z sang giá đô la (H2: H12). Trong ô НЗ, công thức là: u003d F3 * $ C $ 15 + $ C $ 14.

Lưu ý rằng giá trị dự đoán luôn có xu hướng di chuyển về phía trung bình bằng 0. Hệ số tương quan càng gần 0, điểm z dự đoán càng gần 0. Trong ví dụ của chúng tôi, hệ số tương quan giữa khu vực và giá bán là 0,67 và giá dự báo là 1,0 * 0,67, tức là 0,67. Điều này tương ứng với phần vượt quá giá trị so với giá trị trung bình, bằng hai phần ba độ lệch chuẩn. Nếu hệ số tương quan bằng 0,5, thì giá dự báo sẽ là 1,0 * 0,5, tức là 0,5. Điều này tương ứng với giá trị vượt quá giá trị trung bình, chỉ bằng một nửa độ lệch chuẩn. Bất cứ khi nào giá trị của hệ số tương quan khác với giá trị lý tưởng, tức là lớn hơn -1,0 và nhỏ hơn 1,0, ước tính của biến được dự đoán phải gần với giá trị trung bình của nó hơn ước tính của biến dự đoán (độc lập) của chính nó. Hiện tượng này được gọi là hồi quy về giá trị trung bình, hay đơn giản là hồi quy.

u003d SLOPE (known_y’s, known_x’s)

Đây tại Là biến được dự đoán, và x Là biến độc lập. Bạn phải tuân thủ nghiêm ngặt thứ tự này của các biến. Hệ số góc hồi quy, hệ số tương quan, độ lệch chuẩn của các biến và hiệp phương sai có quan hệ mật thiết với nhau (Hình 6). INTERCEPT () trả về giá trị bị cắt bởi dòng hồi quy trên trục tung:

Lưu ý rằng số lượng giá trị x và y được cung cấp cho các hàm SLOPE () và INTERCEPT () làm đối số phải giống nhau.

Trong phân tích hồi quy, một chỉ số quan trọng khác được sử dụng – R 2 (R-square), hoặc hệ số xác định. Nó xác định mối quan hệ được xác định bằng hồi quy đóng góp như thế nào vào sự biến đổi dữ liệu tổng thể. x và tại… Excel có hàm KVPIRSON () cho nó, hàm này nhận các đối số chính xác giống như hàm CORREL ().

Hai biến có hệ số tương quan khác 0 giữa chúng được cho là giải thích phương sai hoặc có phương sai giải thích. Thông thường, phương sai được giải thích được biểu thị dưới dạng phần trăm. Vì thế R 2 u003d 0,81 có nghĩa là 81% phương sai (chênh lệch) của hai biến được giải thích. 19% còn lại là do biến động ngẫu nhiên.

Excel có hàm TREND giúp tính toán dễ dàng hơn. Hàm TREND ():

lấy các giá trị đã biết mà bạn cung cấp x và các giá trị đã biết tại;

tính toán độ dốc của đường hồi quy và một hằng số (đoạn);

trả về giá trị dự đoán tạiđược xác định bằng cách áp dụng phương trình hồi quy cho các giá trị đã biết x (hình 7).

Hàm TREND () là một hàm mảng (nếu bạn chưa gặp các hàm như vậy trước đây, tôi khuyên bạn nên dùng nó).

Để nhập hàm TREND () dưới dạng công thức mảng trong ô G3: G12, hãy chọn phạm vi G3: G12, nhập công thức TREND (SZ: C12; OZ: B12), nhấn và giữ các phím và chỉ sau đó nhấn phím … Lưu ý rằng công thức được đặt trong dấu ngoặc nhọn: (và). Đây là cách Excel thông báo cho bạn rằng công thức này được hiểu là công thức mảng. Không tự nhập các dấu ngoặc đơn: nếu bạn cố gắng tự nhập chúng như một phần của công thức, Excel sẽ diễn giải đầu vào của bạn là một chuỗi văn bản thông thường.

Nếu bạn có ba biến và bạn muốn xác định mối tương quan giữa hai trong số chúng, loại trừ ảnh hưởng của biến thứ ba, bạn có thể sử dụng tương quan một phần… Giả sử bạn quan tâm đến mối quan hệ giữa tỷ lệ phần trăm cư dân thành phố tốt nghiệp đại học và số lượng sách trong các thư viện thành phố. Bạn đã thu thập dữ liệu cho 50 thành phố, nhưng … Vấn đề là cả hai thông số này có thể phụ thuộc vào sức khỏe của cư dân của một thành phố cụ thể. Tất nhiên, rất khó để tìm thấy 50 thành phố khác có cùng mức độ hạnh phúc.

Bằng cách sử dụng các kỹ thuật thống kê để loại trừ tác động của phúc lợi đối với cả hỗ trợ tài chính cho thư viện và sự sẵn có của trường đại học, bạn có thể định lượng tốt hơn mối quan hệ giữa các biến quan tâm, cụ thể là số lượng sách và số sinh viên tốt nghiệp. Mối tương quan có điều kiện này giữa hai biến, khi giá trị của các biến khác là cố định, được gọi là tương quan một phần. Một cách để tính toán nó là sử dụng phương trình:

Một khả năng khác là xác định mối tương quan nửa riêng tư. Ví dụ, bạn đang nghiên cứu ảnh hưởng của chiều cao và tuổi tác lên cân nặng. Vì vậy, bạn có hai biến dự báo, chiều cao và tuổi, và một biến dự báo, cân nặng. Bạn muốn loại bỏ ảnh hưởng của một biến dự báo này đối với biến dự báo khác, nhưng không ảnh hưởng đến biến dự báo:

Chương 4. Hàm LINEST ()

LINEST () trả về 10 thống kê hồi quy. LINEST () là một hàm mảng. Để nhập nó, hãy chọn một phạm vi chứa năm hàng và hai cột, nhập công thức và nhấn (hình 9):

LINEST (B2: B21; A2: A21; TRUE; TRUE)

LINEST () trả về:

hệ số hồi quy (hoặc độ dốc, ô D2);

phân đoạn (hoặc hằng số, ô E3);

sai số tiêu chuẩn của hệ số hồi quy và hằng số (phạm vi D3: E3);

hệ số xác định R 2 cho hồi quy (ô D4);

sai số tiêu chuẩn của ước tính (ô E4);

Kiểm định F cho hồi quy đầy đủ (ô D5);

số bậc tự do cho tổng bình phương còn lại (ô E5);

hồi quy tổng bình phương (ô D6);

tổng dư của bình phương (ô E6).

Lỗi tiêu chuẩn trong trường hợp của chúng tôi, nó là độ lệch chuẩn được tính cho các lỗi lấy mẫu. Đó là, đây là một tình huống khi dân số chung có một thống kê, và mẫu có một thống kê khác. Bằng cách chia hệ số hồi quy cho sai số chuẩn, bạn nhận được 2,092 / 0,818 u003d 2,559. Nói cách khác, hệ số hồi quy 2,092 là hai sai số tiêu chuẩn rưỡi cách 0.

Nếu hệ số hồi quy bằng 0, thì giá trị trung bình của biến dự đoán là ước lượng tốt nhất. Hai sai số tiêu chuẩn rưỡi là một con số khá lớn và bạn có thể an toàn giả định rằng hệ số hồi quy cho tổng thể là khác không.

Bạn có thể xác định xác suất nhận được hệ số hồi quy mẫu là 2,092 nếu giá trị thực của nó trong tổng thể là 0,0 bằng cách sử dụng hàm

STUDENT.DIST.RF (t-test u003d 2,559; bậc tự do u003d 18)

Nói chung, số bậc tự do u003d n – k – 1, trong đó n là số quan sát và k là số biến dự báo.

Công thức này trả về 0,00987 hoặc 1% được làm tròn. Nó cho chúng ta biết rằng nếu hệ số hồi quy dân số là 0%, thì xác suất lấy được một mẫu gồm 20 người mà hệ số hồi quy ước tính là 2,092 là một% khiêm tốn.

Kiểm định F (ô D5 trong Hình 9) thực hiện chức năng tương tự đối với hồi quy hoàn toàn như kiểm định t đối với hệ số của hồi quy theo cặp đơn giản. Kiểm định F được sử dụng để kiểm tra xem liệu hệ số xác định R 2 của hồi quy có đủ lớn để bác bỏ giả thuyết rằng trong tổng thể chung nó có giá trị là 0,0 hay không, điều này cho thấy sự vắng mặt của phương sai được giải thích bởi công cụ dự đoán và biến dự đoán. Nếu chỉ có một biến dự báo, kiểm định F chính xác bằng bình phương của kiểm định t.

Cho đến nay, chúng tôi đã xem xét các biến khoảng thời gian. Nếu bạn có các biến có thể nhận một số giá trị, là các tên đơn giản, ví dụ: Người và Phụ nữ hoặc Bò sát, Lưỡng cư và Cá, hãy biểu diễn chúng dưới dạng mã số. Các biến như vậy được gọi là danh nghĩa.

Thống kê R 2 định lượng tỷ lệ phương sai được giải thích.

Sai số chuẩn của ước lượng. Trong bộ lễ phục. 4.9 cho thấy các giá trị dự đoán của biến Cân nặng, thu được trên cơ sở mối quan hệ của nó với biến Chiều cao. Phạm vi E2: E21 chứa các giá trị dư của biến Trọng lượng. Chính xác hơn, những phần dư này được gọi là sai số – do đó có thuật ngữ sai số tiêu chuẩn của ước lượng.

Sai số tiêu chuẩn của ước lượng càng nhỏ, phương trình hồi quy càng chính xác và bất kỳ dự đoán nào được đưa ra bởi phương trình sẽ càng khớp với quan sát thực tế mà bạn mong đợi. Sai số chuẩn của một ước tính cung cấp một cách để định lượng những kỳ vọng này. Cân nặng của 95% những người có chiều cao nhất định sẽ nằm trong khoảng:

(chiều cao * 2.092 – 3.591) ± 2.092 * 21.118

Thống kê F Là tỷ lệ giữa phương sai giữa các nhóm và phương sai nội nhóm. Tên này được đặt ra bởi nhà thống kê George Snedecor để vinh danh Ngài, người đã phát triển Phân tích phương sai (ANOVA) vào đầu thế kỷ 20.

Hệ số xác định R 2 thể hiện tỷ lệ của tổng bình phương liên kết với hồi quy. Giá trị (1 – R 2) biểu thị phân số của tổng bình phương liên kết với phần dư – sai số dự đoán. Kiểm định F có thể đạt được bằng cách sử dụng LINEST (ô F5 trong Hình 11), sử dụng tổng bình phương (phạm vi G10: J11), sử dụng các phân số của phương sai (phạm vi G14: J15). Các công thức có thể được kiểm tra trong tệp Excel đính kèm.

Khi sử dụng các biến danh nghĩa, mã hóa giả được sử dụng (Hình 12). Thật thuận tiện khi sử dụng các giá trị 0 và 1 để mã hóa các giá trị. Xác suất F được tính bằng cách sử dụng hàm:

F.DIST.RF (K2; I2; I3)

Ở đây, hàm chúng tôi () trả về xác suất đạt được tiêu chí F tuân theo phân phối F trung tâm (Hình 13) cho hai tập dữ liệu có bậc tự do được cho trong ô I2 và I3, giá trị của nó giống với giá trị được cho trong ô K2.

Chương 5. Hồi quy nhiều lần

Khi bạn đi từ hồi quy theo cặp đơn giản với một biến dự báo sang hồi quy nhiều biến, bạn thêm một hoặc nhiều biến dự báo. Lưu trữ giá trị của các biến dự báo trong các cột liền kề, chẳng hạn như cột A và B cho hai yếu tố dự đoán hoặc A, B và C cho ba yếu tố dự đoán. Trước khi bạn nhập công thức bao gồm LINEST (), hãy chọn năm hàng và bao nhiêu cột có biến dự đoán, cộng thêm một cột nữa cho hằng số. Trong trường hợp hồi quy có hai biến dự báo, có thể sử dụng cấu trúc sau:

LINEST (A2: A41; B2: C41 ;; TRUE)

Tương tự như vậy trong trường hợp ba biến:

LINEST (A2: A61; B2: D61 ;; TRUE)

Giả sử bạn muốn nghiên cứu những tác động có thể có của tuổi tác và chế độ ăn uống đối với LDL, một loại lipoprotein mật độ thấp được cho là nguyên nhân gây ra mảng xơ vữa động mạch gây ra chứng xơ vữa động mạch (Hình 14).

R 2 của hồi quy bội (được phản ánh trong ô F13) lớn hơn R 2 của bất kỳ hồi quy đơn giản nào (E4, H4). Hồi quy nhiều lần sử dụng nhiều biến dự báo cùng một lúc. Trong trường hợp này, R 2 hầu như luôn luôn tăng.

Đối với bất kỳ phương trình hồi quy tuyến tính đơn giản nào với một biến dự báo, sẽ luôn có mối tương quan hoàn hảo giữa các giá trị dự đoán và giá trị của biến dự báo, vì trong phương trình như vậy, các giá trị dự báo được nhân với một hằng số và một hằng số khác được thêm vào mỗi sản phẩm. Hiệu ứng này không tồn tại trong hồi quy bội.

Hiển thị kết quả trả về bởi LINEST () cho hồi quy bội (Hình 15). Hệ số hồi quy được hiển thị như một phần của kết quả trả về bởi LINEST () theo thứ tự ngược lại của các biến (G – H – I tương ứng với C – B – A).

Các nguyên tắc và thủ tục được sử dụng trong phân tích hồi quy với một biến dự báo có thể dễ dàng thích ứng để giải thích cho nhiều biến dự báo. Nó chỉ ra rằng phần lớn sự thích ứng này phụ thuộc vào việc loại bỏ ảnh hưởng của các biến dự báo lên nhau. Loại thứ hai được kết hợp với các mối tương quan một phần và nửa riêng tư (Hình 16).

Trong Excel, các hàm có sẵn cung cấp thông tin về phân phối t- và F. Các hàm có tên bao gồm một phần của DIST, chẳng hạn như chúng tôi () và chúng tôi (), lấy t- hoặc F-test làm đối số và trả về xác suất nhìn thấy giá trị được chỉ định. Các hàm có tên bao gồm một phần của OBR, chẳng hạn như chúng tôi () và chúng tôi (), lấy giá trị xác suất làm đối số và trả về giá trị tiêu chí tương ứng với xác suất được chỉ định.

Vì chúng tôi đang tìm các giá trị tới hạn của phân phối t cắt bỏ các cạnh của vùng đuôi của nó, chúng tôi chuyển 5% làm đối số cho một trong các hàm chúng tôi (), trả về giá trị tương ứng với xác suất này (Hình 17, 18).

Bằng cách thiết lập quy tắc quyết định trong trường hợp vùng alpha có một phía, bạn tăng sức mạnh thống kê của thử nghiệm. Nếu khi bắt đầu thử nghiệm, bạn chắc chắn rằng bạn có mọi lý do để mong đợi hệ số hồi quy dương (hoặc âm), thì bạn nên thực hiện thử nghiệm một phía. Trong trường hợp này, khả năng bạn đưa ra quyết định đúng đắn, bác bỏ giả thuyết về hệ số hồi quy bằng 0 trong tổng thể, sẽ cao hơn.

Các nhà thống kê thích sử dụng thuật ngữ này kiểm tra định hướng thay vì thuật ngữ kiểm tra một bên và điều khoản kiểm tra vô hướng thay vì thuật ngữ kiểm tra hai bên… Các thuật ngữ định hướng và không định hướng được ưu tiên hơn vì chúng nhấn mạnh loại giả thuyết hơn là bản chất của các đuôi phân phối.

Một cách tiếp cận dựa trên mô hình để đánh giá ảnh hưởng của các yếu tố dự báo. Trong bộ lễ phục. 19 cho thấy kết quả của một phân tích hồi quy kiểm tra sự đóng góp của biến Chế độ ăn uống vào phương trình hồi quy.

Chương 6. Phân tích hồi quy Các giả định và cảnh báo

Thuật ngữ “giả định” không được định nghĩa đủ chặt chẽ và cách nó được sử dụng cho thấy rằng nếu giả định không được đáp ứng, thì kết quả của toàn bộ phân tích ít nhất là có nghi vấn hoặc có thể không hợp lệ. Trên thực tế, không phải như vậy, mặc dù tất nhiên, có những trường hợp khi vi phạm giả định sẽ thay đổi hoàn toàn bức tranh. Các giả định cơ bản: a) phần dư của biến Y được phân phối chuẩn tại bất kỳ điểm X nào dọc theo đường hồi quy; b) Các giá trị Y phụ thuộc tuyến tính vào các giá trị X; c) phương sai của các phần dư xấp xỉ như nhau tại mỗi điểm X; d) không có sự phụ thuộc giữa các chất dư.

Nếu dữ liệu không phù hợp với các giả định hồi quy tuyến tính, bạn có các cách tiếp cận khác với hồi quy tuyến tính. Một trong số đó là hồi quy logistic (Hình 20). Trong vùng lân cận của giới hạn trên và giới hạn dưới của biến dự báo, hồi quy tuyến tính dẫn đến các dự đoán không thực tế.

Trong bộ lễ phục. 6.8 cho thấy kết quả của hai phương pháp phân tích dữ liệu nhằm điều tra mối quan hệ giữa thu nhập hàng năm và khả năng mua nhà. Rõ ràng, khả năng mua hàng sẽ tăng lên khi thu nhập ngày càng tăng. Biểu đồ giúp bạn dễ dàng phát hiện ra sự khác biệt giữa kết quả dự đoán khả năng mua nhà bằng cách sử dụng hồi quy tuyến tính và kết quả bạn có thể nhận được bằng cách tiếp cận khác.

Theo cách nói của nhà thống kê, việc loại bỏ một giả thuyết rỗng khi nó thực sự đúng được gọi là lỗi Loại I.

Trong kiến u200bu200btrúc thượng tầng Phân tích dữ liệu cung cấp một công cụ thuận tiện để tạo các số ngẫu nhiên, cho phép người dùng chỉ định hình dạng mong muốn của phân phối (ví dụ: Chuẩn, Nhị thức hoặc Poisson), cũng như giá trị trung bình và độ lệch chuẩn.

Sự khác biệt giữa các hàm của họ chúng tôi (). Bắt đầu từ Excel 2010, ba dạng khác nhau của hàm có sẵn trả về tỷ lệ phân phối ở bên trái và / hoặc bên phải của một giá trị thử nghiệm t nhất định. Hàm chúng tôi () trả về phần diện tích bên dưới đường cong phân phối ở bên trái của giá trị kiểm tra t mà bạn chỉ định. Giả sử bạn có 36 trường hợp, do đó số bậc tự do để phân tích là 34 và phép thử t là 1,69. Trong trường hợp này, công thức

ĐIỂM CỦA SINH VIÊN (+1,69; 34; ĐÚNG)

trả về 0,05 hoặc 5% (Hình 21). Đối số thứ ba của hàm chúng tôi () có thể là TRUE hoặc FALSE. Nếu được đặt thành TRUE, hàm trả về diện tích tích lũy dưới đường cong bên trái của phép thử t được chỉ định, được biểu thị dưới dạng phân số. Nếu là FALSE, hàm trả về độ cao tương đối của đường cong tại điểm tương ứng với phép thử t. Các phiên bản khác của hàm chúng tôi () – chúng tôi () và STUDENT.DIST.2X () – chỉ lấy giá trị kiểm tra t và số bậc tự do làm đối số và không yêu cầu đối số thứ ba.

Để xác định khu vực bên phải tiêu chí t, hãy sử dụng một trong các công thức:

1 – chúng tôi (1, 69; 34; TRUE)

STUDENT.DIST.PH (1,69; 34)

Toàn bộ diện tích dưới đường cong phải là 100%, vì vậy trừ đi 1 phần diện tích ở bên trái của phép thử t, hàm trả về sẽ cho phần diện tích ở bên phải của phép thử t. Bạn có thể thấy thích hợp hơn khi lấy trực tiếp phần diện tích mà bạn quan tâm bằng cách sử dụng hàm chúng tôi (), trong đó RH là viết tắt của phần đuôi bên phải của phân phối (Hình 22).

Sử dụng các hàm chúng tôi () hoặc chúng tôi () giả sử rằng bạn đã chọn một giả thuyết làm việc có định hướng. Giả thuyết làm việc theo hướng, kết hợp với cài đặt alpha là 5%, có nghĩa là bạn đặt tất cả 5% vào đuôi bên phải của các bản phân phối. Bạn sẽ chỉ cần bác bỏ giả thuyết vô hiệu nếu xác suất của giá trị kiểm định t của bạn là 5% trở xuống. Các giả thuyết định hướng thường dẫn đến các thử nghiệm thống kê nhạy hơn (độ nhạy lớn hơn này còn được gọi là sức mạnh thống kê hơn).

Trong thử nghiệm vô hướng, giá trị alpha vẫn ở cùng mức 5%, nhưng phân phối sẽ khác. Vì bạn phải cho phép hai kết quả, nên xác suất dương tính giả phải được phân phối giữa hai phần đuôi của phân phối. Nó thường được chấp nhận để phân phối xác suất này như nhau (Hình 23).

STUDENT.DIST.2X (1,69; 34)

Không vì lý do cụ thể nào, STUDENT.DIST.2X () trả về mã lỗi #NUM! Nếu nó được cung cấp một phép thử t âm làm đối số đầu tiên của nó.

Nếu các mẫu chứa các lượng dữ liệu khác nhau, hãy sử dụng thử nghiệm t hai mẫu với các phương sai khác nhau được bao gồm trong gói Phân tích dữ liệu.

Chương 7. Sử dụng hồi quy để kiểm tra sự khác biệt giữa các phương tiện nhóm

Các biến trước đây được gọi là biến dự đoán sẽ được gọi là biến kết quả trong chương này và thuật ngữ nhân tố biến sẽ được sử dụng thay cho biến dự báo.

Cách tiếp cận đơn giản nhất để mã hóa một biến danh nghĩa là mã hóa giả (hình 24).

Khi sử dụng bất kỳ loại mã hóa giả nào, cần tuân thủ các quy tắc sau:

Số cột dành riêng cho dữ liệu mới phải bằng số lượng cấp nhân tố trừ đi

Mỗi vectơ đại diện cho một mức yếu tố.

Đối tượng của một cấp, thường là nhóm đối chứng, nhận mã 0 trong tất cả các vectơ.

Công thức trong ô F2: H6 u003d LINEST (A2: A22; C2: D22 ;; TRUE) trả về thống kê hồi quy. Để so sánh, Hình. 24 cho thấy kết quả của phân tích phương sai truyền thống được trả về bởi công cụ Phân tích phương sai một chiều cấu trúc thượng tầng Phân tích dữ liệu.

Mã hóa hiệu ứng.Trong một loại mã hóa khác được gọi là hiệu ứng mã hóa, giá trị trung bình của mỗi nhóm được so sánh với giá trị trung bình của nhóm. Khía cạnh này của mã hóa hiệu ứng là do việc sử dụng -1 thay vì 0 làm mã cho một nhóm nhận cùng một mã trong tất cả các vectơ mã (Hình 25).

Khi mã hóa giả được sử dụng, giá trị hằng được trả về bởi LINEST () là giá trị trung bình của nhóm mà mã 0 được gán trong tất cả các vectơ (thường là nhóm điều khiển). Trong trường hợp hiệu ứng mã hóa, hằng số bằng tổng trung bình (ô J2).

Mô hình tuyến tính tổng quát là một cách hữu ích để khái niệm hóa các thành phần của giá trị của biến kết quả:

Y ij u003d μ + α j + ε ij

Việc sử dụng các chữ cái Hy Lạp thay cho các chữ cái Latinh trong công thức này nhấn mạnh thực tế là nó đề cập đến dân số mà từ đó các mẫu được lấy ra, nhưng nó có thể được viết lại dưới dạng biểu thị rằng nó đề cập đến các mẫu được trích xuất từ u200bu200btổng thể đã công bố:

Y ij u003d Y̅ + a j + e ij

Ý tưởng là mỗi quan sát Y ij có thể được xem là tổng của ba thành phần sau: tổng trung bình, μ; hiệu ứng xử lý j, và j; giá trị của e ij, đại diện cho độ lệch của chỉ tiêu định lượng riêng lẻ Y ij so với giá trị kết hợp của giá trị trung bình tổng thể và ảnh hưởng của phương pháp xử lý thứ j (Hình 26). Mục tiêu của phương trình hồi quy là giảm thiểu tổng bình phương của các phần dư.

Phân tích nhân tố.Nếu mối quan hệ giữa biến hiệu quả và hai hoặc nhiều yếu tố được khảo sát đồng thời, thì trong trường hợp này, chúng ta sử dụng phân tích nhân tố. Việc bổ sung một hoặc nhiều yếu tố vào ANOVA đơn biến có thể làm tăng sức mạnh thống kê. Trong ANOVA đơn biến, sự thay đổi trong một biến kết quả không thể được quy cho một nhân tố được bao gồm trong bình phương trung bình còn lại. Nhưng nó cũng có thể là biến thể này được dệt kim với một yếu tố khác. Sau đó, biến thể này có thể được loại bỏ khỏi sai số bình phương gốc, sự giảm xuống dẫn đến tăng giá trị kiểm tra F và do đó làm tăng sức mạnh thống kê của kiểm tra. Kiến trúc thượng tầng Phân tích dữ liệu bao gồm một công cụ cung cấp xử lý hai yếu tố cùng một lúc (Hình 27).

Điểm chính là cả Giới tính (Cột chú thích trong ô E28) và Điều trị (Mẫu chú thích trong ô E27) đều được đưa vào bảng ANOVA dưới dạng nguồn biến thể. Mức trung bình của nam giới khác với mức trung bình của phụ nữ và điều này tạo ra một nguồn khác biệt. Phương tiện cho ba phương pháp điều trị cũng khác nhau – đây là một nguồn biến thể khác. Ngoài ra còn có một nguồn thứ ba, Tương tác, đề cập đến tác động tổng hợp của các biến Giới tính và Điều trị.

Chương 8. Phân tích hiệp phương sai

Phân tích Covariation (ANCOVA) làm giảm độ chệch và tăng sức mạnh thống kê. Tôi xin nhắc bạn rằng một trong những cách để đánh giá độ tin cậy của phương trình hồi quy là kiểm định F:

F u003d Hồi quy MS / Thặng dư MS

trong đó MS (Mean Square) là bình phương trung bình và các chỉ số Hồi quy và Thặng dư cho biết các thành phần hồi quy và thặng dư, tương ứng. Thặng dư MS được tính theo công thức:

Dư lượng MS u003d Dư lượng SS / Dư lượng df

trong đó SS (Sum of Squares) là tổng các bình phương và df là số bậc tự do. Khi bạn thêm hiệp phương sai vào một phương trình hồi quy, một số phần nhỏ của tổng bình phương tổng không được bao gồm trong SS ResiduaI mà trong SS Regression. Điều này dẫn đến giảm SS Residua l, và do đó MS dư. Thặng dư MS càng thấp, điểm F càng lớn và bạn càng có nhiều khả năng bác bỏ giả thuyết vô hiệu rằng không có sự khác biệt giữa các phương tiện. Kết quả là bạn phân phối lại độ biến thiên của biến kết quả. Trong ANOVA, khi hiệp phương sai không được tính đến, sự biến động sẽ trở thành lỗi. Nhưng trong ANCOVA, một số biến thể trước đây được cho là do lỗi được gán cho hiệp biến và trở thành một phần của Hồi quy SS.

Hãy xem xét một ví dụ trong đó cùng một tập dữ liệu được phân tích trước tiên với ANOVA và sau đó với ANCOVA (Hình 28).

Nghiên cứu so sánh tác động tương đối của việc tập thể dục, giúp tăng cường sức mạnh cơ bắp và tập thể dục nhận thức (giải ô chữ), giúp kích thích hoạt động của não. Các đối tượng được phân chia ngẫu nhiên vào hai nhóm để cả hai nhóm đều ở trong điều kiện như nhau khi bắt đầu thí nghiệm. Sau ba tháng, các đặc điểm nhận thức của các đối tượng đã được đo lường. Kết quả của các phép đo này được thể hiện trong cột B.

Phạm vi A2: C21 chứa dữ liệu ban đầu được chuyển đến hàm LINEST () để thực hiện phân tích bằng cách sử dụng các hiệu ứng mã hóa. Kết quả của hàm LINEST () được hiển thị trong phạm vi E2: F6, trong đó ô E2 hiển thị hệ số hồi quy được liên kết với vectơ hành động. Ô E8 chứa thử nghiệm t u003d 0,93 và ô E9 kiểm tra độ tin cậy của thử nghiệm t này. Giá trị trong ô E9 chỉ ra rằng xác suất gặp phải sự khác biệt giữa các phương tiện của nhóm được quan sát trong thí nghiệm này là 36% nếu trong tổng thể chung, các phương tiện của nhóm bằng nhau. Chỉ một số ít công nhận kết quả này là có ý nghĩa thống kê.

Trong bộ lễ phục. 29 cho thấy điều gì sẽ xảy ra khi các hiệp biến được thêm vào phân tích. Trong trường hợp này, tôi đã thêm tuổi của từng đối tượng vào tập dữ liệu. Hệ số xác định R 2 cho phương trình hồi quy sử dụng hiệp biến là 0,80 (ô F4). Giá trị R 2 trong phạm vi F15: G19, trong đó tôi sao chép kết quả ANOVA thu được mà không sử dụng hiệp biến, chỉ là 0,05 (ô F17). Do đó, một phương trình hồi quy bao gồm một hiệp biến dự đoán các giá trị của biến Điểm nhận thức chính xác hơn nhiều so với việc sử dụng riêng véc tơ Tác động. Đối với ANCOVA, xác suất vô tình đạt được điểm F hiển thị trong ô F5 là nhỏ hơn 0,01%.

Xử lý thống kê dữ liệu cũng có thể được thực hiện bằng cách sử dụng bổ trợ GÓI PHÂN TÍCH(hình 62).

Từ các mục được đề xuất, anh ta chọn mục ” HỒI QUY “Và nhấp vào nó bằng nút chuột trái. Sau đó bấm OK.

Cửa sổ hiển thị trong Hình. 63.

Công cụ phân tích ” HỒI QUY “Được sử dụng để điều chỉnh một biểu đồ cho một tập hợp các quan sát bằng phương pháp bình phương nhỏ nhất. Hồi quy được sử dụng để phân tích tác động lên một biến phụ thuộc riêng lẻ của các giá trị của một hoặc nhiều biến giải thích. Ví dụ, một số yếu tố ảnh hưởng đến thành tích thể thao của một vận động viên, bao gồm tuổi, chiều cao và cân nặng. Bạn có thể tính toán tác động của từng yếu tố trong ba yếu tố này đến thành tích của một vận động viên, sau đó sử dụng dữ liệu đó để dự đoán thành tích của một vận động viên khác.

Công cụ hồi quy sử dụng hàm CUỐI CÙNG.

Hộp thoại REGRESSION

Nhãn Chọn hộp kiểm nếu hàng đầu tiên hoặc cột đầu tiên của phạm vi đầu vào chứa tiêu đề. Bỏ chọn hộp kiểm này nếu không có tiêu đề. Trong trường hợp này, các tiêu đề thích hợp cho dữ liệu bảng đầu ra sẽ được tạo tự động.

Mức độ tin cậy Chọn hộp kiểm để bao gồm một mức bổ sung trong bảng tổng sản lượng. Trong trường thích hợp, hãy nhập mức độ tin cậy để áp dụng, ngoài mức 95% mặc định.

Constant – zero Chọn hộp kiểm để làm cho đường hồi quy đi qua điểm gốc.

Khoảng cách đầu ra Nhập tham chiếu đến ô trên cùng bên trái của phạm vi đầu ra. Phân bổ ít nhất bảy cột cho bảng tổng sản lượng, sẽ bao gồm: kết quả ANOVA, hệ số, sai số chuẩn của phép tính Y, độ lệch chuẩn, số lần quan sát, sai số chuẩn cho các hệ số.

Trang tính mới Chọn công tắc này để mở trang tính mới trong sổ làm việc và chèn kết quả phân tích bắt đầu từ ô A1. Nếu cần, hãy nhập tên cho trang tính mới vào trường đối diện với vị trí công tắc tương ứng.

Sổ làm việc mới Bấm vào nút chuyển sang vị trí này để tạo một sổ làm việc mới, trong đó kết quả sẽ được thêm vào một trang tính mới.

Phần dư Chọn hộp kiểm để bao gồm phần dư trong bảng đầu ra.

Phần dư được tiêu chuẩn hóa Chọn hộp kiểm để bao gồm phần dư được tiêu chuẩn hóa trong bảng đầu ra.

Vẽ phần dư Chọn hộp kiểm để vẽ phần dư cho mỗi biến độc lập.

Lô phù hợp Chọn hộp kiểm để vẽ các giá trị dự đoán so với quan sát.

Lô xác suất bình thường Chọn hộp để vẽ đồ thị xác suất thông thường.

Để thực hiện các phép tính, hãy chọn ô mà chúng ta muốn hiển thị giá trị trung bình bằng con trỏ và nhấn phím u003d trên bàn phím. Tiếp theo, trong trường Tên, hãy chỉ định chức năng mong muốn, chẳng hạn TRUNG BÌNH CỘNG (hình 22).

Chức năng CUỐI CÙNG tính toán thống kê cho một chuỗi bằng cách sử dụng bình phương nhỏ nhất để tính đường thẳng phù hợp nhất với dữ liệu có sẵn và sau đó trả về một mảng mô tả đường thẳng kết quả. Bạn cũng có thể kết hợp chức năng CUỐI CÙNG với các hàm khác để tính toán các loại mô hình khác tuyến tính với các tham số chưa biết (có tham số chưa biết là tuyến tính), bao gồm chuỗi đa thức, lôgarit, hàm mũ và lũy thừa. Vì một mảng giá trị được trả về nên hàm phải được chỉ định dưới dạng công thức mảng.

Phương trình của một đường thẳng như sau:

y u003d m 1 x 1 + m 2 x 2 +… + b (trong trường hợp có một số dãy giá trị x),

trong đó giá trị phụ thuộc y là một hàm của giá trị x độc lập, các giá trị m là các hệ số tương ứng với mỗi biến x độc lập và b là một hằng số. Lưu ý rằng y, x và m có thể là vectơ. Chức năng CUỐI CÙNG trả về một mảng (mn; mn-1;…; m 1; b). CUỐI CÙNG cũng có thể trả về thống kê hồi quy bổ sung.

CUỐI CÙNG(known_y’s; known_x’s; const; Statistics)

Đã biết_y’s là tập hợp các giá trị y đã biết cho mối quan hệ y u003d mx + b.

Nếu known_y’s có một cột, thì mỗi cột của known_x’s được hiểu là một biến riêng biệt.

Nếu known_y’s có một hàng duy nhất, thì mỗi hàng của known_x’s được hiểu là một biến riêng biệt.

Đã biết_x là một tập hợp các giá trị x tùy chọn đã được biết cho y u003d mx + b.

Known_x’s có thể chứa một hoặc nhiều bộ biến. Nếu chỉ một biến được sử dụng, thì known_y’s và known_x’s có thể có bất kỳ hình dạng nào, miễn là chúng có cùng thứ nguyên. Nếu nhiều biến được sử dụng, thì known_y’s phải là một vectơ (nghĩa là cao một hàng hoặc rộng một cột).

Nếu array_ known_x’s bị bỏ qua, thì mảng này (1; 2; 3; …) được giả sử có cùng kích thước với array_ known_y’s.

Const là một giá trị Boolean cho biết hằng số b có được yêu cầu bằng 0 hay không.

Nếu const là TRUE hoặc bị bỏ qua, hằng số b được đánh giá theo cách thông thường.

Nếu đối số “const” là FALSE, thì giá trị của b được đặt bằng 0 và các giá trị của m được chọn để thỏa mãn quan hệ y u003d mx.

Thống kê là một giá trị Boolean cho biết có trả về thống kê hồi quy bổ sung hay không.

Nếu thống kê là TRUE, LINEST trả về thống kê hồi quy bổ sung. Mảng được trả về sẽ có dạng như sau: (mn; mn-1; …; m1; b: sen; sen-1; …; se1; seb: r2; sey: F; df: ssreg; ssresid).

Nếu thống kê là FALSE hoặc bị bỏ qua, hàm LINEST chỉ trả về các hệ số m và hằng số b.

Thống kê hồi quy bổ sung. (Bảng 17)

se1, se2, …, sen

Giá trị sai số tiêu chuẩn cho các hệ số m1, m2, …, mn.

seb

Giá trị lỗi tiêu chuẩn cho hằng số b (seb u003d # N / A nếu const là FALSE).

r2

Hệ số tất định. Các giá trị y thực tế được so sánh với các giá trị thu được từ phương trình của đường thẳng; Dựa trên kết quả so sánh, hệ số xác định được tính toán, chuẩn hóa từ 0 đến 1. Nếu nó bằng 1 thì hoàn toàn có mối tương quan với mô hình, tức là không có sự khác biệt giữa giá trị y thực tế và ước tính. Ngược lại, nếu hệ số xác định là 0, thì việc sử dụng phương trình hồi quy để dự đoán các giá trị y là vô nghĩa. Để biết thêm thông tin về cách tính r2, hãy xem Chú thích ở cuối phần này.

sey

Sai số tiêu chuẩn cho ước lượng của y.

F

Thống kê F hoặc giá trị quan sát F. Thống kê F được sử dụng để xác định xem mối quan hệ quan sát giữa các biến phụ thuộc và biến độc lập có phải là ngẫu nhiên hay không.

df

ssreg

Hồi quy tổng bình phương.

ssresid

Tổng dư của bình phương. Để biết thêm thông tin về cách tính các giá trị ssreg và ssresid, hãy xem Chú thích ở cuối phần này.

Hình sau đây cho thấy thứ tự mà các thống kê hồi quy bổ sung được trả về (Hình 64).

Ghi chú:

Bất kỳ đường thẳng nào cũng có thể được mô tả bằng hệ số góc của nó và giao điểm với trục y:

Độ dốc (m): để xác định hệ số góc của một đoạn thẳng, thường ký hiệu là m, bạn cần lấy hai điểm của đoạn thẳng (x 1, y 1) và (x 2, y 2); hệ số góc sẽ là (y 2 -y 1) / (x 2 -x 1).

Giao điểm Y (b): Giao điểm y của một đường, thường được ký hiệu là b, là giá trị y của điểm tại đó đường thẳng giao với trục y.

Phương trình đường thẳng có dạng y u003d mx + b. Nếu bạn biết giá trị của m và b, thì bạn có thể tính điểm bất kỳ trên đường thẳng bằng cách thay các giá trị y hoặc x vào phương trình. Bạn cũng có thể sử dụng hàm TREND.

Nếu chỉ có một biến độc lập x, bạn có thể nhận trực tiếp hệ số góc và hệ số chặn y bằng các công thức sau:

Độ dốc: INDEX (LINEST (known_y’s; known_x’s); 1)

Giao lộ Y: INDEX (LINEST (known_y’s; known_x’s); 2)

Độ chính xác của ước lượng dòng LINEST phụ thuộc vào mức độ phân tán trong dữ liệu. Dữ liệu càng gần đường thẳng thì mô hình LINEST càng chính xác. LINEST sử dụng bình phương nhỏ nhất để xác định mức độ phù hợp nhất với dữ liệu. Khi chỉ có một biến độc lập x, m và b được tính bằng các công thức sau:

trong đó x và y là phương tiện mẫu, chẳng hạn như x u003d AVERAGE (known_x’s) và y u003d AVERAGE (known_y’s).

Các hàm phù hợp LINEST và LOGEST có thể tính toán đường cong thẳng hoặc hàm mũ mô tả tốt nhất dữ liệu. Tuy nhiên, họ không trả lời câu hỏi kết quả nào trong hai kết quả phù hợp hơn để giải quyết công việc đang làm. Bạn cũng có thể tính TREND (known_y’s; known_x’s) cho một đường thẳng hoặc TĂNG TRƯỞNG (known_y’s; known_x’s) cho một đường cong hàm mũ. Các hàm này, nếu bạn không chỉ định new_x_values, hãy trả về một mảng các giá trị y được tính toán cho các giá trị x thực dọc theo một đường thẳng hoặc đường cong. Các giá trị được tính toán sau đó có thể được so sánh với các giá trị thực tế. Bạn cũng có thể xây dựng biểu đồ để so sánh trực quan.

Với phân tích hồi quy, Microsoft Excel sẽ tính toán bình phương của chênh lệch giữa giá trị y dự đoán và giá trị y thực tế cho mỗi điểm. Tổng của các chênh lệch bình phương này được gọi là tổng bình phương còn lại (ssresid). Sau đó, Microsoft Excel sẽ tính toán tổng số bình phương (sstotal). Nếu const u003d TRUE hoặc không có giá trị nào cho đối số này được chỉ định, thì tổng bình phương là tổng bình phương của sự khác biệt giữa giá trị y thực tế và giá trị y trung bình. Khi const u003d FALSE, tổng bình phương sẽ bằng tổng bình phương của các giá trị thực của y (mà không trừ giá trị trung bình của y từ giá trị thương của y). Tổng hồi quy của bình phương sau đó có thể được tính như sau: ssreg u003d sstotal – ssresid. Tổng dư của bình phương càng nhỏ, giá trị của hệ số xác định r2 càng lớn, điều này cho thấy phương trình thu được bằng cách sử dụng phân tích hồi quy giải thích mối quan hệ giữa các biến tốt như thế nào. Hệ số r2 là ssreg / sstotal.

Trong một số trường hợp, một hoặc nhiều cột X (để giá trị Y và X nằm trong các cột) không có giá trị dự đoán bổ sung trong các cột X. Nói cách khác, việc xóa một hoặc nhiều cột X có thể dẫn đến các giá trị Y được tính với cùng độ chính xác. Trong trường hợp này, các cột X dư thừa sẽ bị loại khỏi mô hình hồi quy. Hiện tượng này được gọi là “tính thẳng hàng” vì các cột X dư thừa có thể được biểu diễn dưới dạng tổng của nhiều cột không dư thừa. LINEST kiểm tra tính cộng tuyến và loại bỏ bất kỳ cột X dư thừa nào khỏi mô hình hồi quy nếu tìm thấy chúng. Các cột X đã xóa có thể được xác định trong đầu ra LINEST bằng hệ số 0 và giá trị se bằng 0. Việc xóa một hoặc nhiều cột vì thừa sẽ thay đổi giá trị df vì nó phụ thuộc vào số lượng cột X thực sự được sử dụng cho mục đích dự đoán. Để biết thêm thông tin về cách tính df, hãy xem Ví dụ 4. Khi df thay đổi do loại bỏ các cột dư thừa, sey và F cũng thay đổi. Tính cộng gộp thường không được khuyến khích. Tuy nhiên, nó nên được sử dụng nếu một số cột X chứa 0 hoặc 1 làm chỉ báo cho biết đối tượng của thử nghiệm có thuộc một nhóm riêng biệt hay không. Nếu const u003d TRUE hoặc bị bỏ qua, LINEST sẽ chèn thêm một cột X để lập mô hình giao điểm. Nếu có một cột có giá trị 1 cho nam và 0 cho nữ và cũng có một cột có giá trị 1 cho nữ và 0 cho nam, thì cột cuối cùng sẽ bị xóa vì các giá trị của nó có thể lấy từ cột có “chỉ báo về giới tính nam”.

Cách tính df cho trường hợp các cột của X không bị xóa khỏi mô hình do tính thẳng hàng như sau: nếu có k cột của known_x’s và giá trị của const u003d TRUE hoặc không được chỉ định, thì df u003d n – k – 1. Nếu const u003d FALSE thì df u003d n – k. Trong cả hai trường hợp, loại bỏ các cột X do tính thẳng hàng sẽ làm tăng giá trị df lên 1.

Công thức trả về mảng phải được nhập dưới dạng công thức mảng.

Khi nhập một mảng hằng số, chẳng hạn như known_x’s, hãy sử dụng dấu chấm phẩy để phân tách các giá trị trên cùng một dòng và dấu hai chấm để phân tách các dòng. Các ký tự phân tách khác nhau tùy thuộc vào các tùy chọn được đặt trong cửa sổ Ngôn ngữ và Tiêu chuẩn trên bảng điều khiển.

Cần lưu ý rằng các giá trị y được dự đoán bởi phương trình hồi quy có thể không đúng nếu chúng nằm ngoài phạm vi giá trị y đã được sử dụng để xác định phương trình.

Thuật toán chính được sử dụng trong hàm CUỐI CÙNG, khác với thuật toán hàm chính INCLINE và PHÂN ĐOẠN DÒNG… Sự khác biệt giữa các thuật toán có thể dẫn đến các kết quả khác nhau cho dữ liệu không xác định và cộng tuyến. Ví dụ: nếu điểm dữ liệu của known_y’s là 0 và điểm dữ liệu của known_x’s là 1, thì:

Chức năng CUỐI CÙNG trả về giá trị bằng 0. Thuật toán hàm CUỐI CÙNG được sử dụng để trả về các giá trị hợp lệ cho dữ liệu thẳng hàng, trong trường hợp này có thể tìm thấy ít nhất một câu trả lời.

Hàm SLOPE và INTERCEPT trả về lỗi # DIV / 0 !. Thuật toán hàm SLOPE và INTERCEPT chỉ được sử dụng để tìm kiếm một câu trả lời và trong trường hợp này có thể có một số.

Ngoài việc tính toán thống kê cho các loại hồi quy khác, LINEST có thể được sử dụng để tính toán phạm vi cho các loại hồi quy khác bằng cách nhập các hàm của biến x và y dưới dạng chuỗi x và y cho LINEST. Ví dụ, công thức sau:

LINEST (giá trị y, giá trị x ^ COLUMN ($ A: $ C))

hoạt động bằng cách có một cột giá trị Y và một cột giá trị X để tính giá trị gần đúng cho một hình lập phương (đa thức bậc 3) có dạng sau:

y u003d m 1 x + m 2 x 2 + m 3 x 3 + b

Công thức có thể được thay đổi để tính toán các loại hồi quy khác, nhưng trong một số trường hợp, cần phải điều chỉnh các giá trị đầu ra và các thống kê khác.

Kết nối gói phân tích

Tuy nhiên, để sử dụng chức năng cho phép bạn thực hiện phân tích hồi quy, trước tiên bạn cần kích hoạt Gói phân tích. Chỉ khi đó các công cụ cần thiết cho quy trình này mới xuất hiện trên dải băng Excel.

Di chuyển đến tab “Tệp”.

Chuyển đến phần “Tham số”.

Cửa sổ Tùy chọn Excel mở ra. Chuyển đến phần phụ “Tiện ích bổ sung”.

Ở cuối cửa sổ mở ra, hãy di chuyển công tắc trong khối “Điều khiển” sang vị trí “Phần bổ trợ Excel”, nếu công tắc ở vị trí khác. Nhấp vào nút “Go”.

Một cửa sổ bổ trợ Excel có sẵn sẽ mở ra. Chúng tôi đánh dấu chọn bên cạnh mục “Gói phân tích”. Nhấp vào nút “OK”.

Có một số loại hồi quy:

Chúng tôi sẽ nói chi tiết hơn về hiệu suất của loại phân tích hồi quy cuối cùng trong Excel.

Hồi quy tuyến tính trong Excel

Phương trình hồi quy tuyến tính tổng quát như sau: Y u003d a0 + a1x1 +… + akhk. Trong công thức này, Y có nghĩa là biến số, ảnh hưởng của các yếu tố mà chúng ta đang cố gắng nghiên cứu. Trong trường hợp của chúng tôi, đây là số lượng người mua. Giá trị x là các yếu tố khác nhau ảnh hưởng đến biến. Các tham số a là hệ số hồi quy. Đó là, chính họ là người xác định tầm quan trọng của một yếu tố cụ thể. Chỉ số k cho biết tổng số các yếu tố giống nhau này.

Kết quả của phân tích hồi quy được hiển thị dưới dạng một bảng ở nơi được chỉ định trong cài đặt.

Một chỉ số quan trọng khác nằm trong ô ở giao điểm của hàng “Giao điểm Y” và cột “Hệ số”. Nó cho biết giá trị Y sẽ có, và trong trường hợp của chúng tôi, đây là số lượng người mua, với tất cả các yếu tố khác bằng không. Trong bảng này, giá trị này là 58,04.

Giá trị tại giao điểm của cột “Biến X1” và “Hệ số” cho thấy mức độ phụ thuộc của Y vào X. Trong trường hợp của chúng ta, đây là mức độ phụ thuộc của số lượng khách hàng của cửa hàng vào nhiệt độ. Tỷ lệ 1,31 được coi là một chỉ số tác động khá cao.

Như bạn thấy, sử dụng Microsoft Excel, việc tạo một bảng phân tích hồi quy khá dễ dàng. Tuy nhiên, chỉ một người được đào tạo mới có thể làm việc với dữ liệu thu được ở đầu ra và hiểu được bản chất của chúng.

Bài báo này hữu ích với bạn?

ŷ là giá trị mong đợi của y với một giá trị cho trước của x,

x là một biến độc lập,

a – đoạn trên trục y cho một đường thẳng,

b – hệ số góc của đường thẳng.

Hình bên dưới thể hiện khái niệm này bằng đồ thị:

Hình trên cho thấy đường thẳng được mô tả bởi phương trình ŷ u003d 2 + 0.5x. Trục y là giao điểm của trục y; trong trường hợp của chúng ta a u003d 2. Hệ số góc của đoạn thẳng, b, tỷ số giữa độ tăng của đoạn thẳng và độ dài của đoạn thẳng, có giá trị là 0,5. Độ dốc dương có nghĩa là đường tăng từ trái sang phải. Nếu b u003d 0, đường nằm ngang, nghĩa là không có mối quan hệ giữa các biến phụ thuộc và độc lập. Nói cách khác, việc thay đổi giá trị x không ảnh hưởng đến giá trị y.

Ŷ và y thường bị nhầm lẫn. Biểu đồ cho thấy 6 cặp điểm và một đoạn thẳng có thứ tự theo phương trình này

Hình này cho thấy điểm tương ứng với cặp thứ tự x u003d 2 và y u003d 4. Lưu ý rằng giá trị kỳ vọng của y theo dòng tại x u003d 2 là ŷ. Chúng ta có thể xác nhận điều này bằng phương trình sau:

ŷ u003d 2 + 0,5x u003d 2 +0,5 (2) u003d 3.

Giá trị y là điểm thực tế và giá trị là giá trị y dự kiến u200bu200bsử dụng phương trình tuyến tính tại một giá trị x nhất định.

Bước tiếp theo là xác định phương trình tuyến tính phù hợp nhất với tập các cặp có thứ tự, chúng ta đã nói về điều này trong bài trước, nơi chúng ta xác định dạng của phương trình bằng phương pháp bình phương nhỏ nhất.

Sử dụng Excel để xác định hồi quy tuyến tính

Để sử dụng công cụ phân tích hồi quy được tích hợp trong Excel, bạn phải kích hoạt phần bổ trợ Gói phân tích… Bạn có thể tìm thấy nó bằng cách nhấp vào tab Tệp -u003e Tùy chọn(2007+), trong hộp thoại xuất hiện Thông sốExcelđi đến tab Tiện ích bổ sung.Trong lĩnh vực Điều khiểnchọn Tiện ích bổ sungExcelvà bấm vào Đi.Trong cửa sổ xuất hiện, hãy đặt dấu kiểm đối diện Gói phân tích,chúng tôi nhấn ĐỒNG Ý.

Trong tab Dữ liệutrong một nhóm Phân tíchmột nút mới sẽ xuất hiện Phân tích dữ liệu.

Để chứng minh cách thức hoạt động của phần bổ trợ, hãy sử dụng dữ liệu từ bài viết trước, nơi một chàng trai và một cô gái ngồi chung bàn trong phòng tắm. Nhập dữ liệu cho ví dụ về bồn tắm của chúng tôi vào cột A và B của trang tính trắng.

Đi tới tab Dữ liệu,trong một nhóm Phân tíchnhấp chuột Phân tích dữ liệu.Trong cửa sổ xuất hiện Phân tích dữ liệu lựa chọn hồi quy như hình và bấm OK.

Đặt các tham số hồi quy bắt buộc trong cửa sổ hồi quy, như thể hiện trên hình:

Những kết quả này phù hợp với những kết quả chúng tôi thu được bằng cách tự tính toán trong bài viết trước.

Các loại hồi quy

Bản thân khái niệm này đã được đưa vào toán học bởi Francis Galton năm 1886. Hồi quy xảy ra:

ví dụ 1

Chúng ta hãy xem xét vấn đề xác định sự phụ thuộc của số lượng lao động nghỉ việc vào tiền lương bình quân tại 6 doanh nghiệp công nghiệp.

Bài tập. Sáu doanh nghiệp đã phân tích mức lương bình quân hàng tháng và số lượng nhân viên tự ý bỏ việc. Ở dạng bảng, chúng ta có:

Đối với bài toán xác định sự phụ thuộc của số lao động nghỉ việc vào tiền lương bình quân tại 6 doanh nghiệp, mô hình hồi quy có dạng phương trình Y u003d a0 + a1 × 1 +… + akxk, trong đó xi là biến ảnh hưởng, ai là hệ số hồi quy, và k là số nhân tố.

Đối với nhiệm vụ này, Y là chỉ số nhân viên nghỉ việc, và yếu tố ảnh hưởng là tiền lương, chúng ta ký hiệu là X.

Sử dụng khả năng của bộ xử lý bảng Excel

Phân tích hồi quy trong Excel phải được đặt trước bằng việc áp dụng các hàm tích hợp cho dữ liệu dạng bảng hiện có. Tuy nhiên, cho những mục đích này, tốt hơn là sử dụng phần bổ trợ “Gói Phân tích” rất hữu ích. Để kích hoạt nó, bạn cần:

từ tab “Tệp” chuyển đến phần “Tham số”;

trong cửa sổ mở ra, chọn dòng “Bổ trợ”;

nhấp vào nút “Bắt đầu” nằm bên dưới, bên phải dòng “Điều khiển”;

đánh dấu vào bên cạnh tên “Gói phân tích” và xác nhận hành động của bạn bằng cách nhấp vào “OK”.

Nếu mọi thứ được thực hiện chính xác, nút bắt buộc sẽ xuất hiện ở bên phải của tab “Dữ liệu”, nằm phía trên trang tính “Excel”.

Hồi quy tuyến tính trong Excel

Bây giờ chúng ta đã có trong tay tất cả các công cụ ảo cần thiết để thực hiện các phép tính toán kinh tế, chúng ta có thể bắt đầu giải quyết vấn đề của mình. Đối với điều này:

bấm vào nút “Phân tích dữ liệu”;

trong cửa sổ mở ra, nhấp vào nút “Hồi quy”;

trong tab xuất hiện, nhập phạm vi giá trị cho Y (số lượng nhân viên nghỉ việc) và X (lương của họ);

chúng tôi xác nhận hành động của mình bằng cách nhấn nút “Ok”.

Kết quả là chương trình sẽ tự động điền vào trang tính mới của bộ xử lý bảng tính với dữ liệu phân tích hồi quy. Ghi chú! Excel có khả năng xác định độc lập vị trí mà bạn thích cho mục đích này. Ví dụ: đó có thể là cùng một trang tính chứa các giá trị Y và X hoặc thậm chí là một sổ làm việc mới được thiết kế đặc biệt để lưu trữ dữ liệu đó.

Phân tích kết quả hồi quy cho R-Square

Trong Excel, dữ liệu thu được trong quá trình xử lý dữ liệu của ví dụ được đề cập là:

Trước hết, bạn nên chú ý đến giá trị của R-square. Nó đại diện cho hệ số xác định. Trong ví dụ này, R bình phương u003d 0,755 (75,5%), tức là các tham số được tính toán của mô hình giải thích mối quan hệ giữa các tham số được xem xét bằng 75,5%. Giá trị của hệ số xác định càng cao thì mô hình được chọn càng được coi là có thể áp dụng cho một nhiệm vụ cụ thể. Người ta tin rằng nó mô tả đúng tình huống thực tế khi giá trị bình phương R trên 0,8. Nếu bình phương R là tcr, thì giả thuyết về sự không đáng kể của số hạng tự do của phương trình tuyến tính bị bác bỏ.

Trong bài toán đang xem xét về một số hạng tự do sử dụng công cụ Excel, ta nhận được rằng t u003d 169.20903 và p u003d 2,89E-12, tức là chúng ta có xác suất bằng không để giả thuyết đúng về sự không đáng kể của số hạng tự do sẽ bị bác bỏ. Đối với hệ số tại thời điểm chưa biết t u003d 5,79405, và p u003d 0,001158. Nói cách khác, xác suất giả thuyết đúng về sự không đáng kể của hệ số với ẩn số sẽ bị bác bỏ là 0,12%.

Do đó, có thể lập luận rằng phương trình hồi quy tuyến tính thu được là đầy đủ.

Vấn đề hiệu lực của việc mua một khối cổ phiếu

Hồi quy nhiều lần trong Excel được thực hiện bằng cùng một công cụ Phân tích dữ liệu. Hãy xem xét một nhiệm vụ được áp dụng cụ thể.

Ban lãnh đạo của công ty “NNN” phải quyết định việc cố vấn mua 20% cổ phần của Công ty cổ phần “MMM”. Chi phí của gói thầu (liên doanh) là 70 triệu USD. Các chuyên gia của NNN đã thu thập dữ liệu về các giao dịch tương tự. Người ta đã quyết định đánh giá giá trị của khối cổ phiếu bằng các thông số như vậy, được biểu thị bằng hàng triệu đô la Mỹ, như:

các khoản phải trả (VK);

doanh thu hàng năm (VO);

các khoản phải thu (VD);

nguyên giá tài sản cố định (SOF).

Ngoài ra, tham số là khoản nợ lương của công ty (V3 P) tính bằng hàng nghìn đô la Mỹ.

Giải pháp bảng tính Excel

Trước hết, bạn cần tạo một bảng dữ liệu ban đầu. Nó trông như thế này:

gọi cửa sổ “Phân tích dữ liệu”;

chọn phần “Hồi quy”;

trong hộp “Khoảng đầu vào Y” nhập phạm vi giá trị của các biến phụ thuộc từ cột G;

nhấp vào biểu tượng có mũi tên màu đỏ ở bên phải cửa sổ Khoảng đầu vào X và chọn trên trang tính phạm vi của tất cả các giá trị từ các cột B, C, D, F.

Kiểm tra mục “Bảng tính mới” và nhấp vào “Ok”.

Nhận phân tích hồi quy cho một nhiệm vụ nhất định.

Nghiên cứu kết quả và kết luận

Chúng tôi “thu thập” từ dữ liệu làm tròn được trình bày ở trên trên trang tính của bảng tính Excel, phương trình hồi quy:

SP u003d 0,103 * SOF + 0,541 * VO – 0,031 * VK + 0,405 * VD + 0,691 * VZP – 265,844.

Trong một dạng toán học quen thuộc hơn, nó có thể được viết là:

y u003d 0,103 * x1 + 0,541 * x2 – 0,031 * x3 + 0,405 * x4 + 0,691 * x5 – 265,844

Dữ liệu cho Công ty cổ phần “MMM” được trình bày trong bảng:

Thay chúng vào phương trình hồi quy, con số là 64,72 triệu đô la Mỹ. Điều này có nghĩa là không nên mua cổ phiếu của Công ty cổ phần “MMM”, vì giá trị 70 triệu đô la Mỹ của nó là khá phóng đại.

Như bạn có thể thấy, việc sử dụng bộ xử lý bảng tính Excel và phương trình hồi quy cho phép chúng tôi đưa ra quyết định sáng suốt về khả năng tư vấn của một giao dịch rất cụ thể.

Phân tích hồi quy và tương quan – phương pháp nghiên cứu thống kê. Đây là những cách phổ biến nhất để chỉ ra sự phụ thuộc của một tham số vào một hoặc nhiều biến độc lập.

Phân tích hồi quy trong Excel

Cho biết ảnh hưởng của một số giá trị (độc lập, không phụ thuộc) lên biến phụ thuộc. Ví dụ, số lượng dân số hoạt động kinh tế phụ thuộc vào số lượng doanh nghiệp, quy mô tiền lương và các thông số khác. Hoặc: đầu tư nước ngoài, giá năng lượng, v.v., ảnh hưởng như thế nào đến mức GDP.

Kết quả phân tích cho phép bạn sắp xếp thứ tự ưu tiên. Và dựa trên các yếu tố chính, dự báo, lập kế hoạch phát triển các lĩnh vực ưu tiên, đưa ra các quyết định quản lý.

Hồi quy xảy ra:

tuyến tính (y u003d a + bx);

parabol (y u003d a + bx + cx 2);

lũy thừa (y u003d a * exp (bx));

power (y u003d a * x ^ b);

hypebol (y u003d b / x + a);

logarit (y u003d b * 1n (x) + a);

hàm mũ (y u003d a * b ^ x).

Hãy xem một ví dụ về xây dựng mô hình hồi quy trong Excel và giải thích kết quả. Hãy lấy một kiểu hồi quy tuyến tính.

Bài tập. Tiền lương bình quân hàng tháng và số lao động nghỉ việc được phân tích tại 6 doanh nghiệp. Cần xác định sự phụ thuộc của số lao động nghỉ việc vào tiền lương bình quân.

Mô hình hồi quy tuyến tính như sau:

Y u003d a 0 + a 1 x 1 + … + a k x k.

Trong đó a – hệ số hồi quy, x – biến ảnh hưởng, k – số nhân tố.

Trong ví dụ của chúng tôi, Y là chỉ số nhân viên nghỉ việc. Yếu tố ảnh hưởng là tiền lương (x).

Excel có các hàm tích hợp mà bạn có thể sử dụng để tính toán các tham số của mô hình hồi quy tuyến tính. Nhưng phần bổ trợ Gói phân tích sẽ làm điều đó nhanh hơn.

Chúng tôi kích hoạt một công cụ phân tích mạnh mẽ:

Sau khi kích hoạt, phần bổ trợ sẽ có sẵn trên tab Dữ liệu.

Bây giờ chúng ta hãy đi thẳng vào phân tích hồi quy.

Trước hết, hãy chú ý đến bình phương R và các hệ số.

R-square là hệ số xác định. Trong ví dụ của chúng tôi, 0,755 hoặc 75,5%. Điều này có nghĩa là các tham số tính toán của mô hình giải thích mối quan hệ giữa các tham số nghiên cứu là 75,5%. Hệ số xác định càng cao thì mô hình càng tốt. Tốt – trên 0,8. Xấu – dưới 0,5 (phân tích như vậy khó có thể được coi là hợp lý). Trong ví dụ của chúng tôi – “không tệ”.

Hệ số 64.1428 cho thấy Y sẽ là bao nhiêu nếu tất cả các biến trong mô hình đang xét đều bằng 0. Nghĩa là các yếu tố khác không được mô tả trong mô hình ảnh hưởng đến giá trị của tham số được phân tích.

Hệ số -0,16285 thể hiện quyền số của biến X đối với Y. Tức là mức lương trung bình hàng tháng trong mô hình này ảnh hưởng đến số người nghỉ việc với trọng số -0,16285 (đây là mức độ ảnh hưởng nhỏ). Dấu “-” cho thấy tác động tiêu cực: lương càng cao, càng ít người bỏ việc. Đó là công bằng.



Phân tích tương quan trong Excel

Phân tích tương quan giúp xác định liệu có mối quan hệ giữa các chỉ tiêu trong một hoặc hai mẫu. Ví dụ, giữa thời gian hoạt động của máy và chi phí sửa chữa, giá thiết bị và thời gian hoạt động, chiều cao và cân nặng của trẻ em, v.v.

Nếu có một mối quan hệ, sự gia tăng của một tham số có dẫn đến tăng (tương quan thuận) hay giảm (tiêu cực) trong tham số kia. Phân tích tương quan giúp nhà phân tích xác định liệu giá trị của một chỉ số có thể dự đoán giá trị có thể có của chỉ số khác hay không.

Hệ số tương quan được ký hiệu là r. Thay đổi từ +1 đến -1. Việc phân loại các mối tương quan cho các khu vực khác nhau sẽ khác nhau. Khi hệ số bằng 0, không có mối quan hệ tuyến tính giữa các mẫu.

Hãy xem cách sử dụng công cụ Excel để tìm hệ số tương quan.

Để tìm các hệ số được ghép nối, hàm CORREL được sử dụng.

Nhiệm vụ: Xác định xem có mối quan hệ nào giữa thời gian hoạt động của máy tiện và chi phí bảo dưỡng máy tiện hay không.

Chúng ta đặt con trỏ vào ô bất kỳ và nhấn nút fx.

Đối số “Mảng 1” – phạm vi giá trị đầu tiên – thời gian vận hành máy: A2: A14.

Đối số mảng 2 – phạm vi giá trị thứ hai – chi phí sửa chữa: B2: B14. Bấm OK.

Để xác định loại kết nối, bạn cần nhìn vào số tuyệt đối của hệ số (đối với mỗi lĩnh vực hoạt động có thang điểm riêng).

Để phân tích tương quan của một số tham số (hơn 2), sẽ thuận tiện hơn khi sử dụng Phân tích dữ liệu (phần bổ trợ Gói phân tích). Trong danh sách, bạn cần chọn một mối tương quan và chỉ định một mảng. Tất cả.

Các hệ số thu được sẽ được hiển thị trong ma trận tương quan. Một cái gì đó như thế này:

Phân tích hồi quy tương quan

Trong thực tế, hai kỹ thuật này thường được sử dụng cùng nhau.

Thí dụ:

Bây giờ dữ liệu hồi quy cũng hiển thị.

MS Excel cho phép bạn thực hiện hầu hết công việc rất nhanh chóng khi xây dựng phương trình hồi quy tuyến tính. Điều quan trọng là phải hiểu cách diễn giải các kết quả thu được. Để xây dựng một mô hình hồi quy, hãy chọn Công cụ \ Phân tích Dữ liệu \ Hồi quy (trong Excel 2007, chế độ này nằm trong phần Dữ liệu / Phân tích Dữ liệu / Hồi quy). Sau đó sao chép kết quả thu được vào khối để phân tích.

Cách Vẽ Đồ Thị Trong Excel 2007, Vẽ Hình Trong Excel

Sau bài này, bạn sẽ biết cách xác định vùng dữ liệu và vẽ đồ thị biểu diễn vùng dự liệu đó. Bạn cũng sẽ biết thêm các kiểu đồ thị Excel hỗ trợ cũng như cách áp dụng từng loại đồ thị cho từng trường hợp cụ thể.

1. Giới thiệu đồ thị

Đồ thị giúp trình bày các số liệu khô khan bằng việc vẽ thành các hình ảnh trực quan, dễ hiểu. Đồ thị được liên kết với dữ liệu của nó trong bảng tính, do đó khi thay đổi dữ liệu của nó trong bảng tính thì lập tức đồ thị sẽ thay đổi tương ứng theo. Trong Excel 2007 việc vẽ đồ thị chưa bao giờ dễ dàng và đẹp như bây giờ. Excel có rất nhiều kiểu đồ thị khác nhau phục vụ cho nhiều mục đích khác nhau của rất nhiều loại đối tượng sử dụng bảng tính, chúng ta sẽ cùng nhau khám phá thế giới đồ thị của Excel trong bài học này.

Đồ thị là một đối tượng (object) của Excel, đối tượng này chứa các dữ liệu và biểu diễn thành hình ảnh với màu sắc và kiểu dáng rất phong phú. Nhìn chung, Excel 2007 có 2 loại đồ thị đó là đồ thị nằm trong WorkSheet (còn gọi là Embedded chart) và ChartSheet. Để chuyển đổi qua lại giữa 2 loại đồ thị này ta làm như sau:

2.1 Bước cơ bản để vẽ đồ thị

Bước 1: Chọn dữ liệu mà bạn muốn tạo biểu đồ, bao gồm các cột tiêu đề (tháng một, tháng hai, tháng ba) và tên của nhân viên bán hàng.

Nếu bạn muốn thay đổi các loại biểu đồ sau khi bạn tạo biểu đồ của bạn, nhấp chuột vào bên trong các biểu đồ. Trên tab Design, dưới Chart Tools, trong nhóm Type, bấm vào Change Chart Type và chọn một loại biểu đồ khác.

2.2 Ví dụ về vẽ một đồ thị trong Excel 2007 và các tình huống thường gặp

Phần này trình bày các bước vẽ đồ thị từ một bảng số liệu cho trước và một số tùy chọn của đồ thị. Kết quả nghiên cứu về sự thõa mãn của các nhóm khách hàng phân theo độ tuổi được cho như hình bên dưới, nếu chúng ta dùng kết quả này để báo cáo cũng không có vấn đề gì, tuy nhiên báo cáo sẽ sinh động và thuyết phục hơn nếu chúng ta biến các con số này thành đồ thị để được nhìn thấy một cách trực quan hơn.

Và được kết quả:

B5. Nếu thấy kiểu đồ thị trên không đẹp, chúng ta có thể đổi đồ thị sang kiểu khác bằng cách như sau: , hộp thoại Insert Chart hiển thi ̣ liệt kê toàn bộ các kiểuđồ thị mà Excel có tha hồ cho bạn lựa chọn.

3. Các thao tác trên đồ thị

3.1 Nhận biết các thành phần trên đồ thị Excel

Các thành phần thông dụng được chỉ ra trong bảng sau:

Trong đó:

Vertical axis title: Tiêu trục tung của đồ thị, xác định kiểu dữ liệu trình diễn trên trục tung.

Horizontal gridlines: Các dường lưới ngang.

Vertical gridlines: Các đường lưới dọc.

Trong đồ thị 3D còn có thêm các thành phần sau:

Back wall: Màu/ hình hền phía sau đồ thị.

Floor: Màu/ hình nền bên dưới đồ thị

Column depth: Độ sâu của các thành phần biểu diễn chuỗi số liệu dưới dạng 3-D.

3.2 Các thao tác với đồ thị

Chọn thành phần trên đồ thị

Cách dễ nhất là dùng chuột nhấp lên thành phần cần chọn, thành phần được chọn sẽ có 4 hoặc 8 nút xuất hiện bao quanh.

Khi đồ thị đã được chọn, chúng ta có thể dùng các phím mũi tên ← ↑ → ↓ để di chuyển đến các thành phần trong đồ thị.

Di chuyển đồ thị

Đồ thị là Embedded Chart, nhấp trái chuột lên đồ thi ̣ vùng Chart Area để chọn đồ thị di chuyễn, khi đó đầu con trỏ chuột có thêm ký hiệu mũi tên 4 chiều.

Giữ trái chuột và di chuyể n đồ thi ̣ đến nơi k hác.

Thay đổi kích thươc đồ thị

Đồ thị là Embedded Chart, nhấp trái chuột lên đồ thi ̣ vùng Chart Area để chọn đồ thị cần thay đổ i kích thươc, khi đó xung quanh đồ thi ̣ xuất hiện 8 nút nắm

Di chuyể n chuột vào các nút này, giữ trái chuột và kéo hướng vô tâm đồ thi ̣ để thu nhỏ và hướng ra ngoài để phóng to.

Sao chép đồ thị

Chọn đồ thị, dùng tổ hợp phímđể chép đồ thị vào bộ nhớ, rồi di chuyể n đến một ô nào đó trong bảng tính và nhấnđể dán đồ thị vào.

Xóa đồ thị

Chọn đồ thị sau đó nhấn phím Delete để xóa đồ thị. Để xóa Chart Sheet, trước tiên hãy chọn Chart Sheet, sau đó nhấp phải chuột và chọn Delete từ thực đơn ngữ cảnh hiện ra.

Thêm các thành phần của đồ thị Sắp xếp và xóa các thành phần của đồ thị

Một số thành phần trong đồ thị có thể di chuyển được như tựa đề, chú thích, nhãn. Muốn di chuyển thành phần nào trước tiên hãy dùng chuột chọn nó, sau đó nhấp và giữ trái tại cạnh của thành phần và kéo đến vị trí mới trong đồ thị.

Để xóa thành phần nào, bạn chỉ cần dùng chuột chọn thành phần đó và nhấn phím Delete.

In đồ thị

In đồ thị cũng giống như in các đối tượng khác của Excel lưu ý Print Preview trước khi in để đảm bảo trang in được trọn vẹn nội dung.

Nếu bạn muốn in đồ thị thành một trang riêng thì hãy chọn đồ thị và nhấp nút Print để in, khi đó Excel chỉ ra lệnh in đồ thị mà bạn đang chọn.

3.3. Hiệu chỉnh và định dạng đồ thị

Hiệu chỉnh Chart Area Hiệu chỉnh Flot Area

Xem ví dụ hình dưới:

Hiệu chỉnh tiêu đề đồ thị, chú thích, tiêu đề trục hoành và trục tung,…

Tùy theo kiểu đồ thị đang xử lý mà Excel cho phép bạn hiệu chỉnh các loại tiêu đề của kiểu đồ thị đó.

Value Axis

Hầu hết các đồ thị trình bày giá trị trên trục tung và phân nhóm trên trục hoành. Các bạn nên chọn thử để biết rõ hơn công dụng của các tùy chọn.

Values in reverse order Đảo thứ tự sắp xếp các giá trị trên trục

Logarithmic scale Chuyển các giá trị trên trục sang hệ logarith với cơ số do ta qui định tại Base (không thể dùng cho giá trị âm hay bằng 0)

Display units Chọn đơn vị hiển thị trên trục.

Show display units label on chart Cho hiển thị các nhãn đơn vị trên đồ thị

Major tick mark type Qui định cách hiển thị ký hiệu phân cách chính trên đồ thị.

None Không sử dụng

Inside Hiển thị phía bên trong trục

Outside Hiển thị phía bên ngoài trục

Cross Hiển thị cắt ngang trục

Minor tick mark type Qui định cách hiển thị ký hiệu phân cách phụ trên đồ thị.

Axis labels Xác định cách hiển thị các nhãn trên trục

None Không sử dụng

High Hiển thị phía bên phải trục

Low Hiển thị phía bên trái trục

Next to Axis Hiển thị kế bên trục (theo mặc định)

Horizontal axis crosses Các lựa chọn qui định cách mà trục tung cắt trục hoành (Đối với đồ thị 3-D đó chính là tùy chọn Floor crosses at)

Automatic Do Excel tự xác định.

Axis value Xác định giá trị mà các trục sẽ giao nhau

Maximum axis value Hai trục sẽ giao nhau tại giá trị lớn nhất trên trục (tung)

Category Axis

Interval between tick marks Xác định sự thể hiện các nhóm theo khoảng chia (có bao nhiêu nhóm trong một khoảng chia, thường là một nhóm).

Categories in reverse order Các nhóm thể hiện theo thứ tự ngược lại

Label distance from axis Xác định khoảng cách của nhãn so với trục

Axis Type Xác định loại trục sử dụng như Automatic, Text axis hoặc Date axis

Major tick mark type Qui định cách hiển thị ký hiệu phân cách chính trên đồ thị.

Minor tick mark type Qui định cách hiển thị ký hiệu phân cách phụ trên đồ thị.

Axis labels Xác định cách hiển thị các nhãn trên trục

Vertical axis crosses Các lựa chọn qui định cách mà trục tung cắt trục tung

Automatic Do Excel tự xác định.

At category number Xác định số nhóm mà tại đó các trục sẽ giao nhau

At maximum category Hai trục sẽ giao nhau tại giá trị lớn nhất trên trục

Time Scale Axis

Minimum Xác định giá trị (ngày) nhỏ nhất trên trục (ngày bắt đầu)

Maximum Xác định giá trị (ngày) lớn nhất trên trục (ngày kết thúc)

Major unit Xác định khoảng chia chính trên trục (đơn vị là ngày, tháng hay năm)

Minor unit Xác định khoảng chia phụ trên trục (đơn vị là ngày, tháng hay năm)

Base Unit Xác định đơn vị tính cho trục

Dates in reverse order Hiển thị theo thứ tự ngược lại trên trục

Axis Type Xác định loại trục sử dụng như Automatic, Text axis hoặc Date axis

Major tick mark type Qui định cách hiển thị ký hiệu phân cách chính trên đồ thị.

Minor tick mark type Qui định cách hiển thị ký hiệu phân cách phụ trên đồ thị.

Axis labels Xác định cách hiển thị các nhãn trên trục

Vertical Axis crosses Các lựa chọn qui định cách trục tung giao với trục hoành

Between dates Do Excel tự xác định

At date Giao nhau tại ngày do bạn nhập vào

At maximum date Giao nhau tại ngày lớn nhất (gần đây nhất) trên trục

3.4. Các thao tác với chuỗi số liệu trong đồ thị

Xóa bớt một chuỗi số liệu khỏi đồ thị

Chọn chuỗi số liệu trên đồ thị (Ví dụ: chọn đường cung)

Nhấn phím Delete trên bàn phím để xót chuỗi khỏi đồ thị

Thêm chuỗi mới vào đồ thị

Chọn đồ thị cần thêm chuỗi mới vào.

Nhấp nút Add, hộp thoại Edit Series xuất hiện

Đặt tên cho chuỗi mới tại Series Name (bằng tham chiếu hoặc nhập trực tiếp tên vào từ bàn phím) và chọn vùng chứa dữ liệu tại Series Values

Ngoài ra ta có thể thêm nhanh chuỗi mới vào đồ thị bằng cách chép (Ctrl+C) dữ liệu của nó vào bộ nhớ, sau đó chọn đồ thị và dán (Ctrl+V) vào đồ thị.

Thay đổi chuỗi số liệu

Cũng với ví dụ trên nhưng ta muốn chỉ vẽ cho 2 sản phẩm Giày và Dép

Chọn đồ thị cần chỉnh sửa

Chọn chuỗi cần chỉnh sửa, nhấp nút Edit, hộp thoại Edit Series xuất hiện

Chọn lại vùng dữ liệu mới tại Series Values. Làm tương tự cho các chuỗi số liệu khác.

Ngoài ra chúng ta có thể sử dụng hàm Series cho đồ thị. Cú pháp như sau:

= Series(series_name, category_labels, values, order, sizes)

Trong đó:

Series_name: (tùy chọn) tham chiếu đến ô chứa tên chuỗi, bạn có thể nhập văn bản trực tiếp vào nhớ đặt trong cặp nháy kép.

Category_labels: (tùy chọn) tham chiếu đến vùng chứa các nhãn cho các nhóm số liệu trên trục, nếu bỏ trống Excel tự đánh số các nhóm bắt đầu là số 1. Có thể nhập trực tiếp các nhãn vào ngăn cách nhau bằng dấu phẩy và đặt trong cặp ngoặc móc {}.

Values: (bắt buộc) tham chiếu đến vùng chứa số liệu của các chuỗi cần vẽ.

Order: (bắt buộc) Là số nguyên qui định thứ tự vẽ của các chuỗi (nếu đồ thị có nhiều hơn 1 chuỗi)

Sizes: (chỉ dùng cho đồ thị bong bóng – Bubble chart) Tham chiếu đến vùng chứa dữ liệu về kích thước của bong bóng trong đồ thị kiểu Bubble (sử dụng trong phân tích tài chính). Các giá trị có thể nhập trực tiếp ngăn cách nhau bằng dấu phẩy và đặt trong cặp ngoặc móc {}.

Thêm đường xu hướng vào đồ thị

Khi vẽ các đồ thị với dữ liệu theo thời gian chúng ta thường vẽ thêm đường xu hướng để biết được xu hướng trong tương lai của tập dữ liệu. Một tập số liệu có thể có nhiều đường xu hướng tùy theo cách phân tích của chúng ta. Để thêm đường xu hướng bạn vào:

3.5 Sao chép một biểu đồ sang Word

B1. Lựa chọn biểu đồ

B3. Chuyển sang tài liệu Word, và kích chuột vào nơi bạn muốn đặt biểu đồ

4. Vẽ nhiều kiểu biểu đồ khác nhau trong cùng trục tọa độ

Trong Excel cho phép bạn vẽ nhiều loại biểu đồ khác nhau trong cùng trục tọa độ.

Ví dụ bạn có dữ liệu từ tháng 1 tới tháng 12 bên cạnh có là những dữ liệu kế hoạch ban đầu và kế hoạch thực tế.

Bạn có thể tạo ra nhiều kiểu biểu đồ để dễ nhận dạng các kết quả kinh doanh. Theo hướng dẫn sau đây là vẽ biểu đồ hình cột và hình dây.

Đầu tiên tạo một biểu đồ. Bạn chọn tab Insert . Trong tab này có để cho phép lựa chọn các biểu đồ khác . Trong ví dụ này chọn hình đầu tiên của nhóm Column.

Khi đó trong màn hình Excel xuất hiện một khung hình trắng là nơi sẽ chữa biểu đồ . Bấm chuột phải vào khung hình này và chọn Select Data

Đánh dấu vùng dữ liệu ví dụ từ D6 tới D17 khi ấy bạn sẽ nhìn thấy như hình sau:

Khi bấm nút OK bạn sẽ lại thấy cửa sổ Select Data Source hiện ra . Nếu biểu đồ thứ nhất bạn làm chưa đúng có thể chọn lại bằng cách bấm Edit.

Và quá trình làm tiếp tương tự như từ hình 4 . Sau khi kết thúc quá trình thêm biểu đồ thứ hai ta sẽ thấy như hình dưới

Để thay đổi biểu đồ nào , ví dụ là , bạn bấm chuột phải vào biểu đồ này và chọn

Và lại chọn tiếp kiểu biểu đồ mà bạn muốn. Trong ví dụ chọn biểu đồ kiểu Line

Sau khi kết thúc việc lựa chọn bạn sẽ thấy như hình dưới:

5. Tạo biểu đồ đường Gantt cho quản lý dự án

Biểu đồ Gantt là loại biểu đồ được sử dụng rộng rãi trong quản lý dự án nó biểu diễn thời gian thực hiện nhiệm vụ trong dự án, giúp cho các nhà quản lý dự án theo dõi và quản lý công việc chơn chu hơn.

Nhìn vào biểu đồ gantt người quản lý dự án, cũng như các thành viên thực hiện dự án biết được:

Trình tự thực hiện mỗi nhiệm vụ.

Tiến độ dự án biết được mình đã làm được gì và tiếp tục phải thực hiện công việc đó thế nào, bởi vì mỗi công việc được giao phải hoàn thành trong thời gian đã định.

Thấy sự phụ thuộc lẫn nhau giữa các công việc.

Đây là mẫu đồ thị Gantt mô tả quá trình thực hiện dự án được xây dựng từ kiểu đồ thị thanh ngang của Excel 2007, thể hiện thời gian bắt đầu và kết thúc của từng nhiệm vụ trong dự án.

Bước 1: Chọn dữ liệu bạn muốn lập biểu đồ. Ví dụ phạm vi A1:C6 theo hình dưới

6. Các kiểu đồ thị

Khi bạn tạo biểu đồ, bạn có rất nhiều tùy chọn. Dù bạn định dùng loại biểu đồ được gợi ý cho dữ liệu của bạn hoặc loại biểu đồ mà bạn sẽ chọn từ danh sách tất cả các biểu đồ, bạn cũng nên biết qua về từng loại biểu đồ.

6.1 Biểu đồ cột

Dữ liệu được sắp xếp theo các cột hoặc các hàng trên bảng tính có thể được biểu thị bằng biểu đồ cột. Một biểu đồ cột thường biểu thị các thể loại dọc theo trục ngang (thể loại) và các giá trị dọc theo trục dọc (giá trị), giống như trong biểu đồ sau:

6.2 Biểu đồ đường

Dữ liệu được sắp xếp theo các cột hoặc các hàng trên bảng tính có thể được biểu thị bằng biểu đồ đường. Trong biểu đồ đường, dữ liệu thể loại được phân bổ đều dọc theo trục ngang và tất cả các dữ liệu giá trị được phân bổ đều dọc theo trục dọc. Các biểu đồ đường có thể biểu thị các dữ liệu liên tục theo thời gian trên trục được chia độ đều và vì vậy rất phù hợp để biểu thị các khuynh hướng dữ liệu tại các khoảng thời gian bằng nhau như tháng, quý hoặc năm tài chính.

GHI CHÚ

Bạn nên sử dụng biểu đồ đường khi có nhiều chuỗi dữ liệu trong biểu đồ – nếu bạn chỉ có một chuỗi dữ liệu, hãy cân nhắc thay thế bằng biểu đồ tán xạ.

Biểu đồ đường xếp chồng bổ sung thêm dữ liệu mà có thể bạn không mong muốn. Không dễ quan sát các đường xếp chồng lên nhau, vì vậy hãy cân nhắc sử dụng một loại biểu đồ đường khác hoặc biểu đồ vùng xếp chồng thay thế.

6.3 Biểu đồ hình tròn

Dữ liệu được sắp xếp theo một cột hoặc một hàng trên trang tính có thể được biểu thị bằng biểu đồ hình tròn. Biểu đồ hình tròn biểu thị kích cỡ các mục trong một chuỗi dữ liệu, tỷ lệ với tổng số các mục. Các điểm dữ liệu trong biểu đồ hình tròn được biểu thị dưới dạng phần trăm của toàn bộ hình tròn.

Cân nhắc sử dụng biểu đồ hình tròn khi:

6.4 Biểu đồ vành khuyên bị cắt

Dữ liệu được sắp xếp chỉ theo các cột hoặc các hàng trên bảng tính có thể được biểu thị bằng biểu đồ vành khuyên bị cắt. Giống như biểu đồ hình tròn, biểu đồ vành khuyên bị cắt biểu thị mối quan hệ giữa các phần với tổng số, nhưng nó có thể bao gồm nhiều chuỗi dữ liệu.

6.5 Biểu đồ thanh

Cân nhắc sử dụng biểu đồ thanh khi:

6.6 Biểu đồ vùng

Dữ liệu được sắp xếp theo các cột hoặc các hàng trên bảng tính có thể được biểu thị bằng biểu đồ vùng. Các biểu đồ vùng được sử dụng để biểu thị sự thay đổi theo thời gian và hướng sự chú ý đến tổng giá trị qua một khuynh hướng. Bằng cách biểu thị tổng giá trị, một biểu đồ vùng cũng biểu thị mối quan hệ của các phần so với tổng thể.

6.7 Biểu đồ XY (phân tán) và biểu đồ bong bóng

Dữ liệu được sắp xếp theo các cột hoặc các hàng trên bảng tính có thể được biểu thị bằng biểu đồ xy (phân tán). Đặt các giá trị x theo hàng hoặc cột, sau đó nhập các giá trị y tương ứng vào các hàng hoặc cột liền kề.

Một biểu đồ tán xạ có hai trục giá trị: trục giá trị ngang (x) và dọc (y). Nó kết hợp các giá trị x và y vào trong các điểm dữ liệu duy nhất và hiển thị chúng theo những khoảng hoặc cụm không đều. Biểu đồ tán xạ thường được sử dụng để biểu thị và so sánh các giá trị số như các dữ liệu khoa học, thống kê và kỹ thuật.

Cân nhắc việc sử dụng biểu đồ tán xạ khi:

6.8 Biểu đồ bong bóng

Cũng giống như biểu đồ tán xạ, một biểu đồ bong bóng bổ sung thêm một cột thứ ba để định rõ kích cỡ bong bóng mà nó dùng để biểu thị cho các điểm dữ liệu trong chuỗi dữ liệu.

6.9 Biểu đồ chứng khoán

Dữ liệu được sắp xếp theo các cột hoặc các hàng theo một trình tự cụ thể trên trang tính có thể được biểu thị bằng biểu đồ chứng khoán. Đúng như tên gọi, biểu đồ chứng khoán dùng để minh họa những dao động lên xuống của giá cổ phiếu. Tuy nhiên, biểu đồ này cũng có thể minh họa sự lên xuống của các dữ liệu khác, như lượng mưa hàng ngày hoặc nhiệt độ hàng năm. Hãy đảm bảo rằng bạn sắp xếp dữ liệu theo đúng trật tự để tạo một biểu đồ chứng khoán.

Ví dụ, để tạo một biểu đồ chứng khoán cao – thấp – đóng cửa dạng đơn giản, hãy sắp xếp dữ liệu với các tiêu đề cột Cao, Thấp và Đóng cửa theo đúng trật tự đó.

6.10 Biểu đồ bề mặt

Dữ liệu được sắp xếp theo các cột hoặc các hàng trên bảng tính có thể được biểu thị bằng biều đồ bề mặt. Biểu đồ này rất hữu ích khi bạn muốn tìm cách kết hợp tối ưu giữa các tập hợp dữ liệu. Giống như trong bản đồ địa hình, màu sắc và kiểu dáng sẽ cho biết các khu vực thuộc cùng một phạm vi giá trị. Bạn có thể tạo một biều đồ bề mặt khi cả thể loại và chuỗi giá trị đều là các giá trị số.

6.11 Biểu đồ radar

Dữ liệu được sắp xếp theo các cột hoặc các hàng trên bảng tính có thể được biểu thị bằng biểu đồ radar. Biểu đồ radar so sánh các giá trị tổng hợp của một vài chuỗi dữ liệu.

6.12 Biểu đồ kết hợp

Dữ liệu được sắp xếp theo các cột và các hàng có thể được biểu thị bằng biểu đồ kết hợp. Biểu đồ kết hợp phối hợp hai hay nhiều loại biểu đồ với nhau nhằm giúp biểu thị dữ liệu sao cho dễ hiểu hơn, đặc biệt khi nguồn dữ liệu quá đa dạng. Được biểu thị với một trục phụ, biểu đồ này sẽ càng dễ đọc hơn. Trong ví dụ này, chúng tôi đã sử dụng biểu đồ cột để biểu thị số lượng nhà bán được giữa tháng 1 và tháng 6 rồi sau đó sử dụng biểu đồ đường để giúp độc giả dễ dàng xác định được doanh thu trung bình hàng tháng.

7. Vẽ hình trong Excel

Các công cụ này sử dụng tương tự như trong Word 2007 nên các bạn xem bài sau: Cách trình bày văn bản đẹp

Nếu bạn muốn cảm ơn, vui lòng sử dụng các icon Facebook phía dưới cùng để chia sẻ cho bạn bè mình. Đó là cách để giới thiệu cũng như giúp chúng tôi phát triển.

Cách Vẽ Lưu Đồ Trong Excel

Có rất nhiều tùy chọn tồn tại để tạo lưu đồ (hay còn gọi là sơ đồ luồng hoặc flowchart), Tuy nhiên nếu đã đăng ký Microsoft Office 365 thì điều này không cần thiết. Quản Trị Mạng đã chỉ cho bạn đọc cách tạo lưu đồ trong Word, vậy còn Excel thì sao?

Trong bài viết này, chúng ta sẽ tìm hiểu cách thiết lập môi trường lưu đồ và tạo lưu đồ trong Excel. Một số liên kết để bạn đọc có thể tải xuống các mẫu lưu đồ cho Microsoft Excel miễn phí sẽ được đặt ở cuối bài.

Thiết lập lưới lưu đồ trong Excel

Khi tạo lưu đồ trong Excel, có một cách hữu ích để định vị và đặt kích thước cho các phần tử trong lưu đồ.

Tạo lưới

Để tạo lưới, người dùng cần thay đổi chiều rộng của tất cả các cột bằng với chiều cao hàng mặc định.

Đầu tiên, chọn tất cả các ô trên bảng tính bằng cách nhấp vào hộp ở góc trên bên trái của lưới bảng tính. Sau đó, nhấp chuột phải vào bất kỳ tiêu đề cột nào và chọn Column Width.

Nếu sử dụng phông chữ mặc định ( Calibri, kích thước 11), chiều cao hàng mặc định là 15 point, tương đương 20 pixel. Để làm cho chiều rộng cột đều bằng 20 pixel, phải thay đổi nó thành 2.14.

Kích hoạt Snap to Grid

Các tính năng Snap to Grid giúp dễ dàng đặt và thay đổi kích thước hình dạng trên lưới, để có thể thay đổi kích thước chúng một cách nhất quán và căn chỉnh cho chúng bằng nhau.

Nhấp vào tab Page Layout. Sau đó, nhấp Align trong phần Arrange và chọn Snap to Grid. Biểu tượng Snap to Grid trên menu được highlight bằng hộp màu xám khi bật tính năng này.

Thiết lập bố cục trang trong Excel

Người dùng nên thiết lập bố cục trang cho lưu đồ của mình để biết ranh giới trước khi đặt lưu đồ. Ví dụ, nếu muốn chèn lưu đồ vào tài liệu Word, nên đặt kích thước trang trong Microsoft Excel giống với trong tài liệu Word. Bằng cách đó, người dùng sẽ tạo ra một lưu đồ bằng kích thước các trang trong tài liệu Word.

Để thiết lập các mục như Margins (lề), Page orientation (hướng trang) và Page size (kích thước trang), hãy nhấp vào tab Page Layout. Sử dụng các nút trong phần Page Setup để thay đổi cài đặt cho các tùy chọn bố cục khác nhau.

Cách tạo lưu đồ trong Excel

Bây giờ, phần bảng tính đã được thiết lập cho lưu đồ. Hãy bắt tay vào việc tạo một lưu đồ như sau.

Thêm một hình dạng bằng cách sử dụng công cụ Shapes

Chọn một hình trong phần Flowchart của menu drop-down.

Kéo hình dạng để tăng kích thước như mong muốn trên bảng tính. Nếu tính năng Snap to Grid được bật, hình dạng sẽ tự động khớp với các đường lưới khi người dùng vẽ nó.

Thêm hình dạng khác cho lưu đồ bằng cách sử dụng tab Format

Khi người dùng vẽ hình dạng đầu tiên của mình và chọn nó, tab Format đặc biệt sẽ khả dụng. Người dùng có thể sử dụng tab này để thêm nhiều hình dạng hơn vào lưu đồ của mình và định dạng các hình dạng của mình.

Người dùng cũng có thể bấm đúp vào một hình trên menu để thêm nó vào bảng tính. Để thay đổi kích thước hình dạng, chọn nó và kéo một trong các tay handle (chấm tròn) dọc theo các cạnh.

Để di chuyển hình dạng, di con trỏ qua lại cho đến khi con trỏ trở thành hình chữ thập có mũi tên. Sau đó, nhấp và kéo hình dạng đến nơi mong muốn.

Thêm văn bản vào hình dạng

Để thêm văn bản vào một hình dạng, chỉ cần chọn hình dạng và bắt đầu nhập. Sau đó, hãy định dạng và căn chỉnh phần văn bản này.

Để chỉnh sửa văn bản trong một hình dạng, bấm vào phần văn bản trong hình dạng đó. Điều này đưa người dùng vào chế độ chỉnh sửa, cho phép thêm, thay đổi hoặc xóa văn bản.

Nhấp ra ngoài hình dạng hoặc chọn giống như khi di chuyển hình dạng trong phần trước.

Thêm đường kết nối giữa các hình dạng

Sau khi thêm một số hình dạng vào lưu đồ, đã đến lúc kết nối chúng. Chọn Line Arrow trên thư viện Shapes trên tab Insert hoặc tab Format.

Con trỏ trở thành biểu tượng dấu cộng. Di chuyển con trỏ đến hình dạng đầu tiên muốn kết nối. Các chấm xuất hiện tại các điểm có thể kết nối với hình dạng đó.

Nhấp vào điểm kết nối nơi đường bắt đầu và kéo đường thẳng đến hình tiếp theo, cho đến khi thấy các điểm kết nối trên đó xuất hiện. Nhả chuột vào một trong những điểm đó.

Một mũi tên hiển thị nơi đường nối kết thúc. Khi một đường được kết nối đúng với hình dạng, điểm kết nối sẽ không biến mất. Nếu không muốn thấy điểm kết nối, thì đừng liên kết các hình dạng bằng đường nối.

Thêm văn bản vào đường kết nối

Trong các chương trình tạo lưu đồ như Visio và Lucidchart, người dùng có thể thêm văn bản trực tiếp vào các dòng kết nối. Trong Microsoft Excel, người dùng cũng có thể làm điều đó.

Để thêm văn bản vào một đường kết nối, hãy tạo một hộp văn bản và đặt nó dọc theo hoặc trên đường kết nối đó.

Vẽ hộp văn bản gần đường nối muốn gắn nhãn. Di chuyển hộp văn bản đến vị trí mong muốn, giống như cách di chuyển các hình dạng trong lưu đồ.

Có thể tắt Snap to Grid khi định vị hộp văn bản trên các đường kết nối. Điều này cho phép người dùng tinh chỉnh kích thước và vị trí của các hộp văn bản.

Để thêm văn bản, chọn hộp văn bản và bắt đầu nhập nội dung. Bài viết sẽ chỉ cho bạn cách định dạng và định vị các hộp văn bản trong phần sau.

Thêm ghi chú bằng cách sử dụng các chú thích

Nhưng, điều đó có thể gây nhầm lẫn cho người xem vì nó trông giống như một bước trong lưu đồ. Để làm cho một ghi chú trông khác biệt, hãy sử dụng một chú thích.

Vẽ chú thích trên bảng tính giống như khi vẽ một hình dạng trong lưu đồ.

Thêm văn bản vào chú thích và sử dụng các chấm để thay đổi kích thước.

Ban đầu, một phần của chú thích hiển thị ở viền dưới cùng. Để tạo điểm chú thích ở vị trí mong muốn, hãy nhấp và kéo điểm đó. Khi điểm đó kết nối với một hình dạng, điểm kết nối sẽ chuyển sang màu đỏ.

Cách định dạng lưu đồ trong Excel

Excel có nhiều tùy chọn định dạng. Nhưng bài viết hôm nay sẽ chỉ cho bạn đọc một vài điều cơ bản, để có thể định dạng các hình, văn bản và đường kết nối của mình.

Định dạng các hình

Một cách dễ dàng để định dạng các hình và văn bản trong hình dạng là sử dụng Theme Styles.

Chọn tất cả các hình dạng muốn định dạng với cùng một kiểu. Nhấp vào hình đầu tiên, sau đó nhấn và giữ Shift trong khi nhấp vào các hình khác. Sau đó, nhấp vào tab Format.

Khi di chuột qua các kiểu theme khác nhau, người dùng sẽ thấy chúng trông như thế nào trên hình dạng. Bấm vào kiểu muốn sử dụng.

Định dạng văn bản trong hình dạng và hộp văn bản

Định dạng văn bản trong hình dạng và hộp văn bản được thực hiện giống như cách thường dùng để định dạng văn bản trong các ô.

Đầu tiên, ta sẽ định dạng hình dạng. Chọn tất cả các hình có chứa văn bản muốn định dạng, bằng cách giữ phím Shift trong khi nhấp vào các hình cần chọn.

Nhấp vào tab Home và sử dụng các lệnh trong phần Font và Alignment để định dạng văn bản. Ví dụ, sử dụng các nút Center và Middle Align trong phần Alignment để căn giữa văn bản trong hình dạng theo chiều ngang và chiều dọc. Sau đó, áp dụng tùy chọn Bold cho tất cả phần văn bản.

Thực hiện tương tự với các hộp văn bản dọc theo các đường kết nối để định dạng và căn chỉnh văn bản.

Định dạng đường kết nối

Định dạng mặc định trên các đường kết nối hơi mảnh nên cần làm cho chúng dày hơn để dễ quan sát.

Chọn tất cả các đường kết nối muốn định dạng bằng cách giữ phím Shift. Sau đó, nhấp vào tab Format.

Nhấp vào Shape Outline trong phần Shape Styles và chọn màu từ phần Theme Colors hoặc Standard Colors. Sau đó, trên cùng một menu, đi đến phần Weight và chọn độ dày cho các đường kết nối từ menu con.

Các mẫu lưu đồ Excel

Khả năng tạo lưu đồ trong Microsoft Excel làm cho nó trở thành một công cụ rất hữu ích và linh hoạt để giữ mọi thứ ngăn nắp. Tuy nhiên, đây không phải là lựa chọn duy nhất. Có một số công cụ tạo lưu đồ miễn phí tốt có sẵn cho Windows trên Internet.

Cách Vẽ Biểu Đồ Cột Trong Excel

Trước khi hướng dẫn bạn cách vẽ biểu đồ cột trong excel thì bạn cần phải biết được biểu đồ cột trong excel là gì. Biểu đồ cột là biểu đồ thể hiện giá trị theo cột dọc của các đơn vị cần biểu diễn ở cột ngang. Thường thì người dùng hay sử dụng biểu đồ cột dọc, biểu đồ cột ngang, biểu đồ cột ghép, biểu đồ cột chồng,… Trong excel cũng có rất nhiều các loại biểu đồ cột khác nhau.

2. Tạo biểu đồ cột trong excel 2003

-Truy cập trang tính excel mở file dữ liệu bảng tính cần vẽ biểu đồ, khoanh vùng số liệu cần thể hiện.

Ví dụ tạo biểu đồ cột trong excel. Hình 2

Trên màn hình hiện ra hộp thoại chart Wizard – Step 1 of 4 – Chart Type, cho ra nhiều loại biểu đồ khác nhau cho chúng ta chọn, ở đây nhấp vào Custom types để chọn biểu đồ cột để vẽ trong excel là Columns with Depth sẽ trông rõ ràng hơn.

Ví dụ tạo biểu đồ cột trong excel. Hình 3

Ấn Next để tiếp tục thao tác.

– Thêm các giá trị vào biểu đồ trong hộp thoại mới.

Ví dụ tạo biểu đồ cột trong excel. Hình 5

Tại biểu đồ đã tạo có thể chỉnh sửa để phù hợp hơn thì ấn nháy chuột vào biểu đồ và chỉnh sửa.

3. Tạo biểu đồ cột excel 2007, 2010

Ví dụ với bảng số liệu, muốn vẽ được biểu đồ cột trong excel bạn cần thực hiện: – Chọn vùng cách dữ liệu cần vẽ

Sau khi đã chọn biểu đồ phù hợp, trang tính hiện ra biểu đồ mặc định và chúng ta có thể sửa lại cho đầy đủ khi nhấp vào biểu đồ, biểu đồ này chưa có vẻ là hoàn thiện nhưng phần nào đã thể hiện được sự thay đổi rõ rệt doanh số của các tháng giữa 2 năm.

– Chỉnh sửa biểu đồ về kích thước nhấp vào cạnh biểu đồ di chuyển chuột nhỏ vào và rộng ra theo tùy chỉnh.

Ví dụ tạo biểu đồ cột trong excel. Hình 7

– Chỉnh sửa vị trí tên biểu đồ thì nhấp vào biểu đồ, thao tác chọn Chart title và chọn Centered Overlay Title, với bước chọn này, tên biểu đồ sẽ được đặt giữa.

Ví dụ tạo biểu đồ cột trong excel. Hình 8

Ta được bảng biểu đồ cụ thể, hoàn thiện và chi tiết.

4. Tạo biểu đồ cột excel 2013, 2016

Để được hướng dẫn cụ thể hơn và chi tiết hơn cho tạo biểu đồ cột excel 2013, 2016 các bạn có thể tham gia khóa học excel cùng chuyên gia của UNICA. Đến với excel, bạn sẽ được trải nghiệm vô vàn những cách làm thủ thuật hỗ trợ tích cực cho bạn trong công việc và học hành. UNICA sẵn sàng làm đệm bước cho bạn vươn cao tầm tri thức.

Bạn đang đọc nội dung bài viết Cách Vẽ Tóm Tắt Trong Excel Kinh Tế Lượng. Hồi Quy Trong Excel trên website Techcombanktower.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!