Hướng dẫn correlation heatmap excel - bản đồ nhiệt tương quan excel

Trong bài viết này, chúng tôi chứng minh cách tạo mối tương quan giữa các biến trong bộ dữ liệu và tạo bản đồ nhiệt từ nó. Quá trình này thường liên quan đến hàng đống dữ liệu được thu thập từ các nơi khác nhau và phân tích chúng để tìm thấy bất kỳ mối quan hệ nào có thể có ích trong việc sử dụng sau này. Phân tích các dữ liệu khổng lồ này cũng được tạo ra cũng tạo ra một bộ dữ liệu khổng lồ chứa kết quả. Việc thấm nhuần những kết quả này là khá khó khăn trong một khoảng thời gian ngắn. Một bản đồ nhiệt của những kết quả này cho một cái nhìn tổng quan vững chắc về những điều này. Cách thực hiện mối tương quan giữa hai bộ dữ liệu và cách tạo bản đồ nhiệt trong Excel được thảo luận ở đây với các ví dụ đầy đủ.

Tải xuống Sổ làm việc thực hành

Tải xuống sách bài tập thực hành này dưới đây.

Quy trình từng bước để tạo bản đồ nhiệt tương quan trong Excel

Chúng tôi sẽ sử dụng bộ dữ liệu dưới đây để tính toán mối tương quan giữa chúng và tạo bản đồ nhiệt của các hệ số tương quan. Bộ dữ liệu này chủ yếu chứa các dấu hiệu của học sinh trong các bài kiểm tra trong các môn học khác nhau. Loại dữ liệu này sẽ giúp chúng tôi tương quan các nhãn hiệu. Điều đó có nghĩa là nó sẽ đánh giá xem sinh viên có thực hiện đồng đều hay không trong tất cả các môn học. Sau đó, chúng tôi sẽ tạo một bản đồ nhiệt động hoặc tĩnh của bộ dữ liệu tương quan này để hiểu rõ hơn.

Bước 1: Kích hoạt công cụ phân tích dữ liệu

Theo mặc định, không có tùy chọn phân tích dữ liệu trực tiếp trong Excel, chúng ta cần kích hoạt đó từ các tùy chọn.Data Analysis option in Excel, We need to enable that from the options.

• Lúc đầu, chúng tôi cần bật lệnh phân tích dữ liệu trong tab Dữ liệu, vì theo mặc định, chúng không được thêm vào Excel.Data Analysis command in the Data tab, because by default they are not added to Excel.
• Nhấp vào tệp trong bảng tính để đi đến các tùy chọn.File in the worksheet to go to the Options.

• Nhấp vào các tùy chọn.Options.

• Một cửa sổ mới sẽ mở, từ cửa sổ đi vào các tiện ích bổ sung, sau đó chọn adcel bổ trợ từ menu thả xuống quản lý. Add-ins, then select Excel Add-in from Manage drop-down menu.
• Nhấp vào đi.Go.

• Một cửa sổ nhỏ mới sẽ mở.
• Đánh dấu vào hộp công cụ phân tích và bấm OK.Analysis ToolPak box and click OK.

Sau khi lệnh phân tích dữ liệu này sẽ có sẵn trong tab Dữ liệu. Data Analysis command will be available in the Data tab.

Bước 2: Sử dụng công cụ phân tích dữ liệu để tạo ma trận tương quan

Khi chúng tôi kích hoạt lệnh phân tích dữ liệu, chúng tôi có thể sử dụng nó để tính toán trực tiếp tương quan.Data Analysis command, we can use it to calculate correlation directly.

• Bây giờ nếu bạn đi đến tab dữ liệu, thì bạn sẽ nhận thấy rằng lệnh phân tích dữ liệu hiện có sẵn.Data tab, then you will notice that the Data Analysis command is available now.

• Nhấp vào lệnh phân tích dữ liệuData Analysis command
• Sau đó, một cửa sổ nhỏ sẽ mở và từ cửa sổ đó chọn tương quan ..Correlation..
• Bấm OK sau này.OK after this.

