Biểu đồ nhiệt kế Excel - Tải xuống miễn phí & Cách tạo

Hướng dẫn này sẽ trình bày cách tạo biểu đồ nhiệt kế trong tất cả các phiên bản Excel: 2007, 2010, 2013, 2016 và 2022.

Biểu đồ nhiệt kế là một sửa đổi đặc biệt của biểu đồ cột xếp chồng lên nhau. Các cột được đặt chồng lên nhau và trông giống như một nhiệt kế, sẽ đầy lên khi bạn tiến tới mục tiêu của mình.

Bất kỳ lúc nào bạn cần theo dõi tiến độ đạt được mục tiêu - có thể là tài trợ, doanh số, doanh thu hoặc các số liệu có liên quan khác - biểu đồ này cung cấp một cách đơn giản và hiệu quả để trực quan hóa dữ liệu đó.

Nhưng một biểu đồ nhiệt kế đơn giản không giúp cắt cải nữa (thôi nào, đây là năm 2022!).

Trong hướng dẫn từng bước, chuyên sâu này, bạn sẽ học cách vẽ biểu đồ mục tiêu nhiệt kế đa năng đáng kể với một nhãn ưa thích hoạt động ngay cả khi kết quả của bạn vượt quá mục tiêu đã nêu.

Chỉ cần kiểm tra nó ra:

Như bạn sẽ thấy ngay sau đây, việc xây dựng một biểu đồ nhiệt kế ấn tượng cần rất nhiều nỗ lực. Đó là lý do tại sao chúng tôi thiết kế Phần bổ trợ Trình tạo Biểu đồ, một công cụ để xây dựng các biểu đồ Excel nâng cao chỉ trong một vài cú nhấp chuột.

Vì vậy, chúng ta hãy đi xuống vấn đề thực tế.

Bắt đầu

Đối với mục đích minh họa, hãy tưởng tượng bạn ra mắt một cửa hàng thương mại điện tử như một người hối hả và muốn xây dựng biểu đồ nhiệt kế để theo dõi hiệu suất của cửa hàng so với mục tiêu doanh thu đã nêu của bạn. Vào năm 2022, bạn đã có một hoạt động khá tốt.

Cửa hàng đã tạo ra doanh thu 21.734 đô la, vượt quá mục tiêu ban đầu của bạn một chút (bạn có thể khui một chai sâm panh sau đó).

Điều đó đang được nói, hãy xem tập dữ liệu cho biểu đồ:

Trước khi chuyển sang xây dựng biểu đồ, chúng ta cần chia nhỏ từng điểm dữ liệu để bạn có thể dễ dàng truy xuất lại các bước sử dụng dữ liệu của mình.

Tập dữ liệu có hai cột:

• Dữ liệu gốc: Cột này đại diện cho dữ liệu thực tế của bạn, bất kể nó có thể là gì. Cứ để nguyên như vậy.
• Đầu vào biểu đồ: Đây là nơi tất cả những điều kỳ diệu xảy ra. Bảng chứa tất cả các giá trị đặc biệt mà bạn sẽ sử dụng để xây dựng biểu đồ. Ngoài ra, một lưu ý quan trọng: các danh mục phải được sắp xếp theo cách chính xác như được hiển thị trên ảnh chụp màn hình ở trên!

Bạn có thể tự hỏi tất cả các giá trị trong bảng thứ hai đến từ đâu. Chà, bạn thực sự sẽ phải tự mình tính toán chúng bằng cách sử dụng các công thức Excel đặc biệt được liệt kê dưới đây (ngạc nhiên, ngạc nhiên).

Thật không may, không có cách nào khác để giải quyết vấn đề này - nhưng các hướng dẫn bên dưới sẽ giúp bạn thực hiện khá dễ dàng.

Vậy là cuộc hành trình bắt đầu!

Bước # 1: Thiết lập bảng trợ giúp.

Vì là mấu chốt giữ tất cả lại với nhau, nên mỗi phần tử trong bảng thứ hai đều có mục đích của chúng. Hãy trình bày chi tiết hơn về những chàng trai hư này.

Doanh thu mục tiêu: Điểm dữ liệu này phải khớp với giá trị mục tiêu (B7). Đơn giản vậy thôi.

Công thức Tổng Doanh thu: Hàm IF này ngăn không cho các dấu dữ liệu xếp chồng lên nhau theo cách sai trong trường hợp kết quả vượt quá mục tiêu:

