Hướng dẫn excel not adding numbers correctly - excel không thêm số chính xác

Một số giá trị Microsoft Excel trông giống như số, nhưng không cộng lại. Hoặc, bạn không thể thay đổi định dạng cho các số Excel - nó luôn giữ nguyên. Hãy thử các giải pháp trong bài viết này, để sửa những con số bị hỏng.

Để biết các vấn đề định dạng ngày, hãy xem trang định dạng Fix Dates EXCEL.

Lưu ý: Để biết hướng dẫn về việc thay đổi các từ viết thành số [ví dụ: từ ba đến 3], hãy xem các từ thành số trong Excel.changing written words to numbers [e.g. from Three to 3], see Words to Numbers in Excel.

Trông giống như những con số, nhưng đừng cộng lại

Nếu bạn sao chép dữ liệu từ một chương trình khác hoặc từ tệp văn bản hoặc trang web, Excel có thể coi các số là văn bản. Trong Excel, các giá trị có thể trông giống như các số, nhưng chúng không hoạt động như số và không hiển thị tổng số chính xác, trong một công thức tổng, như bạn có thể thấy trong bảng tính bên dưới.

Ngoài ra, bạn không thể thay đổi định dạng số - nó luôn giữ nguyên, bất kể bạn cố gắng định dạng các ô như thế nào.

Trong ảnh chụp màn hình ở trên, các giá trị trong cột C trông giống như số, nhưng chúng không cộng lại. Có 3 ô có công thức:

• Tổng: Trong ô C7, tổng công thức bổ sung bằng 0, thay vì hiển thị câu trả lời đúng.: In cell C7, the total for the addition formula is zero, instead of showing the correct answer.
• Counta: Ở bên phải, trong ô F4, hàm Counta được sử dụng trong một công thức. Hàm này đếm bất kỳ ô nào không trống. Kết quả công thức là 4, cho thấy có 4 mục trong phạm vi của ô, C3: C6.: At the right, in cell F4, the COUNTA function is used in a formula. This function counts any cells that are not empty. The formula result is 4, which shows that there are 4 entries in that range of cells, C3:C6.
• Đếm: Tuy nhiên, chức năng đếm trong ô F5 cho thấy kết quả là không. Điều đó cho thấy rằng không có giá trị nào trong các ô C3: C6 được công nhận là số.: However, the COUNT function in cell F5 show a result of zero. That shows that none of the values in cells C3:C6 are recognized as numbers.

Chuyển đổi văn bản thành các số bằng dán đặc biệt

Đối với một số số "văn bản", bạn có thể khắc phục sự cố với Paste Special. Xem hướng dẫn video ngắn này, để xem các bước và các hướng dẫn bằng văn bản bên dưới. Bảng điểm video nằm trên trang.

Lưu ý: Nếu bạn cần làm điều này thường xuyên, bạn có thể sử dụng macro để tự động hóa tác vụ.

Dòng thời gian video

• 0:00 Giới thiệu
• 0:42 Kiểm tra nội dung ô
• 1:40 Kiểm tra thanh công thức
• 2:00 sửa các số
• 3:08 Nhận tệp mẫu

Cách sửa số văn bản

Để sửa các số được xem là văn bản, hãy làm theo các bước sau:

1. Nhấp chuột phải vào một ô trống và nhấp vào Sao chép
2. Chọn các ô chứa các số "văn bản"
3. Nhấp chuột phải vào một trong các ô được chọn và nhấp vào Dán đặc biệt
4. Hộp thoại đặc biệt dán mở ra
5. Trong phần dán, chọn các giá trị
6. Trong phần Hoạt động, chọn Thêm
7. Bấm OK

Mẹo: Sau khi các số đã được sửa, bạn có thể áp dụng định dạng, bằng cách sử dụng các lệnh định dạng số trên tab Home của Ribbon.: After the numbers have been fixed, you can apply formatting, by using the Number Format commands on the Ribbon's Home tab.

Chuyển đổi văn bản thành các số bằng VBA

Nếu bạn thường xuyên chuyển đổi văn bản thành các số, bạn có thể sử dụng một macro, giống như mô tả bên dưới.

