Hàm COUNTIF bị lỗi?

[Hoàng Trọng Nghĩa]

Đây là một bài mà tôi nhận được mail của một thành viên GPE mà tôi cũng không thể lý giải cũng như đưa ra giải pháp, bởi vì người đó là giáo viên và lớp học có 15 lớp từ lớp 1/1 đến lớp 1/15

Khi sử dụng Excel từ 2007 trở lên gặp trường hợp này:

Tại cột A tôi định dạng Text.

Ô A1 tôi gõ: 1/1

Ô A2 tôi gõ: 1/13

Tại B1 tôi ghi công thức: =COUNTIF($A$1:$A$2,$A$1)

Kết quả ra 2 (lẽ ra phải ra là 1 mới đúng)

Xin cho biết tại sao và cách khắc phục?

Lưu ý: Excel 2003 không xảy ra tình trạng này?

Cám ơn.

 

[hoamattroicoi]

Em đang dùng Excel 2010 thấy kết quả của Countif() trả về đúng anh ạ!

 

[Hoàng Trọng Nghĩa]

Em đang dùng Excel 2010 thấy kết quả của Countif() trả về đúng anh ạ!

Anh đang xài Excel 2010, kết quả khi chưa cho Edit (tức nguyên xi kết quả của em) là 1, nhưng khi bấm Allow Edit rồi thì nó là 2 em à!

Mặc dù anh cũng có giải pháp:

=SUMPRODUCT(($A$1:$A$2=$A$1)*1)


Nhưng anh lại muốn xem nó từ nguyên lý nào mà ra kỳ vậy!

 

[Lê Duy Thương]

Anh đang xài Excel 2010, kết quả khi chưa cho Edit (tức nguyên xi kết quả của em) là 1, nhưng khi bấm Allow Edit rồi thì nó là 2 em à!

Mặc dù anh cũng có giải pháp:

=SUMPRODUCT(($A$1:$A$2=$A$1)*1)


Nhưng anh lại muốn xem nó từ nguyên lý nào mà ra kỳ vậy!

chắc bạn hiền nghịch quá nên lỗi chăng ? tôi mở vẫn bằng một

 

[dhn46]

Em dùng Ex2010 Porttable cũng gặp tình trạng giống anh Nghĩa.
Định dạng trước rồi mới chèn dữ liệu.

 

[Hoàng Trọng Nghĩa]

chắc bạn hiền nghịch quá nên lỗi chăng ? tôi mở vẫn bằng một

Nghịch gì đâu bạn hiền! Đâu chỉ mỗi mình tôi bị đâu bạn! Người gửi mail hỏi tôi trước, tôi cũng thí nghiệm bị vậy, rồi cái hình dưới đây là file của bé Còi nè!

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

Cái vấn đề là hình của bạn không có cái tam giác màu xanh (cảnh báo về lỗi) ở các ô 1/13, còn của tôi thì có!

Vậy cho hỏi bạn định dạng ngày tháng trong Control Panel như thế nào? Còn tôi là dạng dd/mm/yyyy.

 

[Lê Duy Thương]

Nghịch gì đâu bạn hiền! Đâu chỉ mỗi mình tôi bị đâu bạn! Người gửi mail hỏi tôi trước, tôi cũng thí nghiệm bị vậy, rồi cái hình dưới đây là file của bé Còi nè!

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

Cái vấn đề là hình của bạn không có cái tam giác màu xanh (cảnh báo về lỗi) ở các ô 1/13, còn của tôi thì có!

Vậy cho hỏi bạn định dạng ngày tháng trong Control Panel như thế nào? Còn tôi là dạng dd/mm/yyyy.

tôi định dạng M/d/yyyy . thử định dạng lại xem là hết lỗi ngay

 

[Hoàng Trọng Nghĩa]

tôi định dạng M/d/yyyy . thử định dạng lại xem là hết lỗi ngay

