Tách số & tính tổng

[Ăn mày dĩ vãng]

Tôi có 1 số ĐT, muốn tính thành 2 tổng:
1. Tổng của 4 số đầu
2. Tổng của 5 số cuối
Tôi dùng hàm MID để tách rồi cộng
Có cách nào ngắn hơn không ?
Xin cảm ơn !

 

[duongdv]

Công thức này bạn không phải tách số như trước nữa. Nhưng công thức hơi dài. Không biết có bác nào có cách hay hơn không?
Ví dụ B10 là ô chứa số điện thoại
- Tính tổng 4 số đầu
=VALUE(MID(B10,1,1))+VALUE(MID(B10,2,1)+VALUE(MID(B10,3,1))+VALUE(MID(B10,4,1)))
-Tính tổng 5 số cuối
=VALUE(MID(B10,5,1))+VALUE(MID(B10,6,1)+VALUE(MID(B10,7,1))+VALUE(MID(B10,8,1))+VALUE(MID(B10,9,1)))

 

[BNTT]

Công thức này bạn không phải tách số như trước nữa. Nhưng công thức hơi dài. Không biết có bác nào có cách hay hơn không?
Ví dụ B10 là ô chứa số điện thoại
- Tính tổng 4 số đầu
=VALUE(MID(B10,1,1))+VALUE(MID(B10,2,1)+VALUE(MID(B10,3,1))+VALUE(MID(B10,4,1)))
-Tính tổng 5 số cuối
=VALUE(MID(B10,5,1))+VALUE(MID(B10,6,1)+VALUE(MID(B10,7,1))+VALUE(MID(B10,8,1))+VALUE(MID(B10,9,1)))

Vẫn là cách của bạn, có thể làm cho ngắn hơn: Bỏ hết VALUE sang một bên đi, không cần thiết.

Tính tổng 4 số đầu

=MID(B10,1,1) + MID(B10,2,1) + MID(B10,3,1) + MID(B10,4,1)​

Tính tổng 5 số cuối

=MID(B10,5,1) + MID(B10,6,1) + MID(B10,7,1) + MID(B10,8,1) + MID(B10,9,1)​

 

[ndu96081631]

Tôi có 1 số ĐT, muốn tính thành 2 tổng:
1. Tổng của 4 số đầu
2. Tổng của 5 số cuối
Tôi dùng hàm MID để tách rồi cộng
Có cách nào ngắn hơn không ?
Xin cảm ơn !

Dùng cách này xem:
1> SUM 4 số đầu:

=SUMPRODUCT(1*MID($B$10,ROW($1:$4),1))

2> SUM 5 số cuối:

=SUMPRODUCT(1*MID($B$10,ROW($5:$9),1))

Có thể dùng cách này để tính tổng từ bất kỳ vị trí nào ---> Số càng nhiều công thức vẩn nhiêu đó mà thôi

 

[Ăn mày dĩ vãng]

Một vấn đề mới phát sinh là: Số ĐT bây giờ có cả đầu 11 số (bao gồm cả số 0).
1. Tổng 1: 4 số đầu (không tính số 0 ở đầu)
2. Tổng 2: 5 số cuối (cho đầu 10 số), hoặc 6 số cuối (cho đầu 11 số).
Tôi đã khắc phục bằng cách dùng hàm LEN để đếm số ký tự, sau đó dùng hàm IF.
Tuy nhiên như vậy công thức sẽ rất dài, có cách nào ngắn hơn không ?
Xin chân thành cảm ơn !

 

[ndu96081631]

Một vấn đề mới phát sinh là: Số ĐT bây giờ có cả đầu 11 số (bao gồm cả số 0).
1. Tổng 1: 4 số đầu (không tính số 0 ở đầu)
2. Tổng 2: 5 số cuối (cho đầu 10 số), hoặc 6 số cuối (cho đầu 11 số).
Tôi đã khắc phục bằng cách dùng hàm LEN để đếm số ký tự, sau đó dùng hàm IF.
Tuy nhiên như vậy công thức sẽ rất dài, có cách nào ngắn hơn không ?
Xin chân thành cảm ơn !

