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

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

2158 Views

Bạn đang đọc: Tổng hợp dữ liệu từ nhiều sheet trong Google Sheet – BigTOP

Bài viết này sẽ hướng dẫn những 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 kỳ thứ gì nằm trong ô đó :

Lưu ý : Sử dụng tham chiếu ô tương đối để nó tự đổi khác khi được sao chép sang những ô khác. Nếu không, nó sẽ trả về dữ liệu không đúng mự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:

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 ô.

Mặc dù đây có lẽ rằng là cách tiên phong bạn hoàn toà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 gọn và lịch sự nhất. May mắn thay, Google đã sẵn sàng chuẩn bị những công cụ khác đặc biệt quan trọng cho mục tiêu 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 những tab chăm sóc 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:

Đ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:

Khi Sheet được sao chép, bạn sẽ nhận được thông báo xác nhận tương ứng:

Bạn hoàn toàn có thể nhấn OK và liên tục với Sheet hiện tại hoặc nhấp vào link 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 tiên phong đã có ở đó :

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):

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:

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:

Trong số những thiết lập khác, vui mừng chỉ định dấu phân tách và quy đổ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.

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 ít thời hạn để 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 link đế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ối thiểu quyền truy vấn 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 (#):

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:

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

Xem thêm: Tìm việc Làm Giám đốc Đầu tư và Phát triển Dự án Tuyển Dụng 19/04/2023 | https://thomaygiat.com

Đố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 đó :

Bằng cách được cho phép truy vấn, 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 vấn 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 những ô, chỉ kéo những giá trị. Bạn sẽ cần phải vận dụng format theo cách bằng tay thủ công sau đó .

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 động hoàn toàn có thể được sử dụng trong Sheet Google để tích 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 toàn bộ nhân viên cấp dưới xuất xuất nhất trong việc làm trong những tháng khác nhau .
Chuyển đến Sheet tiên phong – 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 những khoanh vùng phạm vi bằng dấu chấm phẩy để lấy dữ liệu từ những tab khác nhau xuống dưới một tab khác. Sử dụng dấu phẩy để thay thế sửa chữa 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ừ những trang khác đã được hợp nhất thành một bảng dưới một bảng khác :

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 những khóa từ URL thay vì hàng loạt link 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ổ trợ đặc biệt quan trọng tiên phong Kết hợp Sheet được phong cách thiết kế với một mục tiêu duy nhất là nhập dữ liệu từ nhiều Sheet của Google. Nó đủ mưu trí để nhận ra những cột giống nhau trong những Sheet khác nhau và tập hợp dữ liệu lại với nhau cho tương thích nếu bạn cần .

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 những bản ghi từ những 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 những khoanh vùng 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 tích hợp ba bảng nhỏ của mình với tiện ích bổ trợ :

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

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 đề đó).

Xem thêm: Tìm việc Làm Giám đốc Đầu tư và Phát triển Dự án Tuyển Dụng 19/04/2023 | https://thomaygiat.com

Hợp nhất Sheet cũng nhận ra những tiêu đề chung trong toàn bộ những 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 tiên phong. Luôn có một tùy chọn để hợp nhất những Sheet của Google và giám sát những ô 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ở.