Help, cần bạn hiểu về hàm sumif giúp ạ (6 người xem)

[antet83]

Nhờ mọi người xem giúp em tại sao số liệu em lại bị lệch:
Tổng nợ 1121.01 là: 4,564,541,264 đồng
Bảng cân đối số phát sinh: 9.688.958.712 đồng.
Lệch: 3.678.235.013.
Nhờ các cao thủ giúp em ạ. Em cảm ơn ạ.

​

 

[leonguyenz]

Bạn đang Filter sheet PHATSINH thời gian trong năm 2018, SUBTOTAL tính các dòng hiển thị, còn lại đang ẩn thì không tính.

 

[tieuthubuongbinh]

@leonguyenz
Giúp em bài này với, em ko giỏi hàm sumproduct nên loay hoay quá
kết quả là sumifs(T1,dò region, dò code)+sumif(T2, dò region, dò code)

 

[leonguyenz]

@leonguyenz
Giúp em bài này với, em ko giỏi hàm sumproduct nên loay hoay quá
kết quả là sumifs(T1,dò region, dò code)+sumif(T2, dò region, dò code)

Công thức sau nhé:

=SUMPRODUCT(($A$2:$A$49=$L$1)*($D$2:$D$49=K2),$B$2:$B$49+$C$2:$C$49)

 

[tieuthubuongbinh]

Công thức sau nhé:

=SUMPRODUCT(($A$2:$A$49=$L$1)*($D$2:$D$49=K2),$B$2:$B$49+$C$2:$C$49)

Anh ơi, nếu em có nhiều đk dò tìm hơn (vd 5 đk), và nhiều cột tính tổng hơn (vd 10 cột) thì sao?

Nếu nhiều đk dò tìm, mà chỉ 1 cột tính thì em làm sumproduct(sumifs(...)) thành công, mà có nhiều cột tính thì ko biết sao luôn

 

[leonguyenz]

Anh ơi, nếu em có nhiều đk dò tìm hơn (vd 5 đk), và nhiều cột tính tổng hơn (vd 10 cột) thì sao?

Nếu nhiều đk dò tìm, mà chỉ 1 cột tính thì em làm sumproduct(sumifs(...)) thành công, mà có nhiều cột tính thì ko biết sao luôn

Nếu vậy làm 2 cột phụ: cột điều kiện là nối các điều kiện với nhau, cột tính tổng
Sau đó SUMIF.
Thế có khỏe hơn không.

 

[tieuthubuongbinh]

Em chưa hiểu. Em làm file giả lập rồi nè, nhờ anh viết CT cho mảng xanh lá giùm em nha. Và làm vd cho cái anh nói dưới này luôn

Nếu vậy làm 2 cột phụ: cột điều kiện là nối các điều kiện với nhau, cột tính tổng
Sau đó SUMIF.
Thế có khỏe hơn không.

 

[leonguyenz]

Em chưa hiểu. Em làm file giả lập rồi nè, nhờ anh viết CT cho mảng xanh lá giùm em nha. Và làm vd cho cái anh nói dưới này luôn

Làm 2 cột phụ:
I6 =C6&D6&B6
J6 =SUM(F6:H6)
Tổng theo điều kiện: =SUMIF($I$6:$I$20,L6&M6&N6,$J$6:$J$20)

 

[LamNA]

Em chưa hiểu. Em làm file giả lập rồi nè, nhờ anh viết CT cho mảng xanh lá giùm em nha. Và làm vd cho cái anh nói dưới này luôn

Bạn thử mà cố gắng tập trung nha

=INDEX($F$5:$F$20+$G$5:$G$20+$H$5:$H$20,MATCH(1,INDEX((J6=$C$5:$C$20)*(K6=$D$5:$D$20)*(L6=$B$5:$B$20),0),0))

 

[tieuthubuongbinh]

Làm 2 cột phụ:
I6 =C6&D6&B6
J6 =SUM(F6:H6)
Tổng theo điều kiện: =SUMIF($I$6:$I$20,L6&M6&N6,$J$6:$J$20)

