Gọi số từ cột số liệu gồm cả số và chữ... Sau đó tính tổng

[notmehp]

Em dùng phần mềm quản lý Export dữ liệu ra excel được Cột A và B như file đính kèm:
Thường số liệu ít em sẽ gõ tay lại cột số liệu như cột D rồi dùng hàm Sum để tính tổng.
Nhưng số liệu nhiều vài chục vài trăm thứ cước chi phí thì nhập tay khó quá.
Em muốn gọi ra số liệu sau đó tỉnh tổng để kiểm tra tổng số mà không biết phải làm thế nào.
Mong anh chị cao nhân giúp đỡ em với ạ

 

[siwtom]

Em dùng phần mềm quản lý Export dữ liệu ra excel được Cột A và B như file đính kèm:
Thường số liệu ít em sẽ gõ tay lại cột số liệu như cột D rồi dùng hàm Sum để tính tổng.
Nhưng số liệu nhiều vài chục vài trăm thứ cước chi phí thì nhập tay khó quá.
Em muốn gọi ra số liệu sau đó tỉnh tổng để kiểm tra tổng số mà không biết phải làm thế nào.
Mong anh chị cao nhân giúp đỡ em với ạ

Gọi số là gì vậy?
Tôi hiểu là có một chuỗi chứa trong thân mình một số. Và cần lọc, tách cái số đó ra? Hay là gọi: "Này số ơi, ra ăn kẹo nào"?

Lần sau bạn đặt tiêu đề là: "Tách số từ một chuỗi". Cũng khỏi cần "sau đó tính tổng" vì công thức bạn biết viết rồi còn gì?

1. Chọn ô D2, tức ô công thức đầu tiên

2.
Đặt name text

=CHAR(32)&$B2&CHAR(32)

Đặt name arr

=IF(MOD(ISNUMBER(--MID(text;ROW(INDIRECT("$2:$"&LEN(text)));1))+
ISNUMBER(--MID(text;ROW(INDIRECT("$1:$"&(LEN(text)-1)));1));2)=0;"";
ROW(INDIRECT("$2:$"&LEN(text))))

3. Công thức cho D2

=--MID(text;SMALL(arr;1);LARGE(arr;1)-SMALL(arr;1))

Nhược điểm: nếu phần mềm quản lý đổ vào chuỗi trong đó dùng ký tự "," là dấu thập phân thì chuỗi lọc ra sẽ có ký tự "," và nếu mang sang máy khác có thiết lập ký tự "." là dấu thập phân thì sẽ có lỗi.

Cách khắc phục: đổi công thức cho D2 thành

=MID(text;SMALL(arr;1);LARGE(arr;1)-SMALL(arr;1))

Tức xóa "--" ở đầu. Tiếp theo do tôi thích dùng notepad (luôn trong tầm tay) nên tôi sẽ copy cột D và dán vào notepad --> Edit --> Replace --> chuyển "," hoặc "." sang ký tự được thiết lập trên máy --> copy --> Paste Special ở Excel và chọn Values.
Thao tác trên chỉ làm 1 lần. Khi Excel đã ghi lòng tạc dạ là ở đấy là những số thì khi mang sang máy khác Excel sẽ tự thay đổi. Số, ngày tháng chỉ phải nhập đúng. Sau đó thì mang sang máy nào cũng sẽ đúng.
----------
Cũng có thể dùng code. Cách lọc, tách có nhiều, vd. dùng For duyệt để lấy "điểm cắt đầu" và "điểm cắt cuối" rồi dùng MID để cắt. Tôi dùng RegExp

1. Đặt trên sheet1 1 Button (Developer --> Insert --> Form Controls --> Button) --> chuột phải lên Button và chọn Assign Macro --> New

2. Toàn bộ nội dung của Module được tạo tại điểm 1

Function SystemDecimalSeparator() As String
Dim a As Double
    a = 3 / 2
    SystemDecimalSeparator = Mid(a, 2, 1)
End Function

Sub NumberFromString()
Dim arguments As String, arg, Arr, index As Long, re As Object, text As String
On Error GoTo end_
    arguments = InputBox("Hay nhap ten cot du lieu, ten cot ket qua va chi so dong dau o dang" & vbCrLf & _
                    "CotDuLieu,CotKetQua,ChiSoDongDau" & vbCrLf & _
                    "Vi du BE,BG,2", , "BE,BG,2")
    If arguments = "" Then GoTo end_
    arg = Split(arguments, ",")
    Arr = ActiveSheet.Range(arg(0) & arg(2) & ":" & arg(0) & ActiveSheet.Range(arg(0) & 65536).End(xlUp).Row)
    char = SystemDecimalSeparator
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "[\d,\.]+"
    For index = 1 To UBound(Arr)
        If re.test(Arr(index, 1)) Then
            text = Replace(re.Execute(Arr(index, 1)).Item(0), ".", char)
            text = Replace(text, ",", char)
            Arr(index, 1) = CDbl(text)
        Else
            Arr(index, 1) = 0
        End If
    Next
    ActiveSheet.Range(arg(1) & arg(2)).Resize(UBound(Arr)).Value = Arr
end_:
    Set re = Nothing
End Sub

Sub Button1_Click()
    NumberFromString
End Sub

3. Lưu tập tin ở dạng XLSM

4. Khi nhấn Button thì có InputBox nhẩy ra. Cần nhập tên cột dữ liệu, tên cột kết quả và chỉ số dòng dữ liệu đầu tiên - dấu phân cách là dấu phẩy. Trong trường hợp của bạn thì nhập B,D,2 hoặc b,d,2

5. Tôi không kiểm tra kỹ

 

[chuot0106]

Em dùng phần mềm quản lý Export dữ liệu ra excel được Cột A và B như file đính kèm:
Thường số liệu ít em sẽ gõ tay lại cột số liệu như cột D rồi dùng hàm Sum để tính tổng.
Nhưng số liệu nhiều vài chục vài trăm thứ cước chi phí thì nhập tay khó quá.
Em muốn gọi ra số liệu sau đó tỉnh tổng để kiểm tra tổng số mà không biết phải làm thế nào.
Mong anh chị cao nhân giúp đỡ em với ạ

Bạn dùng hàm http://www.trungtamdaotao.vn/2013/02/ham-extractnumber-tach-so-ra-khoi-chuoi.html để tách số ra khỏi chuỗi nhé.