Lặp lại điền vào excel

Rất nhiều lần, bạn có thể gặp phải một tập dữ liệu chỉ có một ô được điền dữ liệu và các ô bên dưới trống cho đến giá trị tiếp theo

Một cái gì đó như hình dưới đây

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 Pivot hoặc sử dụng nó trong tính toán

Và điều này có một sửa chữa dễ dàng

Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách điền nhanh các ô trong Excel cho đến giá trị được điền tiếp theo

Bạn có thể dễ dàng thực hiện việc này bằng cách sử dụng 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

Hướng dẫn này bao gồm

• Phương pháp 1 – Điền xuống Sử dụng Chuyển đến Đặc biệt + Công thức
  • Chuyển đổi công thức thành giá trị
  • Thay đổi định dạng ngày
• Phương pháp 2 – Điền xuống bằng mã VBA đơn giản
• Phương pháp 3 – Điền xuống bằng Power Query

Phương pháp 1 – Điền xuống Sử dụng Chuyển đến Đặc biệt + Công thức

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 dữ liệu vào cột A và cột B

Trong cột B, mục đích 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 các ô bên dưới cho đến khi các ô trống

Bên dưới các bước sử dụng, hãy chuyển đến phần đặ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

1. Chọn dữ liệu mà bạn muốn điền vào (A1. D21 trong ví dụ của chúng tôi)
2. Chuyển đến tab 'Trang chủ'

1. Trong nhóm Chỉnh sửa, nhấp vào biểu tượng ‘Tìm & Chọn’ (điều này sẽ hiển thị thêm một số tùy chọn trong trình đơn thả xuống)

1. Nhấp vào tùy chọn 'Chuyển đến'

1. Trong hộp thoại Go-To mở ra, 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'

1. Trong hộp thoại Đi đến Đặc biệt, nhấp vào Khoảng trống

1. 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 (như hình bên dưới)

Trong các ô trống được chọn, bạn sẽ nhận thấy rằng một ô sáng hơn so với phần còn lại của vùng chọn. Ô này là ô đang hoạt động nơi chúng ta sẽ 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ờ, bên dưới là các bước để điền dữ liệu vào các ô trống đã chọn

1. Nhấn phím bằng (=) trên bàn phím của bạn. Thao tác này sẽ chèn dấu bằng vào ô hiện hoạt
2. 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à ô đang hoạt động và khi chúng tôi thực hiện hai bước này, nó sẽ nhập =B2 vào ô
3. Giữ phím Control và nhấn phím Enter

Các bước trên sẽ tự động chèn tất cả các ô trống có giá trị ở trên chúng

Mặc dù điều này có vẻ giống như quá nhiều bước, nhưng khi bạn hiểu rõ về phương pháp này, bạn sẽ có thể nhanh chóng điền dữ liệu vào Excel trong vòng vài giây

Bây giờ, có hai điều nữa mà bạn cần quan tâm khi sử dụng phương pháp này

Chuyển đổi công thức thành giá trị

Điều đầu tiên là đảm bảo rằng bạn chuyển đổi công thức thành giá trị (để bạn có các giá trị tĩnh và mọi thứ không bị xáo trộn trong trường hợp bạn thay đổi dữ liệu trong tương lai)

Dưới đây là video hướng dẫn các bạn cách chuyển đổi công thức thành giá trị trong Excel nhanh chóng

Thay đổi định dạng ngày

Trong trường hợp bạn đang sử dụng ngày trong dữ liệu của mình (như tôi đang sử dụng trong dữ liệu ví dụ), bạn sẽ nhận thấy rằng các giá trị được điền trong cột ngày là số chứ không phải ngày

Nếu đầu ra trong cột ngày ở định dạng ngày mong muốn, bạn không cần phải làm gì khác

Nhưng nếu những thứ này không ở định dạng ngày chính xác, bạn cần thực hiện một điều chỉnh nhỏ. Trong khi bạn có các giá trị phù hợp, bạn chỉ cần thay đổi định dạng để nó được hiển thị dưới dạng ngày trong ô

Dưới đây là các bước để làm điều này

1. Chọn cột có ngày
2. Nhấp vào tab Trang chủ
3. Trong nhóm Số, nhấp vào trình đơn thả xuống Định dạng rồi chọn định dạng ngày

Nếu thỉnh thoảng bạn cần thực hiện thao tác điền này, tôi khuyên bạn nên sử dụng kỹ thuật công thức và đặc biệt Go-To này

Mặc dù có một vài bước, nhưng nó đơn giản và cung cấp cho bạn kết quả ngay trong tập dữ liệu của bạn

Nhưng trong trường hợp bạn phải làm điều này khá thường xuyên, thì tôi khuyên bạn nên xem VBA và các phương pháp Power Query được trình bày tiếp theo

Phương pháp 2 – Điền xuống bằng mã VBA đơn giản

Bạn có thể sử dụng mã macro VBA thực sự đơ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í nhiều sổ làm việc trên hệ thống của mình

Dưới đây là mã VBA sẽ đi qua từng ô trong vùng chọn và điền vào tất cả các ô trống

'This VBA code is created by Sumit Bansal from TrumpExcel.com
Sub FillDown()
For Each cell In Selection
  If cell = "" Then
    cell.FillDown
  End If
