Tách lấy số trong Lý trình trong nhiều trường hợp

vanle33 · 6/1/23

Như tiêu đề, tôi mong các thành viên giúp đỡ công thức tại cell C28 và C32 sheet "Cao do - Ho mong' với các trường hợp của dữ liệu khác nhau ở cột E sheet "Khai bao DL" với các yêu cầu:
1) Bằng công thức càng đơn giản càng tốt. Trong Excel 2010.
2) Với dữ liệu ở E9 sheet "Khai bao DL" thì kết quả đúng ở C28 sheet "Cao do - Ho mong" là 684,51
Với dữ liệu ở E35 sheet "Khai bao DL" thì kết quả đúng ở C28 sheet "Cao do - Ho mong" là 299,85
Với dữ liệu ở E36 sheet "Khai bao DL" thì kết quả đúng ở C28 sheet "Cao do - Ho mong" là 1.158,43
Với dữ liệu ở E38 sheet "Khai bao DL" thì kết quả đúng ở C28 sheet "Cao do - Ho mong" là 1.334,68
3) Cell C32 thì lấy các dữ liệu tương tự là các số ở bên phải các cell E9, E35, E36, E38 [Tôi đang dùng hàm RIGHT].
Xin trân trọng cảm ơn!

vanle33 · 30/1/23

Mong các thành viên chỉ giúp.

OverAC · 30/1/23

Chào, tôi thấy trong dữ liệu của bạn có 11 loại lý trình, vậy để đơn giản, bạn tính luôn điểm đầu, điểm cuối trong bảng "Khai Bao DL" rồi lấy giá trị đó qua vlookup luôn là gọn nhất.

vanle33 · 30/1/23

OverAC đã viết:
Chào, tôi thấy trong dữ liệu của bạn có 11 loại lý trình, vậy để đơn giản, bạn tính luôn điểm đầu, điểm cuối trong bảng "Khai Bao DL" rồi lấy giá trị đó qua vlookup luôn là gọn nhất.

Ý anh là nhập tay các số lý trình điểm đầu, điểm cuối vào 2 cột cạnh cột E của "Khai bao DL".
Có cách nào dùng công thức để lấy ra 02 giá trị đó từ cột E không a?

cantl · 30/1/23

vanle33 đã viết:
Ý anh là nhập tay các số lý trình điểm đầu, điểm cuối vào 2 cột cạnh cột E của "Khai bao DL".
Có cách nào dùng công thức để lấy ra 02 giá trị đó từ cột E không a?

Hàm này chỉ giải quyết được theo bài 1, thêm yêu cầu là tịt, lâu rồi tưởng bài này xong rồi chứ.
Nhưng mà nói thật, sao mà cảm giác cực rối rối, lại còn file thì xấu quá, tốt nhất là công ty mua phần mềm QLCL về dùng, nó in xẹt xẹt, đến tháng chỉ lãnh lương thôi.

Mã:

C28 =TEXT(VALUE(SUBSTITUTE(MID(A9;SEARCH("Km";A9)+3;8);"+";""));"#.###,00")
C32 =TEXT(VALUE(SUBSTITUTE(MID(A9;SEARCH("Km";A9;SEARCH("Km";A9)+1)+3;8);"+";""));"#.###,00")

vanle33 · 31/1/23

cantl đã viết:
Hàm này chỉ giải quyết được theo bài 1, thêm yêu cầu là tịt, lâu rồi tưởng bài này xong rồi chứ.
Nhưng mà nói thật, sao mà cảm giác cực rối rối, lại còn file thì xấu quá, tốt nhất là công ty mua phần mềm QLCL về dùng, nó in xẹt xẹt, đến tháng chỉ lãnh lương thôi.

Mã:

C28 =TEXT(VALUE(SUBSTITUTE(MID(A9;SEARCH("Km";A9)+3;8);"+";""));"#.###,00") C32 =TEXT(VALUE(SUBSTITUTE(MID(A9;SEARCH("Km";A9;SEARCH("Km";A9)+1)+3;8);"+";""));"#.###,00")

Cảm ơn bạn.
Với công thức của bạn đã giải quyết được 11 loại dữ liệu cột E sheet "Khai bao DL".
File chế nhiều lần cho các công trình khác nhau nên 'xấu' thật.
Mua phần mềm QLCL là 1 trong những ý hay. Để tôi nghiên cứu.
Bạn đang dùng phần mềm nào. Gợi ý cho tôi về cách mua + sử dụng với.