Lưu trữ macro sau trong một sổ làm việc luôn mở, chẳng hạn như sổ làm việc cá nhân của bạn. Sau đó, thêm macro đó vào thanh công cụ truy cập nhanh của bạn hoặc vào một trong các tab trên ruy băng Excel. Để sửa các số "văn bản", chọn các ô và nhấp vào nút Macro của bạn.

Sub ConvertToNumbers[]
Dim rng As Range

'get constants in selected range
On Error Resume Next
Set rng = Selection _
  .SpecialCells[xlCellTypeConstants, 23]
On Error GoTo errHandler

If Not rng Is Nothing Then
  'copy blank cell outside used range
  Cells.SpecialCells[xlCellTypeLastCell] _
      .Offset[0, 1].Copy
  
  'add to selected cells
   rng.PasteSpecial Paste:=xlPasteValues, _
       Operation:=xlPasteSpecialOperationAdd
Else
  MsgBox "Could not find Constants in selection"
End If

exitHandler:
  Application.CutCopyMode = False
  Set rng = Nothing
  Exit Sub
errHandler:
  MsgBox "Could not change text to numbers"
  Resume exitHandler
End Sub 

Chuyển đổi ngày bằng thay thế tất cả

Đây là một cách khác để cố gắng khắc phục số vấn đề. Trong ví dụ này, các số là ngày và Excel không nhận ra chúng là ngày thực.

Như thể hiện trong ảnh chụp màn hình bên dưới, những ngày này được định dạng với các dấu gạch chéo, ở định dạng ngày: M/D/YYm/d/yy

Để khắc phục "ngày văn bản" ở định dạng này, bạn có thể cố gắng chuyển đổi chúng thành ngày thực bằng cách sử dụng tính năng tìm và thay thế Excel, để thay thế các dấu gạch chéo.

Thực hiện theo các bước này để thay thế các dấu gạch chéo, sẽ sửa ngày "văn bản":

1. Chọn các ô có chứa ngày
2. Trên tab Home của Ribbon, nhấp vào Tìm & Chọn, sau đó nhấp vào Thay thế
• Hoặc, sử dụng phím tắt, ctrl+hCtrl+H
3. Cửa sổ tìm và thay thế mở ra
• Nhấp vào tab Thay thế, nếu nó chưa được chọn
4. Trong hộp tìm kiếm, nhập một dấu gạch chéo phía trước: & nbsp; & nbsp;//
5. Trong hộp thay thế, nhập một dấu gạch chéo phía trước: & nbsp; & nbsp;//
6. Tiếp theo, nhấp vào nút Thay thế tất cả
7. Khi hộp thông báo xuất hiện, bấm OK, để xác nhận việc thay thế
8. Nhấp vào nút Đóng, để loại bỏ cửa sổ Tìm và thay thế

Mẹo: Sau khi ngày đã được sửa, bạn có thể áp dụng một định dạng ngày khác vào danh sách. Chọn tất cả các ô ngày cố định và sử dụng các lệnh định dạng số trên tab Home của Ribbon.: After the dates have been fixed, you can apply a different date format to the list. Select all of the fixed date cells, and use the Number Format commands on the Ribbon's Home tab.

Khắc phục các ký tự ẩn

Nếu bạn sao chép dữ liệu từ một trang web, dữ liệu đó có thể bao gồm các ký tự ẩn, một không gian không phá vỡ như vậy.

Trong Excel, một không gian không phá vỡ có mã ký tự 160 và mã đó không được cố định bởi một số kỹ thuật làm sạch số. Thay vào đó, bạn có thể tìm kiếm mã ký tự đó và thay thế nó bằng không có gì [một chuỗi trống].code of 160, and that code is not fixed by some of the number cleanup techniques. Instead, you can search for that character code, and replace it with nothing [an empty string].

Mẹo: Nếu bạn cần sửa các ký tự ẩn thường xuyên, bạn có thể sử dụng macro để tự động hóa tác vụ, như macro trong phần bên dưới

Thực hiện theo các bước này để xóa các ký tự ẩn, bằng cách sử dụng tìm và thay thế:

