Đếm và tính tổng các giá trị duy nhất trong các giá trị trùng lặp theo điều kiện (2 người xem)

[wuchengcai]

Chào!

Mình đang gặp khó khăn trong việc đếm số lượng "Thành phố", "Thị xã", "Quận" và "Huyện" của 1 tỉnh trong file đính kèm.
Nhờ các bạn giúp mình nhé.
Cám ơn nhiều!

 

[vanthinh3101]

Chào!

Mình đang gặp khó khăn trong việc đếm số lượng "Thành phố", "Thị xã", "Quận" và "Huyện" của 1 tỉnh trong file đính kèm.
Nhờ các bạn giúp mình nhé.
Cám ơn nhiều!

Bạn tham khảo file đính kèm.

 

[leonguyenz]

Tham khảo thêm SUMPRODUCT, kết hợp các điều kiện.

=SUMPRODUCT(($D$5:$D$75=$D$2)*(LEFT($H$5:$H$75,2)="TP")/COUNTIF($H$5:$H$75,$H$5:$H$75))

 

[wuchengcai]

Tham khảo thêm SUMPRODUCT, kết hợp các điều kiện.

=SUMPRODUCT(($D$5:$D$75=$D$2)*(LEFT($H$5:$H$75,2)="TP")/COUNTIF($H$5:$H$75,$H$5:$H$75))

Dòng công thức của bạn chỉ áp dụng đến dòng 75, nếu số lượng dòng vượt quá 75 thì phải sửa lại.
VD: khi D2 chọn Hà Nội thì số lượng dòng là 581 thì sửa thành:
=SUMPRODUCT(($D$5:$D$581=$D$2)*(LEFT($H$5:$H$581,2)="TP")/COUNTIF($H$5:$H$581,$H$5:$H$581))
Nhưng khi D2 chọn tỉnh khác có số lượng dòng nhỏ hơn 581 thì bị lỗi #DIV/0!.
Bạn xem lại giúp nhé.

 

[leonguyenz]

Bạn xem file đính kèm và áp dụng thử xem bị lỗi không nhé.

 

[Phan Thế Hiệp]

Dòng công thức của bạn chỉ áp dụng đến dòng 75, nếu số lượng dòng vượt quá 75 thì phải sửa lại.
VD: khi D2 chọn Hà Nội thì số lượng dòng là 581 thì sửa thành:
=SUMPRODUCT(($D$5:$D$581=$D$2)*(LEFT($H$5:$H$581,2)="TP")/COUNTIF($H$5:$H$581,$H$5:$H$581))
Nhưng khi D2 chọn tỉnh khác có số lượng dòng nhỏ hơn 581 thì bị lỗi #DIV/0!.
Bạn xem lại giúp nhé.

Xem file kèm xem đúng yêu cầu chưa!?

Thành phố=COUNT(1/(MATCH($B$5:$B$100,$B$5:$B$100,)=ROW($1:$100)))
Huyện/Quận=COUNT(1/(MATCH($H$5:$H$100,IF(ISNUMBER(SEARCH(M6,$H$5:$H$100)),$H$5:$H$100),)=ROW($1:$100)))
Thị trấn/Phường/Xã=COUNT(1/(MATCH($I$5:$I$100,IF(ISNUMBER(SEARCH(M10,$K$5:$K$100)),$I$5:$I$100),)=ROW($1:$100)))

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

Muốn thêm nhiều dòng đổi 100 thành 1000, 1500...

Thân

 

[hongphuong1997]

Xem file kèm xem đúng yêu cầu chưa!?

Thành phố=COUNT(1/(MATCH($B$5:$B$100,$B$5:$B$100,)=ROW($1:$100)))
Huyện/Quận=COUNT(1/(MATCH($H$5:$H$100,IF(ISNUMBER(SEARCH(M6,$H$5:$H$100)),$H$5:$H$100),)=ROW($1:$100)))
Thị trấn/Phường/Xã=COUNT(1/(MATCH($I$5:$I$100,IF(ISNUMBER(SEARCH(M10,$K$5:$K$100)),$I$5:$I$100),)=ROW($1:$100)))

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

