Hướng dẫn trình bày các cách khác nhau để kết hợp các tờ trong Excel tùy thuộc vào kết quả của bạn sau - hợp nhất dữ liệu từ nhiều bảng tính, kết hợp một số trang bằng cách sao chép dữ liệu của họ hoặc hợp nhất hai bảng tính Excel thành một theo cột chính.
Hôm nay chúng tôi sẽ giải quyết một vấn đề mà nhiều người dùng Excel đang phải vật lộn với hàng ngày - làm thế nào để hợp nhất nhiều tờ Excel thành một mà không sao chép và dán. Hướng dẫn bao gồm hai kịch bản phổ biến nhất: củng cố dữ liệu số [tổng, đếm, v.v.] và các bảng hợp nhất [tức là sao chép dữ liệu từ nhiều bảng tính thành một].consolidating numeric data [sum, count, etc.] and merging sheets [i.e. copying data from multiple worksheets into one].
Hợp nhất dữ liệu từ nhiều bảng tính trong một bảng tính
Cách nhanh nhất để hợp nhất dữ liệu trong Excel [nằm trong một sổ làm việc hoặc nhiều sổ làm việc] là bằng cách sử dụng tính năng Excelconsolidate tích hợp.Excel Consolidate feature.
Hãy xem xét ví dụ sau. Giả sử bạn có một số báo cáo từ các văn phòng khu vực công ty của bạn và bạn muốn hợp nhất các số liệu đó vào một bảng tính chính để bạn có một báo cáo tóm tắt với tổng doanh số của tất cả các sản phẩm.
Như bạn thấy trong ảnh chụp màn hình bên dưới, ba bảng tính được hợp nhất có cấu trúc dữ liệu tương tự, nhưng số lượng hàng và cột khác nhau:
Để hợp nhất dữ liệu trong một bảng tính duy nhất, hãy thực hiện các bước sau:
- Sắp xếp dữ liệu nguồn đúng cách. Để tính năng củng cố excel hoạt động chính xác, hãy đảm bảo rằng: For the Excel Consolidate feature to work correctly, make sure
that:
- Mỗi phạm vi [bộ dữ liệu] bạn muốn hợp nhất cư trú trên một bảng tính riêng biệt. Đừng đặt bất kỳ dữ liệu nào trên trang mà bạn có kế hoạch xuất dữ liệu hợp nhất.
- Mỗi tờ có cùng một bố cục và mỗi cột có một tiêu đề và chứa dữ liệu tương tự.
- Không có hàng hoặc cột trống trong bất kỳ danh sách nào.
- Chạy excel củng cố. Trong bảng tính chính, nhấp vào ô trên bên trái nơi bạn muốn dữ liệu hợp nhất xuất hiện, hãy chuyển đến tab Dữ liệu và nhấp vào củng cố.tip. Bạn nên củng cố dữ liệu vào một tờ trống. Nếu bảng tính chính của bạn đã có một số dữ liệu, hãy đảm bảo có đủ không gian [hàng trống và cột] để chứa dữ liệu được hợp nhất. In the master worksheet, click the upper-left cell where you want the consolidated data to appear, go to the Data
tab and click Consolidate.
Tip. It's is advisable to consolidate data into an empty sheet. If your master worksheet already has some data, make sure there is enough space [blank rows and columns] to contain the merged data.
- Định cấu hình cài đặt hợp nhất. Các cửa sổ Hộp thoại Hợp nhất xuất hiện và bạn làm như sau: The Consolidate dialog windows appears and you do the following:
- Trong hộp chức năng, chọn một trong các hàm tóm tắt bạn muốn sử dụng để hợp nhất dữ liệu của mình [đếm, trung bình, tối đa, tối thiểu, v.v.]. Trong ví dụ này, chúng tôi chọn tổng.
- Trong hộp tham chiếu, nhấp vào biểu tượng hộp thoại sụp đổ và chọn phạm vi trên bảng tính đầu tiên. Sau đó nhấp vào nút Thêm để có phạm vi đó được thêm vào tất cả các tài liệu tham khảo Lặp lại bước này cho tất cả các phạm vi bạn muốn hợp nhất.and select the range on the first worksheet. Then click the Add button to have that range added to the All references Repeat this step for all the ranges you want to consolidate.
Nếu một hoặc một số tờ nằm trong một sổ làm việc khác, hãy nhấp vào đáy duyệt để xác định vị trí của sổ làm việc.
- Định cấu hình cài đặt cập nhật. Trong cùng một cửa sổ hộp thoại hợp nhất, chọn bất kỳ tùy chọn nào sau đây:. In the same Consolidate dialog window, select any of the following options:
- Kiểm tra hàng trên cùng và/hoặc các hộp cột bên trái trong nhãn sử dụng nếu bạn muốn các nhãn hàng và/hoặc cột của các phạm vi nguồn được sao chép để hợp nhất.Top row and/or Left column boxes under Use labels if you want the row and/or column labels of the source ranges to be copied to the consolidation.
- Chọn hộp liên kết tạo đến dữ liệu nguồn nếu bạn muốn dữ liệu hợp nhất tự động cập nhật bất cứ khi nào dữ liệu nguồn thay đổi. Trong trường hợp này, Excel sẽ tạo các liên kết đến bảng tính nguồn của bạn cũng như một phác thảo như trong ảnh chụp màn hình sau.Create links to source data box if you want the consolidated data to update automatically whenever the source data changes. In this case, Excel will create links to your source worksheets as well as an outline like in the following screenshot.
Nếu bạn mở rộng một số nhóm [bằng cách nhấp vào ký hiệu phác thảo cộng], sau đó nhấp vào ô với một giá trị nhất định, một liên kết đến dữ liệu nguồn sẽ hiển thị trong thanh công thức.
Như bạn thấy, tính năng củng cố Excel rất hữu ích để kết hợp dữ liệu từ một số bảng tính. Tuy nhiên, nó có một vài hạn chế. Cụ thể, nó chỉ hoạt động cho các giá trị số và nó luôn tóm tắt các số đó theo cách này hay cách khác [tổng, đếm, trung bình, v.v.]numeric values only and it always summarizes those numbers in one way or another [sum, count, average, etc.]
Nếu bạn muốn hợp nhất các tờ trong Excel bằng cách sao chép dữ liệu của họ, tùy chọn hợp nhất không phải là cách để đi. Để kết hợp chỉ một vài tờ, bạn có thể không cần bất cứ thứ gì khác ngoài bản sao/dán cũ tốt. Nhưng nếu bạn phải hợp nhất hàng chục tờ, các lỗi với việc sao chép/dán thủ công là không thể tránh khỏi. Trong trường hợp này, bạn có thể muốn sử dụng một trong các kỹ thuật sau đây để tự động hóa việc hợp nhất.copying their data, the consolidation option is not the way to go. To combine just a couple of sheets, you may not need anything else but the good old copy/paste. But if you are to merge tens of sheets, errors with manual copying/pasting are inevitable. In this case, you may want to employ one of the following techniques to automate the merge.
Làm thế nào để hợp nhất các tấm excel thành một
Nhìn chung, có bốn cách để hợp nhất bảng tính Excel thành một mà không cần sao chép và dán:
- Kết hợp các tấm với bộ cuối cùng
- Kịch bản VB
- Truy vấn sức mạnh cho Excel
- Hợp nhất hai tấm excel thành một bởi [các] cột chính
Cách kết hợp bảng tính Excel với Ultimate Suite
Tính năng củng cố excel tích hợp có thể tóm tắt dữ liệu từ các bảng khác nhau, nhưng nó không thể kết hợp các tờ bằng cách sao chép dữ liệu của họ. Đối với điều này, bạn có thể sử dụng một trong các công cụ hợp nhất & kết hợp bao gồm với bộ cuối cùng của chúng tôi cho Excel.
Kết hợp nhiều bảng tính thành một với các bản sao
Giả sử bạn có một vài bảng tính chứa thông tin về các sản phẩm khác nhau và bây giờ bạn cần hợp nhất các tờ này thành một bảng tính tóm tắt, như thế này:
Với các bản sao được thêm vào ruy băng của bạn, 3 bước đơn giản là tất cả những gì cần thiết để hợp nhất các tờ được chọn thành một.
- Bắt đầu các thuật sĩ bản sao.
Trên Ribbon Excel, đi đến tab ABLEBITS, Merge Group, nhấp vào Sao chép tờ và chọn một trong các tùy chọn sau:Copy Sheets, and choose one of the following options:
- Sao chép các tờ trong mỗi sổ làm việc vào một tờ và đặt các tờ kết quả vào một sổ làm việc.
- Hợp nhất các tờ có tên giống hệt nhau thành một.
- Sao chép các tờ được chọn vào một sổ làm việc.
- Kết hợp dữ liệu từ các tờ được chọn với một tờ.
Vì chúng tôi đang tìm cách kết hợp một số tờ bằng cách sao chép dữ liệu của họ, chúng tôi chọn tùy chọn cuối cùng:
- Chọn bảng tính và, tùy chọn, phạm vi hợp nhất.
Trình hướng dẫn bản sao hiển thị một danh sách tất cả các tờ trong tất cả các sổ làm việc mở. Chọn bảng tính bạn muốn kết hợp và nhấp vào Tiếp theo.
Nếu bạn không muốn sao chép toàn bộ nội dung của một bảng tính nhất định, hãy sử dụng biểu tượng hộp thoại sụp đổ để chọn phạm vi mong muốn như trong ảnh chụp màn hình bên dưới.
Trong ví dụ này, chúng tôi đang hợp nhất ba tờ đầu tiên:
Mẹo. Nếu bảng tính bạn muốn hợp nhất cư trú trong một sổ làm việc khác hiện đang đóng, hãy nhấp vào nút Thêm tệp ... để duyệt cho sổ làm việc đó. If the worksheets you want to merge reside in another workbook that is currently closed, click the Add files... button to browse for that workbook.
- Chọn cách hợp nhất các tấm.
Trong bước này, bạn phải định cấu hình các cài đặt bổ sung để bảng tính của bạn sẽ được kết hợp chính xác theo cách bạn muốn.
Cách dán dữ liệu:
- Dán tất cả - Sao chép tất cả các dữ liệu [giá trị và công thức]. Trong hầu hết các trường hợp, nó là tùy chọn để chọn.
- Chỉ có giá trị dán - Nếu bạn không muốn các công thức từ các tờ ban đầu được dán vào bảng tính tóm tắt, hãy chọn tùy chọn này.
- Tạo liên kết đến dữ liệu nguồn - Điều này sẽ liên kết các công thức liên kết dữ liệu được hợp nhất với dữ liệu nguồn. Chọn tùy chọn này nếu bạn muốn dữ liệu được hợp nhất tự động cập nhật bất cứ khi nào bất kỳ dữ liệu nguồn nào thay đổi. Nó hoạt động tương tự như các liên kết tạo đến tùy chọn dữ liệu nguồn của Excel Hợp nhất.
Cách sắp xếp dữ liệu:
- Đặt các phạm vi sao chép này dưới một phạm vi khác - sắp xếp các phạm vi sao chép theo chiều dọc.
- Đặt các phạm vi được sao chép cạnh nhau - Sắp xếp các phạm vi được sao chép theo chiều ngang.
Cách sao chép dữ liệu:
- Bảo tồn định dạng - Tự giải thích và rất thuận tiện.
- Tách các phạm vi được sao chép bằng một hàng trống - chọn tùy chọn này nếu bạn muốn thêm một hàng trống giữa dữ liệu được sao chép từ các bảng tính khác nhau.
- Sao chép bảng với tiêu đề của họ. Kiểm tra tùy chọn này nếu bạn muốn các tiêu đề bảng được bao gồm trong bảng kết quả.
Ảnh chụp màn hình bên dưới hiển thị các cài đặt mặc định hoạt động tốt cho chúng tôi:
Nhấp vào nút Sao chép và bạn sẽ có thông tin từ ba tờ khác nhau được hợp nhất thành một bảng tính tóm tắt như được hiển thị trong phần đầu của ví dụ này.Copy button, and you will have the information from three different sheets merged into one summary worksheet like shown in the beginning of this example.
Những cách khác để kết hợp các tấm trong Excel
Ngoài các trình hướng dẫn bản sao, bộ cuối cùng cho Excel cung cấp một vài công cụ hợp nhất để xử lý các kịch bản cụ thể hơn.
Ví dụ 1. Hợp nhất các tờ Excel với một thứ tự khác của các cột
Khi bạn đang xử lý các tờ được tạo bởi những người dùng khác nhau, thứ tự của các cột thường khác nhau. Làm thế nào bạn có thể xoay xở được chuyện này? Bạn sẽ sao chép các tấm thủ công hoặc di chuyển các cột trong mỗi tờ? Cũng không! Cam kết công việc với trình hướng dẫn kết hợp của chúng tôi:
Và dữ liệu sẽ được kết hợp hoàn hảo bởi các tiêu đề cột:column headers:
Ví dụ 2. Hợp nhất các cột cụ thể từ nhiều tờ
Nếu bạn có các tấm thực sự lớn với hàng tấn các cột khác nhau, bạn có thể chỉ muốn hợp nhất các bảng quan trọng nhất cho bảng tóm tắt. Chạy Trình hướng dẫn kết hợp bảng tính và chọn các cột có liên quan. Đúng, thật dễ dàng!
Kết quả là, chỉ có dữ liệu từ các cột mà bạn đã chọn vào bảng tóm tắt:
Những ví dụ này chỉ chứng minh một vài công cụ hợp nhất của chúng tôi, nhưng có nhiều hơn nữa! Sau khi thử nghiệm một chút, bạn sẽ thấy tất cả các tính năng hữu ích như thế nào. Phiên bản đánh giá đầy đủ chức năng của Ultimate Suite có sẵn để tải xuống ở cuối bài này.
Hợp nhất các tờ trong Excel bằng mã VBA
Nếu bạn là người dùng Power Excel và cảm thấy thoải mái với macro và VBA, bạn có thể kết hợp nhiều tờ Excel thành một bằng cách sử dụng một số tập lệnh VBA, ví dụ như điều này.
Xin lưu ý rằng để mã VBA hoạt động chính xác, tất cả các bảng tính nguồn phải có cùng cấu trúc, cùng một tiêu đề cột và cùng một thứ tự cột.
Kết hợp dữ liệu từ nhiều bảng tính với truy vấn điện
Truy vấn sức mạnh là một công nghệ rất mạnh mẽ để kết hợp và tinh chỉnh dữ liệu trong Excel. Lúc đó, nó khá phức tạp và đòi hỏi một đường cong học tập dài. Hướng dẫn sau đây giải thích các cách sử dụng phổ biến một cách chi tiết: Kết hợp dữ liệu từ nhiều nguồn dữ liệu [truy vấn nguồn].
Cách hợp nhất hai tấm excel thành một bởi [các] cột chính
Nếu bạn đang tìm kiếm một cách nhanh chóng để phù hợp và hợp nhất dữ liệu từ hai bảng tính, thì bạn có thể sử dụng chức năng VLookup Excel hoặc nắm lấy trình hướng dẫn Hợp nhất Bảng.Cái sau là một công cụ thân thiện với người dùng trực quan cho phép bạn so sánh hai bảng tính Excel với [các] cột chung và kéo dữ liệu phù hợp từ bảng tra cứu.Ảnh chụp màn hình sau đây cho thấy một trong những kết quả có thể.match and merge data from two worksheets, then you can either employ the Excel VLOOKUP function or embrace the Merge Tables Wizard. The latter is a visual user-friendly tool that lets you compare two Excel spreadsheets by a common column[s] and pull matching data from the lookup table. The following screenshot demonstrates one of possible results.
Trình hướng dẫn Hợp nhất cũng được bao gồm trong bộ cuối cùng cho Excel.
Đây là cách bạn củng cố dữ liệu và hợp nhất các tờ trong Excel.Tôi hy vọng bạn sẽ tìm thấy thông tin trong hướng dẫn ngắn này hữu ích.Dù sao, tôi cảm ơn bạn đã đọc và mong được gặp bạn trên blog này vào tuần tới!
Tải xuống có sẵn
Phiên bản hoàn toàn đầy đủ chức năng 14 ngày [Tệp .exe] 14 ngày [Tệp .exe]