bebo021999 · 31/1/23

Một cách khác: Tự tạo hàm người dùng (UDF)
=KM(Ô chứa Text,n)
Với tham số n=1: Km bắt đầu; n=2: Km kết thúc
Click chuột phải tại tên sheet, chọn Insert/Module rồi dán code này vào:

PHP:

Option Explicit
Function KM(cellText As Range, n As Integer) As String
Dim val As String, st1 As String, st2 As String
Dim p1a&, p1b&, p2a&, p2b&
val = cellText.Value & " " ' cell chua text
p1a = InStr(1, val, "Km ") ' vi tri cua "Km" dau tien
If p1a = 0 Then ' neu khong co "Km" thi tra ve o trong
    KM = ""
    Exit Function
End If
p1b = InStr(InStr(p1a + 1, val, "+") + 1, val, " ") ' vi tri ky tu trang sau "Km"
st1 = Replace(Mid(val, p1a, p1b - p1a), ")", "") ' tach chuoi sau "Km"
p2a = InStr(p1b, val, "Km ") ' ' vi tri cua "Km" thu 2
p2b = InStr(InStr(p2a + 1, val, "+") + 1, val, " ") ' vi tri ky tu trang sau "Km" thu 2
If p2a > 0 Then
st2 = Replace(Mid(val, p2a, p2b - p2a), ")", "") ' tach chuoi sau "Km" thu 2
KM = IIf(n = 1, st1, st2) ' neu doi so =1 thi lay chuoi 1, 2 thì lay chuoi 2
Else
KM = st1 ' neu chi có 1 "Km" thi coi no la Km bat dau
End If
End Function

Tại ô C28, muốn chuyển lấy Km bắt đầu tại ô A9:
=KM(A9,1)
TẠi ô C32:
=KM(A9,2)

Tại sheet Testing, mình copy tất cả các dòng text và test công thức và tất cả OK.
Save as file dưới dạng .xlsm nhé.

hungpecc1 · 31/1/23

vanle33 đã viết:
Như tiêu đề, tôi mong các thành viên giúp đỡ công thức tại cell C28 và C32 sheet "Cao do - Ho mong' với các trường hợp của dữ liệu khác nhau ở cột E sheet "Khai bao DL" với các yêu cầu:
1) Bằng công thức càng đơn giản càng tốt. Trong Excel 2010.
2) Với dữ liệu ở E9 sheet "Khai bao DL" thì kết quả đúng ở C28 sheet "Cao do - Ho mong" là 684,51
Với dữ liệu ở E35 sheet "Khai bao DL" thì kết quả đúng ở C28 sheet "Cao do - Ho mong" là 299,85
Với dữ liệu ở E36 sheet "Khai bao DL" thì kết quả đúng ở C28 sheet "Cao do - Ho mong" là 1.158,43
Với dữ liệu ở E38 sheet "Khai bao DL" thì kết quả đúng ở C28 sheet "Cao do - Ho mong" là 1.334,68
3) Cell C32 thì lấy các dữ liệu tương tự là các số ở bên phải các cell E9, E35, E36, E38 [Tôi đang dùng hàm RIGHT].
Xin trân trọng cảm ơn!

* Khai code đầu xuân 2023
Với bài này tôi viết hàm tự tạo sử dụng Regular expression để nhìn code gọn hơn:

Mã:

Public Function Tachlytrinh(s, i)
    Dim str$, sMatch As IMatchCollection2
        str = Trim(CStr(s))
On Error Resume Next
    With New RegExp
        .Global = True
        .IgnoreCase = True
        .Pattern = "km\s*\d+\+(\d+\.*\d+)"
        If .Test(str) Then
            Set sMatch = .Execute(str)
            Tachlytrinh = sMatch.Item(i - 1).SubMatches(0)
        End If
    End With
End Function

hàm trong excel sẽ là
= Tachlytrinh("ô dữ liệu chứa số liệu lý trình cần lấy", "vị trí cần lấy (là 1 hoặc 2)" )
ví dụ :Tachlytrinh(A1,1)

***************
Chúc mừng năm mới vạn sự như ý