Next
End Sub

Đoạn mã trên sử dụng vòng lặp For để đi qua từng ô trong vùng chọn

Trong vòng lặp For, tôi đã sử dụng điều kiện Nếu-Thì để kiểm tra xem ô có trống hay không

Trong trường hợp ô trống, nó sẽ được điền giá trị từ ô phía trên và trong trường hợp ô không trống, thì vòng lặp for chỉ cần 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

1. Chọn dữ liệu mà bạn muốn điền vào
2. Nhấp vào tab Nhà phát triển

1. Bấm vào biểu tượng Visual Basic (hoặc có thể 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

1. Trong Visual Basic Editor, ở bên trái, bạn sẽ có Project Explorer. Nếu bạn không nhìn thấy nó, hãy nhấp vào tùy chọn 'Xem' trong menu rồi nhấp vào Project Explorer

1. Trong trường hợp bạn mở nhiều sổ làm việc Excel, Project Explorer sẽ hiển thị cho bạn tất cả các tên sổ làm việc. Định vị tên sổ làm việc nơi bạn có dữ liệu
2. 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, rồi 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 đó

1. Nhấp đúp vào ‘Mô-đun’ bạn vừa chèn ở bước trên. Nó sẽ mở cửa sổ mã cho mô-đun đó

1. Sao chép-dán mã VBA vào cửa sổ mã

1. Đặt con trỏ ở bất kỳ đâu trong mã và sau đó 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 cần lưu tệp này dưới dạng sổ làm việc Excel có hỗ trợ macro (có. phần mở rộng XLSM)

Một điều khác mà bạn có thể làm là thêm macro này vào Thanh công cụ truy cập nhanh, luôn hiển thị và bạn sẽ có quyền truy cập vào macro này chỉ bằng một cú nhấp chuột (trong sổ làm việc nơi bạn có mã ở phần phụ trợ)

Vì vậy, lần tới khi bạn có dữ liệu cần điền vào, tất cả những gì bạn cần làm là thực hiện lựa chọn rồi nhấp vào nút macro trong Thanh công cụ truy cập nhanh

Bạn cũng có thể thêm macro này vào Sổ làm việc Macro Cá nhân rồi sử dụng nó trong bất kỳ sổ làm việc nào trên hệ thống của bạn

Nhấp vào đây để đọc thêm về sổ làm việc macro cá nhân và cách thêm mã vào chúng

Phương pháp 3 – Điền xuống bằng Power Query

Power Query có chức năng sẵn có cho phép bạn điền thông tin xuống chỉ bằng một cú nhấp chuột

Bạn nên sử dụng Power Query khi bạn vẫn đang sử dụng nó để chuyển đổi dữ liệu của mình 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 tại của bạn, bạn có thể sử dụng tùy chọn điền xuống trong Power Query để nhanh chóng điền vào các ô trống

Để 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 của mình thành bảng Excel, thì bạn sẽ phải tạo một dải ô đã đặt tên cho dữ liệu, rồi sử dụng dải ô đã đặt tên đó trong Power Query

Bên dưới mình có bộ dữ liệu mà mình đã convert thành bảng excel rồi. Bạn có thể làm điều đó bằng cách chọn tập dữ liệu, chuyển đến tab Chèn, sau đó 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

1. Chọn bất kỳ ô nào trong tập dữ liệu
2. Nhấp vào tab Dữ liệu

1. Trong nhóm Get & Transform Data, nhấp vào ‘From Sheet’. Thao tác này sẽ mở trình soạn thảo Power Query. Lưu ý rằng các ô trống sẽ hiển thị giá trị 'null' trong Power Query

1. Trong trình soạn thảo Power Query, hãy chọn các cột mà bạn muốn điền vào. Để thực hiện việc này, hãy giữ phím Control rồi bấm vào tiêu đề của các cột mà bạn muốn chọn. Trong ví dụ của chúng tôi, đó sẽ là cột Ngày và Sản phẩm
2. Nhấp chuột phải vào bất kỳ tiêu đề nào đã chọn

1. Chuyển đến tùy chọn 'Điền' và sau đó nhấp vào 'Xuống'. Điều này sẽ điền dữ liệu vào tất cả các ô trống

1. Nhấp vào tab Tệp rồi nhấp vào 'Đóng và Tải'. Thao tác này sẽ chèn một trang tính mới vào sổ làm việc với bảng kết quả

Mặc dù phương pháp này nghe có vẻ hơi quá mức cần thiết nhưng một 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ả trong trường hợp dữ liệu gốc của bạn thay đổi

Ví dụ: nếu bạn thêm nhiều bản ghi vào dữ liệu gốc, bạn không cần phải thực hiện lại tất cả các thao tác trên. Bạn có thể chỉ cần nhấp chuột phải vào bảng kết quả và sau đó 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 mà bạn muốn điền vào các ô trống, thì sử dụng phương thức Go To đặc biệt hoặc phương thức VBA (đã trình bày ở trên) sẽ thuận tiện hơn

Vì vậy, đây là ba cách đơn giản mà bạn có thể sử dụng để điền vào các ô trống cho đến giá trị tiếp theo trong Excel