Tách số và chữ ngẫu được sắp xếp ngẫu nhiên (1 người xem)

anh.nguyen287 · 4/5/12

Tính tổng các số có trong cột theo điều kiện

Ngày 6/5

Cảm ơn các bạn bây giờ mình cần hỏi như sau:

VD: AB5 + AC4 + AB + AC + 2.5A3 + 3B4 3B1.5 + 2C0.5

làm sao cộng tất cả các số đứng trước ký tự đầu tiên: đó là 2.5A3 + 3B4 3B1.5 + 2C0.5 = 10.5

làm sao cộng tất cả các số đứng sau ký tự đầu tiên: AB5 + AC4 + 2.5A3 + 3B4 3B1.5 + 2C0.5 = 18

Cụ thể như file đính kèm, cần tính khoảng 30 cột cho 1 tháng lương, cần tính tổng các số đứng trước ký tự đầu tiên của mỗi dòng và tổng các số đứng sau ký tự cuối cùng. Chủ yếu là áp dụng trên 2003

Cảm ơn các bạn

mhung12005 · 5/5/12

anh.nguyen287 đã viết:
Vd: A1: abc12, B1: d4, C1: hgdgvv4, D1: 6hb45hfh...
Các số và chữ xuất hiện ngẫu nhiên, làm sao có thể tách các dòng đó thành 1 bên số và 1 bên chữ

Khi tách thành số làm sao tính tổng luôn E = tổng A1+B1+c1+D1

Bạn xem file đính kèm nhé.

bebo021999 · 5/5/12

mhung12005 đã viết:
Bạn xem file đính kèm nhé.

Công thức của mhung12005:

=SUM(SUM(IFERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0)),SUM(IFERROR(--MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1),0)),SUM(IFERROR(--MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1),0)),SUM(IFERROR(--MID(D1,ROW(INDIRECT("1:"&LEN(D1))),1),0)))

Chỉ cần vầy thôi:

Mã:

=SUM(IF(ISNUMBER(--MID(A1&B1&C1&D1,ROW(INDIRECT("1:"&LEN(A1&B1&C1&D1))),1)),--MID(A1&B1&C1&D1,ROW(INDIRECT("1:"&LEN(A1&B1&C1&D1))),1),0))

Kết thúc bằng Ctrl-shift-enter

mhung12005 · 5/5/12

bebo021999 đã viết:
Công thức của mhung12005:

=SUM(SUM(IFERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0)),SUM(IFERROR(--MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1),0)),SUM(IFERROR(--MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1),0)),SUM(IFERROR(--MID(D1,ROW(INDIRECT("1:"&LEN(D1))),1),0)))

Chỉ cần vầy thôi:

Mã:

=SUM(IF(ISNUMBER(--MID(A1&B1&C1&D1,ROW(INDIRECT("1:"&LEN(A1&B1&C1&D1))),1)),--MID(A1&B1&C1&D1,ROW(INDIRECT("1:"&LEN(A1&B1&C1&D1))),1),0))

Kết thúc bằng Ctrl-shift-enter

Nếu thế thì em rút gọn thế này ah.

=SUM(IFERROR(--MID(A1&B1&C1&D1,ROW(INDIRECT("1:"&LEN(A1&B1&C1&D1))),1),0))

bebo021999 · 5/5/12

mhung12005 đã viết:
Nếu thế thì em rút gọn thế này ah.

Chắc gì chủ topic dùng 2007 nhỉ? Nếu dùng 2007 thì dùng IFERROR quả là ngắn hơn.

hoamattroicoi · 5/5/12

Em nghĩ anh và bạn mhung2005 hiểu sai ý tác giả, theo những gì tác giả mô tả có lẽ bạn ấy muốn tính E = A1+B1+C1+D1 =12+4+4+645 = 665 mới đúng??? Không dùng VBA thì yêu cầu này khó nhai, hic!

SA_DQ · 6/5/12

