lúc này mình đang hướng dẫn chúng ta cách để tổng hợp được nhiều Sheet vào Excel để ship hàng cho các các bước và mục tiêu của chúng ta nhé. Câu hỏi này khá phức hợp nhưng có lợi nên chúng ta cố cầm cố là theo gần như gì mình chỉ dẫn nhé. Bọn họ cùng vào bài xích nhé.
Tổng hợp dữ liệu của những sheet
Bạn có thể tải file để thuộc mình thực hành tại đây.
Bạn đang xem: Cách cộng dữ liệu từ nhiều sheet
Ở đây mình có 1 ví dụ. Các bạn cùng làm ví dụ này cùng mình nhé.
Bây giờ chúng ta sẽ với mọi người trong nhà tổng hợp dữ liệu từ các Sheet này vào Sheet tổng thích hợp nhé.
Đầu tiên bọn họ sẽ mang tên tỉnh cho nên trước. Để đem tên các khoanh vùng thì chúng ta chỉ bắt buộc sang bôi black cột khoanh vùng bên Sheet1 nhấn buộc phải chuột rồi lựa chọn copy tiếp đến 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 giấc thành duy nhất.
Tiếp theo chúng ta sẽ đi lấy tên Sheet. Để đem tên bí quyết thì bọn họ 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 gồm công thức Define name các bạn sẽ copy cách làm đó rồi vào thẻ Formulas rồi chọn Define name thì hành lang cửa số Define sẽ hiện ra rồi trong số ấy 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 chúng ta có thể vào phần Name Manager để kiểm tra cái Define name chúng ta vừa tạo thành và ví như các bạn có nhu cầu sửa lại phương pháp thì rất có thể ấn vào mục Edit.
Bây tiếng các các bạn sẽ đánh số máy tự theo số Sheet của các bạn ví dụ mình có 4 Sheet mình sẽ đánh số thiết bị tự từ là một đến 4 để lấy tên Sheet. Tiếp theo các các bạn sẽ sử dụng hàm Index. Vào hàm Index thành phần trước 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ứ hai sẽ là địa điểm ô thời điểm nãy các bạn đánh số 1 tức Sheet 1 thì nó đang ra dòng Sheet đầu tiên của bản thân là tổng hợp.
Ví dụ như trong bài của chính mình sẽ là: =INDEX(Lay
Ten
Sheet,E1)
Sau đó chúng ta kéo sang ngang ô vừa new Index để copy bí quyết lấy tài liệu sang những ô mặt cạnh. Các ô hình như theo máy tự những số sẽ là sản phẩm tự lần lượt các Sheet.
Như vậy bản thân đã mang tên Sheet rồi đúng không. Đầu tiên các bạn vào ô đựng tên Sheet nên tổng hợp với tên quanh vùng muốn tổng hợp rồi nhập áp dụng hàm Count
IF. Ở thành phần trước tiên sẽ là cái cột dữ liệu ở Sheet nơi bắt đầu để mang theo tổng hợp, trong bài xích này vẫn là cột khu vực vực các bạn tíchchọn cột khu vực vực tiếp nối thành phần thứ 2 sẽ là địa điểm ô chứa tiêu chuẩn để tổng thích hợp ví dụ trong bài xích này là thủ đô hà nội thì chúng ta tick chọn ô thành phố hà nội là được rồi Enter.
Ví dụ bài xích này sẽ là: =COUNTIF(Sheet1!B:B,tonghop!D3)
Tuy nhiêndùng giải pháp này các bạn sẽ không thể copy công thức cho những ô khác được phải cách bạn sẽ đổi bí quyết cho mình như sau.
Bây giờ mình sẽ thực hiện hàm Count
If kết hợp với hàm Indirect. Đầu tiên chúng ta vào ô chứa tên Sheet phải tổng hợp cùng tên khu vực muốn tổng đúng theo rồi nhập lồng 2 hàm như sau =COUNTIF(INDIRECT("'"&F$2&"'!$B:$B"),tonghop!$D3) trong những số ấy F$2 là vị trí đựng tên Sheet sau khi các bạn F4 2 lần, !$B:$B là cột chứa tài liệu từ Sheet gốc không tổng hòa hợp trong bài của bản thân mình là Sheet 1, tonghop!$D3 là tên Sheet đang chứa vị tríô yêu cầu tổng hợp theo tiêu chí nào sau khoản thời gian đã F4 3 lần như trong bài của bản thân là khu vực vực: Hà Nội. (Phần này tương đối phức tạp chúng ta nên xem clip của mình để hiểu rõ hơn).
Xem thêm: Những Câu Nói Hay Về Người Lùn Khiến Mọi Người Phải Cúi Nhìn
Ví dụ: =COUNTIF(INDIRECT("'"&F$2&"'!$B:$B"),tonghop!$D3)
Sau kia bạn chỉ cần kéo ra các ô khác nhằm copy phương pháp thì công dụng các ô giống như sẽ hiện nay ra.
Các bạn lưu ý kết quả của mình có 1 ô trống. Đó là những quý khách hàng không có khu vực hoặc ko thuộc tỉnh thành nào cả hotline là Blanks.
Bây giờ đồng hồ mình đã đổi nó thành Other để triển khai tổng hòa hợp nó nhé. Các bạn sẽ dùng hàm Count
A nhé. Trong nhân tố Count
A các các bạn sẽ chọn cột số thiết bị tự mặt cái Sheet ban đầu vì chưng cột kia khôngcó tài liệu trống rồi trừ đi 1 đó là dòng tiêu đề nhằm tính ra tất cả bao nhiêu người sử dụng sau đó các bạn trừ đi các người sử dụng đã bao gồm 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 xuôi phải đóng góp ngoặc vào luôn luôn nhé.
Ví dụ như vào bài của chính bản thân mình sẽ là: =COUNTA(Sheet1!A:A)-1-SUM(tonghop!F3:F8)
Tương trường đoản cú như bài trên để copy cách làm sang những ô khác chúng ta phải sửahàm thành như sau =COUNTA(INDIRECT("'"&F$2&"'!$A:$A"))-1-SUM(tonghop!F3:F8)
Giống như trên cần mình không gợi ý lại nữa. Các bạn cũng chỉ việc kéo sang các ô ở bên cạnh là được.
Cuối thuộc cột tinhtong chúng ta chỉ phải dùng hàm SUM nhằm tính tổng theo từng quanh vùng là được.
Ví dụ: =SUM(F16:H16)
Tương tự chúng ta kéo copy công thức cho những ô dưới là được. Rồi chúng ta tiến hành sửa đổi bảng tất cả 2 cột khu vực và tinhtong sao cho đẹp tuyệt vời nhất là được ví dụ như kẻ khung, thoa đậ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ẽ auto cập nhật. Dường như các chúng ta cũng có thể ẩn đi những dữ liệu không nên thiết bằng phương pháp bôi black chúng rồi nháy cần 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 lốt chấm phẩy liền nhau rồi bấm Ok là được.
Trong một số trong những trường hợp thực tế, chúng ta cũng có thể lưu tài liệu trong cùng 1 file Excel nhưng trong tương đối nhiều bảng tính (sheet) khác nhau. Bạn có thể lưu dữ liệu mỗi tháng tại một sheet, hoặc dữ liệu công dụng làm bài toán của mỗi nhân viên cấp dưới hay chi nhánh công ty tại 1 bảng tính. Đặc trưng của kiểu tàng trữ dữ liệu này là cấu trúc cột của những bảng dữ liệu giống nhau, chỉ có độ dài những bảng tài liệu là rất có thể khác nhau. Làm sao bạn có thể tạo Pivot Table từ rất nhiều sheet vào Excel?
Chúng ta, tại vị trí quản lý, cần phải có cái nhìn tổng thể hơn dựa trên những bảng dữ liệu, trong trường phù hợp này chúng ta cũng có thể sử dụng giải pháp Pivot Table vào Excel. Tuy vậy vấn đề đặt ra là, tài liệu lại sẽ nằm ở những sheet không giống nhau trong một tệp tin Excel. Bài này học Excel Online đang hướng dẫn các bạn một chuyên môn để có thể tạo Pivot Table từ rất nhiều sheet vào Excel.
Xem nhanh
Sửa tên sheet
Đầu tiên, họ hãy đi sửa lại tên bảng tính để tiện lợi cho quá trình sau. Bỏ phần này, chúng ta có thể đặt tên sheet ngắn gọn, không có dấu phương pháp và không tồn tại kí tự sệt biệt. Vì tài liệu mẫu trong bài bác này đang rất được lưu theo năm, bắt buộc mình để tên những sheet theo lần lượt là Y2017, Y2018, Y2019, Y2020. Để đổi tên sheet, chúng ta click đúp con chuột trái vào tên sheet với gõ thương hiệu mới.
Ở hành lang cửa số tiếp theo, bạn phải chọn đúng đường dẫn tới file Excel hiện tại
Tiếp theo, chúng ta sẽ gạn lọc nơi đã lưu dữ liệu ở file Excel là 1 trong những worksheet mới
Như vậy là từ bỏ bảng dữ liệu này, chúng ta cũng có thể tạo ra Pivot Table và những report cần thiết. Khi dữ liệu ở các bảng dữ liệu ban sơ thay đổi, bạn chỉ cần bấm Refresh tài liệu trong thẻ Table kiến thiết là dữ liệu sẽ tiến hành cập nhật
Nếu công việc phía trên còn chưa rõ với các bạn, mời các bạn xem đoạn clip sau đây:
Chúc thành công các bạn thành công với bài hướng dẫn này của chúng tôi. Cập nhật thêm những kiến thức và kỹ năng về Power Query sống những bài viết sau nhé!
