Ẩn giá trị 0 trong Excel (để trống ô nếu giá trị là XNUMX)

Đôi khi trong Excel, bạn có thể muốnẩn giấucác giá trị bằng không trong tập dữ liệu và hiển thị các ô đó dưới dạng trống.

Giả sử bạn có một tập dữ liệu như hình dưới đây và bạn muốn ẩn giá trị 0 trong tất cả các ô đó (hoặc muốn thay thế nó bằng một thứ gì đó như dấu gạch ngang hoặc văn bản "Không có sẵn").

Mặc dù bạn có thể thực hiện việc này theo cách thủ công với các tập dữ liệu nhỏ, nhưng bạn cần một cách tiếp cận tốt hơn khi làm việc với các tập dữ liệu lớn.

Trong hướng dẫn này, tôi sẽ chỉ cho bạn một số cách để ẩn các số không trong Excel và một cách để chọn và xóa tất cả các số không khỏi tập dữ liệu.

重要 提示: Khi bạn ẩn số 0 trong một ô bằng phương pháp được hiển thị trong hướng dẫn này, nó chỉ ẩn số 0 mà không xóa nó.Mặc dù các ô có thể trống, nhưng các ô này vẫn chứa các số 0.Nếu bạn sử dụng các ô này (số không ẩn) trong bất kỳ công thức nào, các ô đó sẽ được sử dụng trong công thức.

Hãy bắt tay vào thực hiện các phương pháp này!

Tự động ẩn các giá trị XNUMX trong ô

Excel có một tính năng tích hợp cho phép bạn tự động ẩn tất cả các giá trị XNUMX cho toàn bộ trang tính.

Tất cả những gì bạn phải làm là bỏ chọn một hộp trong tùy chọn Excel và các thay đổi sẽ áp dụng cho toàn bộ trang tính.

Giả sử bạn có tập dữ liệu bán hàng được hiển thị bên dưới và bạn muốn ẩn tất cả các giá trị XNUMX và hiển thị một ô trống.

Dưới đây là các bước để ẩn số không trong tất cả các ô trong sổ làm việc Excel:

1. Mở sổ làm việc mà bạn muốn ẩn tất cả các số không
2. Nhấp vào tab Tệp
3. bấm vào tùy chọn
4. Trong hộp thoại Tùy chọn Excel mở ra, hãy bấm vào tùy chọn Nâng cao trong ngăn bên trái
5. Cuộn xuống phần "Tùy chọn hiển thị cho trang tính này" và chọn trang tính mà bạn muốn ẩn các số không.
6. Bỏ chọn tùy chọn "Hiển thị số không trong các ô có giá trị bằng không"
7. Bấm OK

Các bước trên sẽ ẩn các số không trong tất cả các ô trong trang tính đã chọn cùng một lúc.

Thay đổi này cũng áp dụng cho các ô có kết quả công thức bằng XNUMX.

Hãy nhớ rằng phương pháp này chỉ ẩn các giá trị 0 trong ô, nó không xóa chúng.0 vẫn ở đó, nó chỉ bị ẩn.

Đây là cách tốt nhất và nhanh nhất để ẩn các giá trị XNUMX trong Excel.Nhưng điều này chỉ hữu ích nếu bạn muốn ẩn các giá trị XNUMX trong toàn bộ trang tính.Nếu bạn chỉ muốn nó ẩn các giá trị bằng không trong một phạm vi ô cụ thể, tốt hơn nên sử dụng các phương pháp khác được mô tả sau trong hướng dẫn này.

Ẩn các giá trị XNUMX trong các ô bằng cách sử dụng định dạng có điều kiện

Mặc dù phương pháp trên là thuận tiện nhất, nhưng nó không cho phép bạn ẩn các số không chỉ trong một phạm vi cụ thể (nó ẩn các giá trị XNUMX trên toàn bộ trang tính).

Nếu bạn chỉ muốn ẩn các số không cho một tập dữ liệu cụ thể (hoặc nhiều tập dữ liệu), bạn có thể sử dụng định dạng có điều kiện.

Với định dạng có điều kiện, bạn có thể áp dụng định dạng cụ thể dựa trên giá trị trong một ô.Vì vậy, nếu một số ô có giá trị bằng 0, bạn có thể chỉ cần viết định dạng có điều kiện để ẩn nó (hoặc thậm chí đánh dấu nó nếu cần).

