Tổng hợp dữ liệu từ nhiều sheet trong Google Sheet

Bài viết này sẽ hướng dẫn các bạn cách tổng hợp dữ liệu từ nhiều Sheet thành một.

Tham chiếu các ô trong Google Sheet để lấy dữ liệu từ một tab khác

Bạn có thể kéo toàn bộ bảng vào một file bằng cách tham chiếu các ô có dữ liệu từ các sheet khác. Điều này sẽ thực hiện nếu bạn cần hợp nhất hai hoặc nhiều sheet trong một Sheet Google. Để hợp nhất nhiều Sheet Google thành một, hãy chuyển ngay sang phương pháp tiếp theo .
Ở ví dụ, dữ liệu nằm rải rác trên các sheet khác nhau: June, July, August.. Và bạn muốn lấy dữ liệu từ July và August sang June để có một bảng bằng cách:

  • Tìm ô trống đầu tiên ngay sau bảng của bạn (June) và đặt con trỏ vào đó.
  • Nhập tham chiếu ô đầu tiên của bạn. Bảng đầu tiên muốn truy xuất bắt đầu từ A2 trong Sheet July. Vì vậy, bạn có thể đặt: =July!A2

Lưu ý: Nếu có khoảng trắng trong tên Sheet của bạn, bạn phải đặt nó trong dấu ngoặc kép như sau:
=’July 2019′!A2

Điều này ngay lập tức sao chép bất cứ thứ gì nằm trong ô đó:

Enter your first cell reference below the table.

Lưu ý: Sử dụng tham chiếu ô tương đối để nó tự thay đổi khi được sao chép sang các ô khác. Nếu không, nó sẽ trả về dữ liệu không chính xác.

  • Đảm bảo rằng ô có tham chiếu được chọn và nhấp vào hình vuông nhỏ màu xanh lam ở góc dưới cùng bên phải của nó. Con trỏ chuột sẽ biến thành một dấu cộng lớn màu đen. Giữ chuột và kéo con trỏ đến nhiều cột bên phải nếu bạn cần để điền vào chúng bằng các bản ghi mới:
Copy cell references over other columns.
  • Chọn toàn bộ hàng mới này, nhấp vào hình vuông nhỏ màu xanh lam đó một lần nữa, giữ và kéo chuột của bạn xuống, lần này để lấp đầy toàn bộ các hàng với tham chiếu ô và đưa dữ liệu mới từ một sheet khác và mang dữ liệu từ một sheet khác với các tham chiếu ô.
Bring data from another sheet with cell references.

Mặc dù đây có lẽ là cách đầu tiên bạn có thể nghĩ đến để lấy dữ liệu từ một tab khác, nhưng nó không phải là cách nhanh chóng và thanh lịch nhất. May mắn thay, Google đã chuẩn bị các công cụ khác đặc biệt cho mục đích này.

Sao chép các tab vào một Sheet

Một trong những cách tiêu chuẩn là sao chép các tab quan tâm vào một sheet:

  • Mở file chứa các sheet bạn muốn chuyển.
  • Nhấp chuột phải vào tab đầu tiên bạn cần xuất và chọn Copy to > Existing spreadsheet:
Copy the tab into an existing spreadsheet or a new spreadsheet.
  • Điều tiếp theo bạn sẽ thấy là cửa sổ bật lên mời bạn chọn Sheet. Duyệt tìm nó, nhấp vào để đánh dấu và nhấn Select khi bạn đã sẵn sàng:
Locate the spreadsheet to import data into.
  • Khi Sheet được sao chép, bạn sẽ nhận được thông báo xác nhận tương ứng:
A message confirming the sheet has been exported.

Bạn có thể nhấn OK và tiếp tục với Sheet hiện tại hoặc nhấp vào liên kết có tên là Open spreadsheet. Nó sẽ ngay lập tức đưa bạn đến một Sheet khác với Sheet đầu tiên đã có ở đó:

The tab is copied to another spreadsheet.

