programming excel

Cách cộng số ở định dạng text trong excel

Số không hàng đầu. Số thập phân. Thêm văn bản. số lớn. Ký tự lặp lại. Màu sắc. Ngày & Giờ

Excel có nhiều định dạng tích hợp sẵn mà bạn có thể sử dụng. Tiền tệ, Kế toán, Ngày, Giờ, Tỷ lệ phần trăm, v.v. Nếu bạn không thể tìm thấy định dạng phù hợp, bạn có thể tạo định dạng số tùy chỉnh

Số không hàng đầu

Ví dụ: bạn có thể có mã bao gồm 5 số. Thay vì nhập 00041, chỉ cần nhập 41 và để Excel thêm các số 0 ở đầu

1. Nhập giá trị 41 vào ô A1

2. Chọn ô A1, bấm chuột phải, rồi bấm Định dạng Ô

3. Chọn Tùy chỉnh

4. Nhập mã định dạng số sau. 00000

5. Nhấp vào OK

Ghi chú. Excel cung cấp cho bạn bản xem trước trực tiếp về cách số sẽ được định dạng [bên dưới Mẫu]

Kết quả

Ghi chú. ô A1 vẫn chứa số 41. Chúng tôi chỉ thay đổi giao diện của số này chứ không thay đổi chính số đó

Số thập phân

Bạn cũng có thể kiểm soát số lượng chữ số thập phân. Sử dụng 0 để hiển thị giá trị số nguyên gần nhất. Sử dụng 0. 0 cho một chữ số thập phân. Sử dụng 0. 00 cho hai chữ số thập phân, v.v.

1. Nhập giá trị 839. 1274 vào ô A1

2. Sử dụng mã định dạng số sau. 0. 00

Thêm văn bản

Bạn cũng có thể thêm văn bản vào các số của mình. Ví dụ: thêm "ft"

1. Nhập giá trị 839. 1274 vào ô A1

2. Sử dụng mã định dạng số sau. 0. 0 "ft"

Ghi chú. hãy nhớ rằng chúng tôi chỉ thay đổi giao diện của số này chứ không thay đổi chính số đó. Bạn vẫn có thể sử dụng số này trong tính toán của mình

số lớn

Bạn cũng có thể kiểm soát số lượng lớn. Sử dụng một dấu phẩy [,] để hiển thị hàng nghìn và sử dụng hai dấu phẩy [,,] để hiển thị hàng triệu

1. Nhập các giá trị sau vào các ô A1, B1, C1 và D1. 1000000, 2500000, 81000000 và 700000

2. Sử dụng mã định dạng số sau. 0. 0,, "M"

Ghi chú. chúng tôi đã sử dụng 0. 0 cho một chữ số thập phân và "M" để thêm chữ M

Ký tự lặp lại

Sử dụng dấu hoa thị [*] theo sau một ký tự để điền vào một ô bằng ký tự đó

1. Nhập Hi vào ô A1

2. Sử dụng mã định dạng số sau. @ *-

Ghi chú. biểu tượng @ được sử dụng để nhập văn bản

Màu sắc

Bạn có thể kiểm soát tất cả các số dương, số âm, giá trị 0 và văn bản cùng một lúc. Mỗi phần được phân tách bằng dấu chấm phẩy [;] trong mã định dạng số của bạn

1. Nhập các giá trị sau vào các ô A1, B1, C1 và A2. 5000000, 0, Chào và -5. 89

2. Sử dụng mã định dạng số sau. [Green]$#,##0_];[Red]$[#,##0];"zero";[Blue]"Văn bản. " @

Ghi chú. #,## dùng để thêm dấu phẩy cho số lớn. Để thêm khoảng trắng, hãy sử dụng dấu gạch dưới "_" theo sau là một ký tự. Độ dài của khoảng trắng sẽ là độ dài của ký tự này. Trong ví dụ của chúng tôi, chúng tôi đã thêm dấu ngoặc đơn "]". Kết quả là số dương thẳng hàng với số âm đặt trong ngoặc đơn. Sử dụng hai phần được phân tách bằng dấu chấm phẩy [;] để chỉ kiểm soát số dương và số âm. Sử dụng ba phần được phân tách bằng dấu chấm phẩy [;] để chỉ kiểm soát số dương, số âm và giá trị 0

Ngày và Giờ

Bạn cũng có thể kiểm soát ngày và giờ. Sử dụng một trong các định dạng Ngày hoặc Giờ hiện có làm điểm bắt đầu

1. Nhập giá trị 42855 vào ô A1

2. Chọn ô A1, bấm chuột phải, rồi bấm Định dạng Ô

3. Chọn Ngày và chọn Ngày dài

Ghi chú. Excel cung cấp cho bạn bản xem trước trực tiếp về cách số sẽ được định dạng [bên dưới Mẫu]

4. Chọn Tùy chỉnh

5. Thay đổi một chút mã định dạng số thành. mm/dd/yyyy, dddd

6. Nhấp vào OK

Kết quả

lưu ý chung. một định dạng số tùy chỉnh được lưu trữ trong sổ làm việc nơi bạn tạo nó. Nếu bạn sao chép một giá trị có định dạng số tùy chỉnh sang một sổ làm việc khác, thì giá trị đó cũng sẽ có sẵn trong sổ làm việ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 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 đó

Đố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ạ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ư 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

