dòng dưới cùng. Trong bài đăng này, chúng tôi xem xét 3 cách để sao chép các giá trị trong các ô trống trong một cột. Các kỹ thuật bao gồm sử dụng công thức, Power Query và macro VBA
Cấp độ kỹ năng. Trung gian
Xem hướng dẫn
Xem trên YouTube và Đăng ký kênh của chúng tôiTải xuống tệp Excel
Bạn có thể download cả file BEGIN và FINAL nếu muốn theo dõi và luyện tập
Điền-Xuống-Kỹ thuật-BEGIN. zip Tải xuống
Điền-Xuống-Kỹ thuật-CUỐI CÙNG. zip Tải xuống
Điền vào các ô trống
Excel giúp bạn dễ dàng điền hoặc sao chép một giá trị vào các ô bên dưới. Bạn có thể chỉ cần bấm đúp hoặc kéo xuống ô điều khiển điền cho ô mà bạn muốn sao chép, để điền vào các ô bên dưới ô có cùng giá trị
Nhưng có cách nào dễ dàng để sao chép quy trình đó hàng trăm lần cho các báo cáo có lượng dữ liệu lớn không?
Trong bài đăng này, chúng tôi sẽ xem xét ba cách để tự động hóa quy trình này với. một công thức đơn giản, Power Query và macro VBA
1. Điền xuống bằng cách sử dụng công thức
Cách đầu tiên để giải quyết vấn đề này là sử dụng một công thức rất đơn giản trong tất cả các ô trống tham chiếu đến ô ở trên. Dưới đây là các bước
Bước 1. Chọn các ô trống
Để chọn các ô trống trong một cột và điền vào chúng bằng công thức, chúng tôi bắt đầu bằng cách chọn tất cả các ô [bao gồm cả các ô đã điền]. Có nhiều cách để thực hiện điều này, bao gồm giữ phím Shift trong khi bạn điều hướng đến cuối cột hoặc nếu dữ liệu của bạn nằm trong Bảng Excel, hãy sử dụng phím tắt Ctrl+Dấu cách
Kiểm tra các bài đăng của tôi về 7 Phím tắt để chọn Ô và Phạm vi trong Excel và 5 Phím tắt cho Hàng và Cột trong Excel để tìm hiểu thêm
Với tất cả các ô được chọn, chúng tôi có thể giảm bớt lựa chọn của mình để chỉ bao gồm các ô trống. Điều này được thực hiện bằng cách sử dụng cửa sổ Chuyển đến Đặc biệt từ menu Tìm & Chọn trên tab Trang chủ của dải băng
Một cách khác là mở cửa sổ Đi tới bằng F5 hoặc Ctrl+G. Sau đó nhấn nút Đặc biệt cho cửa sổ Chuyển đến Đặc biệt
Khi cửa sổ Chuyển đến Đặc biệt mở ra, bạn có thể chọn tùy chọn có nội dung Khoảng trống. Thao tác này sẽ chỉ chọn các ô trống từ vùng chọn hiện tại
Khi bạn nhấn OK, bạn sẽ nhận thấy rằng các ô có giá trị trong đó không còn được chọn nữa
Bước 2. Viết công thức
Từ đây bạn có thể bắt đầu gõ công thức, rất đơn giản. Nhập dấu bằng [=] và sau đó tham chiếu ô ở trên [trong trường hợp ví dụ của chúng tôi là B2]. Đó là tất cả những gì bạn cần cho công thức
Bước 3. Ctrl+Nhập công thức
Sau khi viết công thức, đừng chỉ nhấn Enter. Thay vào đó, hãy sử dụng Ctrl+Enter để điền vào tất cả các ô đã chọn bằng cùng một công thức. Giữ phím Ctrl, sau đó nhấn Enter
Bởi vì tham chiếu công thức của bạn là tương đối [B2], không phải tuyệt đối [$B$2], mỗi ô sẽ chỉ sao chép giá trị cho ô ngay phía trên nó
Bước 4. Sao chép và dán giá trị
Bây giờ, bạn nên sao chép toàn bộ cột dữ liệu rồi dán lên cột đó bằng cách sử dụng tùy chọn Dán giá trị để dữ liệu được mã hóa cứng. Sau đó, nếu bạn thực hiện bất kỳ sắp xếp nào, dữ liệu sẽ không thay đổi. Bạn có thể tìm thấy tùy chọn Dán giá trị trên tab Trang chủ trong menu thả xuống Dán
Có nhiều cách khác để dán giá trị, bao gồm menu chuột phải hoặc sử dụng phím tắt. Những bài đăng này giải thích các tùy chọn đó
Dán các giá trị bằng phím tắt nhấp chuột phải và kéo chuột
5 phím tắt để dán giá trị trong Excel
2. Điền xuống bằng cách sử dụng Power Query
Đối với tùy chọn này, dữ liệu của bạn phải ở định dạng Bảng Excel. Từ bất kỳ đâu trong bảng, bạn có thể chọn tab Dữ liệu hoặc Power Query rồi chọn Từ bảng/Phạm vi. Bạn cũng có thể tạo truy vấn kết nối với nguồn dữ liệu khác như cơ sở dữ liệu hoặc web
Điều này sẽ mở ra Power Query Editor. Trong Power Query, các ô trống được gắn nhãn là null trong mỗi ô
Để điền xuống, chỉ cần nhấp chuột phải vào tiêu đề cột và chọn Điền rồi Xuống
Power Query sẽ điền vào từng phần của các ô trống trong cột bằng giá trị từ ô phía trên nó
Khi bạn nhấp vào Đóng & Tải, một trang tính mới sẽ được thêm vào sổ làm việc với những thay đổi này
Quá trình điền xuống này có thể được thực hiện đồng thời cho nhiều cột bằng cách trước tiên chọn nhiều cột bằng Ctrl hoặc Shift, sau đó thực hiện thao tác Điền > Xuống
Nếu Power Query hơi mới đối với bạn, tôi mời bạn xem bài đăng này. Tổng quan về Power Query. Giới thiệu về Công cụ dữ liệu mạnh mẽ nhất của Excel
Hội thảo trực tuyến miễn phí về Power Query, VBA, v.v.
Tôi cũng có một hội thảo trực tuyến miễn phí đang diễn ra ngay bây giờ bao gồm phần giới thiệu về Power Query và các công cụ Excel khác như Power Pivot, Power BI, Macro & VBA, bảng tổng hợp, v.v.
Nhấn vào đây để đăng ký hội thảo trên web
3. Điền xuống bằng cách sử dụng Macro
Để điền bằng cách sử dụng macro, hãy bắt đầu bằng cách mở Trình soạn thảo VB. Bạn có thể thực hiện việc này bằng cách chuyển đến tab Nhà phát triển và nhấp vào nút Visual Basic hoặc bằng cách sử dụng phím tắt Alt+F11. Chèn một mô-đun mã mới, sau đó viết macro của bạn
Macro này lặp qua tất cả các ô trong phạm vi đã chọn và thực hiện phương thức FillDown nếu ô trống. Phương thức FillDown sao chép giá trị từ ô bên trên
Nếu đây là một macro mà bạn có thể sử dụng thường xuyên, bạn có thể cân nhắc việc thêm macro này vào Sổ làm việc Macro cá nhân của mình và tạo một nút tùy chỉnh cho nó. Đó là những gì tôi đã làm và nó trông như thế này trên Dải băng của tôi
Để biết hướng dẫn về cách tạo Sổ làm việc Macro Cá nhân và các nút Dải băng tùy chỉnh, hãy xem hướng dẫn này. Cách tạo Sổ làm việc Macro cá nhân [Chuỗi video]
Ưu và nhược điểm
Vậy phương pháp nào trong ba phương pháp này phù hợp với bạn?
- Phương pháp công thức đơn giản và dễ thực hiện. Nó có thể được thực hiện mà không cần bất kỳ kiến thức nào về Power Query hoặc VBA. Tuy nhiên, nó không nhanh bằng giải pháp một nút bấm
- Power Query yêu cầu dữ liệu nguồn xuất thành một trang tính riêng biệt, do đó, đây là một quy trình hơn là chỉ điền vào các ô trống. Nếu bạn đã làm sạch dữ liệu thường xuyên cho những thứ khác, thì việc thêm điều này vào quy trình hiện tại của bạn có thể có ý nghĩa hơn
- Khi macro ban đầu được thiết lập, VBA là một giải pháp nhanh chóng, dễ sử dụng—chỉ cần nhấp vào một nút. Tuy nhiên, có một nhược điểm lớn của phương pháp này. Bạn không thể hoàn tác hành động của mình sau khi nhấn nút đó, vì vậy bạn phải đảm bảo lưu một bản sao lưu trước nếu đó là mối lo ngại đối với bạn
Phần kết luận
Có ba phương pháp khác nhau cho một tác vụ Excel rất phổ biến. Tôi đã bỏ lỡ bất kỳ? . Thanks. 🙂
Trước đó LỌC Công thức trả về các cột không liền kề theo thứ tự bất kỳ
Tiếp theo Vlookup to Other Workbook – Quản lý, cập nhật và chia sẻ tệp bằng liên kết công thức
Bạn cũng có thể thích
Tham chiếu toàn bộ cột có thể khiến bạn gặp RẮC RỐI với Excel
Thử thách công thức Excel. Biển số chẵn hoặc lẻ
Cách khắc phục và ngăn chặn lỗi #VALUE với VLOOKUP
Khi nào nên sử dụng VLOOKUP thay vì XLOOKUP
Thiên thần nói.
Xin chào Jon, bạn có video hoặc hướng dẫn về cách nhập dữ liệu bằng Power Query khi số lượng và thứ tự của cột thay đổi không?
Ví dụ [giả sử tất cả các cột đều có dữ liệu số và [A], [B], [C], [D], [F] là tiêu đề]
Báo cáo tháng 1 chứa các cột này
[A B C D]
Báo cáo tháng 2 chứa các cột này
[A] [C] [D] [F]
Báo cáo tháng 3 chứa các cột này
[B] [F]
Arif nói.
Một vid tuyệt vời khác Jon
Sid nói.
Chào bạn,
Tôi đã sử dụng Excel & VBA được 20~25 năm rồi và tôi không biết về cái thứ điền dbl-click. -ô
Vì vậy, đã đăng ký nhận bản tin của bạn, nhưng tôi không thể thấy cách nào để trở thành thành viên?
Cảm ơn – trang web tuyệt vời btw
Jian nói.
Video hay, rất hữu ích. cảm ơn John
Shayla nói.
Này Jon. Tôi đã sử dụng nút GoTo để chọn các ô trống được một thời gian rồi, vì vậy tôi rất vui khi thấy một macro cho nó. Khi tôi dùng thử của bạn, nó đã khóa máy tính của tôi vì tôi đã chọn toàn bộ cột do số lượng hàng trong dữ liệu của tôi dao động. Tuy nhiên, khi tôi thử thêm một bản sửa lỗi "phạm vi hoạt động" để đáp ứng nhu cầu của mình, tôi thấy rằng phương thức GoTo [Lựa chọn. Ô đặc biệt[xlCellTypeBlanks]. Select] ở đầu macro đã thực hiện công việc. Cảm ơn vì đã khuyến khích tự động hóa một nhiệm vụ hàng ngày khác
3 начина за запълване на празни клетки - Excel от АВС до VBA says.
[…] Източник. 3 cách để điền vào các ô trống. […]
Adrian nói.
Tôi đã sử dụng mã VBA điền vào chỗ trống mà tôi đã thêm vào dây đai công cụ cá nhân của mình và nó hoạt động rất tốt. Tôi có một vấn đề bây giờ mặc dù với một số dữ liệu
Tôi có một công thức trong các ô trả về một giá trị hoặc để trống một ô =IF[ISBLANK['AR Balance Pivot'. B4],””,’Trục cân bằng AR’. B 4]. Tuy nhiên, khi tôi cố gắng sử dụng macro điền vào khoảng trống, nó sẽ không hoạt động vì ô không thực sự trống mà chứa công thức, kết quả công thức được hiển thị của nó là ô trống
Có cách nào để thay đổi mã điền vào ô trống để nó coi kết quả công thức trống là ô trống không?
Scott nói.
Thật tuyệt vời. Tôi đã cố gắng tìm giải pháp cho việc này trong một thời gian. Cảm ơn bạn vì điều này – đó là một cách tiết kiệm thời gian rất lớn
Jeff Coulter nói.
Cảm ơn bạn về thông tin. Đáng buồn thay, phương thức công thức này từng hoạt động với tôi, nhưng bây giờ nó không hoạt động với tôi nữa, ctrl+shift sẽ dán cùng một hàm vào từng ô trống đã chọn =a1, =a1, =a1. Nó không dán dưới dạng công thức gia tăng =a1, =a2, =a3
yl wong nói.
Jon thân mến, có một phương pháp đơn giản hơn. Chọn ô đã điền cộng với các ô trống bên dưới [hoặc bên phải nếu bạn muốn sao chép theo chiều ngang] và nhấn CTRL+D [hoặc CTRL+R để sao chép theo chiều ngang]
Sean nói.
Điều đó sẽ điền vào tất cả các ô có giá trị trong ô được chọn trên cùng. Phương pháp được mô tả được sử dụng khi sao chép xuống để lấp đầy khoảng trống giữa các giá trị khác nhau
JAVIER SILVA nói.
Tôi ghét rằng mã không dễ sao chép
Abe nói.
Tôi cần trợ giúp sao chép một cột có 10 ô ở cuối hàng và sao chép chúng dưới nhau trong cùng một cột. Phương pháp này chỉ hoạt động nếu bạn có một ô và sao chép bên dưới ô đó. Điều gì sẽ xảy ra nếu bạn có nhiều [giả sử là 5] và cần sao chép 5 cái đó dưới nhau trong một cột?
Kirt nói.
Cảm ơn nhiều. Đây chính xác là những gì tôi cần để tạo một báo cáo tại nơi làm việc
Dani Figueiro nói.
Cảm ơn nhiều. Nó đã giúp rất nhiều 🙂
Anand P N nói.
CT 6/9/2022 16. 42 Đang hoạt động Chưa bắt đầu more_vert
Tan Cheung Meng
1000800. IEEE-000114
NN 6/9/2022 16. 42 Đang hoạt động Chưa bắt đầu more_vert
y tá Napitupulu
10000900. Aee-185476
Vì vậy, đối với dữ liệu như trên nếu tôi phải kéo ô thứ hai “Tan Cheung Meng” và ô thứ 3 “1000800. IEEE-000114” vào cùng một dòng mà nó hiển thị đang hoạt động, tôi có thể sử dụng công thức VBA nào?