Thêm số 0 vào trước số trong Excel. Mặc định Excel không lấy số 0 đằng trước số trong ô. Bạn đã bao giờ thử nhập một số dữ liệu như 000123 vào Excel chưa? . Điều này có thể thực sự khó chịu nếu bạn muốn có những số 0 đứng đầu trong dữ liệu và không biết cách làm cho Excel giữ chúng. May mắn thay, có khá nhiều cách để đệm số của bạn bằng số 0 khi bắt đầu. Trong bài viết này, tôi sẽ giải thích các cách khác nhau để thêm hoặc giữ các số 0 đứng đầu các số của bạn
Các chức năng khác nhau để thêm số không [0] ở phía trước
Như chúng ta đã biết hàm TEXT là hàm được sử dụng nhiều nhất để thêm số 0 vào trước nhưng ở đây tôi sẽ giải thích thêm các cách thêm số không [0] vào số
- chức năng VĂN BẢN
- chức năng QUYỀN
- chức năng CƠ SỞ
- Thêm 0 vào giá trị bảng tổng hợp
Định dạng dưới dạng văn bản
Một trong những giải pháp là chuyển định dạng của số thành văn bản vì nếu chúng ta nhập 00xyz, excel sẽ coi giá trị này là văn bản và giữ số 0 ở phía trước. Có nhiều cách khác nhau để chuyển đổi một ô số sang định dạng văn bản
- Sử dụng định dạng tùy chỉnh
- Sử dụng hàm TEXT
- Sử dụng Dấu nháy đơn [ ' ] ở đầu giá trị khi nhập
Định dạng tùy chỉnh
Hãy cùng tìm hiểu những phương pháp này. Sử dụng định dạng tùy chỉnh để thay đổi định dạng là cách dễ nhất để thay đổi các số đã tồn tại trong phạm vi. Chỉ cần chọn phạm vi và Sử dụng phím tắt Ctrl + 1 hoặc chọn Định dạng số khác từ danh sách thả xuống trên tab Trang chủ hiển thị Chung
Thay đổi định dạng từ Chung thành Văn bản
- Chọn phạm vi ô bạn muốn nhập các số 0 đứng đầu trong
- Chuyển đến tab Trang chủ
- Trong phần Số nhấp vào lựa chọn Định dạng thả xuống
- Chọn Văn bản từ các tùy chọn định dạng
Bây giờ nếu bạn cố gắng nhập các số có số 0 đứng đầu, chúng sẽ không biến mất vì chúng được nhập dưới dạng giá trị văn bản thay vì số. Người dùng Excel có thể thêm định dạng tùy chỉnh để định dạng số có số 0 đứng đầu
Mặc dù vậy, chúng sẽ chỉ có các số 0 đứng đầu. Dữ liệu cơ bản sẽ không được thay đổi thành văn bản với các số 0 được thêm vào
Thêm một định dạng tùy chỉnh để hiển thị các số 0 đứng đầu
- Chọn phạm vi ô bạn muốn thêm các số 0 đứng đầu và mở hộp thoại Định dạng ô
- Click chuột phải chọn Format Cells
- Sử dụng phím tắt Ctrl + 1
- Chuyển đến tab Số
- Chọn Tùy chỉnh từ các tùy chọn danh mục
- Thêm một định dạng tùy chỉnh mới trong đầu vào Loại. Nếu bạn muốn tổng số chữ số bao gồm bất kỳ số 0 đứng đầu nào là 6 thì hãy thêm 000000 làm định dạng tùy chỉnh
- Nhấn nút OK
Chức năng VĂN BẢN
Hàm TEXT sẽ cho phép bạn áp dụng định dạng tùy chỉnh cho bất kỳ dữ liệu số nào đã có trong bảng tính của bạn
= TEXT [ Giá trị, Định dạng]
- Giá trị – Đây là giá trị bạn muốn chuyển thành văn bản và áp dụng định dạng cho
- Định dạng – Đây là định dạng để áp dụng
= VĂN BẢN [ B3, "000000"]
Nếu bạn muốn thêm các số không vào một số trong ô B3 để tổng số chữ số là 6, thì bạn có thể sử dụng công thức trên
Sử dụng dấu nháy đơn [ ' ] khi bắt đầu
Bạn có thể buộc Excel nhập một số dưới dạng văn bản bằng cách sử dụng dấu nháy đơn ở đầu
Điều này có nghĩa là bạn sẽ có thể giữ các số 0 đó ở phía trước khi nhập dữ liệu của mình
Phương pháp này nhanh chóng và dễ dàng trong khi nhập dữ liệu. Chỉ cần nhập ký tự ' trước bất kỳ số nào. Điều này sẽ cho Excel biết dữ liệu có nghĩa là văn bản chứ không phải số
Khi bạn nhấn Enter, các số 0 đứng đầu sẽ vẫn hiển thị trong trang tính. ' sẽ không hiển thị trong trang tính, nhưng vẫn ở đó và có thể được nhìn thấy trong thanh công thức khi con trỏ ô hiện hoạt nằm trên ô
QUYỀN Chức năng
Một cách khác để đưa số 0 của bạn vào phía trước bằng công thức là sử dụng hàm RIGHT. Bạn có thể nối một chuỗi số 0 với số và sau đó cắt bỏ các phần bổ sung bằng hàm RIGHT
Hàm RIGHT sẽ trích xuất N ký tự ngoài cùng bên phải từ một giá trị văn bản
= RIGHT [ Văn bản, [Số]]
- Văn bản – Đây là văn bản bạn muốn trích xuất các ký tự từ
- Số [Tùy chọn]- Đây là số ký tự để trích xuất từ văn bản. Nếu đối số này không được nhập, thì chỉ ký tự đầu tiên sẽ được trích xuất
= QUYỀN [ "000000" & B3, 6 ]
Công thức trên sẽ nối một số số 0 vào đầu một số trong ô B3, sau đó nó sẽ trả về 6 ký tự ngoài cùng bên phải dẫn đến một số số 0 đứng đầu
Chức năng CƠ SỞ
Bạn sẽ nhận thấy bài viết này mô tả 9 cách để thêm các số 0 đứng đầu, nhưng video YouTube của tôi chỉ hiển thị 8 cách
Đó là bởi vì tôi không biết rằng bạn có thể sử dụng hàm BASE để thêm các số 0 vào đầu cho đến khi ai đó đề cập đến nó trong phần nhận xét video
Hàm BASE cho phép bạn chuyển đổi một số thành dạng biểu diễn văn bản với cơ số cho trước
Các số bạn thường sử dụng là cơ số 10, nhưng bạn có thể sử dụng hàm này để chuyển đổi số cơ số 10 thành biểu diễn cơ số 2 [nhị phân]
= CƠ SỞ [ Số, Cơ sở, [Độ dài tối thiểu]]
- Số – Đây là số bạn muốn chuyển đổi thành giá trị văn bản trong một cơ số khác
- Cơ sở - Đây là cơ sở bạn muốn chuyển đổi giá trị thành
- Độ dài tối thiểu [Tùy chọn] – Đây là độ dài tối thiểu của các ký tự của giá trị văn bản được chuyển đổi
Sử dụng công thức
Công thức trên sẽ chuyển đổi một số trong ô B3 thành cơ số 10 [nó đã là số cơ số 10 nhưng điều này sẽ giữ nó ở dạng cơ số 10] và chuyển đổi nó thành giá trị văn bản có ít nhất 6 ký tự. Nếu giá trị được chuyển đổi nhỏ hơn 6 ký tự, nó sẽ đệm giá trị bằng số 0 để có tối thiểu 6 ký tự
Chắc chắn là một cách sử dụng mới thú vị cho một chức năng mà tôi không bao giờ sử dụng
Chỉ thêm số không [0] trước các số không phải trên giá trị văn bản
Ở đây chúng tôi sẽ xem xét kịch bản bao gồm cả giá trị văn bản và số trong cùng một danh sách. Bây giờ excel coi mỗi ô khác nhau về định dạng. Bây giờ làm sao để excel biết nếu ô có giá trị số thì thêm số 0 vào trước còn nếu ô có chữ hay giá trị nào khác thì để nguyên
Đối với điều này, chúng tôi sẽ sử dụng kết hợp hai hàm IF và hàm ISNUMBER. Ở đây chúng tôi có một số giá trị để thử
Công thức chung tiếp tục như
=IF[ISNUMBER[cell_ref],"0"&cell_ref, cell_ref]
Đây
ô_ref. tham chiếu ô của ô tương ứng
Sử dụng công thức trong ô
=IF[ISNUMBER[F3],"0"&F3, F3]Giải trình. Hàm ISNUMBER kiểm tra ô để tìm số. Hàm IF kiểm tra xem ô có số không thì nó thêm số 0 vào trước số khác nó chỉ trả về giá trị ô
Như bạn có thể thấy trong hình trên, số không [0] được thêm vào trước số. Hãy kiểm tra xem chức năng này có hoạt động trên các giá trị khác không. Sao chép công thức trong các ô khác, chọn các ô lấy ô đầu tiên đã áp dụng công thức, sử dụng phím tắt Ctrl+D
nó sẽ đệm giá trị bằng số 0 để có tối thiểu một số 0 trước các ký tự. Chắc chắn là một cách sử dụng mới thú vị cho một chức năng mà tôi không bao giờ sử dụng
Cột được tính Power Pivot
Có một tùy chọn khác để thêm số không vào bảng tổng hợp. Bạn có thể thêm chúng vào một cột được tính toán trong Power Pivot. Bằng cách này, bạn có thể sử dụng cột mới trong khu vực Bộ lọc, Hàng hoặc Cột của bảng tổng hợp
= ĐỊNH DẠNG [ Số[Số], "000000"]
Trong phần bổ trợ Power Pivot, bạn có thể thêm một cột mới và sử dụng hàm FORMAT để tạo các số 0 đứng đầu trong cột với công thức như trên. Một cột được tính toán sẽ tính toán một giá trị cho mỗi hàng, vì vậy không cần phải bọc hàm bên trong một ô. Giờ đây, bạn có thể sử dụng cột mới này bên trong khu vực Hàng của bảng tổng hợp giống như bất kỳ cột dữ liệu nào khác
Tóm lược
Có thể khó chịu khi thấy tất cả các số không của bạn biến mất khi chúng tôi không biết tại sao điều đó lại xảy ra hoặc cách ngăn chặn điều đó. Như bạn có thể thấy, có rất nhiều cách để đảm bảo rằng tất cả các số 0 đó đều có trong dữ liệu của bạn. Dù yêu cầu của bạn là gì, chắc chắn sẽ có giải pháp tốt cho bạn
Đây là tất cả các ghi chú quan sát sử dụng công thức trong Excel
Ghi chú.
- Công thức có thể được sử dụng cho cả văn bản và số
- Văn bản tìm và thay thế phải được sắp xếp trong một bảng. Khi chúng tôi sử dụng chỉ mục để tìm và thay thế, hãy đảm bảo rằng văn bản 'tìm' có cùng chỉ mục với văn bản 'thay thế'. Sẽ tốt hơn nếu có cả hai danh sách cạnh nhau như chúng ta có trong ví dụ thứ hai
- Công thức chung có hai hàm SUBSTITUTE. Bạn sẽ cần nhiều chức năng thay thế như nhiều chức năng thay thế mà bạn có để thực hiện
- ký tự đại diện giúp trích xuất giá trị từ các chuỗi con
- The function supports logical operators like , , = but these are used using double quote sign [ " ] with arguments. But if you are using cell reference for the criteria quotes can be ignored.
- Đối số tiêu chí nên sử dụng dấu ngoặc kép ["] cho văn bản [như "Gillen"] hoặc số [như ">50" ]
Hy vọng bài viết về Các cách khác nhau để thêm số 0 [s] ở phía trước trong Excel là giải thích. Tìm thêm các bài viết về tính giá trị và công thức Excel liên quan tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại info@exceltip. com
Những bài viết liên quan
Hàm THAY THẾ so với hàm SUBSTITUTE trong Excel. Các hàm REPLACE và SUBSTITUTE là các hàm bị hiểu lầm nhiều nhất. Để tìm và thay thế một văn bản đã cho, chúng tôi sử dụng chức năng SUBSTITUTE. Trong đó REPLACE được sử dụng để thay thế một số ký tự trong chuỗi
Thay thế văn bản từ cuối chuỗi bắt đầu từ vị trí biến. Để thay thế văn bản từ cuối chuỗi, chúng ta sử dụng hàm REPLACE. Hàm REPLACE sử dụng vị trí của văn bản trong chuỗi để thay thế
Cách kiểm tra xem một chuỗi có chứa một trong nhiều văn bản trong Excel không. Để kiểm tra xem một chuỗi có chứa bất kỳ văn bản nào hay không, chúng tôi sử dụng công thức này. Chúng tôi sử dụng hàm SUM để tổng hợp tất cả các kết quả khớp và sau đó thực hiện logic để kiểm tra xem chuỗi có chứa bất kỳ chuỗi nào trong số nhiều chuỗi không
Đếm các ô có chứa văn bản cụ thể. Một hàm COUNTIF đơn giản sẽ làm nên điều kỳ diệu. Để đếm số lượng nhiều ô chứa một chuỗi đã cho, chúng tôi sử dụng toán tử ký tự đại diện với hàm COUNTIF
Các bài viết phổ biến
50 phím tắt Excel để tăng năng suất của bạn. Thực hiện nhanh hơn các nhiệm vụ của bạn trong Excel. Những phím tắt này sẽ giúp bạn tăng hiệu quả làm việc trong Excel
Cách sử dụng Hàm VLOOKUP trong Excel. Đây là một trong những hàm phổ biến và được sử dụng nhiều nhất trong excel được sử dụng để tra cứu giá trị từ các phạm vi và trang tính khác nhau
Cách sử dụng Hàm IF trong Excel. Câu lệnh IF trong Excel kiểm tra điều kiện và trả về một giá trị cụ thể nếu điều kiện là TRUE hoặc trả về một giá trị cụ thể khác nếu FALSE
Cách sử dụng Hàm SUMIF trong Excel. Đây là một chức năng thiết yếu khác của bảng điều khiển. Điều này giúp bạn tổng hợp các giá trị trên các điều kiện cụ thể
Cách sử dụng Hàm COUNTIF trong Excel. Đếm các giá trị có điều kiện bằng chức năng tuyệt vời này. Bạn không cần lọc dữ liệu của mình để đếm các giá trị cụ thể. Chức năng Countif là điều cần thiết để chuẩn bị bảng điều khiển của bạn