Tổng 5 hoặc 6 số cuối:

=SUMPRODUCT(1*MID($B11,ROW(INDIRECT("5:"&LEN(B11))),1))

 

[hoangdanh282vn]

Một vấn đề mới phát sinh là: Số ĐT bây giờ có cả đầu 11 số (bao gồm cả số 0).
1. Tổng 1: 4 số đầu (không tính số 0 ở đầu)
2. Tổng 2: 5 số cuối (cho đầu 10 số), hoặc 6 số cuối (cho đầu 11 số).
Tôi đã khắc phục bằng cách dùng hàm LEN để đếm số ký tự, sau đó dùng hàm IF.
Tuy nhiên như vậy công thức sẽ rất dài, có cách nào ngắn hơn không ?
Xin chân thành cảm ơn !

Tính tổng 1->4 số đầu :

=SUMPRODUCT(--MID(VALUE(B11),ROW($1:$4),1))

Tính tổng 5 hay 6 số cuối (cho dù co hay không có số không đầu):

=SUMPRODUCT(--MID(VALUE(B11),ROW(INDIRECT("5:"&LEN(VALUE(B11)))),1))

 

[BNTT]

Ủa, công thức của Hoàng Danh có khác gì của anh Ndu? Mà dùng cái *1 ngắn hơn dùng VALUE.
Nếu để chuyển text thành số, hầu như mình chỉ dùng *1, chả bao giờ rờ tới cái VALUE.

 

[hoangdanh282vn]

Ủa, công thức của Hoàng Danh có khác gì của anh Ndu? Mà dùng cái *1 ngắn hơn dùng VALUE.
Nếu để chuyển text thành số, hầu như mình chỉ dùng *1, chả bao giờ rờ tới cái VALUE.

Anh chưa xem kỹ rồi, khác nhau hoàn toàn chứ ạ.
Cái của em là chuyển nó thành số (tức là bỏ số 0 đầu) rồi sau đó mới tính tiếp.

Em nghĩ, theo như yêu cầu của tác giả thì "0986112548" sẽ cho ra 20 chứ không phải 21 như công thức của anh NDU ạ.

 

[Ăn mày dĩ vãng]

Có một số trang họ coi phong thủy cho số ĐT, tò mò tìm hiểu thì biết được cách tính theo Mai Hoa dịch, tôi dùng những hàm trên để lập quẻ Mai Hoa.
Vấn đề là: Nếu dùng công thức:
=SUMPRODUCT(1*MID($B$10,ROW($1:$4),1))
thì khi ấn F2 sẽ thấy nó tìm kiếm trong một vùng rất rộng, từ hàng 1 đến hàng 4.
File của tôi có 4 sheet, mỗi sheet khoảng 3.500 số ĐT, tôi thử dùng công thức trên cho 1 sheet thì thấy dung lượng tăng từ 8MB lên 11MB và chạy rất chậm so với công thức:
=MID(B10,1,1) + MID(B10,2,1) + MID(B10,3,1) + MID(B10,4,1)
Có cách nào giải quyết không ?

 

[ndu96081631]

Có một số trang họ coi phong thủy cho số ĐT, tò mò tìm hiểu thì biết được cách tính theo Mai Hoa dịch, tôi dùng những hàm trên để lập quẻ Mai Hoa.
Vấn đề là: Nếu dùng công thức:
=SUMPRODUCT(1*MID($B$10,ROW($1:$4),1))
thì khi ấn F2 sẽ thấy nó tìm kiếm trong một vùng rất rộng, từ hàng 1 đến hàng 4.
File của tôi có 4 sheet, mỗi sheet khoảng 3.500 số ĐT, tôi thử dùng công thức trên cho 1 sheet thì thấy dung lượng tăng từ 8MB lên 11MB và chạy rất chậm so với công thức:
=MID(B10,1,1) + MID(B10,2,1) + MID(B10,3,1) + MID(B10,4,1)
Có cách nào giải quyết không ?

