Công thức thay thế nhóm công thức liên quan đến Countif

[congthanh6868]

Chào các bạn,

Trong file đính kèm mình có nhóm công thức liên quan đến Countif đã cho kết quả đúng "IF(C$1=0;"";IF(OR(COUNTIF(F$1:F$10;$B12)>0;COUNTIF(F$1:F$10;$C12)>0;COUNTIF(F$1:F$10;$D12)>0);SUM(COUNTIF(F$1:F$10;$B12);COUNTIF(F$1:F$10;$C12);COUNTIF(F$1:F$10;$D12));0)".
Tuy nhiên mình thấy công thức khá dài và có thể rườm rá. Vậy rất mong bạn nào có thể giúp dùng công thức khác gọn nhẹ hơn.

Cám ơn các bạn rất nhiều.

 

[dhn46]

Bạn dùng công thức này

=IF(C$1=0,"",SUMPRODUCT(COUNTIF(F$1:F$10,$B12:$D12)))

 

[Phuocam]

Chào các bạn,

Trong file đính kèm mình có nhóm công thức liên quan đến Countif đã cho kết quả đúng "IF(C$1=0;"";IF(OR(COUNTIF(F$1:F$10;$B12)>0;COUNTIF(F$1:F$10;$C12)>0;COUNTIF(F$1:F$10;$D12)>0);SUM(COUNTIF(F$1:F$10;$B12);COUNTIF(F$1:F$10;$C12);COUNTIF(F$1:F$10;$D12));0)".
Tuy nhiên mình thấy công thức khá dài và có thể rườm rá. Vậy rất mong bạn nào có thể giúp dùng công thức khác gọn nhẹ hơn.
Cám ơn các bạn rất nhiều.

Thử công thức:
=IF(C$1=0,"",SUM(COUNTIF(F$1:F$10,$B12),COUNTIF(F$1:F$10,$C12),COUNTIF(F$1:F$10,$D12)))

 

[congthanh6868]

Bạn dùng công thức này

=IF(C$1=0,"",SUMPRODUCT(COUNTIF(F$1:F$10,$B12:$D12)))

Cám ơn Bạn. Công thức của Bạn rất gọn nhưng làm file nặng quá, gấp đôi liền. Bạn xem có giúp mình được Công thức nào khác nữa không?

 

[congthanh6868]

Cám ơn Bạn. Công thức của Bạn rất gọn nhưng làm file nặng quá, gấp đôi liền. Bạn xem có giúp mình được Công thức nào khác nữa không?

Có ACE nào có cao kiến giúp mình với được không. Vì Công thức trên thì quá nặng. Còn nếu vẫn dùng Countif của mình thì File nặng quá, lên tới gần 500 Mb.
Mong nhận được sự giúp đỡ của ACE.

 

[dhn46]

Có ACE nào có cao kiến giúp mình với được không. Vì Công thức trên thì quá nặng. Còn nếu vẫn dùng Countif của mình thì File nặng quá, lên tới gần 500 Mb.
Mong nhận được sự giúp đỡ của ACE.

Công thức đơn giản nhưng là dạng mảng nên tốc độ xử lý chậm bạn ah. Không biết bạn sử dụng bao nhiêu công thức mà dung lượng File lên khủng thế? Tôi chưa thấy file nào nặng như vậy, liệu có nguyên nhân gì khác không? Nếu quá nhiều thì VBA là giải pháp tốt nhất.

 

[susu16b]

Chào các bạn,

Trong file đính kèm mình có nhóm công thức liên quan đến Countif đã cho kết quả đúng "IF(C$1=0;"";IF(OR(COUNTIF(F$1:F$10;$B12)>0;COUNTIF(F$1:F$10;$C12)>0;COUNTIF(F$1:F$10;$D12)>0);SUM(COUNTIF(F$1:F$10;$B12);COUNTIF(F$1:F$10;$C12);COUNTIF(F$1:F$10;$D12));0)".
Tuy nhiên mình thấy công thức khá dài và có thể rườm rá. Vậy rất mong bạn nào có thể giúp dùng công thức khác gọn nhẹ hơn.

Cám ơn các bạn rất nhiều.

Thử công thức này xem sao:

