Sumproduct để dò tìm nhiều giá trị và tính toán

[tieuthubuongbinh]

Cha`o ACE,

Nho* ACE giu'p CT sumproduct cho o^C13

 

[leoheocon]

Cha`o ACE,

Nho* ACE giu'p CT sumproduct cho o^C13

Bạn
CT này ở C13 xem:

C13=OFFSET($A$4,MATCH($B13,$A$5:$A$8,),MATCH(C$12,$B$4:$M$4,))*OFFSET($A$4,MATCH($B13,$A$5:$A$8,),MATCH($A13,$B$4:$M$4,))

Fill sang phải, fill xuống!!!

 

[quocgiacan]

Bạn
CT này ở C13 xem:

C13=OFFSET($A$4,MATCH($B13,$A$5:$A$8,),MATCH(C$12,$B$4:$M$4,))*OFFSET($A$4,MATCH($B13,$A$5:$A$8,),MATCH($A13,$B$4:$M$4,))

Fill sang phải, fill xuống!!!

Góp thêm 2 công thức nữa với em, để chủ topic tham khảo cho vui:

C13=SUMPRODUCT(MAX(($A$5:$A$8=$B13)*($B$4:$G$4=$A13)*$B$5:$G$8),MAX(($A$5:$A$8=$B13)*($I$4:$M$4=C$12)*($I$5:$M$8)))

C13=VLOOKUP($B13,$A$5:$G$8,MATCH($A13,$A$4:$G$4,0),0)*HLOOKUP(C$12,$I$4:$M$8,MATCH($B13,$A$4:$A$8,0),0)

Chúc em ngày vui.

 

[Ba Tê]