• Một cửa sổ nhỏ mới sẽ mở.
• Đánh dấu vào hộp công cụ phân tích và bấm OK.
• Sau khi lệnh phân tích dữ liệu này sẽ có sẵn trong tab Dữ liệu.Columns in Grouped By option if you want to group the data by columns.
• Bước 2: Sử dụng công cụ phân tích dữ liệu để tạo ma trận tương quanLabels in the first row if your data has table headers or labels.
• Khi chúng tôi kích hoạt lệnh phân tích dữ liệu, chúng tôi có thể sử dụng nó để tính toán trực tiếp tương quan.Output Options. Here, we want to place these output results in the new worksheet, choose that option, and input that sheet name.
• Bấm OK sau này.OK after this.

• Từ cửa sổ đó, chọn phạm vi đầu vào của bộ dữ liệu của bạn.

Sau đó nhấp vào các cột được nhóm theo tùy chọn nếu bạn muốn nhóm dữ liệu theo các cột.

Đánh dấu vào nhãn trong hàng đầu tiên nếu dữ liệu của bạn có tiêu đề hoặc nhãn bảng.

Chọn nơi bạn muốn đặt đầu ra của mình vào các tùy chọn đầu ra. Ở đây, chúng tôi muốn đặt các kết quả đầu ra này trong bảng tính mới, chọn tùy chọn đó và nhập tên trang đó.

• Tiếp theo, bạn sẽ thấy một bảng tính mới được mở và có bộ dữ liệu của chúng tôi với giá trị tương quan của chúng với mỗi giá trị ô có mặt.Conditional Formatting from the Home tab in the Styles group.
• Bây giờ chúng tôi thực hiện mối tương quan giữa các biến trong Excel và bây giờ chúng tôi áp dụng định dạng có điều kiện cho nó để tạo bản đồ nhiệt.New Rule.

• Bước 3: Định dạng có điều kiện để tạo bản đồ nhiệt tương quanFormat all cells based on their values in Select a Rule Type.
• Chúng tôi sẽ áp dụng định dạng có điều kiện cho các giá trị tương quan để trực quan hóa chúng theo giá trị ô của chúng.Format Style from 2 to 3.

• Để áp dụng định dạng có điều kiện, nhấp vào định dạng có điều kiện từ tab Home trong nhóm Styles.Number.
• Một menu thả xuống sẽ xuất hiện, từ menu đó. Nhấp vào quy tắc mới.Values -1.
• Sau khi nhấp vào quy tắc mới, một cửa sổ mới sẽ mở, trong cửa sổ đó, chọn định dạng tất cả các ô dựa trên các giá trị của chúng trong chọn loại quy tắc.Red.
• Để áp dụng định dạng có điều kiện, nhấp vào định dạng có điều kiện từ tab Home trong nhóm Styles.Number.
• Một menu thả xuống sẽ xuất hiện, từ menu đó. Nhấp vào quy tắc mới.Values.
• Sau khi nhấp vào quy tắc mới, một cửa sổ mới sẽ mở, trong cửa sổ đó, chọn định dạng tất cả các ô dựa trên các giá trị của chúng trong chọn loại quy tắc.White.
• Để áp dụng định dạng có điều kiện, nhấp vào định dạng có điều kiện từ tab Home trong nhóm Styles.Number.
• Một menu thả xuống sẽ xuất hiện, từ menu đó. Nhấp vào quy tắc mới.Values.
• Sau khi nhấp vào quy tắc mới, một cửa sổ mới sẽ mở, trong cửa sổ đó, chọn định dạng tất cả các ô dựa trên các giá trị của chúng trong chọn loại quy tắc.Blue.
• Bấm OK sau này.OK after this.

• Từ cửa sổ đó, chọn phạm vi đầu vào của bộ dữ liệu của bạn.OK you will notice that the correlated dataset is now arranged as a color-coded heatmap.

Sau đó nhấp vào các cột được nhóm theo tùy chọn nếu bạn muốn nhóm dữ liệu theo các cột.

Đánh dấu vào nhãn trong hàng đầu tiên nếu dữ liệu của bạn có tiêu đề hoặc nhãn bảng.