Có cách: Dùng VBA ---> Bạn đưa file lên đi ---> Nhấn nút 1 nhát ----> Ra kết quả (khỏi sợ chậm và tốn dung lượng)

 

[Ăn mày dĩ vãng]

Gửi file nhờ anh dùng tuyệt chiêu VBA giúp cho.
Cảm ơn anh nhiều !

 

[ndu96081631]

Gửi file nhờ anh dùng tuyệt chiêu VBA giúp cho.
Cảm ơn anh nhiều !

Dử liệu nhiều, có lẻ dùng Sub sẽ hay hơn (vì đở tốn dung lượng)

Sub Test()
  Dim i As Long, Dong As Long, Cot As Long, Clls As Range
  Dim SDT As Range, Bang1 As Range, Bang2 As Range
  On Error GoTo Thoat
  Set SDT = Application.InputBox("Chon vung chua SDT", Type:=8)
  Set Bang1 = Application.InputBox("Chon bang 1", Type:=8)
  Set Bang2 = Application.InputBox("Chon bang 2", Type:=8)
  For Each Clls In SDT
    For i = 1 To Len(Clls)
      If i < 5 Then Cot = Cot + Mid(Clls, i, 1)
      If i >= 5 Then Dong = Dong + Mid(Clls, i, 1)
    Next
    Clls(, 2) = Bang1((Dong - 1) Mod 8 + 1, (Cot - 1) Mod 8 + 1)
    Clls(, 4) = ((Dong + Cot - 1) Mod 6) + 1
    Clls(, 3) = Bang2.Resize(, 1).Find(Clls(, 2), , , xlWhole)(, Clls(, 4) + 1)
    Dong = 0: Cot = 0
  Next
Thoat:
End Sub

 

[huuthang_bd]

Gửi file nhờ anh dùng tuyệt chiêu VBA giúp cho.
Cảm ơn anh nhiều !

Hoặc có thể dùng UDF:

Function Dau(No As String) As Integer
Dau = 0
For i = 1 To 4
Dau = Dau + Mid(No, i, 1)
Next
End Function

Function Cuoi(No As String) As Integer
Cuoi = 0
For i = 5 To Len(No)
Cuoi = Cuoi + Mid(No, i, 1)
Next
End Function

 

[ndu96081631]

Hoặc có thể dùng UDF:

Function Dau(No As String) As Integer
Dau = 0
For i = 1 To 4
Dau = Dau + Mid(No, i, 1)
Next
End Function

Function Cuoi(No As String) As Integer
Cuoi = 0
For i = 5 To Len(No)
Cuoi = Cuoi + Mid(No, i, 1)
Next
End Function

Như đã nói, nếu dùng UDF thì sẽ làm tăng dung lượng lên rất nhiều (với file chứa nhiều dử liệu).. vì vậy cách này không khả thi lắm
Mặc khác, nếu để tính tổng các chử số thì hàm trên nên gom lại thành 1, kiểu như hàm MID vậy

Function SumChar(NumText As String, Point1 As Long, Point2 As Long) As Long
  Dim i As Long
  For i = Point1 To Point2
    SumChar = SumChar + Mid(NumText, i, 1)
  Next i
End Function

Cú pháp sẽ là:
SumChar(C7,1,4) ---> Sum các chử số ở vị trí thứ 1 đến thứ 4
SumChar(C7,5,9) ---> Sum các chử số ở vị trí thứ 5 đến thứ 9
Có điều ra kết quả đến đây vẫn chưa xong đâu ---> Còn phải lấy phần dư của nó với 8, sau đó LOOKUP vào bảng dử liệu mới cho ra được kết quả cuối cùng