Giả sử bạn có một tập dữ liệu như bên dưới và bạn muốn ẩn các số không trong các ô.

Dưới đây là các bước để ẩn số không trong Excel bằng cách sử dụng định dạng có điều kiện:

1. Chọn một tập dữ liệu
2. Nhấp vào tab Trang chủ
3. Trong nhóm Kiểu, bấm Định dạng có Điều kiện.
4. Đặt con trỏ vào tùy chọn "Đánh dấu ô quy tắc" và nhấp vào tùy chọn "Bằng" trong các tùy chọn được hiển thị.
5. Trong hộp thoại Bằng, nhập "0" vào trường bên trái.
6. Chọn menu thả xuống Định dạng và nhấp vào Định dạng tùy chỉnh.
7. Trong hộp thoại Định dạng Ô, chọn tab Phông chữ.
8. Từ danh sách thả xuống Màu, chọn Màu trắng.
9. Bấm OK.
10. Bấm OK.

Các bước trên thay đổi màu phông chữ của các ô có số không thành màu trắng, điều này làm cho các ô đó trông trống.

Cách giải quyết này sẽ chỉ hoạt động nếu bạn có nền trắng trong trang tính của mình.Nếu có bất kỳ màu nền nào khác trong ô, màu trắng có thể vẫn hiển thị số XNUMX.

Nếu bạn thực sự muốn ẩn các số không trong ô, ô trông trống bất kể màu nền của ô, bạn có thể sử dụng các bước sau.

1. Chọn một tập dữ liệu
2. Nhấp vào tab Trang chủ
3. Trong nhóm Kiểu, bấm Định dạng có Điều kiện.
4. Đặt con trỏ vào tùy chọn "Đánh dấu ô quy tắc" và nhấp vào tùy chọn "Bằng" trong các tùy chọn được hiển thị.
5. Trong hộp thoại Bằng, nhập 0 vào trường bên trái.
6. Chọn menu thả xuống Định dạng và nhấp vào Định dạng tùy chỉnh.
7. Trong hộp thoại Định dạng Ô, chọn tab Số, rồi bấm vào tùy chọn Tùy chỉnh trong ngăn bên trái.
8. Trong Tùy chọn Định dạng Tùy chỉnh, hãy nhập thông tin sau vào trường Loại:;;; (ba dấu chấm phẩy, không có dấu cách)
9. Bấm OK.
10. Bấm OK.

Các bước trên thay đổi định dạng tùy chỉnh cho các ô có giá trị bằng 0.

Khi bạn sử dụng ba dấu chấm phẩy làm định dạng, nó sẽ ẩn mọi thứ trong ô (cho dù đó là số hay văn bản).Và vì định dạng này chỉ áp dụng cho các ô có giá trị 0 trong đó nên nó chỉ ẩn các số không chứ không ẩn phần còn lại của tập dữ liệu.

Bạn cũng có thể sử dụng phương pháp này để đánh dấu các ô này bằng một màu khác, chẳng hạn như đỏ hoặc vàng.Để thực hiện việc này, chỉ cần thêm một bước khác (sau bước 8), nơi bạn cần chọn tab Tô màu trong hộp thoại Định dạng Ô và chọn màu bạn muốn đánh dấu các ô.

Hai phương pháp trên cũng hoạt động khi 0 là kết quả của công thức trong ô.

Lưu ý: Cả hai phương pháp được mô tả trong phần này chỉ ẩn các giá trị bằng không.Nó không loại bỏ những số không.Điều này có nghĩa là nếu bạn sử dụng các ô này trong một phép tính, tất cả các số không cũng sẽ được sử dụng.

Ẩn các giá trị XNUMX với định dạng tùy chỉnh

Một cách khác để ẩn các giá trị 0 khỏi tập dữ liệu là tạo một định dạng tùy chỉnh để ẩn các giá trị trong các ô bằng XNUMX, trong khi bất kỳ giá trị nào khác xuất hiện như mong đợi.

Dưới đây là các bước để ẩn số không trong các ô có định dạng tùy chỉnh:

