SUMPRODUCT, em viết hoài mà không ra ???

[bun_bo_hue]

Các cho hỏi có cách viết hàm SUMPRODUCT nào (hoặc lập trình VBA) giải quyết đc vấn đề như trong file đính kèm không ạ ???

 

[Ba Tê]

Các cho hỏi có cách viết hàm SUMPRODUCT nào (hoặc lập trình VBA) giải quyết đc vấn đề như trong file đính kèm không ạ ???

Làm sao để tính tháng 6 = 8 như thế này được, chẳng có điều kiện cụ thể gì cả, ai hiểu được mà giúp bạn đây?

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

F​

​

|

G​

​

|

H​

​

|

I​

​

|

J​

​

|

K​

​

|

L​

​

|

M​

​

|

N​

​

|

1​

​

|

​

​

|

01/06/2010​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

2​

​

|Date >>|

1​

|

2​

|

3​

|

4​

|

5​

|

6​

|

7​

|

8​

|

9​

|

10​

|

11​

|

12​

|Tổng kì T6|

3​

​

|Data|

1​

|a|

2​

|f|

1​

|z|a| | | | | |

8​

|

 

[SA_DQ]

Lập trình, thì lập trình (Hàm tự tạo)

Option Explicit
Function NumStr(Thg As Range)
 Dim Clls As Range

 For Each Clls In Thg
   If IsNumeric(Clls) Then
      NumStr = NumStr + Clls.Value
   Else
      NumStr = 1 + NumStr
   End If
 Next Clls
End Function

Cú fáp hàm tại [AA3] như sau: =NumStr(N3:Y3)

& mong là không fải đưa file lên!

 

[bun_bo_hue]

Làm sao để tính tháng 6 = 8 như thế này được, chẳng có điều kiện cụ thể gì cả, ai hiểu được mà giúp bạn đây?

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

F​

​

|

G​

​

|

H​

​

|

I​

​

|

J​

​

|

K​

​

|

L​

​

|

M​

​

|

N​

​

|

1​

​

|

​

​

|

01/06/2010​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

2​

​

|Date >>|

1​

|

2​

|

3​

|

4​

|

5​

|

6​

|

7​

|

8​

|

9​

|

10​

|

11​

|

12​

|Tổng kì T6|

3​

​

|Data|

1​

|a|

2​

|f|

1​

|z|a| | | | | |

8​

|

À, mình quên giải thích. Tức là mình muốn tính tổng số kì (nếu là chữ thì đếm, số thì cộng dồn) theo từng tháng xác định.
Month >> Jun-10 Jul-10 hàm SUMPRODUCT phải viết như thế nào, tính tổng bao gồm số + chuỗi, theo từng tháng xác định ? Date >> 01 02 03 04 05 06 07 08 09 10 11 12 01 02 03 04 05 06 07 08 09 10 11 12 Tổng kì tháng6 Tổng kì tháng7





Số kì >> 1 a 2 f 1 z a 1 a 2 f 1 z 8 7





số kì có thể là 1 chữ, số

 

[ndu96081631]

À, mình quên giải thích. Tức là mình muốn tính tổng số kì (nếu là chữ thì đếm, số thì cộng dồn) theo từng tháng xác định.
Month >> Jun-10 Jul-10 hàm SUMPRODUCT phải viết như thế nào, tính tổng bao gồm số + chuỗi, theo từng tháng xác định ? Date >> 01 02 03 04 05 06 07 08 09 10 11 12 01 02 03 04 05 06 07 08 09 10 11 12 Tổng kì tháng6 Tổng kì tháng7
Số kì >> 1 a 2 f 1 z a 1 a 2 f 1 z 8 7
số kì có thể là 1 chữ, số

Tổng T6 = Sum(Vùng T6) + Counta(Vùng T6) - Count(Vùng T6)
Vùng T6 có thể xác định bằng 1 name động
Vậy thôi

 

[dat_butmuc]

Các cho hỏi có cách viết hàm SUMPRODUCT nào (hoặc lập trình VBA) giải quyết đc vấn đề như trong file đính kèm không ạ ???

Cách như Ndu đã nói với name động Data là:

=OFFSET($B$1,ROW()-1,12*(COLUMN(B$1)-COLUMN($B$1)),,12)

Xem thêm file

 

[ndu96081631]

Cách như Ndu đã nói với name động Data là:

=OFFSET($B$1,ROW()-1,12*(COLUMN(B$1)-COLUMN($B$1)),,12)

Xem thêm file

Nêu sửa 2 chổ:
Z2 thành số 6
AA2 thành số 7
Dùng Custom Format để định dạng cho nó hiển thị "Tổng kì T..." như vậy chính xác hơn mà khỏi phải COLUMN gì ráo
Khi đó, đặt con trỏ tại dòng 3 vào đặt name:

=OFFSET($B3,,MATCH(DATE(2010,Sheet1!Z$2,1),$B$2:$Y$2,0)-1,,12)

 