Anh giúp em viết CT cho M6 và N6 với.
M6 lọc theo L6 và lấy ngày gần với today() nhất khi bị trùng
N6 em nghĩ là dùng lookup, mà dò tìm 2 đk bằng lookup em chưa học

Bài đã được tự động gộp: 11/4/19

Bạn thử mà cố gắng tập trung nha

=INDEX($F$5:$F$20+$G$5:$G$20+$H$5:$H$20,MATCH(1,INDEX((J6=$C$5:$C$20)*(K6=$D$5:$D$20)*(L6=$B$5:$B$20),0),0))

Với cách này thì mình làm cách bài 4 đỡ đau đầu hơn ko nè hihi. Dù sao cũng cám ơn bạn, mình học được thêm kiểu nhân nhau trong index.
Trình độ của mình đã nâng tầm từ đọc ko hiểu đã thành đọc và hiểu, nhưng ko tự nghĩ ra đc hihi

 

[leonguyenz]

Anh giúp em viết CT cho M6 và N6 với.
M6 lọc theo L6 và lấy ngày gần với today() nhất khi bị trùng
N6 em nghĩ là dùng lookup, mà dò tìm 2 đk bằng lookup em chưa học

Dò lấy ngày gần nhất, M6 (CSE):

=INDEX($D$6:$D$20,MATCH(MIN(IF($C$6:$C$20=L6,ABS(TODAY()-$D$6:$D$20),"")),IF($C$6:$C$20=L6,ABS(TODAY()-$D$6:$D$20),""), 0))

LOOKUP 2 điều kiện: =Lookup(2,1/dk1/dk2,mảng trả về)

 

[Phan Thế Hiệp]

Em chưa hiểu. Em làm file giả lập rồi nè, nhờ anh viết CT cho mảng xanh lá giùm em nha. Và làm vd cho cái anh nói dưới này luôn

Đã học MMULT() rồi mà:

M6=LOOKUP(2,1/(J6=$C$6:$C$20)/(K6=$D$6:$D$20)/(L6=$B$6:$B$20),MMULT($F$6:$H$20,{1;1;1}))

Nếu nhiều cột hơn dùng ROW(n:m)^0, Vd cần 12 cột dùng ROW(1:12)^0.

Thân

 

[tieuthubuongbinh]

@leonguyenz cám ơn anh lắm lắm, sao thấy CT cũng đơn giản mà em nghĩ ko ra, cứ hàm dính liếu đến min, max, rank là em lọng cọng.

Đã học MMULT() rồi mà:

M6=LOOKUP(2,1/(J6=$C$6:$C$20)/(K6=$D$6:$D$20)/(L6=$B$6:$B$20),MMULT($F$6:$H$20,{1;1;1}))

Nếu nhiều cột hơn dùng ROW(n:m)^0, Vd cần 12 cột dùng ROW(1:12)^0.

Thân

Hihi, em chỉ nghĩ lookup là dò tìm, em ko biết là nó có thể trả vùng tìm kiếm mà có thể tính tổng.

1. Anh giải thích giúp em khi nào thì [điều kiện 1 / điều kiện 2 / điều kiện 3], khi nào thì [điều kiện 1 * điều kiện 2 * điều kiện 3] (như bài 9)
2. Bài sumif voi sum_range: em thử thêm cách mmult giống bài loc ngay lon nhat. Mà sao kết quả lại khác anh nhỉ.

L2 =LOOKUP(2,1/($A$2:$A$49=L$1)/($D$2:$D$49=$K2),MMULT($B$2:$C$49,{1;1})) = 1040
L2 =SUM(SUMIFS(OFFSET($A$2:$A$49,,{1,2}),$D$2:$D$49,K2,$A$2:$A$49,L$1)) = 2556

