Nhờ giúp công thức tính tổng theo điều kiện trên nhiều sheet

[thaitungchi]

Như tiêu để của bài viết, nhờ mọi người giúp tạo công thức theo hàm của excel. Trong file đính kèm là ví dụ cụ thể, tính doanh thu theo từng mặt hàng trong từng tháng của nhiều chi nhánh (sheet). Ở từng sheet là có các mặt hàng giống nhau nhưng sắp xếp không theo thứ tự.

Có thể đặt tên thành các table để tính nhưng vẫn chưa tìm ra cách tính nên rất mong mọi người giúp.

Nếu dùng VBA thì mình có thể xử lý hoặc cho vào cùng một sheet thì mình có thể xử lý được, nhưng ở đây vấn đề là trên nhiều sheet.

 

[truongthon304]

Nếu không có yêu cầu gì đặc biệt thì đề xuất Sumif cho cái này- Ô E7:
=SUMIF('Chi nhanh A'!$D$28:$D$33;$D7;'Chi nhanh A'!E$28:E$33)+SUMIF('Chi nhanh B'!$D$28:$D$33;$D7;'Chi nhanh B'!E$28:E$33)+SUMIF('Chi nhanh C'!$D$28:$D$33;$D7;'Chi nhanh C'!E$28:E$33)
Xong kéo qua các ô liền kề!

 

[thaitungchi]

Cám ơn bạn truongthon304. Đúng là có cách này hoặc dùng vlookup, sumproduct nữa. Nhưng nếu trong trường hợp có khoảng 10 chi nhánh và thì công thức sẽ rất dài.

Liệu có cách nào dùng công thức mảng, hoặc đặt tên cho bảng để dùng trong trường hợp này được không?

 

[Nhattanktnn]

Cám ơn bạn truongthon304. Đúng là có cách này hoặc dùng vlookup, sumproduct nữa. Nhưng nếu trong trường hợp có khoảng 10 chi nhánh và thì công thức sẽ rất dài.

Liệu có cách nào dùng công thức mảng, hoặc đặt tên cho bảng để dùng trong trường hợp này được không?

Tại E7:
Nếu tên chi nhánh (Không phải A,B,C) thì:

=SUM(SUMIF(INDIRECT("'Chi nhanh "&{"A";"B";"C"}&"'!D7:D12"),$D7,INDIRECT("'Chi nhanh "&{"A";"B";"C"}&"'!"&CHAR(COLUMN(A1)+68)&7)))

Nếu tên chi nhánh đúng là A,B,C (làm với số lượng nhiều sheet) thì thế này(sửa số 67 trong row(65:67)):

=SUMPRODUCT(SUMIF(INDIRECT("'Chi nhanh "&CHAR(ROW($65:$67))&"'!D7:D12"),$D7,INDIRECT("'Chi nhanh "&CHAR(ROW($65:$67))&"'!"&CHAR(COLUMN(A1)+68)&7)))

 

[thaitungchi]

Cám ơn bạn Nhattanktnn.

Mình đã thử với công thức trên nhưng kết quả bằng 0 cho dù ấn cả ctrl shift enter.

Bạn có thể gửi file trong cả hai trường hợp, có số lượng nhiều chi nhánh được không?

 

[Nhattanktnn]

Cám ơn bạn Nhattanktnn.

Mình đã thử với công thức trên nhưng kết quả bằng 0 cho dù ấn cả ctrl shift enter.

Bạn có thể gửi file trong cả hai trường hợp, có số lượng nhiều chi nhánh được không?

Bạn đưa file dữ liệu (thật) của bạn lên luôn đi, mình sửa công thức trong đó cho bạn luôn (có 10 sheet đưa 10 sheet)

 

[thaitungchi]

Cám ơn bạn nhé. Mình làm được rồi. Do tham chiếu bị sai. Cám ơn nhattanktnn rất nhiều nhiều

 

[xuongrongdat]

