Vấn đề với hàm SUMPRODUCT (1 người xem)

[Vo Duy Minh]

Chào các bạn
Rất mong nhận được ý kiến giúp đỡ của các bạn với vấn đề dưới đây.
Tôi dùng hàm Sumproduct để tìm số giá trị không trùng lắp trong một danh sách.
Vấn đề là yêu cầu tìm trong danh sách có thay đổi. Cụ thể với một danh sách như trong file với 40 dòng (thực tế thì dài hơn nhiều), khi thì yêu cầu tìm từ dòng 1 đến dòng 10, khi thì từ dòng 1 đến dòng 5, khi thì từ dóng 1 đến dòng 20, khi thì trọn danh sách (luôn bắt đầu từ dòng đầu).
Trong file đính kèm thì yêu cầu xuất hiện theo lựa chọn trong cell F3
Để giải quyết tôi ghép hàm, như (tôi ngắt dòng cho dể nhìn)
=IF(F3=11,SUMPRODUCT(1/COUNTIF(D313,D313)),
IF(F3=12,SUMPRODUCT(1/COUNTIF(D314,D314)),
IF(F3=13,SUMPRODUCT(1/COUNTIF(D315,D315)),
IF(F3=14,SUMPRODUCT(1/COUNTIF(D316,D316)),
IF(F3=15,SUMPRODUCT(1/COUNTIF(D317,D317)),
IF(F3=16,SUMPRODUCT(1/COUNTIF(D318,D318)),
IF(F3=17,SUMPRODUCT(1/COUNTIF(D319,D319)),
IF(F3=18,SUMPRODUCT(1/COUNTIF(D320,D320)),
IF(F3=19,SUMPRODUCT(1/COUNTIF(D321,D321)),
IF(F3=20,SUMPRODUCT(1/COUNTIF(D322,D322)),"")))))))))))
dĩ nhiên như thế thì quá thủ công và sẽ không thể giải quyết nếu có số dòng rất cao.

Rất mong các bạn góp ý để có thể giải quyết vấn đề, dùng hàm sumproduct hay hàm nào khác thay thế
Xin cám ơn các bạn trước.

 

[patuan1010]

dùng thử hàm aggregate nhé

 

[qtm1987]

G3=SUMPRODUCT(1/COUNTIF(INDIRECT("D3: D"&F3+2);INDIRECT("D3: D"&F3+2))) Bạn thử chỉ dùng thêm Indirect gọn lại vậy là được.

 

[batman1]

=SUMPRODUCT(1/COUNTIF(OFFSET(D3,,,F3),OFFSET(D3,,,F3)))

hoặc

=SUMPRODUCT(1/COUNTIF($D$3:INDEX($D$3:$D$1000,$F$3),$D$3:INDEX($D$3:$D$1000,$F$3)))

Tôi không hâm mộ OFFSET.

Có thể đặt name data:

=OFFSET($D$3,,,$F$3)

hoặc

=$D$3:INDEX($D$3:$D$1000,$F$3)

và công thức

=SUMPRODUCT(1/COUNTIF(data,data))

 

[Vo Duy Minh]

Cám ơn bạn rất nhiều
Tôi đã dùng và thấy rất tốt.
Chúc bạn và gia đình bình an trong đại dịch hiện nay.

 

[Vo Duy Minh]

Tuy nhiên có vấn đề khác là hàm này không thể copy gắn vào chỗ khác được.
Tôi cần copy và paste (cả F3 và G3) sang chỗ khác, với danh sách khác (cùng format)

Hàm =SUMPRODUCT(1/COUNTIF($D$3:INDEX($D$3:$D$1000,$F$3),$D$3:INDEX($D$3:$D$1000,$F$3)))
của bạn batman1 cũng rất hay, nhưng vẫn gặp vấn đề trên khi copy và paste (hàm để dùng với danh sách khác) trên cùng cột.
Một lần nữa rất cám ơn sự hỗ trợ của các bạn.
Đặc biệt là rất nhanh chóng.

 

[qtm1987]

Tuy nhiên có vấn đề khác là hàm này không thể copy gắn vào chỗ khác được.
Tôi cần copy và paste (cả F3 và G3) sang chỗ khác, với danh sách khác (cùng format)

Bạn thử kết hợp địa chỉ tuyệt đối hàng/cột ($) để khi copy sang vùng khác không bị lệch vùng dữ liệu. Khi bạn hiểu rõ công thức thì bạn sẽ tự vận dung được 1 cách linh hoạt nhất. Chúc bạn thành công.

 