Muốn thêm nhiều dòng đổi 100 thành 1000, 1500...

Thân

Bác ơi cho cháu hỏi chút nhé, trong công thức dấu (*) là phép and, dấu (+) là phép or, còn dấu chí (/) để làm gì hở bác?

 

[Phan Thế Hiệp]

Bác ơi cho cháu hỏi chút nhé, trong công thức dấu (*) là phép and, dấu (+) là phép or, còn dấu chí (/) để làm gì hở bác?

Dấu chia "/" cũng giống ý nghĩa dấu nhân "*" coi như là AND("mảng").
Nhưng khác ở chỗ, Ví dụ: mình có Mảng chứa dãy kết quả {TRUE, FALSE, FALSE, TRUE}

• Nếu nhân => 1*{TRUE, FALSE, FALSE, TRUE} = {1,0,0,1}
• Nếu chia => 1/{TRUE, FALSE, FALSE, TRUE} = {1, #DIV/0, #DIV/0, 1}
• Khi sử dụng với COUNT() ở bên ngoài, thì:
  • KQ1=> COUNT( {1,0,0,1} ) =4
  • KQ2=> COUNT( {1, #DIV/0, #DIV/0, 1} ) =2

Thân

 

[hongphuong1997]

Dấu chia "/" cũng giống ý nghĩa dấu nhân "*" coi như là AND("mảng").
Nhưng khác ở chỗ, Ví dụ: mình có Mảng chứa dãy kết quả {TRUE, FALSE, FALSE, TRUE}

• Nếu nhân => 1*{TRUE, FALSE, FALSE, TRUE} = {1,0,0,1}
• Nếu chia => 1/{TRUE, FALSE, FALSE, TRUE} = {1, #DIV/0, #DIV/0, 1}
• Khi sử dụng với COUNT() ở bên ngoài, thì:
  • KQ1=> COUNT( {1,0,0,1} ) =4
  • KQ2=> COUNT( {1, #DIV/0, #DIV/0, 1} ) =2

Thân

Cháu cảm ơn bác nhé

 

[wuchengcai]

Xem file kèm xem đúng yêu cầu chưa!?

Thành phố=COUNT(1/(MATCH($B$5:$B$100,$B$5:$B$100,)=ROW($1:$100)))
Huyện/Quận=COUNT(1/(MATCH($H$5:$H$100,IF(ISNUMBER(SEARCH(M6,$H$5:$H$100)),$H$5:$H$100),)=ROW($1:$100)))
Thị trấn/Phường/Xã=COUNT(1/(MATCH($I$5:$I$100,IF(ISNUMBER(SEARCH(M10,$K$5:$K$100)),$I$5:$I$100),)=ROW($1:$100)))

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

Muốn thêm nhiều dòng đổi 100 thành 1000, 1500...

Thân

Mình làm OK rùi, tuy nhiên công thức của bạn cần chỉnh lại như sau:
Thành phố nên là: =COUNT(1/(MATCH($H$5:$H$100,IF(ISNUMBER(SEARCH(M4,$H$5:$H$100)),$H$5:$H$100),)=ROW($1:$100))).
Nếu dùng =COUNT(1/(MATCH($B$5:$B$100,$B$5:$B$100,)=ROW($1:$100))) thì các tỉnh có 2 thành phố trở lên sẽ cho kết quả chỉ 1 như Quảng Ninh, Khánh Hòa, Quảng Nam...
Thị trấn/Phường/Xã mình nghĩ dùng công thức đơn giản: =COUNTIF($K$5:$K$100,"*"&M8&"*") cũng cho kết quả như công thức của bạn.

 

[wuchengcai]

Xem file kèm xem đúng yêu cầu chưa!?

Thành phố=COUNT(1/(MATCH($B$5:$B$100,$B$5:$B$100,)=ROW($1:$100)))
Huyện/Quận=COUNT(1/(MATCH($H$5:$H$100,IF(ISNUMBER(SEARCH(M6,$H$5:$H$100)),$H$5:$H$100),)=ROW($1:$100)))
Thị trấn/Phường/Xã=COUNT(1/(MATCH($I$5:$I$100,IF(ISNUMBER(SEARCH(M10,$K$5:$K$100)),$I$5:$I$100),)=ROW($1:$100)))

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

Muốn thêm nhiều dòng đổi 100 thành 1000, 1500...

Thân

Mình muốn thống kê tổng số "Thị xã", "Quận", "Huyện" bên sheet "Diaban" thì dùng công thức thế nào bạn?
Mình dùng công thức: =COUNT(1/(MATCH($H$4:$H$15000,IF(ISNUMBER(SEARCH(H3,$H$4:$H$15000)),$H$4:$H$15000),)=ROW($1:$15000))). Nhưng kết quả không đúng, vì có nhiều huyện trùng tên nhau. Ví dụ: Có 10 tỉnh ở Đồng bằng Sông Cửu Long có Huyện Châu Thành.

 

[Phan Thế Hiệp]

Mình muốn thống kê tổng số "Thị xã", "Quận", "Huyện" bên sheet "Diaban" thì dùng công thức thế nào bạn?
Mình dùng công thức: =COUNT(1/(MATCH($H$4:$H$15000,IF(ISNUMBER(SEARCH(H3,$H$4:$H$15000)),$H$4:$H$15000),)=ROW($1:$15000))). Nhưng kết quả không đúng, vì có nhiều huyện trùng tên nhau. Ví dụ: Có 10 tỉnh ở Đồng bằng Sông Cửu Long có Huyện Châu Thành.

1/ Thì bởi do có sự trùng nhau như bây giờ bạn báo

Ví dụ: Có 10 tỉnh ở Đồng bằng Sông Cửu Long có Huyện Châu Thành.

Cho nên, không thể dùng hàm COUNTIF() như bạn nhìn nhận:

Thị trấn/Phường/Xã mình nghĩ dùng công thức đơn giản: =COUNTIF($K$5:$K$100,"*"&M8&"*") cũng cho kết quả như công thức của bạn.

Vì giả sử có 2 xã giống nhau nhưng khác quận/huyện hoặc khác tỉnh thì sao?

2/ Dữ liệu mà lên hơn 1000 dòng dùng công thức mảng không chịu thấu. Bạn hãy chịu khó chờ anh em VBA hỗ trợ nha.

Tôi đính kèm file chứa công thức mảng chỉ dành cho bạn tham khảo (coi chơi thôi!), không khuyến khích sử dụng, vì nó sẽ làm "đơ" máy của bạn.

Thân

 

[wuchengcai]

1/ Thì bởi do có sự trùng nhau như bây giờ bạn báo

Cho nên, không thể dùng hàm COUNTIF() như bạn nhìn nhận:

Vì giả sử có 2 xã giống nhau nhưng khác quận/huyện hoặc khác tỉnh thì sao?

2/ Dữ liệu mà lên hơn 1000 dòng dùng công thức mảng không chịu thấu. Bạn hãy chịu khó chờ anh em VBA hỗ trợ nha.

Tôi đính kèm file chứa công thức mảng chỉ dành cho bạn tham khảo (coi chơi thôi!), không khuyến khích sử dụng, vì nó sẽ làm "đơ" máy của bạn.

Thân

Nếu chỉ dựa vào cột "Tinh", "Huyện" và "Xa" của sheet "Diaban" mà ko dựa vào các cột khác thì công thức thay đổi thế nào bạn?

 

[Phan Thế Hiệp]

Nếu chỉ dựa vào cột "Tinh", "Huyện" và "Xa" của sheet "Diaban" mà ko dựa vào các cột khác thì công thức thay đổi thế nào bạn?
View attachment 214398

Nếu điều kiện như bạn nêu thì chỉ cần nối các Vùng cần Match():

• Nếu là Huyện: thì cột D nối với cột E
• Nếu là Thị trấn, Phường, Xã thì nối D&E&F
• Lúc đó hàm Match đại khái như sau: (Match(D&E&F, IF( ĐK, D&E&F ) ,) =ROW(1:15000))

Nhưng bạn không nên nghĩ tới nữa vì hàm Mảng này nặng lắm.

Thân

 

[wuchengcai]

Nếu điều kiện như bạn nêu thì chỉ cần nối các Vùng cần Match():

• Nếu là Huyện: thì cột D nối với cột E
• Nếu là Thị trấn, Phường, Xã thì nối D&E&F
• Lúc đó hàm Match đại khái như sau: (Match(D&E&F, IF( ĐK, D&E&F ) ,) =ROW(1:15000))

Nhưng bạn không nên nghĩ tới nữa vì hàm Mảng này nặng lắm.

Thân

Vì file này sẽ sử dụng trực tiếp trên điện thoại để tra cứu, nếu dùng VBA thì Office trên điện thoại không hỗ trợ. Sao mình dùng công thức: (Match(D&E&F, IF( ĐK, D&E&F ) ,) =ROW(1:15000)), không thống kê ra được nhỉ?

 

[Phan Thế Hiệp]

Vì file này sẽ sử dụng trực tiếp trên điện thoại để tra cứu, nếu dùng VBA thì Office trên điện thoại không hỗ trợ. Sao mình dùng công thức: (Match(D&E&F, IF( ĐK, D&E&F ) ,) =ROW(1:15000)), không thống kê ra được nhỉ?

Công thức tôi ghi "đại khái" để bạn dễ hiểu thôi, chứ câu lệnh đầy đủ phải là:

Huyện/Quận=COUNT(1/(MATCH(Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200,IF(ISNUMBER(SEARCH(M6,Diaban!$H$4:$H$11200)),Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200),)=ROW($1:$11200)))
Thị trấn/Phường/Xã=COUNT(1/(MATCH(Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200&Diaban!$K$4:$K$11200,IF(ISNUMBER(SEARCH(M8,Diaban!$K$4:$K$11200)),Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200&Diaban!$K$4:$K$11200),)=ROW($1:$11200)))

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

Thân

 

[wuchengcai]

Công thức tôi ghi "đại khái" để bạn dễ hiểu thôi, chứ câu lệnh đầy đủ phải là:

Huyện/Quận=COUNT(1/(MATCH(Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200,IF(ISNUMBER(SEARCH(M6,Diaban!$H$4:$H$11200)),Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200),)=ROW($1:$11200)))
Thị trấn/Phường/Xã=COUNT(1/(MATCH(Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200&Diaban!$K$4:$K$11200,IF(ISNUMBER(SEARCH(M8,Diaban!$K$4:$K$11200)),Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200&Diaban!$K$4:$K$11200),)=ROW($1:$11200)))

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

Thân

Mình thống kê okie rồi, cám ơn bạn nhiệt tình hỗ trợ.

 

[HieuCD]

Công thức tôi ghi "đại khái" để bạn dễ hiểu thôi, chứ câu lệnh đầy đủ phải là:

Huyện/Quận=COUNT(1/(MATCH(Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200,IF(ISNUMBER(SEARCH(M6,Diaban!$H$4:$H$11200)),Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200),)=ROW($1:$11200)))
Thị trấn/Phường/Xã=COUNT(1/(MATCH(Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200&Diaban!$K$4:$K$11200,IF(ISNUMBER(SEARCH(M8,Diaban!$K$4:$K$11200)),Diaban!$D$4:$D$11200&Diaban!$H$4:$H$11200&Diaban!$K$4:$K$11200),)=ROW($1:$11200)))

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

Thân

Mình mở điện thoại làm thử, không biết làm cách nào Ctrl+Shift+Enter, chỉ mình với

 

[Phan Thế Hiệp]

Mình mở điện thoại làm thử, không biết làm cách nào Ctrl+Shift+Enter, chỉ mình với

Phải đúng con "Ai phôn ích xì" mới có cái nút đặc biệt đó!
Anh em mình toàn xài "xì mát phôn sam sung" hay hàng của Đại ca "Tập" thì mần sao mà có!!!

Khà khà khà.


Chúc anh ngày vui

 

[Phan Thế Hiệp]

Mình thống kê okie rồi, cám ơn bạn nhiệt tình hỗ trợ.

Muốn thống kê đúng hay sai bạn cũng có thể dùng Pivot Table như file kèm.

Thân