Cám ơn bạn nhé. Mình làm được rồi. Do tham chiếu bị sai. Cám ơn nhattanktnn rất nhiều nhiều

Lành thay, lành thay! Bạn có thể chia sẻ cho mọi người tham khảo cách làm của bạn không?
Mình nãy giờ cũng mò giải cái bài của bạn theo hướng bạn @Nhattanktnn mà làm chưa ra được như bạn ấy. híc

 

[thaitungchi]

Bạn @Nhattanktnn có thể cho hỏi thêm, trong trường hợp cho tên chi nhánh vào trong một danh sách từ S1:S10 thì phần {“A”;”B”;”C”} sẽ thay đổi như thế nào?

Bài đã được tự động gộp: 24/12/20

@xuongrongdat
File mình đưa lên thì đang để dữ liệu ở D27:K33. Hoặc bạn copy dữ liệu lên trên giữ nguyên công thức ở trên, hoặc là chỉnh lại theo công thức dưới.

=SUM(SUMIF(INDIRECT("'Chi nhanh "&{"A";"B";"C"}&"'!D2833"),$D7,INDIRECT("'Chi nhanh "&{"A";"B";"C"}&"'!"&CHAR(COLUMN(A1)+68)&28)))

 

[leonguyenz]

Bạn @Nhattanktnn có thể cho hỏi thêm, trong trường hợp cho tên chi nhánh vào trong một danh sách từ S1:S10 thì phần {“A”;”B”;”C”} sẽ thay đổi như thế nào?

Bạn có thể sử dụng hàm Get.Workbook để lấy danh sách tên Sheet, đưa vào S1:Sn. Tham khảo cách sau:
Define Name: ShName=GET.WORKBOOK(1)
Công thức S7:

=MID(INDEX(ShName,ROWS(A$1:A1)),FIND("]",INDEX(ShName,ROWS(A$1:A1)))+1,255)

Công thức tính tổng nhiều sheet:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$S$7:$S$9&"'!D7:D12"),$D7,INDIRECT("'"&$S$7:$S$9&"'!"&CHAR(COLUMN(A1)+68)&7)))

Save file dạng .xlsm/.xls/.xlsb để lưu hàm Marco 4.0, nếu lưu file .xlsx thì mất name nhé.

 

[Nhattanktnn]

trong trường hợp cho tên chi nhánh vào trong một danh sách từ S1:S10 thì phần {“A”;”B”;”C”} sẽ thay đổi như thế nào?

Mở rộng chỗ S1:S3 nhé bạn:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$S$1:$S$3&"'!D7:D12"),$D7,INDIRECT("'"&$S$1:$S$3&"'!"&CHAR(COLUMN(A1)+68)&7)))

 

[truongthon304]

Đã thử và thành công!
Có điều phải gõ thủ công tên các sheet

 

[thaitungchi]

Cám ơn bạn @leonguyenz. Bạn còn cho cả công thức lấy tên sheet cho vào danh sách. Quả là quá nhiều cao thủ.

 

[Sil_90]

Như tiêu để của bài viết, nhờ mọi người giúp tạo công thức theo hàm của excel. Trong file đính kèm là ví dụ cụ thể, tính doanh thu theo từng mặt hàng trong từng tháng của nhiều chi nhánh (sheet). Ở từng sheet là có các mặt hàng giống nhau nhưng sắp xếp không theo thứ tự.

Có thể đặt tên thành các table để tính nhưng vẫn chưa tìm ra cách tính nên rất mong mọi người giúp.

Nếu dùng VBA thì mình có thể xử lý hoặc cho vào cùng một sheet thì mình có thể xử lý được, nhưng ở đây vấn đề là trên nhiều sheet.

View attachment 251717

theo cái này, mình dùng cách dễ nhất là power query để tổng hợp giữ liệu và dùng công thức đơn giản là có thể giải quyết đc vấn đề này.
chúc bạn thành công

 

[Lê Duy Thương]

