Cách rút gọn công thức Sumif x nhiều tỉ lệ allocation (3 người xem)

[anhvan123]

Dear các anh chị
Nhờ các anh chị chỉ giáo rút gọn công thức giúp
Ví dụ ở Sheet Expense Department chi phí phòng F5 có chi phí của riêng nó (ký hiệu là DF5) và 1 số chi phí chung (CCT, CF5, CNM...) với các tỷ lệ phân bổ khác nhau (ở sheet Allocation rate)
Hiện tại em đang để công thưc là sum của chi phí riêng nó + chi phí chung CCT x tỉ lệ phân bổ CCT + chi phí chung CF5 x tỉ lệ phân bổ CF5....(tổng cộng 7 lần sum)
nhưng làm như vậy rất dài
nên hỏi có cao nhân nào hướng dẫn làm gọn lại giúp
em cảm ơn

 

[Phan Thế Hiệp]

Dear các anh chị
Nhờ các anh chị chỉ giáo rút gọn công thức giúp
Ví dụ ở Sheet Expense Department chi phí phòng F5 có chi phí của riêng nó (ký hiệu là DF5) và 1 số chi phí chung (CCT, CF5, CNM...) với các tỷ lệ phân bổ khác nhau (ở sheet Allocation rate)
Hiện tại em đang để công thưc là sum của chi phí riêng nó + chi phí chung CCT x tỉ lệ phân bổ CCT + chi phí chung CF5 x tỉ lệ phân bổ CF5....(tổng cộng 7 lần sum)
nhưng làm như vậy rất dài
nên hỏi có cao nhân nào hướng dẫn làm gọn lại giúp
em cảm ơn

Có phải bạn muốn điều chỉnh công thức tại sheet "Expense direct":

B5=SUMIFS(expenses!C:C,expenses!$B:$B,"D"&$B$1,expenses!$A:$A,$A5)+SUMIFS(expenses!C:C,expenses!$B:$B,"CCT",expenses!$A:$A,$A5)*INDEX('Allocation rate'!$C$4:$CN$24,MATCH($B$1,'Allocation rate'!$A$4:$A$24,0),MATCH(B$4&"cct",'Allocation rate'!$C$1:$CN$1,0))+SUMIFS(expenses!C:C,expenses!$B:$B,"CF5",expenses!$A:$A,$A5) * INDEX('Allocation rate'!$C$4:$CN$24,MATCH($B$1,'Allocation rate'!$A$4:$A$24,0),MATCH(B$4&"CF5",'Allocation rate'!$C$1:$CN$1,0))

Nếu đúng, thử:

B5=SUM(SUMIFS(expenses!C:C,expenses!$B:$B,CHOOSE({1,2,3},"D"&$B$1,"CCT","CF5"),expenses!$A:$A,$A5)*IFERROR(LOOKUP({0,1,2},{1,2,3},N(OFFSET('Allocation rate'!$B$2,MATCH($B$1,'Allocation rate'!$A$3:$A$24,0),{0,26}+MATCH(B$4,'Allocation rate'!$C$3:$N$3,)))),1),)

Kết thúc bằng Ctrl+Shift+Enter.

Thân

 

[anhvan123]

Dạ đúng rồi bạn, có điều công thức mảng hơi phức tạp chút, thanks bạn

Bài đã được tự động gộp: 17/10/18

À bây giờ thí dụ thêm 1 số allocation nữa thì mình chỉnh ở khu vực nào ạ?? Tại công thức mảng hơi khó hiểu?

còn {0,26} là gì ạ??

=SUM(SUMIFS(expenses!E:E,expenses!$B:$B,CHOOSE({1,2,3},"D"&$B$1,"CCT","CF5"),expenses!$A:$A,$A8)*IFERROR(LOOKUP({0,1,2},{1,2,3},N(OFFSET('Allocation rate'!$B$2,MATCH($B$1,'Allocation rate'!$A$3:$A$24,0),{0,26}+MATCH(D$4,'Allocation rate'!$C$3:$N$3,)))),1),)

 

[Phan Thế Hiệp]

Dạ đúng rồi bạn, có điều công thức mảng hơi phức tạp chút, Cảm ơn bạn

Có thể làm gọn thêm chút nữa.

B5=SUMPRODUCT(SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$P$1,expenses!$A:$A,$A5)*(N(OFFSET('Allocation rate'!$B$2,MATCH($B$1,'Allocation rate'!$A$3:$A$24,),{13,0,26}+MATCH(B$4,'Allocation rate'!$C$3:$N$3,)*{0,1,1}))+{1,0,0}))

Chỉ Enter, fill qua phải, rồi fill xuống.

