Làm cách nào để tự động điền dữ liệu từ một trang tính khác trong excel?

Khi điền vào bảng tính, các tính năng tự động điền của Excel là cách hiệu quả nhất để tiết kiệm thời gian. Hầu hết mọi người không nhận ra rằng họ có thể tự động hóa nhiều việc họ làm thủ công trong Excel

Ví dụ: có thể bạn chỉ muốn áp dụng một công thức cho mỗi hàng thứ hai hoặc hàng thứ ba khi bạn kéo xuống để tự động điền. Hoặc có thể bạn muốn điền vào tất cả các khoảng trống trên một trang tính. Bài viết này sẽ chỉ cho bạn một số cách hiệu quả nhất để tự động điền cột

1. Tự động điền mọi ô khác trong Excel

Bất kỳ ai đã sử dụng Excel một thời gian đều biết cách sử dụng tính năng tự động điền để tự động điền một ô Excel dựa trên một ô khác

Bạn chỉ cần nhấp và giữ chuột ở góc dưới bên phải của ô và kéo nó xuống để áp dụng công thức trong ô đó cho mọi ô bên dưới nó (tương tự như sao chép công thức trong Excel)

Trong trường hợp ô đầu tiên chỉ là số chứ không phải công thức, Excel sẽ tự động điền vào các ô bằng cách đếm ngược lên trên một. Tuy nhiên, nếu bạn không muốn áp dụng công thức tự động điền Excel cho mọi ô bên dưới nó thì sao?

Cách tự động điền vào mọi ô khác trong Excel

Thực hiện theo các bước được cung cấp bên dưới để tự động điền dữ liệu vào mọi ô khác trong Excel

1. Đánh dấu hai ô đầu tiên trong cột mong muốn
2. Hơn nữa, đặt chuột ở góc dưới bên phải của hai ô cho đến khi con trỏ chuyển thành dấu '+'

3. Bây giờ hãy giữ và kéo nó xuống như bình thường

Excel không còn tự động điền từng ô đơn lẻ dựa trên ô đầu tiên mà giờ đây chỉ điền từng ô thứ hai trong mỗi khối

Tự động điền khá hữu ích trong nhiều trường hợp và có thể giúp bạn tiết kiệm thời gian. Ví dụ: nếu bạn chỉ muốn mọi ô khác tự động điền và nối họ và tên, nhưng muốn giữ nguyên các dòng địa chỉ

Các tế bào khác được xử lý như thế nào

Nếu những ô thứ hai không trống thì sao?

Ví dụ: nếu ô thứ hai có "1" trong đó, thì Excel sẽ tự động điền vào mọi ô khác bằng cách đếm lên 1

Bạn có thể tưởng tượng tính linh hoạt này có thể nâng cao đáng kể mức độ hiệu quả của việc bạn có thể tự động điền dữ liệu vào trang tính như thế nào. Đó là một trong nhiều cách Excel giúp bạn tiết kiệm thời gian khi xử lý nhiều dữ liệu. Nếu bạn sử dụng Excel hàng ngày, chúng tôi thực sự khuyên bạn nên tìm hiểu một số công thức Microsoft Excel hữu ích để giúp bạn tiết kiệm thời gian và làm việc hiệu quả hơn

2. Tự động điền vào cuối dữ liệu trong Microsoft Excel

Khá dễ dàng để kéo con trỏ chuột từ trên xuống dưới của một tập hợp từ 100 đến 200 hàng để tự động điền vào cột đó. Nhưng, nếu thực sự có 10.000 hoặc 20.000 hàng trong bảng tính thì sao?

Có một mẹo nhanh để làm điều này hiệu quả hơn. Đây là cách tự động điền vào các khu vực rộng lớn trong Excel

1. Mở bảng tính Excel và chọn hai ô bất kỳ trong một cột
2. Thay vì kéo hết cột xuống, bạn chỉ cần giữ phím shift trên bàn phím
3. Bây giờ, bạn sẽ nhận thấy khi đặt chuột ở góc dưới bên phải của ô, thay vì biểu tượng dấu cộng, đó là biểu tượng có hai đường kẻ ngang, song song

4. Bây giờ, tất cả những gì bạn phải làm là nhấp đúp vào biểu tượng đó và Excel sẽ tự động điền toàn bộ cột, nhưng chỉ xuống đến vị trí cột liền kề thực sự có dữ liệu

Thủ thuật này có thể tiết kiệm vô số thời gian lãng phí khi cố gắng kéo chuột qua hàng trăm hoặc hàng nghìn hàng

3. Điền vào chỗ trống

Hãy tưởng tượng bạn được giao nhiệm vụ dọn dẹp một bảng tính Excel và sếp của bạn muốn bạn áp dụng một công thức cụ thể cho từng ô trống trong một cột

Bạn không thể thấy bất kỳ mẫu có thể dự đoán nào, vì vậy bạn không thể sử dụng thủ thuật tự động điền 'mọi dấu x khác' ở trên. Ngoài ra, cách tiếp cận đó sẽ xóa sạch mọi dữ liệu hiện có trong cột. Bạn có thể làm gì?

Trong bảng trên, sếp của bạn muốn bạn điền vào bất kỳ ô trống nào bằng chuỗi "N/A". Trong một trang tính chỉ có một vài hàng, đây sẽ là một quy trình thủ công dễ dàng. Nhưng trong một trang tính có hàng nghìn hàng, bạn sẽ mất cả ngày

Vì vậy, không làm điều đó bằng tay. Thực hiện theo các bước sau để điền vào chỗ trống bằng chuỗi cần thiết