Chọn nơi bạn muốn đặt đầu ra của mình vào các tùy chọn đầu ra. Ở đây, chúng tôi muốn đặt các kết quả đầu ra này trong bảng tính mới, chọn tùy chọn đó và nhập tên trang đó.

Tiếp theo, bạn sẽ thấy một bảng tính mới được mở và có bộ dữ liệu của chúng tôi với giá trị tương quan của chúng với mỗi giá trị ô có mặt.E4 and E9 exhibit this type of negative relation

Và đó là cách mà bạn có thể tạo ra một mối tương quan giữa các biến trong bộ dữ liệu và tạo bản đồ nhiệt từ nó trong Excel.

Cách tạo bản đồ nhiệt tương quan động trong Excel

Trong phương pháp này, chúng tôi sẽ sử dụng một bộ dữ liệu trong đó mối tương quan được thực hiện giữa các biến và sau đó chúng tôi tạo một bản đồ nhiệt động trong Excel với hộp kiểm. Điều đó có thể hiển thị hoặc ẩn định dạng có điều kiện chỉ bằng một lần nhấn trên hộp.

Bước 1: Tạo bộ dữ liệu tương quan

Đầu tiên, chúng ta cần có bộ dữ liệu sẽ được sửa đổi để có bản đồ nhiệt động. Sử dụng bộ dữ liệu tương quan được tạo trong phương thức trước là một cách tốt để bắt đầu.

Bước 2: Thêm hộp kiểm trong bảng tính

Chúng tôi có thể thêm một hộp kiểm bằng tab Nhà phát triển.Developer tab.

• Bây giờ từ tab Nhà phát triển, nhấp vào lệnh chèn.Insert command.
• Từ menu thả xuống, nhấp vào biểu tượng hộp kiểm.Check Box icon.

• Sau đó, bạn sẽ nhận thấy rằng có một hộp kiểm được sinh ra trong bảng tính.Check Box spawned in the worksheet.
• Bạn có thể chỉnh sửa và thay đổi kích thước hộp như bạn mong muốn.

• Chọn hộp và nhấp chuột phải vào chuột. Sẽ có một menu ngữ cảnh. Sau đó nhấp vào tùy chọn điều khiển định dạng.Format Control option.

• Sau khi nhấp vào điều khiển định dạng, một cửa sổ mới sẽ xuất hiện. Từ cửa sổ đó, nhập vị trí của ô sẽ được thích với hộp.Format Control, a new window will appear. From that window, enter the location of the cell which will be liked with the box.
• Bấm OK sau này.OK after this.

• Sau này, bạn sẽ nhận thấy rằng vị trí chúng tôi chỉ chọn, bây giờ có văn bản sai lầm.FALSE”.
• Nhấp vào hộp kiểm sẽ chuyển đổi giá trị của ô L4 giữa đúng và sai.L4 between TRUE and FALSE.

Bước 3: Áp dụng định dạng có điều kiện

Để áp dụng định dạng có điều kiện đúng cách, chúng ta cần sử dụng các chức năng sau bắt đầu bằng & nbsp; nếu, tối thiểu và các hàm tối đa. IF, MIN, and MAX functions.

• Bây giờ chúng tôi có hộp kiểm với một liên kết được gắn vào nó.
• Tiếp theo, chúng tôi sẽ chọn bộ dữ liệu và nhấp vào định dạng có điều kiện trong nhóm kiểu từ nhàConditional Formatting in the Style group from the Home
• Sau đó nhấp vào quy tắc mới.New Rule.

• Sau khi nhấp vào quy tắc mới, một cửa sổ mới sẽ mở, trong cửa sổ đó, chọn định dạng tất cả các ô dựa trên các giá trị của chúng trong chọn loại quy tắc.Format all cells based on their values in Select a Rule Type.
• Bây giờ thay đổi kiểu định dạng từ 2 thành 3.Format Style from 2 to 3.
• Sau đó trong cửa sổ thả xuống tối thiểu. Chọn công thức.Formula.
• Sau đó nhập công thức sau trong trường giá trị (tối thiểu):Value (Minimum) field:

=IF($L$4=TRUE,MIN($C$5:$I$11),FALSE)