1	= IF (B6 <= B7, B6, B7)

Đây là phiên bản được giải mã dựa trên dữ liệu gốc:

1	= IF ({Total} <= {Goal}, {Total}, {Goal})

Dịch sang ngôn ngữ cơ bản của con người: nếu kết quả (B6) nhỏ hơn hoặc bằng mục tiêu (B7), công thức trả về giá trị kết quả (B6) -trong trường hợp của chúng tôi, bất kỳ thứ gì giảm xuống dưới mốc 20 nghìn đô la. Ngược lại, nếu kết quả (B6) vượt quá mục tiêu (B7), công thức trả về giá trị mục tiêu (B7).

Mẹo nhanh: nhấp vào nút “Định dạng số kế toán ” cái nút (Tab Trang chủ > Nhóm số) để hiển thị đầu ra của hàm dưới dạng tiền tệ.

Đã làm quá mức: Cái này Hàm IF tính toán xem mục tiêu của bạn có được thực hiện quá mức hay không. Bất cứ khi nào điều đó xảy ra, công thức sẽ trừ mục tiêu (B7) từ kết quả (B6) và trả lại những gì còn lại. Nếu không, công thức chỉ hiển thị số không:

1	= IF (B7 <= B6, B6-B7, 0)

Phiên bản đã giải mã:

1	= IF ({Goal} <= {Total}, {Total} - {Goal}, 0)

Tiến triển: Điều này khá dễ hiểu. Chúng tôi cần đo lường tiến độ theo tỷ lệ phần trăm của mục tiêu cho nhãn cực kỳ lạ mắt của chúng tôi. Đơn giản chỉ cần chia kết quả (B6) bởi mục tiêu (B7) và định dạng đầu ra dưới dạng tỷ lệ phần trăm, nhấp vào “Phần trăm kiểu dáng ” cái nút (Tab Trang chủ > Nhóm số).

1

= B6 / B7

Phiên bản đã giải mã:

1	= {Tổng số} / {Mục tiêu}

Nhãn mác: Kem trên chiếc bánh. Giá trị này kết hợp các giá trị số và tỷ lệ phần trăm đại diện cho tiến trình đạt được mục tiêu của bạn và định dạng chúng một cách gọn gàng. Để giải quyết vấn đề này, chúng ta sẽ chuyển sang hàm TEXT (người mới sử dụng Excel, dấu ngoặc nhọn để tác động):

1	= TEXT (B6, "$ # 0,00") & "(" & TEXT (E5, "## 0%") & ")"

Và phiên bản được giải mã:

1	= TEXT ({Total}, "{Định dạng giá trị dưới dạng tiền tệ}") & "(" & TEXT ({Tiến trình}, "{Định dạng giá trị dưới dạng phần trăm}") & ")"

Nếu điều đó có vẻ khó khăn, hãy xem bài viết được liên kết ở trên đề cập đến hàm.

Đây là cách nó trông như thế nào trong thực tế:

Chà, điều tồi tệ nhất đang ở phía sau chúng ta. Để tổng hợp tất cả, hãy xem qua các công thức:

