Close Menu
Subscribe

Hướng Dẫn Cách Fill Down Trong Excel: Tự Động Lấp Đầy Ô Trống (3 Phương Pháp)

Không có bình luận12 Mins Read
Dữ liệu Excel mẫu với nhiều ô trống ở cột ngày tháng và sản phẩm
Dữ liệu Excel mẫu với nhiều ô trống ở cột ngày tháng và sản phẩm

Thời gian đọc ước tính: 12 phút

Những điểm chính:

  • Hiểu rõ vấn đề: Các ô trống trong dữ liệu làm gián đoạn quy trình tạo Pivot Table và tính toán.
  • Phương pháp thủ công: Sử dụng tính năng “Go To Special” kết hợp công thức để xử lý nhanh các tập dữ liệu nhỏ.
  • Tự động hóa với VBA: Áp dụng mã Macro đơn giản để lấp đầy dữ liệu chỉ với một cú nhấp chuột cho các tác vụ lặp lại.
  • Xử lý dữ liệu lớn: Sử dụng Power Query để chuẩn hóa dữ liệu đầu vào một cách chuyên nghiệp và có thể tái sử dụng.

Trong quá trình xử lý dữ liệu trên Excel, bạn sẽ thường xuyên gặp phải các bảng tính mà chỉ có ô đầu tiên của nhóm dữ liệu được điền thông tin, còn các ô bên dưới bị bỏ trống cho đến khi xuất hiện giá trị tiếp theo. Định dạng trình bày này giúp mắt người dễ nhìn hơn, nhưng lại là một trở ngại lớn đối với máy tính khi xử lý dữ liệu.

Ví dụ điển hình như hình bên dưới:

Dữ liệu Excel mẫu với nhiều ô trống ở cột ngày tháng và sản phẩmDữ liệu Excel mẫu với nhiều ô trống ở cột ngày tháng và sản phẩm

Vấn đề nằm ở chỗ: Bạn không thể sử dụng dữ liệu ngắt quãng này để tạo Pivot Table chính xác hoặc thực hiện các hàm tính toán logic. Excel cần dữ liệu liên tục để hiểu rằng các ô trống bên dưới thuộc về nhóm dữ liệu phía trên.

Bài viết này sẽ hướng dẫn bạn 3 phương pháp cụ thể để Fill Down (lấp đầy dữ liệu xuống dưới) cho đến khi gặp giá trị mới. Chúng ta sẽ đi từ cách sử dụng công cụ có sẵn, dùng mã VBA, cho đến công cụ xử lý dữ liệu nâng cao Power Query.

Phương pháp 1: Sử dụng Go To Special kết hợp công thức (Nhanh & Hiệu quả)

Đây là phương pháp phổ biến nhất để xử lý các tập dữ liệu vừa và nhỏ mà không cần cài đặt thêm bất kỳ công cụ nào. Giả sử bạn có bảng dữ liệu từ A1:D21 và cần lấp đầy thông tin cho Cột A (Date) và Cột B (Product).

Bảng dữ liệu Excel cần thực hiện lấp đầy ô trống ở cột A và BBảng dữ liệu Excel cần thực hiện lấp đầy ô trống ở cột A và B

Mục tiêu là điền giá trị “Printer” xuống các ô trống bên dưới nó cho đến khi gặp từ “Scanner”, và tiếp tục quy trình đó cho các nhóm còn lại.

Quy trình thực hiện chi tiết

Để chọn toàn bộ các ô trống và điền giá trị tự động, bạn thực hiện theo 7 bước sau:

  1. Quét chọn vùng dữ liệu: Bôi đen vùng dữ liệu bạn cần xử lý (trong ví dụ này là vùng A1:D21).
  2. Truy cập thẻ Home: Tại thanh công cụ (Ribbon), chọn tab Home.

Chọn thẻ Home trên thanh công cụ ExcelChọn thẻ Home trên thanh công cụ Excel

  1. Mở công cụ tìm kiếm: Trong nhóm công cụ “Editing” (Chỉnh sửa), nhấp vào biểu tượng Find & Select.

Chọn tính năng Find and Select trong nhóm EditingChọn tính năng Find and Select trong nhóm Editing

  1. Chọn Go To: Trong danh sách xổ xuống, chọn Go To… (hoặc nhấn phím tắt Ctrl + G).

