Rút gọn công thức SUMIFS nhiều sheet (1 người xem)

[donhungtin2013]

KÍnh gửi: Anh/em diễn đàn
File quản lý của em bao gồm nhiều cutting list, mỗi cutting list là 1 sheet nên sheet tổng hợp sẽ link các cutting list lại với nhau do đó công thức quá dài dòng và phúc tạp:

=SUMIFS('cutting list no.19'!R:R,'cutting list no.19'!C:C, "="&B101, 'cutting list no.19'!E:E, "="&C101)+SUMIFS('cutting list no.20'!R:R,'cutting list no.20'!C:C, "="&B101, 'cutting list no.20'!E:E, "="&C101)+SUMIFS('cutting list no.21'!R:R,'cutting list no.21'!C:C, "="&B101, 'cutting list no.21'!E:E, "="&C101)+SUMIFS('cutting list no.22'!R:R,'cutting list no.22'!C:C, "="&B101, 'cutting list no.22'!E:E, "="&C101)+SUMIFS('cutting list no.23'!R:R,'cutting list no.23'!C:C, "="&B101, 'cutting list no.23'!E:E, "="&C101)

Tuy nhiên về nội dung thì giống nhau chỉ lặp đi lặp lại thôi, ý em muốn nhờ anh/em diễn đàn viết giùm em đoạn code hoặc có cách nào rút gọn công thức trên giúp em để cho đơn giản và gọn nhẹ hơn. Em cảm ơn nhiều.

 

