Cách sửa một số trong công thức Excel
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 số Excel - nó luôn giữ nguyên. Hãy thử các giải pháp trong bài viết này, để khắc phục những số bị hỏng đó Show
Đối với các vấn đề về định dạng ngày, hãy xem trang Định dạng Sửa ngày trong Excel GHI CHÚ. Để được hướng dẫn chuyển chữ viết sang số (e. g. từ Ba đến 3), hãy xem Từ thành Số trong Excel Trông giống như những con số, nhưng không cộng lạiNế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ư số, nhưng chúng không hoạt động như số và không hiển thị tổng chính xác, trong công thức SUM, 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 ô 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ư những con số, nhưng chúng không cộng lại. Có 3 ô có công thức
Chuyển đổi văn bản thành số bằng Paste SpecialĐối với một số số "văn bản", bạn có thể khắc phục sự cố bằng Paste Special. Xem video hướng dẫn ngắn này để biết các bước và. Phần tiếp theo ở cuối trang GHI CHÚ. Nếu bạn cần làm điều này thường xuyên, bạn có thể Dòng thời gian video
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
Mẹo. Sau khi các số đã được cố định, 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 Trang chủ của Dải băng Chuyển văn bản thành số bằng VBANếu bạn thường xuyên chuyển đổi văn bản thành số, bạn có thể sử dụng macro, như macro được hiển thị bên dưới Lưu trữ macro sau trong 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 nhập Nhanh của bạn hoặc vào một trong các tab trên Dải băng Excel. Để sửa các số "văn bản", hãy 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 các số sự cố. 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ư được hiển thị trong ảnh chụp màn hình bên dưới, những ngày này được định dạng bằng dấu gạch chéo, ở định dạng ngày. tháng/ngày/năm Để sửa "ngày văn bản" ở định dạng này, bạn có thể thử 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ế của Excel để thay thế các dấu gạch chéo Thực hiện theo các bước sau để thay thế các dấu gạch chéo, điều này sẽ sửa ngày "văn bản"
Mẹo. Sau khi ngày đã được cố định, bạn có thể áp dụng định dạng ngày khác cho 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 Trang chủ của Dải băng Sửa các ký tự ẩnNế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, chẳng hạn như khoảng trắng không ngắt Trong Excel, một khoảng trắng không ngắt có mã ký tự là 160 và mã đó không được cố định bằng một số kỹ thuật xóa 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) Mẹo. Nếu cần sửa ký tự ẩn thường xuyên, bạn có thể, như macro ở phần bên dưới Làm theo các bước sau để xóa các ký tự ẩn bằng cách sử dụng Tìm và Thay thế
Sửa Kí Tự Ẩn Bằng VBANếu bạn thường xuyên cần xóa ký tự dấu cách không ngắt bị ẩn, bạn có thể sử dụng macro Lưu trữ macro sau trong 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 thanh công cụ hiện có và đính kèm macro vào nút đó. Để sửa các số "văn bản", hãy chọn các ô và nhấp vào nút trên 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 số với văn bản thành cộtKỹ thuật nhanh này, sử dụng tính năng Văn bản thành Cột của Excel, có thể khắc phục một số số mà Excel không nhận ra là số 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 hiệu quả với dữ liệu của bạn, bạn có thể thử một trong các phương pháp khác trên trang này Chuyển đổi tiền tệ với dấu phân cách khác nhauNếu một trang tính có tiền tệ ở định dạng sử dụng các dấu tách khác nhau, hãy sử dụng lệnh Văn bản thành Cột để chuyển đổi các giá trị Ví dụ: nếu dữ liệu có các số được hiển thị bằng đơn vị tiền tệ của Đức -- 987. 654,32 -- cài đặt Excel của bạn có thể không nhận ra đó là số thực do có dấu phân cách trong số
Hãy thử các bước sau, để chuyển đổi dữ liệu từ đơn vị tiền tệ của Đức sang đơn vị tiền tệ của Mỹ -- 987.654. 32
Chuyển đổi Trailing dấu trừNếu bạn nhập các số có dấu trừ ở cuối, bạn có thể sử dụng một trong các kỹ thuật sau để chuyển đổi chúng thành số âm Văn bản thành CộtVới tính năng Văn bản thành Cột, các số đã nhập có dấu trừ ở cuối có thể dễ dàng chuyển đổi thành số âm
Ghi chú. Nếu 'Dấu trừ cho số âm' được chọn, bạn có thể nhấp vào Kết thúc ở Bước 1 của trình hướng dẫn Văn bản thành Cột. Chuyển đổi Dấu trừ Dấu - Công thứcCảm ơn Bob Ryan, từ Simply Learning Excel, người đã gửi công thức này để sửa các số đã nhập có dấu trừ ở cuối Thực hiện theo các bước sau để tạo công thức, được hiển thị trong ảnh chụp màn hình bên dưới
Trong công thức, hàm RIGHT trả về ký tự cuối cùng trong ô A1 Nếu ký tự đó là dấu trừ, hàm VALUE trả về giá trị số ở bên trái của dấu trừ ở cuối Dấu trừ trước hàm VALUE thay đổi giá trị thành số âm. Chuyển đổi các dấu trừ theo sau theo chương trìnhTrong tất cả các phiên bản Excel, bạn có thể sử dụng macro sau để chuyển đổi các số có dấu trừ ở cuối Sub TrailingMinus() ' = = = = = = = = = = = = = = = = ' Use of CDbl suggested by Peter Surcouf ' Program by Dana DeLouis, [email protected] ' 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 Dán dưới dạng CSVKhi nhập dữ liệu, bạn có thể ngăn các số đã sao chép bị dán dưới dạng văn bản nếu bạn dán dữ liệu dưới dạng CSV
Bảng điểm. Sửa các số không cộng lạiĐây là bảng điểm đầy đủ cho hình trên ---------------------------- Nếu bạn nhập hoặc sao chép dữ liệu vào Excel, chẳng hạn như bảng sao kê ngân hàng, đôi khi các số không cộng lại chính xác. Chúng ta sẽ xem cách khắc phục điều đó Đây là một ví dụ rất nhỏ về tài khoản ngân hàng. Chúng tôi có số séc và số tiền của mỗi séc Để tạo tổng, tôi có thể chuyển đến tab Trang chủ và ở bên phải, nhấp vào Tự động tính tổng. 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 những số đó theo cách thủ công và nhấn Enter, và nó hiển thị số không. Vì vậy, mặc dù tôi đã có hàng trăm đô la, nhưng nó hiển thị số không Kiểm tra nội dung ôTôi sẽ thêm một số chức năng khác vào trang tính này và chỉ cần tìm hiểu điều gì đang diễn ra trong các ô này Trong ô này, tôi sẽ đếm 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 dấu ngoặc mở COUNTA bằng COUNTA. Sau đó, tôi sẽ chọn các ô có số, đóng dấu ngoặc và nhấn Enter Bốn tế bào này có một cái gì đó trong chúng. Nhưng có bao nhiêu trong số đó có số? Và trong ô này, tôi sẽ sử dụng COUNT và nó chỉ đếm số. Vì vậy, bằng COUNT dấu ngoặc mở, hãy chọn lại bốn ô, đóng dấu ngoặc và nhấn Enter Chúng tôi có bốn ô có thứ gì đó trong đó, nhưng không ô nào trong số đó có số Kiểm tra thanh công thứcNếu tôi nhìn vào một trong những ô này và tra cứu trong Thanh công thức, Tôi có thể nhìn thấy số, nhưng phía trước số có dấu nháy đơn và điều đó cho biết đây là văn bản chứ không phải số Vì vậy, bất cứ điều gì chúng tôi đã tải xuống hoặc sao chép từ đâu đó đều xuất hiện dưới dạng văn bản sửa sốTuy nhiên, có một cách nhanh chóng chúng ta có thể khắc phục điều này. Chúng ta sẽ chọn một ô trống, sau đó sử dụng tính năng dán đặc biệt để dán nó lên trên các số này và thao tác này sẽ thêm số 0 vào mọi thứ, thao tác 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 thành số 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 tới 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 cái đó. Nhấp vào OK Và bây giờ, tất cả những thứ này đã chuyển thành số và chúng ta có thể thấy tổng số ở dưới cùng Tôi có thể định dạng những thứ này để tất cả chúng đều thẳng hàng. Tôi cũng có thể định dạng cái này. Và bây giờ, chúng tôi có tổng số chính xác và được định dạng đẹp mắt ' ---------- Bấm vào đây, để quay lại hình trên Lấy tệp mẫuTải xuống tệp nén có dữ liệu mẫu và macro. Tệp nén có định dạng xlsm và chứa macro, vì vậy hãy bật chúng để kiểm tra mã |