programming excel

Cách highlight hàng trống trong excel

Thứ 6 [17/04/2015] tôi bay từ Vizag [thị trấn của tôi] đến Hyderabad để kịp chuyến bay đến San Francisco dự hội nghị. Vì tôi có 10 tiếng xen kẽ giữa các chuyến bay ở Hyderabad, tôi đã đăng ký vào khu vực phòng chờ để có thể xem một số môn thể thao, ăn đồ ăn trong khi giả vờ làm việc trên máy tính xách tay của mình. Có một người đàn ông đang ngồi trong không gian bên cạnh đang làm một số công việc trong Excel. Khi tôi bắt đầu soạn một vài email, người đàn ông ngồi ở ghế bên cạnh hỏi tôi làm gì để kiếm sống. Cuộc trò chuyện của chúng tôi diễn ra như thế này

Tôi. Tôi điều hành một công ty phần mềm
Anh ấy. Ồ, vậy bạn phải giỏi về máy tính
Tôi. cười và co rúm lại trước sự rập khuôn
Anh ấy. Công thức để chọn tất cả các ô trống trong dữ liệu Excel của tôi và đánh dấu chúng bằng màu Vàng là gì

Xin lưu ý, anh ấy không biết rằng tôi làm việc trong Excel. Chúng tôi là 2 kẻ tình cờ ngồi trong phòng chờ sân bay xem thể thao và ăn những món khốn khổ

Tôi. Chà, bạn đang cố làm gì vậy?
Anh ấy. Bạn thấy đấy, tôi đang kiểm tra dữ liệu này. Tôi cần xác định tất cả các hàng trống và đặt chúng bằng các màu khác nhau để nhân viên của tôi có thể điền thông tin còn thiếu. Ngay bây giờ, tôi đang chọn từng hàng một và tô màu. Có giải pháp một bước nào cho vấn đề này không?

Không cần phải nói, tôi đã chỉ cho anh ấy cách làm nhanh hơn, dẫn đến 3 giờ thú vị tại phòng chờ

Kết thúc câu chuyện có thật

Vì vậy, hôm nay, hãy cùng tìm hiểu cách tìm và đánh dấu tất cả các ô trống trong dữ liệu

Hãy xem dữ liệu

Đây là một mẫu dữ liệu

Một điều quan trọng cần ghi nhớ

Dữ liệu này không có cấu trúc dưới dạng bảng

Có 3 phương pháp mạnh mẽ và đơn giản để tìm và tô sáng các ô trống

Phương pháp 1. Phương pháp lựa chọn & đánh dấu

Trong phương pháp này, chúng tôi chỉ cần chọn tất cả các ô trống trong một lần và tô chúng bằng màu vàng

Trước tiên, chọn toàn bộ phạm vi ô chứa dữ liệu của bạn. Sử dụng các phím CTRL+Mũi tên sẽ không hoạt động vì các ô trống ở giữa. Thay vào đó, hãy làm theo điều này

Chọn ô trên cùng bên trái của dữ liệu của bạn [giả sử B2]
Nhấp và kéo hộp hình chữ nhật nhỏ trong thanh cuộn dọc xuống hết cỡ
Giữ phím Shift và nhấp vào ô cuối cùng [dưới cùng bên phải]

Bây giờ tất cả dữ liệu đã được chọn,

Nhấn F5 và nhấp vào Đặc biệt
Chọn khoảng trống. bấm ok
Điều này sẽ chỉ chọn các ô trống
Tô màu vàng [hoặc màu khác] bằng cách nhấp vào biểu tượng tô màu và chọn màu
Xong

Đây là một bản demo nhanh về điều này

https. //chandoo. org/wp/wp-content/uploads/2015/04/method1. mp4

Phương pháp 2. phương pháp lọc

Cách tiếp cận trên [lựa chọn & tô sáng] hoạt động tốt nếu bạn quan tâm đến các ô trống ở bất kỳ đâu. Điều gì sẽ xảy ra nếu bạn chỉ muốn tìm và đánh dấu chỉ những hàng có khoảng trống trong một cột nhất định. Giả sử, bạn muốn đánh dấu tất cả các hàng có nhận xét trống

Trong trường hợp này,

Chọn tất cả dữ liệu bằng các bước trong phương pháp 1
Nhấn CTRL+Shift+L để kích hoạt bộ lọc
Tiếp tục lựa chọn & Lọc cột bạn muốn chỉ hiển thị các giá trị trống
Bây giờ tô màu vàng
Xong