=IF(C$1=0,"",SUM(--(F$1:F$10=$B12),--(F$1:F$10=$C12),--(F$1:F$10=$D12)))

Ctrl+Shift+Enter

 

[ChanhTQ@]

Bạn nên tránh công thức mảng, 1 khi file lặc lè; Như thử cái ni xem sao?
=COUNTIF(F1:F10,B$18)+COUNTIF(F1:F10,C$18)+COUNTIF(F1:F10,D$18)

 

[siwtom]

Chào các bạn,

Trong file đính kèm mình có nhóm công thức liên quan đến Countif đã cho kết quả đúng "IF(C$1=0;"";IF(OR(COUNTIF(F$1:F$10;$B12)>0;COUNTIF(F$1:F$10;$C12)>0;COUNTIF(F$1:F$10;$D12)>0);SUM(COUNTIF(F$1:F$10;$B12);COUNTIF(F$1:F$10;$C12);COUNTIF(F$1:F$10;$D12));0)".
Tuy nhiên mình thấy công thức khá dài và có thể rườm rá. Vậy rất mong bạn nào có thể giúp dùng công thức khác gọn nhẹ hơn.

Cám ơn các bạn rất nhiều.

Công thức rườm rà vì bạn viết rườm rà.

Ta xét IF "bên trong" trong công thức của bạn

