Tách và cộng các số trong một ô. (1 người xem)

DOSNET · 20/2/13

Chào các bạn trên GPE thân mến, đã lâu Dosnet không tham gia diễn đàn, giờ ngó lại công thức cái gì cũng thấy mới, thấy lạ. Có một bài loay hoay mãi mà chưa biết dùng hàm gì để tách được, nhờ các bạn giúp đỡ. Mình có các ô dữ liệu đã được nhập theo kiểu này, ví dụ nội dung trong một ô A1 nhé:

- 28/12/12 thu lãi 185k(đến 28/12)
- 7/1/13 thu lãi 185k(đến 7/1)
- 19/1/13 thu lãi 185k(đến 17/1)
- 28/1/13 thu 185k (27/1)
- 7/2/13 thu 370k (16/2)

Bây giờ mình muốn dùng công thức để cộng toàn bộ số tiền trong ô (số đứng trước ký tự "k" = ( 185+185+185+185+370). Nhờ các cao thủ giúp đỡ. Cảm ơn các bạn nhiều!

Quang_Hải · 20/2/13

DOSNET đã viết:
Chào các bạn trên GPE thân mến, đã lâu Dosnet không tham gia diễn đàn, giờ ngó lại công thức cái gì cũng thấy mới, thấy lạ. Có một bài loay hoay mãi mà chưa biết dùng hàm gì để tách được, nhờ các bạn giúp đỡ. Mình có các ô dữ liệu đã được nhập theo kiểu này, ví dụ nội dung trong một ô A1 nhé:

Bây giờ mình muốn dùng công thức để cộng toàn bộ số tiền trong ô (số đứng trước ký tự "k" = ( 185+185+185+185+370). Nhờ các cao thủ giúp đỡ. Cảm ơn các bạn nhiều!

Theo mình thì bài này dùng phương pháp thủ công cho gọn
1. Xử lý chuổi từ chữ k về bên phải
2. Xử lý chuỗi từ dấu cách về bên trái >> ta còn lại số tiền là cộng được rồi

Hic nhầm rồi, tưởng đâu dữ liệu trong từng cell

ndu96081631 · 20/2/13

DOSNET đã viết:
Chào các bạn trên GPE thân mến, đã lâu Dosnet không tham gia diễn đàn, giờ ngó lại công thức cái gì cũng thấy mới, thấy lạ. Có một bài loay hoay mãi mà chưa biết dùng hàm gì để tách được, nhờ các bạn giúp đỡ. Mình có các ô dữ liệu đã được nhập theo kiểu này, ví dụ nội dung trong một ô A1 nhé:

Bây giờ mình muốn dùng công thức để cộng toàn bộ số tiền trong ô (số đứng trước ký tự "k" = ( 185+185+185+185+370). Nhờ các cao thủ giúp đỡ. Cảm ơn các bạn nhiều!

Hỏi lại:
- Trong ô luôn chưa 5 dòng hay số dòng tùy ý?
- Xuống dòng bằng Alt + Enter chăng?
vân vân...
Nếu số dòng tùy ý thì e rằng phải dùng đến macro4 (hàm EVALUATE). Mà đã gọi là macro4 thì cũng là macro, vẫn phải Enable Macros mới chạy được hàm ----> Vậy thôi dùng VBA cho nó khỏe

DOSNET · 20/2/13

ndu96081631 đã viết:
Hỏi lại:
- Trong ô luôn chưa 5 dòng hay số dòng tùy ý?
- Xuống dòng bằng Alt + Enter chăng?
vân vân...
Nếu số dòng tùy ý thì e rằng phải dùng đến macro4 (hàm EVALUATE). Mà đã gọi là macro4 thì cũng là macro, vẫn phải Enable Macros mới chạy được hàm ----> Vậy thôi dùng VBA cho nó khỏe

- Số dòng tùy ý bác ah
- Chính xác là dùng Alt+enter

Nhờ bác giúp cái code cũng được ah. Đa tạ!

ndu96081631 · 20/2/13

DOSNET đã viết:
- Số dòng tùy ý bác ah
- Chính xác là dùng Alt+enter

Nhờ bác giúp cái code cũng được ah. Đa tạ!

Dùng VBA thì quá dễ
Làm chơi cái:

Mã:

Function SumSpec(ByVal Text As String) As Double
  Dim arr, aSub, tmp As String
  Dim i As Long, dT As Double
  On Error Resume Next
  arr = Split(Text, vbLf)
  For i = 0 To UBound(arr)
    tmp = arr(i)
    tmp = Mid(tmp, 1, InStrRev(tmp, "k"))
    tmp = Trim(tmp)
    tmp = Mid(tmp, InStrRev(tmp, " "))
    dT = dT + Val(tmp)
  Next
  SumSpec = dT
End Function

Dữ liệu khác hơn, sai ráng chịu nha

DOSNET · 20/2/13

ndu96081631 đã viết:
Dùng VBA thì quá dễ
Làm chơi cái:

Mã:

Function SumSpec(ByVal Text As String) As Double Dim arr, aSub, tmp As String Dim i As Long, dT As Double On Error Resume Next arr = Split(Text, vbLf) For i = 0 To UBound(arr) tmp = arr(i) tmp = Mid(tmp, 1, InStrRev(tmp, "k")) tmp = Trim(tmp) tmp = Mid(tmp, InStrRev(tmp, " ")) dT = dT + Val(tmp) Next SumSpec = dT End Function

Dữ liệu khác hơn, sai ráng chịu nha

Hiện tại đang phân biệt chữ Hoa và chữ thường, do dữ liệu có cả chữ "k" và "K", bác tư vấn sửa giúp. E mở rộng như này bác xem có điều chỉnh code được ko ạ?

- 28/12/12 thu lãi 185K(đến 28/12)
- 7/1/13 thu lãi 185k(đến 7/1)
- 19/1/13 thu lãi 185k(đến 17/1)
- 28/1/13 thu 185k + 200K (27/1)
- 7/2/13 thu 370k (16/2)

Quang_Hải · 20/2/13

DOSNET đã viết:
Hiện tại đang phân biệt chữ Hoa và chữ thường, do dữ liệu có cả chữ "k" và "K", bác tư vấn sửa giúp. E mở rộng như này bác xem có điều chỉnh code được ko ạ?

Góp thêm code này.
Mượn cái thư viện Reg để tránh dùng vòng lặp mà chẳng biết làm sao cả. Có bạn nào biết góp ý giúp.

PHP:

Function cong(cell As Range)
Dim tam, kq As Double, S
With CreateObject("vbscript.regexp")
   .Global = True
   .Pattern = "\s\d+\k"
   .Ignorecase = True
   Set tam = .Execute(cell)
   For Each S In tam
      kq = kq + Val(Replace(S, "k", ""))
   Next
End With
cong = kq
End Function

Hai Lúa Miền Tây · 20/2/13

DOSNET đã viết:
Hiện tại đang phân biệt chữ Hoa và chữ thường, do dữ liệu có cả chữ "k" và "K", bác tư vấn sửa giúp. E mở rộng như này bác xem có điều chỉnh code được ko ạ?

Thêm hàm Ucase vào là được.

Mã:

Function SumSpec(ByVal Text As String) As Double
  Dim arr, aSub, tmp As String
  Dim i As Long, dT As Double
  On Error Resume Next
  arr = Split(Text, vbLf)
  For i = 0 To UBound(arr)
    tmp = arr(i)
    tmp = Mid(tmp, 1, InStrRev(UCase(tmp), "K"))
    tmp = Trim(tmp)
    tmp = Mid(tmp, InStrRev(tmp, " "))
    dT = dT + Val(tmp)
  Next
  SumSpec = dT
End Function

DOSNET · 20/2/13

quanghai1969 đã viết:
Góp thêm code này.
Mượn cái thư viện Reg để tránh dùng vòng lặp mà chẳng biết làm sao cả

PHP:

Function cong(cell As Range) Dim tam, kq As Double, S With CreateObject("vbscript.regexp") .Global = True .Pattern = "\s\d+\k" .Ignorecase = True Set tam = .Execute(cell) For Each S In tam kq = kq + Val(Replace(S, "k", "")) Next End With cong = kq End Function

Thanks bác, chữ hoa và chữ thường OK rồi. em mới sửa lại data base (dòng thứ 4) chưa thấy bác điều chỉnh code

Quang_Hải · 20/2/13

DOSNET đã viết:
Thanks bác, chữ hoa và chữ thường OK rồi. em mới sửa lại data base (dòng thứ 4) chưa thấy bác điều chỉnh code

Code tự nhận ra mà, đâu cần sửa thêm gì

DOSNET · 20/2/13

quanghai1969 đã viết:
Code tự nhận ra mà, đâu cần sửa thêm gì

Bác hiểu sai ý em. Dữ liệu có dòng do nhân viên thu 2 lần nên viết chung vào một dòng. VD như chỗ e bôi đỏ thì cũng cộng thêm .

- 28/12/12 thu lãi 185K(đến 28/12)
- 7/1/13 thu lãi 185k(đến 7/1)
- 19/1/13 thu lãi 185k(đến 17/1)
- 28/1/13 thu 185k + 200K (27/1)
- 7/2/13 thu 370k (16/2)

Quang_Hải · 20/2/13

DOSNET đã viết:
Bác hiểu sai ý em. Dữ liệu có dòng do nhân viên thu 2 lần nên viết chung vào một dòng. VD như chỗ e bôi đỏ thì cũng cộng thêm .

Thì mình hiểu vậy mà. Đã test như vậy và code ok mà

DOSNET · 20/2/13

Thanks các bác đã nhiệt tình giúp đỡ. Code đã giải quyết vấn đề chính, dữ liệu có một số chỗ đặc biệt hơn em sẽ sửa tay để code đỡ phức tạp hơn.

ndu96081631 · 20/2/13

DOSNET đã viết:
Hiện tại đang phân biệt chữ Hoa và chữ thường, do dữ liệu có cả chữ "k" và "K", bác tư vấn sửa giúp. E mở rộng như này bác xem có điều chỉnh code được ko ạ?

Các hàm InStr, InStrRev, Replace đều cho phép so sánh chuổi theo nhiều kiểu, trong đó so sánh theo kiểu vbCompareText là không phân biệt hoa thường

Mã:

Function SumSpec(ByVal Text As String) As Double
  Dim arr, aSub, Item, tmp1 As String, tmp2 As String
  Dim i As Long, j As Long, dT As Double
  On Error Resume Next
  arr = Split(Text, vbLf)
  For i = 0 To UBound(arr)
    tmp1 = arr(i)
    tmp1 = Mid(tmp1, 1, InStrRev(tmp1, "k", , vbTextCompare))
    tmp1 = Trim(tmp1)
    If InStr(tmp1, " ") Then
      aSub = Split(tmp1, " ")
      For j = 0 To UBound(aSub)
        tmp2 = aSub(j)
        If InStr(1, tmp2, "k", vbTextCompare) Then
          tmp2 = Replace(tmp2, "k", "+0", , , vbTextCompare)
          dT = dT + Evaluate(tmp2)
        End If
      Next
    End If
  Next
  SumSpec = dT
End Function

ndu96081631 · 20/2/13

quanghai1969 đã viết:
Thì mình hiểu vậy mà. Đã test như vậy và code ok mà

Code của Hải có đoạn:
kq = kq + Val(Replace(S, "k", ""))
Thừa mà thiếu
Nên nhớ hàm Replace có cho phép so sánh chuổi theo nhiều kiểu khác nhau. Nếu ghi như trên là có phân biệt HOA thường... Cũng may, hàm Val đã giải quyết được
Vậy:
- Nếu dùng Replace thì phải ghi vầy: kq = kq + Replace(S, "k", "", , , vbTextCompare)
- Nếu dùng Val thì chỉ cần vầy: kq = kq + Val(S)
--------------
Ngoài ra, tést code thấy chưa chính xác.
- Chuổi dang: 6/2/13 thu 1450k + 100k (đến 4/2) sẽ cho kết quả = 1550 (đúng)
- Chuổi dạng: 6/2/13 thu 1450k+100k (đến 4/2) sẽ cho kết quả = 1450 (sai)
(chuổi đầu có khoảng trắng 2 đầu dấu +)

VMH0307 · 20/2/13

Nếu sử dụng công thức thì bạn có thể dùng như thế này:
đặt 1 name

Mã:

Data=SUBSTITUTE(TRIM(MID(SUBSTITUTE("K"&UPPER(Sheet1!$A$1),"K",REPT(" ",255)),ROW(INDIRECT("1:"&LEN(Sheet1!$A$1)-LEN(SUBSTITUTE(UPPER(Sheet1!$A$1),"K",""))))*255,255))," ",REPT(" ",255))

và công thức trong ô kết quả là

Mã:

=SUMPRODUCT(--RIGHT(data,255))

Ghi chú: cú pháp của dòng 4 cần có dấu cách sau dấu cộng (+) - 28/1/13 thu 185K + 200K (27/1)

siwtom · 20/2/13

quanghai1969 đã viết:
Thì mình hiểu vậy mà. Đã test như vậy và code ok mà

Hải à, sao pattern lại thế? pattern = "\s\d+\k"?

1. Tại sao lại \k? k là đủ, tức "\s\d+k"
2. Tại sao lại \s? Tôi chưa nghĩ kỹ nhưng "\d+k" là được. Bởi mặc định thì Reg Exp luôn cố trả về đoạn khớp dài nhất. Vậy nếu có vd. "a 12345k" thì nó chắc chắn trả về 12345k chứ không có chuyện trả về 2345k, 345k, 45k, 5k
Mà dùng thêm \s không chỉ là thừa. Nếu người nhập liệu sơ ý mà nhập vd. "...thu45k ..." thì có phải rách việc không? Người nhập liệu có thể sơ ý (mệt mỏi, đang nghĩ về ...) mà không gõ dấu cách chỗ này nhưng chỗ khác lại gõ thừa (2 dấu cách). Chuyện thường gặp mà.
3. Nhưng sao lại "\d+k" rồi sau đó lại replace? Như thế là mua việc.
Hải thử xem:

Mã:

pattern = "\d+(?=k)"
...
kq = kq + S

Dạng pattern như trên chắc chắn Hải sẽ hiểu. Tức "45k" thì chấp nhận chứ "45 k" thì là dữ liệu "lỗi"
Mà theo dạng pattern ta thấy kq là số nguyên dương nên thay vì kq as Double tôi nghĩ dùng kq as Long.

Tôi chỉ nhìn code rồi nhận xét thôi chứ không test gì cả. Hải thử xem lại xem.

Quang_Hải · 20/2/13

To anh Siwtom:
Cảm ơn anh đã hướng dẫn cái pattern này. Chắc chắn rất ít người am hiểu cái Reg như anh. Reg khó thật.
Cái Regexp này thật là thú vị. Mỗi lần chạm vào thì lại học được cái mới. Em e rằng cái mới vào thì cái cũ ra đi...
.............
Chỉnh lại như hướng dẫn của anh Siwtom thì thấy kết quả đúng. Chủ topic thử xem sao nhé.

PHP:

Function cong(cell As Range)
Dim tam, kq As Long, S
   With CreateObject("vbscript.regexp")
      .Global = True
      .Pattern = "\d+(?=k)"
      .Ignorecase = True
      Set tam = .Execute(cell)
      For Each S In tam
         kq = kq + S
      Next
   End With
cong = kq
End Function

To anh NDU:
Cảm ơn anh đã nhắc lại chức năng này của hàm Val
Em cũng đã có dịp xem qua chức năng của hàm Val vậy mà trong bài này lại quên mất cách ứng dụng của nó. Ăn tết có mấy ngày mà quên nhanh thật.

VetMini · 21/2/13

quanghai1969 đã viết:
To anh Siwtom:
Cảm ơn anh đã hướng dẫn cái pattern này. Chắc chắn rất ít người am hiểu cái Reg như anh. Reg khó thật.
Cái Regexp này thật là thú vị. Mỗi lần chạm vào thì lại học được cái mới. Em e rằng cái mới vào thì cái cũ ra đi...
.............
[....]

Regex được phát minh để giải quyết những vấn đề về chuỗi ký tự (text string). Vì vậy hầu như phần lớn (lưu ý: phần lớn chứ không phải là tất cả) các bài toán liên quan đên kiểm chứng hay tách chuỗi đều có thể giải quyết được bằng Regex một cách hữu hiệu. Nếu tôi nhớ không lầm thì Windows đưa cổ máy Regex (regex engine) vào script để đối phó với sự thành công rực rỡ của Perl và Unix (regex bắt đầu từ SNOBOL nhưng không thành công, chính Perl mới đưa nó lên đỉnh vinh quang hiện nay).

Trong quá trình tách chuỗi, nhất là có sử dụng "bắt mẩu con" (capture) thì luật "dòm trước ngó sau" (positive/negative look forward - look behind) rất quan trọng. Nắm vững được hai luật này sẽ giải quyết được rất nhiều vấn đề.

Rất tiếc là VBScript (và một số phần mềm) chỉ hổ trợ "dòm trước" (look ahead - như trong ví dụ bài này) chứ không hổ trợ kiểu "ngó sau" (look behind - tôi không hiểu tại sao, vị nào biết xin chỉ dẫn) cho nên khi dùng phải rán nhớ điểm này.

siwtom · 21/2/13

VetMini đã viết:
Rất tiếc là VBScript (và một số phần mềm) chỉ hổ trợ "dòm trước" (look ahead - như trong ví dụ bài này) chứ không hổ trợ kiểu "ngó sau" (look behind - tôi không hiểu tại sao, vị nào biết xin chỉ dẫn) cho nên khi dùng phải rán nhớ điểm này.

Tôi cũng không biết tại sao bác Bill lại không hỗ trợ lookbehind. Cái này chắc phải viết thư hỏi thôi.
Cũng chả cần nhớ là không hỗ trợ behind, không cần biết thế nào là behind thế nào là ahead.
Chỉ cần nhớ là có 4 dạng được hỗ trợ:
1. zero-width positive lookahead assertion (tiếng Anh, không nhớ): (?=pattern1)pattern2 và pattern2(?=pattern1)
2. zero-width negative lookahead assertion (tiếng Anh, không nhớ): (?!pattern1)pattern2 và pattern2(?!pattern1)

Tách và cộng các số trong một ô. (1 người xem)

Người dùng đang xem chủ đề này

Thành viên gắn bó

Thành viên gạo cội

Huyền thoại GPE

Thành viên gắn bó

Huyền thoại GPE

Thành viên gắn bó

Thành viên gạo cội

❆❆❆❆❆❆❆❆

Thành viên gắn bó

Thành viên gạo cội

Thành viên gắn bó

Thành viên gạo cội

File đính kèm

Thành viên gắn bó

Huyền thoại GPE

Huyền thoại GPE

Girls From Your City - Anonymous Casual Dating - N

Thành viên gắn bó

Thành viên gạo cội

Đang đi tìm hòn đá

Thành viên gắn bó

Thời gian đếm ngược.