Phương pháp 3. Phương pháp định dạng có điều kiện

Cả phương pháp 1 & phương pháp 2 đều có nhược điểm. Nếu dữ liệu của bạn thay đổi, bạn phải dọn dẹp & tô sáng lại

Đây là nơi định dạng có điều kiện tỏa sáng. Bạn có thể yêu cầu Excel chỉ đánh dấu các ô nếu chúng trống. Khi một số dữ liệu được nhập vào [hoặc sao chép đã dán hoặc làm mới kết nối], màu sẽ tự động biến mất

Để thiết lập định dạng có điều kiện,

Chọn tất cả dữ liệu
Chuyển đến Trang chủ > Định dạng có điều kiện > Quy tắc mới
Nhấp vào “Chỉ định dạng các ô có chứa”
Thay đổi tùy chọn “Giá trị ô” thành “Trống”
Thiết lập định dạng bạn muốn bằng cách nhấp vào nút Định dạng
Nhấp vào ok và bạn đã hoàn tất

Thao tác này sẽ tự động đánh dấu tất cả các ô trống bằng màu yêu thích của bạn

Đợi đã, nếu tôi muốn đánh dấu toàn bộ hàng nếu một cột nào đó trống thì sao?

Bạn cũng có thể sử dụng định dạng có điều kiện trong những trường hợp như vậy. Thực hiện theo các bước sau

Giả sử bạn muốn kiểm tra các khoảng trống trong Cột G và điểm dữ liệu đầu tiên của bạn là trong G4

Chọn tất cả dữ liệu [chỉ dữ liệu, không có tiêu đề]
Chuyển đến Trang chủ > Định dạng có điều kiện > Quy tắc mới
Chọn loại quy tắc là “Sử dụng công thức…”
Nhập công thức là =LEN[$G4]=0
Thiết lập định dạng bạn muốn
Nhấp vào ok và bạn đã hoàn tất

Đợi một chút, điều `LEN[$G4]=0` là gì?

Công thức LEN[] cho chúng ta biết độ dài của nội dung ô. Vì vậy, nếu một ô trống, LEN[cell] sẽ là 0

$G4 là kiểu tham chiếu hỗn hợp. Bằng cách này, ngay cả khi định dạng có điều kiện đang kiểm tra các cột khác, nó vẫn nhìn vào cột G để xem cột đó có thực sự trống không

Có liên quan. Giới thiệu về tham chiếu ô Excel

mẹo thưởng

Q] Làm cách nào để đánh dấu nếu một trong hai cột G hoặc H bị trống?
A] =OR[LEN[$G4]=0, LEN[$H4]=0]

Q] Làm cách nào để đánh dấu nếu cả hai cột G & H đều trống?
A] =AND[LEN[$G4]=0, LEN[$H4]=0] 0 Thì
Mục tiêu. Nội địa. Color = rgbYellow
End If
End Sub

Để đặt lại các màu này thành Không tô, tô sáng khu vực và chỉ cần xóa định dạng tô màu

Đáp lại

Oscar nói.

21 Tháng Tư, 2015 lúc 8. 26 giờ sáng

Công thức CF này làm nổi bật các ô trống nhưng không làm nổi bật nếu toàn bộ hàng trống

=[B3=""]*KHÔNG[VÀ[[$B3. $J3=""]]]

Đáp lại

indzara nói.

21 Tháng Tư, 2015 lúc 1. 11 giờ tối

Câu chuyện tuyệt vời, Chandoo. 🙂

Đáp lại

Haz nói.

24 Tháng Tư, 2015 lúc 9. 44 giờ chiều

Bạn cũng có thể đánh dấu toàn bộ hàng khi bất kỳ giá trị nào trống.
=OR[ISBLANK[$B3. $J3]]

Đáp lại

Excel Roundup 20150427 « Blog bối cảnh nói.

27 Tháng Tư, 2015 lúc 4. 01 giờ sáng

[…] anh ấy đang ngồi trong phòng chờ ở sân bay, Chandoo vẫn đang giúp mọi người giải quyết các vấn đề về Excel. Đọc các mẹo đánh dấu các ô trống của anh ấy – anh ấy chia sẻ 3 cách để làm […]

Đáp lại

Randy nói.

29 Tháng Tư, 2015 lúc 11. 30 giờ chiều

Tôi chỉ sử dụng chức năng Thay thế. Đánh dấu vùng chứa các ô CÓ và KHÔNG CÓ giá trị hoặc để trống. Nhập CNTRL+H và thực hiện điền định dạng vào "thay thế bằng. " các ô có màu bạn chọn

