Theo dõi tiến độ hướng tới mục tiêu - Các bài báo trên TechTV

Với năm mới sắp đến, bạn có thể muốn theo dõi tiến độ hướng tới một số loại mục tiêu. Biểu đồ thú vị này trong Excel chứa các yếu tố cho biết bạn có đang đi đúng hướng để đạt được mục tiêu hay không. Giả sử bạn muốn để chạy 250 dặm trong quý này.

Đường màu đen là mục tiêu
Dòng màu xanh là "đi đúng hướng" dòng - bạn phải chạy 2,7 dặm mỗi ngày để đáp ứng mục tiêu
Đường màu xanh lá cây là quãng đường chạy thực tế
Đường màu đỏ là dự đoán về nơi bạn sẽ hoàn thành quý, dựa trên tiến độ của bạn cho đến nay. Đường màu đỏ cho đến nay là phần thú vị nhất của biểu đồ.

Đây là cách tạo biểu đồ.

Nhập các tiêu đề này ở đầu trang tính. Trong ô A5, nhập ngày đầu tiên cho biểu đồ.
Đặt điểm di động trong A5. Bạn sẽ nhận thấy rằng có một chấm vuông ở góc dưới bên phải của A5. Đây là chốt điền. Với chuột, nắm lấy chốt điền và kéo xuống dưới. Xem chú giải công cụ cho đến khi bạn thấy rằng bạn đã kéo đủ xa để bao gồm ngày cuối cùng của quý.
Công thức của B2 là =COUNT(A5:A200). Điều này sẽ tính số ngày trong phạm vi của bạn.
Công thức của B3 là =B1/B2. Điều này tính toán bạn phải chạy bao xa mỗi ngày để đạt được mục tiêu.
Sử dụng cột B để nhập quãng đường bạn đã chạy mỗi ngày.
Mặc dù cột B chứa dữ liệu thực tế của bạn, nhưng nó không phù hợp để tạo biểu đồ. Bạn sẽ thực sự xây dựng một phạm vi biểu đồ hoàn toàn mới trong các cột C: F sẽ chứa các yếu tố cần thiết cho biểu đồ.
Cột C sẽ chứa ngày tháng. Nếu một trục của phạm vi biểu đồ của bạn chứa số hoặc ngày, bạn phải để trống ô trên cùng bên trái của phạm vi biểu đồ.
Đây là một lỗi kỳ lạ trong Excel - việc để trống ô đó sẽ cho phép Intellisense của Excel nhận ra phạm vi biểu đồ sau này. Vì vậy, hãy để trống ô C5 và nhập các tiêu đề cho Khoảng cách, Theo dõi và Mục tiêu trong D5: F5.
Công thức ở C5 là =A5.
Chọn ô C5. Từ menu, chọn Định dạng ô. Trên tab Số, hãy chọn danh mục Ngày, sau đó chọn định dạng 3/14.
Chọn ô C5. Kéo chốt điền xuống để sao chép công thức này. Kéo xuống đến mức bạn có dữ liệu trong cột A.
Đối với cột D & E, công thức ở hàng 5 sẽ khác với công thức ở hàng 6 trở xuống. Các công thức trong hàng 5 sẽ cung cấp một bắt đầu bằng số. Các công thức trong hàng 6 trở xuống sẽ tăng các giá trị số đó trong hàng 5. Công thức cho D5 là =B5- điều này chỉ đơn giản là sao chép khoảng cách của ngày đầu tiên vào phạm vi biểu đồ.
Trong bước sau, Excel sẽ thêm một đường xu hướng dựa trên các giá trị trong cột D. Nếu bạn chỉ sao chép D5 xuống, bạn sẽ nhận được các số 0 cho các ngày trong tương lai. Các số 0 sẽ làm mất hiệu lực của đường xu hướng. Mẹo là bạn cần điền vào các ngày trong tương lai với giá trị lỗi # N / A.
Khi đó, trong tiếng Anh, về cơ bản, công thức nói, "Nếu hôm nay chúng ta chạy, thì hãy cộng giá trị của ngày hôm nay vào tổng của ngày hôm qua. Nếu không, hãy đặt N / A vào ô này.