Cha`o ACE,

Nho* ACE giu'p CT sumproduct cho o^C13

Nếu bạn chỉ khoái SUMPRODUCT thì vầy:

=SUMPRODUCT(($A$5:$A$8=$B13)*($B$4:$G$4=$A13)*($B$5:$G$8))*SUMPRODUCT(($A$5:$A$8=$B13)*($I$4:$M$4=C$12)*($I$5:$M$8))

 

[tieuthubuongbinh]

Góp thêm 2 công thức nữa với em, để chủ topic tham khảo cho vui:

C13=SUMPRODUCT(MAX(($A$5:$A$8=$B13)*($B$4:$G$4=$A13)*$B$5:$G$8),MAX(($A$5:$A$8=$B13)*($I$4:$M$4=C$12)*($I$5:$M$8)))

C13=VLOOKUP($B13,$A$5:$G$8,MATCH($A13,$A$4:$G$4,0),0)*HLOOKUP(C$12,$I$4:$M$8,MATCH($B13,$A$4:$A$8,0),0)

Em bị báo lỗi khi sd sumproduct, lỗi này là sao ạ?
Em dùng cho file giả lập thì được nhưng đưa vào file thật rất nhiều cột và có cột trống như file đính kèm (N4 là cột trống do có tháng ngày trải dài trong 6 tuần - row 3 là số ngày sales trong tuần, row 4 là số tuần trong năm, nên số tuần này có thể bị lập lại như M4 và O4, em để cột trống để tạo range luôn là 6 cột cho 1 tháng để khi kéo CT không phải chỉnh lại).

Em không dùng Vlookup được vì file thật phần % nó cách rất xa với cột có giá trị dò tìm (em add thêm B4 many cols để diễn tả việc phần data nó cách mấy chục cột so với A4

Hàm offset em chưa rành lắm nên không hiểu cách sd, do file thật còn nhiều cột chèn giữa nên em không biết cách dùng

Excel ran out of resources while attempting to calculate one or more formulars. As a result, these formulars cannot be evaluated

 

[quocgiacan]

Em bị báo lỗi khi sd sumproduct, lỗi này là sao ạ?
Em dùng cho file giả lập thì được nhưng đưa vào file thật rất nhiều cột và có cột trống như file đính kèm (N4 là cột trống do có tháng ngày trải dài trong 6 tuần - row 3 là số ngày sales trong tuần, row 4 là số tuần trong năm, nên số tuần này có thể bị lập lại như M4 và O4, em để cột trống để tạo range luôn là 6 cột cho 1 tháng để khi kéo CT không phải chỉnh lại).

Em không dùng Vlookup được vì file thật phần % nó cách rất xa với cột có giá trị dò tìm (em add thêm B4 many cols để diễn tả việc phần data nó cách mấy chục cột so với A4

Hàm offset em chưa rành lắm nên không hiểu cách sd, do file thật còn nhiều cột chèn giữa nên em không biết cách dùng

Excel ran out of resources while attempting to calculate one or more formulars. As a result, these formulars cannot be evaluated

Bạn xem file kèm tôi đã gom hết các công thức phía trân vào chung một sheet cho bạn dễ tham khảo.

Chúc bạn ngày vui.

 

[Ba Tê]

Em bị báo lỗi khi sd sumproduct, lỗi này là sao ạ?
Em dùng cho file giả lập thì được nhưng đưa vào file thật rất nhiều cột và có cột trống như file đính kèm (N4 là cột trống do có tháng ngày trải dài trong 6 tuần - row 3 là số ngày sales trong tuần, row 4 là số tuần trong năm, nên số tuần này có thể bị lập lại như M4 và O4, em để cột trống để tạo range luôn là 6 cột cho 1 tháng để khi kéo CT không phải chỉnh lại).

Em không dùng Vlookup được vì file thật phần % nó cách rất xa với cột có giá trị dò tìm (em add thêm B4 many cols để diễn tả việc phần data nó cách mấy chục cột so với A4

Hàm offset em chưa rành lắm nên không hiểu cách sd, do file thật còn nhiều cột chèn giữa nên em không biết cách dùng

Excel ran out of resources while attempting to calculate one or more formulars. As a result, these formulars cannot be evaluated

Nhìn dữ liệu của bạn chắc J2="SEP", P2="OCT" và còn nữa......
Điều kiện này sẽ được tính theo cái bảng dưới bằng ô C10 ?
File này làm theo cách hiểu như vậy. Bạn thay đổi ô C10 bằng "SEP", "OCT" .... sẽ có kết quả cho bạn tham khảo.
Chú ý là có 2 Name: CoL và GPE

 

[quocgiacan]

...............
Em dùng cho file giả lập thì được nhưng đưa vào file thật rất nhiều cột và có cột trống như file đính kèm (N4 là cột trống do có tháng ngày trải dài trong 6 tuần - row 3 là số ngày sales trong tuần, row 4 là số tuần trong năm, nên số tuần này có thể bị lập lại như M4 và O4, em để cột trống để tạo range luôn là 6 cột cho 1 tháng để khi kéo CT không phải chỉnh lại).......

Chào tieuthubuongbinh,

Tôi có chút gợi ý cho bạn để bạn dễ thực hiện và tổng kết hơn:
1. Tạo 2 sheet chứa các bảng điều kiện
2. Chỉ cần thay đổi tháng thì công thức tự động truy tìm ra kết quả theo tháng.

Bạn tham khảo file kèm tôi sử dụng theo công thức của Anh Ba Tê.

Chúc bạn ngày vui.

 

[anhtuan2012]

Góp thêm 2 công thức nữa với em, để chủ topic tham khảo cho vui:

C13=SUMPRODUCT(MAX(($A$5:$A$8=$B13)*($B$4:$G$4=$A13)*$B$5:$G$8),MAX(($A$5:$A$8=$B13)*($I$4:$M$4=C$12)*($I$5:$M$8)))

C13=VLOOKUP($B13,$A$5:$G$8,MATCH($A13,$A$4:$G$4,0),0)*HLOOKUP(C$12,$I$4:$M$8,MATCH($B13,$A$4:$A$8,0),0)

Chúc em ngày vui.

Chỉ là tìm kiếm thôi mà các anh lỡ lòng nào bỏ rơi thằng em "INDEX"
=INDEX($I$5:$M$8,MATCH($B13,$A$5:$A$8,),MATCH(C$12,$I$4:$M$4,))*INDEX($B$5:$G$8,MATCH($B13,$A$5:$A$8,),MATCH($A13,$B$4:$G$4,))

 

[tieuthubuongbinh]

Chào tieuthubuongbinh,

Tôi có chút gợi ý cho bạn để bạn dễ thực hiện và tổng kết hơn:
1. Tạo 2 sheet chứa các bảng điều kiện
2. Chỉ cần thay đổi tháng thì công thức tự động truy tìm ra kết quả theo tháng.

Bạn tham khảo file kèm tôi sử dụng theo công thức của Anh Ba Tê.

Chúc bạn ngày vui.

Cám ơn bác. Nhưng em đổi tháng thì row 2 không thay đổi, bác cài giúp em luôn với ạ

Em vẫn chưa áp dụng vào file thật được vì dữ liệu thật phức tạp về cột và cách trình bày không cho phép tách sheet nhưng em đã học được cách xử lý mới, cám ơn các bác rất nhiều

 

[quocgiacan]

Cám ơn bác. Nhưng em đổi tháng thì row 2 không thay đổi, bác cài giúp em luôn với ạ

Em vẫn chưa áp dụng vào file thật được vì dữ liệu thật phức tạp về cột và cách trình bày không cho phép tách sheet nhưng em đã học được cách xử lý mới, cám ơn các bác rất nhiều

Chào tieuthubuongbinh,

Hoặc theo hướng anh Ba Tê gợi ý (phù hợp với kết cấu hiện nay của file bạn gửi), hoặc nếu có thể điều chình lại kết cấu thì theo hướng tôi gợi ý.

Bạn cứ đem file dữ liệu thật đã bỏ bớt những sheet không dính dấp để anh em xem thử mới có thể góp ý hoặc hỗ trợ bạn tốt hơn.

Chúc bạn ngày vui.

 

[tieuthubuongbinh]

Chào các bác,

Em có file đang dung sumproduct cho nhiều sheet, nhờ các bác tạo giúp em CT sao cho

1/ tự tìm sheet theo cột B.
Vd: D6=SUMPRODUCT(A!$M$6:$M$12*A!U$6:U$12*(A!$A$6:$A$12=$C6)) nó sẽ hiểu lấy tên sheet từ B6, D18 lấy tên sheet từ B18

2/ tự tìm array theo cột A.
Vd: D6=SUMPRODUCT(A!$M$6:$M$12*A!U$6:U$12*(A!$A$6:$A$12=$C6)) nó sẽ hiểu tìm cột trong mảng M5:R12 ở các sheet tương ứng với A6

(File that của em đến 3000 dòng và 10 mấy sheet)

Em nghĩ dung offset kết hợp indirect hay sao nhỉ? Em làm tạm sumproduct nhưng vẫn chưa là tối ưu, nhờ các bác giúp với ạ

 

[quocgiacan]

Chào các bác,

Em có file đang dung sumproduct cho nhiều sheet, nhờ các bác tạo giúp em CT sao cho

1/ tự tìm sheet theo cột B.
Vd: D6=SUMPRODUCT(A!$M$6:$M$12*A!U$6:U$12*(A!$A$6:$A$12=$C6)) nó sẽ hiểu lấy tên sheet từ B6, D18 lấy tên sheet từ B18

2/ tự tìm array theo cột A.
Vd: D6=SUMPRODUCT(A!$M$6:$M$12*A!U$6:U$12*(A!$A$6:$A$12=$C6)) nó sẽ hiểu tìm cột trong mảng M5:R12 ở các sheet tương ứng với A6

(File that của em đến 3000 dòng và 10 mấy sheet)

Em nghĩ dung offset kết hợp indirect hay sao nhỉ? Em làm tạm sumproduct nhưng vẫn chưa là tối ưu, nhờ các bác giúp với ạ

Chào tieuthubuongbinh,

Đúng như bạn nghĩ là phải dùng thêm hàm kết hợp, nhưng là Indirect() hoặc Address().

Giải pháp trong file kèm là dùng 3 Name: VungA612, VungM612, VungU612 tạo vùng động biến thiên theo cột B và cột C như bạn yêu cầu.

Bạn tham khảo file kèm nha.

Chúc bạn ngày vui.

 

[leoheocon]

Chào các bác,

Em có file đang dung sumproduct cho nhiều sheet, nhờ các bác tạo giúp em CT sao cho

1/ tự tìm sheet theo cột B.
Vd: D6=SUMPRODUCT(A!$M$6:$M$12*A!U$6:U$12*(A!$A$6:$A$12=$C6)) nó sẽ hiểu lấy tên sheet từ B6, D18 lấy tên sheet từ B18

2/ tự tìm array theo cột A.
Vd: D6=SUMPRODUCT(A!$M$6:$M$12*A!U$6:U$12*(A!$A$6:$A$12=$C6)) nó sẽ hiểu tìm cột trong mảng M5:R12 ở các sheet tương ứng với A6

(File that của em đến 3000 dòng và 10 mấy sheet)

Em nghĩ dung offset kết hợp indirect hay sao nhỉ? Em làm tạm sumproduct nhưng vẫn chưa là tối ưu, nhờ các bác giúp với ạ

Dùng indirect kết hợp với offset, bạn dùng CT này ở D6:

D6=SUMPRODUCT(OFFSET(INDIRECT($B6&"!$M$6:$M$12"),,MATCH($A6,A!$M$5:$R$5,)-1)*OFFSET(INDIRECT($B6&"!U$6:U$12"),,COLUMNS($A1:A1)-1)*(INDIRECT($B6&"!$A$6:$A$12")=$C6))

Fill sang phải rồi fill xuống!!!

 

[tieuthubuongbinh]

Chào tieuthubuongbinh,

Đúng như bạn nghĩ là phải dùng thêm hàm kết hợp, nhưng là Indirect() hoặc Address().

Giải pháp trong file kèm là dùng 3 Name: VungA612, VungM612, VungU612 tạo vùng động biến thiên theo cột B và cột C như bạn yêu cầu.

Bạn tham khảo file kèm nha.

Chúc bạn ngày vui.

Cám ơn quocgiacan rất rất nhiều. Bạn có thể giải thích them là sao mình đứng ở mỗi cell thì name nó thay đổi, nhờ vào chỗ nào của CT vậy? mình học hỏi them để sau này mày mò ứng dung cho việc khác. Mình đoán là nhờ index phải không? bạn có thể cho mình xin link của hàm char mà nó list ra tất cả các ký tự máy tính tương ứng với number (vd: char(24) là mũi tên chỉ lên trời (mình chon đại 1 số xem nó ra gì).

Vùng A612: =INDEX(INDIRECT("'"&GT!$B6&"'!"&CHAR(65)&"6:"&CHAR(65)&"12"),) => nếu không phải data ở cột A mà là AA thì hàm char không giúp đơợc mà nên thay bang address và column như kiểu vùng U612 phải không?
Vùng M612: =INDEX(INDIRECT("'"&GT!$B6&"'!"&CHAR(77+MOD(ROW()-6,6))&"6:"&CHAR(77+MOD(ROW()-6,6))&"12"),) => Mod(row()-6 : tại sao -6 và chia cho 6? mình hỏi là vì nếu dữ lieu có số dòng bắt đầu khác nhau thì áp dung như thế nào? có phải vì row chứa dữ lieu tính toán bắt đầu là 6 không? ví dụ data của mình bắt đầu từ row 7 thì sẽ thay mod(row()-7,7) đúng không?
Vùng U612: =INDEX(INDIRECT(ADDRESS(6,COLUMN()+17,,,GT!$B6)&":"&ADDRESS(12,COLUMN()+17,,,)),)

Sẵn tiện bạn cho mình hỏi them, mình muốn tính sum theo tháng, nhưng hiện giờ kéo phải CT thì không đơợc vì mỗi lần sum cho 6 cột, bạn làm giúp mình với

Vd:

P6=Sum(D6:I6), P7=sum(J6:O6)
Mình đã cố tình format 1 tháng có 6 cột đều nhau để dễ copy CT, nhu7nng vẫn chưa nghĩ ra cách nào kéo CT về phải thì nó tự hiểu lấy mảng 6 cột tháng kế tiếp, bạn giúp được thì hay quá

 

[tieuthubuongbinh]

Dùng indirect kết hợp với offset, bạn dùng CT này ở D6:

D6=SUMPRODUCT(OFFSET(INDIRECT($B6&"!$M$6:$M$12"),,MATCH($A6,A!$M$5:$R$5,)-1)*OFFSET(INDIRECT($B6&"!U$6:U$12"),,COLUMNS($A1:A1)-1)*(INDIRECT($B6&"!$A$6:$A$12")=$C6))

Fill sang phải rồi fill xuống!!!

Mình đưa vào file that thì do số cột bị khác, mình lại không hiểu cách dung hàm offset nên chưa vận dung được.

Dữ lieu that của mình có cùng cấu trúc nhưng cột U trong file that lại là cột FJ, mình sửa về 2 ...offset(indirect($B6&"!FJ$6:FJ$12"),,columns($A1:A1)-1)... thì lỗi REF

 

[befaint]

(File that của em đến 3000 dòng và 10 mấy sheet)

3000*10=30,000 dòng => xài vba cho lẹ.

p/s: Bạn này thả dấu kiểu VNI hay bị rớt dấu...

 

[tieuthubuongbinh]

3000*10=30,000 dòng => xài vba cho lẹ.

p/s: Bạn này thả dấu kiểu VNI hay bị rớt dấu...

mình không biết tí gì về VBA. Đúng là file của mình lên đến 12MB chạy rất chậm.