1. Chọn các ô chứa các số có ký tự ẩn
2. Trên tab Home của Ribbon, nhấp vào Tìm & Chọn [ở phía bên phải]
3. Trong danh sách thả xuống, nhấp vào thay thế
4. Nhấp vào Tìm hộp gì
5. Nhấn và giữ phím ALT và trên bàn phím số, nhập 01600160
• Không có gì sẽ xuất hiện trong hộp tìm kiếm, sau khi bạn nhập mã đó
6. Để thay thế bằng, để trống hộp
• Các ký tự ẩn với mã 160 sẽ được thay thế bằng không có gì [một chuỗi trống]
7. Nhấp vào Thay thế tất cả
8. Bấm OK để xác nhận người thay thế
9. Đóng cửa sổ Tìm và thay thế

Khắc phục các ký tự ẩn với VBA

Nếu bạn thường xuyên cần loại bỏ ký tự không gian không phá vỡ ẩn, bạn có thể sử dụng macro.

Lưu trữ macro sau trong một sổ làm việc luôn mở, chẳng hạn như sổ làm việc cá nhân của bạn. Sau đó, thêm một nút vào một thanh công cụ hiện có và gắn macro vào nút đó. Để sửa các số "văn bản", chọn các ô và nhấp vào nút thanh công cụ.

Sub CleanCode160[]
Dim rng As Range
Dim arr As Variant
Dim i As Long
Set rng = Selection
'removes character 160
'non-breaking space
'from selected cells
If rng.Cells.Count = 1 Then
   ReDim arr[1 To 1, 1 To 1]
   arr[1, 1] = rng.Value
Else
   arr = rng.Value
End If

For i = 1 To UBound[arr, 1]
   arr[i, 1] = Replace[arr[i, 1], Chr[160], ""]
Next i

rng.Value = arr

End Sub 

Chuyển đổi văn bản thành các số có văn bản thành các cột

Kỹ thuật nhanh này, sử dụng tính năng Excel Text to Cột, có thể sửa một số số mà Excel không nhận ra là số thực.

1. Chọn các ô chứa các số
2. Trên tab Dữ liệu của Ribbon, nhấp vào văn bản vào các cột
3. Hộp thoại chuyển đổi văn bản thành cột mở ra, hiển thị bước 1
4. Trong Bước 1, chọn Phân định được phân định dưới dạng loại tệp
5. Sau đó, nhấp vào nút Kết thúc

Trong một số trường hợp, điều đó thay đổi số văn bản thành số thực.

Nếu kỹ thuật đó không hoạt động cho dữ liệu của bạn, bạn có thể thử một trong các phương thức khác trên trang này.

Chuyển đổi tiền tệ bằng các phân tách khác nhau

Nếu một bảng tính có tiền tệ ở định dạng sử dụng các dấu phân cách khác nhau, hãy sử dụng lệnh văn bản sang cột để chuyển đổi các giá trị.

Ví dụ: nếu dữ liệu có số được hiển thị bằng tiền Đức - 987.654,32 - Cài đặt Excel của bạn có thể không nhận ra đó là số thực, vì các phân tách trong các số.987.654,32 -- your Excel settings might not recognize those as real numbers, because of the separators in the numbers.

• Tiền tệ Đức sử dụng một khoảng thời gian làm dấu phân cách hàng ngàn người và dấu phẩy là dấu phân cách thập phânperiod as the Thousands separator, and a comma as the Decimal separator
• Tiền tệ Hoa Kỳ sử dụng dấu phẩy làm dấu phân cách hàng ngàn và một khoảng thời gian là dấu phân cách thập phâncomma as the Thousands separator, and a period as the Decimal separator

Hãy thử các bước sau, để chuyển đổi dữ liệu thành tiền tệ của Đức, sang tiền tệ của Hoa Kỳ - 987.654.32 987,654.32

1. Chọn các ô chứa các số
2. Trên tab Dữ liệu của Ribbon, nhấp vào văn bản vào các cột
3. Hộp thoại chuyển đổi văn bản thành cột mở ra, hiển thị bước 1
4. Trong Bước 1, chọn Phân định được phân định dưới dạng loại tệp
5. Sau đó, nhấp vào nút Kết thúc
6. Trong một số trường hợp, điều đó thay đổi số văn bản thành số thực.
7. Nếu kỹ thuật đó không hoạt động cho dữ liệu của bạn, bạn có thể thử một trong các phương thức khác trên trang này.
8. Chuyển đổi tiền tệ bằng các phân tách khác nhau

