Sao chép dữ liệu Bộ lọc Excel

Thông thường, chúng ta có thể áp dụng nhanh tính năng Advanced Filter để trích xuất dữ liệu từ dữ liệu thô trong cùng một bảng tính. Tuy nhiên, đôi khi, khi bạn cố gắng sao chép kết quả đã lọc sang một trang tính khác, bạn sẽ nhận được thông báo cảnh báo sau. Trong trường hợp này, bạn có thể xử lý tác vụ này trong Excel như thế nào?

Sao chép dữ liệu sang một trang tính khác với Bộ lọc nâng cao bằng cách sử dụng tính năng Bộ lọc nâng cao

Đây là một cách giải quyết đơn giản để bạn giải quyết công việc này, vui lòng làm như thế này

1. Kích hoạt trang tính mà bạn muốn sao chép kết quả đã lọc bằng cách sử dụng chức năng Bộ lọc nâng cao

2. Sau đó nhấp vào Dữ liệu > Nâng cao, xem ảnh chụp màn hình

3. Trong hộp thoại Bộ lọc nâng cao, chọn Sao chép sang vị trí khác từ phần Hành động, sau đó nhấp vào nút

4. Sau đó nhấp vào nút

Sao chép dữ liệu sang trang tính khác bằng Bộ lọc nâng cao bằng cách sử dụng mã VBA

Nếu bạn thành thạo mã VBA, bạn có thể hoàn thành công việc này với mã VBA sau

1. Kích hoạt trang tính mà bạn muốn lọc và sao chép kết quả sang trang tính khác

2. Giữ phím ALT + F11 trong Excel và nó sẽ mở cửa sổ Microsoft Visual Basic for Applications

3. Nhấp vào Chèn > Mô-đun và dán đoạn mã sau vào Cửa sổ Mô-đun

mã VBA. Sao chép dữ liệu sang trang tính khác bằng Bộ lọc nâng cao

