Hỏi số 12 xuất hiện bao nhiêu lần trong cột A (1 người xem)

[trungvdb]

Bài toán của em đặt ra là hãy đếm xem chuỗi 12 xuất hiện bao nhiêu lần trong cột A

Ví dụ: ô A1 = 123456 ---> tính là 1 lần
A6 = 96123 ---> Tính là 1 lần
ô A4 = 1212 ---> tính là 2 lần

----
(trong file em đính kèm minh họa kết quả là 4)

Xin hãy giúp em bằng 2 cách càng tốt (ưu tiên công thức vì em đang muốn trau dồi thằng này).

 

[hoamattroicoi]

Tại B1 bạn nhập công thức này :

=SUM(IF(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),2))=12,1))

Fill cho các ô còn lại trong sheet.
Nhớ kết thúc bằng Ctrl+Shift+Enter hoặc có thể dùng Sumproduct :

=SUMPRODUCT((VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),2))=12)*1)

 

[trungvdb]

Công thức sum của bác hoamattroicoi đặt vào ô B4 chưa chính xác (kết quả đếm ô A4 phải bằng 2 thì nó ra là 1).
---------

Công thức sumproduct của bác tính cho từng ô chuẩn rồi. Tuy vậy, yêu cầu của đầu bài ra tính tổng xuất hiện ở tất cả các ô trong cột A mà bác.

 

[hoamattroicoi]

Công thức sum của bác hoamattroicoi đặt vào ô B4 chưa chính xác (kết quả đếm ô A4 phải bằng 2 thì nó ra là 1).

Đầu bài ra tính tổng xuất hiện ở tất cả các ô trong cột A mà bác.
---------

Công thức sumproduct của bác tính cho từng ô chuẩn rồi.

Với công thức 1 bạn quên mất dòng này tôi viết phía dưới :

Nhớ kết thúc bằng Ctrl+Shift+Enter

 

[trungvdb]

Trong công thức (1) của bác hoamattroicoi

=SUMPRODUCT((VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),2))=12)*1)

Nếu em thay Value = 1* tức là trở thành công thức (2) như sau

=SUMPRODUCT((1*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),2))=12)*1)

ra kết quả như nhau. Nhưng em không hiểu vai trò, ý nghĩa của 2 số 1 trong công thức (2) bởi chỉ cần xóa bất kỳ số 1 nào đó --> kết quả sai ngay,

Rất mong mọi người giúp em hiểu về sự cần thiết 2 số 1 trong đoạn

(SUMPRODUCT((1*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),2))=12)*1)
--------
Số 1 đằng trước (1*) có thể hiểu chuyển chuối thành Value, nhưng số 1 ở sau (*1) tại sao phải có nó nhỉ?

 

[nghiaphuc]

Trong công thức (1) của bác hoamattroicoi

=SUMPRODUCT((VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),2))=12)*1)

Nếu em thay Value = 1* tức là trở thành công thức (2) như sau

=SUMPRODUCT((1*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),2))=12)*1)

ra kết quả như nhau.

Nhưng em không hiểu vai trò, ý nghĩa của 2 số 1 trong công thức (2) bởi chỉ cần xóa bất kỳ số 1 nào đó --> kết quả sai ngay

- Số 1 phía trước dùng để nhân với hàm MID cho ra số thực sự để so sánh với 12.
- Số 1 phía sau dùng để nhân vào với các giá trị True, False của phép so sánh phía trước để trở thành số thực sự (1, 0). Khi đó, hàm SUMPRODUCT sẽ dựa vào kết quả chuyển đổi này để tính cho ra kết quả cuối cùng.

 

[ndu96081631]

Bài toán của em đặt ra là hãy đếm xem chuỗi 12 xuất hiện bao nhiêu lần trong cột A

Ví dụ: ô A1 = 123456 ---> tính là 1 lần
A6 = 96123 ---> Tính là 1 lần
ô A4 = 1212 ---> tính là 2 lần

----
(trong file em đính kèm minh họa kết quả là 4)

Xin hãy giúp em bằng 2 cách càng tốt (ưu tiên công thức vì em đang muốn trau dồi thằng này).

Chủ đề này hỏi hoài
Công thức tốt nhất cho bài này là:

=(LEN(A1)-LEN(SUBSTITUTE(A1,12,"")))/2

Cái "ngon lành" của nó là không hề bị bất cứ lỗi gì với bất cứ kiểu dữ liệu nào

 

[huuthang_bd]

Cẩn thận với công thức của bạn hoamattroicoi. Nếu muốn tìm số 11 thay vì số 12 thì sẽ có sự khác biệt lớn đấy.

 

[trungvdb]