Giải thích:
B5=SUMPRODUCT( SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$P$1,expenses!$A:$A,$A5) * (N(OFFSET('Allocation rate'!$B$2,MATCH($B$1,'Allocation rate'!$A$3:$A$24,),{13,0,26}+MATCH(B$4,'Allocation rate'!$C$3:$N$3,)*{0,1,1}))+{1,0,0}))

1. SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$P$1,expenses!$A:$A,$A5), với N1: P1 là mảng chứa các mục phân bổ của bạn gồm "Df5" "CCT" và "CF5". Nó sẽ trích lục ra chi phí theo khoản mục chi phí và tháng theo ba Phân mục tại N1: P1.
2. (N(OFFSET('Allocation rate'!$B$2,MATCH($B$1,'Allocation rate'!$A$3:$A$24,),{13,0,26}+MATCH(B$4,'Allocation rate'!$C$3:$N$3,)*{0,1,1}))+{1,0,0}). Tìm đến dòng phù hợp theo B1="f5", "nhảy cóc" lấy ra một mảng gồm:
   • Phần tử thứ 1 của mảng luôn bằng 1
   • Phần tử thứ 2 lấy theo tháng của Phân mục "CCT"
   • Phần tử thứ 3 lấy theo tháng của Phân mục "CFN"

Xem file kèm.

Thân

 

[anhvan123]

Cảm ơn anh Hiệp
Ví dụ có thêm 1 số tỉ lệ chung mới thì chỉnh sửa phần bôi đên thôi phải không ạ?
với lại các ,{13,0,26} {0,1,1}))+{1,0,0}) là sao ạ, anh có thể hướng dẫn thêm, em cảm ơn nhiều

=SUMPRODUCT(SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$P$1,expenses!$A:$A,$A5)*(N(OFFSET('Allocation rate'!$B$2,MATCH($B$1,'Allocation rate'!$A$3:$A$24,),{13,0,26}+MATCH(B$4,'Allocation rate'!$C$3:$N$3,)*{0,1,1}))+{1,0,0}))

 

[anhvan123]

Dear anh Hiệp
em có thêm 5 cái tỉ lệ chung nữa, và có chỉnh lại công thức phía dưới mà ra kết quả #n/a

=SUMPRODUCT(SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$U$1,expenses!$A:$A,$A5)*(N(OFFSET('Allocation rate'!$B$2,MATCH($B$1,'Allocation rate'!$A$3:$A$24,),{13,0,26}+MATCH(B$4,'Allocation rate'!$C$3:$N$3,)*{0,1,1,1,1,1,1,1}))+{1,0,0,0,0,0,0,0}))

 

[Phan Thế Hiệp]

Dear anh Hiệp
em có thêm 5 cái tỉ lệ chung nữa, và có chỉnh lại công thức phía dưới mà ra kết quả #n/a

=SUMPRODUCT(SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$U$1,expenses!$A:$A,$A5)*(N(OFFSET('Allocation rate'!$B$2,MATCH($B$1,'Allocation rate'!$A$3:$A$24,),{13,0,26}+MATCH(B$4,'Allocation rate'!$C$3:$N$3,)*{0,1,1,1,1,1,1,1}))+{1,0,0,0,0,0,0,0}))

View attachment 205842

Điều chỉnh thành:

B5=SUMPRODUCT(SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$U$1,expenses!$A:$A,$A5)*(N(OFFSET('Allocation rate'!$B$2,MATCH($B$1,'Allocation rate'!$A$3:$A$24,),(ABS(COLUMN($A:$H)-2)*13)+MATCH(B$4,'Allocation rate'!$C$3:$N$3,)*SIGN(COLUMN($A:$H)-1)))+1-SIGN(COLUMN($A:$H)-1)))

Enter, fill qua phải, rồi fill xuống.

Thân

 

[Phan Thế Hiệp]

Dear anh Hiệp
em có thêm 5 cái tỉ lệ chung nữa, và có chỉnh lại công thức phía dưới mà ra kết quả #n/a

=SUMPRODUCT(SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$U$1,expenses!$A:$A,$A5)*(N(OFFSET('Allocation rate'!$B$2,MATCH($B$1,'Allocation rate'!$A$3:$A$24,),{13,0,26}+MATCH(B$4,'Allocation rate'!$C$3:$N$3,)*{0,1,1,1,1,1,1,1}))+{1,0,0,0,0,0,0,0}))

Gửi cho bạn thêm một 2 công thức nữa, hy vọng cái này sẽ giúp bạn dễ hiểu hơn:
1/ Công thức này sử dụng cách mà anh em tôi gọi đùa là "bước nhảy Kangaroo" (@huonglien1901: em thấy đó, sử dụng đúng bài nó sẽ hiệu quả, cũng từ sản phẩm vọc phá đùa vui với hàm của anh em mình )