Sub Advancedfiltertoanothersheet()
'Updateby Extendoffice
    Dim xStr As String
    Dim xAddress As String
    Dim xRg As Range
    Dim xCRg As Range
    Dim xSRg As Range
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the filter range:", "Kutools for Excel", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xCRg = Application.InputBox("Please select the criteria range:", "Kutools for Excel", "", , , , , 8)
    If xCRg Is Nothing Then Exit Sub
    Set xSRg = Application.InputBox("Please select the output range:", "Kutools for Excel", "", , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xRg.AdvancedFilter xlFilterCopy, xCRg, xSRg, False
    xSRg.Worksheet.Activate
    xSRg.Worksheet.Columns.AutoFit
End Sub

4. Và sau đó nhấn phím F5 để chạy mã này và một hộp nhắc xuất hiện để cho phép bạn chọn phạm vi dữ liệu mà bạn muốn lọc, xem ảnh chụp màn hình

5. Sau đó nhấp vào OK và một hộp thoại khác sẽ xuất hiện để nhắc bạn chọn phạm vi tiêu chí mà bạn muốn lọc dựa trên đó, xem ảnh chụp màn hình

6. Tiếp tục nhấp vào OK, trong hộp thoại bật ra, vui lòng chọn một ô từ một trang tính mới khác mà bạn muốn xuất kết quả bộ lọc, xem ảnh chụp màn hình

7. Cuối cùng, bấm nút OK, dữ liệu được lọc đã được xuất sang một trang tính cụ thể khác theo nhu cầu của bạn

Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách sao chép dữ liệu từ tập dữ liệu đã lọc và cách dán vào cột đã lọc trong khi bỏ qua các ô ẩn

Mục lục

Sao chép từ một cột đã lọc Bỏ qua các ô ẩn

Giả sử bạn có tập dữ liệu dưới đây

Đưa ra bảng trên, giả sử bạn chỉ muốn sao chép tất cả các hàng của nhân viên từ bộ phận CNTT

Đối với điều này, bạn có thể áp dụng bộ lọc cho bảng của mình như sau

1. Chọn toàn bộ bảng
2. Từ tab Dữ liệu, chọn nút 'Bộ lọc' trong nhóm 'Sắp xếp & Lọc'
3. Bạn sẽ thấy các mũi tên nhỏ trên mỗi ô của hàng tiêu đề. Chúng nhằm giúp bạn lọc các ô của mình. Bạn có thể nhấp vào bất kỳ mũi tên nào để chọn bộ lọc cho cột tương ứng
4. Trong ví dụ này, chúng tôi chỉ muốn lọc ra những hàng có chứa Khoa “CNTT”. Vì vậy, hãy chọn mũi tên bên cạnh tiêu đề Bộ phận và bỏ chọn các hộp bên cạnh tất cả các bộ phận, ngoại trừ “CNTT”. Bạn chỉ cần bỏ chọn “Select All” để nhanh chóng bỏ chọn mọi thứ và sau đó chỉ cần chọn “IT”
5. Nhấp vào OK. Bây giờ bạn sẽ chỉ thấy các hàng có Bộ phận “CNTT”

Bây giờ, việc sao chép từ một bảng đã lọc khá đơn giản. Khi bạn sao chép từ một cột hoặc bảng đã lọc, Excel sẽ tự động sao chép chỉ những hàng hiển thị

Vì vậy, tất cả những gì bạn cần làm là

1. Chọn các hàng hiển thị mà bạn muốn sao chép
2. Nhấn CTRL+C hoặc nhấp chuột phải->Sao chép để sao chép các hàng đã chọn này
3. Chọn ô đầu tiên mà bạn muốn dán các ô đã sao chép
4. Nhấn CTRL+V hoặc nhấp chuột phải->Dán để dán các ô

Điều này sẽ chỉ khiến các hàng hiển thị từ bảng đã lọc được dán

Đôi khi, bạn có thể gặp sự cố khi sao chép các hàng hiển thị, đặc biệt là khi làm việc với Tổng phụ hoặc các tính năng tương tự

Trong những trường hợp như vậy, chỉ sao chép các hàng hiển thị cũng khá dễ dàng. Đây là những gì bạn cần làm

1. Chọn các hàng hiển thị mà bạn muốn sao chép
2. Nhấn ALT+; . Nếu bạn đang sử dụng máy Mac, hãy nhấn Cmd+Shift+Z. Phím tắt này cho phép bạn chỉ chọn các hàng hiển thị, trong khi bỏ qua các ô ẩn
3. Nhấn CTRL+C hoặc nhấp chuột phải->Sao chép để sao chép các hàng đã chọn này
4. Chọn ô đầu tiên mà bạn muốn dán các ô đã sao chép
5. Nhấn CTRL+V hoặc nhấp chuột phải->Dán để dán các ô

Vì vậy, bạn thấy việc sao chép từ các cột đã lọc khá đơn giản

Nhưng bạn không thể nói như vậy khi dán vào cột đã lọc

Dán một giá trị ô đơn vào tất cả các hàng hiển thị của cột được lọc

Khi dán vào cột đã lọc có thể xảy ra 2 trường hợp

• Bạn có thể muốn dán một giá trị vào tất cả các ô hiển thị của cột đã lọc
• Bạn có thể muốn dán một tập hợp các giá trị vào các ô hiển thị của cột đã lọc

Đối với trường hợp đầu tiên, việc dán vào cột đã lọc khá dễ dàng

Giả sử chúng ta muốn thay thế tất cả các ô có Department = “IT” bằng dạng đầy đủ. "Công nghệ thông tin"

Đối với điều này, bạn có thể nhập từ “Công nghệ thông tin” vào bất kỳ ô trống nào, sao chép và dán từ đó vào các ô hiển thị của Cột “Bộ phận” đã lọc. Dưới đây là hướng dẫn từng bước về cách thực hiện việc này

1. Chọn một ô trống và gõ từ “Công nghệ thông tin”
2. Sao chép nó bằng cách nhấn CTRL + C hoặc Nhấp chuột phải-> Sao chép
3. Chọn tất cả các ô hiển thị trong cột có tiêu đề “Bộ phận”
4. Dán giá trị đã sao chép bằng cách nhấn CTRL+V hoặc Nhấp chuột phải->Dán

Bạn sẽ tìm thấy giá trị “Công nghệ thông tin” được dán vào chỉ các ô hiển thị của cột “Bộ phận”

Để xác minh điều này, hãy xóa bộ lọc bằng cách chọn Dữ liệu-> Bộ lọc. Lưu ý rằng tất cả các ô khác của cột “Bộ phận” không thay đổi

Hai cách để dán một tập hợp các giá trị vào các hàng hiển thị của một cột được lọc

Bây giờ, hãy xem điều gì sẽ xảy ra khi bạn muốn dán một tập hợp các giá trị vào các ô hiển thị của một cột đã lọc. Giả sử bạn muốn dán danh sách tiền lương chỉ cho các hàng chứa Bộ phận =”Công nghệ thông tin”

Nếu bạn thử sao chép các ô này và dán chúng vào cột Lương đã lọc, bạn có thể sẽ nhận được thông báo lỗi như “Không thể sử dụng lệnh trên nhiều lựa chọn”

Điều này là do bạn không thể dán vào các ô trong phạm vi có chứa các hàng hoặc cột bị ẩn. Đó là một trong những hạn chế của Excel. Không có cách nào khác, nhưng có một số thủ thuật mà bạn có thể sử dụng để thực hiện việc này

Dưới đây là hai thủ thuật mà bạn có thể sử dụng để dán một tập hợp các giá trị vào một cột đã lọc, bỏ qua các ô ẩn

Dán Tập hợp Giá trị vào Hàng Hiển thị của Cột được Lọc – Sử dụng Công thức

Trong phương pháp này, chúng tôi sử dụng một công thức để chỉ cần sao chép giá trị của ô sang ô đích

Đối với ví dụ trên (nơi bạn muốn sao chép một tập hợp các giá trị lương vào chỉ các hàng có Bộ phận= “Công nghệ thông tin”), đây là các bước bạn cần thực hiện

1. Nhấn dấu bằng (‘=’) trong ô đầu tiên của cột bạn muốn dán vào (G3)
2. Bây giờ hãy chọn ô đầu tiên từ danh sách bạn muốn sao chép (H3 trong ví dụ của chúng tôi)
3. Điều này sẽ chỉ tạo một tham chiếu đến ô. Bạn nên xem công thức. =H3 trong ô G3
   
4. Sao chép công thức này xuống bằng cách kéo thanh điều khiển điền xuống (ở góc dưới cùng bên phải của ô G3). Điều này sẽ chỉ dán công thức vào các ô hiển thị của cột G
5. Để xác minh điều này, hãy xóa bộ lọc bằng cách chọn Dữ liệu->Bộ lọc. Đây là hình ảnh của cột G không có bộ lọc sau thao tác sao chép-dán. Để bạn thấy rõ hơn, tôi đã đánh dấu các ô đã sao chép bằng màu xanh lục nhạt
   
6. Bây giờ những gì bạn đã sao chép chỉ là tham chiếu đến các ô ban đầu. Vì vậy, nếu bạn cố xóa các ô ban đầu sau khi sao chép xong, các giá trị được sao chép cũng sẽ biến mất khỏi cột G
7. Để tránh điều này, bạn cần dán các kết quả công thức này làm giá trị. Điều này khá dễ dàng. Khi bạn đang ở chế độ chưa được lọc, hãy sao chép tất cả các ô của cột G, nhấp chuột phải và chọn 'Dán giá trị' từ menu bật lên
   
8. Thế là xong, bây giờ bạn có thể tiếp tục và xóa các giá trị ban đầu

Dán Tập hợp Giá trị vào Hàng Hiển thị của Cột được Lọc – Sử dụng VBScript

Đây là một phương pháp khá dễ dàng hơn và nhanh hơn. Tất cả những gì bạn cần làm là sao chép VBScript được cung cấp bên dưới vào cửa sổ nhà phát triển của bạn và chạy nó

Sub paste_to_filtered_col()
Dim s As Range
Dim visible_source_cells As Range
Dim destination_cells As Range
Dim source_cell As Range
Dim dest_cell As Range
Set s = Application.Selection
s.SpecialCells(xlCellTypeVisible).Select
Set visible_source_cells = Application.Selection
Set destination_cells = Application.InputBox("Please select the destination cells:", Type:=8)
For Each source_cell In visible_source_cells
source_cell.Copy
For Each dest_cell In destination_cells
If dest_cell.EntireRow.RowHeight <> 0 Then
dest_cell.PasteSpecial
Set destination_cells = dest_cell.Offset(1).Resize(destination_cells.Rows.Count)
Exit For
End If
Next dest_cell
Next source_cell
End Sub

Thực hiện theo các bước sau để sử dụng mã trên

1. Chọn tất cả các hàng bạn cần lọc (bao gồm cả tiêu đề cột)
2. Từ Ruy-băng Menu Nhà phát triển, chọn Visual Basic
3. Khi cửa sổ VBA của bạn mở ra, hãy nhấp vào Chèn-> Mô-đun và dán đoạn mã trên vào cửa sổ mô-đun

Macro của bạn hiện đã sẵn sàng để chạy. Để chạy mã

1. Đầu tiên, chọn các ô mà bạn muốn sao chép
2. Chạy tập lệnh bằng cách điều hướng đến Nhà phát triển->Macros->paste_to_filtered_col
3. Mã sẽ yêu cầu bạn chọn các ô đích (nơi bạn muốn dán các ô đã sao chép)
4. Chọn các ô và bấm OK
   

Các ô đã chọn của bạn bây giờ sẽ được sao chép và dán vào các ô đích. Bạn có thể tiếp tục và xóa các ô ban đầu nếu muốn

Bạn cũng có thể tạo lối tắt nhỏ (sử dụng Thanh công cụ truy cập nhanh) để chạy macro của mình bất cứ khi nào bạn cần. Đây là cách

1. Nhấp vào mũi tên Tùy chỉnh Thanh công cụ mà bạn sẽ tìm thấy phía trên ruy-băng menu của Excel
2. Chọn 'Lệnh khác' từ menu thả xuống xuất hiện
3. Thao tác này sẽ mở hộp thoại 'Tùy chọn Excel'
4. Nhấp vào danh sách thả xuống bên dưới 'Chọn lệnh từ' và chọn 'Macro'
5. Chọn tên của macro bạn đã tạo. Trong trường hợp của chúng tôi, đó là 'ThisWorkbook. paste_to_filtered_col’ Nhấp vào nút ‘Add>>’
6. Nhấp vào OK

Giờ đây, bạn sẽ nhận được nút macro Truy cập nhanh để nhanh chóng chạy macro của mình chỉ bằng một cú nhấp chuột

Bất cứ khi nào bạn cần sao chép và dán một tập hợp các ô vào một cột đã lọc, chỉ cần chọn các ô nguồn của bạn, nhấp vào nút đã tạo này rồi chọn các ô đích