Export/import sheets
Một cách khác để nhập dữ liệu từ nhiều Google Sheet là xuất từng sheet trước, sau đó nhập tất cả chúng vào một file cần thiết:

  • Mở sheet chứa sheet mà bạn muốn lấy dữ liệu từ đó.
  • Làm cho sheet quan tâm hoạt động bằng cách chọn nó.
  • Đi tới File > Download > Comma-separated values (.csv, current sheet):
Download the sheet as .csv

File sẽ được tải xuống máy tính của bạn.

Sau đó, mở một Sheet khác – Sheet mà bạn muốn thêm Sheet vào.
Lần này, chọn File > Import  từ menu và chuyển đến tab Upload trong cửa sổ Import file:

Upload the sheet you downloaded earlier.

Nhấn Select a file from your device và tìm sheet bạn vừa tải xuống.

  • Sau khi file được tải lên, bạn sẽ thấy một cửa sổ với các tùy chọn bổ sung để nhập Sheet. Để thêm nội dung của Sheet khác sau bảng hiện có của bạn, hãy chọn Append to current sheet:
Adjust the options to import sheet.

Trong số các cài đặt khác, vui lòng chỉ định dấu phân tách và chuyển đổi văn bản thành số, ngày tháng và công thức.

  • Kết quả là, bạn sẽ nhận được hai Sheet được hợp nhất. Trong đó, một bảng dưới một bảng khác: Bảng thứ hai đã được thêm vào bảng đầu tiên.
The second table has been appended to the first one.

Nhưng vì đây là file .csv mà bạn cần nhập, bảng thứ hai vẫn được format theo cách chuẩn. Bạn sẽ phải dành một chút thời gian để format nó khi bạn cần.

Google Sheet có chức năng kết hợp dữ liệu từ nhiều Sheet

Tất nhiên, sẽ không phải là Google nếu nó không có chức năng hợp nhất dữ liệu trong Google Sheet.
Như tên của hàm cho thấy, IMPORTRANGE nhập dữ liệu từ nhiều sheet của Google vào một sheet. Chức năng này giúp Google Sheet lấy dữ liệu từ một tài liệu khác cũng như từ các tab khác từ cùng một file.
Đây là những gì hàm yêu cầu:

=IMPORTRANGE(spreadsheet_url, range_string)

Spreadheet_url: không gì khác ngoài liên kết đến Sheet từ nơi bạn cần lấy dữ liệu. Nó phải luôn được đặt giữa dấu ngoặc kép.

range_string: là viết tắt của những ô cụ thể mà bạn cần đưa vào Sheet hiện tại của mình.
Và đây là mẫu được làm theo để nhập dữ liệu từ nhiều Google Sheet bằng IMPORTRANGE:

  • Mở sheet mà bạn muốn lấy dữ liệu.

Đảm bảo rằng bạn có ít nhất quyền truy cập xem file đó.

  • Nhấp vào thanh URL của trình duyệt và sao chép liên kết đến file này ngay cho đến khi có dấu thăng (#):
Copy the link to the spreadsheet of interest.

Bạn sẽ cần URL này ngay cả khi bạn định kết hợp các sheet từ cùng một file.
Mặc dù Google cho biết chức năng này yêu cầu toàn bộ URL, bạn có thể dễ dàng truy cập bằng một khóa – một phần của URL giữa /d/ và /edit :
…google.com/spreadsheets/d/1pNqFucLhKIf3PYEp5JhBsYb7jDdf6h1Tca9ar4Hynks/edit

Quay lại Sheet mà bạn muốn thêm thông tin, nhập IMPORTRANGE vào vị trí bảng sẽ xuất hiện và chèn liên kết làm đối số đầu tiên. Sau đó, tách nó khỏi phần tiếp theo bằng dấu phẩy:


Paste the URL into the formula.

Hãy nhớ rằng liên kết phải được bao quanh bởi dấu ngoặc kép.

  • Đối với phần thứ hai của công thức, hãy nhập tên của Sheet và phạm vi chính xác mà bạn muốn kéo. Xác nhận bằng cách nhấn Enter.

Cũng bao gồm đối số thứ hai trong dấu ngoặc kép:
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1pNqFucLhKIf5PYEp2JhBsYb5jDdf6h1Tca9ar4Hynks/edit”,”May!A2:D5″)

  • Mặc dù bây giờ công thức đã sẵn sàng, nhưng nó sẽ trả về lỗi #REF ngay từ đầu. Đó là bởi vì lần đầu tiên bạn cố gắng lấy dữ liệu từ một số Sheet, IMPORTRANGE sẽ yêu cầu quyền truy cập vào Sheet đó. Khi quyền được cấp, bạn sẽ dễ dàng nhập các bản ghi từ các Sheet khác của file đó.