[Vo Duy Minh]

Cám ơn bạn qtm1987 rất nhiều
Tôi dùng hàm
=SUMPRODUCT(1/COUNTIF($D$3:INDEX($D$3:$D$1000,$F$3),$D$3:INDEX($D$3:$D$1000,$F$3)))
được bạn batman1 đề nghị với chút thay đổi
=SUMPRODUCT(1/COUNTIF(D3:INDEX(D350,F3),D3:INDEX(D350,F3)))
Thì thấy có thể copy và paste rất hiệu quả.

Xin cám ơn bạn và bạn batman1 rất nhiều.
Hai bạn giúp tôi không chỉ áp dụng thành công mà còn hiểu thêm về việc vận dụng hàm Sumproduct.

 

[batman1]

Tuy nhiên có vấn đề khác là hàm này không thể copy gắn vào chỗ khác được.

Chọn G3 (ô công thức) -> thẻ Formulas -> Name Manager -> đặt name data -> Refers to

=$D$3:INDEX($D$3:$D$1000,F3)

Bây giờ copy F3:G3 tới vd. I6:J6 xem sao.

 

[Vo Duy Minh]

Cám ơn bạn batman1
Tôi đã thực hiện thành công theo hướng dẫn của bạn, thực sự là hơn cả mong đợi của tôi nữa.
Cám ơn bạn rất nhiều với những giúp đỡ trong hai năm qua.
Những hướng dẫn của bạn rất tuyệt với.
Chúc bạn và gia đình bình an trong đại dịch này.
Và mong đại dịch sớm qua đi.

 

[SteveNguyen1991]

Nếu hàm như bên dưới thì có vấn đề gì không các bác.
=SUM(1/COUNTIF(D3:INDIRECT("D"&F3+2),D3:INDIRECT("D"&F3+2)))
em thử thì đúng với file của chủ thớt!!

 

[qtm1987]

Bạn dùng Sum thì kết quả luôn luôn là 1. Còn Sumproduct sẽ xét từng giá trị trong mảng để tính tổng lại. Ví dụ Phân tích công thức như sau:
Data = “Long An, HCM, Long An”
Dùng riêng lẻ hàm Countif(data,data) => kết quả luôn =1
Nếu kết hợp Sumproduct (Countif(data,data)) nghĩa là sẽ tính Countif lần lượt cho từng phần tử i trong mảng Data rồi cộng giá trị các phần tử lại:
- i1 (Long An) =2, i2(HCM) =1, i3(Long An)=2 Rồi cộng tổng lại 2+ 1 +2 =5
Kết hợp Sumproduct ( 1/ Countif(data,data)) cách hiểu tương tự như trên nhưng mục đích loại kết quả trùng lặp:
- i1 (Long An) =1/2, i2(HCM) =1, i3(Long An)=1/2 Rồi cộng tổng lại 1/2+ 1 +1/2 =2 (đây là kết quả theo mục đính tính toán của chủ thớt muốn tính tổng không trùng lặp)

 

[Vo Duy Minh]

Cám ơn bạn qtm1987 rất nhiều với những hướng dẫn cụ thê của bạn.
Hiện nay tôi dùng hàm
SUMPRODUCT(1/COUNTIF(D3:INDEX(D350,F3),D3:INDEX(D350,F3)))
thì chạy rất ổn và có thể copy paste sang chỗ khác.
Hàm bạn đề nghị cũng hoạt động rất tốt nhưng tôi copy paste sang chỗ khác không được do dính chỗ "D3" nên không thể chuyển sang danh sách được dù danh sách cùng độ dài.

Tôi cũng hơi tham nên xin bạn (và cả bạn batman1) giúp thêm vấn đề sau
Trong chuỗi (giả định) D350 nếu có cell trống thì hàm không chạy được, hoặc chạy và tính cell trống (do từ hàm khác) là 1 giá trị như các giá trị khác.
Tôi không rõ làm thế nào để có thể áp dụng hàm như trên (hoặc hàm nào khác) để có thể tính giá trị trùng lắp như thế trong một chuỗi có các ô trống và không tính một giá trị nhất định nào đó trong chuỗi (như Long An chẳng hạn)
Xin cám ơn hai bạn rất nhiều (thú thật, tôi đã mất cả ngày vật lộn với cái sumproduct này, mà chỉ có thể làm với cái hàm thủ công thôi)

 