Để đạt được kết quả này, hãy nhập =IF(NOT(ISBLANK(B6)),D5+B6,NA())vào ô D6.
Chọn ô D6. Nhấp đúp vào chốt điền để sao chép công thức này xuống. Thủ thuật bấm đúp hoạt động bởi vì cột ở bên trái của ô này chứa các giá trị sẽ hướng dẫn Excel cách công thức cần sao chép.
Công thức trong E5 cần sao chép mỗi ngày từ B3. Nhập =B3E5.
Công thức cho phần còn lại của E cần thêm B3 vào ngày hôm trước. Công thức cho E6 có thể là =E5+B3nhưng công thức này sẽ không hoạt động khi bạn sao chép ô xuống các hàng khác. Bạn luôn muốn công thức thêm B3 vào ô ở trên.
Vì vậy, sử dụng =E5+$B$3trong E6. Các ký hiệu đô la đảm bảo rằng khi bạn sao chép công thức, nó sẽ luôn trỏ đến B3.
Di chuyển điểm di động trở lại E6 và bấm đúp vào chốt điền để sao chép công thức xuống.
Công thức cho tất cả các cột F là =$B$1. Phạm vi này sẽ được sử dụng để vẽ đường mục tiêu màu đen trên biểu đồ. Đây là một mẹo để nhập công thức này. Bắt đầu trong F5. Gõ dấu bằng. Sử dụng chuột, chạm vào B1. Sau đó, gõ phím F4 để đưa các ký hiệu đô la vào.
Nhấn Ctrl + Enter để chấp nhận công thức và ở trong ô hiện tại. Bây giờ bạn có thể bấm đúp vào chốt điền để sao chép công thức xuống.
Cuối cùng, bạn đã sẵn sàng để tạo biểu đồ. Chọn dãy C4: F94. Sử dụng Chèn - Biểu đồ từ menu hoặc nhấp vào biểu tượng Trình hướng dẫn Biểu đồ trong thanh công cụ tiêu chuẩn.
Trong bước 1, chọn Biểu đồ đường.
Biểu đồ sẽ yêu cầu rất nhiều tùy chỉnh, nhưng không có điều nào xảy ra trong trình hướng dẫn, vì vậy chỉ cần nhấp vào Kết thúc để tạo biểu đồ trông khủng khiếp này.
Nhấp chuột phải vào nền màu xám và chọn Định dạng Khu vực Lô đất.
Chọn Hiệu ứng tô màu.
Thiết lập gradient 2 màu như hình dưới đây.
Nhấp chuột phải vào dòng Mục tiêu màu vàng. Chọn Định dạng chuỗi dữ liệu.
Chọn một đường kẻ dày màu xanh lam cho chuỗi mục tiêu.
Lặp lại các bước 23-24 để thay đổi đường rãnh thành một đường mảnh màu đen.
Rất khó để định dạng chuỗi Khoảng cách, vì chỉ có 2 điểm và gần như không thể nhìn thấy. Nếu khó nhấp chuột phải vào một phần tử của biểu đồ, bạn có thể sử dụng trình đơn thả xuống trong thanh công cụ Biểu đồ.
Khi chuỗi khoảng cách được chọn, hãy sử dụng biểu tượng Thuộc tính để mở hộp thoại Định dạng cho chuỗi đã chọn.
Thêm Marker vào hộp thoại này. Thay đổi màu thành xanh lam đậm và độ dày đường kẻ thành trung bình.
Để thêm đường xu hướng, bạn sẽ phải nhấp chuột phải vào chuỗi khoảng cách. Điều này gần như không thể thực hiện vào thời điểm này. Vì vậy - tôi đề nghị bạn nên "ăn gian". Nhập điểm dữ liệu giả là 20 vào ô B7. Điều này sẽ cho phép bạn nhấp chuột phải vào chuỗi khoảng cách. Chọn Thêm đường xu hướng.
Trên hộp thoại Đường xu hướng, chọn Đường xu hướng tuyến tính.
Chọn ô B7 và gõ phím Delete để xóa giá trị giả ở đó. Bây giờ bạn sẽ có đường xu hướng được vẽ dưới dạng một đường màu đen dày.
Nhấp chuột phải vào đường xu hướng và chọn Định dạng đường xu hướng.
Thay đổi Đường xu hướng thành một đường màu đỏ bị đứt gãy với trọng số mỏng.
Trên tab Tùy chọn của Đường xu hướng Định dạng, hãy thay đổi tên chuỗi thành Dự đoán

Bây giờ bạn có một biểu đồ đã hoàn thành. Nếu bạn bỏ lỡ một ngày chạy, hãy nhập số 0 vào cột B. Nếu bạn bỏ lỡ một vài ngày, dòng dự đoán màu đỏ sẽ cho thấy rằng bạn không đi đúng hướng để đạt được mục tiêu.