hôm nay mình vẫn hướng dẫn chúng ta cách để tổng hợp được không ít Sheet vào Excel để giao hàng cho các quá trình và mục tiêu của chúng ta nhé. Bài toán này khá phức hợp nhưng có lợi nên chúng ta cố thế là theo đầy đủ gì mình lý giải nhé. Bọn họ cùng vào bài nhé.
Tổng phù hợp dữ liệu của những sheet
Bạn hoàn toàn có thể tải tệp tin để cùng mình thực hành thực tế tại đây.
Bạn đang xem: Cộng nhiều sheet trong excel
Ở phía trên 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ờ họ sẽ cùng cả nhà tổng hợp dữ liệu từ những Sheet này vào Sheet tổng đúng theo nhé.
Đầu tiên bọn họ sẽ mang tên tỉnh bởi thế trước. Để lấy tên các khu vực thì các bạn chỉ cần sang bôi black cột khoanh vùng bên Sheet1 nhấn nên chuột rồi lựa chọn copy tiếp nối quay lại Sheet tổng hợp nhằm Paste ra rồi sau đó các bạn vào thẻ Data chọn Remove Duplicates để kéo ra các tỉnh thành duy nhất.
Tiếp theo họ sẽ đi mang tên Sheet. Để mang tên công thức 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 có công thức Define name các bạn sẽ copy bí quyết đó rồi vào thẻ Formulas rồi lựa chọn Define name thì hành lang cửa số Define sẽ chỉ ra rồi trong các số đó phần Name các các bạn sẽ đặt tên tiêu đề bất kỳbạn ao ước rồi phần Refers to chúng ta Paste cái bí quyết vừa Copy vào rồi ấn Ok.
Các bạn có thể vào phần Name Manager để chất vấn cái Define name các bạn vừa tạo nên và trường hợp các bạn có nhu cầu 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ố đồ vật tự theo số Sheet của các bạn ví dụ mình tất cả 4 Sheet mình vẫn đánh số vật dụng tự từ 1 đến 4 để mang tên Sheet. Tiếp theo các các bạn sẽ sử dụng hàm Index. Trong hàm Index thành phần thứ nhất của các bạn sẽ là tên Define name thời điểm nãy, còn thành phần thứ 2 sẽ là địa chỉ ô cơ hội nãy các bạn đánh hàng đầu tức Sheet 1 thì nó sẽ ra dòng Sheet đầu tiên của mình là tổng hợp.
Ví dụ như trong bài của bản thân mình sẽ là: =INDEX(Lay
Ten
Sheet,E1)
Sau đó chúng ta kéo thanh lịch ngang ô vừa mới Index nhằm copy công thức lấy dữ liệu sang các ô bên cạnh. Những ô ngoài ra theo trang bị tự các số vẫn là đồ vật tự lần lượt các Sheet.
Như vậy bản thân đã có tên Sheet rồi đúng không. Đầu tiên chúng ta vào ô chứa tên Sheet buộc phải tổng hợp cùng tên khu vực muốn tổng đúng theo rồi nhập áp dụng hàm Count
IF. Ở thành phần trước tiên sẽ là loại cột dữ liệu ở Sheet nơi bắt đầu để đưa đi tổng hợp, trong bài xích này sẽ là cột khu vực vực chúng ta tíchchọn cột khu vực kế tiếp thành phần thứ 2 sẽ là địa điểm ô chứa tiêu chí để tổng thích hợp ví dụ trong bài này là thủ đô thì các bạn tick lựa chọn ô tp hà nội là được rồi Enter.
Ví dụ bài này đã 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 các ô khác được đề nghị cách các bạn sẽ đổi công thức cho bản thân như sau.
Bây giờ bản thân sẽ thực hiện hàm Count
If kết phù hợp với hàm Indirect. Đầu tiên các bạn vào ô đựng tên Sheet nên tổng hợp với tên khoanh vùng muốn tổng vừa lòng 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í đựng tên Sheet sau khi chúng ta F4 2 lần, !$B:$B là cột chứa tài liệu từ Sheet gốc không tổng đúng theo trong bài của mình là Sheet 1, tonghop!$D3 là tên gọi Sheet đang đựng vị tríô phải tổng phù hợp theo tiêu chí nào sau khi đã F4 3 lần như trong bài của bản thân mình là khu vực vực: Hà Nội. (Phần này khá phức tạp các bạn nên xem clip của mình để hiểu rõ hơn).
Ví dụ: =COUNTIF(INDIRECT("'"&F$2&"'!$B:$B"),tonghop!$D3)
Sau kia bạn chỉ việc kéo ra những ô khác để copy phương pháp thì công dụng các ô tương tự như sẽ hiện ra.
Các bạn xem xét kết trái của mình có 1 ô trống. Đó là những khách hàng không có khoanh vùng hoặc không thuộc thức giấc thành làm sao cả call là Blanks.
Bây giờ đồng hồ mình vẫn đổi nó thành Other để tiến hành tổng đúng theo nó nhé. Các các bạn sẽ dùng hàm Count
A nhé. Trong nguyên tố Count
A các các bạn sẽ chọn cột số lắp thêm tự bên cái Sheet ban đầu do cột đó khôngcó tài liệu trống rồi trừ đi 1 đó được coi là dòng tiêu đề để tính ra tất cả bao nhiêu quý khách sau đó các bạn trừ đi các quý khách đã có khu vực bằng phương pháp sử dụng hàm Sum các kết quả đã tổng hợp bên trên.
Lưu ý sau khoản thời gian chọn cột kết thúc phải đóng góp ngoặc vào luôn luôn nhé.
Ví dụ như vào bài của bản thân mình sẽ là: =COUNTA(Sheet1!A:A)-1-SUM(tonghop!F3:F8)
Tương từ bỏ như bài bác trên để copy bí quyết 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 đề xuất mình không giải đáp lại nữa. Chúng ta cũng chỉ cần kéo sang những ô ở kề bên là được.
Cuối cùng cột tinhtong chúng ta chỉ nên dùng hàm SUM để 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 ô bên dưới là được. Rồi chúng ta tiến hành chỉnh sửa bảng tất cả 2 cột khu vực và tinhtong sao cho đẹp nhất là được ví như kẻ khung, trét đậm..
Ngoài ra các bạn cũng có thể vào sửa thương hiệu cột tinhtong bằng cách vào đổi tên Sheet tinhtong thành Tính tổng rồi chúng ta ấn vô ô tinhtong cũ rồi ấn Enter nó sẽ tự động cập nhật. Dường như các bạn có thể ẩn đi những dữ liệu không đề nghị thiết bằng cách bôi black chúng rồi nháy buộc phải chuột lựa chọn Format Cells.
Rồi tiếp đó chúng ta vào thẻ Costom sửa phần General thành 3 vệt chấm phẩy ngay tức khắc nhau rồi bấm Ok là được.
Cách tính tổng vào excel nói khó không phải khó tuy nhiên để hiểu hết các chức năng và sử dụng rất tốt các hàm tính tổng vào excel thì bạn cần phải nghiên cứu giúp và đọc thêm rất nhiều.
Trong nội dung bài viết dưới đây, chứng từ Tin Học công sở sẽ phía dẫn các bạn cách tính tổng trong Excel các sheet tất cả điều kiện. Hy vọng hoàn toàn có thể giúp các bạn áp dụng tốt vào các bước của mình.
I. Cách tính tổng trong excel trường hợp trên các trang tính
Nhiều dân văn phòng công sở khi luôn gặp phải sự việc không biết làm gắng nào nhằm tính tổng dữ liệu, trong những lúc dữ liệu này lại nằm rải rác rến ở những trang tính (sheets) khác nhau. Phương pháp tính tổng trong excel bằng cách dùng hàm SUM trên các sheet vào Excel, hay nói một cách khác là cách cộng những sheet vào Excel để giúp các bạn làm việc hiệu quả hơn.
Đôi khi dữ liệu của chúng ta cũng có thể kéo dài một vài trang tính trong một tệp Excel. Điều này là phổ biến đối với dữ liệu được thu thập định kỳ. Từng trang tính trong sổ làm việc có thể chứa dữ liệu trong một khoảng thời gian nhất định. Chúng tôi muốn một bí quyết tính tổng dữ liệu có trong nhị hoặc những trang tính.
Cách tính tổng trong excel bằng hàm SUM được cho phép bạn thuận tiện tổng hợp tài liệu trên những trang tính bằng phương pháp sử dụng Tham chiếu 3D: = SUM (Trang tính1: Trang tính2! A1)
Ví dụ này đang tổng thích hợp số lượng ship hàng theo kế hoạch cho mỗi Khách sản phẩm trên những trang tính, mỗi trang tính duy trì dữ liệu tương quan đến một tháng khác nhau, ta có thể thực hiện phương pháp tính tổng vào excel bằng phương pháp sử dụng các Hàm SUMIFS, SUMPRODUCT cùng INDIRECT:
= SUMPRODUCT (SUMIFS (INDIRECT (“‘” & F3: F6 và “‘!” & “D3: D7”), INDIRECT (“‘” và F3: F6 & “‘!” và “C3: C7”), H3))
1. Bước 1: Tạo công thức SUMIFS chỉ cho 1 trang tính đầu vào
Chúng tôi thực hiện Hàm SUMIFS để tổng vừa lòng Số lượng giao hàng theo Kế hoạch của bạn cho một bảng tài liệu đầu vào:
= SUMIFS (D3: D7, C3: C7, H3)
2. Bước 2: Thêm tham chiếu trang tính vào công thức
Chúng tôi duy trì nguyên công dụng công thức, nhưng chúng tôi chỉ định rằng dữ liệu đầu vào bên trong trang tính có tên là ‘Bước 2’
= SUMIFS (‘Bước 2’! D3: D7, ‘Bước 2’! C3: C7, H3)
3. Bước 3: Lồng vào một hàm SUMPRODUCT
Để sẵn sàng công thức để thực hiện các phép tính SUMIFS trên nhiều trang tính và kế tiếp tổng đúng theo các kết quả lại với nhau, shop chúng tôi thêm một Hàm SUMPRODUCT bao quanh công thức
= SUMPRODUCT (SUMIFS (‘Bước 3’! D3: D7, ‘Bước 3’! C3: C7, H3))
Sử dụng Hàm SUMIFS trên một trang tính sẽ mang đến một cực hiếm duy nhất. Trên các trang tính, hàm SUMIFS xuất ra một mảng giá trị (một cho từng trang tính). Công ty chúng tôi sử dụng Hàm SUMPRODUCT để tổng các giá trị trong mảng này.
4. Bước 4: sửa chữa Tham chiếu Trang tính bằng danh sách Tên Trang tính
Chúng tôi muốn sửa chữa phần tên trang tính của công thức bởi một danh sách tài liệu chứa các giá trị: mon 1, tháng 2, tháng 3 với Tháng 4. Danh sách này được lưu giữ trữ trong các ô F3: F6.
Hàm INDIRECT để đảm bảo rằng list văn bạn dạng hiển thị tên trang được xem là 1 phần của tham chiếu ô thích hợp lệ vào Hàm SUMIFS.
= SUMPRODUCT (SUMIFS (INDIRECT (“‘” & F3: F6 và “‘!” & “D3: D7”), INDIRECT (“‘” và F3: F6 và “‘!” & “C3: C7”), H3))
Trong công thức này, tham chiếu phạm vi đang viết trước đó: ‘Bước 3’! D3: D7 Được sửa chữa bởi: INDIRECT (“‘” & F3: F6 & “‘!” và “D3: D7”)
Dấu ngoặc kép tạo nên công thức cực nhọc đọc, vì chưng vậy ở đây nó được hiển thị với các khoảng trắng được thêm vào: INDIRECT (“‘” và F3: F6 & “‘!” và “D3: D7”)
Sử dụng bí quyết này nhằm tham chiếu danh sách ô cũng chất nhận được chúng tôi tóm tắt dữ liệu từ nhiều trang tính ko tuân theo phong cách danh sách số. Tham chiếu 3 chiều tiêu chuẩn chỉnh sẽ yêu cầu tên trang tính theo kiểu: Input1, Input2, Input3, v.v., tuy thế ví dụ trên được cho phép bạn thực hiện danh sách bất kỳ Tên trang tính nào và để chúng được tham chiếu trong một ô riêng biệt.
5. Khóa tham chiếu ô
Để làm cho những công thức của công ty chúng tôi dễ gọi hơn, cửa hàng chúng tôi đã hiển thị những công thức mà không tồn tại tham chiếu ô bị khóa:
= SUMPRODUCT (SUMIFS (INDIRECT (“‘” & F3: F6 & “‘!” & “D3: D7”), INDIRECT (“‘” và F3: F6 và “‘!” và “C3: C7”), H3))
Nhưng những công thức này sẽ không hoạt động thông thường khi xào nấu và dán ở khu vực khác trong tệp của bạn. Nỗ lực vào đó, chúng ta nên sử dụng những tham chiếu ô bị khóa như sau:
= SUMPRODUCT (SUMIFS (INDIRECT (“‘” và $ F $ 3: $ F $ 6 & “‘!” và “D3: D7”), INDIRECT (“‘” & $ F $ 3: $ F $ 6 và “‘!” & “C3: C7”), H3))
II. Cách tính tổng vào Excel giả dụ trên các trang tính vào Google Trang tính
Không thể sử dụng Hàm INDIRECT để tham chiếu danh sách những trang tính trong Hàm SUMPRODUCT với SUMIFS trong Google Trang tính.
Thay vào đó, những phép tính SUMIFS riêng lẻ có thể được thực hiện cho mỗi trang đầu vào và các kết quả được cùng lại cùng với nhau:
= SUMIFS (Tháng 1! D3: D7, tháng 1! C3: C7, H3)+ SUMIFS (Tháng 2! D3: D7, mon 2! C3: C7, H3)+ SUMIFS (Tháng 3! D3: D7, tháng 3! C3: C7, H3)+ SUMIFS (Tháng 4! D3: D7, mon 4! C3: C7, H3)
Trên đấy là những hướng dẫn cụ thể nhất của chứng chỉ Tin Học văn phòng trong phương pháp tính tổng vào excel có điều kiện từ dữ liệu tổng hợp của đa số sheet. Hi vọng những hướng dẫn trên có thể giúp bạn tiến hành được phương pháp tính tổng vào excel tác dụng nhất.
Xem thêm:
Nếu các bạn muốn nâng cao thêm kỹ năng thực hành tin học công sở để công việc của chúng ta được xử lý một cách lập cập và hiệu quả trong thời đại công nghệ số, các chúng ta cũng có thể thâm khảo: Khóa học tập tin học tập văn phòng. Search một khóa huấn luyện và đào tạo thực hành cùng với các chuyên viên ở phần lớn trung tâm đào tạo uy tín để chuyển đổi tác phong làm cho việc của bạn nhé.