Tôi nghi ngờ vậy nên hỏi bạn, thì ra đúng là như thế, vậy cho nên hàm của Excel 2007 trở về sau còn nhiều bất cập quá! Đã định dạng Text rồi thì cứ giá trị làm sao thì "quất" như vậy đi, cho dù dạng nào đi nữa thì 1/1 và 1/13 đâu có giống cái gì đâu mà lại cho kết quả ra là 2 chứ!

Nếu vì định dạng ngày tháng như thế thì tất cả dạng ngày tháng của Excel bị đảo lộn khi ta muốn dd/mm/yyyy sao ta?

Ngại thiệt! Thôi thì cứ SUMPRODUCT thay cho COUNTIF cho chắc cú nếu gặp dạng na ná dạng ngày tháng!

 

[ndu96081631]

Đây là một bài mà tôi nhận được mail của một thành viên GPE mà tôi cũng không thể lý giải cũng như đưa ra giải pháp, bởi vì người đó là giáo viên và lớp học có 15 lớp từ lớp 1/1 đến lớp 1/15

Khi sử dụng Excel từ 2007 trở lên gặp trường hợp này:

Tại cột A tôi định dạng Text.

Ô A1 tôi gõ: 1/1

Ô A2 tôi gõ: 1/13

Tại B1 tôi ghi công thức: =COUNTIF($A$1:$A$2,$A$1)

Kết quả ra 2 (lẽ ra phải ra là 1 mới đúng)

Xin cho biết tại sao và cách khắc phục?

Lưu ý: Excel 2003 không xảy ra tình trạng này?

Cám ơn.

COUNTIF và SUMIF đều bị tình trạng này (đã nói nhiều lần trên GPE rồi)
Nguyên nhân: Trong quá trình xứ lý dữ liệu, nó "tưởng lầm" 1/1 là ngày 1 tháng 1 (năm hiện hành) và 1/13 là tháng 1 năm 2013 (ngày 1) ---> Thế nên 2 cell ấy xem như bằng nhau
Vậy nên: Trong những trường hợp dữ liệu hơi đặc biệt thế này thì:
- Hãy chuyển sang SUMPRODUCT mà dùng, đừng có đổi định dạng M/d hay d/M gì cho mất công (sang máy người khác có khi lại sai bét tiếp)
- Có thể sửa lại dữ liệu bằng cách thêm tiền tố (hay hậu tố) vào dữ liệu. Chẳng hạn *1/1 và *1/13... vân vân. Thiếu chi cách ---> Tóm lại: đứng để anh Bill phải hiểu lầm là đươc rồi
------------------
Nhân đây cũng nói thêm về lỗi tương tự khi xử lý mảng:
- Ta có dữ liệu như của Nghĩa
- Sau khi chạy code, ta cho những dữ liệu ấy vào 1 mảng
- Cuối cùng gán mảng kết quả và 1 range
Bài toán này nếu xử lý không khéo thì khi đưa vào range sẽ sai tùm lum
Ví dụ:

Sub Test()
  Dim arr
  arr = Array("1/1", "1/13")
  Range("A1:B1").Value = arr
End Sub

Rõ ràng mảng arr có các phần tử là TEXT, vậy mà khi gán xuống sheet lại cho kết quả là Date
Tôi nghĩ đây cũng là sai sót gần tương tự như khi ta dùng COUNTIF hay SUMIF

 

[Hoàng Trọng Nghĩa]