[batman1]

Nếu vùng có ô trống thì ví dụ:

=SUM(IF(ISBLANK(D3:D12),0,1/COUNTIF(D3:D12,D3:D12)))

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

Nếu không muốn tính vd. "Long An" thì trừ đi (COUNTIF(D3: D12,"Long An")>0), tức

=SUM(IF(ISBLANK(D3:D12),0,1/COUNTIF(D3:D12,D3:D12)))-(COUNTIF(D3:D12,"Long An")>0)

 

[Vo Duy Minh]

Cám ơn bạn batman1 rất nhiều
Không thể có gì tốt hơn.
Có điều là hàm ra kết quả lẻ, 4,832222 chẳng hạn, có lẽ do có cell hoàn toàn trống, có cell trống do hàm khác đưa về kết quả trống
Tôi giải quyết bằng cách thêm INT thì hoàn toàn chính xác.

Cám ơn bạn rất nhiều.
Chúc bạn mọi điều tốt đẹp.

 

[batman1]

Cám ơn bạn batman1 rất nhiều
Không thể có gì tốt hơn.
Có điều là hàm ra kết quả lẻ, 4,832222 chẳng hạn, có lẽ do có cell hoàn toàn trống, có cell trống do hàm khác đưa về kết quả trống
Tôi giải quyết bằng cách thêm INT thì hoàn toàn chính xác.

ISBLANK - ô BLANK là ô trống như em bé mới sinh ấy. Ô chứa chuỗi rỗng, vd. do hàm trả về, không là BLANK.

 

[Khanhsky]

=SUMPRODUCT(1/COUNTIF($D$3:INDEX($D$3:$D$1000,$F$3),$D$3:INDEX($D$3:$D$1000,$F$3)))

Anh Batman1 có thể giải thích chi tiết công thức này được không?

 

[batman1]

Anh Batman1 có thể giải thích chi tiết công thức này được không?

Bạn định hỏi về hàm đếm không trùng hay cách dùng hàm INDEX?
1. Công thức đếm không trùng.
Nếu dùng hàm SUM (dùng SUMPRODUCT thì không phải nhấn Ctrl + Shift + Enter mà chỉ Enter)

=SUM(1/COUNTIF(D3:D100, D3:D100))

Giả sử trong 98 ô từ D3 tới D100 có k giá trị duy nhất: a1 xuất hiện n1 lần, a2 xuất hiện n2 lần, ..., ak xuất hiện nk lần.
Ta có n1 + n2 + ... + nk = 98.

1/COUNTIF(D3: D100, D3: D100) sẽ trả về mảng 98 giá trị trong đó có: n1 giá trị 1/n1, n2 giá trị 1/n2, ..., nk giá trị 1/nk.
Do vậy SUM(1/COUNTIF(D3: D100, D3: D100)) = n1*(1/n1) + n2*(1/n2) + ... + nk*(1/nk) =
= 1 + 1 + ... + 1 (số chữ số 1 bằng số giá trị duy nhất - k) = k

Tức SUM(1/COUNTIF(D3: D100, D3: D100)) = k (số giá trị duy nhất).

Để mục sổ thị thì ta xét D3: D8 = {"bla", "hichic","hehe", "hehe", "hichic", "hehe"}

Có 3 giá trị duy nhất:
a1 = "bla" xuất hiện 1 lần -> n1 = 1
a2 = "hichic" xuất hiện 2 lần -> n2 = 2
a3 = "hehe" xuất hiện 3 lần -> n3 = 3

COUNTIF(D3: D8, D3: D8) trả về mảng 6 giá trị {1, 2, 3, 3, 2, 3}

1/COUNTIF(D3: D8, D3: D8) trả về mảng 6 giá trị {1/1, 1/2, 1/3, 1/3, 1/2, 1/3)

Tức n1 = 1 giá trị 1/1 = 1/n1, n2 = 2 giá trị 1/2 = 1/n2, n3 = 3 giá trị 1/3 = 1/n3

=> SUM(1/COUNTIF(D3: D8, D3: D8)) = 1/1 + 1/2 + 1/3 + 1/3 + 1/2 + 1/3 = 1*(1/1) + 2*(1/2) + 3*(1/3) = 1 + 1 + 1 = 3