1. Mở bảng tính Excel của bạn và chọn tất cả dữ liệu trong cột
2. Sau đó vào Home menu, chọn biểu tượng Find & Select, chọn Go To Special

3. Trong cửa sổ tiếp theo, chọn Khoảng trống

4. Bây giờ, trong cửa sổ tiếp theo, bạn có thể nhập công thức vào ô trống đầu tiên. Trong trường hợp này, bạn chỉ cần nhập N/A rồi nhấn CTRL + Enter để điều tương tự áp dụng cho mọi ô trống được tìm thấy

Nếu muốn, thay vì 'Không áp dụng', bạn có thể nhập công thức vào ô trống đầu tiên (hoặc nhấp vào giá trị trước đó để sử dụng công thức từ ô ngay phía trên ô trống)

Phương pháp này hoạt động tốt nếu bạn muốn điền vào tất cả các khoảng trống trong trang tính của mình bằng cùng một chuỗi, nhưng nếu bạn hoàn toàn không muốn các khoảng trống thì sao?

4. Điền vào Macro giá trị trước đó

Thủ thuật cuối cùng đó thực sự mất vài bước. Bạn cần nhấp vào một loạt các mục trong menu—và việc giảm số nhấp chuột là điều trở nên hiệu quả hơn, phải không?

Vì vậy, hãy thực hiện thủ thuật cuối cùng đó thêm một bước nữa. Hãy tự động hóa nó bằng một macro. Về cơ bản, macro sau đây sẽ tìm kiếm trong một cột và kiểm tra ô trống. Nếu nó trống, nó sẽ sao chép giá trị hoặc công thức từ ô phía trên nó. Đây là cách bạn có thể sử dụng macro để hoàn thành công việc của mình một cách dễ dàng

Để tạo macro, bấm vào mục menu Nhà phát triển, sau đó bấm vào biểu tượng Macro. Nếu bạn đang sử dụng phiên bản Microsoft Excel mới nhất, tùy chọn Macro sẽ nằm trong tab Dạng xem

Đặt tên cho macro rồi nhấp vào nút Tạo Macro. Thao tác này sẽ mở cửa sổ soạn thảo mã. Dán đoạn mã sau vào chức năng mới

FirstColumn = InputBox("Please enter the column letter.")

FirstRow = InputBox("Please enter the row number.")

LastRow = Range(FirstColumn & "65536").End(xlUp).Row


    

For i = FirstRow To LastRow

If Range(FirstColumn & i).Value = "" Then

Range(FirstColumn & (i - 1)).Copy Range(FirstColumn & i)

End If

Next i

Cách tiếp cận trong đoạn script trên là làm cho nó linh hoạt, vì vậy nó sẽ cho phép người dùng của trang tính chỉ định cột và hàng nào sẽ bắt đầu. Vì vậy, bây giờ, khi bạn có một trang tính trông như thế này

Sau khi bạn điền vào ô trống cùng công thức với ô phía trên, bạn có thể chạy macro để điền vào các khoảng trống trong cột G

Sau khi bạn trả lời các lời nhắc cho cột và hàng bắt đầu, nó sẽ điền vào tất cả các khoảng trống trong cột đó mà không chạm vào dữ liệu hiện có

Về cơ bản, nó tự động điền vào cột trong khi để nguyên dữ liệu hiện có. Điều này không dễ thực hiện bằng cách chỉ kéo chuột xuống cột, nhưng sử dụng phương pháp dựa trên trình đơn được mô tả ở trên hoặc phương pháp vĩ mô được nêu trong phần này, có thể thực hiện được

5. Macro tính toán lặp đi lặp lại

Tính toán lặp lại dựa trên kết quả của các hàng trước đó. Ví dụ: lợi nhuận công ty tháng sau có thể phụ thuộc vào lợi nhuận tháng trước

Trong trường hợp đó, bạn phải bao gồm giá trị của ô trước đó trong phép tính kết hợp dữ liệu từ khắp trang tính hoặc sổ làm việc. Hoàn thành điều này có nghĩa là bạn không thể chỉ sao chép và dán ô mà thay vào đó, hãy thực hiện phép tính dựa trên kết quả thực tế bên trong ô

Hãy sửa đổi macro trước đó để thực hiện phép tính mới dựa trên kết quả từ ô trước đó

FirstColumn = InputBox("Please enter the column letter.")

FirstRow = InputBox("Please enter the first row number.")

LastRow = InputBox("Please enter the last row number.")

For i = FirstRow To LastRow

Range(FirstColumn & i).Value = 5000 + (Range(FirstColumn & (i - 1)).Value * 0.1)

Next i

Hãy nhớ rằng đây chỉ là một cách tiếp cận thay thế cho phép tính lặp. Bạn có thể làm điều tương tự bằng cách nhập công thức trực tiếp vào ô trống tiếp theo và bao gồm cả ô trước đó trong công thức. Sau đó, khi bạn tự động điền vào cột đó, nó sẽ kết hợp giá trị trước đó theo cách tương tự

Nếu chưa từng sử dụng macro, bạn có thể tìm hiểu thêm các lỗi cần tránh khi tạo macro Excel tại đây

Tự động điền các cột Excel thật dễ dàng

Như bạn có thể thấy, phương pháp bạn chọn khi cột tự động điền thực sự có thể giảm khối lượng công việc của bạn. Sau khi thực hành, bạn sẽ có thể dễ dàng điền vào các công thức trong Excel

Bạn cũng có thể làm quen với nhiều công thức và hàm Excel để nhanh chóng trở thành bậc thầy về Excel và tăng năng suất khi làm việc với nhiều trang tính