Xin tìm giúp lỗi tính tổng cả năm khi dùng hàm SUMPRODUCT VÀ INDIRECT

[nguyenluy0902]

Nhờ ACE tìm giúp lỗi hoặc có cách nào hay hơn để tính bảng tổng hợp lương cả năm ạ. Trong file ví dụ e đã tính được bằng hàm SUMPRODUCT nhưng nếu dùng để tính cho 12 tháng (12 Sheet) thì công thức sẽ dài nên e đã thử kết hợp với hàm INDIRECT nhưng nó lại báo lỗi. Ở đây họ tên có thể trùng nhau nên công thức sẽ phải dùng nhiều điều kiện và e dùng EXCEL 2003 nên không có hàm SUMIFS nhé. Thanks!

 

[Let'GâuGâu]

Nhờ ACE tìm giúp lỗi hoặc có cách nào hay hơn để tính bảng tổng hợp lương cả năm ạ. Trong file ví dụ e đã tính được bằng hàm SUMPRODUCT nhưng nếu dùng để tính cho 12 tháng (12 Sheet) thì công thức sẽ dài nên e đã thử kết hợp với hàm INDIRECT nhưng nó lại báo lỗi. Ở đây họ tên có thể trùng nhau nên công thức sẽ phải dùng nhiều điều kiện và e dùng EXCEL 2003 nên không có hàm SUMIFS nhé. Thanks!

chắc nó lộn xộn đâu đó
thử cái này xem

=SUMPRODUCT(SUMIFS(INDIRECT("Sheet"&ROW($1:$2)&"!$D$2:$D$5"),INDIRECT("Sheet"&ROW($1:$2)&"!$B$2:$B$5"),B2,INDIRECT("Sheet"&ROW($1:$2)&"!$C$2:$C$5"),C2))

 

[nguyenluy0902]

Nếu sử dụng 12 hàm thì chỉ dài thôi còn nếu định dạng như nhau thì chỉ cần copy công thức cho sheet1 rồi chỉnh sửa sheet1 thành sheet2 là được. Mình muốn làm sao cho ngắn và tốc độ tính toán nó nhanh hơn.

 

[nguyenluy0902]

chắc nó lộn xộn đâu đó
thử cái này xem

=SUMPRODUCT(SUMIFS(INDIRECT("Sheet"&ROW($1:$2)&"!$D$2:$D$5"),INDIRECT("Sheet"&ROW($1:$2)&"!$B$2:$B$5"),B2,INDIRECT("Sheet"&ROW($1:$2)&"!$C$2:$C$5"),C2))

Mình dùng EXCEL 2003 không có hàm SUMIFS a

 

[nguyenluy0902]

Nếu mỗi Sheet insert thêm 1 cột và lấy giá trị bằng cột họ tên và cột đơn vị thì dùng được hàm sumif nhưng không được tiện lắm.

 

[gtri]

Nhờ ACE tìm giúp lỗi hoặc có cách nào hay hơn để tính bảng tổng hợp lương cả năm ạ. Trong file ví dụ e đã tính được bằng hàm SUMPRODUCT nhưng nếu dùng để tính cho 12 tháng (12 Sheet) thì công thức sẽ dài nên e đã thử kết hợp với hàm INDIRECT nhưng nó lại báo lỗi. Ở đây họ tên có thể trùng nhau nên công thức sẽ phải dùng nhiều điều kiện và e dùng EXCEL 2003 nên không có hàm SUMIFS nhé. Thanks!

Tham gia một đoạn code

Public Sub Thay_Ham()
Dim Ws As Worksheet, DL, Tam, kq(), r As Long,c As Long

With CreateObject("Scripting.Dictionary")
For Each Ws In Worksheets
If Ws.Name <> "Tong" Then
DL = Ws.Range("A1").CurrentRegion

For r = 1 To UBound(DL)
Tam = Array(DL(r, 3), DL(r, 4), DL(r, 5))
If Not .exists(DL(r, 2) & "#" & DL(r, 3)) Then
.Add DL(r, 2) & "#" & DL(r, 3), Tam
Else
Tam = .Item(DL(r, 2) & "#" & DL(r, 3))
Tam(1) = Tam(1) + DL(r, 4)
.Item(DL(r, 2) & "#" & DL(r, 3)) = Tam
End If
Next r

End If
Next Ws
Tam = .keys
ReDim kq(1 To .Count, 1 To UBound(DL, 2) + 1)

For c = 1 To UBound(DL, 2)
kq(1, c) = DL(1, c)
Next c

For r = 1 To UBound(Tam)
kq(r + 1, 1) = r: kq(r + 1, 2) = Split(Tam(r), "#")(0)
For c = 0 To UBound(.Item(Tam(r)))
kq(r + 1, c + 3) = .Item(Tam(r))(c)
Next c
Next r

End With
Sheet3.UsedRange.Clear
Sheet3.Range("A1").Resize(UBound(kq), UBound(kq, 2)).Value = kq
Sheet3.UsedRange.Columns.AutoFit
End Sub

 

[nguyenluy0902]

Có cao thủ nào xem hộ e với a? E không biết VBA nên muốn dùng hàm.

 

[_leopard_]

Các bác cho hỏi trong công thức của bác Gâu Gâu ở trên thì tại sao INDIRECT({"Sheet1!$D$2:$D$5";"Sheet2!$D$2:$D$5"}) lại trả về {1;1} vậy nhỉ?

 

[giangleloi]

Các bác cho hỏi trong công thức của bác Gâu Gâu ở trên thì tại sao INDIRECT({"Sheet1!$D$2:$D$5";"Sheet2!$D$2:$D$5"}) lại trả về {1;1} vậy nhỉ?

Vì giá trị D2 đầu tiên của 2 sheet đều bằng 1 thì ra {1,1}. Thử đổi số xem sao

 

[_leopard_]

Vì giá trị D2 đầu tiên của 2 sheet đều bằng 1 thì ra {1,1}. Thử đổi số xem sao

Sao nó lại tham chiếu đến giá trị D2 đầu tiên mà không phải là D3, D4 vậy bác?

 

[giangleloi]

Sao nó lại tham chiếu đến giá trị D2 đầu tiên mà không phải là D3, D4 vậy bác?

Bạn fill công thức xuống, giá trị thứ 2 thì nó sẽ lấy D3, thứ 3 thì D4....

 

[comet_1701]

mình dùng excel 2003 không có hàm sumifs a

=sumproduct(sumifs(indirect("'"&$k$1:$k$2&"'!d2:d5000"),indirect("'"&$k$1:$k$2&"'!b2:b5000"),b2,indirect("'"&$k$1:$k$2&"'!c2:c5000"),c2))

Nếu dùng sumif s thêm dấu nháy vào cũng được. Cũng nên chuyển dần e xcel đời cao hơn bạn ah

 

[_leopard_]

Bạn fill công thức xuống, giá trị thứ 2 thì nó sẽ lấy D3, thứ 3 thì D4....

Ý mình hỏi là không hiểu tại sao hàm Indirect cho mảng {"Sheet1!$D$2:$D$5";"Sheet2!$D$2:$D$5"} thì khi fill xuống như thế nó sẽ tham chiếu theo thứ tự từ D2, rồi D3, D4... Bác có link bài nào về cái này cho mình xin để đọc với

 

[tinh_c3bachuc]

Tôi chưa hiểu công việc bạn lắm, hãy mô tả công việc, dữ liệu nhập vào, xuất ra là gì...
Tư đó mới có ý tưởng giúp bạn.

 

[nguyenluy0902]

Tôi chưa hiểu công việc bạn lắm, hãy mô tả công việc, dữ liệu nhập vào, xuất ra là gì...
Tư đó mới có ý tưởng giúp bạn.

Mình có 1 worksheet, mỗi sheet là lương của toàn Nhà máy trong 1 tháng. Mình cần tổng hợp lương của từng người trong cả năm (hoặc khi cần báo cáo).

 

[Ba Tê]

Mình có 1 worksheet, mỗi sheet là lương của toàn Nhà máy trong 1 tháng. Mình cần tổng hợp lương của từng người trong cả năm (hoặc khi cần báo cáo).

Không biết VBA thì người khác viết dùm.
Muốn xài Hàm thì người khác viết Hàm tự tạo bằng VBA.
Sường rồi còn phải muốn lu bu.
Xem hàm bằng VBA đây. Chỉ cần biết Enable Macros khi mở file là xong.
Trong file chỉ có 1 sheet "Tong", ngoài ra là hàng trăm sheet con để tính tổng, khôngcó sheet "lu bu" khác.

 

[nguyenluy0902]

Không biết VBA thì người khác viết dùm.
Muốn xài Hàm thì người khác viết Hàm tự tạo bằng VBA.
Sường rồi còn phải muốn lu bu.
Xem hàm bằng VBA đây. Chỉ cần biết Enable Macros khi mở file là xong.
Trong file chỉ có 1 sheet "Tong", ngoài ra là hàng trăm sheet con để tính tổng, khôngcó sheet "lu bu" khác.

Cảm ơn bạn rất nhiều nhưng thực sự thì mình chưa biết viết code nên dùng thấy cũng k thoải mái. Bạn có thể cho mình xin ít tài liệu về VBA k? Mình muốn ít nhất là đọc hiểu được code để khi sử dụng có thể chỉnh sửa nữa. Thanks! Mail của mình: nguyenluy0902@gmail.com

 

[Ba Tê]

Cảm ơn bạn rất nhiều nhưng thực sự thì mình chưa biết viết code nên dùng thấy cũng k thoải mái. Bạn có thể cho mình xin ít tài liệu về VBA k? Mình muốn ít nhất là đọc hiểu được code để khi sử dụng có thể chỉnh sửa nữa. Thanks! Mail của mình: nguyenluy0902@gmail.com

Tài liệu tôi chỉ "xin" được 1 quyển này:
http://www.giaiphapexcel.com/forum/...cel-cho-người-mới-bắt-đầu&p=558203#post558203
Bạn liên hệ mua mà nghiên cứu. Tôi cũng chỉ học lóm trên GPE và trong quyển sách đó thôi.
Muốn nâng cao thêm thì mua thêm cuốn này
http://www.giaiphapexcel.com/forum/...hiện-và-tăng-tốc-Năm-2013&p=482430#post482430

 

[nguyenluy0902]

Tài liệu tôi chỉ "xin" được 1 quyển này:
http://www.giaiphapexcel.com/forum/...cel-cho-người-mới-bắt-đầu&p=558203#post558203
Bạn liên hệ mua mà nghiên cứu. Tôi cũng chỉ học lóm trên GPE và trong quyển sách đó thôi.
Muốn nâng cao thêm thì mua thêm cuốn này
http://www.giaiphapexcel.com/forum/...hiện-và-tăng-tốc-Năm-2013&p=482430#post482430

Bạn có thể chú thích đoạn code bạn viết cho mình được không ạ?