I. Các ứng dụng phân tích dữ liệu trong Excel
1. Đường xu hướng hay chính xác thì gọi là Trendline trong Excel. Đường xu hướng, Trendline là gì?Trendline là một đường biểu thị xu hướng của tập giá trị có sẵn. Người ta dùng trendline có thể ước đoán xu hướng tương lai dựa vào mẫu giá trị cũng như dùng nội duy các giá trị khác. Show
Về cách vẽ Trendline kĩ thuật xin không bàn ở đây. Bài viết này chỉ hướng dẫn cách vẽ Trendline trong Excel. Các bạn tìm hiểu thêm các khái niệm cũng như ứng dụng của trendline trong phân tích dữ liệu nhé. Bảng dữ liệuDưới đây là một ví dụ cơ bản về Dường xu hướng Trendline trong Excel. Giả sử ta có bảng số liệu về sốtrẻ emsuy dinh dưỡng trong các năm 1990, 1995, 2010 như dưới đây:(Nguồn dữ liệu từ WHO) Nguồn WHO Bây giờ, ta vẽ đồ thị cho bàng số liệu này qua các năm. Cách vẽ đồ thị thì các bạn xem lạibài hướng dẫn về vẽ đồ thị trong Excelnhé. Bài toán đặt ra là dựa vào đồ thị này, hãy dự đoán xu hướng và số trẻ em suy dinh dưỡng vào năm 2015. Đó là một nhiệm vu cơ bản của Trendline Đường xu hướng. Chúng ta bắt đầu vẽ đường xu hướng cho dãy dữ liệu này. Cách vẽ Trendline trong ExcelNhấp chuột phải vào đồ thị, chọnAdd Trendlinenhư hình vẽ dưới Trong hộp thoại hiện ra, ChọnLinearrồi clickClose.Excelđã tính toán và vẽ cho ta đường Trendline. Kết quả như hình dưới đây.Đồ thịđã được thêm đường xu hướng. Bây giờ ta thử làm thấy bói dự đoán số trẻ em suy dinh dưỡng năm 2015 nhé. Đầu tiên chọn Trendline rồi click chuột phải vào nó (Lưu ý là rất khó chọn Trendline vì nó khá mảnh khảnh!). Trong menu xổ ra, chọnFormat Trendline Trong hộp thoạiFormat Trendline, chọnDisplay Equation on ChartvàDisplay R-squared value on Chart Ta sẽ thấy phương trìnhy=-13.9x+170.5 và R2 = 0.980hiện lên trên đồ thị. Trong đó:
Để dự đoán số trẻ suy dinh dưỡng năm 2015, cùng bước nhãy 5 năm với nhóm dữ liệu trên, ta làm như sau:
Chúc các bạn thành công. 2. Kỹ năng vẽ biểu đồ trong Excel Bài này thuộc chuyên đềHướng dẫn sử dụng MS Excel 2007,bạn có thể click vào để xem các bài hấp dẫn khác cùng chuyên đề này. 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ể. Ngoài ra, bài này còn giúp bạn biết cách định dạng đồ thị cho đẹp và hợp lý cũng như các thao tác vẽ đồ thị nâng cao khác. Vui lòng đọc mục lục dưới đây để có cái nhìn tổng quan về chuyên đề này. 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ệucủ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 nhauphụ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úngta sẽ cùng nhau khám phá thế giớiđồ thị của Exceltrong bài học này. Trong Excel 2007, bạn có thể tạo ra một biểu đồ như thế này trong 10 giây Những đồ thị này không phải là chuyện khó Đồ 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ànhhình ảnh với màu sắc và kiểu dáng rất phong phú. Nhìn chung,Excel 2007có 2 loại đồ thị đó là đồ thị nằm trongWorkSheet (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: Chọn đồ thị ->-> chọn Object in + Tên Sheet (đồ thị nằm trong Worksheet) hay chọn New sheet + Nhập tên ChartSheet bạn muốn vào. Chọn ChartSheet hay Embedded chart 3 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. Bước 2:Sau đó, click vào tabInsert, và trong nhómCharts, click vào nútColumn. Bạn có thể lựa chọn một loại biểu đồ, nhưng biểu đồ cột thường được sử dụng để so sánh. Bước 3:Sau khi bạn click vàoColumn, bạn sẽ thấy một số loạibiểu đồ cộtđể lựa chọn. Click vàoClustered Column, cột đầu tiên của biểu trong danh sách2-D Column. Một ScreenTip hiển thị tên biểu đồ loại khi bạn ngừng con trỏ vào bất cứ biểu đồ nào. Các ScreenTip cũng sẽ cho bạn thấy thông tin của từng loại biểu đồ. 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 tabDesign, dướiChart Tools, trong nhómType, bấm vàoChange Chart Typevà chọn một loại biểu đồ khác. Ví dụ về vẽ một đồ thị trong Excel 2007 và các tình huống thường gặpPhầ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. Bảng số liệu nghiên cứu Dưới đây là một số tình huống cụ thể trong việc xây dựng đồ thị: Chọn kiểu đồ thị B3.Xong bước 2 là chúng ta đã có ngay một đồ thị dạng cột như hình trên , tuy nhiên chúng ta cóthể thay đổi cách bố trí các thành phần của đồ thị bằng cách: Chọn đồ thị ->-> Chọn cách bố trí thích hợp. Ví dụ ta chọn kiểu Layout 3 trong Chart Layout như hình vẽ dưới đây: B4.Đảo các chuỗi số liệu từ dòng thành cột và ngược lại từ cột thành dòngbằng cách: Chart Tools ->Switch Row/Column. Chúng ta thực hiện lệnh này khi các đồ thị ở bước trên chưa hiển thi đúng như mong muốn. Ví dụ chúng ta muốn nhóm các nhóm tuổi lại để dễ so sánh giữa các tháng với nhau, thực hiện như hình vẽ:Và được kết quả: Đảo cột dòng để đồ thị ý nghĩa hơn B5.Nếu thấy kiểu đồ thị trên không đẹp, chúng ta có thểđổi đồ thị sang kiểu khácbằ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. Chọn lại kiểu đồ thị cho đẹp bằng hộp thoại Insert Chart B6.Nếu bạn còn cảm thấy kiểu màu chưa được đẹp thì cũng có thể chọn lại bằng cách vào chọn More Chọn Màu đồ thị bằng Chart Style Các thao tác trên đồ thịNhận biết các thành phần trên đồ thị ExcelCác thành phần thông dụng được chỉ ra trong bảng sau: Các thành phần trên đồ thị Excel Trong đó:Chart area: hay làPlot area:, hay làChart title:....có rất nhiều dạng. Trong đồ thị 3D còn có thêm các thành phần sau: Các thành phần bổ sung trong đồ thị 3D
Các thao tác với đồ thịChọn thành phần trên đồ thị
Chọn các thành phần trong một đồ thị Di chuyển đồ thị
Thay đổi kích thươc đồ thị
Sao chép đồ thị
Xóa đồ thị Sắp xếp và xóa các thành phần của đồ thị Thanh ribbon chứa các thành phần trên đồ thị In đồ thị Hiệu chỉnh và định dạng đồ thịViệc hiệu chỉnh và định dạng các thành phần trên đồ thị ta có thể dùng Ribbon hay dùng các hộp thoại. Cách dễ thực hiện nhất là nhấp phải chuột lên thành phần cần định dạng và chọn Format hay nhấn <Ctrl+1>. Sau đây là minh họa hiệu chỉnh và định dạng một số thành phần của đồ 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ị đó. Hiệu chỉnh chú thích cho đồ thị từ hộp thoại Data Source Hiệu chỉnh đường lưới ngang và dọc Hiệu chỉnh các trục Muốn hiệu chỉnh thông số cho trục trên đồ thị thì bạn hãy chọn trục -> 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. Category Axis Time Scale Axis 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ị Minh họa xóa chuỗi đường cung khỏi đồ thị Thêm chuỗi mới vào đồ thị Minh họa thêm chuỗi dữ liệu của tháng 32008 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 Minh họa thay đổi chuỗi số liệu trong đồ thị excel 2007 Ngoài ra chúng ta có thể sử dụng hàm Series cho đồ thị. Cú pháp như sau: Thật tuyệt vời phải không các bạn. Trong đó: Minh họa hàm Series của chuỗi số liệu tháng 3-2008 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ướngbạn vào: Chart Minh họa thêm đường xu hướng vào đồ thị 3.5 Sao chép một biểu đồ sang Word B1.Lựa chọn biểu đồ B2.Click vào 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 đồ B4.Click 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ảng dữ liệu vẽ đồ thị 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 tabInsert. Trong tab này có Trong tab này có nhóm Charts và những biểu đồ có sẵn 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ọnSelect Data Cửa sổ Select Data Source Trong cửa sổSelect Data Sourcebạn bắt đầu chọn vùng dữ liệu bằng cách. Cửa sổEdit Serieshiện ra , bạn chọn vùng dữ liệu của biểu đồ. Ví dụ bấm vào mũi tên đỏ trong Đá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: Đánh dấu vùng dữ liệu vẽ đồ thị Tiếp theo chọn tên của biểu đồ này bằng cách gõ thẳng vào mục Vẽ xong biểu đồ thứ nhất Khi bấm nútOKbạn sẽ lại thấy cửa sổSelect Data Sourcehiệ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ấmEdit. Tiếp theo bạn vẽ biểu đồ thứ hai bằng cách bấm 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 Chọn dữ liệu cho biểu đồ thứ hai Để thay đổi biểu đồ nào , ví dụ làThực tế, 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ọnbiểu đồ kiểuLine Chọn lại kiểu Line cho đồ thị thứ 2 Sau khi kết thúc việc lựa chọn bạn sẽ thấy như hình dưới: Kết quả vẽ nhiều kiểu biểu đồ khác nhau trong cùng trục tọa độ Tạo biểu đồ đường Gantt choquản lý dự ánBiể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: Đâ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. Mẫu biểu đồ đường Gantt 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 Dữ liệu bảng mẫu cho biểu đồ Gantt Bước 2:Vẽ một đồ thị thanh xếp chồng bằng cách chọnRibbon -> Insert -> Bar -> Stacked Bar Vẽ một đồ thị thanh xếp chồng Bước 3:Tiếp theo chúng ta sẽ định dạng biểu đồ thanh xếp chồng để nó hiển thị giống như biểu đồ Gantt mô tả tiến độ công việc. Trong biểu đồ, bấm chuột phải vào Chỉnh sử để thành Biểu đồ Gantt Bước 4:Chọn Tab Mẫu biểu đồ đường Gantt vẽ bằng Excel Các kiểu đồ thịKhi bạntạ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 đồ. Biểu đồ cộtDữ 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ằngbiể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: Biểu đồ cột liên cụm Biểu đồ đườngDữ 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ằngbiể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. Biểu đồ đường có đánh dấuBạ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 đồ hình trònDữ 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ộtchuỗi dữ liệu, tỷ lệ với tổng số các mục. Cácđiểm dữ liệutrong 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. Biểu đồ hình tròn Cân nhắc sử dụngbiểu đồ hình trònkhi: Biểu đồ vành khuyên bị cắtDữ 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ênbị 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ềuchuỗi dữ liệu. Biểu đồ vành khuyên bị cắt Biểu đồ thanhDữ 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 đồ thanh. Biểu đồ thanh minh họa mối quan hệ so sánh giữa các mục riêng. Trong biểu đồ thanh, các thể loại thường được tổ chức dọc theo trục dọc và các giá trị dọc theo trục ngang. Biểu đồ thanh Cân nhắc sử dụngbiểu đồ thanhkhi: Biểu đồ vùngDữ 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ácbiể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ể. Biểu đồ vùng Biểu đồ XY (phân tán) và biểu đồ bong bóngDữ 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. Biểu đồ tán xạ Cân nhắc việc sử dụngbiểu đồ tán xạkhi: Biểu đồ bong bóngCũng giống như biểu đồ tán xạ, mộtbiểu đồ bong bóngbổ 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. Biểu đồ bong bóng Biểu đồ chứng khoánDữ 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ộtbiểu đồ chứng khoáncao 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ự đó. Biểu đồ chứng khoán Biểu đồ bề mặtDữ 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ộtbiều đồ bề mặtkhi cả thể loại và chuỗi giá trị đều là các giá trị số. Biều đồ bề mặt Biểu đồ radarDữ 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 đồ radarso sánh các giá trị tổng hợp của một vàichuỗi dữ liệu. Biểu đồ radar Biểu đồ kết hợpDữ 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 nhaunhằ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ụngbiể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ụngbiểu đồ đườngđể giúp độc giả dễ dàng xác định được doanh thu trung bình hàng tháng. Biểu đồ kết hợp với trục phụ Vẽ hình trong ExcelMặc dù là công cụ tính toán nhưng Excel 2007 cũng được trang bị các công cu vẽ hình khá mạnh tương tự nhu Word 2007. Các công cụ vẽ hình nằm trong Ribbon -> |