Nhấp vào ô có lỗi và nhấn vào dấu nhắc Allow access màu xanh lam đó :

Connect the sheets to combine them.

Bằng cách cho phép truy cập, bạn cho sheet biết rằng bạn không ngại bất kỳ cộng tác viên hiện có hoặc tiềm năng nào trên Sheet này đang truy cập dữ liệu từ file khác.

  • Khi công thức kết nối với sheet khác, nó sẽ nhập dữ liệu từ đó:

Hàm IMPORTRANGE không kéo format của các ô, chỉ kéo các giá trị. Bạn sẽ cần phải áp dụng format theo cách thủ công sau đó.

Import data from multiple Google Sheets with IMPORTRANGE.

Nếu các bảng khá lớn, chỉ cần cho phép một thời gian để công thức kéo tất cả các bản ghi.
Các bản ghi do hàm trả về sẽ được cập nhật tự động nếu bạn thay đổi chúng trong file gốc.
Vui lòng kiểm tra thêm một số chức năng để lấy dữ liệu từ Web .

Google Sheet QUERY để nhập phạm vi từ nhiều Sheet

Hàm QUERY rất linh hoạt có thể được sử dụng trong Sheet Google để kết hợp dữ liệu từ nhiều Sheet.

Trong ví dụ hợp nhất ba Sheet khác nhau của Google: Winter 2019, Spring 2019, và Summer 2019. Chúng chứa tên của tất cả nhân viên xuất xuất nhất trong công việc trong các tháng khác nhau.

Chuyển đến Sheet đầu tiên – Winter 2019 – và sử dụng hàm QUERY ngay dưới bảng hiện có:

=QUERY({‘Spring 2019′!A2:D7;’Summer 2019′!A2:D7},”select * where Col1 <>””)mer 2019’!A2:D7},”select * where Col1 <>””)

Trong đó:

{‘Spring 2019′!A2:D7;’Summer 2019’!A2:D7} – là tất cả các Sheet và phạm vi cần nhập.
Các Sheet phải được viết giữa các dấu ngoặc nhọn. Nếu tên của họ chứa khoảng trắng, hãy sử dụng dấu ngoặc kép để liệt kê tên.

Tách các phạm vi bằng dấu chấm phẩy để lấy dữ liệu từ các tab khác nhau xuống dưới một tab khác. Sử dụng dấu phẩy để thay thế chúng được nhập cạnh nhau.

Hãy sử dụng dãy vô hạn như A2: D .

select * where Col1 <>” – Yêu cầu công thức chỉ nhập tất cả các bản ghi (select *) nếu các ô trong cột đầu tiên của bảng (trong đó Col1) không trống (<> ”). Bạn có thể sử dụng một cặp dấu ngoặc kép để chỉ ra những chỗ trống.

Sử dụng <>” vì cột chứa văn bản. Nếu cột của bạn chứa kiểu dữ liệu khác (ví dụ: ngày hoặc giờ,…), bạn cần sử dụng is not null thay vào đó: “select *, trong đó “select * where Col1 is not null”

Kết quả là, hai bảng từ các trang khác đã được hợp nhất thành một bảng dưới một bảng khác:

Google Sheets QUERY – import ranges from multiple sheets.

Nếu bạn muốn sử dụng Google Sheets QUERY để nhập phạm vi từ nhiều Sheet, bạn sẽ phải triển khai hàm IMPORTRANGE. Đây là công thức để lấy dữ liệu của bạn từ các tài liệu khác:
=QUERY({IMPORTRANGE(“1pNqFucLhKIf5PYEp2JhBsYb5jDdf6h1Tca9ar4Hynks”,”Mar-Apr-May!A2:D6″);IMPORTRANGE(“1ie8-uTlIAzahJZHSlhMGLSW_xA6ZBqNmt1I0ADo4N4M”,”Jun-Jul-Aug!A2:D4″)},”select * where Col1<>””)