B5=SUMPRODUCT(SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$U$1,expenses!$A:$A,$A5)*(SUMIF('Allocation rate'!$A$4:$A$24,$B$1,OFFSET('Allocation rate'!$B$4:$B$24,,$N$2:$U$2+COLUMN(A$1)*$N$3:$U$3))+$N$4:$U$4))

Enter, fill qua phải, rồi fill xuống.

2/ Do thấy bạn sử dụng hàng phụ để kết nối "Tháng" và các 'Đề mục' cho nên bạn có thể tận dụng điều này mà không cần tính toán các bước nhảy như công thức trên:

B5=SUMPRODUCT(SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$U$1,expenses!$A:$A,$A5)*(SUMIF('Allocation rate'!$C$1:$CN$1,B$4&$N$1:$U$1,OFFSET('Allocation rate'!$C$3:$CN$3,MATCH($B$1,'Allocation rate'!$A$4:$A$23,),))+$N$2:$U$2))

Enter, fill qua phải, rồi fill xuống.


Thân

 

[hml89]

Gửi cho bạn thêm một 2 công thức nữa, hy vọng cái này sẽ giúp bạn dễ hiểu hơn:
1/ Công thức này sử dụng cách mà anh em tôi gọi đùa là "bước nhảy Kangaroo" (@huonglien1901: em thấy đó, sử dụng đúng bài nó sẽ hiệu quả, cũng từ sản phẩm vọc phá đùa vui với hàm của anh em mình )

B5=SUMPRODUCT(SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$U$1,expenses!$A:$A,$A5)*(SUMIF('Allocation rate'!$A$4:$A$24,$B$1,OFFSET('Allocation rate'!$B$4:$B$24,,$N$2:$U$2+COLUMN(A$1)*$N$3:$U$3))+$N$4:$U$4))

Enter, fill qua phải, rồi fill xuống.

2/ Do thấy bạn sử dụng hàng phụ để kết nối "Tháng" và các 'Đề mục' cho nên bạn có thể tận dụng điều này mà không cần tính toán các bước nhảy như công thức trên:

B5=SUMPRODUCT(SUMIFS(expenses!C:C,expenses!$B:$B,$N$1:$U$1,expenses!$A:$A,$A5)*(SUMIF('Allocation rate'!$C$1:$CN$1,B$4&$N$1:$U$1,OFFSET('Allocation rate'!$C$3:$CN$3,MATCH($B$1,'Allocation rate'!$A$4:$A$23,),))+$N$2:$U$2))

Enter, fill qua phải, rồi fill xuống.


Thân

Khó quá, tôi không làm được.

 

[Phan Thế Hiệp]

Khó quá, tôi không làm được.

Xem các ví dụ dễ hơn:

1. https://www.giaiphapexcel.com/diendan/threads/Đếm-và-tính-tổng-1-giá-trị-trong-mảng-với-điều-kiện.128987/#post-808978
2. https://www.giaiphapexcel.com/diendan/threads/tÌm-ĐƠn-giÁ-thỎa-mÃn-hai-ĐiỀu-kiỆn.125339/#post-785930
3. https://www.giaiphapexcel.com/diendan/threads/sự-khác-nhau-giữa-hàm-dsum-và-sumifs.124890/page-2#post-782438

Đầu tiên, xem yêu cầu của chủ thớt theo từng bài, sau đó lấy công thức trong file bài giải cùng xem lời giải thích trong các đoạn trao đổi phía dưới.

Thân

 

[hml89]

Xem các ví dụ dễ hơn:

1. https://www.giaiphapexcel.com/diendan/threads/Đếm-và-tính-tổng-1-giá-trị-trong-mảng-với-điều-kiện.128987/#post-808978
2. https://www.giaiphapexcel.com/diendan/threads/tÌm-ĐƠn-giÁ-thỎa-mÃn-hai-ĐiỀu-kiỆn.125339/#post-785930
3. https://www.giaiphapexcel.com/diendan/threads/sự-khác-nhau-giữa-hàm-dsum-và-sumifs.124890/page-2#post-782438

Đầu tiên, xem yêu cầu của chủ thớt theo từng bài, sau đó lấy công thức trong file bài giải cùng xem lời giải thích trong các đoạn trao đổi phía dưới.

Thân

Để tôi luyện tập xem có khá hơn không, cảm giác lúc này, đầu tôi như là báo lỗi "out of Range" ấy. Không biết mô tả như thế nào cho đúng.