Câu đố: Tìm công thức ngắn nhất tính tổng có điều kiện (1 người xem)

[ptm0412]

Gởi mọi người,
Lâu không viết bài sợ quên hết công thức , nên tôi ra câu đố này nhờ anh chị em tìm hộ công thức ngắn nhất tính tổng có điều kiện trong sheet Data, điều kiện trong sheet Rule. Kết quả mẫu trong sheet Data cột Q.
Chỉ dùng hàm và công thức, không dùng VBA, không chơi cộng từng ô vì không fill được.
Có thể dùng mọi cách, kể cả cột phụ.

 

[khuongvietphong]

Công thức này được không thầy:

=SUMPRODUCT((OFFSET(Rule!$A$5,MATCH(Data!C2,Rule!$A$6:$A$13,0),1,,8)="x")*(D2:K2))

 

[ptm0412]

Công thức này được không thầy:

=SUMPRODUCT((OFFSET(Rule!$A$5,MATCH(Data!C2,Rule!$A$6:$A$13,0),1,,8)="x")*(D2:K2))

Đúng thì đúng rồi, để xem còn công thức ngắn hơn không

 

[HieuCD]

Gởi mọi người,
Lâu không viết bài sợ quên hết công thức , nên tôi ra câu đố này nhờ anh chị em tìm hộ công thức ngắn nhất tính tổng có điều kiện trong sheet Data, điều kiện trong sheet Rule. Kết quả mẫu trong sheet Data cột Q.
Chỉ dùng hàm và công thức, không dùng VBA, không chơi cộng từng ô vì không fill được.
Có thể dùng mọi cách, kể cả cột phụ.

công thức thường
=SUMPRODUCT((Rule!$A$6:$A$13=C2)*(Rule!$B$6:$I$13="x")*(D2:K2))
nếu dùng công thức mảng
=SUM((Rule!$A$6:$A$13=C2)*(Rule!$B$6:$I$13="x")*(D2:K2))

 

[ptm0412]

SumProduct là hàm mảng, Sum của HieuCD là công thức mảng. Có cách nào xài công thức thường như SumIf không?

 

[ptm0412]

Thêm điều kiện:
Trường hợp người dùng thêm vài ba sản phẩm được thưởng vào sheet Rule, không cần sửa công thức?

Tất nhiên thứ tự sản phẩm trong rule theo cột giống thứ tự trong Data

 

[lenongree]

Thêm điều kiện:
Trường hợp người dùng thêm vài ba sản phẩm được thưởng vào sheet Rule, không cần sửa công thức?

Tất nhiên thứ tự sản phẩm trong rule theo cột giống thứ tự trong Data

CT này được không thầy:

=SUMPRODUCT((Rule!$B$6:$M$13="X")*(Rule!$A$6:$A$13=C2)*D2:O2)

 

[Let'GâuGâu]

SumProduct là hàm mảng, Sum của HieuCD là công thức mảng. Có cách nào xài công thức thường như SumIf không?

em chơi ăn gian, do các SP 01-->SP 12 của thầy ở 2 sheet sắp xếp giống nhau

P2=SUMIF(OFFSET(Rule!$B$5:$I$5,MATCH(C2,Rule!$A$6:$A$13,0),),"x",Data!D2:O2)

 

[HieuCD]

Thêm điều kiện:
Trường hợp người dùng thêm vài ba sản phẩm được thưởng vào sheet Rule, không cần sửa công thức?

Tất nhiên thứ tự sản phẩm trong rule theo cột giống thứ tự trong Data

đảm bảo không phải hàm mảng

=SUMIF((OFFSET(Rule!$B$5:$M$5,MATCH(Data!C2,Rule!$A$6:$A$13,0),)),"x",(D2:O2))

 

[ptm0412]

Hình như câu đố dễ quá, cả điều kiện bổ sung cũng dễ. Chỉ cần công thức trùm điều kiện đến cột M là được.

Các câu trả lời đều ra kết quả đúng, nhưng tôi thích SumIf hơn, nó nhanh hơn công thức mảng.

 

[Let'GâuGâu]

Hình như câu đố dễ quá, cả điều kiện bổ sung cũng dễ. Chỉ cần công thức trùm điều kiện đến cột M là được.

Các câu trả lời đều ra kết quả đúng, nhưng tôi thích SumIf hơn, nó nhanh hơn công thức mảng.

bây giờ mà đố xài sumif(S) mà vị trí các SP để tùm lum mới ghê đó thầy...hihihi

 

[ptm0412]

Công thức của tôi:

=SUMIF(DK,"X",Data!D2:O2)

DK = INDEX(Rule!$B$6:$M$13,MATCH(Data!$C2,Rule!$A$6:$A$13,0),0)

SumProduct và Sum mảng chắc phải xài 2 name, trong khi SumIf chỉ cần 1 name.

 

[lenongree]

bây giờ mà đố xài sumif(S) mà vị trí các SP để tùm lum mới ghê đó thầy...hihihi

Nếu Sumproduc thì em sài CT này:

=SUMPRODUCT((Rule!$B$6:$M$13="X")*(Rule!$A$6:$A$13=C2)*(COUNTIF($D$1:$O$1,Rule!$B$5:$M$5)*D2:O2))

 

[Phuocam]

Trường hợp Rule thay sản phẩm SP 02 thành SP 12, theo các bạn, công thức sao cho ngắn nhất ?

 

[lenongree]

Trường hợp Rule thay sản phẩm SP 02 thành SP 12, theo các bạn, công thức sao cho ngắn nhất ?

CT bài #13 được không anh, em không nghĩ được sumif cho trường hợp này!!!

 

[Phuocam]

CT bài #13 được không anh, em không nghĩ được sumif cho trường hợp này!!!

Kết quả chưa đúng bạn ơi. Tiếp tục nhé!

 

[HieuCD]

bây giờ mà đố xài sumif(S) mà vị trí các SP để tùm lum mới ghê đó thầy...hihihi

mình nghĩ hàm sumifS là hàm mảng không biết đúng hay sai?

 

[Phuocam]

mình nghĩ hàm sumifS là hàm mảng không biết đúng hay sai?

He he, thử là biết liền.

 

[ptm0412]

Ý nghĩa của câu đố này là:

1. "Có thể dùng mọi cách" nghĩa là được dùng Name, không phải ai cũng biết ăn gian cho công thức ngắn
2. Dùng Sumproduct công thức dài. Kể cả dùng Name cũng phải dùng 2 name trở lên và vẫn dài. Đã vậy SumProduct sẽ chậm hơn
3. Công thức Sum dùng mảng cũng chậm
4. Quan trọng nhất (đối với tôi): Tôi muốn giới thiệu hàm Index với chỉ số dòng hoặc cột = 0

 

[leoheocon]

----------------------------------

 

[lenongree]

Kết quả chưa đúng bạn ơi. Tiếp tục nhé!

Thử lại nhe anh, tưởng đơn giản ai ngờ phức tạp nghê!!!

=SUM(ISNUMBER(MATCH($D$1:$O$1,IF(OFFSET(Rule!$B$5:$M$5,MATCH(C2,Rule!$A$6:$A$13,),)="X",Rule!$B$5:$M$5),))*D2:O2)

Ctrl+Sfit+Enter!!

 

[HungQuoc49]

Thử dùng sumif() theo file bài 1

P2=SUMIF(INDEX(Rule!$B$6:$I$13,MATCH(C2,Rule!$A$6:$A$13,0),0),"X",(D2:K2))