Hàm sumifs bị lỗi khi có điều kiện thời gian (2 người xem)

[Denly]

Về tổng hợp tiền lương thu nhập
em có 1 sheet tổng hợp (TH) tiền lương lấy từ sheet thống kê (TK).
Yêu cầu là tổng hợp chi phí tiền lương theo mã đơn vị, chức vụ, danh mục chi, thời gian chi.
Em sử dụng hàm sumproduct thì cho ra kết quả, tuy nhiên khi nhiều dữ liệu và sử dụng nhiều công thức sumproduct thì file rất chậm.
em có thử sử dụng hàm sumifs như khi đưa điều kiện thời gian vào thì bị lỗi (file đính kèm)
Nhờ anh chị và các bạn xem giúp ạ

 

[Phan Thế Hiệp]

Về tổng hợp tiền lương thu nhập
em có 1 sheet tổng hợp (TH) tiền lương lấy từ sheet thống kê (TK).
Yêu cầu là tổng hợp chi phí tiền lương theo mã đơn vị, chức vụ, danh mục chi, thời gian chi.
Em sử dụng hàm sumproduct thì cho ra kết quả, tuy nhiên khi nhiều dữ liệu và sử dụng nhiều công thức sumproduct thì file rất chậm.
em có thử sử dụng hàm sumifs như khi đưa điều kiện thời gian vào thì bị lỗi (file đính kèm)
Nhờ anh chị và các bạn xem giúp ạ

Sheet 'TH hàm sumifs, bạn đã lọc theo từng dòng theo tiêu chí lọc (tại cột B), vd: theo tiêu chí lọc "TL T1", bạn muốn qua sheet 'TK chọn cả cột "TL T1", thì mặc nhiên thời gian cũng đã cố định (gắn liền) với tiêu chí đó rồi, bạn so thêm thời gian thì đâu có tác dụng gì đâu!?

Bạn thử dán công thức sau vào ô C5:

=IF($A5="",IF(C$3="cộng",SUMIF(D$2:$O$2,C$2,D5),SUMIFS(INDEX(TK!$G$5:$AM$15,,MATCH($B5,TK!$G$2:$AM$2,)),TK!$F$5:$F$15,C$2,TK!$E$5:$E$15,C$3)),SUM(OFFSET(C6,,,IFERROR(MATCH("*",$A6:$A$40,)-1,100))))

fill qua phải, rồi fill xuống dưới. Tự định dạng (tô màu và chữ đậm) lại tùy bạn muốn.

Thân

 

[Denly]

Sheet 'TH hàm sumifs, bạn đã lọc theo từng dòng theo tiêu chí lọc (tại cột B), vd: theo tiêu chí lọc "TL T1", bạn muốn qua sheet 'TK chọn cả cột "TL T1", thì mặc nhiên thời gian cũng đã cố định (gắn liền) với tiêu chí đó rồi, bạn so thêm thời gian thì đâu có tác dụng gì đâu!?

Bạn thử dán công thức sau vào ô C5:

=IF($A5="",IF(C$3="cộng",SUMIF(D$2:$O$2,C$2,D5),SUMIFS(INDEX(TK!$G$5:$AM$15,,MATCH($B5,TK!$G$2:$AM$2,)),TK!$F$5:$F$15,C$2,TK!$E$5:$E$15,C$3)),SUM(OFFSET(C6,,,IFERROR(MATCH("*",$A6:$A$40,)-1,100))))

fill qua phải, rồi fill xuống dưới. Tự định dạng (tô màu và chữ đậm) lại tùy bạn muốn.

Thân

Như vậy nếu muốn tổng hợp số liệu theo khoảng thời gian bất kỳ (ví dụ từ tháng 01/1 đến 31/3) thì lại không tính được ạ

 

[Phan Thế Hiệp]

Như vậy nếu muốn tổng hợp số liệu theo khoảng thời gian bất kỳ (ví dụ từ tháng 01/1 đến 31/3) thì lại ko tính đc ạ

Bài trên viết nguyên chữ được, bài dưới lại lồi ra "ko" với "đc" nữa rồi!

 

[Hoàng Tuấn 868]

Bài trên viết nguyên chữ được, bài dưới lại lồi ra "ko" với "đc" nữa rồi!

Như vậy là tự giảm khả năng được hỗ trợ đó anh.

 

[Denly]

Bài trên viết nguyên chữ được, bài dưới lại lồi ra "ko" với "đc" nữa rồi!