Chọn mục Go To trong menu xổ xuốngChọn mục Go To trong menu xổ xuống

  1. Mở hộp thoại Special: Khi hộp thoại Go To xuất hiện, nhấp vào nút Special… ở góc dưới.

Nhấn nút Special trong hộp thoại Go ToNhấn nút Special trong hộp thoại Go To

  1. Chọn Blanks: Trong bảng Go To Special, tích chọn vào mục Blanks (Ô trống).

Chọn tùy chọn Blanks để tìm các ô trốngChọn tùy chọn Blanks để tìm các ô trống

  1. Xác nhận: Nhấn OK.

Lúc này, Excel sẽ tự động chọn tất cả các ô trống nằm trong vùng dữ liệu bạn đã bôi đen ban đầu.

Các ô trống trong bảng dữ liệu đã được chọn đồng loạtCác ô trống trong bảng dữ liệu đã được chọn đồng loạt

Bạn sẽ thấy có một ô trống có màu sáng hơn các ô còn lại (thường là ô đầu tiên trong vùng chọn). Đây chính là ô hiện hành (active cell) nơi chúng ta sẽ nhập công thức.

Thiết lập công thức điền dữ liệu

Sau khi các ô trống đã được chọn, tuyệt đối không nhấp chuột ra ngoài. Hãy thực hiện chính xác thao tác sau:

  1. Nhấn phím dấu bằng (=) trên bàn phím.
  2. Nhấn phím Mũi tên lên (↑). Excel sẽ tham chiếu đến ô nằm ngay phía trên ô hiện hành. Ví dụ: Nếu ô đang chọn là B3, công thức sẽ là =B2.
  3. Thao tác quan trọng: Nhấn và giữ phím Ctrl, sau đó nhấn Enter.

Kết quả là công thức sẽ được sao chép đồng loạt vào tất cả các ô trống đang được chọn, lấy giá trị từ ô liền kề phía trên nó.

Kết quả sau khi thực hiện Fill Down: Tất cả ô trống đã được điền dữ liệuKết quả sau khi thực hiện Fill Down: Tất cả ô trống đã được điền dữ liệu

Xử lý hậu kỳ: Chuyển công thức thành giá trị và định dạng ngày

Sau khi thực hiện xong, dữ liệu trong các ô này vẫn đang ở dạng công thức. Nếu bạn sắp xếp (sort) hoặc di chuyển dữ liệu, kết quả sẽ bị sai lệch. Do đó, bạn cần thực hiện thêm hai bước nhỏ:

  1. Chuyển đổi sang giá trị tĩnh (Paste Values):

    • Bôi đen lại toàn bộ vùng dữ liệu vừa làm.
    • Nhấn Ctrl + C để copy.
    • Nhấp chuột phải, chọn biểu tượng Values (V) trong mục Paste Options. Thao tác này giúp loại bỏ công thức và giữ lại dữ liệu cố định.

  2. Định dạng lại ngày tháng (Date Format):

    • Các ô ngày tháng sau khi fill down có thể hiển thị dưới dạng số (ví dụ: 44345 thay vì 01/06/2021).
    • Chọn cột chứa ngày tháng.
    • Vào thẻ Home, tại nhóm Number, chọn định dạng Short Date hoặc Long Date tùy nhu cầu.

Chọn định dạng ngày tháng trong nhóm Number trên thẻ HomeChọn định dạng ngày tháng trong nhóm Number trên thẻ Home

Phương pháp này tuy có nhiều bước nhưng rất linh hoạt và áp dụng được ngay mà không cần kiến thức lập trình.

Phương pháp 2: Sử dụng mã VBA (Tự động hóa)

Nếu bạn phải thực hiện thao tác fill down này hàng ngày trên nhiều file khác nhau, việc lặp lại các bước thủ công ở trên sẽ tốn thời gian. Giải pháp tối ưu hơn là sử dụng một đoạn mã VBA ngắn (Macro).

Bạn chỉ cần thêm mã này vào file một lần và có thể tái sử dụng mãi mãi.

Đoạn mã VBA Fill Down

