Tổng hợp dữ liệu từ nhiều Sheet trong Excel 4/2023

Tổng hợp dữ liệu của các sheet

Bạn hoàn toàn có thể tải file để cùng mình thực hành thực tế tại đây .
Ở đây mình có 1 ví dụ. Các bạn cùng làm ví dụ này cùng mình nhé .

ví dụ

Bây giờ chúng ta sẽ cùng nhau tổng hợp dữ liệu từ các Sheet này vào Sheet tổng hợp nhé.

Bạn đang đọc: Tổng hợp dữ liệu từ nhiều Sheet trong Excel 4/2023

Đầu tiên chúng ta sẽ lấy tên tỉnh thành ra trước. Để lấy tên các khu vực thì các bạn chỉ cần sang bôi đen cột Khu vực bên Sheet1 nhấn phải chuột rồi chọn copy sau đó quay lại Sheet tổng hợp để Paste ra rồi sau đó các bạn vào thẻ Data chọn Remove Duplicates để lấy ra các tỉnh thành duy nhất.

Tiếp theo chúng ta sẽ đi lấy tên Sheet. Để lấy tên công thức thì chúng ta phải sử dụng Define name. Công thức của Define name là: =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)

Sau khi có công thức Define name các bạn sẽ copy công thức đó rồi vào thẻ Formulas rồi chọn Define name thì cửa sổ Define sẽ hiện ra rồi trong đó phần Name các bạn sẽ đặt tên tiêu đề bất kỳ bạn muốn rồi phần Refers to các bạn Paste cái công thức vừa Copy vào rồi ấn Ok.

Các bạn có thể vào phần Name Manager để kiểm tra cái Define name các bạn vừa tạo và nếu các bạn muốn sửa lại công thức thì có thể ấn vào mục Edit.

Bây giờ các bạn sẽ đánh số thứ tự theo số Sheet của các bạn ví dụ mình có 4 Sheet mình sẽ đánh số thứ tự từ 1 đến 4 để lấy tên Sheet. Tiếp theo các bạn sẽ sử dụng hàm Index. Trong hàm Index thành phần đầu tiên của các bạn sẽ là tên Define name lúc nãy, còn thành phần thứ 2 sẽ là vị trí ô lúc nãy các bạn đánh số 1 tức Sheet 1 thì nó sẽ ra cái Sheet đầu tiên của mình là tổng hợp.

Ví dụ như trong bài của mình sẽ là: =INDEX(LayTenSheet,E1)

Sau đó những bạn kéo sang ngang ô vừa mới Index để copy công thức lấy dữ liệu sang những ô bên cạnh. Các ô cạnh bên đó theo thứ tự những số sẽ là thứ tự lần lượt những Sheet .

Như vậy mình đã có tên Sheet rồi đúng không. Đầu tiên các bạn vào ô chứa tên Sheet cần tổng hợp và tên khu vực muốn tổng hợp rồi nhập sử dụng hàm CountIF. Ở thành phần đầu tiên sẽ là cái cột dữ liệu ở Sheet gốc để mang đi tổng hợp, trong bài này sẽ là cột Khu vực các bạn tích chọn cột Khu vực sau đó thành phần thứ 2 sẽ là vị trí ô chứa tiêu chí để tổng hợp ví dụ trong bài này là Hà Nội thì các bạn tick chọn ô Hà Nội là được rồi Enter.

Ví dụ bài này sẽ là: =COUNTIF(Sheet1!B:B,tonghop!D3)

Tuy nhiên dùng cách này những bạn sẽ không hề copy công thức cho những ô khác được nên cách bạn sẽ đổi công thức cho mình như sau .

Bây giờ mình sẽ sử dụng hàm CountIf kết hợp với hàm Indirect. Đầu tiên các bạn vào ô chứa tên Sheet cần tổng hợp và tên khu vực muốn tổng hợp rồi nhập lồng 2 hàm như sau =COUNTIF(INDIRECT(“‘”&F$2&”‘!$B:$B”),tonghop!$D3) trong đó F$2 là vị trí chứa tên Sheet sau khi các bạn F4 2 lần, !$B:$B là cột chứa dữ liệu từ Sheet gốc chưa tổng hợp trong bài của mình là Sheet 1, tonghop!$D3 là tên Sheet đang chứa vị trí ô cần tổng hợp theo tiêu chí nào sau khi đã F4 3 lần như trong bài của mình là Khu vực: Hà Nội. (Phần này khá phức tạp các bạn nên xem video của mình để hiểu rõ hơn).

Ví dụ: =COUNTIF(INDIRECT(“‘”&F$2&”‘!$B:$B”),tonghop!$D3)

Xem thêm: TOP 5 game tư duy trí tuệ cực hấp dẫn để luyện não

Sau đó bạn chỉ cần kéo ra những ô khác để copy công thức thì hiệu quả những ô tựa như sẽ hiện ra .

Các bạn lưu ý kết quả của mình có 1 ô trống. Đó là những khách hàng không có khu vực hoặc không thuộc tỉnh thành nào cả gọi là Blanks.

Bây giờ mình sẽ đổi nó thành Other để thực hiện tổng hợp nó nhé. Các bạn sẽ dùng hàm CountA nhé. Trong thành phần CountA các bạn sẽ chọn cột số thứ tự bên cái Sheet ban đầu do cột đó không có dữ liệu trống rồi trừ đi 1 đó là dòng tiêu đề để tính ra có bao nhiêu khách hàng sau đó các bạn trừ đi các khách hàng đã có khu vực bằng cách sử dụng hàm Sum các kết quả đã tổng hợp bên trên.

Lưu ý sau khi chọn cột xong phải đóng ngoặc vào luôn nhé .

Ví dụ như trong bài của mình sẽ là: =COUNTA(Sheet1!A:A)-1-SUM(tonghop!F3:F8)

Tương tự như bài trên để copy công thức sang các ô khác các bạn phải sửa hàm thành như sau =COUNTA(INDIRECT(“‘”&F$2&”‘!$A:$A”))-1-SUM(tonghop!F3:F8)

Giống như trên nên mình không hướng dẫn lại nữa. Các bạn cũng chỉ cần kéo sang những ô bên cạnh là được .
Cuối cùng cột tinhtong những bạn chỉ cần dùng hàm SUM để tính tổng theo từng khu vực là được .

Ví dụ: =SUM(F16:H16)

Tương tự các bạn kéo copy công thức cho các ô dưới là được. Rồi các bạn tiến hành chỉnh sửa bảng gồm 2 cột Khu vực và tinhtong sao cho đẹp nhất là được ví dụ như kẻ khung, bôi đậm..

Ngoài ra các bạn có thể vào sửa tên cột tinhtong bằng cách vào đổi tên Sheet tinhtong thành Tính tổng rồi các bạn ấn vô ô tinhtong cũ rồi ấn Enter nó sẽ tự động cập nhật. Bên cạnh đó các bạn có thể ẩn đi những dữ liệu không cần thiết bằng cách bôi đen chúng rồi nháy phải chuột chọn Format Cells.

Rồi tiếp đó các bạn vào thẻ Costom sửa phần General thành 3 dấu chấm phẩy liền nhau rồi bấm Ok là được.

Xem thêm: TOP 5 game tư duy trí tuệ cực hấp dẫn để luyện não

Chúc những bạn thành công xuất sắc .

Video hướng dẫn