Bài viết: Chiêu thứ 76: Không bỏ sót ô rỗng

[kyo]

Chiêu thứ 76: Không bỏ sót ô rỗng


Mọi người thường sẽ bỏ trống 1 ô nào đó khi dữ liệu cho ô đó giống với dữ liệu của ô trên nó. Việc này làm cho danh sách dễ nhìn, dễ đọc, nhưng về mặt cấu trúc thì không được tốt lắm. Chiêu thứ 76 sẽ giúp bạn điền vào tất cả những ô trống theo danh sách cách nhanh chóng và dễ dàng.

Đa phần các chức năng của Excel được thiết kế để sử dụng trên các danh sách. Để các chức năng có thể làm việc chính xác, danh sách không được chứa bất kỳ ô trống, và tiêu đề cột nên được định dạng khác với các dữ liệu trong danh sách. Công việc thiết lập dữ liệu trong Excel sẽ trôi chảy hơn nếu bạn thực hiện đúng những yêu cầu trên. Tuy nhiên, nhiều danh sách lại được thiết lập như hình dưới đây.

Trong khi Giá cả được lặp đi lặp lại nhiều lần, các loại trái cây trong cột Trái cây lại không được lặp lại như vậy. Như đã nói ở trên, điều này sẽ tạo ra nhiều vấn đề khi sử dụng các tính năng như Subtotals và PivotTables. Trong hầu hết các trường hợp, Excel hy vọng các dữ liệu của bạn sẽ được thiết lập trong 1 danh sách liên tục hoặc trong 1 bảng, và không có ô trống. Vậy làm thế nào để điền hết các ô trống mà không bỏ sót?

Bạn có thể điền vào các ô trống trong danh sách trên bằng cách sử dụng công thức hoặc bằng macro.

Cách 1: Sử dụng Công thức

Lấy lại ví dụ trên, bạn sẽ làm như sau:

1/ Chọn tất cả các dữ liệu trong cột A. Sau đó chọn Home -> Editing -> Find & Select -> Go To Special (có thể dùng phím tắt: Ctrl-G, nhấn vào Special).

2/ Chọn tùy chọn Blanks và nhấn OK.

Và kết quả bạn sẽ chọn được các ô trống trong danh sách như hình.

3/ Nhấn phím =, rồi lại nhấn phím mũi tên lên, và cuối cùng Ctrl-Enter. Bạn có thể hiểu hành động này nhằm mục đích cho Excel hiểu đối với những ô rỗng thì sẽ mang công thức = giá trị của ô liền kề phía trên, giả sử như trong hình dưới đây thì ô A3 sẽ mang công thức =A2.

4/ Bây giờ để chuyển đổi từ công thức thành giá trị, bạn chọn cột A, nhấn chuột phải và chọn Copy (Ctrl-C). Nhấn chuột phải lần nữa và chọn Paste Special…, nhấn vào ô Values, sau đó nhấn OK.

Cách 2 : Sử dụng Marco

Công việc đòi hỏi bạn phải điền vào ô trống thường xuyên, tốn nhiều thời gian quá. Bạn cần phương án tối ưu hơn. Tại sao chúng ta không xử lý vấn đề này thông qua macro? Đoạn macro sau đây sẽ giúp bạn xử lý công việc dễ dàng hơn.

1/ Để sử dụng macro, bạn nhấn đồng thời 2 phím Alt-F11, sau đó chọn Insert -> Module.

2/ Nhập đoạn mã sau vào hộp thoại:

Sub FillBlanks()
    Dim rRange1 As Range, rRange2 As Range
    Dim lReply As Integer
        If Selection.Cells.Count = 1 Then
            MsgBox "Ban can chon mot vung va vung do can co o rong"
            Exit Sub
        ElseIf Selection.Columns.Count > 1 Then
            MsgBox "Vung cua ban chi co the trong 1 cot duy nhat"
            Exit Sub
        End If
        Set rRange1 = Selection
        On Error Resume Next
        Set rRange2 = rRange1.SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If rRange2 Is Nothing Then
            MsgBox "Khong co o rong duoc tim thay"
            Exit Sub
        End If
        rRange2.FormulaR1C1 = "=R[-1]C"
        lReply = MsgBox("Ban co muon Paste Value cho cac o rong?", vbYesNo + vbQuestion)
        If lReply = vbYes Then rRange1 = rRange1.Value
End Sub

3/ Sau khi nhập mã xong, đóng lại và trở về Excel, lưu lại Bảng tính này.

4/ Bây giờ, vào thẻ Developer, chọn Code -> Macros hoặc dùng phím tắt Alt-F8. Sau đó, chọn Macro FillBlanks và Run.

LƯU Ý: Phải chọn danh sách có chứa những ô trống trước khi mở hộp thoại Macro và nhấn nút Run, và chỉ chọn duy nhất 1 cột có chứa những ô trống để thực hiện lệnh.

Nguyễn Bảo Khanh

MỤC LỤC Những tuyệt chiêu trong Excel
​

Một số bài viết có liên quan:
1/ Chiêu thứ 75: Đối chiếu dữ liệu cũ và mới
2/ Chiêu thứ 74: Di chuyển công thức chứa địa chỉ tương đối mà không làm thay đổi tham chiếu
3/ Chiêu thứ 73: Thêm mô tả vào công thức của bạn
4/ Chiêu thứ 72: Tạo số tổng cộng cho biểu đồ cột nhiều thành phần (Stacked Column Chart)
5/ Chiêu thứ 71: Đánh dấu tên trục theo điều kiện
6/ Chiêu thứ 70: Thay thế điểm bằng hình
7/ Chiêu thứ 69: Biểu đồ cơn lốc
8/ Chiêu thứ 68: Vị trí thông số của trục
9/ Chiêu thứ 67: Chỉnh lại trục X khi sử dụng dữ liệu ngày tháng
10/ Chiêu thứ 65: Thêm mũi tên chỉ hướng cho biểu đồ đường

 

[dinhthopro123]

hay quá, vậy mà trước mình cứ loay hoay tìm trên google mãi không được.