IF([B][COLOR=#ff0000]OR(COUNTIF(F$1:F$10;$B12)>0;COUNTI F(F$1:F$10;$C12)>0;COUNTIF(F$1:F$10;$D12)>0)[/COLOR][/B];
SUM(COUNTIF(F$1:F$10;$B12);COUNTIF(F$1:F$10;$C12);COUNTIF(F$1:F$10;$D12))
;0)

Tức nếu điều kiện (đk) đỏ đỏ không thỏa thì trả về 0.
Nhưng đk không thỏa có nghĩa là gì? Có nghĩa là có đồng thời

[COLOR=#0000ff]COUNTIF(F$1:F$10;$B12) = 0
COUNTI F(F$1:F$10;$C12) = 0
COUNTIF(F$1:F$10;$D12) = 0[/COLOR]

Tức lúc đó

SUM(COUNTIF(F$1:F$10;$B12);COUNTIF(F$1:F$10;$C12);COUNTIF(F$1:F$10;$D12)) = 0

Vậy thì IF để làm gi? Cứ trả về SUM thôi. Nếu sẩy ra xanh xanh thì tự SUM = 0 mà.

Tóm lại code của chính bạn có thể viết thành

IF(C$1=0;"";SUM(COUNTIF(F$1:F$10;$B12);COUNTIF(F$1:F$10;$C12);COUNTIF(F$1:F$10;$D12)))

Nếu là phép cộng 3 số thì thay cho SUM(a; b; c) cũng có thể dùng luôn phép cộng a+b+c. Rõ ràng ngắn hơn được vài ký tự mà không phải gọi hàm SUM

 

[congthanh6868]

Công thức đơn giản nhưng là dạng mảng nên tốc độ xử lý chậm bạn ah. Không biết bạn sử dụng bao nhiêu công thức mà dung lượng File lên khủng thế? Tôi chưa thấy file nào nặng như vậy, liệu có nguyên nhân gì khác không? Nếu quá nhiều thì VBA là giải pháp tốt nhất.

Cám ơn Bạn, mình đã hiểu lý do tốc độ xử lý File chậm. File của mình điền công thức trên ở hầu như toàn bộ vùng dữ liệu đến dòng gần 50.000. Mình đang thử các công thức được giúp đỡ bởi: susu16b, ChanhTQ@, Siwtom xem cái nào nhẹ nhất. Kết quả như thế nào mình sẽ báo cáo sau khi thử xong.

 

[congthanh6868]

Cám ơn Bạn, mình đã hiểu lý do tốc độ xử lý File chậm. File của mình điền công thức trên ở hầu như toàn bộ vùng dữ liệu đến dòng gần 50.000. Mình đang thử các công thức được giúp đỡ bởi: susu16b, ChanhTQ@, Siwtom xem cái nào nhẹ nhất. Kết quả như thế nào mình sẽ báo cáo sau khi thử xong.

Thưa các Bạnhuocam, susu16b, ChanhTQ@, Siwtom, dhn46. Cám ơn các Bạn đã giúp đỡ.
Công thức các Bạn giúp đã thỏa mãn được mong muốn của mình là Ngắn gọn.
Tuy nhiên mình mong muốn được công thức gúp File nhẹ hơn nữa, vì File thực tế của mình sẽ dùng Công thức cho vùng dữ liệu đến tận dòng 50.000. Qua thử các công thức thì mình chưa thấy nhẹ hơn (Xin attach kèm các File đã áp dụng công thức các Bạn làm giúp)
Rất mong tiếp tục nhận được sự giúp đỡ của các Bạn.
Cám ơn rất nhiều.

 

[dhn46]

Có thể chắc chắn một điều rằng công thức oàn vùng dữ liệu 50.000 dòng sẽ không thể làm file giảm dung lượng hay tăng tốc độ lên được. VBA mới là giải pháp triệt để.

Bạn post file với cấu trúc thật lên, dữ liệu có thể ảo nhưng đúng với dạng chuẩn mọi người sẽ giúp bạn VBA. Nếu chỉ có dữ liệu thường thì file sẽ rất nhẹ và xử lý nhanh chóng.

 

[congthanh6868]

Có thể chắc chắn một điều rằng công thức oàn vùng dữ liệu 50.000 dòng sẽ không thể làm file giảm dung lượng hay tăng tốc độ lên được. VBA mới là giải pháp triệt để.

Bạn post file với cấu trúc thật lên, dữ liệu có thể ảo nhưng đúng với dạng chuẩn mọi người sẽ giúp bạn VBA. Nếu chỉ có dữ liệu thường thì file sẽ rất nhẹ và xử lý nhanh chóng.

Cám ơn Bạn.
Dữ liệu trong các file đính kèm là cấu trúc thật rồi. Mình chỉ xóa bớt dữ liệu đi để File nhẹ đúng yêu cầu của diễn đàn.
Mong tiếp tục nhận được sự giúp đỡ của Bạn & ACE trên diễn đàn.

 

[siwtom]

Thưa các Bạnhuocam, susu16b, ChanhTQ@, Siwtom, dhn46. Cám ơn các Bạn đã giúp đỡ.
Công thức các Bạn giúp đã thỏa mãn được mong muốn của mình là Ngắn gọn.
Tuy nhiên mình mong muốn được công thức gúp File nhẹ hơn nữa, vì File thực tế của mình sẽ dùng Công thức cho vùng dữ liệu đến tận dòng 50.000. Qua thử các công thức thì mình chưa thấy nhẹ hơn (Xin attach kèm các File đã áp dụng công thức các Bạn làm giúp)
Rất mong tiếp tục nhận được sự giúp đỡ của các Bạn.
Cám ơn rất nhiều.

Chính xác là: tôi không đưa ra công thức của riêng tôi. Tôi chỉ rút gọn công thức của bạn, bỏ đi đoạn mà tôi cho là thừa. Hiểu là: nếu đã viết như bạn, ý tưởng như bạn, dùng các hàm như bạn thì theo tôi phải sửa như tôi đã sửa.

Tôi nghĩ là tôi đã hiểu được cách phân tích vấn đề của bạn. Bạn có thể hiểu thế này: tôi kiểm tra điều kiện để nếu không thỏa thì không phải tính SUM nữa cho tốn điện nước mà trả luôn giá trị 0 về. Nếu là thế thì không chỉ dài thêm mà thực ra kiểm tra đk mới là tốn thêm điện nước.

Tôi giải thích thế này:
Với công thức của bạn + đã rút gọn như tôi đề nghị thì ta luôn luôn tính SUM. Tức luôn luôn tính 3 COUNTIF + 2 phép cộng (a+b+c = (a+b)+c). Không lúc nào phải tính thêm gì nữa. Với công thức của bạn thì ta luôn luôn phải tính điều kiện, tức luôn luôn phải tính 3 COUNTIF + 3 phép so sánh (tính giá trị của 3 biểu thức lô gíc). Đấy là luôn luôn. Nhưng gặp trường hợp đk thỏa thì phải tính tiếp 3 COUNTIF (dùng để tính SUM) + 2 phép cộng. Tức khi mà thực tế đk sẽ thỏa thì bạn phải tính 3 COUNTIF (cho nhu cầu kiểm tra điều kiện) + 3 biểu thức lô gíc + 3 COUNTIF (dùng để tính SUM) + 2 phép cộng. Tức với công thức kiểm tra đk và với đk không thỏa thì 2 công thức cùng dùng năng lượng như nhau nhưng trong trường hợp đk thỏa thì công thức với kiểm tra đk so với công thức không kiểm tra đk sẽ phải tính thêm 3 COUNTIF (cho nhu cầu kiểm tra điều kiện) + 3 biểu thức lô gíc.

Như vậy thì thậm chí với dữ liệu 100 dòng thay cho 50 000 dòng thì cũng phải sửa công thức như tôi đề nghị. Vừa gọn hơn vừa tốn ít điện nước hơn. Ý bài viết của tôi là vậy.

Riêng về code thì tôi xin nghỉ vì các bạn trả lời trong chủ đề này toàn là "chuyên gia" cả.

 

[congthanh6868]

Chính xác là: tôi không đưa ra công thức của riêng tôi. Tôi chỉ rút gọn công thức của bạn, bỏ đi đoạn mà tôi cho là thừa. Hiểu là: nếu đã viết như bạn, ý tưởng như bạn, dùng các hàm như bạn thì theo tôi phải sửa như tôi đã sửa.

Tôi nghĩ là tôi đã hiểu được cách phân tích vấn đề của bạn. Bạn có thể hiểu thế này: tôi kiểm tra điều kiện để nếu không thỏa thì không phải tính SUM nữa cho tốn điện nước mà trả luôn giá trị 0 về. Nếu là thế thì không chỉ dài thêm mà thực ra kiểm tra đk mới là tốn thêm điện nước.

Tôi giải thích thế này:
Với công thức của bạn + đã rút gọn như tôi đề nghị thì ta luôn luôn tính SUM. Tức luôn luôn tính 3 COUNTIF + 2 phép cộng (a+b+c = (a+b)+c). Không lúc nào phải tính thêm gì nữa. Với công thức của bạn thì ta luôn luôn phải tính điều kiện, tức luôn luôn phải tính 3 COUNTIF + 3 phép so sánh (tính giá trị của 3 biểu thức lô gíc). Đấy là luôn luôn. Nhưng gặp trường hợp đk thỏa thì phải tính tiếp 3 COUNTIF (dùng để tính SUM) + 2 phép cộng. Tức khi mà thực tế đk sẽ thỏa thì bạn phải tính 3 COUNTIF (cho nhu cầu kiểm tra điều kiện) + 3 biểu thức lô gíc + 3 COUNTIF (dùng để tính SUM) + 2 phép cộng. Tức với công thức kiểm tra đk và với đk không thỏa thì 2 công thức cùng dùng năng lượng như nhau nhưng trong trường hợp đk thỏa thì công thức với kiểm tra đk so với công thức không kiểm tra đk sẽ phải tính thêm 3 COUNTIF (cho nhu cầu kiểm tra điều kiện) + 3 biểu thức lô gíc.

Như vậy thì thậm chí với dữ liệu 100 dòng thay cho 50 000 dòng thì cũng phải sửa công thức như tôi đề nghị. Vừa gọn hơn vừa tốn ít điện nước hơn. Ý bài viết của tôi là vậy.

Riêng về code thì tôi xin nghỉ vì các bạn trả lời trong chủ đề này toàn là "chuyên gia" cả.

Cám ơn Bạn, cám ơn cả nhà đã giúp đỡ.
Vì em không phải chuyên gia về Excel nên khi đăng Chủ đề này em cứ nghĩ là Công thức ngắn gọn thì sẽ cho File nhẹ. Qua phân tích của các Bạn giờ em đã hiểu thêm.
Vậy, giờ em xin phép làm rõ ý định nhờ giúp đỡ của em là mong muốn có công thức để có được File nhẹ, chạy nhanh khi lấp đầy công thức đến ô thứ 50.000.
Rất xin lỗi làm phiền cả nhà do sự hiểu nhầm ngu ngơ của em.
Nay rất mong tiếp tục nhận được sự giúp đỡ của cả nhà. Em cám ơn rất nhiều.

 

[congthanh6868]

Thưa các Bạnhuocam, susu16b, ChanhTQ@, Siwtom, dhn46. Cám ơn các Bạn đã giúp đỡ.
Công thức các Bạn giúp đã thỏa mãn được mong muốn của mình là Ngắn gọn.
Tuy nhiên mình mong muốn được công thức gúp File nhẹ hơn nữa, vì File thực tế của mình sẽ dùng Công thức cho vùng dữ liệu đến tận dòng 50.000. Qua thử các công thức thì mình chưa thấy nhẹ hơn (Xin attach kèm các File đã áp dụng công thức các Bạn làm giúp)
Rất mong tiếp tục nhận được sự giúp đỡ của các Bạn.
Cám ơn rất nhiều.

Mình mày mò thêm được 2 kiểu nữa nhưng vẫn chưa đạt yêu cầu mong muốn. 2 cách mới này vẫn cho File nặng hơn file ban đầu.
Rất mong Bạn nào có thể giúp mình. Cám ơn rất nhiều.

 

[congthanh6868]

Mình mày mò thêm được 2 kiểu nữa nhưng vẫn chưa đạt yêu cầu mong muốn. 2 cách mới này vẫn cho File nặng hơn file ban đầu.
Rất mong Bạn nào có thể giúp mình. Cám ơn rất nhiều.

Thưa ACE,

Sau một hồi mày mò thì tự mình thấy không dùng công thức bình thường để thay thế giúp cho file nhẹ hơn.
Có bạn nói có thể dùng VBA tạo công thức. Tuy nhiên mình không biết dùng VBA, vậy phiền ACE nào bớt chút thời gian giúp mình bài này.
Trong file cập nhật này mình cần dùng với 2 loại điều kiện: OR và AND.
Rất mong nhận được sự giúp đỡ của ACE.
Trân trọng cảm ơn.

 

[dhn46]

Thưa ACE,

Sau một hồi mày mò thì tự mình thấy không dùng công thức bình thường để thay thế giúp cho file nhẹ hơn.
Có bạn nói có thể dùng VBA tạo công thức. Tuy nhiên mình không biết dùng VBA, vậy phiền ACE nào bớt chút thời gian giúp mình bài này.
Trong file cập nhật này mình cần dùng với 2 loại điều kiện: OR và AND.
Rất mong nhận được sự giúp đỡ của ACE.
Trân trọng cảm ơn.

Để sử dụng VBA bạn cần bố trí dữ liệu chuẩn, không được sai khác điạ chỉ. Tôi làm ví dụ với file của bạn nhưng bố trí lại 1 chút

- Sheet1 bạn cứ copy đủ 50000 dòng hoặc hơn (không quá 65535 dòng)
- Sheet2 có 2 phần so sánh Or và And, bạn copy điều kiện theo cấu trúc đó

Sau đó bạn bấm nút lệnh Run để có kết quả. Bạn nhớ Enable macro, cách enable bạn search diễn đàn.

 

[congthanh6868]

Để sử dụng VBA bạn cần bố trí dữ liệu chuẩn, không được sai khác điạ chỉ. Tôi làm ví dụ với file của bạn nhưng bố trí lại 1 chút

- Sheet1 bạn cứ copy đủ 50000 dòng hoặc hơn (không quá 65535 dòng)
- Sheet2 có 2 phần so sánh Or và And, bạn copy điều kiện theo cấu trúc đó

Sau đó bạn bấm nút lệnh Run để có kết quả. Bạn nhớ Enable macro, cách enable bạn search diễn đàn.

Cám ơn Bạn đã giúp đỡ. Tuy nhiên, do mình không trình bày rõ ràng nên hàm Bạn làm giúp chưa được như mong muôn. Nay mình gửi lại File tách riêng dạng OR & AND thành 2 sheet, và đây là công thức tính toán theo dữ liệu cập nhật hàng ngày. Các dòng từ 14 trở đi thực tế sẽ được điền công thức.
Mong tiếp tục nhận được sự giúp đỡ của Bạn.
Cám ơn Bạn nhiều.

 

[dhn46]

Bạn thử với File sau nhưng công thức tại file bài #19 với bài #17 của bạn là khác nhau, phần And. Tôi làm cho bạn theo công thức bài #17.