Em bắt đầu hiểu nhiều hơn khi kết hợp mảng trong hàm rồi.
3. Có thể làm mảng để ghép nhiều chuỗi với nhau ko anh? vd vlookup(A1&B1,table array cũng hiểu là A2:A3&C2:C3 : D2 : D3,2,0)
Em hỏi để xem cái kiểu suy nghĩ của em có cách giải ko thôi, chứ giờ em nghỉ chơi vlookup rồi hihi, làm lookup cho xong.

 

[Phan Thế Hiệp]

@leonguyenz cám ơn anh lắm lắm, sao thấy CT cũng đơn giản mà em nghĩ ko ra, cứ hàm dính liếu đến min, max, rank là em lọng cọng.


Hihi, em chỉ nghĩ lookup là dò tìm, em ko biết là nó có thể trả vùng tìm kiếm mà có thể tính tổng.

1. Anh giải thích giúp em khi nào thì [điều kiện 1 / điều kiện 2 / điều kiện 3], khi nào thì [điều kiện 1 * điều kiện 2 * điều kiện 3] (như bài 9)
2. Bài sumif voi sum_range: em thử thêm cách mmult giống bài loc ngay lon nhat. Mà sao kết quả lại khác anh nhỉ.

L2 =LOOKUP(2,1/($A$2:$A$49=L$1)/($D$2:$D$49=$K2),MMULT($B$2:$C$49,{1;1})) = 1040
L2 =SUM(SUMIFS(OFFSET($A$2:$A$49,,{1,2}),$D$2:$D$49,K2,$A$2:$A$49,L$1)) = 2556

Em bắt đầu hiểu nhiều hơn khi kết hợp mảng trong hàm rồi.
3. Có thể làm mảng để ghép nhiều chuỗi với nhau ko anh? vd vlookup(A1&B1,table array cũng hiểu là A2:A3&C2:C3 : D2 : D3,2,0)
Em hỏi để xem cái kiểu suy nghĩ của em có cách giải ko thôi, chứ giờ em nghỉ chơi vlookup rồi hihi, làm lookup cho xong.

Một bài toán có nhiều cách giải, ăn thua mình có biết chức năng của nhiều hàm hay không thôi!.
Ví dụ:

• Bài #3: có thể dùng trọn chức năng của MMULT, như sau:

  L2=SUM(MMULT(($D$2:$D$49=K2)*($A$2:$A$49=L$1)*$B$2:$C$49,{1;1}))

  Chỉ Enter fill xuống. Nếu nhiều cột hơn thì sau khi điều chỉnh lại vùng cần cộng $B$2:$C$49 theo thực tế, thì điều chỉnh mảng {1;1} bằng hàm ROW(n:m)^0, tức thực tế có m cột cần cộng, thì dùng ROW(1:m)^0.
• Tương tự, bài #7, nếu cần phải cộng cho những mã cùng điều kiện nhưng phát sinh ở nhiều dòng, thì dùng:

  M6=SUM(MMULT((J6=$C$6:$C$20)*(K6=$D$6:$D$20)*(L6=$B$6:$B$20)*$F$6:$H$20,{1;1;1}))

  Chỉ Enter, fill xuống.
• LOOKUP(2,1/,...) chỉ trích lục duy nhất cho dữ liệu khớp với điều kiện, và xuất hiện sau cuối.

Thân

 

[tieuthubuongbinh]

Một bài toán có nhiều cách giải, ăn thua mình có biết chức năng của nhiều hàm hay không thôi!.

Thân

Vì em học ko có hệ thống nên em góp nhặt từng chút một hihi. Những người viết ra excel thật tuyệt vời và những chân truyền trong GPE cũng rất tuyệt vời, chân thành cảm ơn các tiền bối trực tiếp và gián tiếp cho em những kiến thức hay.

 

[LamNA]

Vì em học ko có hệ thống nên em góp nhặt từng chút một hihi. Những người viết ra excel thật tuyệt vời và những chân truyền trong GPE cũng rất tuyệt vời, chân thành cảm ơn các tiền bối trực tiếp và gián tiếp cho em những kiến thức hay.

Các kỹ thuật viên excel đọc được nội dung này chắc mừng lắm, vẫn có người thầm lặng cám ơn.