• Chọn màu đỏRed
• Sau đó trong cửa sổ thả xuống tối thiểu. Chọn số.Number.
• Sau đó nhập vào các giá trị 0.Values 0.
• Chọn màu, màu trắng.White.
• Sau đó trong cửa sổ thả xuống tối thiểu. Chọn công thức.Formula.
• Sau đó nhập công thức sau trong trường giá trị (tối thiểu):Value (Maximum) field:

=IF($L$4=TRUE,MAX($C$5:$I$61),FALSE)

• Chọn màu đỏ Blue.
• Bấm OK sau này.OK after this.

• Sau này, bạn sẽ nhận thấy rằng vị trí chúng tôi chỉ chọn, bây giờ có văn bản sai lầm.Conditional Formatting Rules Manager, complete with an overview of all rules.
• Nhấp vào hộp kiểm sẽ chuyển đổi giá trị của ô L4 giữa đúng và sai.OK after that.

Bước 3: Áp dụng định dạng có điều kiện

• Để áp dụng định dạng có điều kiện đúng cách, chúng ta cần sử dụng các chức năng sau bắt đầu bằng & nbsp; nếu, tối thiểu và các hàm tối đa.: This formula will return the minimum value in the range of cells $C$5:$I$11.
• Bây giờ chúng tôi có hộp kiểm với một liên kết được gắn vào nó. This formula means that the If the value of L4 is true. Then the minimum value of the range of cell $C$5:$I$11 will be the input value. Otherwise, there will be no value available to input. Hence there will be no formatting.
• Tiếp theo, chúng tôi sẽ chọn bộ dữ liệu và nhấp vào định dạng có điều kiện trong nhóm kiểu từ nhàThis formula will return the minimum value in the range of cells $C$5:$I$11
• Sau đó nhấp vào quy tắc mới.This formula means that the If the value of L4 is true. Then the maximum value of the range of cell $C$5:$I$11 will be the input value. Otherwise, there will be no value available to input. Hence there will be no formatting.

Sau khi nhấp vào quy tắc mới, một cửa sổ mới sẽ mở, trong cửa sổ đó, chọn định dạng tất cả các ô dựa trên các giá trị của chúng trong chọn loại quy tắc.

• Bây giờ thay đổi kiểu định dạng từ 2 thành 3.OK, we will see that the dataset now has Conditional Formatting which is dynamic. When the cell value of cell L4 is TRUE. conditional formatting with the heat map will be visible.

• Sau đó trong cửa sổ thả xuống tối thiểu. Chọn công thức.L4 back which, in turn, will hide the heat map from the conditional formatting. Which is shown in the image below.

Sau đó nhập công thức sau trong trường giá trị (tối thiểu):

Chọn màu đỏ

Sau đó trong cửa sổ thả xuống tối thiểu. Chọn số. Pearson Product Moment Correlation. In other words, your variables may be strongly related in another way, curvilinearly, and still have a correlation coefficient close to or equal to zero or close to zero.

Sau đó nhập vào các giá trị 0.

Chọn màu, màu trắng.Pearson correlation coefficient is used. In this case, it’s better to rank those two variable values and perform the Spearman Correlation Coefficient analysis.

Sự kết luận

Tóm lại, câu hỏi Làm thế nào để tạo bản đồ nhiệt tương quan trong Excel, được trả lời ở đây bằng cách tạo mối tương quan giữa các biến trong tập dữ liệu và sau đó tạo bản đồ nhiệt từ nó.Làm thế nào chúng ta có thể tạo một bản đồ nhiệt động với các hộp kiểm cũng được xây dựng.Hơn nữa, những khó khăn tiềm tàng thường phải đối mặt với các mối tương quan và làm thế nào chúng ta có thể giảm bớt chúng cũng được trình bày.

Đối với vấn đề này, một sổ làm việc được gắn vào nơi bạn có thể thực hành các phương pháp này.

Hãy hỏi bất kỳ câu hỏi hoặc phản hồi thông qua phần bình luận.Bất kỳ đề xuất nào cho sự cải thiện của cộng đồng Exceldemy sẽ được đánh giá caoExceldemy community will be highly appreciable