1. Chọn toàn bộ tập dữ liệu để áp dụng định dạng này.Chọn tất cả các ô nếu bạn muốn tùy chọn này cho toàn bộ trang tính
2. Với ô đã chọn, hãy nhấp vào tab Trang chủ
3. Trong nhóm Ô, bấm Tùy chọn Định dạng
4. Từ các tùy chọn thả xuống, hãy nhấp vào Định dạng ô.Thao tác này sẽ mở hộp thoại Định dạng ô
5. Trong hộp thoại Định dạng Ô, chọn tab Số
6. Trong các tùy chọn ở ngăn bên trái, bấm Tùy chọn tùy chỉnh.
7. Trong các tùy chọn ở bên phải, hãy nhập định dạng sau vào trường Loại:0; -0 ;; @
8. Bấm OK

Các bước trên sẽ áp dụng định dạng tùy chỉnh cho các ô đã chọn, trong đó chỉ các ô có giá trị 0 được định dạng để ẩn giá trị, trong khi các ô còn lại hiển thị dữ liệu như mong đợi.

Định dạng tùy chỉnh này hoạt động như thế nào?

Trong Excel, bạn có thể chỉ định bốn loại định dạng dữ liệu:

1. 正數
2. số âm
3. 零
4. Văn bản

Đối với mỗi kiểu dữ liệu này, bạn có thể chỉ định một định dạng.

Trong hầu hết các trường hợp, có một định dạng mặc định - chung, giúp hiển thị tất cả các kiểu dữ liệu này.Nhưng bạn có thể linh hoạt để tạo các định dạng cụ thể cho từng loại dữ liệu.

Đây là cấu trúc định dạng tùy chỉnh mà bạn cần tuân theo trong trường hợp bạn áp dụng định dạng tùy chỉnh cho từng loại dữ liệu sau:

;;;

Trong phương pháp được trình bày ở trên, tôi đã chỉ định định dạng là:

0; -0 ;; @

Định dạng trên thực hiện như sau:

1. Các số dương được hiển thị nguyên trạng
2. Số âm được hiển thị bằng dấu trừ
3. Zeros bị ẩn (vì không có định dạng nào được chỉ định)
4. Văn bản được hiển thị như hiện tại

Thay thế các số không bằng dấu gạch ngang hoặc một số văn bản cụ thể

Trong một số trường hợp, bạn có thể không chỉ muốn ẩn các số không trong ô mà còn thay thế chúng bằng một thứ gì đó có ý nghĩa hơn.

Ví dụ: bạn có thể muốn xóa các số không và thay thế chúng bằng dấu gạch ngang hoặc văn bản như "không có sẵn" hoặc "dữ liệu chưa có sẵn"

Điều này được thực hiện một lần nữa với định dạng số tùy chỉnh, nơi bạn có thể chỉ định ô sẽ hiển thị thay vì số XNUMX.

Sau đây là các bước để chuyển 0 thành dấu gạch ngang trong Excel:

1. Chọn toàn bộ tập dữ liệu để áp dụng định dạng này.Chọn tất cả các ô nếu bạn muốn tùy chọn này cho toàn bộ trang tính
2. Với ô đã chọn, hãy nhấp vào tab Trang chủ
3. Trong nhóm Ô, bấm Tùy chọn Định dạng
4. Từ các tùy chọn thả xuống, hãy nhấp vào Định dạng ô.Thao tác này sẽ mở hộp thoại Định dạng ô
5. Trong hộp thoại Định dạng Ô, chọn tab Số
6. Trong các tùy chọn ở ngăn bên trái, bấm Tùy chọn tùy chỉnh.
7. Trong các tùy chọn ở bên phải, hãy nhập định dạng sau vào trường Loại:0; -0; -; @
8. Bấm OK

Các bước trên sẽ giữ nguyên tất cả các ô khác và thay đổi các ô có giá trị 0 để hiển thị dấu gạch ngang (-).

注意: Trong ví dụ này, tôi đã chọn thay thế các số không bằng dấu gạch ngang, nhưng bạn có thể sử dụng bất kỳ văn bản nào.Ví dụ: nếu bạn muốn hiển thị NA trong một ô, bạn có thể sử dụng định dạng sau:0; -0; "NA"; @

Mẹo chuyên nghiệp: Bạn cũng có thể chỉ định một số màu văn bản ở định dạng số tùy chỉnh.Ví dụ: nếu bạn muốn các ô có số không hiển thị văn bản NA màu đỏ, bạn có thể sử dụng định dạng sau:0; -0; [Màu đỏ] "NA"; @

Ẩn các giá trị XNUMX trong bảng tổng hợp