TỔNG. Trong ô C7, tổng số cho công thức cộng bằng 0, thay vì hiển thị câu trả lời đúng
ĐẾM. Ở bên phải, trong ô F4, hàm COUNTA được sử dụng trong 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, điều này cho thấy có 4 mục nhập trong phạm vi ô đó, C3. C 6
ĐẾM. Tuy nhiên, hàm COUNT trong ô F5 hiển thị kết quả bằng 0. Điều đó cho thấy rằng không có giá trị nào trong ô C3. C6 được công nhận là số

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 để xem các bước và hướng dẫn bằng văn bản bên dưới. Bản ghi video ở phía dưới trang

GHI CHÚ. Nếu bạn cần thực hiện việc 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 số
3. 08 Lấy File 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

Bấm chuột phải vào một ô trống, rồi bấm sao chép
Chọn các ô có chứa số "văn bản"
Bấm chuột phải vào một trong các ô đã chọn, rồi bấm Dán Đặc biệt
Hộp thoại Dán Đặc biệt mở ra
Trong phần Dán, chọn Giá trị
Trong phần Hoạt động, chọn Thêm
Nhấp vào OK

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 VBA

Nế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"

Chọn các ô chứa ngày tháng
Trên tab Trang đầu của Dải băng, hãy bấm vào Tìm & Chọn, sau đó bấm vào Thay thế

Hoặc, sử dụng phím tắt, Ctrl + H

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

Trong hộp Tìm gì, hãy nhập dấu gạch chéo lên. /
Trong hộp Thay thế bằng, hãy nhập dấu gạch chéo lên. /
Tiếp theo, nhấp vào nút Thay thế tất cả
Khi hộp thông báo xuất hiện, bấm OK, để xác nhận việc thay thế
Nhấp vào nút Đóng để đóng cửa sổ Tìm và Thay thế

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ự ẩ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, 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 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

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ế

Chọn các ô chứa các số có ký tự ẩn
Trên tab Trang chủ của Dải băng, bấm Tìm & Chọn [ở ngoài cùng bên phải]
Trong danh sách thả xuống, nhấp vào Thay thế
Nhấp vào hộp Tìm gì
Nhấn và giữ phím Alt, trên bàn phím số, nhập 0160

Không có gì sẽ xuất hiện trong hộp Tìm gì sau khi bạn nhập mã đó

Đối với Thay thế bằng, hãy để trống ô

Các ký tự ẩn có mã 160 sẽ được thay thế bằng không có gì [một chuỗi rỗng]

Nhấp vào Thay thế tất cả
Nhấn OK để xác nhận thay thế
Đóng cửa sổ Tìm và Thay thế

Sửa Kí Tự Ẩn Bằng VBA

Nế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ột

Kỹ 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

Chọn các ô có chứa số
Trên tab Dữ liệu của Dải băng, bấm Chuyển văn bản thành Cột
Hộp thoại Trình hướng dẫn Chuyển đổi Văn bản thành Cột mở ra, hiển thị Bước 1
Ở Bước 1, chọn Phân tách làm Loại tệp
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 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 nhau

Nế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ố

Đơn vị tiền tệ của Đức sử dụng dấu chấm làm dấu phân cách Hàng nghìn và dấu phẩy làm dấu phân cách Số thập phân
Đơn vị tiền tệ của Hoa Kỳ sử dụng dấu phẩy làm dấu phân cách Hàng nghìn và dấu chấm làm dấu phân cách Số thập phân

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

Chọn các ô có chứa số
Trên tab Dữ liệu của Dải băng, bấm Chuyển văn bản thành Cột
Hộp thoại Trình hướng dẫn Chuyển đổi Văn bản thành Cột mở ra, hiển thị Bước 1
Nhấp vào nút Tiếp theo, hai lần [Bước 1 và Bước 2]
Ở Bước 3, nhấp vào nút Nâng cao
Từ trình đơn thả xuống Dấu phân cách thập phân, hãy chọn dấu phân cách hiện được sử dụng trong các giá trị -- "," [dấu phẩy] trong ví dụ này
Từ trình đơn thả xuống Dấu phân cách hàng nghìn, hãy chọn dấu phân cách hiện đang được sử dụng trong các giá trị -- ". " [dấu chấm] trong ví dụ này
Nhấp vào OK, sau đó nhấp vào Kết thúc.

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

Sử dụng Văn bản thành Cột

Sử dụng một công thức

Sử dụng Macro

Văn bản thành Cột

Vớ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

Chọn các ô có chứa số
Chọn Dữ liệu>Văn bản thành Cột
Để xem cài đặt Trailing Minus, nhấp vào Tiếp theo, nhấp vào Tiếp theo
Ở Bước 3, nhấp vào nút Nâng cao
Chọn hộp 'Dấu trừ cho số âm', nhấp vào OK
Nhấp vào Kết thúc

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ức

Cả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 ví dụ này, số đầu tiên có dấu trừ ở cuối nằm trong ô A1
Chọn ô B1 và nhập công thức này
=IF[RIGHT[A1,1]="-",-VALUE[LEFT[A1,LEN[A1]-1]],VALUE[A1]]
Sao chép công thức xuống hàng dữ liệu cuối cùng

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ình

Trong 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, dana2@msn.com
' 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 CSV

Khi 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

Sao chép dữ liệu trong chương trình khác
Chuyển sang Excel
Bấm chuột phải vào ô mà quá trình dán sẽ bắt đầu, rồi bấm Dán Đặc biệt
Chọn Chỉnh sửa> Dán Đặc biệt
Chọn CSV, nếu nó xuất hiện trong danh sách và nhấp vào OK

Bảng điểm. Sửa các số không cộng lại

Đây là bản ghi đầy đủ cho video Fix Numbers That Don't Add đượ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ư 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ức

Nế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

' ----------

Nhấp vào đây, để quay lại video Fix Numbers That Don't Add được hiển thị ở trên

Lấy tệp mẫu

Tả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ã