COUNTIF và SUMIF đều bị tình trạng này (đã nói nhiều lần trên GPE rồi)
Nguyên nhân: Trong quá trình xứ lý dữ liệu, nó "tưởng lầm" 1/1 là ngày 1 tháng 1 (năm hiện hành) và 1/13 là tháng 1 năm 2013 (ngày 1) ---> Thế nên 2 cell ấy xem như bằng nhau
Vậy nên: Trong những trường hợp dữ liệu hơi đặc biệt thế này thì:
- Hãy chuyển sang SUMPRODUCT mà dùng, đừng có đổi định dạng M/d hay d/M gì cho mất công (sang máy người khác có khi lại sai bét tiếp)
- Có thể sửa lại dữ liệu bằng cách thêm tiền tố (hay hậu tố) vào dữ liệu. Chẳng hạn *1/1 và *1/13... vân vân. Thiếu chi cách ---> Tóm lại: đứng để anh Bill phải hiểu lầm là đươc rồi
------------------
Nhân đây cũng nói thêm về lỗi tương tự khi xử lý mảng:
- Ta có dữ liệu như của Nghĩa
- Sau khi chạy code, ta cho những dữ liệu ấy vào 1 mảng
- Cuối cùng gán mảng kết quả và 1 range
Bài toán này nếu xử lý không khéo thì khi đưa vào range sẽ sai tùm lum
Ví dụ:

Sub Test()
  Dim arr
  arr = Array("1/1", "1/13")
  Range("A1:B1").Value = arr
End Sub

Rõ ràng mảng arr có các phần tử là TEXT, vậy mà khi gán xuống sheet lại cho kết quả là Date
Tôi nghĩ đây cũng là sai sót gần tương tự như khi ta dùng COUNTIF hay SUMIF

Cám ơn Thầy đã nói rõ nguyên do trên. Tuy nhiên tại sao Excel 2013 trở về trước không bị tình trạng này? Phải chăng trong công thức cũ, "Anh Bill" cũng cho update? Nhưng nếu ta cài 2003 và có cài thêm phần convert 2007 thì nó cũng bị tình trạng này!

 

[ndu96081631]

Cám ơn Thầy đã nói rõ nguyên do trên. Tuy nhiên tại sao Excel 2013 trở về trước không bị tình trạng này? Phải chăng trong công thức cũ, "Anh Bill" cũng cho update? Nhưng nếu ta cài 2003 và có cài thêm phần convert 2007 thì nó cũng bị tình trạng này!

Cái đó chưa chắc!
Tôi dùng Excel 2003 Portable cũng bị tình trạng này đấy (các bạn khác test xem)
Tức là nếu tôi vào Control Panel chỉnh lại Date Format thành M/d/yyyy thì kết quả =1. Còn với Date Format d/M/yyyy thì kết quả =2

 

[Hoàng Trọng Nghĩa]

Cái đó chưa chắc!
Tôi dùng Excel 2003 Portable cũng bị tình trạng này đấy (các bạn khác test xem)
Tức là nếu tôi vào Control Panel chỉnh lại Date Format thành M/d/yyyy thì kết quả =1. Còn với Date Format d/M/yyyy thì kết quả =2

Em kiểm tra kỹ rồi Thầy. Trên máy Excel 2003, định dạng Control Panel là dd/mm/yyyy, vẫn cho kết quả đúng là 1. Tức là máy nào không dính dáng gì đến đã cài đặt Excel 2007 trở về sau thì tính đúng. Còn máy đã cài Excel 2007 trở về sau, hay có phần convert hay cài song song 2003 và 2007 thì sẽ bị dính tới việc này.

Vì thế máy của Thầy đã cài 2010 mà dùng portable 2003 thì vẫn bị dính nếu định dạng d/m/yyyy.

 

[vodoi2x]

Đây không phải là lỗi của excel

Excel theo mặc định đã coi dữ liệu dạng
#/##
là dữ liệu ngày tháng rùi,
nên tất nhiên nó sẽ xử lý dạng ngày tháng --> nên có kết quả như các bạn mô tả

vậy nếu có Lỗi ở đây (quan niệm theo người hỏi) thì ra do lỗi quan niệm sai của người dùng về dữ liệu mà thui - nói cách khác là chúng ta không chuẩn hoá theo chương trình đã mặc định - mà chúng ta (người dùng) lại cứ áp cái quan niệm riêng và thích sử dụng dạng riêng vào dẫn đến KQ đúng mà lại nghĩ là sai (sai do với cái riêng của người dùng)