Dưới đây là đoạn code giúp quét qua từng ô trong vùng chọn và tự động điền giá trị nếu ô đó trống:

'Code được viết bởi Sumit Bansal từ TrumpExcel.com
Sub FillDown()
    For Each cell In Selection
        If cell = "" Then
            cell.FillDown
        End If
    Next
End Sub

Giải thích cơ chế hoạt động:

  • For Each cell In Selection: Vòng lặp For Loop sẽ chạy qua từng ô mà bạn đang bôi đen.
  • If cell = “” Then: Kiểm tra điều kiện xem ô đó có trống (rỗng) hay không.
  • cell.FillDown: Nếu ô trống, lệnh này sẽ lấy giá trị của ô ngay phía trên để điền vào.

Cách cài đặt mã VBA vào Excel

  1. Chọn vùng dữ liệu bạn muốn xử lý.
  2. Trên thanh Ribbon, chọn thẻ Developer (Nhà phát triển).

Chọn thẻ Developer trên thanh công cụChọn thẻ Developer trên thanh công cụ

  1. Nhấp vào biểu tượng Visual Basic ở góc trái (hoặc nhấn Alt + F11).

Mở trình soạn thảo Visual BasicMở trình soạn thảo Visual Basic

  1. Trong cửa sổ Visual Basic Editor, nhìn sang cột bên trái (Project Explorer). Tìm tên file Excel bạn đang mở.
  2. Nhấp chuột phải vào tên file, chọn Insert > Module.

Chèn Module mới vào Project ExplorerChèn Module mới vào Project Explorer

  1. Một cửa sổ trắng hiện ra, bạn hãy sao chép và dán đoạn mã VBA ở trên vào đây.

Dán đoạn mã VBA vào cửa sổ ModuleDán đoạn mã VBA vào cửa sổ ModuleSao chép code VBA vào cửa sổ soạn thảoSao chép code VBA vào cửa sổ soạn thảo

  1. Để chạy mã, nhấn nút Play (hình tam giác màu xanh lá) trên thanh công cụ hoặc nhấn phím F5.

Ngay lập tức, dữ liệu trong vùng chọn sẽ được lấp đầy.

Lưu ý: Để lưu file có chứa mã VBA, bạn phải lưu file dưới định dạng Excel Macro-Enabled Workbook (.xlsm).

Bạn cũng có thể thêm Macro này vào Quick Access Toolbar hoặc lưu vào Personal Macro Workbook để sử dụng cho mọi file Excel trên máy tính của mình mà không cần copy lại code.

Phương pháp 3: Sử dụng Power Query (Xử lý chuyên nghiệp)

Power Query là công cụ mạnh mẽ nhất để xử lý và chuẩn hóa dữ liệu. Phương pháp này đặc biệt hữu ích khi bạn cần kết hợp việc fill down với các quy trình làm sạch dữ liệu khác (như gộp bảng, tách cột, lọc dữ liệu).

Điều kiện tiên quyết: Dữ liệu nên được định dạng dưới dạng Excel Table. Nếu chưa, bạn có thể bôi đen dữ liệu và nhấn Ctrl + T.

Dữ liệu đã được chuyển sang định dạng Excel TableDữ liệu đã được chuyển sang định dạng Excel Table

Các bước thực hiện với Power Query

  1. Chọn bất kỳ ô nào trong bảng dữ liệu.
  2. Vào thẻ Data trên thanh Ribbon.

Chọn thẻ Data trên thanh công cụChọn thẻ Data trên thanh công cụ

  1. Trong nhóm “Get & Transform Data”, chọn From Sheet (hoặc From Table/Range tùy phiên bản Excel). Thao tác này sẽ mở cửa sổ Power Query Editor.

Chọn From Sheet để nạp dữ liệu vào Power QueryChọn From Sheet để nạp dữ liệu vào Power Query

Lưu ý: Trong Power Query, các ô trống sẽ được hiển thị là giá trị null.

  1. Chọn các cột cần fill down (giữ phím Ctrl để chọn nhiều cột cùng lúc, ví dụ cột Date và Product).
  2. Nhấp chuột phải vào tiêu đề cột đã chọn.

Nhấp chuột phải vào tiêu đề các cột đã chọnNhấp chuột phải vào tiêu đề các cột đã chọn

  1. Chọn Fill > Down.

