Cách vẽ biểu đồ chu kỳ kinh doanh trong Excel
|
Có một số biểu đồ quy trình chu trình để lựa chọn, một số biểu đồ có mũi tên, như Chu trình cơ bản và các biểu đồ khác thể hiện mối quan hệ với ý tưởng trung tâm, như Chu trình hướng tâm Show Đây là cách bạn chèn một Chu kỳ xuyên tâm BƯỚC 1. Nhấp vào Chèn > Nghệ thuật thông minh > Chu kỳ > Chu kỳ xuyên tâm BƯỚC 2. Nhập tiêu đề chu trình bằng cách nhấp vào một hình BƯỚC 3. Để vào một chu kỳ mới, bạn cần nhấp vào một hình dạng và chọn Công cụ SmartArt > Thiết kế > Thêm hình dạng (Bạn cũng có thể nhấp chuột phải vào hình dạng và chọn tùy chọn này) BƯỚC 4. Để di chuyển chu trình lên trên hoặc xuống dưới, bạn cần nhấp vào một hình và chọn Công cụ SmartArt > Thiết kế > Di chuyển lên hoặc Di chuyển xuống BƯỚC 5. Để thay đổi chu kỳ từ phải sang trái, bạn cần nhấp vào một hình và chọn Công cụ SmartArt > Thiết kế > Phải sang trái BƯỚC 6. Để thay đổi màu của chu trình, bạn cần nhấp vào một hình và chọn Công cụ SmartArt > Thiết kế > Thay đổi màu BƯỚC 7. Trong trình đơn thả xuống SmartArt Tools > Design > SmartArt Styles, bạn có thể thay đổi kiểu chu trình của mình Xem hướng dẫn bên dưới để biết Biểu đồ Chu kỳ được tạo trong Excel dễ dàng như thế nào  TẢI XUỐNG SÁCH
NGUỒN HỮU ÍCH Nếu bạn thích thủ thuật Excel này, hãy chia sẻ nó liên kết Nếu bạn có doanh số bán hàng theo mùa hoặc các biện pháp đo lường hiệu suất khác, Biểu đồ chu kỳ có thể cung cấp thông tin chi tiết hơn về hiệu suất của bạn so với các kỹ thuật biểu đồ truyền thống Qua Charley Kyd11967 liên kết Biểu đồ Excel này cho thấy xu hướng bán hàng liên tục trong khoảng thời gian mười một năm. Ngoại trừ sự sụt giảm trong những năm Đại suy thoái, doanh số bán hàng dường như đang tăng lên Mặc dù biểu đồ này làm cho mô hình chung trở nên rõ ràng, nhưng nó giúp ích rất ít trong việc hiểu chi tiết về hiệu suất bán hàng. Điều này là do các chi tiết tốt hơn bị che khuất bởi tính thời vụ đáng kể của doanh số bán hàng (Bạn có thể tải xuống hai sổ làm việc được thảo luận trong bài viết này tại đây. ) Ví dụ, tháng nào có doanh số bán hàng nhiều nhất? Hình 2 cho thấy một cách phổ biến khác để trình bày dữ liệu như thế này. Thay vì hiển thị một xu hướng liên tục như trong biểu đồ trên, chúng tôi sử dụng Excel để vẽ một chuỗi dữ liệu mỗi năm Nhưng cách tiếp cận này mang lại rất ít cải tiến. Chỉ với một vài năm được hiển thị, chúng ta có thể dễ dàng nhìn thấy các xu hướng tiêu biểu trong năm hơn. Nhưng với nhiều năm trưng bày như trên thì dòng bầy trở nên vô nghĩa Naomi Robbins, viết trong cuốn sách của mình, Tạo đồ thị hiệu quả hơn, giới thiệu một cách khác để hiển thị cùng một dữ liệu, sử dụng Biểu đồ chu kỳ Trong biểu đồ Excel này, chúng ta có thể dễ dàng so sánh tình hình hoạt động của từng tháng trong năm. Các đường màu đen hiển thị xu hướng hiệu suất cho mỗi tháng một, mỗi tháng hai, v.v. trong tất cả mười một năm. Các đường ngang màu xám hiển thị doanh số bán hàng trung bình cho mỗi tháng trong những năm đó. Và các đường màu xanh đậm cho thấy xu hướng bán hàng của mỗi tháng trong các năm Dữ liệu này cho thấy doanh số bán hàng tháng 6 tăng nhanh hơn so với các tháng khác và doanh số bán hàng tháng 11 và tháng 12 tăng chậm hơn. Bởi vì những tháng có doanh số bán hàng cao của chúng tôi đang tăng nhanh hơn, biểu đồ này cho thấy doanh số bán hàng đang trở nên theo mùa hơn Vẫn còn một cách khác để hiển thị dữ liệu theo mùa là theo ngày trong tuần Và đây là một cách khác để làm điều đó Lần đầu tiên tôi sử dụng biểu đồ chu kỳ cho dữ liệu hàng tuần. Nhưng sau đó tôi tự hỏi liệu sử dụng biểu đồ cột có cung cấp nhiều thông tin hữu ích hơn cho doanh số hàng ngày hay không. Ở đây, chúng ta thấy rằng doanh số bán hàng có xu hướng giảm vào giữa tuần nhưng tăng vào cuối tuần. Nhưng vì tổng mức thay đổi chỉ chiếm khoảng 2% doanh thu nên hiệu quả không đáng kể trong trường hợp này Ngoài ra, vì hai nhóm cột hầu như giống hệt nhau nên chúng tôi biết rằng mẫu này không thay đổi nhiều giữa nửa đầu và nửa sau của tổng thời gian báo cáo Cách tạo biểu đồ chu kỳ trong ExcelĐể bài viết này không biến thành một cuốn sách ngắn, tôi sẽ chỉ giải thích những yếu tố quan trọng nhất của sổ làm việc gốc chứa các biểu đồ này. Bạn có thể tải xuống sổ làm việc thực tế tại đây Bốn bước cần thiết để tạo Biểu đồ chu kỳ như Hình 3. Đây là bước đầu tiên Bước 1. Thiết lập dữ liệu của bạnĐây là bước dễ nhất, về mặt khái niệm. Bạn chỉ cần có dữ liệu của mình trong Excel để có thể truy cập dễ dàng—thường là với hàm SUMIFS. Thông thường, dữ liệu của bạn sẽ ở trong Bảng Excel Bước 2. Thiết lập các bảng phân tầngMỗi biểu đồ trước đó lấy dữ liệu từ một hoặc nhiều bảng phân tầng. Và mấu chốt của mỗi biểu đồ chính là cấu trúc của bảng phân tầng Tất cả đều sử dụng công thức để trả về dữ liệu từ cùng một tập dữ liệu. Và trong tất cả chúng, tôi đã tô màu xanh lục để hiển thị cho bạn các khu vực của bảng phân tầng mà mỗi biểu đồ tham chiếu. Bạn sẽ tìm thấy các Bảng này trong CyclePlotA. sổ làm việc xlsx, bạn có thể tải xuống tại đây Dưới đây là một vài hàng trên cùng của bảng phân tầng cho biểu đồ 1, biểu đồ có một dòng Và đây là đầu bảng phân tầng cho Biểu đồ 2 Các công thức trả về số trong các ô màu lục sử dụng mẫu được hiển thị ở đây B15. =INDEX(Thực tế hàng tháng,MATCH(DATE($A15,B$13,1),Ngày hàng tháng,0))*ScaleReport Thông thường, chúng tôi sẽ sử dụng hàm SUMIFS để trả về các giá trị từ Bảng có giá trị hàng tháng. Nhưng những công thức này sử dụng INDEX-MATCH để thay thế. Điều này là do nếu MATCH không thể tìm thấy ngày trong Bảng, nó sẽ trả về #N/A—chính xác là những gì chúng tôi muốn cho các giá trị được vẽ trên biểu đồ Mặt khác, nếu chúng ta sử dụng SUMIFS, nó sẽ trả về 0 cho những ngày không tìm thấy. Và điều đó sẽ khiến biểu đồ vẽ các số 0 cho các giá trị của ngày không tìm thấy Tuy nhiên, nếu phiên bản Excel của bạn bao gồm hàm LET, bạn có thể sử dụng mẫu như thế này để đạt được kết quả tương tự =LET(MySum, SUMIFS(bất kỳ), Kết quả, IF(MySum=0, NA(), MySum), Kết quả) Các giá trị dữ liệu cho Biểu đồ 3 là Và đối với các giá trị trung bình cho Biểu đồ 3, chúng tôi có Dưới đây là các công thức cho cột số đầu tiên C53. =AVERAGEIFS(C$35. C$48,C$35. C$48,”<>#N/A”) C54. =C53 Trong ô C53, hàm AVERAGEIFS tính trung bình các số trong cột riêng của nó trong bảng phân loại cho Biểu đồ 3a…miễn là các số đó không bằng nhau #N/A. Hàm có thể neo các tham chiếu của nó trong hai hàng màu xám vì AVERAGEIFS (và AVERAGE) bỏ qua các hàng trống Và bởi vì mỗi cột màu xanh lục trong bảng phân tầng 3b chứa cùng một giá trị nên ô C54 và các ô màu lục bên dưới đều trả về các giá trị từ các ô trước đó Dưới đây là các giá trị XU HƯỚNG trong Biểu đồ 3 Ở đây, các công thức cho Biểu đồ 3c phải sử dụng hàm OFFSET để tính XU HƯỚNG. Điều này là do TREND không cho phép chúng tôi tham chiếu các hàng màu xám. Đây là một trong những công thức C70. =TREND(OFFSET(C$35,1,0,C$68),OFFSET($A$18,1,0,C$68),$A70) Và đây là cú pháp cho OFFSET và TREND =OFFSET(tham chiếu, hàng, cột, chiều cao, chiều rộng) =TREND(known_ys, known_xs, new_xs, const) Do đó, trong ô C70, hàm OFFSET đầu tiên bắt đầu ở hàng màu xám trên cùng, di chuyển tham chiếu xuống một hàng, không di chuyển sang trái hoặc phải, sau đó cung cấp cho tham chiếu chiều cao là 12 hàng, như được tính bởi ô C68 OFFSET thứ hai trả về một phạm vi tương tự cho cột A. Và sau đó, XU HƯỚNG sử dụng các phạm vi này để tính toán các giá trị cho hàng đầu tiên đến hàng thứ mười hai Tương tự, đối với Biểu đồ 4, đây là các giá trị dữ liệu Và các giá trị trung bình cho Biểu đồ 4 Và các giá trị xu hướng cho Biểu đồ 4 Và cuối cùng, đây là các giá trị của Biểu đồ 5, biểu đồ cột Bước 3. Thiết lập tên dãyBây giờ chúng ta sẽ tạo ba tên dải ô có thể không giống với bất kỳ tên dải ô Excel nào mà bạn đã từng tạo trước đây. Bạn sẽ bắt đầu quá trình bằng cách chọn mười hai phạm vi "không liền kề" Để đơn giản hóa mọi thứ, chúng tôi sẽ sử dụng bảng sau mà bạn sẽ tìm thấy trong CyclePlotB. xlsx, bạn có thể tải xuống tại đây, cùng với CyclePlotA. xlsx Để bắt đầu, hãy nhấp và kéo từ ô C7 đến C20, như bạn thường làm để chọn phạm vi đó. Nhả nút chuột của bạn Giữ phím Ctrl của bạn Bây giờ hãy nhấp và kéo từ ô D6 đến D19. Thả nút chuột của bạn một lần nữa. Nếu bạn không giữ phím Ctrl, Excel sẽ di chuyển lựa chọn của bạn sang phạm vi thứ hai này. Nhưng thay vào đó, Excel hiện thêm phạm vi thứ hai đó vào lựa chọn của bạn Với phím Ctrl vẫn được giữ, hãy thao tác theo cách của bạn trên bảng trong Excel, chọn tổng cộng 12 phạm vi độc lập, một phạm vi cho mỗi tháng. Trong hình bên dưới—với tám hàng bị ẩn—bạn có thể thấy một khoảng trắng nhỏ giữa mỗi cột được chọn. Điều này cho bạn biết rằng mỗi cột trong số 12 cột đó được chọn riêng lẻ. Tức là chúng không liền nhau Sau khi tôi chọn các phạm vi như được hiển thị, tôi đã đặt tên cho vùng chọn này. Để làm như vậy, tôi đã chọn Công thức, Tên xác định, Tên xác định. Trong hộp thoại, tôi đã nhập PlotData làm tên, sau đó chọn OK Sau đó, tôi làm tương tự với hai bảng phân tầng khác trong sổ làm việc. Tôi đã chọn không liên tục 12 cột trong phần Plot Average và xác định lựa chọn này là PlotAvg. Và tôi cũng làm như vậy cho phần PlotTrend, đặt tên cho vùng chọn là PlotTrend. Trong mọi trường hợp, tôi đảm bảo bao gồm các hàng viền màu xám bên trên và bên dưới các cột dữ liệu Tại sao sử dụng các hàng viền màu xám? . Điều đó mang lại cho chúng tôi 12 phân đoạn dòng mà bạn thấy cho các dòng màu đen, dòng màu xanh lam và dòng màu xám ở đây Cuối cùng, tôi đã xác định hai tên phạm vi bình thường. Tôi đã xác định DateText để chỉ phạm vi C2. N2, như thể hiện trong hình bên dưới. Sau đó, tôi đã xác định DateValue để chỉ phạm vi C3. N3 Bước 4. Tạo biểu đồBây giờ nền tảng đã được chuẩn bị xong, chúng ta có thể tạo biểu đồ Cycle Plot. Chuỗi dữ liệu đầu tiên sẽ chỉ hiển thị các nhãn trục X được hiển thị trong biểu đồ 1 đến 4 ở trên. Ba chuỗi dữ liệu khác sẽ hiển thị các đường được hiển thị trong biểu đồ trước Để bắt đầu, hãy chọn phạm vi màu xanh lục C2. N3, như được hiển thị ở đây Để vẽ dữ liệu này, hãy chọn Chèn, Biểu đồ, Đường, rồi chọn loại phụ trên cùng bên trái Đối tượng biểu đồ của bạn sẽ hiển thị các tháng trong trục X nhưng không hiển thị dữ liệu Bạn sẽ không cần tiêu đề của biểu đồ. Do đó, hãy chọn nó trong biểu đồ của bạn rồi nhấn phím Delete để xóa nó Với trục X được chọn, nhấn Ctrl+1 hiển thị ngăn Định dạng Trục. Sau đó chọn biểu tượng Kích thước & Thuộc tính, như được hiển thị ở đây Trong phần Căn chỉnh, trong hộp chỉnh sửa Góc tùy chỉnh, nhấp vào mũi tên lên một lần rồi nhấp vào mũi tên xuống một lần. Điều này sẽ đặt Góc tùy chỉnh thành 0 Vẫn trong Ngăn định dạng trục, nhấp vào biểu tượng Tùy chọn trục (biểu đồ cột), như thể hiện trong hình trước. Trong phần Tùy chọn trục, đối với cài đặt Vị trí trục, hãy nhấp vào Dấu kiểm. Điều đó sẽ cung cấp cho bạn một biểu đồ trông như thế này Nói chung, bạn nên thay đổi tham chiếu ô của biểu đồ thành tên dải ô. Điều này giúp ghi lại các công thức của bạn và thêm tính linh hoạt. Để gán tên, nhấp vào đường màu xanh phía trên trục X để hiển thị công thức SERIES trong thanh công thức của bạn. Sau đó thay đổi công thức này… =SERIES(,Dữ liệu. $C$2. $N$2,Dữ liệu. $C$3. $N$3,1) …đến công thức này… =SERIES(,CyclePlotB. xlsx. DateText,CyclePlotB. xlsx. DateAxisValue,1) Bây giờ, hãy thêm hàm SERIES thứ hai, hàm này sẽ hiển thị dữ liệu Biểu đồ chu kỳ Bạn sẽ thêm chuỗi theo hai bước. Trước tiên, chọn bất kỳ phạm vi dữ liệu hai ô nào trong một hàng, giả sử C8. D8, như được hiển thị ở đây. Sao chép phạm vi này và dán nó vào biểu đồ của bạn Khi bạn làm như vậy, biểu đồ của bạn sẽ giống như thế này Tại thời điểm này, bạn nên ẩn dòng SERIES 1. Để làm như vậy, bạn cần phải chọn nó. Điều này có thể khó thực hiện vì dòng SERIES 1 chồng lên đường viền trục X Một cách dễ dàng để giải quyết vấn đề này là chọn toàn bộ Vùng biểu đồ của bạn rồi nhấn Ctrl + Mũi tên xuống trên bàn phím của bạn một lần. Làm như vậy để chọn chức năng SERIES được tạo gần đây nhất, là đường màu đỏ. Nhấn lại để chọn đường màu xanh Ngoài ra, với biểu đồ của bạn được chọn, chọn Định dạng. Và sau đó, trong nhóm Lựa chọn hiện tại, bạn sẽ thấy hộp danh sách thả xuống cho phép bạn chọn bất kỳ phần nào của biểu đồ mà bạn muốn chọn. Vì vậy, trong trường hợp này, bạn sẽ chọn Series 1 Với công thức SERIES 1 hiển thị trong thanh công thức của bạn, hãy nhấn Ctrl+1 để hiển thị ngăn Định dạng chuỗi dữ liệu. Chọn biểu tượng Fill & Line (hộp sơn). Trong phần Line, chọn No Line Tiếp theo, chọn đường màu đỏ, như trong biểu đồ trên. Thay thế tham chiếu đến C8. D8 với tham chiếu đến phạm vi PlotData. Tức là thay… =SERIES(,CyclePlotB. xlsx. Ngày Văn bản, Dữ liệu. $C$8. $D$8,2) …đến… SERIES(,CyclePlot. xls. Ngày Văn bản, Biểu đồ. Dữ liệu lô ,2) …và biểu đồ của bạn sẽ giống như thế này Tin tốt là biểu đồ hiện hiển thị các Lô chu kỳ. Tin xấu là nhãn trục X là một thảm họa Lý do của sự cố này là SERIES 1, nhãn ngày của chúng tôi và SERIES 2, Lô chu kỳ của chúng tôi, hiện đang chia sẻ cùng một trục X, nhưng các yêu cầu của chúng xung đột đáng kể. Điều này là do các đường màu đỏ đang vẽ biểu đồ dữ liệu hàng tháng trong 12 năm… tổng cộng 144 điểm dữ liệu. Mặt khác, trục X chỉ vẽ một năm nhãn hàng tháng Để khắc phục sự cố này, chúng ta cần thực hiện bốn bước Đầu tiên, chúng tôi thiết lập sê-ri Lô chu kỳ màu đỏ (Sê-ri 2), để sử dụng trục thứ cấp. Để làm như vậy, hãy chọn Sê-ri 2 bằng cách nhấp vào bất kỳ dòng màu đỏ nào. Nhấn Ctrl + 1 để hiển thị ngăn Định dạng chuỗi dữ liệu, sau đó trong phần Tùy chọn chuỗi, bấm Trục phụ Thứ hai, với biểu đồ được chọn, hãy chọn "chicklet" của Thành phần biểu đồ ở bên phải của biểu đồ, sau đó đặt cài đặt Trục mà bạn thấy ở đây Thứ ba, chúng ta cần xóa trục lộn xộn hiện ở trên cùng của biểu đồ. Để làm như vậy, bấm vào khu vực lộn xộn đó, nhấn Ctrl + 1 để hiển thị ngăn Định dạng Trục, sau đó, trong biểu tượng Tùy chọn Trục (biểu đồ cột), trong phần Nhãn, đặt Vị trí Nhãn thành Không có Và cuối cùng, tại biểu tượng Fill & Line (hộp sơn), trong phần Line, nhấp vào No Line cho trục phụ đó Biểu đồ của bạn bây giờ sẽ trông giống như thế này Đó là tất cả xuống đồi từ đây Chúng ta cần thêm hai dòng nữa vào biểu đồ. Một cái sẽ hiển thị mức trung bình cho mỗi tháng và cái còn lại sẽ hiển thị các đường xu hướng. Để làm như vậy, chọn dòng Cycle Plot màu đỏ. Bạn sẽ thấy công thức này trong thanh công thức của mình =SERIES(,CyclePlotB. xls. DateText, CyclePlotB. xls. Lô dữ liệu, 2) Chọn toàn bộ công thức trong thanh công thức của bạn, rồi nhấn Ctrl+C để sao chép Chọn cạnh của biểu đồ của bạn để hiển thị Vùng biểu đồ. Làm như vậy sẽ xóa thanh công thức của bạn. Nhấp vào thanh công thức của bạn rồi nhấn Ctrl + V để dán chuỗi đã sao chép của bạn vào đó. Sau đó sửa lại công thức đã sao chép để phần tô đậm bên dưới được như hình =SERIES(,CyclePlotB. xls. DateText, CyclePlotB. xls. PlotAvg , 3) Cuối cùng, nhấn Enter để nhập công thức mới này (Tùy thuộc vào phiên bản Excel của bạn, bạn có thể không chỉnh sửa được công thức SERIES trong thanh công thức của mình. Trong trường hợp này, với biểu đồ của bạn được chọn, hãy chọn Thiết kế biểu đồ, Chọn dữ liệu và sử dụng hộp thoại Chọn nguồn dữ liệu để chỉnh sửa công thức SERIES của bạn. ) Chọn lại biểu đồ của bạn. Nhấn Ctrl + V lần nữa để dán lại công thức SERIES đã sao chép của bạn. Sửa đổi công thức đã sao chép để nó trông như thế này =SERIES(,CyclePlotB. xls. DateText, CyclePlotB. xls. Xu hướng lô , 4) Và nhấn Enter Khi bạn hoàn tất, bạn sẽ có một biểu đồ giống như thế này Chắc chắn bạn sẽ muốn thử nghiệm với kích thước biểu đồ, màu sắc, độ dày của đường và các định dạng khác. Để định dạng các đường, hãy chọn một đường trong biểu đồ của bạn, nhấn Ctrl + 1, sau đó điều chỉnh các cài đặt có liên quan mà bạn sẽ tìm thấy Để thêm tiêu đề, phụ đề hoặc văn bản bên dưới biểu đồ, hãy đặt cài đặt Tô màu của Vùng biểu đồ thành Không tô màu và cài đặt Dòng của nó thành Không có dòng. Và sau đó nhập văn bản của bạn vào bảng tính của bạn. Biểu đồ 3 ở gần đầu trang này có thể cung cấp cho bạn một số ý tưởng về định dạng Để thêm hoặc giảm số năm dữ liệu mà biểu đồ của bạn hiển thị, chỉ cần chèn hoặc xóa các hàng dữ liệu hoặc công thức giữa các đường viền màu xám được tô bóng trong ba bảng phân loại Một lần nữa, bạn có thể tải xuống hai sổ làm việc mẫu bằng cách nhấp vào liên kết này
liên kết Bài viết trước Cách tạo biểu đồ dấu đầu dòng để thay thế thước đo trong Excel Bài viết tiếp theo Cách tạo huyền thoại biểu đồ động trong Excel Charley Kyd Khi mới vào nghề, tôi đã làm việc gần 20 năm với tư cách là Giám đốc tài chính của các công ty khởi nghiệp và quay vòng. Nhưng cuối cùng tôi đã kiệt sức khi phải vật lộn liên tục với dòng tiền. Đó là khi tôi bắt đầu viết về Lotus 1-2-3, phần mềm bảng tính mà tôi đã sử dụng hầu hết thời gian trong những ngày làm CFO. Khi Excel chuẩn bị được giới thiệu cho PC, một trong những biên tập viên tạp chí của tôi đã tổ chức một cuộc họp để tôi xem sản phẩm, nói chuyện với các nhà phát triển và viết một câu chuyện trang bìa về Excel. Vì vậy, tôi đã sử dụng phiên bản Excel đầu tiên trước khi nó được tung ra. Và tôi cũng đã sử dụng phiên bản đầu tiên của VisiCalc trước khi nó được ra mắt. Và sau đó, |