vanle33 · 31/1/23

@hungpecc1 : Tôi thử code thì trên máy tính của tôi báo lỗi như hình dưới

Bài đã được tự động gộp: 31/1/23

@bebo021999 : Em chờ công thức và code của bác cho vấn đề của em ở #1 mãi từ sau chủ đề "Tạo dãy số ngẫu nhiên không trùng lặp"!.
Cảm ơn bác. Nhưng sau khi chạy code ra kết quả là có chữ Km 0+.... hoặc Km 1+.... mà em chỉ cần kết quả là các con số như bài #1 em đã nói. để có thể tính toán được.

hungpecc1 · 31/1/23

vanle33 đã viết:
@hungpecc1 : Tôi thử code thì trên máy tính của tôi báo lỗi như hình dưới

Bài đã được tự động gộp: 31/1/23

@bebo021999 : Em chờ công thức và code của bác cho vấn đề của em ở #1 mãi từ sau chủ đề "Tạo dãy số ngẫu nhiên không trùng lặp"!.
Cảm ơn bác. Nhưng sau khi chạy code ra kết quả là có chữ Km 0+.... hoặc Km 1+.... mà em chỉ cần kết quả là các con số như bài #1 em đã nói. để có thể tính toán được.

Bạn vào Tool - References tìm và tích vào ô .... Regular Expression...

bebo021999 · 31/1/23

Em chờ công thức và code của bác cho vấn đề của em ở #1 mãi từ sau chủ đề "Tạo dãy số ngẫu nhiên không trùng lặp"!.
Cảm ơn bác. Nhưng sau khi chạy code ra kết quả là có chữ Km 0+.... hoặc Km 1+.... mà em chỉ cần kết quả là các con số như bài #1 em đã nói. để có thể tính toán được.

Sorry quên mất vụ này, thêm 1 chút nhé

PHP:

Option Explicit
Function KM(cellText As Range, n As Integer)
Dim val As String, st1 As String, st2 As String
Dim p1a&, p1b&, p2a&, p2b&
val = Replace(cellText.Value & " ", "Km ", "Km0") ' cell chua text
p1a = InStr(1, val, "Km0") + 2 ' vi tri cua "Km" dau tien
If p1a = 0 Then ' neu khong co "Km" thi tra ve o trong
    KM = ""
    Exit Function
End If
p1b = InStr(InStr(p1a + 1, val, "+") + 1, val, " ") ' vi tri ky tu trang sau "Km"
st1 = Replace(Mid(val, p1a, p1b - p1a), ")", "")  ' tach chuoi sau "Km"
p2a = InStr(p1b, val, "Km0") + 2 ' ' vi tri cua "Km" thu 2
p2b = InStr(InStr(p2a + 1, val, "+") + 1, val, " ") ' vi tri ky tu trang sau "Km" thu 2
If p2a > 0 Then
    st2 = Replace(Mid(val, p2a, p2b - p2a), ")", "") ' tach chuoi sau "Km" thu 2
    KM = IIf(n = 1, st1, st2) ' neu doi so =1 thi lay chuoi 1, 2 thì lay chuoi 2
Else
    KM = st1 ' neu chi có 1 "Km" thi coi no la Km bat dau
End If
If Left(KM, 2) = "0+" Then
    KM = Replace(KM, ",", "")
Else
    KM = Replace(KM, ",", ".")
End If
KM = CDbl(Split(KM, "+")(0) + Split(KM, "+")(1))
End Function

vanle33 · 31/1/23

hungpecc1 đã viết:
Bạn vào Tool - References tìm và tích vào ô .... Regular Expression...

Ra kết quả là các số chẵn bạn à. Ví dụ là 649 chứ không phải đúng cần là 649,26.

Bài đã được tự động gộp: 31/1/23

bebo021999 đã viết:
Sorry quên mất vụ này, thêm 1 chút nhé

Ra kết quả là 64926 ở C28 sheet "Cao do - Ho mong" chứ không phải là 649,26 bác a.

hungpecc1 · 31/1/23

vanle33 đã viết:
Ra kết quả là các số chẵn bạn à. Ví dụ là 649 chứ không phải đúng cần là 649,26.

Bài đã được tự động gộp: 31/1/23