Bài bày nếu dùng để nghiên cứu công thức thì mình sẽ không có ý kiến. vì các công thức của các bạn rất hay. Nhưng nếu áp dụng cho công việc thì ta nên dùng consolidate cho nhanh. khỏe, và chính xác

 

[hocexcel_1991]

Như tiêu để của bài viết, nhờ mọi người giúp tạo công thức theo hàm của excel. Trong file đính kèm là ví dụ cụ thể, tính doanh thu theo từng mặt hàng trong từng tháng của nhiều chi nhánh (sheet). Ở từng sheet là có các mặt hàng giống nhau nhưng sắp xếp không theo thứ tự.

Có thể đặt tên thành các table để tính nhưng vẫn chưa tìm ra cách tính nên rất mong mọi người giúp.

Nếu dùng VBA thì mình có thể xử lý hoặc cho vào cùng một sheet thì mình có thể xử lý được, nhưng ở đây vấn đề là trên nhiều sheet.

View attachment 251717

Dùng PowerQuery

 

[thaitungchi]

Cám ơn bạn @hocexcel_1991 . Cố gắng thu xếp thời gian để học thêm power querry. Tuy nhiên chưa có điều kiện nâng cấp nên hiện giờ vẫn đang dùng excel 2007

Công thức lần này không phải chỉ để phô diễn mà nó thực sự rất hữu ích. Ví dụ như có 10 chi nhánh, mỗi người tạo một sheet khoảng 50-100 mặt hàng thì rất mất thời gian để tổng hợp dữ liệu.

Cám ơn bạn @Nhattanktnn lần nữa. Nếu trong trường hợp bảng ở sử dụng name để đặt cho các bảng thì có sử dụng ở công thức này không?
ví dụ bảng ở chi nhánh A đặt là CN_A, tương tự CN_B, CN_C thì có cách nào dùng các tên này không?

 

[VetMini]

Nhìn cái file, bảo dùng Excel 2007 nghe muốn bịnh.
Có cả đống điềun kiện, cả đống mặt hàng, chi nhánh thì LUÔN LUỘN chịu khó cập nhật phiên bản mới nhất.
Công việc càng lúc càng phức tạp mà cứ bám vào công cụ cũ rích!

Cứ thấy dữ liệu càng lúc càng phức tạp quản lý chật vật thì nhảy lên Office 365. Không có lý do gì để biện bạch.

 

[thaitungchi]

Ơ re ka. Đã tìm ra được công thức áp dung cho các bảng có đặt tên sau một thời gian dài nghiên cứu.
Xin gửi tặng mọi người ở đây cho những ai cần tham khảo.

=SUMPRODUCT(SUMIF(INDIRECT($N$19:$N$21&"["&$D$19&"]"),$D20,INDIRECT($N$19:$N$21&"["&E$19&"]")))

Công thức của bạn @Nhattanktnn có một nhược điểm là khi dùng hàm char để đổi ra giá trị tham khảo của cột thì trong trường hợp bảng dài hơn 26 cột phải sửa lại một chút.
Nhưng với công thức trên thì không cần phải như vậy. Công thức này kết hợp sử dụng tính năng tạo bảng.

Vì sao tạo bảng mà không tạo bằng name thì là tại vì bảng có tính năng loại bỏ dòng tiêu đề (dòng đầu tiên) rất hay.

Mình đính kèm file để mọi người tham khảo. Với công thức này có thể tính tổng trên nhiều bảng (hoặc nhiều sheet), với điều kiện ngang và dọc.
Và như vậy trường hợp chi nhánh A có 4 cửa hàng đông, tây, nam, bắc thì vẫn áp dụng được.
Không thể còn điều gì tuyệt vời hơn. Mình xin kết thúc chủ đề này tại đây. Cám ơn mọi người đã dành thời gian quan tâm.

 

[Hoàng Tuấn 868]

Nếu dùng VBA thì mình có thể xử lý hoặc cho vào cùng một sheet thì mình có thể xử lý được, nhưng ở đây vấn đề là trên nhiều sheet.

Bạn tham khảo........