Lấy danh sách các ô phù hợp (Microsoft Excel)
Khi Michael thực hiện thao tác “Tìm tất cả” trong Excel, chương trình sẽ hiển thị một cách hữu ích danh sách tất cả các ô chứa bất cứ thứ gì anh ta đang tìm kiếm.
Michael muốn sao chép danh sách địa chỉ ô đó vào một trang tính khác, vì vậy anh ấy tự hỏi liệu có cách nào để sao chép danh sách đó vào Clipboard để anh ấy có thể dán nó vào một trang tính hay không.
Có một số cách bạn có thể thực hiện nhiệm vụ này và hầu hết chúng đều liên quan đến việc sử dụng macro. Tuy nhiên, trước khi đến với các phương pháp tiếp cận dựa trên macro, chúng ta hãy xem cách bạn có thể truy cập địa chỉ bằng cách sử dụng các dải ô đã đặt tên và Trình quản lý tên:
-
Sử dụng FindAll như trước đây, nhưng không đóng hộp thoại Tìm và Thay thế.
-
Trong danh sách địa chỉ bạn được hiển thị, cuộn xuống dưới cùng, giữ phím Shift và nhấp vào kết quả phù hợp cuối cùng. Excel chọn tất cả các ô phù hợp.
-
Nhấn Esc để đóng hộp thoại Tìm và Thay thế. Tất cả các ô phù hợp vẫn được chọn.
-
Nhập tên vào Hộp Tên (ngay bên trái thanh Công thức và ngay trên ô A1). Điều này tạo ra một phạm vi được đặt tên bao gồm tất cả các ô đã chọn.
-
Hiển thị tab Công thức của ruy-băng.
-
Nhấp vào công cụ Name Manger. Excel sẽ hiển thị hộp thoại của Trình quản lý tên. (Xem Hình 1.)
-
Nhấp vào tên bạn đã tạo ở bước 4.
-
Danh sách các ô sẽ nằm trong hộp Tham chiếu đến, ở cuối hộp thoại.
Tại thời điểm này, bạn có thể sao chép thông tin trong hộp Tham khảo và dán nó vào bất kỳ thứ gì bạn muốn (bao gồm cả một trang tính khác). Bạn sẽ cần chỉnh sửa dữ liệu một chút sau khi dán, vì danh sách chỉ có vậy – một danh sách nối tiếp các địa chỉ ô.
Rõ ràng, điều này ảnh hưởng đến sổ làm việc của bạn, vì nó tạo ra một phạm vi được đặt tên. Nếu bạn làm điều đó nhiều lần, bạn sẽ có nhiều phạm vi được đặt tên được tạo.
Tất nhiên, điều này có thể nhanh chóng trở nên khó sử dụng nếu bạn cần thực hiện nhiệm vụ khá thường xuyên. Đây là lúc các giải pháp vĩ mô phát huy tác dụng. Sau đây là một ví dụ về macro sẽ tìm kiếm một giá trị cụ thể và sau đó đặt địa chỉ của mọi ô chứa giá trị đó vào một trang tính khác.
Sub CellAdressList() Dim c1 As String Dim nxt As String Sheets("Sheet1").Select Range("A1").Select Cells.Find(What:="qrs", After:=ActiveCell, _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate c1 = ActiveCell.Address Sheets("Sheet2").Select Range("A1").Select Range("A1").Value = c1 Do Until nxt = c1 Sheets("Sheet1").Select Cells.FindNext(After:=ActiveCell).Activate nxt = ActiveCell.Address Sheets("Sheet2").Select ActiveCell.Offset(1, 0).Select ActiveCell.Value = nxt Loop ActiveCell.Value = "" End Sub
Macro đưa ra một vài giả định. Đầu tiên, nó giả định rằng bạn đang tìm kiếm thông tin trên trang tính có tên là Sheet1. Thứ hai, nó giả sử bạn muốn danh sách các địa chỉ được đặt trong trang tính có tên là Sheet2. Cuối cùng, nó giả định rằng bạn đang tìm kiếm giá trị “qrs” trong Sheet1. Tất cả các yếu tố này của macro có thể được thay đổi, nếu muốn.
Đối với một cái gì đó linh hoạt hơn một chút, hãy xem xét macro sau. Nó giả định rằng bạn đã chọn tất cả các ô có chứa giá trị bạn muốn. (Nói cách khác, bạn cần thực hiện các bước từ 1 đến 3 trong số các bước gần phần đầu của mẹo này.) Sau đó, bạn có thể chạy macro.
Sub CopyFindAllSelection() Dim outcell As Range Dim c As Range Set outcell = Range("Sheet2!A1") For Each c In Selection outcell.Value = c.Address Set outcell = outcell.Offset(1, 0) Next End Sub
Kết quả là địa chỉ của các ô đã chọn được đặt vào trang tính Sheet2. Macro này linh hoạt hơn một chút vì nó cho phép bạn tìm thấy bất kỳ thứ gì trong bất kỳ trang tính nào. Phần duy nhất “được mã hóa cứng”
là trang tính (Sheet2) mà các địa chỉ được đặt vào.
_Lưu ý: _
Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.
ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.
Mẹo này (13581) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.