Ra kết quả là 64926 ở C28 sheet "Cao do - Ho mong" chứ không phải là 649,26 bác a.

thử dùng dòng code dưới (hiệu chỉnh chỗ tô đỏ) xem kết quả ntn:
hoặc nếu không kết hợp thêm hàm round, hoặc hàm int lấy phần nguyên,....

Mã:

Public Function Tachlytrinh(s, i)
    Dim str$, sMatch As IMatchCollection2
        str = Trim(CStr(s))
On Error Resume Next
    With New RegExp
        .Global = True
        .IgnoreCase = True
        .Pattern = "km\s*\d+\+(\d+)\.*\d+"
        If .Test(str) Then
            Set sMatch = .Execute(str)
            Tachlytrinh = sMatch.Item(i - 1).SubMatches(0)
        End If
    End With
End Function

vanle33 · 1/2/23

hungpecc1 đã viết:
thử dùng dòng code dưới (hiệu chỉnh chỗ tô đỏ) xem kết quả ntn:
hoặc nếu không kết hợp thêm hàm round, hoặc hàm int lấy phần nguyên,....

Mã:

Public Function Tachlytrinh(s, i) Dim str$, sMatch As IMatchCollection2 str = Trim(CStr(s)) On Error Resume Next With New RegExp .Global = True .IgnoreCase = True .Pattern = "km\s*\d+\+(\d+)\.*\d+" If .Test(str) Then Set sMatch = .Execute(str) Tachlytrinh = sMatch.Item(i - 1).SubMatches(0) End If End With End Function

Với code trên thì ra kết quả chỉ còn số 64. Chứ không là 649,26

bebo021999 · 1/2/23

vanle33 đã viết:
Ra kết quả là 64926 ở C28 sheet "Cao do - Ho mong" chứ không phải là 649,26 bác a.

Mình ra 649.26. Nếu muốn 649,26 thì chỉnh phân cách thập phân thôi.

vanle33 · 1/2/23

bebo021999 đã viết:
Mình ra 649.26. Nếu muốn 649,26 thì chỉnh phân cách thập phân thôi.

Sau khi định dạng số là Number thì ra kết quả như hình dưới a.
Cell C28

ps: Có lẽ trên máy tính của bác hiển thị được là 649,26.
Chứ trên máy tính em thì khi nhấn F2 tại cell C28 rồi Enter thì ra kết quả là 64926

bebo021999 · 1/2/23

Lạ nhỉ.
Bạn post lại file bị sai xem nào
File mình vẫn đúng.

hungpecc1 · 1/2/23

vanle33 đã viết:
Với code trên thì ra kết quả chỉ còn số 64. Chứ không là 649,26

Lỗi này là do nguyên nhân người nhập liệu hoặc định dạng máy của bạn về ký hiệu phân tách phần thập phân cụ thể là dấu chấm (.) hoặc dấu phẩy (,)
có thể Hiệu chỉnh lại code đề phù hợp hơn như sau:

Public Function Tachlytrinh(s, i)
Dim str$, sMatch As IMatchCollection2
str = Trim(CStr(s))
On Error Resume Next
With New RegExp
.Global = True
.IgnoreCase = True
.Pattern = "km\s*\d+\+(\d+)(\.*|,*)\d*"
If .Test(str) Then
Set sMatch = .Execute(str)
Tachlytrinh = sMatch.Item(i - 1).SubMatches(0)
End If
End With
End Function

vanle33 · 1/2/23

@hungpecc1 : Dấu phân cách phần thập phân trên máy tính tôi đang là dấu chấm.
Thử code bạn vừa sửa ở #18 vẫn ra kết quả là 649

@bebo021999 : Em up file như em đã nói ở #16

hocexcel_1991 · 1/2/23

Bạn thử dùng công thức này
C28=TEXT(AGGREGATE(14,6,0+LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A9,"Km",REPT(" ",255),1),255)),"+",REPT(" ",255),1),255)),",",""),ROW($1:$20)),1),"###,##.00"),
C32=> thay chỗ màu đó số 1 thành số 2.

Tách lấy số trong Lý trình trong nhiều trường hợp

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

File đính kèm

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

Đỗ Nguyên Bình

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

!!! Giải thoát !!!

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

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ó

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

File đính kèm

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ạo cội

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

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

Bắt đầu học Excel

Facebook

Group