Mục lục
Sử dụng Excel Tìm trong một dải ô
Công cụ tìm kiếm có thể giúp bạn tìm thông tin nhất định trong trang tính của mình. Để thực hiện việc này, hãy chọn một dải ô để xem trong
Chuyển đến tab trang chủ > Nhóm chỉnh sửa > nhấp vào Tìm & Chọn > nhấp vào Tìm. Ngoài ra, bạn có thể nhấn phím tắt CTRL + F
Trong hộp Tìm gì, nhập các ký tự bạn muốn tìm và bấm Tìm tất cả hoặc Tìm tiếp
Tìm tất cả sẽ mở tất cả các lần xuất hiện của ký tự đã nhập và bạn có thể điều hướng đến ô tương ứng bằng cách nhấp vào bất kỳ mục nào trong danh sách
Tìm Tiếp theo khi bạn nhấp vào tùy chọn này, Excel sẽ chọn lần xuất hiện đầu tiên của giá trị tìm kiếm trên trang tính và khi bạn nhấp lại, nó sẽ chọn lần xuất hiện thứ hai trên ô. Điều này tiếp tục cho đến khi mục cuối cùng được tìm kiếm
Sử dụng công cụ Tìm và Thay thế
Tìm và thay thế sẽ thay đổi giá trị của ô này sang ô khác trong một phạm vi ô trong trang tính. Tab thay thế có thể thay đổi ký tự, văn bản và số trong ô excel
bước
1. Chọn phạm vi ô mà bạn muốn thay thế văn bản hoặc số.
2. Chuyển đến Menu chính > nền chỉnh sửa > chọn Tìm & Chọn > Nhấp vào Thay thế hoặc nhấn CTRL+H từ bàn phím
3. Trên Tìm hộp nhập văn bản hoặc giá trị bạn muốn tìm kiếm. Trong hộp Thay thế bằng, hãy nhập văn bản hoặc giá trị bạn muốn thay thế bằng.
4. Nhấp vào nút Thay thế để thay thế một văn bản hoặc nhấp vào Thay thế Tất cả để thay thế toàn bộ trang tính bằng giá trị hoặc văn bản đó.
Cách tìm và thay thế nhiều giá trị cùng lúc bằng mã VBA
Tìm và Thay thế có thể được sử dụng để tìm nhiều giá trị và thay thế chúng bằng các giá trị bạn muốn bằng mã VBA của Excel
bước
1. Tạo điều kiện mà bạn cần sử dụng. Điều này nên được tạo thành từ một danh sách các giá trị cũ và giá trị thay thế.
2. Nhấp vào tab Nhà phát triển rồi chọn Visual basic trong nhóm mã hoặc nhấn giữ phím chức năng ALT+F11 để mở cửa sổ Visual Basic.
3. Nhấp vào Chèn > mô-đun và dán đoạn mã sau vào cửa sổ mô-đun.
Sub MultiFindNReplace()
'Cập nhật 20140722
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Đặt InputRng = Ứng dụng. Lựa chọn
Đặt InputRng = Ứng dụng. InputBox("Phạm vi gốc ", xTitleId, InputRng. Địa chỉ, Loại. =8)
Đặt ReplaceRng = Ứng dụng. InputBox("Thay thế phạm vi. ", xTitleId, Loại. =8)
Đăng kí. Đang cập nhật màn hình = Sai
Đối với mỗi Rng trong ReplaceRng. Cột(1). tế bào
Đầu vàoRng. Thay cái gì. =Rng. Giá trị, thay thế. =Rng. Độ lệch (0, 1). Giá trị
Tiếp theo
Đăng kí. Đang cập nhật màn hình = True
kết thúc phụ
4. Nhấp vào Chạy hoặc nhấn Phím F5 để chạy mã này. Chỉ định phạm vi dữ liệu trong cửa sổ bật lên.
5. Nhấp OK và một hộp thoại nhắc khác sẽ xuất hiện để bạn chọn các tiêu chí bạn đã tạo ở bước 1.
6. Sau đó nhấp vào ok. Từ ảnh chụp màn hình bên dưới, bạn có thể thấy tất cả các giá trị đã được thay thế bằng các giá trị mới.
Sử dụng Hàm THAY THẾ trong Excel
Hàm REPLACE trong Excel cho phép bạn tìm một số ký tự nhất định hoặc một ký tự đơn trong chuỗi văn bản và thay đổi nó bằng một bộ ký tự khác
cú pháp
THAY THẾ (old_text, start_num, num_chars, new_text)
đối số chức năng
Old_text – văn bản gốc (hoặc tham chiếu đến một ô có văn bản gốc) mà bạn muốn thay thế một số ký tự trong đó
Start_num – vị trí của ký tự đầu tiên trong old_text mà bạn muốn thay thế
Num_chars – số ký tự bạn muốn thay thế
New_text – văn bản thay thế
Ví dụ. Chúng tôi muốn thay vị trí đầu bếp ở ô B22 thành phụ bếp
bấm ok
Áp dụng công thức thay thế lồng nhau để tìm và thay thế
Hàm SUBSTITUTE thay thế văn bản hiện có bằng văn bản mới trong chuỗi văn bản. Chúng ta có thể lồng hàm SUBSTITUTE cho nhiều giá trị được thay thế
Ví dụ
1. Hãy xem xét dữ liệu trong các bảng bên dưới. Cột L có một số dữ liệu văn bản ngẫu nhiên. Bảng bên phải biểu thị giá trị phải được thay thế bằng giá trị mới.
2. Trong ô xuất đầu tiên, Ô M5, công thức liên quan sẽ là.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(L5. L10,O5,P5),O6,P6),O7,P7)
3. Nhấn Enter và bạn sẽ nhận được một mảng với các giá trị văn bản mới cùng một lúc. Chúng tôi đã sử dụng công thức SUBSTITUTE ba lần vì chúng tôi phải thay thế ba giá trị khác nhau trong bảng bên phải
Sử dụng hàm XLOOKUP để tìm kiếm và thay thế
Hàm XLOOKUP tìm kiếm một phạm vi cho phù hợp và trả về mục tương ứng trong phạm vi thứ hai
Ví dụ
Cột văn bản cũ chứa một số giá trị văn bản . Bảng thứ hai bên phải thể hiện dữ liệu được thay thế đồng thời.
Công thức bắt buộc với hàm XLOOKUP trong ô đầu ra đầu tiên, M5 phải là
=XLOOKUP($L10,$O$5. $O$10,$P$5. $P$10,$L10)
Sau khi nhấn Enter và tự động điền toàn bộ cột sẽ được hiển thị với dữ liệu đúng cách
Để Tìm và Thay thế Định dạng trong Excel
Đây là một tính năng tuyệt vời khi bạn muốn thay thế định dạng hiện có bằng một số định dạng khác. Ví dụ: bạn có thể muốn thay thế các định dạng như màu nền, đường viền, loại/kích thước/màu phông chữ và thậm chí hợp nhất các ô, bạn có thể sử dụng Tìm và Thay thế để thực hiện việc đó
Bậc thang;
1. Chọn các ô hoặc thậm chí toàn bộ trang tính mà bạn muốn thay thế định dạng
2. Đi tới Trang chủ-> Tìm và chọn -> Thay thế (điều khiển phím tắt + H)
3. Nhấp vào nút Tùy chọn
4. Sau đó, nhấp vào nút Tìm định dạng và trình đơn thả xuống có hai tùy chọn sẽ hiển thị Định dạng và Chọn định dạng từ ô.
5. Bạn có thể chỉ định định dạng theo cách thủ công bằng cách nhấp vào nút Định dạng hoặc bạn có thể chọn định dạng từ một ô trong trang tính. Để chọn định dạng từ ô, chọn tùy chọn Chọn định dạng từ ô rồi bấm vào ô mà bạn muốn chọn định dạng.
6. Khi định dạng được chọn theo cách thủ công từ hộp thoại hoặc từ ô, bạn sẽ thấy bản xem trước ở bên trái của nút Định dạng.
7. Sau đó, bạn cần chỉ định định dạng mà bạn muốn khác với định dạng đã chọn ở bước 6. Nhấp vào nút Thay thế bằng Định dạng, trình đơn thả xuống với hai tùy chọn sẽ hiển thị;
8. Bạn có thể chỉ định thủ công hoặc chọn một định dạng hiện có. Khi một định dạng được chọn, bạn sẽ thấy định dạng đó dưới dạng Bản xem trước ở bên trái của nút định dạng
9. Nhấp vào nút Thay thế tất cả.
Kết hợp các hàm IFNA và VLOOKUP để tìm và thay thế nhiều giá trị
Hàm VLOOKUP (Vertical LOOKUP) là hàm thay thế cho hàm XLOOKUP
Nó xác định các giá trị ở cột ngoài cùng bên trái trong tập dữ liệu và trả về giá trị trong cùng một hàng từ một cột cụ thể. Nó sẽ trả về lỗi N/A nếu không tìm thấy giá trị tra cứu. Do đó, để tránh lỗi như vậy, bạn có thể đưa hàm IFNA sửa tất cả các lỗi vào Hàm VLOOKUP
bước
1. Chọn ô để nhập công thức, chẳng hạn như ô C5, và kết hợp các hàm IFNA và VLOOKUP như bên dưới;
=IFNA(VLOOKUP($B5,$E$5. $F$10,2,SAI),B5)
2. Nhấn nút Enter và bạn sẽ thấy kết quả hiển thị trong ô C5.
3. Bây giờ, bạn có thể sử dụng biểu tượng Fill Handle (+) để kéo công thức xuống cột.
Sử dụng hàm LAMBDA để thay thế nhiều lần
Những người sử dụng Excel 365 có thể hưởng lợi từ hàm LAMBDA (ngôn ngữ công thức truyền thống) đã được bật sẵn để tìm và thay thế nhiều giá trị. Phương pháp này cho phép họ chuyển đổi một công thức rất dài và phức tạp thành một công thức đơn giản và cô đọng. Nó cũng tạo ra các chức năng mới không tồn tại trong Excel, giống như mã VBA. Nhược điểm duy nhất là nó chỉ khả dụng trong Excel 365 và bạn không thể sử dụng nó trong các trang tính khác nhau
Ví dụ: nếu bạn muốn tìm và thay thế nhiều từ, bạn có thể tạo một hàm LAMBDA tùy chỉnh và đặt tên là MultiReplace. Tên mới có thể lấy hai công thức như hình bên dưới
=LAMBDA(văn bản, cũ, mới, IF(cũ"", MultiReplace(SUBSTITUTE(văn bản, cũ, mới), OFFSET(cũ, 1, 0), OFFSET(mới, 1, 0)), văn bản))
Hoặc
=LAMBDA(văn bản, cũ, mới, IF(cũ="", văn bản, MultiReplace(SUBSTITUTE(văn bản, cũ, mới), OFFSET(cũ, 1, 0), OFFSET(mới, 1, 0))))
Mặc dù cả hai công thức có thể trông giống nhau (hàm đệ quy), nhưng bạn có thể lưu ý sự khác biệt bằng cách sử dụng các điểm thoát của chúng. Chẳng hạn, hàm IF trong công thức đầu tiên kiểm tra xem văn bản cũ có trống không (old””). Nếu nó trống (TRUE), hàm MultiReplace được gọi. Nếu nó không trống (FALSE), hàm sẽ đưa ra văn bản ở dạng hiện tại và thoát
Mặt khác, hàm IF trong công thức thứ hai sử dụng logic đảo ngược. nếu cũ trống (old=""), thì nó trả về một văn bản và thoát. Nhưng nếu cái cũ không trống, hãy gọi MultiReplace. Do đó, để sử dụng bất kỳ công thức nào, trước tiên bạn cần đặt tên cho hàm MultiReplace trong Trình quản lý tên. Sau đó, khi bạn nhận được tên, bây giờ bạn có thể tiếp tục sử dụng nó để tìm và thay thế nhiều giá trị như được thảo luận bên dưới
1. Đầu tiên, hãy biết cú pháp của hàm MultiReplace là.
MultiReplace(văn bản, cũ, mới), ở đâu;
văn bản là dữ liệu nguồn
old là giá trị bạn cần tìm
mới là những giá trị bạn cần thay thế bằng
2. Nhập công thức vào ô (cột) bên cạnh dữ liệu bạn muốn tìm và thay thế. Ví dụ: nếu bạn muốn thay thế các giá trị trong cột A, bạn có thể nhập công thức sau vào cột B, chẳng hạn như ô B2.
=MultiReplace(A2. A10, D2, E2)
3. Nhấn nút Enter và một văn bản mới sẽ xuất hiện trong ô B2.
4. Tiếp tục kéo công thức xuống cột bằng cách sử dụng Bộ điều khiển tự động điền.
Tìm và thay thế hàng loạt bằng UDF
Bạn cũng có thể sử dụng hàm do người dùng xác định (UDF) để tìm hàng loạt và thay thế nhiều giá trị trong Excel bằng mã VBA truyền thống. UDF gần với hàm do LAMBDA xác định, nhưng bạn có thể phân biệt chúng bằng cách đặt tên cho kỹ thuật UDF là MassReplace. Nó được đại diện bởi mã
Hàm MassReplace(InputRng As Range, FindRng As Range, ReplaceRng As Range) As Variant()
Mảng Dim arRes() As Variant' để lưu kết quả
Mảng Dim arSearchReplace(), sTmp As String 'nơi chứa các cặp tìm/thay thế, chuỗi tạm thời
Dim iFindCurRow, cntFindRows As Long 'chỉ mục của hàng hiện tại của mảng SearchReplace, số lượng hàng
Dim iInputCurRow, iInputCurCol, cntInputRows, cntInputCols As Long 'chỉ mục của hàng hiện tại trong phạm vi nguồn, chỉ mục của cột hiện tại trong phạm vi nguồn, số lượng hàng, số lượng cột
cntInputRows = InputRng. hàng. Đếm
cntInputCols = InputRng. Cột. Đếm
cntFindRows = FindRng. hàng. Đếm
ReDim arRes(1 Đến cntInputRows, 1 Đến cntInputCols)
ReDim arSearchReplace(1 To cntFindRows, 1 To 2) 'chuẩn bị mảng các cặp tìm/thay thế
Đối với iFindCurRow = 1 Đến cntFindRows
arSearchReplace(iFindCurRow, 1) = FindRng. Ô(iFindCurRow, 1). Giá trị
arSearchReplace(iFindCurRow, 2) = ReplaceRng. Ô(iFindCurRow, 1). Giá trị
Tiếp theo
'Tìm kiếm và thay thế trong phạm vi nguồn
Đối với iInputCurRow = 1 Đến cntInputRows
Đối với iInputCurCol = 1 Đến cntInputCols
sTmp = InputRng. Các ô (iInputCurRow, iInputCurCol). Giá trị
'Thay thế tất cả các cặp tìm/thay thế trong mỗi ô
Đối với iFindCurRow = 1 Đến cntFindRows
sTmp = Thay thế(sMtp, arSearch Thay thế(iFindCurRow, 1), arSearch Thay thế(iFindCurRow, 2))
Tiếp theo
arRes(iInputCurRow, iInputCurCol) = sTmp
Tiếp theo
Tiếp theo
MassReplace = arRes
chức năng kết thúc
Hàm UDF hoặc MassReplace chỉ hoạt động trong sổ làm việc mà bạn đã chèn mã. Do đó, để áp dụng nó, bạn có thể sử dụng các bước tương tự như chạy mã VBA. Khi bạn chèn mã, công thức sau sẽ xuất hiện trong công thức intellisense
MassReplace(phạm vi đầu vào, phạm vi tìm kiếm, phạm vi thay thế), ở đâu;
Input_range là dải nguồn bạn muốn thay thế các giá trị
Find_range là toàn bộ chuỗi bao gồm chủ ngữ và từ cần kiểm tra và tìm kiếm
Replace_range là các ký tự, chuỗi hoặc văn bản mới cần thay thế bằng
bước
1. Khi sử dụng Excel 365 hoặc các phiên bản có hỗ trợ mảng động, bạn có thể nhập công thức sau vào ô bên cạnh dữ liệu muốn tìm và thay thế. Ví dụ: bạn có thể chọn ô B2 và viết công thức này.
=MassReplace(A2. A10,D2. D4,E2. E 4)
2. Nhấn nút Enter để hiển thị văn bản hoặc ký tự mới trong ô B2.
3. Bây giờ, bạn có thể sử dụng Bộ điều khiển Tự động điền để kéo công thức xuống cột.
4. Khi sử dụng các phiên bản Excel cũ không hỗ trợ mảng động, bạn có thể chọn thủ công toàn bộ phạm vi ô, chẳng hạn như B2. B10 và nhấn đồng thời các nút Ctrl + Shift + Enter.
Nhiều Tìm và Thay thế bằng Công cụ Chuỗi con
Sử dụng công cụ chuỗi con cũng là một trong những phương pháp dễ dàng nhất để tìm và thay thế nhiều giá trị trong Excel. Tuy nhiên, trước khi thay thế các giá trị, bạn nên cân nhắc xem bạn có đang xử lý hộp Phân biệt chữ hoa chữ thường hay không. Chẳng hạn, bạn nên quyết định xem bạn có muốn coi chữ hoa và chữ thường là các ký tự khác nhau không