Đôi khi, bạn có thể có một tập dữ liệu trong đó có các ô trống/trống trong tập dữ liệu lẽ ra phải được điền bằng cùng một giá trị
Dưới đây là một ví dụ phổ biến trong đó ngày chỉ được đề cập một lần và tất cả các bản ghi còn lại cho cùng ngày đó đều có các ô ngày trống
Mặc dù loại dữ liệu này có thể trông gọn gàng hơn [đặc biệt là khi được in], nhưng sẽ không tuyệt vời khi bạn cố gắng sử dụng dữ liệu này để tính toán hoặc tạo Bảng Pivot bằng cách sử dụng nó
Trong nhiều trường hợp, bạn sẽ cần điền vào các ô trống này bằng giá trị trong ô trên
Mục lục
Cách điền vào các ô trống với giá trị trên trong Excel
Trong hướng dẫn Excel này, tôi sẽ chỉ cho bạn ba cách thực sự dễ dàng để điền vào các ô trống với giá trị trên trong Excel
- Sử dụng Go To Special với một công thức
- Sử dụng Tìm và Thay thế bằng công thức
- Sử dụng VBA
Phần khó khăn của toàn bộ quá trình này thực sự là chọn các ô trống. Khi bạn đã chọn các ô trống, có nhiều cách để sao chép các giá trị ô ở trên
Vậy hãy bắt đầu
Điền vào các ô có giá trị ở trên bằng cách sử dụng 'Chuyển đến đặc biệt' + Công thức
Bước đầu tiên để điền vào các ô trống từ giá trị trên là chọn các ô trống này. Và điều này có thể dễ dàng được thực hiện bằng cách sử dụng tùy chọn 'Go To Special' trong Excel
Giả sử bạn có một tập dữ liệu như hình bên dưới và bạn muốn điền ngày vào tất cả các ô trống trong cột A với ngày từ ô bên trên
Dưới đây là các bước để chọn tất cả các ô trống này cùng một lúc
- Chọn tập dữ liệu mà bạn có các ô trống/trống này
- Nhấn phím F5 trên bàn phím của bạn [sử dụng ⌃ + G nếu bạn đang sử dụng máy Mac]. Thao tác này sẽ mở hộp thoại 'Chuyển đến'
- Nhấp vào nút Đặc biệt
- Trong hộp thoại 'Chuyển đến Đặc biệt', chọn tùy chọn 'Trống'
- Nhấp vào OK
Các bước trên sẽ chọn tất cả các ô trống trong tập dữ liệu này
Bây giờ bạn đã chọn tất cả các ô trống, bước tiếp theo là điền vào tất cả các ô trống này từ giá trị trên
Làm theo các bước sau để sử dụng công thức sao chép giá trị từ ô ở trên
- Nhập = [dấu bằng]. theo mặc định, điều này sẽ chỉ nhập dấu bằng vào ô hiện hoạt
- Nhấn phím mũi tên lên. Thao tác này sẽ chọn ô ngay phía trên ô hiện hoạt
- Giữ phím Control và nhấn phím enter [Command + Enter nếu bạn đang sử dụng máy Mac]
Đó là nó
Các bước trên sẽ áp dụng cùng một công thức [chỉ đơn giản là tham chiếu đến ô ở trên] cho tất cả các ô trống đã chọn
Khi bạn đã điền tất cả các ô trống này, hãy nhớ chuyển đổi công thức thành giá trị
Lưu ý rằng để phương pháp này hoạt động, các ô cần hoàn toàn trống. Trong trường hợp có các ký tự khoảng trắng, tính năng 'Go-To' sẽ không coi đây là các ô trống và chúng sẽ không được chọn
Điền vào các ô có giá trị ở trên bằng cách sử dụng 'Tìm và thay thế' + Công thức
Phương pháp [Chuyển đến Đặc biệt] ở trên hoạt động tốt khi bạn chỉ có các ô trống mà bạn muốn điền vào
Nhưng điều gì sẽ xảy ra nếu bạn nhận được một tập dữ liệu trong đó các ô không thực sự trống [nhưng có thể có dấu gạch ngang trong đó hoặc một số văn bản chẳng hạn như NA]
Trong trường hợp này, bạn cũng có thể sử dụng phương pháp Tìm và Thay thế
Phương pháp này hoạt động tương tự như Go To Special, nhưng có thêm lợi thế là có thể chọn các ô dựa trên giá trị
Với phương pháp này, bạn có thể chọn các ô trống hoặc bạn có thể chọn các ô có bất kỳ văn bản/giá trị cụ thể nào trong đó
Để tôi chỉ cho bạn cách nó hoạt động, chỉ lấy một ví dụ về các ô trống
Giả sử bạn có một tập dữ liệu như hình bên dưới và bạn muốn điền ngày vào tất cả các ô trống trong cột A với ngày từ ô bên trên
Dưới đây là các bước để chọn tất cả các ô trống bằng Tìm và Thay thế
- Chọn tập dữ liệu mà bạn có các ô trống/trống này
- Giữ phím Control và nhấn phím F [hoặc Command + F nếu bạn đang sử dụng máy Mac]
- Trong hộp thoại Tìm và Thay thế mở ra, nhấp vào nút 'Tìm tất cả'. Điều này sẽ tìm thấy tất cả các ô trống và bạn cũng sẽ thấy một danh sách các địa chỉ ô này
- Giữ phím Control và nhấn phím A [hoặc Command + A nếu bạn đang sử dụng máy Mac]. Thao tác này sẽ chọn tất cả các ô trống này
- Đóng hộp thoại Tìm và Thay thế
Khi kết thúc các bước này, bạn sẽ có tất cả các ô trống được chọn
Bây giờ, bạn có thể sử dụng công thức để lấy giá trị từ ô trên và điền vào các ô trống trong Excel
Làm theo các bước sau để sử dụng công thức sao chép giá trị từ ô ở trên
- Nhập = [dấu bằng]. theo mặc định, điều này sẽ chỉ nhập dấu bằng vào ô hiện hoạt
- Nhấn phím mũi tên lên. Thao tác này sẽ chọn ô ngay phía trên ô hiện hoạt
- Giữ phím Control và nhấn phím enter [Command + Enter nếu bạn đang sử dụng máy Mac]
Một trong những lợi ích của việc sử dụng Tìm và Thay thế. Nó hoạt động khi bạn có các ô trống muốn điền và bạn cũng có thể sử dụng nó để tìm và chọn các ô có giá trị cụ thể
Ví dụ: giả sử bạn có tập dữ liệu như hình bên dưới, thay vì các ô trống, chúng tôi có dấu gạch ngang thay thế
Để làm điều này, hãy làm theo các bước dưới đây
- Chọn tập dữ liệu
- Giữ phím Control và nhấn phím F [hoặc Command + F nếu bạn đang sử dụng máy Mac]
- Trong hộp thoại Tìm và Thay thế mở ra, hãy nhập - [dấu gạch ngang] vào trường 'Tìm gì'
- Nhấp vào nút Tìm tất cả. Thao tác này sẽ tìm tất cả các ô có biểu tượng dấu gạch ngang và bạn cũng sẽ thấy danh sách địa chỉ của các ô này
- Giữ phím Control và nhấn phím A [hoặc Command + A nếu bạn đang sử dụng máy Mac]. Thao tác này sẽ chọn tất cả các ô này
- Đóng hộp thoại Tìm và Thay thế
Khi bạn đã chọn các ô này, bạn có thể sử dụng các bước công thức được hiển thị ở trên để điền các giá trị từ các ô phía trên [hoặc thậm chí các giá trị đầy đủ của ô từ bên dưới/phải/trái]
Điền vào các ô có giá trị trên bằng VBA
Mặc dù hai phương pháp trên hoạt động hoàn toàn tốt, nhưng trong trường hợp bạn phải thực hiện việc này khá thường xuyên, bạn cũng có thể sử dụng mã VBA đơn giản để thực hiện việc này
Và nếu bạn muốn trở nên siêu hiệu quả và hoàn thành việc này chỉ bằng một cú nhấp chuột, bạn có thể thêm macro VBA vào Thanh công cụ truy cập nhanh [QAT] và chỉ cần nhấp vào nó để điền vào các ô trống trong vùng chọn bằng giá trị ô từ
Dưới đây là mã VBA sẽ thực hiện việc này
Sub FillCellFromAbove[] For Each cell In Selection If cell.Value = "" Then cell.Value = cell.Offset[-1, 0].Value End If Next cell End Sub
Để sử dụng mã này, trước tiên, hãy chọn tập dữ liệu mà bạn muốn sao chép giá trị từ ô ở trên, sau đó chạy mã này
Đoạn mã trên sử dụng vòng lặp FOR EACH trong đó nó đi qua từng ô trong vùng chọn. Ngay khi nó tìm thấy một ô trống [được kiểm tra bằng câu lệnh IF], nó sẽ sao chép giá trị từ ô bên trên [được thực hiện bằng OFFSET]
Trong ví dụ cụ thể này, bạn có thể nhận thấy rằng
Ghi chú. Trong trường hợp bạn muốn lưu sổ làm việc có mã macro, bạn cần lưu sổ làm việc đó vào thư mục. Định dạng XLSM [bật macro]
Điều tốt nhất khi sử dụng mã VBA này là nó không yêu cầu bạn chuyển đổi công thức thành giá trị. Với phương pháp này, bạn chỉ nhận được các giá trị tĩnh. Điều này làm cho nó trở thành một phương pháp thực sự hiệu quả [trong cả ba phương pháp được đề cập trong hướng dẫn này]
Tuy nhiên, có hai điều bạn cần biết khi sử dụng VBA để điền vào các ô trống bằng một giá trị từ ô trên