Jean đã hỏi câu hỏi Excel của tuần này:
Tôi đang cố gắng chuyển đổi sổ giá nhà cung cấp trong Excel để khớp mã UPC của họ với hệ thống mã UPC của chúng tôi. Sổ giá của họ có một cột trong Excel với một chuỗi số, ví dụ. 000004560007 HOẶC cel000612004. Những gì tôi phải làm là gấp hai lần. Tôi cần loại bỏ ba số 0 đầu tiên mà không xóa bất kỳ số 0 nào khác trong ô. Tiếp theo, tôi phải thêm một mã 3 chữ số "upc" trước chữ số đầu tiên trong ô. Đây sẽ là một nhu cầu liên tục. Tôi sẽ phải đào tạo một số người sử dụng hệ thống Excel.
Có vẻ như Jean đã cân nhắc sử dụng Edit-Replace để loại bỏ ba số 0. Điều này sẽ không hoạt động vì thực hiện chỉnh sửa thay thế trên "000004560007" sẽ khiến cả hai lần xuất hiện 000 biến mất.
Trước tiên, tôi muốn đề nghị Jean chèn một cột tạm thời bên cạnh mã giá hiện tại, viết công thức để thực hiện chuyển đổi, sau đó sử dụng Edit-Copy, Edit-PasteSpecial-Values để sao chép lại công thức trên mã giá gốc.
Hàm REPLACE () được ghi chép kém sẽ thực hiện thủ thuật trong trường hợp này. Tôi thất vọng với việc triển khai REPLACE (). Tôi đã nghĩ rằng nó sẽ yêu cầu một văn bản cụ thể để thay thế, nhưng thay vào đó nó yêu cầu người dùng tìm ra vị trí ký tự để thay thế. REPLACE sẽ thay thế một phần của chuỗi văn bản bằng một chuỗi mới. Bốn đối số mà bạn truyền cho hàm là ô chứa văn bản cũ, vị trí của ký tự trong văn bản cũ mà bạn muốn thay thế, số ký tự cần thay thế và văn bản mới sẽ sử dụng.
Giả định đơn giản hóa của tôi là ba số 0 đại diện cho sự khởi đầu của mã UPC trong chuỗi. Do đó, không cần phải tìm kiếm ký tự số đầu tiên trong ô. Khi công thức tìm thấy ba số 0, nó có thể thay thế ba số 0 đó bằng chuỗi "upc".
Để tìm vị trí xuất hiện đầu tiên của "000" trong văn bản, bạn sẽ sử dụng công thức sau:
=FIND(A2,"000")
Công thức mà Jean cần nhập vào ô B2 sẽ là:
=REPLACE(A2,FIND("000",A2),3,"upc")
Ý tưởng của tôi là yêu cầu các nhà phân tích định giá đánh dấu một loạt các ô và sau đó gọi macro này. Macro sẽ sử dụng một vòng lặp với "Cho mỗi ô trong Vùng chọn" ở đầu. Với vòng lặp này, "ô" trở thành một biến phạm vi đặc biệt. Bạn có thể sử dụng cell.address hoặc cell.value hoặc cell.row để tìm địa chỉ, giá trị hoặc hàng của ô hiện tại trong vòng lặp. Đây là macro:
Public Sub Jean() ' This macro will replace the first occurrence of "000" ' in each selected cell with the string "upc" ' copyright 1999 www.MrExcel.com For Each cell In Selection cell.Value = Replace(cell.Value, "000", "upc", 1, 1, vbTextCompare) Next cell End SubLưu ý rằng đây là một macro kiểu phá hủy. Khi người dùng đánh dấu một dải ô và chạy macro, các ô đó sẽ được thay đổi. Không cần phải nói rằng bạn muốn kiểm tra toàn diện macro của mình trên dữ liệu thử nghiệm trước khi đặt nó trên dữ liệu sản xuất thực. Khi bạn gặp trường hợp như Jean's, nơi bạn sẽ liên tục được yêu cầu biến đổi một cột bằng cách sử dụng một công thức phức tạp, viết một macro đơn giản như macro được minh họa ở đây có thể là một công cụ hiệu quả và tiết kiệm thời gian.