Có hai trường hợp có thể xảy ra trong đó bảng tổng hợp hiển thị giá trị là 0:

1. Các ô dữ liệu nguồn được tóm tắt trong bảng tổng hợp có 0 giá trị
2. Các ô dữ liệu nguồn cho phần tóm tắt trong bảng tổng hợp trống và bảng tổng hợp đã được định dạng để hiển thị các ô trống dưới dạng số không

Hãy xem cách ẩn các số không trong bảng tổng hợp trong từng trường hợp:

Khi ô dữ liệu nguồn là 0

Dưới đây là tập dữ liệu mẫu mà tôi đã sử dụng để tạo bảng tổng hợp.Lưu ý rằng tất cả các con số Số lượng / Doanh thu / Lợi nhuận được ghi lại cho "Tây" đều là 0.

Bảng tổng hợp được tạo bằng tập dữ liệu này trông giống như sau:

Vì tất cả các bản ghi ở khu vực phía tây đều có số 0 trong tập dữ liệu, bạn sẽ thấy các số 0 trong bảng tổng hợp phía tây tại đây.

Nếu bạn muốn ẩn các số không trong bảng tổng hợp này, bạn có thể sử dụng định dạng có điều kiện để làm như vậy.

Tuy nhiên, định dạng có điều kiện trong Excel PivotTables hoạt động hơi khác so với dữ liệu thông thường.

Dưới đây là các bước để áp dụng định dạng có điều kiện cho bảng tổng hợp để ẩn các số không:

1. Chọn bất kỳ ô nào trong cột có chứa số 0 trong tóm tắt PivotTable (không chọn tất cả các ô, chỉ một ô)
2. Nhấp vào tab Trang chủ
3. Trong nhóm Kiểu, bấm Định dạng có Điều kiện.
4. Đặt con trỏ vào tùy chọn "Đánh dấu ô quy tắc" và nhấp vào tùy chọn "Bằng" trong các tùy chọn được hiển thị.
5. Trong hộp thoại Bằng, nhập 0 vào trường bên trái.
6. Chọn menu thả xuống Định dạng và nhấp vào Định dạng tùy chỉnh.
7. Trong hộp thoại Định dạng Ô, chọn tab Số, rồi bấm vào tùy chọn Tùy chỉnh trong ngăn bên trái.
8. Trong Tùy chọn Định dạng Tùy chỉnh, hãy nhập thông tin sau vào trường Loại:;;; (ba dấu chấm phẩy, không có dấu cách)
9. Bấm OK.Bạn sẽ thấy các biểu tượng tùy chọn định dạng nhỏ ở bên phải của các ô đã chọn.
10. Nhấp vào biểu tượng Tùy chọn định dạng này
11. Trong số các tùy chọn có sẵn, hãy chọn - Tất cả các ô hiển thị giá trị Tổng Doanh thu cho Khu vực.

Các bước trên ẩn các số không trong bảng tổng hợp, nếu bạn thay đổi cấu trúc bảng tổng hợp (bằng cách thêm nhiều tiêu đề hàng / cột vào đó, các số không sẽ tiếp tục bị ẩn).

Nếu bạn muốn tìm hiểu thêm về cách nó hoạt động, đây là một hướng dẫn chi tiết giải thíchÁp dụng định dạng có điều kiện cho bảng tổng hợpmột cách chính xác.

Khi ô dữ liệu nguồn có các ô trống

Dưới đây là một ví dụ mà tôi có một tập dữ liệu với các ô trống cho tất cả các bản ghi của phương Tây.

Nếu tôi tạo một bảng tổng hợp với dữ liệu này, tôi sẽ nhận được kết quả như bên dưới.

Theo mặc định, bảng tổng hợp hiển thị cho bạn các ô trống khi tất cả các ô trong dữ liệu nguồn đều trống.Tuy nhiên, nếu bạn vẫn thấy số 0 trong trường hợp này, hãy làm theo các bước sau để ẩn số XNUMX và hiển thị ô trống:

1. Nhấp chuột phải vào bất kỳ ô nào trong bảng tổng hợp
2. Bấm vào Tùy chọn PivotTable
3. Trong hộp thoại Tùy chọn PivotTable, hãy bấm vào tab Bố cục và Định dạng
4. Trong các tùy chọn định dạng, hãy chọn tùy chọn và "Hiển thị: cho các ô trống" và để trống.
5. Bấm OK.

