Bài viết dưới đây hướng dẫn chi tiết cách tổng hợp dữ liệu từ nhiều Sheet trong Excel.
Ví dụ: Tổng hợp doanh thu 3 quý của một cửa hàng bán phần mềm trong năm 2015. Dữ liệu trong 3 quý theo thứ trong Sheet là Quý 1, Quý 2, Quý 3. Tổng hợp doanh thu của các sản phẩm vào Sheet Tổng hợp.
Gợi ý cách làm: Sử dụng hàm Vlookup để tổng hợp dữ liệu từ các Sheet. Do các mặt hàng bán trong mỗi quý là khác nhau nên sử dụng thêm hàm IF và ISNA để kiểm tra. Nếu tên mặt hàng tồn tại trong quý 1 thì mới tiến hành cộng doanh thu. Điều này giúp các bạn tránh lỗi N/a.
Bước 1: Tạo 1 sheet đặt tên là Tổng hợp -> Tạo danh sách tất cả các sản phẩm đã bán trong 3 Quý.
Bước 2: Tại ô E4 nhập câu lệnh sau:
Bước 3: Nhập giá trị bảng chứa giá trị tìm kiếm của hàm Vlookup trong quý 1.
- Di chuyển sang Sheet Quy 1 -> lựa chọn vùng dữ liệu chứa tên mặt hàng và dữ liệu cần tổng hợp.
- Nhấn Enter -> hộp thoại xuất hiện -> OK để quay lại Sheet Tổng hợp. Kết quả:
Chú ý: Do bảng tìm kiếm nằm trên Sheet khác nên có tên Sheet trước địa chỉ ô dữ liệu.
Bước 4: Chuyển địa chỉ ô dữ liệu thành địa chỉ tuyệt đối.
- Lựa chọn địa chỉ -> F4 -> Địa chỉ chuyển dạng tuyệt đối.
Bước 5: Nếu Quý 1 chứa giá trị cần tìm kiếm -> sử dụng hàm Vlookup lấy doanh thu của giá trị cần tìm kiếm trong quý 1 -> có câu lệnh sau:
Bước 6: Thao tác tương tự với quý 2, 3 đồng thời tính tổng 3 quý -> câu lệnh tổng hợp:
Cách nhập câu lệnh:
- Mỗi quý sẽ có 1 lệnh trùng nhau, bạn nên sử dụng lệnh Copy.
IF[ISNA[VLOOKUP[D5,'Quy 1'!$D$4:$E$8,2,0]],0,[VLOOKUP[D5,'Quy 1'!$D$4:$E$8,2,0]]: Sử dụng hàm ISNA kiểm tra giá trị nếu có giá trị trả về là giá trị của mặt hàng đó.
Bước 7: Sau khi nhập xong lệnh bạn nhấn Enter -> Copy công thức cho các mặt hàng còn lại -> Kết quả:
Chúc các bạn thành công!
Ngày nay, việc quản lý dữ liệu thường được thực hiện qua các phần mềm. Một trong những cách quản lý đó là qua excel. Đây được coi là phần mềm dùng để tính toán hay quản lý dữ liệu rất hữu ích. Tùy vào công việc, người ta thường lưu về các sheet khác nhau để tránh sự nhầm lẫn. Nhiều người muốn quản lý dễ dàng , vậy có cách nào để tổng hợp dữ liệu từ nhiều sheet trong excel có cấu trúc giống nhau? Nếu bạn là chuyên gia excel thì công việc này có vẻ đơn giản. Nhưng đối với người mới làm quen thì việc này thực sự không phải dễ. Bài viết dưới đây giúp bạn để giải quyết vấn đề này nhé!
Vì sao cần tổng hợp dữ liệu từ nhiều sheet trong excel có cấu trúc giống nhau?
Như các bạn cũng biết, trong quá trình phân chia các công việc khác nhau. Người ta thường phân chia theo hàng và cột để tránh những nhầm lẫn khi lập bảng theo dõi. Excel được xem như công cụ phổ biến để cho các công việc văn phòng quản lý nhiều công việc khác nhau. Đặc biệt, đối với nhân viên kế toán thì excel được coi như người bạn vô cùng thân thiết trong công việc.
Tuy nhiên, khi các bạn làm việc trong nhiều sheet khác nhau thì không tránh khỏi các sự nhầm lẫn do trùng lặp thông tin dữ liệu. Chính vì thế, việc tổng hợp các sheet có cấu trúc giống nhau là rất cần thiết.
Cách tổng hợp dữ liệu từ nhiều sheet khác nhau
Với việc tiện ích trong cách sử dụng excel, công việc của chúng ta thường đạt được kết quả như mong đợi. Tuy nhiên, những bí kíp để đạt được những kết quả tốt sẽ giúp ích cho bạn hơn. Có rất nhiều dữ liệu được nằm trong nhiều sheet khác nhau nhưng lại có liên quan đến nhau. Vì thế, để lọc được dữ liệu chính xác người ta dùng cách để lọc dữ liệu giữa các sheet. Đặc biệt là cách tổng hợp dữ liệu từ nhiều sheet khác nhau như bên dưới. Các bạn cùng tham khảo nhé!
Dùng hàm Vlookup
- Hình ảnh bên dưới là giả sử công ty bạn có danh sách nhân viên như sheet 1:
- Tiếp theo là sheet 2 chứa mã chức vụ và mức lương của mỗi người khác nhau được thể hiện tương ứng với các dòng cột tương ứng.
Qua 2 hình ảnh trên ta có thể hình dung được yêu cầu như sau: Bạn cần dò tìm mã vị trí ở bảng dữ liệu trong Sheet 1 với mã vị trí ở bảng dữ liệu trong Sheet 2. Nếu giống nhau mã vị trí thì trả về tiền lương tương ứng với mỗi cán bộ nhân viên như danh sách trên. Để tìm giá trị của một cột bạn sử dụng hàm VLOOKUP trước tiên cho ô giá trị E4 theo danh sách nhân viên với công thức như sau:
- Lookup_value: giá trị dò tìm. Sau đó bạn chọn D4. Nhưng vì đây là ô dữ liệu nằm trong Sheet1 nên bạn sẽ nhập là Sheet1!D4. Do đó, công thức bây giờ là =VLOOKUP[Sheet1!D4]
- Table_array: Chúng ta mở qua sheet2 và sau đó chọn bảng dò tìm ở Sheet2. Là vùng giá trị A4:B8. Tuy nhiên đây là bảng giá trị ở Sheet 2 nên bạn sẽ phải nhập là Sheet2!A4:B8. Ngoài ra bạn cần thêm ký tự $ để cố định bảng dò tìm này lại. Cuối cùng chúng ta có công thức sẽ là =VLOOKUP [Sheet1!D4, Sheet2!$A$4:$B$8]
- Col_index_num: được gọi là cột chứa giá trị tham chiếu. Cột lương là cột thứ 2 của bảng lương nên bạn nhập là 2. Công thức bây giờ là =VLOOKUP[Sheet!D4,Sheet2!$A$4:$B$8,2]
- Range_lookup: tức là ta cần kết quả trả về chính xác với dữ liệu trong bảng dò tìm cho nên Range_lookup sẽ là 0. Công thức tìm kiếm sẽ là:
=VLOOKUP[Sheet1!D4, Sheet2!$A$4:$B$8,2,0]
- Hình ảnh bên dưới cột lương hiển thị chính xác theo mục đích tìm kiếm bên trên và chúng ta đã đạt được điều đó. Kết quả chính xác chỉ vài thao tác đơn giản.
Tiếp theo chúng ta sao chép công thức cho các ô còn lại. Kể các có bao nhiêu cột bạn chỉ cần sao chép như vậy thì công thức sẽ tự chạy và ra kết quả như ý muốn.
Như vậy lấy giá trị từ các Sheet khác nhau ta sẽ nhập ô giá trị hoặc mảng dữ liệu tham chiếu để kết nối dữ liệu với các sheet khác nhau. Bằng cách này, các bạn có thể hoàn thành công việc cách nhanh nhất.
Dùng công cụ Macro
Khi tổng hợp các dữ liệu từ nhiều sheet trong excel, nếu sao chép từ các sheet thì rất mất thời gian. Macro có những giải pháp giúp bạn đạt hiệu quả công việc dễ dàng hơn.
Giả sử chúng ta có 3 sheets với cấu trúc giống nhau và chỉ khác nhau về dữ liệu như bên dưới:
Bước 1: Trước hết bạn cần mở file Tong hop.xlsm
Bước 2: Sau đó bạn nên bấm tổ hợp phím ALT + F11 để mở trình soạn thảo
Bước 3: Tiếp theo bạn cần mở Project Explorer sau đó click vào View > Project Explorer
Bước 4: Khi bạn nhìn thấy cửa sổ Project Explorer thì nên tạo 1 Module mới
Bước 5: Tiếp theo bạn sao chép vào trong module mới vừa tạo ra
Bước 6: Cuối cùng bạn bấm F5 trên bàn phím và nhận kết quả. Đây là thao tác giúp tăng hiệu quả công việc. Hơn nữa, khi làm báo cáo cũng chuyên nghiệp và đảm bảo chính xác tuyệt đối nếu chúng ta thao tác đúng.
Trên đây là những chia sẻ của chúng tôi về cách tổng hợp dữ liệu từ nhiều sheet trong excel có cấu trúc giống nhau. Hy vọng các bạn có thể áp dụng thuận lợi trong công việc.