(/ấn đề là tác giả quá tiết kiệm lời, nếu cho biết D1 là bao nhiêu thì nhanh nhận được câu trả lời hơn.
Thôi, đành chờ tác giả vậy?

anh.nguyen287 · 6/5/12

Ngày 6/5

Cảm ơn các bạn bây giờ e cần hỏi như sau:

VD: AB5 + AC4 + AB + AC + 2.5A3 + 3B4 3B1.5 + 2C0.5

làm sao cộng tất cả các số đứng trước ký tự đầu tiên: đó là 2.5A3 + 3B4 3B1.5 + 2C0.5 = 10.5

làm sao cộng tất cả các số đứng sau ký tự đầu tiên: AB5 + AC4 + 2.5A3 + 3B4 3B1.5 + 2C0.5 = 18

Cụ thể như file đính kèm, cần tính khoảng 30 cột cho 1 tháng lương, cần tính tổng các số đứng trước ký tự đầu tiên của mỗi dòng và tổng các số đứng sau ký tự cuối cùng. Chủ yếu là áp dụng trên 2003

Cảm ơn các bạn

mhung12005 · 6/5/12

anh.nguyen287 đã viết:
Ngày 6/5

Cảm ơn các bạn bây giờ e cần hỏi như sau:

VD: AB5 + AC4 + AB + AC + 2.5A3 + 3B4 3B1.5 + 2C0.5

làm sao cộng tất cả các số đứng trước ký tự đầu tiên: đó là 2.5A3 + 3B4 3B1.5 + 2C0.5 = 10.5

làm sao cộng tất cả các số đứng sau ký tự đầu tiên: AB5 + AC4 + 2.5A3 + 3B4 3B1.5 + 2C0.5 = 18

Cụ thể như file đính kèm, cần tính khoảng 30 cột cho 1 tháng lương, cần tính tổng các số đứng trước ký tự đầu tiên của mỗi dòng và tổng các số đứng sau ký tự cuối cùng. Chủ yếu là áp dụng trên 2003

Cảm ơn các bạn

Bạn ơi, mình có thấy cái file đính kèm nào của bạn đâu nhỉ. Bạn tải lại file lên nhé.

anh.nguyen287 · 6/5/12

Đã úp.

Mình dùng =IF(ISERROR(LOOKUP(9^99,--LEFT(C9,ROW(1:96)))),"",LOOKUP(9^99,--LEFT(C9,ROW(1:96)))) và dùng Sum thì ra tổng rồi.

Ra được từng ô nhưng có cách nào chỉ 1 lần thì ra tổng được hết các cột đó.

CẢm ơn các bạn

Lê Duy Thương · 6/5/12

anh.nguyen287 đã viết:
Ngày 6/5

Cảm ơn các bạn bây giờ e cần hỏi như sau:

VD: AB5 + AC4 + AB + AC + 2.5A3 + 3B4 3B1.5 + 2C0.5

làm sao cộng tất cả các số đứng trước ký tự đầu tiên: đó là 2.5A3 + 3B4 3B1.5 + 2C0.5 = 10.5

làm sao cộng tất cả các số đứng sau ký tự đầu tiên: AB5 + AC4 + 2.5A3 + 3B4 3B1.5 + 2C0.5 = 18

hoamattroicoi đã viết:

Không dùng VBA thì yêu cầu này khó nhai, hic!

Nhấp chuột vào đây để mở rộng...

hỏng cần vba với dữ liệu mâũ này đâu còi

Tính tổng giá trị các số sau 1 hoặc 2 hoặc 3 ký tự

PHP:

=SUM(IFERROR(IFERROR(IFERROR(--RIGHT(C1:Z1,3),--RIGHT(C1:Z1,2)),--RIGHT(C1:Z1,1)),0))+SUM(IFERROR(IFERROR(IFERROR(--RIGHT(LEFT(C1:Z1,FIND(" ",C1:Z1)),3),--RIGHT(LEFT(C1:Z1,FIND(" ",C1:Z1)),2)),--RIGHT(LEFT(C1:Z1,FIND(" ",C1:Z1)),1)),0))

Tính tổng giá trị các số trước 1 hoặc 2 hoặc 3 ký tự

PHP:

=SUM(IFERROR(IFERROR(IFERROR(--LEFT(C1:Z1,3),--LEFT(C1:Z1,2)),--LEFT(C1:Z1,1)),0))+SUM(IFERROR(IFERROR(IFERROR(--LEFT(RIGHT(C1:Z1,LEN(C1:Z1)-FIND(" ",C1:Z1)),3),--LEFT(RIGHT(C1:Z1,LEN(C1:Z1)-FIND(" ",C1:Z1)),2)),--LEFT(RIGHT(C1:Z1,LEN(C1:Z1)-FIND(" ",C1:Z1)),1)),0))

anh.nguyen287 · 6/5/12

Nếu dùng trên 2003 thì sao anh

Lê Duy Thương · 6/5/12

anh.nguyen287 đã viết:
Nếu dùng trên 2003 thì sao anh

thì dùng name cho ngắn công thức lại

anh.nguyen287 · 6/5/12

Em không biết nhiều về excel, anh có thể hướng dẫn thêm cho e trên 2003

hoamattroicoi · 7/5/12

anh.nguyen287 đã viết:
Em không biết nhiều về excel, anh có thể hướng dẫn thêm cho e trên 2003

Xin hỏi công ty của bạn có bao nhiêu nhân viên vậy??? Nếu khoảng hơn 1000 nhân viên thì hãy quên cái công thức của anh Thương đi nhé vì bạn có uống hết vài ly cafe thì nó chạy cũng chưa xong mà còn cáu nữa chứ. Hiii. Bạn nên nhờ anh ấy viết VBA cho nhé, chạy roẹt cái xong.
Thanks!
(Có nhiều người giúp bạn rồi, vui nhen)

anh.nguyen287 · 7/5/12

Cũng khoảng đó, vì phần mềm nhân sự đang hư nên chuyển quua excel, vậy a giúp e cái VBA luôn nha, hi cảm ơn anh và các bạn

SA_DQ · 7/5/12

Chỉ tại ban đầu bạn nêu vấn đề chưa thật rõ lắm

Mình ra được từng ô nhưng có cách nào chỉ 1 lần thì ra tổng được hết các cột đó.

Dùng hàm tự tạo này xem sao nhe

Lê Duy Thương · 7/5/12

hoamattroicoi đã viết:
Nếu khoảng hơn 1000 nhân viên thì hãy quên cái công thức của anh Thương đi nhé vì bạn có uống hết vài ly cafe thì nó chạy cũng chưa xong mà còn cáu nữa chứ.

chưa chắc à nha thử xem file đính kèm đã trên 1000 người mà chỉ hết 4 giây

hoamattroicoi · 7/5/12

Lê Duy Thương đã viết:
chưa chắc à nha thử xem file đính kèm đã trên 1000 người mà chỉ hết 4 giây

Chà anh kỳ công quá, thật ngưỡng mộ. Cảm ơn anh! 4s cũng tùy và tốc độ xử lý của mỗi máy nữa anh ạ và 4s với điều kiện trong file chỉ có mỗi sheet tính toán những số liệu đó. Em nghĩ trong file của bạn ấy sẽ có nhiều dữ liệu và công thức tính toán khác nữa. Vậy thì 4s chỉ với dữ liệu mẫu trên và cũng chỉ lấy được tối đa 3 ký tự số (nếu không có dấu thập phân) phải không anh nhỉ???

Cảm ơn bác SA_DQ rất nhiều về bài VBA, đọc bài của bác bé Còi lại học được vài chiêu mới.

anh.nguyen287 · 7/5/12

Toàn siêu excel, xin mời anh chị vào SG uống beer

fendaica · 18/7/12

hoamattroicoi đã viết:
Em nghĩ anh và bạn mhung2005 hiểu sai ý tác giả, theo những gì tác giả mô tả có lẽ bạn ấy muốn tính E = A1+B1+C1+D1 =12+4+4+645 = 665 mới đúng??? Không dùng VBA thì yêu cầu này khó nhai, hic!

Em có bài toán tương tự như ý của bác ở trên đề cập, mọi người giúp em với: (em dùng được 2007)

Chuỗi: Bàn,:20 Ghế,:24 Xe,:50 Dụng cụ,: 56

Nhờ các bác tách số ra và tính sum giúp em được không ạ: 20+24+50+56

Em cám ơn nhiều :x

siwtom · 18/7/12

fendaica đã viết:
Em có bài toán tương tự như ý của bác ở trên đề cập, mọi người giúp em với: (em dùng được 2007)

Chuỗi: Bàn,:20 Ghế,:24 Xe,:50 Dụng cụ,: 56

Nhờ các bác tách số ra và tính sum giúp em được không ạ: 20+24+50+56

Em cám ơn nhiều :x

Dữ liệu ví dụ thấy không có qui luật rõ ràng - có chỗ sau dấu ":" là chữ số, có chỗ sau ":" là dấu cách rồi mới đến chữ số. Nhưng không sao. Hàm dưới đây tính tổng tất cả các số tự nhiên (mà bài của bạn thì số bàn ghế, bia, rượu thì là những số tự nhiên) có trong chuỗi. Không quan trọng chuyện trước chữ số đầu và sau chữ số cuối của mỗi số là ký tự gì. Trong tương lai bạn có thể thay đổi cấu trúc dữ iệu tùy ý. vd. chuỗi

PHP:

Bàn,:20 Ghế,*24 Xe,-50 Dụng cụ,: (chỉ toàn hàng hiệu thôi nhé, hãng xịn mà) 56

mà không phải thay đổi công thức, code
Vào VBA --> Insert --> Module --> dán code dưới đây

Mã:

Function SumNumeric(ByVal text As String) As Long

    Dim objRegExp As Object
    Dim baonhieu As Long, match As Object
    
    Set objRegExp = CreateObject("VBScript.RegExp")
    With objRegExp
        .Pattern = "\d+"
        .Global = True
        If .Test(text) Then
            For Each match In .Execute(text)
                baonhieu = baonhieu + match.Value
            Next
        End If
    End With
    
    SumNumeric = baonhieu
    Set objRegExp = Nothing
End Function

Giả sử ô A1 có chuỗi, kết quả tại B1 thì công thức cho B1 là = SumNumeric(A1)
Có thể sửa code để:
1. tính các số (không nhất thiết nguyên dương) kể cả có phần thập phân, số âm
2. nhập bảng các chuỗi và tính tổng cho muỗi chuỗi - để đập một nhát xuống sheet. Đã làm được cho 1 chuỗi thì cũng làm được cho 1 mảng chuỗi.

Về hàm thì bạn tự test, tôi chỉ viết "nháp" thế thôi.

fendaica · 19/7/12

Trùi ui bác pro quá, em ngưỡng mộ quá ạ :x
Em cảm ơn nha

vanle33 · 19/7/12

anh.nguyen287 đã viết:
Ngày 6/5

Cảm ơn các bạn bây giờ mình cần hỏi như sau:

VD: AB5 + AC4 + AB + AC + 2.5A3 + 3B4 3B1.5 + 2C0.5

làm sao cộng tất cả các số đứng trước ký tự đầu tiên: đó là 2.5A3 + 3B4 3B1.5 + 2C0.5 = 10.5

làm sao cộng tất cả các số đứng sau ký tự đầu tiên: AB5 + AC4 + 2.5A3 + 3B4 3B1.5 + 2C0.5 = 18

Cụ thể như file đính kèm, cần tính khoảng 30 cột cho 1 tháng lương, cần tính tổng các số đứng trước ký tự đầu tiên của mỗi dòng và tổng các số đứng sau ký tự cuối cùng. Chủ yếu là áp dụng trên 2003

Cảm ơn các bạn

Bạn nên chú ý quy định của Diễn đàn nhé, không nên dùng quá nhiều chữ đậm màu đỏ, không là phạm quy đó!

Quang_Hải · 19/7/12

fendaica đã viết:
Em có bài toán tương tự như ý của bác ở trên đề cập, mọi người giúp em với: (em dùng được 2007)

Chuỗi: Bàn,:20 Ghế,:24 Xe,:50 Dụng cụ,: 56

Nhờ các bác tách số ra và tính sum giúp em được không ạ: 20+24+50+56

Em cám ơn nhiều :x

Góp thêm 1 hàm để cộng theo dữ liệu dạng này, và hàm này có thể tách số ra khởi chuỗi nếu xoá hết khoảng trắng trong chuỗi.

PHP:

Public Function Congso(cell As String) As Long
Dim i, tam, congvao
For i = 1 To Len(cell)
    If IsNumeric(Mid(cell, i, 1)) Then
        If Mid(cell, i + 1, 1) = " " Then congvao = "+"
    End If
    If IsNumeric(Mid(cell, i, 1)) Then
        tam = tam & Mid(cell, i, 1) & congvao
    End If
    congvao = Empty
Next
If Right(tam, 1) = "+" Then tam = Left(tam, Len(tam) - 1)
Congso = Evaluate("=" & tam)
End Function

Tách số và chữ ngẫu được sắp xếp ngẫu nhiên (1 người xem)

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

Thành viên mới

Thành viên chậm chạm

File đính kèm

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

Thành viên chậm chạm

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

Thành viên gắn bó

/(hông là gì!

Thành viên mới

Thành viên chậm chạm

Thành viên mới

Cạo lấy gì gội (Dịch quá không gội được)

File đính kèm

Thành viên mới

Cạo lấy gì gội (Dịch quá không gội được)

Thành viên mới

Thành viên gắn bó

Thành viên mới

/(hông là gì!

File đính kèm

Cạo lấy gì gội (Dịch quá không gội được)

File đính kèm

Thành viên gắn bó

Thành viên mới

Thành viên mới

Thành viên gắn bó

Thành viên mới

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

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

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