Bạn sử dụng các khóa từ URL thay vì toàn bộ liên kết trong công thức đủ dài này.

2 cách nhanh nhất để hợp nhất nhiều Sheet của Google

Kết hợp tiện ích bổ sung Sheet

Tiện ích bổ sung đặc biệt đầu tiên Kết hợp Sheet được thiết kế với một mục đích duy nhất là nhập dữ liệu từ nhiều Sheet của Google. Nó đủ thông minh để nhận ra các cột giống nhau trong các Sheet khác nhau và tập hợp dữ liệu lại với nhau cho phù hợp nếu bạn cần.

Combine Sheets working scheme.

Tất cả những gì bạn phải làm là:

  • Chọn Sheet hoặc toàn bộ Sheet để hợp nhất và chỉ định phạm vi nếu cần. Khả năng thực hiện tìm kiếm nhanh trong Drive giúp việc này thậm chí còn nhanh hơn.
  • Chọn cách lấy dữ liệu:

Như một công thức. Đánh dấu hộp kiểm có tên Use a formula to combine sheets nếu bạn muốn có một sheet chính sẽ tự động thay đổi dựa trên nội dung ban đầu của bạn. Mặc dù bạn sẽ không thể chỉnh sửa bảng kết quả, nhưng công thức của nó sẽ luôn được liên kết với các sheet nguồn: chỉnh sửa một ô hoặc thêm hay xóa toàn bộ các hàng ở đó và sheet chính sẽ được thay đổi tương ứng.

Như các giá trị. Nếu việc chỉnh sửa bảng kết quả theo cách thủ công là quan trọng hơn, hãy bỏ qua tùy chọn trên và tất cả dữ liệu sẽ được kết hợp dưới dạng giá trị.
Các tùy chọn bổ sung có ở đây để tinh chỉnh:

Nối các bản ghi từ các cột giống nhau thành một cột

Giữ nguyên format

Thêm một dòng trống giữa các phạm vi khác nhau để nhận thấy chúng ngay lập tức

  • Quyết định vị trí đặt bảng đã hợp nhất: Sheet mới, Sheet mới hoặc ở vị trí bạn chọn.

Dưới đây là minh họa nhanh về cách kết hợp ba bảng nhỏ của mình với tiện ích bổ sung:

Import data from multiple Google sheets into one with Combine Sheets.

Tất nhiên, các bảng của bạn có thể lớn hơn nhiều và bạn có thể hợp nhất nhiều Sheet khác nhau miễn là Sheet kết quả không vượt quá giới hạn 5M cho các ô.

Tiện ích bổ sung hợp nhất Sheet

Hợp nhất Sheet là một bổ sung tương đối mới cho các tiện ích bổ sung. Sự khác biệt chính của nó so với công cụ đã nói ở trên là khả năng thêm dữ liệu trong các cột trong Google Sheet (hoặc hàng hoặc ô đơn, cho vấn đề đó).

Hợp nhất Sheet cũng nhận ra các tiêu đề chung trong tất cả các Sheet của Google để tham gia, ngay cả khi chúng nằm ở cột ngoài cùng bên trái hoặc hàng đầu tiên. Luôn có một tùy chọn để hợp nhất các Sheet của Google và tính toán các ô dựa trên vị trí của chúng trong bảng.

  • Chọn sheet để hợp nhất. Nhập thêm file từ Drive nếu cần ngay từ tiện ích bổ sung.
  • Chọn chức năng để hợp nhất trong Google Sheet.
  • Chọn cách thêm các ô trong Google Sheet: theo nhãn (nhãn tiêu đề, nhãn cột bên trái hoặc cả hai) hoặc vị trí.
  • Quyết định vị trí đặt dữ liệu tổng hợp: sheet mới hoặc bất kỳ vị trí cụ thể nào trong file đã mở.
Google Sheets: pull data from another document and calcualte at the same time.

Xem thêm về:

Chia sẻ suy nghĩ, quan điểm của bạn

Leave a reply

Tìm kiếm
Sao Hải Vương
Logo