Cảm ơn bác ạ nhắc, em quen nhắn tin nên quên ạ

 

[Phan Thế Hiệp]

Cảm ơn bác ạ nhắc, em quen nhắn tin nên quên ạ

Bạn cầu thị, anh em sẵn lòng hỗ trợ

Công thức (4 trong 1) tại C5:

=IF($A5="",IF(C$3="cộng",SUMIF(D$2:$O$2,C$2,D5),IF(OR(($B5=TK!$G$2:$AM$2)*TEXT(INDEX(TK!$G$3:$AM$4,MATCH($B$1,TK!$C$3:$C$4,),),"[<"&$C$1&"]\0;[>"&$D$1&"]\0;\1")),SUMIFS(INDEX(TK!$G$5:$AM$15,,MATCH($B5,TK!$G$2:$AM$2,)),TK!$F$5:$F$15,C$2,TK!$E$5:$E$15,C$3),)),SUM(OFFSET(C6,,,IFERROR(MATCH("*",$A6:$A$40,)-1,100))))

fill qua phải, rồi fill xuống dưới.

Thân

 

[Denly]

Bạn cầu thị, anh em sẵn lòng hỗ trợ

Công thức (4 trong 1) tại C5:

=IF($A5="",IF(C$3="cộng",SUMIF(D$2:$O$2,C$2,D5),IF(OR(($B5=TK!$G$2:$AM$2)*TEXT(INDEX(TK!$G$3:$AM$4,MATCH($B$1,TK!$C$3:$C$4,),),"[<"&$C$1&"]\0;[>"&$D$1&"]\0;\1")),SUMIFS(INDEX(TK!$G$5:$AM$15,,MATCH($B5,TK!$G$2:$AM$2,)),TK!$F$5:$F$15,C$2,TK!$E$5:$E$15,C$3),)),SUM(OFFSET(C6,,,IFERROR(MATCH("*",$A6:$A$40,)-1,100))))

fill qua phải, rồi fill xuống dưới.

Thân

Cảm ơn bác rất nhiều, em chưa hiểu công thức bác viết cho nhưng sẽ cố gắng tìm hiểu. Nếu chỗ nào chưa hiểu mong bác giúp đỡ tiếp ạ

 

[Denly]

Bạn cầu thị, anh em sẵn lòng hỗ trợ

Công thức (4 trong 1) tại C5:

=IF($A5="",IF(C$3="cộng",SUMIF(D$2:$O$2,C$2,D5),IF(OR(($B5=TK!$G$2:$AM$2)*TEXT(INDEX(TK!$G$3:$AM$4,MATCH($B$1,TK!$C$3:$C$4,),),"[<"&$C$1&"]\0;[>"&$D$1&"]\0;\1")),SUMIFS(INDEX(TK!$G$5:$AM$15,,MATCH($B5,TK!$G$2:$AM$2,)),TK!$F$5:$F$15,C$2,TK!$E$5:$E$15,C$3),)),SUM(OFFSET(C6,,,IFERROR(MATCH("*",$A6:$A$40,)-1,100))))

fill qua phải, rồi fill xuống dưới.

Thân

Có 1 số chỗ em chưa hiểu, nếu được bác cho xin thông tin liên hệ hoặc bác có thể nhắn qua zalo số này 0943553311 giúp em. em xin hỏi nhờ 1 chút về công thức ạ. Cảm ơn bác

 

[Phan Thế Hiệp]

Có 1 số chỗ em chưa hiểu, nếu được bác cho xin thông tin liên hệ hoặc bác có thể nhắn qua zalo số này 0943553311 giúp em. em xin hỏi nhờ 1 chút về công thức ạ. Cảm ơn bác

Bạn cứ nêu thắc mắc của mình lên diễn đàn, đừng ngại. Không chỉ mình tôi, mà bạn sẽ còn được nhiều anh em khác kiến thức hơn tôi, trả lời giúp bạn hiểu vấn đề bạn thắc mắc một cách thấu đáo hơn.

Thân

 

[Denly]

Bạn cứ nêu thắc mắc của mình lên diễn đàn, đừng ngại. Không chỉ mình tôi, mà bạn sẽ còn được nhiều anh em khác kiến thức hơn tôi, trả lời giúp bạn hiểu vấn đề bạn thắc mắc một cách thấu đáo hơn.

Thân