Tại em đang mải nghĩ các trường hợp nên cứ hay phức tạp vấn đề (bị cuốn theo Row(Indirect("1:"&...)..., mà quên mất công thức rất hiệu quả này (vừa đọc hôm nọ của thày xong).

 

[hoamattroicoi]

Cẩn thận với công thức của bạn hoamattroicoi. Nếu muốn tìm số 11 thay vì số 12 thì sẽ có sự khác biệt lớn đấy.

Cám ơn anh, cụm Row(...) đó chính xác phải thay bằng {1,3,5,7....) mới chuẩn (nếu theo bạn trungvdb mô tả cách lấy số 12 ở trên)
@trungvdb : Với từng ấy bài viết ở trên thì bạn tìm được câu trả lời cho câu hỏi này :

tổng xuất hiện ở tất cả các ô trong cột A???

rùi chứ???
Chúc bạn mau tiến bộ về công thức nhé!

 

[trungvdb]

Em giải như sau:
- Name

Tmp =Sheet1!$A$1:$A$15

- Công thức tính tổng xác định:

=SUMPRODUCT(1*(LEN(Tmp)-LEN(SUBSTITUTE(Tmp,12,"")))/2)

Nhưng không biết có cách tối ưu hơn không. Rất mong nhận được sự chỉ bảo của các thày.

 

[ndu96081631]

Em giải như sau:
- Name

Tmp =Sheet1!$A$1:$A$15

- Công thức tính tổng xác định:

=SUMPRODUCT(1*(LEN(Tmp)-LEN(SUBSTITUTE(Tmp,12,"")))/2)

Nhưng không biết có cách tối ưu hơn không. Rất mong nhận được sự chỉ bảo của các thày.

Công thức này đâu cần phải nhân 1 chứ
Bản thân (LEN(Tmp)-LEN(SUBSTITUTE(Tmp,12,"")))/2 cho kết quả là 1 mảng với các phần tử là số rồi còn gì
--------------------------

Cám ơn anh, cụm Row(...) đó chính xác phải thay bằng {1,3,5,7....) mới chuẩn (nếu theo bạn trungvdb mô tả cách lấy số 12 ở trên)

Cũng chưa chắc! Sao biết chắc rằng số 12 xuất hiện tại vị trí 1, 3, 5... mà không phải là 2, 4, 6...

 

[hoamattroicoi]

Cũng chưa chắc! Sao biết chắc rằng số 12 xuất hiện tại vị trí 1, 3, 5... mà không phải là 2, 4, 6...

Đúng là chưa biết chắc nhưng sư phụ quên mất câu này em viết :

nếu theo bạn trungvdb mô tả cách lấy số 12 ở trên

 

[LinDan]

Mọi người có thể giải theo cách VBA được không ah? Xin cảm ơn rất nhiều.

 

[ndu96081631]

Mọi người có thể giải theo cách VBA được không ah? Xin cảm ơn rất nhiều.

Thì cũng vậy thôi!
Ví dụ đếm số 12 có trong Chuổi:
So = (Len(Chuổi) - Len(Replace(Chuổi, 12, "")))/2
Hàm Replace trong VBA tương đương với hàm SUBSTITUE trên bảng tính
----------------
Tuy nhiên tôi có 1 cách khác hơi "quái chiêu"
So = UBound(Split(Chuoi, 12))
Ẹc... Ẹc...

 

[hoamattroicoi]

Mọi người có thể giải theo cách VBA được không ah? Xin cảm ơn rất nhiều.

VBA thì để bạn trungvdb giúp bạn nhen, thanks!
Trong 1 thời gian rất ngắn mà bạn ấy làm chủ đc thuật toán của VBA và Dictionary quả thật là đáng khâm phục!

 

[trungvdb]

Code của bài theo gợi ý của sư phụ Ndu:

Function Dem(Rng As Range, KT As String) As Integer
   Dim i As Integer
   Dim Clls As Range
   For Each Clls In Rng
     i = i + (Len(Clls) - Len(Replace(Clls, KT, ""))) / 2
   Next
   Dem = i
End Function

--------

Tuy nhiên tôi có 1 cách khác hơi "quái chiêu"
So = UBound(Split(Chuoi, 12))
Ẹc... Ẹc...

Thày giúp em cách này với ah, em vẫn còn chưa thạo cái Split và Join cho lắm.

 

[hoamattroicoi]

Theo tôi thì chỉ cần thế này:

Function Dem(Clls As Range, KT As String) As Long
Dim i As Long
     Dem = (Len(Clls) - Len(Replace(Clls, KT, ""))) / 2
End Function

 

[ndu96081631]

Code của bài theo gợi ý của sư phụ Ndu:

Function Dem(Rng As Range, KT As String) As Integer
   Dim i As Integer
   Dim Clls As Range
   For Each Clls In Rng
     i = i + (Len(Clls) - Len(Replace(Clls, KT, ""))) / [COLOR=#ff0000][B]2[/B][/COLOR]
   Next
   Dem = i
End Function

Xem lại số 2 màu đỏ nha!
Sao bạn chắc là sẽ chia 2
Nếu người ta đếm số 123 hoặc đếm số 2 thôi thì sao?
-------------------------------

Thày giúp em cách này với ah, em vẫn còn chưa thạo cái Split và Join cho lắm.

Thì đại khái thế này:

Function CountText(ByVal sArray, ByVal StrCriteria As String) As Long
  Dim tmpArr(), Item, lCount As Long, tmp
  On Error Resume Next
  sArray = sArray
  If TypeName(sArray) <> "Variant()" Then
    ReDim tmpArr(0)
    tmpArr(0) = sArray
  Else
    tmpArr = sArray
  End If
  For Each Item In tmpArr
    tmp = Split(CStr(Item), StrCriteria)
    If UBound(tmp) > -1 Then lCount = lCount + UBound(tmp)
  Next
  CountText = lCount
End Function

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

Theo tôi thì chỉ cần thế này:

Function Dem(Clls As Range, KT As String) As Long
Dim i As Long
     Dem = (Len(Clls) - Len(Replace(Clls, KT, ""))) / 2
End Function

Đã viết thành UDF rồi thì viết luôn cho nhiều cell, chứ viết code cho 1 cell thì viết làm gì cho mất công

 

[trungvdb]

Theo tôi thì chỉ cần thế này:
Function Dem(Clls As Range, KT As String) As Long
Dim i As Long
Dem = (Len(Clls) - Len(Replace(Clls, KT, ""))) / 2
End Function

Không được đâu bác ah, không có next nó không thể len một lúc cả Range đâu.

(Len chỉ áp dụng với 1 Cell, chứ không áp dụng với 1 Range được)