Đáp lại

Damaris624 nói.

1 Tháng Năm, 2015 lúc 1. 44 giờ sáng

Điều gì sẽ xảy ra nếu tôi muốn đánh dấu một hàng nhưng chỉ khi có điều kiện. Ví dụ: bảng tính của tôi có 10 cột và tôi muốn đánh dấu các khoảng trống trong hàng nhưng chỉ khi $A1 không bằng khoảng trống

Đáp lại

Oscar nói.
1 Tháng Năm, 2015 lúc 7. 03 giờ sáng
Damaris624,
Công thức CF.
=[$A1""]*[A1=""]
Đáp lại
- Oscar nói.
  1 Tháng Năm, 2015 lúc 7. 05 giờ sáng
  WordPress đã di chuyển các dấu nhỏ hơn và lớn hơn của tôi
  Công thức CF.
  =[$A1nhỏ hơn,lớn hơn""]*[A1=""]
  Đáp lại

Manoj nói.

24 Tháng Năm, 2015 lúc 11. 47 giờ sáng

Xin chào,

Cảm ơn vì ví dụ. Đã học được một cái gì đó mới ngày hôm nay.
Tôi đã cố gắng sử dụng công thức bên dưới trong bảng của mình.
=OR[LEN[$B4]=0,LEN[$C4]=0]

Thay vì đánh dấu toàn bộ hàng, nó đánh dấu hàng nằm ngay phía trên hàng có giá trị ô length=0

xin lỗi tôi là người mới bắt đầu

Đáp lại

Huy. nói.
24 Tháng Năm, 2015 lúc 3. 02 giờ chiều
@Manoj
Giả sử phạm vi dữ liệu của bạn là A10. D15
Nếu bạn chọn A9. D15 thay vì phạm vi ban đầu sẽ giải thích điều đó
Chọn phạm vi và xóa CF
Thử lại
Nếu cách này không hiệu quả, bạn có thể đặt câu hỏi tại Diễn đàn và đính kèm tệp mẫu
http. //chandoo. tổ chức/diễn đàn/
Đáp lại

Ashley nói.

10 Tháng mười một, 2015 lúc 2. 42 giờ chiều

Tôi đang cố gắng sử dụng tính năng "Go To Special-Blanks" để điền vào chỗ trống từ một trục. Tôi luôn sử dụng tính năng này, nhưng tôi đang cố gắng thực hiện việc này từ máy tính xách tay của mình [chứ không phải trên trạm nối của tôi như thường lệ] và nó không hoạt động. Điều này không thể thực hiện được từ máy tính xách tay ??

Đáp lại

Anita nói.

3 Tháng mười hai, 2015 lúc 11. 09 giờ tối

Cảm ơn bạn cho những hướng dẫn rõ ràng;

Đáp lại

nhà phát triển sharon nói.

Tháng Một 29, 2016 lúc 5. 14 giờ chiều

Xin chào Chandoo,
Tôi cần bạn trợ giúp về định dạng có điều kiện cho khoảng trống.

Tôi đã chọn phạm vi cho trang tính của mình, sử dụng định dạng có điều kiện để hiển thị các khoảng trống và sử dụng các bộ lọc để thu hẹp xuống từng cá nhân để họ điền vào các khoảng trống. Vì vậy, tôi đã tạo macro cho từng người cần điền vào chỗ trống. [để tiết kiệm thời gian]
Tuy nhiên, sau khi ghi macro thay vì chỉ hiển thị/tô sáng các ô trống, nó lại tô sáng toàn bộ hàng bằng các ô trống. Làm thế nào để tôi sửa lỗi này.
Dưới đây là mã