Chọn lệnh Fill rồi chọn Down trong menu ngữ cảnhChọn lệnh Fill rồi chọn Down trong menu ngữ cảnh

Dữ liệu sẽ ngay lập tức được lấp đầy thay thế cho các giá trị null.

  1. Tại thẻ Home của Power Query, chọn Close & Load để xuất dữ liệu đã xử lý ra một sheet mới trong Excel.

Chọn Close and Load để xuất dữ liệu ra ExcelChọn Close and Load để xuất dữ liệu ra Excel

Ưu điểm vượt trội của Power Query:
Nếu dữ liệu nguồn thay đổi hoặc được thêm mới, bạn không cần làm lại các bước trên. Chỉ cần nhấp chuột phải vào bảng kết quả và chọn Refresh, Power Query sẽ tự động cập nhật và fill down cho dữ liệu mới.

FAQ: Câu hỏi thường gặp về Fill Down trong Excel

1. Tại sao tôi nhấn Ctrl + D nhưng không fill down được như ý muốn?
Phím tắt Ctrl + D (Fill Down) chỉ hoạt động để sao chép dữ liệu từ ô ngay phía trên xuống ô hiện tại. Nếu bạn muốn fill down cho cả một danh sách dài các nhóm dữ liệu khác nhau, Ctrl + D sẽ chỉ sao chép giá trị dòng đầu tiên xuống toàn bộ cột, làm sai lệch dữ liệu. Bạn cần dùng phương pháp Go To Special hoặc công thức mảng (Ctrl + Enter) như hướng dẫn ở Phương pháp 1.

2. Làm thế nào để xử lý nếu dữ liệu gốc là các ô bị gộp (Merged Cells)?
Các ô gộp (Merged Cells) là nguyên nhân chính gây lỗi khi fill down. Trước khi thực hiện bất kỳ phương pháp nào, bạn phải bỏ gộp ô (Unmerge) toàn bộ vùng dữ liệu:

  • Bôi đen vùng dữ liệu.
  • Vào thẻ Home > nhấp vào Merge & Center để tắt chế độ gộp.
  • Sau khi bỏ gộp, các ô trống sẽ xuất hiện và bạn có thể áp dụng 3 phương pháp trên.

3. Tôi có thể dùng cách này cho dữ liệu số không?
Hoàn toàn được. Cả 3 phương pháp đều hoạt động tốt với dữ liệu dạng văn bản (Text), số (Number), và ngày tháng (Date). Tuy nhiên, với dữ liệu số, hãy cẩn thận kiểm tra xem bạn có đang muốn điền giá trị giống nhau hay muốn tạo dãy số tăng dần (Series). Các phương pháp trong bài viết này dùng để sao chép giá trị giống nhau (Copy Cells).

4. Khi nào nên dùng Power Query thay vì công thức?
Bạn nên dùng Power Query khi:

  • Dữ liệu đầu vào rất lớn (hàng chục nghìn dòng).
  • Dữ liệu cần được cập nhật thường xuyên từ nguồn bên ngoài.
  • Bạn cần kết hợp fill down với các bước xử lý khác như lọc, gộp bảng, thay thế ký tự.

Nguồn tham khảo

Dưới đây là các nguồn tài liệu uy tín giúp bạn tìm hiểu sâu hơn về các kỹ thuật trong Excel:

  1. Microsoft Support: Fill data automatically in worksheet cells – Hướng dẫn chính thức từ Microsoft về các tính năng tự động điền dữ liệu.
  2. Excel Jet: How to fill blank cells with value above – Một nguồn tham khảo uy tín về các công thức và phím tắt Excel.
  3. Contextures: Excel Data Entry Tips – Các mẹo nhập liệu và xử lý ô trống hiệu quả.

Việc nắm vững kỹ thuật Fill Down không chỉ giúp bảng tính của bạn trở nên chuyên nghiệp, sạch sẽ mà còn là tiền đề quan trọng để thực hiện các phân tích dữ liệu phức tạp hơn. Hãy chọn phương pháp phù hợp nhất với nhu cầu công việc của bạn để tiết kiệm thời gian và nâng cao hiệu suất.

Share.

Related Posts

Leave A Reply