[Let'GâuGâu]

KÍnh gửi: Anh/em diễn đàn
File quản lý của em bao gồm nhiều cutting list, mỗi cutting list là 1 sheet nên sheet tổng hợp sẽ link các cutting list lại với nhau do đó công thức quá dài dòng và phúc tạp:

=SUMIFS('cutting list no.19'!R:R,'cutting list no.19'!C:C, "="&B101, 'cutting list no.19'!E:E, "="&C101)+SUMIFS('cutting list no.20'!R:R,'cutting list no.20'!C:C, "="&B101, 'cutting list no.20'!E:E, "="&C101)+SUMIFS('cutting list no.21'!R:R,'cutting list no.21'!C:C, "="&B101, 'cutting list no.21'!E:E, "="&C101)+SUMIFS('cutting list no.22'!R:R,'cutting list no.22'!C:C, "="&B101, 'cutting list no.22'!E:E, "="&C101)+SUMIFS('cutting list no.23'!R:R,'cutting list no.23'!C:C, "="&B101, 'cutting list no.23'!E:E, "="&C101)

Tuy nhiên về nội dung thì giống nhau chỉ lặp đi lặp lại thôi, ý em muốn nhờ anh/em diễn đàn viết giùm em đoạn code hoặc có cách nào rút gọn công thức trên giúp em để cho đơn giản và gọn nhẹ hơn. Em cảm ơn nhiều.

bạn nên đưa file lên, nhìn vậy sao mà làm?
bạn địng chuyển nó thành code vba?
nếu vậy bạn nên post bên bõ lập trình

 

[donhungtin2013]

bạn nên đưa file lên, nhìn vậy sao mà làm?
bạn địng chuyển nó thành code vba?
nếu vậy bạn nên post bên bõ lập trình

File nặng lắm anh ơi, gần 30 MB lận, em gởi đường link cho anh nha. Em lỡ post nhầm bên chuyên mục này nhờ ban quản trị chuyển giúp vào mục lập trình với ạ. em cảm ơn

 

[Let'GâuGâu]

File nặng lắm anh ơi, gần 30 MB lận, em gởi đường link cho anh nha. Em lỡ post nhầm bên chuyên mục này nhờ ban quản trị chuyển giúp vào mục lập trình với ạ. em cảm ơn

file gởi cho tôi bị lổi rồi, "extension not invalid"
với lại bạn gởi lên đây để có nhiều người giúp, gởi riêng cho tôi là bạn bĩ thiệt thòi đó

 

[donhungtin2013]

file gởi cho tôi bị lổi rồi, "extension not invalid"
với lại bạn gởi lên đây để có nhiều người giúp, gởi riêng cho tôi là bạn bĩ thiệt thòi đó

File nặng lắm anh ơi gần 30MB lận nên không post được, đường link em gởi mở bình thường mà anh, em thử rồi

 

[Let'GâuGâu]

File nặng lắm anh ơi gần 30MB lận nên không post được, đường link em gởi mở bình thường mà anh, em thử rồi

không biết, nhưng tải về nó báo invalid cái gì đó
svae một file khác, xóa đi nhưng sheet kông liên quan, xóa bớt các dòng dự liệu, nếu có style, hay màu mè gì đó thì bỏ luôn,xóa các name ko sử dụng lưu file lại dưới dạng .xlsb

 

[befaint]

Bạn có thể làm như sau:
Bạn chép file của bạn ra một bản nữa. Sau đó, bạn xóa bớt dữ liệu đi (mỗi bảng dữ liệu chỉ cần để lại 3-5 dòng là ok rồi).
Tại sheet tổng hợp: Bạn nêu cụ thể yêu cầu kết quả như thế nào (cách tổng hợp kết quả, vị trí ghi kết quả...)
Cuối cùng, bạn lưu file lại. Đóng gói bằng winrar / 7zip để giảm dung lượng của file (chắc lúc đó còn khoảng 200KB).
Bạn up file đó lên đây, đảm bảo sẽ rất nhanh có người giúp bạn đúng yêu cầu của bạn (đỡ mất thời gian).
Thân!

 

[donhungtin2013]

Bạn có thể làm như sau:
Bạn chép file của bạn ra một bản nữa. Sau đó, bạn xóa bớt dữ liệu đi (mỗi bảng dữ liệu chỉ cần để lại 3-5 dòng là ok rồi).
Tại sheet tổng hợp: Bạn nêu cụ thể yêu cầu kết quả như thế nào (cách tổng hợp kết quả, vị trí ghi kết quả...)
Cuối cùng, bạn lưu file lại. Đóng gói bằng winrar / 7zip để giảm dung lượng của file (chắc lúc đó còn khoảng 200KB).
Bạn up file đó lên đây, đảm bảo sẽ rất nhanh có người giúp bạn đúng yêu cầu của bạn (đỡ mất thời gian).
Thân!

Cảm ơn bạn mình gởi lại file đã xoá đi chỉ còn lại 18kb nhờ mọi người giúp mình với. Cảm ơn mọi người rất nhiều

 

[ndu96081631]

1. Giả sử liệt kê tên của tất cả các sheet con vào 1 vùng, ví dụ ta Gõ vào ùng I6:I8 (sheet tổng hợp nha) tên của các sheet con (phải gõ đúng tên từng sheet con.)

[TABLE="width: 72"]
[TR]
[TD="class: xl81, width: 72"]cutting list no.15[/TD]
[/TR]
[TR]
[TD="class: xl81"]cutting list no.21[/TD]
[/TR]
[TR]
[TD="class: xl81"]cutting list no.22[/TD]
[/TR]
[/TABLE]




2. Công thức tại D7

 D7 =SUMPRODUCT(SUMIFS(INDIRECT("'"&[COLOR=#ff0000][B]$I$6:$I$8[/B][/COLOR]&"'![B][COLOR=#0000cd]J:J[/COLOR][/B]"),INDIRECT("'"&[B][COLOR=#ff0000]$I$6:$I$8[/COLOR][/B]&"'![B][COLOR=#0000cd]C:C[/COLOR][/B]"),"="&B7,INDIRECT("'"&[COLOR=#ff0000][B]$I$6:$I$8[/B][/COLOR]&"'![B][COLOR=#0000cd]E:E[/COLOR][/B]"),"="&C7))

3. Nếu file thật của bạn có nhiều hơn 3 sheet con như ví dụ thì bạn cứ liệt kê hết tên các sheet con vào 1 vùng như tôi nói, rồi sửa công thức trên ở vùng màu đỏ ($I$6:$I$8) theo cái vùng tên sheet mới của bạn đã liệt kê

Thêm 1 chuyện rất quan trọng: Cho dù hàm SUMIFS có nhanh nhưng tham chiếu cái kiểu nguyên cột (như chỗ màu xanh) thì chỉ tổ phí tài nguyên hệ thống, dẫn đến file bị chậm đi rất nhiều
Tóm lại:
- Dữ liệu đến đâu thì tham chiếu đến nấy, có thể cho dư ra một ít nhưng tuyệt đối không nên tham chiếu nguyên cột
- Excel 2007 trở về sau này, nguyên 1 cột là tương đương với 1,048,576 dòng dữ liệu đấy các bạn à. Bắt Excel tính toán với khối lượng khủng khiếp thế kia thì có mà... đói

 

[befaint]

3. Nếu file thật của bạn có nhiều hơn 3 sheet con như ví dụ thì bạn cứ liệt kê hết tên các sheet con vào 1 vùng như tôi nói, rồi sửa công thức trên ở vùng màu đỏ ($I$6:$I$8) theo cái vùng tên sheet mới của bạn đã liệt kê

Thêm đoạn sau để lấy tên các sheet cho nhanh:

Sub get_name_sheet()
Dim i, n, t As Integer
n = Worksheets.Count
t = 6
For i = 1 To n
    Sheets("tonghop").Range("I" & t).Value = Sheets(i).Name
    t = t + 1
Next
End Sub

 

[donhungtin2013]

Thêm 1 chuyện rất quan trọng: Cho dù hàm SUMIFS có nhanh nhưng tham chiếu cái kiểu nguyên cột (như chỗ màu xanh) thì chỉ tổ phí tài nguyên hệ thống, dẫn đến file bị chậm đi rất nhiều
Tóm lại:
- Dữ liệu đến đâu thì tham chiếu đến nấy, có thể cho dư ra một ít nhưng tuyệt đối không nên tham chiếu nguyên cột
- Excel 2007 trở về sau này, nguyên 1 cột là tương đương với 1,048,576 dòng dữ liệu đấy các bạn à. Bắt Excel tính toán với khối lượng khủng khiếp thế kia thì có mà... đói

Bác ndu ơi
Công thức này nó có 1 số hạn chế, ví dụ khi mình cho dữ liệu từ I6: I10 nó sẽ không hiểu, hay khi mình tăng sheet thì mình phải điều chỉnh vùng dữ liệu bằng thủ công chứ công thức nó không tự điều chỉnh được, vậy mình có cách gì khắc phục được không anh?

 

[chucuoi92]

Bác ndu ơi
Công thức này nó có 1 số hạn chế, ví dụ khi mình cho dữ liệu từ I6: I10 nó sẽ không hiểu, hay khi mình tăng sheet thì mình phải điều chỉnh vùng dữ liệu bằng thủ công chứ công thức nó không tự điều chỉnh được, vậy mình có cách gì khắc phục được không anh?

Đang tập tành cốt kiếc, viết cho bạn xem thử