Thiết lập biểu đồ	Công thức
Công thức Tổng doanh thu	= IF (B6 <= B7, B6, B7)
Quá mức	= IF (B7 <= B6, B6-B7, 0)
Tiến triển	= B6 / B7
Nhãn mác	= TEXT (B6, “$ # 0,00 ″) &” (”& TEXT (E5,“ ## 0% ”) &“) ”

Khi đó, hãy chuyển sang bước tiếp theo.

Bước # 2: Tạo biểu đồ cột xếp chồng.

1. Đánh dấu phạm vi ô E2: E4 bên trong Giá trị
2. Chọn Chèn
3. Nhấn vào "Chèn biểu đồ cột hoặc thanh" biểu tượng.
4. Lựa chọn "Cột xếp chồng.”

Bước # 3: Xếp chuỗi dữ liệu chồng lên nhau.

Khi biểu đồ xuất hiện, hãy sắp xếp lại chuỗi dữ liệu:

1. Bấm vào biểu đồ.
2. Chuyển sang Thiết kế
3. Lựa chọn "Chuyển đổi Hàng / Cột.”

Bước # 4: Thay đổi màu đánh dấu dữ liệu.

Để biến biểu đồ thành một thứ giống nhiệt kế, hãy bắt đầu bằng cách thay đổi màu sắc của các điểm đánh dấu dữ liệu. Trước tiên, hãy tạo điểm đánh dấu dữ liệu cho "Doanh thu mục tiêu" của Series 1 (E2) trong suốt và đặt các đường viền xung quanh nó.

Nhấp chuột phải vào điểm đánh dấu dữ liệu đại diện cho "Doanh thu mục tiêu" (E2-phần dưới cùng của biểu đồ) và chọn “Định dạng chuỗi dữ liệu.”

Trong ngăn tác vụ bật lên, hãy làm như sau:

1. Điều hướng đến Điền vào dòng
2. Dưới "Lấp đầy," lựa chọn "Không có điền.”
3. Dưới "Biên giới," lựa chọn "Đường vẽ liền.”
4. Dưới "Biên giới," nhấn vào Tô màu biểu tượng để mở bảng màu và chọn màu đỏ.
5. Đặt Chiều rộng có giá trị đối với 2 pt.

Tô màu các điểm đánh dấu dữ liệu khác như sau:

1. Đối với Series 2 “Công thức Tổng doanh thu” (E3):
   1. Chọn điểm đánh dấu dữ liệu (E3) với Định dạng chuỗi dữ liệu ngăn tác vụ vẫn mở.
   2. Dưới "Lấp đầy," lựa chọn "Điền đặc.”
   3. Nhấn vào Tô màu biểu tượng và chọn màu đỏ.
2. Đối với Series 3 “Quá mức” (E 4):
   1. Chọn điểm đánh dấu dữ liệu (E 4) với Định dạng chuỗi dữ liệu ngăn tác vụ vẫn mở.
   2. Dưới "Lấp đầy," lựa chọn "Điền đặc.”
   3. Nhấn vào Tô màu biểu tượng và chọn màu xanh lợt.

Bước # 5: Thêm nhãn dữ liệu vào biểu đồ.

Bây giờ đã đến lúc chèn nhãn dữ liệu đáng yêu (E6) mà chúng tôi đã lấy rất nhiều máu, mồ hôi và nước mắt để cùng nhau.

Nhấp chuột phải vào Chuỗi 2 “Công thức Tổng doanh thu” (cái có tô màu đỏ) và lựa chọn "Thêm nhãn dữ liệu.”

Bước # 6: Thay đổi giá trị nhãn dữ liệu.

Liên kết nhãn với giá trị trong bảng thứ hai.

1. Bấm đúp vào nhãn.
2. Kiểu “=” vào Thanh công thức.
3. Chọn giá trị Nhãn (E6).

Bây giờ chúng ta cần tiết chế nó một chút. Làm cho nhãn in đậm, tô màu nó bằng màu trắng và thay đổi kích thước phông chữ để tăng tính thẩm mỹ cho phần tử (Tab Trang chủ > Nhóm phông chữ).

Bước # 7: Định vị lại nhãn dữ liệu.

Sau khi bạn đã thiết lập nhãn, hãy đẩy nhãn lên đầu trên của điểm đánh dấu dữ liệu liên quan. Nhấp chuột phải vào nhãn dữ liệu và chọn “Định dạng Nhãn Dữ liệu.”

Sau đó, trong Tùy chọn nhãn tab, dưới Vị trí nhãn, lựa chọn "Inside End.”

Bước # 8: Di chuyển Chuỗi 1 "Doanh thu mục tiêu" sang trục phụ.

Để tạo khuôn biểu đồ thành hình dạng nhiệt kế, bạn cần phải Chuỗi 1 "Doanh thu mục tiêu" (E2) ở đúng vị trí.

1. Nhấp chuột phải vào Chuỗi 1 "Doanh thu mục tiêu" (cái trong suốt ở dưới cùng).
2. Nhặt "Định dạng chuỗi dữ liệu.”

Trong ngăn tác vụ xuất hiện, dưới Loạt âm mưu đang bật, lựa chọn "Trục thứ cấp.”

Bước # 9: Loại bỏ trục thứ cấp.

Trục thứ cấp đã làm công việc của nó, vì vậy chúng ta không cần nó nữa.

Nhấp chuột phải vào trục phụ (cột số ở bên phải) và chọn “Xóa bỏ.”

Bước # 10: Sửa đổi phạm vi tỷ lệ trục chính.

Như bạn có thể nhận thấy, tỷ lệ trục chính có vẻ hơi lệch. Hãy thay đổi điều đó.

Nhấp chuột phải vào tỷ lệ trục chính và chọn “Định dạng trục.”

Đặt Giới hạn tối thiểu có giá trị đối với -10000. Tại sao? Làm như vậy sẽ giải phóng một số không gian bên dưới cho bóng đèn nhiệt kế.

Giá trị này khác nhau dựa trên dữ liệu biểu đồ của bạn. Một nguyên tắc chung là sử dụng số mục tiêu của bạn và chia nó cho hai.

Ví dụ: giả sử mục tiêu doanh thu của bạn là 100.000 đô la. Sau đó của bạn Giới hạn tối thiểu giá trị sẽ là -50000.

Bước # 11: Thay đổi định dạng số của tỷ lệ trục chính và thêm dấu đánh dấu.

Những con số âm đó không giúp ích gì cho chúng ta ở đây. May mắn thay, chúng ta có thể dễ dàng loại bỏ chúng bằng cách sử dụng sức mạnh của các định dạng số tùy chỉnh.

Với Định dạng trục ngăn tác vụ vẫn mở, bên dưới Tùy chọn trục, điều hướng đến Con số và làm như sau:

1. Vì "Loại," lựa chọn ""
2. Kiểu "$#,##0.00;;$0" vào Định dạng mã trường và nhấp vào Thêm vào.

Ngoài ra, dưới Đánh dấu chọn, lựa chọn "Bên trong" từ "Loại chính" trình đơn thả xuống.

Bây giờ, hãy chuyển sang Điền vào dòng chuyển hướng. Thay đổi màu của các dấu tích thành màu đen và đặt Chiều rộng có giá trị đối với 1,5 pt.

Bước # 12: Xóa tiêu đề biểu đồ, đường lưới và trục hoành.

Xóa các phần tử biểu đồ không có giá trị thực tế: tiêu đề biểu đồ, đường lưới và trục hoành. Nhấp chuột phải vào từng phần tử và chọn “Xóa bỏ.”

Bước # 13: Thay đổi độ rộng khoảng trống của chuỗi dữ liệu trên trục phụ.

Khi bạn đang hoàn thiện những nét cuối cùng trên biểu đồ của mình, hãy làm mỏng ống nhiệt kế. Bắt đầu với chuỗi được đặt trên trục phụ (Dòng 1 “Doanh thu mục tiêu”).

Nhấp chuột phải vào Series 1 “Doanh thu mục tiêu” (E2), mở Định dạng chuỗi dữ liệu ngăn tác vụ và đặt Chiều rộng khoảng cách có giá trị đối với 250%. Tăng tỷ lệ phần trăm nếu bạn muốn làm mỏng nó hơn.

Bước # 14: Thay đổi độ rộng khoảng trống của chuỗi dữ liệu trên trục chính.

Sau đó, nhấp vào cạnh của ống màu đỏ một lần nữa để chọn Dòng 2 “Công thức Tổng doanh thu” (E3), được đặt trên trục chính. Đặt giống nhau Chiều rộng khoảng cách giá trị (250%).

Bước # 15: Thêm bầu nhiệt kế vào biểu đồ.

Như một điều chỉnh cuối cùng, hãy tạo và thêm bầu nhiệt kế vào biểu đồ.

1. Chuyển sang Chèn
2. Chọn "Hình dạng" cái nút.
3. Nhặt "Hình trái xoan.”

Nhấn giữ Sự thay đổi trong khi vẽ để tạo ra một hình tròn hoàn hảo. Sau đó, nhấp chuột phải vào vòng kết nối và trong menu xuất hiện, thay đổi Hình dạng phác thảo và Tô hình dạng màu sắc sang màu đỏ.

Chỉ một điều cuối cùng. Sao chép vòng kết nối bằng cách chọn nó và nhấn Ctrl + C. Sau đó chọn biểu đồ và nhấn Ctrl + V để dán hình tròn vào biểu đồ nhiệt kế. Cuối cùng, thay đổi kích thước và điều chỉnh vị trí của bóng đèn cho phù hợp với hình ảnh.

Vì vậy, bạn có nó. Bây giờ bạn đã sẵn sàng để tạo các biểu đồ nhiệt kế ngoạn mục trong Excel!