Giả sử bạn có một danh sách trong đó, với các mức độ "đơn giản" khác nhau, dữ liệu ban đầu được ghi - ví dụ: địa chỉ hoặc tên công ty:
 |  |
Rõ ràng là cùng một thành phố hoặc công ty có mặt ở đây trong các biến thể motley, điều này rõ ràng sẽ tạo ra rất nhiều vấn đề khi làm việc với các bảng này trong tương lai. Và nếu bạn suy nghĩ một chút, bạn có thể tìm thấy rất nhiều ví dụ về các nhiệm vụ tương tự từ các lĩnh vực khác.
Bây giờ, hãy tưởng tượng rằng dữ liệu quanh co như vậy đến với bạn thường xuyên, tức là đây không phải là câu chuyện “sửa thủ công, quên nó đi” một lần, mà là một vấn đề thường xuyên và trong một số lượng lớn các ô.
Để làm gì? Không thay thế thủ công văn bản bị cong 100500 lần bằng văn bản đúng thông qua hộp “Tìm và Thay thế” hoặc bằng cách nhấp vào Ctrl+H?
Điều đầu tiên nghĩ đến trong tình huống như vậy là thực hiện thay thế hàng loạt theo một cuốn sách tham khảo được biên soạn trước để so khớp các phương án đúng và sai - như thế này:
Thật không may, với sự phổ biến rõ ràng của một nhiệm vụ như vậy, Microsoft Excel không có các phương pháp tích hợp đơn giản để giải quyết nó. Để bắt đầu, hãy tìm cách thực hiện điều này với các công thức mà không liên quan đến "pháo hạng nặng" dưới dạng macro trong VBA hoặc Power Query.
Trường hợp 1. Thay thế đầy đủ số lượng lớn
Hãy bắt đầu với một trường hợp tương đối đơn giản - tình huống mà bạn cần thay thế văn bản cũ bị cong bằng văn bản mới. đầy đủ.
Giả sử chúng ta có hai bảng:
Trong phần đầu tiên - các tên khác nhau ban đầu của các công ty. Trong thứ hai - một cuốn sách tham khảo về thư từ. Nếu chúng tôi tìm thấy trong tên của công ty trong bảng đầu tiên bất kỳ từ nào trong cột Để tìm, sau đó bạn cần phải thay thế hoàn toàn tên quanh co này bằng một tên chính xác - từ cột Thay thế bảng tra cứu thứ hai.
Cho thuận tiện:
- Cả hai bảng đều được chuyển đổi thành động (“thông minh”) bằng phím tắt Ctrl+T hoặc đội Chèn - Bảng (Chèn - Bảng).
- Trên tab xuất hiện xây dựng (Thiết kế) bảng đầu tiên có tên Ngàyvà bảng tham chiếu thứ hai - Thay thế.
Để giải thích logic của công thức, chúng ta hãy đi từ xa một chút.
Lấy công ty đầu tiên từ ô A2 làm ví dụ và tạm thời quên đi các công ty còn lại, hãy thử xác định tùy chọn nào từ cột Để tìm gặp nhau ở đó. Để thực hiện việc này, hãy chọn bất kỳ ô trống nào trong phần trống của trang tính và nhập hàm vào đó ĐỂ TÌM (TÌM THẤY):
Hàm này xác định xem chuỗi con đã cho có được bao gồm hay không (đối số đầu tiên là tất cả các giá trị từ cột Để tìm) vào văn bản nguồn (công ty đầu tiên từ bảng dữ liệu) và phải xuất ra số thứ tự của ký tự mà từ đó văn bản được tìm thấy hoặc lỗi nếu không tìm thấy chuỗi con.
Bí quyết ở đây là vì chúng ta đã chỉ định không phải một mà là một số giá trị làm đối số đầu tiên, nên hàm này cũng sẽ trả về kết quả không phải là một giá trị mà là một mảng gồm 3 phần tử. Nếu bạn không có phiên bản Office 365 mới nhất hỗ trợ mảng động, thì sau khi nhập công thức này và nhấp vào đăng ký hạng mục thi bạn sẽ thấy mảng này ngay trên trang tính:
Nếu bạn có các phiên bản Excel trước, thì sau khi nhấp vào đăng ký hạng mục thi chúng ta sẽ chỉ thấy giá trị đầu tiên từ mảng kết quả, tức là lỗi #VALUE! (#GIÁ TRỊ!).
Bạn không nên lo lắng 🙂 Trên thực tế, công thức của chúng tôi hoạt động và bạn vẫn có thể xem toàn bộ mảng kết quả nếu bạn chọn hàm đã nhập trong thanh công thức và nhấn phím F9(chỉ cần đừng quên nhấn Escđể quay lại công thức):
Dãy kết quả thu được có nghĩa là trong tên công ty bị cắt xén ban đầu (GK Morozko OAO) của tất cả các giá trị trong một cột Để tìm chỉ tìm thấy thứ hai (Morozko)và bắt đầu từ ký tự thứ 4 liên tiếp.
Bây giờ hãy thêm một hàm vào công thức của chúng ta XEM(TRA CỨU):
Hàm này có ba đối số:
- Giá trị mong muốn - bạn có thể sử dụng bất kỳ số lượng đủ lớn nào (điều chính là nó vượt quá độ dài của bất kỳ văn bản nào trong dữ liệu nguồn)
- Đã xem_vector - phạm vi hoặc mảng mà chúng tôi đang tìm kiếm giá trị mong muốn. Đây là chức năng đã giới thiệu trước đó ĐỂ TÌM, trả về mảng {#VALUE !: 4: #VALUE!}
- Vectơ_các kết quả - phạm vi mà chúng ta muốn trả về giá trị nếu giá trị mong muốn được tìm thấy trong ô tương ứng. Đây là những tên chính xác từ cột Thay thế bảng tham khảo của chúng tôi.
Đặc điểm chính và không rõ ràng ở đây là hàm XEM nếu không có kết quả khớp chính xác, hãy luôn tìm giá trị nhỏ nhất (trước đó) gần nhất. Do đó, bằng cách chỉ định bất kỳ số khổng lồ nào (ví dụ: 9999) làm giá trị mong muốn, chúng tôi sẽ buộc XEM tìm ô có số nhỏ nhất gần nhất (4) trong mảng {#VALUE !: 4: #VALUE!} và trả về giá trị tương ứng từ vectơ kết quả, tức là sửa tên công ty từ cột Thay thế.
Sắc thái thứ hai là, về mặt kỹ thuật, công thức của chúng ta là một công thức mảng, bởi vì hàm ĐỂ TÌM trả về kết quả không phải là một mà là một mảng gồm ba giá trị. Nhưng kể từ khi hàm XEM hỗ trợ mảng ngoài hộp, sau đó chúng tôi không phải nhập công thức này dưới dạng công thức mảng cổ điển - bằng cách sử dụng phím tắt Ctrl+sự thay đổi+đăng ký hạng mục thi. Một cái đơn giản sẽ đủ đăng ký hạng mục thi.
Đó là tất cả. Hy vọng bạn nhận được logic.
Nó vẫn để chuyển công thức đã hoàn thành vào ô B2 đầu tiên của cột đã sửa - và nhiệm vụ của chúng ta đã được giải quyết!
Tất nhiên, với các bảng thông thường (không thông minh), công thức này cũng hoạt động tuyệt vời (chỉ cần đừng quên khóa F4 và sửa các liên kết có liên quan):
Trường hợp 2. Thay thế một phần số lượng lớn
Trường hợp này phức tạp hơn một chút. Một lần nữa, chúng tôi có hai bảng "thông minh":
Bảng đầu tiên với các địa chỉ được viết một cách gian dối cần được sửa chữa (tôi đã gọi nó là Dữ liệu2). Bảng thứ hai là sách tham khảo, theo đó bạn cần thay thế một phần chuỗi con bên trong địa chỉ (tôi gọi là bảng này Thay người2).
Sự khác biệt cơ bản ở đây là bạn chỉ cần thay thế một đoạn của dữ liệu gốc - ví dụ: địa chỉ đầu tiên có “St. Petersburg ” bên phải “St. Petersburg ”, giữ nguyên phần còn lại của địa chỉ (mã vùng, đường phố, số nhà).
Công thức thành phẩm sẽ như thế này (để dễ nhận biết, tôi chia nó thành bao nhiêu dòng sử dụng Khác+đăng ký hạng mục thi):
Công việc chính ở đây được thực hiện bởi hàm văn bản Excel tiêu chuẩn THAY THẾ (THAY THẾ), có 3 đối số:
- Văn bản nguồn - địa chỉ bị cắt xén đầu tiên từ cột Địa chỉ
- Những gì chúng tôi đang tìm kiếm - ở đây chúng tôi sử dụng thủ thuật với hàm XEM (TRA CỨU)từ cách trước để kéo giá trị từ cột Để tìm, được bao gồm dưới dạng một đoạn trong một địa chỉ cong.
- Thay thế bằng cái gì - theo cách tương tự, chúng tôi tìm giá trị chính xác tương ứng với giá trị đó từ cột Thay thế.
Nhập công thức này với Ctrl+sự thay đổi+đăng ký hạng mục thi cũng không cần thiết ở đây, mặc dù trên thực tế, nó là một công thức mảng.
Và có thể thấy rõ ràng (xem lỗi # N / A trong hình trước) rằng công thức như vậy, vì tất cả sự sang trọng của nó, có một vài nhược điểm:
- Chức năng SUBSTITUTE phân biệt chữ hoa chữ thường, vì vậy "Spb" ở dòng áp chót không được tìm thấy trong bảng thay thế. Để giải quyết vấn đề này, bạn có thể sử dụng hàm ZAMENIT (THAY THẾ), hoặc sơ bộ đưa cả hai bảng vào cùng một thanh ghi.
- Nếu văn bản ban đầu là chính xác hoặc trong đó không có mảnh nào để thay thế (dòng cuối cùng), thì công thức của chúng ta sẽ xuất hiện một lỗi. Thời điểm này có thể được trung hòa bằng cách chặn và thay thế các lỗi bằng cách sử dụng chức năng SỐ PHIẾU (SẾ PHẠM):
- Nếu văn bản gốc chứa một số đoạn từ thư mục cùng một lúc, thì công thức của chúng tôi chỉ thay thế công thức cuối cùng (ở dòng thứ 8, Ligovsky «Avenue« thay đổi để "Pr-t", Nhưng "S-Pb" on “St. Petersburg ” không còn nữa, bởi vì “S-Pb”Cao hơn trong thư mục). Vấn đề này có thể được giải quyết bằng cách chạy lại công thức của riêng chúng tôi, nhưng đã dọc theo cột đã sửa:
Không hoàn hảo và rườm rà ở những nơi, nhưng tốt hơn nhiều so với thay thế thủ công tương tự, phải không? 🙂
PS
Trong phần tiếp theo, chúng tôi sẽ tìm ra cách thực hiện thay thế hàng loạt như vậy bằng cách sử dụng macro và Power Query.
- Cách hoạt động của hàm SUBSTITUTE để thay thế văn bản
- Tìm các văn bản phù hợp chính xác bằng cách sử dụng hàm EXACT
- Tìm kiếm và thay thế phân biệt chữ hoa chữ thường (VLOOKUP phân biệt chữ hoa chữ thường)