Phạm vi["A3. L27"]. Chọn
Cửa sổ Hoạt động. ScrollColumn = 7
ActiveWindow. ScrollColumn = 8
ActiveWindow. ScrollColumn = 9
ActiveWindow. ScrollColumn = 10
ActiveWindow. ScrollColumn = 11
Phạm vi["A3. L27,N3. N27"]. Chọn
Phạm vi["N3"]. Kích hoạt
ActiveWindow. ScrollColumn = 12
ActiveWindow. ScrollColumn = 13
ActiveWindow. ScrollColumn = 14
ActiveWindow. ScrollColumn = 15
Phạm vi["A3. L27,N3. N27,R3. R27,W3. W27"]. Chọn
Phạm vi["W3"]. Kích hoạt
ActiveWindow. ScrollColumn = 14
ActiveWindow. ScrollColumn = 12
ActiveWindow. ScrollColumn = 8
ActiveWindow. ScrollColumn = 6
Lựa chọn. điều kiện định dạng. thêm loại. =xlExpression, Formula1. = _
"=LEN[TRIM[W3]]=0"
Lựa chọn. FormatConditions[Lựa chọn. điều kiện định dạng. Đếm]. SetFirstPriority
Với lựa chọn. Định dạngĐiều kiện[1]. Nội thất
. Hoa văn = xlGray8
. PatternColorIndex = xlAutomatic
. ColorIndex = xlAutomatic
Kết thúc bằng
Lựa chọn. Định dạngĐiều kiện[1]. StopIfTrue = False
ActiveSheet. Phạm vi["$A$3. $W$27"]. Trường bộ lọc tự động. =7, Tiêu chí1. = _
"Sharon Dev"
ActiveWindow. ScrollRow = 14
ActiveWindow. ScrollRow = 13
ActiveWindow. ScrollRow = 10
ActiveWindow. ScrollRow = 7
ActiveWindow. ScrollRow = 4
Kết thúc phụ

Đáp lại

mike nói.

Tháng Hai 2, 2016 lúc 7. 59 giờ chiều

tại sao không phải vba macro để tránh lặp lại tất cả điều này?

Đáp lại

nhà phát triển sharon nói.

22 Tháng hai, 2016 lúc 5. 39 giờ tối

Chào Chandoo,

Tôi đã sử dụng định dạng có điều kiện để tìm và hiển thị tất cả các ô trống trong trang tính excel. Tôi đã sử dụng các bộ lọc trên một cột vì tôi cần tìm số ô trống mà mỗi nhân viên có. Tôi đã ghi macro cho từng nhân viên và gán một số phím tắt trong macro

Ví dụ;

Bây giờ tôi muốn biết số ô trống của từng nhân viên và gặp khó khăn trong việc này

Tôi phải làm như thế nào. Đây là một số mã
Sub Displayblanks_John_Doe[]
'
' Displayblanks_John_Doe Macro
'< . Ctrl+Shift+J
' Keyboard Shortcut: Ctrl+Shift+J
'
Phạm vi["A3. L26"]. Chọn
Phạm vi["A3. L26,N3. N26"]. Chọn
Phạm vi["N3"]. Kích hoạt
Phạm vi["A3. L26,N3. N26,R3. R26"]. Chọn
Phạm vi["R3"]. Kích hoạt
Phạm vi["A3. L26,N3. N26,R3. R26,W3. W26"]. Chọn
Phạm vi["W3"]. Kích hoạt
Lựa chọn. điều kiện định dạng. thêm loại. =xlExpression, Formula1. = _
"=LEN[TRIM[A3]]=0"
Lựa chọn. FormatConditions[Lựa chọn. điều kiện định dạng. Đếm]. SetFirstPriority
Với lựa chọn. Định dạngĐiều kiện[1]. Nội thất
. Hoa văn = xlGray8
. PatternColorIndex = xlAutomatic
. ColorIndex = xlAutomatic
Kết thúc bằng
Lựa chọn. Định dạngĐiều kiện[1]. StopIfTrue = False
ActiveSheet. Phạm vi["$A$3. $W$26"]. Trường bộ lọc tự động. =7, Tiêu chí1. = _
"John Doe"
Giảm số lượng của tôi chừng nào
mycount = Ứng dụng. Bảng TínhChức Năng. CountBlank[ActiveSheet. Phạm vi["$A$3. $W$26"]]
MsgBox ["Số ô trống = " & số của tôi]

kết thúc phụ

Tuy nhiên, nó không hiển thị chính xác các ô trống. Ngoài ra, khi tôi đóng mssgbox, nó sẽ quay lại trang gốc. Tôi cần nó ở trên trang tính để cho phép nhân viên điền vào chỗ trống

Hãy xem dữ liệu

Phương pháp 1. Phương pháp lựa chọn & đánh dấu

Phương pháp 2. phương pháp lọc

Phương pháp 3. Phương pháp định dạng có điều kiện

Đợi đã, nếu tôi muốn đánh dấu toàn bộ hàng nếu một cột nào đó trống thì sao?

Đợi một chút, điều LEN[$G4]=0 là gì?

mẹo thưởng

Bài Viết Liên Quan

Toplist mới

Bài mới nhất

Chủ Đề

Đợi một chút, điều `LEN[$G4]=0` là gì?