Các bước trên sẽ ẩn các số không trong bảng tổng hợp và hiển thị một ô trống.

Bạn có thể chỉ định trong bước 0 nếu bạn muốn bảng tổng hợp hiển thị nội dung nào đó thay vì 4.Ví dụ: nếu bạn muốn hiển thị văn bản - "Dữ liệu không có sẵn", thay vì 0, trong trường (được hiển thị bên dưới).

Tìm và loại bỏ các số không trong Excel

Trong tất cả các phương pháp trên, tôi đã hướng dẫn bạn cách ẩn giá trị 0 trong ô, nhưng giá trị vẫn nằm trong ô.

Nếu bạn muốn xóa các giá trị XNUMX khỏi tập dữ liệu (sẽ để lại cho bạn các ô trống), hãy làm theo các bước sau:

1. Chọn một tập dữ liệu (hoặc toàn bộ trang tính)
2. Nhấp vào tab Trang chủ
3. Trong nhóm Chỉnh sửa, bấm Tìm & Chọn
4. Từ các tùy chọn xuất hiện trong danh sách thả xuống, hãy nhấp vào Thay thế
5. Trong hộp thoại Tìm và Thay thế, hãy thực hiện các thay đổi sau:
   • Trong trường Tìm gì, hãy nhập 0
   • Trong trường "Thay thế bằng", không nhập gì (để trống)
   • Nhấp vào nút Tùy chọn và chọn tùy chọn "Khớp toàn bộ nội dung ô"
6. bấm vào thay thế tất cả

Các bước trên sẽ ngay lập tức thay thế tất cả các ô có giá trị bằng XNUMX bằng ô trống.

Nếu bạn không muốn xóa các số không ngay lập tức và chọn các ô trước, hãy làm theo các bước sau:

1. Chọn một tập dữ liệu (hoặc toàn bộ trang tính)
2. Nhấp vào tab Trang chủ
3. Trong nhóm Chỉnh sửa, bấm Tìm & Chọn
4. Từ các tùy chọn xuất hiện trong danh sách thả xuống, hãy nhấp vào Thay thế
5. Trong hộp thoại Tìm và Thay thế, hãy thực hiện các thay đổi sau:
   • Trong trường Tìm gì, hãy nhập 0
   • Trong trường "Thay thế bằng", không nhập gì (để trống)
   • Nhấp vào nút Tùy chọn và chọn tùy chọn "Khớp toàn bộ nội dung ô"
6. Nhấp vào nút Tìm tất cả.Thao tác này sẽ cung cấp cho bạn danh sách tất cả các ô chứa 0
7. Nhấn Control-A (giữ phím điều khiển và nhấn A).Thao tác này sẽ chọn tất cả các ô có 0 trong đó.

Bây giờ bạn đã chọn tất cả các ô của 0, bạn có thể xóa chúng, thay thế chúng bằng một cái gì đó khác hoặc thay đổi màu và đánh dấu chúng.

ẩn số không loại bỏ các số không

Khi xử lý các ô có số 0 trong chúng, bạn cần biết sự khác biệt giữa ẩn chúng và xóa chúng.

Khi bạn ẩn giá trị XNUMX trong một ô, giá trị đó sẽ ẩn đối với bạn, nhưng vẫn ở trong ô đó.Điều này có nghĩa là nếu bạn có một tập dữ liệu và sử dụng nó để tính toán, thì giá trị 0 đó cũng sẽ được sử dụng để tính toán.

Ngược lại, khi bạn có một ô trống, việc sử dụng nó trong công thức có thể có nghĩa là Excel sẽ tự động bỏ qua các ô đó (tùy thuộc vào công thức bạn sử dụng).

Ví dụ: đây là những gì tôi nhận được khi sử dụng công thức AVERAGE để lấy giá trị trung bình của hai cột (kết quả là hàng 14).Giá trị 0 trong các ô của cột A đã bị ẩn (nhưng vẫn hiển thị trong các ô) và các giá trị 0 trong các ô của cột B đã bị loại bỏ.

Mặc dù bộ dữ liệu của cả hai bộ dữ liệu trông giống nhau, nhưng bạn có thể thấy sự khác biệt trong kết quả.

Điều này là do công thức AVERAGE bỏ qua các ô trống trong cột B, nhưng nó sử dụng các ô trong cột A (vì các ô trong cột A vẫn có giá trị 0).