Nếu một bảng tính có tiền tệ ở định dạng sử dụng các dấu phân cách khác nhau, hãy sử dụng lệnh văn bản sang cột để chuyển đổi các giá trị.

Ví dụ: nếu dữ liệu có số được hiển thị bằng tiền Đức - 987.654,32 - Cài đặt Excel của bạn có thể không nhận ra đó là số thực, vì các phân tách trong các số.

Tiền tệ Đức sử dụng một khoảng thời gian làm dấu phân cách hàng ngàn người và dấu phẩy là dấu phân cách thập phân

Tiền tệ Hoa Kỳ sử dụng dấu phẩy làm dấu phân cách hàng ngàn và một khoảng thời gian là dấu phân cách thập phân

Hãy thử các bước sau, để chuyển đổi dữ liệu thành tiền tệ của Đức, sang tiền tệ của Hoa Kỳ - 987.654.32

Nhấp vào nút Tiếp theo, hai lần [Bước 1 và Bước 2]

Trong bước 3, nhấp vào nút nâng cao

1. Chọn các ô chứa các số
2. Trên tab Dữ liệu của Ribbon, nhấp vào văn bản vào các cột
3. Hộp thoại chuyển đổi văn bản thành cột mở ra, hiển thị bước 1
4. Sau đó, nhấp vào nút Kết thúc
5. Trong một số trường hợp, điều đó thay đổi số văn bản thành số thực.
6. Nếu kỹ thuật đó không hoạt động cho dữ liệu của bạn, bạn có thể thử một trong các phương thức khác trên trang này.

Chuyển đổi tiền tệ bằng các phân tách khác nhau

Nếu một bảng tính có tiền tệ ở định dạng sử dụng các dấu phân cách khác nhau, hãy sử dụng lệnh văn bản sang cột để chuyển đổi các giá trị.

Ví dụ: nếu dữ liệu có số được hiển thị bằng tiền Đức - 987.654,32 - Cài đặt Excel của bạn có thể không nhận ra đó là số thực, vì các phân tách trong các số.

Tiền tệ Đức sử dụng một khoảng thời gian làm dấu phân cách hàng ngàn người và dấu phẩy là dấu phân cách thập phân

1. Tiền tệ Hoa Kỳ sử dụng dấu phẩy làm dấu phân cách hàng ngàn và một khoảng thời gian là dấu phân cách thập phân
2. Hãy thử các bước sau, để chuyển đổi dữ liệu thành tiền tệ của Đức, sang tiền tệ của Hoa Kỳ - 987.654.32
3. =IF[RIGHT[A1,1]="-",-VALUE[LEFT[A1,LEN[A1]-1]],VALUE[A1]]
4. Nhấp vào nút Tiếp theo, hai lần [Bước 1 và Bước 2]

Trong bước 3, nhấp vào nút nâng cao

Từ bộ phân cách thập phân thả xuống, chọn phân tách hiện đang được sử dụng trong các giá trị - "," [dấu phẩy] trong ví dụ này

Từ hàng ngàn dải phân cách thả xuống, chọn phân tách hiện đang được sử dụng trong các giá trị - "." [giai đoạn] trong ví dụ này

Nhấp vào OK, sau đó nhấp vào Kết thúc.

Chuyển đổi dấu hiệu trừ dấu hiệu

Sub TrailingMinus[]
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis, 
' modified by Tom Ogilvy
' = = = = = = = = = = = = = = = =
  Dim rng As Range
  Dim bigrng As Range

  On Error Resume Next
  Set bigrng = Cells _
     .SpecialCells[xlConstants, xlTextValues].Cells
  If bigrng Is Nothing Then Exit Sub

  For Each rng In bigrng.Cells
    If IsNumeric[rng] Then
      rng = CDbl[rng]
    End If
  Next
End Sub

Nếu bạn nhập các số có dấu trừ dấu vết, bạn có thể sử dụng một trong các kỹ thuật sau để chuyển đổi chúng thành các số âm.

Sử dụng văn bản cho các cột

1. Sử dụng một công thức
2. Sử dụng một macro
3. Văn bản vào các cột
4. Với tính năng văn bản thành các cột, các số được nhập có thể dễ dàng chuyển đổi các dấu hiệu thành số âm.
5. Chọn Dữ liệu> Văn bản vào các cột

Để xem cài đặt trừ dấu vết, nhấp vào Tiếp theo, nhấp vào Tiếp theo