[dat_butmuc]

Nêu sửa 2 chổ:
Z2 thành số 6
AA2 thành số 7
Dùng Custom Format để định dạng cho nó hiển thị "Tổng kì T..." như vậy chính xác hơn mà khỏi phải COLUMN gì ráo
Khi đó, đặt con trỏ tại dòng 3 vào đặt name:

=OFFSET($B3,,MATCH(DATE(2010,Sheet1!Z$2,1),$B$2:$Y$2,0)-1,,12)

Hix, phải là=OFFSET($B3,,MATCH(DATE(2010,Sheet1!Z$2,1),$B$1:$Y$1,0)-1,,12)
chứ nhỉ?

 

[ndu96081631]

Hix, phải là=OFFSET($B3,,MATCH(DATE(2010,Sheet1!Z$2,1),$B$1:$Y$1,0)-1,,12)
chứ nhỉ?

Cũng thế thôi àm bạn!
Dòng 1 hay dòng 2 đều là Ngày tháng

 

[bun_bo_hue]

Tổng T6 = Sum(Vùng T6) + Counta(Vùng T6) - Count(Vùng T6)
Vùng T6 có thể xác định bằng 1 name động
Vậy thôi

Bác NDU quá siêu, em làm được rồi. Cám ơn bác nhiều lắm ạ.

 

[bun_bo_hue]

Nêu sửa 2 chổ:
Z2 thành số 6
AA2 thành số 7
Dùng Custom Format để định dạng cho nó hiển thị "Tổng kì T..." như vậy chính xác hơn mà khỏi phải COLUMN gì ráo
Khi đó, đặt con trỏ tại dòng 3 vào đặt name:

=OFFSET($B3,,MATCH(DATE(2010,Sheet1!Z$2,1),$B$2:$Y$2,0)-1,,12)

Bác ơi, cho em hỏi, công thức bác đưa em làm đc rồi. Nhưng có 1 vấn đề, số ngày trong tháng đâu phải lúc nào cũng là 12 (công thức OFFSET name động), vì 1 tháng có thể có 28,29,30 hoặc 31 ngày mà. Vậy chỗ số 12 làm sao bi giờ bác ????

 

[ndu96081631]

Bác ơi, cho em hỏi, công thức bác đưa em làm đc rồi. Nhưng có 1 vấn đề, số ngày trong tháng đâu phải lúc nào cũng là 12 (công thức OFFSET name động), vì 1 tháng có thể có 28,29,30 hoặc 31 ngày mà. Vậy chỗ số 12 làm sao bi giờ bác ????

Dữ liệu thật của bạn ra sao? Hãy đưa lên đây mới biết hướng để làm ---> Vì đàng nào cũng phải xem mới biết "điểm dừng" nằm ở chổ nào bạn à
Tôi làm sơ qua dựa trên file cũ của bạn đây!
Xem thử nhé! Trong file có thêm 1 name Days dùng để đếm số ngày trong tháng

 

[bun_bo_hue]

Dữ liệu thật của bạn ra sao? Hãy đưa lên đây mới biết hướng để làm ---> Vì đàng nào cũng phải xem mới biết "điểm dừng" nằm ở chổ nào bạn à
Tôi làm sơ qua dựa trên file cũ của bạn đây!
Xem thử nhé! Trong file có thêm 1 name Days dùng để đếm số ngày trong tháng

Quá chính xác luôn bác ơi. Bác quá siêu. À , cho em hỏi về công thức tí, công thức này nè "=SUMPRODUCT(1*(MONTH(Sheet1!$B$2:$AB$2)=Sheet1!AC$2))", số "1*" dùng để làm j vậy bác ???

 

[duongdv]

Cái này hơi khó diễn giải đây. Bạn vào đây tham khảo để biết về dấu "--", "1*" hay sử dụng trong hàm Sumproduct().
http://www.giaiphapexcel.com/forum/showthread.php?59-SUMPRODUCT-v%C3%A0-C%C3%B4ng-th%E1%BB%A9c-m%E1%BA%A3ng-Ph%C3%A9p-t%C3%ADnh-c%C3%B3-nhi%E1%BB%81u-%C4%91i%E1%BB%81u-ki%E1%BB%87n

 

[ndu96081631]

Quá chính xác luôn bác ơi. Bác quá siêu. À , cho em hỏi về công thức tí, công thức này nè "=SUMPRODUCT(1*(MONTH(Sheet1!$B$2:$AB$2)=Sheet1!AC$2))", số "1*" dùng để làm j vậy bác ???

Nếu không có số 1 thì mảng MONTH(Sheet1!$B$2:$AB$2)=Sheet1!AC$2 sẽ là những giá trị TRUE hoặc FALSE (đương nhiên vì so sánh mà)
Nhân 1 vào thì TRUE sẽ thành 1 và FALSE sẽ thành 0 ---> thế mới SUM được chứ

 

[bun_bo_hue]

À à, em hiểu rồi. Cám ơn pác nhiều lắm.