Một vấn đề xuất hiện khá thường xuyên trong Excel là các số đã được nhập hoặc định dạng dưới dạng giá trị văn bản
Điều này có thể khiến nhiều người đau đầu khi cố gắng khắc phục sự cố tại sao một công thức như SUM không đưa ra câu trả lời chính xác
Mặc dù dữ liệu trông giống như một số, nhưng nó thực sự có thể là một giá trị văn bản và sẽ bị bỏ qua khỏi bất kỳ phép tính số nào như tổng
Trong bài đăng này, tôi sẽ chỉ cho bạn cách bạn có thể xác định các số được lưu dưới dạng giá trị văn bản và 7 cách bạn có thể sử dụng để chuyển đổi văn bản thành giá trị số thích hợp
Cách xác định các số được nhập dưới dạng văn bản
Làm cách nào để biết dữ liệu số của bạn đã được nhập dưới dạng văn bản hay chưa?
Có khá nhiều cách dễ dàng để nói
Căn lề trái
Cách đầu tiên bạn có thể biết liệu bạn có giá trị văn bản thay vì giá trị số hay không là cách dữ liệu được căn chỉnh trong ô
Theo mặc định, văn bản được căn trái và số được căn phải
Trừ khi định dạng căn chỉnh đã được thay đổi so với mặc định, đây sẽ là một cách dễ dàng để phát hiện các số đã được định dạng dưới dạng văn bản
Kiểm tra lỗi tam giác xanh
Nếu bạn không nhận ra mình đã nhập các số dưới dạng giá trị văn bản, điều đó có khả năng gây ra một số lỗi nghiêm trọng. Đây là lý do tại sao nó bị gắn cờ bởi trình kiểm tra lỗi tích hợp của Excel
Bất kỳ ô nào được gắn cờ có lỗi sẽ hiển thị một hình tam giác nhỏ màu xanh lá cây ở bên trái của ô để biểu thị lỗi một cách trực quan
Khi bạn chọn một ô như vậy, một biểu tượng cảnh báo nhỏ sẽ xuất hiện
Click vào cái này nó báo lỗi gì. Trong trường hợp này, bạn có thể thấy lỗi là Số được lưu dưới dạng văn bản
Bạn có thể tắt hoàn toàn tính năng kiểm tra lỗi này hoặc tùy chỉnh các lỗi được gắn cờ
Chuyển đến tab Tệp rồi chọn Tùy chọn ở dưới cùng. Thao tác này sẽ mở menu Tùy chọn Excel
Trong phần Công thức của menu Tùy chọn Excel ➜ Kiểm tra lỗi
Bỏ chọn tùy chọn Bật kiểm tra lỗi nền để tắt tính năng này. Bạn cũng có thể tùy chỉnh màu được sử dụng để chỉ ra lỗi tại đây
Định dạng văn bản được áp dụng
Một cách mà các số được nhập dưới dạng văn bản là khi ô đã được định dạng dưới dạng văn bản. Điều này khiến các giá trị được nhập dưới dạng giá trị văn bản bất kể chúng có phải là số hay không
Có một cách nhanh chóng để xác định xem một ô đã được áp dụng định dạng văn bản hay chưa
Chọn ô và chuyển đến tab Trang chủ. Bạn sẽ có thể xem liệu Văn bản có phải là định dạng đã chọn hay không trong menu thả xuống được tìm thấy trong phần Số của dải băng
Dấu nháy đơn trước
Một cách khác mà các số có thể được nhập dưới dạng văn bản là sử dụng dấu nháy đơn trước
Khi bạn nhập dấu nháy đơn ' làm ký tự đầu tiên trong một ô, mọi thứ sau đó sẽ được Excel coi là giá trị văn bản
Điều này có nghĩa là nếu bạn thấy dấu nháy đơn đứng đầu trong thanh công thức thì bạn biết dữ liệu là một giá trị văn bản
Sử dụng hàm ISTEXT
Excel có một chức năng mà bạn có thể sử dụng để kiểm tra xem một giá trị có phải là giá trị văn bản hay không
ISTEXT [ input ]- đầu vào là giá trị, ô hoặc phạm vi mà bạn muốn kiểm tra xem đó có phải là văn bản không
Hàm ISTEXT sẽ trả về TRUE nếu giá trị là văn bản và FALSE nếu không
= ISTEXT [ B3 ]Tại đây, bạn có thể thấy hàm ISTEXT đang được sử dụng để xác định xem các ô có chứa văn bản hay không. Hàm trả về TRUE khi tìm thấy giá trị văn bản
ISNUMBER [ input ]- đầu vào là giá trị, ô hoặc phạm vi mà bạn muốn kiểm tra xem đó có phải là số không
Tương tự, bạn có thể kiểm tra xem một giá trị có phải là số hay không bằng cách sử dụng hàm ISNUMBER. Nó nhận một đầu vào duy nhất và trả về TRUE khi giá trị là một số và FALSE nếu không
Ở đây, cùng một dữ liệu được kiểm tra bằng hàm ISNUMBER. Hàm trả về FALSE khi không tìm thấy số
Thanh trạng thái chỉ hiển thị số lượng
Một cách khác để nhanh chóng kiểm tra một dải ô để xem chúng có phải là toàn bộ văn bản hay không bằng cách sử dụng thanh trạng thái
Khi bạn chọn một dải giá trị số, Excel sẽ hiển thị cho bạn một số thống kê cơ bản như tổng, nhỏ nhất, tối đa và trung bình trong thanh trạng thái
Nếu tất cả chúng đều là số được nhập dưới dạng giá trị văn bản thì những thống kê cơ bản này sẽ không được tính toán. Thay vào đó, bạn sẽ chỉ thấy số lượng ô trong thanh trạng thái
Ghi chú. Nếu chỉ một trong các ô là giá trị số, bạn sẽ thấy toàn bộ thống kê trong thanh trạng thái
Chuyển đổi văn bản thành số bằng cách kiểm tra lỗi
Bạn đã thấy rằng bạn có thể sử dụng tính năng kiểm tra lỗi để phát hiện các số được nhập dưới dạng văn bản
Việc kiểm tra lỗi cũng sẽ cho phép bạn chuyển đổi các giá trị văn bản này thành các số thích hợp
Nhấp vào biểu tượng Lỗi và chọn Chuyển đổi thành Số từ các tùy chọn. Sau đó, Excel sẽ chuyển đổi từng ô trong phạm vi đã chọn thành một số
Ghi chú. Tùy chọn này có thể rất chậm khi bạn có một số lượng lớn ô cần chuyển đổi
Chuyển đổi văn bản thành số với văn bản thành cột
Thông thường, Text to Columns là để chia dữ liệu thành nhiều cột. Nhưng với thủ thuật này, bạn có thể sử dụng nó để chuyển định dạng văn bản sang định dạng chung
Văn bản thành Cột sẽ cho phép bạn chọn một ký tự phân cách để phân chia dữ liệu của bạn dựa trên. Nếu bạn bỏ chọn tùy chọn này và không chọn bất kỳ dấu phân cách nào thì nó sẽ không phân chia dữ liệu của bạn
Sau đó, bạn có thể chọn cách định dạng kết quả và đây là nơi bạn có thể chọn định dạng chung cho đầu ra
Làm theo các bước sau để sử dụng tính năng chuyển văn bản thành cột để chuyển đổi văn bản thành số
- Chọn các ô có chứa văn bản mà bạn muốn chuyển đổi thành số
- Chuyển đến tab Dữ liệu
- Nhấp vào lệnh Chuyển văn bản thành cột được tìm thấy trong tab Công cụ dữ liệu
- Chọn Được phân cách trong tùy chọn Kiểu dữ liệu gốc
- Nhấn nút Tiếp theo
- Nhấn nút Tiếp theo một lần nữa trong bước 2 của trình hướng dẫn Chuyển đổi Văn bản thành Cột. Bạn có thể giữ các tùy chọn mặc định
- Chọn Chung từ tùy chọn Định dạng dữ liệu cột
- Chọn một Đích nếu bạn muốn đặt các giá trị đã chuyển đổi ở một vị trí mới. Nếu không, bạn có thể giữ giá trị mặc định là ô hiện hoạt, điều này sẽ thay thế văn bản bằng các giá trị số
- Nhấn nút Kết thúc
Điều này sẽ chuyển đổi giá trị văn bản của bạn thành giá trị số
Lệnh Văn bản thành Cột thường được sử dụng để phân tách dữ liệu được phân tách bằng dấu phẩy hoặc dấu phân cách khác thành nhiều cột
Mọi dữ liệu được phân tách sẽ là dữ liệu văn bản, do đó, khi Excel chia dữ liệu thành nhiều cột, nó cũng chuyển đổi dữ liệu số thành số để thuận tiện hơn
Điều này cũng đúng ngay cả khi không có gì để chia
Vì vậy, trình hướng dẫn Văn bản thành Cột có thể được sử dụng để chuyển đổi các giá trị văn bản số thành số
Chuyển đổi văn bản thành số với nhân với 1
Ngay cả khi các giá trị được lưu trữ dưới dạng văn bản, bạn vẫn có thể thực hiện các phép tính số nhất định với chúng và kết quả của các phép tính này cũng sẽ là các giá trị số
Bạn có thể sử dụng điều này để chuyển đổi văn bản thành số bằng cách nhân chúng với 1. Vì bạn đang nhân với 1 nên nó sẽ không thay đổi các số mà sẽ chuyển đổi chúng
= B3 * 1Bạn có thể sử dụng công thức trên trong một ô liền kề và sao chép và dán nó xuống để chuyển đổi toàn bộ cột
Nếu bạn muốn xóa công thức sau đó, bạn có thể sao chép và dán chúng dưới dạng giá trị
Chuyển đổi văn bản thành số với Paste Special Multiply
Nhân với 1 là một thủ thuật hay để chuyển đổi văn bản thành giá trị, nhưng bạn có thể không muốn sử dụng công thức cho việc này
Tin tuyệt vời là có một cách dễ dàng để nhân văn bản của bạn với 1 mà không cần sử dụng công thức
Điều này có nghĩa là bạn có thể chuyển đổi văn bản của mình tại chỗ và bạn không cần sao chép và dán công thức dưới dạng giá trị trong một bước bổ sung
Bạn có thể sử dụng Paste Special Multiply để chuyển đổi các giá trị văn bản của mình. Làm theo các bước sau để nhân với 1 bằng Paste Special
- Nhập 1 vào một ô ở nơi khác trong trang tính. Số 1 thậm chí không cần phải là giá trị số và có thể là giá trị văn bản
- Sao chép ô chứa 1 bằng Ctrl + C trên bàn phím hoặc nhấp chuột phải và chọn Sao chép
- Chọn các ô có giá trị văn bản để chuyển đổi
- Nhấn Ctrl + Alt + V trên bàn phím hoặc nhấp chuột phải và chọn Dán Đặc biệt. Thao tác này sẽ mở menu Dán Đặc biệt
- Chọn Giá trị trong tùy chọn Dán và chọn Nhân trong tùy chọn Thao tác
- Nhấn nút OK
Điều này sẽ nhân tất cả các giá trị với 1 là giá trị trong ô được sao chép. Kết quả là, văn bản được chuyển đổi thành giá trị, nhưng vì bạn đang nhân với 1 nên các số sẽ không thay đổi
Chuyển văn bản thành số bằng hàm VALUE
Thực sự có một chức năng chuyên dụng mà bạn có thể sử dụng để chuyển đổi văn bản thành giá trị số
Hàm VALUE nhận một giá trị văn bản và trả về giá trị văn bản dưới dạng số
= VALUE [ text ]- văn bản là giá trị văn bản bạn muốn chuyển đổi thành giá trị số
Nếu văn bản chứa bất kỳ ký tự không phải số nào thì hàm VALUE sẽ trả về lỗi #VALUE. lỗi. Nó không trích xuất số từ văn bản, nó chỉ có thể chuyển đổi số được nhập dưới dạng văn bản thành số
= VALUE [ B3 ]Bạn có thể sử dụng công thức trên để chuyển văn bản trong ô B3 thành một số rồi sao chép và dán công thức để chuyển đổi toàn bộ cột
Chuyển đổi văn bản thành số bằng Power Query
Power Query là một công cụ tuyệt vời cho mọi loại chuyển đổi dữ liệu cần thiết. Nó chắc chắn cũng có thể được sử dụng để chuyển đổi văn bản thành số
Power Query được gõ mạnh. Điều này có nghĩa là các phép tính với kiểu dữ liệu không tương thích sẽ dẫn đến lỗi. Vì vậy, bạn sẽ không thể nhân các giá trị văn bản với 1 để chuyển đổi chúng thành số
Nhưng Power Query đi kèm với một cách dễ dàng để chuyển đổi các loại dữ liệu, bao gồm văn bản thành số
Làm theo các bước sau khi dữ liệu của bạn đã được nhập vào Power Query
Nhấp vào biểu tượng kiểu dữ liệu ở phía bên trái của tiêu đề cột cho các giá trị bạn muốn chuyển đổi
Mỗi cột trong trình soạn thảo Power Query có một biểu tượng kiểu dữ liệu hiển thị kiểu dữ liệu hiện tại của cột. Nếu cột chưa được gán kiểu dữ liệu thì biểu tượng hiển thị sẽ là ABC123
Có một số kiểu dữ liệu số có sẵn trong Power Query và kiểu dữ liệu bạn chọn sẽ phụ thuộc vào mức độ chính xác của số thập phân mà bạn cần
Trong trường hợp này, bạn có thể chọn tùy chọn Whole Number từ menu
Lưu ý rằng các giá trị trong cột hiện đã được căn phải và biểu tượng kiểu dữ liệu đã thay đổi?
= Table.TransformColumnTypes[Source,{{"Text", Int64.Type}}]Bạn sẽ thấy một bước Loại thay đổi mới đã được áp dụng cho truy vấn và nó sẽ sử dụng công thức mã M ở trên
Sau đó, bạn có thể nhấn nút Đóng và Tải trong tab Trang chủ để lưu kết quả và tải dữ liệu vào Excel
Bạn thậm chí có thể không cần áp dụng bước chuyển đổi loại dữ liệu này cho truy vấn của mình. Khi bạn nhập dữ liệu vào Power Query lần đầu tiên, nó thường sẽ đoán kiểu dữ liệu và áp dụng chuyển đổi cho bạn
Chuyển đổi văn bản thành số bằng Power Pivot
Nếu bạn muốn phân tích hoặc tóm tắt dữ liệu của mình sau khi chuyển đổi từ văn bản sang số, thì bạn có thể muốn thực hiện chuyển đổi trong Power Pivot
Power Pivot là một phần bổ trợ cho phép bạn phân tích hiệu quả hàng triệu hàng trong mô hình dữ liệu bằng Bảng Pivot
Với Power Pivot, bạn có thể tạo phép tính cấp hàng bằng ngôn ngữ công thức DAX để tạo cột mới trong bộ dữ liệu của mình. Sau đó, bạn có thể truy cập các cột mới này trong bảng tổng hợp được kết nối với mô hình dữ liệu
Trong trường hợp này, công thức DAX mà bạn có thể sử dụng để chuyển đổi văn bản thành số hoàn toàn giống với công thức trong lưới
Làm theo các bước sau bên trong phần bổ trợ Power Pivot để tạo một cột được tính toán mới chuyển đổi văn bản thành số
Chọn bất kỳ ô nào dưới tiêu đề Thêm cột
=VALUE[TextNumbers[Text]]Chèn công thức trên. TextNumbers là tên bảng dữ liệu trong Power Pivot và Text là tên cột muốn chuyển đổi
Nháy đúp vào tiêu đề cột để đổi tên
Nhấn biểu tượng Lưu và đóng phần bổ trợ Power Pivot
Giờ đây, bạn có thể tạo bảng tổng hợp từ mô hình dữ liệu và cột mới này sẽ có sẵn để sử dụng bên trong bảng tổng hợp giống như bất kỳ trường nào khác
- Chuyển đến tab Chèn
- Bấm vào nút PivotTable
- Chọn Từ Mô hình Dữ liệu trong các tùy chọn có sẵn
Cột được tính toán sẽ có sẵn trong danh sách các trường trong bảng tổng hợp của bạn và bạn sẽ có thể sử dụng nó giống như bất kỳ trường số nào khác
kết luận
Có nhiều lý do tại sao bạn có thể có dữ liệu chứa các số được định dạng dưới dạng văn bản
Nếu bạn muốn tự do sử dụng các giá trị này trong tính toán của mình, bạn sẽ cần chuyển đổi chúng thành số
Rất may, có nhiều tùy chọn dễ dàng để chuyển đổi văn bản thành số chẳng hạn như kiểm tra lỗi, dán đặc biệt, phép nhân cơ bản và hàm VALUE. Đây là tất cả những cách dễ dàng để chuyển đổi văn bản bên trong lưới
Nếu bạn đang nhập dữ liệu của mình bằng Power Query hoặc Power Pivot, bạn có thể thực hiện chuyển đổi bên trong mỗi công cụ này. Cả hai đều có sẵn các phương thức để chuyển đổi văn bản thành số
Bạn có sử dụng bất kỳ phương pháp nào trong số này để thay đổi giá trị văn bản thành số không?