Vâng, khi em áp dụng công thức bác làm cho bên bảng tổng hợp thì khi có 2 giá trị thỏa mãn điều kiện (ví dụ TL T3), công thức chỉ lấy kết quả đầu tiên, chứ không cộng các giá trị đáp ứng điều kiện ạ.
Nhờ bác xem giúp ạ

 

[Phan Thế Hiệp]

Vâng, khi em áp dụng công thức bác làm cho bên bảng tổng hợp thì khi có 2 giá trị thỏa mãn điều kiện (ví dụ TL T3), công thức chỉ lấy kết quả đầu tiên, chứ không cộng các giá trị đáp ứng điều kiện ạ.
Nhờ bác xem giúp ạ

Tiền lương trong tháng mà phát 2, 3 hoặc nhiều lần hả bạn? Việc này thì tôi mới biết và cũng lạ nữa! Nếu có trường hợp này xảy ra tại Cty của bạn, thì theo tôi bạn nên dùng công thức Sumproduct() mà bạn đã thực hiện, nó sẽ gọn gàng hơn.

Nhưng nếu bạn vẫn muốn thử sức với hàm Sumifs(), thì chìu bạn vậy. Với tôi thì làm chơi cho vui chứ không thích công thức này đâu: vẫn ô C5

=IF($A5="",IF(C$3="cộng",SUMIF(D$2:$O$2,C$2,D5),IF(OR(($B5=TK!$G$2:$AR$2)*TEXT(INDEX(TK!$G$3:$AR$4,MATCH($B$1,TK!$C$3:$C$4,),),"[<"&$C$1&"]\0;[>"&$D$1&"]\0;\1")),SUM(SUMIFS(OFFSET(TK!$A$5,,AGGREGATE(15,6,COLUMN(TK!$G:$AR)/($B5=TK!$G$2:$AR$2),COLUMN(OFFSET($A$1,,,,COUNTIF(TK!$G$2:$AR$2,$B5))))-1,11),TK!$F$5:$F$15,C$2,TK!$E$5:$E$15,C$3)),)),SUM(OFFSET(C6,,,IFERROR(MATCH("*",$A6:$A$40,)-1,100))))

Thân

 

[Denly]

Tiền lương trong tháng mà phát 2, 3 hoặc nhiều lần hả bạn? Việc này thì tôi mới biết và cũng lạ nữa! Nếu có trường hợp này xảy ra tại Cty của bạn, thì theo tôi bạn nên dùng công thức Sumproduct() mà bạn đã thực hiện, nó sẽ gọn gàng hơn.

Nhưng nếu bạn vẫn muốn thử sức với hàm Sumifs(), thì chìu bạn vậy. Với tôi thì làm chơi cho vui chứ không thích công thức này đâu: vẫn ô C5

=IF($A5="",IF(C$3="cộng",SUMIF(D$2:$O$2,C$2,D5),IF(OR(($B5=TK!$G$2:$AR$2)*TEXT(INDEX(TK!$G$3:$AR$4,MATCH($B$1,TK!$C$3:$C$4,),),"[<"&$C$1&"]\0;[>"&$D$1&"]\0;\1")),SUM(SUMIFS(OFFSET(TK!$A$5,,AGGREGATE(15,6,COLUMN(TK!$G:$AR)/($B5=TK!$G$2:$AR$2),COLUMN(OFFSET($A$1,,,,COUNTIF(TK!$G$2:$AR$2,$B5))))-1,11),TK!$F$5:$F$15,C$2,TK!$E$5:$E$15,C$3)),)),SUM(OFFSET(C6,,,IFERROR(MATCH("*",$A6:$A$40,)-1,100))))

Thân

Ý là cũng là chi lương tháng 3 nhưng có đơn vị thực chi trong tháng 3, có đơn vị thực chi trong tháng 4 ạ. Công thức sumproduct em có thử khi sử dụng nhiều thì file tổng hợp bị lag ạ.
Bác xem giúp em: Khi em lựa chọn tổng hợp theo "Thực chi" và thời gian tổng hợp từ 1/1/2024 đến 31/3/2024 thì nó vẫn tổng hợp các các khoản chi (T3) thực chi trong tháng 4 ạ.

 

[Phan Thế Hiệp]

Ý là cũng là chi lương tháng 3 nhưng có đơn vị thực chi trong tháng 3, có đơn vị thực chi trong tháng 4 ạ. Công thức sumproduct em có thử khi sử dụng nhiều thì file tổng hợp bị lag ạ.
Bác xem giúp em: Khi em lựa chọn tổng hợp theo "Thực chi" và thời gian tổng hợp từ 1/1/2024 đến 31/3/2024 thì nó vẫn tổng hợp các các khoản chi (T3) thực chi trong tháng 4 ạ.

