Jean zadał pytanie dotyczące programu Excel w tym tygodniu:
Próbuję przekonwertować cennik dostawców w programie Excel, aby dopasować ich kody UPC do naszego systemu kodów UPC. Ich cennik posiada kolumnę w Excelu z szeregiem liczb np. 000004560007 LUB cel000612004. Muszę zrobić dwa razy. Muszę usunąć pierwsze trzy zera bez usuwania innych zer w komórce. Następnie muszę dołączyć 3-cyfrowy kod „upc” przed pierwszą cyfrą w komórce. Będzie to ciągła potrzeba. Będę musiał przeszkolić kilka osób do obsługi systemu Excel.
Wygląda na to, że Jean rozważał już użycie opcji Edytuj-Zamień, aby pozbyć się trzech zer. To nie zadziała, ponieważ wykonanie zamiany edycji na „000004560007” spowodowałoby zniknięcie obu wystąpień 000.
Najpierw chcę zasugerować Jean, aby wstawił tymczasową kolumnę obok aktualnych kodów cen, napisał formułę, aby dokonać transformacji, a następnie użyj Edytuj-Kopiuj, Edytuj-Wklej-Wartości-Specjalne, aby skopiować wzór z powrotem na oryginalne kody cen.
Słabo udokumentowana funkcja REPLACE () załatwi w tym przypadku sprawę. Byłem rozczarowany implementacją REPLACE (). Pomyślałbym, że poprosi o konkretny tekst do zastąpienia, ale zamiast tego prosi użytkownika o ustalenie pozycji znaków do zastąpienia. REPLACE zamieni część ciągu tekstowego na nowy. Cztery argumenty, które przekazujesz do funkcji, to komórka zawierająca stary tekst, pozycja znaku w starym tekście, który chcesz zamienić, liczba znaków do zastąpienia oraz nowy tekst do użycia.
Moje upraszczające założenie jest takie, że trzy zera reprezentują początek kodu UPC w ciągu. Dlatego nie ma potrzeby wyszukiwania pierwszego znaku numerycznego w komórce. Gdy formuła znajdzie trzy zera, może zastąpić te trzy zera ciągiem „upc”.
Aby znaleźć miejsce pierwszego wystąpienia liczby „000” w tekście, należy użyć następującego wzoru:
=FIND(A2,"000")
Formuła, którą Jean musiałby wprowadzić w komórce B2, wyglądałaby następująco:
=REPLACE(A2,FIND("000",A2),3,"upc")
Myślę, że analitycy cen powinni zaznaczyć zakres komórek, a następnie wywołać to makro. Makro użyje pętli z napisem „Dla każdej komórki w zaznaczeniu” na początku. Dzięki tej pętli „komórka” staje się specjalną zmienną zakresu. Możesz użyć cell.address, cell.value lub cell.row, aby znaleźć adres, wartość lub wiersz bieżącej komórki w pętli. Oto makro:
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 SubZauważ, że jest to makro typu destrukcyjnego. Gdy użytkownik podświetli zakres komórek i uruchomi makro, te komórki zostaną zmienione. Jest rzeczą oczywistą, że chcesz dokładnie przetestować swoje makro na danych testowych, zanim uwolnisz je od rzeczywistych danych produkcyjnych. Jeśli masz sytuację taką jak Jean, w której będziesz stale musiał przekształcić kolumnę za pomocą złożonej formuły, napisanie prostego makra, takiego jak pokazano tutaj, może być skutecznym i oszczędzającym czas narzędziem.