Dưới đây là bảng điểm đầy đủ cho các số sửa lỗi không thêm video được hiển thị ở trên.

--------------------------------

Nếu bạn nhập hoặc sao chép dữ liệu vào Excel, chẳng hạn như sao kê ngân hàng, đôi khi các số không cộng vào chính xác. Chúng ta sẽ xem cách khắc phục điều đó.

Đây là một mẫu rất nhỏ của tài khoản ngân hàng. Chúng tôi đã có số kiểm tra và số lượng của mỗi séc.

Để tạo tổng cộng, tôi có thể đi đến tab Home, và ở bên phải, nhấp vào Autosum. Và thông thường, điều đó sẽ chọn bất kỳ số nào ở trên, nhưng trong trường hợp này thì không, vì vậy tôi sẽ chọn thủ công các số đó và nhấn Enter, và nó hiển thị bằng không. Vì vậy, mặc dù tôi đã có hàng trăm đô la, nhưng nó hiển thị bằng không.

Kiểm tra nội dung ô

Tôi sẽ thêm một vài chức năng khác trên bảng tính này và chỉ cần tìm hiểu những gì đang diễn ra trong các ô này.

Trong ô này, tôi sẽ nhận được tất cả mọi thứ trong các ô đó, cho dù đó là văn bản hay số.

Ở đây, tôi sẽ sử dụng giá trị mở của Counta Equals. Sau đó, tôi sẽ chọn các ô có số, đóng khung và nhấn Enter.

Bốn ô này có một cái gì đó trong đó. Nhưng có bao nhiêu trong số đó có số? Và để làm điều đó ở đây, chúng tôi sử dụng Counta.

Và trong ô này, tôi sẽ sử dụng đếm và nó chỉ đếm số. Vì vậy, bằng tính đếm khung mở, chọn bốn ô một lần nữa, đóng khung và nhấn ENTER.

Chúng tôi có bốn ô với một cái gì đó trong đó, nhưng không có ô nào có số.

Kiểm tra thanh công thức

Nếu tôi nhìn vào một trong những ô này và tìm kiếm trong thanh công thức,

Tôi có thể thấy số, nhưng phía trước số có một dấu nháy đơn và điều đó chỉ ra rằng đây là văn bản chứ không phải là một số.

Vì vậy, bất cứ điều gì chúng tôi đã tải xuống hoặc sao chép từ một nơi nào đó đều xuất hiện dưới dạng văn bản.

Sửa các số

Có một cách nhanh chóng chúng ta có thể sửa chữa điều này, mặc dù. Chúng tôi sẽ chọn một ô trống và sau đó sử dụng dán đặc biệt để dán nó qua các số này và nó sẽ thêm số 0 vào mọi thứ, điều này sẽ không có bất kỳ ảnh hưởng nào đến các giá trị này, nhưng sẽ thay đổi chúng từ văn bản sang các số với bước đơn giản đó.

Tôi sẽ chọn một ô trống và sao chép. Sau đó chọn các ô mà tôi muốn sửa và đi lên để dán, nhấp vào mũi tên thả xuống và đi xuống để dán đặc biệt. Và ở đây, tôi muốn thêm, vì vậy tôi sẽ chọn nó. Bấm OK.

Và bây giờ, tất cả đã thay đổi thành các con số, và chúng ta có thể thấy tổng cộng ở phía dưới.

Tôi có thể định dạng những thứ này để tất cả chúng xếp hàng độc đáo. Tôi cũng có thể định dạng cái này. Và bây giờ, chúng tôi có một tổng số chính xác và được định dạng độc đáo.

'------------

Nhấn vào đây, để quay lại các số sửa chữa không thêm video được hiển thị ở trên

Nhận tệp mẫu

Tải xuống tệp bị nén với dữ liệu mẫu và macro. Tệp nén ở định dạng XLSM và chứa các macro, vì vậy cho phép chúng kiểm tra mã.

Thêm hướng dẫn nhập dữ liệu

Lời khuyên nhập dữ liệu

Định dạng sửa lỗi ngày Excel

Điền vào các ô trống

Tăng số với dán đặc biệt

Thêm số vào nhiều ô

Video nhập dữ liệu Excel

Tại sao Autosum không tính toán chính xác?