Nội dung
Nếu định dạng văn bản được đặt cho bất kỳ ô nào trên trang tính (điều này có thể được thực hiện bởi người dùng hoặc chương trình khi tải dữ liệu lên Excel), thì các số được nhập sau vào các ô này Excel sẽ bắt đầu coi là văn bản. Đôi khi những ô như vậy được đánh dấu bằng chỉ báo màu xanh lá cây mà bạn rất có thể đã thấy:
Và đôi khi một chỉ báo như vậy không xuất hiện (tệ hơn nhiều).
Nhìn chung, việc xuất hiện số dưới dạng văn bản trong dữ liệu của bạn thường dẫn đến rất nhiều hậu quả rất đáng tiếc:
- việc sắp xếp ngừng hoạt động bình thường – “số giả” bị vắt kiệt và không được sắp xếp theo thứ tự như mong đợi:
- chức năng gõ VLOOKUP (VLOOKUP) không tìm thấy các giá trị được yêu cầu, vì đối với chúng, số và cùng một số dưới dạng văn bản là khác nhau:
- khi lọc, số giả được chọn sai
- nhiều hàm Excel khác cũng ngừng hoạt động bình thường:
- và vv
Điều đặc biệt buồn cười là mong muốn tự nhiên chỉ thay đổi định dạng ô thành số lại không giúp ích được gì. Những thứ kia. theo nghĩa đen, bạn chọn các ô, nhấp chuột phải vào chúng, chọn Định dạng ô (Định dạng ô), thay đổi định dạng thành Số (số), vắt kiệt OK - và không có gì xảy ra! Ở tất cả!
Tất nhiên, có lẽ “đây không phải là một lỗi mà là một tính năng”, nhưng điều này không giúp chúng tôi dễ dàng hơn. Vì vậy, hãy xem xét một số cách để khắc phục tình trạng này – một trong số đó chắc chắn sẽ giúp ích cho bạn.
Cách 1. Góc chỉ báo màu xanh
Nếu bạn nhìn thấy góc chỉ báo màu xanh lục trên một ô có số ở định dạng văn bản thì hãy coi mình là người may mắn. Bạn có thể chỉ cần chọn tất cả các ô có dữ liệu và nhấp vào biểu tượng bật lên màu vàng có dấu chấm than, sau đó chọn lệnh Chuyển đổi sang số (Chuyển sang số):
Tất cả các số trong phạm vi đã chọn sẽ được chuyển đổi thành số đầy đủ.
Nếu không có góc xanh nào cả, hãy kiểm tra xem chúng có bị tắt trong cài đặt Excel của bạn không (Tệp – Tùy chọn – Công thức – Số được định dạng dưới dạng văn bản hoặc đứng trước dấu nháy đơn).
Cách 2: Nhập lại
Nếu không có nhiều ô thì bạn có thể thay đổi định dạng của chúng thành số, sau đó nhập lại dữ liệu để việc thay đổi định dạng có hiệu lực. Cách dễ nhất để làm điều này là đứng trên ô và nhấn các phím theo thứ tự F2 (vào chế độ chỉnh sửa, ô bắt đầu nhấp nháy con trỏ) và sau đó đăng ký hạng mục thi. Ngoài ra thay vì F2 bạn có thể chỉ cần nhấp đúp vào ô bằng nút chuột trái.
Không cần phải nói rằng nếu có nhiều ô thì phương pháp này tất nhiên sẽ không hiệu quả.
Phương pháp 3. Công thức
Bạn có thể nhanh chóng chuyển đổi số giả thành số bình thường nếu bạn tạo một cột bổ sung có công thức cơ bản bên cạnh dữ liệu:
Dấu trừ kép, trong trường hợp này, trên thực tế có nghĩa là nhân với -1 hai lần. Một dấu trừ bằng một dấu trừ sẽ cho một dấu cộng và giá trị trong ô sẽ không thay đổi, nhưng thực tế việc thực hiện một phép toán sẽ chuyển định dạng dữ liệu sang định dạng số mà chúng ta cần.
Tất nhiên, thay vì nhân với 1, bạn có thể sử dụng bất kỳ phép toán vô hại nào khác: chia cho 1 hoặc cộng và trừ XNUMX. Hiệu quả sẽ giống nhau.
Phương pháp 4: Dán đặc biệt
Phương pháp này được sử dụng trong các phiên bản Excel cũ hơn, khi những nhà quản lý hiệu quả hiện đại đã chui xuống gầm bàn về nguyên tắc chưa có góc chỉ báo màu xanh lá cây (nó chỉ xuất hiện vào năm 2003). Thuật toán là thế này:
- nhập 1 vào bất kỳ ô trống nào
- sao chép nó
- chọn các ô có số ở định dạng văn bản và thay đổi định dạng của chúng thành số (sẽ không có gì xảy ra)
- nhấp chuột phải vào các ô có số giả và chọn lệnh Dán đặc biệt (Dán đặc biệt) hoặc sử dụng phím tắt Ctrl + Alt + V
- trong cửa sổ mở ra, chọn tùy chọn Giá trị (Giá trị) и Nhân (Nhân)
Trên thực tế, chúng tôi thực hiện tương tự như trong phương pháp trước – nhân nội dung của các ô với một – nhưng không phải bằng công thức mà trực tiếp từ bộ đệm.
Phương pháp 5. Văn bản theo cột
Nếu các số giả cần chuyển đổi cũng được viết sai dấu phân cách thập phân hoặc dấu phân cách hàng nghìn thì có thể sử dụng một cách tiếp cận khác. Chọn phạm vi nguồn có dữ liệu và nhấp vào nút Văn bản theo cột (Chuyển văn bản thành cột) chuyển hướng Ngày (Ngày). Trên thực tế, công cụ này được thiết kế để chia văn bản cố định thành các cột, nhưng trong trường hợp này, chúng tôi sử dụng nó cho một mục đích khác.
Bỏ qua hai bước đầu tiên bằng cách nhấp vào nút Sau (Kế tiếp), và vào ngày thứ ba, sử dụng nút Ngoài ra (Nâng cao). Một hộp thoại sẽ mở ra nơi bạn có thể đặt các ký tự phân cách hiện có trong văn bản của chúng tôi:
Sau khi nhấp vào Kết thúc Excel sẽ chuyển đổi văn bản của chúng tôi thành số bình thường.
Phương pháp 6. Macro
Nếu bạn phải thực hiện những chuyển đổi như vậy thường xuyên thì việc tự động hóa quy trình này bằng một macro đơn giản là điều hợp lý. Nhấn Alt+F11 hoặc mở tab nhà phát triển (Nhà phát triển) Và bấm vào Visual Basic. Trong cửa sổ soạn thảo xuất hiện, thêm mô-đun mới thông qua menu Chèn - Mô-đun và sao chép mã sau vào đó:
Sub Convert_Text_to_Numbers() Selection.NumberFormat = "Chung" Selection.Value = Selection.Value End Sub
Bây giờ sau khi chọn phạm vi, bạn luôn có thể mở tab Nhà phát triển - Macro (Nhà phát triển - Macro), chọn macro của chúng tôi trong danh sách, nhấn nút chạy (Chạy) – và ngay lập tức chuyển đổi số giả thành số chính thức.
Bạn cũng có thể thêm macro này vào sổ macro cá nhân của mình để sử dụng sau này trong bất kỳ tệp nào.
PS
Câu chuyện tương tự cũng xảy ra với ngày tháng. Một số ngày cũng có thể được Excel nhận dạng dưới dạng văn bản, do đó việc nhóm và sắp xếp sẽ không hoạt động. Các giải pháp cũng giống như đối với số, chỉ có định dạng phải được thay thế bằng ngày giờ thay vì số.
- Chia văn bản dính thành các cột
- Tính toán không có công thức bằng cách dán đặc biệt
- Chuyển đổi văn bản thành số bằng tiện ích bổ sung PLEX