Điều chỉnh lại điều kiện của nó cho phù hợp với tình trạng phát lương nhiều lần: ô C5

=IF($A5="",IF(C$3="cộng",SUMIF(D$2:$O$2,C$2,D5),SUM(IFERROR(SUMIFS(OFFSET(TK!$A$5,,AGGREGATE(15,6,COLUMN(TK!$G:$AR)/($B5=TK!$G$2:$AR$2)/TEXT(INDEX(TK!$G$3:$AR$4,MATCH($B$1,TK!$C$3:$C$4,),),"[<"&$C$1&"]\0;[>"&$D$1&"]\0;\1"),COLUMN(OFFSET($A$1,,,,COUNTIF(TK!$G$2:$AR$2,$B5))))-1,11),TK!$F$5:$F$15,C$2,TK!$E$5:$E$15,C$3),))),SUM(C6:INDEX(C6:C$40,IFERROR(MATCH("*",$A6:$A$40,)-1,))))

Việc 'lag' hay không bạn tự kiểm tra nha, chứ theo tôi khi dữ liệu nhiều nó cũng không khá hơn bao nhiêu đâu, trừ phi bạn nhờ anh em khác hỗ trợ bằng VBA.

Thân

 

[VetMini]

Cảm ơn bác ạ nhắc, em quen nhắn tin nên quên ạ

Bạn là người hỏi bài. Bạn phải tự biết đặt mình ở vị trí không thể quên.
Đem câu hỏi ra mà quên chỗ này, thiếu chỗ nọ chỉ tổ nhận được đáp án sai thôi.

 

[Denly]

Điều chỉnh lại điều kiện của nó cho phù hợp với tình trạng phát lương nhiều lần: ô C5

=IF($A5="",IF(C$3="cộng",SUMIF(D$2:$O$2,C$2,D5),SUM(IFERROR(SUMIFS(OFFSET(TK!$A$5,,AGGREGATE(15,6,COLUMN(TK!$G:$AR)/($B5=TK!$G$2:$AR$2)/TEXT(INDEX(TK!$G$3:$AR$4,MATCH($B$1,TK!$C$3:$C$4,),),"[<"&$C$1&"]\0;[>"&$D$1&"]\0;\1"),COLUMN(OFFSET($A$1,,,,COUNTIF(TK!$G$2:$AR$2,$B5))))-1,11),TK!$F$5:$F$15,C$2,TK!$E$5:$E$15,C$3),))),SUM(C6:INDEX(C6:C$40,IFERROR(MATCH("*",$A6:$A$40,)-1,))))

Việc 'lag' hay không bạn tự kiểm tra nha, chứ theo tôi khi dữ liệu nhiều nó cũng không khá hơn bao nhiêu đâu, trừ phi bạn nhờ anh em khác hỗ trợ bằng VBA.

Thân

Bác cho em hỏi nhờ, sao trong sheet "TK" em cứ thêm dòng là sheet tổng hợp không ra kết quả nữa ạ?
em cảm ơn bác đã giúp đỡ ạ

 

[Denly]

Bạn cứ nêu thắc mắc của mình lên diễn đàn, đừng ngại. Không chỉ mình tôi, mà bạn sẽ còn được nhiều anh em khác kiến thức hơn tôi, trả lời giúp bạn hiểu vấn đề bạn thắc mắc một cách thấu đáo hơn.

Thân

Bác cho em hỏi nhờ, sao trong sheet "TK" em cứ thêm dòng là sheet tổng hợp không ra kết quả nữa ạ?
em cảm ơn bác đã giúp đỡ ạ

em có thêm hàm rows vào để công thức cập nhật số dòng.
Nếu được bác có thể giải thích giúp em công thức đoạn này ạ "SUMIFS(OFFSET(TK!$A$5,,AGGREGATE(15,6,COLUMN(TK!$G:$AR)/($B5=TK!$G$2:$AR$2)/TEXT(INDEX(TK!$G$3:$AR$4,MATCH($B$1,TK!$C$3:$C$4,),),"[<"&$C$1&"]\0;[>"&$D$1&"]\0;\1"),COLUMN(OFFSET($A$1,,,,COUNTIF(TK!$G$2:$AR$2,$B5))))-1,11)"