Nhiều lần, bạn có thể gặp phải tập dữ liệu Excel trong đó chỉ có một ô được điền dữ liệu và các ô bên dưới nó trống cho đến giá trị tiếp theo.
Như hình bên dưới:
Mặc dù định dạng này phù hợp với một số người, nhưng vấn đề với loại dữ liệu này là bạn không thể sử dụng nó để tạo bảng tổng hợp hoặc sử dụng nó trong tính toán.
Thật dễ dàng để sửa chữa!
Trong hướng dẫn này, tôi sẽ chỉ cho bạn cáchNhanh chóng điền vào các ô cho đến khi điền giá trị tiếp theo trong Excel.
Bạn có thể thực hiện việc này một cách dễ dàng bằng cách sử dụng các kỹ thuật hộp thoại đặc biệt Go-To đơn giản, VBA hoặc Power Query.
Vậy hãy bắt đầu!
Nội dung
Phương pháp 1 - Điền bằng cách sử dụng công thức Chuyển đến Đặc biệt +
Giả sử bạn có một tập dữ liệu như hình dưới đây và bạn muốn điền dữ liệu vào cột A và B.
Trong cột B, mục tiêu là điền "máy in" cho đến ô trống cuối cùng bên dưới nó, sau đó khi "máy quét" bắt đầu, sau đó điền "máy quét" vào ô bên dưới cho đến khi ô trống.
Các bước dưới đây sử dụng chuyển sang đặc biệt để chọn tất cả các ô trống và điền vào bằng một công thức đơn giản:
- Chọn dữ liệu để điền (A1: D21 trong ví dụ của chúng tôi)
- Chuyển đến tab "Trang chủ"
- Trong nhóm chỉnh sửa, nhấp vào biểu tượng Tìm và Chọn (biểu tượng này sẽ hiển thị nhiều tùy chọn hơn trong trình đơn thả xuống)
- Nhấp vào tùy chọn "Bắt đầu"
- Trong hộp thoại Đi tới mở ra, hãy nhấp vào nút Đặc biệt.Thao tác này sẽ mở hộp thoại "Chuyển đến Đặc biệt"
- Trong hộp thoại Chuyển đến Đặc biệt, bấm vào Trống
- Bấm OK
Các bước trên sẽ chọn tất cả các ô trống trong tập dữ liệu (hiển thị bên dưới).
Trong số các ô trống được chọn, bạn sẽ nhận thấy rằng một ô sáng hơn phần còn lại của vùng chọn.Ô này là ô hiện hoạt mà chúng ta muốn nhập công thức.
Đừng lo lắng về vị trí của ô trong vùng chọn, vì phương pháp của chúng tôi sẽ hoạt động trong mọi trường hợp.
Bây giờ, đây là các bước để điền dữ liệu vào các ô trống đã chọn:
- Nhấp vào phím dấu bằng (=) trên bàn phím của bạn.Thao tác này sẽ chèn một 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 tham chiếu ô của ô phía trên ô hiện hoạt.Ví dụ: trong trường hợp của chúng tôi, B3 là ô hiện hoạt và khi chúng tôi thực hiện hai bước này, nó sẽ nhập = B2 trong ô
- Giữ phím Control và nhấn Enter
Các bước trên sẽ tự động chèn tất cả các ô trống với các giá trị trên.
Mặc dù đây có vẻ là quá nhiều bước, nhưng một khi bạn hiểu rõ, bạn sẽ có thể nhanh chóng điền dữ liệu vào Excel trong vài giây.
Bây giờ, có hai điều nữa bạn cần phải lưu ý khi sử dụng phương pháp này.
Chuyển đổi công thức thành giá trị
Đầu tiên là đảm bảo bạn chuyển đổi công thức thành giá trị (vì vậy bạn có giá trị tĩnh và không bị rối khi bạn thay đổi dữ liệu trong tương lai).
Thay đổi định dạng ngày
Nếu bạn sử dụng ngày trong dữ liệu của mình (như tôi đã làm trong dữ liệu mẫu của mình), bạn sẽ nhận thấy rằng các giá trị được điền trong cột ngày là số thay vì ngày.
Nếu đầu ra trong cột ngày ở định dạng ngày mong muốn, thì bạn đã tốt và không cần phải làm gì khác.
Nhưng nếu những ngày đó không đúng định dạng, thì cần phải tinh chỉnh.Trong khi bạn có giá trị chính xác, bạn chỉ cần thay đổi định dạng để nó hiển thị dưới dạng ngày tháng trong ô.
Dưới đây là các bước để thực hiện việc này:
- Chọn các cột chứa ngày tháng
- Nhấp vào tab Trang chủ
- Trong nhóm Số, bấm vào menu thả xuống Định dạng và chọn một định dạng ngày.
Nếu bạn cần thực hiện phần đệm này theo thời gian, tôi khuyên bạn nên thực hiện kỹ thuật đặc biệt và công thức Go-To này.
Mặc dù nó có một vài bước, nhưng nó rất đơn giản và cung cấp cho bạn kết quả trong tập dữ liệu.
Nhưng nếu bạn phải làm điều này thường xuyên thì tôi khuyên bạn nên xem xét VBA và phương pháp Power Query được mô tả tiếp theo
Phương pháp 2 - Điền bằng mã VBA đơn giản
Bạn có thể sử dụng mã macro VBA rất đơn giản để nhanh chóng điền vào các ô trong Excel cho đến giá trị trống cuối cùng.
Bạn chỉ cần thêm mã VBA vào tệp một lần và sau đó bạn có thể dễ dàng sử dụng lại mã đó nhiều lần trong cùng một sổ làm việc hoặc thậm chí trên nhiều sổ làm việc trên hệ thống của mình.
Đây là mã VBA sẽ lặp lại qua từng ô trong vùng chọn và điền vào bất kỳ ô trống nào:
Sub FillDown () Cho Mỗi ô Trong Lựa chọn Nếu ô = "" Sau đó Ô.FillDown Kết thúc Nếu Tiếp theo Kết thúc Sub
Đoạn mã trên sử dụng vòng lặp For để lặp qua từng ô trong vùng chọn.
Trong vòng lặp For, tôi sử dụng điều kiện If-Then để kiểm tra xem ô có trống không.
Nếu ô trống, nó được điền giá trị từ ô ở trên, nếu không, vòng lặp for sẽ bỏ qua ô đó và chuyển sang ô tiếp theo.
Bây giờ bạn đã có mã VBA, hãy để tôi chỉ cho bạn nơi đặt mã này trong Excel:
- Chọn dữ liệu để điền
- Nhấp vào tab nhà phát triển
- Nhấp vào biểu tượng Visual Basic (hoặc bạn có thể sử dụng phím tắt ALT + F11).Thao tác này sẽ mở trình soạn thảo Visual Basic trong Excel
- Trong trình soạn thảo Visual Basic ở bên trái, bạn sẽ có Trình khám phá dự án.Nếu bạn không thấy nó, hãy nhấp vào tùy chọn View trong menu, sau đó nhấp vào Project Explorer
- Nếu bạn có nhiều sổ làm việc Excel đang mở, Project Explorer sẽ hiển thị tất cả các tên sổ làm việc.Tìm tên của sổ làm việc mà bạn có dữ liệu.
- Bấm chuột phải vào bất kỳ đối tượng nào trong sổ làm việc, đi tới Chèn và bấm vào Mô-đun.Thao tác này sẽ chèn một mô-đun mới cho sổ làm việc này
- Nhấp đúp vào "Mô-đun" bạn vừa chèn trong các bước ở trên.Nó sẽ mở cửa sổ mã cho mô-đun đó
- Sao chép và dán mã VBA vào cửa sổ mã
- Đặt con trỏ ở bất kỳ vị trí nào trong mã và chạy mã macro bằng cách nhấp vào nút màu xanh lục trên thanh công cụ hoặc sử dụng phím tắt F5
Các bước trên sẽ chạy mã VBA và dữ liệu của bạn sẽ được điền vào.
Nếu bạn muốn sử dụng lại mã VBA này trong tương lai, bạn sẽ cần lưu tệp này dưới dạng sổ làm việc Excel hỗ trợ macro (với phần mở rộng .XLSM)
Một điều khác bạn có thể làm là thêm macro này vào Thanh công cụ truy cập nhanh của bạn, thanh công cụ này luôn hiển thị và bạn có thể truy cập macro này chỉ bằng một cú nhấp chuột (trong sổ làm việc có mã trong phần phụ trợ).
Vì vậy, lần tới khi bạn có dữ liệu cần được điền vào, bạn có thể chỉ cần thực hiện lựa chọn và nhấp vào nút macro trong Thanh công cụ Truy nhập Nhanh.
Bạn cũng có thể thêm macro này vào sổ làm việc macro cá nhân của mình và sau đó sử dụng nó trong bất kỳ sổ làm việc nào trên hệ thống của bạn.
Bấm vào đây để đọc thêm về sổ làm việc macro cá nhân của bạn và cách thêm mã vào đó.
Phương pháp 3 - Điền bằng Power Query
Truy vấn nguồnCó chức năng tích hợp cho phép bạn điền thông tin bằng một cú nhấp chuột.
Khuyến nghị khi bạn vẫn đang sử dụng Power Query để chuyển đổi dữ liệu hoặc kết hợp dữ liệu từ nhiều trang tính hoặc nhiều sổ làm việc.
Là một phần của quy trình làm việc hiện có, bạn có thể nhanh chóng điền vào các ô trống bằng cách sử dụng tùy chọn Điền trong Power Query.
Để sử dụng Power Query, dữ liệu của bạn nên ở định dạng bảng Excel.Nếu bạn không thể chuyển đổi dữ liệu sang bảng Excel, bạn phải tạo một dải ô đã đặt tên cho dữ liệu và sau đó sử dụng dải ô được đặt tên đó trong Power Query.
Dưới đây, tôi có một tập dữ liệu đã được chuyển đổi thành bảng Excel.Bạn có thể thực hiện việc này bằng cách chọn tập dữ liệu, chuyển đến tab Chèn và nhấp vào biểu tượng Bảng.
Dưới đây là các bước sử dụng Power Query để điền dữ liệu cho đến giá trị tiếp theo:
- Chọn bất kỳ ô nào trong tập dữ liệu
- Nhấp vào tab Dữ liệu
- Trong nhóm Lấy và Biến đổi Dữ liệu, hãy bấm Từ Trang tính.Thao tác này sẽ mở trình chỉnh sửa Power Query.Lưu ý rằng các ô trống sẽ hiển thị giá trị "null" trong Power Query
- Trong Power Query Editor, hãy chọn cột bạn muốn điền vào.Để thực hiện việc này, hãy giữ phím Control và nhấp vào tiêu đề của cột bạn muốn chọn.Trong ví dụ của chúng tôi, nó sẽ là cột ngày và sản phẩm.
- Nhấp chuột phải vào bất kỳ tiêu đề đã chọn nào
- Chuyển đến tùy chọn Điền và nhấp vào Xuống.Điều này sẽ điền vào tất cả các ô trống với dữ liệu
- Bấm vào tab Tệp, sau đó bấm Đóng và Tải.Thao tác này sẽ chèn một trang tính mới với bảng kết quả trong sổ làm việc
Mặc dù cách tiếp cận này nghe có vẻ quá mức cần thiết, nhưng một trong những lợi ích tuyệt vời của việc sử dụng Power Query là nó cho phép bạn nhanh chóng cập nhật dữ liệu kết quả khi dữ liệu ban đầu thay đổi.
Ví dụ: nếu bạn thêm nhiều bản ghi vào dữ liệu ban đầu, bạn không cần phải thực hiện lại tất cả những điều trên.Bạn có thể chỉ cần nhấp chuột phải vào bảng kết quả và nhấp vào làm mới.
Mặc dù phương pháp Power Query hoạt động tốt, nhưng tốt nhất bạn nên sử dụng nó khi bạn đã sử dụng Power Query trong quy trình làm việc của mình.
Nếu bạn chỉ có một tập dữ liệu với các ô trống để điền vào, sẽ thuận tiện hơn khi sử dụng phương pháp Đi tới đặc biệt hoặc phương pháp VBA (được mô tả ở trên).
Vì vậy, bạn có thể sử dụng ba phương pháp đơn giản này đểĐiền vào các ô trống cho đến giá trị tiếp theo trong Excel.
Tôi hy vọng bạn thấy hướng dẫn này hữu ích.