2. Cấu trúc D3:INDEX(D3: D100, F3)

Giả sử F3 = 12, tức INDEX(D3: D100, F3) = INDEX(D3: D100, 12) = D14. Giả sử D14 = "Ngày mai em đi".

Khi dùng hàm INDEX phải nhớ là INDEX tùy theo "NGỮ CẢNH" hoặc trả về GIÁ TRỊ (value) hoặc trả về THAM CHIẾU (reference)

1. Ở đâu đó vd. K1 có công thức

=INDEX(D3:D100, F3) & ". Biển nhớ tên em gọi về"

Lúc đó INDEX trả về GIÁ TRỊ "Ngày mai em đi" và trong K1 có "Ngày mai em đi. Biển nhớ tên em gọi về"

2. Trong công thức đếm duy nhất ở trên INDEX trả về THAM CHIẾU D14, do vậy D3:INDEX(D3: D100, F3) trả về THAM CHIẾU D3: D14

 

[Khanhsky]

Bạn định hỏi về hàm đếm không trùng hay cách dùng hàm INDEX?
1. Công thức đếm không trùng.
Nếu dùng hàm SUM (dùng SUMPRODUCT thì không phải nhấn Ctrl + Shift + Enter mà chỉ Enter)

=SUM(1/COUNTIF(D3:D100, D3:D100))

Giả sử trong 98 ô từ D3 tới D100 có k giá trị duy nhất: a1 xuất hiện n1 lần, a2 xuất hiện n2 lần, ..., ak xuất hiện nk lần.
Ta có n1 + n2 + ... + nk = 98.

1/COUNTIF(D3: D100, D3: D100) sẽ trả về mảng 98 giá trị trong đó có: n1 giá trị 1/n1, n2 giá trị 1/n2, ..., nk giá trị 1/nk.
Do vậy SUM(1/COUNTIF(D3: D100, D3: D100)) = n1*(1/n1) + n2*(1/n2) + ... + nk*(1/nk) =
= 1 + 1 + ... + 1 (số chữ số 1 bằng số giá trị duy nhất - k) = k

Tức SUM(1/COUNTIF(D3: D100, D3: D100)) = k (số giá trị duy nhất).

Để mục sổ thị thì ta xét D3: D8 = {"bla", "hichic","hehe", "hehe", "hichic", "hehe"}

Có 3 giá trị duy nhất:
a1 = "bla" xuất hiện 1 lần -> n1 = 1
a2 = "hichic" xuất hiện 2 lần -> n2 = 2
a3 = "hehe" xuất hiện 3 lần -> n3 = 3

COUNTIF(D3: D8, D3: D8) trả về mảng 6 giá trị {1, 2, 3, 3, 2, 3}

1/COUNTIF(D3: D8, D3: D8) trả về mảng 6 giá trị {1/1, 1/2, 1/3, 1/3, 1/2, 1/3)

Tức n1 = 1 giá trị 1/1 = 1/n1, n2 = 2 giá trị 1/2 = 1/n2, n3 = 3 giá trị 1/3 = 1/n3

=> SUM(1/COUNTIF(D3: D8, D3: D8)) = 1/1 + 1/2 + 1/3 + 1/3 + 1/2 + 1/3 = 1*(1/1) + 2*(1/2) + 3*(1/3) = 1 + 1 + 1 = 3

2. Cấu trúc D3:INDEX(D3: D100, F3)

Giả sử F3 = 12, tức INDEX(D3: D100, F3) = INDEX(D3: D100, 12) = D14. Giả sử D14 = "Ngày mai em đi".

Khi dùng hàm INDEX phải nhớ là INDEX tùy theo "NGỮ CẢNH" hoặc trả về GIÁ TRỊ (value) hoặc trả về THAM CHIẾU (reference)

1. Ở đâu đó vd. K1 có công thức

=INDEX(D3:D100, F3) & ". Biển nhớ tên em gọi về"

Lúc đó INDEX trả về GIÁ TRỊ "Ngày mai em đi" và trong K1 có "Ngày mai em đi. Biển nhớ tên em gọi về"

2. Trong công thức đếm duy nhất ở trên INDEX trả về THAM CHIẾU D14, do vậy D3:INDEX(D3: D100, F3) trả về THAM CHIẾU D3: D14

Thank anh batman1 nhé. Excel của em ở mức cơ bản, anh giải thích tương đối kỹ, em cũng hiểu được chút ít. !!