Cách làm mẫu nhiệt kế trong Excel

Dùng biểu đồ nhiệt kế trong Excel là lựa chọn tốt để theo dõi các mục tiêu tài chính. Dưới đây là hướng dẫn chi tiết từng bước cách làm biểu đồ nhiệt trong Excel.

Dù đang lên kế hoạch đi nghỉ, chạy marathon hay tiết kiệm tiền để xây dựng ngôi nhà trong mơ, bạn vẫn sẽ muốn theo dõi tiến trình.

Một cách hay để làm việc này là dùng biểu đồ nhiệt kế trong Excel. Nó là một cách đơn giản, hiệu quả trong việc theo dõi mục tiêu tài chính và chia sẻ nó với đội, đối tác hoặc bạn bè.

Thiết lập bảng tính

Trước khi chế tạo nhiệt kế, chúng ta cần thiết lập mục tiêu. Trong trường hợp này, hãy nhìn vào kế hoạch theo dõi quỹ cho chuyến đi của một nhóm.

1. Mở worksheet mới trong Excel.

2. Tạo bảng Excel với 2 cột: một cho Month và một cho Amount Deposited.

3. Bên dưới bảng, hãy chú ý tới các ô: Target, Total và Percentage. Ở đây, chúng ta sẽ tạo các công thức cho nhiệt kế.

4. Tiếp theo tới Target, nhập số lượng mục tiêu trong cột B. Mục tiêu ở đây là 115.000 USD.

5. Bên cạnh Total, viết công thức:

=Sum(B5:B16)

Công thức này hiện số lượng đã thu thập ở cột B.

6. Cuối cùng, chúng ta có thể tính phần trăm bằng:

=B20/B19

Nhập nó trong cột B21.

7. Thay đổi định dạng sang phần trăm bằng cách click chuột phải vào ô đó và chọn Format Cells. Thay đổi thư mục sang Percentage, chọn số vị trí thập phân bạn muốn hiện và nhấn OK.

Tạo biểu đồ nhiệt trong Excel

1. Click tab Insert.

2. Trong phần charts, click insert column or bar chart và chọn 2-D Clustered Column.

Hành động này sẽ tạo một biểu đồ cạnh bảng của bạn.

3. Tiếp theo, thêm bảng vào biểu đồ bằng Select Data. Chọn ô chứa phần trăm của tổng số. Nhấn OK để điền vào biểu đồ.

4. Giờ bạn có thể phân tách biểu đồ. Click chuột phải vào tên biểu đồ và xóa nó. Làm việc tương tự cho tiêu đề cột và các đường nằm ngang.

Lưu ý: Nếu không muốn xóa chúng, bạn có thể ẩn các trục biểu đồ trong Excel.

5. Click đúp vào trục y để mở hộp thoại. Từ đây, bạn có thể thay đổi giới hạn minimum và maximum của biểu đồ sang 0.0 và 1.0 tương ứng. Trước khi đóng menu, chọn Numbers và đổi vị trí thập phân thành 0.

6. Click chuột phải vào cột và chọn Format Data Series. Điều chỉnh Gap Width sang 0.

Hành động này đảm bảo cột của bạn lấp đầy vùng biểu đồ, thay vì cố gắng ẩn ở trong góc. Giờ bạn có thể thu nhỏ biểu đồ sang kích thước giống nhiệt kế.

7. Cuối cùng, quay lại tab Insert, chọn Shapes và tìm hình bầu dục đẹp mắt. Vẽ hình bầu dục và thêm nó vào phía dưới cùng của biểu đồ nhiệt, sau đó chỉnh lại kích thước của khu vực biểu đồ. Nó sẽ vừa khít quanh phần đầu tròn của nhiệt kế, chẳng hạn như:

Lưu ý: Bạn có thể đổi nhiệt kế sang màu đỏ bằng cách click chuột phải vào phần được tô màu và đổi màu lấp đầy.

Thêm chi tiết vào biểu đồ nhiệt trong Excel

Nếu đang theo dõi số lượng tiền lớn trong kế hoạch dài hạn, việc xem lại ngày tăng tiền nhiều nhất có thể hữu ích, nhất là ở những hoạt động từ thiện. Từ đó, bạn có thể phân tích hiệu quả hoạt động của đội.

Đầu tiên, thay đổi bảng Excel. Bạn cần nhiều chi tiết hơn, bao gồm ngày tháng và tên của người quyên góp. Bạn cần tách riêng các cột ngày tháng và số tiền nhận được. Bằng cách này, bạn có thể giám sát từng biến.

Tiếp theo, bạn cần thiết lập Dynamic Named Range. Các phạm vi được đặt tên hữu ích trong việc cung cấp quyền kiểm soát một nhóm ô mà không phải update công thức. Bạn có thể tự động yêu cầu công thức tính cho bất kỳ dữ liệu thêm vào bảng.

Thiết lập Dynamic Named Range

Để làm mọi thứ dễ dàng hơn sau này, hãy biến bảng của bạn thành bảng chính thức. Làm việc này bằng cách chọn toàn bộ vùng của bảng. Select Format as Table, rồi chọn kiểu bạn muốn. Kiểm tra ô cạnh My Table Has Headers và click OK.

Giờ bạn đã có một bảng có thể tìm kiếm với các header. Tiếp theo, chúng ta có thể liên kết bảng với phần tổng số (ô B20).

1. Trong ô tổng số, nhập:

=SUM(Table1[Amount])

Công thức này yêu cầu ô tính cột Amount. Thông tin phần trăm vẫn được giữ nguyên, bằng cách chia tổng theo mục tiêu và vẫn được liên kết với nhiệt kế.

2. Chọn nội dung của cột Amount (C26:C41).

3. Chọn tab Formulas và xác định Name Manager. Click New.

4. Trong box Refers to, bạn sẽ thấy =Table1[Amount]. Bạn cần thêm công thức bên dưới để mỗi lần thêm một giá trị vào cột Amount, tổng số của bạn sẽ tự động tăng lên.

=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1)

Công thức trông sẽ như thế này:

Thêm ngày tháng bằng SUMIFS

SUMIFS là một công thức mạnh mẽ, cho phép so sánh thông tin từ hai nguồn trở lên. Dùng hàm SUMIFS để tính số lượng quyên góp được trong 14 ngày. Kết quả trông sẽ như sau:

1. Nhập ngày bắt đầu (cell G26).

2. Ở ô G27, gõ:

=G26+14

Excel sẽ tự động chèn ngày tháng cho bạn và tiếp tục update nó dựa trên ô G26.

3. G28 sẽ chứa công thức SUMIFS. Trong ô này, gõ:

=SUMIFS($C$26:$C$95,$A$26:$A$95,">="&$G$26,$A$26:$A$95,"<="&$G$27)

4. Bây giờ, ô G28 sẽ biểu thị giá trị của các khoản đóng góp nhận được giữa các ngày bạn chỉ định.

Thế là xong!

Trên đây là cách vẽ biểu đồ nhiệt kế trong Excel. Hi vọng bài viết hữu ích với các bạn.